Conversion Functions – PARSE()

I am going to continue the series of articles (tidbits) on type conversion functions available in the Transaction SQL (T-SQL) language. One analogy of a data type conversion is apply the correct action to change the physical form of water (H2O). You can convert water to ice by cooling it. You can convert water to vapor by heating it. However, the chemical composition of water is still the same! Information is stored in a database as rows in a table. Each field in a table is defined with a certain…

String Functions – FORMAT()

I am going to continue my series of very short articles or tidbits on Transaction SQL string functions. I will exploring the FORMAT() function today. The FORMAT() function takes a value [V], a format string [F], and a optional culture specification [C] as input parameters. It returns a locale-aware formatted string. For general conversions, consider using the CAST or CONVERT functions. I personally think this string function was a valuable addition to SQL Server 2012. Please refer to the .NET Framework 4.5 formatting types for both standard and custom format…

Summarizing Data – Part 3

The SELECT reserved word is part of the Data Manipulation Language (DML) defined by Codd and is used to query data from the database. Because of its popularity, there are many clauses and functions that can be used to construct a query to retrieve data. I am continuing our exploration by reviewing SELECT queries that aggregate data. Data aggregation is the process of converting many records into a few records with special meaning. I will be using the Adventure Works 2012 sample database supplied by Microsoft during this talk. I…

Summarizing Data – Part 2

The SELECT reserved word is part of the Data Manipulation Language (DML) defined by Codd and is used to query data from the database. Because of its popularity, there are many clauses and functions that can be used to construct a query to retrieve data. I am continuing our exploration by reviewing SELECT queries that aggregate data. Data aggregation is the process of converting many records into a few records with special meaning. I will be using the Adventure Works 2012 sample database supplied by Microsoft during this talk. Today,…

Summarizing Data – Part 1

The SELECT reserved word is part of the Data Manipulation Language (DML) defined by Codd and is used to query data from the database. Because of its popularity, there are many clauses and functions that can be used to construct a query to retrieve data. Today, I am continuing our exploration by reviewing SELECT queries that aggregate data. Data aggregation is the process of converting many records into a few records with special meaning. Many of the functions used in aggregation have statistical meaning such as minimum value, maximum value,…

Import & Export Data – Part 4

The BCP command line utility is the Cadillac of ETL programs for text based data files. It is REALLY FAST. It can perform data imports, data exports and generate format files. What it lacks in the transformation step can be crafted with custom TSQL. Today, I am going to concentrate on how BCP can be used to export data to text files. The format of the output text files can be either character, native, or unicode. A quick demonstration of how to create character and xml format files will also…