In part 1 of this series, we tried a simple example from the Books Online without any SXI index at all, and an SXI with all the paths covered. No XEvents were emitted in either case. Now let’s change the XSI around (by dropping and recreating, although we could use ALTER, so envision a drop […]
Bob Beauchemin
SQL Server Blog
Choosing Paths for Selective XML Index – Part 2 – Using the SXI XEvents
Posted on: January 7, 2013 1:07 pm
Choosing Paths for Selective XML Index – Part 1
Posted on: January 7, 2013 11:12 am
About a month ago, I wrote a series of blog entries on the Selective XML Index introduced in SQL Server 2008 SP1. Just to summarize before I start up again, the Selective XML Index (I’ve abbreviate as SXI) is a feature introduced to allow indexing of only certain paths in an XML column. It uses […]
New XEvent events in SQL Server 2012 SP1
Posted on: January 7, 2013 9:34 am
I’ve been looking a bit more into the SQL Server 2012 SP1 release, mostly to find diagnostics related to the Selective XML Index (SXI). I found some additional extended events for SXI (there are four of them in the “index” category) and I’ll cover them in, as well as exercise them, the next posting. But […]
How To Return Document Property Values in Full-Text Search
Posted on: December 12, 2012 10:30 am
About a week ago I got involved with a question via Twitter that was posted originally to Stack Exchange. The question was "Is it possible to list document properties via FTS"? The question refers to SQL Server 2012's new support or property-based search with a syntax that looks like this: SELECT name DocumentName, file_stream.GetFileNamespacePath() Path […]
Reacting to XEvents in almost real-time
Posted on: November 29, 2012 2:48 pm
I've heard it said that the main difference between SQL Server Extended Events and SQL Server Event Notifications is that you can't "react to" Extended Events. Event Notifications are written to the service broker service of your choice, and multiple Event Notifications can even be routed to the same service, even on different machines. But […]
Loading spatial data and cleansing-converting with SQL Server 2012
Posted on: November 29, 2012 2:27 pm
Someone asked, on Twitter, if it were possible to get a set of spatial data that included cities, counties (administrative regions) and countries in database table format for SQL Server spatial. I'd come across just these datasets (and more) as shapefiles at the CDC website and pointed them out. Then came the question about how […]
Selective XML Index – Why is this compelling?
Posted on: September 22, 2012 5:42 pm
OK. VM with the SP1 CTP is shut down. Fun's over (until I think of something new and cool or you think of something and tell me about it). Time to answer Aaron's implied question about "what makes this so special it should be shipped in a Service Pack, no less?". This could be a […]
Selective XML Indexes – Learning the rules through error messages
Posted on: September 22, 2012 4:40 pm
Experimenting without docs is a good way to get lots of error messages along the way. I'm learning by attrition, and it seems like I'd be better off knowing the rules. It's not the best way to learn anything, but you can at least learn the limits through the error messages. I was tired of […]
Selective XML Index – Secondary Selective XML Indices
Posted on: September 22, 2012 4:12 pm
I've only been talking so far about the "primary" Selective XML Index. But you can also create 0-n "secondary" Selective XML Indexes. The syntax looks a little bit like secondary "non-selective" XML Indexes in that you use the "USING [related XML index]". You specify one (and only one) pathspec. Additionally, the XML value that the […]
Selective XML Index – Implementation Details
Posted on: September 22, 2012 3:02 pm
A while after I posted part 1, there was a twitter message from Aaron (@AaronBertrand), a well-known "smart person" in the SQL Server space, about using feature packs as "ship vehicles", especially when they (possibly) have the effect of requiring keeping all instances in-sync at the service pack level to keep things working and possibly even making SP […]
Selective XML Indexes in SQL Server – First Try…it works
Posted on: September 22, 2012 12:15 pm
OK, so let's try something. Load a bunch of documents. Although, actually this part works with an empty table. create selective xml index fooidx2 on foo(thexml) for ( pathname1 = 'foo/bar/baz2' ); Msg 6379, Level 16, State 201, Line 1 selective XML index 'fooidx' already exists on column 'thexml' in table 'foo'. Multiple selective XML […]
Getting started with Selective XML Indexes in SQL Server
Posted on: September 22, 2012 11:59 am
So, it was announced that SQL2012 SP1 CTP4 was released yesterday. You've likely already heard this (more than once, everyone seems to revel in repeating announcements from the team). And it contains a new feature (I was surprised, thought that wasn't supposed to happen, new features in service packs, meanwhile…) called Selective XML Indexes. Interesting concept, […]
Using filetable with full-text? Lose the redundant index
Posted on: July 31, 2012 9:47 am
I'm not sure how many full-text search over filetable demos I've seen (including some I've written that I was just perusing), that do something like this. CREATE TABLE foo AS FILETABLE WITH (FILETABLE_DIRECTORY = N'ftdir'); CREATE UNIQUE INDEX fooidx ON foo(stream_id); CREATE FULLTEXT INDEX ON foo (file_stream TYPE COLUMN file_type) KEY INDEX fooidx; After looking […]
Saving the contents of a SQL Server XML column to a file
Posted on: July 12, 2012 11:41 am
While preparing for my TechEd talk on filetables and full-text search, I came across this tidbit that I thought was interesting. With the advent of XML support in databases, specifically SQL Server's XML data type in SQL Server 2005, there has been two main ways to store XML; in files on the file system, and […]
Loading ShapeFiles into SQL Server 2008 and 2012
Posted on: June 21, 2012 1:50 pm
Since I find myself "in a spatial mood", I thought I'd write one more today. About importing data from shapefiles. According to Wikipedia "The Esri shapefile or simply a shapefile is a popular geospatial vector data format for geographic information systems software". And the question always goes something like this: "Does SQL Server have anything […]
I’ve got the last talk at TechEd and its almost LAST CALL
Posted on: June 14, 2012 8:15 am
I've been at TechEd North America in Orlando this week and have seen some amazing new things. Upcoming operating systems, new hardware, and a detailed technical followup on the Azure (cloud) announcements from last week, including some Windows Azure SQL Database (was SQL Azure) upcoming enhancements. I've spend quite a bit of time at the […]
DACFx 3.0 – Import and Export with BACPACs
Posted on: May 22, 2012 10:12 am
The last piece of the DAC puzzle (at least for now) is import and export. Export makes a non-transactionally consistant copy of database content that uses the BACPAC format (schema and data) and Import creates a new database with the BACPAC schema and data. Currently, its the most-used method of backing up and restoring a […]
DACFx 3.0: The new file formats
Posted on: May 17, 2012 11:58 am
In the last posting, I talked about the differences between DACFx 3.0 and previous versions from an API point of view. This time, I'll look at the files in a DACPAC and see how those differ. DACPAC really stores a database model, that can be used to recreate the database and server (3.0 only) objects in the […]
DACFx 3.0: The new programming API
Posted on: May 16, 2012 12:10 pm
Looking at the API and at the serialized form (i.e. the DACPAC), it turns out that DACFx 3.0 is not just "DAC V-next". It's an entire quantum change from all other versions, including DAC 2.0, the version that introduced the BACPAC (serialized schema and data). This has some interesting repercussions with compatibility. Let's start with the […]
Scripting SQL Server databases sans SMO Scripter
Posted on: May 10, 2012 11:38 am
I can script out a database schema (and optionally data) from SQL Server databases. Any version from 2005 and beyond, as well as SQL Azure Database. And most all instance level objects. And I'm not the SMO scripter object. Or a DBA. Who am I? I would be DACFx 3.0. And I can do some things (unlike […]
The Rest of the Story: Co-existing VS2010 and SSDT database projects
Posted on: April 17, 2012 3:00 pm
After my last adventure, I thought it would be interesting to try the "upgrade path" with SSDT and older "Database Projects" (i.e. Visual Studio for Database Professionals, or whatever the last name was before we went to the nice SSDT acronym, I always called them "Data Dude" projects after Gert Drapers, the original Data Dude). […]
Adventures in autodeploy: SQL Server 2012, SSDT, and SQLCLR
Posted on: April 17, 2012 2:06 pm
I'm the kind of person that doesn't often/always use Visual Studio "autodeploy" (that is, the "Deploy Solution" option) for SQLCLR projects. It's always been missing things (like ALTER ASSEMBLY) and never quite kept up with the newer things you could do with SQLCLR (e.g. ordered table-valued functions, multiparameter aggregates). But every once-in-a-while, especially while I'm […]
Security Best Practices Whitepaper for SQL Server 2012 is Live.
Posted on: March 9, 2012 9:52 am
I recently completed another update to the whitepaper, now entitled "SQL Server 2012 Security Best Practices – Operational and Administrative Tasks" to cover best usage of the security features in SQL Server 2012. And, concurrently with the launch of SQL Server 2012, it was available on the web a few days ago. Many thanks to the […]
SQL Server 2012 at DevWeek London at the end of March
Posted on: February 22, 2012 9:23 pm
Hope that you'll find time in your schedule to attend DevWeek 2012 in London, March 26-30. I'll be doing a preconference talk, an entire day of SQL Server 2012 for Developers. In addition, I present some breakouts on SQL Server security best practices, Extended Events and the new profiler, Mission-critical SQLCLR and other relevent topics. Here's […]
New T-SQL 2012 scalar functions (all but 1) work in SQL Azure
Posted on: February 11, 2012 3:44 pm
Back when I was saw SQL Azure was producing an @@version string that mentioned version 11 (that's SQL Server 2012's major version number), I'd started looking at the version number every week or so. And looking for SQL Server 2012 functionality in SQL Azure. See the blog post "SQL Denali T-SQL features in SQL Azure […]
XEvents in SQL Server 2012: No more “lazy XML” in event harvesting scripts
Posted on: January 23, 2012 12:49 pm
I've said before how thrilled I am will the new extended event UI in SSMS for SQL Server 2012. However, you might be one of the early adopters who made up their own scripts to define extended event sessions, and use hardcoded scripts to harvest the results. So, you may run into this problem if […]
SQL Server 2012 FileTable and T-SQL on AlwaysOn secondary replicas
Posted on: January 17, 2012 12:01 pm
OK, back to PathName() with AlwaysOn, which I started on in the previous blog post. PathName() in SQL Server 2012 doesn't return the computer name by default ("AlwaysOn1" or "AlwaysOn2" in my example) but returns the VNN name (virtual network name). That is, it returns the availability group "listener share" name. In my case, the […]
SQL Server 2012 FileTables – AlwaysOn support and PathName()
Posted on: January 17, 2012 10:03 am
When I was covering the FileTable-specific functions and methods, I didn't mention PathName(), a filestream-related function, that (naturally) can be used with FileTables. This posting is about PathName(), but, mostly about what happens with FileTable in an AlwaysOn availability group configuration. There were some surprises there. None of the FileTable-specific functions and methods mention AlwaysOn, […]
SQL Server 2012 FileTables in T-SQL part 3: hierarchyid methods
Posted on: January 16, 2012 1:06 pm
So finally, we get to using FileTable's path_locator column with the methods of hierarchyid. To pick up where we left off, remember that hierarchyid has a method named GetLevel(). Given the following directory structure in the FileTable's share: File1.txt File2.txt SQLFiles SubdirFile1.txt SubdirFile2.txt I have a few more levels of subdirectory here, but […]
SQL Server 2012 FileTables in T-SQL part 2: new rows
Posted on: January 16, 2012 8:00 am
So the functions/methods that I wrote about in previous post are needed because FileTables don't store the UNC path name of the file, they store the path_locator as a hierarchyid data type. Wonder what encoding scheme they're using. Let's see, by doing SELECT path_locator.ToString(), Name FROM dbo.Documents We get hierarchyid strings that look like this: […]
SQL Server 2012 FileTables in T-SQL part 1: functions and methods
Posted on: January 15, 2012 10:17 pm
I've been working with the SQL Server 2012 FileTable feature lately. Besides learning to appreciate the esoteric features of the NTFS file system and SMB protocol, only some of which are supported by FileTables, I've been trying to work with FileTables in SQL Server using T-SQL. This turns out to be an interesting exercise, especially […]
Auditing database-level objects in SQL Express 2012
Posted on: December 1, 2011 9:43 pm
… Continued from previous blog entry … The point of using a Server Audit Specification with database events in SQL Server 2012 is this. In SQL Server 2008, audit is an Enterprise-only feature. Only available in Enterprise, Evaluation, and Developer Edition. In SQL Server 2012, one of the new features (to quote BOL) is: "Support […]
Using filtering and server audit specs to audit DB objects in SQL Server 2012
Posted on: December 1, 2011 9:34 pm
I've always been pretty "standard" in my approach to SQL Server's auditing feature. That is, Server Audit Specifications are for auditing server-level objects and Database Audit Specifications are for auditing database-level objects. There have always been a few "Audit Action Groups" that pertain to database objects that could be specified in Server Audit Specifications. An […]
Local Windows Groups for Service Accounts (almost) gone in SQL Server 2012
Posted on: December 1, 2011 2:47 pm
When I installed CTP3 of SQL Server 2012 (on Windows Server 2008 R2 OS), I noticed that the "Service SID account" (known as the Managed Service Account) was directly available in the setup dropdown box, selected it, wrote a blog entry mentioning it, and went on. Lately, I've been looking at the local Windows groups (or […]
SQLCLR .NET 4-based behavior differences in SQL Server 2012
Posted on: November 27, 2011 6:52 pm
As I mentioned in a few previous blog entries, SQL Server 2012 runs the .NET 4 version inside of SQL Server. Your SQLCLR code may be affected by some behavior differences between .NET versions 2.0/3.5/3.5 SP1 and .NET 4.0. I like the way SQL Server chose to deal with these differences. Although the documentation on these differences […]
Per-appdomain diagnostic info in SQL Server 2012 SQLCLR
Posted on: November 27, 2011 6:45 pm
In SQL Server 2012, SQLCLR now loads .NET 4.0 code rather than .NET 2.0 as in previous versions of SQL Server. Note that the "main system" assemblies (e.g. mscorlib.dll, System.Data.dll) in .NET did not appear as a separate directory, between version 2.0 and 4.0; instead, the .NET 2.0 librares were updated. As an example, SQL […]
Native ODBC components in SQL Server 2012 SSIS
Posted on: November 26, 2011 4:58 pm
Just had the occasion to look at SSIS Sources and Destinations in SQL Server 2012 RC0. After reading about the stated direction to use ODBC for SQL Server/relational data access over OLE DB and writing up my own blog post about it, I wondered how long it would be before a native ODBC source and destination appeared […]
Using SQL Server 2012 tools with older database versions
Posted on: November 21, 2011 2:44 pm
Last week, while teaching a class based around SQL Server 2008 R2, I happened to "brag up" the new tools in SQL Server 2012, mainly the Extended Event graphic user interface in SSMS and the ability of Database Tuning Advisor to tune from the plan cache. This, of course, immediately brought up the question: "Do […]
SQL Server 2012 client features – ADO.NET 4.02 released
Posted on: November 3, 2011 2:09 pm
This post is about the changes in the client stack for SQL Server 2012. It's a very short post. The last two major releases of SQL Server (2005 and 2008) corresponded to .NET 2.0 and .NET 3.5 SP1 respectively. In addition, each one corresponded to a new version of SNAC (SQL Native Client, that is, […]
Spatial index compression in SQL Server Denali
Posted on: September 30, 2011 2:11 pm
Since SQL Server Denali adds support for index compression of spatial indexes, using the same options as data and index compression in SQL Server 2008 and above. As opposed to what I expected at first (because spatial indexes consist of only a few small fixed-length columns), using compression with spatial indexes appears to be quite effective. With […]
LOTS of New Goodies in SQL Server Denali Query Plans
Posted on: September 27, 2011 10:48 pm
After the last post on new warnings in SQL Server Denali Showplan, just for the fun of it, I had a look at the XML version of the showplan. And saw some fairly amazing new things I hadn't noticed before. Everyone's been looking into the new "Batch Execution Mode" with the new ColumnStore index feature. […]
Additional Warnings in SQL Server Denali Query Plans
Posted on: September 27, 2011 9:31 pm
Sort and hash warnings can be found using SQL Server profiler, but they don't appear in query plans like "Columns With No Statstics" warnings in SQL Server 2008 R2 and below. In SQL Server Denali, they DO appear in query plans. No profiler needed. I'm not sure if all the pertainant events from the trace […]
SQL Denali T-SQL features in SQL Azure now
Posted on: September 22, 2011 2:46 pm
Earlier this month I noticed that SQL Azure, with the latest upgrade has a version of 11…something (actually 11.0.1467.26, to be exact). And I wondered, aside from the long-touted new spatial library, if there were any Denali T-SQL enhancements that were now available. That is, what version of "version 11" is this? I did do […]
October’s Developer Immersion Event adds Denali topics
Posted on: September 16, 2011 10:26 am
With the release of SQL Server Denali approaching, (some Denali features are already available in SQL Azure Database), I've decided its time to sift a few Denali features into my 5-day SQLskills Developer Skills Immersion Event on the week of October 24 in Chicago. This will be the first time I've presented with the expanded […]
Contents and purpose of SemanticsDB in Denali
Posted on: September 11, 2011 7:07 pm
The new Semantic Search feature of SQL Server Denali enhances full-text search by letting you do a search on the meaning of documents. To use this feature, its required that you install the SemanticsDB. The SemanticsDB ships with SQL Server or can be downloaded as part of the SQL Server Denali CTP3 Feature Pack. One […]
Full-text query plans and stats in Denali
Posted on: September 10, 2011 1:26 pm
I've been looking at full-text query plans in Denali lately, and looking at the SQL query plan for the full-text part, there isn't much to see (modulo docid lookup or lack of it, see a couple of posts ago). Everything seemed to be hidden behind the TableValuedFunction iterator for FulltextMatch function, and although there's the usual […]
What’s this semantic search about anyhow?
Posted on: September 6, 2011 5:19 pm
I've been working lately with SQL Server Denali's Semantic Search feature. There's a more complete description of this feature (as well as how to set it up, its an addition to full-text indexing) in BOL, but I just wanted to summarize my one sentence "raison d'etre" for it on a function by function basis. And […]
Of filetables, full-text and semantic search, and docidmaps
Posted on: September 6, 2011 12:45 pm
SQL Server Denali's new Filetable feature and full-text search are made for each other. Set up database for non-transactional filestream access with a directory name, create a table "AS FILETABLE" with a subdirectory name, drag over a bunch of files and turn on full-text search. Voila, instant search with two predicates and two TVFs. Even […]
SQL Azure “version 11” arrives on my server
Posted on: September 6, 2011 12:44 am
I've been trying this out every day or so since I'd heard about the update (originally named the July 2011 Service Release), but I'd forgotten about it for about a week. So I don't exactly know when this happened on my SQL Azure server. But tonight, around midnight, SELECT @@version returns: Microsoft SQL Azure (RTM) […]
Denali memory allocator changes and SQLCLR
Posted on: August 19, 2011 11:58 am
Once upon a time (well, now, currently), there were two memory allocators in SQL Server, the single-page allocator and the multi-page allocator. The single-page allocator was used for almost everything (data buffers, caches, etc); the multi-page allocator was used for somewhat more esoteric things. Like most of SQLCLR (that's CLR code that runs inside SQL […]
Correlating client and server-side trace in SQL Server Denali
Posted on: August 19, 2011 11:28 am
I'm not usually one for repeating stuff you can find in BOL. Usually, a waste of your time and mine. But this feature, for SQL Server Denali, is pretty well buried; well at least from me, and I knew what I was looking for. I've always been interested in (well, wrote a whitepaper on) using […]
Window clause, range specification, and missing values
Posted on: August 18, 2011 3:12 pm
In the previous post, I insinuated that allowing LAG/LEAD to be sensitive to value RANGEs might help with series of data with missing values. It won't do that unless there's some logic that allows the offset that LAG/LEAD uses to be calculated on a row-by-row basis (i.e. LAG/LEAD uses an expression based on the window […]
Remember: LAG function in SQL Server Denali uses rows
Posted on: August 17, 2011 4:52 pm
You've heard my rant before "measure what you think you are measuring". If not, follow the link. Here's an example using the LAG function, new in SQL Server Denali, to measure sales trends. We'll start with a view from AdventureWorksDW2008R2 (the double cast is for clarity). CREATE VIEW dbo.EmployeeSalesByMonth AS SELECT EmployeeKey as [Employee], […]
Thanks to the Portland SQL Server User Group
Posted on: August 1, 2011 3:41 pm
A (couple of days late) thanks to all the folks that attended my "What's New in Denali" demo fest at Portland SQL Server User Group last Thursday. I hope the code-based explanation of the "zen" of Columnstore Index, Semantic Search/Fulltext Enhancements/FileTable, and Extended Event Graphic Session support was clear. Modulo the heat in the room […]
Denali window clause followup question
Posted on: August 1, 2011 3:04 pm
So, quickly on the heels of the first window clause and last_value() question, came a followup: OK smartie, why does last_value work fine here? I didn't have to change from the default window. What gives? select SalesPersonID, SalesOrderID, first_value(SalesOrderID) over(partition by SalesPersonID order by SalesPersonID) as FirstOrderForSalesPerson, last_value(SalesOrderID) over(partition by SalesPersonID order by SalesPersonID) as […]
An FAQ about the window clause in SQL Server Denali
Posted on: August 1, 2011 1:47 pm
I've been working with the new Denali T-SQL windowing functionality and ran into someone who asked about this "problem". It's almost sure to become an FAQ. Why does last_value not always "work right"? Take, as an example, the following query (against Adventureworks2008R2) select AccountNumber, SalesPersonID, SalesOrderID, first_value(SalesOrderID) over(partition by AccountNumber order by SalesOrderID) as FirstOrderForAcct, […]
Hold it right there, you’re a user-defined aggregate
Posted on: July 27, 2011 6:43 pm
I've been trying out the windowing extensions (the OVER clause extensions and friends) with different kinds of functions (aggregate functions, first_value, last_value() etc), and thought I'd use a SQLCLR aggregate. After all, they're just "regular" aggregates, right? You can't build a user-defined ranking aggregate in SQLCLR, so they can't be confused with one of those. […]
What exactly does PERCENTILE_CONT do, anyhow?
Posted on: July 14, 2011 3:57 pm
I'm always leery when I hear people say "statistics show that…" followed by whatever their opinion is. Scientists do it. And your users probably do it too. I worked with a product called SAS once, on statistics for response time. Got some lovely reports and statistics, *from which other folks draw conclusions*. It's important to step […]
Install me next… Denali CTP3 Books Online
Posted on: July 12, 2011 9:47 am
After downloading CTP3, I immediately (well, almost immediately) went to set the options to view Books Online from local storage. You'all do know how to do that, Help and Community off the start menu, Manage Help Settings. But downloading BOL gets CTP1 also. Online BOL is still CTP1, last time I tried. Bummer. So I […]
Interesting observations on a late-night CTP3 install
Posted on: July 12, 2011 8:48 am
So I was awake late last night and, lo and behold, there was a web link for Denali CTP3 posted on Twitter. Downloaded and installed it. Brought it up and looked for a few new items (features) announced at SQLPASS last year and TechEd US 2011 this year that should be there. They were. Maybe […]
Speaking at Portland SQL Server User Group this month
Posted on: July 8, 2011 12:46 pm
Just to let you know… I'll be speaking at the Portland, OR, SQL Server User Group meeting this month on July 28. The topic will be "What's New in SQL Server Denali". There's a lot of new features (and more coming soon), so I'll try not to just list off the name of each of […]
A First Look at Data-Tier Applications 2.0
Posted on: July 6, 2011 1:44 pm
The next version of Data-Tier Applications is version 2.0. You can get a CTP of it today; where the CTP is located really gives away the game. It's located at SQL Azure Labs, listed under SQL Azure "Import/Export". The writeup on this page identifies a few interesting things: 1. It's a preview of the Denali […]
Visualizing the new Denali curve spatial types
Posted on: February 7, 2011 2:37 pm
I'd been fretting over the lack of a true visualizer for the new circularstring and compoundcurve spatial types in SQL Server Denali. I'd installed SSMS from SQL Server 2008 R2, and although it works just fine for Denali spatial types (even though the binary format has changed since 2008/R2), trying to use it with either […]
Using the Denali spatial aggregates on the client
Posted on: January 25, 2011 4:52 am
One of the interesting spatial features in SQL Server Denali is the inclusion of spatial aggregates, namely CollectionAggregate, ConvexHullAggregate, EnvelopeAggregate, and UnionAggregate. Here's an example of unioning two squares together to make a rectangle. create table t1 (g geometry) insert t1 values('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))') insert t1 values('POLYGON((1 0, […]
SQL Server Denali Fulltext Properties – Where?
Posted on: January 24, 2011 4:57 am
When I originally wrote the SQL Server Denali full-text search on properties cheap demo, I used property sets with properties (GUIDs) that I got from the SQL Server BOL example (although the example itself didn't work). As it says in BOL, CREATE SEARCH PROPERTY LIST creates an empty property list. Which got me to wondering, where […]
Yet another semicolon rule in SQL Server Denali
Posted on: January 23, 2011 9:51 am
I was telling students this week about the story of the semicolon and SQL Server. And, when going over some Denali demos, it came up again. SQL Server does not mandate semicolons as end-of-statement indicators as some databases do (as well as the ANSI standard). You can use a semicolon (or as many semicolons as […]
Speaking at the NYC SQL Server user group next Thursday
Posted on: January 19, 2011 6:19 pm
I'll be speaking at the New York City SQL Server user group next Thursday (Jan 27) on "What's New in the SQL Server Denali Database Engine". I'll go through almost all of the new features with demos on each one, including contained databases, additional extended event and PowerShell support, T-SQL enhancements, spatial data, and full-text […]
What’s new in Denali CTP1. And what isn’t in CTP1.
Posted on: January 5, 2011 11:38 am
At SQLPASS a couple of months ago, Microsoft announced SQL Server Denali and, at during the conference, handed out CTP1. Nice. But, the conference keynotes and sessions on it alluded to many more of the upcoming Denali features than appear on the CTP1. That's all well and good, but I've heard tell of one person who […]
The nearest neighbor optimization in SQL Server Denali
Posted on: January 3, 2011 2:24 pm
The nearest neighbor query is one of the most common queries against spatial data. How many people haven't gone to a mapping app, typed in their current location and asked for the 10 nearest [your favorite thing goes here]? The obvious way to phrase the spatial query for this, given an STDistance method on the […]
The “other fullglobe feature” in SQL Server Denali
Posted on: December 29, 2010 2:38 pm
Now about that "other" feature that could be called fullglobe in Denali that I mentioned. I don't know which feature will retain the moniker or whether they both will…that's why I'm not in marketing. Here's the deal… the SQL Server spatial geography type is limited to a single hemisphere in size in SQL Server 2008 […]
New curve types in SQL Server Denali are now in OGC spec
Posted on: December 29, 2010 1:14 pm
Once upon a time, I wrote a blog entry about CircularString, CompoundCurve, and CurvePolygon (the new geometric types supported in Denali). And mentioned that they came from ISO-ANSI SQL/MM and weren't in the OGC spec. Well, lo and behold, while browsing the latest OGC Simple Features – Part 1 spec, the last incantation (version 1.2.1) […]
Measuring the earth with SQL Server Denali
Posted on: December 29, 2010 10:29 am
So what's the area of the earth? Well…depends on who's measurement you want to use. There an interesting SQL Server Denali spatial feature known as fullglobe. Actually there's two features with that name, more later; I was using the FULLGLOBE geometric type (geometric type is the term used by OGC, this one only works with SQL […]
SQL Server Denali – the new autogrid spatial index
Posted on: December 23, 2010 5:17 pm
I've been trying out the new Denali "AUTO_GRID" spatial index (that is …USING GEOGRAPHY_AUTO_GRID or GEOMETRY_AUTO_GRID in the CREATE SPATIAL INDEX DDL) on some of my old spatial performance use cases. It's worth repeating (from Ed's excellent whitepaper) that the AUTOGRID index uses 8 levels of tessellation instead of the 4 levels that the original […]
SQL Server Denali – FTS on properties, the cheap demo
Posted on: December 22, 2010 12:07 pm
So here's a cheap demo of FTS property search in Denali. For the motivation for this feature, see the previous blog entry. I'm a minimalist (minimalism is a teaching aid), so its just the basics. In some places, I may be doing more than is minimally required for clarity. First off, I didn't use the […]
SQL Server Denali – FTS on properties, the motivation
Posted on: December 22, 2010 12:00 pm
Years after its inception, I think I've figured out what WinFS was supposed to do. It sometime takes me a while. I got a new mp3 player (I don't have appliances at my house with cute monikers that start with the letter "i") and wondered why it sometime played songs "out of order". I like […]
TechEd 2010 Europe; Denali at TechEd
Posted on: November 13, 2010 3:53 pm
TechEd Europe 2010 ended yesterday. I thought I'd write about it when I got home, but now I'm unsure if that will happen in a timely manner. I'm waiting at the fourth different gate in Dulles for my flight home (last of three) to Portland. It's just been delayed by 3 hours, and I hope […]
There are some new PowerShell Cmdlets in Denali CTP1
Posted on: November 11, 2010 1:17 am
Quick correction to my PowerShell Denali CTP1 post here. Max Trinidad did the obvious get-command call and found some new PowerShell cmdlets related to the new HADR feature. Knew I should have tried that. 😉
New spatial query hint when using indexing
Posted on: November 9, 2010 4:01 pm
OK, last one for the evening, its getting late in Berlin. And BTW, we might have some Denali goodness here at TechEd. Search for "futures", perhaps. There a new hint for spatial queries in Denali CTP1, namely, SPATIAL_WINDOW_MAX_CELLS. This is a table hint that, as far as I can tell, this hint affects the query […]
My favorite feature in Denali. Well one of…
Posted on: November 9, 2010 3:09 pm
Only because I've been waiting for it since I first saw try-catch back in 2005 beta 2 or something. In dirt-simple code: begin try delete jobs end try begin catch throw end catch Nuff said. Error 547 is sooooo much more backward compatible than error 50000. Realize that, if throw is preceeded by another statement (begin […]
Using SQL Server Denali Full-Text Search With Extended Properties
Posted on: November 9, 2010 3:05 pm
Once upon a time, there was to be a SQL Server-based file system for Windows. This file system would not only let you search on file properties like filename and file length, but also on what's known as "Extended Properties". For an idea of what extended properties refers to, have a look at the "Properties" […]
Repost/Revision Updated serialization formats for SQL Server spatial types
Posted on: November 9, 2010 2:49 pm
SQL Server Denali CTP1 was released today. I don’t want to be the fiftieth person to post the link to the download; reference Ed's blog posting for the location. This is a repost (with some corrections now that the CTP is actually out) of a blog posting I did when someone discovered a web location […]
SQL Server Denali CTP1 – PowerShell and XEvents
Posted on: November 9, 2010 1:47 pm
Just having a look at PowerShell support in SQL Server Denali CTP1. In Denali you must install PowerShell V2 or it will be installed as part of the installation process. Although the minishell architecture of SQLPS is unchanged so far. Rumor is that changing this (maybe into a module, like Chad Miller did?) might in […]
