Database SCHEMA

From SQL Server 2005 the DATABASE SCHEMA is introduced by Microsoft. Before understanding the database schema we must review the SQL Server 2000 to understand it properly.

Problem with earlier version of SQL Server (Before SQL Server 2005)

In SQL Server 2000 the schema is owned by, and was inextricably linked to that means a user creates a table in the database, that user cannot be deleted without deleting the table or first transferring it to another user.

How Microsoft helps us to solving this problem

To solve this problem Microsoft is introducing database schema from SQL Server 2005.

A database schema is a way to logically group objects such as tables, views, stored procedures etc. Think of a schema as a container of objects. We can assign user login permissions to a single schema so that the user can only access the objects they are authorized to access. Schemas can be created and altered in a database, and users can be granted access to a schema. A schema can be owned by any user, and schema ownership is transferable.

Default Schema

Users can be defined with a default schema. The default schema is the first schema that is searched when it resolves the names of objects it references.

The default schema for a user can be defined by using the DEFAULT_SCHEMA option of CREATE USER or ALTER USER. If no default schema is defined for a user account, SQL Server will assume "dbo" is the default schema. It is important note that if the user is authenticated by SQL Server as a member of a group in the Windows operating system, no default schema will be associated with the user. If the user creates an object, a new schema will be created and named the same as the user, and the object will be associated with that user schema.

Some properties of database schema

     1.     Ownership of schema and schema-scoped securable is transferable.

2. Objects can be moved between schemas.
3. A single schema can contain objects owned by multiple database users.
4. Multiple database users can share a single default schema.
5. Permissions on schemas and schema-contained securable can be managed with greater precision than in earlier releases.
6. A schema can be owned by any database principal. This includes roles and application roles.
7. A database user can be dropped without dropping objects in a corresponding schema.

To get the Information of Schema for database objects

SELECT  sys.objects.name [Object Name],

        sys.schemas.name AS [Schema Name]

FROM    sys.objects

        INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id

Object Name                Schema Name

sysrscols                      sys

sysrowsets                   sys

Tbl_1                            dbo

Tbl_2                            dbo

The Example of Database Schema

USE master

GO

CREATE DATABASE my_db

GO

USE my_db

GO

-- Created Schema my_Employee

CREATE SCHEMA my_Employee

GO

-- Created table named in EmpInfo on the my_Employee schema –

CREATE TABLE my_Employee.EmpInfo

    (

      EmpNo int Primary Key identity(1,1),

      EmpName varchar(20)

    )

-- Data insertion

INSERT INTO my_Employee.Empinfo

Values ('Joydeep'),('Tuhin'),('Sangram')

-- Data Selection

SELECT *

FROM   my_Employee.Empinfo

-- Created another schema HR_Dept

CREATE SCHEMA HR_Dept

-- Transfer Objects between Schemas

ALTER SCHEMA HR_Dept

TRANSFER my_Employee.Empinfo

-- Assigning Permission to Schema

GRANT SELECT ON SCHEMA::HR_Dept TO Joydeep

  

Hope you like it.

Posted by: MR. JOYDEEP DAS

SQL 2008 FILESTREAM storage

SQL Server never is good to storing unstructured data like video, graphics file, MS-Office file etc.  Before SQL server 2008, we have two choices to manage such kind of data mentioned bellow. 

1.    By using VARBINARY(MAX) columns inside the database.

 

2.    Store the data outside the database as part of file system, and include the pointers inside columns that point the file location.

 

Problem that we faced in earlier system

1.    The VERBINARY(MAX) has the 2 GB size limit and can dramatically increases the size of the data base.

 

2.    Storing file in the file system requires a unique naming system. Security is one of the problems and that's why we need NTFS. And required separate backup systems for database and file.

So what the solution

To help resolves those problem Microsoft SQL Server 2008 has introduced FILESTREAM storage. Which is an hybrid approach that combines the best features of previous VERBINARY(AMX) and storing in file system.

FILESTREAM storage is implemented in SQL Server 2008 by storing VARBINARY(MAX) binary large objects (BLOBs) outside of the database and in the NTFS file system. While this sounds very similar to the older method of storing unstructured data in the file system and pointing to it from a column, it is much more sophisticated. Instead of a simple link from a column to an outside file, the SQL Server Database Engine has been integrated with the NTFS file system for optimum performance and ease of administration.

For example, FILESTREAM data uses the Windows OS system cache for caching data instead of the SQL Server buffer pool. This allows SQL Server to do what it does best: manage structured data; and allows the Windows OS to do what is does best: manage large files. In addition, SQL Server handles all of the links between database columns and the files.

The Additional benefits of FILESTREAM

1.    T-SQL can be used in FILESTREAM (SELECT, INSERT, UPDATE, DELETE)

2.    FILESTREAM data is backed up and restore as part of the database file. The options are there that we can backup database without FILESTREAM data.

 

3.    The size of the stored data is only limited by the available space of the file system. Standard VARBINARY(MAX) data is limited to 2 GB.

In what condition we use the FILESTREAM storage

1.    When the BLOB file sizes average 1MB or higher.

 

2.    When fast read access is important to your application.

 

3.    When applications are being built that use a middle layer for application logic.

 

4.    When encryption is not required, as it is not supported for FILESTREAM data.

Implementation of FILESTREAM storage

1.    Enabling the SQL Server instance to use FILESTREAM data

2.    Enabling a SQL Server database to use FILESTREAM data

3.    When creating FILESTREAM-enabled columns in a table, specifying the "VARBINARY(MAX) FILESTREAM" data type.

By default, FILESTREAM storage is not turned on after you install a new SQL Server 2008 instance. If you want to take advantage of it, you must enable it, which is a two step process.

STEP-1

This can be performed by SQL Server 2008 Configuration Manager or by using the sp_filestream_configure system stored procedure.

SQL Server 2008 Configuration Manager

Click on SQL Server Services in the left window, and then in the right window, right-click on the SQL Server instance you want to enable FILESTREAM storage on, choose Properties, and then click on the FILESTREAM tab and follow the instruction over there.

sp_filestream_configure

By SQL Server Management Studio (SSMS)

EXEC sp_configure filestream_access_level, 2

RECONFIGURE

FILESTREAM storage has now been enabled for the SQL Server instance.

STEP-2

The next step is to enable FILESTREAM storage for a particular database.

For existing data base we can use the ALTER DATABASE statements.

CREATE DATABASE FILESTREAM_Database

ON

PRIMARY ( NAME = Data1,

    FILENAME = 'C:\DATA\FILESTREAM_DB.mdf'),

FILEGROUP FileStreamGroup CONTAINS FILESTREAM( NAME = FILESTREAM_Data,

    FILENAME = 'C:\DATA\FILESTREAM_Data')

LOG ON  ( NAME = Log1,

    FILENAME = 'C:\DATA\FILESTREAM_DB.ldf')

GO

After the above code runs, and the database is created, a new sub-folder is created with the name of "FILESTREAM_Data". Inside this newly created folder named "FILESTREAM_Data" is a called "filestream.hdr" and an empty sub-folder called $FSLOG.  The "filestream.hdr" file is used to keep track of the FILESTREAM data.

STEP-3

At this point, our database is FILESTREAM-enabled, and you begin adding new tables that include the VARBINARY(MAX) data type

CREATE TABLE dbo.FILESTREAM_Table

      (    

         ID      UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE ,

         DATA    VARCHAR(100),

         Catalog VARBINARY(MAX) FILESTREAM

      )

Hope you like it.

Posted by: MR. JOYDEEP DAS