SQL Server Performance

What does this syntax signify

Discussion in 'General Developer Questions' started by amara, Sep 14, 2006.

  1. amara Member

    "GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON"

    Dear friends,could you please let me know,what the above syntax signifies.

    Thanks,
    Amara
  2. ghemant Moderator

    Hi,
    ------------------------------------------------------------------------------------------------------
    When SET QUOTED_IDENTIFIER is ON (default), all strings delimited by double quotation marks are interpreted as object identifiers. Therefore, quoted identifiers do not have to follow the Transact-SQL rules for identifiers. They can be reserved keywords and can include characters not generally allowed in Transact-SQL identifiers. Double quotation marks cannot be used to delimit literal string expressions; single quotation marks must be used to enclose literal strings. If a single quotation mark (') is part of the literal string, it can be represented by two single quotation marks ("). SET QUOTED_IDENTIFIER must be ON when reserved keywords are used for object names in the database.

    When SET QUOTED_IDENTIFIER is OFF, literal strings in expressions can be delimited by single or double quotation marks. If a literal string is delimited by double quotation marks, the string can contain embedded single quotation marks, such as apostrophes.
    ------------------------------------------------------------------------------------------------------

    i would suggest to refer BOL for more.

    Hemantgiri S. Goswami
    ghemant@gmail.com
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri S. Goswami
    ------------------------
    http://hemantgirisgoswami.blogspot.com
  3. amara Member

    thanks hemanth.
    I got another doubt:
    Say I want to create ab table in a Database.I first want to check whether we have any table of that name or not.If that table is already there,query shld print out a message that table already exists.if the table is not there it should create one

    CREATE TABLE [dbo].[temporary] (
    [DriveLetter] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Reason] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DenyDate] [datetime] NULL
    ) ON [PRIMARY]
    GO

    what should be the syntax to check if this table exists or not:
  4. ghemant Moderator

    you may use object_id function to check the existance of the object from master..sysobjects table.

    Regards

    Hemantgiri S. Goswami
    ghemant@gmail.com
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri S. Goswami
    ------------------------
    http://hemantgirisgoswami.blogspot.com
  5. Roji. P. Thomas New Member

    Note that the current settings are saved with the stored procedure definition and will be used everytime you execute the procedure.

    Roji. P. Thomas
    Microsoft SQL Server MVP
    http://toponewithties.blogspot.com
  6. ramkumar.mu New Member

    if object_id('temporary') is not null
    print 'table exists'
    else
    CREATE TABLE [dbo].[temporary] (
    [DriveLetter] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Reason] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DenyDate] [datetime] NULL
    ) ON [PRIMARY]
    GO




    quote:Originally posted by amara

    thanks hemanth.
    I got another doubt:
    Say I want to create ab table in a Database.I first want to check whether we have any table of that name or not.If that table is already there,query shld print out a message that table already exists.if the table is not there it should create one

    CREATE TABLE [dbo].[temporary] (
    [DriveLetter] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Reason] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DenyDate] [datetime] NULL
    ) ON [PRIMARY]
    GO

    what should be the syntax to check if this table exists or not:


    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  7. Madhivanan Moderator

    quote:if object_id('temporary') is not null
    print 'table exists'
    That systax is common to check for all objects. To check for table, then use sysobjects with xtype='u' or make use of ObjectProperty



    Madhivanan

    Failing to plan is Planning to fail

Share This Page