SQL Server Tell You Which Indexes to Rebuild

SELECT 'ALTER INDEX [' + ix.name + '] ON [' + s.name + '].[' + t.name + '] ' +        CASE WHEN ps.avg_fragmentation_in_percent > 40 THEN 'REBUILD' ELSE 'REORGANIZE' END +        CASE WHEN pc.partition_count > 1 THEN ' PARTITION = ' + cast(ps.partition_number as nvarchar(max)) ELSE '' END FROM   sys.indexes AS ix INNER JOIN sys.tables t            ON t.object_id = ix.object_id        INNER JOIN sys.schemas s            ON t.schema_id = s.schema_id        INNER JOIN (SELECT object_id, index_id, avg_fragmentation_in_percent, partition_number                    FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL)) ps            ON t.object_id = ps.object_id AND ix.index_id = ps.index_id        INNER JOIN (SELECT object_id, index_id, COUNT(DISTINCT partition_number) AS partition_count                    FROM sys.partitions                    GROUP BY object_id, index_id) pc            ON t.object_id = pc.object_id AND ix.index_id = pc.index_id WHERE  ps.avg_fragmentation_in_percent > 10 AND        ix.name IS NOT NULL

The above query will give you a list of recommended index rebuild / reorganize statements for your database, according Pinal Dave’s 10-40 rule, although you are welcome to tweak it to your liking. It supports non-partitioned as well as partitioned indexes. If you want a more intense check for fragmentation, change the last NULL in thedm_db_index_physical_stats call to 'SAMPLED' or even 'DETAILED' (include quotes).

It’s a handy little tool for database administrators and saves a lot of the hassle of monitoring index fragmentation.

SQL SERVER – Find Stored Procedure Related to Table in Database – Search in All Stored Procedure

Following code will help to find all the Stored Procedures (SP) which are related to one or more specific tables. sp_help and sp_depends does not always return accurate results.

----Option 1
SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%tablename%'
----Option 2
SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%tablename%'

Reference : Pinal Dave (http://www.SQLAuthority.com)

Try Catch Error Handling in Sqlserver2005

BEGIN TRY
– Generate a divide-by-zero error.
SELECT 1/0;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO

Here:-


• ERROR_NUMBER: returns the error number, and is the same value of @@ERROR.
• ERROR_SEVERITY: returns the severity level of the error that invoked the CATCH block.
• ERROR_STATE: returns the state number of the error.
• ERROR_LINE: returns the line number where the error occurred.
• ERROR_PROCEDURE: returns the name of the stored procedure or trigger for which the error occurred.
• ERROR_MESSAGE: returns the full message text of the error. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.

Get Common Records From Two Tables Without Using Join

If you are using SQL Server 2005, then you can use Intersect Key word, which gives you common records.

SELECT column1
FROM table1
INTERSECT
SELECT column1
FROM table2

If you want in the output both column1 and column2 from table1 which has common columns1 in both tables.

SELECT column1, column2
FROM table1
WHERE column1 IN
(
SELECT column1
FROM table1
INTERSECT
SELECT column1
FROM table2
)

Connection Strings For .NET and Sqlserver 2005

Standard Security

Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

Trusted Connection

Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;

Connecting to an SQL Server instance

Server=myServerName\theInstanceName;Database=myDataBase;
Trusted_Connection=True;

Connecting to an SQL Server instance via an IP address

Data Source=192.168.1.80,Network Library=DBMSSOCN;
Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;

Sql-List All Stored Procedure Modified in Sqlserver

In SqlServer 2000

SELECT ROUTINE_NAME,CREATED,LAST_ALTERED,ROUTINE_TYPE
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE LIKE '%Proc%'
ORDER BY LAST_ALTERED DESC

ROUTINE_TYPE can be "Function","Procedure"


In Sqlserver 2oo5

SELECT name,create_date, modify_date
FROM sys.objects
WHERE type = ‘P’
AND DATEDIFF(D,modify_date, GETDATE()) < 7

This will return a list of all user defined stored procedures in a database along with their name, creation date & last modified date. This will only work if the ALTER command was used to update the sprocs, if the sprocs were dropped and re-created only the create_date will contain any useful information.

SQL SERVER - DECLARE Multiple Variables in One Statement

5 days ago. one of my friend asking me that can be declare multiple variable in one statement.
i found that its possible and thought to post this to my blog.

Method 1: Multiple statements for declaring multiple variables

DECLARE @Var1 INT
DECLARE @Var2 INT
SET @Var1 = 1
SET @Var2 = 2
SELECT @Var1 ‘Var1′, @Var2 ‘Var2′



Method 2: Single statements for declaring multiple variables

DECLARE @Var1 INT, @Var2 INT
SET @Var1 = 1
SET @Var2 = 2
SELECT @Var1 ‘Var1′, @Var2 ‘Var2′
GO

From above example its clear that we can declare multiple variables in one statements.


Difference Between COALESCE And ISNULL

1. COALESCE correctly promotes its arguments to the highest data type in the expression list, ISNULL does not
2. ISNULL can only work with 2 values while COALESCE can take a lot more
3. The alternate value takes the length of the first value with ISNULL, with COALESCE this doesn't happen