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.

REPLACE() VS RTRIM() and LTRIM()

   I recently had a VERY interesting experience while cleaning up some data. I had about 8GB of SQL Server table data - all nvarchar(max) stuff. The tables needed to be compared to find the differences so the data really did need to be cleaned up. For a while I used an UPDATE statement that contained the REPLACE() function in it. It worked well, but took over an hour to complete. I began to think that there HAD to be a better way to do this. All I was really concerned about was trimming white space off the front and end of the field. I thought I would try a combination of the RTRIM() and LTRIM() functions just to see what would happen like so:

UPDATE table
SET field1 = LTRIM(RTRIM(field1))


   This one simple change shaved 40 minutes off my job! Why? Because REPLACE() has to crawl across each field character by character to find each what needs to be replaced. The TRIM functions are only concerned about white space at the beginning and end of the field and so, in this case, they are much faster. There is nothing quite like using the right tool for the job. Many thanks to a very wise DBA for explaining what was going on.

Delete All Tables but Keep the Database

I needed to delete all the tables from a database this afternoon but keep the database itself. I managed to dig up this little snippet by Googling and it worked like a charm:


EXEC sp_MSforeachtable 'DROP TABLE ?'