Request for topics…

Please post requests for topics or features that you would like to know about. This can be any of the SQL/TSQL language features or programmability in general. There are so many things to discuss about wrt SQL Server 2005 so it will be good to gauge interest of database developers/DBAs out there. Thanks.



Comments (28)

  1. Keith Farmer says:

    Actually, I’d be interested in knowing if/how one might create a SQL Server service which accepted serialized .NET objects.

    Like an Expression Tree generated by LINQ, perhaps…

  2. venu says:


    Even though the DBMirror feature is dropped in the up-coming release, wanted to know How this DBMirror is implemented in SQL Server 2005 from the design prospect.

    Is it something Replication/Synchronization, what kind of technology you guys used and considering while implementing this. Is the mirroring is done only for committed transactions or even un-committed ones ?

    Would like to here from you what you guys implemented.


  3. veny says:

    Oops..just got a link towards some qns I posted..

    Thanks anyway

  4. Davide Mauri says:

    I’d be interested to dig into the varchar(max) type, to understand how it is stored on the disk and if there are any perfomace impacts (like the "text" type)

  5. Ian Ringrose says:

    We have a database that is created as part of our automatic system tests. These checks run each time we do a check in so having them run faster would be of great benefit.

    Sql server seems to spent a lot of time writing to the disk while creating the database and running all of our SQL to create the tables. It would be great if there was a way to tell Sql Server that we do not care if the database is lost when power goes down.

    Within each unit test it’s self we:

    a) start a transaction

    b) delete all rows from all tables

    c) add the data we need for the test.

    d) run the test

    e) abort the transaction.

    This works great, in that anyone can run a unit test on there local database without messing up any data they have carefully created in the application by hand. However it is a bit slow. Is there anyway to give sql server a “hint” that the transaction will NEVER be committed and therefore that it can do everything in RAM.

  6. AndrewG says:

    Coming from an Oracle background I’d like to see:

    1) Bitmap indexes

    2) Less restrictive indexes views

    3) More analytic functions (should be easy to add!)

    4) MERGE statements

    Also, I often find table pivoting frustrating in both Oracle and SQL Server. With the new function you still have to specify the column values – I would like SQL Server to dynamically display all the values automatically

  7. Cristian Lefter, SQL Server MVP says:

    I would love to see more tips on using Dynamic Management Views

  8. In Integration Services, are there any plans for being about to input raw EBCDIC files that contain Packed Decimal formatted columns (Comp3, Zoned, etc)?

    This is something seen quite frequently in the healthcare industry and could be an excellent driver for migrating away from Oracle systems into Yukon.

  9. sqletips says:

    <DIV>I posted another sample on how to use the new execution related DMVs to get costly query plans and other details. Check it out.</DIV>



  10. sqletips says:

    Use of SQLCLR should allow you do this but it also has it’s own restrictions/limitations in this release. Please check out the SQLCLR samples that come with the setup CD.

    As for LINQ, it is still a work in progress. And IMO it doesn’t have the same expressive power of SQL. It will probably help people to write applications that use simple queries but I don’t see myself using it though in any form soon.


  11. sqletips says:

    Sure. I will post some information on this soon. At high-level, the MAX data types use the same infrastructure as text/ntext/image. The defaults are reversed however in terms of how they are stored in a row. The MAX data types are in-row by default whereas the text is always out-of-row. These can be modified later by using sp_tableoption. The MAX data types also provide you the capability to declare variables and use them locally in TSQL modules.

    Some of the performance considerations include your data access pattern, storage requirements, use of MAX variables in code etc.


  12. sqletips says:

    You can control the amount of logging by using SIMPLE recovery model. Note that SQL Server is a RDBMS that adheres to the ACID principles so all operations are logged in some form or another. There are however minimally logged operations like BULK INSERT, bcp, truncate table, SELECT…INTO etc which will improve performance. Other than these options, you cannot instruct SQL Server to suppress logging in any form. As far as database creation is concerned, SQL Server 2005 supports fast file initialization on Windows Server 2003 platform which greatly reduces the time taken to create empty database files of any size.


  13. sqletips says:

    >>1) Bitmap indexes

    We do create bitmaps internally as part of query execution to simplify certain join operations. For example, during the hash building process we will create a bitmap that can be used by the probe operator to throw out rows before doing the actual hash match.

    >> 2) Less restrictive indexes views

    Yes. This is something we are considering in each new release of SQL Server. SQL Server 2005 has relaxed some of the restrictions in SQL Server 2000.

    >> 3) More analytic functions (should be easy to add!)

    >> 4) MERGE statements

    >> Dynamic pivot

    – Yes. These are some features we are considering for a future release. I would encourage you to send your requests to so that we can track them. It would help to also explain your scenario for these features.


  14. sqletips says:

    I do not know about the plans for Integration Services. You should search MSDN for blogs from members of that team and ask there.


  15. PP says:

    Hi Umachandar

    I have following things to add:

    1. Code Library


    I have always been a fan of collecting code that can be treated as repository. I’m pretty sure there are quite a number of people who write SAME code again & again, if these code(s) can be provided as LIBRARIES along with base-product and if there is any drive going on in that space, I would love to see what you guys are up-to.

    2. Coding Practices


    There are quite a few way of writing code, if you guys can do some Best Practices on what / how to write some of the COMPLEX codes, may be that would help every body.

    3. Code Review


    What are the most common problems you guys have seen from developers coding practices, such that people wont repeat the same mistakes

    4. SQL Errors


    If you guys can come up with a central repository for FAQ’s nothing like it

  16. Hi Umachandar,

    When writing a CLR UDT, is it possible to get see the name of the table and the field through reflection?

    Could you write a short demo on how to do that?



  17. Freaky says:

    Can anyone tell me if there’s a reason why we souldn’t ALWAYS use varchar(max)/nvarchar(max) instead of needlessly limiting our data in size (like in varchar(50))? It seems to me that with 2005 we don’t need to use the "old" data types anymore. The question applies to table columns, stored procedure parameters, and variables.



  18. SimonS says:

    It was clear talking to the MS guys at the PDC that the focus has been on shipping Yukon and not the…

  19. SimonS says:

    It was clear talking to the MS guys at the PDC that the focus has been on shipping Yukon and not the…

  20. Fred L says:

    Is there a way to determine the number and makeup of the resultsets of a stored procedure?  I want to write a code generator to create class to encapsulate the resultsets.  I have many stored procedures which work with multiple tables and return information pulled from the parts of many tables.

  21. sqletips says:
    There is no way to determine the number and makeup of the resultsets of a stored procedure without actually executing if. You could use the approach that the driver/provider uses which is to use SET FMTONLY ON and execute the sp or code. But this has lot of restrictions like it will not work for resultsets whose shape/metadata differs based on the parameter values or uses temporary tables and so on. If your stored procedure logic is deterministic then you can use the SET FMTONLY ON approach. Hope this helps.
  22. The Dialog / Conversation model used in the DBMail subsystem.  I have a several mail servers (XMAIL – that dbmail will not have a polite conversation with.  I would like to see more information about how to modify the real dbmail settings, or a best-practices or guideline on either modifing or going around dbmail to talk to various ExternalMailService systems.

  23. It was clear talking to the MS guys at the PDC that the focus has been on shipping Yukon and not the…

  24. sqlrajan says:

    thank u

  25. dear Umachandar

    A few days ago ,I read somewhere about  UPSERT/Merge  but I could not test it

    because the express edition of sql 2005 which I used ,dose not support this command

    I would be most grateful if you tell me what it used for and is it available

    in Express edition or not.

  26. sqletips says:

    UPSERT/MERGE is a single DML statement that can be used to insert, update or delete rows from a table using another table source or query expression. Also, MERGE statement is part of the ANSI SQL standard so you can look there or Oracle documentation to know more about the syntax and details. SQL Server does not currently support UPSERT/MERGE statement so you will have to use individual INSERT/UPDATE/DELETE statements in a transaction.


  27. It was clear talking to the MS guys at the PDC that the focus has been on shipping Yukon and not the