instructor: craig duckett lecture 17: tuesday, december 1 st, 2015 sql server keys, files,...

1

Instructor: Craig Duckett

Lecture 17: Tuesday, December 1st, 2015SQL Server Keys, Files,

Functional Concepts and Architecture, Database Files

BIT275: Database Design (Fall 2015)

2

We will be looking at Microsoft SQL Server, Azure, virtual server, and Visual Studio until the end of the quarter.

NOTE: Today will be the last lecture in BIT275. This Thursday, December 3rd, and next Tuesday, December 8th , will be dedicated to Assignment 3 Workshops. I will be supplying templates for building tables, inserting data, creating views, and creating stored procedures that you can use to construct your individual database projects. I will also be offering one-on-one help for those who feel they need additional help in order to complete their project.

• Assignment 3 is due LECTURE 19, Tuesday, December 8th, in StudentTracker by MIDNIGHT

• Final Exam is LECTURE 20, Thursday, December 10th

3

Tuesday/Thursday (LECTURE 16)• Database Design for Mere Mortals: Chapters 12, 13

4

• SQL Server Keys• SQL Server Database Files• Transactions and Locks (Separate PowerPoint)• Triggers (Separate PowerPoint)

FOR REFERENCE ONLY• Functional Concepts and Architecture• Database Files

SQL Server Primary and Foreign Keys

An Overview

Setting Up Your Primary Key (PK)

1. From the main window, select your database2. Double-click on Tables to expand3. Select table of your choice, right-click on it, and select Design4. Find the Column Name that you want to make the Primary Key, right-click on it a select Set

Primary Key5. This creates the Primary Key and places a yellow key icon beside it in the Design table

ERROR: Setting Up Your Primary Key (PK) From Allowed NULL to NOT Allowed NULL

From the main window, select Tools > Options > Designers > Table and Database Designers• Uncheck the option Prevent

saving changes that require table re-creation

• OK

Add AUTO INCREMENT to the Primary Key

1. In the Designer section, highlight the Primary Key row (by clicking on the yellow Primary Key icon)

2. In the Column Properties tab, scroll down to Identity Specification, and in the dropdown menu beside (Is Identity) change this from No to Yes

3. You can go with the default values of '1' for Increment and Seed or you can change these to your specifications

Inserting Data into a Table (See the AI at Work)

1. Right-click on table of your choice, and select Edit Top 200 Rows

Adding Foreign Keys (FK)

NOTE: Before adding FOREIGN KEYS you must already have all your PRIMARY KEYS set and configured in your pertinent tables

1. Open your database, and expand your Tables directory tree2. Right-click on the table that is going to contain the Foreign Key(s), and select Design3. Right-click on the blank white area and select Relationships4. In the Foreign Key Relationships window, click Add 5. Click Tables And Columns Specification, then the 'three ellipsis' button […]


6. Select the Primary Key Table and field that has been designated the primary Key, and then select the Foreign Key Table and the key that will be the associated Foreign Key, then OK

7. Save, and click by the warning about saving


8. I repeat the process for my second Foreign Key field, this time pointing to the pertinent Primary Key tables, fields, Foreign Key tables, fields, etc, then saving


9. Back in the main window, I select my database, then right-click on Database Diagrams > New Database Diagram

10. I highlight the tables I want to add, then Add


11. The Database Diagram comes up showing the selected tables with their associated relationships using the Primary and Foreign Keys

12. At this point you have the option of saving the diagram (I usually save mine, because I can always delete them later as I build complexity into the database).


13. Back in the main window, you can expand all your tables and Key folders to see the Primary Keys. To see the Foreign Keys on the linking table, you will have to expand the Columns folder.

SQL Server Database Files

SQL Server Database FilesSQL Server System Databases

• master• model• msdb• tempdb

SQL Server System Databases are very simple and pretty small databases (msdb might be huge in some cases if we are not properly maintaining it) at a very high level, but they are also most important databases within SQL Server. Why am I stressing 'the most important'? Because SQL Server stores the majority of its configuration information in these databases, besides storing some in the Registry and few in configuration(ini/xml) files, and SQL Server needs them to coexist in a healthy and happy shape in order to remain in a running state. So what are the System Databases in SQL Server?

http://www.youtube.com/watch?v=Wjv1c1lgitY

http://www.youtube.com/watch?v=Wjv1c1lgitY

SQL Server Database FilesMASTER MSDN Information about Master

The master database stores all the system-level information for SQL Server. The data stored by the master database includes information for: configuration settings, logon accounts, linked servers and endpoints, user database file locations, and properties.

The master database is the most important database on SQL Server, because due to the nature of the data stored, SQL Server cannot operate without the master database. So it is a very good idea to backup this database after changing the SQL Server configuration, modifying, adding, or removing any databases.

Examples:

Tables > System TablesViews > select * from sys.databasesProgrammability > sp_attach_db

http://msdn.microsoft.com/en-us/library/ms187837(v=sql.105).aspx

http://msdn.microsoft.com/en-us/library/ms188279.aspx

https://www.simple-talk.com/sql/database-administration/sql-server-endpoints-soup-to-nuts/

SQL Server Database FilesMSDBMSDN Information About MSDB

The msdb database is used by SQL Server to store information on operations performed by SQL Server.

