Friday, August 22, 2014

SQL Server 2014 - InMemory Tables, Indexes and Stored Procedures

http://www.sqlservercurry.com/2013/09/sql-server-2014-inmemory-tables-indexes_30.html
Thanks to Pravinkumar Dabade 

 In this article, we will explore how to create memory optimized Tables, Indexes and Stored Procedures introduced in Microsoft SQL Server 2014.SQL Server 2014 as of this writing is in CTP1

To enhance the performance of OLTP, SQL Server 2014 has introduced a number of new features. One of them is In-Memory Tables, Indexes and Stored Procedures. The in-memory tables and indexes are memory optimized structures which allows the rows to be accessed using memory pointers. Simply put, using memory pointers, we can now access the rows more efficiently. 

When you create indexes on in-memory tables, they do not occupy  disk space. The index existence is now in-memory. The in-memory data structures in SQL Server 2014 are lock-free data structures. So when you implement a transaction and two user’s try to modify the same row, the second transaction is always failed.
The in-memory structures does not have separate tools and a query language. This feature is fully integrated into SQL Server and thus you will use the same tools and language [T-SQL] for querying the data.

For demonstrations in this article, I am using SQL Server 2014 CPT 1 and Windows 8 OS. I have already created a database with the name “PurchaseOrderDB”. Let’s take a look at the following script – 

CREATE TABLE Employees
(
    EmployeeID INT
        NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH(BUCKET_COUNT = 100000),
    EmployeeName NVARCHAR(200)
        COLLATE Latin1_General_100_BIN2 NOT NULL INDEX [IdxEmpName] HASH WITH (BUCKET_COUNT = 100000),
    City NVARCHAR(200)
        COLLATE Latin1_General_100_BIN2 NOT NULL INDEX [IdxCity] HASH WITH(BUCKET_COUNT = 100000),
    Country NVARCHAR(200) NOT NULL,
    Region NVARCHAR(10) NOT NULL
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)

In the above CREATE TABLE script, you might have noticed some new keywords. Let’s first understand these new keywords before we proceed – 

1. MEMORY_OPTIMIZED = ON – by using this keyword, you are creating memory optimized tables. Also note that this keyword can be used only with the CREATE TABLE statement. 

2. DURABILITY = SCHEMA_AND_DATA – this option tells that the schema and data are durable/persisted. In case for some reason if the server crashes, you will still get the schema and data back. This is an optional setting and the default value is the same which we are using during our CREATE TABLE statement – SCHEMA_AND_DATA. The other option is Schema only.

3. NONCLUSTERED HASH WITH – HASHED INDEX is a new index which you can use only with in-memory tables. HASH INDEXES are used for point lookups. With HASH INDEX the BUCKET_COUNT is a must which is used for specifying how many buckets you want to have in the HASH table.

4. Other indexes which are known as secondary indexes excluding primary key index, now can be specified with the CREATE TABLE. As you can see that in the above script, we have indexes specified on EmployeeName and City column.

Note – The HASH INDEX can be specified only with CREATE TABLE.

When you write the above CREATE TABLE statement, internally SQL Server will generate the code for the table and then compile this code into a DLL. Once the DLL is ready, it will load it into the memory.

To access the tables and indexes from our PurchaseOrderDB database, we can create natively compiled stored procedures(SP’s). In these SP’s, we can write our business logic and the Data Manipulation Language (DML) statements as per our domain requirements. Let’s take a look at the syntax for creating a natively compiled stored procedure – 

native-stored-procedure

The create procedure script contains some new keywords. Let’s first understand these new keywords – 

1. NATIVE_COMPILATION – This keyword is used for making Stored Procedures as native stored procedures.

2. SCHEMABINDING – This keyword is a must-use-keyword so that the table which is getting accessed inside the SP, should not get dropped accidently. 

3. EXECUTE AS OWNER – which context the SP should get executed. That means under which user this stored procedure will run. The possible values are owner, self or a specific user.

4. Begin ATOMIC – a required block in native stored procedures. The ATOMIC block automatically COMMIT’s or ROLLBACK’s the transaction. If there is already a transaction, then it will create a SAVEPOINT or if there is no transaction, it will start with a new transaction.

5. The WITH block – This block decides the various session settings which will be always be fixed at SP creation time. The two required parameters are TRANSACTION ISOLATION LEVEL and LANGUAGE. You can set multiple options as well.

Now let’s start SQL Server 2014 if you haven’t already and create a database with the name “PurchaseOrderDB”. Once the database is ready, write the following code in your query window – 

Step 1 – Write the USE database command first as shown below – 

USE PurchaseOrderDB
GO 

Step 2 – Now alter the database using the following commands – 

ALTER DATABASE PurchaseOrderDB ADD  FILEGROUP [PODB_fg]
CONTAINS MEMORY_OPTIMIZED_DATA
GO
ALTER DATABASE PurchaseOrderDB ADD FILE
( NAME = N'PODB_FG', FILENAME = N'C:\PODBDATA')
TO FILEGROUP [PODB_FG] 

The question is why did we alter the database as shown above? The reason is when you directly execute the script shown for CREATE TABLE in the beginning of this article, you will receive the following error – 

The Memory_Optimized_DATA filegroup does not exist or is empty. Memory optimized tables cannot be created for database until it has one Memory_Optimized_DATA filegroup that is not empty

The error clearly tells you that the memory optimized table needs separate file group. So we are configuring our database with the new file group and giving the path of a directory which will be used by SQL Server to store the DLLs, which are created for Data Definition Language (DDL) statements as shown below – 

sql-2014-dll

Step 3 – Now let’s write the CREATE TABLE script as shown below – 

CREATE TABLE Employees
(
    EmployeeID INT
        NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH(BUCKET_COUNT = 100000),
    EmployeeName NVARCHAR(200)
        COLLATE Latin1_General_100_BIN2 NOT NULL INDEX [IdxEmpName] HASH WITH (BUCKET_COUNT = 100000),
    City NVARCHAR(200)
        COLLATE Latin1_General_100_BIN2 NOT NULL INDEX [IdxCity] HASH WITH(BUCKET_COUNT = 100000),
    Country NVARCHAR(200) NOT NULL,
    Region NVARCHAR(10) NOT NULL
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA) 

If you observe the code, we have also included collate with Latin1_General_100_Bin2. This is because string columns in a memory optimized columns must use BIN2 collation.

sql-server-nonbin

For more information, please refer the following MSDN link which lists the limitations, restrictions and workaround [if available] – http://msdn.microsoft.com/en-us/library/dn169381(v=sql.15).aspx

Once your table is created, you can query the table using our everyday T-SQL statements.

Step 4 – Write the following SELECT statement to query Employees table – 

SELECT * FROM Employees

Step 5 – Let’s insert some records into our Employees table by using the following script – 

BEGIN
    DECLARE @EmpID INT=1
    WHILE(@EmpID<100000)
        BEGIN
            INSERT INTO Employees VALUES(@EmpID,'DNCEmp','New York','USA','EAST')
            SET @EmpID+=1
        END
END 

Now execute the query given in Step 4 and you will see the following result – 

insert-employees

You can also use order by clause to get all the rows in order as shown below – 

SELECT * FROM Employees ORDER BY EmployeeID

Step 6 – Write the memory optimized stored procedure as shown below – 

CREATE PROCEDURE InsertEmployee
(
    @EmployeeID INT,
    @EmployeeName NVARCHAR(200),
    @City NVARCHAR(200),
    @Country NVARCHAR(200)
) WITH NATIVE_COMPILATION,
       SCHEMABINDING,
       EXECUTE AS OWNER
AS
BEGIN ATOMIC
    WITH
    (
        TRANSACTION ISOLATION LEVEL = SNAPSHOT,
        LANGUAGE = 'us_english'
    )
    SET @EmployeeID = 1
    WHILE(@EmployeeID<100000)
        BEGIN
            INSERT INTO dbo.Employees VALUES(@EmployeeID,@EmployeeName,@City,@Country,'EAST')
            SET @EmployeeID+=1
        END
END 

Execute the SP as shown below – 

exec InsertEmployee 1,'DNC','London','UK'

Now see the result in your table by repeating Step 4

Abstract – In this article we have seen how to create an in-memory or memory optimized table, indexes and Stored Procedures in SQL Server 2014. To access the rows, we have use T-SQL statements and to perform DML [Insert] operation, we have used Natively Complied Stored Procedure.
Posted by at No comments:
Labels: ,

Friday, July 25, 2014

Features Supported by the Editions of SQL Server 2008

Features Supported by the Editions of SQL Server 2008                                                                                

Copied from MSDN
Use the following tables to determine which features are supported by the different editions of SQL Server 2008.

Scalability

Feature Name
Enterprise
Standard
Workgroup
Web
Express
Express Tools
Express Advanced
Partitioning
Yes
Data compression
Yes
Resource governor
Yes
Partition table parallelism
Yes

High Availability

Feature Name
Enterprise
Standard
Workgroup
Web
Express
Express Tools
Express Advanced
Multi-instance support1
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Log shipping
Yes
Yes
Yes
Yes
Database mirroring2
Yes (full)
Yes (safety full only)
Witness only
Witness only
Witness only
Witness only
Witness only
Failover clustering
Operating system maximum3
2 nodes
Dynamic AWE
Yes
Yes
Failover without client configuration
Yes
Yes
Automatic corruption recovery from mirror
Yes
Yes
Database snapshots
Yes
Fast recovery
Yes
Online indexing
Yes
Online restore
Yes
Mirrored backups
Yes
Hot add memory
Yes
Online configuration of P2P nodes
Yes
Hot add CPU
Yes
Backup compression
Yes
1 SQL Server supports 50 instances on a stand-alone server for all SQL Server editions. SQL Server supports 25 instances on a failover cluster.
2Asynchronous database mirroring is supported only by SQL Server 2005 Enterprise Edition SP1 and later versions.
3Windows Server 2003 supports a maximum of 8 failover cluster nodes. Windows Server 2008 supports a maximum of 16 failover cluster nodes.

Security

Feature Name
Enterprise
Standard
Workgroup
Web
Express
Express Tools
Express Advanced
C2 audit mode
Yes
Yes
Yes
Yes
Yes
Yes
Yes
SQL Server auditing
Yes
Transparent database encryption
Yes
ISV encryption (off-box key management)
Yes

Replication

Feature Name
Enterprise
Standard
Workgroup
Web
Express
Express Tools
Express Advanced
Merge replication
Yes
Yes
Subscriber only¹
Subscriber only
Subscriber only
Subscriber only
Subscriber only
Transactional replication
Yes
Yes
Subscriber only¹
Subscriber only
Subscriber only
Subscriber only
Subscriber only
Snapshot replication
Yes
Yes
Subscriber only
Subscriber only
Subscriber only
Subscriber only
Subscriber only
SQL Server change tracking
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Heterogeneous subscribers
Yes
Yes
Oracle publishing
Yes
P2P transactional replication
Yes
¹If an instance of WorkGroup is used as a Publisher, it supports a maximum of 25 subscriptions to all merge publications, and five subscriptions to all transactional publications. It supports an unlimited number of subscriptions to snapshot publications.

Manageability

Feature Name
Enterprise
Standard
Workgroup
Web
Express
Express Tools
Express Advanced
User instances
Yes
Yes
Yes
Dedicated admin connection
Yes
Yes
Yes
Yes
Yes (Under trace flag)
Yes (Under trace flag)
Yes (Under trace flag)
Policy-Based Management
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Policy-Based Management automation
Yes
Yes
Yes
Yes
Policy-Based Management best practices policies
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Performance data collection and warehouse
Yes
Yes
Yes
Yes
Standard performance reports
Yes
Yes
Plan guides
Yes
Yes
Plan freezing for plan guides
Yes
Yes
Distributed partitioned views
Yes
Partial. Distributed Partitioned Views are not updatable
Partial. Distributed Partitioned Views are not updatable
Partial. Distributed Partitioned Views are not updatable
Partial. Distributed Partitioned Views are not updatable
Partial. Distributed Partitioned Views are not updatable
Partial. Distributed Partitioned Views are not updatable
Parallel index operations
Yes
Automatic query-to-indexed-view matching
Yes
Parallel database backup checksum check
Yes
Database mail
Yes
Yes
Yes
Yes
SQL Server Migration Assistant1
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Maintenance plans
Yes
Yes
Yes
Yes
1For more information about SQL Server Migration Assistant, see Migrate to SQL Server on the SQL Server web site.

Management Tools

Feature Name
Enterprise
Standard
Workgroup
Web
Express
Express Tools
Express Advanced
SQL Server management objects (SMO)
Yes
Yes
Yes
Yes
Yes
Yes
Yes
SQL Server Configuration Manager
Yes
Yes
Yes
Yes
Yes
Yes
Yes
SQL CMD (command prompt tool)
Yes
Yes
Yes
Yes
Yes
Yes
Yes
SQL Server Management Studio
Yes
Yes
Yes
Yes (Basic version)
Yes (Basic Version)
Yes (Basic version)
SQL Server Profiler
Yes
Yes
Yes
Yes
SQL Server Agent
Yes
Yes
Yes
Yes
Database Engine Tuning Advisor
Yes
Yes
Yes
Yes
Microsoft Operations Manager Pack
Yes
Yes
Yes
Yes
Development Tools
Feature Name
Enterprise
Standard
Workgroup
Web
Express
Express Tools
Express Advanced
Microsoft Visual Studio Integration
Yes
Yes
Yes
Yes
Yes
Yes
Yes
SQL query and edit and design tools
Yes
Yes
Yes
IntelliSense (Transact-SQL and MDX)
Yes
Yes
Yes
No
(SSMS is not installed with this edition)
Yes
Yes
Version control support
Yes
Yes
Yes
Business Intelligence Development Studio
Yes
Yes
MDX edit, debug, and design tools
Yes
Yes

Programmability

Feature Name
Enterprise
Standard
Workgroup
Web
Express
Express Tools
Express Advanced
Common language runtime (CLR) integration
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Native XML support
Yes
Yes
Yes
Yes
Yes
Yes
Yes
XML indexing
Yes
Yes
Yes
Yes
Yes
Yes
Yes
MERGE capabilities
Yes
Yes
Yes
Yes
Yes
Yes
Yes
FILESTREAM support
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Date and Time data types
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Internationalization support
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Full-text search
Yes
Yes
Yes
Yes
Yes
Specification of language in query
Yes
Yes
Yes
Yes
Yes
Service Broker (messaging)
Yes
Yes
Yes
Client only
Client only
Client only
Client only
XML/A support
Yes
Yes
Web services (HTTP/SOAP endpoints)
Yes
Yes

Spatial and Location Services

Feature Name
Enterprise
Standard
Workgroup
Web
Express
Express Tools
Express Advanced
Spatial indexes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Geodetic data type
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Advanced spatial libraries
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Standards-based spatial support
Yes
Yes
Yes
Yes
Yes
Yes
Yes

Integration Services

SQL Server Standard or Enterprise is required to design and run Integration Services packages. The Integration Services features that are installed by Workgroup, Web, and Express are only for use by the SQL Server Import and Export Wizard.
Feature
Enterprise
Standard
Workgroup
Web
Express
Express Tools
Express Advanced
SQL Server Import and Export Wizard, and the basic Integration Services features required by the wizard
Yes
Yes
Yes
Yes
Yes
Yes
Yes
SSIS Designer including VSTA scripting
Yes
Yes
Integration Services service, wizards, and command prompt utilities
Yes
Yes
Basic tasks and transformations in addition to those used by the Import and Export Wizard
Yes
Yes
Log providers and logging
Yes
Yes
Data profiling tools
Yes
Yes
Additional sources and destinations:
Raw File source
XML source
DataReader destination
Raw File destination
Recordset destination
SQL Server Compact destination
SQL Server destination
Yes
Yes
Advanced sources, transformations, and destinations:
Data Mining Query transformation
Fuzzy Lookup and Fuzzy Grouping transformations
Term Extraction and Term Lookup transformations
Data Mining Model Training destination
Dimension Processing destination
Partition Processing destination
Yes

