Galin Iliev's blog

Software Architecture & Development

Get list result from Stored Procedure

Stored procedures are very powerful and they have many benefits than using UDF (user defined function).

There is one cons though - resultset cannot be manipulated further from T-SQL.

There is one trick that will allow it: by using OPENROWSET:

SELECT  * 
FROM    OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;','set fmtonly off exec master.dbo.sp_who')
AS tbl

Read full blog post here

Access Remote SQL Server with SQL Management Studio and Windows Authentication

I've been working with SQL Management Studio since it's release (and even before) and I think it is a very good tool. Especially as in next version there will be IntelliSense.

In my daily work I need to access several remote SQL Servers (over VPN) and some of them require windows authentication. With SQL Server Authentication is easy - just create VPN connection and use SQL Server Management Studio from local machine entering SQL credentials in the wide-known box below:

image

This is not the case with windows authentication. Especially in domain environment it is better to give access to domain groups and users instead of creating SQL ones.

As a workaround (the one I've used 'till today) you can do remote desktop connection to the server and do the job via RDC console. I have struggled with this approach for a long time and although it is not that bad there are some issues working all the time via RDC.

I've tried runas command but somehow my credentials weren't accepted.... but the switch /NetOnly did the job

so I use now

C:\>runas /netonly /user:domainName\userName "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\ssmsee.exe"

and it works...

Hope this helps

How (not) to bloat SQL Server execution plan cache?!

Short answer: don't use SqlParameterCollection.AddWithValue() method!

Why?! Because if you have query like this:

   1: string lastName = "Galcho";
   2:  
   3: SqlCommand selectCommand = new SqlCommand(
   4:         "SELECT ContactID, FirstName, LastName
   5:         FROM AdventureWorks.Person.Contact
   6:         WHERE LastName = @LastName",
   7:         connection);
   8:  
   9:  
  10: selectCommand.Parameters.AddWithValue("@LastName", lastName);

the execution plan cache entry is like this

(@LastName nvarchar(6))SELECT ContactID, FirstName, LastName FROM AdventureWorks.Person.Contact WHERE LastName = @LastName

So if you execute the code with different value in lastName variable you'll get different first part of the cached executed plan.

Using selectCommand.Parameters.Add("@LastName", SqlDbType.NVarChar, 50).Value = lastName; instead is a way better.

Read full story here

Power "toys" for VS 2008 DB Edition

A new set of tools has been released for Microsoft Visual Studio Team System 2008 Database Edition. This is a set of tools that enable you to create unit tests against database.

According the download site here is what's included in these tools :

This release updates the Power Tools for the Database Edition to work with Visual Studio 2008 and provides several new features. The new features include two custom unit test conditions, a new Data Generation Wizard, and new MSBuild tasks to support running TSQL Static Code Analysis from the command line. The updated features include 5 refactoring types, a dependency viewer, additional data generators and editors, 2 MSBuild tasks for Schema and Data Compare and the TSQL Static Code Analysis feature

New Test Conditions for Database Unit Tests
• ChecksumCondition – You can use this test condition to verify that the checksum of the data set returned by a database unit test matches the checksum of an expected data set.
• ExpectedSchemaTestCondition – You can use this test condition to verify that the column names and data types of the returned data set match expected values.

Data Generator Improvements
• New Data Generator Wizard – This new wizard creates a data generation plan that is configured to copy data from a source database. You can use this wizard when you need to copy most of your data from a live source, but need to make small changes to ensure privacy.

MSBuild Task Improvements
• SqlAnalysis Task – You can use this build task to run T-SQL Static Code Analysis from MSBuild.
TSQL Static Code Analysis
• Static Code Analysis - A precursor to the functionality that will be in future versions of VSTS that will allow you to perform Static Code Analysis on T-SQL code.

Refactoring
• “Move Schema” Refactoring - Allows a user to right click on an object and move it to a different but existing schema
• SP Rename Generation - Generate a new script that will contain sp_renames for all rename refactored objects that the user can then execute.
• Wildcard Expansion - Automatically expand the wildcard in a select to the appropriate columns.
• Fully-Qualified Name Support - Automatically inject fully-qualified names when absent in a script
• Refactoring extended to Dataset - Refactor into strongly typed dataset definitions

MSBuild Tasks
• Data / Schema Compare Build Tasks - MSBuild tasks that can generate scripts as if the user had run the Data / Schema compare UI

Schema View
• API Access to Schema View - Insert / Update / Delete to schema View and list schema objects and their associated files

Dependency Tool Window
• Dependency Tree - Show the dependencies ( incoming / outgoing ) for selected schema objects in a new tool window

Miscellaneous Tools
• Script Preprocessor - Expand SQLCMD variables and include files and command line version (sqlspp.exe) & an MSBuild version ( wraps the command line version )

Grab it from here.

Well Gert posted a good walkthrough in screenshots showing how new Data generation wizard works.

LINQ to SQL vs LINQ to Entities

Elisa Flasko from Microsoft Data Platform Team wrote a nice article "Introducing LINQ to Relational Data" highlighting main features or two main ORMs coming out form Microsoft with Visual Studio 2008 (ADO.NET EF is not released so far but it will be released as additional package to VS2008)

In short:

LINQ to SQL is applicable when:

  • Use an ORM solution and my database is 1:1 with my object model
  • Use an ORM solution with inheritance hierarchies that are stored in a single table
  • Use my own plain CLR classes instead of using generated classes or deriving from a base class or implementing an interface
  • Leverage LINQ as the way I write queries
  • Use an ORM but I want something that is very performant and where I can optimize performance through stored procedures and compiled queries

LINQ to Entities is applicable when:

Write applications that can target different database engines in addition to Microsoft SQL Server

Define domain models for my application and use these as the basis for my persistence layer.

Use an ORM solution where my classes may be 1:1 with the database or may have a very different structure from the database schema

Use an ORM solution with inheritance hierarchies that may have alternative storage schemes (single table for the hierarchy, single table for each class, single table for all data related to specific type)

Leverage LINQ as the way I write queries and have the query work in a database vendor agnostic manner.

Use an ORM but I want something that is very performant and where I can optimize performance through stored procedures and compiled queries

 

Read article on MSDN or download Word 2007 version.

Top 10 Hidden Gems in SQL Server 2005

Here are ten precious SQL Server 2005 features that could help you make your DB flying:

  1. TableDiff.exe
  2. Triggers for Logon Events (New in Service Pack 2)
  3. Boosting performance with persisted-computed-columns (pcc).
  4. DEFAULT_SCHEMA setting in sys.database_principles
  5. Forced Parameterization
  6. Vardecimal Storage Format
  7. Indexing made easier with SQL Server 2005
  8. Figuring out the most popular queries in seconds
  9. Scalable Shared Databases
  10. Soft-NUMA

Read full article on MS TechNet

MS SQL Server 2008 Webcast for devs

Here are some interesting webcasts for MS SQL Server 2008 as a platform for application development. These casts could help you to ramp up with the latest stuff around new version of the popular DB server from Microsoft.

  • Building Spatial Applications with SQL Server 2008
    SQL Server 2008 delivers new Spatial data types that enable you to consume, use, and extend location-based data through spatial-enabled applications. Attend this webcast to learn how to use spatial functionality in next version of SQL Server to build and optimize Spatial queries.
    Audience: Mid-Level
    Focus: Spatial Data
  • Entity Framework for Database Administrators
    The Entity Framework is a new data technology from Microsoft that may particularly interest database administrators (DBAs). Watch this webcast to see how this technology can radically change the development of applications from a DBA perspective, and also change access patterns on servers.
  • XML Tools in Visual Studio 2008
    Did you know that the Microsoft Visual Studio development system has great tools to help developers work with XML? Join the XML tools team in this session to learn about the XML tools available today in Microsoft Visual Studio 2005 and the many changes that we are making for Microsoft Visual Studio 2008, in addition to some of our longer term projects.
  • New T-SQL Programmability Features in SQL Server 2008
    View this webcast on new T-SQL programmability features in Microsoft SQL Server 2008, an extension from last year's presentation on top Microsoft SQL Server 2005 T-SQL features. We cover exciting new features such as: New data types (i.e. date/time, filestream, HierarchyID, large UDT, sparse columns), dependency management for database objects, and much more.
  • Programming SQL Server 2008
    Microsoft SQL Server 2008, code name "Katmai," introduces a significant amount of new or improved functionality, including new data types, in addition to performance and security enhancements. In this webcast, we discuss how you can easily take advantage of the new functionality available in SQL Server 2008 within your application development.
  • Microsoft SQL Server 2008: Beyond Relational
    As the data your applications work with changes from “words and numbers” to “sights and sounds”, we are evolving our data platform to go beyond relational data, beyond OLAP, to truly support all of the digital data types of the future. We will strive to deliver the best platform for integrated storage, and advanced applications such as spatial data, while also making it dramatically easier for you to build data-driven applications, without needing to invest significant resources in bridging the gap between data and programming language data structures. Come learn about the new enhancements in SQL Server 2008 that will help you manage all types of data.
    Audience: Mid-level
  • Data Programmability and SQL Server 2008
    A comprehensive data programmability platform enables developers to build data-centric solutions that target desktops, mobile devices, online Web servers, and enterprise servers using a variety of products, programming frameworks, Web services, and data connectivity technologies that work together to access and manage data. Join this webcast as we discuss the new data programmability features that are supported by Microsoft SQL Server 2008.
    Audience: Technical

 

Full list can be found here.

Sample databases can be found on CodePlex

Note: Do not forget - you have to install patches to Visual Studio 2005 and even Visual Studio 2008.

ADO.NET Data Services CTP is released!

ADO.NET Data Services aka Project "Astoria" December CTP is released. Mike Flasko (PM @ Astora team) posted some key points:

The following features are in this CTP:

  • Support to create ADO.NET Data Services backed by:
    • A relational database by leveraging the Entity Framework
    • Any data source (file, web service, custom store, application logic layer, etc)
  • Serialization Formats:
    • Industry standard AtomPub serialization
    • JSON serialization
  • Business Logic & Validation
    • Insert custom business/validation logic into the Request/response processing pipeline
    • simple infrastructure to build custom access policy 
  • Access Control
    • Easily control the resources viewable from a data service
  • Simple HTTP interface
    • Any platform with an HTTP stack can easily consume a data service
    • Designed to leverage HTTP semantics and infrastructure already deployed at large
  • Client libraries:
    • .NET Framework
    • ASP.NET AJAX
    • Silverlight (coming soon)
  • For more information see ADO.NET Data Services official site.