HoneyHoney

BEGIN TRANSACTION

DELETE TOP (1) p FROM tbl p 

EXEC ##TranLocks

ROLLBACK TRANSACTION

CREATE PROCEDURE ##TranLocks
    @objectName NVARCHAR(255) = '%',
    @requestMode VARCHAR(10) = '%',
    @processID INT = @@SPID,
    @ignoreSharedDatabaseLocks BIT = 1
AS
SELECT 
    DB_NAME() AS database_name,
    CASE 
        WHEN dt1.resource_type = 'OBJECT' THEN object_name(dt1.resource_associated_entity_id, resource_database_id) 
        WHEN dt1.resource_type IN('KEY', 'PAGE') THEN object_name(pa1.object_id) 
        ELSE dt1.resource_description
        END AS object_name,
    in1.name AS index_name,
    pa1.partition_number,
    dt1.resource_type,
    dt1.request_mode,
    COUNT(*) AS locks,
    dt1.request_owner_id,
    dt1.request_session_id
FROM
    sys.dm_tran_locks dt1
    LEFT OUTER JOIN sys.partitions pa1 ON(dt1.resource_associated_entity_id = pa1.hobt_id)
    LEFT OUTER JOIN sys.indexes in1 ON(pa1.object_id = in1.object_id AND pa1.index_id = in1.index_id)
WHERE 
    (request_session_id = @processID OR @processID = 0) 
    AND request_mode LIKE @requestMode
    AND(NOT(dt1.resource_type = 'DATABASE' AND dt1.request_mode = 'S') OR @ignoreSharedDatabaseLocks = 0)
    AND dt1.resource_database_id = DB_ID()
    AND 
        CASE 
            WHEN dt1.resource_type = 'OBJECT' THEN object_name(dt1.resource_associated_entity_id, resource_database_id) 
            WHEN dt1.resource_type IN('KEY', 'PAGE') THEN object_name(pa1.object_id) 
            ELSE dt1.resource_description
            END LIKE @objectName
GROUP BY
    dt1.resource_type,
    CASE 
        WHEN dt1.resource_type = 'OBJECT' THEN object_name(dt1.resource_associated_entity_id, resource_database_id) 
        WHEN dt1.resource_type IN('KEY', 'PAGE') THEN object_name(pa1.object_id) 
        ELSE dt1.resource_description
        END,
    in1.name,
    pa1.partition_number,
    dt1.request_mode,
    dt1.request_session_id,
    dt1.request_owner_id
ORDER BY database_name, object_name, index_name, partition_number, request_mode, locks;
GO

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的。