frequently used
๋น ํ์ผ๋ก ์ฑ์ฐ๊ธฐ (์ถ๋ ฅ๋ฌผ)
DECLARE @v_ordnum varchar(20) = '201811260004' -- 8๊ฑด
--# ์์ ํ
์ด๋ธ ๋ณ์ ์ ์
DECLARE @tb_table Table
(
idx int
primary key (idx)
)
DECLARE @v_total_count int = 30, -- ํ ํ์ด์ง์ ์ต๋ ํํํ ๋ฐ์ดํฐ ๊ฑด ์
@v_data_count int = 0, -- ์์ธ ํ
์ด๋ธ ๋ฐ์ดํฐ ๊ฑด ์
@v_temp_idx int = 0
SELECT @v_data_count = COUNT(1) FROM sa110t WHERE ordnum = @v_ordnum
--# ํํํ ํ์ ์ ๋งํผ ํ
์ด๋ธ ๋ณ์์ INSERT
WHILE (@v_total_count > @v_temp_idx)
BEGIN
INSERT INTO @tb_table
VALUES (@v_temp_idx + 1)
SET @v_temp_idx = @v_temp_idx + 1
END
--# ์ค์ ๊ฒฐ๊ณผ
SELECT itemcd, itemnm, qty FROM sa110t WHERE ordnum = @v_ordnum
UNION ALL
SELECT '' as itemcd, '' as itemnm, 0 as qty
FROM @tb_table B
--# ์ ์ฟผ๋ฆฌ์ ๋์ผํ ๊ฒฐ๊ณผ์ COUNT๋ฅผ ์กฐ๊ฑด์ผ๋ก ์ฌ์ฉ
WHERE idx <= (((@v_data_count / @v_total_count) + 1) * @v_total_count) - @v_data_count
Database ONLINE/OFFLINE
-- ์คํ๋ผ์ธ
ALTER DATABASE [DATABASE_NAME] SET OFFLINE WITH ROLLBACK IMMEDIATE
-- ์จ๋ผ์ธ
ALTER DATABASE [DATABASE_NAME] SET ONLINE
์์ ํ
์ด๋ธ ์ญ์
์์ ํ ์ด๋ธ ์ ์ธํ๊ธฐ ์ ๊ณผ, BEGIN CATCH ๋ฌธ์ ์์ ๋ถ๋ถ์ ์์ฑํ์ฌ ๋ฐฉ์ด์ ์ผ๋ก ์ฟผ๋ฆฌ๋ฅผ ์์ฑ
IF OBJECT_ID('tempdb..#์์ํ
์ด๋ธ๋ช
') IS NOT NULL
DROP TABLE #์์ํ
์ด๋ธ๋ช
DBCC SHRINKFILE
๋ก๊ทธ ๋ฐ์ดํฐ ์ ๋ฆฌ
use tempdb
--backup log tempdb WITH NO_LOG
dbcc shrinkfile (templog,0)
dbcc shrinkfile (tempdev,0)
๋ก๊ทธ ์ ๋ฆฌ๊ฐ ๋์ง ์์ผ๋ฉด ์๋ ์ฟผ๋ฆฌ ์คํ ํ ์ฌ์๋
--ํ๋ก์์ ์บ์
DBCC FREEPROCCACHE
GO
-- Buffer Pool์ ์กด์ฌํ๋ ๋ฐ์ดํฐ ํ์ด์ง๋ฅผ ์ ๊ฑฐ
DBCC DROPCLEANBUFFERS
GO
--์ฌ์ฉ ํ์ง ์๋ ๋ชจ๋ ์บ์ ์ญ์
DBCC FREESYSTEMCACHE ('ALL')
--๋ถ์ฐ ์ฟผ๋ฆฌ์์ Microsoft SQL Server ์ธ์คํด์ค์ ๋ํด ์ฌ์ฉํ ๋ถ์ฐ ์ฟผ๋ฆฌ ์ฐ๊ฒฐ ์บ์๋ฅผ ํ๋ฌ์
DBCC FREESESSIONCACHE
GO
ํ๋ก์์ ๋ด ๋ฌธ์์ด ๊ฒ์
DECLARE @v_str varchar(100) = 'string'
SELECT TT.name
FROM (
SELECT B.name
FROM syscomments A, sysobjects B
WHERE A.id = B.id
AND A.text LIKE '%' + @v_str + '%'
GROUP BY B.name
) TT
ํ๋ก์์ ์ ํฌํจ๋ ํ
์ด๋ธ
DECLARE @v_procedure_name varchar(100) = '{ํ๋ก์์ ์ด๋ฆ}'
SELECT B.nameย ASย proc_name,
C.nameย ASย table_name
FROMย sysdependsย A
INNERย JOINย sysobjectsย Bย ONย A.idย =ย B.id
INNERย JOINย sysobjectsย Cย ONย A.depidย =ย C.id
WHEREย B.xtypeย =ย 'P'
AND B.name = @v_procedure_name
GROUP BY B.name, C.name
IDENTITY
(on/off) : data insert
SET IDENTITY_INSERT [TABLE_NAME] ON
INSERT INTO [TABLE_NAME]
(
// ...
)
VALUES (
//...
)
SET IDENTITY_INSERT [TABLE_NAME] OFF
check
-- DBCC CHECKIDENT([ํ
์ด๋ธ๋ช
], noreseed)
DBCC CHECKIDENT(tbl_test, noreseed)
initial
-- DBCC CHECKIDENT([ํ
์ด๋ธ๋ช
], reseed, [์ด๊ธฐํ ์ํฌ ๊ฐ])
DBCC CHECKIDENT(tbl_test, reseed, 0)
Service Broker
Enable/Disable
-- ํ์ฑํ
ALTER DATABASE MyDatabase
SET ENABLE_BROKER
WITH ROLLBACK IMMEDIATE
GO
-- ๋นํ์ฑํ
ALTER DATABASE MyDatabase
SET DISABLE_BROKER
WITH ROLLBACK IMMEDIATE
GO
-- ์ํ ์กฐํ
SELECT is_broker_enabled FROM sys.database WHERE name = 'MyDatabase'
ํน์ ํ
์ด๋ธ์ ์ฐ๋๋ณ ์ค์ ์ ์ฅ๋ ๋ฐ์ดํฐ ํฌ๊ธฐ
@v_table_name
: ํ ์ด๋ธ ์ด๋ฆ@v_date_field_name
: ์ง๊ณํ ๋ ์ง ์ ํ์ ํ๋๋ช
DECLARE
@v_table_name varchar(40) = 'SA100T',
@v_date_field_name varchar(40) = 'orddt',
@v_query nvarchar(MAX) = ''
SET @v_query = 'SELECT YEAR(CONVERT(datetime, ' + @v_date_field_name +')) AS YEAR, ' + char(13)
SELECT @v_query = @v_query + 'SUM(ISNULL(DATALENGTH(' + B.name + '),0)) +' + char(13)
FROM sys.tables A, sys.columns B
WHERE A.object_id = B.object_id
AND A.name = @v_table_name
AND B.name <> @v_date_field_name
ORDER BY column_id
SET @v_query = SUBSTRING(@v_query, 0, LEN(@v_query) - 1)
SET @v_query = @v_query + ' FROM ' + @v_table_name + char(13) + 'WHERE ISNULL(' + @v_date_field_name + ', '''') <> ''''' + ' GROUP BY YEAR(CONVERT(datetime, ' + @v_date_field_name +'))'
EXEC (@v_query)
SQL Server CLR ํตํฉ
์ด์
๋ธ๋ฆฌ ๋ฑ๋ก, ์ค์นผ๋ผ ํจ์ ์ ์
USE master
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;
GO
ALTER DATABASE {database} SET trustworthy on
CREATE ASSEMBLY [GST.PlusWin6]
from 'D:\MSSQL\GST.PlusWin6.SqlFunction.dll'
WITH permission_set = unsafe
GO
CREATE FUNCTION [dbo].[EncryptAES256](@value1 [nvarchar](max), @value2 [nvarchar](max))
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [GST.PlusWin6].[GST.PlusWin6.SqlFunction.Functions].[EncryptAES256]
GO
CREATE FUNCTION [dbo].DecryptAES256(@value1 [nvarchar](max), @value2 [nvarchar](max))
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [GST.PlusWin6].[GST.PlusWin6.SqlFunction.Functions].DecryptAES256
GO
Last updated