Tips
Calculating and verifying financial values in SQL Server
When working with cash flow calculations in SQL Server one of the key concepts is the Net Present Value of a stream of payments. In a database of payment information, how can the Net Present Value of a stream of payments be calculated in a way that is easy for users to request? Just as important, once the number is calculated, is being sure that the answer is correct? How does the coder or tester know? In this tip I show you a couple of ways you can confirm your T-SQL calculations.
Calculating Mathematical Values in SQL Server
In our application we have the need to perform mathematical calculations. Right now we are doing so in our front end application. Unfortunately we are starting to experience performance problems with large data sets and differences in calculations due to developers using different logic. We are seeking some other options to perform the calculations. Does SQL Server perform basic mathematical calculations?
Comparing performance for different SQL Server paging methods
Every DBA has worked on an application that does some sort of search and returns paged lists to the user. Each version of SQL Server has introduced new ways to return these paged lists (aside from the obvious of returning the entire list and let the application server handle the paging). This tip will look at the different methods that can be used in each version, starting with SQL Server 2000, and compare their performance in a SQL 2012 database.
Comparing performance for the MERGE statement to SELECT, INSERT, UPDATE or DELETE
Now that SQL Server 2008 has been out for quite awhile I've started to see a lot more use of the MERGE statement that became available in this version. While I do find it makes code more readable, I wondered whether or not there were any performance benefits or drawbacks to using this new statement as compared with just using the classic SELECT/INSERT/UPDATE/DELETE statements to complete the same task.
Create SQL Server temporary tables with the correct collation
When we write code, we sometimes use temporary tables. Using a temporary table is a convenient way to store intermediate results, and then use them at a later phase in our application logic. When using temporary tables without specifying a collation (for the column used) SQL Server will inherit the collation for our newly created temporary table from the SQL Server instance default. In case our SQL Server database has a different collation setting than the instance’s default (there might be various reasons for that) we might fall into a trap of having two tables containing similar information, but with different collation settings. How can I create the temporary tables with the correct collation?
Delete duplicate rows with no primary key on a SQL Server table
Every once in awhile a table gets created without a primary key and duplicate records get entered. The problem gets even worse when you have two identical rows in the table and there is no way to distinguish between the two rows. So how do you delete the duplicate record?
Deleting Data in SQL Server with TRUNCATE vs DELETE commands
There are two main keywords used for deleting data from a table: TRUNCATE and DELETE. Although each achieves the same result, the methods employed for each vastly differ. There are advantages, limitations, and consequences of each that you should consider when deciding which method to use.
Determining SET Options for a Current Session in SQL Server
With each session that is made to SQL Server the user can configure the options that are SET for that session and therefore affect the outcome of how queries are executed and the underlying behavior of SQL Server. Some of these options can be made via the GUI, while others need to be made by using the SET command. When using the GUI it is easy to see which options are on and which options are off, but how can you get a better handle on all the options that are currently set for the current session?
Differences between SQL Server temporary tables and table variables
I have heard of table variables, but not sure how to use them in a stored procedure. What purpose do they serve and why not just use temporary tables instead?
Dropping multiple SQL Server objects with a single DROP statement
Almost every SQL Server object that is created may need to be dropped at some time. Especially when you are developing you create a bunch of temporary objects that you probably do not want to keep in the database long term. Most SQL Server users drop one object at a time using either SSMS or a single drop statement. In many scenarios we may need to drop several objects of the same type. Is there a way to drop several objects through less lines of code? And what types of SQL Server objects can be dropped simultaneously through a single drop statement?
Getting a SQL Server RowCount Without doing a Table Scan
Sometimes there is a need to get record counts from every table in your database. One way of doing this is to do a SELECT count(*) on all of your tables, but this could create a lot of overhead especially for large databases and large tables. If you don't require an exact answer, it isn't necessary use a SELECT count(*) query on the rows in a table to get the row count.
Getting creative with Computed Columns in SQL Server
Computed columns are easy to setup for simple scenarios, but in this tip we look at how to get more creative when using computed columns.
Keeping data available in the SQL Server data cache with PINTABLE
Have you ever wondered why even after optimizing a database that it still takes considerable time to fetch the results? The problem is whenever a query is fired SQL Server fetches the data from the database by means of bringing the data pages into memory. Depending on the database activity the data you may be using quite frequently may be getting paged in and out of memory which may account for why sometimes it takes longer then other times to fetch the data. This paging is done automatically, but if you feel that a table is being used very frequently then this is where the DBCC PINTABLE command may come in handy.
Limit amount of data returned with the SQL Server TEXTSIZE command
When working with large-value data types such as varchar(max), nvarchar(max), varbinary(max), text, ntext, and image data types sometimes you do not need to return the complete column contents, but maybe only a portion of the data. This may be for testing or maybe just to check to see if there is a value or not. This can be done by using the SUBSTRING function or the LEFT function, for each column that uses this data type, but are there any other functions that can be used to limit the amount of data returned by a SELECT statement for all columns of these data types?
Nullability settings with select into and variables
Traditionally there has been a single option for pulling data from one result set into a new table using a minimally logged operation without pushing the data to a flat file first – the select…into statement.
Preventing TSQL code from running on a Production SQL Server
I have T-SQL code that I cannot have run on my production SQL Server. How can I be sure that it does not run in production by mistake from a programmatic perspective? If the code ran in production, it would take us hours to get back up and running. The code that I am concerned about is responsible for purging data. Due to business requirements, I need a way to protect our production environment from this code being executed. Any thoughts?
Problem using DISTINCT in case insensitive SQL Server databases
SQL Server gives you the ability to store mixed case data in your databases, but depending on how you create your databases SQL Server will ignore the case when you issue T-SQL commands. One of the problems you may be faced with is that you want to get a distinct list of values from a table to show the differences in your table, but if your database is setup as case insensitive the DISTINCT clause does not show the differences it all gets grouped together. So based on this what options are there?
Renaming SQL Server database objects and changing object owners
As with most things in life, nothing ever stays the same. This is true with SQL Server and also with your applications that utilize SQL Server. The way applications and databases were originally designed may or not meet your current or future needs and therefore there is a need to change what you thought was the perfect solution when it was first rolled out. Changes that you make on a daily or weekly basis probably are embedded in the application or database code (stored procs, triggers, etc...), but one change that you may face is the need to rename database objects that already exist. What options are available to rename database objects?
Script to calculate the Median value for SQL Server data
The standard SQL language has a number of aggregate functions like: SUM, MIN, MAX, AVG, but a common statistics function that SQL Server does not have is a built-in aggregate function for median. The median is the value that falls in the middle of a sorted resultset with equal parts that are smaller and equal parts that are greater. Since there is no built-in implementation for the median, the following is a simple solution I put together to find the median.
SQL Server 2008 TSQL Debugger is back in SSMS
If you recall your days working with SQL Server 2000, you would remember debugging a routine (Stored Procedure, UDF and trigger) in Query Analyzer, as a debugger tool was available with it. This was moved to Visual Studio for SQL Server 2005, but it is back in SQL Server 2008 Management Studio. This tip shows some of the highlights of how to use this tool.
SQL Server Bitwise operators store multiple values in one column
Sometimes there may be the need to save multiple values into one column instead of creating multiple columns to store these indicators. Let's take for example we need to know who has different telephone types and instead of having multiple indicator columns for each type of telephone number you use one column to store the different values. So for example value 1 = home phone, 2 = mobile phone, 3 = fax, etc... You could have three different bit (yes/no) columns such as homeNumberIndicator, mobileNumberIndicator and faxNumberIndicator, but each time you add a new phone type you need to add a new column to your table. Another way to do this is to store the data in a binary format and use the bitwise (&) operator.
SQL Server Four part naming
With some applications there may be a need to pull data from other instances of SQL Server or from other databases within the same instance. Often this can be achieved by having multiple connections from your application pointing to each of these data sources. This is great for this one application, but what if there is a need to do this within SQL Server or for stored procedures or views that are called from several applications?
SQL Server Insert Tutorial
This SQL Server tutorial is full of SQL insert examples. The SQL Server INSERT command is used to add data to tables. The SQL insert statement is valuable to build your SQL Developer skills.
SQL Server SELECT Tutorial
In this tutorial we will cover the most used T-SQL statement: SELECT. We will cover creating your first SELECT statement and provide a basic explanation for typical SELECT statements. The tutorial high level outline is: overview, multiple columns, WHERE, ORDER BY, aliasing columns, aliasing tables, JOINing tables and auto generating SELECT statements. Check out this tutorial to get up to speed on the SELECT command.
SQL Server sp_ prefix when and when not to use it
There have been many articles written about the issue with prefixing SQL Server stored procedures with "sp_". In this tip we will take a look at some of these issues and also some differences between SQL 2000 and SQL 2005, 2008 and 2008 R2.
SQL Server stored procedure to generate random passwords
SQL Server is used to support many applications and one such feature of most applications is the storage of passwords. Sometimes there is a need to reset a password using a temporary password or generate a random password for a new user. In this tip I cover a simple stored procedure to generate random passwords that can be incorporated into your applications.
SQL Server work around for Msg 2714 There is already an object named #temp in the database
I was working on a development project and was getting these intermittent error messages like "Msg 2714... There is already an object named 'pk_#PackageWeight' in the database." which plagued a group of stored procedures that create seemingly simple reports that use temporary tables. After a little digging I found the problem and this tip offers a solution to work around this error message.
Swap SQL Server column data due to inserting data into wrong columns
This article is for newbies who have just started their career in SQL development. Everyone might have faced this problem when importing or inserting data into tables when they accidentally put the data in the wrong columns. The first thought would be to delete the data and start over, but in this tip we look at a simple solution to swap the data.
Using INSERT OUTPUT in a SQL Server Transaction
Frequently I find myself in situations where I need to insert records into a table in a set-based operation wrapped inside of a transaction where secondarily, and within the same transaction, I spawn-off subsequent inserts into related tables where I need to pass-in key values that were the outcome of the initial INSERT command. Thanks to a Transact/SQL enhancement in SQL Server, this just became much easier and can be done in a single statement... WITHOUT A TRIGGER!
Using MERGE in SQL Server to insert, update and delete at the same time
In a typical data warehousing application, quite often during the ETL cycle you need to perform INSERT, UPDATE and DELETE operations on a TARGET table by matching the records from the SOURCE table. For example, a products dimension table has information about the products; you need to sync-up this table with the latest information about the products from the source table. You would need to write separate INSERT, UPDATE and DELETE statements to refresh the target table with an updated product list or do lookups. Though it seems to be straight forward at first glance, but it becomes cumbersome when you have do it very often or on multiple tables, even the performance degrades significantly with this approach. In this tip we will walk through
Using SOUNDEX and DIFFERENCE to Standardize Data in SQL Server
My client wants to standardize address information for existing and future addresses collected for their customers, particularly the street suffixes. The application used to enter and collect address information has the street suffix separated from the address field, but it is a textbox instead of a drop down list therefore things are not standardized. I know there are some options out there to standardize data, but they would like a less expensive alternative. In this tip we look at these two functions and how they can be used.
When to use SET vs SELECT when assigning values to variables in SQL Server
SET and SELECT may be used to assign values to variables through T-SQL. Both fulfill the task, but in some scenarios unexpected results may be produced. In this tip I elaborate on the considerations for choosing between the SET and SELECT methods for assigning a value to variable.
Top 10
Comparing performance for different SQL Server paging methods
Every DBA has worked on an application that does some sort of search and returns paged lists to the user. Each version of SQL Server has introduced new ways to return these paged lists (aside from the obvious of returning the entire list and let the application server handle the paging). This tip will look at the different methods that can be used in each version, starting with SQL Server 2000, and compare their performance in a SQL 2012 database.
Comparing performance for the MERGE statement to SELECT, INSERT, UPDATE or DELETE
Now that SQL Server 2008 has been out for quite awhile I've started to see a lot more use of the MERGE statement that became available in this version. While I do find it makes code more readable, I wondered whether or not there were any performance benefits or drawbacks to using this new statement as compared with just using the classic SELECT/INSERT/UPDATE/DELETE statements to complete the same task.
Using MERGE in SQL Server to insert, update and delete at the same time
In a typical data warehousing application, quite often during the ETL cycle you need to perform INSERT, UPDATE and DELETE operations on a TARGET table by matching the records from the SOURCE table. For example, a products dimension table has information about the products; you need to sync-up this table with the latest information about the products from the source table. You would need to write separate INSERT, UPDATE and DELETE statements to refresh the target table with an updated product list or do lookups. Though it seems to be straight forward at first glance, but it becomes cumbersome when you have do it very often or on multiple tables, even the performance degrades significantly with this approach. In this tip we will walk through
Differences between SQL Server temporary tables and table variables
I have heard of table variables, but not sure how to use them in a stored procedure. What purpose do they serve and why not just use temporary tables instead?
Deleting Data in SQL Server with TRUNCATE vs DELETE commands
There are two main keywords used for deleting data from a table: TRUNCATE and DELETE. Although each achieves the same result, the methods employed for each vastly differ. There are advantages, limitations, and consequences of each that you should consider when deciding which method to use.
Calculating Mathematical Values in SQL Server
In our application we have the need to perform mathematical calculations. Right now we are doing so in our front end application. Unfortunately we are starting to experience performance problems with large data sets and differences in calculations due to developers using different logic. We are seeking some other options to perform the calculations. Does SQL Server perform basic mathematical calculations?
SQL Server stored procedure to generate random passwords
SQL Server is used to support many applications and one such feature of most applications is the storage of passwords. Sometimes there is a need to reset a password using a temporary password or generate a random password for a new user. In this tip I cover a simple stored procedure to generate random passwords that can be incorporated into your applications.
Renaming SQL Server database objects and changing object owners
As with most things in life, nothing ever stays the same. This is true with SQL Server and also with your applications that utilize SQL Server. The way applications and databases were originally designed may or not meet your current or future needs and therefore there is a need to change what you thought was the perfect solution when it was first rolled out. Changes that you make on a daily or weekly basis probably are embedded in the application or database code (stored procs, triggers, etc...), but one change that you may face is the need to rename database objects that already exist. What options are available to rename database objects?
SQL Server 2008 TSQL Debugger is back in SSMS
If you recall your days working with SQL Server 2000, you would remember debugging a routine (Stored Procedure, UDF and trigger) in Query Analyzer, as a debugger tool was available with it. This was moved to Visual Studio for SQL Server 2005, but it is back in SQL Server 2008 Management Studio. This tip shows some of the highlights of how to use this tool.
Delete duplicate rows with no primary key on a SQL Server table
Every once in awhile a table gets created without a primary key and duplicate records get entered. The problem gets even worse when you have two identical rows in the table and there is no way to distinguish between the two rows. So how do you delete the duplicate record?
Last 10
Comparing performance for different SQL Server paging methods
Every DBA has worked on an application that does some sort of search and returns paged lists to the user. Each version of SQL Server has introduced new ways to return these paged lists (aside from the obvious of returning the entire list and let the application server handle the paging). This tip will look at the different methods that can be used in each version, starting with SQL Server 2000, and compare their performance in a SQL 2012 database.
Comparing performance for the MERGE statement to SELECT, INSERT, UPDATE or DELETE
Now that SQL Server 2008 has been out for quite awhile I've started to see a lot more use of the MERGE statement that became available in this version. While I do find it makes code more readable, I wondered whether or not there were any performance benefits or drawbacks to using this new statement as compared with just using the classic SELECT/INSERT/UPDATE/DELETE statements to complete the same task.
SQL Server stored procedure to generate random passwords
SQL Server is used to support many applications and one such feature of most applications is the storage of passwords. Sometimes there is a need to reset a password using a temporary password or generate a random password for a new user. In this tip I cover a simple stored procedure to generate random passwords that can be incorporated into your applications.
Script to calculate the Median value for SQL Server data
The standard SQL language has a number of aggregate functions like: SUM, MIN, MAX, AVG, but a common statistics function that SQL Server does not have is a built-in aggregate function for median. The median is the value that falls in the middle of a sorted resultset with equal parts that are smaller and equal parts that are greater. Since there is no built-in implementation for the median, the following is a simple solution I put together to find the median.
SQL Server sp_ prefix when and when not to use it
There have been many articles written about the issue with prefixing SQL Server stored procedures with "sp_". In this tip we will take a look at some of these issues and also some differences between SQL 2000 and SQL 2005, 2008 and 2008 R2.
SQL Server Insert Tutorial
This SQL Server tutorial is full of SQL insert examples. The SQL Server INSERT command is used to add data to tables. The SQL insert statement is valuable to build your SQL Developer skills.
Getting creative with Computed Columns in SQL Server
Computed columns are easy to setup for simple scenarios, but in this tip we look at how to get more creative when using computed columns.
Create SQL Server temporary tables with the correct collation
When we write code, we sometimes use temporary tables. Using a temporary table is a convenient way to store intermediate results, and then use them at a later phase in our application logic. When using temporary tables without specifying a collation (for the column used) SQL Server will inherit the collation for our newly created temporary table from the SQL Server instance default. In case our SQL Server database has a different collation setting than the instance’s default (there might be various reasons for that) we might fall into a trap of having two tables containing similar information, but with different collation settings. How can I create the temporary tables with the correct collation?
SQL Server SELECT Tutorial
In this tutorial we will cover the most used T-SQL statement: SELECT. We will cover creating your first SELECT statement and provide a basic explanation for typical SELECT statements. The tutorial high level outline is: overview, multiple columns, WHERE, ORDER BY, aliasing columns, aliasing tables, JOINing tables and auto generating SELECT statements. Check out this tutorial to get up to speed on the SELECT command.
Using INSERT OUTPUT in a SQL Server Transaction
Frequently I find myself in situations where I need to insert records into a table in a set-based operation wrapped inside of a transaction where secondarily, and within the same transaction, I spawn-off subsequent inserts into related tables where I need to pass-in key values that were the outcome of the initial INSERT command. Thanks to a Transact/SQL enhancement in SQL Server, this just became much easier and can be done in a single statement... WITHOUT A TRIGGER!
