MSDN-SQL小摘抄[积累更新中...]

--set indentity on 任何时候，一个会话中只有一个表的 IDENTITY_INSERT 属性可以设置为 ON。如果某个表已将此属性设置为 ON，则对另一个表发出 SET IDENTITY_INSERT ON 语句时，SQL Server 将返回一个错误信息，指出 SET IDENTITY_INSERT 已设置为 ON，并报告已将其属性设置为 ON 的表。 如果插入值大于表的当前标识值，则 SQL Server 自动将新插入值作为当前标识值使用。 SET IDENTITY_INSERT 的设置是在执行或运行时设置的，而不是在分析时设置的。 以下示例将创建一个包含标识列的表，并显示如何使用 SET IDENTITY_INSERT 设置来填充由 DELETE 语句导致的标识值中的空隙。 USE AdventureWorks; GO -- Create tool table. CREATE TABLE dbo.Tool( ID INT IDENTITY NOT NULL PRIMARY KEY, Name VARCHAR(40) NOT NULL ) GO -- Inserting values into products table. INSERT INTO dbo.Tool(Name) VALUES ('Screwdriver') INSERT INTO dbo.Tool(Name) VALUES ('Hammer') INSERT INTO dbo.Tool(Name) VALUES ('Saw') INSERT INTO dbo.Tool(Name) VALUES ('Shovel') GO -- Create a gap in the identity values. DELETE dbo.Tool WHERE Name = 'Saw' GO SELECT * FROM dbo.Tool GO -- Try to insert an explicit ID value of 3; -- should return a warning. INSERT INTO dbo.Tool (ID, Name) VALUES (3, 'Garden shovel') GO -- SET IDENTITY_INSERT to ON. SET IDENTITY_INSERT dbo.Tool ON GO -- Try to insert an explicit ID value of 3. INSERT INTO dbo.Tool (ID, Name) VALUES (3, 'Garden shovel') GO SELECT * FROM dbo.Tool GO -- Drop products table. DROP TABLE dbo.Tool GO

2.RAND (Transact-SQL) Returns a pseudo-random float value from 0 through 1, exclusive. Transact-SQL Syntax Conventions Syntax RAND ( [ seed ] ) Arguments seed Is an integer expression (tinyint, smallint, or int) that gives the seed value. If seed is not specified, the SQL Server Database Engine assigns a seed value at random. For a specified seed value, the result returned is always the same. Return Types ---返回类型float类型 float Remarks Repetitive calls of RAND() with the same seed value return the same results. For one connection, if RAND() is called with a specified seed value, all subsequent calls of RAND() produce results based on the seeded RAND() call. For example, the following query will always return the same sequence of numbers. SELECT RAND(100), RAND(), RAND() Examples The following example produces four different random numbers that are generated by the RAND function. ---同一个连接下的随机数的种子都是特定相同的,所以结果相同. DECLARE @counter smallint; SET @counter = 1; WHILE @counter < 5 BEGIN SELECT RAND() Random_Number SET @counter = @counter + 1 END; GO A common way to generate random numbers from RAND is to include something relatively variable as the seed value, such as adding several parts of a GETDATE: 通过结合用GETDATE函数,可以使产生不同结果的随机数,但是如果是在毫秒级中批量产生随机数,由于时间相等,会产生重复现象,这时就要考虑加入其它的参量来产生不重复随机数了,比如在前台程序中用GUID. SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 ) + (DATEPART(ss, GETDATE()) * 1000 ) + DATEPART(ms, GETDATE()) );When you use an algorithm based on GETDATE to generate seed values, RAND can still generate duplicate values if the calls to RAND are made within the interval of the smallest datepart used in the algorithm. This is especially likely when the calls to RAND are included in a single batch. Multiple calls to RAND in a single batch can be executed within the same millisecond. This is the smallest increment of DATEPART. In this case, incorporate a value based on something other than time to generate the seed values.

3.DBCC INPUTBUFFER Displays the last statement sent from a client to an instance of Microsoft SQL Server. .语法: DBCC INPUTBUFFER ( session_id [ , request_id ] ) [WITH NO_INFOMSGS ] .参数: session_id Is the session ID associated with each active primary connection. request_id Is the exact request (batch) to search for within the current session. SELECT request_id --获取request_id，这个是可选项，也可以不要 FROM sys.dm_exec_requests WHERE session_id = @@spid WITH Enables options to be specified. NO_INFOMSGS Suppresses all informational messages that have severity levels from 0 through 10. .产生的结果集{下面是一个举例结果集} EventType Parameters EventInfo -------------- ---------- --------------------- Language Event 0 DBCC INPUTBUFFER (11) 使用权限： 1）User must be a member of the sysadmin fixed server role. 2）User must have VIEW SERVER STATE permission. 3）session_id must be the same as the session ID on which the command is being run. To determine the session ID execute the following query: