ITPub博客

两种方法 方法一： 　　用触发器 建一个序列 create sequence a_seq increment by 1 start with 100; 建一个触发器, 自动+1 create or replace trigger your_seq_tri before insert on your_table1 for each row declare next_id number; begin select your_seq.nextval into next_id from dual; :new.id := next_id; end; 方法二： 　　建一个序列 create sequence

运行 select * from table where id in (select in from othertable) 出现： collation是SQL_Latin1_General_CP1_CI_AS，而SQL Server的默认是Chinese_PRC_CI_AS。 解决方法：修改othertable的collation后边有更详细的reference[@more@]我就决定去修改原来aspnet_regsql使用的sql文件。原来的sql文件没有包含有collation的东西的。我在每个字符数据类型的字段都加上collate database_default 。但一般这些都是在SELECT语句中使用就OK了。 不能如此，否则会天下大乱的。我改用alter database [DB Name] collate Chinese_PRC_CI_AS 来

Learn 10 useful tips and tricks that help boost SQL Server application performance—and streamline development and deployment as well. 学习10个有用的技巧来提高SQL Server应用程序的运行效率，简化开发和部署。－by Roman Rehak, 本文属于转载，谢谢原作者Roman Rehak. Developers love database programming tips, tricks, and workarounds—especially those that slash development time or boost application performance. Here's a collection of such nuggets for developing SQL Server applications.[@mor

本文节选自MSDN的文章《五种提高 SQL 性能的方法》，提出如何提高基于SQL Server应用程序的运行效率，非常值得推荐。对一些Traffic很高的应用系统而言，如何提高和改进SQL指令，是非常重要的，也是一个很好的突破点。*文章主要包括如下一些内容（如感兴趣，请直接访问下面的URL阅读完整的中英文文档）：[@more@] 1, 从 INSERT 返回 IDENTITY SELECT @@IDENTITY2, 内嵌视图与临时表 临时表 － 在 tempdb 中的临时表会导致查询进行大量 I/O 操作和磁盘访问，临时表会消耗大量资源。内嵌视图 －使用内嵌视图取代临时表。内嵌视图只是一个

KILL { session ID | UOW } [ WITH STATUSONLY ][@more@] 参数 session ID 要终止的进程的会话 ID。session ID 是在建立连接时为每个用户连接分配的唯一整数 (int)。在连接期间，会话 ID 值与该连接捆绑在一起。连接结束时，则释放该整数值，并且可以将它重新分配给新的连接。 使用 KILL session ID 可终止与指定的会话 ID 关联的常规非分布式事务和分布式事务。 UOW 标识分布式事务的工作单元 ID (UOW - Unit of Work ID)。UOW is a character string that may be obtained from the syslockinfo table, which gives the UOW fo

大多数SQL Server表需要索引来提高数据的访问速度，如果没有索引，SQL Server 要进行表格扫描读取表中的每一个记录才能找到索要的数据。索引可以分为簇索引和非簇索引，簇索引通过重排表中的数据来提高数据的访问速度，而非簇索引则通过维护表中的数据指针来提高数据的索引。[@more@]1. 索引的体系结构 为什么要不断的维护表的索引？首先，简单介绍一下索引的体系结构。SQL Server在硬盘中用8KB页 面在数据库文件内存放数据。缺省情况下这些页面及其包含的数据是无组织的。为了使混乱变为有序，就要生成索引。生成索引后，就有了索引页和

Updated with an author's note When this article was first published, it produced some lively debate. It was believed by some that the article misled readers into thinking that NOLOCK should be a tool for performance tuning, which it is not. There is a divided opinion on the appropriate use of NOLOCK, and therefore I am adding this foreword to my article in an effort to clarify matters. NOLOCK is a query optimizer hint. It has advantages, and disadvantages, and it potentially breaks

I am neither a big fan of XML nor an expert. Over the years I have seen XML used and abused yet another silver bullet to solve all of our problems. I have seen XML fail on many projects because it is too verbose - consuming too much bandwidth and processing power where good ol' fixed-length records would have been fine. I have also seen XML as a good solution to interfacing problems and I (almost) unknowingly use it every day interfacing with web services and configuring systems.

Microsoft® SQL Server™ 2000 uses locking to ensure transactional integrity and database consistency. Locking prevents users from reading data being changed by other users, and prevents multiple users from changing the same data at the same time. If locking is not used, data within the database may become logically incorrect, and queries executed against that data may produce unexpected results. Although SQL Server enforces locking automatically, you can design applications th

Transact-SQL Referencesp_lock Reports information about locks.http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_la-lz_6cdn.asp[@more@] Syntax sp_lock [[@spid1 =] 'spid1'] [,[@spid2 =] 'spid2'] Arguments [@spid1 =] 'spid1' Is the Microsoft® SQL Server™ process ID number from master.dbo.sysprocesses. spid1 is int, with a default of NULL. Execute sp_who to obtain process infor

