Posted in Stored Procedure

sp_helpconstraint Stored Procedure in SQL Server

Hi friends, today in SQL Server system stored procedure you will learn how to get information about constraints defined on a table using stored procedure sp_helpconstraint.

Sp_helpconstraint stored procedure lists all constraints defined on a table along with their user_defined or system-supplied name. It also lists all columns on which constraints are defined and expressions defined for DEFAULT and CHECK constraints.

Syntax

sp_helpconstraint [ @objname = ] ‘table’

[ , [ @nomsg = ] ‘no_message’ ]

Arguments

@objname is the table name on which constraints are defined having datatype nvarchar(776) with no default value.

@nomsg is an optional parameter that’s prints the table name.

Now, let’s create a table Person defining some constraints on it:

CREATE TABLE dbo.Person

(

PersonId INT NOT NULL,

PersonName VARCHAR(25) NOT NULL,

Country VARCHAR(10),

DateAdded DATETIME CONSTRAINT DF_DateAdded DEFAULT GETDATE(),

CONSTRAINT PK_Person_PersonID PRIMARY KEY CLUSTERED (PersonId)

)

In table Person I have created two constraints DEFAULT on column DateAdded and PRIMARY KEY on column PersonId.

Let’s execute the execute procedure –

EXEC sp_helpconstraint ‘Person’

sp_helpconstraint

Sp_helpconstraint returns result set that includes following columns:

Constraint_Type – It shows the type of constraint (PRIMARY KEY, FOREIGN KEY, DEFAULT, CHECK, so on) defined on column.

Constraint_Name – It shows unique user_defined or system-supplied constraint name of a column.

Delete_action – It is applicable for FOREIGN KEY constraint with either cascade or No Action. It is cascade only when a FOREIGN KEY column has ON DELETE CASCADE rule.

Update_Action – It is applicable to FOREIGN KEY constraint with either cascade or No Action. It is cascade only when a FOREIGN KEY column has ON UPDATE CASCADE rule.

Status_Enabled – It indicates whether FOREGIN KEY or CHECK constraint is enabled or not.

Status_For_Replication – It indicates whether FOREIGN KEY or CHECK constraint is to be enforced during replication or not.

Constraint_Keys – It shows expression defined for CHECK and DEFAULT constraint, column name for other constraint.

That’s all folks for the day, I will continue with other system stored procedure in next blog.

Hope you like this post.

Advertisements
Posted in Stored Procedure

Drop extended property using sp_dropextendedproperty in SQL Server

Hi friends today we will continue with other extended properties related stored procedure sp_dropextendedproperty.

Sp_dropextendedproperty is used to drop an existing extended property.

Syntax of sp_dropextendedproperty is as follows:

sp_dropextendedproperty

[ @name = ] { ‘property_name’ }

[ , [ @level0type = ] { ‘level0_object_type’ }

, [ @level0name = ] { ‘level0_object_name’ }

[ , [ @level1type = ] { ‘level1_object_type’ }

, [ @level1name = ] { ‘level1_object_name’ }

[ , [ @level2type = ] { ‘level2_object_type’ }

, [ @level2name = ] { ‘level2_object_name’ }

]  ]  ]    ]

Arguments

@name is name of the property to be dropped and it cannot be NULL.

@level0type is type of level 0 objects having datatype varchar(128) with a default of NULL. Valid inputs for level0type are ASSEMBLY, CONTRACT, EVENT NOTIFICATION, FILEGROUP, MESSAGE TYPE, PARTITION FUNCTION, PARTITION SCHEME, REMOTE SERVICE BINDING, ROUTE, SCHEMA, SERVICE, USER, TRIGGER, TYPE.

@level0name is the name of level0type object.

@level1type is type of level 1 objects having datatype varchar(128) with a default of NULL.  Valid inputs for level1type are AGGREGATE, DEFAULT, FUNCTION, LOGICAL FILE NAME, PROCEDURE, QUEUE, RULE, SYNONYM, TABLE, TABLE_TYPE, TYPE, VIEW, XML SCHEMA COLLECTION.