This includes information for: the SQL Server Agent, Database Mail, the Service Broker, SSIS packages, log shipping, backup, and maintenance plan job parameters.

Examples:

Tables > System Tables > dbo.sysjobs - SQL Server Agent - SSIS Packages (SS Integration Services for crunching/manipulating data) Views > dbo. sysjobs_viewProgrammability > Stored Procedures




http://msdn.microsoft.com/en-us/library/bb522893.aspx

http://www.youtube.com/watch?v=jLVSnyakwzU


http://ola.hallengren.com/sql-server-backup.html


SQL Server Database FilesTEMPDBMSDN Information About TEMPDB

As the name implies, SQL Server uses the tempdb database for storing temporary data and data objects. The tempdb database is used when an operation requires a temporary table, stored procedure, or other database object to be performed. Intermediary data for large sort operations is also stored in the tempdb database as well as temporary data for internal SQL Server operations. It is where instances of database queries are cached.

Every time SQL Server is restarted, the tempdb system database is recreated thus clearing any temporary data stored during the last SQL Server session. In cases where a high volume of users and operations are performed with SQL Server the tempdb database can grow to use a significantly large amount of disk space. It is important to plan accordingly in these scenarios since running out of disk space where the tempdb database is stored will have catastrophic effects on the operation of SQL Server. DBAs will often store the tempdb on a completely different drive


SQL Server Database FilesMODELMSDN Information About MODEL

SQL Server uses the model database for creating new databases. Simply put, this is a template.

When the “create database” statement is used, SQL Server copies the contents of the model database to the newly created database. If there are any common database objects that would prove useful in all databases created by SQL Server, it is possible to add those objects to the model database.

Then when a database is created by the SQL Server instance, the user defined objects will be copied to it along with the default objects. Since SQL Server recreates the tempdb database every time it is started, the model database is required in order for SQL Server to start.

Demo:

Create 'Awesome' Table in ModelCreate instance of a new Database


SQL Server Database FilesRESOURCEMSDN Information About RESOURCE

The resource db is a "read-only" database used for storing all the system views and stored procedures. Logically, each SQL Server database will contain all these system objects, however, they are physically stored within the resource database. The resource database is read-only and does not include any user data.

In previous versions of SQL Server, the system objects were stored in the master database. The motivation behind moving the objects to a separate database is to make updating the SQL Server more efficient. Improvements and fix-ups to the SQL Server system generally manifest mostly on the system objects. A separate database to store the system objects reduces the number of files that need to be replaced with an update.

LOCATION:

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn

FYI:

It is recommends that you don't move the resource databases to a separate directory or drive but to leave 'as is'


SQL Server Books OnlineMSDN Information About SQL SERVER BOOKS ONLINE

Accessing SQL Server Books Online

You can access SQL Server Books Online in the following ways:

From the Start menu: Click Start > All Programs > Microsoft SQL Server 2008 R2 > Documentation and Tutorials > SQL Server Books Online

From SQL Server Management Studio: On the Help menu, click How Do I, Search, Contents, Index, or Help Favorites.

From F1 or Help buttons in the user interface - For context-sensitive information, press F1 on your keyboard, or click Help in the user interface dialog box.

From the Dynamic Help window - The Dynamic Help window automatically displays links to Books Online topics related to the task you are doing. To launch Dynamic Help, click Dynamic Help on the Help menu in SQL Server Management Studio or Business Intelligence Development Studio.


SQL Server TutorialsAccessing SQL Server Tutorials

MSDN Information About SQL Server Tutorials

From the Start menu: Click Start > All Programs > Microsoft SQL Server 2008 R2 > Documentation and Tutorials > SQL Server Tutorials

Tutorial: SQL Server Management Studio Tutorial: Writing Transact-SQL Statements


http://msdn.microsoft.com/en-us/library/bb934498(v=sql.105).aspx


Computed | Calculated Columns

Computed (Calculated) ColumnsA computed column is a column that contains values that are calculated, rather than inserted. When the column is defined, a computed column specification is provided. This definition includes the expression that is used to determine the value of the column in each row of the table. It is not possible to specify the values for these columns in INSERT or SELECT statements.

Adding a Computed Column

1. Select a database table that might benefit from having a calculated column

http://www.blackwasp.co.uk/SQLComputedColumns.aspx

http://www.blackwasp.co.uk/SQLComputedColumns.aspx

Computed (Calculated) Columns2. Right-click on the table, select Design, and a create a column and data type that will be used

for the computed or calculated column 3. While having the focus, go down into Column Properties and select the Computed Column

Specification, click down in the (Formula) section, and type in your calculation

Computed (Calculated) Columns4. Now when I examine the table after right-clicking and invoking Select Top 1000 Rows, I see

the calculated column Total

Computed (Calculated) Columns

MSDN Information: Specify Computed Columns in a Table


Functional Concepts and Architecture

The Following Slides Have Been Included For Reference Only

Functional Concepts and ArchitectureSQL – Structure Query LanguageMost vendors, back in the day, began to adopt proprietary versions of that although they had a lot of overlap of certain types of language elements, specifically the language elements intended for

DML – Data Manipulation Language ANSI SQLDML tended to be pretty much the same across different vendors with just really minor variations. To help that cleaner across the industry ANSI SQL was developed and there have been 2 or 3 revisions of this over the years.