Data Warehouse Creation

Feature Name
Enterprise
Standard
Workgroup
Web
Express
Express Tools
Express Advanced
Create cubes without a database
Yes
Yes
Auto-generate staging and data warehouse schema
Yes
Yes
Attribute relationship designer
Yes
Yes
Efficient aggregation designers
Yes
Yes

Data Warehouse Scale and Performance

Feature Name
Enterprise
Standard
Workgroup
Web
Express
Express Tools
Express Advanced
Change data capture
Yes
Star join query optimization
Yes
Scalable read-only AS configuration
Yes
Proactive caching
Yes
Auto parallel partition processing
Yes
Partitioned cubes
Yes
Distributed partitioned cubes
Yes

Multi-Dimensional Analytics

Feature Name
Enterprise
Standard
Workgroup
Web
Express
Express Tools
Express Advanced
SQL Server Analysis Services service
Yes
Yes
SQL Server Analysis Services backup
Yes
Yes
General performance/scale improvements
Yes
Yes
Dimension, attribute relationship, aggregate, and cube design improvements
Yes
Yes
Personalization extensions
Yes
Yes
Financial aggregations
Yes
Custom rollups
Yes
Semi-additive measures
Yes
Writeback dimensions
Yes
Linked measures and dimensions
Yes
Binary and compressed XML transport
Yes
Yes
Account intelligence
Yes
Perspectives
Yes
Analysis Services shared, scalable databases
Yes

Data Mining

Feature Name
Enterprise
Standard
Workgroup
Web
Express
Express Tools
Express Advanced
Standard algorithms
Yes
Yes
Data mining tools: wizards, editors, query builders
Yes
Yes
Cross validation
Yes
Models on filtered subsets of mining structure data
Yes
Time series: custom blending between ARTXP and ARIMA models
Yes
Time series: prediction with new data
Yes
Unlimited concurrent data mining queries
Yes
Advanced configuration and tuning for algorithms
Yes
Algorithm plug-in API
Yes
Parallel model processing
Yes
Time series: cross-series prediction
Yes
Unlimited attributes for association rules
Yes
Sequence prediction
Yes
Multiple prediction targets for naïve Bayes, neural network, and logistic regression
Yes

Reporting

Feature Name
Enterprise
Standard
Workgroup
Web
Express
Express Tools
Express Advanced
Report server
Yes
Yes
Yes
Yes
Yes
Report Designer
Yes
Yes
Yes
Yes
Yes
Report Manager
Yes
Yes
Yes
Yes (Report Manager)
Yes (Report Manager)
Role-based security
Yes
Yes
Yes (Fixed roles)
Yes (Fixed roles)
Yes (Fixed roles)
Ad-hoc reporting (Report builder)
Yes
Yes
Yes
Word export and enhanced text formatting
Yes
Yes
Yes
Yes
Yes
Enterprise-scale reporting engine
Yes
Yes
Yes
Yes
Yes
IIS-agnostic report deployment
Yes
Yes
Yes
Yes
Yes
Updated management tools
Yes
Yes
Yes
Yes
Yes
Report definition customization extension (RDCE)
Yes
Yes
Yes
Yes
Yes
SharePoint integration
Yes
Yes
Enhanced SSRS gauges and charting
Yes
Yes
Yes
Yes
Yes
Custom authentication
Yes
Yes
Yes
Yes
Export to Excel, PDF, and images
Yes
Yes
Yes
Yes
Yes
Remote and non-relational data source support
Yes
Yes
E-mail and file share delivery
Yes
Yes
Report history, scheduling, subscriptions, and caching
Yes
Yes
Data source, delivery, and rendering extensibility
Yes
Yes
Scale out (Web farms)
Yes
Infinite clickthrough
Yes
Data-driven subscriptions
Yes
Reporting Services memory limits
OS Maximum
OS Maximum
4 GB
4 GB
4 GB

