–View the current value: DBCC CHECKIDENT (“{table name}”, NORESEED) –Set it to the max value plus one: DBCC CHECKIDENT (“{table name}”, RESEED) –Set it to a spcefic value: DBCC CHECKIDENT (“{table name}”, RESEED, {New Seed Value})
Tag: mssql
How to find a specific text string in a SQL Server Stored Procedure, Function, View or Trigger
— Applicable for SQL 2005 and later versions USE [Your_DB]; SELECT [Scehma] = schema_name(o.schema_id), o.Name, o.type FROM sys.sql_modules m INNER JOIN sys.objects o ON o.object_id = m.object_id WHERE m.definition like ‘%your_text_to_search%’
How to get the first and the last day of previous month in SQL Server
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0) –First day of previous month SELECT DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1) –Last Day of previous month
Not Null check on LEFT function with T-SQL
CONCAT function ignores NULLs: SELECT CONCAT(LEFT(LastName, 1), ‘,’ , LEFT(FirstName, 1), ‘ ‘ + LEFT(MiddleName, 1)) theNameWithInitials FROM myTable If you use CONCAT, you will not need to worry about whether LEFT(X) is null or not.
How to use OUTPUT for Insert, Update and Delete in SQL
The OUTPUT clause was introduced in SQL Server 2005. The OUTPUT clause returns the values of each row that was affected by an INSERT, UPDATE, or DELETE statement. It even supports a MERGE statement, which was introduced in SQL Server 2008 version. The OUTPUT clause has access to two temporary or in-memory SQL tables, INSERTED […]
How to get difference between 2 tables in MSSQL
If you have two tables A and B, both with column C, here are the records, which are present in the table A but not in B: SELECT A.* FROM A LEFT JOIN B ON (A.C = B.C) WHERE B.C IS NULL To get all the differences with a single query, a full join must be used, like this: […]
How to use Nested Cursors in SQL
Declare @Parameter1 int; Declare @Parameter2 int; DECLARE Cur1 CURSOR FOR SELECT Column1 From Table1; OPEN Cur1 FETCH NEXT FROM Cur1 INTO @Parameter1; WHILE @@FETCH_STATUS = 0 BEGIN PRINT ‘Processing Column1: ‘ + Cast(@Parameter1 as Varchar); DECLARE Cur2 CURSOR FOR SELECT Column2 FROM Table2 Where Column2 = @Parameter1; OPEN Cur2; FETCH NEXT FROM Cur2 INTO @Parameter2; […]
Filter Rows By Max Date in SQL
SELECT m1.Column1, m1.Column2, m1.Column3, r.MaxTime FROM ( SELECT Column1, MAX(TimeColumn) as MaxTime FROM MyTable GROUP BY Column1 ) r INNER JOIN MyTable m1 ON m1.Column1= r.Column1AND m1.TimeColumn = r.MaxTime