SQL Server

Хранение древовидных структур данных

Часто перед нами встаёт вопрос хранения древовидных структур в базе данных. Несмотря на то, что звучит это просто, часто из-за неправильной организации хранения появляются проблемы со скоростью выборки таких данных. […]

Немного примеров по использованию динамических объектов в SQL Server

SQL Server постоянно контролирует себя и собирает информацию, полезную для мониторинга состояния экземпляра, находит проблемы, такие как отсутствующие индексы, и оптимизирует запросы. SQL Server предоставляет эту информацию посредством динамических административных […]

Чистый код – SQL

В отрасли разработки ПО немаловажную роль играет чистота кода. Чистый код легко читать и модифицировать любому разработчику. Это особенно важно после введения продукта в эксплуатацию, а именно, когда через длительное […]

Работа с представлениями SQL

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

Анализ работы медленного запроса

Если у вас медленно работает запрос, используйте эту штуку:

SET STATISTICS IO ON
SET STATISTICS TIME ON
--Also, before each query, flush the SQL Server memory cache:
CHECKPOINT
DBCC DROPCLEANBUFFERS

SET STATISTICS IO ON

SET STATISTICS TIME ON

--Also, before each query, flush the SQL Server memory cache:

CHECKPOINT

DBCC DROPCLEANBUFFERS

Так вы сможете понять, где именно возникает задержка (на вкладке Messages)

Посмотреть размер всех таблиц в базе данных SQL SERVER

Существует несколько вариантов, чтобы получить количество строк для всех таблиц в БД: sys.partitions Catalog View

SELECT
      QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]
      , SUM(sPTN.Rows) AS [RowCount]
FROM 
      sys.objects AS sOBJ
      INNER JOIN sys.partitions AS sPTN
            ON sOBJ.object_id = sPTN.object_id
WHERE
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
      AND index_id < 2 -- 0:Heap, 1:Clustered
GROUP BY 
      sOBJ.schema_id
      , sOBJ.name
ORDER BY [TableName]
GO

SELECT

QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]

, SUM(sPTN.Rows) AS [RowCount]

FROM

sys.objects AS sOBJ

INNER JOIN sys.partitions AS sPTN

ON sOBJ.object_id = sPTN.object_id

WHERE

sOBJ.type = 'U'

AND sOBJ.is_ms_shipped = 0x0

AND index_id < 2 -- 0:Heap, 1:Clustered

GROUP BY

sOBJ.schema_id

, sOBJ.name

ORDER BY [TableName]

sys.dm_db_partition_stats Dynamic Management View (DMV)

SELECT
      QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]
      , SUM(sdmvPTNS.row_count) AS [RowCount]
FROM
      sys.objects AS sOBJ
      INNER JOIN sys.dm_db_partition_stats AS sdmvPTNS
            ON sOBJ.object_id = sdmvPTNS.object_id
WHERE 
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
      AND sdmvPTNS.index_id < 2
GROUP BY
      sOBJ.schema_id
      , sOBJ.name
ORDER BY [TableName]
GO

SELECT

QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]

, SUM(sdmvPTNS.row_count) AS [RowCount]

FROM

sys.objects AS sOBJ

INNER JOIN sys.dm_db_partition_stats AS sdmvPTNS

ON sOBJ.object_id = sdmvPTNS.object_id

WHERE

sOBJ.type = 'U'

AND sOBJ.is_ms_shipped = 0x0

AND sdmvPTNS.index_id < 2

GROUP BY

sOBJ.schema_id

, sOBJ.name

ORDER BY [TableName]

sp_MSforeachtable System Stored Procedure

DECLARE @TableRowCounts TABLE ([TableName] VARCHAR(128), [RowCount] INT) ;
INSERT INTO @TableRowCounts ([TableName], [RowCount])
EXEC sp_MSforeachtable 'SELECT ''?'' [TableName], COUNT(*) [RowCount] FROM ?' ;
SELECT [TableName], [RowCount]
FROM @TableRowCounts
ORDER BY [TableName]
GO

DECLARE @TableRowCounts TABLE ([TableName] VARCHAR(128), [RowCount] INT) ;

INSERT INTO @TableRowCounts ([TableName], [RowCount])

EXEC sp_MSforeachtable 'SELECT ''?'' [TableName], COUNT(*) [RowCount] FROM ?' ;

