How to log when a function is called?

This question came up today and here is one way of doing it. It requires running xp_cmdshell so this is probably not such a good idea.
The problem with functions is that you cannot just insert into any table. INSERT, UPDATE, and DELETE statements modifying table variables local to the function.
EXECUTE statements calling an extended stored procedures are allowed.
So with this in mind we know that we can call xp_cmdshell, from xp_cmdshell we can use osql
Let's take a look
We will be using tempdb


--Create the table
USE tempdb
go
CREATE TABLE LogMeNow (SomeValue varchar(50), SomeDate datetime default getdate())
go

--Here is the proc
CREATE PROC prLog
@SomeValue varchar(50)
AS
INSERT LogMeNow (SomeValue) VALUES(@SomeValue)
go

--And here is the function
CREATE FUNCTION fnBla(@id int)
RETURNS int
AS
BEGIN
DECLARE @SQL varchar(500)
SELECT @SQL = 'osql -S' +@@servername +' -E -q "exec tempdb..prLog ''fnBla''"'
EXEC master..xp_cmdshell @SQL
RETURN @id
END

Now call the function a couple of times

SELECT dbo.fnBla(1)
SELECT dbo.fnBla(2)
SELECT dbo.fnBla(4)

And look inside the table

SELECT * FROM LogMeNow

What if you were to run this?

SELECT dbo.fnBla(4),* FROM sys.sysobjects

See the problem? The function will be called for every row, if you have a big table this can be problematic!!!!!!!!

I tested this on SQL 2000 and on SQL 2005(including a named instance). So there you have it, this is one way. does it smell kludgy and do I feel somewhat dirty now? yes it does indeed :-(

SQL Teaser: @@ROWCOUNT

Without running this what do you think will be printed?


SET ROWCOUNT 0
DECLARE @ int
SET @ =6
IF @@ROWCOUNT = 1
PRINT 'yes'
ELSE
PRINT 'no'
PRINT @@rowcount

Should SQLServer Have The CREATE [OR REPLACE] PROCEDURE Syntax?

I was asked by a developer at work the other day why SQL Server does not have the create or replace syntax. I started thinking and there were some advantages and one big disadvantage

First the advantages

Advantage
When scripting out a database you don’t have to generate if exists.....drop statements

When modifying an object from a file in source control you don’t have to change back and forth between CREATE and ALTER. This is really annoying sometimes; usually you create a proc or script out a proc and store it in Subversion/SourceSafe. Now you take that file, make some changes to the proc and run it. Of course it will tell you that the proc already exists, in general you don’t want to drop the proc and then execute the CREATE proc statement because then you have to worry about the permissions.(not everyone is running as dbo you know)

Disadvantage
I can overwrite a proc without even knowing it. What if we both are working on a proc and somehow we pic the same name I create my proc, you work in a remote location, you create yours and mine is gone.

Of course it is all what you are used to, I am sure the Oracle guys are not overwriting each other’s procs every day either

So what do you think? Would you be in favor of this syntax or not? Can you think of more advantages or disadvantages?

What Would You Like To Ask Erland Sommarskog?

In the Who do you want to see interviewed next?(sqlblog) and Who do you want to see interviewed next?(here) blog post I asked for some names of people who YOU would like to see interviewed. Erland Sommarskog's name was submitted in comments a couple of times. I contacted Erland and am happy to anounce that he has agreed to do this.

The first time I heard of Erland Sommarskog was in the SQL Server programming newsgroups. I also read all the articles on his site. When I answer questions I usually give the link out to these 4 articles
Arrays and Lists in SQL Server
Implementing Error Handling with Stored Procedures
Error Handling in SQL Server – a Background
and of course The curse and blessings of dynamic SQL

I always wondered why he didn't write a SQL book. Hey, now I can ask him that :-)

Before you think of any questions please visit Erland's page first (http://www.sommarskog.se/index.html) and read some of the articles he wrote.

You can submit questions until Monday May 12th and then I will forward the questions to Erland.

What Would You Like To Ask Craig Freedman?

In the Who do you want to see interviewed next?(sqlblog) and Who do you want to see interviewed next?(here) blog post I asked for some names of people who YOU would like to see interviewed. Craig Freedman's name was submitted in comments a couple of times. I contacted Craig and am happy to anounce that he has agreed to do this.

Before you think of any questions please visit Craig's blog first(http://blogs.msdn.com/craigfr/) to get a feel of what kind of questions to ask. It would make sense to keep the questions focused on query processing, query execution, and query plans. I told Craig that if he doesn't like a question that he does not have to answer it.

You can submit questions until Monday May 12th and then I will forward the questions to Craig

Do you depend on sp_depends (no pun intended)

HTML Source EditorWord wrap I answered this question on the MSDN forums: How can I search all my sprocs to see if any use a function?
Several people suggested using sp_depends. You can't really depend on sp_depends because of deferred name resolution. Take a look at this

First create this proc

CREATE PROC SomeTestProc
AS
SELECT dbo.somefuction(1)
GO


now create this function

CREATE FUNCTION somefuction(@id int)
RETURNS int
AS
BEGIN
SELECT @id = 1
RETURN @id
END
Go


now run this


sp_depends 'somefuction'

result: Object does not reference any object, and no objects reference it.



Most people will not create a proc before they have created the function. So when does this behavior rear its ugly head? When you script out all the objects in a database, if the function or any objects referenced by an object are created after the object that references them then sp_depends won't be 100% correct

SQL Server 2005 makes it pretty easy to do it yourself

SELECT

specific_name,*

FROM information_schema.routines

WHERE

object_definition(object_id(specific_name)) LIKE '%somefuction%'

AND

routine_type = 'procedure'

BTW somefuction is not a type, I already had a somefunction but was too lazy to change more than one character

Testing for SQL Server Vulnerabilities

I found this SQL Server Testing (not unit but vulnerability) page and decided I would post a link to it since it has some useful stuff. The link is below

http://www.owasp.org/index.php/Testing_for_SQL_Server

Here is what is covered. Enjoy (or live in fear over the weekend)


1 Brief Summary
2 Short Description of the Issue
3 Black Box testing and example
3.1 SQL Server Peculiarities
3.2 Example 1: Testing for SQL Injection in a GET request.
3.3 Example 2: Testing for SQL Injection in a GET request (2).
3.4 Example 3: Testing in a POST request
3.5 Example 4: Yet another (useful) GET example
3.6 Example 5: custom xp_cmdshell
3.7 Example 6: Referer / User-Agent
3.8 Example 7: SQL Server as a port scanner
3.9 Example 8: Upload of executables
3.10 Obtain information when it is not displayed (Out of band)
3.11 Blind SQL injection attacks
3.11.1 Trial and error
3.11.2 In case more than one error message is displayed
3.11.3 Timing attacks
3.11.4 Checking for version and vulnerabilities
3.12 Example 9: bruteforce of sysadmin password
4 References

Who do you want to see interviewed next?

I have interviewed a bunch of people already, some of them on this blog and some of them on SQLBlog

Below is the list of all the people so far

Louis Davidson
Itzik Ben-Gan
Ken Henderson
Kalen Delaney
Adam Machanic
Interview With Stéphane Faroult About Refactoring SQL Applications
Database Refactoring Interview With Scott W. Ambler
Interview With Joe Celko About The Forthcoming Book Thinking In Sets

Who else should I interview? I have some ideas but I would like you to participate. Here is my thinking, after we have a list of possible people, I will contact them and ask if they will agree to an interview. If they agree I will make a post here and ask you what you would like to ask this person. I usually have a good set of questions but there might be someone out there with some real good/interesting questions.
So post the name of the person you would like to see interviewed and hopefully this person will agree.

It would be nice if you did not pick a person who blogs at SQLBlog but if_you_really_wanted_to then I will not stop you :-0

Please keep it database related, it doesn't have to be specific to SQL Server but it has to be applicable to SQL Server.

SQL Teaser: Some Things Are Not What They Seem

This one is a little sneaky, don’t send me hate mail for it.

What does this return?



SELECT ISNUMERIC('+'),ISNUMERIC('–')

Copy and paste it into QA/SSMS to verify :-0

How to rename a column in a SQL Server table without using the designer

If you have a table and you want to rename a column without using the designer, how can you do that?

First create this table

CREATE TABLE TestColumnChange(id int)
INSERT TestColumnChange VALUES(1)


SELECT * FROM TestColumnChange

As you can see the select statement returns id as the column name. you can use ALTER table ALTER Column to change the dataype of a column but not the name.

Here is what we will do, execute the statement below

EXEC sp_rename 'TestColumnChange.[id]', 'value', 'COLUMN'


Now do the select, you will see that the column name has changed

SELECT * FROM TestColumnChange

That is it, very simple

Interview With Stéphane Faroult About Refactoring SQL Applications

I decided to interview Stéphane Faroult, the author of The Art of SQL because it is one of my favorite SQL books. Stéphane told me he has a new book coming out soon, the name of this book is Refactoring SQL Applications and the majority of this interview is focused on that book.

You can find that interview here: Interview With Stéphane Faroult About Refactoring SQL Applications

Solutions for Common T-SQL Problems Wiki Launched

Volunteer Moderators and Answerers who support the Microsoft MSDN SQL Server Forums have launched a Wiki with Solutions for Common T-SQL Problems. Check it out here: http://code.msdn.microsoft.com/SQLExamples

Use IDENT_CURRENT() to return the last identity value generated in all tables in a SQL Server Database

This is how you return all the tables with their last generated identity values in a SQL Server Database

SELECT IDENT_CURRENT(table_name),*
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'base table'
AND OBJECTPROPERTY(OBJECT_ID(table_name),'TableHasIdentity')=1

Links Of The Week 20080414

Here are the links for this week

Database

SQL Server 2005 Memory Limits and Related Questions
Microsoft to Increase Use of SQL Server 2005 Best Practices Analyzer (SQLPBA) - So Should You
SqlClient, System.Transactions, SQL Server 2008, and MARS
Visual Linq query builder for Linq to Sql
Converting an EAV design to sparse columns and populating
How It Works: SQL Server Page Allocations
How It Works: File Streams Requires Integrated Security (Windows Authentication)
Troubleshooting xp_cmdshell failures
How It Works: SQL Server Checkpoint (FlushCache) Outstanding I/O Target

Non DB tech

CSS Message Box collection
WCF: Reliable Messaging and Retry Timeouts
Google Starts to Index the Invisible Web
Download YouTube Videos as MP4 Files
New WCF Adapter Code Samples on MSDN!
IUpdatable & ADO.NET Data Services Framework
.NET Rocks! #332 - Ted Neward on the New Language Renaissance

Non tech

Why 24 hours in a Day?
The Publishing Industry Takes Another Hit
What job ads really mean
It's A Dog, Not A Towel (Pics.)
I've got your Highlander right here! [Pic]

Video: SQL Server Data Services Architecture

SQL Server Technical Evangelist Ryan Dunn sits down and talks turkey with two of the creators of SQL Server Data Services (SSDS), Architect Istvan Cseri and Development Manager Nigel Ellis. Istvan and Nigel dig into how to design applications for SSDS and cover a number of the interesting aspects of working with SSDS in terms of features, design, and security.

Watch it here: http://channel9.msdn.com/ShowPost.aspx?PostID=395843#395843

Links Of The Week 20080405

Here are the links for this week

Database
Send Table or View as embedded HTML in an email – stored procedure
How It Works: SQL Server 2005 DBCC Shrink* May Take Longer Than SQL Server 2000
How It Works: Non-Yielding Resource Monitor
Overhead of Row Versioning
TempDB:: Table variable vs local temporary table
sp_helpindex2 to show included columns (2005+) and filtered indexes (2008) which are not shown by sp_helpindex
Yet Another Stored Procedure vs. Ad-hoc Query Discussion?
Geek City: How SQL Server Detects the Correct Versioned Rows
SQLIOSim is "NOT" an I/O Performance Tuning Tool
Forensic Analysis of a SQL Server 2005 Database Server

Non DB tech
Microsoft Live Search Gains Market Share
Unit Testing with Silverlight
Using Spring.NET and NHibernate with ASP.NET MVC
Enterprise Library 4.0 Community Technology Preview
Hanselminutes Podcast 107 - Digital Photography Explained (for Geeks) with Aaron Hockley
How do Extension Methods work and why was a new CLR not required?
Core War: Two Programs Enter, One Program Leaves
How to set an IIS Application or AppPool to use ASP.NET 3.5 rather than 2.0
Let That Be a Lesson To You, Son: Never Upgrade.
Silverlight 2 DIGG Sample Part I
Silverlight 2 DIGG Sample Part II
Silverlight 2 DIGG Sample Part III
Google Developer Podcast: Picasa Web and Google Gears for Mobile
Ted Neward: On Java and .NET, Software Academics versus Practioners, General Purpose Programming Languages
Whirlwind 7: What's New in C# 3 - Lambda Expressions
Google To Launch BigTable As Web Service To Compete With Amazon's SimpleDB

Non tech
SMCB: Charles Manson Pulls a Radiohead
Possibly the best name ever.
Thank God for Torrents (Pic)

Database Refactoring Interview With Scott W. Ambler

Since I am doing scrum and other agile methodologies myself I decided to interview the authority on agile in the database world.

Scott W. Ambler is the Practice Leader Agile Development for IBM Rational

You can find more about Scott here: http://www.ibm.com/software/rational/bios/ambler.html

I did not feel like duplicating the content here so you can read the interview here: Database Refactoring Interview With Scott W. Ambler

SQL Champ - A Quiz to Prove Your SQL Knowledge

In case you are bored CodeProject has a small SQL test (7 questions)

Take the test here: SQL Champ - A Quiz to Prove Your SQL Knowledge

And did you get everything correct?

And yes they wrote Knowlege instead of Knowledge :-)

