Less Than Dot A New community Site Has Been Launched

Myself and a bunch of friends have been working on Less Than Dot for a while now. The site has a forum, blogs and a wiki. More info why we started Less Than Dot and who we are can be found here: http://www.lessthandot.com/aboutus.php

Since I am mostly a SQL guy, I wrote a collection of SQL Server hacks. This collection of SQL hacks is available on the Wiki, right now we have 8 sections and between 70 and 80 hacks. Ideally we will have more hacks and we will also have a SQL admin hacks page in the future.


SQL Server Hacks Sections
* 1 NULLS
* 2 Dates
* 3 Sorting, Limiting Ranking, Transposing and Pivoting
* 4 Handy tricks
* 5 Pitfalls
* 6 Query Optimization
* 7 Undocumented but handy
* 8 Usefull Admin stuff For The Developer


Below are direct links to all the hacks on the wiki, you can also get a list of all the hacks on the wiki itself here: SQL Server Programming Hacks


NULLS

Testing for NULL values
Use COALESCE To Return First Non Null Value
How To Check If Any, ALL Or No Parameters Have A NULL Value
Return first non null value
Return Null If A Value Is A Certain Value
Concatenation with nulls
NOT IN and nulls

Dates

First and last days of month, quarter, week
Epoch date
Get Datetime Without Time
Date Ranges Without Loops (numbers table)
Calculate birthday in years
Formatting Dates
Calling Stored Procedures With Datetime Parameters
Trouble With ISDATE And Converting To SMALLDATETIME

Sorting, Limiting,Ranking, Transposing and Pivoting

Return Top N Rows
Dynamic top
Sorting Numbers Stored In A Varchar Column
How To Use ROW_NUMBER() In A WHERE Clause
Row To Column (PIVOT)
Column To Row (UNPIVOT)
Split A String By Using A Number Table
Concatenate Values From Multiple Rows Into One Column
Concatenate Values From Multiple Rows Into One Column Ordered
Rank
Dense rank
Rownumber
Sort certain values last
Returning The Maximum Value For A Row

Handy tricks

Five ways to return all rows from one table which are not in another table
Order IP Addresses
Data formatting dates
Data formatting SSN
6 Different Ways To Get The Current Identity Value
Use XACT_ABORT to roll back non trapable error transactions
Random Sorting
Sort Values Ascending But NULLS Last
Adding Leading Zeros To Integer Values
How do I format money/decimal data with commas?
Find Out How Many Occurrences Of A Substring Are In A String
Ten SQL Server Functions That You Have Ignored Until Now
Use the *1 trick to do math with two varchar values
Store The Output Of A Stored Procedure In A Table Without Creating A Table
Find all The Rows Where Any Of The Columns Is Null Or Zero Or Both
Three Ways To Display Two Counts From a Table Side By Side
Finding duplicates across columns
Use REPLACE To eliminate unwanted characters

Pitfalls

Update triggers
Integer math
Identity Values And Triggers
Use XACT_STATE() To Check For Doomed Transactions
Three differences between COALESCE and ISNULL
Non deterministic functions and nullif
Subquery typo with using in
ISNUMERIC Trouble
Case Without Else

Query Optimization

Case sensitive search
Functions on left side of the operator
Query Optimizations With Dates
Optimization: Set Nocount On
Don’t use arithmetic operators on a column in the where clause
Don’t use * but list the columns

Undocumented but handy

xp_getnetname
xp_fileexist
xp_dirtree
xp_subdirs
xp_getfiledetails
xp_fixeddrives
Sp_tempdbspace
xp_enumdsn
xp_enumerrorlogs
Some Undocumented DBCC Commands
sp_MSforeachtable
sp_MSforeachDB

Usefull Admin stuff For The Developer

sys.dm_exec_sessions
Find all tables that contain a certain column
Find All Tables Without Triggers In SQL Server
Find all Primary and Foreign Keys In A Database
Find Out If A Table Has An Identity Column
Use the sys.dm_db_index_usage_stats dmv to check if indexes are being used

Hopefully you will like the site and find some good content, if you have a question then don't hesitate to ask it in a forum.

Google Hosts Popular JavaScript Libraries script.aculo.us, Prototype, jQuery, Dojo and more

If you've ever wanted to use a JavaScript library like script.aculo.us, Prototype, jQuery or Dojo, but you couldn't upload its files to a site or you found it difficult to manage all the different versions, Google hosts them for you.

Check it out here: AJAX Libraries API

Now if they also had Ext JS things would be good

ASP.NET MVC Preview 3 Released

ASP.NET MVC Preview 3 has been released.

Download the ASP.NET MVC Preview 3

Much more(videos, tutorials) here: http://www.asp.net/mvc/

Microsoft Source Analysis for C# Announced By Microsoft

Microsoft Source Analysis for C# has been announced. Here is what I found on the Microsoft site:

This tool is known internally within Microsoft as StyleCop, and has been used for many years now to help teams enforce a common set of best practices for layout, readability, maintainability, and documentation of C# source code.

Source Analysis is similar in many ways to Microsoft Code Analysis (specifically FxCop), but there are some important distinctions. FxCop performs its analysis on compiled binaries, while Source Analysis analyzes the source code directly. For this reason, Code Analysis focuses more on the design of the code, while Source Analysis focuses on layout, readability and documentation. Most of that information is stripped away during the compilation process, and thus cannot be analyzed by FxCop.

The ultimate goal of Source Analysis is to allow you to produce elegant, consistent code that your team members and others who view your code will find highly readable.

More info can be found here: http://blogs.msdn.com/sourceanalysis/

Source Analysis for C# can be downloaded here: https://code.msdn.microsoft.com/Release/ProjectReleases.aspx?ProjectName=sourceanalysis.

LessThanDot - A New IT Community Coming To You June 1st

Some friends and I are launching a new IT Community site. This site will have a forum, blogs and a wiki. The co-founders are experts from many of the top forums around the internet, who also blog on professional blogs, such as http://sqlblog.com/ and http://weblogs.asp.net.. If you're even a semi regular user of places like Tek Tips, Google Groups, Microsoft Forums, Experts Exchange, LinuxQuestions and many others, you are likely to have been helped by one of the 30+ co-founders of LessThanDot - many of whom are top MVP's in their fields.

LessThanDot intends to cover all aspects of IT - from Web Developers to Sys Admins, from Architects to Data Management, IT Professionals (e.g. Project & Service management) to IT Students and Researchers, Desktop Developers to Enterprise Developers. And if it's not there - just ask.

There will be a "blow off some steam" forum where you can read fun stuff, tech rants, a tech news stream and more.

Unlike other fora we will accept questions from students, but instead of giving an answer we will direct you to where the answer can be found. After all you want to do some work yourself.

Right now you can see 3 screen shots here LessThanDot and make sure to come back June 1st.

SQL people that should be followed on twitter

A fellow blogger statisticsio has made a post: SQL people that I follow on twitter
If SQL is your thing I recommend checking out the link and subscribing to some of these people on twitter

And don't forget my Use Twitter To Stay On Top Of The .NET Programming News

Podcast: Brad Abrams and Omar Khan from Microsoft officially announce .NET 3.5 SP1 and Visual Studio 2008 SP1

Dotnetrocks has a podcast where Brad Abrams and Omar Khan from Microsoft officially announce .NET 3.5 SP1 and Visual Studio 2008 SP1, and detail all the new features and improvements!


Brad Abrams was a founding member of both the Common Language Runtime, and .NET Framework teams at Microsoft Corporation where he is currently the Group Program Manager for the UI Framework and Services team which is responsible for delivering the developer platform that spans both clients and web based applications as well as the common services that are available to all applications. Specific technologies owned by this team include ASP.NET, Atlas, and Windows Forms. Brad has been designing parts of the .NET Framework since 1998 when he started his framework design career building the BCL (Base Class Library) that ship as a core part of the .NET Framework. Brad was also the lead editor on the Common Language Specification (CLS), the .NET Framework Design Guidelines and the libraries in the ECMA\ISO CLI Standard. Brad has been deeply involved with the WinFX and Windows Vista efforts from their beginning. Brad co-authored Programming in the .NET Environment, and was editor on .NET Framework Standard Library Annotated Reference Vol1 and Vol2 and the Framework Design Guidelines.


Omar Khan is Product Unit Manager at Microsoft corporation responsible for running the Visual Web Developer product team. Since joining Microsoft in 1995, Omar has worked on several product releases, including Visual InterDev 1.0 , Visual InterDev 6.0, Visual Studio 2003, Visual Studio 2005, and Visual Studio 2008. Prior to joining Microsoft, Omar attended the University of Michigan - Ann Arbor from 1992-1995, majoring in a Bachelor's of Computer Science.

Listen to the podcast here: http://www.dotnetrocks.com/default.aspx?showNum=340
Download the Visual Studio 2008 and .NET Framework 3.5 Service Pack 1 Beta here: http://msdn.microsoft.com/en-us/vstudio/products/cc533447.aspx
Scott Guthrie wrote about it here: Visual Studio 2008 and .NET Framework 3.5 Service Pack 1 Beta

Check If xp_cmdshell is enabled on SQL Server 2005

How do you check from Transact SQL if xp_cmdshell has been enabled on SQL Server 2005?

Run the following code

USE master;
GO

EXEC SP_CONFIGURE 'show advanced option', '1';

RECONFIGURE WITH OVERRIDE;
EXEC SP_CONFIGURE;

You will see a long list but the one you want to check is at the bottom

Output:
name minimum maximum config_value run_value
----------------------------------------------------------
xp_cmdshell 0 1 1 1


here is the complete list

Ad Hoc Distributed Queries
affinity I/O mask
affinity mask
Agent XPs
allow updates
awe enabled
blocked process threshold
c2 audit mode
clr enabled
common criteria compliance enabled
cost threshold for parallelism
cross db ownership chaining
cursor threshold
Database Mail XPs
default full-text language
default language
default trace enabled
disallow results from triggers
fill factor (%)
ft crawl bandwidth (max)
ft crawl bandwidth (min)
ft notify bandwidth (max)
ft notify bandwidth (min)
index create memory (KB)
in-doubt xact resolution
lightweight pooling
locks
max degree of parallelism
max full-text crawl range
max server memory (MB)
max text repl size (B)
max worker threads
media retention
min memory per query (KB)
min server memory (MB)
nested triggers
network packet size (B)
Ole Automation Procedures
open objects
PH timeout (s)
precompute rank
priority boost
query governor cost limit
query wait (s)
recovery interval (min)
remote access
remote admin connections
remote login timeout (s)
remote proc trans
remote query timeout (s)
Replication XPs
scan for startup procs
server trigger recursion
set working set size
show advanced options
SMO and DMO XPs
SQL Mail XPs
transform noise words
two digit year cutoff
user connections
user options
Web Assistant Procedures
xp_cmdshell


When using sp_configure to change a setting, use the RECONFIGURE WITH OVERRIDE statement for the change to take immediate effect. Otherwise, the change takes effect after SQL Server is restarted.

So do you think deciding between a clustered or non clustered index is difficult? Enter the world of Sybase IQ my friend

I have been messing around with Sybase IQ for a bit now. Sybase IQ is a columnar databases; data is stored in columns not in rows. This makes retrieval very fast but inserts are slower than a traditional RDBMS.

You are looking at the data in your table, most of the queries are ranges queries but every now and then a query gets executed for exactly one row. How would you index this? This is a very tough decision, right? You have 2 types of indexes to choose from, and if the index is a composite (multi column) index then you also have to worry about which column to place first so that you get a lean mean index seek.

With Sybase IQ you have these indexes to choose from

The Default column index
For any column that has no index defined, or whenever it is the most effective, query results are produced using the default index. This structure is fastest for projections, but generally is slower than any of the three column index types you define for anything other than a projection. Performance is still faster than most RDBMSs since one column of data is fetched, while other RDBMSs need to fetch all columns which results in more disk I/O operations.

The Low_Fast (LF) index type
This index is ideal for columns that have a very low number of unique values (under 1,000) such as sex, Yes/No, True/False, number of dependents, wage class, and so on. LF is the fastest index in Sybase IQ.

The High_Group (HG) index type
The High_Group index is commonly used for join columns with integer data types. It is also more commonly used than High_Non_Group because it handles GROUP BY efficiently.

The High_Non_Group (HNG) index type
Add an HNG index when you need to do range searches.
An HNG index requires approximately three times less disk space than an HG index requires. On that basis alone, if you do not need to do group operations, use an HNG index instead of a HG index.

The Compare (CMP) index type
A Compare (CMP) index is an index on the relationship between two columns. You may create Compare indexes on any two distinct columns with identical data types, precision, and scale. The CMP index stores the binary comparison (<, >, or =) of its two columns.

The Containment (WD) index type
This index allows you to store words from a column string of CHAR and VARCHAR data.

The Date (DATE) index type
The Time (TIME) index type
The Datetime (DTTM) index type
Three index types are used to process queries involving date, time, or datetime quantities:

The JOIN Index (Linear joins and Star joins)
Join indexes usually provide better query performance than when table joins are first defined at query time (ad hoc joins). In many situations, however, you can gain optimal performance on joined columns without creating join indexes.

Yes, a lot more than two, Sybase IQ does not have clustered or non clustered indexes. You can specify clustered index but it will be ignored.

What do you think? Do we need more indexes in SQL Server? What about BitMap indexes?

What is Live Mesh?

Microsoft launched Live Mesh. So what is this Live Mesh thing?

Robert Scoble wrote about it here: Ray Ozzie delivers with Live Mesh

Channel 9 has two videos
Abolade Gbadegesin: Live Mesh Architecture
Ray Ozzie: Introducing Live Mesh

Start playing with it here: http://www.mesh.com


Have fun watching/reading/playing

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

Windows XP SP3 Released To Manufacturing

Windows XP SP3 will be released to the web (RTW) on April 29, 2008.

From the MSDN forum

Today we are happy to announce that Windows XP Service Pack 3 (SP3) has released to manufacturing (RTM). Windows XP SP3 bits are now working their way through our manufacturing channels to be available to OEM and Enterprise customers.



We are also in the final stages of preparing for release to the web (i.e. you!) on April 29th, via Windows Update and the Microsoft Download Center. Online documentation for Windows XP SP3, such as Microsoft Knowledge Base articles and the Microsoft TechNet Windows XP TechCenter, will be updated then. For customers who use Windows XP at home, Windows XP SP3 Automatic Update distribution for users at home will begin in early summer.



Thanks to everyone here who installed the public betas – you not only gave us detailed feedback but also helped each other out with timely troubleshooting. Through the beta program we found several important issues and were able to confirm some essential fixes. We couldn’t have done this without you.



We will still be monitoring this forum during the next few weeks in case you have more feedback about the release of Windows XP SP3.



On behalf of myself, Shashank Bansal and Windows Serviceability, many thanks.


Chris Keroack
Release Manager, Windows XP Service Pack 3
Windows Serviceability

There you have it.

Use Twitter To Stay On Top Of The .NET Programming News

Do you use twitter? If you don't you should, it is a great way to find out what is happening in the programming world. Below is a list of some well known programmers and their twitter URL. Start following them and you might learn a thing or two. And if you know of some other people then leave a comment and I will add them to the list.


Don Demsak
http://twitter.com/donxml

Keyvan Nayyeri
http://twitter.com/keyvan

Phil Haack
http://twitter.com/haacked

Miguel de Icaza
http://twitter.com/migueldeicaza

Shawn Wildermuth
http://twitter.com/TheADOGuy

Andrew Badera
http://twitter.com/andrewbadera

Michael Palermo
http://twitter.com/palermo4

Jeff Atwood
http://twitter.com/codinghorror

Scott Hanselman
http://twitter.com/shanselman

