The Create Statements in SQL

The CREATE DATABASE Statement

The CREATE DATABASE statement is used to create a database.

SQL CREATE DATABASE Syntax

CREATE DATABASE database_name

CREATE DATABASE Example

Now we want to create a database called "Employee".

We use the following CREATE DATABASE statement:

CREATE DATABASE Employee

Database tables can be added with the CREATE TABLE statement.

The CREATE TABLE Statement

The CREATE TABLE statement is used to create a table in a database.

SQL CREATE TABLE Syntax

CREATE TABLE table_name

(

column_name1 data_type,

column_name2 data_type,

column_name3 data_type,

....

)

The data type specifies what type of data the column can hold. For a complete reference of all the data types available in MS Access, MySQL, and SQL Server, go to our complete Data Types reference.


CREATE TABLE Example

Now we want to create a table called " EmpDetails " that contains five columns: EmpId, FirstName, LastName, Address.

We use the following CREATE TABLE statement:

CREATE TABLE EmpDetails 

(

EmpId int,

FirstName varchar(200),

LastName varchar(200),

Address varchar(500)

)

The EmpId column is of type int and will hold a number. The LastName, FirstName, Address columns are of type varchar with a maximum length of 200,200,500 resp. characters.

Good Practices to Write Stored Procedures in SQL Server

1.    Use proper indentation for the statements in SQL Server. It will improve the readability.

2.    Write the proper comments between the logics. So the others can understand quickly.

3.    Write all the SQL Server keywords in the CAPS letter. For example SELECT, FROM, ALTER, DELETE and CREATE

4.    Write the stored procedure name with full qualified names.
CREATE PROCEDURE [dbo].EmployeeDetailsInsert

5.    Always try to declare the DECLARATION and initialization at the beginning of the stored procedure.

6.    It is not recommended to use more variables in the procedure. It will occupy more space in the memory.
 

7.    Do not write the stored procedure name beginning with sp_. It is reserved for the system stored procedures in SQL Server and when the request comes to the SQL Server engine, it will be considerd to be a system stored procedure and looks for it in the master database. After it understands that this is a user defined stored procedure, it requires a bit more response time. So name the procedure name with another prefix such an fsp_.

8.    Set the SET NOCOUNT ON option in the beginning of the stored procedure to avoid the unnecessary message like number of rows affected by the SQL Server.

9.    Try to avoid the temp table in the stored procedure. Stored procedures usually use a cached execution plan to increase the performance. When you use the temp table it will do the compilation every time.

10.  Do not use the select all columns (SELECT *) option; use only specific columns to query the result.

11.  Try to avoid the cursor in the stored procedure. It will consume more memories. It will degrade the performance of the stored procedure. Try to use the table variable and WHILE loop statement to iterate the query result set.

12.  Set the default value to the parameter and always set the size of the variable to be equivalent to or more than the table field column length. For example Name (10) in the table, but if you give Name(25) in the procedure then you will get the run time error time "string truncated  error".

13.  Use the Try catch statement properly in the stored procedure to handle the errors in the runtime.

14.  Move the complex query into views.

15.  If you want to return the single column result then prefer to use the output statement to return the result to the calling program rather than table result.

16.  Avoid the sub-queries and use the INNER JOIN. Try to avoid the filtering condition in the where clause and it can be written in the joining time itself. When joins the table itself it will be filtered and it will filter again from the joined result table.

17.  Use the SELECT TOP 1 in the exists condition checking.

18.  Do not do like this:

SELECT @name=name FROM empdetails WHERE name like '%Sun%'
This will give the run time error when returns more than one result.

SELECT TOP 1 @name=name FROM empdetails WHERE name like '%sun%'

It is always recommended to use the TOP 1 in that case. The result may differ from what is  expected.

19.  Avoid the nested IF statements and use the CASE statement. It will execute the matching part immediately.

20.  Dynamic Queries - Try to minimize the usage of dynamic queries. If you are using a dynamic query like:

SELECT * FROM mydb.dbo.empdetails where empid = @id then there is no problem.

You can supply a value for the @eid parameter and there is no recompilation of the execution plan in the database cache. But if you are using a SQL query like SELECT * FROM EmpDetails where Empid = " + @id and supply a parameter.
 

21.  Use the ORDER BY and DISTINCT, TOP only when requires. The SQL Server engine will get the result first and it will do again the query execution for these operations.

22.  It is recommended to use a Table variable when the result set is small. It is always in the memory and when the limit exceeds it will be created as a table in the temp. But the temp table will be created on the temp database and that makes it slower.

23.  Use the proper indexing to the columns in the table. Do not create an index on the columns that are not used anywhere in the where clause. It will require an extra roundtrip to query the result.

SQL AUTO INCREMENT Field

Auto-increment permits a unique number to be generated when a new record is inserted into a table.

AUTO INCREMENT a Field

Very frequently we would like the value of the primary key field to be created automatically every time a new record is inserted.

We would like to create an auto-increment field in a table.

Syntax for SQL Server

The following SQL statement defines the "EmpID" column to be an auto-increment primary key field in the "Employee" table:

CREATE TABLE Employee

(

EmpID Bigint IDENTITY(1,1) PRIMARY KEY,

LastName Nvarchar (200) NOT NULL,

FirstName Nvarchar (200),

Address Nvarchar (500),

CityCode Nvarchar(3)

)

The SQL Server uses the IDENTITY keyword to perform an auto-increment feature.

In the example above, the starting value for IDENTITY is 1, and it will increment by 1 for each new record.

Tip: To specify that the "EmpID" column should start at value 200 and increment by 1, change it to IDENTITY(200,1).

To insert a new record into the " Employee " table, we will NOT have to specify a value for the "EmpID" column a unique value will be added automatically.

SQL Constraints

SQL Constraints

SQL constraints are used to identify rules for the data in a table.

 If there is any violation between the constraint and the data action, the action is terminated by the constraint.

Constraints can be specified when the table is created (inside the CREATE TABLE statement) or after the table is created (inside the ALTER TABLE statement).

 In SQL, we have the following constraints:

• NOT NULL - Indicates that a column cannot store NULL value
• UNIQUE - Ensures that each row for a column must have a unique value
• PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Ensures that a column (or combination of two or more columns) have a unique identity which helps to find a particular record in a table more easily and quickly
• FOREIGN KEY - Ensure the referential integrity of the data in one table to match values in another table
• CHECK - Ensures that the value in a column meets a specific condition
• DEFAULT - Specifies a default value for a column

SPARSE COLUMN-SQL SERVER 2008

SPARSE COLUMN-SQL SERVER 2008

SQL SERVER 2008's new feature SPARSE COLUMN. Sparse columns are ordinary columns that have an optimized storage for null values. Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve nonnull values. Consider using sparse columns when the space saved is at least 20 percent to 40 percent. Sparse columns and column sets are defined by using the CREATE TABLE or ALTER TABLE statements.

All SPARSE columns are stored as one XML column in database. Let us see some of the advantage and disadvantage of SPARSE column.

Advantages of SPARSE column are:

• INSERT, UPDATE, and DELETE statements can reference the sparse columns by name. SPARSE column can work as one XML column as well.
• SPARSE column can take advantage of filtered Indexes, where data are filled in the row.
• SPARSE column saves lots of database space when there are zero or null values in database.
• A non-null value stored in a GUID (UniqueIdentifier) column is 16 bytes + 4 bytes = 20 bytes. So if only 50% of these are NULL, that's still a net savings.
• Storing a null in a sparse column takes up no space at all.
• To any external application the column will behave the same
• Sparse columns work really well with filtered indexes as you will only want to create an index to deal with the non-empty attributes in the column.
• You can create a column set over the sparse columns that returns an xml clip of all of the non-null data from columns covered by the set. The column set behaves like a column itself. Note: you can only have one column set per table.
• Change Data Capture and Transactional replication both work, but not the column sets feature.

Disadvantages of SPARSE column are:

• SPARSE column does not have IDENTITY or ROWGUIDCOL property.
• SPARSE column can not be applied on text, ntext, image, timestamp, geometry, geography or user defined datatypes.
• SPARSE column can not have default value or rule or computed column.
• Clustered index or a unique primary key index can not be applied SPARSE column. SPARSE column can not be part of clustered index key.
• Table containing SPARSE column can have maximum size of 8018 bytes instead of regular 8060 bytes. A table operation which involves SPARSE column takes performance hit over regular column.
• If a sparse column has data in it it will take 4 more bytes than a normal column e.g. even a bit (0.125 bytes normally) is 4.125 bytes and unique identifier rises form 16 bytes to 20 bytes.
• Not all data type can be sparse: text, ntext, image, timestamp, user-defined data type, geometry, or geography or varbinray (max) with the FILESTREAM attribute cannot be sparse. (Changed17/5/2009 thanks Alex for spotting the typo)
• computed columns can't be sparse (although sparse columns can take part in a calculation in another computed column)
• You can't apply rules or have default values.
• Sparse columns cannot form part of a clustered index. If you need to do that use a computed column based on the sparse column and create the clustered index on that (which sort of defeats the object).
• Merge replication doesn't work.
• Data compression doesn't work.
• Access (read and write) to sparse columns is more expensive, but I haven't been able to find any exact figures on this.

·          

Syntax to create sparsed columns

CREATE TABLE Sparsed(ID INT IDENTITY(1,1),

FirstCol INT SPARSE,

SecondCol VARCHAR(100) SPARSE,

ThirdCol SmallDateTime SPARSE)

GO