|
MySQL 문자열 길이 구하는 함수
1. LENGTH() : 길이를 bytes로 나타낸다.
SELECT LENGTH('한글') -> 6
SELECT LENGTH('abc') -> 3
2. CHAR_LENGTH() : 글자의 수를 나타낸다.
SELECT CHAR_LENGTH('한글') -> 2
SELECT CHAR_LENGTH('abc') -> 3
3. BIT_LENGTH() : 길이를 bit로 나타낸다.
SELECT BIT_LENGTH('한글a') -> 56
SELECT BIT_LENGTH('한글') -> 48
** 참고로 MySQL에서는 UTF-8일 경우 한글은 3bytes, 영어는 1bytes 처리하네요 ^^
날짜타입 길이
smalldatetime 4bytes
datetime 8bytes
date 3bytes
date 타입은 SQL 2008 에 추가된 타입입니다.
참고 : http://technet.microsoft.com/en-us/library/ms187819.aspx
1. 현재값 확인
SELECT current_id = IDENT_CURRENT('Table_Name')
2. 초기화
DBCC CHECKIDENT('TableName', RESEED, 0)
3. 재설정
DBCC CHECKIDENT('TableName', RESEED)
현재 row 수 기준으로 초기화
원격디비 연결을 하기 위한 몇가지 방법
1. Openrowset
Syntax
OPENROWSET
( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password'
| 'provider_string' }
, { [ catalog. ] [ schema. ] object
| 'query'
}
| BULK 'data_file' ,
{ FORMATFILE = 'format_file_path' [ <bulk_options> ]
| SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
} )
<bulk_options> ::=
[ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
[ , ERRORFILE = 'file_name' ]
[ , FIRSTROW = first_row ]
[ , LASTROW = last_row ]
[ , MAXERRORS = maximum_errors ]
[ , ROWS_PER_BATCH = rows_per_batch ]
[ , ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) [ UNIQUE ]
>> SQL Server Native Client OLE DB Provider
SELECT a.*
FROM OPENROWSET('SQLNCLI',
'Server=Seattle1;
Trusted_Connection=yes;',
'SELECT GroupName, Name, DepartmentID
FROM AdventureWorks2008R2.HumanResources.Department
ORDER BY GroupName, Name') AS a;
>> Microsoft OLE DB Provider For Jet
SELECT CustomerID, CompanyName
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';
'admin';'',Customers);
>> Inner Join
USE Northwind ;
GO
SELECT c.*, o.*
FROM Northwind.dbo.Customers AS c
INNER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';'admin';'', Orders)
AS o
ON c.CustomerID = o.CustomerID ;
GO
>> Bulk insert file data into a varbinary(max) column
USE AdventureWorks2008R2;
GO
CREATE TABLE myTable(FileName nvarchar(60),
FileType nvarchar(60), Document varbinary(max));
GO
INSERT INTO myTable(FileName, FileType, Document)
SELECT 'Text1.txt' AS FileName, '.txt' AS FileType, *
FROM OPENROWSET(BULK N'C:\Text1.txt', SINGLE_BLOB) AS Document;
GO
>> From a txt file
USE AdventureWorks2008R2;
GO
CREATE TABLE myTable(FileName nvarchar(60),
FileType nvarchar(60), Document varbinary(max));
GO
INSERT INTO myTable(FileName, FileType, Document)
SELECT 'Text1.txt' AS FileName,
'.txt' AS FileType,
* FROM OPENROWSET(BULK N'C:\Text1.txt', SINGLE_BLOB) AS Document;
GO
이런 식으로 해주면 된다~
아래와 같이 미리 설정이 되어있어야한다..
REPAIR_FAST 또는 REPAIR_REBUILD 를 먼저 사용한 후 해결이 되지 않을 경우
REPAIR_ALLOW_DATA_LOSS 옵션을 쓰는게 좋다고 하는군요..
ALTER DATABASE Dxxxx SET multi_user
