Techie Madness

Primary Key: Can be only one in a table It never allows null values Primary Key is a unique key identifier and can not be null Unique Key: Can be more than one unique key in one table Unique key can have null values(only single null is allowed) It can be a candidate key Unique key can be null and may not be unique

Functions are computed values and cannot perform permanent environmental changes to SQL Server (i.e. no INSERT or UPDATE statements allowed). A Function can be used inline in SQL Statements if it returns a scalar value or can be joined upon if it returns a result set.

UPDATE  SimpleTable SET  Gender  =  CASE  Gender  WHEN  'male'  THEN  'female'  ELSE  'male'  END GO SELECT  * FROM  SimpleTable GO

With a clustered index the rows are stored physically on the disk in the same order as the index. There can therefore be only one clustered index. With a non clustered index there is a second list that has pointers to the physical rows. You can have many non clustered indexes, although each new index will increase the time it takes to write new records. It is generally faster to read from a clustered index if you want to get back all the columns. You do not have to go first to the index and then to the table. Writing to a table with a clustered index can be slower, if there is a need to rearrange the data.

If we want to delete data from large table with some where caluse, the best way to do is : Select COUNT(*)  FROM  [Server].[Database].[MyTable]  WHERE ... By doing above we can know the count of records to be deleted. DECLARE @RowsDeleted INTEGER DECLARE @RowsToDeleteAtATime SET @RowsDeleted = 1 SET  @RowsToDeleteAtATime = 50000 WHILE (@RowsDeleted > 0)     BEGIN         DELETE TOP ( @RowsToDeleteAtATime ) FROM [Server].[Database].[MyTable] WHERE ...         SET @RowsDeleted = @@ROWCOUNT     END And by doing : Select COUNT(*)  FROM [Server].[Database].[MyTable] WHERE ... We can know the status.