Constraints

Constraints let you define the way the Database Engine automatically enforces the integrity of a database. Constraints define rules regarding the values allowed in columns and are the standard mechanism for enforcing integrity.

1.       NOT NULL specifies that the column does not accept NULL values.

2.       CHECK constraints enforce domain integrity by limiting the values that can be put in a column.

3.       UNIQUE constraints enforce the uniqueness of the values in a set of columns.

4.       PRIMARY KEY constraints identify the column or set of columns that have values that uniquely identify         a row in a table.

5.       FOREIGN KEY constraints identify and enforce the relationships between tables.

 The ON DELETE clause controls what actions are taken when you try to delete a row to which existing foreign keys point. The ON DELETE clause has the following options:

·         NO ACTION specifies that the deletion fails with an error.

·         CASCADE specifies that all the rows with foreign keys pointing to the deleted row are also deleted.

·         SET NULL specifies that all rows with foreign keys pointing to the deleted row are set to NULL.

·         SET DEFAULT specifies that all rows with foreign keys pointing to the deleted row are set to their default value.

Schema

A schema is a collection of database objects that are owned by a single principal and form a single namespace. Schemas own objects and principals own schemas. A schema can be owned by either a primary or secondary principal, with the term “principal” meaning any SQL Server entity that can access securable objects.

Users can now have a default schema assigned using the DEFAULT_SCHEMA option of CREATE USER and ALTER USER commands. If no default schema is supplied for a user then DBO will be used as the default schema.

            The primary purpose of SQL schema was -is- to facilitate security management: define who [which principals] can access what [which database objects]. This was made particularly easier starting with SQL 2005 when the schema stopped being directly tied to the owner. 

Another use of schema is to serve as a namespace that is preventing name clashes between objects from different schemas.

       The original use of this was to allow multiple [interactive, i.e. ad-hoc like] users of a given database to create their own tables or stored procedures (or other objects), without having to worry about the existence of similarly named objects possibly introduced by other users. The Namespace-like nature of schema can also be put to use in a planned database setting, i.e. one when a single architect designs the database structure in a way which provides distinct type of access, and indeed different behaviors, for distinct user groups.

Schemas provide the opportunity to simplify administration of security, backup and restore, and database management by allowing database objects, or entities, to be logically grouped together. This is especially advantageous in situations where those objects are often utilized as a unit by applications. For example, a hotel-management system may be broken down into the following logical entities or modules: Rooms, Bar/Restaurant, and Kitchen Supplies. These entities can be stored as three separate physical databases. Using schemas however, they can be combined as three logical entities in one physical database. This reduces the administrative complexity of managing three separate databases.

CREATE LOGIN [User1] WITH PASSWORD=N'User1'

CREATE USER [User1] FOR LOGIN [User1] WITH DEFAULT_SCHEMA=[Pract]

CREATE ROLE [Pract_Role] AUTHORIZATION [dbo]

CREATE SCHEMA [Pract_Sch] AUTHORIZATION [User1]

GRANT DELETE,SELECT,INSERT,UPDATE,ALTER ON SCHEMA::[Pract_Sch] TO [Pract_Role]

EXEC sp_addrolemember N'Pract_Role', N'User1'

GO

GRANT CREATE TABLE TO [Pract_Role]

1.       We have created a login called User1

2.       Created a user with same name

3.       Created a role

4.       Created a schema under User1

5.       Granted permissions on schema to the role

6.       Added the role to the user

7.       Granted create table to the role

Here, we can observer that we have to grant permissions on table/schema to the role.

Isolation levels Demo

READ UNCOMMITTED

BEGIN TRAN

UPDATE  dbo.TestIsolationLevels

SET     EmpSalary = 25000

WHERE   EmpID = 2900

Now select the value that's being updated using the following (in a separate query window):

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SET NOCOUNT ON

GO

SELECT EmpID, EmpName, EmpSalary

FROM dbo.TestIsolationLevels

WHERE EmpID = 2900

READ COMMITTED (snapshot)

ALTER DATABASE [YourDB] SET READ_COMMITTED_SNAPSHOT ON

GO

BEGIN TRAN

UPDATE  dbo.TestIsolationLevels

SET     EmpSalary = 25000

WHERE   EmpID = 2900

Now in a separate query window:

SELECT  EmpID, EmpName, EmpSalary

FROM    dbo.TestIsolationLevels WITH (READCOMMITTEDLOCK)

WHERE   EmpID = 2900

The query will hang as it is waiting for the key lock on EmpID to be released. Allow the query to execute by issuing in your first window:

ROLLBACK;

READ COMMITTED

ALTER DATABASE [YourDB] SET READ_COMMITTED_SNAPSHOT ON

GO

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SET NOCOUNT ON

GO

BEGIN TRAN

UPDATE  dbo.TestIsolationLevels

SET     EmpSalary = 25000

WHERE   EmpID = 2900

Now in a separate query window:

SELECT  EmpID, EmpName, EmpSalary

FROM    dbo.TestIsolationLevels

