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: [cc lang=”sql”] DECLARE @smalldatetime SMALLDATETIME SET @smalldatetime = GETDATE() WHILE 1 = 1 BEGIN BEGIN TRY SET @smalldatetime = DATEADD(dd,1,@smalldatetime) END TRY BEGIN CATCH WHILE 1 = 1 BEGIN BEGIN TRY SET @smalldatetime = DATEADD(mi,1,@smalldatetime) END TRY BEGIN CATCH SELECT @smalldatetime BREAK END CATCH END BREAK; END CATCH END [/cc] The precision for smalldatetime is 1 minute. Meaning that you can only specify minute intervals. If you stumbled on this article and are wondering why someone might want the max smalldatetime, here’s a possible use. Say you have an effective date for a record. By this, I mean that this particular record is active from x date to y date. If the current day falls outside of that range, then this record is no longer active. So if you do not want to define an end date that the record is effective for (meaning it is always active), the best way to do this, would be to insert the maximum smalldatetime in order to signify, “always effective”. 2079 not long enough of a time for you? Hmm.. either you are a purist or somehow think you will be around that long, or are VERY devoted to your company. In any case, use Max datetime value instead.
Continue reading ...
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, and the other is to perform a count of distinct records. [cc lang=”sql”] SELECT COUNT(DISTINCT ProductID) FROM Production.TransactionHistory SELECT COUNT(*) FROM Production.TransactionHistory [/cc] This is likely the most common use that I use it for. Often I will want to find the cardinality of a specific field within a table. What I mean by that, is to determine how unique the column is when compared to all the rows in the table. That’s what the query above does. Let’s look at the results. From this we see that the ProductID field is duplicated quite a bit in the TransactionHistory table.
Continue reading ...
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 will hold that value. You may be wondering what the catch is with the italic bold statement above stating “anywhere within that connection”. Well, it is a catch because this is what can make the @@identity variable so dangerous. Here’s the scenario. Say you perform an insert into the customers table. [cc lang=”sql”] INSERT INTO Customers ( FirstName, LastName ) SELECT FName, LName FROM dbo.NewCustomers WHERE CutomerID = 1 SELECT @@identity [/cc] According to this example, the @@identity will surely contain the value of the primary key which is the auto increment for the Customers table right?? Hmm.. maybe.. and most likely… but maybe not. Here’s why. If the Customers table fires a trigger to insert into say, an audit table, and that audit table has an auto increment primary key, then you might find that auto increment returned into the @@identity variable. This is because the trigger is executed in the context of the same connection as the original insert. And remember, @@identity contains the most recent identity column from anywhere within the connection. So what do you do? Use the Scope_Identity() function. It will only return the identity column inserted from the last […]
Continue reading ...
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 return so. Well, unfortunately that’s not the case. Let’s look at an example: [cc lang=”sql”] — Insert two values (null and 1) SELECT VAL INTO #HASNULL FROM ( SELECT VAL = NULL UNION SELECT VAL = 1 ) t SELECT 1 WHERE 5 NOT IN (SELECT VAL FROM #HASNULL) [/cc] From above, we see that we create a table named #HASNULL and insert two values, a one and a null. So when we query to see if the value 5 is in the result set, it should return a 1. But it does not. A little scary. While there may be a logistical reason for this, I’m not able to see it. Let’s just make sure that the null is the problem. So, let’s delete the NULL and re-query: [cc lang=”sql”] DELETE FROM #HASNULL WHERE VAL IS NULL SELECT 1 WHERE 5 NOT IN (SELECT VAL FROM #HASNULL) [/cc] There we have it. Now we see that a 1 is returned. This brings me to my next point. Use Exists when checking for existence. [cc lang=”sql”] SELECT 1 WHERE NOT EXISTS ( SELECT 1 FROM #HASNULL WHERE VAL = 5 ) [/cc] As most developers […]
Continue reading ...
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 small number of columns. When using Select Distinct, you should keep in mind that this is generally a workaround to either improper joining of tables, or an improper where clause that is not limiting the result set to only returning one instance of a record. In order to utilize distinct, all you need to do, is place it before the select list. [cc lang=”sql”] SELECT DISTINCT * FROM yourtable [/cc] This will surely only return a unique instance of every record in the result.
Continue reading ...
