Is it possible to create a partitioned view on a table that has a text field in it (obviously not as a partitioning field)? I tried but I get the following error: "The text, ntext, or image data type cannot be selected as DISTINCT."
[quoted text, click to view] >> I didn't see anything in the BOL about a restriction on tables with text
>> fields.
The error message is generic; the documentation ( though at an unusual location ) states: If you have a view that includes a text field, and you reference the text field in a SELECT clause, then you are not allowed to use SELECT DISTINCT. ( http://msdn.microsoft.com/library/en-us/repospr/rpoptimizing_219v.asp ) -- Anith
I am not using DISTINCT. I was assuming that it was just a requirement of creating a partitioned view and that's why I am getting the error. Here is the view definition: create view dbo.vSearch_Record AS SELECT Search_ID, Time_Stamp, Search_On, Shopper_ID, Search_Type_ID, Min_Price, Max_Price, SKU_String, Keyword_Input_String, Search_Schematic, Term_List_Ordered, Elapsed_Time, Hit_Count, Shopper_Client_IP, Recent, Full_Search_Query, Generated_Clause FROM dbo.Search_Record_current UNION SELECT Search_ID, Time_Stamp, Search_On, Shopper_ID, Search_Type_ID, Min_Price, Max_Price, SKU_String, Keyword_Input_String, Search_Schematic, Term_List_Ordered, Elapsed_Time, Hit_Count, Shopper_Client_IP, Recent, Full_Search_Query, Generated_Clause FROM dbo.Search_Record_history The Search_ID field in the tables are PKs and they are the partitioning field as well. --Buddy [quoted text, click to view] Adam Machanic wrote:
> The issue is not creating a partitioned view, it's using DISTINCT on a text
> column (just as the error says).
>
> Why do you need to select DISTINCT?
>
I am not using DISTINCT. I was assuming that it was just a requirement of creating a partitioned view and that's why I am getting the error. Here is the view definition: create view dbo.vSearch_Record AS SELECT Search_ID, Time_Stamp, Search_On, Shopper_ID, Search_Type_ID, Min_Price, Max_Price, SKU_String, Keyword_Input_String, Search_Schematic, Term_List_Ordered, Elapsed_Time, Hit_Count, Shopper_Client_IP, Recent, Full_Search_Query, Generated_Clause FROM dbo.Search_Record_current UNION SELECT Search_ID, Time_Stamp, Search_On, Shopper_ID, Search_Type_ID, Min_Price, Max_Price, SKU_String, Keyword_Input_String, Search_Schematic, Term_List_Ordered, Elapsed_Time, Hit_Count, Shopper_Client_IP, Recent, Full_Search_Query, Generated_Clause FROM dbo.Search_Record_history The Search_ID field in the tables are PKs and they are the partitioning field as well. --Buddy [quoted text, click to view] Anith Sen wrote: >>>I didn't see anything in the BOL about a restriction on tables with text >>>fields. > > > The error message is generic; the documentation ( though at an unusual > location ) states: If you have a view that includes a text field, and you > reference the text field in a SELECT clause, then you are not allowed to use > SELECT DISTINCT. > ( http://msdn.microsoft.com/library/en-us/repospr/rpoptimizing_219v.asp )
DUH, of course you're supposed to use UNION ALL when creating a partitioned view. Thanks for the catch. [quoted text, click to view] Adam Machanic wrote:
> Try using UNION ALL instead of UNION. I believe that partitioned views
> require that anyway. UNION does a DISTINCT behind the scenes.
The issue is not creating a partitioned view, it's using DISTINCT on a text column (just as the error says). Why do you need to select DISTINCT? -- Adam Machanic SQL Server MVP http://www.sqljunkies.com/weblog/amachanic -- [quoted text, click to view] "Buddy Ackerman" <buddy_nospam@buddyackerman.com> wrote in message
news:u7WKwAX6EHA.1188@tk2msftngp13.phx.gbl...
> Is it possible to create a partitioned view on a table that has a text
field in it (obviously not as a partitioning
> field)? I tried but I get the following error:
> "The text, ntext, or image data type cannot be selected as DISTINCT."
>
> I didn't see anything in the BOL about a restriction on tables with text
fields.
Actually, view or no view, SELECT DISTINCT is not allowed on TEXT columns: create table #a(blah text) select distinct blah from #a -- Server: Msg 8163, Level 16, State 3, Line 3 -- The text, ntext, or image data type cannot be selected as DISTINCT. -- Adam Machanic SQL Server MVP http://www.sqljunkies.com/weblog/amachanic -- [quoted text, click to view] "Anith Sen" <anith@bizdatasolutions.com> wrote in message news:%235pIuRX6EHA.2196@TK2MSFTNGP11.phx.gbl... > >> I didn't see anything in the BOL about a restriction on tables with text > >> fields. > > The error message is generic; the documentation ( though at an unusual > location ) states: If you have a view that includes a text field, and you > reference the text field in a SELECT clause, then you are not allowed to use > SELECT DISTINCT. > ( http://msdn.microsoft.com/library/en-us/repospr/rpoptimizing_219v.asp ) > > -- > Anith > >
Try using UNION ALL instead of UNION. I believe that partitioned views require that anyway. UNION does a DISTINCT behind the scenes. -- Adam Machanic SQL Server MVP http://www.sqljunkies.com/weblog/amachanic -- [quoted text, click to view] "Buddy Ackerman" <buddy_nospam@buddyackerman.com> wrote in message news:egWXuVX6EHA.540@TK2MSFTNGP12.phx.gbl... > I am not using DISTINCT. I was assuming that it was just a requirement of creating a partitioned view and that's why I > am getting the error. Here is the view definition: > > create view dbo.vSearch_Record > AS > SELECT Search_ID, Time_Stamp, Search_On, Shopper_ID, Search_Type_ID, Min_Price, Max_Price, SKU_String, > Keyword_Input_String, Search_Schematic, Term_List_Ordered, Elapsed_Time, Hit_Count, Shopper_Client_IP, Recent, > Full_Search_Query, Generated_Clause > FROM dbo.Search_Record_current > > UNION > > SELECT Search_ID, Time_Stamp, Search_On, Shopper_ID, Search_Type_ID, Min_Price, Max_Price, SKU_String, > Keyword_Input_String, Search_Schematic, Term_List_Ordered, Elapsed_Time, Hit_Count, Shopper_Client_IP, Recent, > Full_Search_Query, Generated_Clause > FROM dbo.Search_Record_history > > > The Search_ID field in the tables are PKs and they are the partitioning field as well. > > > > --Buddy > > Anith Sen wrote: > >>>I didn't see anything in the BOL about a restriction on tables with text > >>>fields. > > > > > > The error message is generic; the documentation ( though at an unusual > > location ) states: If you have a view that includes a text field, and you > > reference the text field in a SELECT clause, then you are not allowed to use > > SELECT DISTINCT. > > ( http://msdn.microsoft.com/library/en-us/repospr/rpoptimizing_219v.asp ) > >
[quoted text, click to view] "Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in message
news:%23FSu0YX6EHA.4028@TK2MSFTNGP15.phx.gbl...
> Try using UNION ALL instead of UNION. I believe that partitioned views
> require that anyway. UNION does a DISTINCT behind the scenes.
Here's verification that the same error message is produced: create table #a(blah text) select blah from #a union select blah from #a .... Changing that to UNION ALL fixes the issue. -- Adam Machanic SQL Server MVP http://www.sqljunkies.com/weblog/amachanic --
True, I was simply quoting BOL since the OP stated he couldn't find it.
--
Anith
Don't see what you're looking for? Try a search.
|