Not An April Fool’s Joke: SQL Server 2000 Mainstream Support Ends In A Week

April 1st 2008 is the day on which support for SQL Server 2000 ends (and it is also my birthday)


SQL 2000 SP4 - currently supported; support ends on either 12 months after SP5 is released or on 4/8/2008 whichever comes first. Extended support under which you get only break-fix assistance via Premier ends on 4/9/2013. I am not aware of release date for SP5 so far.

SQL 2005 SP2 - currently supported; support ends on either 12 months after SP3 is released or on 4/12/2011 whichever comes first. Extended support under which you get only break-fix assistance via Premier ends on 4/13/2016


More details can be found here: http://blogs.msdn.com/sqlreleaseservices/archive/2007/07/12/sql-server-support-lifetimes-customer-actions.aspx

And here:
http://support.microsoft.com/lifecycle/?p1=2852

Links Of The Week 20080330

Who needs SQL links when you have this gem?

Check out (grand)ma in the background, she doesn't miss a beat.

How To Use COALESCE And NULLIF To Prevent Updating Columns When A Parameter IS NULL Or A Default Value

A variation of this question popped up twice in the SQL programming newsgroup since yesterday, this means it is time for a blogpost.
Let's say you have a proc like this

CREATE PROC prUpdateTable
@Salesman int = -1
AS

..........

If the user calls the proc like this exec prUpdateTable null then @Salesman will be null, if the user calls the proc like this exec prUpdateTable then the value of @Salesman will be -1. In both of this cases you don't want to change the value of the column. What can you do?
You can use a combination of NULLIF and COALESCE to handle this. Your update statement would look like this

UPDATE table
SET Column = COALESCE(NULLIF(@variable,-1),Column)

Here is some code to demonstrate that

CREATE TABLE #foo (id int,salesman int)
INSERT #foo VALUES(1,1)
INSERT #foo VALUES(2,1)
INSERT #foo VALUES(3,1)
INSERT #foo VALUES(4,1)



SELECT * FROM #foo
-------------
1 1
2 1
3 1
4 1

DECLARE @salesman int
SELECT @salesman = 5

--Column value will change to 5
UPDATE #foo
SET salesman = COALESCE(NULLIF(@salesman,-1),salesman)
WHERE ID =1

--Column value won't change
SELECT @salesman = -1
UPDATE #foo
SET salesman = COALESCE(NULLIF(@salesman,-1),salesman)
WHERE ID =2

--Column value won't change
SELECT @salesman = NULL
UPDATE #foo
SET salesman = COALESCE(NULLIF(@salesman,-1),salesman)
WHERE ID =3

--Column value will change to 3
SELECT @salesman = 3
UPDATE #foo
SET salesman = COALESCE(NULLIF(@salesman,-1),salesman)
WHERE ID =4

--And here is the output, as you can see when @salesman was -1 or NULL the table did not get updated
SELECT * FROM #foo
-------------
1 5
2 1
3 1
4 3

DROP TABLE #foo

As you can see only the first and the last update statement changed the value of the salesman column
Of course you would never do this if you were to update only one column, you would skip the update instead. If you have to update multiple columns then this is something you can use instead of writing a bunch of dynamic SQL or nested IF statements.

Technet Radio: A Look Inside SQLCAT (SQL Customer Advisory Team)

Channel9 has a podcast with the SQLCAT team.

On this episode of TechNet Radio, We learn more about SQL CAT – the SQL Customer Advisory Team. With Launch of SQL 2008 on the way, the SQL CAT team has been working hard preparing lists of best practices, recommendations, technical whitepapers and technical end-to-end case studies on customer implementations.

Listen to it here: http://channel9.msdn.com/ShowPost.aspx?PostID=392382#392382

Links Of The Week 20080322

Here are the links for this week

Database
Programming Policy-Based Management with SMO - Part 1 - Intro
Programming Policy-Based Management with SMO - Part 2 - Conditions
Programming Policy-Based Management with SMO - Part 3 - ExpressionNode and Policy
Programming Policy-Based Management with SMO - Part 4 - Introducing ObjectSets
Programming Policy-Based Management with SMO - Part 5 - TargetSets and TargetSetLevels
Programming Policy-Based Management with SMO - Part 6 - Categories, Subscriptions, Wrapup
How It Works: SQL Server 2005 I/O Affinity and NUMA Don't Always Mix
Database Programming: The String Concatenation XML Trick, Sans Entitization
Database Programming: The String Concatenation XML Trick, Finalized
SQL Server 2008: Interesting Full-Text Dynamic Management Function
Minimal Logging changes in SQL Server 2008 (part-1)
More about sparse columns and column_sets
Which to use: "<>" or "!="?
Hanselminutes #105 - Rocky Lhotka on Data Access Mania, LINQ and CSLA.NET
geekSpeak Recording: Extending SQL Server Integration Services with Reza Madani

Non DB tech
Unit Testing for Silverlight...
IronPython and the DLR march on
The Weekly Source Code 21 - ASP.NET MVC Preview 2 Source Code
Hanselminutes Podcast 104 - Dave Laribee on ALT.NET
The First Rule of Programming: It's Always Your Fault
The Weekly Source Code 20 - A Web Framework for Every Language
It’s common sense, stupid: How Not to Evangelize Unit Testing
Getting LINQ to SQL and LINQ to Entities to use NOLOCK
Google's Design Guidelines
Microsoft give an awesome response to the guy whose XBox was cleaned
Joe Duffy and Igor Ostrovsky: Parallel LINQ under the hood
FLOSS Weekly 27: Ward Cunningham

Non tech
TheGoogle.com - a Google offering for older adults
Open Letter to Comcast: STAY OUT OF MY COMPUTER!
The Laptop Cat [Pic]
Questions on Bear Stearns buyout - shareholders want answers on how the deal was arranged, and gained government approval and financing, all in a few hours, and seemingly without alternative bidders.

SOT: I found a new 'worst query'

Before you ask, SOT means Slightly Off Topic :-())

Take a look at Hi, I need help on simplyfying this Update query!

I don't even want to count the nested SELECTS, The query is 688 lines long, somehow schadenfreude enters my mind.

Now where does the update come into the picture?

What tastes better, Microsoft SQL Server 2005 or Oracle 11g?

Find out here: http://www.databasetastechallenge.com/landing/index.html
You can win some cool prizes. the video is very funny

Links Of The Week 20080317

Here are the links for this week

Database
TechNet Radio: SQL 2008 Part 2 of 2: Management, Troubleshooting and Throttling
SQL Server: XQuery/XPath, Retrieval Functions
sp_send_dbmail in a transaction with @query causes unresolvable deadlock
New Features Announced In SQL Server 2008
SQL Down Under show 35 - Roger Doherty - SQL Server 2008 for Database Developers
Applying SQL Server Service Packs and HotFixes
Database Programming: The String Concatenation XML Trick Revisited (Or, Adam Is Right, But We Can Fix It)
Geek City: Nonclustered Index Keys
Sybase iAnywhere Unveils Advantage Database Server 9.0
SQL Server Integration Services and Clustering - confguration gotcha to ensure SSIS works with failover of cluster!

Non DB tech
How the BBC rendered a spinning globe in 1985
I wrote Super Pac-Man: More confessions of an ex-Atari employee
From BFS to ZFS: past, present, and future of file systems
LINQPad.net - So Great! So Great!
.NET Rocks! #324 - Emre Kiciman on AjaxView
Mashups with SyndicationFeed and LINQ
Microsoft Research Offers Behind-the-Scenes Look at Future of Computing
IBM Researchers Develop World’s Tiniest Nanophotonic Switch to route optical data between cores in future computer chips
Getting up to speed with ASP.NET and the 3.5 Extensions
Using Unity and the ASP.NET MVC Preview 2
PowerShell Plus is now official!
List Of .NET Dependency Injection Containers (IOC).
The Weekly Source Code 19 - LINQ and More What, Less How
You Know, There's a Much Easier Way...

