indentical coloumn in view - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

indentical coloumn in view

Author

Topic

rds207
Posting Yak Master

198 Posts

Posted - 2009-11-17 : 12:32:01

can we create an identical column in view?

if yes what is the syntax?

Please help!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-17 : 12:35:56

What do you mean?
Select the same column of a table twice?

No, you're never too old to Yak'n'Roll if you're too young to die.

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-17 : 12:38:01

CREATE VIEW myView99
AS
SELECT Col1, Col1 AS myCol1
FROM myTable99
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-17 : 12:40:34

Yeah that's right - don't wait for an answer...
We don't want members that will/can ask questions more clear!

No, you're never too old to Yak'n'Roll if you're too young to die.

rds207
Posting Yak Master

198 Posts

Posted - 2009-11-17 : 12:41:46

Yeah i have already created the view, but to improve the performance of the view i have to create an index, i thought i can create an identical columsn which is increments by 1...

i know how it can be create din a table,but not sure about the view...is there a way we can create an indentical columns in view which increaments by 1?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-11-17 : 13:04:34

What version of SQL Server are you using?

rds207
Posting Yak Master

198 Posts

Posted - 2009-11-17 : 13:06:50

sql server 2008

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-11-17 : 13:23:26

Well if you want to just add a sequence number you can use a ranking function like ROW_NUMBER().

If you want to actually index the view you should refer to BOL as there are several requirements that must be met before you can index a view.

rds207
Posting Yak Master

198 Posts

Posted - 2009-11-17 : 13:26:46

Can you please let me know the syntax for row_number()?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-11-17 : 13:34:14

Syntax

ROW_NUMBER ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> )

rds207
Posting Yak Master

198 Posts

Posted - 2009-11-17 : 14:14:49

I GET THE ERROR BELOW

CANNOT CREATE INDEX ON VIEW VIEWNAME BECAUSE IT CONTAINS A RANKING OR AN AGGREAGATE FUNCTION,REMOVE THE FUNCTION FROM THE VIEW , ALTERNATIVELY DO NOT INDEX THE VIEW (MS SQL SERVER ERROR:10143)

pLEASE HELP

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-17 : 14:16:13

quote:
Originally posted by webfred

Yeah that's right - don't wait for an answer...
We don't want members that will/can ask questions more clear!

No, you're never too old to Yak'n'Roll if you're too young to die.

Have you no respect for the Kaiser?

And if we waited for clear an concise questions....well....I thought this was part guessing game...

And besides...didn't I answer the question correctly?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

rds207
Posting Yak Master

198 Posts

Posted - 2009-11-17 : 14:30:45

am sorry if am missing something or someone here :-(
not sure who is kaiser ...?

and was anything wrong with my qestion ?
i was trying with the options i get as response but it was'nt working ,and i am not guessing or playing here ... as i am serious about my job and i need some help from you guys because am a beginner with sql server...

and response from X002548 was to create a view , but i already have my view as i have said ealier and just trying to create an index on view basically to improve performance of the view.

someone suggested me with row number function i tried that and it came up with some error , which i posted in my recent reply...

please help me if you can with this...or correct me if am missing out something in my question

Thank you...

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-11-17 : 14:41:14

quote:
Originally posted by rds207

I GET THE ERROR BELOW

CANNOT CREATE INDEX ON VIEW VIEWNAME BECAUSE IT CONTAINS A RANKING OR AN AGGREAGATE FUNCTION,REMOVE THE FUNCTION FROM THE VIEW , ALTERNATIVELY DO NOT INDEX THE VIEW (MS SQL SERVER ERROR:10143)

pLEASE HELP

I'll go ahead and quote myself here since it was already suggested to look at BOL:

quote:
Originally posted by Lamprey

If you want to actually index the view you should refer to BOL as there are several requirements that must be met before you can index a view.

Search for "Creating Indexed Views."

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-17 : 14:42:18

quote:
Originally posted by rds207

can we create an identical column in view?

if yes what is the syntax?

Please help!

INDEX?????

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-17 : 14:46:15

OK, got it now.....

You want to improve performance on a view....

Well a view doesn't incur performace issues until it's called...

So, would you care to share with us what the performance problem is by supplying us with these 2 things?

1). The DDL of the actual View
2). The DDL of the tables that make up the view
3). The T-SQL command that is incurring performance problems, and how long it is taking. In addition, the Execution Plan would also be very helpful

