1. keeping @@identity in a trigger

-- The ResetIdentity stored procedure
CREATE TRIGGER [tg_i_orders] ON [Orders] 
FOR INSERT 
AS
DECLARE @neworder AS int
SELECT @neworder = @@identity
INSERT INTO other_table (orderid, customerid, orderdate)
  SELECT * FROM inserted
EXEC ResetIdentity @neworder
GO

-- The revised trigger
-- In SQL Server 6.5 drop and recreate the trigger
CREATE PROC ResetIdentity
  @prevident AS int
AS
DECLARE @SQL AS varchar(100)
SELECT @SQL =
  'SELECT IDENTITY(int, ' +
            CAST(@prevident as varchar) +
            ', 1) AS ident ' +
  'INTO #TmpIdent'
EXEC(@SQL)
GO

2. to add identity column to a table:

ALTER TABLE SomeTable ADD col_x INTEGER NOT NULL UNIQUE IDENTITY(1,1)
or 
select identity(int,1,1), fields into #temp_table from SomeTable

3. to create sequence number out of a unique primary key:
 SELECT
  (SELECT COUNT(*)
   FROM Authors
   WHERE au_id <= A.au_id) AS id
  ,*
  FROM Authors AS A

4. to generate unique id:

CREATE PROCEDURE GetNextId
AS
  DECLARE @MyNextId int -- Or whatever datatype is correct.

  UPDATE TheIDTable
    SET @MyNextId = TheID,
        TheID = TheID + 1

  SELECT myNextId = @MyNextId
GO

5. To open a cursor:

declare @haha varchar(100)
DECLARE Employee_Cursor CURSOR FOR
  SELECT LastName FROM Northwind.dbo.Employees
-- DECLARE @C CURSOR
-- SET @C=CURSOR FOR SELECT ID FROM my_table WHERE ROWID=1
OPEN Employee_Cursor
FETCH NEXT FROM Employee_Cursor into @haha
WHILE @@FETCH_STATUS = 0
BEGIN
  print @haha
  -- UPDATE SET ID='NEW_ID' WHERE CURRENT OF @C
  FETCH NEXT FROM Employee_Cursor into @haha
END
CLOSE Employee_Cursor
DEALLOCATE Employee_Cursor


6. Returning rows as result set:

CREATE PROCEDURE byroyalty @percentage int
AS
select au_id from titleauthor
where titleauthor.royaltyper = @percentage

7. Returning one row from a variable:

CREATE PROCEDURE myproc @parameter int AS
declare @myint int
set @myint = @parameter
select @myint as myint

8. To convert an identity column into a normal column

   select identity_col+0, field2, from table
   select convert(int, identity_col), field2 from table

9. parameters and returned values:

   CREATE PROCEDURE myproc @parm INT OUTPUT AS
   set @parm=@parm+1

   declare @xx int;
   set @xx=1;
   exec myproc @xx output;
   print @xx    <-- prints 2

   =sqlexec(handle, "exec myproc ?@xxx")
   =sqlexec(handle, "{call myproc(?@xxx)}")

   CREATE PROCEDURE myproc2 @parm INT AS
   return @parm+1

   declare @xx int;
   set @xx=3;
   exec @xx=myproc2 @xx;
   print @xx	<-- prints 4

   =sqlexec(m.handle, "{?@xxx = call another(?xxx)}")

10. 
create function initcap (@text varchar(4000))
returns varchar(4000)
as
begin
	declare @counter int, 
		@length int,
		@char char(1),
		@textnew varchar(4000)

	set @text	= rtrim(@text)
	set @text	= lower(@text)
	set @length 	= len(@text)
	set @counter 	= 1

	set @text = upper(left(@text, 1) ) + right(@text, @length - 1) 

	while @counter <> @length --+ 1
	begin
		select @char = substring(@text, @counter, 1)

		IF @char = space(1)  or @char =  '_' or @char = ','  or @char = '.' or @char = '\' or @char = '/' or @char = '(' or @char = ')'
		begin
			set @textnew = left(@text, @counter)  + upper(substring(@text, @counter+1, 1)) + right(@text, (@length - @counter) - 1)
			set @text	 = @textnew
		end

		set @counter = @counter + 1
	end

	return @text
end

11. Datetime computations:

http://www.aspfaq.com/show.asp?id=2271

DECLARE @dt1 DATETIME, @dt2 DATETIME 
SET @dt1 = '2002-03-27 09:20:25' 
SET @dt2 = '2002-03-27 09:20:45' 
 
DECLARE @seconds INT, @minutes INT, @hours INT 
DECLARE @secStr VARCHAR(2), @minStr VARCHAR(2), @hoursStr VARCHAR(4) 
DECLARE @diff CHAR(10) 
 
SET @seconds = DATEDIFF(SECOND, @dt1, @dt2)  
 
SET @minutes = @seconds / 60 
SET @hours = @minutes / 60 
 
SET @minutes = @minutes % 60 
SET @seconds = @seconds % 60 
 
SET @secStr = CAST(@seconds AS VARCHAR(2)) 
SET @minStr = CAST(@minutes AS VARCHAR(2)) 
SET @hoursStr = CAST(@hours AS VARCHAR(2)) 
 
SET @diff = LEFT('00'+@hoursStr,2) + ':'  
    + LEFT('00'+@minStr,2) + ':' 
    + LEFT('00'+@secStr,2) 
 
SELECT diff = @diff 

12. To debug stored procedure:

SET NOEXEC ON|OFF

http://sqljunkies.com/weblog/sqlpartner/posts/479.aspx

sp_helptext "store_proc_name"


13. Dynamic SQL

declare @SQLStatement nvarchar(4000)
@TableName sysname,
@ColumnAValue nvarchar(200)
set @TableName = 'OrderFor' + convert(nvarchar(50),datepart
( month(getdate()))
set @ColumnAValue = convert(nvarchar(50),datepart(day
(getdate())

set @SQLStatement =  'Select * from ' + @TableName + ' 
where ColumnA = ''' + @ColumAValue " '''

execute sp_ExecuteSQL @SQLStatement

14.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
-- do something
COMMIT TRAN

15.  Duplicate a table using Enterprise Manager

Open the Enterprise Manager,
Open your database
Right Click on the table
Select 'All Tasks'
Select 'Generate Scripts'
Click the formatting tab
REMOVE THE CHECK TO DROP EXISTING TABLES! , check everything else except the last item
Click the OPtions Tab
Select: 'Script Object Permissions', 'Script Indexes', 'Script Triggers', 'Script Primary Keys'
DO NOT - DO NOT script the database!!!
Click the General tab
Click Preview
Click Copy

Paste into Notepad. 

16. Paginate a table

http://www.aspfaq.com/show.asp?id=2120

CREATE PROCEDURE SampleCDs_Paging_Subquery 
    @pagenum INT = 1, 
    @perpage INT = 50 
AS 
BEGIN 
    SET NOCOUNT ON 
 
    DECLARE 
        @ubound INT, 
        @lbound INT, 
        @pages INT, 
        @rows INT 
 
    SELECT 
        @rows = COUNT(*), 
        @pages = COUNT(*) / @perpage 
    FROM 
        SampleCDs WITH (NOLOCK) 
 
    IF @rows % @perpage != 0 SET @pages = @pages + 1 
    IF @pagenum < 1 SET @pagenum = 1 
    IF @pagenum > @pages SET @pagenum = @pages 
 
    SET @ubound = @perpage * @pagenum  
    SET @lbound = @ubound - (@perpage - 1)  
 
    /*
		SELECT 
        CurrentPage = @pagenum, 
        TotalPages = @pages, 
        TotalRows = @rows 
		*/
 
    -- this method uses a COUNT subquery to  
    -- peg a sliding range to the desired set 
    -- of rows 
 
    SELECT 
        A.ArtistName, 
        A.Title 
    FROM 
        SampleCDs A WITH (NOLOCK) 
    WHERE 
    ( 
        SELECT COUNT(*) 
        FROM SampleCDs B WITH (NOLOCK) 
        WHERE B.ArtistName+'~'+B.Title 
        <= A.ArtistName+'~'+A.Title 
    ) 
    BETWEEN 
        @lbound AND @ubound 
    ORDER BY 
        A.ArtistName, 
        A.Title 
END 

17. Clean the transaction log

http://www.support.microsoft.com/?id=272318

18. Deatach a DB

ALTER DATABASE <DBNAME> SET SINGLE_USER WITH Rollback Immediate
GO
SP_Detach_db <dbname>

19. 

  select max(AA.EFF_DATE) as XDATE, BB.* ;
    from WA_EXP AA, WA_EXP BB ;
    group by AA.EMP_NO, BB.EFF_DATE ;
    where AA.EMP_NO = BB.EMP_NO ;
      and AA.EFF_DATE <= m.ddate ;
    having BB.EFF_DATE = XDATE


20. Every nth item in a table

SELECT empno
FROM Employees AS E1
WHERE EXISTS (
  SELECT MAX(empno)
  FROM Employees AS E2
  WHERE E1.empno >= E2.empno
  HAVING (COUNT(*) MOD n) = 0
  
SELECT E1.empno
FROM Employees AS E1, Employees AS E2
WHERE (E1.empno >= E2.empno)
GROUP BY E1.empno
HAVING (COUNT(*) MOD n) = 0;