Features Supported by the Editions of SQL Server 2008

Use the following tables to determine which features are supported by the different editions of SQL Server 2008.

Scalability

Feature Name
Enterprise
Standard
Workgroup
Web
Express
Express Tools
Express Advanced
Partitioning
Yes
Data compression
Yes
Resource governor
Yes
Partition table parallelism
Yes

High Availability

Feature Name
Enterprise
Standard
Workgroup
Web
Express
Express Tools
Express Advanced
Multi-instance support1
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Log shipping
Yes
Yes
Yes
Yes
Database mirroring2
Yes (full)
Yes (safety full only)
Witness only
Witness only
Witness only
Witness only
Witness only
Failover clustering
Operating system maximum3
2 nodes
Dynamic AWE
Yes
Yes
Failover without client configuration
Yes
Yes
Automatic corruption recovery from mirror
Yes
Yes
Database snapshots
Yes
Fast recovery
Yes
Online indexing
Yes
Online restore
Yes
Mirrored backups
Yes
Hot add memory
Yes
Online configuration of P2P nodes
Yes
Hot add CPU
Yes
Backup compression
Yes
1 SQL Server supports 50 instances on a stand-alone server for all SQL Server editions. SQL Server supports 25 instances on a failover cluster.
2Asynchronous database mirroring is supported only by SQL Server 2005 Enterprise Edition SP1 and later versions.
3Windows Server 2003 supports a maximum of 8 failover cluster nodes. Windows Server 2008 supports a maximum of 16 failover cluster nodes.

Security

Feature Name
Enterprise
Standard
Workgroup
Web
Express
Express Tools
Express Advanced
C2 audit mode
Yes
Yes
Yes
Yes
Yes
Yes
Yes
SQL Server auditing
Yes
Transparent database encryption
Yes
ISV encryption (off-box key management)
Yes

Replication

Feature Name
Enterprise
Standard
Workgroup
Web
Express
Express Tools
Express Advanced
Merge replication
Yes
Yes
Subscriber only¹
Subscriber only
Subscriber only
Subscriber only
Subscriber only
Transactional replication
Yes
Yes
Subscriber only¹
Subscriber only
Subscriber only
Subscriber only
Subscriber only
Snapshot replication
Yes
Yes
Subscriber only
Subscriber only
Subscriber only
Subscriber only
Subscriber only
SQL Server change tracking
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Heterogeneous subscribers
Yes
Yes
Oracle publishing
Yes
P2P transactional replication
Yes
¹If an instance of WorkGroup is used as a Publisher, it supports a maximum of 25 subscriptions to all merge publications, and five subscriptions to all transactional publications. It supports an unlimited number of subscriptions to snapshot publications.

Manageability

Feature Name
Enterprise
Standard
Workgroup
Web
Express
Express Tools
Express Advanced
User instances
Yes
Yes
Yes
Dedicated admin connection
Yes
Yes
Yes
Yes
Yes (Under trace flag)
Yes (Under trace flag)
Yes (Under trace flag)
Policy-Based Management
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Policy-Based Management automation
Yes
Yes
Yes
Yes
Policy-Based Management best practices policies
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Performance data collection and warehouse
Yes
Yes
Yes
Yes
Standard performance reports
Yes
Yes
Plan guides
Yes
Yes
Plan freezing for plan guides
Yes
Yes
Distributed partitioned views
Yes
Partial. Distributed Partitioned Views are not updatable
Partial. Distributed Partitioned Views are not updatable
Partial. Distributed Partitioned Views are not updatable
Partial. Distributed Partitioned Views are not updatable
Partial. Distributed Partitioned Views are not updatable
Partial. Distributed Partitioned Views are not updatable
Parallel index operations
Yes
Automatic query-to-indexed-view matching
Yes
Parallel database backup checksum check
Yes
Database mail
Yes
Yes
Yes
Yes
SQL Server Migration Assistant1
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Maintenance plans
Yes
Yes
Yes
Yes
1For more information about SQL Server Migration Assistant, see Migrate to SQL Server on the SQL Server web site.

Management Tools

Feature Name
Enterprise
Standard
Workgroup
Web
Express
Express Tools
Express Advanced
SQL Server management objects (SMO)
Yes
Yes
Yes
Yes
Yes
Yes
Yes
SQL Server Configuration Manager
Yes
Yes
Yes
Yes
Yes
Yes
Yes
SQL CMD (command prompt tool)
Yes
Yes
Yes
Yes
Yes
Yes
Yes
SQL Server Management Studio
Yes
Yes
Yes
Yes (Basic version)
Yes (Basic Version)
Yes (Basic version)
SQL Server Profiler
Yes
Yes
Yes
Yes
SQL Server Agent
Yes
Yes
Yes
Yes
Database Engine Tuning Advisor
Yes
Yes
Yes
Yes
Microsoft Operations Manager Pack
Yes
Yes
Yes
Yes
Development Tools
Feature Name
Enterprise
Standard
Workgroup
Web
Express
Express Tools
Express Advanced
Microsoft Visual Studio Integration
Yes
Yes
Yes
Yes
Yes
Yes
Yes
SQL query and edit and design tools
Yes
Yes
Yes
IntelliSense (Transact-SQL and MDX)
Yes
Yes
Yes
No
(SSMS is not installed with this edition)
Yes
Yes
Version control support
Yes
Yes
Yes
Business Intelligence Development Studio
Yes
Yes
MDX edit, debug, and design tools
Yes
Yes

Programmability

Feature Name
Enterprise
Standard
Workgroup
Web
Express
Express Tools
Express Advanced
Common language runtime (CLR) integration
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Native XML support
Yes
Yes
Yes
Yes
Yes
Yes
Yes
XML indexing
Yes
Yes
Yes
Yes
Yes
Yes
Yes
MERGE capabilities
Yes
Yes
Yes
Yes
Yes
Yes
Yes
FILESTREAM support
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Date and Time data types
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Internationalization support
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Full-text search
Yes
Yes
Yes
Yes
Yes
Specification of language in query
Yes
Yes
Yes
Yes
Yes
Service Broker (messaging)
Yes
Yes
Yes
Client only
Client only
Client only
Client only
XML/A support
Yes
Yes
Web services (HTTP/SOAP endpoints)
Yes
Yes

Spatial and Location Services

Feature Name
Enterprise
Standard
Workgroup
Web
Express
Express Tools
Express Advanced
Spatial indexes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Geodetic data type
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Advanced spatial libraries
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Standards-based spatial support
Yes
Yes
Yes
Yes
Yes
Yes
Yes

Integration Services

SQL Server Standard or Enterprise is required to design and run Integration Services packages. The Integration Services features that are installed by Workgroup, Web, and Express are only for use by the SQL Server Import and Export Wizard.
Feature
Enterprise
Standard
Workgroup
Web
Express
Express Tools
Express Advanced
SQL Server Import and Export Wizard, and the basic Integration Services features required by the wizard
Yes
Yes
Yes
Yes
Yes
Yes
Yes
SSIS Designer including VSTA scripting
Yes
Yes
Integration Services service, wizards, and command prompt utilities
Yes
Yes
Basic tasks and transformations in addition to those used by the Import and Export Wizard
Yes
Yes
Log providers and logging
Yes
Yes
Data profiling tools
Yes
Yes
Additional sources and destinations:
Raw File source
XML source
DataReader destination
Raw File destination
Recordset destination
SQL Server Compact destination
SQL Server destination
Yes
Yes
Advanced sources, transformations, and destinations:
Data Mining Query transformation
Fuzzy Lookup and Fuzzy Grouping transformations
Term Extraction and Term Lookup transformations
Data Mining Model Training destination
Dimension Processing destination
Partition Processing destination
Yes

Data Warehouse Creation

Feature Name
Enterprise
Standard
Workgroup
Web
Express
Express Tools
Express Advanced
Create cubes without a database
Yes
Yes
Auto-generate staging and data warehouse schema
Yes
Yes
Attribute relationship designer
Yes
Yes
Efficient aggregation designers
Yes
Yes

