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 of this reserved word by reviewing queries that merge data using the APPLY operator. The APPLY operator come in two flavors: CROSS and OUTER. I am going to concentrate on the later during this discussion. I will be using…
Tag: SQL Server
Cross Apply Operator
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 of this reserved word by reviewing queries that merge data using the APPLY operator. The APPLY operator come in two flavors: CROSS and OUTER. I am going to concentrate on the former during this discussion. I will be using…
Multiline Table Value Functions
Today, I continuing my talk about database objects that are associated with stored (compiled) code. A user-defined function is any Transactional SQL (TSQL) or Common Language Runtime (CLR) routine that accepts parameters, performs an action, and returns the result of that action as a value. The return value can either be a scalar (single) value datatype or a composite (multiple) values table. These Table Valued Functions (TVF) functions come in two flavors: inline and multiline. I am going to concentrate on the later. Like most Data Definition Language (DDL) constructs,…
Inline Table Value Functions
Today, I continuing my talk about database objects that are associated with stored (compiled) code. A user-defined function is any Transactional SQL (TSQL) or Common Language Runtime (CLR) routine that accepts parameters, performs an action, and returns the result of that action as a value. The return value can either be a scalar (single) value datatype or a composite (multiple) values table. These Table Valued Functions (TVF) functions come in two flavors: inline and multiline. I am going to concentrate on the former. Like most Data Definition Language (DDL) constructs,…
Scalar Valued Functions
Today, I want to start talking about database objects that are associated with stored (compiled) code. A user-defined function is any Transactional SQL (TSQL) or Common Language Runtime (CLR) routine that accepts parameters, performs an action, and returns the result of that action as a value. The return value can either be a scalar (single) value datatype or a composite (multiple) values table. I am going to concentrate on Scalar Value Functions today. Like most Data Definition Language (DDL) constructs, a user define function has three operations associated with it:…
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,…
Check Database Integrity Task
Today, I will be continuing the series of articles on the control flow tasks available in SQL Server Integration Services (SSIS) to perform Database Administration (DBA) tasks. We will be designing a SSIS package that checks the database integrity of all the user databases. I am going to start up SQL Server Data Tools (SSDT) which leverages the Visual Studio 2010 shell. This development environment replace the older Business Intelligence Development Studio (BIDS) which was released with SQL Server 2005. I will create a project named “check-database-integrity” under the “c:\ssis-depot\”…
Identity Column Insert
Today, I want to talk about how to insert a value into a identity column. Most of the time, the auto increment or IDENTITY column will just be happy by being left alone. Very seldom, a power user does something stupid like turn off the foreign key constraint and remove a key from a reference table. How do we add back the key? I will be dusting off the [WILDLIFE] database from a prior article. This database has one table named [ANIMALS]. It has 445 unique animal names each with…