Today, I want to continue talking about database objects that are associated with stored (compiled) code. A view can be though as a virtual table whose contents are defined by a TSQL query based on one or more tables in the database. Typical uses of a view are the following: To simplify or customize the perception each user has of the database. Security mechanism to grant users access to the view, not the underlying base tables. To provide a backward compatible interface to emulate a table whose schema has…
Tag: John F. Miner III
User Defined Views – Part 1
Today, I want to continue talking about database objects that are associated with stored (compiled) code. A view can be though as a virtual table whose contents are defined by a TSQL query based on one or more tables in the database. Typical uses of a view are the following: To simplify or customize the perception each user has of the database. Security mechanism to grant users access to the view, not the underlying base tables. To provide a backward compatible interface to emulate a table whose schema has…
Outer 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 later during this discussion. I will be using…
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:…
Basic Training – Default Constraint
Today, I continuing my series of talks on fundamental SQL Server database topics. I am a proud United States Army Reservist (USAR) Veteran. Just like boot camp I went to so long ago, I am going nick name the series BASIC TRAINING. In my last article, I was talking about how to ADD, ALTER and DROP columns using the ALTER TABLE statement. One problem that we faced was how to add a column with a NOT NULL clause to an existing table that contained data. I will be introducing the…
Basic Training – Columns
Today, I continuing my series of talks on fundamental SQL Server database topics. I am a proud United States Army Reservist (USAR) Veteran. Just like boot camp I went to so long ago, I am going nick name the series BASIC TRAINING. The most basic part of a database is a TABLE which consists of COLUMNS. Many databases are works in progress that never ends since business requirements change as the company ages. With that said, changing the structure of a table is not an uncommon DBA task. We are…
Basic Training – All About NULL
Today, I continuing my series of talks on fundamental SQL Server database topics. I am a proud United States Army Reservist (USAR) Veteran. Just like boot camp I went to so long ago, I am going nick name the series BASIC TRAINING. The most basic part of a database is a TABLE which consists of COLUMNS. After choosing the data type for the COLUMN, we need to decide if the value is optional for some of the data rows. If it is, we need to define the column as NULL,…