WHERE   EmpID = 2900

The query will return the last consistent row of data. Note the empSalary column is 22000.00 despite the transaction being open and the update written (but uncommitted). This is correct, and the SELECT is reading from the previous row version, not the present state of the row. This is compliant with the C in ACID - consistency.

SNAPSHOT isolation

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

SET NOCOUNT ON

GO

BEGIN TRAN

UPDATE  dbo.TestIsolationLevels

SET     EmpSalary = 25000

WHERE   EmpID = 2900

Now in a separate query window:

SELECT  EmpID, EmpName, EmpSalary

FROM    dbo.TestIsolationLevels

WHERE   EmpID = 2900

You will note that, like READ COMMITTED, the correct snapshot of the data row is returned, yielding empSalary = 22000, which is consistent and correct. Rollback the transaction.

REPEATABLE READ

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

SET NOCOUNT ON

GO

BEGIN TRAN

SELECT  EmpID, EmpName, EmpSalary

FROM    dbo.TestIsolationLevels

WHERE   EmpID = 2900

WAITFOR DELAY '00:00:10'

SELECT  EmpID, EmpName, EmpSalary

FROM    dbo.TestIsolationLevels

WHERE   EmpID = 2900

COMMIT

Run the below while the above is executing:

BEGIN TRAN

UPDATE  dbo.TestIsolationLevels

SET     EmpSalary = 25000

WHERE   EmpID = 2900

COMMIT

Problem with this isolation level is, we can see phantom rows (newly inserted) within the transaction.

Serializable Isolation

SERIALIZABLE has all the features of READ COMMITTED, REPEATABLE READ but also ensures concurrent transactions are treated as if they had been run in serial. This means guaranteed repeatable reads, and no phantom rows. Be warned, however, that this (and to some extent, the previous two isolation levels) can cause large performance losses as concurrent transactions are effectively queued.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

SET NOCOUNT ON

GO

BEGIN TRAN

SELECT  EmpName

FROM    dbo.TestIsolationLevels

WAITFOR DELAY '00:00:10'

SELECT  EmpName

FROM    dbo.TestIsolationLevels

COMMIT

Run the below while the above is executing:

BEGIN TRAN

INSERT INTO dbo.TestIsolationLevels VALUES (3427, 'Phantom Employee 1', 30000)

COMMIT

DBCC Commands

1.       DBCC INPUTBUFFER       Displays the last statement sent from a client to an instance of Microsoft SQL Server.

DBCC INPUTBUFFER ( session_id [ , request_id ])

DBCC INPUTBUFFER (52);

2.       DBCC OPENTRAN             helps to identify active transactions that may be preventing log truncation. DBCC OPENTRAN displays information about the oldest active transaction and the oldest distributed and nondistributed replicated transactions, if any, within the transaction log of the specified database.

DBCC OPENTRAN

3.       DBCC PROCCACHE           Displays information in a table format about the procedure cache.

4.       DBCC SHOW_STATISTICS              displays current query optimization statistics for a table or indexed view. The query optimizer uses statistics to estimate the cardinality or number of rows in the query result, which enables the query optimizer to create a high quality query plan.

DBCC SHOW_STATISTICS ( table_or_indexed_view_name , target )

DBCC SHOW_STATISTICS ("Person.Address", AK_Address_rowguid);

5.       DBCC CHECKCONSTRAINTS          Checks the integrity of a specified constraint or all constraints on a specified table in the current database.

DBCC CHECKCONSTRAINTS(Table1)

6.       DBCC CHECKDB                 Checks the logical and physical integrity of all the objects in the specified database. Validates the contents of every indexed view in the database.

7.       DBCC CHECKIDENT          Checks the current identity value for the specified table in SQL Server 2012 and, if it is needed, changes the identity value. You can also use DBCC CHECKIDENT to manually set a new current identity value for the identity column.

DBCC CHECKIDENT ('Person.AddressType')

8.       DBCC CHECKTABLE          Checks the integrity of all the pages and structures that make up the table or indexed view.

DBCC CHECKTABLE ("HumanResources.Employee")

9.       DBCC CLEANTABLE          Reclaims space from dropped variable-length columns in tables or indexed views.

DBCC CLEANTABLE (AdventureWorks2012,"Production.Document", 0)

10.   DBCC DROPCLEANBUFFERS         Removes all clean buffers from the buffer pool.

11.   DBCC FREEPROCCACHE                 Removes all elements from the plan cache, removes a specific plan from the plan cache by specifying a plan handle or SQL handle, or removes all cache entries associated with a specified resource pool.

12.   DBCC INDEXDEFRAG       Defragments indexes of the specified table or view.

DBCC INDEXDEFRAG (AdventureWorks2012, "Production.Product", PK_Product_ProductID)

13.   DBCC SHRINKDATABASE               Shrinks the size of the data and log files in the specified database.

DBCC SHRINKDATABASE (UserDB, 10);

DBCC SHRINKDATABASE (AdventureWorks2012, TRUNCATEONLY);