Free SQL Server Goodies

Michael K. Campbell has shared some useful SQL Server goodies that are available online, in his article in DevProConnections. I picked the ones I liked -

* Scripts - TechNet's Script Repository has a vast repository of scripts provided by Microsoft and by its users/customers that you can adapt for your own requirements.

* Books - Red Gate Software which sells some great developer tools also publishes books. Some of them are available for free download in PDF format -

• SQL Server Stumpers Vol.5 


• Inside the SQL Server Query Optimizer 


• SQL Server Execution Plans 


• Mastering SQL Server Profiler


• SQL Server Statistics 


• The Red Gate Guide to SQL Server Team-based Development 


• Defensive Database Programming


• Confessions of an IT Manager


• SQL Server Tacklebox 


• How to Become an Exceptional DBA


• The Art of XSD 


• SQL Server Hardware


• The SysAdmin Handbook - The Best of Simple Talk 


• Brad's Sure Guide to SQL Server Maintenance Plans 


• Exchange 2010 - A Practical Approach 


• Don't Just Roll the Dice


• Protecting SQL Server Data


• Book of Red Gate (about Red Gate, not SQL Server) 

* Videos

• The Basics of SQL Server Profiler 


• How to Interpret Profiler Results

Related:
Free SQL Server Videos
HOW TO try Windows Azure & SQL Azure for free (without any credit card)

Read More

Notes from Pluralsight SQL Azure course

Here is a summary of the 2h 38min duration Pluralsight SQL Azure course  -

* Advantages of SQL Azure -

• Highly available
• Manageable
• Scalable
• Familiar – similar to SQL Server 2005 & 2008, but differences/limitations exist
• Maintained by MS IT

* Some differences from standard SQL Server  -

• Data migration – SSIS, BCP, Scripts, SQL Azure Migration Wizard, System.Data.SqlClient.SqlBulkCopy; Restore not supported
• Transactions – possible in same DB instance only
• Collation applies only to Column & Expression level; Server, Database excluded
• Tables must have clustered index
• Supports most but not all of TSQL 

* Candidate apps that can leverage SQL Azure -

• Require relational data model. Consider Windows Azure Table for other needs
• Easily partitioned data sets, 5 – 50 GB database
• All queries use one database. (Most Enterprise apps may need to access multiple DBs)
• Require high availability

* Besides Memory, Time, Disk costs, Devs must also understand SQL Azure pricing
* SQL Azure pricing based on usage units: Web (upto 149 databases of 1—5GB each possible) or Business (upto 15 databases of 10—50GB each possible)
* No cost for data transfer if SQL & Azure are on the same availability zone
* Bill accessible through Windows Azure Portal
* SQL Azure usage scenarios -

• Cloud Hub Data Collaboration
• Scale-out Enterprise Applications
• Departmental Applications
• SaaS
• BI

* MS Tools to access SQL Azure – SSMS, Visual Studio, SQL Azure Portal
* Understand Connection termination failure codes to manage recovery -

• System is busy, reconnect after 10s: 40501
• Database hit maximum size: 40544
• Transaction Termination: 40549
• Lock Consumption: 40550
• TempDB uses 5GB: 40551
• Log File issues:  40552
• Excessive Memory Usage: 40553

* Ideal candidates for migration to SQL Azure –

• Tables with indices
• Transations stay in a single db
• DB under 50GB

* Tasks in migration -

• Migrate users & login IDs
• Map data types
• Map db objects: tables, triggers, views, SPs
• Update incompatible objects to work with SQL Azure
• Update apps to work with SQL Azure
• Migrate data to SQL Azure

* It is possible to expose SQL Azure database over OData especially to non-SQL consumers
* OData makes your data consumable everywhere
* OData is a web protocol for querying & updating data
* OData builds on HTTP, Atom Publishing Protocol, JSON
* Feeds may supply more than one collection
* SQL Azure can feed needs of data analysis (through Excel, SSRS etc)

Also see:

Things I learnt from the Pluralsight course "Introduction to Visual Studio 2010"

Read More

Can NoSQL replace SQL Server?

Twitter, Facebook, Digg, and Rackspace are notable sites using various types of NoSQL implementations.

Michael Otey explains why NoSQL isn’t really a viable replacement for SQL Server -

The term “NoSQL” was coined back in 1998, and it originally stood for Not Only SQL. So the name NoSQL is a bit of a misnomer.

NoSQL databases aren’t relational at all.

Today’s NoSQL databases are distributed data stores that are designed for very large-scale data access requirements. Probably the most popular NoSQL database is the Cassandra open source project. Cassandra began as Facebook’s proprietary database, but Facebook released it as open source in 2008.

Other NoSQL implementations include Google BigTable, Apache Hadoop, MapReduce, MemcacheDB, and Voldemort.

...though NoSQL databases are designed for performance and scalability, they aren’t designed to support the atomicity, consistency, isolation, and durability (ACID) properties that are standard in relational databases such as SQL Server....each different NoSQL implementation uses a proprietary data access mechanism.

SQL Server DBAs don’t need to fear that NoSQL represents some new technology wave that will sweep away relational databases like SQL Server. Instead NoSQL is just another tool in the application developer’s toolbox.

NoSQL implementations solve a data storage problem that relational databases weren’t really designed to address. So the bottom line on NoSQL is that NoSQL isn’t a technology that’s going to replace relational database systems like SQL Server—except in very specialized instances.

Read More

SQL Server DATENAME function returns Month number instead of name?

I recently came across a query posted by a puzzled developer -
Why does SELECT DATENAME(MONTH,GETDATE()) return '09' instead of the obvious result of 'September'?

The DATENAME function, after all, returns a character string representing the specified datepart of the specified date.

It turns out that if the language environment is set to Japanese, Korean or Traditional/Simplified Chinese (during installation or using SET LANGUAGE), months are stored as numerals instead of words for those languages & that's how they show up.

This can be verified by running the sp_helplanguage system stored procedure which reports information about a language.


Also see:
HOW TO find a column's value without specifying the column name

Read More

Book Review: Beginning SQL Server 2005 for Developers

Beginning SQL Server 2005 for Developers by Robin Dewson lives up to it's title of being a helpful resource for beginners to SQL Server 2005. The author patiently walks the reader through the basics & a wide breadth of topics with adequate examples in the 500+ paged book spanning 14 chapters. Many of the examples are demonstrated using multiple modes - SQL Server Management Studio, TSQL & Templates.

The book is filled with interesting facts, "gotchas", practical tips & tricks that make the chapters engaging. I have used the SELECT statement for years but I did not know that $IDENTITY & $ROWGUID can be used with the SELECT statement to return the value from the IDENTITY column & the ROWGUID column if such columns exist within the table being querying upon. Similarly I have used the COUNT function but didn't know there was a COUNT_BIG function as well that returns a bigint data type value unlike COUNT which returns an int type value.

I feel the information in the book could have been better presented & organized. There were some places where the examples of usage could have been closer to the topic for better assimilation.

My rating: 4/5

Also see:
SSMS Tips & Tricks
Free SQL Server 2008 Learning Resources

Read More

Appending to file with BCP

I found an interesting tip about BCP (the SQL Server command line tool) on the MSDN Forums

While exporting data from SQL Server to a file using BCP, it always overwrites the contents of the file specified in command if it already exists or creates a new file but it cannot append.

If ever you need the output of a query appended to the info in a existing data file, use an intermediary file to manage the updation of the data file -

Exec master..xp_cmdshell 'bcp "select name from sysobjects" queryout "c:\tempexportfile.txt" -c -T -SYourServerName'
Exec master..xp_cmdshell 'type "c:\tempexportfile.txt" >> "c:\mainexportfile.txt"

Also see:
Exporting to Excel with bcp

Read More

Exporting to Excel with bcp

I was recently looking for a quick way to export SQL Server table data (over 1,00,000 records) to a Excel file.

I found that the bcp command line tool can export SQL Server table data to a .xls file but when I export it to a Excel 2007 (.xlsx) file, I couldn't open the file. It throws a warning - "Excel cannot open the file because the file format or extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file".

The fix to this issue reported in ASP.NET Forums which points to a MS KB article, is complicated.

I wondered, if bcp can export to a .xls file why can't it smoothly export to a .xlsx file?

Curiously, I was able to able to export over 7 lakh records to a .xls file (which is supposed to have a row limit of ~65K rows in 22seconds) with bcp and the .xls file opens fine in Excel 2007 displaying all the 7 lakh records.

Vinod helped me understand how the process actually works -

Fundamentally, you need to understand that the BCP command doesn’t actually write as a real XLS file. Try to open it in Notepad and it would be a mere csv format. It is just that Excel seamlessly opens it without any problem. Now with .XLSX extension Excel 2007 expects the same to be in the XML format (which it isn’t actually) and hence the error.

BTW, the format of XLS or XLSX doesn’t determine the number of rows. It is the version of Office (2003/2007) you are running.

Read More

Free SQL Server Tools

SQL Server Magazine has compiled The Mega Guide to Free SQL Server Tools that has over 80 tools for under these categories -

• Administration
• Backup and Recovery
• Best Practice and Compliance
• Business Intelligence
• Change Management
• Code Formatters
• Database Comparison
• Database Security
• Development
• Job Management
• Log Management
• Performance and Monitoring
• Prevent SQL Server Injection Attacks
• Web Administration

Check them out....

I'll list my favorites among them here after I try them out.

Also see:
Free SQL Server 2008 Learning Resources
Free SQL Server Videos
Database Schema Samples

Read More

Can SQL Server Express be used on a Production Server?

Yes...with some limitations, going by these threads on the ASP.NET Forums.

The FAQ for the Express products has another interesting point about SQL Server Express

What is the difference between SQL Server 2008 Express and SQL Server Compact Edition?
SQL Server Compact Edition is a lightweight , easy to deploy database system that is ideal for Windows database development. A 2MB footprint that requires no administration makes it an ideal solution for creating most data storage needs. Some of the limitations of SQL Server Compact Edition:

* Does not accept remote connections
* It is not compatible with the design time tools for LINQ to SQL
* Does not support stored procedures

SQL Server Express is designed to be feature-rich and powerful database system. It is an ideal solution for creating client-server Windows applications and Web development. Some of the features of SQL Server Express:

* Develop stored procedures with Visual Basic Express and Visual C# Express
* Full support of design time tools like LINQ to SQL
* A huge data storage of 4GB capacity

SQL Server Express is designed to meet the needs of simple applications. It is limited to using 1 CPU and up to 1GB RAM, with a 4GB maximum database size.

Trivia - Did you know, while the estimated retail pricing for U.S. and Canadian editions of SQL Server 2005 is $24,999, the Developer Edition which includes all the functionality of Enterprise Edition is $49

Related:
Can VS 2008 editions be installed side-by-side?

Read More

10 Performance Secrets for SQL Server Developers

Michael K. Campbell highlights 10 common performance problems to avoid in the latest edition of SQL Server Magazine:

1. Failure to Properly Configure AWE (Address Windowing Extensions) Memory Usage by not enabling SQL Server to use more than 2GB of RAM.
2. Failure to Normalize
3. Failure to Use Clustered Indexes
4. Failure to Optimize by ignoring the impact of statistics, indexes etc
5. Failure to Create SARGable Queries (SARG - Search ARGument)
6. Wasted Network Bandwidth due to SQL Server's chatty behavior
7. Improper Use of Cursors
8. Failure to Use Full-Text Indexing where SARGable queries aren’t possible
9. Failure to Use Multiple Data Files. Microsoft recommends a ratio of roughly 0.25 to 1 data file per file group per processor.
10. Failure to Properly Size Data Types

Read More

HOW TO execute a SELECT query Without Column Names

A developer posted an unusual T-SQL question on an online Message Board I frequent. He wanted to know how to find a column's value without specifying the column name. I guessed syscolumns would be part of the puzzle and rather than construct the query I searched & found a complete working snippet by Kalman Toth. It gets the third, fourth and sixth columns of the Contact table (in SQL Server 2005's AdventureWorks database) without explicitly specifying the column names in the SELECT query -

use AdventureWorks
go
declare @SchemaName as sysname, @TableName as sysname
declare @Col3 as sysname, @Col4 as sysname, @Col6 as sysname
declare @SQLstring as nvarchar(512)
set @SchemaName = 'Person'
set @TableName = 'Contact'
select @Col3 = sc.name
from sys.objects as so inner join sys.syscolumns as sc
on so.object_id = sc.id
where so.name = @TableName and sc.colid = 3
select @Col4 = sc.name
from sys.objects as so inner join sys.syscolumns as sc
on so.object_id = sc.id
where so.name = @TableName and sc.colid = 4
select @Col6 = sc.name
from sys.objects as so inner join sys.syscolumns as sc
on so.object_id = sc.id
where so.name = @TableName and sc.colid = 6
select @SQLstring = 'SELECT ' + @Col3 + ',' + @Col4 + ',' + @Col6
+ 'FROM ' + @SchemaName+'.'+@TableName

-- select @SQLstring
exec sp_executesql @SQLstring
go

This goes on to show how sharing discoveries by using the right keywords can help fellow developers. Choosing unambiguous names & titles helps search engines to rank relevant links higher.

On a related note, I remember in the early years of C#, Google misunderstanding the context (like spell-checkers do) would exclude the "#" part possibly as part of sanitizing input

Also see:
HOW TO merge two HTML tables with Javascript
Can VS 2008 editions be installed side-by-side?

Read More

5 free SQL Server tools

In the 10th Anniversary edition of SQL Server Magazine, there are recommendations to 5 free tools for SQL Server. Two of them are for load testing and the others for preventing SQL Injection -

1. SQLQueryStress - query load testing tool written by SQL Server MVP Adam Machanic.
2. SQL Load Generator - run multiple concurrent queries against SQL Server
3. HP Scrawlr — This free scanner utility can detect and identify whether your website is susceptible to an SQL injection attack.
4. URLScan — This security tool actively restricts the kind of HTTP requests that Microsoft IIS will process.
5. Microsoft Source Code Analyzer for SQL Injection — Static code analysis tool for finding SQL Injection vulnerabilities in ASP code

The following are Kalen Delaney's recommendations for testing before deployment:
1. Test with real data.
2. Test with real data volumes.
3. Test with a real number of concurrent users.

Also see:
SQL Server Performance Audit Checklist
Tips on ASP.NET Hosting & Deployment

Read More

Deprecated SQL Server 2008 Features

"Overnight success takes a long time"

- Paul Buchheit, original lead developer of GMail

If you plan to migrate to SQL Server 2008 or later in the future, you can save yourself a lot of trouble by being aware of the deprecated features. Some of the changes have been made to get in line with ANSI SQL standards.

MSDN has a detailed list of the Deprecated Database Engine Features in SQL Server 2008 and those planned for the future versions. T-SQL expert Itzik Ben-Gan has highlighted some of the more important ones among them & suggested best practices to follow.

Read More

SSMS Tips & Tricks

There are innumerable features in SQL Server Management Studio. In my years of working with SQL Server 2005, I've discovered some that I feel are underused. Here are a few that have helped me -

Tip #1: Open Object Explorer and new query on start up - If you have used SQL Server 2000 Query Analyzer a lot in the past, you will surely notice that the query window does not open on start up. Go to Tools > Options. Select the Environment option in the treeview and from the At startup drop down on the right, select "Open Object Explorer and new query". From the next time on, a new query window will automatically open when you start a new instance just as in the old times.

Tip #2: Screen tips & Shortcut Keys - If you don't normally use keyboard shortcuts, there is a simple way to get acquainted with icons on the toolbar and their related shortcut keys. Select the Show Screentips on Toolbars & Show shortcut keys in ScreenTips checkboxes from the menu option Tools > Customize.

If you like keyboard shortcuts, check my compilation of SSMS shortcuts

Tip #3: Configure Shortcuts - This tip, derived from an older SQL Server 2000 article, helps you save typing "SELECT * FROM " every time you need to see a table listing. Go to the menu option Tools > Options and in the treeview expand Environment and select Keyboard underneath it. In the Query Shortcuts section, you can assign your own stored procedures to specific key combinations.

Tip #4: Filter objects - If your Table or Stored Procedure list is large, you often lose several seconds trying to locate a specific object. Let's say you want to look for tables related to Sales. You can right click on Tables under the database you are working in Object Explorer to invoke Filter > Filter Settings from the context menu. In the dialog box that opens up, type Sales in the Value column against the Name property while keeping the Operator as Contains.

You can filter database objects including Tables and Stored Procedure by Name, Schema & Creation Date. There are a host of Operators in the Filter Criteria to fine tune our search.

Tip #5: Select adjacent columns or rows - Occasionally you may want to select only certain columns or rows from the Results pane after you execute a query. You can select adjacent columns or rows by keeping Ctrl and Alt keys pressed while selecting the desired columns or rows.

For more detailed notes and screenshots, check my recent article.

Read More

Free SQL Server 2008 Learning Resources

Microsoft Press is offering a free 258 paged e-book - Introducing Microsoft SQL Server 2008

Microsoft Learning has a free e-Learning course - Collection 6187: What’s New in Microsoft SQL Server 2008

Red Gate has two free offerings - Dissecting SQL Server Execution Plans and How to Become an Exceptional DBA. (Thanks Mladen)

Related links:
Free SQL Server Videos
Track Free Microsoft e-Learning products with RSS feed

Read More

Free SQL Server Videos

The MSDN Webcasts site has a nice bunch of downloadable webcasts (beside Virtual Labs & Podcasts) on SQL Server 2008 and SQL Server 2005

Michael K. Campbell has recently launched www.sqlservervideos.com–a site that provides free SQL Server training videos.

There aren't too many videos for now, but it's something to watch out for.

In the Windows Dev Pro Update Newsletter, he has shared deployment issues that he has faced while putting up the website that made for interesting reading. I'm excerpting it here as I didn't find an online link for the same material -

