If you’re a consultant connecting to remote client servers, or if you have a heterogenous network environment with different Active Directory forests without established trust relationships, you’ll have a few extra challenges connecting to SQL Server using Windows authentication, and SQL Server authentication may not be available.
In a sense, you could call me lazy. If there’s a script that will perform a task for me, I’d rather use that script than reinvent another wheel. Then again, if needs be, I’d rather spend a day writing such a script, rather than spending ten minutes just getting the job done.
Somehow, that makes me a happier developer.
The “include actual execution plan” feature in SQL Server Management Studio is an invaluable tool for performance tuning. It returns the actual execution plan used for each statement, including actual row counts, tempdb spills and a lot of other information you need to do performance tuning.
But sometimes you want to run a series of statements or procedures where you only want the execution plan for some of the statements. Here’s how:
I wish the DATEDIFF() function would count the number of working days (mondays through fridays) between two dates for me, but until that happens, I’ve had to roll my own scalar function. I tried to think of a smart way involving perhaps a modulus calculation, but I quickly succumbed to a more down-to-earth approach.
Moving a database or some of its files from one drive to another or from one instance of SQL Server to another is as simple as detaching it and re-attaching it again. This is actually pretty smart, compared to backup–restore, because you only perform one I/O operation (moving the file), as opposed to two (backing up, restoring).
But when you try to attach the database, you might get something like
Msg 5120, Level 16, State 101, Line 3 Unable to open the physical file "E:\Microsoft SQL Server\SQL2014\MSSQL\Data\Playlist.mdf". Operating system error 5: "5(Access is denied.)".
The reason, as I found out the hard way, is that SQL Server can actually modify the file permissions of the .mdf and .ldf files when it detaches a database.
A very common challenge in T-SQL development is filtering a result so it only shows the last row in each group (partition, in this context). Typically, you’ll see these types of queries for SCD 2 dimension tables, where you only want the most recent version for each dimension member. With the introduction of windowed functions in SQL Server, there are a number of ways to do this, and you’ll see that performance can vary considerably.
The SQL Server query optimizer can find interesting ways to tackle seemingly simple operations that can be hard to optimize. Consider the following query on a table with two indexes, one on (a), the other on (b):
SELECT a, b FROM #data WHERE a<=10 OR b<=10000;
The basic problem is that we would really want to use both indexes in a single query.
In this post, we’re going to take a look at a few examples of how this type of query would be optimized, as well as how statistics can affect the query plan, and finally, we’ll take a look at a slightly rare plan operator called “Merge Join (Concatenation)”.
Here’s a quick tip: When you’re evaluating query strategies, you may want to consider how your query will scale when the volume in your database goes up. This does not neccessarily mean that you have to start filling your tables with gigabytes on gigabytes of data.
Although it’s rotten practice, values like dates, times, numbers, etc are often stored in columns with the wrong data type. Sometimes, it’s because the data model needs to allow for multiple types of data in the same column (sometimes even on the same row in the form of a formula or text syntax), but sometimes, it’s just plain and simple lazy.
With this type of setup, it’s only a matter of time before you run into conversion errors like this one:
Msg 245, Level 16, State 1, Line 29 Conversion failed when converting the varchar value 'xyz' to data type int.
To make things worse, these errors can be tricky to pinpoint, because they can appear to come and go without any real recognizable pattern. In this post, I’ll take a look at how this happens, how to look for these errors, and ultimately how to fix them if you can’t change the database schema.
You can move or copy a database from Enterprise Edition (or Developer Edition, which supports more or less the same feature set) to Standard Edition. The simplest way is to take a backup of the database and restore that on the new server. However, if there are any Enterprise Edition features left in the database, the restored database won’t start up, and you’ll get this error, or something similar:
TITLE: Microsoft SQL Server Management Studio ------------------------------ Restore of database ‘databaseName’ failed. (Microsoft.SqlServer.Management.RelationalEngineTasks) ------------------------------ ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.SmoExtended) ------------------------------ Database 'databaseName' cannot be started in this edition of SQL Server because part or all of object 'myTableName' is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition. Database 'databaseName' cannot be started because some of the database functionality is not available in the current edition of SQL Server. (Microsoft SQL Server, Error: 909)