Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

My technology blog on SQL Server, TSQL, XML, FILESTREAM and other areas of SQL Server.
Browse by Tags · View All
XML 112
TSQL 69
XQuery 69
XQuery Functions 67
XQuery Training 65
XQuery in TSQL 64
XQuery Tutorial 63
SQL Server XQuery 63
XQuery-Labs 57
BRH 38

Archive · View All
September 2008 32
August 2008 30
July 2008 21
August 2009 19
June 2009 19
May 2010 18
January 2009 15
January 2010 14
October 2008 14
June 2008 13

SQL Server–Partitioning FILESTREAM data with RANGE LEFT does not work

Jun 12 2011 8:15PM by Jacob Sebastian   

One of the problems I came across while writing the partitioning chapter for my FILESTREAM book is that the partitioning does not work correctly with RANGE LEFT. It works as expected when using RANGE RIGHT. Something is wrong with RANGE LEFT and the problem exists in SQL Server 2008, R2 and Denali CTP1.

I created a repro script and wanted to submit a connect bug. However, the connect page does not allow me to post scripts longer than 2000 characters. So I thought of posting the script here and add a link to this post in the connect item. Here is the repro script for those of you who are interested to try it, and here is the connect item I submitted.

-- -----------------------------------------------
-- Create the database
-- -----------------------------------------------
USE master
GO

IF DB_ID('NorthPole') IS NOT NULL 
	DROP DATABASE NorthPole 
GO

CREATE DATABASE NorthPole ON
PRIMARY ( 
    NAME = NorthPoleData1, 
    FILENAME = 'C:\Demos\Data\NorthPoleData1.mdf'),
FILEGROUP NorthPoleDB2(
    NAME = NorthPoleData2, 
    FILENAME = 'C:\Demos\Data\NorthPoleData2.ndf'),
FILEGROUP NorthPoleFS1 CONTAINS FILESTREAM DEFAULT( 
    NAME = NorthPoleFS1,
    FILENAME = 'C:\Demos\FS\NorthPoleFS1'),
FILEGROUP NorthPoleFS2 CONTAINS FILESTREAM( 
    NAME = NorthPoleFS2,
    FILENAME = 'C:\Demos\FS\NorthPoleFS2')
LOG ON ( 
    NAME = NorthPoleLOG,
    FILENAME = 'C:\Demos\Data\NorthPoleLOG.ldf')
GO

-- -----------------------------------------------
-- Create Partition Function and Scheme
-- -----------------------------------------------
USE NorthPole 
GO

CREATE PARTITION FUNCTION NPPartFN (INT) AS 
RANGE LEFT FOR VALUES (3)

CREATE PARTITION SCHEME NPPartDBSch AS 
PARTITION NPPartFN TO([PRIMARY],[NorthPoleDB2]) 
GO

CREATE PARTITION SCHEME NPPartFSSch AS 
PARTITION NPPartFN TO(NorthPoleFS1, NorthPoleFS2) 
GO

-- -----------------------------------------------
-- Create Table
-- -----------------------------------------------
CREATE TABLE [dbo].[Items](
	[ItemID] [int] IDENTITY(1,1) PRIMARY KEY ON NPPartDBSch(ItemID),
	[ItemGuid] [uniqueidentifier] ROWGUIDCOL  NOT NULL 
		UNIQUE ON [PRIMARY],
	[ItemNumber] [varchar](20) NULL,
	[ItemDescription] [varchar](50) NULL,
	[ItemImage] [varbinary](max) FILESTREAM  NULL
) ON NPPartDBSch(ItemID)
FILESTREAM_ON NPPartFSSch

-- -----------------------------------------------
-- Insert 6 Rows. 1-3 will go to FS1 and 4-6 will
--   go to FS2
-- -----------------------------------------------
INSERT INTO Items(itemguid, itemnumber, itemdescription, ItemImage)
SELECT NEWID(), 'ITM001', 'Item 1', CAST(1 AS VARBINARY(MAX))
UNION ALL
SELECT NEWID(), 'ITM002', 'Item 2', CAST(2 AS VARBINARY(MAX))
UNION ALL
SELECT NEWID(), 'ITM003', 'Item 3', CAST(3 AS VARBINARY(MAX))
UNION ALL
SELECT NEWID(), 'ITM004', 'Item 4', CAST(4 AS VARBINARY(MAX))
UNION ALL
SELECT NEWID(), 'ITM005', 'Item 5', CAST(5 AS VARBINARY(MAX))
UNION ALL
SELECT NEWID(), 'ITM006', 'Item 6', CAST(6 AS VARBINARY(MAX))


-- -----------------------------------------------
-- Add a new file group (DB and FS)
-- -----------------------------------------------
ALTER DATABASE NorthPole 
ADD FILEGROUP NorthPoleFS3 CONTAINS FILESTREAM

ALTER database NorthPole 
ADD FILE 
( 
    NAME = 'NorthPoleFS3', 
    FILENAME = 'C:\Demos\FS\NorthPoleFS3' 
) 
TO FILEGROUP NorthPoleFS3

ALTER DATABASE NorthPole 
ADD FILEGROUP NorthPoleDB3

ALTER database NorthPole 
ADD FILE 
( 
    NAME = 'NorthPoleDB3', 
    FILENAME = 'C:\Demos\Data\NorthPoleData3.ndf' 
) 
TO FILEGROUP NorthPoleDB3


-- -----------------------------------------------
-- Insert 3 more records. This will go to FS2
-- -----------------------------------------------
INSERT INTO Items(itemguid, itemnumber, itemdescription, ItemImage)
SELECT NEWID(), 'ITM007', 'Item 7', CAST(7 AS VARBINARY(MAX))
UNION ALL
SELECT NEWID(), 'ITM008', 'Item 8', CAST(8 AS VARBINARY(MAX))
UNION ALL
SELECT NEWID(), 'ITM009', 'Item 9', CAST(9 AS VARBINARY(MAX))


-- -----------------------------------------------
-- Switch partitions. Alter the partition function
--   so that rows 7 to 9 will go to the new 
--   file group
-- -----------------------------------------------
ALTER PARTITION SCHEME NPPartFSSch  
NEXT USED NorthPoleFS3

ALTER PARTITION SCHEME NPPartDBSch 
NEXT USED NorthPoleDB3

ALTER PARTITION FUNCTION NPPartFN() 
SPLIT RANGE (6);

-- -----------------------------------------------
-- After the above, a new FILESTREAM folder is
--   created in FS1. No data goes to FS3
-- -----------------------------------------------

Tags: FILESTREAM, TSQL, SQL SERVER, BRH, #TSQL, #SQL Server,


Jacob Sebastian
1 · 100% · 32220
3
 
0
Lifesaver
 
0
Refreshed
 
 
0
Incorrect



Submit

14  Comments  

  • Do you have any idea about 'Content search with sql filestream'? I want to use sql filestream, and there in my project I have NEAR search as well as WildCard search, so I want to know could sql filestream be used for Proximity or WildCard search through the documents.

    commented on Oct 24 2011 4:05AM
    RobinJain
    327 · 0% · 129
  • FILESTREAM supports Full Text Search (FTS). Therefore, you can create an FTS catalog on your FILESTREAM column and you should be able to perform all the search functionalities offered by FTS on the data stored in a FILESTREAM column.

    commented on Oct 24 2011 4:24AM
    Jacob Sebastian
    1 · 100% · 32220
  • I think FILESTREAM column just has files' names, so is it like I can only search for documents by their names. Or I can use FTS, proximity search on the actual content the file has.

    commented on Oct 24 2011 4:39AM
    RobinJain
    327 · 0% · 129
  • A FILESTREAM column contains the the entire content of the file.

    commented on Oct 24 2011 5:08AM
    Jacob Sebastian
    1 · 100% · 32220
  • I just tried to define Full Text Index on a table created with FILESTREAM column, but its not being defined. Without a defined full text index FREETEXT queries can't be executed. Do I need to do something different?

    commented on Oct 24 2011 6:14AM
    RobinJain
    327 · 0% · 129
  • What do you mean by "not being defined"? I am able to create FTS catalogs on FILESTREAM columns. Probably, it will be beneficial if you write down the exact steps you are performing and post it here.

    commented on Oct 24 2011 6:53AM
    Jacob Sebastian
    1 · 100% · 32220
  • Step 1: Create Database

    USE [master]
    GO
    
    /****** Object:  Database [Archive]    Script Date: 10/24/2011 17:37:59 ******/
    CREATE DATABASE [Archive] ON  PRIMARY 
    ( NAME = N'Arch1', FILENAME = N'C:\Program Files\Microsoft SQL Server\ MSSQL10_50.SQLEXPRESS\MSSQL\DATA\archdat1.mdf' , 
      SIZE = 2304KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 
     FILEGROUP [FileStreamGroup1] CONTAINS FILESTREAM  DEFAULT 
    ( NAME = N'Arch3', FILENAME = N'C:\Program Files\Microsoft SQL Server\ MSSQL10_50.SQLEXPRESS\MSSQL\DATA\filestream1' )
     LOG ON 
    ( NAME = N'Archlog1', FILENAME = N'C:\Program Files\Microsoft SQL Server\ MSSQL10_50.SQLEXPRESS\MSSQL\DATA\archlog1.ldf' , 
      SIZE = 1024KB ,
       MAXSIZE = 2048GB , FILEGROWTH = 10%)
    GO
    
    ALTER DATABASE [Archive] SET COMPATIBILITY_LEVEL = 100
    GO
    
    IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
    begin
    EXEC [Archive].[dbo].[sp_fulltext_database] @action = 'enable'
    end
    GO
    
    **Step 2: Create Table named Test and index PK_Test**
    CREATE TABLE Test
    (
        [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE, 
        [SerialNumber] INTEGER UNIQUE,
        [Chart] VARBINARY(MAX) FILESTREAM NULL,
    CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED 
    (
        [Id] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON,
        ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    **Step 3: Create CATALOG**
    CREATE FULLTEXT CATALOG [archive_catalog]WITH ACCENT_SENSITIVITY = ON
    AUTHORIZATION [dbo]
    

    Step 4: Create FULL TEXT INDEX on column CHART Here I am unable to create full text index.

    commented on Oct 24 2011 7:26AM
    RobinJain
    327 · 0% · 129
  • What is the exact error you are getting? I would suggest you try the following script which is working correctly on my end. Run each step and see at what point it fails. You will have make some changes to the file system path used in the script.

    Creating a Full Text Index Catalog (FTS) on a FILESTREAM column

    commented on Oct 24 2011 8:03AM
    Jacob Sebastian
    1 · 100% · 32220
  • I am unable to view my comments, what should I do to view them.

    commented on Oct 31 2011 12:30AM
    RobinJain
    327 · 0% · 129
  • Which specific comment are you not able to view?

    In my last post, I suggested you try the script I posted. Did you try that?

    commented on Oct 31 2011 12:35AM
    Jacob Sebastian
    1 · 100% · 32220
  • Actually none of the previous comments were visible sometime back. Now after adding the last comment all others are also visible. I tried everything you mentioned in new script. There are comments added on that script about what I exactly want and what I am getting now.

    commented on Oct 31 2011 12:59AM
    RobinJain
    327 · 0% · 129
  • I have two docs, first has its content as 'His Red Bull Racing teammate Mark Webber was the closest anyone got to him but that too when the cars were stationary at the starting grid. Jenson Button, who started at fourth, smartly manoeuvred his way across Ferrari Fernando Alonso and Webber and tried hard to play catch-up with the ever-quick Vettel throughout the race. Button took the second place while Alonso was on the podium for the ninth time this year.'

    while the other's content is 'After arriving at the circuit, Tendulkar first met F1 supremo Bernie Ecclestone at the lounge. The duo was later joined by legendary racing driver Jackie Stewart and Bollywood actor Gulshan Grover. The cricketing icon then headed straight to the Mercedes team building where he exchanged pleasantries with Mercedes team principal Ross Brawn before meeting his long-time friend Schumacher.'

    Now here I am trying to run a query as select * from documents where contains(DocBody, 'button')

    it returns one row but in case I run the query

    select * from documents where contains(DocBody, 'icon') it doesn't return any row.

    I tried it with 3 documents as well, but it returns result for only first doc.

    commented on Oct 31 2011 1:17AM
    RobinJain
    327 · 0% · 129
  • I would recommend you post this question to Michael here.

    commented on Oct 31 2011 1:34AM
    Jacob Sebastian
    1 · 100% · 32220
  • Thanks a lot Jacob.

    commented on Oct 31 2011 1:36AM
    RobinJain
    327 · 0% · 129

Your Comment


Sign Up or Login to post a comment.

"SQL Server–Partitioning FILESTREAM data with RANGE LEFT does not work" rated 5 out of 5 by 3 readers
SQL Server–Partitioning FILESTREAM data with RANGE LEFT does not work , 5.0 out of 5 based on 3 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]