SELECT [TableName], [RowCount]

FROM @TableRowCounts

ORDER BY [TableName]

COALESCE() Function

DECLARE @QueryString NVARCHAR(MAX) ;
SELECT @QueryString = COALESCE(@QueryString + ' UNION ALL ','')
                      + 'SELECT '
                      + '''' + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
                      + '.' + QUOTENAME(sOBJ.name) + '''' + ' AS [TableName]
                      , COUNT(*) AS [RowCount] FROM '
                      + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
                      + '.' + QUOTENAME(sOBJ.name) + ' WITH (NOLOCK) '
FROM sys.objects AS sOBJ
WHERE
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
ORDER BY SCHEMA_NAME(sOBJ.schema_id), sOBJ.name ;
EXEC sp_executesql @QueryString
GO

DECLARE @QueryString NVARCHAR(MAX) ;

SELECT @QueryString = COALESCE(@QueryString + ' UNION ALL ','')

+ 'SELECT '

+ '''' + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))

+ '.' + QUOTENAME(sOBJ.name) + '''' + ' AS [TableName]

, COUNT(*) AS [RowCount] FROM '

+ QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))

+ '.' + QUOTENAME(sOBJ.name) + ' WITH (NOLOCK) '

FROM sys.objects AS sOBJ

WHERE

sOBJ.type = 'U'

AND sOBJ.is_ms_shipped = 0x0

ORDER BY SCHEMA_NAME(sOBJ.schema_id), sOBJ.name ;

EXEC sp_executesql @QueryString

[…]

Получить скрипт для создания страницы при помощи запроса

Получить скрипт для создания sql-таблицы, включая include primary key, foreign key, indexes,triggers и т.д., можно при помощи такого запроса в SQL Server:

declare @table varchar(100)
set @table = 'MyTable' -- set table name here
declare @sql table(s varchar(1000), id int identity)
 
-- create statement
insert into  @sql(s) values ('create table [' + @table + '] (')
 
