집계 쿼리 (SUM, AVG, MIN, MAX, COUNT .. 등) 를 하다보면
"경고: 집계 또는 다른 SET 작업에 의해 Null 값이 제거되었습니다. "
이런 경고 메시지가 나오는 경우가 있다.
원인은 ..
집계 데이터에 Null 이 존재하는 경우이다.
이런 경우 Null을 제거하고 집계했다는 메시지인 듯하다.
따라서 결과는 잘 나온다.
테스트 ...
-- 테이블 생성
create table pjs_test
(
seqno int,
price int
)
-- 데이터 입력
insert into pjs_test
select 1, null
union all select 1, 100
-- 집계 쿼리
select sum(price) from pjs_test
-- 결과
끝
간혹, DB가 손상되는 경우가 있다.
하지만 이런 경우에도 DB 백업시 Checksum 옵션을 사용하지 않으면 정상 백업이 가능하다.
심지어 CheckSum 옵션을 사용했는데도 불구하고 정상 백업되는 경우가 있다.
이런 일을 방지하고자 한다면,
우선 dbcc checkdb 로 오류 검사를 한후
오류가 없는경우 Database Backup 을 하면 된다.
SET @sql = 'DBCC CHECKDB (' + QuoteName(@name) + ') WITH ... options ...' EXEC sp_executesql @sql IF @@ERROR <> 0 BEGIN .. Error Handler ... END ... Continue with backup ...
dbcc checkdb로 해당 DB를 체크한 후, 오류 발생시 @@ERROR가 0이 아니므로 가능한 코드겠다
* 참고
@@ERROR : https://technet.microsoft.com/ko-kr/library/ms190193(v=sql.105).aspx
* 원문
http://forums.sqlteam.com/t/only-create-backup-if-dbcc-checkdb-is-ok/9459/2
DB 에러 로그를 확인해보면.. 서비스를 재시작하지도 않았는데..
시도때도 없이 database를 시작하고 있다.
찾아봤더니.. AUTO_CLOSE 옵션 때문이라고함.
기본 ON으로 되어있어.. 자꾸 재시작하는 것~
이것 때문에 IO 부하가 생기고 Performance도 떨어짐
가끔 프로그램에서 DB 연결이 끊어지는 오류가 발생하기도 한다.
해결방법은
ALTER DATABASE [데이터베이스명] SET AUTO_CLOSE OFF
AUTO_CLOSE 를 OFF 하면 된다고.. 함.
* 참고
https://msdn.microsoft.com/en-us/library/ms190249.aspx
1. -- DB분리 ( DB가 사용중 일 경우 분리 안됨. 모든 연결을 끊고 한다.)
use master
exec sp_detach_db [데이터베이스명], 'true'
2. -- 파일 이동
mdf, ldf 파일을 이동한다.
3. -- DB 붙이기
exec sp_attach_db [데이터베이스명], 'MDF파일 물리 경로', 'LDF파일 물리 경로'
3.1. -- mdf만 붙이기
exec sp_attach_single_file_db [데이터베이스명], 'MDF파일 물리 경로'
3.2. -- 붙인DB 가 (읽기전용)인경우
옮긴 디렉터리 권한을 수정한 후 DB를 다시 분리(sp_detach_db) 후 붙인(sp_attach_db)다.
-- ON DELETE CASCADE 제약조건 실습..
-- 부모 테이블
CREATE TABLE TAB_A (
idx int identity(1,1) primary key,
value varchar(10)
)
-- 서브 테이블
CREATE TABLE TAB_A_SUB (
seq int identity(1,1) primary key,
value varchar(10),
idx int
)
-- FK 설정
ALTER TABLE [dbo].[TAB_A_SUB] WITH CHECK ADD CONSTRAINT [FK_TAB_A_idx] FOREIGN KEY([idx])
REFERENCES [dbo].[TAB_A] ([idx])
-- 데이터 삽입
INSERT INTO TAB_A (value) VALUES('aaa');
INSERT INTO TAB_A_SUB(value, idx) VALUES ('ttt', 1);
-- 삭제
DELETE [TAB_A] WHERE idx = 1
-- FK 제약조건때문에 삭제가 되지 않는다.
-- 삭제하려면 서브 테이블의 데이터 부터 지워야한다.
-- 그러나 ON DELETE CASCADE 제약조건을 서브 테이블에 부여해주면 부모테이블 삭제시 서브 테이블의 데이터도 함께 삭제 된다.
-- ON DELETE CASCADE 제약조건
ALTER TABLE [dbo].[TAB_A_SUB] ADD CONSTRAINT [FK_cascade_idx] FOREIGN KEY ([idx])
REFERENCES [dbo].[TAB_A]([idx]) ON DELETE CASCADE
-- 삭제
DELETE [TAB_A] WHERE idx = 1
MSSQL 에서 천단위 컴마를 찍고 싶을 때
SELECT CONVERT(VARCHAR(50), CAST(20000 AS MONEY), 1)
결과는 20,000.00
MONEY 타입은 소숫점 둘째자리까지 표현됨.
소숫점을 없애고 싶으면 REPLACE 하면됨
SELECT REPLACE(CONVERT(VARCHAR(50), CAST(20000 AS MONEY), 1) , '.00', '')
결과는 20,000
MSSQL의 CAST AND CONVERT 도움말 중
When expression is money or smallmoney, style can be one of the values shown in the following table. Other values are processed as 0.
|
Value |
Output |
|---|---|
|
0 (default) |
No commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 4235.98. |
|
1 |
Commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 3,510.92. |
|
2 |
No commas every three digits to the left of the decimal point, and four digits to the right of the decimal point; for example, 4235.9819. |
|
126 |
Equivalent to style 2 when converting to char(n) or varchar(n) |
DB 내용 입력중
줄바꿈 문자가 필요할 경우
print '이름 : ' + CHAR(13) + CHAR(10) + '김또깡'
이렇게 하면 된다.
줄바꿈 문자는 CHAR(13) + CHAR(10)
CHAR can be used to insert control characters into character strings.
The following table shows some frequently used control characters.
|
Control character |
Value |
|---|---|
|
Tab |
char(9) |
|
Line feed |
char(10) |
|
Carriage return |
char(13) |
Carriage Return & Line Feed
10진수 16진수 ASCII
10 0x0A LF
13 0x0D CR
캐리지 리턴 : 커서의 위치를 현재 줄의 맨 처음으로 보냄
라인 피드 : 커서를 다음 줄로 옮김.
유닉스는 LF만으로 개행을 표현하고 윈도우즈는 CR/LF의 조합으로 개행을 표현한다.
데이터를 암/복호화 하기위해 AES암호화를 사용하고있다.
헌데 java, C# 코드는 찾기 쉬운데 .. MSSQL에서 직접 사용할 수 있는 sp가 없더라..
그래서 만들어봤다.
.net 코드로 만든 클래스 라이브러리를 MSSQL에서 바로 호출이 가능하다.
방법은 이렇다.
1. 코드를 작성한다. (C#으로 되어있는 AES 암/복호화 코드를 copy해왔다.) 컴파일하여 .dll 파일을 얻는다.
namespace AESCrypto
{
public class CAesCrypto
{
public static void Encrypt(SqlString textToEncrypt, SqlString key, out SqlString strValue)
{
try
{
RijndaelManaged rijndaelCipher = new RijndaelManaged();
rijndaelCipher.Mode = CipherMode.CBC;
rijndaelCipher.Padding = PaddingMode.PKCS7;
rijndaelCipher.KeySize = 0x80;
rijndaelCipher.BlockSize = 0x80;
string strKey = key.ToString();
byte[] pwdBytes = Encoding.UTF8.GetBytes(strKey);
byte[] keyBytes = new byte[0x10];
int len = pwdBytes.Length;
if (len > keyBytes.Length)
{
len = keyBytes.Length;
}
Array.Copy(pwdBytes, keyBytes, len);
rijndaelCipher.Key = keyBytes;
rijndaelCipher.IV = keyBytes;
ICryptoTransform transform = rijndaelCipher.CreateEncryptor();
string strTextToEncrypt = textToEncrypt.ToString();
byte[] plainText = Encoding.UTF8.GetBytes(strTextToEncrypt);
strValue = Convert.ToBase64String(transform.TransformFinalBlock(plainText, 0, plainText.Length));
}
catch (Exception ex)
{
strValue = ex.ToString();
throw new Exception(ex.Message);
}
}
public static void Decrypt(SqlString textToDecrypt, SqlString key, out SqlString strValue)
{
try
{
RijndaelManaged rijndaelCipher = new RijndaelManaged();
rijndaelCipher.Mode = CipherMode.CBC;
rijndaelCipher.Padding = PaddingMode.PKCS7;
rijndaelCipher.KeySize = 0x80;
rijndaelCipher.BlockSize = 0x80;
string strTextToDecrypt = textToDecrypt.ToString();
byte[] encryptedData = Convert.FromBase64String(strTextToDecrypt);
string strKey = key.ToString();
byte[] pwdBytes = Encoding.UTF8.GetBytes(strKey);
byte[] keyBytes = new byte[0x10];
int len = pwdBytes.Length;
if (len > keyBytes.Length)
{
len = keyBytes.Length;
}
Array.Copy(pwdBytes, keyBytes, len);
rijndaelCipher.Key = keyBytes;
rijndaelCipher.IV = keyBytes;
byte[] plainText = rijndaelCipher.CreateDecryptor().TransformFinalBlock(encryptedData, 0, encryptedData.Length);
strValue = Encoding.UTF8.GetString(plainText);
}
catch (Exception ex)
{
strValue = ex.ToString();
throw new Exception(ex.Message);
}
}
}
}
2. MSSQL에서 CLR을 사용할수 있게 변경한다.
| -- CLR을 사용할수 있도록 변경 exec sp_configure 'clr enabled', 1 reconfigure with override |
3. 해당 DB에 권한을 부여한다.
|
ALTER DATABASE [DATABASE_NAME] SET |
4. 1에서 만든 DLL로 ASSEMBLY를 생성한다.
|
CREATE ASSEMBLY AESCrypto |
4.1. 소유자가 달라서 안되면...소유자를 변경한다.
- 해당메시지는 DB를 만든 사용자와 복원한 사용자가 다를때 나타난다.
| /* master 데이터베이스에 기록된 데이터베이스 소유자 SID가 데이터베이스 '[DATABASE_NAME]'에 기록된 데이터베이스 소유자 SID와 다릅니다. ALTER AUTHORIZATION 문을 사용하여 데이터베이스 '[DATABASE_NAME]'의 소유자를 다시 설정하여 이 문제를 해결해야 합니다. */ SELECT owner_sid FROM sys.databases WHERE database_id=DB_ID() SELECT sid FROM sys.database_principals WHERE name=N'dbo' EXEC sp_changedbowner '[USER_ID]' ALTER AUTHORIZATION ON [DATABASE_NAME] TO [domain\user] |
5. 프로시져를 생성한다.
--
CREATE PROCEDURE usp_AESCryptoDecrypt
@strText NVARCHAR(100),
@strKey NVARCHAR(50),
@strDec NVARCHAR(200) OUTPUT
AS EXTERNAL NAME AESCrypto.[AESCrypto.CAesCrypto].Decrypt
6. 사용해본다.
7. 참고 사이트 및 호환성 문제.
* 참고 : http://zmeun.tistory.com/46
* 호환성 문제.
출처 : http://livelock.tistory.com/entry/DB-%ED%98%B8%ED%99%98%EC%84%B1-%EB%AC%B8%EC%A0%9C
SQL Server 2000->2005 마이그레이션 등을 수행한 후,
2005부터 지원되는 SQL문을 사용하려고 하는 경우, 다음과 같은 메시지가 나타날 때가 있다.
Msg 325, Level 15, State 1, Line 6
'PIVOT' 근처의 구문이 잘못되었습니다. 이 기능을 사용하려면 현재 데이터베이스의 호환성 수준 값을 더 높게 설정해야 합니다. 저장 프로시저 sp_dbcmptlevel에 대해서는 도움말을 참조하십시오.
에러 메시지에서 볼 수 있듯이, 호환성과 관련된 문제이다.
따라서 다음과 같은 구문을 실행하여 호환성 문제를 해결한다.
호환성 번호는 80: SQL Server 2000, 90: SQL Server 2005 등이다.
* tempDB 를 tempDB2 로 변경하기
ALTER DATABASE tempDB
SET single_user
go
ALTER DATABASE tempDB
MODIFY NAME = tempDB2
go
ALTER DATABASE tempDB
SET multi_user
결과 집합 파티션 내의 행 일련 번호를 반환합니다. 각 파티션의 첫 번째 행은 1로 시작합니다.
FROM 절이 생성한 결과 집합을 ROW_NUMBER 함수가 적용되는 파티션으로 나눕니다. PARTITION BY 구문은 OVER 절(Transact-SQL)을 참조하십시오.
파티션에서 ROW_NUMBER 값이 행에 할당되는 순서를 결정합니다. 자세한 내용은 ORDER BY 절(Transact-SQL)를 참조하십시오. 순위 함수에 <order_by_clause>가 사용된 경우 정수는 열을 나타낼 수 없습니다.
 참고: |
|---|
OVER 절의 ORDER BY는 ROW_NUMBER를 정렬합니다. 'Row Number'가 아닌 열 기준으로 정렬하는 SELECT 문에 ORDER BY 절을 추가하면 결과 집합은 외부 ORDER BY를 기준으로 정렬됩니다.
|
다음 예에서는 연간 판매 실적에 따라 AdventureWorks의 영업 사원에 대한 ROW_NUMBER를 반환합니다.
USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
,ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number'
,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s
INNER JOIN Person.Contact c
ON s.SalesPersonID = c.ContactID
INNER JOIN Person.Address a
ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL
AND SalesYTD <> 0;
다음 예에서는 행 번호 50부터 60까지의 행을 OrderDate순으로 반환합니다.
USE AdventureWorks;
GO
WITH OrderedOrders AS
(
SELECT SalesOrderID, OrderDate,
ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
FROM Sales.SalesOrderHeader
)
SELECT *
FROM OrderedOrders
WHERE RowNumber BETWEEN 50 AND 60;
다음 예에서는 PARTITION BY 인수를 사용하는 방법을 보여 줍니다.
USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
,ROW_NUMBER() OVER
(PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS 'Row Number'
,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s
INNER JOIN Person.Contact c
ON s.SalesPersonID = c.ContactID
INNER JOIN Person.Address a
ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL
AND SalesYTD <> 0;
출처 : http://msdn.microsoft.com/ko-kr/library/ms186734(SQL.90).aspx