Docstoc

SQL Server Difference FAQS-1

Document Sample
SQL Server Difference FAQS-1 Powered By Docstoc
					1) What are the Differences between TRUNCATE and Delete?

  S.N   Truncate                       Delete
  o
  1     Truncate is faster             Delete is comparatively slower
  2     Removes all rows from a        Can remove specific rows with
        table                          Where clause
  3     Is DDL Command                 Is DML Command
  4     Resets identity of the table   Does not reset identity of the
                                       table
  5     Removes the data by            Removes one row at a time and
        deallocating the data pages    records an entry in the
        and logs the deallocation.     transaction log for each deleted
                                       row.
  6     Cannot be rolled back          Can be rolled back


2) What are the differences between Primary key and Unique
   key?

  S.N   Primary Key                    Unique Key
  o
  1     Creates Clustered index        Creates Non-Clustered index
  2     Null values are not allowed    Allows only one null value


3) What are the Differences between Clustered Indexes and Non-
   Clustered Indexes?



  S.N   Clustered Indexes              Non-Clustered Indexes
  o
  1     It reorders the physical       It sorts and maintain a separate
        storage of records in the      storage
        table
  2     There can be only one          More than one
        Clustered index per table
  3     The leaf nodes contain data    The leaf node contains pointer to
                                       data


4) What are the differences between Stored Procedures and User
   Defined Functions?

  S.N   Stored Procedures              User Defined Functions
  o
  1     Stored Procedure cannot be     User Defined Function can be
        used in a Select statement     used in a Select statement
  2     Stored procedure supports       User Defined Function does not
        Deferred Name Resolution        support Deferred Name
                                        Resolution
  3     Stored Procedures are           User Defined Functions are
        generally used for              generally used for Computations
        performing Business Logic
  4     Stored Procedure need not       User Defined Functions should
        return a value                  return a value
  5     Stored Procedures can return    User Defined Functions cannot
        any datatype                    return Image
  6     Stored Procedures can           User Defined Functions accept
        accept more number of input     lesser number of input
        parameters than User            parameters than Stored
        Defined Functions. Stored       Procedures. UDF can have upto
        Procedures can have upto        1023 input parameters
        21000 input parameters
  7     Stored Procedures can use       Temporary Tables cannot be
        Temporary Tables                used in a User Defined Function
  8     Stored Procedures can           User Defined Functions cannot
        execute Dynamic SQL             execute Dynamic SQL
  9     Stored Procedure supports       User Defined Function does not
        error handling                  support error handling.
                                        RAISEERROR or @@ERROR are
                                        not allowed in UDFs
  10    Non-deterministic functions     Non-deterministic functions
        can be used in Stored           cannot be used in User Defined
        Procedures.                     Functions (UDFs). For example,
                                        GETDATE() cannot be used in
                                        User Defined Functions(UDFs)


5) What are the differences between Where and Having clauses?

  S.N   Where clause                    Having clause
  o
  1     It applies to individual rows   It applies to a group as a whole
  2     It selects rows before          It selects rows after grouping
        grouping
  3     It cannot contain aggregate     It can contain aggregate
        functions                       functions
  4     It can be used in select,       It is used only in select clause
        delete ,insert etc.


6) What are the differences between Union and UnionAll?

  S.N   Union                           UnionAll
  o
  1     This is used to eliminate       It will not eliminate duplicate
        duplicate rows                  rows
  2     This selects only distinct rows   It selects all the values
  3     It can be used to combine         It can be used to combine
        any number of queries             maximum of 2 queries
  4     It cannot contain aggregate       It can contain aggregate
        functions                         functions


7) What is the difference between normal Select statement and a
   Cursor?

  S.N   Select statement                  Cursor
  o
  1     Select statements are used        Cursors are used for row-level
        for table-level processing        processing

				
DOCUMENT INFO
Shared By:
Tags:
Stats:
views:57
posted:3/23/2010
language:English
pages:3
Description: This provides a SQL Server FAQS-1 which are of "Difference between" kind