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.