HoneyHoney

NVARCHAR(MAX)

IF OBJECT_ID('LobData') IS NOT NULL
    DROP TABLE dbo.LobData

create table dbo.LobData
(
    ID int not null,
    Col1 nvarchar(max) null
);

insert into dbo.LobData(ID, Col1) 
values (1, replicate(convert(varchar(max),'a'),16000));


SELECT allocated_page_file_id as PageFID, allocated_page_page_id as PagePID,
       object_id as ObjectID, partition_id AS PartitionID,
       allocation_unit_type_desc as AU_type, page_type as PageType
FROM sys.dm_db_database_page_allocations(db_id('test_db'), object_id('LobData'),
                                          null, null, 'DETAILED');
GO

DBCC TRACEON (3604); 
GO 
DBCC PAGE ('test_db', 1, 623876, 3); 
GO

1. 04 00000001 00000020 1c0000 metadata
2. 68 1f0000 length
3. 01 850900 page number
4. 01 00 file number
5. 0000 slow number

TEXT

VARCHAR(MAX)

IF OBJECT_ID('LobData') IS NOT NULL
    DROP TABLE dbo.LobData

create table dbo.LobData
(
    ID int not null,
    Col1 varchar(max) null
);

insert into dbo.LobData(ID, Col1) 
values (1, replicate(convert(varchar(max),'a'),400));

1. http://aboutsqlserver.com/2013/11/05/sql-server-storage-engine-lob-storage/
2. http://improve.dk/what-is-the-size-of-the-lob-pointer-for-max-types-like-varchar-varbinary-etc/

IF OBJECT_ID('RowOverflow') IS NOT NULL
    DROP TABLE RowOverflow;
GO
create table dbo.RowOverflow 
( 
    ID int not null, 
    Col1 varchar(8000) null, 
    Col2 varchar(8000) null 
);

insert into dbo.RowOverflow(ID, Col1, Col2) 
values (1,replicate('a',8000),replicate('b',8000));

SELECT allocated_page_file_id as PageFID, allocated_page_page_id as PagePID,
       object_id as ObjectID, partition_id AS PartitionID,
       allocation_unit_type_desc as AU_type, page_type as PageType
FROM sys.dm_db_database_page_allocations(db_id('test_db'), object_id('RowOverflow'),
                                          null, null, 'DETAILED');
GO

DBCC TRACEON (3604); 
GO 
DBCC PAGE ('test_db', 1, 610871, 3); 
GO

1. 02000000 01000000 29000000 401f0000 是metadata attribute。
2. 31520900 是page number
3. 0001 是file number
4. 0000 是slot number

The first 16 bytes of a row-overflow pointer

DBCC PAGE ('test_db', 1, 610871, 3); 
GO

1. http://aboutsqlserver.com/2013/11/05/sql-server-storage-engine-lob-storage/
2. https://www.microsoftpressstore.com/articles/article.aspx?p=2225060

USE Master; 
GO  
SET NOCOUNT ON 

-- 1 - Variable declaration 
DECLARE @dbName sysname 
DECLARE @backupPath NVARCHAR(500) 
DECLARE @dbDataPath NVARCHAR(500)
DECLARE @cmd NVARCHAR(500) 
DECLARE @fileList TABLE (backupFile NVARCHAR(255)) 
DECLARE @lastFullBackup NVARCHAR(500) 
DECLARE @backupFile NVARCHAR(500) 
DECLARE @NoExec bit
DECLARE @debug bit


-- 2 - Initialize variables 
SET @dbName = 'KEY_WORD_IN_YOUR_BACKUP_FILE' 
SET @backupPath = 'D:\' 
SET @dbDataPath = 'D:\Data\'
SET @NoExec = 1
SET @debug = 1

-- 3 - get list of files 
SET @cmd = 'DIR /b ' + @backupPath 

INSERT INTO @fileList(backupFile) 
EXEC master.sys.xp_cmdshell @cmd 

-- 4 - Find latest full backup 
SELECT @lastFullBackup = MAX(backupFile)  
FROM @fileList  
WHERE backupFile LIKE '%.BAK'  
   AND backupFile LIKE 'Servlet.' + @dbName + '%' 

SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = '''  
       + @backupPath + @lastFullBackup + '''' + CHAR(10) + 'WITH RECOVERY, REPLACE,'  + CHAR(10)
  + 'MOVE N''RECNETSTARTUP_dat'' TO N''' + @dbDataPath + @dbName + '.mdf'','   + CHAR(10)
  + 'MOVE N''RECNETSTARTUP_log'' TO N''' + @dbDataPath + @dbName + '_log.LDF'',  NOUNLOAD,  STATS = 5'

IF @debug = 1
PRINT @cmd 
IF @NoExec <> 1
EXEC (@cmd)

IF OBJECT_ID('DBO.T1') IS NOT NULL
    DROP TABLE t1

CREATE TABLE T1(
    ID BIGINT,
    ANOTHER_ID INT,
    DATA VARCHAR(200),
    CONSTRAINT PK_T1 PRIMARY KEY CLUSTERED (ID)
)

INSERT INTO T1(ID, ANOTHER_ID,DATA)
SELECT n,
    n%4, 
    CASE WHEN n % 4 = 1 THEN 'pHoEnIx'
                    WHEN n % 4 = 2 THEN 'eRiK'
                    WHEN n % 4 = 3 THEN 'dBe'
                    ELSE 'Papa'
                    END
FROM dbo.getnums(50000)

CREATE NONCLUSTERED INDEX idx_t1_another_id ON t1(another_id)

SELECT ID FROM T1
SELECT DATA FROM T1

CREATE TABLE T2(
    ID INT
)

INSERT INTO T2(ID)
SELECT n*4
FROM dbo.getnums(50000)

DELETE FROM T2 WHERE NOT EXISTS(SELECT 1 FROM T1 WHERE T1.ID = t2.ID)

• Oracle Instant Client
• ruby-oci8 gem
• activerecord-oracle_enhanced-adapter gem

安装Oracle Instant Client

unzip instantclient-basic-macos.x64-12.1.0.2.0.zip
unzip instantclient-sqlplus-macos.x64-12.1.0.2.0.zip
unzip instantclient-sdk-macos.x64-12.1.0.2.0.zip

cd /opt/oracle/instantclient_12_1
ln -s libclntsh.dylib.12.1 libclntsh.dylib

ln -s libocci.dylib.12.1 libocci.dylib

export ORACLE_HOME=/opt/oracle/instantclient_12_1
export OCI_DIR=/opt/oracle/instantclient_12_1
export PATH=$ORACLE_HOME:$PATH
export TNS_ADMIN=$HOME
export NLS_LANG="AMERICAN_AMERICA.UTF8"

安装Gem

gem install 'ruby-oci8' -v '~> 2.1.0'

gem install 'activerecord-oracle_enhanced-adapter' -v '~> 1.5.0'

测试

ActiveRecord::Base.establish_connection(
    :adapter => "oracle_enhanced",
    :database => "database",
    :username => "username",
    :password => "password")

    cursor = ActiveRecord::Base.connection.execute("SELECT 1 n FROM table")

    # query data
    result_data = []

    result_data << cursor.column_metadata.map { |e| e.name }

    while row = cursor.fetch
        result_data << row
    end

1. ODBC。Open Database Connectivity，是很老的一个数据库连接API。现在基本上没有用了。
2. 微软后来开发了OLE DB，算是ODBC的替代品，同时支持更多的数据源，比如spreadsheets
3. ADO.NET是基于.NET framework来连接关系型和非关系型数据库。看上去像ADO的进化版，实际上完全是全新的东西。
4. JDBC。同ODBC，不过是给JAVA用的。
5. JDBC有两款driver，一个是微软自己开发的sqljdbc4，另外一个是jtds。前者不支持NamedPipe。
6. 可以通过
   jdbc:jtds:sqlserver://./DatabaseName;instance=LOCALDB#88893A09;namedPipe=true 连接namedPipe
7. jtds是基于FreeTds。
8. ruby下面tiny_tds也是基于FreeTds的。