A blog about SQL Server, Books, Movies and life in general
Tuesday, April 15, 2008
Solutions for Common T-SQL Problems Wiki Launched
Monday, April 14, 2008
Use IDENT_CURRENT() to return the last identity value generated in all tables 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
Sunday, April 13, 2008
Links Of The Week 20080414
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]
Monday, April 07, 2008
Video: SQL Server Data Services Architecture
Watch it here: http://channel9.msdn.com/ShowPost.aspx?PostID=395843#395843
Saturday, April 05, 2008
Links Of The Week 20080405
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)
Wednesday, April 02, 2008
Database Refactoring Interview With Scott W. Ambler
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
Tuesday, April 01, 2008
SQL Champ - A Quiz to Prove Your SQL Knowledge
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
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
Sunday, March 30, 2008
Links Of The Week 20080330
Check out (grand)ma in the background, she doesn't miss a beat.
Friday, March 28, 2008
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.
Tuesday, March 25, 2008
Technet Radio: A Look Inside SQLCAT (SQL Customer Advisory 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
Saturday, March 22, 2008
Links Of The Week 20080322
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.
Thursday, March 20, 2008
SOT: I found a new 'worst query'
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?
Tuesday, March 18, 2008
What tastes better, Microsoft SQL Server 2005 or Oracle 11g?
You can win some cool prizes. the video is very funny
Monday, March 17, 2008
Links Of The Week 20080317
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]
Wednesday, March 12, 2008
6th Annual Financial Services Developer Conference
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
Sunday, March 09, 2008
StrataFrame Or CSLA.NET, Which Framework Would You Use?
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.
Friday, March 07, 2008
Video: SQL Server Data Services and the Future of Data in the Cloud
Watch it here: http://channel9.msdn.com/ShowPost.aspx?PostID=388698#388698
Wednesday, March 05, 2008
Microsoft Announces 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
Wednesday, February 27, 2008
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 :-(