Database and Sql Server

A table with FILESTREAM column(s) must have a non-NULL unique ROWGUID column

2011-09-27T16:39:00.000+05:00

I was trying to add a filestream column in one of my existing tables, initially i thought it would be very easy for me, but when i got the error "A table with FILESTREAM column(s) must have a non-NULL unique ROWGUID column." i started to look here and there. Surfed on the net but didn't find a solution.
Below is the solution which i tried.

Workaround to produce the problem again

CREATE TABLE Employee (
    [emp_id] [int] IDENTITY(1,1) NOT NULL,
    [emp_name] [nvarchar](150) NULL,
    [emp_FHname] [nvarchar](150) NULL,
    [emp_age] [int] NULL,
    [emp_sex] [bit] NULL,
CONSTRAINT [PK_Employee_1] PRIMARY KEY CLUSTERED
(
    [emp_id] ASC
)
) ON [PRIMARY]

GO
emp_id is defined as IDENTITY and Primary key

now i wants to add a filestream enabled column named "emp_pic" to store employee pictures, and the table doesn't have any ROWGUIDCOL so i added a empid

ALTER TABLE Employee ADD
    empid UNIQUEIDENTIFIER DEFAULT (newid()) ROWGUIDCOL NOT NULL

After this i executed this statement

ALTER TABLE Employee ADD
    emp_pic [varbinary](max) FILESTREAM

it will give you the error Msg 5505, Level 16, State 1, Line 1
A table with FILESTREAM column(s) must have a non-NULL unique ROWGUID column.

To solve this problem i just changed my primary key column to empid that i added later as ROWGUIDCOL
After changing the primary key, execute the statement

ALTER TABLE Employee ADD
    emp_pic [varbinary](max) FILESTREAM
It will be executed successfully

An easy way to get the Definition of an Object

2011-04-11T16:34:00.001+05:00

I was using the sp_helptext very frequently to get the definition of an object.

Here is another easy way to get the definition of an object

SELECT OBJECT_DEFINITION(object_id) FROM sys.objects WHERE type='V'

You can use the, ALTER in the result set of the above query by using the REPLACE.

In the where condition you can specify the Procedure(P) or Functions(FN) as well.

Difference between NEWSEQUENTIALID() and NEWID()

2011-04-04T11:04:00.000+05:00

In this article, we will be looking at the Difference between NEWSEQUENTIALID() and NEWID() and How NEWSEQUENTIALID() is generated.

NEWSEQUENTIALID() generates the unique identifier values sequentially.
Whereas NEWID() generates the unique identifier values randomly.

NEWSEQUENTIALID() function creates the GUIDs greater than the previously generated GUIDs, since the last restart of the system, because after restarting the system the next NEWSEQUENTIALID can be started from the lower range.
Both NEWID() and NEWSEQUENTIALID() are globally unique. Starting of NEWSEQUENTIALID after the restart of the computer does not affect its globally uniqueness.

NEWSEQUENTIALID() uses the default constraint, and it can't be used in SELECT or SET queries like NEWID().
The following query SELECT NEWSEQUENTIALID() will generate error

The newsequentialid() built-in function can only be used in a DEFAULT expression for a column of type 'uniqueidentifier' in a CREATE TABLE or ALTER TABLE statement. It cannot be combined with other operators to form a complex scalar expression.

This will also generate the similar error

declare @varunique UNIQUEIDENTIFIER
SET @varunique=NEWSEQUENTIALID()
SELECT @varunique

Msg 302, Level 16, State 0, Line 3
The newsequentialid() built-in function can only be used in a DEFAULT expression for a column of type 'uniqueidentifier' in a CREATE TABLE or ALTER TABLE statement. It cannot be combined with other operators to form a complex scalar expression.

while if you use
SELECT NEWID()
OR
declare @varunique UNIQUEIDENTIFIER
SET @varunique=NEWSEQUENTIALID()
SELECT @varunique

No error will be produced and guid will be returned.

NEWID() is not good for performance because it is generated randomly so that it increases the page splits in the indexes.
NEWSEQUENTIALID() can be used to generate GUIDs to reduce page contention at the leaf level of indexes.

Each GUID generated by using NEWSEQUENTIALID() is unique on that computer.
GUIDs generated by using NEWSEQUENTIALID() are unique across multiple computers only if the source computer has a network card.
NEWSEQUENTIALID() returns the value that includes the MAC of the NIC of the system. If the system has the network card, then it is guaranteed that the GUID generated using NEWSEQUENTIALID() will globally unique across the servers.
If the NEWSEQUENTIALID() used on a computer that doesn't have NIC, the values generated using NEWSEQUENTIALID() are not guaranteed to be unique across the servers, it will be unique only for that system.
Internally NEWSEQUENTIALID() usses a Windows API called UuidCreateSequential(), which generates the value based on the NIC's MAC address and an internal hardware timestamp.

Here you can see the Newsequentialid (Histrory/Benefits and Implementation)

Find Created date and Modified date of Database objects

2011-03-22T13:30:00.001+05:00

This script will return the objects in sql server database with modified and created date.
However you can change the order by clause as well as the Where clause according to your requirements

SELECT sys.schemas.name + '.' + sys.objects.name,
create_date,
modify_date
FROM sys.objects
INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id
WHERE type = 'P'
OR type = 'U'
OR type = 'FN'
OR type = 'V'
ORDER BY modify_date DESC

The newsequentialid() built-in function can only be used in a DEFAULT expression

2011-03-10T11:26:00.001+05:00

Msg 302, Level 16, State 0, Line 3
The newsequentialid() built-in function can only be used in a DEFAULT expression for a column of type 'uniqueidentifier' in a CREATE TABLE or ALTER TABLE statement. It cannot be combined with other operators to form a complex scalar expression.

This error occurs when we try to use the NEWSEQUENTIALID() function in the SQL statements or in SET statements. e.g.

SELECT NEWSEQUENTIALID()
OR
declare @varunique UNIQUEIDENTIFIER
SET @varunique=NEWSEQUENTIALID()
SELECT @varunique
OR
INSERT INTO AA(id, name) VALUES(NEWSEQUENTIALID(),'abc')

The NEWSEQUENTIALID() built-in function can only be used in a DEFAULT expression for a column of type 'uniqueidentifier' in a CREATE TABLE or ALTER TABLE statement. It cannot be combined with other operators to form a complex scalar expression.

SQL Server Interview Questions (Part 2)

2011-03-07T17:00:00.006+05:00

Few days back, i went for an interview , following questions were asked

How to Performance Tune a Stored Procedure?
Type of Triggers?
How to get the old value of a field from a trigger when updating a table?
What is the difference between horizontal partition and vertical partition?
if we have three parameters in the where condition (query has the joins with two or more tables table1.filed1=4 and table2.field2=5 and table3.field3=1)
what will be the Order of execution of the where condition
Which condition would be executed first i.e. field1 or field2 or field3?
What are the disadvantages of the cursors?
A stored procedure has cursors in it, how we can eliminate the cursors?
What is the difference between temporary table and table variable?
How to restore the Differential Backup?
What are Backup Types and difference between them?
What are Replication Types, What is snapshot replication?
What is the difference between Replication and Mirroring?
Can a sub report in Crystal Reports can have another sub report?
What is log shipping?
Should we normalize the Database on more than 3rd Normal form?

What is Parameter Sniffing, How it can affect the Performance, and What is the Solution

2011-02-15T12:03:00.000+05:00

What is parameter sniffing?
How it can affect the performance.
What is solution of parameter sniffing?

Let us try to answer these questions,

Whenever a stored procedure is executed for the first time, its execution plan is created. An execution plan for a stored procedure is created the first time a stored procedure is executed. When the SQL SERVER Database engine compiles a stored procedure it looks at the parameters being passed to the stored procedure and then creates an execution plan based on these parameters. The process of looking at parameter values when compiling a stored procedure is called "Parameter sniffing". Sometimes Parameter sniffing can lead to inefficient execution plans, especially when a stored procedure is called with parameter values that have different cardinality.

Parameter sniffing can be considered a plus to the performance of the system, but in those cases where it's not, various mechanisms can be used to avoid it. One of the simplest is to use local variables. You can also use the OPTIMIZE FOR query hint or the WITH RECOMPILE query hint. In extreme cases you can force specific execution plans onto procedures.

We can also say that Parameter sniffing is a process that occurs when executing a stored procedure for the first time that allows SQL Server to build an effective query plan. Normally that is a good thing, but in some cases can actually hurt performance of the query.

According to the white paper
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005

Published in the Microsoft Site:

"Parameter sniffing" refers to a process whereby SQL Server's execution environment "sniffs" the current parameter values during compilation or recompilation, and passes it along to the query optimizer so that they can be used to generate potentially faster query execution plans. The word "current" refers to the parameter values present in the statement call that caused a compilation or a recompilation"

When a SP is not in the procedure cache, when it is executed the query optimizer needs to compile the SP to create an execution plan. In order to do this the query optimizer needs to look at the parameters that are passed and the body of the SP to determine the best method to go about processing the SP. When the query optimizer looks at the SP's parameters, to help determine how to optimize the execution of the SP, it is known as parameter sniffing.

Before I show you with an example about parameter sniffing, you need to know that the query execution plan generated by the SQL Server depends on lot of factors; parameter sniffing is just one of them. So the execution plan I show here might be different from the execution plan that you will generate.

Let's look at the following code

USE
AdventureWorks

CREATE PROCEDURE

GetCustOrders (@FirstCust int, @LastCust int)

SELECT * FROM Sales.SalesOrderHeader

WHERE CustomerID between @FirstCust and @LastCust;

This procedure GetCustOrders accepts two parameters @FirstCust int, @LastCust int . These parameters are passed in the where clause of the query to get the customer orders. Now look at the execution plan of the procedure by execution this procedure by passing different parameters.

USE AdventureWorks

DBCC FREEPROCCACHE

EXEC GetCustOrders 1, 1000

I had used the statement DBCC FREEPROCCACHE to clear the procedure cache so that query optimizer should make a new plan for this execution instead of using any existing one.

Look at the

Actual number of Rows 31465 and

Estimated number of Rows 31465

Here you can see that query optimizer performed a Clustered Index scan operation to get the records.

Now execute EXEC GetCustOrders 600,610 and see the execution plan

It uses the Clustered index scan for this operation as well, because this plan was present in the procedure cache.

Check the number of actual rows and estimated rows,

Actual number of Rows 31465

Estimated number of Rows 31465

Note the in both cases the actual and estimated number of rows are same.

Now clear the procedure cache and execute the procedure with smaller values

DBCC FREEPROCCACHE

EXEC GetCustOrders600,610

Exmaince the actual number of rows and estimated number of rows, they are different now, because optimizer created a new plane for this

Query optimizer performed the seek operation. This means that by passing the smaller range of values , you can get the Index Seek operation, depending on your execution.

If you execute the procedure with smaller values first and then execute the procedure with larger values. Then you can get Index seek for both of the executions.

The first compile of the Procedure generates the execution plan depending on the the parameters passed to the procedure and that plan is kept in the procedure cache for use of future executions of that procedure.

How to eliminate parameter sniffing?

There are three methods to avoid parameter sniffing,

By using local variables in the procedure
By using RECOMPILE Query Hint
Query hint OPTIMIZE FOR

By using Local variables

Parameter sniffing can be disabled by using the local variables in the stored procedure.

CREATE PROCEDURE GetCustOrders (@FirstCustint, @LastCustint)

DECLARE @FC int/td>

DECLARE @LC int

SET @FC = @FirstCust

SET @LC = @LastCust

SELECT * FROM Sales.SalesOrderHeader

WHERE CustomerID BETWEEN @FC AND @LC

You can create two local variables @FC AND @LC that takes the values from parameters of the stored procedure and then pass those values to the query. In this way the actual values of the parameters are no longer contained in the BETWEEN clause in the SELECT statement, instead only those local variables are present.

This method of removing the parameter sniffing problem doesn’t mean you will get a best plan for each execution of the SP

By using Recompile

Creating a stored procedure that specifies the WITH RECOMPILE option in its definition indicates that SQL Server does not cache a plan for this stored procedure; the stored procedure is recompiled each time it is executed. Use the WITH RECOMPILE option when stored procedures take parameters whose values differ widely between executions of the stored procedure

USEAdventureWorks

CREATE PROCEDURE GetCustOrders(@FirstCust int,@LastCust int)

WITH RECOMPILE

SELECT*FROMSales.SalesOrderHeader

WHERECustomerIDbetween@FirstCustand@LastCust ;

By using Optimize For

Instructs the query optimizer to use statistical data instead of the initial values for all local variables when the query is compiled and optimized, including parameters created with forced parameterization

CREATE PROCEDUREGetCustOrders(@FirstCustint,@LastCustint)

WITH RECOMPILE

SELECT*FROMSales.SalesOrderHeader

WHERECustomerID between @FirstCustand @LastCust

OPTION (OPTIMIZE FOR (@FirstCust UNKNOWN,@LastCust UNKNOWN));

You can read more about Query Hints from msdn

SQL Load Generator

2011-02-08T14:27:00.000+05:00

A really nice tool for Load testing on SQL Server. SQL Load Generator is used to generate a load simulating several different users. It is is used to run multiple concurrent queries against SQL Server. The user can choose the number of concurrent queries to run, provide different queries, choose SQL or domain accounts, and provide application name settings.

read more or if you want to download SQL Load Generator you need to go on codeplex SQL Load Generator

Unable to start T-SQL Debugging. Could not attach to SQL Server Process

2011-02-07T13:03:00.000+05:00

How to Enable TSQL Debugging in SQL SERVER ?

Click on the menu Debug >> Start Debugging
An error will be produced "Unable to start T-SQL Debugging. Could not attach to SQL Server Process"

Here is the solution to get rid of this error

Expand the SQL Sever Management Studio
Expand the Security

Right Click on the Logins
Click on New Login
A new window will appear to define the Login

Specify the Login Name.
Specify the Server roles.

Check the sysadmin Server role and then press OK.
Now Debug your Query /SP Debug >> Start Debugging

This can also be done using TSQL.
Here is the Way to do this using TSQL

sp_addsrvrolemember 'Domain\Name', 'sysadmin'
GO

EXEC master..sp_addsrvrolemember @loginame = N'Dmain-Name\Administrator', @rolename = N'sysadmin'
GO

if you are not using any domain, then try your system name.

SQL Server Performance Killers

2011-01-05T15:47:00.001+05:00

There might be several individual factors that can kill the performance of your database. If you aware of the main performance killers in SQL Server in advance, you will be able to focus your tuning efforts on the likely causes.
You also need to look the hardware, operating and SQL Server settings
The main performance killers in SQL SERVER performance are as follows

Poor Indexing
Inaccurate statistics
Excessive blocking and deadlocks
No-set-based operations, usually T-SQL cursors
Non set-based thinking leads to excessive use of cursors and loops rather than exploring more efficient joins and sub-queries. SQL has rich mechanism to get the data instead of trying to loop or row by row approach to be used in the code, this will kill the performance

Poor query design
Querying the data from a table that is not required, or using a table in joins that is not required.
Poor database design
No reusable execution plans
Poor execution plans, usually caused by parameter sniffing
Frequent recompilation of execution plans
Improper use of cursors
Always use set based tsql query. By using cursors, you add a large amount of overhead on SQL SERRVER.

But if you are forced to use cursors, try to use the efficient cursor types such as fast-forward only.
Remember cursors are called the performance killers.
Improper configuration of database log
Excessive use or improper use configuration of tempdb
Every SQL Server instance has only one tempdb, which is used to store the information such as operations involving temporary tables, table variables, also operations such as sorts and row versioning, tempdb can become a bottleneck.

How to Attach Database in SQL SERVER

2010-12-19T10:06:00.000+05:00

This is a step by step walkthrough to attach the Database in SQL SERRVER, if you have data file (.mdf) only and you don’t have log file (.ldf)
I had tried to simplify the steps with the help of screen shots

Connect to your SQL SERVER enterprise manager, in my case I have connected to SQL SERVER 2011 “Denali”
Right click on the Databases, a menu will appear,

Click on Attach, a form Attach Databases will appear

Click on the Add button, file open dialog will appear to select the required file.

Locate your desired data file (.mdf) from your system, and then select that file and then click OK
You will be back again on the Attach Databases dialog, showing the file you have selected. I have selected the AdventureWorks data file.

You can see the row, I highlighted, this is because, I am going to attach the data file with the log file. If you have the log file, then you wouldn’t the message “Not Found”
You can see that currently Remove button is disabled, click on the Row showing the log file

After clicking the Row, Remove button will be enabled, and then click on the Remove button
The Row containing the log file will be removed. If you don’t remove the row containing the log file, the Database wouldn’t be attached, and it will give the error “An error occurred while attaching the Database”.
Click on OK button, the database will be attached successfully.

Sample Database for SQL Server 2011 Denali CTP1

2010-12-17T07:59:00.001+05:00

Sample Database AdventureWorks for SQL Server Denali CTP1 can be downloaded from AdventureWorks Database

This database was used with SQL SERVER 2008R2 and now with 2011 as well

How to Install SQL SERVER 2011

2010-12-13T22:54:00.005+05:00

This is step by step Walkthrough installation guide with images for SQL SERVER 2011
The installation of SQL SERVER 2011 is very much similar to SQL SERVER 2008, If you have already installed SQL SERVER 2008, You might not face any problem.

Microsoft has launched SQL SERVER 2011 "Denali"; I have downloaded MICROSOFT SQL SERVER "Denali" from

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=6a04f16f-f6be-4f92-9c92-f7e5677d91f9&displaylang=en

I hope SQL Server database professionals like administrators and t-sql developers find my SQL SERVER 2011 setup notes useful.
The Operating System requirements for Microsoft SQL Server 2011 "Denali" are
Windows Vista with SP2,
Windows Server 2008 with SP2,
Windows 2008 R2, and
Windows 7 operating systems.
What is interesting related with the supported operating systems list is Windows XP is not supported any more.
Although I have successfully installed MS SQL Server 2008 R2 Developer Edition on my Windows XP PC, SQL Server 2011 Denali CTP1 cannot be installed.
Here is a short list which you can review:
Windows PowerShell 2.0
Microsoft .NET Framework 3.5 SP1
Microsoft .NET Framework 4.0
Please note that these requirements for SQL Server Denali CTP may change with the new releases of Microsoft SQL Server 2011 and SQL Server Setup Application wizard.
Hardware and software requirements can also be seen from
http://msdn.microsoft.com/en-us/library/ms143506%28SQL.110%29.aspx

This is the step by step installation guide of the SQL SERVER 2011 “Denali” on windows 7
I have installed x86 version of the product, you can proceed according to your system.

Extract the file at your desired location.

On start of setup, the first screen will be

These screens are very much similar to the SQL Server 2008.
After a while, next screen will appear

To view the hardware and software requirements for SQL Server 2011, visit the following Microsoft web site
http://msdn.microsoft.com/en-us/library/ms143506%28SQL.110%29.aspx

On selection of New SQL Server stand-alone installation, the following screen will appear. On this screen Setup Support Rules will be executed to identify problems that might occur during installation SQL Server Setup support files. Before continuing to installation process, the failures listed must be corrected

No Reboot package error is found.
Reboot Package: : This rule determines whether the computer has the required update package that ensures that the computer will not have to be rebooted because of the Microsoft .NET Framework 4 installation.

If you are installing Microsoft SQL Server 2011 (aka Denali) on Windows 7 or on Windows 2008 R2 OS computer or server, please download the .NET 4.0 update from KB Article 958488
If you are installing Microsoft SQL Server 2011 on Windows Vista SP2 or on Widows Server 2008 SP2 operating system, then please download Microsoft .NET Framework 4.0 update from KB Article 956250
After the installation of .Net Framework 4.0, system will start, and after resuming the setup again, the screen will be displayed

After clicking OK button, the next screen will be of Product key, I prefer to use Microsoft SQL Server 2011 Enterprise Edition so I select Enterprise Evaluation from dropdown list.
Select the Enterprise Evaluation.

You must accept the license terms in order to continue the installation of Microsoft SQL Server 2011 (SQL Server Denali CTP).Accept the license terms in order to continue to Microsoft SQL Server 2011 installation (SQL Server Denali CTP).and then click next.

Click Install button to install SQL Server 2011 Setup Support files. SQL SERVER will install the setup support files that necessary during the installation.
Click Next button after selecting Microsoft SQL Server 2011 Denali CTP 1 features.
I just select the following Microsoft SQL Server 2011 features.
Click Next button for selecting among Microsoft SQL Server 2011 Denali CTP 1 features.

Select the SQL SERVER 2011 “Denali” features according to your requirements. I have selected all the features except SQL Server Replication, Full-Text Search, Analysis Services and Reporting Services for faster installation of SQL Server Setup

After SQL Server 2011 features selection for setup process, the installation rules are applied and give reasons for a possible failure.

On this screen (Instance Configuration), you can change the instance name of the system. This screen will also show the instances that already installed on the machine.
I have kept the default instance because I don’t have SQL Server installed on my machine.When you click on the Next button, the following screen will be displayed.

Information about the disk space required and available is displayed on this screen. Click the Next button to proceed.

This is screen is used for configuration of SQL Server. This screen will be used to specify the accounts under which the SQL SERVER services will run.
I have used the Windows NT service account, if you want to the same, then click on the button “Use the Same account for all SQL Services”. After clicking following screen will be displayed.

You can see the selection of NT AUTHORITY/SYSTEM. When you click OK, you will be back on SERVER CONFIGURATION screen.

On server configuration, you can also specify the startup type.ie You wants to start the service automatically , when the windows starts or you wants to start the service later from control panel > administrative tools > services.
You can also go to your services panel by just entering services.msc on your windows Start >Run

You can also specify the collation (Collation refers to a set of rules that determine how data is sorted and compared) for this instance of SQL Server.
I have kept the default settings for collation.
Click the Next, after doing necessary configurations.

This screen is used to give specify the Database Engine Configuration. From this screen we specify the authentication mode, Directories for SQL SERVER and Configuration for FILESTREAM. We will use the windows authentication. Click on “Add Current User”, the windows user will be added. Click on Data Directories tab.

You can change the path of SQL SERVER data directories. I have kept all the default options for this page.
Click on FILESTREAM tab.

You can configure the FILESTREAM from this screen. Just enable all the options specified. I didn’t configure the FILESTREAM so I didn’t make any changes on this page.
Click the next button to proceed.

This screen is used to send the errors that might occur during the installation of SQL SERVER.
Click the Next button.

On this screen SQL SERVER checks few rules, there is nothing to do on this screen.
Click Next

This is the last screen to start the installation. On this screen, SQL SERVER shows the features/components that are going to be installed.
Click the install button to start the installation.

This screen will be displayed at the END of installation process. It means that your installation of SQL SERVER is completed.

Select SQL Server Management Studio, this will be come under
Start >> Microsoft SQL Server Denali CTP1>>SQL Server Management Studio

The Operating System on this computer does not meet the minimum requirements for SQL SERVER "Denali"

2010-12-08T10:43:00.003+05:00

When installing SQL Server 2011 Denal , you might get the following error, if you are installing on Windows XP operating System , because Windows XP is not supported for SQL Server 2011.

The operating System on this computer does not meet the minimum requirements for SQL SERVER "Denali".For more information, see Hardware and Software Requirements for installing SQL SERVER at http://go.microsoft.com/fwlink/?LinkID=195092

Microsoft SQL Server 2011 Requirements

During Microsoft SQL Server 2011 installation, in order to install SQL Server 2011 without a problem and an interruption please take care to the Microsoft SQL Server 2011 Requirements list. It is better to install the Microsoft SQL Server 2011 requirements before starting

http://download.microsoft.com/download/A/6/B/A6BA76B8-BA7D-4408-A93A-F5EF8A1E1DD5/SQLFULL_x86_ENU.exe

http://download.microsoft.com/download/A/6/B/A6BA76B8-BA7D-4408-A93A-F5EF8A1E1DD5/SQLFULL_ENU.iso

http://download.microsoft.com/download/A/6/B/A6BA76B8-BA7D-4408-A93A-F5EF8A1E1DD5/SQLFULL_x64_ENU.exe

The operating system requirements for Microsoft SQL Server 2011 are as follows.

Microsoft SQL Server 2011 aka SQL Server Denali CTP 1 Evaluation version supports:

Windows Vista with SP2,

Windows Server 2008 with SP2,

Windows 2008 R2, and

Windows 7 operating systems.

What is interesting related with the supported operating systems list is Windows XP is not supported any more.

I have successfully installed MS SQL Server 2008 R2 on my Windows XP machine; SQL Server 2011 Denali CTP 1 cannot be installed.

Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created.

2010-12-03T12:35:00.002+05:00

When making changes in a table, an error might occur

Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table can't be re-created or enabled the option Prevent saving changes that require the table to be re-created.

The reason behind this error is that, whenever we add or delete a field of a table, that table requires to be dropped and recreate again. SQL Server prevents this change to happen ie it does not allow for drop and recreate of the table.

Here is solution to this problem

Open the SQL Server Management Studio

Go to the Tools Menu and select Options

Select the Designers from the Options and uncheck "Prevent saving changes that require table re-creation"

Sparse columns do not take any space

2010-11-27T14:43:00.003+05:00

Sparse columns are the new feature in SQL SERVER 2008. Sparse columns are better, when dealing with NULL in SQL SERVER. Sparse columns do not take any space. Defining the column as sparse can save a significant amount of disk space but at the cost of more overhead to retrieve non null values. Sparse columns can be defined by using the CREATE TABLE or ALTER TABLE statements.

CREATE TABLE DocumentStore
(

DocID int PRIMARY KEY,
Title varchar(200) NOT NULL,
ProductionSpecification varchar(20) SPARSE NULL,
ProductionLocation smallint SPARSE NULL,
MarketingSurveyGroup varchar(20) SPARSE NULL

) ;
GO

Advantages of Sparse Columns

Storing a null in a sparse column takes up no space at all.
Sparse Columns will behave as the ordinary columns; SPARSE column can work as one XML column as well.
Sparse columns work really well with filtered indexes, where data are filled in the row. A filtered index on a sparse column can index only the rows that have populated values. This creates a smaller and more efficient index
SPARSE column saves database space when there are null values in database.

Disadvantages of Parse Columns

