June 2025 Discount: Comprehensive Database Performance Health Check | Testimonials

Home

SQL SERVER – Fix : Error : Server: Msg 544, Level 16, State 1, Line 1 Cannot insert explicit value for identity column in table

Pinal Dave
SQL, SQL Server, SQL Tips and Tricks
SQL Error Messages, SQL Identity, SQL Scripts
Error Message: Server: Msg 544, Level 16, State 1, Line 1 Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF. This error message appears when you try to insert a value into a column for which the IDENTITY property was declared, but without having…
Read More

SQL SERVER – 2005 Change Database Compatible Level – Backward Compatibility

Pinal Dave
SQL, SQL Server, SQL Tips and Tricks
Database Compatible Level, SQL Scripts, SQL Stored Procedure
sp_dbcmptlevel Sets certain database behaviors to be compatible with the specified version of SQL Server. Example: ----SQL Server 2005 database compatible level to SQL Server 2000 EXEC sp_dbcmptlevel AdventureWorks, 80; GO ----SQL Server 2000 database compatible level to SQL Server 2005 EXEC sp_dbcmptlevel AdventureWorks, 90; GO Version of SQL Server…
Read More

SQL SERVER – Change Default Fill Factor For Index

Pinal Dave
SQL Performance
SQL Advanced Option, SQL Index, SQL Scripts, SQL Server

SQL Server has default value for fill factor is Zero (0). The fill factor is implemented only when the index is created; it is not maintained after the index is created as data is added, deleted, or updated in the table. When creating an index, you can specify a fill factor to leave extra gaps and reserve a percentage of free space on each leaf level page of the index to accommodate future expansion in the storage of the table’s data and reduce the potential for page splits. Let us learn about how to change default fill factor of index.

Read More

SQL SERVER – Stored Procedure to display code (text) of Stored Procedure, Trigger, View or Object

Pinal Dave
SQL, SQL Server, SQL Tips and Tricks
SQL Scripts, SQL Stored Procedure, SQL Trigger, SQL View
This is another popular question I receive. How to see text/content/code of Stored Procedure. System stored procedure that prints the text of a rule, a default, or an unencrypted stored procedure, user-defined function, trigger, or view. Syntax sp_helptext @objname = 'name' sp_helptext [ @objname = ] 'name' [ , […
Read More