Introduction SQL Server Editions

SQL Server Editions

There are several edition of SQL Server. Each Edition is differing in features, hardware, and cost.

Enterprise Edition : SQL Server 2012 Enterprise Edition supports 160 physical processors. It support all advanced availability features (e.g., table partitioning, data compression, creating and managing a SQL Server cluster). SQL Server 2012 manages thousands of connections. It includes both the core database engine and add-on services, with a range of tools for. It can manage databases as large as 524 petabytes and address 2 terabytes of memory. 

Developer Edition:  The Developer Edition is the same as the Enterprise Edition, but it’s licensed only for development and testing. It can be easily upgraded to Enterprise without reinstallation. This edition is available to download by students free of charge as a part of Microsoft's Dream Spark program.

Standard Edition: SQL Server Standard edition includes the core database engine, along with the stand-alone services. It supports fewer active instances (number of nodes in a cluster) and does not include some high-availability functions such as hot-add memory (allowing memory to be added while the server is still running), and parallel indexes. It is a good choice when advanced functions (such as data compression, compressed backups, indexed views, etc.) are not required for the business.

 

Standard edition is an affordable option for small and medium-sized organizations. ItIncludes the core functionality needed for non-mission-critical e-commerce, data warehousing, and line-of-business solutions.

Workgroup Edition: it is suitable for smaller departments. It supports systems up to 2 CPU and 4 GB RAM. It has features Import/Export, Limited replication publishing, clustering Back-up, log shipping, basic reporting. The key feature missing from Workgroup Edition is Integration Services. This edition has been retired in SQL Server 2012

Web Edition: As the name implies, the Web Edition, new for SQL Server 2008 is licensed for hosting websites. It supports 4 CPU and OS maximum of memory. Database size in web edition is not limited.

Express:  SQL Server Express Edition is free edition of SQL Server. It includes the core database engine. While there are no limitations on the number of databases or users supported, it is limited to using one processor, 1 GB memory and 10 GB database files (4 GB database files prior to SQL Server Express 2008 R2).

Compact: It is free embedded database for mobile application development. The maximum size of the database is 4 GB. The SQL CE engine is based on SQL Mobile (initially designed for use with hand-held devices) and does not share the same binaries. Due to its small size it supports a subset of the standard data types, does not support stored procedures or Views or multiple-statement batches (among other limitations). It is limited to 4 GB maximum database.

SQL Server Analysis Services and SQL Server Integration Services are included only in Enterprise and Standard Edition. SQL Server Reporting Services is also very limited in the free editions.

Introduction System Databases in SQL Server

SQL Server 2008 uses five system databases to store system information, track operations, and provide a temporary work area.

Master:  Master database holds information about the running server’s databases and server’s Configuration. It contains all of the system level information for SQL Server – all logins, linked servers, endpoints, and other system-wide configuration settings.

SQL Server cannot start if the master database is unavailable because master database records the existence of all other databases and the location of those database files and records the initialization information for SQL Server.

We can use master database objects in other databases e.g. store procedure in master database may be called from user database.

If master database is corrupted and sql server is start with damaged Master database then master database will be restored from backup.Sometimes Master database is corrupted and we cannot start SQL Server.  Then master database cannot be restored.  In this situation rebuild the master database using command prompt and restored from latest backup once again.

Msdb: Msdb database Maintains lists of activities, such as backups and jobs, Maintenacne  plan, DTS packages ,database mail, Service Broker ,and tracks which database backup goes with which user database.  By default msdb use simple recovery model. It  is database for sql agent.

Model: Model database is template database for new databases. Any object placed in the

 model database will be copied into any new database.

Tempdb: The tempdb is a temporary workspace  for storing temporary tables, worktables that hold intermediate results during the sorting or query processing ,batches, stored procedures (including Microsoft stored procedures), and the     SQL Server engine itself. If SQL Server needs to create temporary heaps or lists during query execution, it creates them in tempdb. tempdb is dropped and recreated when SQL Server is restarted.

The tempdb is created from model database and reset to its last configured size.
we cannot backup and restore tempdb.

Resource: This hidden and read-only database, added in SQL Server 2005. It contains

contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata.

Difference between @@IDENTITY, SCOPE_IDENTITY () and IDENT_CURRENT (TABLE).

When we are working with identity columns the problem arise how to determining the value of the identity (column with identity property) that was just created. The new identity value is created with SQL Server at the time of the insert and we want to display this inserted identity value to user or we want to display the row on a user-interface grid within an application.

1     @@IDENTITY: This is global variable .It returns the last identity value generated by SQL Server for any table, connection, or scope. If another insert takes place between the time of your insert and the time when you check @@IDENTITY, @@IDENTITY will return not your insert, but the last insert. For this reason, don’t use @@IDENTITY.

Example: Suppose you have table category and a insert trigger on category table. This trigger is used to insert identity value in Product table. Then @@IDENTITY returns the last inserted value from Product table.

---------------Session 1---------------

If OBJECT_ID('Category','U') is not null

Drop table Category

go

Create table Category(CatID int not null identity(1,1),CatName varchar(20))

go

If OBJECT_ID('Product','U') is not null

Drop table Product

go

Create table Product(ProdID int not null identity(1000,1),ProdName varchar(20))

go

------------Insert Trigger on Category Table----------

Create Trigger CategoryTrigger ON Category FOR INSERT

AS

BEGIN

            INSERT into Product(ProdName) values('Product1');

END;

---------insert in Category table----

insert into Category(CatName) values('PromDresses');

-------------Check value of both table-------

select * from Category

select * from Product

------------Check value of @@IDENTITY-------

select @@IDENTITY

            ---This will return 1000

2    SCOPE_IDENTITY (): This is function. It returns the last generated identity value within the scope of the calling batch or procedure rather than identity created by any trigger or any udf (user defined function). It is the safest way to determine the identity value   you last generated.

----Check scope identity----

select scope_identity()

------This will return 1 last inserted value of category table in current session

3    IDENT_CURRENT (TABLE): This function returns the last identity value of a specific table in any session and any scope.

-------Checking Ident_current Value-------

Select Ident_current('Category')

-----This will return 1-------

Select Ident_current('Product')

---------This will return 100--------------

Check all the values in new session(Open new query window)

--------------Session 2----------------

Select @@IDENTITY -------It will return null

Select Scope_Identity()--------return null

Select Ident_current('Category')

-----This will return 1-----------------------

Select Ident_current('Product')

---------This will return 100----------------