How to Modify IDENTITY Primary Key Column Data Type

You need three steps to modfiy the identity primary column.

Step 1: drop the table primary key

  

declare @tablename NVARCHAR(256) /* sysname */ = N'dbo.test';
declare @sql NVARCHAR(2000);
SELECT @sql = N'ALTER TABLE ' + @tablename 
    + ' DROP CONSTRAINT ' + name + ';'
    FROM sys.all_objects
    WHERE [type] = 'PK' AND [parent_object_id] = OBJECT_ID(@tablename);

EXEC sp_executeSQL @sql;
 

Step 2: modify the column data type

  
alter table dbo.test
alter  column id bigint

 

Sept 3: Add the primary back to the column

  
alter table dbo.test
add primary key (id)

 

A sample:

  
create table dbo.test (id int identity(1,1) primary key,col int)

declare @tablename NVARCHAR(256) /* sysname */ = N'dbo.test';
declare @sql NVARCHAR(2000);
SELECT @sql = N'ALTER TABLE ' + @tablename 
    + ' DROP CONSTRAINT ' + name + ';'
    FROM sys.all_objects
    WHERE [type] = 'PK' AND [parent_object_id] = OBJECT_ID(@tablename);

EXEC sp_executeSQL @sql;

alter table dbo.test
alter  column id bigint

alter table dbo.test
add primary key (id)

drop table dbo.test
 

Test query for SQL Server Object_id generation

When you create the first object in a new database in SQL Server 2012 or 2014, the first object_id will be assigned as 245575913. The second objectid will be 261575970 (245575913+16000057).
The following objectids will be assign in the incremental of 16000057 until the number reached to the upper limit of int type: 2147483647. When the number is larger than the limit,
it willturn around to use another number wihin the int positive range in this way the overlimit number minus @intmax () -1 and start over again to add 160057 and repeat the cycle.
The first cycle will generate 119 objectids and the next cycle will generate 135 objectids followed by four 134 objects cycles and one 135 cycle and another three 134 cycles…
You can find a test script at the end to find the objectid list generated.
The positive number from 0 t0 98 are belong to system objects’ objectids. The number 2147483647 (over 2 billion) is a huge number.
I cannnot image a database with so many objects ever created in it. Even with a single number table with only two billion numbers is huge.
You can generate a number table with over a million rows in one second or two but it will take hours to generate a number table with 2 billion rows if you do have enough space.

I have tested the process of objectid generated in one database and it took the script days to finish. The objectid will be recycled if the old object got dropped.
It is important that not to use objectid hardcoded in your query.

  

 -- Table of numbers" based on the idea from Itzik Ben-Gan  
;WITH
  t0 as (select 1 as C union all select 1), --2 rows
  t1 as (select 1 as C from t0 as A, t0 as B),--4 rows
  t2 as (select 1 as C from t1 as A, t1 as B),--16 rows
  t3 as (select 1 as C from t2 as A, t2 as B),--256 rows
  t4 as (select 1 as C from t3 as A, t3 as B),--65536 rows
  t5 as (select 1 as C from t4 as A, t2 as B ),-- 1048576  rows
Tally as (select row_number() over(order by C) as Number from t5)

  select * from Tally
  --took 2 seconds.

 

  

-- object_id generated test query

declare @intMax bigint =2147483647,@first bigint=245575913
declare @num int= 400000

  ;with mycte as 
  (
   select 1 as seq,     @first as objectid, 1 as n
   Union all
   Select seq+1 as seq,   Case when objectid+Cast(16000057  as bigint)> @intMax then objectid+ 16000057 -@intMax-1 
   else  objectid+ 16000057   End
   , Case when objectid+Cast(16000057  as bigint)> @intMax then 0 else n+1 end n from mycte 
   WHERE seq<=@num  ---change this for testing

   )

 Select * from mycte

 --, mycte1 as  (
 --  Select seq,objectid,ISNULL(seq-lag(seq)Over(order by seq),seq-1) as intervals from mycte
 --  where n=0
 --  )
 --  ,mycte2 as (
 --  select *, row_number() Over(Order by seq)- row_number() Over(Partition by intervals Order by seq) grp

 --  from mycte1)

 --Select 1 seq,245575913 as objectid, 0 as intervals,1 as cnt
 --  Union all
 --  Select seq,objectid,intervals ,count(*)Over(partition by intervals,  grp) cnt from mycte2
 --  Order by seq

   option (maxrecursion 0)

 

