Great Quote of the Day

This concept is simple and primary, but often overlooked:
"...it is a good idea to create non-clustered indexes on all foreign keys. You will always run lots of queries, and also use the JOIN operator, based on your primary and foreign keys."
- Wagner Crivelini
From the article SQL and the JOIN Operator

Converting a Julian Date

I had to accomplish this at work yesterday. I did not see anything in the pre-defined SQL Server functions that would do it, so I started Googleing. Thank goodness for stackoverflow.com.


DECLARE @JULIAN_DATE INT = 2012146

SELECT DATEADD(D, CAST(SUBSTRING(
CAST(@JULIAN_DATE AS VARCHAR), 5, LEN(@JULIAN_DATE) - 4) AS INT) - 1,
        CAST('1/1/' + SUBSTRING(CAST(@JULIAN_DATE AS VARCHAR),1,4) AS DATE))
AS [NEW DATE]
     
http://stackoverflow.com/questions/2692361/most-concise-way-to-convert-julian-date-yyyyday-of-year-to-sql-datetime

This T-SQL is a little too verbose to be writing over and over again so later today I'm going to roll it up into a UDF (User Defined Function).

Modifying Data Through a View

The following requirements MUST be met to modify data through a view in SQL Server:

• The modification can reference exactly ONE table
• Columns in the view must reference columns in a table directly
• The column cannot be derived from an aggregate
• The column cannot be computed as the result of a UNION/UNION ALL, INTERSECT, EXCEPT, or CROSSJOIN
• The column being modified cannot be affected by the DISTINCT, GROUP BY, or HAVING clause
• The TOP operator cannot be used

NOT, AND, OR: the Boolean Operators in T-SQL

The boolean operators in T-SQL are NOT, AND, and OR and are executed in a SQL statement in that order.

• The NOT operator generally hurts query performance because indexes cannot be used for a WHERE clause when the NOT operator is used *
• Indexes can be used when the OR operator is specified, but all columns referenced by the OR must be included in an index or none of the indexes are used
• Performance is often improved when the AND operator is used because the AND operator generally results in a smaller result set

* Just like with the NOT operator, indexes cannot be utilized when a leading wildcard character is used. For example:

SELECT FirstName, LastName 
FROM [Users].[dbo].[Employees]
WHERE LastName LIKE '*ones' 

Get the Number of Rows from Each Table in a Database

  In order to see how many rows a particular table in a database has in SQL Server, you can simply right click on the table and select properties. It will be in available in one of the sections. However, what if you need to see how many rows every table in the database has at the same time? I find this query works well for me in those cases:

SELECT T.NAME, S.ROWS
FROM SYSINDEXES S
INNER JOIN SYS.TABLES T
 ON T.OBJECT_ID = S.ID
WHERE S.INDID > 2
ORDER BY S.ROWS DESC

SQL Server Times Out When Adding a New Column to a Table

Recently at work, I needed to add an auto-incremented int field to a table that had 2.5 million records and was about 50 columns wide. Not a terribly large table to some folks, but our server is not the most robust. So every time I tried to do this in the UI in SSMS, it would process for a while and then time out and tell me something to the effect that the changes could not be save to the table. I then got the bright idea just to try this as a query:

ALTER TABLE [database].[dbo].[tableName]
ADD ID INT IDENTITY(1,1) NOT NULL

It processed in something like 2 second and worked like a charm. Weird that it should fail in the UI, but work as a script. I will keep this in mind. Also, it NEVER hurts to be able to write the T-SQL by hand to accomplish a task.

IIS 7 401 Error - Unauthorized: Access is denied due to invalid credentials

I thought this one was going to drive me crazy. I set up a web application on a Windows Server 2008 using IIS 7 and ASP.NET 4. I set it up to use Window Authentication but I just kept getting a 401 error - which made no sense. Everything on the network was windows-based. There should have been no problem finding my credentials. I was finally lucky enough to come across this on the web:

http://social.technet.microsoft.com/Forums/en-US/winserversecurity/thread/c9239a89-fbee-4adc-b72f-7a6a9648331f/

Below are the steps to stop this from happening:

• Open IIS and select the website that is causing the 401
• Open the "Authentication" property under the "IIS" header
• Click the "Windows Authentication" item and click "Providers"
• The issue should be that Negotiate is above NTLM. Move the NTLM to the top spot and that should fix it.