Top 10 T-SQL Enhancements in SQL Server 2008

As SQL Server 2008 is getting in the advances phases of release, a lot has been published about new features and changes. There have been a few enhancements to Transact SQL that will be a great help for both developers and administrators. Here is list of the top 10 in no particular order, with links to detailed examples. In other words:

SELECT TOP(10) t_sql_enhancements

FROM SQLServer2008

ORDER BY CHECKSUM(NEWID());

• Delighters: http://pratchev.blogspot.com/2008/04/delighters-in-sql-server-2008.html
• Row constructors: http://pratchev.blogspot.com/2008/01/table-value-constructors-in-sql-server.html
• MERGE: http://pratchev.blogspot.com/2008/03/upsert-and-more-with-merge.html
• Composable DML: http://pratchev.blogspot.com/2008/04/composable-dml.html
• Table valued parameters: http://pratchev.blogspot.com/2008/04/table-valued-parameters.html
• Filtered indexes: http://pratchev.blogspot.com/2008/04/filtered-indexes.html
• Sparse columns: http://pratchev.blogspot.com/2008/04/sparse-columns.html
• Hierarchy ID: http://pratchev.blogspot.com/2008/05/hierarchies-in-sql-server-2008.html
• Date and Time data types: http://pratchev.blogspot.com/2008/05/new-date-and-time-data-types.html
• FILESTREAM data type: http://pratchev.blogspot.com/2008/05/filestream-data-type.html

Here are a few more that did not make the list (casualty of random ordering) but are still of importance:

• Spatial data (GEOMETRY and GEOGRAPHY): http://pratchev.blogspot.com/2008/06/spatial-support-in-sql-server.html
• Grouping Sets: http://msdn.microsoft.com/en-us/library/bb522495(SQL.100).aspx
• Table hints: http://msdn.microsoft.com/en-us/library/bb510478(SQL.100).aspx
• Star join query optimizations: http://technet.microsoft.com/en-us/magazine/cc434693(TechNet.10).aspx
• T-SQL debugger: http://blogs.msdn.com/buckwoody/archive/2008/04/25/sql-server-2008-management-improvements-t-sql-debugger.aspx

Having different CellEditor for different rows (Xceed WPF Grid)

Currently I needed to make an Xceed WPF Grid to have different CellEditors for one column for different rows based on a condition (in my case the condition was the content of the cell). I’ve found no info for a similar situation on Xceed’s Forums. I’ve tried using the CellContentTemplateSelector for that column but after some tests I’ve noticed that when user types in the cell for that column the template disappears. And this was unacceptable. After some brainstorming and with a small hint from Xceed Support I was able to make a solution. I’ll present it to you in this article.

This solution utilizes WPF DataTriggers. So if you are not very familiar with them please read the related articles on MSDN

CellEditor.xaml

<xcdg:CellEditor x:Key="CellEditorSelector">
    <xcdg:CellEditor.EditTemplate>
        <DataTemplate>
            <StackPanel Orientation="Vertical">
                <TextBox x:Name="txtTextBox"
                        Visibility="Visible"
                        />
                <ComboBox x:Name="lstCombo"
                        Visibility="Collapsed"
                        />
                <CheckBox x:Name="chkCheckBox"
                        Visibility="Collapsed"
                        />
            </StackPanel>
            <DataTemplate.Triggers>
                <DataTrigger Binding="{Binding RelativeSource={RelativeSource FindAncestor, AncestorType={x:Type xcdg:Cell}}
                                                , Path=Content
                                                , Mode=OneWay}"
                                                Value="txt"
                >
                    <Setter TargetName="txtTextBox" Property="Visibility" Value="Visible"/>
                    <Setter TargetName="lstCombo" Property="Visibility" Value="Collapsed"/>
                    <Setter TargetName="chkCheckBox" Property="Visibility" Value="Collapsed"/>
                </DataTrigger>
                <DataTrigger Binding="{Binding RelativeSource={RelativeSource FindAncestor, AncestorType={x:Type xcdg:Cell}}
                                                , Path=Content
                                                , Mode=OneWay}"
                                                Value="lst"
                >
                    <Setter TargetName="txtTextBox" Property="Visibility" Value="Collapsed"/>
                    <Setter TargetName="lstCombo" Property="Visibility" Value="Visible"/>
                    <Setter TargetName="chkCheckBox" Property="Visibility" Value="Collapsed"/>
                </DataTrigger>
                <DataTrigger Binding="{Binding RelativeSource={RelativeSource FindAncestor, AncestorType={x:Type xcdg:Cell}}
                                                , Path=Content
                                                , Mode=OneWay}"
                                                Value="chk"
                >
                    <Setter TargetName="txtTextBox" Property="Visibility" Value="Collapsed"/>
                    <Setter TargetName="lstCombo" Property="Visibility" Value="Collapsed"/>
                    <Setter TargetName="chkCheckBox" Property="Visibility" Value="Visible"/>
                </DataTrigger>
            </DataTemplate.Triggers>
        </DataTemplate>
    </xcdg:CellEditor.EditTemplate>
</xcdg:CellEditor>

By assigning the above given CellEditor to a column of and Xceed WPF Grid it will change the editor of the cell to a TextBox if the text in the cell is “txt”, to a ComboBox if the text in the cell is “lst” and to a CheckBox if the text in the cell is “chk”. By default the TextBox will be displayed.

This is a very simple example. I’ll leave to your imagination what else you can do with a similar CellEditor. You can have very complex conditions to choose which template to show. All you have to do is encapsulate the logic in a method and bind to the method in the DataTrigger. Binding property. <

Enjoy the Power WPF!

Spatial Support in SQL Server

SQL Server 2008 adds new spatial data types and methods for storing and handling spatial data. The two new data types are GEOMETRY and GEOGRAPHY. This new functionality provides great capabilities to process spatial data.

Here is one brief example to demonstrate the GEOGRAPHY data type and one of the related methods. The code below finds if a geographical location defined by latitude and longitude coordinates is within a geographical region defined by a polygon.

DECLARE @point GEOGRAPHY;

DECLARE @polygon GEOGRAPHY;

 

SET @point = geography::Parse('POINT(49.274138 73.098562)');

SET @polygon = geography::Parse('POLYGON((47.0 90.0, 47.0 73.0, 50.0 52.0, 50.0 54.0, 47.0 90.0))');

 

SELECT @polygon.STIntersects(@point);

Additional resources:

Working with Spatial Data
http://msdn.microsoft.com/en-us/library/bb933876(SQL.100).aspx

Geometry Data Type Method Reference
http://msdn.microsoft.com/en-us/library/bb933973(SQL.100).aspx

Geography Data Type Method Reference
http://msdn.microsoft.com/en-us/library/bb933802(SQL.100).aspx

Working with Spatial Indexes
http://msdn.microsoft.com/en-us/library/bb895265(SQL.100).aspx