A common problem face by inexperienced SQL developers is in forming joins with tables containing NULL values. Let's take the following ContactInfo & City tables as an example:
ContactInfo columns:
ContactId INT
ContactName VARCHAR(50)
CityId INT
ContactInfo Data: { (1, 'Nitin', 1), {2, 'Reddy', NULL) }
City columns:
CityId INT
CityName VARCHAR(50)
City Data: { (1, 'Dubai') }
When joining the ContactInfo and the City tables, using the SQL statement
select * from ContactInfo
join City on ContactInfo.CityId = City.CityId
you would get only one row in the result: { (1, 'Nitin', 1) }
This occurs because the join removes the rows with NULL values as the expression from the join condition (NULL = 1) evaluates to false.
To retrieve data with the null values, the query has to be modified to:
select * from ContactInfo
left outer join City on ContactInfo.CityId = City.CityId
Sunday, November 9, 2008
Saturday, November 8, 2008
Hosting Screencasts
If you've come up with a really cool screencast, hosting it would be a bit of a problem. Most video hosting services such as YouTube (the video upload feature on Blogger is, currently, very YouTube-like) limit the size of the video thus reducing the clarity, often making text unreadable.
However, there are services such as Vimeo and Screencast.com that are just perfect for sharing screencasts. They support much larger sizes for videos and are free to use. (Screencast.com limits you to 2GB of storage and 2GB of bandwidth for their free services)
However, there are services such as Vimeo and Screencast.com that are just perfect for sharing screencasts. They support much larger sizes for videos and are free to use. (Screencast.com limits you to 2GB of storage and 2GB of bandwidth for their free services)
Management Studio 2008 'dislikes' SQL 2005
The Microsoft SQL Server Management Studio 2008 included with the Microsoft SQL Server 2008 runs in a 'downgraded' mode when connecting to SQL Server 2005 & 2000, disabling the Intellisense. If you would like to have intellisense support with older versions of SQL Server, you could use the RedGate SQL Prompt.
Filestream storage in MS SQL Server 2008
You can store binary data to the filesystem using the Filestream attribute for a column with the Varbinary(max) data type. A standard Varbinary(max) column has a capacity of 2GB but using Filestream storage extends it to the file system volume size. Filestream data is stored in filestream file groups (they consist of data containers or file system directories). A backup of the database includes filestream data (use a partial backup that excludes the filestream file groups to avoid this). Using filestream storage also enables the use of Win32 interfaces to manipulate data. Deleting a row also removes the associated data from the filestream storage.
Filestream data is not encrypted and uses the system cache instead of the buffer pool. Filestream storage is not enabled by default.
The use of filestream storage is suggested for large (over 1MB) data; storing small amounts of data in the database provides better performance.
Note: When using failover clustering, the file groups must be on a shared storage medium.
Filestream data is not encrypted and uses the system cache instead of the buffer pool. Filestream storage is not enabled by default.
The use of filestream storage is suggested for large (over 1MB) data; storing small amounts of data in the database provides better performance.
Note: When using failover clustering, the file groups must be on a shared storage medium.
MS SQL Server 2008 - Installation Incompatibilities
I installed the Microsoft SQL Server 2008 yesterday. The setup required me to get rid of the Express editions of Visual Studio 2008 as they weren't SP 1, but I didn't mind as I had the SP1 on Visual Studio Team Suite 2008. I also had to get rid of SQL Server 2005 Express Edition for the installation to continue. Oh well, out with the old stuff and in with the new.
The certification exams associated with MS SQL Server 2008 are 70-432 for DBAs and 70-433 for database developers. The one-exam certification makes it really attractive especially since they aren't forcing DBAs to turn into developers or vice versa to prepare for the exams.
The certification exams associated with MS SQL Server 2008 are 70-432 for DBAs and 70-433 for database developers. The one-exam certification makes it really attractive especially since they aren't forcing DBAs to turn into developers or vice versa to prepare for the exams.
Validation Application Block - Video
Check out this video demonstration on using the Validation Application Block from the Microsoft Enterprise Library. (Sorry, no sound)
Monday, November 3, 2008
Paste Table: MS IE for MS Excel
Have you ever tried to paste a table on a web page into an Excel sheet? If you use Microsoft Internet Explorer as your web browser, everything plays along nicely, but when you use Firefox instead you end up with a mess of data splashed against your worksheet in Excel.
On MacOS X, however, there are reports that Firefox is able to paste data into Excel while Safari is not.
I guess we should just post everything in XML to work across all combinations of browsers, spreadsheets and operating systems :-(
On MacOS X, however, there are reports that Firefox is able to paste data into Excel while Safari is not.
I guess we should just post everything in XML to work across all combinations of browsers, spreadsheets and operating systems :-(
Sunday, November 2, 2008
ThreadAbort within an Event Handlers in ASP.NET
If you attach multiple event handlers to an event, the handlers are executed in the order in which they were attached. If an event handler appearing earlier causes a ThreadAbortException (such as when calling Response.Redirect), the later event handlers are skipped.
This is an issue when a button has an event handler on the page to navigate to another page but the event is also handled by user controls to save data. Just one of those not-so-common scenarios......
This is an issue when a button has an event handler on the page to navigate to another page but the event is also handled by user controls to save data. Just one of those not-so-common scenarios......
Subscribe to:
Posts (Atom)