In SQL Server 2012+, you can use OFFSET…FETCH. The below query will help you to get 2nd row. But with little changes, you can get the ‘n’th row as well. SELECT <column(s)> FROM <table(s)> ORDER BY <sort column(s)> OFFSET 1 ROWS — Skip this number of rows FETCH NEXT 1 ROWS ONLY; — Return this […]
Recursive triggers are set at the database level. It’s part of the database metadata information and is available through “sys.databases” view. You can use the below query to check whether the recursive triggers are enabled or not on your database: SELECT name AS ‘Database’, is_recursive_triggers_on AS ‘Recursive Trigger Enabled’ FROM sys.databases Here is the output:
CHECKPOINT GO –Cleaen Query Cache DBCC DROPCLEANBUFFERS; GO –Clean Execution Plan Cache DBCC FREEPROCCACHE; GO
A stored procedure is a saved block of T-SQL code, such as a query to list the rows in a table. A block of T-SQL code can be saved in a T-SQL script file. You can also store the code from a script file in a stored procedure. There are several benefits that result from […]
Declare @spnames CURSOR Declare @spname nvarchar(max) Declare @moddef nvarchar(max) Set @spnames = CURSOR FOR select distinct object_name(c.id) from syscomments c, sysobjects o where c.text like ‘%findtext%’ and c.id = o.id and o.type = ‘P’ OPEN @spnames FETCH NEXT FROM @spnames into @spname WHILE @@FETCH_STATUS = 0 BEGIN Set @moddef = (SELECT Replace ((REPLACE(definition,’findtext’,’replacetext’)),’ALTER’,’create’) FROM sys.sql_modules […]
If you want to use the combination of ORDER BY, CASE and UNION then you should use inner query for a solution without any headache: SELECT * FROM ( SELECT Col_a, Col_b, 0 AS Col_c FROM table1 WHERE conditions UNION SELECT Col_a, NULL AS Col_b, Col_c FROM table2 WHERE conditions ) x ORDER BY CASE […]
SELECT AVG (CASE WHEN Value <> 0 THEN Value ELSE NULL END) …. AVG function will not take into account NULL values. So you can also use this AVG (NULLIF(Value, 0))
SQL Server provides a number of options you can use to format a date/time string. One of the first considerations is the actual date/time needed. The most common is the current date/time using getdate(). This provides the current date and time according to the server providing the date and time. If a universal date/time is needed, […]
SELECT * FROM SQLTABLE WHERE SUBSTRING(TableColumn, 1, 1) = ‘x’ COLLATE SQL_Latin1_General_CP1_CS_AS
First of all, I must thank to Maulik Dhorajia for his great post. That post saved me a lot. That’s the @Company table we will use: And here is the SQL Query we need to use: — Working Example ;WITH CTECompany AS ( SELECT EmpID, ParentID, PersonName , 0 AS HLevel, CAST(RIGHT(REPLICATE(‘_’,5) + CONVERT(VARCHAR(20),EmpID),20) AS […]