Tips
Benefits and limitations of using synonyms in SQL Server 2005
On my new job I faced a situation where archived tables were created in the production database and now there was a requirement to move them as the database was growing. These archival tables were being used by several jobs and also in the application code. Moving them was demanding and also a very complicated process. I wanted to find a way to minimize the amount of work that the development team had to do, since their time was limited too. Based on the needs and the limited time I was not sure what was the best option.
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 Files from Different Folders Using SQL Server and XML
Sometimes there is a need to process files in a folder, but first you need to determine which files need to be processed compared to older files that have already been processed. There are several ways that this can be done, but in this tip I show you a way this can be done using SQL Server and XML.
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.
Deleting duplicate rows when there is no primary key on a SQL Server database 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?
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 probalby 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?
Dynamic SQL execution on remote SQL Server using EXEC AT
With SQL Server 2000, we had OPENQUERY and OPENROWSET to execute a pass-through query on the specified server, but it has several inherent limitations. Starting with SQL Server 2005 we have another more elegant way using "EXEC AT" to execute a pass-through query on the specified linked server which also addresses several shortcomings of OPENQUERY and OPENROWSET table functions. In this tip I am going to start my brief discussion with OPENQUERY and OPENROWSET table functions, its limitation and how the new EXEC AT command overcomes them.
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.
How and why should I use SQL Server 2005 synonyms?
Changing the name of an table once an application has been deployed has traditionally been a difficult task in SQL Server 2000. The typical solution in SQL Server 2000 for referencing a different object was to use a View. A second option was to use the sp_rename system stored procedure to rename objects as needed. Unfortunately, this limits the capabilities of referencing other objects. The need to be able to reference another object can become critical during system migrations, application testing, data corruption, etc. Does SQL Server 2005 handle this in a more elegant manner and support more than just tables?
How to get length of data in Text, NText and Image columns in SQL Server
There is sometimes a need to figure out the maximum space that is being used by a particular column in your database. You would initially think that the LEN() function would allow you to do this, but this function does not work on Text, NText or Image data types, so how do you figure out the length of a value in a column that has one of these data types?
How to Use Synonyms to Abstract the Location of Database Objects in SQL Server
I have an ETL process where the source and staging databases are on the same SQL Server instance. Over time many stored procedures have been deployed to the staging database and these stored procedures access tables in the source database by using the three part name; i.e. [databasename].[schema].[tablename]. We are ready to move the staging database to a new server. We want to update the stored procedures in staging so that they can access the tables in the source database whether the source database is on the same or a different SQL Server instance. In other words we want to make a single change to the stored procedures that will allow them to work regardless of where the source database is deployed. Do you have any ideas on how to
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.
Limiting amount of data returned with the TEXTSIZE command in SQL Server
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?
Preventing T-SQL 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?
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 work around for Msg 2714 There is already an object named #... 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 column data due to inserting data into wrong columns for SQL Server table
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.
T-SQL Debugger is back in SQL Server 2008 ... debugging now made easy
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.
Using OPENROWSET to read large files into SQL Server
OPENROWSET is a T-SQL function that allows for reading data from many sources including using the SQL Server’s BULK import capability. One of the useful features of the BULK provider is its ability to read individual files from the file system into SQL Server, such as loading a data from a text file or a Word document into a SQL Server table. This capability is the subject of this tip.
Using SQL Server Bitwise operators to 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.
Using SQL Servers OPENROWSET to break the rules
Many times I would like to insert the results of a stored procedure into a table so I can do some extra processing with the result set. I always have to create the table first in order to perform an Insert Into Exec on the desired stored procedure since Exec Into is not an option. Is there a way to do this without having to manually create the table each time?
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
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.
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?
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?
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?
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.
How to get length of data in Text, NText and Image columns in SQL Server
There is sometimes a need to figure out the maximum space that is being used by a particular column in your database. You would initially think that the LEN() function would allow you to do this, but this function does not work on Text, NText or Image data types, so how do you figure out the length of a value in a column that has one of these data types?
T-SQL Debugger is back in SQL Server 2008 ... debugging now made easy
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.
Using OPENROWSET to read large files into SQL Server
OPENROWSET is a T-SQL function that allows for reading data from many sources including using the SQL Server’s BULK import capability. One of the useful features of the BULK provider is its ability to read individual files from the file system into SQL Server, such as loading a data from a text file or a Word document into a SQL Server table. This capability is the subject of this tip.
Deleting duplicate rows when there is no primary key on a SQL Server database 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?
Dynamic SQL execution on remote SQL Server using EXEC AT
With SQL Server 2000, we had OPENQUERY and OPENROWSET to execute a pass-through query on the specified server, but it has several inherent limitations. Starting with SQL Server 2005 we have another more elegant way using "EXEC AT" to execute a pass-through query on the specified linked server which also addresses several shortcomings of OPENQUERY and OPENROWSET table functions. In this tip I am going to start my brief discussion with OPENQUERY and OPENROWSET table functions, its limitation and how the new EXEC AT command overcomes them.
Last 10
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.
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.
How to Use Synonyms to Abstract the Location of Database Objects in SQL Server
I have an ETL process where the source and staging databases are on the same SQL Server instance. Over time many stored procedures have been deployed to the staging database and these stored procedures access tables in the source database by using the three part name; i.e. [databasename].[schema].[tablename]. We are ready to move the staging database to a new server. We want to update the stored procedures in staging so that they can access the tables in the source database whether the source database is on the same or a different SQL Server instance. In other words we want to make a single change to the stored procedures that will allow them to work regardless of where the source database is deployed. Do you have any ideas on how to
SQL Server work around for Msg 2714 There is already an object named #... 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.
Dynamic SQL execution on remote SQL Server using EXEC AT
With SQL Server 2000, we had OPENQUERY and OPENROWSET to execute a pass-through query on the specified server, but it has several inherent limitations. Starting with SQL Server 2005 we have another more elegant way using "EXEC AT" to execute a pass-through query on the specified linked server which also addresses several shortcomings of OPENQUERY and OPENROWSET table functions. In this tip I am going to start my brief discussion with OPENQUERY and OPENROWSET table functions, its limitation and how the new EXEC AT command overcomes them.
Comparing Files from Different Folders Using SQL Server and XML
Sometimes there is a need to process files in a folder, but first you need to determine which files need to be processed compared to older files that have already been processed. There are several ways that this can be done, but in this tip I show you a way this can be done using SQL Server and XML.
Swap column data due to inserting data into wrong columns for SQL Server table
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.
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 probalby 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?
T-SQL Debugger is back in SQL Server 2008 ... debugging now made easy
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.
Using OPENROWSET to read large files into SQL Server
OPENROWSET is a T-SQL function that allows for reading data from many sources including using the SQL Server’s BULK import capability. One of the useful features of the BULK provider is its ability to read individual files from the file system into SQL Server, such as loading a data from a text file or a Word document into a SQL Server table. This capability is the subject of this tip.