Now, for the majority of its Data Manipulation Language, SQL SERVER 2008 fully complies with the ANSI SQL standard.

Now Data Manipulation basically means:• Selecting data to a database (SELECT)• Adding data to database (INSERT)• Removing data from database (DELETE)• Changing data in database (UPDATE)

This doesn't give you any management capability, though. It doesn't let you create new databases, change or check performance, optimize data or databases, etc.

ANSI = American National Standards InstituteISO = International Standards Organization

Functional Concepts and ArchitectureDDL – Data Definition LanguageBecause every DBMS works a little bit differently (MySQL, SQL Server, Oracle, DB2, etc) the DDL really can't be standardized, so each vendor comes up with its own variant (or "dialect") of the SQL language. CREATE | DROP | ALTER

Microsoft's superset of the SQL language, or variant, is called Transact-SQL (or T-SQL, for short), and part of that does comply with the ANSI SQL standard for data manipulation language, Example:

SELECT ID, Name, Address FROM Customers WHERE state = 'Nevada';

DELETE from Customers WHERE ID = 5;

It's when we move over to the DDL side of things that the variants or "dialects" between the different DMBS really come into play. The way you use CREATE TABLE in MS SQL Server 2008 is a bit different than the way you'd CREATE TABLE in MySQL or PostgreSQL or DB2, etc.

Functional Concepts and ArchitectureWhat is a Table?

Tables can be thought of in either logical or physical terms. For example, when most DBAs think of a table, they see a two-dimensional set of values arranged into rows and columns, similar in appearance to a spreadsheet. Each column represents a different data element, and each row represents a collection of related data elements.

For example, here is a representation of a logical table that has three columns and four rows. This should look very familiar to DBAs as this is how most of us visualize what a table in a database looks like. It also looks similar to the results we see when we perform a SELECT statement on a table within Management Studio.

Functional Concepts and ArchitectureWhat is a Table?

While a logical visualization of table is very handy for DBAs to use in their work, this is not how SQL Server sees a table. When a table is created, it has to be implemented physically inside a database.

For example, the most basic structure is the database file (.mdf and .ndf files). The database file can include many objects, but for now, let's focus on tables. I'll talk about a third type of file, the transaction log file (.ldf) later on.

Every database has one primary data file. Also, all the data in the database objects (tables, stored procedures, views, triggers.. etc.) are stored in the primary data files. The recommended and the most common file name extension for primary data files is .mdf

You can only have one primary data file for a database, as well as secondary data files. Some databases may not have any secondary data file, but its also possible to have multiple secondary data files for a single database. Secondary data files are usually denoted with the .ndf extension.

Log files in SQL Server databases hold all the log information. This information can be later used to recover the database. The size of the log file is determined by the logging level you have set up on the database. There must be at least one log file for each database. But it is also possible to have more than one log file for a single database. The recommended file name extension for log files is .ldf.

Locations of all the files in a database (primary, secondary and log files) are stored in the primary file of the database and in the master database. When the SQL Server Database Engine want to use the file location information of those files, it retrieves the data from the master database.

Functional Concepts and ArchitectureEach table in a database can be broken into multiple components. Let’s take a brief look at each of them so we better understand what makes up a physical table within SQL Server.

Functional Concepts and ArchitectureThe first component is a partition. A partition is simply a way DBAs can sub-divide rows in a table (and the table’s indexes) into separate sections, generally for the purpose of horizontally spreading rows across more than one filegroup. This often makes it easier for DBAs to manage and scale very large tables.

The topic of partitioning is outside the scope of this class, so we'll assume there is a single partition (which is the default option when creating a table). Only the Enterprise and Developer editions of SQL Server support partitioning.

Partitions contain data rows stored in either the physical form of a heap (a table without a clustered index) or a B-Tree structure (a table with a clustered index). We'll talk about the heap, b-tree structure, clustered and non-clustered indexes in a moment, but first we need to discuss how the data types are sub-divided into three different categories before they are stored in allocation units, comprised of pages.

• In_Row_Data: This is the most common type of allocation unit, and is used to store data and index pages, except for Large Object (LOB) data. In other words, most of your row data (and related indexes) are stored in this type of allocation unit.

• Lob_Data: Used to store Large Object (LOB) data, such as text, ntext, xml, image, varchar(max), nvarchar(max), varbinary(max), and Common Language Runtime (CLR) user-defined data types.

• Row_Overflow_Data: Used to store data pages when the variable data types—varchar, nvarchar, varbinary, and sql_variant data columns—that exceed the 8,060 bytes that can fit onto a single data page.


Allocation units themselves are made up of extents. In our example, we see that each allocation unit has one extent. In the real world, each allocation unit would include many, many extents.

An extent is a collection of eight contiguous 8K pages (for a total of 64K).

A page is the most elemental unit of data storage used by SQL Server, and will be the focus of our next discussion.

Functional Concepts and ArchitecturePrimary Storage Structure in MS SQL Server

How does SQL Server store data? Logically, Data is stored in records, and records are stored in pages.

A database page is exactly 8KB (8192-byte) in size, and acts as an individual piece of a database data file. If you have 1 MB worth of data, that data will be stored on 128 separate pages.

These 8K pages aren't dedicated just for the data only; the first 96 bytes are used to store information about the page type, free space, object id, and location of the table that this page belongs to. This is the header. After this header information, the data itself comes in (in the form of data rows which are inserted one after another in a serial manner). This is the body.

http://sqlblog.com/blogs/linchi_shea/archive/2008/03/03/is-the-8kb-page-obsolete-or-aging.aspx

Following the page header, starting at byte 96 on the page, are the actual data rows. Each data row has a unique row number written within the page. Data rows in SQL Server cannot cross page boundaries. The maximum available space in a SQL Server page is 8096 bytes (8192 bytes minus the 96 byte header), but including 36 bytes for holding log information actually lowers the amount of data that can be held on the page down to 8060 bytes.

http://sqlblog.com/blogs/linchi_shea/archive/2008/03/03/is-the-8kb-page-obsolete-or-aging.aspx

Functional Concepts and ArchitectureAs stated earlier, the limit on data row size does not take into account columns of text, ntext, or image data types because these data values are stored separately from the data row, as we'll look at a bit later on.

Data rows are put on the page serially, starting immediately after the header.

A row offset table starts at the end of the page, and each row offset table contains one entry for each row on the page. Each entry records how far the first byte of the row is from the start of the page.

The entries in the row offset table are in reverse sequence from the sequence of the rows on the page.

Every SQL Server page (8,192 bytes) begins with a 96-byte header used to store metadata about the page. This includes the page’s number, page type, amount of free space on the page, the allocation unit ID of the object that owns the page.

Immediately following the page header is the data to be stored, one row after another. Each page can store a maximum of 8,060 bytes. The number of rows that can be stored on a page depends on the size of the rows. For example, the smaller the rows, the more rows that can fit on a single data page. When a page becomes full, any new data will be inserted in another data page.

In the real world, each row may be a different length, which means that the beginning and ending of each row can be anywhere within the page.

If rows are packed in, one after another, and if each row can vary in size, how does SQL Server know when a row starts and when it ends? That is what row offsets are used for. Every row in a page has a row offset, which indicates how far the first byte of the row is from the start of the page. Row offset data is stored at the bottom of a page in an area reserved for this information. For example, when the first row is added to a page, the location of the first byte of that row is stored in offset row one. When row two is added, the location of the first byte of the second row is stored in offset row two, and so on. This way, SQL Server knows where each row begins and ends.

Functional Concepts and ArchitectureLarge Row Support

Rows cannot span pages, however portions of the row may be moved off the row's page so that the row can actually be very large.

The maximum amount of data and overhead that is contained in a single row on a page is 8,060 bytes (8 KB). However, this does not include the data stored in the Text/Image page type. This restriction is relaxed for tables that contain varchar, nvarchar, varbinary, or sql_variant columns.

When the total row size of all fixed and variable columns in a table exceeds the 8,060 byte limitation, SQL Server dynamically moves one or more variable length columns to pages in the ROW_OVERFLOW_DATA allocation unit, starting with the column with the largest width. This is done whenever an insert or update operation increases the total size of the row beyond the 8060 byte limit.

When a column is moved to a page in the ROW_OVERFLOW_DATA allocation unit, a 24-byte pointer on the original page in the IN_ROW_DATA allocation unit is maintained. If a subsequent operation reduces the row size, SQL Server dynamically moves the columns back to the original data page.

ROW_OVERFLOW_DATAWe'll look how this relates to BLOBS

and FILESTREAMS in a minute

Functional Concepts and ArchitectureExtents

Extents are the basic unit in which space is managed. An extent is eight physically contiguous pages, or 64 KB. This means SQL Server databases have 16 extents per megabyte.

To make its space allocation efficient, SQL Server does not allocate whole extents to tables with small amounts of data. SQL Server has two types of extents:

• Uniform extents are owned by a single object; all eight pages in the extent can only be used by the owning object.

• Mixed extents are shared by up to eight objects. Each of the eight pages in the extent can be owned by a different object.

A new table or index is generally allocated pages from mixed extents. When the table or index grows to the point that it has eight pages, it then switches to use uniform extents for subsequent allocations. If you create an index on an existing table that has enough rows to generate eight pages in the index, all allocations to the index are in uniform extents.

Functional Concepts and ArchitectureDatabases are optimized for structured data that is relatively small – about 8KB per row. At that rate, you can put 130,000 packed-out rows into a database and still have a database under 1 gigabyte.

Generally speaking, smaller databases are easier to backup, restore, and maintain, so keeping them small is good.

Documents, however, tend to be orders of magnitude larger than the average database row size and can quickly bloat the database; a content database upwards of 100 gigabytes is by no means uncommon and that size is comprised mostly by the physical files that reside inside the database.

When your content database size starts to expand to the point of unmanageability, one of the first questions that may come to mind is why the heck are all those files in there in the first place?

It makes perfect sense to store the structured document metadata like the author, location, keywords, document type, and file size directly in the database, for querying purposes, but the physical file itself is just taking up room until someone goes to download it.

So the option to move files outside of the database and back into the file system, where they have traditionally resided, seems very sensible.

Functional Concepts and ArchitectureWhat is a Blob?

As much as you want your documents outside of the database, the folks who make databases really don't like them being in there either. Documents tend to represent unstructured data that makes it difficult to use them in querying, defining relationships, indexing, or any other database operation that normally makes putting data into a database useful. As far as the database is concerned, a document is just a bunch of bits that it has to hold onto until someone asks for them back.

Logically, storing a document in a database row looks something like this

Databases performance is optimized for rows that are under 8KB in size. If the actual implementation looked like this, that size would easily be breached and performance would lag.

Functional Concepts and ArchitectureFortunately, database developers came up with the concept of a Binary Large Object (BLOB). In essence, the BLOB is a pointer that stores the location of binary data. The pointer, which is relatively small, resides with the normal database record. The binary data, which is comparatively huge, resides in some dark, magical location in the database called BLOB storage.

With the binary data offloaded to another location in the database, the database record becomes much smaller and more optimal for index and query performance. If we actually need the binary data associated with the row, the database uses the pointer in the row to acquire the binary data from BLOB storage.

The process of going to BLOB storage is completely transparent, so it appears as though the data is coming directly from the row itself. There is a slight performance cost for going to BLOB storage for raw data retrieval, but the query performance benefits tend to outweigh the retrieval cost.

Functional Concepts and ArchitectureWhat is Remote Blob Storage?

BLOB storage is effectively abstracted from database users. You ask for it, the database goes out and gets it. This brings up a new question: why does the BLOB storage have to be in the database? In SQL Server 2005 it had to be in the database simply because that's how they built it. But in SQL 2008 and new newer versions, you've got a few more options because Microsoft implemented a FileStream provider model for the BLOB storage mechanism that opens up the possibility of storing it elsewhere.

Remote BLOB Storage is the term used to denote that a BLOB provider stores BLOB data outside of the database (i.e. remotely).

Functional Concepts and ArchitectureWhere Can I Store Remote Blob Data?

A marketing guy would eagerly tell you that you can store BLOBs anywhere you want, but the reality is that you can store it anywhere you want as long there is a provider available capable of storing it there. If you're really ambitious you can build your own (not recommended), but the majority of us are just going to use what comes out of the box, or whatever is available from third party providers.

Out of the box you get two providers:

• Native SQL BLOB Provider (Default) Stores BLOB data directly in the database, just like SQL Server 2005. This is the default BLOB provider whenever you setup a new instance of SQL server.

• FileStream BLOB Provider This is the only Remote BLOB provider that ships with SQL Server and it allows you to store BLOB data on any local hard drive on the SQL server.

http://www.ceservices.com/adding-filestream-existing-table-database-sql-2008-r2

Free PDF Book: http://download.red-gate.com/ebooks/SQL/Art_of_SS_Filestream_Sebastian_and_Aelterman.pdf

http://msdn.microsoft.com/en-us/library/cc905212(v=sql.100).aspx

http://www.ceservices.com/adding-filestream-existing-table-database-sql-2008-r2

http://download.red-gate.com/ebooks/SQL/Art_of_SS_Filestream_Sebastian_and_Aelterman.pdf



Functional Concepts and ArchitectureNow that you have a basic understanding of how SQL Server stored data, it’s time to talk about the heaps and B-Trees structures that we talked about briefly a little earlier.

What is a Heap?

A heap is simply a table without a clustered index. (We will talk about clustered indexes a bit later too).

When rows are added to a heap, they are not stored in any particular order on a data page, and data pages are not stored in any particular sequence within a database. In other words, rows are stored wherever there is room available. This means that the data pages that contain the rows of the heap may be scattered throughout a database, in no particular order.

Since a table can’t exist as a bunch of scattered pages, SQL Server provides a way to link them all together so that they act as a single table. This is done using what are called Index Allocation Map (IAM) pages.

IAM pages manage the space allocated to heaps (among other tasks), and is what is used to connect the scattered pages (and their rows) into a table.

Functional Concepts and ArchitectureLet’s say that a query is executed against a heap, and a table scan has to be performed to find the data to be returned.

In order to find the rows in the table that need to be scanned, the SQL Server Database Engine first goes to an IAM page, which includes pointers to the various extents and pages that contain the rows of the table that belong to the heap.

If a heap is large, numerous IAM pages and data pages have to be examined before all the rows are located and scanned. If the data pages happen (by coincidence) to be physically contiguous, then such scans can be I/O efficient because sequential reads can be used to read the data.

But in many cases, the various heap pages are scattered about, which requires less efficient random reads to scan through all the rows of the table, which can hurt performance.

Functional Concepts and ArchitectureWhat is an Index?

In the previous section we talked about heaps, which are tables without a clustered index. In this section, we will talk about what an index is. Later, we will talk about specific kinds of indexes that can be added to SQL Server tables.

As I have already mentioned, an index is simply a way to help queries return data faster from tables. In SQL Server, all indexes physically take the form of what is called a B-Tree.

The “B” in B-Tree refers to “balanced,” so B-Tree is short for Balanced Tree. This is because a B-Tree index self balances, which means that every time a tree branches (splits into two pages because the original page is full), about half of the data is left on the old page, and the other half is put on the new page. One of the key benefits of having a Balanced Tree is that finding any particular row in a table requires about the same amount of SQL Server resources because the index has the same depth (number of levels) throughout its structure.


Notice that B-Trees have several levels. They include:

