MSSQL

Query

//describe table
select * 
  from information_schema.columns 
 where table_name = 'aspnet_Membership'
 order by ordinal_position

//table list 조회
SELECT * FROM INFORMATION_SCHEMA.TABLES
ORDER BY  TABLE_NAME

// 상위 n 개 결과 조회
SELECT top N * from accounts;

SQLCMD

#sqlcmd -S 127.0.0.1,1691  1691 포트
sqlcmd -S 127.0.01 -U sa -P MyPassword@

# list database
select name from sys.databases
go

# database use
USE <databasename>

# table list
select table_name from <database>.information_schema.tables;
go

# Dump database to local path(DB server의 localpath)
sqlcmd -S 127.0.0.1 -U sa -P MyPassword@ 
-Q "BACKUP DATABASE virtual_office TO DISK='/home/mssql/vo.bak'"

# restore database from local path

RESTORE DATABASE [database_name] 
FROM DISK = 'C:\Backup\backup_file.bak' 
WITH RECOVERY

sqlcmd -S 127.0.0.1 -U sa -P MyPassword@ 
-Q "RESTORE DATABASE virtual_office FROM DISK ='/home/mssql/virtualoffice.bak' 
WITH REPLACE"
# 이 명령 실행할때 bak 파일 실행 권한 없으면 문제 발생
# Msg 3201, Level 16, State 2, Server f2c2e9766569, Line 1
# Cannot open backup device '/home/mssql/dump.bak'. Operating system error 5(Access is denied.).
# Msg 3013, Level 16, State 1, Server f2c2e9766569, Line 1
# RESTORE DATABASE is terminating abnormally.

# SQL 파일 실행하기
sqlcmd -S 127.0.0.1 –U sa -P 1234 -i AdventureWorksDW2012.sql

컬럼이름, 테이블 이름 변경

SP_RENAME 'Test_Table.[userName]', 'userName2', 'COLUMN' -- 컬럼 이름변경

ALTER TABLE Test_Table ALTER COLUMN userName nVarchar(100) -- 컬럼 타입 변경

ALTER TABLE 'testtable' ADD test_id nvarchar(30) NULL -- 컬럼 추가하기 

SP_RENAME 'testtable', 'temptable' -- 테이블 이름 변경

DDL

# Drop Database -- 데이터베이스는 현재 사용중이므로 삭제할 수 없습니다 
# 에러 발생 시 아래 sql 실행 후 drop database 하면 됨
USE master

ALTER DATABASE database_name
SET OFFLINE WITH ROLLBACK IMMEDIATE

# Add Foreign Key Constraint
ALTER TABLE Sales.TempSalesReason
   ADD CONSTRAINT FK_TempSales_SalesReason FOREIGN KEY (TempID)
      REFERENCES Sales.SalesReason (SalesReasonID)
      ON DELETE CASCADE
      ON UPDATE CASCADE;

# Remove Foreign Key Constraint
ALTER TABLE virtual_office.dbo.MDOrganizationSceneInfoTable 
DROP CONSTRAINT fk_organization_scene_info_id;

TRUNCATE

• Foreign key constraint를 갖고 있는 table은 foreign key constraint를 disable 함으로써 truncate가 가능하지만, 다른 테이블에 의해 참조되는 테이블은 truncate를 할 수가 없음

• 참조하는 테이블이 비어 있는 것이 확실해진 다음에 DELETE 쿼리를 사용해야 함

Identity

-- identity 초기화
DBCC CHECKIDENT(organizations, reseed, 0);

• DBCC CHECKIDENT : ID 열의 현재 ID 값과 현재 최댓값을 반환함

• DBCC CHECKIDENT (<table_name>, RESEED, <new_reseed_value>)
• 현재 ID 값이 new_reseed_value로 설정됩니다. 테이블이 생성된 후 삽입된 행이 없거나 TRUNCATE TABLE 문을 사용하여 모든 행을 제거한 경우에는 DBCC CHECKIDENT를 실행한 후에 처음 삽입되는 행이 new_reseed_value를 ID로 사용하게 됩니다.
• 테이블에 행이 있거나 DELETE 문을 사용하여 모든 행을 제거한 경우 삽입된 다음 행은 new_reseed_value + 현재 증분 값을 사용합니다

Sequence

-- 시퀀스 생성
CREATE SEQUENCE [dbo].[SEQ]
AS [int]     --int 정수형
START WITH 0 -- 시작 값 0부터
INCREMENT BY 1       -- 증가 값 +1
MINVALUE -2147483648 --최소값 int 크기
MAXVALUE 2147483647  --최대값
CACHE
GO

-- 시퀀스 조회
SELECT * FROM <databaseName>.INFORMATION_SCHEMA.SEQUENCES 
where SEQUENCE_NAME ='admin_seq'

-- 시퀀스 값 초기화
ALTER SEQUENCE <sequence-name> RESTART with 100001;

-- insert 할 시, sequence 이용
INSERT INTO admin_test.dbo.admins (id, organization_id, email, password, role)
values (NEXT VALUE FOR admin_seq, 1, 'admin@test.com', 'abcd1234!@', 'ADMIN');

Connection Pool

• Connection은 pool에 필요한 만큼 추가가 되고, 최대 max pool size는 기본값은 100 이라고 함

SELECT 
	DB_NAME(dbid) as DBName, 
	COUNT(dbid) as NumberOfConnections,
	loginame as LoginName
FROM
	sys.sysprocesses
WHERE 
	dbid > 0
GROUP BY 
	dbid, loginame
ORDER BY  COUNT(dbid) DESC