Learn SQL Server functions with practical tips, examples, and tutorials to improve querying and data analysis.
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 th
SQL Server 2012 introduces two new analytical functions, LEAD() and LAG(). In this tip we will be exploring these functions and how to use them.
SQL Server 2012 introduces new analytical functions FIRST_VALUE and LAST_VALUE. These new functions allow you to get the same value for the first row and the last row for all records in a result set. In this tip we will explore these functions and how t
SQL Server string functions FORMAT and CONCAT can be used to perform an operation on a string input value and return a string or numeric value.
Table Value Functions (TVF) are great if you need to return multiple rows and/or multiple columns, because table valued user defined functions return their result as a table. Since result set acts like a table, you can just return the data or you can join
SQL Server 2012, code named Denali, has new date and time functions such as DATEFROMPARTS, TIMEFROMPARTS, DATETIMEFROMPARTS, DATETIME2FROMPARTS, SMALLDATETIMEFROMPARTS, DATETIMEOFFSETFROMPARTS, EOMONTH that can be used to Get Date and Time Values from the
SQL Server 2012, code named Denali, has new features to perform Logical Functions such as CHOOSE and IIF that can be used to perform logical operations. In this tip we take a look at some basic examples of how this could be used.
SQL Server 2012 code named Denali has a lot of new features to offer. A new set of features includes new Conversion Functions such as PARSE, TRY_PARSE and TRY_CONVERT which support data type casting and converting. In this tip we will take a look at thes
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 sorte
Learn how to use the SQL Server DatePart and DateName functions for specific parts of a date.
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 th
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.
There are situations when you may need to remove HTML tags from your character string data. As an example, consider having to submit a product data feed to a search engine like Google. The detailed product description is mandatory in this case. It is rec
Suppose that your company uses an international ERP application which stores the date and time data as UTC, not as local time. Usually the conversion to the local time is handled at the application level. But this time you need a custom data export to ano
Recently I did a migration for SQL Server 2008 from another platform. When I converted the database schema to SQL Server, I faced a problem converting TIMESTAMP to SQL Server. In MySQL/DB2, Timestamp is a datetime field. It is used to track changes to a
You need to generate random data directly into SQL Server table columns or close to the database engine as variables or expressions. Looking at the SQL Server available functions, you notice that only RAND function offers support for random data generatio
You need to use financial functions as a tool to aid you in making business decisions and sometimes to run what if scenarios. Although there are numerous packages in the market offering this ability, they may be more than you actually need. Simply using
I saw this tip on new date types provided with SQL Server 2008. I am interested in using the datetimeoffset because we support end users around the world. How do I convert the datetime data types to the datetimeoffset data type in SQL Server 2008?
You need to provide data validation at the server level for complex strings like phone numbers, email addresses, etc. You may also need to do data cleanup / standardization before moving it from source to target. Although SQL Server provides a fair number
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.
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 ad
Check out these SQL functions to help you find and replace multiple values in a string.
For security reasons many sites disable the extended stored procedure xp_cmdshell, which is used to run DOS commands or executables. When you really have to run a DOS command or an executable from a stored procedure how can you get around this limitation
The very useful concat aggregate in the article How to pass multiple arguments to a CLR aggregate in SQL Server 2008 accepts multiple arguments and returns concatenated strings delimited by any string requested. That's more useful than the Concatenate ag