Post these things, and I'm sure we can help you

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

rds207
Posting Yak Master

198 Posts

Posted - 2009-11-17 : 15:06:46

Ok ...here is the link of word document with the structure of tables and view

http://www.yousendit.com/download/TzY0eFlkUnEwZ21Ga1E9PQ

The query am using is just selecting top 1000 rows and it takes 7min ,by creating a row index it reduced to 3min , but i cannot create an index in rownumber ....

SELECT TOP 1000 [BUILD_IND]
,[JOB_ID]
,[BUILD_ID]
,[JOB_NAME]
,[PHONE_BUILD]
,[USER_NAME]
,[SITE]
,[TYPE]
,[SUB_SYSTEM]
,[TECHNOLOGY]
,[CHIP_GROUP]
,[TARGET_NAME]
,[BATCH_ENV]
,[BATCH_SERVER_NAME]
,[BATCH_QUEUE_NAME]
,[CLIENT]
,[BUILD_SIZE]
,[POOL_NAME]
,[POOL_USED]
,[RUN_SITE]
,[VERS]
,[BUILD_SITE]
,[BUILT_DT]
,[SUBMIT_TIME]
,[START_TIME]
,[END_TIME]
,[BUILT_BY]
,[MAKETYPE]
,[RUN_GMT_DIFF_SECS]
,[JOB_STATUS]
,[BUILD_STATUS]
,[CUSTOMERS]
,[COUNTRY]
,[ENG_LABEL]
,[JOB_LIMIT]
,[JOB_COUNT]
,[DAY_LIGHT_FLAG]
,[SUBMIT_TIME_SECS]
,[START_TIME_SECS]
,[END_TIME_SECS]
,[GMT_DIFF_SECS]
,[SLOC_STATUS]
,[PREBUILD_ID]
,[SWBASELINE]
,[REQUESTBY]
,[MAKELEVEL]
,[BUILDCHARACTER]
,[POOL_SIZE]
,[MYID]
FROM [ASWTrans].[dbo].[DW_T_BUILD_VIEW]

rds207
Posting Yak Master

198 Posts

Posted - 2009-11-17 : 15:07:44

Can you please let me know wHAT IS BOL???

quote:
Originally posted by Lamprey

quote:
Originally posted by rds207

I GET THE ERROR BELOW

CANNOT CREATE INDEX ON VIEW VIEWNAME BECAUSE IT CONTAINS A RANKING OR AN AGGREAGATE FUNCTION,REMOVE THE FUNCTION FROM THE VIEW , ALTERNATIVELY DO NOT INDEX THE VIEW (MS SQL SERVER ERROR:10143)

pLEASE HELP

I'll go ahead and quote myself here since it was already suggested to look at BOL:
quote:
Originally posted by Lamprey

If you want to actually index the view you should refer to BOL as there are several requirements that must be met before you can index a view.
Search for "Creating Indexed Views."

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-11-17 : 15:09:21

quote:
Originally posted by rds207

Can you please let me know wHAT IS BOL???

Sure, BOL is Books Online. Which you can get to from the Help menu in Management Studio.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-17 : 15:15:06

quote:
Originally posted by X002548

quote:
Originally posted by webfred

Yeah that's right - don't wait for an answer...
We don't want members that will/can ask questions more clear!

No, you're never too old to Yak'n'Roll if you're too young to die.

Have you no respect for the Kaiser?

And if we waited for clear an concise questions....well....I thought this was part guessing game...

And besides...didn't I answer the question correctly?

Brett

8-)

I am so sorry my Kaiser

Well I think now you have checked that is was a bit too early to give the right answer - my Kaiser

No, you're never too old to Yak'n'Roll if you're too young to die.

rds207
Posting Yak Master

198 Posts

Posted - 2009-11-17 : 15:33:15

OH I THINK YOU THOUGHT INDENTITICAL COLOUMNS :-)

I MEANT TO SAY IDENTITY COLUMN, ONE USED AS A PRIMARY KEY!(To create unique index)

Sorry for creating confusion !

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources