Change an existing primary key for table having records; with a new identity column

Steps to change an existing primary key for table having records with a new identity column :

-- Create a sample table with primary key

Create table test3 ( id int primary key, name char(2))

go

-- Insert some records to this table

Insert into test3 values (1,'cc')

Insert into test3 values (2,'cq')

Insert into test3 values (3,'cw')

go

-- Add a new column ( this will be set primary key in subsequent steps)

Alter table test3 add new_id bigint

go

-- Get and drop the primary key constraint (to drop already created primary key)

declare @cons_name varchar(300);

Select @cons_name = name from sysobjects where xtype = 'PK' and parent_obj in ( select id from sysobjects where xtype = 'U' and name = 'test3')

exec (' Alter table test3 Drop constraint ' + @cons_name) ;

-- Update the new column with the sequence number

go

Update test3 set test3.new_id = tt.a

From (

Select row_number() over (order by name) as a ,name from test3) tt

Where test3.name = tt.name

go

-- Set the column as not null

alter table test3 alter column new_id bigint not null

go

-- Set the new column as primary key

alter table test3 add primary key (new_id)

go

Select * from test3

-- You could set the identity key to next value using dbcc_checkindent

-- DBCC CHECKIDENT ("table name", RESEED, 300)

TSQL enhancements in SQL server 2005 # Part 1

1. TOP clause with TIES option : Select TOP(4) with TIES * from test order by idkey desc

2. Supports an OUTPUT clause so a single trip to the server performs the data updateand returns the results.

3. PIVOT operator provides the ability to quickly and easily generate cross tab queries. A cross tab query rotates rows data into columns data. : select VENDORID , [7],[8],[9] from MonthlyPurchaseOrders pivot (sum(subtotal) for Ordermonth in ( [7],[8],[9],[10])) as a

4. Exception Handling with TRY/CATCH

5. ROW_NUMBER() function creates a column that displays a number corresponding the row's position in the query result : select name, row_number() over (order by name) from test

6. RANK() function works much like the ROW_NUMBER() function in that it numbers records in order. They differ in the way they work when duplicate values are contained in the ORDER BY expression. : select name, row_number() over (order by name) , rank() over ( order by name) from test

7. DENSE_RANK() works the same way as RANK() does but eliminates the gaps in the numbering.

8. NTILE() breaks the result set into a specified number of groups and assigns the same number to each record in a group

9. Common Table Expressions (CTE) A Common Table Expression (CTE) is a temporary result set created from a simple query

Using NOLOCK and READPAST table hints in SQL Server

NOLOCK

This table hint, also known as READUNCOMMITTED, is applicable to SELECT statements only. NOLOCK indicates that no shared locks are issued against the table that would prohibit other transactions from modifying the data in the table.

The following example shows how NOLOCK works and how dirty reads can occur. In the script below, I begin a transaction and insert a record in the SalesHistory table.

SELECT COUNT(*) FROM SalesHistory WITH(NOLOCK)
 
The number of records returned is 301. Since the transaction that entered the  record into the SalesHistory table has not been committed, I can undo it. I'll  roll back the transaction by issuing the following statement:
ROLLBACK TRANSACTION
 
This statement removes the record from the SalesHistory table that I  previously inserted. Now I run the same SELECT statement that I ran earlier:
SELECT COUNT(*) FROM SalesHistory WITH(NOLOCK)
 
This time the record count returned is 300. My first query read a record that  was not yet committed -- this is a dirty read.

READPAST
 
This is a much less commonly used table hint than NOLOCK. This hint specifies  that the database engine not consider any locked rows or data pages when  returning results
The READPAST table hint example is very similar to the NOLOCK table hint  example. I'll begin a transaction and update one record in the SalesHistory  table.
BEGIN TRANSACTION
      UPDATE TOP(1) SalesHistory
      SET SalePrice = SalePrice + 1
 
Because I do not commit or roll back the transaction, the locks that were  placed on the record that I updated are still in effect. In a new query editor  window, run the following script, which uses READPAST on the SalesHistory table  to count the number of records in the table.
SELECT COUNT(*)
FROM SalesHistory WITH(READPAST)
 
My SalesHistory table originally had 300 records in it. The UPDATE statement  is currently locking one record in the table. The script above that uses  READPAST returns 299 records, which means that because the record I am updating  is locked, it is ignored by the READPAST hint.

SQL 2005 - Save a password not as clear text

In SQL Server 2005, you can use HashBytes to hash the password and EncryptByKey to encrypt it.

Examples

A. Simple symmetric encryption

The following code shows how to encrypt a column by using a symmetric key.

USE AdventureWorks;

GO

--If there is no master key, create one now.

IF NOT EXISTS

(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)

CREATE MASTER KEY ENCRYPTION BY

PASSWORD = '23987hxJKL969#ghf0%94467GRkjg5k3fd117r$$#1946kcj$n44nhdlj'

GO

CREATE CERTIFICATE HumanResources037

WITH SUBJECT = 'Employee Social Security Numbers';

GO

CREATE SYMMETRIC KEY SSN_Key_01

WITH ALGORITHM = AES_256

ENCRYPTION BY CERTIFICATE HumanResources037;

GO

USE [AdventureWorks];

GO

-- Create a column in which to store the encrypted data.

ALTER TABLE HumanResources.Employee

ADD EncryptedNationalIDNumber varbinary(128);

GO

-- Open the symmetric key with which to encrypt the data.

OPEN SYMMETRIC KEY SSN_Key_01

DECRYPTION BY CERTIFICATE HumanResources037;

-- Encrypt the value in column NationalIDNumber with symmetric

-- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.

UPDATE HumanResources.Employee

SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'), NationalIDNumber);

GO

-- Verify the encryption.

-- First, open the symmetric key with which to decrypt the data.

OPEN SYMMETRIC KEY SSN_Key_01

DECRYPTION BY CERTIFICATE HumanResources037;

GO

-- Now list the original ID, the encrypted ID, and the

-- decrypted ciphertext. If the decryption worked, the original

-- and the decrypted ID will match.

SELECT NationalIDNumber, EncryptedNationalIDNumber

AS 'Encrypted ID Number',

CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))

AS 'Decrypted ID Number'

FROM HumanResources.Employee;

GO

Output option in SQL 2005

"Output" provides you access to inserted and deleted logical tables. The data that has been inserted into the table OR the data after update statement has been executed, is available in inserted logical table.

declare @temptable table
(
empid int ,
name varchar(60)
)
UPDATE AllEmployees set NAME = 'upd'
OUTPUT inserted.employee_id , inserted.name into @temptable
WHERE Employee_id = 9

select * from @temptable

SQL 2005 use level for hierarchy select

To get all the people in the hierarchy starting from Top management we could use the the following query:

Create table AllEmployees ( Employee_id int primary key identity , name varchar(100), ManagerId int)
select * from AllEmployees
insert into AllEmployees(name,managerid) values ( E1, null)
insert into AllEmployees(name,managerid) values ( E2, null)
insert into AllEmployees(name,managerid) values ( 'E3' , 1)
insert into AllEmployees(name,managerid) values ( 'E4' , 2)
insert into AllEmployees(name,managerid) values ( 'E5' , 1)
insert into AllEmployees(name,managerid) values ( 'E6' , 2)
insert into AllEmployees(name,managerid) values ( 'E7' , 4)
insert into AllEmployees(name,managerid) values ( 'E8' , 4)
With LvlAllemployees as
(
select Allemployees.employee_id, Allemployees.name , Allemployees.managerid, 0 as level from AllEmployees where ManagerId is null
union ALL
select Allemployees.employee_id, Allemployees.name , Allemployees.managerid, level+1 as level from LvlAllEmployees
inner join Allemployees on LvlAllemployees.employee_id = allemployees.managerid
)
select * from Lvlallemployees order by level

View contents of a Stored Procedure (quickly)

USe the procedure ‘SP_HELPTEXT ’ to view the content of the stored rpocedure from T-SQL . If you are extensive user of the T-SQL stored procedure, you may assing ehte short key from Tools -> Options -> Keyboard and type in SP_HELPTEXT for a key of your choice.