Non tech
Man had $12,000 in debts, repaid $15,000 over 8 yrs, still owes $12,200. Credit card industry stopped him from testifying to congressional panel
XKCD on mythbusters...
JP Morgan "buys" Bear Stearns for $2 a share, Fed flips the actual bill
2008-03-11, Jim Cramer: "No! No! No! Bear Stearns is not in trouble. If anything, they’re more likely to be taken over. Don’t move your money from Bear."
English is our language [PIC]

6th Annual Financial Services Developer Conference

I went to the 6th Annual Financial Services Developer Conference today in New York City. This year’s focus was on High Performance Computing (HPC). I must say that I have enjoyed this conference a lot. It seems that SQL Server is very prevalent on the street. The OLAP capabilities of SQL Server are making Sybase slowly disappear from Wall Street. Financial firms are doing some very interesting stuff; there is a hedge fund which records an earnings call, runs it through some voice stress analysis software and based on the outcome of that will short or long the stock. You should hear the stories of how financial firms handle IT, the innovation is happening in the financial markets. I also saw a couple of very cool WPF applications. Check out the demos from Lab49: http://www.lab49.com/insidethelab/demos
Visit http://www.financialdevelopers.com/ to download The Silverlight 2 Retail Financial Services Demonstrator



Carl Franklin from Dotnetrocks recorded a podcast at the event, this podcast was about concurrency and how to handle multi-core programming. You should definitely check it out when it becomes available on dotnetrocks. If you are interested about PLINQ and concurrent programming with .NET then visit this site: http://msdn2.microsoft.com/en-us/concurrency/default.aspx



Tomorrow there are three sessions at the same time that I want to attend
Software Engineering with VSTS
Parallelize you .Net applications with parallel extensions to the .NET framework.
Useful evolution: Programming the New features in SQL Server 2008.

I have my blackcrackberry with me so if you are attending the event tomorrow then send me an email at FirstName.LastName@dowjones.com and maybe we can discuss about this event during lunch.

StrataFrame Or CSLA.NET, Which Framework Would You Use?

If you would have to choose between StrataFrame or CSLA.NET, which one would you pick?
The team I am part of at work is trying to pick a framework for new development and they like these two the best. Are these two something you would pick or do you know a better one?

CSLA.NET: http://www.lhotka.net/cslanet/
StrataFrame: http://www.strataframe.net/

Also keep in mind that while CSLA.NET is free, StrataFrame is not.

Video: SQL Server Data Services and the Future of Data in the Cloud

Channel 9 has a 45 minute video with Dave Campbell about SQL Server Data Services. maybe this video will answer some of the question you might have about SQL Server Data Services



Watch it here: http://channel9.msdn.com/ShowPost.aspx?PostID=388698#388698

Microsoft Announces SQL Server Data Services

What is Microsoft SQL Server Data Services?
SQL Server Data Service (SSDS) is a highly scalable and cost-effective on-demand data storage and query processing web service. It is built on robust SQL Server technologies and helps guarantees a business-ready service level agreement covering high availability, performance and security features. Microsoft SSDS is accessible using standards based protocols (SOAP, REST) for quick provisioning of on-demand data-driven & mashup applications.

How does Microsoft SQL Server Data Services differ from traditional on premise relational SQL Server Database?
SQL Server Data Services is a storage and query processing utility providing mega scale, high availability, reliability, and geo-distributed data services in the Cloud. Customers use the service on-demand, with no up-front cost. It eliminates the initial investment in hardware and software and the on-going cost for storage administration, scale maintenance. Developers and Service providers can quickly run their on-demand applications with minimal infrastructure cost.

What can I do with SQL Server Data Services?
Customers can use SQL Server Data Services to store virtually any amount of data in the Cloud. They can query and modify data as required by the specific business scenarios. SQL Server Data Services support standards-based REST and SOAP interfaces designed to work with any Internet-development toolkit. The primary wire format is XML. Developers and service providers can quickly run on-demand applications with ease. The data has flexible schema which can be modified dynamically by the application. The data is provided with high availability and reliability virtually anywhere, anytime.

Rest of the FAQs here: http://www.microsoft.com/sql/dataservices/faq.mspx
Main SQL Server Data Services site here: http://www.microsoft.com/sql/dataservices/default.mspx

In SQL Server 2008 The CONVERT function is enhanced to allow conversions between binary and character hexadecimal values

SQL Server 2008 CTP 6 has enhanced the convert function when you do conversion between binary and character hexadecimal values
There are 3 styles:
Style 0 works the same as on 2005 it converts binary to varchar, if you have 0x64656E6973 then you will get 'denis'
Style 1 converts binary to varchar but the values stay the same, if you have 0x64656E6973 you will get '0x64656E6973'
Style 2 strips the 0x but leaves the rest of the values, if you have 0x64656E6973 you will get '64656E6973'

Try it out

When you run this

SELECT CONVERT(varbinary(5),'denis')

The output will be this 0x64656E6973. Now we can use that in the select statements below

