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…
Tag: database developer
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 – 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,…
Basic Training – Tables
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. A important decision during the initial design is to choose the data types that will capture the information you want in the least amount of space. I did a good job covering the various…
Deleting Large Data
Most of the time, the transaction log is your friend since it can be used for a point in time recovery. However, when deleting large amounts of data from a database, it can be your enemy. I will be reviewing THREE different TSQL patterns to delete data. Today, we will be using the MATH database that contains prime numbers from 2 to 6 million. I will post the actual files on this article so that they can be detached and reattached as needed for each test. We need to remove…
Database Files & Objects – Part 4
Today, I will be continuing my talk on database objects that can be viewed through the system catalog views or system stored procedures. A Microsoft SQL Server database is composed of many different objects. We will be investigating how to retrieve information on these objects in the [MATH] sample database. Please see my first article that creates this database. I will be focusing on functions, stored procedures, triggers and views in this article. Let’s use the USE statement to select the sample database.
|
1 2 3 4 |
<span style="color: #008000; font-size: small;">-- Which database to use. USE [MATH] go </span> |
VIEWS – MSDN QUOTE: “A view…
Database Files & Objects – Part 3
Today, I will be continuing my talk on database objects that can be viewed through the system catalog views or system stored procedures. A Microsoft SQL Server database is composed of many different objects. We will be reviewing how to retrieve information on these objects in the [MATH] sample database. Please see my first article that creates this database. I will be focusing on tables, columns, indexes, and constraints in this article. Let’s use the USE statement to select the sample database.
|
1 2 3 4 |
<span style="color: #008000; font-size: small;">-- Which database to use. USE [MATH] go </span> |
The most basic component of a database…