@level1name is the name of level1type object.

@level2type is the type of level 2 objects having datatype varchar(128) with a default of NULL. Valid inputs for level2type are COLUMN, CONSTRAINT, EVENT NOTIFICATION, INDEX, PARAMETER, TRIGGER.

@level2name is the name of level2type object.

Now we will drop the extended property of table books which we added in our. article of sp_addextendedproperty here.

–Drop extended property of a table

EXEC sp_dropextendedproperty

@name = ‘caption’,

@level0type = ‘Schema’,

@level0name = ‘dbo’,

@level1type = ‘Table’,

@level1name = ‘books’

sp_dropextendprop1

Now when we checked in extended properties of table books we didn’t find any extended properties as we have already dropped that.

sp_dropextendprop2

Similarly we can drop extended property of database and columns.

Hope you like this post.

Posted in Stored Procedure

How to Update extended property using sp_updateextendedproperty in SQL Server

Hi friends, today in this blog we will discuss how to updated the extended property.

Sp_updateextendedproperty stored procedure is used to update the value of existing extended property.

Syntax of sp_updateextendedproperty is as follows:

sp_updateextendedproperty

[ @name = ]{ ‘property_name’ }

[ , [ @value = ]{ ‘value’ }

[, [ @level0type = ]{ ‘level0_object_type’ }

, [ @level0name = ]{ ‘level0_object_name’ }

[, [ @level1type = ]{ ‘level1_object_type’ }

, [ @level1name = ]{ ‘level1_object_name’ }

[, [ @level2type = ]{ ‘level2_object_type’ }

, [ @level2name = ]{ ‘level2_object_name’ }

]

]

]

]

Arguments

@name is name of the property and it cannot be NULL.

@value is the value associated with the property. The size of the value cannot be exceed than 7500 bytes.

@level0type is type of level 0 objects having datatype varchar(128) with a default of NULL. Valid inputs for level0type are ASSEMBLY, CONTRACT, EVENT NOTIFICATION, FILEGROUP, MESSAGE TYPE, PARTITION FUNCTION, PARTITION SCHEME, REMOTE SERVICE BINDING, ROUTE, SCHEMA, SERVICE, USER, TRIGGER, TYPE, PLAN GUIDE.

@level0name is the name of level0type object.

@level1type is type of level 1 objects having datatype varchar(128) with a default of NULL.  Valid inputs for level1type are AGGREGATE, DEFAULT, FUNCTION, LOGICAL FILE NAME, PROCEDURE, QUEUE, RULE, SYNONYM, TABLE, TABLE_TYPE, TYPE, VIEW, XML SCHEMA COLLECTION.

@level1name is the name of level1type object.

@level2type is the type of level 2 objects having datatype varchar(128) with a default of NULL. Valid inputs for level2type are COLUMN, CONSTRAINT, EVENT NOTIFICATION, INDEX, PARAMETER, TRIGGER.

@level2name is the name of level2type object.

Now we will modify the extended property of table books which we added in our previous article of sp_addextendedproperty here.

–Modify extended property of a table

EXEC sp_updateextendedproperty

@name = ‘caption’,

@value = ‘This table contains information about books placed in a library’,

@level0type = ‘Schema’,

@level0name = ‘dbo’,

@level1type = ‘Table’,

@level1name = ‘books’

sp_updateextendprop1

Now when we checked in extended properties of table books we will find the updated value.

sp_updateextendprop2

Similarly we can update extended property to database and column as:

–Updating extended property to database

EXEC sp_updateextendedproperty

@name = ‘caption’,

@value = ‘This is library database’

 

–Updating extended property to column

EXEC sp_updateextendedproperty

@name = ‘caption’,

@value = ‘Contains name of books’,

@level0type = ‘Schema’,

@level0name = ‘dbo’,

@level1type = ‘Table’,

@level1name = ‘books’,

@level2type = ‘Column’,

@level2name = ‘Name’

We will see another stored procedure of extended properties in our next blog in which we will see how to drop extended properties.

Posted in Stored Procedure

How to add extended property using Sp_addextendedproperty Stored Procedure in SQL Server

Hi friends, in this blog you will learn about sp_addextendedproperty in sql server.

Sp_addextendedproperty stored procedure is used to create metadata about our database objects. Using this stored procedure we can put information about Sql server objects like name of developer who created the object, to track the version of objects inside database.

Extended properties are not applicable on system objects, objects outside the scope of user defined function.

Syntax of sp_addextendedproperty is as follows:

sp_addextendedproperty

    [ @name = ] { ‘property_name’ }

    [ , [ @value = ] { ‘value’ }

        [ , [ @level0type = ] { ‘level0_object_type’ }

          , [ @level0name = ] { ‘level0_object_name’ }

                [ , [ @level1type = ] { ‘level1_object_type’ }

                  , [ @level1name = ] { ‘level1_object_name’ }

                        [ , [ @level2type = ] { ‘level2_object_type’ }

                          , [ @level2name = ] { ‘level2_object_name’ }

                        ]                 ]

        ]

    ]

[;]

Arguments

@name is name of the property and it cannot be NULL.

@value is the value associated with the property. The size of the value cannot be exceed than 7500 bytes.

@level0type is type of level 0 objects having datatype varchar(128) with a default of NULL. Valid inputs for level0type are ASSEMBLY, CONTRACT, EVENT NOTIFICATION, FILEGROUP, MESSAGE TYPE, PARTITION FUNCTION, PARTITION SCHEME, REMOTE SERVICE BINDING, ROUTE, SCHEMA, SERVICE, USER, TRIGGER, TYPE, PLAN GUIDE.

@level0name is the name of level0type object.

@level1type is type of level 1 objects having datatype varchar(128) with a default of NULL.  Valid inputs for level1type are AGGREGATE, DEFAULT, FUNCTION, LOGICAL FILE NAME, PROCEDURE, QUEUE, RULE, SYNONYM, TABLE, TABLE_TYPE, TYPE, VIEW, XML SCHEMA COLLECTION.

@level1name is the name of level1type object.

@level2type is the type of level 1 objects having datatype varchar(128) with a default of NULL. Valid inputs for level2type are COLUMN, CONSTRAINT, EVENT NOTIFICATION, INDEX, PARAMETER, TRIGGER.

Let’s take an example and add extended property to a table.

I have created a sample table book for this whose definition is as follows:

CREATE TABLE books

(

ID INT,

Name VARCHAR(15)

)

 –Adding extended property to table

EXEC sp_addextendedproperty

@name = ‘caption’,

@value = ‘This table stores information about books’,

@level0type = ‘Schema’,

@level0name = ‘dbo’,

@level1type = ‘Table’,

@level1name = ‘books’

sp_addextendprop1

After executing stored procedure we can check that extended property using SSMS.

Right Click to tablename -> Select Properties -> Select Extended Properties

sp_addextendprop2

Similarly we can add extended property to database and column as:

–Adding extended property to database

EXEC sp_addextendedproperty

@name = ‘caption’,

@value = ‘This database stores information about library’

 –Adding extended property to column

EXEC sp_addextendedproperty

@name = ‘caption’,

@value = ‘This column stores information about name of books’,

@level0type = ‘Schema’,

@level0name = ‘dbo’,

@level1type = ‘Table’,

@level1name = ‘books’,

@level2type = ‘Column’,

@level2name = ‘Name’

We will continue with other stored procedures of extended properties in next part of this series.

Hope you like this post.

Posted in Stored Procedure

Find blocking using sp_who in SQL Server

Hi friends, in this blog I will tell you how can we find blocking using sp_who system stored procedure. Also, we will learn about sp_who stored procedure.

Sp_who stored procedure returns information about current SQL server processes, users and sessions.

There is another stored procedure sp_who2 which is undocumented stored procedure. Sp_who2provides more comprehensive information in comparison to sp_who.

Syntax of both stored procedure is as follows:

Sp_who [ [@loginame] = ‘login’ | session Id |’ ACTIVE’]

Sp_who2 [[@loginname] = ‘login’ | session Id |’ACTIVE’]

Arguments

Login name identifies process belonged to a particular user login.

Session represents the session ID of the connection.

ACTIVE includes the current user login session.

Sp_who stored procedure return the following:

EXEC sp_who

Sp_who

Column Name Description

  1. Spid – Server process Id. It represents the session Id of the connection.
  2. Ecid – Execution Context Id. It represents what thread the process was executed on. 0 indicates that the process was executed on main thread.
  3. Status – It represents the status of the session. Possible status values are:

Running – It indicates session is performing some work.

Runnable – The session has performed some work but currently has no work to perform.

Sleeping – It indicates session is waiting to perform work.

Background – It indicates session is performing some background tasks.

Suspended – It indicates that session is waiting for an event to complete.

Dormant – It indicates session is being reset by server.

Rollback – It indicates session is currently rolling back a transaction.

Pending – It indicates that session is waiting on an available thread.

Spinloop – It indicates that session is waiting on a spinloop to become free.

  1. LoginName – It represents login associated with the session.
  2. Hostname – It represents hostname with the session.
  3. Blk – It represents session id for blocking process.
  4. Dbname – It represents database name connected to a session.
  5. Cmd – It represents type of command executing on the session.
  6. Request_Id – It represents Id of the request running in the session.

 Now let’s see how we can use sp_who stored procedure to identify blocking queries.

For this blocking demo, I have created a demo table in my database called ‘Customer’

CREATE TABLE [dbo].[Customer](

[CustomerID] [int] IDENTITY(1,1) NOT NULL,

[CustomerName] [varchar](50) NOT NULL,

[CEO] [varchar](40) NULL,

[Phone] [varchar](20) NOT NULL,

PRIMARY KEY CLUSTERED

( [CustomerID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Now I will start a transaction and leave it open without committing or rollback.

BEGIN TRAN

INSERT INTO customer(CustomerName, ceo, phone)

VALUES(‘ABC’, ‘AA’,’111223′)

Now I will execute this query in another window:

SELECT * FROM customer

Now, execute the stored procedure

EXEC sp_who ‘sa’

sp_who1

As, you can see in the picture that in column blk we have value other than 0 which tells that blocked id is  59 and blocked by spid 56.

Now to resolve this blocking either I can kill the connection, executes rollback or commit transaction and it will resolve.

Thats all folks in this post. Hope you will like it.

Posted in Uncategorized

How index gets dropped in Views in SQL Server

Hi Friends,

In this blog I am going to tell about dropping of indexes in view in SQL Server.

Views are virtual tables in which data comprised from one or more tables and gives the result set as our SQL table does with rows and columns. A view doesn’t store the data permanently in the database and at the time of execution only its result set get determined.

When a view contains large amount of rows, complex logic in it then we can create an index on a view to improve the query performance. A view consists of a Unique Clustered Index and it is stored in database as clustered index does.

Now, let’s run few scenarios to check when Clustered index which is created on a view gets dropped automatically.

First I will create a table on which I will run those scenarios:

CREATE TABLE [dbo].[Customer]

(

[CustomerID] [int] IDENTITY(1,1) NOT NULL,

[CustomerName] [varchar](50) NOT NULL,

[CEO] [varchar](40) NULL,

[Phone] [varchar](20) NOT NULL

PRIMARY KEY CLUSTERED

(

[CustomerID] ASC

)

)

GO

Now, I will create a view which will use this Customer table:

— Create view

Create VIEW vw_customer

 WITH SCHEMABINDING

AS

 SELECT  CustomerID, CustomerName, CEO

  from dbo.Customer

  GO

Here in definition of view I have used WITH SCHEMABINDING which is necessary for creating index on a view. This option simulates that we cannot delete any of the base table used in view, in order to make any changes first we need to drop or alter the view.

Also, all the tables references in a view should have two part naming convention (schemaname.tablename) as we have in vw_Customer view (dbo.Customer).

Now, I will create an index on our view:

Scenario 1

Create index IX_CustomerID

 ON vw_customer (CustomerID);

 GO

Error returned: Cannot create index on view ‘vw_customer’. It does not have a unique clustered index.

On views, the first index must be a unique clustered index, so this will throw the error.

Error_Msg1

So, first index on view should be UNIQUE CLUSTERD INDEX else it will throw an error.

Scenario 2

Create unique clustered index IX_CustomerID

 ON vw_customer (CustomerID)

 GO

Now our Indexed view is created having clustered index on it.

Now, I want to alter my view and add one more column GETDATE() as CurrentDate  in view definition and alter the view.

Now alter the view after Scenario 2

Scenario 3

ALTER  VIEW vw_customer

 WITH SCHEMABINDING

AS

 SELECT  CustomerID, CustomerName , GETDATE() as CurrentDate

  from dbo.Customer

GO

Now, as I have altered the view, I want to create another index on column CustomerName which will be a NonClustered index.

Scenario 4

Create index IX_CustomerName

 ON vw_customer (CustomerName);

GO

Again, I get an error: Cannot create index on view ‘vw_customer’. It does not have a unique clustered index.

Error_Msg1

As we have already created Unique Clustered Index on view still its gives an error.

The interesting thing is that after updating the view, the index that was created in Scenario 2 is dropped and the code will throw the same error as in Scenario 1.

As mentioned in bol here:

“ALTER VIEW can be applied to indexed views; however, ALTER VIEW unconditionally drops all indexes on the view.”

Hope you will like this post.

Regards,

Kapil Singh

Follow me on Twitter

Posted in Uncategorized

How CONCAT_NULL_YIELDS_NULL behave with computed columns In Sql Server

Hi friends, in this blog I am going to tell you about effect of CONCAT_NULL_YIELDS_NULL for computed columns while creating index in Sql Server.

What this function does is when it set to ON it will result in NULL values when any value using + operator is NULL, while when CONCAT_NULL_YIELDS_NULL set to OFF it will return original value instead of NULL.

Note: As per the Microsoft document CONCAT_NULL_YIELDS_NULL should always be set to ON as value of CONCAT_NULL_YIELDS_NULL to OFF is not going to be supported in later versions

By default, CONCAT_NULL_YIELDS_NULL is always ON.

First we will create a table:

CREATE TABLE PersonalDetails

(

PersonalDetailsId INT,

FirstName NVARCHAR(20),

MiddleName NVARCHAR(20),

LastName NVARCHAR(20),

FullName AS FirstName + LastName

)

Now lets CONCAT_NULL_YIELDS_NULL set to OFF.

SET CONCAT_NULL_YIELDS_NULL OFF

Create an index on computed column FullName:

CREATE INDEX ix_fullname ON PersonalDetails (FullName)

It will throw an error saying that index cannot be created on computed column when CONCAT_NULL_YIELDS_NULL is set to OFF:

img_Concat1

So, CONCAT_NULL_YIELDS_NULL should always be ON when we create index on computed columns.

Now, let’s turn back to the default setting of CONCAT_NULL_YIELDS_NULL to ON

SET CONCAT_NULL_YIELDS_NULL ON

This time the index gets created successfully.

img_Concat2

Hope you like the post.

Regards,

Kapil Singh

Follow me on Twitter