Data Warehouse Scale and Performance

Feature Name
Enterprise
Standard
Workgroup
Web
Express
Express Tools
Express Advanced
Change data capture
Yes
Star join query optimization
Yes
Scalable read-only AS configuration
Yes
Proactive caching
Yes
Auto parallel partition processing
Yes
Partitioned cubes
Yes
Distributed partitioned cubes
Yes

Multi-Dimensional Analytics

Feature Name
Enterprise
Standard
Workgroup
Web
Express
Express Tools
Express Advanced
SQL Server Analysis Services service
Yes
Yes
SQL Server Analysis Services backup
Yes
Yes
General performance/scale improvements
Yes
Yes
Dimension, attribute relationship, aggregate, and cube design improvements
Yes
Yes
Personalization extensions
Yes
Yes
Financial aggregations
Yes
Custom rollups
Yes
Semi-additive measures
Yes
Writeback dimensions
Yes
Linked measures and dimensions
Yes
Binary and compressed XML transport
Yes
Yes
Account intelligence
Yes
Perspectives
Yes
Analysis Services shared, scalable databases
Yes

Data Mining

Feature Name
Enterprise
Standard
Workgroup
Web
Express
Express Tools
Express Advanced
Standard algorithms
Yes
Yes
Data mining tools: wizards, editors, query builders
Yes
Yes
Cross validation
Yes
Models on filtered subsets of mining structure data
Yes
Time series: custom blending between ARTXP and ARIMA models
Yes
Time series: prediction with new data
Yes
Unlimited concurrent data mining queries
Yes
Advanced configuration and tuning for algorithms
Yes
Algorithm plug-in API
Yes
Parallel model processing
Yes
Time series: cross-series prediction
Yes
Unlimited attributes for association rules
Yes
Sequence prediction
Yes
Multiple prediction targets for naïve Bayes, neural network, and logistic regression
Yes

Reporting

Feature Name
Enterprise
Standard
Workgroup
Web
Express
Express Tools
Express Advanced
Report server
Yes
Yes
Yes
Yes
Yes
Report Designer
Yes
Yes
Yes
Yes
Yes
Report Manager
Yes
Yes
Yes
Yes (Report Manager)
Yes (Report Manager)
Role-based security
Yes
Yes
Yes (Fixed roles)
Yes (Fixed roles)
Yes (Fixed roles)
Ad-hoc reporting (Report builder)
Yes
Yes
Yes
Word export and enhanced text formatting
Yes
Yes
Yes
Yes
Yes
Enterprise-scale reporting engine
Yes
Yes
Yes
Yes
Yes
IIS-agnostic report deployment
Yes
Yes
Yes
Yes
Yes
Updated management tools
Yes
Yes
Yes
Yes
Yes
Report definition customization extension (RDCE)
Yes
Yes
Yes
Yes
Yes
SharePoint integration
Yes
Yes
Enhanced SSRS gauges and charting
Yes
Yes
Yes
Yes
Yes
Custom authentication
Yes
Yes
Yes
Yes
Export to Excel, PDF, and images
Yes
Yes
Yes
Yes
Yes
Remote and non-relational data source support
Yes
Yes
E-mail and file share delivery
Yes
Yes
Report history, scheduling, subscriptions, and caching
Yes
Yes
Data source, delivery, and rendering extensibility
Yes
Yes
Scale out (Web farms)
Yes
Infinite clickthrough
Yes
Data-driven subscriptions
Yes
Reporting Services memory limits
OS Maximum
OS Maximum
4 GB
4 GB
4 GB


DISCLAIMER ========== This e-mail may contain privileged and confidential information which is the property of Persistent Systems Ltd. It is intended only for the use of the individual or entity to which it is addressed. If you are not the intended recipient, you are not authorized to read, retain, copy, print, distribute or use this message. If you have received this communication in error, please notify the sender and delete all copies of this message. Persistent Systems Ltd. does not accept any liability for virus infected mails.
Posted by at No comments:
Labels:
Subscribe to: Posts (Atom)