403 Access Forbidden. Initially, every page on my site greeted me with this error. As an IIS 6 veteran, my initial thought was that I’d forgotten to ACL my files and content. But the site was running on IIS 7 where automatic worker process injection removes that headache. As such, my first thought was that maybe something like UrlScan was interfering with my URL Rewriting Engine. Happily, however, the site is hosted with ORCS Web and they were all over the problem in a jiffy. The culprit? When I initially created a site with them, it was hosted on IIS 6 but was later transitioned to IIS 7. As part of the conversion process, the site was configured to use a classic application pool instead of an integrated pool. As such, my URL rewriting code simply wasn’t allowed to run.

Errors in my Errors. Once the site was up and running, I decided to test out my very cool error pages. The idea was to provide end users, or visitors, with helpful markup and information, while still ensuring that I was throwing HTTP 500 and 404 responses out to search engines and bots. I got errors all right, just not the ones that I wanted. Worse, somehow they managed to ripple through the site, and tear the whole thing down after a few requests. Sadly, this was all my fault, and required a few tweaks of my processing logic. Because the Global.asax was also in charge of routing these errors, I threw in some exception handling code in my error handling code as well. I knew that unhandled exceptions in Global.asax turn ugly very quickly.

Stupid Robots or Stupid Code? Since SEO is such a key part of my site, imagine my horror (a few days after launch) when I discovered that while normal visitors were accessing the site fine, search engines encountered nothing but errors. Tracing my way through my code, I found something written months ago that would help ensure that search engine requests wouldn’t be counted along with normal ad impressions. Sadly, while this code worked in testing, it just didn’t work out in the real world with real robots. I think I’ve fixed the problem, but I’m hoping for the glorious return of the robots.

Replacing "soft 404s" with "hard 404s" as described in Errors in my Errors above is a Google SEO best practice.

Further on, he has also pointed out that there is now a tool to manage migration from IIS 6 applications & servers to IIS 7.

Related links -
Tips on ASP.NET Hosting & Deployment

Read More

SQL Server 2005 - which edition to choose?

SQL Server 2005 has basically 6 editions -

• Enterprise Edition
• Standard Edition
• Workgroup Edition
• Developer Edition
• Compact Edition
• Express Edition

SQL Server 2005 Developer Edition and Evaluation Edition have the same feature set as the Enterprise Edition; the only difference between these editions is the licensing policies.

Did you know the Compact Edition is the smallest among all the editions & it is free like the Express Edition?

Developers can take informed decisions on the optimal edition to choose for a specific requirement by understanding the features and limitations of each.

Microsoft E-Learning is running a free Clinic - "Understanding the Differences Between Microsoft SQL Server 2005 Standard and Enterprise Editions" that exhaustively covers features in the Enterprise Edition.

Read More

20 SQL Server 2005 Keyboard Shortcuts

What is the bored mother of a geek called?
Motherboard

There are a bunch of SQL Server 2005 Keyboard Shortcuts on MSDN, but I find these 20 super convenient & use it very often.

Description	Shortcut
Create a New File	CTRL+N
Toggle full screen mode	SHIFT+ALT+ENTER
Set or remove a bookmark at the current line	CTRL+K, CTRL+K
Next bookmark	CTRL+K, CTRL+N
Previous bookmark	CTRL+K, CTRL+P
Clear bookmarks	CTRL+K, CTRL+L
Make the selected text a comment	CTRL+K, CTRL+C
Uncomment the selected text	CTRL+K, CTRL + U
Make the selected text upper case	CTRL+SHIFT+U
Make the selected text lower case	CTRL+SHIFT+L
Toggle between query and results pane	F6
Display Object Explorer	F8
Output results in text format	CTRL+D
Output results in a grid	CTRL+T
Cancel the executing query	ALT+BREAK
Display the estimated execution plan	CTRL+L
Display the Replace dialog box	CTRL+F
Display the Find dialog box	CTRL+H
Start incremental search	CTRL+I
Find the next occurrence of the previous search text	F3

Related links:
My Favorite Visual Studio 2005 Keyboard Shortcuts

Read More

Which is better - EXISTS check or SELECT COUNT(*) ?

A nice explanation from a detailed article in SQLMag -

Using the T-SQL EXISTS keyword to perform an existence check is almost always faster than using COUNT(*). EXISTS can stop as soon as the logical test proves true, but COUNT(*) must count every row, even after it knows one row has passed the test.

I like collecting tips like these to supplement the numerous tips, tricks & checklists in the (freely downloadable) Patterns & Practices guide "Improving .NET Application Performance and Scalability". I wish they upgraded this .NET bible to include the advances that have happened since the guide was published in 2004.

Read More