SQL Server Planet

Browsing along the SQL Server documentation today, I noticed something that scared me. As a spoiled non ANSI compliant SQL Server developer, I do not use a semicolon to terminate my statements. Well, here’s the excerpt that threw me a curveball: Transact-SQL statement terminator. Although the semicolon is not required for most statements in this [...]

Here is the maximum value for a datetime datatype in SQL Server: 9999-12-31 23:59:59.997 And the minimum: 1753-01-01 00:00:00.000 The maximum precision for a datetime value is 3 milliseconds. This is why the ending milliseconds above are shown as 997 instead of 999. Here’s the proof to get the max datetime in case you are [...]

According to the title of the article, you’re probably here for one reason, to find the maximum value for a smalldatetime. Here it is: 2079-06-06 23:59:00 And the Minimum is: 1900-01-01 00:00:00 And here’s the proof: DECLARE @smalldatetime SMALLDATETIME SET @smalldatetime = GETDATE() WHILE 1 = 1 BEGIN BEGIN TRY SET @smalldatetime = DATEADD(dd,1,@smalldatetime) END [...]

If you are like me, then you will find yourself using the COUNT function many times throughout the day. I’ll go into my many uses of the count function later, but for now let’s look at the syntax. There are basically two ways to use COUNT. One is to perform a count of ALL records, [...]

The @@Identity variable is an intrinsic variable which contains the last value inserted into a primary key via the auto increment method. What this means is that if you create a table that has an identity column as a primary key, once that identity column is inserted anywhere within that connection then the @@identity variable [...]

I’m writing this out of a revelation that when querying to see if a particular value is NOT IN a column that contains NULLS, you will not get the correct result set back.  This is somewhat distressful because you would expect when a specific value is not in the result set, then the query would [...]

Select Distinct a way to tell SQL Server to not return duplicate rows. Adding the distinct keyword makes it an expensive query because it essentially performs a group by for all of the returned columns. That being the case, it should only optimally be used on queries that return a small number of rows, or [...]

If you’re here then you’ve probably run into the situation where you’ve automatically created a temp table in your script, and every time you execute the script you have to drop the temp table manually. Yes, this can be a pain. I’ve done this myself many times. So here’s the easy solution. We need to [...]

The method of inserting records from one table to another differs depending on whether or not the target table already exists. Neither method is difficult, however one method requires more coding. Insert where target table does not exist If it does not, then you are in luck, there is a method that could not be [...]

Here is a simple method to convert a text string to numbers (or an integer). This method evaluates each value in a column to determine if the datatype is numeric. If it is, then it converts it to an integer. SELECT CASE WHEN ISNUMERIC(PostalCode) > 0 THEN CAST(PostalCode AS INT) ELSE 0 END FROM SalesLT.Address [...]