SELECT CONVERT(varchar(18), 0x64656E6973, 0) AS 'Style 0' --denis
SELECT CONVERT(varchar(18), 0x64656E6973, 1) AS 'Style 1' --0x64656E6973
SELECT CONVERT(varchar(18), 0x64656E6973, 2) AS 'Style 2' --64656E6973

When you run the code above on SQL Server 2005 all 3 select statements return 'denis'

It is all in Books On Line for CTP 6, it would have been nice if they also included the sys.dm_tran_commit_table dmv or the sys.dm_exec_trigger_stats dmv. I have no clue what the sys.dm_tran_commit_table dmv is supposed to do :-(

46 New Dynamic Management Views In SQL Server 2008 CTP6

Remember the 33 new DMVs in SQL Server 2008 post? I was just poking around in SQL Server 2008  CTP6 and wanted to know if there were any new Dynamic Management Views since CTP5. There are 13 new DMVs in CTP6 and there are a total of 45 new Dynamic Management Views compared to SQL server 2005. The list of them is below, I think their names make clear what the dmv is supposed to do (or at least I think I know)

dm_audit_actions
dm_audit_class_type_map
dm_cdc_errors
dm_cdc_log_scan_sessions
dm_cryptographic_provider_algorithms
dm_cryptographic_provider_keys
dm_cryptographic_provider_properties
dm_cryptographic_provider_sessions
dm_database_encryption_keys
dm_db_mirroring_auto_page_repair
dm_db_mirroring_past_actions
dm_db_persisted_sku_features
dm_exec_procedure_stats
dm_exec_trigger_stats
dm_filestream_oob_handles
dm_filestream_oob_requests
dm_fts_fdhosts
dm_fts_index_keywords
dm_fts_index_keywords_by_document
dm_fts_outstanding_batches
dm_fts_parser
dm_os_dispatcher_pools
dm_os_dispatchers
dm_os_memory_brokers
dm_os_memory_node_access_stats
dm_os_memory_nodes
dm_os_nodes
dm_os_process_memory
dm_os_spinlock_stats
dm_os_sys_memory
dm_resource_governor_configuration
dm_resource_governor_resource_pools
dm_resource_governor_workload_groups
dm_server_audit_status
dm_sql_referenced_entities
dm_sql_referencing_entities
dm_tran_commit_table
dm_xe_map_values
dm_xe_object_columns
dm_xe_objects
dm_xe_packages
dm_xe_session_event_actions
dm_xe_session_events
dm_xe_session_object_columns
dm_xe_session_targets
dm_xe_sessions



The 13 DMVs listed below are new in CTP6

dm_audit_actions
dm_audit_class_type_map
dm_db_persisted_sku_features
dm_exec_procedure_stats
dm_exec_trigger_stats
dm_fts_fdhosts
dm_fts_index_keywords
dm_fts_index_keywords_by_document
dm_fts_outstanding_batches
dm_fts_parser
dm_os_dispatchers
dm_os_memory_node_access_stats
dm_server_audit_status

If you want to find out what the names of all the new DMVs are by running a query on your SQL Server 2008 box and you don't have a linked server to a SQL Server 2005 box then don't worry I have prepared the query below for you.

 
SELECT * FROM sysobjects
WHERE name LIKE 'dm_%'
AND name NOT IN('dm_os_hosts',
'dm_exec_cached_plan_dependent_objects',
'dm_os_memory_allocations',
'dm_os_loaded_modules',
'dm_db_task_space_usage',
'dm_os_memory_objects',
'dm_os_schedulers',
'dm_os_threads',
'dm_exec_requests',
'dm_db_missing_index_columns',
'dm_repl_tranhash',
'dm_qn_subscriptions',
'dm_db_session_space_usage',
'dm_exec_query_optimizer_info',
'dm_tran_top_version_generators',
'dm_os_waiting_tasks',
'dm_exec_background_job_queue',
'dm_exec_cursors',
'dm_db_missing_index_details',
'dm_clr_properties',
'dm_os_sublatches',
'dm_exec_query_memory_grants',
'dm_tran_current_snapshot',
'dm_io_virtual_file_stats',
'dm_exec_xml_handles',
'dm_os_wait_stats',
'dm_exec_text_query_plan',
'dm_broker_connections',
'dm_os_stacks',
'dm_os_ring_buffers',
'dm_db_missing_index_groups',
'dm_exec_cached_plans',
'dm_exec_sessions',
'dm_broker_forwarded_messages',
'dm_os_memory_clerks',
'dm_repl_articles',
'dm_fts_memory_buffers',
'dm_fts_index_population',
'dm_tran_current_transaction',
'dm_os_child_instances',
'dm_exec_connections',
'dm_exec_background_job_queue_stats',
'dm_fts_active_catalogs',
'dm_tran_database_transactions',
'dm_os_memory_cache_clock_hands',
'dm_repl_schemas',
'dm_db_mirroring_connections',
'dm_db_index_operational_stats',
'dm_db_partition_stats',
'dm_io_pending_io_requests',
'dm_os_memory_cache_entries',
'dm_os_virtual_address_dump',
'dm_tran_transactions_snapshot',
'dm_exec_query_plan',
'dm_os_memory_cache_hash_tables',
'dm_exec_query_stats',
'dm_clr_tasks',
'dm_os_worker_local_storage',
'dm_db_index_usage_stats',
'dm_db_index_physical_stats',
'dm_os_buffer_descriptors',
'dm_tran_active_snapshot_database_transactions',
'dm_tran_active_transactions',
'dm_db_file_space_usage',
'dm_broker_activated_tasks',
'dm_broker_queue_monitors',
'dm_os_memory_cache_counters',
'dm_tran_session_transactions',
'dm_clr_appdomains',
'dm_exec_sql_text',
'dm_os_memory_pools',
'dm_os_latch_stats',
'dm_io_backup_tapes',
'dm_fts_memory_pools',
'dm_os_sys_info',
'dm_tran_locks',
'dm_exec_query_transformation_stats',
'dm_exec_query_resource_semaphores',
'dm_repl_traninfo',
'dm_db_missing_index_group_stats',
'dm_fts_population_ranges',
'dm_os_performance_counters',
'dm_os_workers',
'dm_io_cluster_shared_drives',
'dm_os_tasks',
'dm_exec_plan_attributes',
'dm_tran_version_store',
'dm_os_cluster_nodes',
'dm_clr_loaded_assemblies')

Enjoy exploring these DMVs

 

 

Microsoft SQL Server 2008 Feature Pack CTP, February 2008

The Feature Pack is a collection of stand-alone install packages that provide additional value for SQL Server 2008. It includes the latest versions of:

Redistributable components for SQL Server 2008
Add-on providers for SQL Server 2008
Backward compatibility components for SQL Server 2008

Microsoft SQL Server 2008 Native Client
Microsoft SQL Server 2008 Native Client (SQL Native Client) is a single dynamic-link library (DLL) containing both the SQL OLE DB provider and SQL ODBC driver. It contains run-time support for applications using native-code APIs (ODBC, OLE DB and ADO) to connect to Microsoft SQL Server 2000, 2005, or 2008. SQL Native Client should be used to create new applications or enhance existing applications that need to take advantage of new SQL Server 2008 features. This redistributable installer for SQL Native Client installs the client components needed during run time to take advantage of new SQL Server 2008 features, and optionally installs the header files needed to develop an application that uses the SQL Native Client API.


Microsoft SQL Server 2005 Backward Compatibility Components
The SQL Server Backward Compatibility package includes the latest versions of Data Transformation Services 2000 runtime (DTS), SQL Distributed Management Objects (SQL-DMO), Decision Support Objects (DSO), and SQL Virtual Device Interface (SQLVDI). These versions have been updated for compatibility with both SQL Server 2005 and SQL Server 2008 and include all fixes shipped through SQL Server 2000 SP4 and SQL Server 2005 SP2.

Microsoft SQL Server 2008 Reporting Services Add-in for Microsoft SharePoint Technologies
Microsoft SQL Server 2008 Reporting Services Add-in for SharePoint Technologies allows you to take advantage of SQL Server 2008 report processing and management capabilities in SharePoint integrated mode. This version also includes data-driven subscriptions. The download provides a Report Viewer web part, web application pages, and support for using standard Windows SharePoint Services or Microsoft Office SharePoint Services.

Get it all here: http://www.microsoft.com/downloads/details.aspx?FamilyId=D68DE3C9-60A9-49C9-A28C-5C46BBC3356F&displaylang=en

SQL Server 2008 February CTP is now available for public download

SQL Server 2008 February CTP is finally here, this CTP is supposed to be feature complete.

Download the February CTP from the Download Center!

What's New in SQL Server 2008 February CTP?

Enter to win an Xbox in the SQL Server 2008 February CTP Bug Bash!

If Airplanes Were Made By Using Scrum/Agile

So I have been doing Scrum for a while now, I came across this video and thought it was hilarious. When you do Scrum you develop by using sprints, a sprint is usually between 2 and 4 weeks and at the end of the sprint you should have something working that you can show to the client/business unit

Watch the video



If someone is interested in a high level post about Scrum then leave a comment and I will post something next week

Weekly Links 20080217

I decided to do a weekly links post with interesting SQL Server stuff, I also include a bunch of funny things at the bottom.

Technical Stuff:
Bob Beauchemin has two post that deal with Spatial Data: Using SQL Server 2008 spatial and the Virtual Earth map control - 1 and Using SQL Server 2008 spatial and the Virtual Earth map control - 2. The Spatial Data posts are more for people who do front end as well as back end development.
Two Interesting articles by the PSS SQL Server Engineers
How it Works: SQL Server Per Query Degree Of Parallelism Worker Count(s) and How It Works: SQL Server 2005 Connection and Task Assignments
Kalen Delany's Geek City: String Statistics post. My own Functions That Are Not Often Used: SIGN from sqlblog. Amdahl’s Law by Linchi Shea

If that is not enough for you there are also a bunch of SQL Server 2008 Webcasts available as well as a bunch of SQL Server 2008 Videos.

The non technical stuff:
Like Father Like Grandson
I had friends...
Pronounce this if you can
Peek into offices at LinkedIn, Flickr & Facebook

HTTP Endpoints deprecated In SQL Server 2008, Just Like Notification Services

Just like Notification Services HTTP Endpoints have been deprecated in SQL Server 2008. Bob Beauchemin's Blog has more info here: HTTP Endpoints to be deprecated in SQL Server 2008

SQL Server 2008 Whitepapers

General
SQL Server 2008 Product Overview
Microsoft® SQL Server™ provides a policy-based system for managing one or more instances of SQL Server along with tools for performance monitoring, troubleshooting, and tuning that enable administrators to more efficiently manage their databases and SQL Server instances.
Focus: What's New
Audience: High-level

Business Intelligence
An Introduction to New Data Warehouse Scalability Features in SQL Server 2008
With the 2008 release, SQL Server makes a major advance in scalability for data warehousing. It meets the data warehouse needs of the largest enterprises more easily than ever. SQL Server 2008 provides a range of integrated products that enable you to build your data warehouse, and query and analyze its data. These include the SQL Server relational database system, Analysis Services, Integration Services, and Reporting Services. This paper introduces the new performance and manageability features for data warehousing across all these components. All these features contribute to improved scalability.
Audience: High-level

SQL Server 2008 Analysis Services Overview
Microsoft SQL Server 2008 Analysis Services builds on the value delivered with the significant investments in Analysis Services 2005 around scalability, advanced analytics and Microsoft Office interoperability. This paper covers a high-level look at the key Analysis Services features available in SQL Server 2008.
Audience: High-level

Predictive Analysis with SQL Server 2008
This 10-page paper explores the complete and intuitive set of data mining tools offered in Microsoft SQL Server 2008: Predictive Analysis for All Users, Insight at Every Step of the Data Lifecycle, and Data Mining Awareness in Every Application.
Audience: High-level

Business Intelligence in SQL Server 2008
This 10-page paper includes: Introduction, Unifying Data Storage and Access, Building and Managing Sophisticated BI Solutions, Extending the Reach of Your BI Solution, and Conclusion.Focus: Business Intelligence
Audience: High-level

SQL Server 2008 Reporting Services Whitepaper
This paper discusses how Microsoft SQL Server 2008 Reporting Services provides a complete server-based platform that is designed to support a wide variety of reporting needs including managed enterprise reporting, ad-hoc reporting, embedded reporting, and web based reporting to enable organizations to deliver relevant information where needed across the entire enterprise.Focus: Reporting Services
Audience: High-level

An Introduction to SQL Server 2008 Integration ServicesThis paper discusses the challenges that face businesses that rely on data integration technologies to provide meaningful, reliable information to maintain a competitive advantage in today’s business world. It discusses how SQL Server 2008 Integration Services (SSIS) can help Information Technology departments meet data integration requirements in their companies. Real-world scenarios are included.Focus: Integration Services
Audience: High-level Data Warehousing

Choosing SQL Server 2008 for Data Warehousing
This 10-page paper discusses: The State of Data Warehousing, The Realities of Data Warehousing Today, Putting the Technology Set Together, and Rounding It Out.Focus: Data Warehousing
Audience: High-level


Application Development/Data Programmability
Microsoft SQL Server 2008 and Microsoft Data Platform Development
This 10-page paper includes: Improved Productivity through ADO.NET Entity Framework, LINQ, and Visual Studio, Comprehensive Data Platform, and Scalable Data-Centric Solutions.
Focus: Developer Productivity
Audience: Technical

What's New for XML in SQL Server 2008?
A 16-page paper that discusses how Microsoft SQL Server 2008 builds on the extensive support for XML by extending support for XML schema validation and XQuery, and by enhancing the xml data type.
Focus: XML
Audience: Technical

Managing Unstructured Data with SQL Server 2008
This 11-page paper discusses how Microsoft SQL Server™ 2008 provides a flexible solution for storing unstructured data and combining it with relational data to build comprehensive solutions that encompass the full range of data across an organization.
Focus: Unstructured Data
Audience: Technical

SQL Server 2008: Delivering Location Intelligence with Spatial Data
This 10-page paper discusses how the new spatial support in Microsoft SQL Server™ 2008 can help you to make better decisions through visual analysis of location data that can be stored and manipulated in a SQL Server database.
Focus: Spatial Data
Audience: Mid-level

Online Transaction Processing/Data Management
SQL Server 2008 Security Overview for Database Administrators
SQL Server 2008 is secure by design, default, and deployment. Microsoft is committed to communicating information about threats, countermeasures, and security enhancements as necessary to keep your data as secure as possible. This paper covers some of the most important security features in SQL Server 2008. It tells you how, as an administrator, you can install SQL Server securely and keep it that way even as applications and users make use of the data stored within.
Focus: Security
Audience: Mid-level

Online Transaction Processing in SQL Server 2008
This 10-page paper includes: Introduction, Scale and Performance, High Availability, Security, and Manageability.
Focus: OLTP
Audience: High-level

SQL Server 2008 Manageability
This paper discusses how Microsoft SQL Server 2008 builds on the management capabilities of previous versions and provides a variety of tools and frameworks to help you manage your enterprise data environment.
Focus: Manageability
Audience: High-level

SQL Server 2008 Performance and Scale
This paper discusses how Microsoft SQL Server 2008 incorporates the tools and technologies that are necessary to implement relational databases, reporting systems, and data warehouses of enterprise scale, and provides optimal performance and responsiveness.
Focus: Scale and Performance
Audience: Mid-level

Server Consolidation
Server Consolidation with SQL Server 2008
This 10-page paper includes: Introduction, Flexibility, Manageability, Scalability and Performance, and Conclusion.
Focus: Server Consolidation
Audience: High-level

This is just awesome

SQL Server 2008: When Was The Server Last Started?

SQL Server 2008 has an additional column in the sys.dm_os_sys_info Dynamic Management View. The name of the column is sqlserver_start_time and it will be the time SQL Server was last restarted

SELECT sqlserver_start_time FROM sys.dm_os_sys_info