Root Level: A B-Tree starts with a single index page called the root level. This is where a query begins to look for specific data within the B-Tree. In our example, our root level page only contains two values. Most root level pages have many values, each referring to a page in the intermediate level.

Intermediate Level: Most B-Trees have one or more intermediate levels. The number of intermediate levels depends on the amount of data stored in the table being indexed. Our example has only one intermediate level, which includes two index pages.

Leaf Level: A B-Tree has a single leaf level which may include many, many data pages, depending on the amount of the data stored as part of the leaf level. The leaf level is where the data you are looking for is stored. For example, in the leaf level page that starts with “1”, all the rows with a customer number that ranges from “1” through “2499” are located. On the leaf level page that begins with “2500”, all the rows that range from “2500” to “4999” are located, and so on.


Here’s how a B-Tree index works.

In a B-Tree index search, we always begin at the root level. For example, to find the rows that have a customer number of “4”, the first step is to scan the rows at the root level. In our example, there are two rows than can be scanned: a “1” and a “5000”. Each of these rows points to different pages in the intermediate level. The rows we are seeking have a customer number of “4”, but as you can see, there is no “4” at the root level. But since this index is in numerical order, we know that “4” follows “1”, but is less than “5000”. So, to find the rows that have a value of “4”, we must hop down (transverse) to the intermediate level to further narrow our search. As you can see in the example, the number “1” in the root level points to an index page in the intermediate level that also starts with the number “1”.


When we go to the intermediate level and look at the index page that begins with the number “1”, notice that it only has two records: a “1” and a “2500”. Notice that the number “4” is still nowhere to be found. Again, we know that the number “4” is greater than 1, but less than “2500”, so we need to transverse to the leaf level and see if we can find it there.


So the next step is to transverse the index to the leaf level page that begins with “1” and ends with “2499”, as we know that number “4” falls between these numbers. Once we are at the correct leaf level page, the rows in this page are examined until we find one or more records that have a customer number of “4”.

While this seems like a long way around to find the rows that have a customer number of “4”, it is actually very short. Instead of having to potentially scanning through thousands and thousands of data pages looking for the rows to be returned, the query only had to scan through several different index pages until the rows that matched the WHERE clause of the query are found and returned. This is usually a much faster way to find the rows you want to return. Of course, this example was kept simple. In the real world, there may be more intermediate levels that need to be transversed, but using a B-Tree index to retrieve data is usually much faster than a scan of every row in a table.

Now that you have a basic understanding of how B-Tree indexes work, we are ready to talk about the specific kinds of indexes available to SQL Server. Keep in mind that although each of the following indexes work slightly differently, that are all still B-Tree indexes, and work on the principles described above.

Functional Concepts and ArchitectureTypes of Indexes

SQL Server includes many different types of B-Tree indexes, including:

• Clustered • Non-clustered

• Indexed view • XML • Filtered (New with SQL Server 2008) • Spatial (New with SQL Server 2008) • Compressed Indexes (New to SQL Server 2008)

Today, I'll only be looking at Clustered and Non-Clustered B-Tree Indexes.

Functional Concepts and ArchitectureClustered

Technically speaking, a clustered index is a B-Tree data structure where all the rows in a table are stored at the leaf level of the index. In other words, a clustered index not only includes the root level and intermediate level index pages found in B-Tree indexes, it also includes all the data pages that contain every row in the entire table. In addition, the rows in a clustered table are logically ordered by the key selected for the clustered index (unlike a heap whose rows and pages are unordered). Because of this, a table can only have one clustered index.


A clustered index orders the data stored in it logically. Often, DBAs confuse logical ordering with physical ordering. For example, what if a new row is added between two other rows, and there is no room on the existing page for the new row? When SQL Server faces this problem, here’s what it does. Once SQL Server has determined more space is needed on a page, the page will be split. This means that about half of the rows stay on the existing page and about half of the rows are added to a new page. The physical order of the rows stays intact on the two pages, with the new row being place in the proper order on either the original or the new page.

Think for a moment, if SQL Server required that all rows in a clustered index were to be physically contiguous, then as part of the page split, the new page would have to be physically inserted directly after the original page, and all of the following data pages would have to be shifted down one page. If there were 100,000 pages below the page that was split, that would mean that 100,000 pages would have to be physically moved, which would be hugely resource intensive and inefficient. Instead, what SQL Server does when a page split occurs, is to place the new page where there is room for it, which in many cases won’t be very close to the original page. In other words, the physical order of the pages aren’t maintained. But what is maintained is the logical ordering of the pages. For example, when a page is split between the original and the new page, they are logically connected using the page chain, which is used to enforce the logical ordering of the data.

Functional Concepts and ArchitectureNon-Clustered Index

Like a clustered index, a non-clustered index is a B-Tree data structure (although it is a separate data structure from the clustered index). The main difference between the two is that while a clustered index includes all of the data of a table at the leaf level, a non-clustered index does not. Instead, at the leaf level, a non-clustered index includes the key value and a bookmark (pointer) that tells SQL Server where to find the actual row in the clustered index.

Because a non-clustered index doesn’t normally store the actual data at its leaf level, it is possible to have many non-clustered indexes on a table. For example, a table can have one clustered index and up to 249 non-clustered index.

