Database States

MS SQL Server Database will be in any of the states.

• ONLINE: Database is available for access.
• OFFLINE: Database is unavailable.
• RESTORING: Database Restore is not completed, so The database is unavailable.
• RECOVERING: Database is being recovered. the database state will become online if the recovery succeeds.
• RECOVERY PENDING: recovery is failed due to missing resources, Additional action by the user is required to resolve the error and let the recovery process be completed.
• SUSPECT: database may be damaged. Additional is required to resolve the problem.
• EMERGENCY: This state prevent all the changes, It allows only readonly and access is limited to members of the sysadmin fixed server role. EMERGENCY is primarily used for troubleshooting purposes.

To verify the current state of a database, following SQL Queries or system funtions will be useful:

SELECT name,state_desc FROM sys.databases

SELECT DATABASEPROPERTYEX ( 'Test' , 'status' )

System Databases

SQL Server includes the following system databases:

• master - captures all the information about an SQL instance
• msdb - used by SQL Server agent(schedule alerts & jobs)
• model - Database Template for creating new DB
• Resource - read only database, contains system objects which are logically accessed via all the db via sys schema
• tempdb - storage for temporary objects

Microsoft SQL Server Code Names

Microsoft has given codenames to all their products, Here is the list of the code name of which are assigned to each version of SQL Server Products.

In 1988, Microsoft released its first version of SQL Server. It was developed jointly by Microsoft and Sybase for the OS/2 platform.

1993 – SQL Server 4.21 for Windows NT
1995 – SQL Server 6.0, codenamed SQL95
1996 – SQL Server 6.5, codenamed Hydra
1999 – SQL Server 7.0, codenamed Sphinx
1999 – SQL Server 7.0 OLAP, codenamed Plato
2000 – SQL Server 2000 32-bit, codenamed Shiloh (version 8.0)
2003 – SQL Server 2000 64-bit, codenamed Liberty
2005 – SQL Server 2005, codenamed Yukon (version 9.0)
2008 – SQL Server 2008, codenamed Katmai (version 10.0)
2010 – SQL Server 2008 R2, Codenamed Kilimanjaro (aka KJ)
2012– SQL Server 2012, Codenamed Denali (version 11.0)
2012 - SQL Server Data Tools, Codenamed Juneau (Next CTP Of Denali)
2012 - SQL Server Data Tools, Codenamed Crescent(Included in Denali Version)
2014 - SQL Server In-Memory OLTP, Codenames Hekaton

Microsoft SQL Server 2012 Technologies

Microsoft SQL Server is built with collection of tools/utilities for maintain/manage the data and Data analysis. Some of the tools are listed here:

• Database Engine  - RDBMS System
• Data Quality Services(DQS) - Perform data cleansing by using cloud-based reference data services
• Analysis Services - Provides OLAP & Data Mining Functionality
• Integration Services - Perform data integration and data transformations solutions
• Master Data Services   - Solution for MDM
• Replication  -  Data distribution/Copy Technology from one DB to another
• Reporting Services  - Rich reporting tool

We will look at it in details in next posts.

SQL Server 2008 New Features

Microsoft SQL Server 2008 introduces several important new Transact SQL programmability features and enhances some existing ones

 

Following is the list of key new features

1.Initializing variables

2.Compound assignment operators

3.Enhanced CONVERT function

4.New date and time data types

5.New date and time functions

6.The MERGE statement

7.Grouping sets

8.Table valued parameters

9.Large user-defined types

10.Table value constructor support

11.DDL trigger enhancements

12.Sparse columns

13.Filtered indexes

We will provide the information in detail in next section.

How to import data directly from T-SQL commands without using SQL Server Integration Services?

There are so many ways to import data directly using T-SQL.

Here listed couple of commands which helps to load the Data quickly into the Database:

• BCP
• Bulk Insert
• OpenRowSet
• OPENDATASOURCE
• OPENQUERY

How to insert the values manually for the Identity Column ?

What is Identity Column :

•  For Identity Column, SQL Server will provide the values automatically at the time of inserting each record in the table.

Example :

-- Creating the table

CREATE TABLE TestTable ( TestID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, TestValue NVARCHAR(50) NOT NULL )

-- To insert value to the identity column

INSERT TestTable(TestId, TestValue) VALUES (1, 'record 1')

-- Cannot insert explicit value for identity column in table 'TestTable' when IDENTITY_INSERT is set to OFF.


SET IDENTITY_INSERT TestTable ON       -- Disable the Identity to Insert the values manually

INSERT TestTable(TestId, TestValue) VALUES (1, 'record 1')       -- Insert the values for the identity column

select * from TestTable

SET IDENTITY_INSERT TestTable OFF    -- Enable the Identity to Insert the values by the Server automatically

Differences between Stored procedures and User defined functions

Difference 1:

• Stored procedure used for perform specific tasks
• Normally functions will be used for computing value

Difference 2: 

• Stored procedures may or may not return values
•  But function should return value

Difference 3:

• Stored procedure cannot be used in the select/where/having clause
• But function can be called from select/where/having clause 

Difference 4:

• Stored procedure can run independently. It can be executed using EXECUTE or EXEC command

• But function cannot run independently, It has to be the part of the SQL statement

Difference 5:

• Temporary table (derived) cannot be created on function
• But it can be created in stored procedures 

Difference 6:

• From sql server 2005 onwards, TRY CATCH statements can be used in the stored procedures

• But it cannot be used in the function. But we can use raise error function

Difference 7:

• Stored procedure can call the user defined functions
• But the function cannot call the stored procedures

The function cannot call the stored procedures like procedures. There are many types of stored procedures in sql server.

• System Stored procedure
• User defined Stored procedure
• NET CLR stored procedure
• Extended stored procedure

Except extended stored procedures no one can call the user defined functions

Difference 8:

• Stored procedures can have input and output parameters
• But the function can have only input parameters

Difference 9:

• Stored procedures can have select and all DML operations
• But the function can do only select operation

Functions will not allow to do the DML on existing tables. But still we can do the DML operation only on the table variable inside the user defined functions

Difference 10:

• Function cannot have the transaction statements.

The transaction statement cannot be used in the function. Normally we won't do any DML operations in the function.

• Stored procedure can use transaction statements