Jessy Libertry
http://twitter.com/jliberty

ALT.NET
http://twitter.com/altdotnet

Gavin Joyce
http://twitter.com/gavinjoyce

Sam Gentile
http://twitter.com/SamGentile

Dare Obasanjo
http://twitter.com/Carnage4Life

Chuck Boyce
http://twitter.com/chuckboycejr

Justice Gray
http://twitter.com/justice_gray

Rory Blyth
http://twitter.com/rory_blyth

John Lam
http://twitter.com/john_lam

Jason Massie
http://twitter.com/statisticsio

aspnetmvc
http://twitter.com/aspnetmvc

Paul Nielsen
http://twitter.com/PaulNielsen

Scott Koon
http://twitter.com/lazycoder


--------- added from comments --------
Rob Conery
http://twitter.com/robconery

Jon Galloway
http://twitter.com/jongalloway

Chad Myers
http://twitter.com/chadmyers

Jeremy Miller
http://twitter.com/jeremydmiller

Brad Wilson
http://twitter.com/bradwilson

Chris Bowen
http://twitter.com/chrisbowen

Dan Rigsby
http://twitter.com/DanRigsby

Aaron Lerch
http://twitter.com/AaronLerch

Jeff Moser
http://twitter.com/jeffmoser

Javier Lozano
http://twitter.com/lozanotek

Roy Osherove
http://twitter.com/RoyOsherove

Listen To Joel Spolsky(Joel On Software) And Jeff Atwood(Coding Horror) On The Stackoverflow Podcast

Jeff Atwood is starting a new site, it will be called stackoverflow.com

Listen to the first podcast with Joel Spolsky and Jeff Atwood

Get it here: http://www.stackoverflow.com/

Joel Spolsky wrote an article here: http://www.joelonsoftware.com/items/2008/04/16.html
And Jeff Atwood did the same here: http://www.codinghorror.com/blog/archives/001101.html

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 of 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

Podcast: Rocky Lhotka on CLSA.NET 3.5

Rocky is back on .NET Rocks! to talk about the latest iteration of his CSLA.NET Application Framework, as well as to talk about some .NET technologies that are no longer as necessary as they once were.

Rockford Lhotka is the author of numerous books, including the Expert Visual Basic .NET & C# Business Objects books. He is a Microsoft Software Legend, Regional Director, MVP and INETA speaker. Rockford speaks at many conferences and user groups around the world and is a columnist for MSDN Online. Rockford is the Principal Technology Evangelist for Magenic Technologies, one of the nation's premiere Microsoft Gold Certified Partners dedicated to solving today's most challenging business problems using 100% Microsoft tools and technology.

Download the show here: http://www.dotnetrocks.com/default.aspx?showNum=330

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 :-)

Screencast: What's new in C# 3 - Automatically Implemented Properties, Type Inference, Initializer

Bruce Kyle of Microsoft and Stuart Celarier of CheckFree explore the new languages features in C#. It's a whirlwind tour of the important language features since C# 1. Stuart describes the feature and why it is useful. But doesn't get into best practices nor suggested usages. Just the facts about the feature.

Whirlwinds are bite-sized webcasts, each is shorter than 15 minutes. You can start anywhere in the series to learn about the parts you're most interested in.
Part 5 is about automatically implemented properties, type inference, and initializers. These are features that can save you tons of typing. These features are part of C# 3 in .NET 3.5.

Other Whirlwind episodes: What's new in C# 2
Whirlwind 1 - Generics
Whirlwind 2 - Iterators
Whirlwind 3 - Partial types, Anonymous methods
Whirlwind 4 - Accessors, Static Classes, Nullable Types

What's new in C# 3
Whirlwind 5 - Automatically Implemented Properties, Type Inference, Initializer

ASP.NET MVC Source Code Now Available On CodePlex

Scott Guthrie announces the availability of the ASP.NET MVC source code on CodePlex

http://weblogs.asp.net/scottgu/archive/2008/03/21/asp-net-mvc-source-code-now-available.aspx