Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2007

    Unanswered: SQL Server Tips and Tricks

    For your day-to-day SQL Server issues like query tuning, optimization, TSQL problems, I am writing the blog called

    Some of the latest articles are:
    1. Generating 1 million rows in less than a second
    2. Conditionally add column in the table
    3. Multiple Inserts in one statement
    4. Capture every data operation in SQL Server 2008
    5. 100 Nano seconds precision in SQL Server 2008
    6. Represent Trees and Graphs in TSQL
    7. MERGE Statement of SQL Seerver 2008
    8. Return Last n Orders by using APPLY operator
    9. Number padding in TSQL
    10. Microsoft Performance Point Server and Sharepoint
    11. Caching and Recompilation in SQL Server 2005

    and many more....

    I will really appreciate comments and suggestions.

  2. #2
    Join Date
    Jun 2003
    Provided Answers: 1
    Quote Originally Posted by namwarsblogofincrediblyusefulsqltipsandtricks
    Don't you feel boring sometimes when you have to write an script to insert multiple rows in a table and you do not have any other choice except writing multiple insert statements? In one of my previous posts I showed a way to insert multiple rows by using UNION ALL but wait! there is another way available in SQL Server 2008 called Row Constructor which is an ANSI terminology for pseudo table of rows.
    It is actually a way to provide a set of row values in one statement. Following example will help you understand better. Please note that this example can work only on SQL Server 2008 or above:

    --Switch to tempdb
    Use tempdb

    --Create a test table in temdb
    Create table tblCountries (id int, country varchar(50)

    --Inserting multiple values
    Insert into tblCountries (id,country)
    (1,'USA'), --Row 1
    (2,'UK'), --Row 2
    (3,'France') --Row 3

    --Now select, you will get 3 rows
    Select * from tblCountries
    "SQL Server 2008 or above"????....2008 isn't even released yet.
    "Use tempdb"???? What the heck is that about?
    "--Now select, you will get 3 rows"...yes, aren't databases great? Put three rows in and get three rows out. Thank you for sharing this practical tip. I shall bookmark for the next time I need to put three rows in a table.

    Yet another reason I don't bother reading blogs.
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

  3. #3
    Join Date
    Apr 2002
    Toronto, Canada
    feh, mysql has had row constructors since forever | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Sep 2007

    Psuedo Tables and Row Constructors are ANSI Standards

    SQL Server 2008 CTP has been released and Microsoft is planning for the formal release early next year.
    As far as 3 rows in and 3 rows out thing This is for the typical TSQL scenarios where you are not allowed to create tables but you do not want cursors because of performance reasons.
    Please read some stuff about psuedo tables and row constructors in ANSI SQL.

    Anyway, thanks for your comments

  5. #5
    Join Date
    Nov 2004
    on the wrong server
    Provided Answers: 6
    damn it, is Brett still on vacation?

    Anybody remember something like this...

    Me beist good clever programmer and would appreciate well if you investigate code brillance of mine.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Feb 2004
    One Flump in One Place
    Quote Originally Posted by blindman
    "SQL Server 2008 or above"????....2008 isn't even released yet.
    Future proofing. Now he does not need to edit the page when SQL 2011 comes out.
    Quote Originally Posted by blindman
    "Use tempdb"????
    You see that quite often for sample scripts on t'web.
    Quote Originally Posted by blindman
    "--Now select, you will get 3 rows"...yes, aren't databases great? Put three rows in and get three rows out.
    Ah - but is this specific to three rows? Does it scale up to four? Five? Six? Seven? Eight? Nine? Ten? Eleventeen? .....I'll see if I can think of some more numbers later.
    pootle flump
    ur codings are working excelent.

Posting Permissions

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