Index Scan retrieves all the rows from the table. Index Seek retrieves selective rows from the table.
SQL SERVER – Fix : Error 8101 An explicit value for the identity column in table can only be specified when a column list is used and IDENTITY_INSERT is ON
This error occurs when the user has attempted to insert a row containing a specific identity value into a table that contains an identity column. Run following commands according to your SQL Statement. Let us learn about the IDENTITY_INSERT.
SQL SERVER – Stored Procedure – Clean Cache and Clean Buffer
DBCC FREEPROCCACHE will invalidate all stored procedure plans that the optimizer has cached in memory. Let us learn how to clean cache.Â
SQL SERVER – 2005 Security Best Practices – Operational and Administrative Tasks
This white paper covers some of the operational and administrative tasks associated with SQL Server 2005 security and enumerates best practices and operational and administrative tasks that will result in a more secure SQL Server system.
SQL SERVER – Restore Database Backup using SQL Script (T-SQL)
In this blog post we are going to learn how to restore database backup using T-SQL script. We have already database which we will use to take a backup first and right after that we will use it to restore to the server. Taking backup is an easy thing, but I have seen many times when a user tries to restore the database, it throws an error.
SQL SERVER – Query Analyzer Short Cut to display the text of Stored Procedure
This is quick but interesting trick to display the text of Stored Procedure in the result window. Open SQL Query Analyzer >> Tools >> Customize >> Custom Tab
type sp_helptext against Ctrl+3 (or shortcut key of your choice)
SQL SERVER – Simple Cursor to Select Tables in Database with Static Prefix and Date Created
Following cursor query runs through the database and find all the table with certain prefixed (‘b_’,’delete_’). It also checks if the Table is more than certain days old or created before certain days, it will delete it. We can have any other operation on that table like to delete, print or index.