SQL SERVER - Clear SQL Server Memory Caches

If SQL Server is running slow and operations are throwing errors due to lack of memory, it is necessary to look into memory issue. If SQL Server is restarted all the cache memory is automatically cleaned up. In production server it is not possible to restart the server. In this scenario following three commands can be very useful.

When executed following three commands will free up memory for SQL Server by cleaning up its cache.

DBCC FREESYSTEMCACHE
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE

Reference : Pinal Dave (http://www.SQLAuthority.com)

=============== AND ===============================
For Microsoft SQL (MSSQL).
Use this to clear the cache and buffers to ensure comparison are accurate.

dbcc freeproccache
go
dbcc dropcleanbuffers
go

'1' like '[0-9]' - работает!

The PHONE_CLEAN Function
This function will remove all non-numeric characters from a string.


Declare
@Str1 VarChar(max),
@Str2 VarChar(max);
Select
@Str1='(406) 555-1212',
@Str2='';

With PhoneClean as
(
Select
Case
when SubString(@Str1,1,1) like '[0-9]' then SubString(@Str1,1,1)
else ''
End[Chr],
1[Idx]
Union All
Select
Case
when SubString(@Str1,Idx+1,1) like '[0-9]' then SubString(@Str1,Idx+1,1)
else ''
End,
Idx+1
from PhoneClean
where (Idx+1)<=Len(@Str1)
)
Select
@Str2=@Str2+Chr
from PhoneClean
option (MaxRecursion 0);
Select
@Str2;

Источник http://www.sqlservercentral.com/articles/T-SQL/63591/

Развернуть список в таблицу (несколько вариантов)

Задача - из строки 1,22,334
получить таблицу
1
22
334

ИТОГ - самый быстрый (пока) вариант 4
==== но там используется EXEC неприемлемый для функции
Далее по скорости лучший вариант 5

UPDATE - использование C#

UPDATE2:(XML) Вариант 3а

=================================================
CREATE FUNCTION [dbo].[func$COM$GetListAsTable]
(
@List varchar(1024),
@Delim varchar(1) = ','
)
RETURNS @Table TABLE (Element varchar(50))
AS
BEGIN

IF LEN(@List) = 0
RETURN;

/* --вариант 1 (в лоб)
DECLARE @Pos int;

IF Right(@List, 1) != @Delim
SET @List = @List + @Delim;

SET @pos = charindex(@Delim, @List);
WHILE @pos > 0
BEGIN
INSERT INTO @Table
VALUES (substring(@List, 1, @Pos - 1));

SET @List = substring(@List, @Pos+1, 1024);
SET @Pos = charindex(@Delim, @List);
END

-- вариант 2 (через CTE)
With Parse as
(
Select
SubString@List,1,CharIndex(',', @List)-1)[Element],
CharIndex(',', @List)[Idx]
Union All
Select
SubString@List,Idx+1,case when CharIndex(',', @List, Idx+1)=0 then 255 else CharIndex(',', @List, Idx+1) end -1-Idx),
CharIndex(',', @List, Idx+1)
from Parse
where Idx>0
)

INSERT INTO @Table
Select Element
from Parse
--option (MaxRecursion 0);

*/

-- вариант 3 (через XML)
declare @MyXMLData XML
set @MyXMLData = ''+
replace(@List,',','')+
''
INSERT INTO @Table
select x.item.value('Element[1]','nvarchar(15)')
from @MyXMLData.nodes('/Rows/Row')AS x(item)

-- вариант 3а
declare @CommaList varchar(max)
set @CommaList='ABC'+replicate(cast(',ABC' as varchar(max)),4999)

declare @XMLList xml
set @XMLList=cast(''+replace(@CommaList,',','')+'' as xml)
(вот оно!)
select x.i.value('.','varchar(3)')
from @XMLList.nodes('i') x(i)
или (еще лучше)
select x.i.value('(./text())[1]','varchar(3)')
from @XMLList1.nodes('i') x(i)
(а так хуже)
select x.i.value('.','varchar(3)')
from (select XMLList=cast(''+replace(@CommaList,',','')+'' as xml).query('.')) a
cross apply XMLList.nodes('i') x(i)
источник



-- вариант 4 (через XML)
-- быстрее чем 3
-- НО! с EXEC который невозможен в функции
declare @MyXMLData varchar(max)
set @MyXMLData = ''+
replace(@List,@Delim,'')+
''

DECLARE @DocHandle int
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @MyXMLData

INSERT INTO @Table
select x.Element from
OPENXML (@DocHandle, '/Rows/Row',1) WITH (Element int) AS x
EXEC sp_xml_removedocument @DocHandle

-- вариант 5
declare @pos int;
declare @len int;
declare @number varchar(10);
declare @curchar char;
declare @workStr varchar(MAX);
set @workStr=@List+',';

set @len=len(@workStr);

set @pos=1;
set @number='';

while(@pos<@len+1)
BEGIN
set @curchar=substring(@workStr, @pos, 1);
if(@curchar<>',')
BEGIN
set @number=@number+@curchar;
END
else
BEGIN
--print (@number);
INSERT INTO @Table VALUES (@number);
set @number='';
END

set @pos=@pos+1;
END

RETURN
END

--пример вызова
select * from dbo.func$COM$GetListAsTable('1,2', ',')

Вариант 6

[SqlFunction(FillRowMethodName = "FillRow",Name = "fn_split_clr", TableDefinition = "value bigint")]

public static IEnumerable fn_split_clr([SqlFacet(MaxSize=-1)]SqlString value)

{

return value.Value.Split(new []{';'});

}

public static void FillRow(Object obj, out SqlInt64 value)

{

Int64 res;

value = Int64.TryParse((string)obj,out res) ? new SqlInt64(res) : new SqlInt64();

}

Источник http://www.sqlservercentral.com/articles/XML/63633/

Еще статьи на эту тему http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/
http://www.simple-talk.com/sql/t-sql-programming/faking-arrays-in-transact-sql/

UPDATE

SCOPE_IDENTITY

MSSQL
Получить значение автоинкремента - SCOPE_IDENTITY() - в текущей сессии, @@IDENTITY в текущей но после триггера, IDENT_CURRENT(_table_name) - для всех сессий

Поехали!

Как обычно: "Привет, Мир!"