Simons SQL Blog

I've just moved house and connected up my ADSL broadband. To my suprise even though I only moved 200 metres and probably further away from my exchange my speed has almost doubled. I'm now getting

The only thing I can think is that either BE have done something amazing since I moved, the route from the exchange to my old house was very convulted or the wiring in my old house was really poor. I would tend on the latter as it was an old house with some really dodgy phone wires.

If you are using SQL 2008 then you are probably using intellisense. One thing about the way intellisense works is that it loads the metadata it needs from the database. If you change the database then the intellisense is working off the preloaded version of the meta data from the database. This is obviously annoying.

To get round this, if you make any changes to schema etc then you can get the intellisense to reload its metadata by hitting CTRL+SHIFT+R.

I posted yesterday about a generating an HTML table. Overnight I  had a think as I wasn't happy with the UNION ALL bit of the query and realised you could use sub queries instead.

So here is the simplified version. NoteL I've changed the * for text() this has the same affect but doesn't cause CS to do funny things.

You will notice a DATE related theme of the posts today. This one is about the lack of a system function that returns JUST the date and DOESNT include any tiem component.

Due to time constraints they didn't include such a function, seems like a car without a steerwheel to me, but time is time and thats where we are.

You can vote on Erlands suggestion for such a function and also read some comments from MS as to why they didn't include it

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=293333

The biggest gotcha you will find is if you use the new date data type and need to query everything for today.

declare @tab table (col1 date)

insert @tab values (getdate())

select * 
  from @tab where col1 = sysdatetime ()

This won't return anything because rather than reducing the datetype of sysdatetime down to the DATE type, data type precendence states that the DATE column will be elevated to datetime2 (the data type returned by sysdatetime). This seems really odd for me for dates. I would have expected DATE and TIME to have been above the combined data types because the limit of domain of this types is the same but the granularity is different, unlike ints and big ints which have different limits.

So to get round this you have to cast your function call to the DATE type i.e

select * from @tab where col1 = cast(sysdatetime() as date)

This feels wrong. Interestingly if you compare against any other data type you will get an implicit cast to the DATE type because of the precendence.

declare @dv varchar(100) = sysdatetime ()

select * from @tab where col1 = @dv

How many times has your heart sunk because you thought you were connected to the test system when you were in fact connected to the live system and that last TRUNCATE statement really shouldn't be run on the live system.

I've thought of an idea for ssms that is copied directly from office, the idea of read only access by default.

I've been in many situations where sysadmins have been connected to the wrong server and end up doing damage to the wrong server, i.e. deleting data, dropping tables. Whilst they need sysadmin priviledges to do their job most of the time they don't need to have such full priviledges.

My idea is to prompt the user on connecting whether they really need sysadmin priviledges, which most of the time people don't as they are just doing queries.

The connect item is here https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=378050

What would be another step is to only allow approved/signed scripts to be run on a live system, to stop those little adhoc updates where someone forgets the where clause.

The following are a set of scripts to return specific dates relative to the current date.

The trick is to get dates with no time, that's easy in SQL 2008 because you can do cast(sysdatetime() as date)

Annoyingly there is no "System function to return just the date and no time" as i've blogged here so some of the functions use tricks to get just the date.

Jorg's just written a great post on lookup performance in SSIS http://sqlblogcasts.com/blogs/jorg/archive/2008/10/22/ssis-decrease-your-fact-table-loading-time-up-to-40.aspx

I followed his link to his post on different component types in the data flow http://sqlblogcasts.com/blogs/jorg/archive/2008/02/27/SSIS-_1320_-Non_2D00_blocking_2C00_-Semi_2D00_blocking-and-Fully_2D00_blocking-components.aspx and noticed a comment I had made before. On reading the post again I noticed the "phrase reuse of input buffers". The word reuse is a little misleading. By reuse I read that the underlying thing is maintained but the data is repopulated. This isn't quite true in the way I read it. The output buffer of a synchronous component is the SAME buffer with no changes (except those made by the component) as the input buffer, whats more if you have additional columns add by the component, these will be present on the input buffer as well but not populated.

One of the important design decisions made with SSIS was the copying memory was expensive. Thus where possible things aren't copied, a buffer is defined at the start with enough space to store the data needed for the duration of the buffer through the data flow (until it reaches a asynchronous component). So if you have a column defined as holding DT_STR(100) then your buffer will be 100 characters wide, even if you only have 1 character being stored. This is because it is better to pre allocate the space and deal with known fixed lengths than having to deal with variable lengths and deal with copying data if the values get longer (aka page splits in SQL)
This is why its important to get your data types correct with the correct lengths, and also why the csv importer doesn't just declare all columns as really wide. They try to define the columns as small as possible so each row in a buffer is as small as possible.

So how does this affect lookups. While the error output is synchronous with the input to make any use of the outputs you need to need the two outputs. This involves using the UNION transform. and this transform is a blocking asynchronous transform, i.e. it COPIES ALL the data from ALL inputs to another buffer. That means there is a lot of COPYING going on, and as we said earlier copying is very expensive.

If you have 10 lookups and 10 UNION transforms you will end up with the data being copied 10 times thats a lot of extra work.

Using Jorg's preferred solution data is never copied as all the components are synchronous. Therefore only one set of buffers exist, and so there is no extra memory required to store all these copies and none of the CPU required to make the copies.

In SQL 2008 they have enhanced this further by having multiple active buffers in a data flow to allow each component to be processing a buffer. This provides for greater use of multi processor machines.

 [SS 2008-10-26 Corrected definition of error output following Michaels comment]

There used to be a stored proc you could use to create an HTML table from a query. The only time I came across this was on a training course 8 years ago and then I didn't see the point.

With the introduction of XML support in SQL it's eay to create an HTML table, which is good as the stored proc is now deprecated.

Tony's blogged an option http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/10/24/format-query-output-into-an-html-table-the-easy-way.aspx I've extended it further using some little tricks.

The outer query returns the table element and its attributes. The attributes are specified by prefixing the column names with "@".

We then use a nested query to return the header. The column name is "hr" which results in elements called <th>, because we are using a path ('') no element is wrapped around each row. This nested query has a name of tr which means the xml fragment the query returns is enclosed in a "tr" element

To put the rows in the table we have to turn each row into a tr element and each column into a td element. This is a challenge because if you give each column an alias of td you only get one element.

