Tag: sqlserver

How to insert explicit values into the identity columns in SQL server

Leave a comment


SET IDENTITY_INSERT [ database. [ owner. ] ] { table } { ON | OFF }




Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table ‘CHECKIDENT’ when IDENTITY_INSERT is set to OFF.

USE [SQLJUNKIESHARE]
GO

CREATE TABLE [dbo].[CHECKIDENT](
[ID] [int] IDENTITY(1,1) NOT NULL,
[date] [date] NULL,
[descp] [varchar](10) NULL
) ON [PRIMARY]

GO

-- How to insert explicit values in identity columns with out altering table

INSERT INTO DBO.CHECKIDENT(ID,descp)

VALUES(1,'CHECK')

-- WHEN SET TO ON WE CAN INSERT EXPLICIT VALUES IN TO THE TABLE

SET IDENTITY_INSERT DBO.CHECKIDENT ON

INSERT INTO DBO.CHECKIDENT(ID,descp)

VALUES(1,'CHECK')

INSERT INTO DBO.CHECKIDENT(ID,descp)

VALUES(1,'CHECK')

SELECT * FROM DBO.CHECKIDENT

SET IDENTITY_INSERT DBO.CHECKIDENT OFF

Advertisement

How find the last inserted identity value or values in the SQL server

Leave a comment

CREATE TABLE [dbo].[CHECKIDENT](
[ID] [int] IDENTITY(1,1) NOT NULL,
[date] [date] NULL,
[descp] [varchar](10) NULL
) ON [PRIMARY]

GO

INSERT INTO DBO.CHECKIDENT(DESCP)

VALUES (‘CHECK’)

SELECT @@IDENTITY AS ‘LASTINSERTED IDENTITY VALUE’

INSERT INTO DBO.CHECKIDENT(DESCP)

VALUES (‘CHECK’),
(‘CHECK1’)

SELECT @@IDENTITY AS ‘LASTINSERTED IDENTITY VALUE’

–OR

— find all identity values insereted in the previous satement

declare @t table (ID int)

INSERT INTO DBO.CHECKIDENT(DESCP)

OUTPUT inserted.ID into @t(ID)

VALUES (‘CHECK3’),
(‘CHECK4’)

select * from @t

Set Operators : Except and Intersect -Tsql

Leave a comment

SQL Server provides the following set operators. Set operators combine results from two or more queries into a single result set

1) Intersect and Except

2) Union

Today in this post we will learn Intersect and Except set operators

Returns distinct values by comparing the results of two queries.

EXCEPT returns any distinct values from the left query that are not also found on the right query.

INTERSECT returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand.

The basic rules for combining the result sets of two queries that use EXCEPT or INTERSECT are the following:

  • The number and the order of the columns must be the same in all queries.
  • The data types must be compatible.

 

Lets take a simple example and you will better understand the set operators

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

create table #newproducts(ProductID int, name varchar(20), size varchar(20),price varchar(20))
insert into #newproducts(ProductID,name,size,price)

values(1,’productx’,’small’,20),
(2,’producty’,’small’,40),
(3,’productz’,’large’,50),
(1,’productxy’,’small’,60)
create table #oldproducts(ProductID int, name varchar(20), size varchar(20),price varchar(20))
insert into #oldproducts(ProductID,name,size,price)

values(4,’productq’,’small’,20),
(5,’productk’,’small’,40),
(6,’productz’,’large’,50),
(1,’productxy’,’small’,60),
(1,’productzy’,’small’,80)
select * from #newproducts

select * from #oldproducts
;with oldonly as
(
select * from #oldproducts

except

select * from #newproducts

),

newonly as (select * from #newproducts

except

select * from #oldproducts)
select o.ProductID,o.name as oldname, n.name as newname

from oldonly o inner join newonly n on o.ProductID = n.ProductID

where o.name <> n.name

System Functions – $partition

Leave a comment

Returns the partition number into which a set of partitioning column values would be mapped for any specified partition function.

Syntax

[ database_name. ] $PARTITION.partition_function_name(expression)

It returns a Partition number of column value when mapped to a partition function

this will be useful if you want find the rows in a particular partition

so lets explain this with a simple example

 

create partition function

 

 

 

 

 

 

 

 

 

 

 

now lets create a partitioned table

 

 

 

 

 

insert some sample data

now lets see some examples on $partition function

tempDB contention

1 Comment

What is tempDB contention?

From the outside looking in, tempDB contention may look like any other blocking. There are two types of contention that tends to plague tempDB’s, especially when the tempDB is not configured to best practices (multiple, equally sized data files, located on a dedicated, high-speed drive, etc.). lets focus on latch contention on the allocation pages.

What are allocation pages?

Allocation pages are special pages in the data files that track and mange extent allocations. There are 3 types of allocation pages that can experience contention and bring a server to a slow crawl.

Global Allocation Map (GAM): Tracks which extents have been allocated. There is 1 GAM page for every 4 GB of data file. It is always page 2 in the data file and then repeats every 511,232 pages.

Shared Global Allocation Map (SGAM): Tracks which extents are being used as mixed (shared) extents. There is 1 SGAM page for every 4 GB of data file. It is always page 3 in the data file and then repeats every 511,232 pages.

Page Free Space (PFS): Tracks the allocation status of each page and approximately how much free space it has. There is 1 PFS page for every 1/2 GB of data file. It is always page 1 in the data file and then repeats every 8,088 pages.

Finding Latch Contention on Allocation Pages

You can use the dynamic management view (DMV) sys.dm_os_waiting_tasks to find tasks that are waiting on a resource. Tasks waiting on PageIOLatch or PageLatch wait types are experiencing contention. The resource description points to the page that is experiencing contention, and you can easily parse the resource description to get the page number. Then it’s just a math problem to determine if it is an allocation page.

The Resource Description (sample):

The resource description will be in the form of <database ID>:<file ID>:<page number>. The tempDB is always database ID of 2. A sample resource description may look like 2:3:18070499. We want to focus on the page ID of 18070499.

The formula for determining the page type is as follows:

GAM: (Page ID – 2) % 511232

SGAM: (Page ID – 3) % 511232

PFS: (Page ID – 1) % 8088

If one of these formulas equates to 0, then the contention is on the allocation pages.

The query

 

Select session_id,
    wait_type,
    wait_duration_ms,
    blocking_session_id,
    resource_description,
    ResourceType = Case
        When Cast(Right(resource_description, Len(resource_description)
            - Charindex(':', resource_description, 3)) As Int) - 1 % 8088 = 0 Then 'Is PFS Page'
        When Cast(Right(resource_description, Len(resource_description)
            - Charindex(':', resource_description, 3)) As Int) - 2 % 511232 = 0 Then 'Is GAM Page'
        When Cast(Right(resource_description, Len(resource_description)
            - Charindex(':', resource_description, 3)) As Int) - 3 % 511232 = 0 Then 'Is SGAM Page'
        Else 'Is Not PFS, GAM, or SGAM page'
    End
From sys.dm_os_waiting_tasks
Where wait_type Like 'PAGE%LATCH_%'
And resource_description Like '2:%'
 

Select session_id,wait_type,wait_duration_ms,blocking_session_id,resource_Description,Descr.*

From sys.dm_os_waiting_tasksas waits innerjoinsys.dm_os_buffer_Descriptorsas Descr

on

LEFT(waits.resource_description,Charindex(‘:’, waits.resource_description,0)-1)= Descr.database_id

and

SUBSTRING(waits.resource_description,Charindex(‘:’, waits.resource_description)+1,Charindex(‘:’, waits.resource_description,Charindex(‘:’, resource_description)+1)-(Charindex(‘:’, resource_description)+1))= Descr.[file_id]

and

Right(waits.resource_description,Len(waits.resource_description)–Charindex(‘:’, waits.resource_description, 3))= Descr.[page_id]

Where wait_type Like  ‘PAGE%LATCH_%’

 A modified one with more information

@source SQLsoldier

to know more about GAM,SGAM, PFS 
please use the below links
http://www.sqlskills.com/blogs/paul/post/Inside-The-Storage-Engine-GAM-SGAM-PFS-and-other-allocation-maps.aspx
http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/07/08/under-the-covers-gam-sgam-and-pfs-pages.aspx
http://www.sqlservercentral.com/blogs/livingforsqlserver/2011/02/13/examining-pfs-gam-and-sgam-pages/
http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/06/28/649884.aspx