Tips
Add and Subtract Dates using DATEADD in SQL Server
Date manipulation is a common scenario when retrieving or storing data in a SQL Server database. There are several functions that are available and in this tip we look at how to use the DATEADD function.
CHECKSUM Functions in SQL Server 2005
Determining if two rows or expressions are equal can be a difficult and resource intensive. To address this need the CHECKSUM, CHECKSUM_AGG and BINARY_CHECKSUM functions are available in SQL Server 2005 to natively create a unique expression, row or table for comparison or other application needs.
Date and Time Conversions Using SQL Server
There are many instances when dates and times don't show up at your doorstep in the format you'd like it to be, nor does the output of a query fit the needs of the people viewing it. One option is to format the data in the application itself. Another option is to use the built-in functions SQL Server provides to format the date string for you.
Deciding between COALESCE and ISNULL in SQL Server
When writing T-SQL, a lot of developers use either COALESCE or ISNULL in order to provide a default value in cases where the input is NULL. They have various reasons for their choice, though sometimes this choice may be based on false assumptions. Some think that ISNULL is always faster than COALESCE. Some think that the two are functionally equivalent and therefore interchangeable. Some think that you need to use COALESCE because it is the only one that adheres to the ANSI SQL standard. The two functions do have quite different behavior and it is important to understand the qualitative differences between them when using them in your code.
Dynamically controlling the number of rows affected by a SQL Server query
One thing you may need to do is dynamically return a set amount of rows based on user input. This could be for a search function, reports, dropdown lists or whatever. Instead of hard coding a set value you would like to pass in a variable that will then determine the number of rows to return. How can this be done with T-SQL?
Finding a string value in a SQL Server table
A SQL Server/Web Application Developer in my company approached me yesterday with a dilemma. He needed to be able to find a phrase within a specific field in a SQL Server database he was developing. If he received a "hit" then the process was to return the related value in a field within a parent table. This is a typically simple process that can be handled with the use of the T-SQL CHARINDEX() function, however there were certain caveats in his requirements that would not allow CHARINDEX() to be the complete solution to his problem. He needed to only consider situations where the value of the field started with the phrase or if the phrase was the beginning of a word in the value of the search field it was a valid hit. Read on...
Generate a parameter list for all SQL Server stored procedures and functions
Parameters are widely used in user defined functions (UDF) and stored procedures (SP) in SQL Server. A user defined function in SQL Server can have up to 1,024 input parameters while a stored procedure can have 2,100 input parameters. I need to analyze the all of the parameters for my user defined functions and stored procedures in one of my databases. Specifically I want to know the parameters and the associated data types. So how can I access the user defined functions and stored procedures in one of my databases along with all their parameters to meet my needs?
Page through SQL Server results with the ROW_NUMBER() Function
For many applications searching and providing a subset of a large result set is the core application functionality. Often the result sets are large and it is a resource intensive process to gather and display the data. This is because the same query is issued each time a new page is rendered, but a different portion of the result set is displayed. Once this technique becomes too much of a performance burden, custom solutions are built to meet the need. With all of the improvements in SQL Server 2005, does a simpler and less resource intensive approach exist to efficiently page through a large result set?
Parsing a URL with SQL Server Functions
In my environment I have a few tables with thousands of rows that have URLs in particular columns. We are going through some changes in the environment and need to be able to parse out the data (base URL, file name, query parameters) from the specific columns then move the parsed data to another database design. Moving the data is no problem. I plan on using SQL Server 2005 Integration Services and I have a good sense of how to use that tool. By the way, all of the URLs are in varchar fields if that makes any difference. Can you provide any recommendations on how to parse that data?
Randomly Retrieve SQL Server Records
Have you ever had the need to randomly retrieve a set of records from a database table to display to users? One way of doing this is to use the RAND() function to generate a random number and then tie this back to your original record set. A simpler solution would be to use the NEWID() function.
Retrieving random data from SQL Server with TABLESAMPLE
In a previous tip we talked about how to randomly retrieve records from the database. This tip took a look at both the RAND() function and the NEWID() function. Both of these approaches are helpful, but one problem with both of these is that depending on how you use them you may end up reading through your entire table to get a random distribution of data. The good part about this is that you are pretty much guaranteed that you will get a totally random set of records each time. Because of the potential overhead with reading through the entire table are there any other approaches to randomly retrieving data?
SQL Server 2005 and 2008 Ranking Functions DENSE_RANK and NTILE
SQL Server introduced four new ranking functions either to rank records in a result-set or to rank records within groups of records of a result-set. With these new functions, we are no longer required to write several lines of code to get ranking data. It does not only help in simplifying the query but also improves the performance of the query. So now the questions are: what are these ranking functions, how they work and how they differ from each others?
SQL Server 2005 and 2008 Ranking Functions Row_Number and Rank
SQL Server introduced four different ranking functions either to rank records in a result-set or to rank records within groups of records of a result-set. With this inclusion we are no longer required to write several lines of code to get ranking. It does not only help in simplifying the query, but also improves the performance of the query. In this tip we will cover ROW_NUMBER and RANK.
SQL Server 2008 Functions for Tables, Views, Indexes, Columns, Stored Procedures
SQL Server provides several system meta data functions which allow users to get property values of different SQL Server objects/securables. Although you can use the Catalog views or Dynamic Management Views to get the information, sometimes the system meta data functions simplify the process. In this tip, I am going to demonstrate some of the system meta data functions and their usage in different use case scenarios.
SQL Server 2008 System Functions to Monitor the Instance, Database, Files, etc.
SQL Server provides several system meta data functions which allow users to obtain property values of different SQL Server objects and securables. Although you can also use the SQL Server catalog views or Dynamic Management Views to obtain much of this information, in some circumstances the system meta data functions simplify the process. In this tip I am going to demonstrate some of the available system meta data functions and their usage in different scenarios.
SQL Server CROSS APPLY and OUTER APPLY
SQL Server 2005 introduced the APPLY operator, which is very much like a join clause and which allows joining between two table expressions i.e. joining a left/outer table expression with a right/inner table expression. The difference between join and APPLY operator becomes evident when you have a table-valued expression on the right side and you want this table-valued expression to be evaluated for each row from the left table expression. In this tip I am going to demonstrate what APPLY operator is, how it differs from regular JOINs and what are few of its applications.
SQL Server DateDiff Example
At the moment I have a project where I have to retrieve SQL Server data based on the difference between two dates. I read the tip on the DATEADD function and wanted to know about other functions available for date/time manipulation. Can you point me in the right direction? Check out this tip to learn more about the SQL Server DateAdd function.
SQL Server function to validate Social Security Numbers SSN
Most of us have to deal with storing SSN information in databases, and how do we know if the SSN entered is valid or not. In this tip, the High Group list issued by the Social Security Administration is used to check if a particular SSN is valid.
SQL Server Rounding Functions
I saw your recent tip on Calculating Mathematical Values in SQL Server and have some related issues as I try to round values in my application. My users and me have a difference of opinion on some of the calculations in our reporting applications. All of the code is in T-SQL, but I think the reporting issues are related to data types and rounding down or rounding up rules. Do you have any insight into these issues? I would like to see some examples with a variety of coding options.
SQL Server Text Data Manipulation
Sometimes there is a need to manipulate string values using T-SQL code. With other languages such as Visual Basic, C++, C#, VBScript, etc... there are a lot of commands at your finger tips. With SQL Server 2000 you don't have all the same options, but there are enough commands that if correctly used can result in the same functionality.
SQL Server TSQL Aggregate Functions
I have started a new application where I need to start aggregating some of the data and perform some basic mathematical functions. I know I can perform some of the calculations in the front end application, but I am not sure this is the best approach in the long term. How can I do perform the aggregations in my T-SQL code based on some user needs in the OLTP application and in the corresponding reporting application? What aggregate functions are available in SQL Server 2000 and 2005? Can you provide me with some examples on how to do so in my T-SQL code?
SQLCLR function to return free space for all drives on a server
When looking for impending problems due to lack of disk space it's necessary to know how much space is available on each drive. There have been other tips written about how to do this, but in this tip I show you a way this can be done using SQLCLR
Standardize Text with Multi Search and Replace Function in SQL Server
In a previous tip, SQL Server Find and Replace Values in All Tables and All Text Columns, we walked through how to replace a single value with another single value for all text columns and all tables in a database. I have a similar need, but I need to replace multiple values with new values in one column. In this tip we look at a few examples of how this can be done.
The Many Uses of Coalesce in SQL Server
Many times people come across the Coalesce function and think that it is just a more powerful form of ISNULL. In actuality, I have found it to be one of the most useful functions with the least documentation. In this tip, I will show you the basic use of Coalesce and also some features you probably never new existed.
Using the OBJECT_SCHEMA_NAME function to return the SQL Server schema name
With the introduction of schemas in SQL 2005 more and more DBAs are taking advantage of them versus having everything owned by "dbo". In this tip I will show you a new system function that has been delivered with SQL Server to get a handle on the schemas that objects belong to along with the object information when querying the system tables.
Top 10
Deciding between COALESCE and ISNULL in SQL Server
When writing T-SQL, a lot of developers use either COALESCE or ISNULL in order to provide a default value in cases where the input is NULL. They have various reasons for their choice, though sometimes this choice may be based on false assumptions. Some think that ISNULL is always faster than COALESCE. Some think that the two are functionally equivalent and therefore interchangeable. Some think that you need to use COALESCE because it is the only one that adheres to the ANSI SQL standard. The two functions do have quite different behavior and it is important to understand the qualitative differences between them when using them in your code.
Date and Time Conversions Using SQL Server
There are many instances when dates and times don't show up at your doorstep in the format you'd like it to be, nor does the output of a query fit the needs of the people viewing it. One option is to format the data in the application itself. Another option is to use the built-in functions SQL Server provides to format the date string for you.
The Many Uses of Coalesce in SQL Server
Many times people come across the Coalesce function and think that it is just a more powerful form of ISNULL. In actuality, I have found it to be one of the most useful functions with the least documentation. In this tip, I will show you the basic use of Coalesce and also some features you probably never new existed.
SQL Server Text Data Manipulation
Sometimes there is a need to manipulate string values using T-SQL code. With other languages such as Visual Basic, C++, C#, VBScript, etc... there are a lot of commands at your finger tips. With SQL Server 2000 you don't have all the same options, but there are enough commands that if correctly used can result in the same functionality.
SQL Server CROSS APPLY and OUTER APPLY
SQL Server 2005 introduced the APPLY operator, which is very much like a join clause and which allows joining between two table expressions i.e. joining a left/outer table expression with a right/inner table expression. The difference between join and APPLY operator becomes evident when you have a table-valued expression on the right side and you want this table-valued expression to be evaluated for each row from the left table expression. In this tip I am going to demonstrate what APPLY operator is, how it differs from regular JOINs and what are few of its applications.
SQL Server Rounding Functions
I saw your recent tip on Calculating Mathematical Values in SQL Server and have some related issues as I try to round values in my application. My users and me have a difference of opinion on some of the calculations in our reporting applications. All of the code is in T-SQL, but I think the reporting issues are related to data types and rounding down or rounding up rules. Do you have any insight into these issues? I would like to see some examples with a variety of coding options.
SQL Server TSQL Aggregate Functions
I have started a new application where I need to start aggregating some of the data and perform some basic mathematical functions. I know I can perform some of the calculations in the front end application, but I am not sure this is the best approach in the long term. How can I do perform the aggregations in my T-SQL code based on some user needs in the OLTP application and in the corresponding reporting application? What aggregate functions are available in SQL Server 2000 and 2005? Can you provide me with some examples on how to do so in my T-SQL code?
Add and Subtract Dates using DATEADD in SQL Server
Date manipulation is a common scenario when retrieving or storing data in a SQL Server database. There are several functions that are available and in this tip we look at how to use the DATEADD function.
Finding a string value in a SQL Server table
A SQL Server/Web Application Developer in my company approached me yesterday with a dilemma. He needed to be able to find a phrase within a specific field in a SQL Server database he was developing. If he received a "hit" then the process was to return the related value in a field within a parent table. This is a typically simple process that can be handled with the use of the T-SQL CHARINDEX() function, however there were certain caveats in his requirements that would not allow CHARINDEX() to be the complete solution to his problem. He needed to only consider situations where the value of the field started with the phrase or if the phrase was the beginning of a word in the value of the search field it was a valid hit. Read on...
SQL Server DateDiff Example
At the moment I have a project where I have to retrieve SQL Server data based on the difference between two dates. I read the tip on the DATEADD function and wanted to know about other functions available for date/time manipulation. Can you point me in the right direction? Check out this tip to learn more about the SQL Server DateAdd function.
Last 10
Deciding between COALESCE and ISNULL in SQL Server
When writing T-SQL, a lot of developers use either COALESCE or ISNULL in order to provide a default value in cases where the input is NULL. They have various reasons for their choice, though sometimes this choice may be based on false assumptions. Some think that ISNULL is always faster than COALESCE. Some think that the two are functionally equivalent and therefore interchangeable. Some think that you need to use COALESCE because it is the only one that adheres to the ANSI SQL standard. The two functions do have quite different behavior and it is important to understand the qualitative differences between them when using them in your code.
SQL Server DateDiff Example
At the moment I have a project where I have to retrieve SQL Server data based on the difference between two dates. I read the tip on the DATEADD function and wanted to know about other functions available for date/time manipulation. Can you point me in the right direction? Check out this tip to learn more about the SQL Server DateAdd function.
Add and Subtract Dates using DATEADD in SQL Server
Date manipulation is a common scenario when retrieving or storing data in a SQL Server database. There are several functions that are available and in this tip we look at how to use the DATEADD function.
SQL Server function to validate Social Security Numbers SSN
Most of us have to deal with storing SSN information in databases, and how do we know if the SSN entered is valid or not. In this tip, the High Group list issued by the Social Security Administration is used to check if a particular SSN is valid.
Standardize Text with Multi Search and Replace Function in SQL Server
In a previous tip, SQL Server Find and Replace Values in All Tables and All Text Columns, we walked through how to replace a single value with another single value for all text columns and all tables in a database. I have a similar need, but I need to replace multiple values with new values in one column. In this tip we look at a few examples of how this can be done.
SQL Server 2008 Functions for Tables, Views, Indexes, Columns, Stored Procedures
SQL Server provides several system meta data functions which allow users to get property values of different SQL Server objects/securables. Although you can use the Catalog views or Dynamic Management Views to get the information, sometimes the system meta data functions simplify the process. In this tip, I am going to demonstrate some of the system meta data functions and their usage in different use case scenarios.
SQL Server 2008 System Functions to Monitor the Instance, Database, Files, etc.
SQL Server provides several system meta data functions which allow users to obtain property values of different SQL Server objects and securables. Although you can also use the SQL Server catalog views or Dynamic Management Views to obtain much of this information, in some circumstances the system meta data functions simplify the process. In this tip I am going to demonstrate some of the available system meta data functions and their usage in different scenarios.
SQLCLR function to return free space for all drives on a server
When looking for impending problems due to lack of disk space it's necessary to know how much space is available on each drive. There have been other tips written about how to do this, but in this tip I show you a way this can be done using SQLCLR
SQL Server CROSS APPLY and OUTER APPLY
SQL Server 2005 introduced the APPLY operator, which is very much like a join clause and which allows joining between two table expressions i.e. joining a left/outer table expression with a right/inner table expression. The difference between join and APPLY operator becomes evident when you have a table-valued expression on the right side and you want this table-valued expression to be evaluated for each row from the left table expression. In this tip I am going to demonstrate what APPLY operator is, how it differs from regular JOINs and what are few of its applications.
SQL Server 2005 and 2008 Ranking Functions DENSE_RANK and NTILE
SQL Server introduced four new ranking functions either to rank records in a result-set or to rank records within groups of records of a result-set. With these new functions, we are no longer required to write several lines of code to get ranking data. It does not only help in simplifying the query but also improves the performance of the query. So now the questions are: what are these ranking functions, how they work and how they differ from each others?
