Incrementing a table name

Question

Incrementing a table name

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 1

Reading costs in the execution plan can be both good and bad. For example, if the old code took 5 minutes to run and the new code takes 5 seconds to run, are you really going to care about any cost of 43% in the new code?

I can also show you where costs are absolutely incorrect. I'm on my way to work and I'll try to show you that tonight.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

davidandrews13 SSCertifiable Points: 6825 More actions · Answer 2

[UPDATED: New CREATE TABLE code]

i've been playing around abit more with Partitioned Views but have come across a problem.

here are the 3 tables that i have created as a test:

CREATE TABLE [dbo].[CheckConstraint2008](

[ConfirmDate] [smalldatetime] NOT NULL,

[ID] [int] IDENTITY(1,1) NOT NULL,

CONSTRAINT [PK_CheckConstraint2008] PRIMARY KEY CLUSTERED

(

[ID] ASC,

[ConfirmDate] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[CheckConstraint2008] WITH CHECK ADD CONSTRAINT [CK_CheckConstraint2008_1] CHECK (([ConfirmDate]>='2008-01-01 00:00:00' AND [ConfirmDate]<='2008-12-31 23:59:59'))

GO

ALTER TABLE [dbo].[CheckConstraint2008] CHECK CONSTRAINT [CK_CheckConstraint2008_1]

GO

CREATE TABLE [dbo].[CheckConstraint2009](

[ConfirmDate] [smalldatetime] NOT NULL,

[ID] [int] IDENTITY(1,1) NOT NULL,

CONSTRAINT [PK_CheckConstraint2009] PRIMARY KEY CLUSTERED

(

[ID] ASC,

[ConfirmDate] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[CheckConstraint2009] WITH CHECK ADD CONSTRAINT [CK_CheckConstraint2009_1] CHECK (([ConfirmDate]>='2009-01-01 00:00:00' AND [ConfirmDate]<='2009-12-31 23:59:59'))

GO

ALTER TABLE [dbo].[CheckConstraint2009] CHECK CONSTRAINT [CK_CheckConstraint2009_1]

GO

CREATE TABLE [dbo].[CheckConstraint2010](

[ConfirmDate] [smalldatetime] NOT NULL,

[ID] [int] IDENTITY(1,1) NOT NULL,

CONSTRAINT [PK_CheckConstraint2010] PRIMARY KEY CLUSTERED

(

[ID] ASC,

[ConfirmDate] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[CheckConstraint2010] WITH CHECK ADD CONSTRAINT [CK_CheckConstraint2010_1] CHECK (([ConfirmDate]>='2010-01-01 00:00:00' AND [ConfirmDate]<='2010-12-31 23:59:59'))

GO

ALTER TABLE [dbo].[CheckConstraint2010] CHECK CONSTRAINT [CK_CheckConstraint2010_1]

GO

my View is:

CREATE VIEW [dbo].[vCheckConstraint]

AS

SELECT ConfirmDate,ID

FROM CheckConstraint2010

UNION ALL

SELECT ConfirmDate,ID

FROM CheckConstraint2009

UNION ALL

SELECT ConfirmDate,ID

FROM CheckConstraint2008

unfortunatelyk, when i run an Insert statement, it tells me:

Msg 4436, Level 16, State 12, Line 1

UNION ALL view '[databasename].dbo.vCheckConstraint' is not updatable because a partitioning column was not found.

here's my insert instatement:

insert into vCheckConstraint(ConfirmDate)

values('2009-02-02 10:00:00')

can anyone see what mistake i've made?

i've looked at this link http://social.msdn.microsoft.com/Forums/ar-SA/transactsql/thread/bd3453de-5e4d-48e3-8d4c-f8786d5e8fee but i still can't get it to work.

davidandrews13 SSCertifiable Points: 6825 More actions · Answer 3

resolved the problem. for some reason, when i had:

([ConfirmDate]>='2008-01-01 00:00:00' AND [ConfirmDate]<='2008-12-31 23:59:59')

it was allowing all dates through, including from the year 2009.

if i changed it to

([ConfirmDate]>='2008-01-01 00:00:00' AND [ConfirmDate]<='2008-12-31 23:58:59')

it seemed to work as expected.

i changed it to how Jeff recommended and also removed the Identity column and it worked:

([ConfirmDate]>='2008' AND [ConfirmDate]<'2009')

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 4

I'm pretty confused, right now. Guess I've not had enough coffee, yet. I see no differences between the first two peices of code above.

{Edit} I was right... not enough coffee. I now see the "8".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 5

p.s. There shouldn't have been a problem with the original peice of code you posted unless you had something else in the WHERE clause that was causing a problem. I'll try to take a deeper look tonight after work.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

davidandrews13 SSCertifiable Points: 6825 More actions · Answer 6

well i couldn't work out why i couldn't insert using the Partitioned View.

so i took the code from the CheckConstraint, and ran a straight forward SELECT statement

SELECT * FROM Table2008 WHERE ([ConfirmDate]>='2008-01-01 00:00:00' AND [ConfirmDate]<='2008-12-31 23:59:59')

the only two rows i had in there were for the date '2009-01-01 00:00:00' and both of them were brought back. (the fact i could insert those two dates in teh first place was linked to the same problem.

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 7

I haven't tested it yet but I believe your main problem is that you used SMALLDATETIME in the tables which are accurate only to the nearest minute.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 8

So, did the SMALLDATETIME realization and the fact that the last 30 seconds of the day are rounded up to the next day shed any light on your partitioning problem for you?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

davidandrews13 SSCertifiable Points: 6825 More actions · Answer 9

i haven't had time to test it but it sounds right. as you say, it rounds it to the nearest minute. the data we hold doesn't usually come in with a timestamp of 10 minutes either side of midnight though, which is why i've never noticed this behaviour before.

thanks for your help though