Apr 2025 Discount: Comprehensive Database Performance Health Check | Testimonials

Home

SQL SERVER – @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT – Retrieve Last Inserted Identity of Record

Pinal Dave
SQL, SQL Performance, SQL Server, SQL Tips and Tricks
SQL Function, SQL Index, SQL Scripts
SELECT @@IDENTITY It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value. @@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is…
Read More

SQL SERVER – Fix: Error Msg 128 The name is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

Pinal Dave
SQL, SQL Performance, SQL Server, SQL Tips and Tricks
SQL Download, SQL Error Messages, SQL Scripts, SQL Server Security
Error Message: Server: Msg 128, Level 15, State 1, Line 3 The name is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted. Causes: This error occurs when using a column as the DEFAULT value of another column when a table is…
Read More

SQL SERVER – DBCC command to RESEED Table Identity Value – Reset Table Identity

Pinal Dave
SQL, SQL Performance, SQL Server, SQL Tips and Tricks
SQL Index, SQL Scripts, SQL Server DBCC, SQL Server Security
DBCC CHECKIDENT can reseed (reset) the identity value of the table. For example, YourTable has 25 rows with 25 as last identity. If we want next record to have identity as 35 we need to run following T SQL script in Query Analyzer. DBCC CHECKIDENT (yourtable, reseed, 34) If table…
Read More

SQL SERVER – Union vs. Union All – Which is better for performance?

Pinal Dave
SQL, SQL Performance, SQL Puzzle, SQL Server, SQL Tips and Tricks
SQL Download, SQL Scripts
This article is completely re-written with better example SQL SERVER – Difference Between Union vs. Union All – Optimal Performance Comparison. I suggest all of my readers to go here for update article. UNION The UNION command is used to select related information from two tables, much like the JOIN…
Read More

SQL SERVER – Script to Determine Which Version of SQL Server 2000-2005 is Running

Pinal Dave
SQL, SQL Server, SQL Tips and Tricks
SQL Function, SQL Scripts
To determine which version of SQL Server 2000/2005 is running, connect to SQL Server 2000/2005 by using Query Analyzer, and then run the following code: SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition') The results are: The product version (for example, 8.00.534). The product level (for example, “RTM” or “SP2”). The edition…
Read More