SQL Server의 숨겨진 기능

SQL Server의 숨겨진 기능

잠겼습니다. 이 질문과 답변은이 질문은 부적절하지만 역사적 의미가 있기 때문에 잠겨 있습니다.현재 새로운 답변이나 상호작용을 받아들이지 않고 있습니다.

SQL Server의 숨겨진 기능은 무엇입니까?

예를 들어, 문서화되지 않은 시스템 저장 프로시저, 매우 유용하지만 충분히 문서화되어 있지 않은 작업을 수행하는 요령 등이 있습니다.

---

답들

모든 훌륭한 답변에 감사드립니다!

스토어드 프로시저

• sp_msforeachable:각 테이블 이름으로 대체된 ? 명령어를 실행합니다(v6.5 이상).
• sp_msforeachdb: 각 데이터베이스 이름으로 대체된 ? 명령어를 실행합니다(v7 이상).
• sp_who2: sp_who와 동일하지만 블록의 트러블 슈팅에 관한 많은 정보가 포함되어 있습니다(v7 이후).
• sp_helptext:스토어드 프로시저의 코드가 필요한 경우는, 표시와 UDF 를 참조해 주세요.
• sp_parames: 범위 내 데이터베이스의 모든 테이블 및 뷰 목록을 반환합니다.
• sp_parames_procedures: 모든 저장 프로시저의 목록을 반환합니다.
• xp_sscanf:문자열에서 각 format 인수로 지정된 인수 위치로 데이터를 읽습니다.
• xp_http 드라이브:사용 가능한 공간이 가장 큰 고정 드라이브 찾기
• sp_help: 테이블의 구조, 인덱스 및 구속조건을 알고 싶은 경우.뷰 및 UDF도 있습니다.바로 가기는 Alt+F1입니다.

단편

• 랜덤 순서로 행 반환
• 최종 수정일 기준 모든 데이터베이스 사용자 개체
• 반환일만
• 이번 주 안에 해당하는 날짜를 찾습니다.
• 지난주에 발생한 기록을 찾습니다.
• 현재 주의 시작 날짜를 반환합니다.
• 지난주 시작 날짜를 반환합니다.
• 서버에 전개된 순서의 텍스트를 참조해 주세요.
• 데이터베이스에 대한 모든 연결 끊기
• 테이블 체크섬
• 행 체크섬
• 데이터베이스의 모든 프로시저를 삭제합니다.
• 복원 후 로그인 ID를 올바르게 다시 매핑합니다.
• INSERT 문에서 스토어드 프로시저를 호출하다
• 키워드로 프로시저 검색
• 데이터베이스의 모든 프로시저를 삭제합니다.
• 트랜잭션 로그에서 데이터베이스를 프로그래밍 방식으로 쿼리합니다.

기능들

• 해시 바이트()
• EncryptByKey(암호화 기준)
• 피벗 명령어

기타

• 접속 문자열 추가
• TableDiff.실행
• 로그온 이벤트 트리거(Service Pack 2의 새로운 기능)
• 지속 계산 컬럼(PCC)을 사용하여 퍼포먼스를 향상시킵니다.
• sys.database_princies의 DEFAULT_SCHEMA 설정
• 강제 파라미터화
• Vardecimal 스토리지 형식
• 가장 많이 사용되는 쿼리 검색(초)
• 확장 가능한 공유 데이터베이스
• SQL Management Studio의 테이블/저장 프로시저 필터 기능
• 트레이스 플래그
• 뒤에 GO 처리를
• 스키마를 사용한 보안
• 내장된 암호화 기능, 뷰 및 트리거가 포함된 기본 테이블을 사용한 암호화

Management Studio에서 GO 배치 종료 마커 뒤에 숫자를 입력하면 배치가 이 횟수만큼 반복됩니다.

PRINT 'X'
GO 10

X를 10회 인쇄합니다.이렇게 하면 반복적인 작업을 할 때 지루한 복사/붙여넣기 작업을 줄일 수 있습니다.

많은 SQL Server 개발자들은 DELETE, INSERT 및 UPDATE 문장의 OUTPUT 조항(SQL Server 2005 이후)에 대해 아직 모르고 있는 것 같습니다.

INSERTED,중 알면 구를 하면 INSERTED, UPDATEDDELETD라고 하는 에 쉽게 수 .OUTPUT 절을 사용하면 다음과 같은 "가상" 테이블에 쉽게 액세스할 수 있습니다.inserted ★★★★★★★★★★★★★★★★★」deleted): (트리거와 같이):

DELETE FROM (table)
OUTPUT deleted.ID, deleted.Description
WHERE (condition)

INT IDENT 프라이머리 키필드가 있는 테이블에 OUTPUT 구를 사용하여 값을 삽입하는 경우 삽입된 새 ID를 바로 얻을 수 있습니다.

INSERT INTO MyTable(Field1, Field2)
OUTPUT inserted.ID
VALUES (Value1, Value2)

할 때이 바뀌었는지 이 될 수 요. 이 , 이 경우, 이, 이, 이, 이, 이, 이, 이, 이, 이, 이, 이, 이, 이, 이, 이, 이, 이, 이, 이, 이, 이,insertedUPDATE 후을 나타내며, (UPDATE)는 UPDATE를 나타냅니다.deleted는 UPDATE "UPDATE: "UPDATE" 이전 .

UPDATE (table)
SET field1 = value1, field2 = value2
OUTPUT inserted.ID, deleted.field1, inserted.field1
WHERE (condition)

은 임시 또는 변수OUTPUT)로.OUTPUT INTO @myInfoTable를 참조해 주세요.

매우 유용하고 거의 알려지지 않았습니다!

마크

sp_msforeachtable각이름으로 ? 합니다. 각 테이블 이름으로 대체된 ? 명령어를 실행합니다.

exec sp_msforeachtable "dbcc dbreindex('?')"

테이블마다 최대 3개의 명령어를 발행할 수 있습니다.

exec sp_msforeachtable
    @Command1 = 'print ''reindexing table ?''',
    @Command2 = 'dbcc dbreindex(''?'')',
    @Command3 = 'select count (*) [?] from ?'

ㅇㅇㅇㅇ.sp_MSforeachdb

연결 문자열 추가:

MultipleActiveResultSets=true;

이것으로 ADO가 됩니다.Net 2.0 이상에서는, 1개의 데이타베이스 접속으로 복수의 읽기 전용의 읽기 전용의 결과 세트를 사용할 수 있기 때문에, 대량의 읽기를 실시할 경우에 퍼포먼스가 향상됩니다.여러 쿼리 유형을 혼합하는 경우에도 이 옵션을 설정할 수 있습니다.

응용 프로그램 이름=MyProgramName

syspprocesses 테이블을 조회하여 활성 연결 목록을 보려면 " 대신 program_name 열에 프로그램 이름이 표시됩니다.Net SqlClient 데이터 공급자"

TableDiff.실행

• Table Difference 도구를 사용하면 소스 테이블과 대상 테이블 또는 뷰 간의 차이를 검출하고 조정할 수 있습니다.Tablediff 유틸리티는 스키마와 데이터의 차이를 보고할 수 있습니다.tablediff의 가장 일반적인 기능은 테이블 간의 차이를 조정하는 수신처에서 실행할 수 있는 스크립트를 생성할 수 있다는 것입니다.

링크

행을 랜덤 순서로 되돌리기 위한 덜 알려진 TSQL 기법:

-- Return rows in a random order
SELECT 
    SomeColumn 
FROM 
    SomeTable
ORDER BY 
    CHECKSUM(NEWID())

Management Studio에서는 다음과 같이 테이블의 콤마 구분 열 목록을 빠르게 가져올 수 있습니다.

1. 오브젝트 탐색기에서 지정된 테이블 아래의 노드를 확장합니다(열, 키, 제약 조건, 트리거 등의 폴더가 표시됩니다).
2. Columns 폴더를 가리키고 쿼리로 끕니다.

테이블을 오른쪽 클릭하여 [스크립트 테이블 이름...]를 선택한 후 [삽입처...]를 선택하면 반환되는 악의적인 형식을 사용하지 않을 때 편리합니다.이 트릭은 다른 폴더와 함께 사용할 수 있으므로 폴더에 포함된 이름의 쉼표 목록을 제공합니다.

행 생성자

하나의 삽입문으로 여러 행의 데이터를 삽입할 수 있습니다.

