Is it possible to modify column data type of view

The SQL Server Management Studio shows the data type of each column of views. I am wondering how SQL server determines the types since my SQL code of views does not specifiy data types for any columns.

I am much more interested in knowing whether the data types can be modified. Could anyone offer some hint?

Thanks,

hz

SQL Server uss the underlying schema information to do so unless you don′t specify a different data type than the source data type (liek within CONVERT). You can change the resulting data type in the view e.g. via CONNVERT(VARCHAR(10),GETDATE(),112), which was a datetime before and a varchar afterwards.

HTH, Jens Suessmeyer.|||

Jens, thanks a lot! That is exactly what I was looking for.

hz

Is it possible to merge date from 2 or 3 tables into a View?

Suppose I have three tables Type1Events, Type2Events, and Type3Events where
each table has a DateTime column for the date and time the event occurs,
along with some data specific to the event. Can I pull all events from the
three tables into a single View so that the data appears as if it were one
single table, and use a where condition for the date range so that only
events in a certain date range for all three tables appear? So my output
might look like this
EventDate Type
=================
1/1/2001 1
1/2/2001 2
2/1/2003 3
... etc...
Not sure if this makes sense but. I really don't care about the data in the
tables for this example, only the ocurrances in dates ranges for all three.
thanks,
JIM
EventTables
CREATE TABLE dbo.Type1Events (
id int NOT NULL IDENTITY (1, 1),
eventDate datetime NOT NULL,
somedata text NOT NULL
) ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE dbo.Type2Events (
id int NOT NULL IDENTITY (1, 1),
eventDate datetime NOT NULL,
somedata text NOT NULL
) ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE dbo.Type3Events (
id int NOT NULL IDENTITY (1, 1),
eventDate datetime NOT NULL,
somedata text NOT NULL
) ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO
INSERT INTO Type1Events ( eventDate, someData ) values ( '1/1/2001',
'data1' )
INSERT INTO Type1Events ( eventDate, someData ) values ( '1/1/2002',
'data2' )
INSERT INTO Type2Events ( eventDate, someData ) values ( '1/1/2003',
'data3' )
INSERT INTO Type2Events ( eventDate, someData ) values ( '2/1/2002',
'data4' )
INSERT INTO Type3Events ( eventDate, someData ) values ( '3/1/2001',
'data5' )
INSERT INTO Type3Events ( eventDate, someData ) values ( '4/1/2002',
'data6' )
INSERT INTO Type2Events ( eventDate, someData ) values ( '5/1/2006',
'data7' )
INSERT INTO Type1Events ( eventDate, someData ) values ( '6/1/2004',
'data8' )
INSERT INTO Type3Events ( eventDate, someData ) values ( '7/1/2005',
'data9' )
GOcreate view events
as
select eventDate, someData from Type1Events
union all
select eventDate, someData from Type2Events
union all
select eventDate, someData from Type3Events
go|||Sorry, a more complete solution is:
create view events
as
select 1 as eventType, eventDate, someData from Type1Events
union all
select 2, eventDate, someData from Type2Events
union all
select 3, eventDate, someData from Type3Events
go|||Jeff,
Thanks a lot!, I figured it should be easy but havn't used union much so...
thanks,
JIM
"JeffB" <jeff.bolton@.citigatehudson.com> wrote in message
news:1142016783.270339.21280@.u72g2000cwu.googlegroups.com...
> Sorry, a more complete solution is:
> create view events
> as
> select 1 as eventType, eventDate, someData from Type1Events
> union all
> select 2, eventDate, someData from Type2Events
> union all
> select 3, eventDate, someData from Type3Events
> go
>

Is it possible to hide the report parameters after click on the view report button?

