Drop default constraint on a column in TSQL


DECLARE @tableName VARCHAR(MAX) = 'm_PWT'
DECLARE @columnName VARCHAR(MAX) = 'countInDoc'
DECLARE @ConstraintName nvarchar(200)
SELECT @ConstraintName = Name 
FROM SYS.DEFAULT_CONSTRAINTS
WHERE PARENT_OBJECT_ID = OBJECT_ID(@tableName) 
AND PARENT_COLUMN_ID = (
    SELECT column_id FROM sys.columns
    WHERE NAME = @columnName AND object_id = OBJECT_ID(@tableName))
IF @ConstraintName IS NOT NULL
    EXEC('ALTER TABLE '+@tableName+' DROP CONSTRAINT ' + @ConstraintName)

SQL data types from my query

Иногда нужно узнать типы возвращаемых запросом данных, например для объявления табличной переменной.
Начиная с 2012 SQL Server это легче легкого, для более ранних версий придется создавать временную таблицу из результата запроса, и затем в tempdb.sys.columns.
DECLARE @query nvarchar(max) = '
	select id, measEquiId, zipFlag, psId, dateSet, replaceId, pl1, pl2, pl3, phase, place, joinName, created, whoCreate, dateReplace from (
		select id, measEquiId, zipFlag, dateSet dateReplace, created, whoCreate from [MetrologDev].[dbo].m_eqPlaceParamsList ep
			where ep.deleted is null and ep.zipFlag = 35 and ep.psId = 0 and ep.replaceId=0 
			and not exists (select null from [MetrologDev].[dbo].m_eqPlaceParamsList ep2 where ep2.deleted is null and ep2.measEquiId = ep.measEquiId and ep2.psId <> 0 and ep2.replaceId=0 and ep2.id > ep.id)
			) inZip
		outer apply (
			select id id_r, psId, dateSet, replaceId, pl1, pl2, pl3, phase, place, joinName, created created_r, whoCreate whoCreate_r from (
				select row_number() over(order by id desc) rn, * from [MetrologDev].[dbo].m_eqPlaceParamsList ep
					where inZip.measEquiId=ep.measEquiId and ep.deleted is null and ep.zipFlag = 0 and ep.replaceId<>0)tt where rn=1) isRepl -- пока можно только менять, если снимать, то убираем условие ep.replaceId<>0
';
EXEC sp_describe_first_result_set @query, null, 0;

SELECT FROM stored procedure

Выборка из хранимой процедуры, первоначально условий не было, понадобились, сгородили огород, можно было использовать временную таблицу или табличную переменную.
Было

declare @d1 datetime = convert(datetime, '{$p->dateFromQ}', 104), @d2 datetime = convert(datetime, eomonth(convert(datetime, '{$p->dateToQ}', 104))) + 1.0/1.000001;
exec {$this->db_our}perfReport @d1, @d2;
Стало

select * from 
openquery([LOOPBACK], '
declare @d1 datetime = convert(datetime, ''{$p->dateFromQ}'', 104), @d2 datetime = convert(datetime, eomonth(convert(datetime, ''{$p->dateToQ}'', 104))) + 1.0/1.000001;
exec {$this->db_our}perfReport @d1, @d2
with result sets (( 
	price decimal(25,3), selectedCnt int ,selectedSum decimal(38,3)
	,smoot1 int ,smoot2 int ,approvedInPmes int ,selected int, inOther int
	,id int ,h1_id int ,h2_id int ,h3_id int
	,h1_name varchar(128) ,h2_name varchar(128) ,h3_name varchar(128)
	,h3_extId int ,documentID int ,dName varchar(128)
	,entity varchar(255) ,createdD datetime ,createdDH varchar(30) ,docDateH varchar(30)
	,TIName varchar(4000) ,vr float ,cnt int ,measEquiId int
	,pfId int ,unitPriceId int ,intEntityType int ,hashedName varchar(128)
	,originalName varchar(256) ,moot int ,moot1 int ,moot2 int
	,etName varchar(128) ,ekName varchar(255)
	,ekId int ,serialNumber varchar(64)
	,workNameF varchar(258) ,priceN decimal(14,3)
	,kindId2 int ,isPriced int
))')
 where docDateH between convert(datetime, '{$p->dateFrom}', 104) and convert(datetime, '{$p->dateTo}', 104) + 1.0/1.000001

How remove Memory Optimized File

Моя история избавления от In-Memory OLTP, база на продакшене, так что просто грохнуть ее и забыть, никак.

А началось все с этого: «Msg 41385, Level 16, State 1, Procedure sp_cdc_enable_db, Line 31 [Batch Start Line 2] A database cannot be enabled for both Change Data Capture (CDC) and MEMORY_OPTIMIZED_DATA storage.»

В базе все таблицы в оптимизированные на использование памяти заменил на табличные переменные.
Попытался удалить группу, «The file ‘MOD’ cannot be removed because it is not empty».
Можно пытаться сделать файл пустым и тд и тп, но все будет безрезультатно, потому как читать нужно документацию, и вот что сказал майкрософт, » Once you create a memory-optimized filegroup, you can only remove it by dropping the database. SQL Server does not allow you to remove an In-Memory OLTP filegroup from the database even after you drop all memory-optimized tables.», в общем искоренить MEMORY_OPTIMIZED_DATA storage можно только одним способом дропнув БД.

В моем случае в базе присутствуют таблицы с ограничениями по внешним ключам, а так же таблицы содержащие null-ы при заданном значении по умолчанию для столбца, задействован полнотекстовый поиск.
Ну да ладно, приступим, воспользуемся SQL Server Management Studio (SSMS) и утилитой DTExecUI.exe

1) Создаем скрипт базы. Безымянный1 Безымянный2 Безымянный3 Безымянный4 Безымянный5 Безымянный6