INSERT INTO Colors (id, Color)
VALUES (1, 'Red'),
       (2, 'Blue'),
       (3, 'Green'),
       (4, 'Yellow')

테이블 구조, 인덱스 및 구속조건을 알고 싶은 경우:

sp_help 'TableName'

HashBytes() 입력의 MD2, MD4, MD5, SHA 또는 SHA1 해시를 반환합니다.

가장 많이 사용되는 쿼리 파악

• sys.dm_exec_sec_sec_sec를 사용하면 단일 쿼리로 여러 쿼리 분석 조합을 파악할 수 있습니다.

Commnad와 링크

select * from sys.dm_exec_query_stats 
order by execution_count desc

공간 결과 탭을 사용하여 아트를 생성할 수 있습니다.

링크 설명을 여기에 입력합니다.http://michaeljswart.com/wp-content/uploads/2010/02/venus.png

제외 및 교차

이 두 키워드는 조인과 서브쿼리를 상세하게 작성하는 것이 아니라 두 쿼리 결과를 비교할 때 쿼리의 의도를 보다 우아하고 쉽게 표현할 수 있는 방법입니다.SQL Server 2005의 새로운 버전에서는 이미 몇 년 전부터 TSQL 언어로 존재해 온 UNION을 강력하게 보완하고 있습니다.

EXCEPT, CRESS 및 UNION의 개념은 모든 현대 RDBMS에서 사용되는 관계형 모델링의 기초 및 기초가 되는 집합 이론의 기초입니다.이제 TSQL을 사용하여 Venn 다이어그램 유형의 결과를 보다 직관적이고 쉽게 생성할 수 있습니다.

확실히 숨겨져 있는 것은 아니지만, 피벗 커맨드에 대해 아는 사람은 많지 않습니다.저장 프로시저를 변경할 수 있었습니다.커서로 2분만에 행수의 10분의 1에 해당하는 6초의 코드에 액세스 할 수 있었습니다.

데이터베이스를 테스트 목적으로 복원하거나 기타 용도로 복원할 때 유용합니다.로그인 ID를 올바르게 재매핑합니다.

EXEC sp_change_users_login 'Auto_Fix', 'Mary', NULL, 'B3r12-36'

데이터베이스에 대한 모든 연결을 끊습니다.

Use Master
Go

Declare @dbname sysname

Set @dbname = 'name of database you want to drop connections from'

Declare @spid int
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname)
While @spid Is Not Null
Begin
        Execute ('Kill ' + @spid)
        Select @spid = min(spid) from master.dbo.sysprocesses
        where dbid = db_id(@dbname) and spid > @spid
End

테이블 체크섬

Select CheckSum_Agg(Binary_CheckSum(*)) From Table With (NOLOCK)

행 체크섬

Select CheckSum_Agg(Binary_CheckSum(*)) From Table With (NOLOCK) Where Column = Value

이것이 숨겨진 특징인지 아닌지는 모르겠지만, 우연히 발견되어 많은 경우에 유용하게 사용되고 있습니다.커서를 사용하여 select 문을 반복하지 않고 단일 select 문으로 필드 세트를 합성할 수 있습니다.

예:

DECLARE @nvcConcatonated nvarchar(max)
SET @nvcConcatonated = ''

SELECT @nvcConcatonated = @nvcConcatonated + C.CompanyName + ', '
FROM tblCompany C
WHERE C.CompanyID IN (1,2,3)

SELECT @nvcConcatonated

결과:

Acme, Microsoft, Apple,

저장 프로시저의 코드를 원할 경우 다음을 수행할 수 있습니다.

sp_helptext 'ProcedureName'

(숨겨진 기능인지는 모르겠지만 항상 사용하고 있습니다)

저장 프로시저 트릭은 INSERT 문에서 호출할 수 있다는 것입니다.SQL Server 데이터베이스에서 작업할 때 매우 유용하다는 것을 알게 되었습니다.

CREATE TABLE #toto (v1 int, v2 int, v3 char(4), status char(6))
INSERT #toto (v1, v2, v3, status) EXEC dbo.sp_fulubulu(sp_param1)
SELECT * FROM #toto
DROP TABLE #toto

SQL Server 2005/2008에서 SELECT 쿼리 결과에 행 번호를 표시하는 경우:

SELECT ( ROW_NUMBER() OVER (ORDER BY OrderId) ) AS RowNumber,
        GrandTotal, CustomerId, PurchaseDate
FROM Orders

ORDER BY는 필수 조항입니다.OVER() 절은 SQL Engine에 지정된 열(이 경우 OrderId)의 데이터를 정렬하고 정렬 결과에 따라 번호를 할당하도록 지시합니다.

스토어드 프로시저 인수를 해석하는 데 도움이 됩니다.xp_sscanf

문자열에서 각 format 인수로 지정된 인수 위치로 데이터를 읽습니다.

다음 예제에서는 xp_sscanf를 사용하여 소스 문자열 형식의 위치에 따라 소스 문자열에서2개의 값을 추출합니다.

DECLARE @filename varchar (20), @message varchar (20)
EXEC xp_sscanf 'sync -b -fproducts10.tmp -rrandom', 'sync -b -f%s -r%s', 
  @filename OUTPUT, @message OUTPUT
SELECT @filename, @message

다음은 결과 세트입니다.

-------------------- -------------------- 
products10.tmp        random

반환일만

Select Cast(Floor(Cast(Getdate() As Float))As Datetime)

또는

Select DateAdd(Day, 0, DateDiff(Day, 0, Getdate()))

dm_db_index_syslogs_syslogs

이를 통해 테이블에 DateUpdated 열이 없는 경우에도 테이블의 데이터가 최근에 업데이트되었는지 여부를 알 수 있습니다.

SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'MyDatabase')
AND OBJECT_ID=OBJECT_ID('MyTable')

코드명 : http://blog.sqlauthority.com/2009/05/09/sql-server-find-last-date-time-updated-for-any-table/

참조처: SQL Server - 테이블의 마지막 삽입 행의 날짜/시간

SQL 2005 이후 이용 가능

여기 도움이 되는 기능이 몇 가지 있습니다만, 많은 사람들이 모르는 것 같습니다.

sp_tables

현재 환경에서 쿼리할 수 있는 개체 목록을 반환합니다.이는 동의어 개체를 제외하고 FROM 절에 표시될 수 있는 모든 개체를 의미합니다.

링크

sp_stored_procedures

현재 환경의 저장 프로시저 목록을 반환합니다.

링크

이번 주 안에 해당하는 날짜를 찾습니다.

where dateadd( week, datediff( week, 0, TransDate ), 0 ) =
dateadd( week, datediff( week, 0, getdate() ), 0 )

지난주에 발생한 기록을 찾습니다.

where dateadd( week, datediff( week, 0, TransDate ), 0 ) =
dateadd( week, datediff( week, 0, getdate() ) - 1, 0 )

현재 주의 시작 날짜를 반환합니다.

select dateadd( week, datediff( week, 0, getdate() ), 0 )

지난주 시작 날짜를 반환합니다.

select dateadd( week, datediff( week, 0, getdate() ) - 1, 0 )

숨겨진 기능은 아니지만 Management Studio의 Tools\Options\Keyboard에서 키 매핑을 설정합니다.Alt+F1은 기본적으로 sp_help "selected text"로 설정되어 있지만 sp_helpt "selected text"에 Ctrl+F1을 추가하지 않으면 사용할 수 없습니다.

지속 계산 컬럼

• 계산된 열은 런타임 계산 비용을 데이터 수정 단계로 전환하는 데 도움이 됩니다.계산된 열은 나머지 행과 함께 저장되며 계산된 열과 쿼리의 식이 일치할 때 투명하게 사용됩니다.PCC에 인덱스를 구축하여 식에 대한 필터링 및 범위 검색 속도를 높일 수도 있습니다.

링크

정렬 기준이 되는 열이 없거나 테이블에서 기본 정렬 순서를 원할 때 각 행을 열거할 수 있습니다.그러기 위해서는 "order by" 절에 "select 1"을 입력하면 원하는 것을 얻을 수 있습니다.깔끔하지?

select row_number() over (order by (select 1)), * from dbo.Table as t

EncryptByKey를 통한 심플한 암호화

언급URL : https://stackoverflow.com/questions/121243/hidden-features-of-sql-server