Implementation of Database Object Schemas

A schema is a distinct namespace to facilitate the separation, management, and ownership of database objects. It removed the tight coupling of database objects and owners to improve the security administration of database objects.

Schema can be considered as a container that can hold many database objects. Database objects are created under a specific schema. If you don’t define any schema while creating a table then object will be created under default schema “dbo” which has database owner privilege.

Example of schema: For a Sales database, we can create following schemas:

HR – all the human resource related tables can be created under this schema.
Product – Product related tables an be create under this schema
Sales – Sales transactions tables can be created under this schema.

Advantages of using schemas:

·         Protecting database objects from being altered by users without the knowledge of the database owner.

·         Preventing database base objects, independent software vendor (ISV) databases in particular, from ad hoc or incorrect user access leading to poor application performance

·         Bringing related groups of objects (logical entities) together within one physical database to reduce physical database administrative overhead

 

Note: A schema cannot be dropped if it contains any objects. If a DROP SCHEMA statement is executed while it contains objects, the drop operation fails.

Creating schema using SQL Server Management Studio:

1.       In Object Explorer, expand the Databases folder.

2.       Expand the database in which to create the new database schema.

3.       Right-click the Security folder, point to New, and select Schema.

4.       In the Schema - New dialog box, on the General page, enter a name for the new schema in the Schema name box.

5.       In the Schema owner box, enter the name of a database user or role to own the schema. Alternately, click Search to open the Search Roles and Users dialog box.

6.       Click OK.

 

 

Creating schema using T-SQL

USE [DatabaseName]

GO

 

CREATE SCHEMA [SchemaName] AUTHORIZATION [OwnerName]

GO

 

USE [Test]

GO

 

CREATE SCHEMA [HR] AUTHORIZATION [dbo]

GO

 

System Databases in SQL Server

 When you install SQL Server, you will start with four system databases:

·         master

·         model

·         msdb

·         tempdb

All the above databases are essential to run your server properly.

 

The master database

Every SQL Server, regardless of the version, has master database and hold special set of tables called system tables. These system tables keeps track of the system as whole. For instance, when you create a new database then an entry goes to sys.databases or sysdatabases table.

SELECT * FROM sysdatabases

All the extended and system stored procedures are stored in this database regardless of which database they are intened to use with.

Note: everything that describes  your server is stored in master database so this is very critical database nd hence can not be deleted.

The model database

This is a database on which a copy can be based. Model database forms a template for any new database that we create.So you can modify model database if you want to change what standards, newly created database look like. For instance, you can add some set of tables, functions anmd procedures that need to be part of every newly created database. Also you can include some users that would be cloned into every new database.

Note: this database serves as a template for any other database, it is a required database and can not be deleted.

The msdb database

MSDB database stores all the tasks and processes related to SQL Agent. For instance, if you schedule backups to run on a databases nightly  then there will be an enrty in msdb database.

The tempdb database

One of the key workin area for the server is tempdb. SQl Server needs to create interm temporary tables to solve complex or large queries and these interim tables are created in tempdb.

tempdb is different from any other database. Not only are the objects within it temporary, the database itself is temporary. This is the only database in the system that is completely rebuilt from scratch everytime you start your SQL server.

Column Data-types in SQL Server

Tables are database objects that contain all the data in a database. In tables, data is logically organized in a row-and-column format similar to a spreadsheet. Each row represents a unique record, and each column represents a field in the record. For example, a table that contains employee data for a company might contain a row for each employee and columns representing employee information such as employee number, name, address, job title, and home telephone number.

In this post I will describe SQL Server data-types along with their size (in bytes) and the domain values that can be taken by the data types.

Following data-types can be used for a column.

Data type	Size [bytes]	Description
Exact Numerics
bit	1	An integer data type that can take a value of 1, 0, or NULL. The string values TRUE and FALSE can be converted to bit values: TRUE is converted to 1 and FALSE is converted to 0.
tinyint	1	Integer data type that can store 0 to 2^8-1 (255)
smallint	2	Integer data type that can store -2^15 (-32,768) to 2^15-1 (32,767)
int	4	Integer data type that can store -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)
bigint	8	Integer data type that can store -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)
decimal	5-17	Numeric data types that have fixed precision and scale.
numeric	5-17	Numeric data types that have fixed precision and scale.
smallmoney	4	Currency that can store - 214,748.3648 to 214,748.3647
money	8	Currency that can store -922,337,203,685,477.5808 to 922,337,203,685,477.5807
Approxmate Numerics
float	4-8	Floating point data is approximate; therefore, not all values in the data type range can be represented exactly. Float cans store - 1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308
real	4	Real can store - 3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38.
Date and Time
date	3	stores DATE in 'YYYY-MM-DD'. Range 0001-01-01 through 9999-12-31
smalldatetime	4	stores DATETIME like 'YYYY-MM-DD hh:mm:ss'. Date range 1900-01-01 00:00:00 through 2079-06-06 23:59:59
datetime	8	stores DATETIME like 'YYYY-MM-DD hh:mm:ss'. Date range 1753-01-01 00:00:00 through 9999-12-31 23:59:59
time	5	Stores only time like 'hh:mm:ss.nnnnnnn'. Range 00:00:00.0000000 through 23:59:59.9999999
Character Strings
char	n	CHAR(n). Fixed-length, non-Unicode string data. n defines the string length and must be a value from 1 through 8000.
varchar	n	VARCHAR(n | max). Variable-length, non-Unicode string data. n defines the string length and can be a value from 1 through 8000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB)
text		Variable-length non-Unicode data in the code page of the server and with a maximum string length of 2^31-1 (2,147,483,647). Storage size, in bytes is the string length entered.
Unicode Character Strings
nchar	2n	CHAR(n). Fixed-length, Unicode string data. n defines the string length and must be a value from 1 through 4000.
nvarchar	2n	VARCHAR(n | max). Variable-length, Unicode string data. n defines the string length and can be a value from 1 through 4000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB)
ntext		Variable-length Unicode data with a maximum string length of 2^30 - 1 (1,073,741,823). Storage size, in bytes, is two times the string length entered
Unicode Character Strings
binary	n	Fixed-length binary data with a length of n bytes, where n is a value from 1 through 8000
image		Variable-length binary data from 0 through 2^31-1 (2,147,483,647) bytes.

How to CREATE a table using T-SQL command

Tables are database objects that contain all the data in a database. In tables, data is logically organized in a row-and-column format similar to a spreadsheet. Each row represents a unique record, and each column represents a field in the record. For example, a table that contains employee data for a company might contain a row for each employee and columns representing employee information such as employee number, name, address, job title, and home telephone number.

Tables can be created through SQL Server Management Studio and T-SQL command.

CREATE TABLE command is used to create a table in the database. For instance, following command can be used to create MyTable:

CREATE TABLE dbo.MyTable

(

     ID     int

    ,Name   varchar(30)

    ,DoB    date

    ,gender char(1)

)

If you execute the above command twice, you will get following error:

Msg 2714, Level 16, State 6, Line 1

There is already an object named 'MyTable' in the database.

You need to check the object's existence before creating it to avoid above error as shown below:

IF OBJECT_ID('dbo.MyTable') IS NOT NULL DROP TABLE dbo.MyTable
DROP TABLE dbo.MyTable

GO 

CREATE TABLE dbo.MyTable

(

     ID     int

    ,Name   varchar(30)

    ,DoB    date

    ,gender char(1)

)

You will see following message after executing above query:

Command(s) completed successfully.

Note: This is always a good idea to add some logging information while creating and dropping any object or executing any T-SQL code for that matter. Logging always helps in troubleshooting.

IF OBJECT_ID('dbo.MyTable') IS NOT NULL DROP TABLE dbo.MyTable

BEGIN

    PRINT 'Dropping table dbo.MyTable...'

    DROP TABLE dbo.MyTable

END

GO

PRINT 'Creating table dbo.MyTable...'

CREATE TABLE dbo.MyTable

(

     ID     int

    ,Name   varchar(30)

    ,DoB    date

    ,gender char(1)

)

Now you will see following messages after executing above code:

Dropping table MyTable...

Creating table MyTable...