2) Правим полученный файла, удаляем файловую группу, ну и все это во временную БД, для сверки. Безымянный7 Безымянный8
3) Импорт данных («Import data…», курсор на скрине не на том пункте), готовим пакет для импорта, указываем источник, назначение, выбираем таблицы, указываем на необходимость вставки идентификаторов, сохраняем пакет в файл, затем его подправим и отправим на выполнение. Безымянный9 Безымянный10 Безымянный11 Безымянный12 Безымянный13 Безымянный14 Безымянный15 Безымянный16 Безымянный17 Безымянный18
Отключаем контроль ограничений, иначе будет плохо. Безымянный19 Безымянный20
Вот так, плохо. Безымянный20_1
Меняем keepnulls на true, иначе нулы заменятся на значения по умолчанию. Безымянный21 Безымянный22
И отправляем пакет на выполнение. Безымянный23 Безымянный24 Безымянный25

Готово. По этим же шагам можно и понижение версии сделать.


MS SQL OFFSET FETCH

Воспользовавшись предложением offset fetch (аналог mysql-го limit-a) для пагинации, наткнулся на проблемку, при переходе между страницами запрос выдавал тот же результат. В запросе всегда присутствует сортировка по одному из столбцов.
select rr.[rank] r, * v.*, otfile
, try_convert(date, col12, 104) col12D, convert(varchar(10), try_convert(date, col12, 104), 104) col12DH, ceiling((count(*) over())/10.0) pagesTotal from m_dev v
 inner join (select * from (values('4370'),('4371'),('4372'),('4374'),('3461'),('3462'),('3464'),('3465'))t(code)) codes on (v.col10 like codes.code + '%')
 inner join containstable (m_dev, col99, '("трансформатор" or "трансформатор*") and ("тока" or "тока*") and ("италия" or "италия*")', language 1049) rr on (v.id = rr.[key])
 where v.deleted is null 
 order by r
 offset 20 rows fetch next 10 rows only
Грешил на использование полнотекстового поиска, и объединение с результатами функции CONTAINSTABLE, провел эксперименты над данными попроще.
select t1.id1 id1, t1.id2+t2.id2-10 id2 from 
(select * from (values(1,10),(1,20),(1,30),(1,40),(1,50),(1,60),(1,70),(1,80),(1,90)) t (id1, id2)) t1
 cross join (select * from (values(1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8),(1,9)) t (id1, id2)) t2
 order by id1
 offset 0 rows fetch next 10 rows only
;

select t1.id1 id1, t1.id2+t2.id2-10 id2 from 
(select * from (values(1,10),(1,20),(1,30),(1,40),(1,50),(1,60),(1,70),(1,80),(1,90)) t (id1, id2)) t1
 cross join (select * from (values(1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8),(1,9)) t (id1, id2)) t2
 order by id1
 offset 10 rows fetch next 10 rows only
;

select t1.id1+t2.id1-1 id1, t1.id2+t2.id2-10 id2 from 
(select * from (values(1,10),(1,20),(1,30),(1,40),(1,50),(1,60),(1,70),(1,80),(1,90)) t (id1, id2)) t1
 cross join (select * from (values(1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8),(1,9)) t (id1, id2)) t2
 order by id1
 offset 0 rows fetch next 10 rows only