SPARSE column does not have IDENTITY or ROWGUIDCOL property.
Sparse Column must be null able
SPARSE column cannot be applied on text, ntext, image, timestamp, geometry, geography or user defined data types.
SPARSE column cannot have default value or rule or computed column.
Clustered index or a unique primary key index cannot be applied on SPARSE columns. SPARSE column cannot be part of clustered index key.
Table containing SPARSE column can have maximum size of 8018 bytes instead of regular 8060 bytes.
A table operation which involves SPARSE column takes performance hit over regular column.
Sparse columns are incompatible with data compression (Data compression doesn't work).
Merge replication does not support sparse columns

SQL Server 2011 , Code Named “Denali” is released

2010-11-16T10:48:00.000+05:00

SQL Server 2011 – Code Named “Denali” is released on November 11, 2010 at SQLPASS

You can download CTP1 right now and install on your machine.

SQL Server code-named 'Denali' helps empowers organizations to be more agile in today’s competitive market. Customers will more efficiently deliver mission-critical solutions through a highly scalable and available platform. Industry-leading tools help developers quickly build innovative applications while data integration and management tools help deliver credible data reliably to the right users and extended managed self-service BI capabilities enable meaningful insights.

The major features of the new products are as following:

Enhanced Mission-Critical Platform: an enhanced highly available and scalable platform.
Developer and IT Productivity: new innovative productivity tools and features.
Pervasive Insight: expanding the reach of BI to business users and end-to-end data integration and management.

Book Online of SQL Server 2011 “Denali” is available here.

Installation guide for SQL Server 2011 “Denali” available here.

New Features in Sql Server 2008

2010-01-02T12:59:00.000+05:00

Compressed Backup
AUDITING => Change Data Capture (CDC)
FileStream
Sparse Column Support
Performance Data Management
Encryption => Transparent data encryption (TDE)
Resource Governor
Freeze Plan
LINQ Support

Deprecated Features in future realases (but still available in Sql 2008)

BACKUP {DATABASE | LOG} WITH PASSWORD
BACKUP {DATABASE | LOG} WITH MEDIAPASSWORD
RESTORE {DATABASE | LOG} … WITH DBO_ONLY
RESTORE {DATABASE | LOG} WITH PASSWORD
RESTORE {DATABASE | LOG} WITH MEDIAPASSWORD

CBT Nugget s 70-432

2009-10-17T12:55:00.000+06:00

Download CBT Nuggets cbt-nuggets-exam-pack-70-432 sql-server-2008

http://hotfilms.org/ebooks/tutorials-cbt-nuggets-exam-pack-70-432-oft-sql-server-2008-imple-874433.html

How to Get SQL Server Table Size

2009-09-10T12:40:00.005+06:00

This is a very common problem to get space used by the Database objects.
This stored procedure uses the sp_spaceused.
This stored procedure has been tested and used on a SQL Server 2005 and it will work fine on SQL Server 2008 as well.
It’s a very much simple stored procedure.
This SP declare a cursor that will get the names of all user defined tables and Schemas (concatenating the schema and table names as two part name that is schema.tablename) in the current database.
Then Stored Procedure creates a temporary table to store the individual data elements for each table. Then loop through the created cursor and save the results of the sp_spaceused command to temporary table.
The last step includes closing and deallocating the cursor, selecting all rows from temp table and dropps that table.

-- =============================================
CREATE PROCEDURE utility.Proc_GetDBTableSizes

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @TableName VARCHAR(200)

-- Insert statements for procedure here
DECLARE tableCursor CURSOR FOR
SELECT sys.schemas.[name]+'.'+sys.objects.[name]
FROM sys.schemas INNER JOIN sys.objects ON sys.schemas.schema_id=sys.objects.schema_id
WHERE type='U' AND is_ms_shipped=0 ORDER BY sys.schemas.[name] -- WHERE is_ms_shipped is Microsoft generated objects
FOR READ ONLY
--A procedure level temp table to store the results
CREATE TABLE #TempTable
(
tableName varchar(200),
numberofRows varchar(100),
reservedSize varchar(50),
dataSize varchar(50),
indexSize varchar(50),
unusedSize varchar(50)
)

--Open the cursor
OPEN tableCursor

--Get the first Record from the cursor
FETCH NEXT FROM tableCursor INTO @TableName

--Loop until the cursor was not able to fetch
WHILE (@@Fetch_Status >= 0)
BEGIN
--Insert the results of the sp_spaceused query to the temp table
INSERT #TempTable
EXEC sp_spaceused @TableName

--Get the next Record
FETCH NEXT FROM tableCursor INTO @TableName
END

--Close/Deallocate the cursor
CLOSE tableCursor
DEALLOCATE tableCursor

--Select all records so we can use the reults
SELECT *
FROM #TempTable

DROP TABLE #TempTable

END
GO

SQL Server Interview Questions

2009-08-05T20:22:00.000+06:00

How to implement many-to-many relationships?

What's the difference between a primary key and a unique key?

What is user defined datatypes and when to use them?

Define candidate key, alternate key, and composite key?

Is there a column to which a default value can't be bound?

What are ACID properties?

Explain different isolation levels?

What is MOLAP, ROLAP and HOLAP?

Types of constraints?

What are the types of indexes ? What are the type of the NonClustered Indexes ?

In which situation NonClustered is more fast then clustered index?

What is the difference between deadlock , live lock and blocking ? And How to resolve them?

What are the Query Hints?

What are the different types of DBCC commands?

What are instead of triggers and what’s the difference between Insert and Instead of trigger , which trigger will be fired first ?

What are cursors? Explain different types of cursors. What are the disadvantages of cursors? How can you avoid cursors? Why DBAs don’t like Cursors ?

What is a self join? Is self join physically exists in Sql Server ?

What is the difference between OSQL and Query Analyzer ?

What is BCP and when to use it ?

What is collation ?

What’s the difference between a primary key and a unique key?

When is the use of UPDATE_STATISTICS command?

What types of Joins are possible in Sql Server?
Where are SQL server users names and passwords are stored in sql server?

What is log shipping?
What is the difference between a local and a global variable?
What are the OS services that the SQL Server installation adds?
Specify 3 ways to get an accurate count of the number of records in a table?

What is the basic functions for master, msdb, model, tempdb databases?

What is a table called, if it does not have neither Cluster nor Non-cluster Index? What is it used for?
How do you load large data to the SQL server database?
What is Cross Join?

What is OLTP(OnLine Transaction Processing)? How to define that a Database is OLTP ?

What is the Diffrence between Extents and Page

What is the Diffrence between temp table and table variable

What is SQL injection

Specify the Tips when Optimizing Sql Server 2005 Query

What is the difference between UNION ALL Statement and UNION

What are the different types of Locks

What is Write ahead log?

How to get which Process is Blocked in SQL SERVER

What is SQL Server English Query?

What is XPath?

What is the STUFF and how does it differ from the REPLACE function?

Physical Data structures of SQL Server

2009-07-28T20:39:00.002+06:00

SQL Server data is organized into files. When we create a database,
we will identify the names and locations of these files. Creating a database requires the
use of two types of files: data files and transaction log files. A database can have multiple data
files and transaction log files, and it is required to have at least one of each.
Data Files Data files store all of the information in the database including data values,
indexes, and even configuration data and programmatic objects such as stored procedures
and functions. The data files are, therefore, the main repository in the database.
Transaction Log Files Log files provide a durable record of all modifications to the database.
SQL Server uses a write-ahead modification process that requires all changes to be
written to transaction logs before they are written to the database. These logs enable a variety
of data recovery and integrity features.

To allow SQL Server to locate any data value quickly and efficiently, these data files must be
organized in a way that gives the SQL Server data engine full control of the specific placement
of any data value. SQL Server accomplishes this by organizing data file structures into 8KB
blocks called pages. It means that SQL Server databases have 128 pages per megabyte. Each page begins with a 96-byte header that is used to store system information about the page. This information includes the page number, page type, the amount of free space on the page, and the allocation unit ID of the object that owns the page.
Data rows are put on the page serially, starting immediately after the header. A row offset table starts at the end of the page, and each row offset table contains one entry for each row on the page. Each entry records how far the first byte of the row is from the start of the page. The entries in the row offset table are in reverse sequence from the sequence of the rows on the page.

Large Row Support
Rows cannot span pages, however portions of the row may be moved off the row's page so that the row can actually be very large. The maximum amount of data and overhead that is contained in a single row on a page is 8,060 bytes (8 KB). However, this does not include the data stored in the Text/Image page type. This restriction is relaxed for tables that contain varchar, nvarchar, varbinary, or sql_variant columns.

Data Pages Data pages store all data values except those typed as large value types such
as text, ntext, xml, varchar(max), etc. The majority of pages in a typical database will be
data pages.
Index Pages Index pages store index entries for both clustered and nonclustered indexes.
These entries typically consist of the index key values themselves plus additional information
used by SQL Server to locate data entries and manage the index process.
Large Object Pages Because SQL Server data rows cannot span multiple data pages, large data
values must be stored in alternative locations in cases where the data value is too large for the
data page. Datatypes such as text, ntext, xml, varchar(max), etc. will typically store a small
pointer on the data page that references the large object page where the data entry begins.
Some datatypes such as text and varchar(max) will always be stored in large object pages.
Others, such as varchar and varbinary, will be moved to these pages dynamically by SQL
Server when the data row size exceeds 8KB. If, in the future, the variable length columns are
edited and the size of the data row falls within the 8KB limit again, the data will be moved
back to the data page.
Other Pages Additionally, SQL Server stores other configuration information in special
pages designed specifically for those configurations. These special page types include the
Global Application Map, Index Application Map, Bulk Changed Map, and others.

Extents
Pages are further organized into 64KB blocks called extents. An extent is eight physically contiguous pages. This means SQL Server databases have 16 extents per megabyte. Typically, pages
in a data file are allocated specifically for only one purpose. For example, you will not see data
values and index entries in the same data page. SQL Server has many kinds of pages.

To make its space allocation efficient, SQL Server does not allocate whole extents to tables with small amounts of data. SQL Server has two types of extents:
Uniform extents are owned by a single object; all eight pages in the extent can only be used by the owning object.
Mixed extents are shared by up to eight objects. Each of the eight pages in the extent can be owned by a different object.

A new table or index is generally allocated pages from mixed extents. When the table or index grows to the point that it has eight pages, it then switches to use uniform extents for subsequent allocations. If you create an index on an existing table that has enough rows to generate eight pages in the index, all allocations to the index are in uniform extents.

Difference between Primary Key and Unique Index

2009-07-22T11:03:00.000+06:00

What is the difference between a Primary Key and a Unique Index? Both can be declared on one or more columns, both can be used to enforce foreign keys (if the unique index is on not null column(s)), both can be declared as clustered/non clustered indexes (SQL Server lingo), both can be used on computed columns as well (SQL Server).

The differences between the two are:

Column(s) that make the Primary Key of a table cannot be NULL since by definition, the Primary Key cannot be NULL since it helps uniquely identify the record in the table. The column(s) that make up the unique index can be nullable. A note worth mentioning over here is that different RDBMS treat this differently –> while SQL Server and DB2 do not allow more than one NULL value in a unique index column, Oracle allows multiple NULL values. That is one of the things to look out for when designing/developing/porting applications across RDBMS.
There can be only one Primary Key defined on the table where as you can have many unique indexes defined on the table (if needed).
Also, in the case of SQL Server, if you go with the default options then a Primary Key is created as a clustered index while the unique index (constraint) is created as a non-clustered index. This is just the default behavior though and can be changed at creation time, if needed.

So, if the unique index is defined on not null column(s), then it is essentially the same as the Primary Key and can be treated as an alternate key meaning it can also serve the purpose of identifying a record uniquely in the table.

Sql Server and Database Posts

2009-07-21T19:39:00.000+06:00

Post Sql Server and Database related posts here.