Gaurav_MS : Hello All
Gaurav_MS : Welcome to MSDN India Expert Chat
Gaurav_MS : on T-SQL Enhancements in SQL Server 2005
Gaurav_MS : My name is Gaurav Khanna and am your moderator
Gaurav_MS : Do post your questions
Gaurav_MS : in this window
Gaurav_MS : prefix them with Q:
Vijay : Gaurav, can you briefly explain about the T-SQL enhancements
Gaurav_MS : Our Experts for the day
Gaurav_MS : are
Gaurav_MS : Srinivas Sampth - MVP SQL Server
Gaurav_MS : and Vinod Kumar - MVP SQL Server
Gaurav_MS : they are joining us shortly
Gaurav_MS : So, do keep your questions ready
Jignesh[dotnetjini] : Q: ya would like to know more about "Mobile Database Support"
Vijay : Q: I have a question on SQL server installation. Can I install SQL server enterprise edition on windows XP.
Chintan : Q:what are the effects of integrating CLR into Yukon is it boon or a bane ?
Gaurav_MS : Srinivas is here
Gaurav_MS : and will be chatting using my ID
Gaurav_MS : Welcome Srini
: Srini: Hi folks
Chintan : good afternoon srinivas
: Srini: I work out of Bangalore for a company called SCT and I manage all the technology initiatives of our organization
: Srini: Let me pick up some questions that have already come
: I have a question on SQL server installation. Can I install SQL server ent edition on windows XP.
: The answer is you cannot. Enterprise Edition is not supported in XP
: Srini: Next question. Q:what are the effects of integrating CLR into YUkon is it boon or a bane ?
: Srini: Interesting question and the answer is, it depends on how you look at it and what you want to use it for
: Srini: If you use the CLR features in ways it is not intended for, it is a bane, otherwise, it can be a boon
Chintan : Q:There is already well establised/tested SQL and separate front end tools are there what is the need of CLR in YUKON
: Are there any other questions that I can answer upfront?
: Srini: CLR in Yukon can be used for many purposes.
Chintan : Q:can u just brief me the outstanding features of YUKON
Jignesh[dotnetjini] : Q: ya would like to know more about "Mobile Database Support"
: Srini: For example, in places where the T-SQL capabilities are not sufficient (example String Manipulation), CLR functions can be used.
: Srini: Anyways, let me get onto the main chat and that is T-SQL enhancements
: Srini: Why did we choose to share the T-SQL features and not some absolutely new features of SQL Server 2005?
: Srini: Well, for most SQL Server people, T-SQL is their life and we wanted to assure you that its importance has not diminished a bit with the new release
: Srini: In fact, in several areas, it has received a new face lift and many of the new features in SQL Server 2005 are enabled through T-SQL
: Srini: So, T-SQL is not dead and this chat will explore why
Mandy : Can you provide a list of major enhancements in T-SQL viz CLR in Yukon ?
: Srini: Before we get into the guts of this session, let us define what SQL Server 2005 is all about
: Srini: You can define SQL Server 2005 as "A secure, scalable, Enterprise-class Data Management platform for Windows Server systems"
: Srini: The features introduced in SQL Server 2005 are aimed at the following categories:
Mandy : Guidelines about what should be coded in T-SQL and what should be in CLR ?
: Srini: (1) Developer Productivity
: Srini: (2) Business Intelligence
: Srini: (3) Enterprise Data Management
: Srini: For example, the integration of the .NET CLR represents a significant leap in terms of developer productivity
: Srini: You can now author stored procedures, triggers, functions and aggregates using any .NET language
: Srini: Similarly other new features like service Service Broker, Notification Services aim at improving the overall developer productivity.
: Srini: Before I get into the next set, let me answer Mandy's question on best practices for T-SQL vs. CLR
Praveenkp : how CLR is integrated with YUKON
: Srini: You would use T-SQL when you are performing set based operations like joins etc
: Srini: You would use CLR based code when you want to do CPU intensive functions (like complex calculations, iterations etc).
: Srini: Also, if you need to take advantage of some framework class features, CLR functions are the best choice.
: Srini: Let me continue with the T-SQL enhancements
: Srini: As mentioned, T-SQL has undergone a major set of improvements in SQL Server 2005 based on past customer feedback
GAVI : okay
: Srini: Some of the significant improvement areas are:
: Srini: (1) New Data Types
: Srini: (2) MARS support
: Srini: (3) Native XML support
: Srini: (4) New Isolation Levels
: Srini: (5) Exception Management
: Srini: Recursive Queries
: Srini: It was Recursive Queries
: Srini: (7) New T-SQL functions for ranking and transforming data
Mandy : Thx for ans my Q. Can I use T-SQL and CLR functions in combination. like If-else in CLR and in between set based joins ? You can answer this after completing your current conversation.
Praveenkp : whether yukon BETA is released into the market ?
Mandy : What is MARS ?
: Srini: DDL Triggers
: Srini: It was DDL Triggers as the 8th point
james : any noticeble enhancement in preformance improvement in sql2005
: Srini: In this chat we will touch upon some of the items mentioned above
Praveenkp : why it is called yukon any specific reason, i should have called as sql 2005
: Srini: To answer one question: Yukon is current in its BETA 2 and this is available through MSDN.
Chintan : Q;What is CTE(Common Table Expression) brief me
: Srini: You can also use SQL Server Express
: Srini: CTEs. We will be seeing this down the line. Just hold on
: Srini: Let us now look at some of the new data types introduced in SQL Server 2005
Chintan : ok
: Srini: SQL Server 2005 introduces the following new data types
Praveenkp : ok
: Srini: 1. XML
: Srini: 2. VARCHAR (max), NVARCHAR (max) and VARBINARY (max)
: Srini: We will not be covering XML in this chat as there was a separate chat topic dedicated for the same
Praveenkp : xml support was introduced in sql 2000 i believe
: Srini: Catch the same on MSDN at: Exploring XML in SQL Server 2005 - http://www.microsoft.com/india/msdn/chat/transcripts/151.aspx
: Srini: In prior versions of SQL Server, apart from the VARCAHR and NVARCHAR data types, we also had TEXT and IMAGE
: Srini: We would use TEXT and IMAGE data types of large values (called BLOBs)
: Srini: All the other basic character data types in SQL Server were limited to 8000 bytes (or 4000 if you used Unicode)
: Srini: With the advent of the "max" identifier, you can now store upto 2GB worth of data in VARCHAR fields (or 1GB in NVARCHAR fields)
: Srini: Why this change?
: Srini: The basic driving factor for introducing this new identifier was for flexibility
: Srini: Earlier versions of SQL Server required you to have a different set of functions (like SUBSTRING) to work on small character data
: Srini: and other functions (like TEXTPOINTER etc) for large data types
: Srini: With the "max" identifier you can now use a standard set of character manipulation functions for both small and large character data
Praveenkp : gaurav could u please answer my question
: Srini: This basically flattens out the API that you will need to learn to only a few core functions
: Srini: Praveen, to answer your question. XML was introduced in SQL Server 2K, but not a first class data type like INTEGER
: Srini: In SQL Server 2005, XML is a first class data type and you can now create variables, table columns, parameters etc as XML
james : Q:any noticeble enhancement in performance improvement in sql2005
: Srini: You also have a new query language called XQEURY that you can use to manipulate the XML content and this is a big improvement over what SQL Server 2000 offered.
: Srini: any noticeable enhancement in performance improvement in sql2005
: Srini: Plenty, but you will have to specific on what areas are you looking for
: Srini: Let's continue with the talk...
: Srini: The next feature set that we will cover is that of Ranking Functions
: Srini: Given a set of data, there is sometimes a need to know how each data element ranks with respect to the other
james : ok..
: Srini: For example, if you need to rank your sales representatives based on their sales in each district, how would you do it?
: Srini: Earlier SQL Server solutions require you to create a pseudo-column in the result set that ranks the various records on a given criteria
Praveenkp : since the CLR is integrated with Yukon how does it impact on the execution plan of my queries ?
: Srini: In SQL Server 2005, you now have a set of new functions that do this for you. These are called:
: Srini: RANK
: Srini: DENSE_RANK
: Srini: NTILE
: Srini: RANK basically provides a rank for each row with a given "partition".
: Srini: For example, let us assume that you have a table of students with each row containing the name of the student, the gender and a total percentage of the marks obtained
: Srini: Now, if you want to rank this data based on gender, you can issue the following query:
: Srini: SELECT RANK() OVER (PARTITION BY Gender ORDER BY TotalPercent DESC) AS [Rank] FROM dbo.StudentMarks
: Srini: The PARTITION clause helps divide the result set into various groups over which the RANK function is applied. This is an optional clause
: Srini: The ORDER BY clause determines the ranking order of the rows returned
: Srini: One caveat to note in the RANK function is: If there are two rows with the same rank, the next higher rank will skip by 1
Praveenkp : since the CLR is integrated with yukon how does it impact on the execution plan of my queries ?
: Srini: For example, if two students in the table above have the same rank (say 2), then the next student would have a rank 4 (note that 3 will not be given as a rank)
: Srini: If you want to have 3 as a rank, then use DENSE_RANK. The syntax of the function is the same as that of the above query except that there will be no gaps in the ranking
: Srini: The NTILE function can be used to distribute the rows in an ordered partition into a specified number of groups
: Srini: For example, assume that we want to distribute the students in the above table based on gender and with each gender create two groups, you would issue the following statement
: Srini: SELECT NTILE(2) OVER (PARTITION BY Gender ORDER BY TotalPercent DESC) AS [TiledOnGender]
: Srini: Note that the (2) specified in the NTILE function indicates the number of groups to create
: Srini: There is another interesting function that is present in SQL Server 2005
: Srini: Although it does not necessarily indicate a ranking function, it helps in numbering rows from 1..n
: Srini: The function is called ROW_NUMBER. Here is how you can use it
: Srini: SELECT ROW_NUMBER() OVER (PARTITION BY Gender ORDER BY TotalPercent DESC) AS RowNum, Name FROM dbo.StudentMarks
: Srini: Note that the syntax is very similar to that of the other ranking functions
: Srini: Basically, this function will provide a running number for the rows present in each partition
: Srini: ROW_NUMBER can basically used to implement paging of results and this is something that people have been trying to implement using various methods
: Srini: Ok, let me answer some questions now...
Praveenkp : Mr. Gaurav could you please answer my question
: Srini: Answering Praveen's question. The execution plan will not be affected provided you stick with the guidelines.
Mandy : My Q's
: Srini: Infact, there are many situations where your CLR code can outperform T-SQL equivalents
Praveenkp : how indexes are enhanced in yukon
: Srini: Mandy, what was your question?
: Srini: Let me continue with the chat topic as there as still some areas that I need to cover...
: Srini: The APPLY Operator. The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query.
: Srini: The table-valued function acts as the right input and the outer table expression acts as the left input.
: Srini: The right input is evaluated for each row from the left input and the rows produced are combined for the final output.
: Srini: There are two forms of APPLY: CROSS APPLY and OUTER APPLY.
: Srini: CROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function.
: Srini: OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function.
: Srini: Try this simple syntax in SQL Server 2000 to understand the same.
: Srini: Select .... From myTable a INNER JOIN my_TableValuedFunction (a.Col1) ON ....
: Srini: Mandy, I guess your question was: What is MARS?
: Srini: MARS stands for Multiple Active Result Sets
Mandy : I had asked prev. Again repeating the Q. - Thx for ans my Q. Can I use T-SQL and CLR functions in combination. like If-else in CLR and in between set based joins ?
Praveenkp : how indexes are enhanced in yukon
: Srini: MARS will allow you to fetch multiple results sets using a single connection. Earlier, you would get an error asking you to close the active connection.
: Srini: T-SQL and CLR functions can be interleaved.
: Srini: For example, you can write a query like:
Mandy : oh. Great MARS additon was really needed !
: Srini: SELECT dbo.myCLRFunction(), dbo.myTSQLFunction() FROM xxx
Praveenkp : how indexes are enhanced in yukon
Praveenkp : how indexes are enhanced in yukon
Jignesh[dotnetjini] Any Cursor Enhancements in Yukon...
: Srini: Indexes have not been majorly upgraded, but new indexing options are present which improve performance
Jignesh[dotnetjini] : Any Cursor Enhancements in Yukon...
: Srini: No special Cursor Enhancements. In-fact you can now replace forward-only read-only cursors with CLR functions which are called Streaming table-valued functions.
: Srini: Let me continue with the next T-SQL enhancement
Jignesh[dotnetjini] : OK.
: Srini: DDL Triggers
: Srini: DDL triggers can fire in response to a Transact-SQL event processed in the current database, or on the current server. The scope of the trigger
Praveenkp : i have seen there are major enhancements in indexes, how come ur telling there is no enhancement ??????
: Srini: depends on the event. For example, a DDL trigger created to fire in response to a CREATE TABLE event will do so whenever a CREATE TABLE event
: Srini: Not at the core index architecture level. As I said many new operations and performance enhancements have been done.
: Srini: occurs in the database. A DDL trigger created to fire in response to a CREATE LOGIN event will do so on whenever a CREATE LOGIN event occurs in the server.
: Srini: DDL triggers can be created to fire in response to:
: Srini: One or more particular DDL statements.
: Srini: A pre-defined group of DDL statements.
: Srini: A DDL Trigger can fire after execution of any Transact-SQL event belonging to a pre-defined grouping of similar events.
: Srini: For example, if you want a DDL trigger to fire after any CREATE TABLE, ALTER TABLE, or DROP TABLE statement executes, you can specify FOR DDL_TABLE_EVENTS in the CREATE TRIGGER statement.
: Srini: Refer to this link for more information: http://www.extremeexperts.com/sql/Yukon/DDLTriggers.aspx
: Srini: Let me also quickly brief you on the Exception Handling capabilities. Now Exception handling is more structured like any other .NET language.
: Srini: Its more robust when compared to the conventional @@ERROR statements. More often in TSQL alternate statements used to be @@Error.
: Srini: Welcome to the world of Try - Catch Blocks. Any error that is raised in the TRY block is caught in the CATCH block. There are some system functions that help you take necessary actions like:
: Srini: 1. Error_number – Returns the Error Number encountered
: Srini: Error_Text – Returns the Error Text encountered
: Srini: Error_Severity – Returns the severity number encountered
: Srini: Error_State – Returns the error state number encountered
: Srini: TRY CATCH can be nested to any levels and error raised will be caught at the nearest CATCH block.
: Srini: Read more on this at: http://www.sswug.org/see/20064
Praveenkp : Vinod kumar MVP was saying there are some cool feature of indexes
Praveenkp : whom do i trust, either you or Vinod Kumar im confused and lost also
: Srini: You can trust both of us. Let me explain some "cool" features of indexes.
: Srini: Index operations can now be done online; users can still access the table data and use other indexes on the table while one index is being created, altered or dropped.
: Srini: The MAXDOP clause can now be specified on index data definition language, thus controlling the number of parallel operations used by that specific statement.
Praveenkp : is it true for clustered indexes also ?
: Srini: Indexes in SQL Server also support XML columns, which is a big improvement.
: Srini: I may have to check that.
Praveenkp :
Praveenkp : i know your answer
: Srini: Columns that are not part of the index key can also be included in nonclustered indexes.
: Srini: The new ALLOW_ROW_LOCKS and ALLOW_PAGE_LOCKS options can e used to control the level at which locking occurs for the index.
: Srini: Any other questions that I can answer?
pavanaja : When will be Yukon, I mean, SQL 2005 released?
Praveenkp : what is the equivalent datatype in whidbey for a yukon xml datatype
: Srini: It is slated to be released for mid next year
: Srini: Possibly along with VS.NET 2005
pavanaja : Quite far away
pavanaja : Can I ask abt Yukon and not abt TSQL?
: Srini: In VS.NET 2005, ADO.NET has been enhanced to read XML datatypes from SQL Server and you now have a client side XQUERY library that you can use.
: Srini: Sure "pavanaja". What do you want to know about Yukon?
: Srini: While I wait for more questions, will poll you guys for one thing.
pavanaja : I want to know what are the enhancements in Yukon w.r.t. Indic
: Srini: Would you like to have a continuation of this chat on Wednesday (22nd)?
Chintan : yes
: Srini: We can continue discussing on some of the other enhancements in TSQL.
Praveenkp : in sql 2000 all the stored procs will be stored in the sql cache, but now where it will be stored since it is integrated with CLR , would it be on heap
: Srini: We have probably covered only 20% of them!
Jignesh[dotnetjini] if time permits i would like to know more on "Statement Level ReCompile "
pavanaja : For ex., the Kannada sorting in SQL 2000 is not perfect. Has it been fixed in Yukon?
: Srini: Have to check out on the Indic improvements. Will get back.
Vijay : YS
Chintan : what is indic
Praveenkp : in sql 2000 all the stored procs will be stored in the sql cache, but now where it will be stored since it is integrated with CLR , would it be on heap
Praveenkp : in sql 2000 all the stored procs will be stored in the sql cache, but now where it will be stored since it is integrated with CLR , would it be on heap
pavanaja : ok
: Srini: When a CLR procedure is called, the CLR runtime is invoked and from that point on the .NET Framework does its magic.
pavanaja : Indic refers to Indian languages
: Srini: At the SQL Server level, the procedure is stored as a binary form in the catalog tables.
Praveenkp : sorry i did't get it
: Srini: Let's say you created a CLR procedure.
Praveenkp : ok
: Srini: The first step is to register the procedure in a SQL Server database
Praveenkp : ok
Praveenkp : fine
: Srini: When you do this, SQL Server stores the assembly in binary content in the "sys.assemblies" table and other related tables.
Praveenkp : is it something like a extentend proc
: Srini: Once the assembly has been registered, you will then need to wrap it around a normal SQL procedure or function. The CREATE statements have a new EXTERNAL NAME clause that lets you do that.
: Srini: Once that happens, when you invoke the procedure and SQL finds out that is CLR based, the CLR runtime is invoked.
: Srini: Well, folks that brings me to the end of the first part of this chat.
: Srini: Over to Gaurav to close this session for me...
: Srini: Thanks a lot for your interest and questions. Its truly amazing to see a lot of interest in SQL Server 2005.
Gaurav_MS : Thanks Srini
Praveenkp : but we have tons and tons of queries ????
Chintan : thanx for the session
Gaurav_MS : that seemed to be an excellent chat
Praveenkp : but not for me
Gaurav_MS : Well, how about a sequel to this chat on Wednesday - do u guys want that ?
Praveenkp : yeah I want it
Chintan : very looking forward to
Vijay : yes ..sure
Gaurav_MS : Just two people ?
Gaurav_MS : Very well - lets work on that...
Praveenkp : who they are ????
Gaurav_MS : Keep visiting http://www.microsoft.com/india/msdn/chat/ for details
Jignesh[dotnetjini] : Yes... Count ME too 2+1 = 3