i am working in SQL Reporting services 2005. i had a requirement that
after selecting all the report paramters and click on the view report
button, i need to turn off or hide the report parameters.
please let me know is it possible to implement to do this.
any suggesstion
Thanks
VinodThere is a button the right side of the page of the reports browser
that look like to arrows on top of each other, click that and it will
hide the parameters. There are actually two sets, one for the
parameters and one for the web page header. Hope that helps.
Vinod wrote:
> i am working in SQL Reporting services 2005. i had a requirement that
> after selecting all the report paramters and click on the view report
> button, i need to turn off or hide the report parameters.
> please let me know is it possible to implement to do this.
> any suggesstion
> Thanks
> Vinod|||Thanks for the reply Kerrie.
i have seen the hide button icon in the report layout.but my
requirement is that i need to hide the parameters when i click on the
view report button in the report layout after selecting the all the
report parameters.
if user wants to modify the criteria again, he needs to click on the
hide icon in the report layout. then select the criteria and view the
report.
please let me know if you are not clear
Thanks
vinod
Kerrie wrote:
> There is a button the right side of the page of the reports browser
> that look like to arrows on top of each other, click that and it will
> hide the parameters. There are actually two sets, one for the
> parameters and one for the web page header. Hope that helps.
>
> Vinod wrote:
> > i am working in SQL Reporting services 2005. i had a requirement that
> > after selecting all the report paramters and click on the view report
> > button, i need to turn off or hide the report parameters.
> >
> > please let me know is it possible to implement to do this.
> >
> > any suggesstion
> >
> > Thanks
> > Vinod|||I'm pretty sure there's no function like that in RS out of the box. But I'm
thinking that if you can modify the View Report button's code, you could add
a java script that redirects to a url with parameters toolbar=false and the
chosen parameters. You'd have to tweek the RS GUI to do it, and I don't know
how easy that is.
Alternatively, create a aspx. page that creates the parameters to be chosen,
and send the users to a report using the same paramtererized url as
suggested above.
Kaisa M. Lindahl Lervik
"Vinod" <vinodsh_82@.hotmail.com> wrote in message
news:1161352815.498895.319680@.h48g2000cwc.googlegroups.com...
> Thanks for the reply Kerrie.
> i have seen the hide button icon in the report layout.but my
> requirement is that i need to hide the parameters when i click on the
> view report button in the report layout after selecting the all the
> report parameters.
> if user wants to modify the criteria again, he needs to click on the
> hide icon in the report layout. then select the criteria and view the
> report.
> please let me know if you are not clear
> Thanks
> vinod
>
> Kerrie wrote:
>> There is a button the right side of the page of the reports browser
>> that look like to arrows on top of each other, click that and it will
>> hide the parameters. There are actually two sets, one for the
>> parameters and one for the web page header. Hope that helps.
>>
>> Vinod wrote:
>> > i am working in SQL Reporting services 2005. i had a requirement that
>> > after selecting all the report paramters and click on the view report
>> > button, i need to turn off or hide the report parameters.
>> >
>> > please let me know is it possible to implement to do this.
>> >
>> > any suggesstion
>> >
>> > Thanks
>> > Vinod
>|||Thanks for the reply Kaisa.
i am not creating a aspx page for criteria. i have added 15 report
parameters in the report layout iteself. everything is fine upto this
point.
but my client requriment is that we dont want to show the report
parameters in the report layout after clicking on the view report
button. it should be hided.
let me know is it possible.
Thanks
Vinod
Kaisa M. Lindahl Lervik wrote:
> I'm pretty sure there's no function like that in RS out of the box. But I'm
> thinking that if you can modify the View Report button's code, you could add
> a java script that redirects to a url with parameters toolbar=false and the
> chosen parameters. You'd have to tweek the RS GUI to do it, and I don't know
> how easy that is.
> Alternatively, create a aspx. page that creates the parameters to be chosen,
> and send the users to a report using the same paramtererized url as
> suggested above.
> Kaisa M. Lindahl Lervik
> "Vinod" <vinodsh_82@.hotmail.com> wrote in message
> news:1161352815.498895.319680@.h48g2000cwc.googlegroups.com...
> > Thanks for the reply Kerrie.
> >
> > i have seen the hide button icon in the report layout.but my
> > requirement is that i need to hide the parameters when i click on the
> > view report button in the report layout after selecting the all the
> > report parameters.
> >
> > if user wants to modify the criteria again, he needs to click on the
> > hide icon in the report layout. then select the criteria and view the
> > report.
> >
> > please let me know if you are not clear
> >
> > Thanks
> > vinod
> >
> >
> > Kerrie wrote:
> >> There is a button the right side of the page of the reports browser
> >> that look like to arrows on top of each other, click that and it will
> >> hide the parameters. There are actually two sets, one for the
> >> parameters and one for the web page header. Hope that helps.
> >>
> >>
> >> Vinod wrote:
> >> > i am working in SQL Reporting services 2005. i had a requirement that
> >> > after selecting all the report paramters and click on the view report
> >> > button, i need to turn off or hide the report parameters.
> >> >
> >> > please let me know is it possible to implement to do this.
> >> >
> >> > any suggesstion
> >> >
> >> > Thanks
> >> > Vinod
> >

Is it possible to export a View to Excel in MS SQL 2000 ?

Hi all,

In MS SQL Management Console I can right-click on any Table and I have
the option All Tasks > Export Data where I can export the table to
Excel. In a View however this isn't there. I have many views I want
to simply export to Excel, but the only way I've found to do it is
creating an ODBC connection to the MS SQL database from MS Access,
linking the Views to Access Tables, and exporting from Access. surely
there's someway to export a View to Excel within MS SQL easily like
exporting a table...

Thanks --

AlexIf this is a one-off task, the Import/Export Wizard can export to a
..xls. If you need to run it regularly, then you can save the DTS
package created by the wizard, and modify it as required.

Simon

Is it possible to do this?

In an OLAP data source view I added ten additional fields to it based on certain conditions and it is used for the aggregation for a report. It is only stored as a number '0' or '1' so that I can sum it up for the report as a measure. It is working fine in the report. However I want to reperesent this ten additional fields on the X axis on a graph and I am not sure how to achieve this. It sounds like the reverse of the "pivot table", creating multiple rows (10 in my case) from a single rows of record. I have created a dimension with this ten fields. My question is how to merge this with another two new field, one to capture the column heading and the other to capture the value of it in an OLAP dataset. Thanks.Actually the MDX function I am looking for is "UNPIVOT" like in T-Sql.

Is it possible to deploy reports on server and view it from client machines

Hi,

I'm a newbie to reporting service.

I deployed a report on the server. When I viewed it on the server by entering the URL of http://localhost/reports$SQLExpress, everything's fine. When I tried to view it form a client machine on http://MyServerName/reports$SQLExpress, it failed. I did assign a role to the client user. I'm just wondering whether it is possible to do this.

I'm using SQL Express, the server runs win2003 server and the client machine runs winXP.

What was the error message? What authentication method are you using for reporting services?

sluggy

|||

The error message was"The page cannot be displayed. The page you are looking for is currently unavaiable. blah...blah...".

I use Windows authentication for reporting service. Thanks!

|||

You are probably hitting the double-hop problem with IIS

http://blogs.msdn.com/jgalla/archive/2006/03/16/553314.aspx

The fix is to store the credentials for accessing the datasource in the report server.

Is it possible to create a view within a stored procedure

Is it possible to dynamically create an sql create view statement then execute that sql statement? Or because create views must be the first statement in a query batch, it's not possible?

Hi,

A stored procedure cannot be created with the create View command in it .

But we could create a t-sql string with the create View statement and execute the string with sp_executesql

Though, it returns a Warning message("Cannot add rows to sysdepends for the current stored procedure") the View would be created. The warning message occurs because during the First execution of the SP the View will not be created.

SanDoty

|||

As SanDoty indicated, you cannot create a VIEW inside a STORED PROCEDURE.

However, there are 'tricks', such as using dynamic SQL. Because of various issues, such as dependencies, those 'tricks' should not be considered except under certain circumstances.

However inside a Stored Procedure, you can create a table variable, populate it and use it just like you would use a VIEW.

|||

create proc my_proc as

BEGIN

declare @.sql varchar(max)

select @.sql = 'create view my_sysobjects as select top 5 * from sysobjects'

exec (@.sql)

select * from my_sysobjects

END

go

exec my_proc

|||hi,i tried it and it works..but when i try with my coding to execute, it gives an 'Invalid object name 'vw_test' error.in my stored proc thers some query which does some calculations based on date(im passing in date as parameter in the stored proc - i suspect this might be the cause). how do we overcome this?kindly advice.thanx.

Is it possible to create a view within a stored procedure

Is it possible to dynamically create an sql create view statement then execute that sql statement? Or because create views must be the first statement in a query batch, it's not possible?

Hi,

A stored procedure cannot be created with the create View command in it .

But we could create a t-sql string with the create View statement and execute the string with sp_executesql

Though, it returns a Warning message("Cannot add rows to sysdepends for the current stored procedure") the View would be created. The warning message occurs because during the First execution of the SP the View will not be created.

SanDoty

|||

As SanDoty indicated, you cannot create a VIEW inside a STORED PROCEDURE.

However, there are 'tricks', such as using dynamic SQL. Because of various issues, such as dependencies, those 'tricks' should not be considered except under certain circumstances.

However inside a Stored Procedure, you can create a table variable, populate it and use it just like you would use a VIEW.

|||

create proc my_proc as

BEGIN

declare @.sql varchar(max)

select @.sql = 'create view my_sysobjects as select top 5 * from sysobjects'

exec (@.sql)

select * from my_sysobjects

END

go

exec my_proc

|||hi,i tried it and it works..but when i try with my coding to execute, it gives an 'Invalid object name 'vw_test' error.in my stored proc thers some query which does some calculations based on date(im passing in date as parameter in the stored proc - i suspect this might be the cause). how do we overcome this?kindly advice.thanx.

is it a bug?

it is the second time i am writing this problem.
I have a view named x_vw and while ? exec it as
select * from x_vw where field1='a1'
it returns the results which are field1='a1' but when I exec the query as
select * from x_vw
it returns all results but eccept the results which are field1='a1'
select * from X_VW where SiparisNo='a1'
returns data I want but the same data doesnt exists in the resultset of
select * from X_VW
its funny that I find a stupid solution for a stupid problem:))
select * from X_VW where SiparisNo like '%%'
I'm starving for explanation
is it a bug?
if so how can I report a bug to Microsoft?
CREATE view X_VW
as
SELECT sd.Sirketkod AS 'sirket',c.HesapKodu AS 'Sat?c?', c.Unvan,
sd.EvrakNo AS 'SiparisNo',
sd.MalKodu,
s.MalAdi,
SUM(sd.Miktar) AS 'Siparis',
dbo.DMGetIthSipIptalMikgamet(sd.SirketKod,sd.EvrakNo,sd.MalKodu) as
'iptalMiktar',
dbo.DMGetIthSipFatMikgamet(sd.SirketKod,sd.EvrakNo,sd.MalKodu) as
'Kars?lanan',
SUM(sd.Miktar)-dbo.DMGetIthSipFatMikgamet(sd.SirketKod,sd.EvrakNo,sd.MalKodu
) as 'Kalan',
dbo.DMGetIthStokGirMikgamet(sd.SirketKod,sd.EvrakNo,sd.MalKodu)as 'StokGiris
',
sh.EkSipNo,sh.aciklama,sh.SiparisTip,
s.kod1,
s.kod2,
s.kod3,
s.kod4,
s.kod5,
gamet2003.dbo.DMGetStokMik(sd.Sirketkod,sd.Malkodu)as 'stokmiktar',
Isnull(f.fiyat,0) as 'Ithalat_fiyati', Isnull(f.dovizkod,'yok') as 'Dovizkod
',
sd.Fiyat as 'SipFiyat', Isnull(sh.dovizkod,'yok') as 'SipDovizkod'
FROM gamet2003..SIP_D sd
inner join gamet2003..SIP_H sh on sh.SirketKod = sd.SirketKod AND sh.EvrakNo
= sd.EvrakNo
inner join gamet2003..CHK c on sd.SirketKod = c.SirketKod AND sd.Chk =
c.HesapKodu
inner join gamet2003..STK s on s.SirketKod = sd.SirketKod AND s.MalKodu =
sd.MalKodu
left join gamet2003..FIYATLIST f on f.sirketkod=sd.sirketkod and
s.malkodu=f.malkodu and f.Fiyatkod='ITHALAT'
WHERE sd.SirketKod='gamet' and sh.EvrakNo like 'IAS%'
and exists(select 1 from
(
select x.sirketkod,x.evrakno
from
(
select th.sirketkod,th.evrakno from gamet2003..ITH_SIP_D th,
gamet2003..SIP_D sp
where sp.sirketkod=th.sirketkod and sp.evrakno=th.evrakno and
sp.oldsirano=th.sirano
and (sp.beklet is null or sp.beklet=0)
group by th.sirketkod,th.evrakno
having abs(sum(sp.kalanmiktar-th.karsilananmiktar))>0
union all
select td.sirketkod,td.oldevrakno as evrakno from gamet2003..ITH_D td
left join gamet2003..STI_H sh on sh.sirketkod=td.sirketkod and
sh.Irsaliyeno=td.Evrakno and (sh.beklet is null or sh.beklet=0)
left join gamet2003..STI_D sd on sd.sirketkod=td.sirketkod and
sh.evrakno=sd.evrakno and (sd.beklet is null or sd.beklet=0)
where (td.beklet is null or td.beklet=0)
group by td.sirketkod,td.oldevrakno
having Isnull(sum(td.miktar),0)>Isnull(sum(sd.miktar),0)
)X
group by x.sirketkod,x.evrakno
)y
where y.sirketkod=sd.sirketkod and y.evrakno=sd.evrakno)and (sd.beklet is
null or sd.beklet=0)
group by sd.SirketKod,c.HesapKodu, c.Unvan, sd.EvrakNo,
sd.MalKodu,s.MalAdi,sh.EkSipNo,sh.aciklama,sh.SiparisTip,s.kod1,s.kod2,s.kod
3,s.kod4,s.kod5,f.fiyat,f.dovizkod,sd.Fiyat,sh.dovizkodHi
Call Microsoft PSS.
http://support.microsoft.com/common/international.aspx
You will be expected to be able to give them sufficient data to reproduce
the problem.
If it is not a bug, you will be charged for the support.
Regards
Mike
"POKEMON" wrote:

> it is the second time i am writing this problem.
> I have a view named x_vw and while ? exec it as
> select * from x_vw where field1='a1'
> it returns the results which are field1='a1' but when I exec the query as
> select * from x_vw
> it returns all results but eccept the results which are field1='a1'
> select * from X_VW where SiparisNo='a1'
> returns data I want but the same data doesnt exists in the resultset of
> select * from X_VW
> its funny that I find a stupid solution for a stupid problem:))
> select * from X_VW where SiparisNo like '%%'
> I'm starving for explanation
> is it a bug?
> if so how can I report a bug to Microsoft?
>
> CREATE view X_VW
> as
> SELECT sd.Sirketkod AS 'sirket',c.HesapKodu AS 'Sat?c?', c.Unvan,
> sd.EvrakNo AS 'SiparisNo',
> sd.MalKodu,
> s.MalAdi,
> SUM(sd.Miktar) AS 'Siparis',
> dbo.DMGetIthSipIptalMikgamet(sd.SirketKod,sd.EvrakNo,sd.MalKodu) as
> 'iptalMiktar',
> dbo.DMGetIthSipFatMikgamet(sd.SirketKod,sd.EvrakNo,sd.MalKodu) as
> 'Kars?lanan',
> SUM(sd.Miktar)-dbo.DMGetIthSipFatMikgamet(sd.SirketKod,sd.EvrakNo,sd.MalKo
du) as 'Kalan',
> dbo.DMGetIthStokGirMikgamet(sd.SirketKod,sd.EvrakNo,sd.MalKodu)as 'StokGir
is',
> sh.EkSipNo,sh.aciklama,sh.SiparisTip,
> s.kod1,
> s.kod2,
> s.kod3,
> s.kod4,
> s.kod5,
> gamet2003.dbo.DMGetStokMik(sd.Sirketkod,sd.Malkodu)as 'stokmiktar',
> Isnull(f.fiyat,0) as 'Ithalat_fiyati', Isnull(f.dovizkod,'yok') as 'Dovizk
od',
> sd.Fiyat as 'SipFiyat', Isnull(sh.dovizkod,'yok') as 'SipDovizkod'
> FROM gamet2003..SIP_D sd
> inner join gamet2003..SIP_H sh on sh.SirketKod = sd.SirketKod AND sh.Evrak
No
> = sd.EvrakNo
> inner join gamet2003..CHK c on sd.SirketKod = c.SirketKod AND sd.Chk =
> c.HesapKodu
> inner join gamet2003..STK s on s.SirketKod = sd.SirketKod AND s.MalKodu =
> sd.MalKodu
> left join gamet2003..FIYATLIST f on f.sirketkod=sd.sirketkod and
> s.malkodu=f.malkodu and f.Fiyatkod='ITHALAT'
> WHERE sd.SirketKod='gamet' and sh.EvrakNo like 'IAS%'
> and exists(select 1 from
> (
> select x.sirketkod,x.evrakno
> from
> (
> select th.sirketkod,th.evrakno from gamet2003..ITH_SIP_D th,
> gamet2003..SIP_D sp
> where sp.sirketkod=th.sirketkod and sp.evrakno=th.evrakno and
> sp.oldsirano=th.sirano
> and (sp.beklet is null or sp.beklet=0)
> group by th.sirketkod,th.evrakno
> having abs(sum(sp.kalanmiktar-th.karsilananmiktar))>0
> union all
> select td.sirketkod,td.oldevrakno as evrakno from gamet2003..ITH_D td
> left join gamet2003..STI_H sh on sh.sirketkod=td.sirketkod and
> sh.Irsaliyeno=td.Evrakno and (sh.beklet is null or sh.beklet=0)
> left join gamet2003..STI_D sd on sd.sirketkod=td.sirketkod and
> sh.evrakno=sd.evrakno and (sd.beklet is null or sd.beklet=0)
> where (td.beklet is null or td.beklet=0)
> group by td.sirketkod,td.oldevrakno
> having Isnull(sum(td.miktar),0)>Isnull(sum(sd.miktar),0)
> )X
> group by x.sirketkod,x.evrakno
> )y
> where y.sirketkod=sd.sirketkod and y.evrakno=sd.evrakno)and (sd.beklet is
> null or sd.beklet=0)
> group by sd.SirketKod,c.HesapKodu, c.Unvan, sd.EvrakNo,
> sd.MalKodu,s.MalAdi,sh.EkSipNo,sh.aciklama,sh.SiparisTip,s.kod1,s.kod2,s.k
od3,s.kod4,s.kod5,f.fiyat,f.dovizkod,sd.Fiyat,sh.dovizkod
>|||Hi POKEMON,
Your current view is very complex, too complex to analyse over a
newsgroup (and missing DDL and sample data). But still: It sounds like a
bug. The big question is: is this a known / documented bug or a new bug.
There are some known issues relating to parallellism. I would start by
adding the query hint OPTION (MAXDOP 1) when querying the view. So for
example:
select * from X_VW option (maxdop 1)
And of course, make sure you are running the latest SQL-Server service
pack (3a).
If money is not an issue, then you can contact Microsoft Support (as
posted by Mike). Otherwise, it makes sense to isolate the problem. You
can do this by removing all columns, UDFs, joins, etc. that do not
influence the problem.
For example: does the problem still remain if all UDFs are removed? Does
the problem still remain if the entire EXISTS subquery is removed? Does
the problem still remain if all outer joins are removed? Does the
problem still remain if you rewrite "gamet2003..ITH_SIP_D th,
gamet2003..SIP_D sp" as an INNER JOIN? Does the problem still remain if
you remove all OR operators by rewriting all "<something> is null or
<something> = 0" to "<something> is null"?
In the end, Microsoft will need a script to reproduce the problem.
HTH,
Gert-Jan

Is it a "Partitioned View"?

Hello. Say you have a view and you want to know if it's
recognized by SQL Server as a local "Partitioned View".
Is there any way to see that, short of running queries to
see that it's scanning ALL table's indexes in the view,
not just the one with the specific WHERE clause criteria
you requested?
How do you know a view is treated as partitioned by SQL
Server (7 or 2000)? THanks, BruceBruce
Script out the view, it should be fairly obvious looking
at it if it is a partitioned view. I have not got 2000 bol
where I am at the moment, but in 7 you will see multiple
tables joined using union all, I would think 2000 would be
the same.
I hope that is what you meant.
Regards
John|||John, it's not that straight-forward. I was hoping
there's some SQL function (like an IsPartitionedView) that
just says that SQL Server THINKS this is a partitioned
view. I have a view and it's not being treated by SQL as
partitioned, in as far as using the separate table's
indexes, it's table scanning EVERY table's index. The
Tales have the correct CHECK constraint, and all the rules
APPEAR to be followed ok, so I was hoping there was a way
to see why SQL Server is just treating this view as a
regular UNION'd set of tables, not as a "partitioned"
view.
Scripting out the view doesn't show you the table
attributes, like the NULL PADDING setting, or the
constraints, etc...
There's probably no way to see this I'm guessing, but
thought I'd ask... THanks, Bruce
>--Original Message--
>Bruce
>Script out the view, it should be fairly obvious looking
>at it if it is a partitioned view. I have not got 2000
bol
>where I am at the moment, but in 7 you will see multiple
>tables joined using union all, I would think 2000 would
be
>the same.
>I hope that is what you meant.
>Regards
>John
>.
>|||There is no intrinsic property, it's purely a matter of query optimization.
You have to view the query plan and/or IO statistics to determine if SQL is
appropriately pruning table partitions from the query plan.
SET STATISTICS IO ON
Look for scan counts of zero on the non-qualifying tables.
Also, I've found that it is necessary to add an index on the partitioning
column(s), as non-intuititve as that seems, it helps the optimizer in
certain cases eliminate partitions
.
--
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:04bb01c376c9$573808e0$a001280a@.phx.gbl...
> John, it's not that straight-forward. I was hoping
> there's some SQL function (like an IsPartitionedView) that
> just says that SQL Server THINKS this is a partitioned
> view. I have a view and it's not being treated by SQL as
> partitioned, in as far as using the separate table's
> indexes, it's table scanning EVERY table's index. The
> Tales have the correct CHECK constraint, and all the rules
> APPEAR to be followed ok, so I was hoping there was a way
> to see why SQL Server is just treating this view as a
> regular UNION'd set of tables, not as a "partitioned"
> view.
> Scripting out the view doesn't show you the table
> attributes, like the NULL PADDING setting, or the
> constraints, etc...
> There's probably no way to see this I'm guessing, but
> thought I'd ask... THanks, Bruce
>
>
> >--Original Message--
> >Bruce
> >
> >Script out the view, it should be fairly obvious looking
> >at it if it is a partitioned view. I have not got 2000
> bol
> >where I am at the moment, but in 7 you will see multiple
> >tables joined using union all, I would think 2000 would
> be
> >the same.
> >
> >I hope that is what you meant.
> >
> >Regards
> >
> >John
> >.
> >|||Kevin, yes, adding the new index on the partition column
did the trick, thanks! It seems strange I need to add an
index on a column where every value is the exact same for
that table. The partition column was the middle column of
a 3 col PK. I had also tried changing the order of the
PK, so the parition column was col #1. But I didn't see
that helped, but adding a new index ONLY on the partition
column DID work, interesting! THanks a lot... Bruce
>--Original Message--
>There is no intrinsic property, it's purely a matter of
query optimization.
>You have to view the query plan and/or IO statistics to
determine if SQL is
>appropriately pruning table partitions from the query
plan.
>SET STATISTICS IO ON
>Look for scan counts of zero on the non-qualifying tables.
>Also, I've found that it is necessary to add an index on
the partitioning
>column(s), as non-intuititve as that seems, it helps the
optimizer in
>certain cases eliminate partitions
>..
>--
>Kevin Connell, MCDBA
>----
>The views expressed here are my own
>and not of my employer.
>----
>"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
>news:04bb01c376c9$573808e0$a001280a@.phx.gbl...
>> John, it's not that straight-forward. I was hoping
>> there's some SQL function (like an IsPartitionedView)
that
>> just says that SQL Server THINKS this is a partitioned
>> view. I have a view and it's not being treated by SQL
as
>> partitioned, in as far as using the separate table's
>> indexes, it's table scanning EVERY table's index. The
>> Tales have the correct CHECK constraint, and all the
rules
>> APPEAR to be followed ok, so I was hoping there was a
way
>> to see why SQL Server is just treating this view as a
>> regular UNION'd set of tables, not as a "partitioned"
>> view.
>> Scripting out the view doesn't show you the table
>> attributes, like the NULL PADDING setting, or the
>> constraints, etc...
>> There's probably no way to see this I'm guessing, but
>> thought I'd ask... THanks, Bruce
>>
>>
>> >--Original Message--
>> >Bruce
>> >
>> >Script out the view, it should be fairly obvious
looking
>> >at it if it is a partitioned view. I have not got 2000
>> bol
>> >where I am at the moment, but in 7 you will see
multiple
>> >tables joined using union all, I would think 2000 would
>> be
>> >the same.
>> >
>> >I hope that is what you meant.
>> >
>> >Regards
>> >
>> >John
>> >.
>> >
>
>.
>