Tips

SQL Server Concepts and Best Practices [Part 1]

Hi Folks,

It’s been a while that I don’t post anything here, so it’s time to start a series of SQL Server concepts and Best Practices. Most of them, I’ve already shared in my repository on GitHub.
So, take a peek when you get a chance. https://github.com/danilocbraga/SQLServer

MAX vs Top 1 – which one is better?

When there is a clustered index on the table and the column to be queried, both the MAX() operator and the query SELECT TOP 1 will have almost identical performance.

When there is no clustered index (Heap) on the table and the column to be queried, the MAX() operator offers the better performance. So, consider using MAX rather than SELECT TOP 1.

CTE vs #temp table

CTEs…

* Are unindexable (but can use existing indexes on referenced objects)
* Cannot have constraints
* Are essentially disposable VIEWs
* Persist only until the next query is run
* Can be recursive
* Do not have dedicated stats (rely on stats on the underlying objects)

Temp Tables…

* Are real materialized tables that exist in tempdb
* Can be indexed
* Can have constraints
* Persist for the life of the current CONNECTION
* Can be referenced by other queries or subprocedures
* Have dedicated stats generated by the engine

As far as when to use each, they have very different use cases. If you will have a very large result set, or need to refer to it more than once, put it in a #temp table. If it needs to be recursive, is disposable, or is just to simplify something logically, a CTE is preferred. Also, a CTE should never be used for performance. You will almost never speed things up by using a CTE, because, again, it’s just a disposable view. You can do some neat things with them but speeding up a query isn’t really one of them.

When to use char/varchar/nchar/nvarchar?

  • nchar and nvarchar can store Unicode characters.
  • char and varchar cannot store Unicode characters.
  • char and nchar are fixed-length which will reserve storage space for number of characters you specify even if you don’t use up all that space.
  • varchar and nvarchar are variable-length which will only use up spaces for the characters you store. It will not reserve storage like char or nchar. nchar and nvarchar will take up twice as much storage space, so it may be wise to use them only if you need Unicode support.

It is worth highlighting the importance to keep your code (variables) using the same data type definition as the column definition. Mismatch data types will probably result in Non-Sargable queries.

Using the same column name in different tables but with different data types

Probably anybody will assume that columns with the same name in different tables have the same data type. As a result, they won’t verify data types. Different types is an accident waiting to happen.

Check that before creating a new column with the same name:

SELECT sh.name+'.'+o.name AS ObjectName,
s.name as ColumnName,
CASE WHEN t.name IN ('char','varchar') THEN t.name+'('+CASE WHEN s.max_length<0 then 'MAX' ELSE CONVERT(varchar(10),s.max_length) END+')'
WHEN t.name IN ('nvarchar','nchar') THEN t.name+'('+CASE WHEN s.max_length<0 then 'MAX' ELSE CONVERT(varchar(10),s.max_length/2) END+')'
WHEN t.name IN ('numeric') THEN t.name+'('+CONVERT(varchar(10),s.precision)+','+CONVERT(varchar(10),s.scale)+')'
ELSE t.name END AS DataType
,CASE WHEN s.is_nullable=1 THEN 'NULL' ELSE 'NOT NULL' END AS Nullable     
FROM sys.columns s
INNER JOIN sys.types t ON s.system_type_id=t.user_type_id and t.is_user_defined=0
INNER JOIN sys.objects o ON s.object_id=o.object_id
INNER JOIN sys.schemas sh on o.schema_id=sh.schema_id
WHERE O.name IN (select table_name from information_schema.tables)
--AND s.name = 'NewColumn'
ORDER BY sh.name+'.'+o.name,s.column_id

Float and real datatypes

Why we should not use float and real datatypes to store money in SQL Server? The main problem with them is that they are approximate numerics, so they don’t store exact values.

Take a look at this:

image2017-6-29 10-35-6

The float and real data types are known as approximate data types. The behavior of float and real follows the IEEE 754 specification on approximate numeric data types. Approximate numeric data types do not store the exact values specified for many numbers; they store an extremely close approximation of the value. For many applications, the tiny difference between the specified value and the stored approximation is not noticeable. At times, though, the difference becomes noticeable. Because of the approximate nature of the float and real data types, do not use these data types when exact numeric behavior is required, such as in financial applications, in operations involving rounding, or in equality checks. Instead, use the integer, decimal, money, or smallmoney data types. https://technet.microsoft.com/en-us/library/ms187912(v=sql.105).aspx

Decimal is always a better choice even compared to money datatype, where we might have issues in cases of division.

image2017-6-29 10-35-47

I hope I could help you somehow.
See you soon!

 

Advertisements

Sargable queries – Using indexes effectively

It’s really important to have effective indexes in your database, but also equally important to ensure that yours queries are written properly to use these indexes effectively.
So, one thing that we always need to keep in mind is to avoid using Nonsargable search conditions.

The word sargable is a contraction of “Search ARGument ABLE”, where the optimizer has the ability to seek a row in the index.

Sargable Conditions
The following predicates CAN be matched to an index seek operation:

=,>,>=,<,<=, and BETWEEN, and some LIKE conditions such as LIKE 'Search%'

Nonsargable Conditions
An index cannot be used to seek in the following predicates:

<>,!=,!>,!<, NOT EXISTS, NOT IN, and N0T LIKE IN, OR, and some
LIKE conditions such as LIKE '%Search'

So, basically you should use this:

WHERE ID BETWEEN 51825 AND 51828
WHERE Name LIKE 'Ice%'
WHERE ProductID = 771
WHERE UnitPrice < 3.975
rather than this:
WHERE ID IN (51825, 51826, 51827, 51828)
WHERE Name LIKE '%Ice%'
WHERE LEFT(Name,3) ='Ice'
WHERE ABS(ProductID) = 771
WHERE ProductID <> 771
WHERE ProductID NOT IN (771,772)
WHERE UnitPrice + 1 < 3.975

Also, we shouldn’t use functions in the left side (the field side). Functions on WHERE clause columns also hurt query performance for the same reasons as mentioned before.

So, we should use this

WHERE Startdate >= '20160501' and Startdate < '20160601'

rather than

WHERE MONTH(Startdate)=5 AND year(Startdate)=2016

You might want to see more details about it in the links below.
http://www.simple-talk.com/sql/performance/index-selection-and-the-query-optimizer
http://sqlmag.com/t-sql/inside-search-arguments