Let’s take a brief look at how a non-clustered index works. In this example, we are going expand upon our previous example of a Customer table that has three columns: Customer Number, First Name, and Last Name. As in our previous example, we are going to have a clustered index that uses the Customer Number as its key. In addition, we are going to add a non-clustered index on the Last Name column. Given this, let’s say we want to return all the rows (and all the row’s columns) in the Customer Table that have a last name of “Victoria”, and that we want to use the non-clustered index on the Last Name column to quickly find and return the data.


xxx


When we go to the intermediate level and look at the index page that begins with the letter “M”, notice that it only has two records: an “M” and an “S”. Notice that the letter “V” is still nowhere to be found. Since we can’t find the letter “V” on this index page, we need to transverse to the leaf level and see if we can find it there.

Once we get to the leaf level page that begins with “S”, we now identify all the rows on that page that begin with “V”. Each row that begins with “V” is then compared to the last name “Victoria”. In our case, there is a single match. The last name “Victoria” is found in the Last Name non-clustered index key. Now we know that a row does exist that meets our selection criteria, the problem is that all of the data for that row (Customer Number, First Name, and Last Name) aren’t stored at the leaf level. Instead, the only data stored at the leaf level is the key “Victoria” and a bookmark that points to the corresponding record in the table—which as we know—is a clustered index. In this example, the bookmark correspondents with the Customer Number “4”.

Now that SQL Server knows that a row does exist, and that it exists in the clustered index as Customer Number 4, the actual contents of the row (the three columns) must be retrieved. So next, SQL Server must take the bookmark value, the number “4”, and then start at the root level of the clustered index, and then transverse the intermediate and leaf level until it locates the matching row. At this point, the data is returned back the original query.

The first step in retrieving all of the rows with a Last Name of “Victoria” is to use the non-clustered index on the Last Name column to identify any rows that match. To find the rows, SQL Server begins at the root level of the non-clustered index, looking for any matching rows. The root page is scanned and two rows are found: an “A” and an “M”. Each of these rows points to different pages in the intermediate level. The first letter of “Victoria” is “V”, but there is no row in the root level page that begins with the letter “V”. But since this index is in alphabetical order, we know that “V” follows “M”. So, to find the letter “V”, we must transverse to the intermediate level to further narrow our search. As you can see, the letter “M” points to an index page in the intermediate level that starts with the letter “M”.

SQL Server Database Files.MDF File

• "Main Data File"• Database Tables• Indexes• Triggers• Store Procedures• Etc.

.LDF File

• "Log Data File"• Transaction Log file

.NDF File(s)

• "aNother Data File"• Secondary Database file• Can make Primary• Can change path, size

By default, a database will only have an .MDF and .LDF file. It's up to the database designer / developer / administrator whether or not it also has an .NDF file (or multiple .NDF files).

File placement strategies are all about performance and reliability.

Typically, you are going to want to place your .LDF files on a separate disk/drive from everything else. Ideally you don't want them on the same disk as the operating system or the SQL Server program files, and you definitely don't want them on the same disks as your databases.

SQL Server Database FilesBy default, a database will only have an .MDF and .LDF file. It's up to the database designer / developer / administrator whether or not it also has an .NDF file (or multiple .NDF files)


FILESTREAMDATABASE 1 DATABASE 2


FILESTREAMDATABASE 1 DATABASE 2

FILEGROUPS

SQL Server Database FilesWhere are the SQL Server database files actually located?

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA

SQL Server Database FilesHow do you create or alter the .MDF, .NDF, or . LDF files?

1. Open SQL Server Management Studio (SSMS), and login2. In the Object Explorer column, expand the Databases directory3. Right-click on your database (e.g., Books) and select Properties from the popup

SQL Server Database Files4. In the Database Properties window, select Files (from Select a page column)

SQL Server Database Files5. In the Database Files window, under the Logical Name column, can be found the file

names for your .mdf and .ldf files

6. Here you can change the Initial Size (MB) of the files as well as set up the Autogrowth properties of both files - Click in the Initial Size (MB) window to change the size - Click on the "three ellipsis" button under Autogrowth to change properties

7. Here too, you can scroll to the right to see the Path and File Names of your database files

SQL Server Database Files8. To create a .NDF file for your database, click the Add button in the Database

Properties windows; this will create a new row in the Database files window

SQL Server Database Files9. Give the new Database file a name (since I’m going to use it as a “secondary”

database file I appended a ‘_secondary’ after my name), and then I selected <new filegroup > from the dropdown menu. Give your new Filegroup a name (e.g., zTest_Secondary).

SQL Server Database Files8. To create a .NDF file for your database, click the Add button in the Database

Properties windows; this will create a new row in the Database files window

SQL Server Database Files9. I create a new Database file called zTest_secondary and a new Firegroup called

SECONDARY. If I go look in the MSSQL\DATA directory on the server, I can see my files

C:\Program Files\Microsoft SQL Server\MSSQL11_50.MSSQLSERVER\MSSQL\DATA

SQL Server Database FilesHow to Move Database Files to a Different Physical Location

1. Open SQL Server Management Studio (SSMS), and login2. In the Object Explorer column, expand the Databases directory3. Right-click on your database (e.g., zTest2013) and select Properties from the

popup4. From the left side Select a page column menu, click on Files to see the Path of

your current Data files. Write it down or use Control + C to copy the path and save it for later use (in a text or Word file, whatever you prefer).

5. Back in the main window, detach the database by right-clicking on the database, then selecting Tasks, then Detach…


7. A new window will appear. Click on Drop Connections, then the OK button. You'll notice back in the main window that your database is no longer listed.


8. Navigate to the location of your MSSQL\DATA files that you wrote or copied down earlier.

9. Copy/Move the datafiles to your new physical location (I created a directory on my E: drive called zDatafiles). Note: I always recommend copying instead of moving in case the file(s) get corrupted during the move, you will still have the original file(s) to go back to. Once the copy is successful, you can now safely delete the original files.

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA


10. Once the files are copied, return to the main window, click on the databases folder, and choose Attach.


11. From the new window, click Add.


12. I kept my original files in the MSSQL\DATA directory on the C: drive AND copied them to a new zDatafiles directory on the E: drive (just to show how this is done). Now, I could point to ALL the files on the E: drive and attach them there, OR I could reattach my original MDF file on my C: drive, then attach my LDF file from my E: drive, thus separating my MDF/NDF files from my LDF transaction log file on two physically different drives.

Navigate to the location of your original MDF file and select it.


13. Return to the main window, and your database should now be listed (if not, right-click on the Databases folder and select Refresh)

Adding a Table to a New Database1. Right-click on your database, and select New Table…

Adding a Table to a New Database2. In the Properties table, give your table a pertinent name, then start adding your column

names, data types, etc.

SQL Server FILESTREAM

SQL Server FILESTREAMHow do you create or alter the FILESTREAM files?

To enable and change FILESTREAM settings

1. On the Start menu, point to All Programs, point to Microsoft SQL Server 2008 R2, point to Configuration Tools, and then click SQL Server Configuration Manager.

2. In the list of services, right-click SQL Server Services, and then click Open.

3. In the SQL Server Configuration Manager snap-in, locate the instance of SQL Server on which you want to enable FILESTREAM.

4. Right-click the instance, and then click Properties.

5. In the SQL Server Properties dialog box, click the FILESTREAM tab.

6. Select the Enable FILESTREAM for Transact-SQL access check box.

SQL Server FILESTREAM7. If you want to read and write FILESTREAM data from Windows, click Enable

FILESTREAM for file I/O streaming access. Enter the name of the Windows share in the Windows Share Name box.

8. If remote clients must access the FILESTREAM data that is stored on this share, select Allow remote clients to have streaming access to FILESTREAM data.

9. Click Apply.

10. In SQL Server Management Studio, click New Query to display the Query Editor.

11. In Query Editor, enter the following Transact-SQL code:

EXEC sp_configure filestream_access_level, 2 RECONFIGURE

12. Click Execute.

13. Restart the SQL Server service.

An Introduction to SQL Server FILESTREAM (Simple Talk)

https://www.simple-talk.com/sql/learn-sql-server/an-introduction-to-sql-server-filestream/

SQL Server FILESTREAM1. At the top of the Object Explorer, right-click on your SQL Server, select

Properties

http://technet.microsoft.com/en-us/library/bb933993%28SQL.100%29.aspx SQL BOOKS ONLINE

http://technet.microsoft.com/en-us/library/bb933993(SQL.100).aspx

SQL Server FILESTREAM2. On the left panel click on the Advanced tab, then click on the drop down list

next to Filestream Access Level and select Full access enabled option, then OK

SQL Server FILESTREAM1. Create a new database, type a database name2. In the left-hand column, click Filegroups3. Under the Filestream grid (the lower window), click the lowermost Add button4. Add a name, check Default checkbox5. Select General from left-hand column, add new database file, select Filestream Data from

File Type, the Filegroup name with auto-fill using filegroup you just createdDO NOT PRESS OK YET, but see instructions on next slide

SQL Server FILESTREAM6. Scroll over and set the Path to the drive/directory where the filestream files will be saved,

then OK

http://sqlbeyond.blogspot.com/2011/08/how-to-use-filestream.html

Scroll over and set the Path to the drive/directory where the filestream files will be saved, then OK

http://sqlbeyond.blogspot.com/2011/08/how-to-use-filestream.html

SQL Server FILESTREAM7. In SQL Server 2008 and 2008 R2, SSMS does not support FILESTREAM attribute in CREATE

TABLE designer. That means you cannot create a table that has FILESTREAM column by using SSMS. So TSQL is used to create FILESTREAM enabled table.

CREATE TABLE FSTable ( -- ROWGUID column is required [RowId] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE, [Id] int NOT NULL, -- This is FILESTREAM column [Data] VARBINARY(MAX) FILESTREAM NULL )

SQL Server FILESTREAM8. FileStream table can be used or handled just like other table. This means we

can treat FileStream column as normal varbinary column. One can select, insert, update, delete the same way as other table.

http://weblogs.asp.net/aghausman/archive/2009/03/16/saving-and-retrieving-file-using-filestream-sql-server-2008.aspx

For Information on how to save and retrieve files to and from a filestream directory, see:

http://www.mssqltips.com/sqlservertip/1489/using-filestream-to-store-blobs-in-the-ntfs-file-system-in-sql-server-2008/