However you can use a trick which is to use a subquery that converts a value into an element. The use of the tells the engine that this value should be a text node of the element. (We could have used this trick on the header row as well. Each row returned by the query is then turned into a tr element using the path notation.

It is cruical to use the "type" directive when using nested XML. If you don't then you will end up with your XML being encoded and included as text. (If you find odd characters in your XML then this could be the cause)

SP1 of the .Net Framework 3.5 brought us a nice improvement in the usability of filestream data in managed code.

Within the System.Data.SqlTypes namespace there is now a class called SqlFilestream. This makes it very easy to use filestream data as this inherits from the Stream class and so can be used to instantite a reader or writer of your choice.

In this code we create a StreamReader using a SqlFileStream.

SqlConnection con = new SqlConnection("<your connection string>");

con.Open();

SqlCommand command = new SqlCommand(

      "select Top(1) fsCol.PathName(), "

    + "GET_FILESTREAM_TRANSACTION_CONTEXT () "

    + "from fsTab (nolock)", con);

command.Transaction = con.BeginTransaction(

   System.Data.IsolationLevel.ReadCommitted);

using (SqlDataReader reader = command.ExecuteReader())

{

    while (reader.Read())

    {

        // Create the SqlFileStream

        StreamReader rs = new StreamReader (new SqlFileStream(reader.GetString(0),

            reader.GetSqlBinary(1).Value,

            FileAccess.Read,

            FileOptions.SequentialScan, 0));

        // Read the contents as bytes and write them to the console

        while (!rs.EndOfStream)

        {

            MessageBox.Show(rs.ReadLine());

        }

        rs.Close();

    }

}

command.Transaction.Commit();

At the SQL 2008 unleashed event I demoed some code that incorporated VE with SQL 2008 to find those people attending SQLBits that where near your route to the conference.

I've been asked to provide the code for that, unfortunately the code is to entangled with the SQLbits site and so I've decided to break it into small chunks that you can then put together.

The first of these is some code that converts VERoutes into a sql geography type. This is needed if you want to to any spatial searching based on routes.

Its really neat that VE provides routing, one downside is that the standard VE service only provides waypoints and not the precise route. For that reason if you travel from London to Leeds along the M1 you will only get the entry and exit junctions of the M1. If you join you points together it will look as though you travelled across country and not along the M1. The commercial and developer licensed versions supposedly give the full route. However I haven't been able to get this working.

Anyway, To get a route you need to call the GetDirections method.

In this example I have two layers each with a point on that marks the venue (destination) and the starting location. Note : Use of layers is very useful to control displaying and loading points. However you have to set the "SendLayersToServer" property of the map control to true.

        RouteOptions ro = new RouteOptions ();

        ro.DistanceUnit = RouteDistanceUnit.Mile ;

        ro.DrawRoute = true;

        ro.SetBestMapView = true;

        ro.RouteOptimize = RouteOptimize.MinimizeTime ;

        //Get the location of the venue (the destination)

        var Venue = Map1.GetShapeLayerByIndex(LAYER_BASE).Shapes[0];

        //Only proceed if the user has said where to start from

        if (Map1.GetShapeLayerByIndex(LAYER_LOCATION).GetShapeCount() > 0)

        {

            //Get the location of the start point

            Shape House = Map1.GetShapeLayerByIndex(LAYER_LOCATION).Shapes[0];

            //Ask the control to get the directions.

            //This is an async call, the result is returned in the callback method

            Map1.GetDirections(new List<LatLongWithAltitude>() { Venue.Points[0], House.Points[0] }, ro);

        }

I got this far and was then stumped as to how I got hold of the route that was planned, I thought it was a special layer. After digging through the client side script and loking at the eventhandlers defined for the control I realised the route is past pack using the ServerGetDirections postback event. Whats odd is that it takes a MapRouteEventhandler, it seems the changed from Route to Directions but not everywhere.

Once you've defined your evenhandler you need to do something with the route that is passed back. In this example code I'm building a SqlGeography instance using the new SqlGeographyBuilder api. Its a very easy interface to use even if there are a few gotchas. The main gotcha is the first point has to be defined with the BeginFigure method (from what I could work out), after that you can call AddLine.

The key hear is looping through the different collections that comprise the Route object. The final step is to do something with the instance you've built. In this case I stored the shape as text in a field on the page. I'm sure this isn't great as this could get quite large. I haven't found a way of getting at the Route after this event has fired so you have to do whatever you want with the route in this event, otherwise your stuck.

void Map1_ServerGetDirections(object sender, Microsoft.Live.ServerControls.VE.Route e)

    {

        MapID.Text = "Route found";

        //create a SQLGeography from the route generated

        SqlGeography G = new SqlGeography();

        SqlGeographyBuilder gb = new SqlGeographyBuilder();

        gb.SetSrid(4326);

        gb.BeginGeography(OpenGisGeographyType.LineString);

        bool first = true;

        foreach (var r in e.RouteLegs)

        {

            foreach (var i in r.Itinerary.Items)

            {

                //In the commercial interface you get the full shape of the route

                //otherwise you just get the co-ordinates of the ends of each leg (i.e. junctions)

                if (i.Shape != null)

                {

                    //Look through points in the shape

                    foreach (var p in i.Shape.Points)

                    {

                        if (first)

                        {

                            //First points so start the Geography figure

                            gb.BeginFigure(p.Latitude, p.Longitude);

                            first = false;

                        }

                        else

                        {

                            //Add a line from the last point to this point

                            gb.AddLine(p.Latitude, p.Longitude);

                        }

                    }

                }

                #region OnlyPointsNoShapes

                else

                {

                    if (first)

                    {

                        gb.BeginFigure(i.LatLong.Latitude, i.LatLong.Longitude);

                        first = false;

                    }

                    else

                        gb.AddLine(i.LatLong.Latitude, i.LatLong.Longitude);

                }

                #endregion

            }

        }

        gb.EndFigure();

        gb.EndGeography();

I hope that is useful, its using the VE server control rather than the javascript version.

I will post more in the future

If you want a developer license then you can signup here

https://mappoint-css.live.com/MwsSignUp/Default.aspx

Once signed up you need to enable the app to get a token and use that token in requests. Details on how to get the token per session are here:

http://msdn.microsoft.com/en-us/library/bb924353.aspx

Johannes has a great example here:

http://johanneskebeck.spaces.live.com/blog/cns!42E1F70205EC8A96!4504.entry