For new database:
The first object’s id in SQL Server 2012, it is 245575913.
COMPATIBILITY_LEVEL: 110
The first object’s id in SQL Server 2014, it is 245575913.
COMPATIBILITY_LEVEL: 120
The first object’s id in SQL Server 2016: 565577053
COMPATIBILITY_LEVEL: 130

—*****
The first object’s id in SQL Server 2017: 901578250
COMPATIBILITY_LEVEL: 140
—*****
—*****
The first object’s id in SQL Server 2019(ctp2.2): 581577110

COMPATIBILITY_LEVEL: 150
—*****

  
---SQL Server 2016
select 245575913 + 16000057*20 -565577053 
--0
 

Maximum precision of numeric and decimal data types: 38

  

---A sample
---decimal(p,s)   make p+s<=38
declare @num1 decimal(24,14)=123.45678
declare @num2 decimal(24,14)=12.99

declare @Result decimal(24,14)

Select 123.45678/12.99

Select Cast(123.45678 as decimal(24,14))/Cast(12.99  as decimal(24,14) )

Select @num1/@num2

---https://msdn.microsoft.com/en-us/library/ms190476(v=sql.110).aspx
--https://msdn.microsoft.com/en-us/library/ms187746.aspx
--http://stackoverflow.com/questions/423925/t-sql-decimal-division-accuracy

 

Batch Replace string in SSIS packages

Define -Encoding Utf8 for Set-Content to make sure all packages will work after the replacement.

$filePath="X:\Packages\*.dtsx"

 
get-childitem -Path $filePath |  
 select -expand fullname |
  foreach {
 $mycontent = Get-Content $_
 
if ($mycontent  -like '*OldString*' )
{
(Get-Content $_) -replace "OldString", "NewString"  | Set-Content $_ -Enc Utf8 }
}
 

 

Undocumented Date and Time Styles

  

 ;with mycte as (
SELECT Try_convert( varchar(30), getdate(),[number]) datetimeOutput, number as style,
len( Try_convert( varchar(30) , getdate(),[number]) ) len_datetimeOutput
,Case when number in (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,20,21
,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,120,121
,126,127,130,131) Then 'Yes' Else 'No' End documentedStyle FROM [master].[dbo].[spt_values]
Where type='P' and Number<128+4

)

Select datetimeOutput, style,len_datetimeOutput,documentedStyle from mycte
Where datetimeOutput is not null and documentedStyle='No'

 

https://msdn.microsoft.com/en-us/library/ms187928%28SQL.105%29.aspx?f=255&MSPPError=-2147217396
 

SQL SERVER 2014– Maximum Allowable Length of Objects’ Name

Edit:
As Tom Cooper pointed out from MSDN forum, the maximum length of both table and column is still 128.

  

DECLARE @name sysname
SELECT @name = REPLICATE(N't', 256)
SELECT LEN(@name)  ---128

 

The table name identifier  in SQL Server is stored in database as sysname. In SQL Server 2014, the length is 256 but in SQL Server 2008 R2  it was 128.

You can name a table name with the length 256 but the temp table it is still limited to 116.

  
 
--table 256
DECLARE @name sysname,@sql nvarchar(300)
SELECT @name = REPLICATE(N't', 256)
SELECT @sql = 'CREATE TABLE '+@name+' (id int)'
PRINT @sql
EXEC(@sql)
--temp table 116
SELECT @name = REPLICATE(N't', 115)
SELECT @sql = 'CREATE TABLE #'+@name+' (id int)'
PRINT @sql
EXEC(@sql)

/* In SQL Server 2014 (may be SQL Server 2012) the maximum length of table and col as 
object name from sysname data type is 256
*/
DECLARE @name1 sysname,@sql1 nvarchar(4000), @col1 sysname
SELECT @name1 = REPLICATE(N't', 256),@col1 = REPLICATE(N'c', 256)
SELECT @sql1 = 'CREATE TABLE ' +@name+ '('+@col1+ ' int)'
PRINT @sql1
EXEC(@sql1)
---–I tested this scrip from SQL Server 2008 R2 and SQL Server 2014 and they are working.
 
 

 

Split with multiple delimiters using numbers table

  

 declare @test table(id int identity(1,1), myString varchar(200) );
insert into @test values('1 
0557178441 - PSTN, 00:02:06
2555123 - 2555123, 00:02:06
 
Connect 
 ')

,('2
557509484 - PSTN, 00:03:19
2007007 - 2007007, 00:03:19
 
Connect 
 ');

 --===== Create number table on-the-fly
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n<101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)

, mycte as ( 
SELECT *,  Replace(replace(Replace( Replace(myString, char(13),'|'),',','|') ,' - ','|'),';','|') strCol
FROM @test

) 
SELECT IDENTITY(int, 1,1) id, strCol,
Substring(strCol , n, charindex('|', strCol  + '|', n) - n)  cols

into mytemp

FROM mycte
Cross apply (Select n from nums) d(n) 
Where n <= len(strCol) AND substring('|' + strCol, n, 1) = '|'

 ;with mycte1 as (
  Select strCol ,Cols
  ,row_number() Over(Partition by strCol Order by id ) rn  
  from mytemp
 )

 Select  Max(Case when rn=1 then cols End) c1 
 , Max(Case when rn=2 then cols End) c2 
 , Max(Case when rn=3 then cols End) c3 
 , Max(Case when rn=4 then cols End) c4 
 , Max(Case when rn=5 then cols End) c5 
 , Max(Case when rn=6 then cols End) c6 
 , Max(Case when rn=7 then cols End) c7 
 , Max(Case when rn=8 then cols End) c8 
 , Max(Case when rn=9 then cols End) c9 
 , Max(Case when rn=10 then cols End) c10 
  , Max(Case when rn=11 then cols End) c11 
 --  , Max(Case when rn=12 then cols End) c12 

FROM mycte1
Group by  strCol

  --clean up
 drop table mytemp

 

https://social.msdn.microsoft.com/Forums/en-US/d955e6f7-9e5f-4340-9965-417133959630/delimited-string-column?forum=transactsql

Export varbinary(max) data from SQL Server to file system with CLR UDF

First, we can create a CLR user-defined function from Visual  Studio and deploy the function to SQL Server.
Here is a link to sample code snippet:

http://stackoverflow.com/questions/10325338/fastest-way-to-export-blobs-from-table-into-individual-files

In SQL Server we can write a simple recursive CTE block to retrieve all files to be exported. The finl code is to use the CLR-UDF in the T_SQL code.

;with mycte as
(
select id, parentid, 0 as lvl, path, isfolder,[name], [externalid]
from samples
where id = 1
–95833
Union all
Select s.id, s.parentid, m.lvl+1 as lvl , s.path, s.isfolder, s.[name],s.[externalid]
from samples s join mycte m
on s.parentid=m.id

)

select dbo.WriteToFile(n.storagevalue ,N’c:\temp\myFolder’ +path, 0)
from mycte m JOIN [dbo].[dataStores] n on m.externalid=n.externalid
This method is super fast compared to using cursor to execute bcp command line by line.

Here is a nice post about CLR from DBA’s view.

http://www.sqlconsulting.com/news1009.htm

Remove number from a column (T-SQL)

  

   CREATE TABLE Contacts
    (
    FirstName NVARCHAR(100),
    LastName NVARCHAR(100),
    MiddleName NVARCHAR(100),
    EmailAddress NVARCHAR(100)
    )

    INSERT INTO Contacts
    VALUES ('Abigail','K','Gonzalez','abigail0@adventure-works.com'),
           ('Michael','NULL','Graff','michael16@adventure-works.com')

--===== Create number table on-the-fly
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n<101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)

,processTable as (
    select  EmailAddress, col  as Number_removed_EmailAddress   
    from Contacts Cross Apply (
            select (select C + ''
            from (select N, substring(EmailAddress, N, 1) C from Nums  
			where N<=datalength(EmailAddress)) t
            where PATINDEX('%[^0-9]%',C)> 0
            order by N
            FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
        ) p0 (col)  

) 
SELECT  EmailAddress,   Number_removed_EmailAddress   FROM processTable

drop table Contacts

 

https://social.msdn.microsoft.com/Forums/en-US/5a43eb63-5f2e-428b-90f6-c2163590ff01/query-help-please?forum=transactsql

The prime number 16000057 in object_id creation

I need to find out where this number  was documented  for the usage  to create object_id  in the table sys.sysschobjs primary key id value.

1. 2013-01-29 (Muhammad Imran): http://raresql.com/2013/01/29/sql-server-all-about-object_id/
2. 2005-05-25 (Smirnov Anton)  http://www.sql.ru/forum/186926/generirovanie-nomera-obekta-v-baze-dannyh
3. 2013-11-07 (Sean Pearce)  http://www.sqlservercentral.com/Forums/Topic1512032-391-1.aspx

  
  --Smirnov Anton

if ( curr_id+16000057>=power(2.,31)  )
curr_id=curr_id-2131483591
else
curr_id=curr_id+16000057 ;