Use LOGON Triggers to Audit or Control Users

Ever felt the need for knowing who is logging on to your SQL Server and at what time? Ever felt the need to restrict specific users for certain time-period or firing a trace to track down user activity? Ever felt like limiting the number of concurrent connections for specific users? Well, you can do all that now with Logon Triggers. Logon Trigger allows you to fire a T-SQL, or a stored procedure in response to a LOGON event. You may use logon trigger to audit and... Read more

Best Protocol for Connecting to SQL Server

I often get this question from the community and my answer to this question depends based on your network environment whether you are on a local computer with no networks or whether you are on a Local Area Network or whether you are on a Wide Area Network. Ive tried to provide some guidance / recommendation below that works best for many SQL Server installations. In order to connect to SQL Server, your client machine must have a network protocol enabled that can... Read more

Using Database Snapshot for Mirror Server

In the database mirroring environment Principal interacts with all the users but the mirror server only receives transaction log data from the principal server and other than that mirror database doesnt do anything as its in RECOVERING state all the time. In this knowledge nugget, we will explore how you can use Mirror database for Reporting or Querying purpose. What are Database Snapshots?  Database snapshot is a static, read-only, transaction-consistent... Read more

Using Synonyms for Creating Shortcuts

What would you do when an object that is referenced in several places and in several databases (Stored Procedures, Functions, etc.) is being moved to a different database or is being renamed due to a new object naming policy? Think of how much work this change would generate for you? Wouldnt it be nice if there was a way to take care of these changes automatically? A synonym is an alternative name (think of it like an alias) for objects in SQL Server that provides... Read more

Best Practices for SQL Server Service Account

Securing SQL Server is one of the top priorities of any DBA. It is important for DBA’s to make sure that the database server is TIGHTLY SECURED and is not exposed to users that do not need access to SQL Server. Only valid and approved users MUST be granted required permissions. Apart from configuring the right security/permissions for SQL Server users/logins, it is also important to select the right account to run SQL Server services. In todays tip, we will... Read more

Using CONCAT Function for String Concatenation

CONCAT() string function allows you to concatenate up to 255 string or variable values in to one single string. It requires a minimum of two input values when calling the function. CONCAT takes care of implicitly converting the data values to a string by following the data type conversion rules of SQL Server. This eliminates the need of explicit data conversions when concatenating two values. Note: NULL values are implicitly converted to an empty string. If all... Read more

Data Pagination Using OFFSET and FETCH

SQL Server offers new query hints that allow you to implement query paging solution. In the past, we’ve used TOP operator to return the top number of rows from a table, however, OFFSET & FETCH query clauses can give you more benefits than just the TOP operator. Let’s assume you have 50,000 records in a table and you want to query 1200 rows starting from 25000. Traditionally you would use a cursor to implement this solution however with the... Read more

Improve Index Performance using MAXDOP

Ever noticed that even though you have a beefy server, SQL Server index operations sometime takes time? There are many things that happen behind the scenes that could contribute to the slowness of index operations. Some of the reasons include: Load on SQL Server Out of date statistics Server configuration setting for degree of parallelism (MAXDOP) Amount of available memory Amount of available resources, etc. SQL Server can intelligently detect the load on the... Read more

Using SEQUENCE for Incremental Numbers

There are many applications that require incremental numbers for maintaining unique rows in a table. For several years, weve been using IDENTITY() column as our primary option to generate incremental numbers for unique records or for Primary key values and it has been the first choice for developers. Youve tried IDENTITY() and now try the new feature introduced in SQL Server 2012 and experience the difference. SQL Server introduces a brand new schema bound object... Read more

How to backup large databases

In an ideal world, hard drives and other hardware never fail, software is never defective, users do not make mistakes, and hackers are never successful. However, we live in a less than perfect world and we should plan and prepare to handle adverse events. In todays topic, I will focus on best practices for backing up large mission critical databases. Performing and maintaining good backups is one of the top priority for any DBA/Developer/Engineer working with... Read more

Write Queries Faster using T-SQL Snippets

T-SQL Snippets allow you to quickly build T-SQL statements without having to remember the commands or its syntax. This helps in reduced development time and in increased productivity for developers and DBAs. Snippet templates were introduced in SQL Server 2012 and are based on XML with predefined fields and values. When you use T-SQL snippet, fields are highlighted and the user can tab through each fields and change the values as required. Imagine a series of... Read more

Track DML Changes Without Triggers

SQL Server supports an OUTPUT clause as a part of DML statements that can help you in tracking changes made during any DML operation. The OUTPUT clause can save the result set in a table or table variable. The functionality is similar to what triggers had with INSERTED and DELETED tables which accesses the rows that have been modified during DML operation. Let’s take an example of changing the address by reversing it’s original value: Let’s... Read more

Increase Performance and Prevent Page Splits

Let’s say you have a glass filled with water and you try to put more water in that glass, what happens? Water in the glass will overflow. Exactly the same way, whenever a new row is added to a full index page, SQL Server moves around half of the rows to a new page to make room for the new row. This is known as PAGE SPLIT. Page splits can make room for new records but can be very resource intensive. Page-Splits can also incur fragmentation which may adversely... Read more