How to use Row_number() to insert consecutive numbers (Identity Values) on a Non-Identity column

Scenario:-

We have database on a vendor supported application in which a Table TestIdt has a integer column col1 which is not specified as identity as below,

 

--Create Table

CREATE TABLE [dbo].[TestIdt]

(

[col1] int NOT NULL,

[col2] [varchar](60) NULL,

[col3] [varchar](60) NULL,

[col4] [varchar](50) NULL,

CONSTRAINT [PK_TestIdt] PRIMARY KEY CLUSTERED

(

[col1] ASC

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

) ON [PRIMARY]

 

--Insert Values to the Table

INSERT INTO TestIdt VALUES (1,'Britan','London','Football')

INSERT INTO TestIdt VALUES (2,'Wales','Cardiff','Football')

INSERT INTO TestIdt VALUES (3,'Scotland','Edinburgh','Football')

INSERT INTO TestIdt VALUES (4,'France','Paris','Football')

INSERT INTO TestIdt VALUES (5,'Ireland','Dublin','Football')

 

Now we need to insert values of another table(subsetTbl as below) to TestIdt with col1 on TestIdt need to be increaed by 1 with each row in the subset, In short Col1 should be populated like an identity column.

 

CREATE TABLE [dbo].[subsetTbl]

(

[subcol2] [varchar](60) NULL,

[subcol3] [varchar](60) NULL,

[subcol4] [varchar](50) NULL,

) ON [PRIMARY]

 

--Insert Values to the Table

INSERT INTO subsetTbl VALUES ('Germany','Berlin','Tennis')

INSERT INTO subsetTbl VALUES ('Swiss','Bern','Tennis')

INSERT INTO subsetTbl VALUES ('Italy','Rome','Tennis')

INSERT INTO subsetTbl VALUES ('Belgium','Brussels','Tennis')

INSERT INTO subsetTbl VALUES ('Spain','Madrid','Tennis')

 

Let us see how we can Accomplish this task, One way of doing it is using SQL cursors which looks fine till we have small tables but with large tables and inserts cursor has its own disadvantages of using more resources and

more locking on the table. With the introduction of OVER clause on SQL 2005 we can accomplish this task pretty easily using rownumber function as below,

 

DECLARE @cnt int

SELECT @cnt = MAX(col1) from TestIdt

    INSERT INTO TestIdt

    select @cnt + (row_number() OVER (ORDER BY subcol2 ASC)) As Col1,subcol2,subcol3,subcol4 from subsetTbl

 

col1	col2	col3	col4
1	Britan	London	Football
2	Wales	Cardiff	Football
3	Scotland	Edinburgh	Football
4	France	Paris	Football
5	Ireland	Dublin	Football
6	Belgium	Brussels	Tennis
7	Germany	Berlin	Tennis
8	Italy	Rome	Tennis
9	Spain	Madrid	Tennis
10	Swiss	Bern	Tennis

 

Now that we have inserted subsetTbl values to TestIdt with Col1 values incremented by 1, We can specify the start value as we wish, Let say I want to start with 100 for next insert We can do it as follows,

 

DECLARE @cnt int

SET @cnt = 99

    INSERT INTO TestIdt

    select @cnt + (row_number() OVER (ORDER BY subcol2 ASC)) As Col1,subcol2,subcol3,subcol4 from subsetTbl

 

col1	col2	col3	col4
1	Britan	London	Football
2	Wales	Cardiff	Football
3	Scotland	Edinburgh	Football
4	France	Paris	Football
5	Ireland	Dublin	Football
6	Belgium	Brussels	Tennis
7	Germany	Berlin	Tennis
8	Italy	Rome	Tennis
9	Spain	Madrid	Tennis
10	Swiss	Bern	Tennis
100	Belgium	Brussels	Tennis
101	Germany	Berlin	Tennis
102	Italy	Rome	Tennis
103	Spain	Madrid	Tennis
104	Swiss	Bern	Tennis

 

We have eventually created a Identity Insert for a Non-Identity Column.

Copyright © 2012 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.

Changing Database context inside store procedure

When I was developing a code today  I landed in a situation to create a store proc on database DB1 which inturn needs to create user and schema on database DB2  and also grant roles on DB2, with the below code

Since we know USE DB statement is not allowed in a store procedure, I decided to use as below and tried to execute the query,

 

set @sql = 'USE DB2;CREATE USER ['+@sName + '] FOR LOGIN ['+ @sName +']'

exec(@sql)

--give the user DB_reader/db_dbwriter rights

set @sql = 'USE DB2;sp_addrolemember ''db_datareader'', '+ @sName +''

exec(@sql)

set @sql = 'USE DB2;sp_addrolemember ''db_datawriter'', '+ @sName +''

exec(@sql)

-- create the schema

set @sql = 'USE DB2;CREATE SCHEMA '+ @sName +' AUTHORIZATION '+ @sName

exec(@sql)

-- set the user schema to be the new schema

set @sql = 'USE DB2;ALTER USER ['+ @sName +' ] WITH DEFAULT_SCHEMA=[' + @sName + ']'

exec(@sql)

 

which resulted with the following error,

 

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near 'sp_addrolemember'.

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near 'sp_addrolemember'.

Msg 111, Level 15, State 1, Line 1

'CREATE SCHEMA' must be the first statement in a query batch.

 

So I was not able to use USE DB2 in EXEC statement too, In SQL 2012 I can use

 

USE DB2;ALTER ROLE db_datareader ADD MEMBER test

USE DB2;ALTER ROLE db_datawriter ADD MEMBER test

 

which will resolve the sp_addrolemember syntax error, but CREATE SCHEMA error will not resolve as the statement will need to be the first statement in the query batch. Finally after considering various options this is how I overcame the issue,

 

1. create a Store proc usp_createuser on DB2 with no DB context with the above code.

2. Added the below code to the parent store procedure usp_security

   Exec DB2.dbo.usp_createuser @parameter1,@parameter2

 

which resolved my issue, I am fine with this resolution as of now but we have system with 200 Databases and I don't want to create 200 SP to overcome this problem, I shall blog if I find a better answer to this.

SQL Server Memory Allocation and Monitoring - Part 2

In the part 1 we saw how SQL server memory was allocated at SQL server start up, in this article we will see how to find out what is the memory assigned to the different parts of memory discussed in earlier article.

Most of the scripts used in this article are taken from MSDN white paper "Troubleshooting performance problems in sql server 2008" written by Sunil Agarwal, Boris Baryshnikov, Keith Elmore, Juergen Thomas, Kun Cheng and  Burzin Patel (http://msdn.microsoft.com/en-us/library/dd672789(v=sql.100).aspx). I have merely used these scripts to explain on SQL Server Memory.

1. To find total size of the physical memory on the Server:-  

select total_physical_memory_kb/1024/1024 AS [total_physical_memory_GB] from sys.dm_os_sys_memory

total_physical_memory_GB

------------------------

63

(1 row(s) affected)

2. To Find Total Memory allocated to SQL Server :-

select "Max_Memory" =

CASE

WHEN a.value = 2147483647 THEN (select total_physical_memory_kb/1024/1024 AS [total_physical_memory_GB] fromsys.dm_os_sys_memory)

ELSE a.value

END

from sys.configurations a where name = 'max server memory (MB)'

Max_Memory

------------------

47344

(1 row(s) affected)

3. To Find the total memory allocated to Buffer Pool:-

a. 64- bit SQL:-

        

select SUM(awe_allocated_kb)/1024/1024 from sys.dm_os_memory_clerks where type = 'MEMORYCLERK_SQLBUFFERPOOL'

--------------------

46

(1 row(s) affected)

b. 32 Bit Awe Enable:- To find if Awe is enable run select value from sys.configurations where name = 'awe enabled', The Value 1 indicates AWE is enabled.

select SUM(awe_allocated_kb)/1024/1024 from sys.dm_os_memory_clerks where type = 'MEMORYCLERK_SQLBUFFERPOOL'

--------------------

21

(1 row(s) affected)

c. 32 bit AWE not allocated:-

I could not find exact query but the below query will give you the almost the the correct value, 

select SUM(virtual_memory_reserved_kb)/1024 from sys.dm_os_memory_clerks where type = 'MEMORYCLERK_SQLBUFFERPOOL'

4. To Find Total Memory assigned to the VAS(User Kernel mode):-

The below Query is taken from MSDN white paper "Troubleshooting performance problems in sql server 2008" written by Sunil Agarwal, Boris Baryshnikov, Keith Elmore, Juergen Thomas, Kun Cheng and  Burzin Patel (http://msdn.microsoft.com/en-us/library/dd672789(v=sql.100).aspx) and Christian Bolton's Blog http://sqlblogcasts.com/blogs/christian/archive/2008/01/07/sql-server-memtoleave-vas-and-64-bit.aspx

WITH VASummary(Size,Reserved,Free) AS

(SELECT

Size = VaDump.Size,

Reserved = SUM(CASE(CONVERT(INT, VaDump.Base)^0)

WHEN 0 THEN 0 ELSE 1 END),

Free = SUM(CASE(CONVERT(INT, VaDump.Base)^0)

WHEN 0 THEN 1 ELSE 0 END)

FROM

(

SELECT CONVERT(VARBINARY, SUM(region_size_in_bytes))

AS Size, region_allocation_base_address AS Base

FROM sys.dm_os_virtual_address_dump

WHERE region_allocation_base_address <> 0x0

GROUP BY region_allocation_base_address

UNION

SELECT CONVERT(VARBINARY, region_size_in_bytes), region_allocation_base_address

FROM sys.dm_os_virtual_address_dump

WHERE region_allocation_base_address = 0x0

)

AS VaDump

GROUP BY Size)

SELECT SUM(CONVERT(BIGINT,Size)*Free)/1024/1024 AS [Total avail mem, MB] ,CAST(MAX(Size) AS BIGINT)/1024/1024 AS [Max free size, MB]

FROM VASummary

WHERE Free <> 0

Result:-

Total avail mem, MB Max free size, MB

-------------------- --------------------

288                         84

(1 row(s) affected)

We have seen how to find the total memory allocated to the in each section, now we can discuss how to find the amount of memory used in each parts of SQL memory.

Before SQL 2005 we used DBCC MEMORYSTATUS output to find the memory components used space, post SQL 2005 we can get all the info from OS DMV sys.dm_os_memory_clerks.

As we discussed in PART 1 MultiPage allocation happens in the VAS Area and Single page allocation comes from Buffer Pool, we can calculate the space used in Bpool and outside Bpool with the below query,

The below Query is taken from MSDN white paper "Troubleshooting performance problems in sql server 2008" written by Sunil Agarwal, Boris Baryshnikov, Keith Elmore, Juergen Thomas, Kun Cheng and  Burzin Patel (http://msdn.microsoft.com/en-us/library/dd672789(v=sql.100).aspx)

-- amount of memory consumed by components outside the bBuffer pool

-- note that we exclude single_pages_kb as they come from BPool

select

    sum(multi_pages_kb

        + virtual_memory_committed_kb

        + shared_memory_committed_kb) as [Overall used w/o BPool, Kb]

from

    sys.dm_os_memory_clerks

where

    type <> 'MEMORYCLERK_SQLBUFFERPOOL'

-- amount of memory consumed by BPool

-- note that currenlty only BPool uses AWE

select

    sum(multi_pages_kb

        + virtual_memory_committed_kb

        + shared_memory_committed_kb

        + awe_allocated_kb) as [Used by BPool with AWE, Kb]

from

    sys.dm_os_memory_clerks

where

    type = 'MEMORYCLERK_SQLBUFFERPOOL'

We can also use Ring buffers to find memory used, we can discuss this in a separate article as i am planning write a blog on RING BUFFERS and extended events.

INSERT INTO + CTE SELECT

Today I was doing a development where I wanted to insert a output of a CTE to a temp variable,

normal INSERT T-SQL syntax wont work as CTE needs a semi-colon before the start and will give the below error,

 

Msg 336, Level 15, State 1, Line 11

Incorrect syntax near 'grp_opbx'. If this is intended to be a common table expression, you need to explicitly terminate the previous statement with a semi-colon.

 

Here is the right syntax,

 

DECLARE @tmptbl TABLE (tmp_col1 int,tmp_col2 int);

WITH Testcte( col1, col2) AS

(

SELECT a.samplecol,b.samplecol2 from testdb1.dbo.testfunction(arg1) AS i

JOIN

testdb2.dbo.tbl123 AS b ON a.check1 = b.check2

)

INSERT INTO @tmptbl

SELECT col1, col2 from Testcte

--Check the result

SELECT * from @tmptbl

Monitoring using EVENTDATA() Function

One of most under utilized function by DBA for monitoring SQL server is EVENTDATA() function, With Evendata function you can monitor many database and instance level changes at a granular level.

The only drawback is Eventdata functions can be used only within a DDL trigger or Logon trigger.

Let us see some examples how we can use Evendata() function,

I am going to use DDL_SERVER_LEVEL_EVENTS and DDL_DATABASE_LEVEL_EVENTS event group in a trigger to capture some simple DDL events at server level and database level,

1. DDL_Server_LEVEL_Events:-

CREATE TRIGGER Eventtest

ON ALL SERVER

FOR DDL_SERVER_LEVEL_EVENTS

AS

DECLARE @result XML

SET @result = EVENTDATA()

SELECT

GETDATE() As Time,

@result.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(20)') As EventType,

@result.value('(/EVENT_INSTANCE/SPID)[1]', 'nvarchar(20)') As SPID,

@result.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(20)') As LoginName,

@result.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(300)') As TSQL,

@result.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(20)') As ServerName,

@result.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(20)') As DatabaseName

GO

CREATE DATABASE TEST

GO

ALTER DATABASE TEST SET RECOVERY SIMPLE

GO

CREATE LOGIN [Test] WITH PASSWORD='test',CHECK_POLICY=OFF

GO

DROP LOGIN [Test]

GO

DROP DATABASE TEST

GO

DROP TRIGGER Eventtest

ON ALL SERVER;

GO

Result:-

Time
EventType
SPID
LoginName
TSQL
ServerName
DatabaseName
2012-08-20 17:45:54.927
CREATE_DATABASE
135
Test\Vinoth
CREATE DATABASE TEST   
T01\TEST
TEST
2012-08-20 17:45:54.997
ALTER_DATABASE
135
Test\Vinoth
ALTER DATABASE TEST SET RECOVERY SIMPLE  
T01\TEST
TEST
2012-08-20 17:45:55.040
CREATE_LOGIN
135
Test\Vinoth
CREATE LOGIN [Test] WITH PASSWORD='******',CHECK_POLICY=OFF  
T01\TEST
NULL
2012-08-20 17:45:55.097
DROP_LOGIN
135
Test\Vinoth
DROP LOGIN [Test]  
T01\TEST
NULL
2012-08-20 17:45:55.203
DROP_DATABASE
135
Test\Vinoth
DROP DATABASE TEST  
T01\TEST
TEST

2. DDL_DATABASE_LEVEL_EVENTS:-

CREATE TRIGGER Eventtest

ON DATABASE

FOR DDL_DATABASE_LEVEL_EVENTS

AS

DECLARE @result XML

SET @result = EVENTDATA()

SELECT

GETDATE() As Time,

@result.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(20)') As EventType,

@result.value('(/EVENT_INSTANCE/SPID)[1]', 'nvarchar(20)') As SPID,

@result.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(20)') As LoginName,

@result.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(300)') As TSQL,

@result.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(20)') As ServerName,

@result.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(20)') As DatabaseName

GO

CREATE TABLE test1 (col1 int)

GO

ALTER TABLE test1 ADD col2 int

GO

CREATE LOGIN [Test] WITH PASSWORD='test',CHECK_POLICY=OFF

GO

CREATE USER test for login Test

GO

GRANT SELECT ON test1 TO test

GO

DROP DATABASE TEST

GO

DROP TRIGGER Eventtest

ON DATABASE;

GO

Result:-

You can see all server events are ignored and only database events are captured.

Time
EventType
SPID
LoginName
TSQL
ServerName
DatabaseName
2012-08-20 17:59:09.087
CREATE_TABLE
135
Test\Vinoth
CREATE TABLE test1 (col1 int)  
T01\TEST
TEST
2012-08-20 17:59:09.157
ALTER_TABLE
135
Test\Vinoth
ALTER TABLE test1 ADD col2 int  
T01\TEST
TEST
2012-08-20 17:59:09.197
CREATE_USER
135
Test\Vinoth
CREATE USER test for login Test  
T01\TEST
TEST
2012-08-20 17:59:09.230
GRANT_DATABASE
135
Test\Vinoth
GRANT SELECT ON test1 TO test  
T01\TEST
TEST

I have just given a sample of what can be done, the total events which can be captured using even data can be found in http://msdn.microsoft.com/en-us/library/bb510452.aspx ,You can also find all the events supported by event data C:\Program Files\Microsoft SQL Server\110\Tools\Binn\schemas\sqlserver\2006\11\events\events.xsd.