Go to books ↓
SQL Server (2000, 2005, 2008, 2008 R2, 2012)
It seems like such an innocent request. It isn’t too hard to get this information out of SQL Server. But before you open SSMS and whip out a quick query, understand that there are multiple methods to get this information out of SQL Server – and none of them are perfect!
SQL Server has NEVER put an index on a foreign key column… Indexes are used to make the lookup (in a primary or unique key) for a duplicate value FAST.
Instead of NOT IN, use a correlated NOT EXISTS for this query pattern. Always. Other methods may rival it in terms of performance, when all other variables are the same, but all of the other methods introduce either performance problems or other challenges.
Do you know what triggers lurk in your database?
In this blog we will look at the Extended Event query_post_execution_showplan (similar to Showplan XML Statistics Profile) in SQL Server 2012 which has an interesting addition of cpu_time and duration for filtering.
Slow is relative. Slow is not a metric. In order to effectively measure our systems, we need metrics.This is why baselines for our systems are so essential.
If you’ve ever done spatial work with SQL Server, I hope you’ve come across the ‘nearest’ problem.
CONCAT is the new T-SQL function introduced in SQL Server 2012. It is used to concatenate the values. It accepts many parameter values seperated by comma. All parameter values are concatenated to a single string.
While this column has concentrates on one technology for passing multiple parameters to a stored procedure, that's really just a means to an end. The real goal is to make your application run faster by reducing trips to your database. However you do that will make your users say the best thing: "Hey, that was fast!"
Of course we want our SQL Servers to be fast but we also want them to be highly available and ready to serve applications any time of the day, any day of the year. And we want to do these things in a cost efficient way.
Surfing the web you will find articles saying that Nolock is dangerous and shouldn’t be used, and articles stating that it’s THE solution for many problems. The truth, as in many cases, lies in the middle.
Most are not aware of some of the basic differences between the SQL Server delete and truncate commands as well as whether these operations can be rolled back.
This particular feature was introduced in SQL Server 2008, so what you will see in this blog post applies to every version of SQL Server after SQL Server 2008.
Suppose, we have two tables in two different databases and the data in these tables needs to be fully synchronized. We can compare the data in these tables, update existing rows and add new rows. However, comparing all of the data in the tables is not an effective way and it would be better if we can detect changes in the source table and only apply these changes to the target. This problem can be solved by using the SQL Server rowversion data type.
10 SQL Server functions that are hardly used but should be used a lot more.
So you are a SQL Server DBA, and you are responsible for a database, which gives you a hard time. Users constantly complain about poor performance, and the developers blame the database (what else?). It’s time to dig in and do some serious performance tuning once and for all. But how? Where do you start?
Your boss or client came to you and said, “Give me a quick health check on this SQL Server.”
SQL Server 2016 and Azure SQL Database both give you tables that automatically keep track of changes to your data. Here's how to both create those tables and alter existing tables so they track the history of your data.
By default, SQL Server allows a maximum of 32767 concurrent connections which is the maximum number of users that can simultaneously log in to the SQL server instance. However, in some cases, database performance can be affected by such a huge number of users simultaneously querying the instance, which is not desirable.
Like most databases created in the last few decades, SQL Server allows developers to extend the database by creating functions. But prior to the upcoming 2019 release, SQL Server’s support for scalar functions was fraught with problems.