it's old fasion but important in some cases
DECLARE @AuthorID char(11)
DECLARE c1 CURSOR READ_ONLY
FOR
SELECT au_id
FROM authors
OPEN c1
FETCH NEXT FROM c1 INTO @AuthorID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @AuthorID
FETCH NEXT FROM c1
INTO @AuthorID
END
CLOSE c1
DEALLOCATE c1
Saturday, June 4, 2011
SQL Server 2008 (T-SQL) Creating FullText Index
this example work on adventureworks database.
for working with fulltext index, we need to create index on table . the following code create table with DocID . this column datatype is int with primary kry. note also the column DocContent with varbinary(max) and column DocSummary with nvarchar(max) . the last two column is our target of creating fulltext index.
CREATE TABLE _My_ProductDocs (
DocID INT NOT NULL IDENTITY,
DocTitle NVARCHAR(50) NOT NULL,
DocFilename NVARCHAR(400) NOT NULL,
FileExtension NVARCHAR(8) NOT NULL,
DocSummary NVARCHAR(MAX) NULL,
DocContent VARBINARY(MAX) NULL,
CONSTRAINT [PK_ProductDocs_DocID] PRIMARY KEY CLUSTERED (DocID ASC)
)
GO
--------------------------------------------------------------------------------
Now we need to filling data from Production.Document (adventureworks DB) to our created table.
INSERT INTO _My_ProductDocs
(DocTitle, DocFilename, FileExtension, DocSummary, DocContent)
SELECT Title, FileName, FileExtension, DocumentSummary, Document
FROM Production.Document
GO
--------------------------------------------------------------------------------
Showing data inserted in the our table
select * from _My_ProductDocs
--------------------------------------------------------------------------------
First step now is creating FULLTEXT Catalog.
--Creating the Full-Text Catalog
USE AdventureWorks
GO
CREATE FULLTEXT CATALOG My_ProductFTS
WITH ACCENT_SENSITIVITY = OFF
--------------------------------------------------------------------------------
To know defalut database collation, you can select from system view sys.databases the column name collation_name will tell about the collation of the selected database.
SELECT name, collation_name FROM sys.databases
WHERE name = 'AdventureWorks'
--------------------------------------------------------------------------------
Verify that the catalog has been created
SELECT fulltext_catalog_id, name FROM sys.fulltext_catalogs
--------------------------------------------------------------------------------
define a full-text index . it's need in this step to specify your columns that you need to make your fulltext index. in the following code fulltext index will create on our table _My_ProductDocs and use the columns (DocSummary - DocContent) . because of the DocContent has varbinary(max) datatype you can specify TYPE COLUMN keyword, it's help SQL Server to determine the type file for each cell binary in the DocContent column . also you need to specify the type of your language you will use by your fulltext index it's make by keyword LANGUAGE with the number of your language.
at general, you can't create fulltext index without a primarykey in your table. the creation of your fulltext index will use the primarykey constraint of your table PK_ProductDocs_DocID . fulltext index need also to associate himself with the name of specific fulltext catalog My_ProductFTS .
finally, you can attach s stoplist items for you fulltext index. stoplist item will help SQL Server to detect noisy words that you don't like to be included in your fulltext index.
the line of code WITH STOPLIST = SYSTEM mean that your fulltext index will be use the system STOPLIST the is found in SQL Server. you can make you owen list and configure that .
CREATE FULLTEXT INDEX ON _My_ProductDocs
(DocSummary, DocContent TYPE COLUMN FileExtension LANGUAGE 1033)
KEY INDEX PK_ProductDocs_DocID
ON My_ProductFTS
WITH STOPLIST = SYSTEM
--------------------------------------------------------------------------------
Create a stoplist
CREATE FULLTEXT STOPLIST ProductSL
FROM SYSTEM STOPLIST;
Verify that the stoplist has been created
SELECT stoplist_id, name FROM sys.fulltext_stoplists
---------------------------------------------------------------------------------
You can view stoplist contents
SELECT stopword FROM sys.fulltext_stopwords
WHERE stoplist_id = 5 AND language_id = 1033
---------------------------------------------------------------------------------
Add words to stoplist
ALTER FULLTEXT STOPLIST ProductSL
ADD 'nuts' LANGUAGE 1033;
---------------------------------------------------------------------------------
Test how SQL Server will tokenize a string based on a specific language and stoplist
SELECT special_term, display_term
FROM sys.dm_fts_parser
(' "testing for fruit and nuts, any type of nut" ', 1033, 5, 0)
---------------------------------------------------------------------------------
Drop “nuts” from the stoplist
ALTER FULLTEXT STOPLIST ProductSL
DROP 'nuts' LANGUAGE 1033;
-----------------------------------------------------------------------------------
Modify FULTEXT INDEX with new STOPLIST
ALTER FULLTEXT INDEX ON _My_ProductDocs
SET STOPLIST ProductSL
--------------------------------------------------------------------------------
Return a list of the document types supported by full-text search
SELECT document_type, version, manufacturer
FROM sys.fulltext_document_types
--------------------------------------------------------------------------------
View a list of the identifiers and their associated languages
SELECT * FROM sys.fulltext_languages
ORDER BY lcid
--------------------------------------------------------------------------------
Verify the index has been created and is associated with the correct catalog
SELECT t.name AS TableName, c.name AS FTCatalogName
FROM sys.tables t JOIN sys.fulltext_indexes i
ON t.object_id = i.object_id
JOIN sys.fulltext_catalogs c
ON i.fulltext_catalog_id = c.fulltext_catalog_id
---------------------------------------------------------------------------------
Lists the contents of the index
SELECT display_term, column_id, document_count
FROM sys.dm_fts_index_keywords
(DB_ID('AdventureWorks'), OBJECT_ID('_My_ProductDocs'))
---------------------------------------------------------------------------------
Modifying the Full-Text Thesaurus
EXEC sys.sp_fulltext_load_thesaurus_file 1033
for working with fulltext index, we need to create index on table . the following code create table with DocID . this column datatype is int with primary kry. note also the column DocContent with varbinary(max) and column DocSummary with nvarchar(max) . the last two column is our target of creating fulltext index.
CREATE TABLE _My_ProductDocs (
DocID INT NOT NULL IDENTITY,
DocTitle NVARCHAR(50) NOT NULL,
DocFilename NVARCHAR(400) NOT NULL,
FileExtension NVARCHAR(8) NOT NULL,
DocSummary NVARCHAR(MAX) NULL,
DocContent VARBINARY(MAX) NULL,
CONSTRAINT [PK_ProductDocs_DocID] PRIMARY KEY CLUSTERED (DocID ASC)
)
GO
--------------------------------------------------------------------------------
Now we need to filling data from Production.Document (adventureworks DB) to our created table.
INSERT INTO _My_ProductDocs
(DocTitle, DocFilename, FileExtension, DocSummary, DocContent)
SELECT Title, FileName, FileExtension, DocumentSummary, Document
FROM Production.Document
GO
--------------------------------------------------------------------------------
Showing data inserted in the our table
select * from _My_ProductDocs
--------------------------------------------------------------------------------
First step now is creating FULLTEXT Catalog.
--Creating the Full-Text Catalog
USE AdventureWorks
GO
CREATE FULLTEXT CATALOG My_ProductFTS
WITH ACCENT_SENSITIVITY = OFF
--------------------------------------------------------------------------------
To know defalut database collation, you can select from system view sys.databases the column name collation_name will tell about the collation of the selected database.
SELECT name, collation_name FROM sys.databases
WHERE name = 'AdventureWorks'
--------------------------------------------------------------------------------
Verify that the catalog has been created
SELECT fulltext_catalog_id, name FROM sys.fulltext_catalogs
--------------------------------------------------------------------------------
define a full-text index . it's need in this step to specify your columns that you need to make your fulltext index. in the following code fulltext index will create on our table _My_ProductDocs and use the columns (DocSummary - DocContent) . because of the DocContent has varbinary(max) datatype you can specify TYPE COLUMN keyword, it's help SQL Server to determine the type file for each cell binary in the DocContent column . also you need to specify the type of your language you will use by your fulltext index it's make by keyword LANGUAGE with the number of your language.
at general, you can't create fulltext index without a primarykey in your table. the creation of your fulltext index will use the primarykey constraint of your table PK_ProductDocs_DocID . fulltext index need also to associate himself with the name of specific fulltext catalog My_ProductFTS .
finally, you can attach s stoplist items for you fulltext index. stoplist item will help SQL Server to detect noisy words that you don't like to be included in your fulltext index.
the line of code WITH STOPLIST = SYSTEM mean that your fulltext index will be use the system STOPLIST the is found in SQL Server. you can make you owen list and configure that .
CREATE FULLTEXT INDEX ON _My_ProductDocs
(DocSummary, DocContent TYPE COLUMN FileExtension LANGUAGE 1033)
KEY INDEX PK_ProductDocs_DocID
ON My_ProductFTS
WITH STOPLIST = SYSTEM
--------------------------------------------------------------------------------
Create a stoplist
CREATE FULLTEXT STOPLIST ProductSL
FROM SYSTEM STOPLIST;
Verify that the stoplist has been created
SELECT stoplist_id, name FROM sys.fulltext_stoplists
---------------------------------------------------------------------------------
You can view stoplist contents
SELECT stopword FROM sys.fulltext_stopwords
WHERE stoplist_id = 5 AND language_id = 1033
---------------------------------------------------------------------------------
Add words to stoplist
ALTER FULLTEXT STOPLIST ProductSL
ADD 'nuts' LANGUAGE 1033;
---------------------------------------------------------------------------------
Test how SQL Server will tokenize a string based on a specific language and stoplist
SELECT special_term, display_term
FROM sys.dm_fts_parser
(' "testing for fruit and nuts, any type of nut" ', 1033, 5, 0)
---------------------------------------------------------------------------------
Drop “nuts” from the stoplist
ALTER FULLTEXT STOPLIST ProductSL
DROP 'nuts' LANGUAGE 1033;
-----------------------------------------------------------------------------------
Modify FULTEXT INDEX with new STOPLIST
ALTER FULLTEXT INDEX ON _My_ProductDocs
SET STOPLIST ProductSL
--------------------------------------------------------------------------------
Return a list of the document types supported by full-text search
SELECT document_type, version, manufacturer
FROM sys.fulltext_document_types
--------------------------------------------------------------------------------
View a list of the identifiers and their associated languages
SELECT * FROM sys.fulltext_languages
ORDER BY lcid
--------------------------------------------------------------------------------
Verify the index has been created and is associated with the correct catalog
SELECT t.name AS TableName, c.name AS FTCatalogName
FROM sys.tables t JOIN sys.fulltext_indexes i
ON t.object_id = i.object_id
JOIN sys.fulltext_catalogs c
ON i.fulltext_catalog_id = c.fulltext_catalog_id
---------------------------------------------------------------------------------
Lists the contents of the index
SELECT display_term, column_id, document_count
FROM sys.dm_fts_index_keywords
(DB_ID('AdventureWorks'), OBJECT_ID('_My_ProductDocs'))
---------------------------------------------------------------------------------
Modifying the Full-Text Thesaurus
EXEC sys.sp_fulltext_load_thesaurus_file 1033
encryption and decryption without keys or certificate
the following code will encrypt data and decrypt that with a simple two function EncryptByPassPhrase and DecryptByPassPhrase . the encryption with this function use Triple DES encryption algorithm by default.
by two side of encry or decry, you specify your keyword (the formula that will use for encry and decry). if you encry data by keyword 'cairo city' for example, then you must decry you data by the same keyword. if you use another keyword you will fail to decry your data.
Note that every time you make a encry for fixed string with same keyword encry the result will difference.
DECLARE @org_text nvarchar(1000), @enc_text varbinary(2000);
SET @org_text = N'Type you data here';
SET @enc_text = EncryptByPassPhrase(N'your keyword', @org_text);
SELECT 'Original text = ', @org_text;
SELECT 'Encrypted text = ', @enc_text;
SELECT 'Decrypted plain text = ',
CAST(DecryptByPassPhrase(N'your keyword', @enc_text) AS nvarchar(1000));
by two side of encry or decry, you specify your keyword (the formula that will use for encry and decry). if you encry data by keyword 'cairo city' for example, then you must decry you data by the same keyword. if you use another keyword you will fail to decry your data.
Note that every time you make a encry for fixed string with same keyword encry the result will difference.
DECLARE @org_text nvarchar(1000), @enc_text varbinary(2000);
SET @org_text = N'Type you data here';
SET @enc_text = EncryptByPassPhrase(N'your keyword', @org_text);
SELECT 'Original text = ', @org_text;
SELECT 'Encrypted text = ', @enc_text;
SELECT 'Decrypted plain text = ',
CAST(DecryptByPassPhrase(N'your keyword', @enc_text) AS nvarchar(1000));
Tuesday, May 31, 2011
SQL Server encryption and decryption data with keys and certificate
create database mydb
-----------------------------------------------------------------------------
CREATE TABLE Employee
(Emp_ID int NOT NULL,
Emp_Date datetime DEFAULT GETDATE() NOT NULL,
Emp_Comments varchar(2000) NOT NULL)
-----------------------------------------------------------------------------
USE [master]
GO
CREATE LOGIN [User1] WITH PASSWORD=N'Pa$$w0rd'
GO
USE [mydb]
GO
CREATE USER [User1] FOR LOGIN [User1]
GO
-----------------------------------------------------------------------------
GRANT SELECT, INSERT, UPDATE ON Employee
TO User1
-- Create database master key
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'Pa$$w0rd'
-- Create certificate
CREATE CERTIFICATE MyCert
AUTHORIZATION User1
WITH SUBJECT = 'test certificate'
GO
-- Create symmetric key
CREATE SYMMETRIC KEY MyKey
AUTHORIZATION User1
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE myCert
GO
-- Insert encrypted data
EXECUTE AS USER = 'User1'
OPEN SYMMETRIC KEY MyKey DECRYPTION BY CERTIFICATE MyCert
INSERT INTO Employee
VALUES
(1, DEFAULT, EncryptByKey(Key_GUID('MyKey'),'Employee comment data'))
CLOSE ALL SYMMETRIC KEYS
REVERT
-- Read the decrypted data
EXECUTE AS USER = 'User1'
OPEN SYMMETRIC KEY MyKey DECRYPTION BY CERTIFICATE MyCert
SELECT CONVERT(varchar,DecryptByKey(Emp_Comments)) AS Comments FROM Employee
CLOSE ALL SYMMETRIC KEYS
REVERT
-----------------------------------------------------------------------------
CREATE TABLE Employee
(Emp_ID int NOT NULL,
Emp_Date datetime DEFAULT GETDATE() NOT NULL,
Emp_Comments varchar(2000) NOT NULL)
-----------------------------------------------------------------------------
USE [master]
GO
CREATE LOGIN [User1] WITH PASSWORD=N'Pa$$w0rd'
GO
USE [mydb]
GO
CREATE USER [User1] FOR LOGIN [User1]
GO
-----------------------------------------------------------------------------
GRANT SELECT, INSERT, UPDATE ON Employee
TO User1
-- Create database master key
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'Pa$$w0rd'
-- Create certificate
CREATE CERTIFICATE MyCert
AUTHORIZATION User1
WITH SUBJECT = 'test certificate'
GO
-- Create symmetric key
CREATE SYMMETRIC KEY MyKey
AUTHORIZATION User1
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE myCert
GO
-- Insert encrypted data
EXECUTE AS USER = 'User1'
OPEN SYMMETRIC KEY MyKey DECRYPTION BY CERTIFICATE MyCert
INSERT INTO Employee
VALUES
(1, DEFAULT, EncryptByKey(Key_GUID('MyKey'),'Employee comment data'))
CLOSE ALL SYMMETRIC KEYS
REVERT
-- Read the decrypted data
EXECUTE AS USER = 'User1'
OPEN SYMMETRIC KEY MyKey DECRYPTION BY CERTIFICATE MyCert
SELECT CONVERT(varchar,DecryptByKey(Emp_Comments)) AS Comments FROM Employee
CLOSE ALL SYMMETRIC KEYS
REVERT
Monday, May 30, 2011
Inserting files to database
inserting data from files (jpg, xml, avi ....) into database, easy by the follwing code.
INSERT INTO Photos (FileName, FileType, Picture)
SELECT 'Photo1.jpg' AS FileName,
'.jpg' AS FileType,
* FROM OPENROWSET(BULK N'C:\Photo1.jpg', SINGLE_BLOB) AS Picture
as you see, in the section of bulk you put the path od the file that you want to put it in your database. any kind of file you can put. but you need to specify your data type column to varbinary(max) .
the following code try to insert multiple pics into database.
Declare @imgNumber int
DECLARE @imgString varchar(80)
DECLARE @insertString varchar(3000)
SET @imgNumber = 1
WHILE @imgNumber > 101
BEGIN
SET @imgString = 'E:\images\Picture' + CONVERT(varchar,@imgNumber) + '.jpg'
SET @insertString = N'INSERT INTO images(imageData)
SELECT * FROM OPENROWSET(BULK N''' + @imgString + ''', SINGLE_BLOB) as tempImg'
EXEC(@insertString)
SET @imgNumber = @imgNumber + 1
END
GO
INSERT INTO Photos (FileName, FileType, Picture)
SELECT 'Photo1.jpg' AS FileName,
'.jpg' AS FileType,
* FROM OPENROWSET(BULK N'C:\Photo1.jpg', SINGLE_BLOB) AS Picture
as you see, in the section of bulk you put the path od the file that you want to put it in your database. any kind of file you can put. but you need to specify your data type column to varbinary(max) .
the following code try to insert multiple pics into database.
Declare @imgNumber int
DECLARE @imgString varchar(80)
DECLARE @insertString varchar(3000)
SET @imgNumber = 1
WHILE @imgNumber > 101
BEGIN
SET @imgString = 'E:\images\Picture' + CONVERT(varchar,@imgNumber) + '.jpg'
SET @insertString = N'INSERT INTO images(imageData)
SELECT * FROM OPENROWSET(BULK N''' + @imgString + ''', SINGLE_BLOB) as tempImg'
EXEC(@insertString)
SET @imgNumber = @imgNumber + 1
END
GO
UNIQUEIDENTIFIER with ROWGUIDCOL
Example 1 :
CREATE TABLE MyTable (ID UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(), nam varchar(max))
insert into MyTable (nam) values ('mohamed')
insert into MyTable (nam) values ('tareq')
insert into MyTable (nam) values ('emad')
insert into MyTable (nam) values ('ahmed')
select * from MyTable
Example 2 :
CREATE TABLE MyTable2 (ID UNIQUEIDENTIFIER ROWGUIDCOL PRIMARY KEY DEFAULT NEWSEQUENTIALID() , nam varchar(max))
insert into MyTable2 (nam) values ('mohamed')
insert into MyTable2 (nam) values ('tareq')
insert into MyTable2 (nam) values ('emad')
insert into MyTable2 (nam) values ('ahmed')
select * from MyTable2
select ROWGUIDCOL from MyTable2
select OBJECTPROPERTY (object_id('table1'),'TableHasRowGuidCol')
--Notes
--1) The UNIQUEIDENTIFIER along with ROWGUIDCOL NEWSEQUENTIALID() is far more efficient than normal UNIQUEIDENTIFIER along with NEWID().
--2) The Unique Ids generated with the second approach are sequential in nature, similar to IDENTITY values.
--3) There can be max of one ROWGUIDCOL for a table.
--4) You can query the table for ROWGUIDCOL. Example: SELECT ROWGUIDCOL FROM MyTable
CREATE TABLE MyTable (ID UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(), nam varchar(max))
insert into MyTable (nam) values ('mohamed')
insert into MyTable (nam) values ('tareq')
insert into MyTable (nam) values ('emad')
insert into MyTable (nam) values ('ahmed')
select * from MyTable
Example 2 :
CREATE TABLE MyTable2 (ID UNIQUEIDENTIFIER ROWGUIDCOL PRIMARY KEY DEFAULT NEWSEQUENTIALID() , nam varchar(max))
insert into MyTable2 (nam) values ('mohamed')
insert into MyTable2 (nam) values ('tareq')
insert into MyTable2 (nam) values ('emad')
insert into MyTable2 (nam) values ('ahmed')
select * from MyTable2
select ROWGUIDCOL from MyTable2
select OBJECTPROPERTY (object_id('table1'),'TableHasRowGuidCol')
--Notes
--1) The UNIQUEIDENTIFIER along with ROWGUIDCOL NEWSEQUENTIALID() is far more efficient than normal UNIQUEIDENTIFIER along with NEWID().
--2) The Unique Ids generated with the second approach are sequential in nature, similar to IDENTITY values.
--3) There can be max of one ROWGUIDCOL for a table.
--4) You can query the table for ROWGUIDCOL. Example: SELECT ROWGUIDCOL FROM MyTable
Sunday, May 29, 2011
SQL Server 2008 Streaming
it's new feature in sql server 2008. in the installation steps you can support you server to working with stream .
before working with database stream, goto configuration manager and select properties of your instance that you want to act with streaming.
then open new query in sql server and write the following code to full access enabed file stream on server level .
-- filestream configuration
-- The last parameter to sp_configure specifies the Access Level,
-- where 0 means ‘Disabled’,
-- 1 means ‘Transact-SQL Access Enabled’ and
-- 2 means ‘Full Access Enabled’
EXEC sp_configure filestream_access_level, 2
GO
RECONFIGURE
GO
-- filestream configuration
-- The last parameter to sp_configure specifies the Access Level,
-- where 0 means ‘Disabled’,
-- 1 means ‘Transact-SQL Access Enabled’ and
-- 2 means ‘Full Access Enabled’
EXEC sp_configure filestream_access_level, 2
GO
RECONFIGURE
GO
CREATE DATABASE NorthPole
ON
PRIMARY (
NAME = NorthPoleDB,
FILENAME = 'C:\NorthPoleDB.mdf'
), FILEGROUP NorthPoleFS CONTAINS FILESTREAM(
NAME = NorthPoleFS,
FILENAME = 'C:\NorthPoleFS')
LOG ON (
NAME = NorthPoleLOG,
FILENAME = 'C:\NorthPoleLOG.ldf')
GO
return to database to see the effective of above code .
Return to your query again, and past the following code.
CREATE TABLE [dbo].[Items](
[ItemID] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,[ItemNumber] VARCHAR(20),
[ItemDescription] VARCHAR(50),
[ItemImage] VARBINARY(MAX) FILESTREAM NULL
)
the above code is about create table Items, note the column ItemImage it's varbinary(max) datatype and also filestream. filestrem keyword just attribute behinde varbinary datatype , it's note a datdatype. filestrem keyword found here for working with streaming database.
ItemID column in previous code with datatype UNIQUEIDENTIFIER, for working with streaming in your datdabase add attribute ROWGUIDCOL. this attribute keep UNIQUEIDENTIFIER with sequential result as you know with normal identity column result.
the final step now is to insert binary data to the previous table .
-- Declare a variable to store the image data
DECLARE @img AS VARBINARY(MAX)
-- Load the image data
SELECT @img = CAST(bulkcolumn AS VARBINARY(MAX))
FROM OPENROWSET(
BULK 'C:\img1.jpg',
SINGLE_BLOB ) AS x
-- Insert the data to the table
INSERT INTO Items (ItemID, ItemNumber, ItemDescription, ItemImage)
SELECT NEWID(), 'MS1001','Microsoft Mouse', @img
the previous code try to insert image file named 'img1.jpg' to table items.
but what about streaming, the above code can be run withtout any previous configuration . it's mean that the streaming database not mean inserting data to database, but it's mean how to read data binary from database by synchronise manner.
all previous configuration let you working with streaming database, to read your data by stream manner you can do that by programming language such as vb.net or c#.
Subscribe to:
Posts (Atom)