Dot Net Sample Chapters And Resources: April 2008

Thursday, April 24, 2008

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?

Wednesday, April 23, 2008

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

Tuesday, April 22, 2008

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

Monday, April 21, 2008

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.

Thursday, April 17, 2008

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

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

Tuesday, April 15, 2008

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

Monday, April 14, 2008

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

Monday, April 7, 2008

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

Tuesday, April 1, 2008

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

Dot Net Sample Chapters And Resources