T-SQL Syntax Archives - Bob Pusateri

11 Sep 2018

A Tale of a Trigger

T-SQL Syntax, TSQL Tuesday

This month's T-SQL Tuesday is being hosted by Steve Jones, who has asked for blog posts today on experiences we have had with triggers, good or bad. I am not a fan of triggers. While they represent a very simplistic construct of "if something happens, do something else", I've yet to encounter a situation where they weren't more trouble than they were worth. In my opinion, their biggest flaw is that they're very easy to forget about, and then tend to pop up and create problems when least expected. I avoid them like the plague, and happily tell my clients…

28 Feb 2018

By Bob Pusateri

Finding Month Ends in T-SQL

SQL Server, T-SQL Syntax

Months are funny. Unlike other parts of a date, they vary in length: The last second of a minute is always 59. The last minute of a hour is always 59. The last hour of a day is always 23. But the last day of a month? Well that depends on what month it is. And the year matters too because a leap year means February gets an extra day. This used to be somewhat tricky to calculate, but then for SQL Server 2012 Microsoft added the EOMONTH() function (End Of MONTH) to T-SQL to help with this. EOMONTH() takes…

10 May 2016

By Bob Pusateri

SQL Prompt 7.2 – Now With Execution Warnings!

T-SQL Syntax, Tools

I've loved using Redgate's tools ever since I discovered what they were, and now that I'm a Friend of Redgate it's even more fun because I get to give feedback to their developers and hear all about what's coming out in new releases! Recently, Redgate announced SQL Prompt 7.2, with a bunch of new features and improvements. My personal favorite of all these is execution warnings. Databases (and computers in general) have this pesky habit of always doing exactly what we tell them to do, instead of doing what we really meant to tell them to do. Have you ever been burned…

03 Dec 2015

By Bob Pusateri

Dropping Multiple Databases At Once

SQL Server, T-SQL Syntax

I'm already at peace with the fact that I'll never know all of SQL Server's secrets, but that doesn't stop me from being surprised every time I figure out something new. In this case, it's another "secret" hiding in plain sight (Books Online). It turns out that the DROP DATABASE statement doesn't just have to drop one database. BOL shows that multiple databases can be specified when separated with commas. Let's see it in action. First, create 4 databases: CREATE DATABASE DB1; CREATE DATABASE DB2; CREATE DATABASE DB3; CREATE DATABASE DB5; Now drop them: DROP DATABASE DB1, DB2, DB3, DB5;…

13 Dec 2011

By Bob Pusateri

T-SQL Tuesday #025: Trick or …?

SQL Server, SQLServerPedia Syndication, T-SQL Syntax, TSQL Tuesday, XML

This month's T-SQL Tuesday is hosted by Allen White (blog | @SQLRunr) and is an invitation to show off a nifty T-SQL trick you use to make your job easier. Here's one of my favorites as of late: Some of the source systems we copy from shard their data across multiple similar tables. For example, instead of having 1 table named dbo.Sales, they might have 30 of them named dbo.Sales_001 to dbo.Sales_030. If there were a rhyme or reason to this sharding it might be seen as an advantage, but unfortunately neither myself nor others on the team have ever…