T-SQL
I really enjoy writing code. I don't claim to SQL Dev Super Ninja but enjoy learning new stuff. Below will be some of the constructs I have used to solve business problems that have come up in my day-to-day tasks.
FOR XML PATH and Concatenating Column values
I have seen several folks use the FOR XML Clause and I couldn't figure out what it was doing. I went to report writer at my shop and he walked me through it. He called it flattening the data set. This query here gives you a good example of that. This is also an example of recursion.
|
CREATE TABLE #mytable
(customerID INT, textdata VARCHAR(10)) INSERT INTO #mytable SELECT 1, 'abc' UNION ALL SELECT 1, 'def' UNION ALL SELECT 2, 'uvw' UNION ALL SELECT 2, 'xyz' /* 1. create table and insert records 2. correlated subquery on customerID 3. comma in front and use substring to remove leading comma 4. xml path then flattens the data set 5. must have the group by to get distinct result set or use distinct in front of customerID */ SELECT a.customerID , SUBSTRING(( SELECT ', ' + b.textData FROM #mytable b WHERE b.customerID = a.CustomerID FOR XML PATH('') ), 3, 1000) AS Result FROM #mytable a GROUP BY a.customerID |
Sending a result set in an HTML formatted email
Set the variable for @CurrentSubject. This will be the title for the HTML email. You can define the size by using <H1>-<H6> with <H1> being the largest. You can then define your table border and other HTML attributes. I am no HTML guru so its pretty generic. You then define the column headings or header cell with the <th> tag for your table. The <tr> tag designates a row. Then continue on to turn your result set to into an HTML string via FOR XML PATH('tr').
I am not sure why you need TYPE after FOR XML PATH('tr'). I have read the explanation but still a little foggy on that. Add the ending table tag and you are reading to execute via sp_send_dbmail. You must have a DBMail profile. Pass in a list of emails or distribution list, subject info and body info and you are good to go |
DECLARE @CurrentSubject VARCHAR(MAX) SET @CurrentSubject = '<whatever you want to name the result set' DECLARE @tablehtml NVARCHAR(MAX) SET @tableHTML = N'<H2>'+ @CurrentSubject + '</H2>' + N'<table border="1">' + N'<tr> <th>name of column 1</th> <th>name of column 2</th> <th>name of column 3</th> </tr>' + CAST( (SELECT td = col1 ,'', td = col2 ,'', td = col3 ,'' FROM some_table FOR XML PATH('tr'), TYPE ) AS NVARCHAR(max))+ N'</table>'; EXEC msdb.dbo.sp_send_dbmail @profile_name = <profile name you have configured when setting up DBMail>, @recipients = '[email protected];[email protected];[email protected]', -- @subject = @CurrentSubject, @body = @tableHTML, @body_format = 'HTML' |
Returning list of files with xp_cmdshell |
|
I have seen this command in several scripts out there, mainly for restore scripts.
set @cmd = 'dir /b' + @backuppath
EXEC xp_cmdshell @cmd
Not being a command line junkie I needed to figure out what was going on here.
This link helped me out to understand DIR commands.
http://technet.microsoft.com/en-us/library/cc755121.aspx
DIR /B returns a list of files
You can also pass a UNC path as the backup path.
set @cmd = 'dir /b' + @backuppath
EXEC xp_cmdshell @cmd
Not being a command line junkie I needed to figure out what was going on here.
This link helped me out to understand DIR commands.
http://technet.microsoft.com/en-us/library/cc755121.aspx
DIR /B returns a list of files
You can also pass a UNC path as the backup path.
BCP |
|
I needed to create a simple text file from a SQL Statement for a process I am writing. I am going to execute the BCP via xp_cmdshell.
declare @cmd varchar(2000)
set @cmd = 'bcp "select * from blah.blah" queryout "d:\mytable.csv" -c -T -t^| -S <name of server you want to connect to>'
exec xp_cmdshell @cmd
There are a ton of switches. Use this link to find more info on the various switches
http://msdn.microsoft.com/en-us/library/ms162802.aspx
Here is a brief description of the switches I used.
-c tells BCP how to store the fields you are outputting
-T tells BCP you are using a trusted connection using Windows authentication (see link if you need SQL Authentication)
-t specifes the delimiter. This initially errored when trying to use the pipe. I found you have to use the carrot to escape out and then add the pipe
-S tells BCP which server to connect to. This is optional but if you are using a remote connection you will need to give it a server name.
declare @cmd varchar(2000)
set @cmd = 'bcp "select * from blah.blah" queryout "d:\mytable.csv" -c -T -t^| -S <name of server you want to connect to>'
exec xp_cmdshell @cmd
There are a ton of switches. Use this link to find more info on the various switches
http://msdn.microsoft.com/en-us/library/ms162802.aspx
Here is a brief description of the switches I used.
-c tells BCP how to store the fields you are outputting
-T tells BCP you are using a trusted connection using Windows authentication (see link if you need SQL Authentication)
-t specifes the delimiter. This initially errored when trying to use the pipe. I found you have to use the carrot to escape out and then add the pipe
-S tells BCP which server to connect to. This is optional but if you are using a remote connection you will need to give it a server name.
Issue with xp_cmdshell |
|
When using xp_cmdshell with dynamic SQL be careful. So if you want to change database context and don't put that command inside your dynamic SQL it will fail when using xp_cmdshell. xp_cmdshell executes outside of the scope of the current context. Another example is trying to use temp tables inside xp_cmdshell. If you use a local temp table it will fail due to this behavior mentioned previous. Use global temp tables when using them inside of xp_cmdshell or create regular table and drop at the end of the process.
Dynamically find the end of string |
|
Never know when you will need this.
SELECT SUBSTRING('your string ',len(RTRIM('your string ')),1)
SELECT SUBSTRING('your string ',len(RTRIM('your string ')),1)
Over Clause Issue |
|
I was trying to use the over clause in this statement on a 2008 R2 instance
COUNT(*) OVER (PARTITION BY dbname order by Fullpath)
It failed and it was frustrating me because I know you can use Partition By and Order By with the Over Clause.
When looking at MSDN, for 2008 you can use Partition By and Order by together when doing Ranking but NOT Aggregation.
2012 and forward you can use Partition By and Order by together in Ranking and Aggregation.
COUNT(*) OVER (PARTITION BY dbname order by Fullpath)
It failed and it was frustrating me because I know you can use Partition By and Order By with the Over Clause.
When looking at MSDN, for 2008 you can use Partition By and Order by together when doing Ranking but NOT Aggregation.
2012 and forward you can use Partition By and Order by together in Ranking and Aggregation.
Send Email with TSQL |
|
Today I needed to send an email after a Snapshot was successfully created. Pretty simple to do. You will need to set up a profile first. There are a ton of parameters. Here is the link for more details. You can also get fancy and for the body write HTML to make it look pretty if you want.
http://msdn.microsoft.com/en-us/library/ms190307.aspx
EXEC msdb.dbo.sp_send_dbmail @profile_name = '<profile name>',
@recipients = '<semi-colon separated list of email addresses>', --
@subject = 'This is from your Friendly Neighborhood DBA!!!!',
@body = 'Snapshot was successfully created sucka!!!',
@body_format = 'text';
http://msdn.microsoft.com/en-us/library/ms190307.aspx
EXEC msdb.dbo.sp_send_dbmail @profile_name = '<profile name>',
@recipients = '<semi-colon separated list of email addresses>', --
@subject = 'This is from your Friendly Neighborhood DBA!!!!',
@body = 'Snapshot was successfully created sucka!!!',
@body_format = 'text';
Find the first day of the week with TSQL |
|
Need to find the first day of the week with TSQL or any other day for a matter of fact. Here you go. These was a good exercise in understanding date manipulation.
/*
The code below returns first day of week to be Monday because SQL determines the 0 to be 01/01/1900 which was a Monday. SQL is calculating the number of weeks difference between 01/01/1900 and 08/13/2014 to be 5980. SQL then adds 5980 weeks to 01/01/1900 which returns 08/11/2014
*/
SELECT DATEADD(WEEK, DATEDIFF(WEEK, 0,'08-13-2014'),0)
SELECT DATEADD(WEEK, 5980,0)
/*
Returns first day of week, Monday, but is dynamic due to current_timestamp function
*/
SELECT DATEADD(WEEK, DATEDIFF(WEEK, '19000101',CURRENT_TIMESTAMP),'19000101')
All 3 return the same results
2014-08-11 00:00:00.000
/*Returns first day of week to be Sunday. DatePart finds the number of the weekday starting from Sunday. This example gives the day number for Wednesday which is the fourth day of the week from Sunday.
(1-4) = -3 So when evaluating DatePart the code really is this SELECT DATEADD(DAY, -3, '08-13-2014').
If I wanted to return Monday like the queries above change -1 to -2
*/
SELECT DATEADD(DAY, 1-DATEPART(WEEKDAY,'08-13-2014'), '08-13-2014')
SELECT DATEADD(DAY, 1-DATEPART(WEEKDAY,CURRENT_TIMESTAMP), CURRENT_TIMESTAMP)
These return the same results
2014-08-10 00:00:00.000
/*
The code below returns first day of week to be Monday because SQL determines the 0 to be 01/01/1900 which was a Monday. SQL is calculating the number of weeks difference between 01/01/1900 and 08/13/2014 to be 5980. SQL then adds 5980 weeks to 01/01/1900 which returns 08/11/2014
*/
SELECT DATEADD(WEEK, DATEDIFF(WEEK, 0,'08-13-2014'),0)
SELECT DATEADD(WEEK, 5980,0)
/*
Returns first day of week, Monday, but is dynamic due to current_timestamp function
*/
SELECT DATEADD(WEEK, DATEDIFF(WEEK, '19000101',CURRENT_TIMESTAMP),'19000101')
All 3 return the same results
2014-08-11 00:00:00.000
/*Returns first day of week to be Sunday. DatePart finds the number of the weekday starting from Sunday. This example gives the day number for Wednesday which is the fourth day of the week from Sunday.
(1-4) = -3 So when evaluating DatePart the code really is this SELECT DATEADD(DAY, -3, '08-13-2014').
If I wanted to return Monday like the queries above change -1 to -2
*/
SELECT DATEADD(DAY, 1-DATEPART(WEEKDAY,'08-13-2014'), '08-13-2014')
SELECT DATEADD(DAY, 1-DATEPART(WEEKDAY,CURRENT_TIMESTAMP), CURRENT_TIMESTAMP)
These return the same results
2014-08-10 00:00:00.000
Find how many days in a month, including Leap Year!!! |
|
I can't take credit for this. Here is the link to the query.
http://www.sqlservercentral.com/scripts/T-SQL/116802/
--Gets number of days for a month. Leap years taken in consideration.
CREATE FUNCTION dbo.fnGetMonthDays(@myDate DATE) RETURNS INT
AS
BEGIN
DECLARE @isLeap INT = 0
IF (YEAR(@myDate) % 400 = 0 OR (YEAR(@myDate) % 4 = 0 AND YEAR(@myDate) % 100 !=0))
SET @isLeap=1
DECLARE @month INT = MONTH(@myDate)
DECLARE @days INT
SELECT @days =
CASE
WHEN @month=1 THEN 31
WHEN @month=2 THEN 28 + @isLeap
WHEN @month=3 THEN 31
WHEN @month=4 THEN 30
WHEN @month=5 THEN 31
WHEN @month=6 THEN 30
WHEN @month=7 THEN 31
WHEN @month=8 THEN 31
WHEN @month=9 THEN 30
WHEN @month=10 THEN 31
WHEN @month=11 THEN 30
WHEN @month=12 THEN 31
END
RETURN @days
END
http://www.sqlservercentral.com/scripts/T-SQL/116802/
--Gets number of days for a month. Leap years taken in consideration.
CREATE FUNCTION dbo.fnGetMonthDays(@myDate DATE) RETURNS INT
AS
BEGIN
DECLARE @isLeap INT = 0
IF (YEAR(@myDate) % 400 = 0 OR (YEAR(@myDate) % 4 = 0 AND YEAR(@myDate) % 100 !=0))
SET @isLeap=1
DECLARE @month INT = MONTH(@myDate)
DECLARE @days INT
SELECT @days =
CASE
WHEN @month=1 THEN 31
WHEN @month=2 THEN 28 + @isLeap
WHEN @month=3 THEN 31
WHEN @month=4 THEN 30
WHEN @month=5 THEN 31
WHEN @month=6 THEN 30
WHEN @month=7 THEN 31
WHEN @month=8 THEN 31
WHEN @month=9 THEN 30
WHEN @month=10 THEN 31
WHEN @month=11 THEN 30
WHEN @month=12 THEN 31
END
RETURN @days
END
What is PATINDEX and how can I use it???? |
|
A customer had a column that was supposed to be numeric but the data type was varchar, so of course they ended up with some alpha numeric entries into this column. The vendor screwed up their table design. They wanted to know how many of the entries were of the alpha numeric flavor. The table has only 28,299,556 records so eyeballing it was out of the question. I also found they had empty strings as well. Being that the column is called billing_number that can’t be a good thing. I used PATINDEX to to find strings that contained alpha character data. PATINDEX returns the starting position of where the pattern begins and 0 if the pattern is not found. It accepts wildcards unlike CHARINDEX.
SELECT billing_number from <table name >
WHERE PATINDEX('%[a-z]%', billing_number) <> 0
ORDER BY billing_number DESC
Here is another example of using PATINDEX to dynamically find the starting point of pattern in a string. Using SUBSTRING and PATINDEX together I was able to only return the part of the string I wanted and exclude the pattern indicated in the PATINDEX function.
SELECT SUBSTRING(col1,0,PATINDEX('%pattern',col1)) col1 FROM <tablename >
Of course it would probably be easier to use REPLACE but it was cool to figure out a way to use PATINDEX. I wonder which one is faster. Need to look at the execution plans. If you know drop me an email at t[email protected]
SELECT REPLACE(col1,'string name','') FROM <table name>
SELECT billing_number from <table name >
WHERE PATINDEX('%[a-z]%', billing_number) <> 0
ORDER BY billing_number DESC
Here is another example of using PATINDEX to dynamically find the starting point of pattern in a string. Using SUBSTRING and PATINDEX together I was able to only return the part of the string I wanted and exclude the pattern indicated in the PATINDEX function.
SELECT SUBSTRING(col1,0,PATINDEX('%pattern',col1)) col1 FROM <tablename >
Of course it would probably be easier to use REPLACE but it was cool to figure out a way to use PATINDEX. I wonder which one is faster. Need to look at the execution plans. If you know drop me an email at t[email protected]
SELECT REPLACE(col1,'string name','') FROM <table name>
Check if Schema Exists
I wanted to make my script was robust and check to see if a schema was present in the database prior to executing the rest of the code. So I wrote this..
IF NOT EXISTS( SELECT 1 FROM sys.schemas
WHERE name = '<schema name>')
BEGIN
create schema <schema name>
END
ELSE
PRINT '<schema name> has been created'
The code above will fail being that SQL wants create schema statements in their own batch.
Below is the work around.
IF NOT EXISTS( SELECT 1 FROM sys.schemas
WHERE name = '<schema name>')
BEGIN
EXEC ('create schema <schema name>')
END
ELSE
PRINT '<schema name> has been created'
IF NOT EXISTS( SELECT 1 FROM sys.schemas
WHERE name = '<schema name>')
BEGIN
create schema <schema name>
END
ELSE
PRINT '<schema name> has been created'
The code above will fail being that SQL wants create schema statements in their own batch.
Below is the work around.
IF NOT EXISTS( SELECT 1 FROM sys.schemas
WHERE name = '<schema name>')
BEGIN
EXEC ('create schema <schema name>')
END
ELSE
PRINT '<schema name> has been created'
How to Use LAG Function |
|
LAG will return a value from the previous row without having to do a self join. I used this to find the difference between a value and its predecessor.
LAG(VALUE, OFFSET, DEFAULT)
Value = Expression you are wanting to use with LAG
Offset= How far back you want to go, 1 means previous row
Default = if there is a null what will be shown in place of the null
Execution_Count-(LAG(execution_count,1,0) OVER (ORDER BY Execution_Count))
Execution_count Difference between previous row and current row
1874 166
1708 184
1524 283
1241 300
941 166
LAG(VALUE, OFFSET, DEFAULT)
Value = Expression you are wanting to use with LAG
Offset= How far back you want to go, 1 means previous row
Default = if there is a null what will be shown in place of the null
Execution_Count-(LAG(execution_count,1,0) OVER (ORDER BY Execution_Count))
Execution_count Difference between previous row and current row
1874 166
1708 184
1524 283
1241 300
941 166
Return minimum value between to columns in same row |
|
Developer needed help returning min value from two columns in the same row. Here is an example of our solution.
CREATE TABLE #case
(col1 INT,
col2 int)
INSERT INTO #case
SELECT 160,159
SELECT CASE WHEN col1 < col2 THEN col1
ELSE col2
END AS minval
FROM #case;
CREATE TABLE #case
(col1 INT,
col2 int)
INSERT INTO #case
SELECT 160,159
SELECT CASE WHEN col1 < col2 THEN col1
ELSE col2
END AS minval
FROM #case;
Dynamically Parse String |
|
I wanted to dynamically parse the data file location for backup script I was writing. Here is a solution I created based up on an article I read on tally tables from SQL Server Central.
http://www.sqlservercentral.com/articles/T-SQL/62867/
Solution was to parse the string into a temp table with an ID column associated with each character in the string. Use that temp table to dynamically find data file location and length of the data file name.
I also have a much simpler example below so check that out.....
--dynamically parse data file and log file names
IF OBJECT_ID('tempdb..#elements')IS NOT NULL
DROP TABLE #elements
IF OBJECT_ID('tempdb..#SplitString') IS NOT NULL
DROP TABLE #SplitString
--put each value in the string into a table with an associated ID
CREATE TABLE #Elements
(id INT,
string VARCHAR(100))
CREATE TABLE #SplitString
(dbname VARCHAR(100),
filename VARCHAR(50))
DECLARE c CURSOR LOCAL FAST_FORWARD
FOR
SELECT database_id, file_id, physical_name FROM sys.master_files WHERE database_id > 4
OPEN c
DECLARE @dbid SMALLINT
DECLARE @fileid SMALLINT
DECLARE @pname VARCHAR(200)
FETCH NEXT FROM c INTO @dbid, @fileid, @pname
WHILE @@FETCH_STATUS <> -1
begin
DECLARE @parameter VARCHAR(200)
SET @Parameter = (SELECT @pname FROM sys.master_files WHERE database_ID = @dbid AND FILE_ID = @fileid)
DECLARE @n INT
SET @n = 1
WHILE @n <= LEN(@Parameter)
BEGIN
--iterate through the string and pass the string value into #elements
INSERT INTO #Elements
SELECT @n, SUBSTRING(@Parameter, @n, 1)
SET @n+=1
END
--find the last "wack" via the id column
-- select * from #elements
DECLARE @split INT
SET @split = (SELECT MAX(id) FROM #Elements WHERE string = '\')
/***********************************************************
ADD 1 to split value to return first character after last wack.
Use total length of string minus the split counter value to dynamically return rest of string
Insert those results into #SplitString table
***********************************************************/
INSERT INTO #SplitString
SELECT DB_NAME(database_id) AS dbname ,
SUBSTRING(physical_name, @split + 1, ( LEN(physical_name) - @split )) AS StringParse
FROM sys.master_files
WHERE database_ID = @dbid
AND FILE_ID = @fileid
--truncate #Elements before the next iteration of the loop
TRUNCATE TABLE #Elements
FETCH NEXT FROM c INTO @dbid, @fileid, @pname
end
CLOSE c
DEALLOCATE c
SELECT * FROM #SplitString
http://www.sqlservercentral.com/articles/T-SQL/62867/
Solution was to parse the string into a temp table with an ID column associated with each character in the string. Use that temp table to dynamically find data file location and length of the data file name.
I also have a much simpler example below so check that out.....
--dynamically parse data file and log file names
IF OBJECT_ID('tempdb..#elements')IS NOT NULL
DROP TABLE #elements
IF OBJECT_ID('tempdb..#SplitString') IS NOT NULL
DROP TABLE #SplitString
--put each value in the string into a table with an associated ID
CREATE TABLE #Elements
(id INT,
string VARCHAR(100))
CREATE TABLE #SplitString
(dbname VARCHAR(100),
filename VARCHAR(50))
DECLARE c CURSOR LOCAL FAST_FORWARD
FOR
SELECT database_id, file_id, physical_name FROM sys.master_files WHERE database_id > 4
OPEN c
DECLARE @dbid SMALLINT
DECLARE @fileid SMALLINT
DECLARE @pname VARCHAR(200)
FETCH NEXT FROM c INTO @dbid, @fileid, @pname
WHILE @@FETCH_STATUS <> -1
begin
DECLARE @parameter VARCHAR(200)
SET @Parameter = (SELECT @pname FROM sys.master_files WHERE database_ID = @dbid AND FILE_ID = @fileid)
DECLARE @n INT
SET @n = 1
WHILE @n <= LEN(@Parameter)
BEGIN
--iterate through the string and pass the string value into #elements
INSERT INTO #Elements
SELECT @n, SUBSTRING(@Parameter, @n, 1)
SET @n+=1
END
--find the last "wack" via the id column
-- select * from #elements
DECLARE @split INT
SET @split = (SELECT MAX(id) FROM #Elements WHERE string = '\')
/***********************************************************
ADD 1 to split value to return first character after last wack.
Use total length of string minus the split counter value to dynamically return rest of string
Insert those results into #SplitString table
***********************************************************/
INSERT INTO #SplitString
SELECT DB_NAME(database_id) AS dbname ,
SUBSTRING(physical_name, @split + 1, ( LEN(physical_name) - @split )) AS StringParse
FROM sys.master_files
WHERE database_ID = @dbid
AND FILE_ID = @fileid
--truncate #Elements before the next iteration of the loop
TRUNCATE TABLE #Elements
FETCH NEXT FROM c INTO @dbid, @fileid, @pname
end
CLOSE c
DEALLOCATE c
SELECT * FROM #SplitString
Recursive CTE |
|
This is the best explanation of a Recursive CTE that I have found
Using Except in an Insert Statement |
|
So I have two tables that have lists of server names. My Servers table needs to have new servers inserted that are new to the environment. I have the complete list in a table I imported from Excel that was sent to me by the compliance team. So I need to do this in a some efficient manner. The Except clause works great for this. The except clause finds all records in the top query that are not in the bottom query. BOL says the left query. Top makes more sense to me. Also number of columns must match between the queries just like a UNION since this is an extension of the UNION operator. Also, data types must match. That's why I have cast the columns in the top query, had a truncation error without it.
insert into dbo.servers(servername, IP_address)
select cast(name as varchar(20)),cast([IP Address] as varchar(20))
from [dbo].[LatestServerList]
except
select servername, IP_address from dbo.SERVERS
insert into dbo.servers(servername, IP_address)
select cast(name as varchar(20)),cast([IP Address] as varchar(20))
from [dbo].[LatestServerList]
except
select servername, IP_address from dbo.SERVERS
Convert UNIX timestamp to SQL Server datetime2 |
|
Needed to convert Unix Timestamp to SQL Server datetime2 data type. UNIX timestamp is from 1970-01-01.
There are lots of scripts out there but none included the milliseconds. I wrote a function to take into account the milliseconds.
Create FUNCTION UNIX_TIMESTAMP_TO_DATETIME2 (
@timestamp decimal(30,7)
)
RETURNS datetime2
AS
BEGIN
-- set the return type to datetime2 to give appropriate precision
declare @return datetime2
--cast @timestamp to a string for easier manipulation
declare @stringparse varchar(30)
set @stringparse = ( select cast(@timestamp as varchar(30)))
--parse out the ms from the unix timestamp
declare @epoch_ms varchar(7)
select @epoch_ms = (select substring( @stringparse,charindex('.',@stringparse), len(@stringparse)-charindex('.',@stringparse)) as epoch_ms)
--parse out seconds from 1970-01-01 00:00:00.0 with no milliseconds
declare @epoch varchar(30)
set @epoch = (select substring(@stringparse, 0,charindex('.',@stringparse)))
-- add UNIX time in seconds from 1970 to return datetime
DECLARE @convert datetime
SEt @convert = DATEADD(second, cast(@epoch as int),'1970-01-01');
-- datetime formatting
declare @parsems varchar(27)
set @parsems = (select convert(varchar(30),@convert, 121))
--one final parse of datetime convert to 121
declare @final varchar(27)
set @final = (select substring(@parsems,0,charindex('.',@parsems)))
set @return = (Select cast(@final+@epoch_ms as datetime2))
Return @return
END
There are lots of scripts out there but none included the milliseconds. I wrote a function to take into account the milliseconds.
Create FUNCTION UNIX_TIMESTAMP_TO_DATETIME2 (
@timestamp decimal(30,7)
)
RETURNS datetime2
AS
BEGIN
-- set the return type to datetime2 to give appropriate precision
declare @return datetime2
--cast @timestamp to a string for easier manipulation
declare @stringparse varchar(30)
set @stringparse = ( select cast(@timestamp as varchar(30)))
--parse out the ms from the unix timestamp
declare @epoch_ms varchar(7)
select @epoch_ms = (select substring( @stringparse,charindex('.',@stringparse), len(@stringparse)-charindex('.',@stringparse)) as epoch_ms)
--parse out seconds from 1970-01-01 00:00:00.0 with no milliseconds
declare @epoch varchar(30)
set @epoch = (select substring(@stringparse, 0,charindex('.',@stringparse)))
-- add UNIX time in seconds from 1970 to return datetime
DECLARE @convert datetime
SEt @convert = DATEADD(second, cast(@epoch as int),'1970-01-01');
-- datetime formatting
declare @parsems varchar(27)
set @parsems = (select convert(varchar(30),@convert, 121))
--one final parse of datetime convert to 121
declare @final varchar(27)
set @final = (select substring(@parsems,0,charindex('.',@parsems)))
set @return = (Select cast(@final+@epoch_ms as datetime2))
Return @return
END
SSMS shortcuts |
|
Update using a Case statement |
|
Need to update data based upon specific logic
UPDATE tablename
SET col1 =
( CASE WHEN col12 NOT LIKE '%-a' THEN 1
WHEN col2 LIKE '%-b' THEN 1
ELSE 0 END )
UPDATE tablename
SET col1 =
( CASE WHEN col12 NOT LIKE '%-a' THEN 1
WHEN col2 LIKE '%-b' THEN 1
ELSE 0 END )
Return name of day for a given date |
|
Use the DATENAME fuction with the day of week parameter.
SELECT DATENAME(dw, GETDATE())
https://msdn.microsoft.com/en-us/library/ms174395.aspx
SELECT DATENAME(dw, GETDATE())
https://msdn.microsoft.com/en-us/library/ms174395.aspx
Batch delete process |
|
We are taking in 36 million records of vCenter performance metrics every hour. Only need to keep 3 days worth as the rest are sent to the data warehouse. This process is based upon a process written by Aaron Bertrand . I deleted 36 million records and the log file stayed at 1 GB. If database is in full recovery mode then you would need to add a log backup instead of a checkpoint.
Create PROC [Batch_Delete]
@data_ret INT = 4320,
@batch INT = 50000
AS
/*********************
delete records from set of tables defined in select statement defining the cursor.
@data_ret will set how far back the delete start from in minutes...default is 4320 or 3 days
@batch determines how large each batch will be....default is 50000
transaction is committed after each batch delete
so if you need to stop process you don't have to rollback the entire process
TLog is then flushed to disk via Checkpoint to minimize log file size
**********************/
DECLARE @date DATETIME2
SET @date = DATEADD(MINUTE,-(@data_ret),GETDATE())
DECLARE bdelete CURSOR LOCAL FAST_FORWARD READ_ONLY
FOR SELECT TABLE_SCHEMA, TABLE_NAME
FROM [INFORMATION_SCHEMA].[TABLES]
WHERE TABLE_CATALOG = '<schema name>' AND table_name NOT LIKE '<filter for specific tables>'
DECLARE @schema VARCHAR(15)
DECLARE @table VARCHAR(50)
DECLARE @cmd VARCHAR(max)
OPEN bdelete
FETCH NEXT FROM bdelete INTO @schema, @table
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @cmd =
'SET NOCOUNT ON;
DECLARE @r INT;
SET @r = 1;
WHILE @r > 0
BEGIN
BEGIN TRANSACTION;
DELETE TOP ('+cast(@batch AS VARCHAR(8))+')
From '+@schema+'.'+@table+'
WHere Batch_Created < '+''''+CAST(@date AS VARCHAR(30))+''''+';
SET @r = @@ROWCOUNT;
if @r < 1
Begin
Print ''you got nothing to delete from '+@table+''+' or process is complete sucka!!!''
end
else
Begin
Print cast(@r as varchar(10)) +'' deletes FROM '+@table+'''
End
COMMIT TRANSACTION;
CHECKPOINT;
END'
--PRINT (@cmd)
EXEC (@cmd)
FETCH NEXT FROM bdelete INTO @schema, @table
END
CLOSE bdelete
DEALLOCATE bdelete
Create PROC [Batch_Delete]
@data_ret INT = 4320,
@batch INT = 50000
AS
/*********************
delete records from set of tables defined in select statement defining the cursor.
@data_ret will set how far back the delete start from in minutes...default is 4320 or 3 days
@batch determines how large each batch will be....default is 50000
transaction is committed after each batch delete
so if you need to stop process you don't have to rollback the entire process
TLog is then flushed to disk via Checkpoint to minimize log file size
**********************/
DECLARE @date DATETIME2
SET @date = DATEADD(MINUTE,-(@data_ret),GETDATE())
DECLARE bdelete CURSOR LOCAL FAST_FORWARD READ_ONLY
FOR SELECT TABLE_SCHEMA, TABLE_NAME
FROM [INFORMATION_SCHEMA].[TABLES]
WHERE TABLE_CATALOG = '<schema name>' AND table_name NOT LIKE '<filter for specific tables>'
DECLARE @schema VARCHAR(15)
DECLARE @table VARCHAR(50)
DECLARE @cmd VARCHAR(max)
OPEN bdelete
FETCH NEXT FROM bdelete INTO @schema, @table
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @cmd =
'SET NOCOUNT ON;
DECLARE @r INT;
SET @r = 1;
WHILE @r > 0
BEGIN
BEGIN TRANSACTION;
DELETE TOP ('+cast(@batch AS VARCHAR(8))+')
From '+@schema+'.'+@table+'
WHere Batch_Created < '+''''+CAST(@date AS VARCHAR(30))+''''+';
SET @r = @@ROWCOUNT;
if @r < 1
Begin
Print ''you got nothing to delete from '+@table+''+' or process is complete sucka!!!''
end
else
Begin
Print cast(@r as varchar(10)) +'' deletes FROM '+@table+'''
End
COMMIT TRANSACTION;
CHECKPOINT;
END'
--PRINT (@cmd)
EXEC (@cmd)
FETCH NEXT FROM bdelete INTO @schema, @table
END
CLOSE bdelete
DEALLOCATE bdelete
Another way to Parse a Backup File location from a string using REVERSE |
|
In a previous script I have posted here, I have a way to dynamically parse the backup filename from the backup location string. It's way to complicated. I came up with this using the REVERSE function.
The task is to parse the name of the backup file from this string
'some_server\mybackup\yo_momma.bak'
This can be achieved by flipping around the string using REVERSE. This makes the last '\', the first '\'
SELECT reverse('some_server\mybackup\yo_momma.bak')
results seen here...
'kab.ammom_oy\pukcabym\revres_emos'
Using CHARINDEX find then location of the first '\'
SELECT CHARINDEX('\', REVERSE('some_server\mybackup\yo_momma.bak'))
which returns...
13
Now use this information with SUBSTRING
SELECT SUBSTRING(REVERSE('some_server\mybackup\yo_momma.bak'), 0, CHARINDEX('\', REVERSE('some_server\mybackup\yo_momma.bak')))
which yields...
kab.ammom_oy
Then finally wrap all of that code into another REVERSE and bam!!! You have your backup file name
SELECT REVERSE(SUBSTRING(REVERSE('some_server\mybackup\yo_momma.bak'), 0, CHARINDEX('\', REVERSE('some_server\mybackup\yo_momma.bak'))))
yo_momma.bak
==========================================================================================================
==========================================================================================================
==========================================================================================================
==========================================================================================================
Here is the reverse of the above query and will return the backup location with no filename info.
DECLARE @backuploc VARCHAR(200);
USE msdb;
SELECT @backuploc = REVERSE(SUBSTRING(REVERSE(physical_device_name),
CHARINDEX('\',
REVERSE(physical_device_name)),
LEN(physical_device_name)))
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE database_name = <enter db name>'
AND type = 'L'
AND backup_start_date = ( SELECT MAX(backup_start_date)
FROM msdb.dbo.backupset
);
Select @backuploc
The task is to parse the name of the backup file from this string
'some_server\mybackup\yo_momma.bak'
This can be achieved by flipping around the string using REVERSE. This makes the last '\', the first '\'
SELECT reverse('some_server\mybackup\yo_momma.bak')
results seen here...
'kab.ammom_oy\pukcabym\revres_emos'
Using CHARINDEX find then location of the first '\'
SELECT CHARINDEX('\', REVERSE('some_server\mybackup\yo_momma.bak'))
which returns...
13
Now use this information with SUBSTRING
SELECT SUBSTRING(REVERSE('some_server\mybackup\yo_momma.bak'), 0, CHARINDEX('\', REVERSE('some_server\mybackup\yo_momma.bak')))
which yields...
kab.ammom_oy
Then finally wrap all of that code into another REVERSE and bam!!! You have your backup file name
SELECT REVERSE(SUBSTRING(REVERSE('some_server\mybackup\yo_momma.bak'), 0, CHARINDEX('\', REVERSE('some_server\mybackup\yo_momma.bak'))))
yo_momma.bak
==========================================================================================================
==========================================================================================================
==========================================================================================================
==========================================================================================================
Here is the reverse of the above query and will return the backup location with no filename info.
DECLARE @backuploc VARCHAR(200);
USE msdb;
SELECT @backuploc = REVERSE(SUBSTRING(REVERSE(physical_device_name),
CHARINDEX('\',
REVERSE(physical_device_name)),
LEN(physical_device_name)))
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE database_name = <enter db name>'
AND type = 'L'
AND backup_start_date = ( SELECT MAX(backup_start_date)
FROM msdb.dbo.backupset
);
Select @backuploc
ESCAPE argument and WildCards |
|
Do you need to escape some characters in you where clause when using wildcards?
Example you want to find a string that includes 20-50% off
select * from table
where col1 like '%50!% off' ESCAPE '!'
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql?view=sql-server-2017
Example you want to find a string that includes 20-50% off
select * from table
where col1 like '%50!% off' ESCAPE '!'
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql?view=sql-server-2017
Find the name of the day for a specific date |
|
I needed to find the name of the day for specific date. Here is the code.
SELECT DATENAME(WEEKDAY,GETDATE())
SELECT DATENAME(WEEKDAY,GETDATE())
Set or Select when populating a variable?? |
|
Had an interesting question thrown my way.
Trying to populate a variable using this convention..
select @userid = userid from table where blah = 'a_value'
but the query returned multiple values and it didn't fail. Come to find out using.. Select.. this won't bomb but if you use..
Set @userid = (Seect userid from table where blah = 'a_value') it will bomb.
with the error you would expect..
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Found a good explanation on this issue. Click the link below.
www.mssqltips.com/sqlservertip/1888/when-to-use-set-vs-select-when-assigning-values-to-variables-in-sql-server/
Trying to populate a variable using this convention..
select @userid = userid from table where blah = 'a_value'
but the query returned multiple values and it didn't fail. Come to find out using.. Select.. this won't bomb but if you use..
Set @userid = (Seect userid from table where blah = 'a_value') it will bomb.
with the error you would expect..
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Found a good explanation on this issue. Click the link below.
www.mssqltips.com/sqlservertip/1888/when-to-use-set-vs-select-when-assigning-values-to-variables-in-sql-server/
Finding multiple patterns in a string |
|
This makes my brain hurt but could be useful if ever needed.
sqlserverfast.com/blog/hugo/2019/04/removing-multiple-patterns-from-a-string/
sqlserverfast.com/blog/hugo/2019/04/removing-multiple-patterns-from-a-string/