;

select t1.id1+t2.id1-1 id1, t1.id2+t2.id2-10 id2 from 
(select * from (values(1,10),(1,20),(1,30),(1,40),(1,50),(1,60),(1,70),(1,80),(1,90)) t (id1, id2)) t1
 cross join (select * from (values(1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8),(1,9)) t (id1, id2)) t2
 order by id1
 offset 10 rows fetch next 10 rows only
;
Получил результаты, соответственно для каждого запроса, появились косяки.
id1	id2		id1	id2		id1	id2		id1	id2
1	1		1	12		1	12		1	12
1	2		1	13		1	11		1	11
1	3		1	14		1	9		1	9
1	4		1	15		1	8		1	8
1	5		1	16		1	7		1	7
1	6		1	17		1	6		1	6
1	7		1	18		1	5		1	5
1	8		1	19		1	4		1	4
1	9		1	21		1	3		1	3
1	11		1	22		1	2		1	2
В плане выполнения появилась сортировка. План выполнения Итого, решаем проблему всегда добавляя последней сортировку по уникальному столбцу. Если его нет, генерим, как в примере. Если есть, как в моем рабочем запросе, в нем я просто добавил
order by r, v.id
select t1.id1+t2.id1-1 id1, t1.id2+t2.id2-10 id2, row_number() over (order by (select 1)) rn from 
(select * from (values(1,10),(1,20),(1,30),(1,40),(1,50),(1,60),(1,70),(1,80),(1,90)) t (id1, id2)) t1
 cross join (select * from (values(1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8),(1,9)) t (id1, id2)) t2
 order by id1, rn
 offset 0 rows fetch next 10 rows only
;

select t1.id1+t2.id1-1 id1, t1.id2+t2.id2-10 id2, row_number() over (order by (select 1)) rn from 
(select * from (values(1,10),(1,20),(1,30),(1,40),(1,50),(1,60),(1,70),(1,80),(1,90)) t (id1, id2)) t1
 cross join (select * from (values(1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8),(1,9)) t (id1, id2)) t2
 order by id1, rn
 offset 10 rows fetch next 10 rows only
;

id1	id2	rn		id1	id2	rn
1	1	1		1	12	11
1	2	2		1	13	12
1	3	3		1	14	13
1	4	4		1	15	14
1	5	5		1	16	15
1	6	6		1	17	16
1	7	7		1	18	17
1	8	8		1	19	18
1	9	9		1	21	19
1	11	10		1	22	20

UNPIVOT просто все столбцы в строчки

select * from
  (select * from (values(0,1,1,1,4), (5,6,6,6,9)) t (id1, id2, id3, id4, id5))t
   unpivot (value for columnName in (id1, id2, id3, id4, id5)) ttt
value	columnName
0	id1
1	id2
1	id3
1	id4
4	id5
5	id1
6	id2
6	id3
6	id4
9	id5

Update multiple nodes in SQL XML

Изменить в XML значения в нескольких узлах не получится,
The target of 'replace' must be at most one node, found 'attribute(selectedSum,xdt:untypedAtomic) *'
Выкручиваемся так, в цикле для каждой строки
declare @iCount int;
set @iCount = ( select xmlData.value('count(//*)', 'int') from [Metrolog].[dbo].[m_PWStatus] where deleted is null and pfId=12 and statusId=2 );

declare @i int;
set @i = 1;

declare @repl varchar(30) = '2890659.320';

while (@i <= @iCount)
 begin
 update [Metrolog].[dbo].[m_PWStatus]
  set xmlData.modify('declare namespace ttt="urn:metrolog.fsk-ees.ru"; replace value of (/rows/ttt:row[sql:variable("@i")]/@selectedSum)[1] with sql:variable("@repl")') where deleted is null and pfId=12 and statusId=2;
  update [Metrolog].[dbo].[m_PWStatus]
  set xmlData.modify('declare namespace ttt="urn:metrolog.fsk-ees.ru"; replace value of (/rows/ttt:row[sql:variable("@i")]/@selectedCnt)[1] with "250"') where deleted is null and pfId=12 and statusId=2;
  set @i = @i + 1;
 end

Век живи, век учись, вариант выделения дробной части

Задача, положить дробное число раздельно в два столбца, воспользуемся оператором взятия остатка от деления %, это Лёха подсказал
drop table #test17;

create table #test17(c1 int, c2 decimal(10,10) /*отдадим все 10 знаков на хранение дробной части*/);

insert into #test17
 select c,c from (select 12.34567 c) tt;
-- для MSSQL Arithmetic overflow error converting numeric to data type numeric.
-- The statement has been terminated.
-- или в MySQL Data truncation: Out of range value for column 'c2' at row 1

insert into #test17
 select c,c%1 from (select 12.34567 c) tt;

select * from #test17;

c1	c2
12	0.3456700000

для Оракла функция mod(c, d)

Transforming XML data into a rowset using the nodes() method and Performance

Преобразуем XML данные из столбца таблицы в табличные используя метод xml nodes() и cross apply.
Пример, три варианта, в первых двух сгенерирована XML с 1000 узлов, в последнем 10000, первый и второй варианты отличаются представлением XML, значения в атрибутах или в тэгах. В третьем, главное отличие, XML сохраняется в переменную, и результат, производительность на несколько порядков выше.
Итог: 8 сек, 12 сек, и 125 мс, если хмл-ка маленькая 10-50 узлов можно и не использовать переменную, иначе придется, или использовать OpenXML.
Если запустить первый вариант с 10К, то будем ждать долго, минут 10 (не дождался прервал на 5), деградация в прогрессии.
with 
t as (
 select * from (values(1),(2),(3),(4),(5),(6),(7),(8),(9))t(gid)
), t2 as (
 select row_number() over (order by t.gid) r,t.* from t, t tt1, t tt2, t tt3, t tt4--, t tt5
), t_xml as (
 select cast((select top(1000) * from t2 for xml raw) as xml) xmlData -- всего 1000 нод
), t_from_xml as (
 select
  x.xCol.value('@r','int') id
   ,x.xCol.value('@gid','int') gid 
from  t_xml b
 cross apply b.xmlData.nodes('/row') as x(xCol)  
)
--select top(1000) * from t2
--select * from t_xml
select * from t_from_xml
with 
t as (
 select * from (values(1),(2),(3),(4),(5),(6),(7),(8),(9))t(gid)
), t2 as (
 select row_number() over (order by t.gid) r,t.* from t, t tt1, t tt2, t tt3, t tt4--, t tt5
), t_xml as (
 select cast((select top(1000) * from t2 for xml path('row')) as xml) xmlData -- всего 1000 нод
), t_from_xml as (
 select
  x.xCol.value('(r/text())[1]','int') id
   ,x.xCol.value('(gid/text())[1]','int') gid 
from  t_xml b
 cross apply b.xmlData.nodes('/row') as x(xCol)  
)
--select top(1000) * from t2
--select * from t_xml
select * from t_from_xml
DECLARE @data xml
with 
t as (
 select * from (values(1),(2),(3),(4),(5),(6),(7),(8),(9))t(gid)
), t2 as (
 select row_number() over (order by t.gid) r,t.* from t, t tt1, t tt2, t tt3, t tt4--, t tt5
), t_xml as (
 select cast((select top(10000) * from t2 for xml path('row')) as xml) xmlData -- всего 1000 нод
), t_from_xml as (
 select
  x.xCol.value('(r/text())[1]','int') id
   ,x.xCol.value('(gid/text())[1]','int') gid 
from  t_xml b
 cross apply b.xmlData.nodes('/row') as x(xCol)  
)
--select top(1000) * from t2
select @data = (select xmlData from t_xml)

select
 x.xCol.value('(r/text())[1]','int') id
  ,x.xCol.value('(gid/text())[1]','int') gid 
from  @data.nodes('/row') as x(xCol)  
sql xml

Из таблицы в XML и обратно, for xml, from openxml

Если нужно сохранить результат выполнения запроса, оборачиваем его, и пишем в некий столбец типа xml
". ($saveXml ? "update {$this->db_our}[m_PWStatus] set xmlData = (" : '') . "
select 
tt8.priceN * cnt price,
sum(tt5.selected) over (partition by null) selectedCnt,
sum(coalesce(tt8.priceN * cnt,0)*tt5.selected) over (partition by null) selectedSum,
sum(tt5.moot1) over (partition by tt5.h2_id) smoot1,
sum(tt5.moot2) over (partition by tt5.h2_id) smoot2,
(select count(id) from {$this->db_our}[m_PWSubStatus] pw where pw.deleted is null and pw.statusId=1 and pw.pmesId = h2_id) approvedInPmes,
 * from tt5
 outer apply (
	select et.name etName, ek.name ekName, ek.id ekId, eqs.serialNumber from {$this->db_our}[m_listMeasEqui] eqs
	 inner join {$this->db_our}m_typeMeasEqui et on (et.deleted is null and et.id = eqs.typeMeasEquiID)
	 inner join {$this->db_our}m_equiKinds ek on (ek.deleted is null and ek.id = et.equiKindsID)
	where 1=1 
		and eqs.deleted is null 	
		and eqs.id = tt5.measEquiId 
 ) tt7
 outer apply (
	select tt6.workNameF, tt6.price priceN, tt6.kindId kindId2, tt6.id isPriced from tt6
	where 1=1
	and tt6.documentId = tt5.documentID
	and tt6.entityTypeId = tt5.intEntityType
	and coalesce(tt6.kindId,0) = coalesce(tt7.ekId,0)
	and (coalesce(tt7.ekId,0) not in (30,40) or (tt7.ekId in (30,40) and coalesce(tt6.vl,0) = coalesce(tt5.vr,0)))
 ) tt8

 order by h1_id, h2_id, h3_id, TIName, entity, documentID, vr desc, createdD" .
($saveXml ? "
 for xml raw, BINARY BASE64, root('rows'), XMLSCHEMA('urn:api.mneti.ru') ) 
 where deleted is null and pfId={$p->id} and statusId=2" : '');
raw — построчно в тэгах row,
BINARY BASE64 — бинарные данные преобразовать, если таковые есть
root(‘rows’) — корневой тэг
XMLSCHEMA(‘urn:api.mneti.ru’) — сохранить в том числе и схему (может и пригодится, особенно для внешних систем, данные о типах и размерностях)

Забираем данные, в моем случае, схему для возвращаемых данных описывал подправив сохраненный в результате шаблон схемы
		$sqlReport = [['select * from (',''], ["
select *, sum(priceSum) over (partition by null) priceTotal from (
select h1_id, h2_id, h3_id, h1_name, h2_name, h3_name, entity, vr2 vr, workNameF, sum(cnt) cnt, sum(price) priceSum from (
",
"
group by h1_id, h2_id, h3_id, h1_name, h2_name, h3_name, entity, vr2, workNameF
 ) tt
 order by h1_id, h2_id, h3_id, h1_name, h2_name, h3_name, vr desc, entity, workNameF"]];

		$sql = "
DECLARE @xmlData XML

select @xmlData = xmlData from {$this->db_our}[m_PWStatus] where deleted is null and pfId={$p->id} and statusId=2

DECLARE @handle INT  
DECLARE @PrepareXmlStatus INT  

EXEC @PrepareXmlStatus= sp_xml_preparedocument @handle OUTPUT, @xmlData, ''

".$sqlReport[$report][0]."
SELECT *, (case when ekId in (10) then null else vr end) vr2 FROM OPENXML(@handle, '/rows/ttt:row')
	WITH (
	price decimal(25,3)
	,selectedCnt int
	,selectedSum decimal(38,3)
	,smoot1 int
	,smoot2 int
	,approvedInPmes int
	,selected int
	,inOther int
	,id int
	,h1_id int
	,h2_id int
	,h3_id int
	,h1_name varchar(128)
	,h2_name varchar(128)
	,h3_name varchar(128)
	,h3_extId int
	,documentID int
	,dName varchar(128)
	,entity varchar(255)
	,createdD datetime
	,createdDH varchar(30)
	,docDateH varchar(30)
	,TIName varchar(4000)
	,vr float
	,cnt int
	,measEquiId int
	,pfId int
	,unitPriceId int
	,intEntityType int
	,hashedName varchar(128)
	,originalName varchar(256)
	,moot int
	,moot1 int
	,moot2 int
	,etName varchar(128)
	,ekName varchar(255)
	,ekId int
	,serialNumber varchar(64)
	,workNameF varchar(258)
	,priceN decimal(14,3)
	,kindId2 int
	,isPriced int	
	)
) tt ".$sqlReport[$report][1]."
    
EXEC sp_xml_removedocument @handle
";
Тут есть нюансы, если решили выгружать данные с описанием схемы, то и не забываем указать ее при загрузке, при выгрузке можно не указывать urn схемы, тогда сервер сам подставит что-то вроде этого «urn:schemas-microsoft-com:sql:SqlRowSet22» причем последнее число на свое усмотрение, не наш вариант. И корень не забываем указать.