Reset Identity Column

If we want to check current identify value is. We can use this command to do so:


DBCC CHECKIDENT (‘tablename’, NORESEED)


If we wanted to check the next ID value of my orders table, I could use this command:


DBCC CHECKIDENT (orders, NORESEED)


To set the value of the next ID to be 100, I can use this command:


DBCC CHECKIDENT('Customer', RESEED, 99)

If we want to reset the identity column so we can use the previous command as follows:

DBCC CHECKIDENT('Customer', RESEED, 99)

SQL-Server Performance Tips

Hi,


Query optimization is the required thing in SQL-Server. If we use the following steps in our query then we can get the better performance in SQL-Server.

• Provide the primary key in all the tables
• Avoid using the Boolean operators >, <, >=, <=, is null, is not null
• Avoid using the Not in, !=, Like '%pattern', not exists
• Avoid using the Calculations on unindexed columns or (use union instead), Having (use a WHERE clause instead)
• Don't use function in where clause, instead of use that funtion in where clause.
• Enable aliases to prefix all columns
• use with no lock after the table name
• Return only that column which are required
• Those table which have less number of record use that table first in inner join
• Try to avoid sorting in the select Statement. If possible then do the sorting at the client level.

Find the dependency on an object

In SQL we can find the dependency of an object with the help of TSQL. I am writing the query below which returns the all the function, stored procedure and tables which are used in that object.



SELECT DISTINCT OBJECT_NAME(d.DEPID) DEPENDENT_ON_OBJECT, OBJECT_NAME (d.ID) OBJECTNAME,o.type_desc
FROM SYS.SYSDEPENDS D inner join sys.objects o on d.depid = o.OBJECT_ID
WHERE d.ID =  OBJECT_ID('<OBJECT NAME>')