Monthly Archives: February 2011

How to Import Shapefile (.shp) Spatial Data into SQL Server 2008

1. Get your spatial data ready. If you’re just starting to work with spatial data and don’t have a handy .shp file, you can visit some of these sites which provide publicly available shapefiles:
http://www.vdstech.com/map_data.htm
http://wwwn.cdc.gov/epiinfo/script/shapefiles.aspx
http://www.mapcruzin.com/download-free-arcgis-shapefiles.htm
http://www.census.gov/geo/www/cob/bdy_files.html

2. Download the SQL Server Spatial Tools from ArcGIS, which include the Shape2SQL tool.

Read more

SQLSaturday#65 SSRS 2008/2008 R2 from the Ground Up

Thank you to all the troopers who attended my really-early-morning-cold-and-snowy session at SQLSaturday#65 in Vancouver, BC. It was such a great event; thanks to all volunteers, sponsors, speakers and organizers (kudos to Scott Stauffer (blog | twitter) .. Thanks to Todd McDermid (blog | twitter) too for helping me give out swags during my session, and thanks for the great powerpoint slide decks which I’ve used in 2 SQLSaturdays now! 🙂

sqlsat65-schedule

As promised, here are the SQLSaturday#65 presentation materials for SQL Server 2008/2008 R2 Reporting Services from the Ground Up

Brent Ozar (blog | twitter) has a few pictures of the event 🙂

Sample Reports (pdfs)

Report with different visualization components (data bar, sparkline, indicator, gauge, chart)
sqlsat65 - sample report - employee sales with visualization

Report with drilldown, barcode, gauge
sqlsat65 - sample report - employee sales with drilldown barcode and gauge

Report using map (from ESRI shapefile for Canada) and Bing Maps layer
sqlsat65 - sample report - canada sales on map
Read more

Comparing Simple Efficiencies: T-SQL UDF vs SQLCLR UDF for Splitting Strings

Originally posted in Idera: http://blog.idera.com/sql-server/performance-and-monitoring/comparing-simple-efficiencies-t-sql-udf-vs-sqclr-udf-for-splitting-strings/

Recap of T-SQL vs SQLCLR (pseudo) Debate

There have been numerous posts about benefits of using T-SQL vs SQLCLR, and vice versa. And we all know the mantra – T-SQL for data access, SQLCLR for anything that is computationally intense tasks.

SQL Server is a relational database, and works best with set based operations and direct data access. If you need to do straightforward INSERTs, UPDATEs, DELETEs, SELECTs, stick with T-SQL unless you want to do lots of overtime trying to figure out why you’re data access suddenly became slow.

So when do we use SQLCLR instead of T-SQL?

There are already lots of discussions – even books – about SQLCLR advantages. I will defer you to them (check out the references section), but I will provide a very brief list of scenarios when you might want to consider SQLCLR:

  • Interaction outside SQL Server If you need to work with the OS, files, registry etc.
  • Validation If you need to validate phone numbers, email addresses, postal codes, or any patterns
  • Complex computations If you need running aggregates, complex math equations (what is the square root of x to the nth power divided by 2 * pi?), financial analytics maybe?
  • Custom Data Types If you need to create your own custom business-specific data types. We also have to remember that XML and GEOGRAPHY/GEOMETRY are great additions to SQL Server, and these are technically CLR data types.

The debate between T-SQL vs SQLCLR sometimes can be taken out of context. I call it a pseudodebate, because sometimes it’s made to seem that SQLCLR is meant to replace T-SQL, when it’s not. SQLCLR is *not* evil. It’s just another tool to help you do your job. It *can* become evil though, if you misuse it.

While there are some overlaps in scenarios where you can use both, these two should really be complementary. Where T-SQL is slow or lacking, SQLCLR should at least be considered and tested. Again, consider the right tool for the right job.

Read more

Be a Community Super Hero for SQLSaturday65 (#sqlsat65)

SQLSaturdays are great free training events, that is really done by the community for the community. SQLSaturday showcases both local and non-local speakers, MVPs, passionate SQL Server professionals who selflessly devote time, and travel, to share to the community. What really makes the SQL Server community awesome is the people. If you dont know, SQLSaturdays speakers don’t get paid to present, they are volunteers. They do it out of *love* (it’s ok to be mushy right, it’s Valentine’s anyway).

I am very excited to be part of SQLSaturday#65 in Vancouver, BC. I will be presenting SQL Server 2008 R2 Reporting Services from the Ground Up. I’ve been busy working on my demos, and being overly excited about it. There’s so much cool stuff to do in SQL Server 2008 Reporting!! (I also have some swags, courtesy of Black Ninja Software)

However, I’ve just read from Tim Costello’s site that currently the group organizing SQLSaturday#65 is projecting a budget deficit of about $1800. (And Tim is a community super hero; way to go Tim!)

So this is my plan:
– buy the lottery and if I win, I will pay for the whole deficit 🙂
– backup, and more realistic plan, become a community super hero.
– encourage you – yes, you, to become a community super hero too. It costs $25. It’s $25 that will go a long way in supporting the community, and showing that you care. Or if you want, you can also be a friend. Or a bronze sponsor. Or silver. You get the drift … Sponsorship plans are here.

And if you attend my session, you will see cool demos, and you might get some swags too; that’ll be worth your $25 🙂

belle's sql musings - logo

Different T-SQL Date Variations for Date Dimensions

Just another handy code snippet for generating dates for date dimensions in your data warehouse.

I am providing just the select statement here, but to generate (massive) date records, just create and set your start and end date variables, and enclose your insert and the code below in your WHILE loop.

Read more