SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    I'm a college yuppie now! sbdi's Avatar
    Join Date
    Jul 2000
    Location
    Dublin , Ireland
    Posts
    1,271
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Quick Explanation: SQL

    Hi I've been given the following SQL statement some of it I'm hazy on. Wondering if you could help me out with what it means [img]images/smilies/smile.gif[/img]

    PHP Code:
    if exists (select from sysobjects where id object_id(N'[dbo].[Users]') and OBJECTPROPERTY(idN'IsUserTable') = 1)
    drop table [dbo].[Users]
    GO
    CREATE TABLE 
    [dbo].[Users]
    (
    [
    ID] [intIDENTITY (11NOT NULL ,
    [
    Name] [varchar] (10NOT NULL
    )
    ON [PRIMARY]
    GO
    ALTER TABLE 
    [dbo].[UsersWITH NOCHECK ADD 
    CONSTRAINT 
    [PK_UsersUNIQUE NONCLUSTERED 
    (
    [
    IDID]
    )
    WITH FILLFACTOR 90 ON [PRIMARY
    GO
    GRANT SELECT ON 
    [dbo].[BDPxUserRolesTO [public]
    GO
    GRANT SELECT 
    INSERT DELETE UPDATE ON [dbo].[UsersTO [maint]
    GO 

    Ok I know its creating 2 colummns ID and NAme int and varchat value. Anyone tell me what the identify refers too ? Whats the alter table do as well ?
    Back Again

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    IDENTITY defines an autonumber field -- as rows are inserted into the table, this field is assigned values that start at 1 and increment by 1

    this particular ALTER TABLE statement adds a unique nonclustered index to the table

    ... which i don't understand, because a primary key would be better, except that it would be clustered

    and i'm afraid i'm not enough of a DBA to understand how that particular distinction would manifest itself on larger tables

    in any case i think the syntax is wrong, it says this unique nonclustered index is on a column called IDID and i'm pretty sure it should be ID instead

    do any other tables reference this table via a foreign key?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    I'm a college yuppie now! sbdi's Avatar
    Join Date
    Jul 2000
    Location
    Dublin , Ireland
    Posts
    1,271
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes they do, the ID thing was a typo on my behalf
    Back Again

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    how are those foreign keys defined?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It is creating a primary key constraint on the nonexistant field IDID. Which is pointless.

    Creating a nonclustered index with a fillfactor of 90 for a PK is very interesting, there must be some special reason for it

    On a PK you would normally create a clustered index since MS SQL allows only one. All other indexes use the clustered index to find the row location on the heap. The fillfactor says how likely you believe it will be that you will be having inserts into this keyspace. Quite likely, probably, suggesting a fillfactor of mebbe 50% or so.

    You need the alter table because you can't add a constraint to a table which doesn't exist. So first create the table then alter it.

    I hope you don't already have a table called Users in your DB, because if you do, this script will toast it. This would be unfortunate, since the script doesn't specify in which DB it should be run, accidentally running it in the wrong DB may cause headaches


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •