Index Rebuild This process drops the existing Index and Recreates the index Rebuild all indexes for the respective table ALTER INDEX ALL ON < table name > REBUILD; Rebuild specific indexes for the specific table ALTER INDEX < Index Name > ON < Table Name > REBUILD; Index Reorganize This process physically reorganizes the leaf [...]

SQL Data Manipulation Language (DML): DML is a syntax for executing queries and DML component of SQL comprises have four basic statements: SELECT – Retrieve rows from tables UPDATE – Modify the rows of tables DELETE – Remove rows from tables INSERT – Add new rows to tables. SQL Data Definition Language (DDL): DDL is [...]

Returns the number of rows affected by the last statement. It will let you to do a checking on the record you updated. If the number of rows is more than 2 billion, use ROWCOUNT_BIG. Example USE DB2008; GO UPDATE User SET JobTitle = ‘Manager’ WHERE UserID = ‘u10021′ IF @@ROWCOUNT = 0 PRINT ‘Warning: [...]

This SQL Split Function is use to SPLIT a sentences based on the Delimeter. Delimeter is a string character used to identify substring limits. –Below is Split Function in SQL DECLARE @NextString NVARCHAR(40) DECLARE @Pos INT DECLARE @NextPos INT DECLARE @String NVARCHAR(40) DECLARE @Delimiter NVARCHAR(40) SET @String =’Paro|Haa|Thimphu’ SET @Delimiter = ‘|’ SET @String = [...]

It is easy to query if the default value of the field is NULL, while in certain case when the field is empty. In such case execute the query like below. SELECT * FROM tblUser WHERE (LastName IS NULL) OR (RTRIM(LastName ) = ”) Happy SQLing

In some cases you need to locate if there are duplicate record in a table and you are stuck how to go about, then here’s how you can find it. SELECT cellno, COUNT(cellno) AS NumOccurrences FROM tblClientCellNumber GROUP BY cellno HAVING (COUNT(cellno) > 1)

I came across a problem where i deleted on record accidentally and that record id was in use. So I had to Enter that ID in Identity Column and rest of the data in respective fields of that record line. I could add values in other fields but could not enter in Identity Column. So [...]

Database size keeps on growing daily, and I wanted to know the size of my database. Finally i got a query that fulfills my requirement: SELECT sysDa.Name,sysDa.create_date,sysDa.recovery_model_desc, temp.DBSize8KBPage FROM ( SELECT sysMas.database_ID, sysMas.size, SUM(size) as DBSize8KBPage FROM sys.master_Files sysMas GROUP BY sysMas.DataBase_ID, sysMas.size ) temp INNER JOIN Sys.Databases sysDa on temp.Database_ID = sysDa.DataBase_ID

For a Stored Procedure to be eligible to be executed when SQL Server starts, the stored procedure must be in the “master” database and cannot contain INPUT or OUTPUT parameters. The sp_procoption system stored procedure is useful in setting the Stored Procedure for autoexecution – i.e it runs every time SQL Server service is started. [...]

A very simple query but yet so useful in getting the information of tables size in your database: USE [yourdbname] EXEC sp_MSforeachtable @command1=”EXEC sp_spaceused ‘?’”