May 10, 2007 at 11:36 pm
sql server 2000
i have a question regarding indexing,
difference between clustered index, and non clustered index
primary key has a default clustered index, while unique key has a default non-clustered index.
what is the default sort oder, can we change it
but how does it work?, they will ask me to explain, which is trouble
2) can trigger be manually fired ?
a simple sql questions wud be asked, iam going for an asp developer post, but out of touch with sql server since 2 years
help pls
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
May 11, 2007 at 12:03 am
1. Firstly it's better do not mixed up primary keys with indexes, they have different purposes.
A clustered index defines the physical order of rows in a table, while a primary key is used for defining the RI between tables. By creating a PK you'll get a clustered index by default, but this does not mean you have to define the physical sorting order of that table as being the PK field.
In each table you can have 1 clustered index that can be unique or not (unique is recommended) and many unique-keys defined as non-clustered indexes. An unique key is actualy a constraint; you can define non-clustered indexes (logical order of records) that are not unique keys.
2. a trigger is fired by manually or automatically inserting or updating or deleting data
Hope this answers you questions.
May 11, 2007 at 12:51 am
thank you for your reply.
on clustered index, what is the sort oder ascending or descending can i change it manually?
in some tutorial on the net i have read, u cannot fire trigger manually,and i doubt it
can i fire trigger manually?, if yes how
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
May 11, 2007 at 1:12 am
The sorting order can be defined as asc or desc at the moment of index creation; this can be changed with ALTER INDEX statement or using Enterprise Manager (right click over the table name, choose modify table, click over the indexes icon, choose the index ... sorry, I do not have sql em here);
Triggers are fired only by inserting/updating/deleting data in the table the trigger is built for. If you're thinking to manually execute a trigger without changing records into the parent table, then this is not possible.
May 11, 2007 at 1:52 am
by default primary key creates clustered index, can i change that to non-clustered index.
i a have table with only a single unique key and can i assign clustered index to that
thank you for all your answers
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
May 11, 2007 at 2:26 am
When you create a primary key you can make it clustered or non clustered. Check books online for the syntax. The only way to change from clustered to nonclustered is to drop the pk and recreate.
You can put the clustered index anywhere you want, on the pk, on a unique constraint, as a normal index. No restrictions.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 11, 2007 at 3:38 am
thank you for all your help,
since interviewr is gona twist questions and ask, so i asked all these things
iam weak about indexing, never used them.
so might be a problem
thank u
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply