SQL Server ANSI Information Schema Views

SQL Server ANSI Information Schema Views

Based on the system tables, a set of ANSI views are defined in each database and inherited from the model database; they extract a limited set of information from the database system tables.

Each view has the owner INFORMATION_SCHEMA, and the owner must be referenced to identify the view. For example, to see a list of all the tables in a database, you query the TABLES view with :

SELECT * FROM INFORMATION_SCHEMA.TABLES

The advantage of the ANSI view is that it returns a table that you can manipulate; the disadvantage is that the owner name must be referenced, make the typing rather tedious. Also, the ANSI information schema views do not return as extensive a set of information as the built-in system stored procedures, such as SP_HELP or SP_HELPCONSTRAINT.

Written by,

Kasim Wirama, MCSD.NET, MCDBA

Database Administrator

Share this post: | | | |

System Tables in SQL Server

System Tables in SQL Server

According to the relational model, all data about a database’s tables should be kept in system tables. Every database has a set of catalog tables, which describe and govern all the data tables; and some databases, such as master and msdb, have data tables that act as system tables for the entire server. You can see each database’s system tables and the system databases by making sure that you have the option to see them checked.

System catalog is the set of tables in the master and msdb databases. The tables in master keep track of the server-wide data. For example, the master database has a sysdatabases table to tract the databases on the server, and a syslogins table to keep track of the server’s logins. You can query these tables directly or rely on the system stored procedures.

Each database has a catalog that consists of a set of system tables as well. All the database’s objects are listed in sysobjects, for example, while all the users are in sysusers. As with the system catalog, you can also query these tables directly or rely on the system stored procedures. But remember not to attempt to update the system tables directly unless it is absolutely necessary. A slight error might make the database suspect and unusable. In any case, some of the system tables are actually not persistent, and cannot be updated.

Written by,

Kasim Wirama, MCSD.NET, MCDBA

Database Administrator

Share this post: | | | |

More View Usage Variations in SQL Server

More View Usage Variations in SQL Server

This article will show you how to use view for any scenarios that need improvisation such as partitioned views, function in view, and encrypting views.

Partitioned View

You can make a union of a set of views, provided the views are union-compatible and then select from or update the view.

CREATE VIEW All_Authors AS

            SELECT * FROM CA_Authors

UNION

            SELECT * FROM NY_Authors

When you update a partitioned view, SQL Server is smart enough to know from you values to what table the new or changed row belongs.

Function in view

You can add a function to a view and have it return the result of the function by including the input in the view declaration. For example :

CREATE VIEW Vw_GetAverageSales (TitleType, AvgYTDSales)

AS

SELECT type, AVG(YTD_Sales)

FROM titles

GROUP BY type

Encrypting view

You can also create a view with encryption, just as you can with triggers and stored procedures. Just add the WITH ENCRYPTION option to the view declaration:

CREATE VIEW CA_Authors

WITH ENCRYPTION

AS

            SELECT au_lname, au_fname FROM Authors

            WHERE state = ‘CA’

The purpose of encryption is to store the view definition in an encoded format. This can be useful if you are delivering the database as a product and the view definition contains proprietary information.

Written by,

Kasim Wirama, MCSD.NET, MCDBA

Database Administrator

Share this post: | | | |

Using View in SQL Server

Using View in SQL Server

Views are virtual tables. They are really queries, but you can refer to them as though they were tables. For example, the following code creates a view :

CREATE VIEW CA_Authors AS

            SELECT * FROM Authors

            WHERE state = ‘CA’

You can then select directly from CA_Authors:

SELECT * FROM CA_Authors

And see only the California authors. Views can be used to limit the number of rows or columns that users can see. A DBA can grant rights to a view and not to be the original table, thereby hiding sensitive information from the user.

You can change a view in Transact-SQL using ALTER VIEW command, as in the following code :

ALTER VIEW CA_Authors AS

            SELECT au_lname, au_fname FROM Authors

            WHERE state = ‘CA’

This has the advantage that permissions do not need to be reset for the view.

You should know that if you drop and re-create a view, it gets a new ID in the sysobjects table and requires you to reset its permissions. If you use ALTER VIEW, the ID stays the same and you don’t need to reset permissions.

Table can be updated through view, provided view is against one table and does not contain any computed columns. Once those assumptions are in place, you can execute the Transact-SQL INSERT, UPDATE, and DELETE commands against the view. If you try to update a view that does not allow updates, the error messages may be cryptic, especially on the client. Be extra careful when trying to update views. So if you have a view that shows only a subset of a table’s data and you update the column on which the view depends, you may get surprising results. For example, if you update a view that contains only authors with state=’CA’ and you change a state value to ‘NY’, the resulting row will disappear. That is because it no longer fits the view.

To prevent updating view definition columns, compile the view using the WITH CHECK OPTION switch:

CREATE VIEW CA_Authors AS

            SELECT au_lname, au_fname FROM Authors

            WHERE state = ‘CA’

WITH CHECK OPTION

Written by,

Kasim Wirama, MCSD.NET, MCDBA

Database Administrator

Share this post: | | | |

Table Operations in SQL Server

Table Operations in SQL Server

This article, I want to show you some operations on table such as determining table size, copying, renaming and deleting a table, and changing table structure through the ALTER TABLE command.

Determining table size

You can determine the size that a table takes up with the sp_spaceused stored procedure. If you add a table name to the procedure, as in the example :

Exec sp_spaceused Authors

It will return result as below :

Name              rows                reserved                     data                index_size
-------------------            -------------------            -----------------------------            -------------------            --------------------

Authors                       23                    48 KB                         8 KB               40 KB

You learn that SQL Server has reserved 48 KB for the Authors table, which includes 8 KB of data and 40 KB for indexes.

Copying, renaming and deleting a table

When you copy, rename or delete a table, SQL Server takes care of references to the table in the database’s system tables.

You can use Transact-SQL to copy a table with the SELECT INTO command

SELECT * INTO Authors_copy from Authors;

This will extract all the rows from the Authors table and put the result into a new table called authors_copy.

Remember in mind that in order for the SELECT INTO command to work, the new table must not yet exist, and you must enable Select Into/Bulk Copy database option. You can set it either in database properties or in the stored procedure sp_dboption.

Next, we are going to script renaming table name. You can call the SQL Server system stored procedure sp_rename below :

Exec sp_rename Customer, Customer_copy.

Obviously, you can’t rename a table if here is already a table with the new name.

To drop a table, issue SQL DROP TABLE command :

DROP TABLE Customer_copy;

Remember when you drop table, be careful about dependencies. If you are trying to drop a parent table that has other tables referencing it with foreign key constraints, SQL Server will not allow drop to occur. You must drop the child tables first.

Changing a table’s structure through ALTER TABLE command

You can use the SQL ALTER TABLE command to add and drop columns to a table, and also add and drop constraints.

To add a new column called Address to our Customer table, you should submit :

ALTER TABLE Customer

            ADD Address varchar(50) NULL

With the ALTER TALBE command, you can add and drop columns and also modify their characteristics. Such as changing a data type

ALTER TABLE Customer

            ALTER COLUMN Address CHAR(20) NULL

Also, you ca drop the column :

ALTER TABLE Customer

            DROP COLUMN Address

Any columns you add using ALTER TABLE will be placed at the end of a table, so you cannot insert them in any position you want. However, using GUI, you can place the columns in the order you desire.

Written by,

Kasim Wirama, MCSD.NET, MCDBA

Database Administrator

Share this post: | | | |

SQL SERVER Relational and Storage Engine

SQL SERVER Relational and Storage Engine

There are a lot of factor to keep in mind when building databases in SQL Server. The context for understanding those factors begins with a fundamental fact about SQL Server architecture, namely that at its core, SQL Server consists of two independent subsystems, the relational engine (or query engine) and the storage engine.

The relational engine processes queries, whereas the storage engine stores and retrieves data. The relational engine communicates with the storage engine via the low level OLE DB.

The relational engine processes two fundamental types of queries: data definition (DDL) and data manipulation (DML). You use DDL commands to define the databases, tables, indexes, and constraints that make up your application’s data source. For example, CREATE TABLE, CREATE INDEX, and so on are DDL commands. You use DML commands to manipulate data and populate tables with data. SELECT, INSERT, UPDATE and DELETE are DML command.

Whenever you communicate with SQL Server storage engine, you use the Transact SQL  language, the dialect of SQL that belongs to SQL Server. Let’s take a closer look at the relationship of the relational engine. Transact SQL and the SQL language.

Relational database system, widely used nowadays, is first formulated by E.F. Codd, an IBM mathematician. He based his ideas on the application of set theory to data structure. A few prototype relational database systems are introduced in the early 1980s. since that time, the relational DBMS has become the dominant method for storing database data.

The relational model of database storage is a well developed theory of how data should be stored and manipulated. If you store data in the relational format and if you follow the relational model’s rules, then the results of performing relational operations on the data can be predicted with mathematical certainty.

Written by,

Kasim Wirama, MCSD.NET, MCDBA.

Database Administrator

Share this post: | | | |

Advanced Data Type Value in SQL Server

Advanced Data Type Value in SQL Server

I will show you advanced data type in SQL Server. They are: default values, null, identity property, uniqueidentifier, Unicode character data, and computed columns.

You can also specify whether you want a column to default to a value when you don’t specify the value in an Insert command. For example, in the Customers table, you can specify a default value of blank for the name using the following code :

CREATE TABLE Customer

(

Cust_id            INT,

Cust_name       CHAR(30) DEFAULT ‘New Customer’)

)

Default values are important when considering NULLS that will be discussed below.

You can specify NULL value for a column when creating a table, which means there is no value at all in the column for that row. The NULL is not technically a value at all, just a placeholder indicating that no value is present. You can use NULL to indicate that a value is missing or is unknown.

You can make a column allow a NULL by using the NULL qualifier after the name of the column, for example :

CREATE TABLE Customer

(

Cust_id            INT      NOT NULL,

Cust_name       CHAR(30)       NULL

)

You can use a NOT NULL along with a default value, as in the following code :

CREATE TABLE Customer

(

Cust_id            INT      NOT NULL,

Cust_name       CHAR(30)       NOT NULL     DEFAULT ‘New Customer’

)

In this case, the cust_name column will default to ‘New Customer’ string but will never be NULL. You can detect whether a particular value has the NULL marker with the IS NULL construct.

You should know that, in database world, allowing NULL in a table is controversial. The meaning of NULL can be ambiguous (not entered, unknown, just missing, not relevant, and so on). Many developers prefer to disallow NULL entirely in all columns and add default value instead.

Another advanced data type is identity property. You can have an automatically incrementing column in a table using the identity keyword, as in the following code :

CREATE TABLE Customer

(

Cust_id            INT IDENTITY(1,1) NOT NULL,

Cust_name       CHAR(30) NOT NULL DEFAULT ‘’

)

You can only have one identity column per table, and it must not allow NULL. The identity property works with all numeric (integer, small integer, tiny integer, decimal, numeric and float) data types. The parentheses after the IDENTITY keyword are optional and indicate the seed value (the initial value of the incrementing number), and then the increment amount. Both must be in the integer value.

Usually, you cannot insert values directly into a column when it has the identity property, unless you first override the property by using the SET IDENTITY_INSERT <tablename> ON command. In order to use SET IDENTITY_INSERT, you must be the table owner or aliased to the table owner.

Other commonly used data type is uniqueidentifier data typ. It is alternative to the IDENTITY property. It produces a column that stores a unique GUID (globally unique identifier) in the column, sometimes called the ROWGUIDCOL property. The data in the column stores 32 hexadecimal digits, whtich makes it a 16-byte-wide column (four times the width of an integer). When you display it, SQL Server insert four dashes. For example, you could create the table Customer with a cust_d that is uniqueidentifier as follows:

CREATE TABLE Customer

(

Cust_id            uniqueidentifier,

Cust_name       CHAR(30)

)

You can use the NEWID() function to get SQL Server to populate the row with a GUID value :

INSERT INTO Customer VALUES (NEWID(), ‘New Customer’)

When you display the result, you will the the new ID :

Cust_id                                                                        Cust_name

------------------------------------------------------------      --------------------------------------------

1E3602A4-F87F-11D1-84C6-204C4F4F5020          New Customer

SQL Server will automatically change the result of the GUID to a string for display purpose.

Unlike the IDENTITY property, the uniqueidentifier data type does allow NULLs. However, if you want to use it as a primary key, you must disallow NULL when you create the table.

There is another datatype, ie. NCHAR, NVARCHAR. Unicode data is stored in two bytes per character, instead of the usual one byte per character. With two bytes available per character, every language character that Unicode supports can be assigned to unique value, thereby solving the problem of having multiple character sets that do not translate all characters. You can specify that a string can be interpreted as Unicode by prefixing it with the uppercase N. The following example stores a Unicode value and checks its length.

CREATE TABLE Customer

(

Cust_id            INT,

Cust_name       NCHAR(30)

);

INSERT INTO Customer VALUES(1,’New Customer’);

SELECT DATALENGTH(Cust_name) FROM Customer;

In the last statement, the DATALENGTH() function returns the length of the cust_name value, and it shows 60 bytes, two bytes per character.

Last value type is computed column. You can define columns to a table that actually just compute the values from other columns in the same table. These are called computed columns. SQL Server does not actually store the computation but calculates it at ru time, much as a SELECT statement would. In the following table, for example, the penalty column computes 0.1 times the amt_due column as follows:

CREATE TABLE Customer

(

Cust_id            INT,

Cust_name       CHAR(30),

Amt_due          Money,

Penalty AS       Amt_due * 0.1

)

Go

INSERT INTO Customer VALUES(1, ’New Customer’, 10.00);

So now when you look at the table, you will see :

1                      New Customer 10.0000           1.00000

Written by,

Kasim Wirama, MCSD.NET, MCDBA

Database Administrator

Share this post: | | | |

Data Types in SQL Server

Data Types in SQL Server

Topic for this article seems very simple. But worth it to know because improper data types will impact to suboptimal database performance.

Every database columns have data types that limit the kind of data that can be stored in them. Here is the list