Books On Lies - Partitioned Views

From BOL...

CHECK constraints are not needed for the partitioned view to return the correct results. However, if the CHECK constraints have not been defined, the query optimizer must search all the tables instead of only those that cover the search condition on the partitioning column. Without the CHECK constraints, the view operates like any other view with UNION ALL. The query optimizer cannot make any assumptions about the values stored in different tables and it cannot skip searching the tables that participate in the view definition.

Then why am I getting index scans on my partitioning column on tables that fail the search value based on their check constraint?

Not looking for an answer because I know the query optimizer is a fickle b*tch and I did not post any code, but I needed to rant.Now I have no experience here but I will attempt to dredge something from memory...

I believe, based on some discussions on SQL Team I skimmed, that the view will look through ALL tables on its first run but subsequent to that it should only hit the relevant tables.

Have a search of the SQL Team threads if you want to nail this - I am 78.3% sure I have got that about right. :)|||Not looking for an answer because I know the query optimizer is a fickle b*tch...

Have you had these feelings for a long time?

:D

...but I needed to rant.

I know the feeling well.

Take care,

hmscott|||- I am 78.3% sure I have got that about right. :)

What a coincidence, 78.3% of all statistics are made up on the spot.

Regards,

hmscott|||What a coincidence, 78.3% of all statistics are made up on the spot.I thought it was 79.2? :(|||Now I have no experience here but I will attempt to dredge something from memory...

I believe, based on some discussions on SQL Team I skimmed, that the view will look through ALL tables on its first run but subsequent to that it should only hit the relevant tables.

Have a search of the SQL Team threads if you want to nail this - I am 78.3% sure I have got that about right. :)

nope. same execution plan everytime. costly scans on the partitioning column with my check constraint on tables that fail the search condition. I just re-read the rules for partititioned views. everything looks ok and I am getting pissed.|||I thought it was 79.2? :(

As my old artillery instructor used to say, "what's one percent among friends?"
:shocked:

Regards,

hmscott|||As my old artillery instructor used to say, "what's one percent among friends?"

nothing, as long as your shells are 1% more explosive than your friend's shells.|||it appears that your partitioning column has to be the first column in the primary key. not that this written down anywhere obvious.|||that's because it's self-evident.

;)|||well the other part of the primary key is more selective being a surrogate incrementor, so nsince I had to use a composite key in this case I wanted it first in the index. i have had girlfriends I would call surrogate incrementors. Whoops, a TallCowboy moment.

Block to drop any object with ddl triger

Hi all,
I want block attempt to drop any objects(tables, sp, views...) of my data
base.
Have way to do this?
I did see same thing about DDL trigers, any one can sed-me one sample?
Thanks to allHi all,
Thanks,
I find one sample...
CREATE TRIGGER safety
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
PRINT 'You must disable Trigger "safety" to drop or alter tables!'
ROLLBACK ;
Thanks again
"retf" <re.tf@.terra.com.br> escreveu na mensagem
news:%23cQ9yq6eGHA.1272@.TK2MSFTNGP03.phx.gbl...
> Hi all,
> I want block attempt to drop any objects(tables, sp, views...) of my data
> base.
> Have way to do this?
> I did see same thing about DDL trigers, any one can sed-me one sample?
> Thanks to all
>|||This should really be
CREATE TRIGGER safety
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
BEGIN
RAISERROR('You must disable Trigger "safety" to drop or alter
tables!',16,1)
ROLLBACK ;
END
Without an error, a client may think the statement suceeded.
David
"retf" <re.tf@.terra.com.br> wrote in message
news:%23P2uRt6eGHA.3484@.TK2MSFTNGP02.phx.gbl...
> Hi all,
> Thanks,
> I find one sample...
> CREATE TRIGGER safety
> ON DATABASE
> FOR DROP_TABLE, ALTER_TABLE
> AS
> PRINT 'You must disable Trigger "safety" to drop or alter tables!'
> ROLLBACK ;
>
> Thanks again
> "retf" <re.tf@.terra.com.br> escreveu na mensagem
> news:%23cQ9yq6eGHA.1272@.TK2MSFTNGP03.phx.gbl...
>|||David Browne (davidbaxterbrowne no potted meat@.hotmail.com) writes:
> This should really be
>
> CREATE TRIGGER safety
> ON DATABASE
> FOR DROP_TABLE, ALTER_TABLE
> AS
> BEGIN
> RAISERROR('You must disable Trigger "safety" to drop or alter
> tables!',16,1)
> ROLLBACK ;
> END
> Without an error, a client may think the statement suceeded.
Quibble: there will be an error message without the RAISERROR. To
wit, in SQL 2005 a ROLLBACK in a trigger raises an error message.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Blank Database Name

Somehow a database got created that has no name...not even a space. If you
look in the diagrams, tables, views, and etc there is nothing there. How can
I get rid of this bad db? If I try to delete I get the following:
Error 21776: [SQL-DMO] The name '' was not found in the Databases
collection...
Thanks!
"Mat Powell" <MatPowell@.discussions.microsoft.com> wrote in message
news:52E877A4-B05C-4DF5-8A80-CFD7368B147C@.microsoft.com...
> Somehow a database got created that has no name...not even a space. If
you
> look in the diagrams, tables, views, and etc there is nothing there. How
can
> I get rid of this bad db? If I try to delete I get the following:
> Error 21776: [SQL-DMO] The name '' was not found in the Databases
> collection...
>
> Thanks!
Run the following query and see if you can find the database information in
the system tables.
If you can find it there, then you can probably update the sysdatabases
table and give the DB_ID in question a name that you can work with.
SELECT * FROM master.dbo.sysdatabases
Rick Sawtell
MCT, MCSD, MCDBA
|||Rick...thanks for helping out. I was able to find the entry in the table you
specified. I updated the "Name" column, but the db still does not have a
name in the Database view. I would just delete the record, but I figured I
would check with you first to see what the possible consequences might be.
"Rick Sawtell" wrote:

> "Mat Powell" <MatPowell@.discussions.microsoft.com> wrote in message
> news:52E877A4-B05C-4DF5-8A80-CFD7368B147C@.microsoft.com...
> you
> can
> Run the following query and see if you can find the database information in
> the system tables.
> If you can find it there, then you can probably update the sysdatabases
> table and give the DB_ID in question a name that you can work with.
> SELECT * FROM master.dbo.sysdatabases
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
|||Rick...nevermind, your suggestion did work. I just didn't get it enough
time to refresh. I was able to delete the db using the menus.
Thanks again!!
"Rick Sawtell" wrote:

> "Mat Powell" <MatPowell@.discussions.microsoft.com> wrote in message
> news:52E877A4-B05C-4DF5-8A80-CFD7368B147C@.microsoft.com...
> you
> can
> Run the following query and see if you can find the database information in
> the system tables.
> If you can find it there, then you can probably update the sysdatabases
> table and give the DB_ID in question a name that you can work with.
> SELECT * FROM master.dbo.sysdatabases
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
|||then try sp_rename '', 'New-Name', 'Database'
best Regards,
Chandra
http://chanduas.blogspot.com/
"Mat Powell" wrote:
[vbcol=seagreen]
> Rick...thanks for helping out. I was able to find the entry in the table you
> specified. I updated the "Name" column, but the db still does not have a
> name in the Database view. I would just delete the record, but I figured I
> would check with you first to see what the possible consequences might be.
> "Rick Sawtell" wrote:

Blank Database Name

Somehow a database got created that has no name...not even a space. If you
look in the diagrams, tables, views, and etc there is nothing there. How can
I get rid of this bad db? If I try to delete I get the following:
Error 21776: [SQL-DMO] The name '' was not found in the Databases
collection...
Thanks!"Mat Powell" <MatPowell@.discussions.microsoft.com> wrote in message
news:52E877A4-B05C-4DF5-8A80-CFD7368B147C@.microsoft.com...
> Somehow a database got created that has no name...not even a space. If
you
> look in the diagrams, tables, views, and etc there is nothing there. How
can
> I get rid of this bad db? If I try to delete I get the following:
> Error 21776: [SQL-DMO] The name '' was not found in the Databases
> collection...
>
> Thanks!
Run the following query and see if you can find the database information in
the system tables.
If you can find it there, then you can probably update the sysdatabases
table and give the DB_ID in question a name that you can work with.
SELECT * FROM master.dbo.sysdatabases
Rick Sawtell
MCT, MCSD, MCDBA|||Rick...thanks for helping out. I was able to find the entry in the table you
specified. I updated the "Name" column, but the db still does not have a
name in the Database view. I would just delete the record, but I figured I
would check with you first to see what the possible consequences might be.
"Rick Sawtell" wrote:
> "Mat Powell" <MatPowell@.discussions.microsoft.com> wrote in message
> news:52E877A4-B05C-4DF5-8A80-CFD7368B147C@.microsoft.com...
> > Somehow a database got created that has no name...not even a space. If
> you
> > look in the diagrams, tables, views, and etc there is nothing there. How
> can
> > I get rid of this bad db? If I try to delete I get the following:
> >
> > Error 21776: [SQL-DMO] The name '' was not found in the Databases
> > collection...
> >
> >
> > Thanks!
> Run the following query and see if you can find the database information in
> the system tables.
> If you can find it there, then you can probably update the sysdatabases
> table and give the DB_ID in question a name that you can work with.
> SELECT * FROM master.dbo.sysdatabases
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Rick...nevermind, your suggestion did work. I just didn't get it enough
time to refresh. I was able to delete the db using the menus.
Thanks again!!
"Rick Sawtell" wrote:
> "Mat Powell" <MatPowell@.discussions.microsoft.com> wrote in message
> news:52E877A4-B05C-4DF5-8A80-CFD7368B147C@.microsoft.com...
> > Somehow a database got created that has no name...not even a space. If
> you
> > look in the diagrams, tables, views, and etc there is nothing there. How
> can
> > I get rid of this bad db? If I try to delete I get the following:
> >
> > Error 21776: [SQL-DMO] The name '' was not found in the Databases
> > collection...
> >
> >
> > Thanks!
> Run the following query and see if you can find the database information in
> the system tables.
> If you can find it there, then you can probably update the sysdatabases
> table and give the DB_ID in question a name that you can work with.
> SELECT * FROM master.dbo.sysdatabases
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||then try sp_rename '', 'New-Name', 'Database'
--
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"Mat Powell" wrote:
> Rick...thanks for helping out. I was able to find the entry in the table you
> specified. I updated the "Name" column, but the db still does not have a
> name in the Database view. I would just delete the record, but I figured I
> would check with you first to see what the possible consequences might be.
> "Rick Sawtell" wrote:
> >
> > "Mat Powell" <MatPowell@.discussions.microsoft.com> wrote in message
> > news:52E877A4-B05C-4DF5-8A80-CFD7368B147C@.microsoft.com...
> > > Somehow a database got created that has no name...not even a space. If
> > you
> > > look in the diagrams, tables, views, and etc there is nothing there. How
> > can
> > > I get rid of this bad db? If I try to delete I get the following:
> > >
> > > Error 21776: [SQL-DMO] The name '' was not found in the Databases
> > > collection...
> > >
> > >
> > > Thanks!
> >
> > Run the following query and see if you can find the database information in
> > the system tables.
> >
> > If you can find it there, then you can probably update the sysdatabases
> > table and give the DB_ID in question a name that you can work with.
> >
> > SELECT * FROM master.dbo.sysdatabases
> >
> >
> > Rick Sawtell
> > MCT, MCSD, MCDBA
> >
> >
> >
> >

Blank Database Name

Somehow a database got created that has no name...not even a space. If you
look in the diagrams, tables, views, and etc there is nothing there. How ca
n
I get rid of this bad db? If I try to delete I get the following:
Error 21776: [SQL-DMO] The name '' was not found in the Databases
collection...
Thanks!"Mat Powell" <MatPowell@.discussions.microsoft.com> wrote in message
news:52E877A4-B05C-4DF5-8A80-CFD7368B147C@.microsoft.com...
> Somehow a database got created that has no name...not even a space. If
you
> look in the diagrams, tables, views, and etc there is nothing there. How
can
> I get rid of this bad db? If I try to delete I get the following:
> Error 21776: [SQL-DMO] The name '' was not found in the Databases
> collection...
>
> Thanks!
Run the following query and see if you can find the database information in
the system tables.
If you can find it there, then you can probably update the sysdatabases
table and give the DB_ID in question a name that you can work with.
SELECT * FROM master.dbo.sysdatabases
Rick Sawtell
MCT, MCSD, MCDBA|||Rick...thanks for helping out. I was able to find the entry in the table yo
u
specified. I updated the "Name" column, but the db still does not have a
name in the Database view. I would just delete the record, but I figured I
would check with you first to see what the possible consequences might be.
"Rick Sawtell" wrote:

> "Mat Powell" <MatPowell@.discussions.microsoft.com> wrote in message
> news:52E877A4-B05C-4DF5-8A80-CFD7368B147C@.microsoft.com...
> you
> can
> Run the following query and see if you can find the database information i
n
> the system tables.
> If you can find it there, then you can probably update the sysdatabases
> table and give the DB_ID in question a name that you can work with.
> SELECT * FROM master.dbo.sysdatabases
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Rick...nevermind, your suggestion did work. I just didn't get it enough
time to refresh. I was able to delete the db using the menus.
Thanks again!!
"Rick Sawtell" wrote:

> "Mat Powell" <MatPowell@.discussions.microsoft.com> wrote in message
> news:52E877A4-B05C-4DF5-8A80-CFD7368B147C@.microsoft.com...
> you
> can
> Run the following query and see if you can find the database information i
n
> the system tables.
> If you can find it there, then you can probably update the sysdatabases
> table and give the DB_ID in question a name that you can work with.
> SELECT * FROM master.dbo.sysdatabases
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||then try sp_rename '', 'New-Name', 'Database'
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"Mat Powell" wrote:
[vbcol=seagreen]
> Rick...thanks for helping out. I was able to find the entry in the table
you
> specified. I updated the "Name" column, but the db still does not have a
> name in the Database view. I would just delete the record, but I figured
I
> would check with you first to see what the possible consequences might be.
> "Rick Sawtell" wrote:
>