Free Microsoft E-Learning: What's New in Microsoft SQL Server 2008

Microsoft e-learning site offers free online training for SQL Server 2008
I Hope this helps getting ready for the new release...

Query to get database relations in SQL Server 2005

It is a common need to get a list of foreign key relations in a database
This simple query does this:

Select a.Column_name as [FK Column],a.Constraint_Name as [FK],c.column_name [PK Column] ,Unique_Constraint_Name As [PK]
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE a
join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS b on a.Constraint_Schema=b.Constraint_Schema and a.Constraint_Name=b.Constraint_Name
join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE c on c.Constraint_Schema=b.Unique_Constraint_Schema and c.Constraint_Name=b.Unique_Constraint_Name

Things I liked about Visual Web Developer 2008 Express

Microsoft's decision of releasing free express editions of visual studio (starting from VS 2005) has proven to be a really right step. Beginners and enthusiastic developers found express editions a great start or a chance to look into the MS world of software tools.

I used 2005 Express editions and today I downloaded Visual Web Developer 2008 Express and found nice things that I'd like to share:

• Split view: Now you can view both HTML source and Design view of web pages, this feature makes design much easier, however you need to save or synchronize for HTML changes to reflect in the design view.
  

• New Project and Item types: WcfService and AjaxWcfService
  

• Targeting Framework version: You can choose which .net framework version to target.
  

• AJAX Extensions are pre-installed

These are few things that I liked, discover more yourself by downloading Express editions.

Comments on "Efficiently Uploading Large Files via Streaming" article on 15Seconds.com

In an article Efficiently Uploading Large Files via Streaming, published on 15seconds the author tries to give a solution for uploading huge files to ASP.NET applications.
The problem according to the author is:

When uploading a file to a web server, the upload process generally requires the incoming file to be stored in memory until the upload is complete. If an uploaded file is larger than the available memory, memory usage in general and performance in particular will suffer.

And the suggested solution was to read the uploaded data directly from PostedFile.InputStream as small chunks and store them in a database.

I believe the author has totally missed the point in this article.

First: Huge uploaded files are not kept in memory, they are buffered to the hard disk. at least this is true in ASP.NET 2.0 as stated in the msdn:

Files are uploaded in MIME multipart/form-data format. By default, all requests, including form fields and uploaded files, larger than 256 KB are buffered to disk, rather than held in server memory.

Second: The solution claims that reading the file from the InputStream will be a streaming solution that avoid caching the whole file on the server, which is again not true. The ASP.NET code is called after the whole request has already been submitted to the server.

I wished to clarify this by commenting on the article or by sending to the author, but unfortunately, the site does not allow this :( .

How granting execute permission on stored procedures made easier in SQL Server 2005

In SQL Server 2000, a common problem was faced by developers who design their data access to depend on stored procedures, SQL server had db_datareader and db_datareader roles. But no such db_executer role.
This caused trouble as the user used to execute stored procedure had to be granted execute permission on every stored procedure in the database. This can be automated using scripts that grant execute permissions to the required user. But imagine how much time you'll spend debugging your application after creating a new procedure just to discover that you forgot to run the script.
Another easy and dirty way was to add the user used by the application to the db_owner group and make all procedures owned by the dbo. No need to talk about why this is bad.

In SQL Server 2005, the concept of permissions, securables and schemas ,were introduced.
now you can have it all done using one statement. Assuming you have a user test and a schema named HR, test can have execute permissions on all stored procedures belong to HR schema:

GRANT EXECUTE on SCHEMA::HR to test

Now if you (as dbo) create a procedure like:
CREATE proc HR.GetAccData
as
Select * from dbo.acc
GO

The used test will have execute permission on the HR.GetAccData procedure.

Moving a form without a title bar

How to make a form without a title bar movable?

This is a common request in programming forums, and here is a simple solution that depends on handling mouse down and mouse move events:


public partial class Form1 : Form
{
int m_PrevX;
int m_PrevY;

public Form1()
{
InitializeComponent();
}

private void Form1_MouseMove(object sender, MouseEventArgs e)
{
if (e.Button != MouseButtons.Left)
return;

Left = Left + (e.X - m_PrevX);
Top = Top + (e.Y - m_PrevY);

}

private void Form1_MouseDown(object sender, MouseEventArgs e)
{
if(e.Button!=MouseButtons.Left)
return;

m_PrevX = e.X;
m_PrevY = e.Y;
}

MS SQL Server Views: Order By

If you create a view in SQL Server 2005 management studio and add an order by clause, run the query, the order by seems to take effect.
But when you reference the view in a simple query (with no order by) you find that the returned results are not as expected, records are not ordered..
check the definition of the created view :

sp_helptext view_name

you find that the management studio added a TOP clause to the view.
reviewing SQL Server Books Online:

SQL Server 2000:

The query defining the view cannot include the ORDER BY, COMPUTE, or COMPUTE BY clauses or the INTO keyword.

SQL Server 2005:

The SELECT clauses in a view definition cannot include the following:
.
.
An ORDER BY clause, unless there is also a TOP clause in the select list of the SELECT statement

This makes extreme sense to me, as views represent logical tables, so just like tables, the order in which you get data should not be assumed, unless explicitly requested using an order by in the query.