-- column list
insert into @sql(s)
select 
    '  ['+column_name+'] ' + 
    data_type + coalesce('('+cast(character_maximum_length as varchar)+')','') + ' ' +
    case when exists ( 
        select id from syscolumns
        where object_name(id)=@table
        and name=column_name
        and columnproperty(id,name,'IsIdentity') = 1 
    ) then
        'IDENTITY(' + 
        cast(ident_seed(@table) as varchar) + ',' + 
        cast(ident_incr(@table) as varchar) + ')'
    else ''
    end + ' ' +
    ( case when IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' + 
    coalesce('DEFAULT '+COLUMN_DEFAULT,'') + ','
 
 from information_schema.columns where table_name = @table
 order by ordinal_position
 
-- primary key
declare @pkname varchar(100)
select @pkname = constraint_name from information_schema.table_constraints
where table_name = @table and constraint_type='PRIMARY KEY'
 
if ( @pkname is not null ) begin
    insert into @sql(s) values('  PRIMARY KEY (')
    insert into @sql(s)
        select '   ['+COLUMN_NAME+'],' from information_schema.key_column_usage
        where constraint_name = @pkname
        order by ordinal_position
    -- remove trailing comma
    update @sql set s=left(s,len(s)-1) where id=@@identity
    insert into @sql(s) values ('  )')
end
else begin
    -- remove trailing comma
    update @sql set s=left(s,len(s)-1) where id=@@identity
end
 
-- closing bracket
insert into @sql(s) values( ')' )
 
-- result!
select s from @sql order by id

declare @table varchar(100)

set @table = 'MyTable' -- set table name here

declare @sql table(s varchar(1000), id int identity)

-- create statement

insert into @sql(s) values ('create table [' + @table + '] (')

-- column list

insert into @sql(s)

select

' ['+column_name+'] ' +

data_type + coalesce('('+cast(character_maximum_length as varchar)+')','') + ' ' +

case when exists (

select id from syscolumns

where object_name(id)=@table

and name=column_name

and columnproperty(id,name,'IsIdentity') = 1

) then

'IDENTITY(' +

cast(ident_seed(@table) as varchar) + ',' +

cast(ident_incr(@table) as varchar) + ')'

else ''

end + ' ' +

( case when IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' +

coalesce('DEFAULT '+COLUMN_DEFAULT,'') + ','

from information_schema.columns where table_name = @table

order by ordinal_position

-- primary key

declare @pkname varchar(100)

select @pkname = constraint_name from information_schema.table_constraints

where table_name = @table and constraint_type='PRIMARY KEY'

if ( @pkname is not null ) begin

insert into @sql(s) values(' PRIMARY KEY (')

insert into @sql(s)

select ' ['+COLUMN_NAME+'],' from information_schema.key_column_usage

where constraint_name = @pkname

order by ordinal_position

-- remove trailing comma

update @sql set s=left(s,len(s)-1) where id=@@identity

insert into @sql(s) values (' )')

end

else begin

-- remove trailing comma

update @sql set s=left(s,len(s)-1) where id=@@identity

end

-- closing bracket

insert into @sql(s) values( ')' )

-- result!

select s from @sql order by id

Ссылка на оригинал.

Число записей в каждой таблице БД

Решение:

declare @sql varchar(max),
@tblname sysname,
@tblsch sysname

declare @t table ( tablename sysname, [count] bigint)

DECLARE vendor_cursor CURSOR FOR
select TABLE_SCHEMA,TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE'
OPEN vendor_cursor

FETCH NEXT FROM vendor_cursor
INTO @tblsch, @tblname

WHILE @@FETCH_STATUS = 0
BEGIN

set @sql = 'select '''+@tblname +''' as tablename , count(*) as cnt from '+@tblsch+'.'+@tblname
print @sql
insert @t(tablename ,[count])
exec (@sql)

FETCH NEXT FROM vendor_cursor
INTO @tblsch, @tblname

END
CLOSE vendor_cursor
DEALLOCATE vendor_cursor

select * from @t order by tablename

declare @sql varchar(max),

@tblname sysname,

@tblsch sysname

declare @t table ( tablename sysname, [count] bigint)

DECLARE vendor_cursor CURSOR FOR

select TABLE_SCHEMA,TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE'

OPEN vendor_cursor

FETCH NEXT FROM vendor_cursor

INTO @tblsch, @tblname

WHILE @@FETCH_STATUS = 0

BEGIN

set @sql = 'select '''+@tblname +''' as tablename , count(*) as cnt from '+@tblsch+'.'+@tblname

print @sql

insert @t(tablename ,[count])

exec (@sql)

FETCH NEXT FROM vendor_cursor

INTO @tblsch, @tblname

END

CLOSE vendor_cursor

DEALLOCATE vendor_cursor

select * from @t order by tablename

Создание бекапа и восстановление из рез копии

Решение:

для копирования ArkAS
BACKUP DATABASE ArkAS
TO DISK = 'C:\temp1\arkAS.bak'
WITH INIT ;
---------------------------------
RESTORE DATABASE BDName
FROM DISK = 'C:\temp1\arkAS.bak'
WITH REPLACE,
MOVE 'arkAS' TO 'C:\DBs\dbName.mdf',
MOVE 'arkAS_log' TO 'C:\DBs\dbName.ldf'

для копирования ArkAS

BACKUP DATABASE ArkAS

TO DISK = 'C:\temp1\arkAS.bak'

WITH INIT ;

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

RESTORE DATABASE BDName

FROM DISK = 'C:\temp1\arkAS.bak'

WITH REPLACE,

MOVE 'arkAS' TO 'C:\DBs\dbName.mdf',

MOVE 'arkAS_log' TO 'C:\DBs\dbName.ldf'

Случайное число в базе

Решение:

SELECT ROUND(((1000 - 1 -1) * RAND(DAY(getdate())*100000) + 1), 0)

SELECT ROUND(((@max - @min -1) * RAND(@seed) + @min), 0)

SELECT ROUND(((1000 - 1 -1) * RAND(DAY(getdate())*100000) + 1), 0)

SELECT ROUND(((@max - @min -1) * RAND(@seed) + @min), 0)

Web-Automation.ru

Хранение древовидных структур данных

Немного примеров по использованию динамических объектов в SQL Server

Чистый код – SQL

Работа с представлениями SQL

Анализ работы медленного запроса

Посмотреть размер всех таблиц в базе данных SQL SERVER

Получить скрипт для создания страницы при помощи запроса

Число записей в каждой таблице БД

Создание бекапа и восстановление из рез копии

Случайное число в базе

Как с вами связаться?

+7 (900) 908-88-33

hecruss

ru@rudensoft.ru