Controlling Transactions and Locks with Hints and Snapshots in SQL 2000 and 2005. Locks Locks are the mechanism databases use to control problems that may arise from simultaneous multi-user access. Some common issues locks try to manage include; what happens when two users try to change the same piece of information at the same time - who wins? Or user one tries to read data while user two is making changes on the same information, - which version of the data should user one see, c

1. 监控事例的等待 select event,sum(decode(wait_Time,0,0,1)) "Prev", sum(decode(wait_Time,0,1,0)) "Curr",count(*) "Tot" from v$session_Wait group by event order by 4; [@more@] 2. 回滚段的争用情况 select name, waits, gets, waits/gets "Ratio" from v$rollstat a, v$rollname b where a.usn = b.usn; 3. 监控表空间的 I/O 比例 select df.tablespace_name name,df.file_name "file",f.phyrds pyr, f.

http://www.ixpub.net/644838.html[@more@]

在微软的ASP编程体系中，ADO对象的建立，使得从网页访问数据库成为一 件易事，特别是ADO的Recordset对象使得控制数据的输出显示更为方便、自由。而在Visual InterDev6.0(以下简称VI6.0)中，由于Script Object Model（以下简称SOM）、Design-Time Control(以下简称DTC)以及Data Environment Object Model(以下简称DEOM)等对象模型的引入，使网页对数据库的访问设计显得更为方便。 　　 因为主题方面的原因，关于数据库的连接，下文只给出代码和简要注释，而把重点放在如何利用Recordset对象（或控件）实现数据记录的分页显示方面。 根

Blake Savage gazed in horror at the string of errors in the database activity log of his communications module. It was always at the worst possible moment that SQL Server grenaded itself and produced a corrupt database. Without the database data, all off-planet communications were impossible! How were they to get back to their ship?[@more@]The front of his state-of-the-art 'Goldfish' helmet began to steam up, as panic set in. "Captain", shouted Blake over his futuristic intercom,

ADO (Active Data Objects) is really the way to access data right now. It's pretty much replaced RDO and DAO for new projects. ADO.Net is on the way, but not quite here yet. Whether you're a DBA or a developer, ADO is something you need to know. This week I'm going to explore the Connection object, in upcoming articles I'll also provide some introductory coverage of both the Recordset and Command objects.[@more@]If you have VB or Access, you'll find those environments are great productivit

anaging large amounts of data is always a challenge. Several major database vendors claim that their database engines are ready for terabytes of data, which is true to a certain extent. However, when those terabytes are not static data, but live information that people update constantly, search on, report from, and run web applications against, and when the number of concurrent users accessing the data peaks significantly as well, providing fast, unblocked access to those terabytes beco

简介 像 SQL Server 这样的数据库管理系统依赖于文件输入/输出操作的及时进行。有故障或配置不当的硬件、固件设置、筛选器驱动程序、压缩、程序错误以及 I/O 路径内的其他情况都可能导致阻塞或延迟 I/O 问题，并且很快对 SQL Server 性能产生消极影响。 上述问题对 SQL Server 的影响因问题细节的不同而差异很大，但它们通常导致阻塞、锁存器争用和超时、过长的响应时间以及资源的过度利用。[@more@]阻塞 I/O 是指必须进行外部干预才能完成的 I/O 请求（通常是 I/O 请求包 (IRP)）。这种状况通常需要执行完整的系统重新启动或类似操

存储过程的种类： 1.系统存储过程：以sp_开头,用来进行系统的各项设定.取得信息.相关管理工作, 如 sp_help就是取得指定对象的相关信息 2.扩展存储过程 以XP_开头,用来调用操作系统提供的功能 exec master..xp_cmdshell 'ping 10.8.16.1' 3.用户自定义的存储过程,这是我们所指的存储过程 [@more@]常用格式 Create procedure procedue_name [@parameter data_type][output] [with]{recompile|encryption} as sql_statement 解释: output：表示此参数是可传回的 with {recompile|encryption} recomp

SQL Server 2005 Replication[@more@]一、准备工作，如果完成则可跳过。 1、内网DB服务器作为发布服务器，外网DB服务器作为订阅服务器。 发布服务器和订阅服务器上分别创建Windows用户jl,密码jl，隶属于administrators，注意要保持一致。 2、发布服务器上创建一个共享目录，作为发布快照文件的存放目录。例如：在D盘根目录下建文件夹名为SqlCopy，设置用户jl，权限为完全控制。 3、确定发布服务器和订阅服务器的数据库autoweb保持一致。 4、在发布服务器和订阅服务器的SQL Server中创建用户登陆名jl，作为发布数据库autowe