How to allow nulls in unique fields

An interesting workaround using unique fields Unique fields in SQL Server are created using unique constraints or unique indexes, furthermore, each unique constraint uses a unique index. Regardless of using unique constraint or unique index, the field can accept null values, however the uniqueness will result in only accepting a single row with null value. The solution … Read more

Careful With Trustworthy Setting

Trustworthy database setting is an easy solution for some problems in the server, especially cross database access. However, this setting has also a security problem in some scenarios. The scenario that has a security break is the following: One or more databases are owned by a user with ‘sysadmin’ rights. If the databases were created … Read more

Creating schemas without DBO permission

One result of having the correct care with the access permissions in our databases is the need to avoid the DBO permission even to the users responsible for the database model. Using the database roles such as ddl_admin or even in more grainy ways it’s easy to achieve this goal. However, it’s more complex when … Read more

5 Monitoring Queries for SQL Server

Every DBA squirrels away favourite queries for monitoring SQL Server. Nowadays many of these are too complex to keep in your head. Dennes describes how he uses T-SQL queries for solving problems, whether it involves fixing the problems of missing indexes, preventing unrestrained autogrowth, avoiding index fragmentation, checking whether jobs have failed or avoiding memory stress conditions. … Read more

Identifying client timeouts

Long running queries often causes client timeout in our applications. One good solution to attack this problem is to identify the timeouts and optimize the queries causing the timeouts. Client timeouts or connection interruptions are identified as Attention events. We can capture the attention events using an Extended Events session. The script to create the … Read more

How to identify contention in TempDb

TempDb database is used to store all temp tables created for our applications. Whether explicit or implicit created, all temp tables activity is done in tempdb, creating a bottleneck. For this reason, it’s important to identify tempdb contention. Tempdb contention will appear in the form of latches. Latch is a synchronization object that protects internal … Read more

How to find blocked processes

Blocked processes are often a big problem to DBA’s. They are difficult to monitor. We receive desperate calls saying “Everything is slow!” and before we can do anything a second call “Oh, don’t worry, it’s everything ok again”. Most of times it happens because ill-behaved process that blocks other tasks for short periods. How can … Read more

Are the statistics being updated?

The auto-update statistics configuration updates statistics when the amount of row changes in the table achieves 20% of total rows. However, for tables with too many rows, this threshold is too high. For example, a table with 1 million rows will only update statistics after 200 thousands changes. That’s too much and can affect query … Read more

How to find CPU intensive queries

High CPU usage is a very common problem in SQL Servers. Any query with a bad query plan can steal CPU time and harm system response time. Fortunately we can find queries with high CPU time using sys.dm_exec_query_stats DMV. This DMV, created in SQL Server 2008, keeps performance statistics for cached query plans, allowing us … Read more

Creating a Date Dimension in a Tabular Model

As well as its multidimensional model, SQL Server Analysis Services (SSAS) now has a tabular model of database that either runs in-memory or in DirectQuery mode. The in-memory analytics engine allows the users of Excel or Power View to base reports on tabular model objects. Having shown how to handle date-based information using the Multi-dimensional model, Dennes now turns his attention on the in-memory tabular model.… Read more

How to find blocked process

Blocked processes are often a big problem to DBA’s. They are difficult to monitor. We receive desperate calls saying “Everything is slow!” and before we can do anything a second call “Oh, don’t worry, it’s everything ok again”. Most of times it happens because ill-behaved process that blocks other tasks for short periods. How to … Read more

How to find CPU intensive queries

High CPU usage is a very common problem in SQL Servers. Any query with a bad query plan can steal CPU time and harm system response time. Fortunately we can find queries with high CPU time using sys.dm_exec_query_stats DMV. This DMV, created in SQL Server 2008, keeps performance statistics for cached query plans, allowing us … Read more

Are your check constraints trustable ?

SQL Server keeps a record to identify if a check constraint is trustable or not. When all the records in the database where checked against the constraint it’s trustable, otherwise it’s not. If the check constraint is trustable, it can be used by the query optimizer. For example, if the check constraint avoid values below … Read more

Compress and Decompress in SQL Server 2016

  SQL Server 2016 ctp 3.2 is already available with a lot of news. One of the new features available is the ability to compress and decompress fields during DML (insert/select/update) using the functions Compress and Decompress. One important point to notice is that compressed fields cannot be indexed. On the other hand, if you … Read more

The news about the first day of PASS Summit

Once again, I have the opportunity to attend to this wonderful conference in Seattle, fully of new information about our loved database server. Let’s try to summarize the new information I already saw in the first day and put here some links to you, so you can follow these news and I hope to fill … Read more

A few more about translation of cube dimensions

  Translations are a very usefull feature in multidimensional cubes. We can translate not only the cube structure, but also the data. Of course we need to have the data already translated in our table fields. AdventureWorksDW has a dimension table named dimProducts that has EnglishProductName, SpanishProductName and FrenchProductName, so it’s easy to configure translation, … Read more

Join Simple Talk

Join over 200,000 Microsoft professionals, and get full, free access to technical articles, our twice-monthly Simple Talk newsletter, and free SQL tools.

Sign up