SQL Server 2008的新数据类型(续)

非常遗憾,今天看完了美国一段录像,满心欢喜的去试验SQL Server 2008的FileStream数据类型,却发现如何调试都不能通过,最后证实FileStream,Date,Time,Spatial这些个新数据类型都要在今后版本的CTP中才会出现,不过按照明年2月发布的这个时间表,应该下个CTP就会出现了。不过看完录像,FileStream数据类型确实是一个不小的增强,今后保持BLOB数据和关系型数据的一致性就非常简单了,无需额外编程了。
这里先Post一段准备调试的代码,虽然要等下个版本才能调试:
!!md C:\Demo
CREATE DATABASE Demo
ON PRIMARY(
 NAME = MASTER_FILE,
 FILENAME = ‘C:\Demo\Demo_Master.mdf’
),
FILEGROUP DATA(
 NAME = DATA_FILE,
 FILENAME = ‘C:\Demo\Demo_Data.ndf’
),
FILEGROUP STREAM CONTAINS FILESTREAM(
 NAME = STREAM,
 FILENAME = ‘C:\Demo\Demo_Stream’
)
LOG ON(
 NAME = ‘DEMO_LOG’,
 FILENAME = ‘C:\Demo\Demo_Log.ldf’
)
GO
USE Demo
GO
CREATE TABLE Test_Stream(
 ID int,
 Doc varbinary(max) filestream,
 Version int default(1)
)
GO
CREATE TRIGGER update_version ON Test_Stream
FOR UPDATE
AS
IF UPDATE(Doc) BEGIN
 DECLARE @new_ver int
 SET @new_ver = SELECT Version+1 FROM deleted
 UPDATE Test_Stream SET Version = @new_ver
 WHERE ID = (SELECT ID FROM inserted)
END
GO
INSERT INTO Test_Stream VALUES (1,
 CAST(‘This is a test document…..’ AS varbinary(max)),
 1)
SELECT * FROM Test_Stream
BACKUP DATABASE Demo TO DISK = ‘C:\Demo\Demo.bak’ WITH FORMAT
GO
BEGIN TRAN
–you will get the tx param used later with following line code
SELECT Get_filestream_transaction_context()
–you will get the path param used later with line followed
SELECT Doc.PathName() FROM Test_Stream
–Win32 handle base read – obtain the handle by caling SqlOpenFileStream
–Use fsacreat -open -r -srwd -readfile -tx=<tx_context> <path_name>
–fsacreat -ow -w -srd -writefile="The document was updated!" -tx=<tx_context> <path_name>
–Read the doc content in TSQL
SELECT ID, CAST(Doc AS varchar(max)) AS Doc, Version FROM Test_Stream
–fsacreat -open -r -srwd -readfile
ROLLBACK TRAN
SELECT ID, CAST(Doc AS varchar(max)) AS Doc, Version FROM Test_Stream
BEGIN TRAN
SELECT Get_filestream_transaction_context()
SELECT Doc.PathName() FROM Test_Stream
–fsacreat -ow -w -srd -writefile="The document was updated!" -tx=<tx_context> <path_name>
SELECT ID, CAST(Doc AS varchar(max)) AS Doc, Version FROM Test_Stream
–This time we commit the transaction
COMMIT TRAN
SELECT ID, CAST(Doc AS varchar(max)) AS Doc, Version FROM Test_Stream
USE master
GO
RESTORE DATABASE Demo FROM DISK=’C:\Demo\Demo.bak’ WITH RECOVERY
GO
USE Demo
GO
–After restore, you will see the filestream content and version id was been recovered
SELECT ID, CAST(Doc AS varchar(max)) AS Doc, Version FROM Test_Stream
Advertisements
Post a comment or leave a trackback: Trackback URL.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: