Entries Tagged 'SQL' ↓

Check For Row Existance Only

It’s something you need to take care of in almost every ASP.NET Application. What if you would just like to know if that specific row exists or not, in SQL Server 2005 or SQL Server 2008. For example: Did this user vote on that topic already or not? There are different possibilities for that kind of situation. However, I wanted the fastet solution. If it’s about a query which looks like this:

SELECT somefield FROM sometable WHERE somefield = ‘somevalue’

In this case ‘somevalue’ is used as a literal key value, which also has an index running on it.

So if this query will always return either 0 or 1 rows, then – from an I/O point of view – using SELECT COUNT(*) or using EXISTS will be equally fast.

Why? Unfortunately SQL Server is not shortcutting an index seek if the value is being discovered in an intermediate index level. It also doesn’t shortcut it, if the value is out of range. So we always got a logical read for all index levels.

For the discussion COUNT(*) versus EXISTS, it does not matter whether the index on ‘somefield’ is clustered or not. However, the definition of the clustered index (if present) does affect performance. That’s about testing, you need to check out if the clustered on is faster or not. It mainly depends on the key size of the clustered index, the row size and the size of ‘somefield’. In theory, the fastest situation would be a clustered “and” nonclustered index on ‘somefield’. This will make the nonclustered index on ‘somefield’ the most shallow index possible, so the index seek on thisindex will use the least amount of I/O.

Finally handing over a resultset will be more costly than returning a return value.

Error Handling in SQL Server 2005

Just a quick note on Error Handling for people switching from SQL Server 2000 to SQL Server 2005. The new structured error handing mechanism provided in T-SQL 2005 provides a more simple way to handle exceptions in code. Let’s have a look at the following example which is catching a divide by zero error:

BEGIN TRY
SELECT 5/0
END TRY
BEGIN CATCH
PRINT 'Error Number =  ' + CONVERT(VARCHAR(10), Error_Number())
PRINT 'Error Message =  ' + Error_Message()
END CATCH

This is serving you with a well formed error message and a clean try/catch error handling as known from programming in .NET. Moreover these classes are available:

ERROR_NUMBER() – Number of error
ERROR_MESSAGE() – Error Message
ERROR_LINE() – Line on which error occurred
ERROR_PROCEDURE() – Procedure in which error occurred
ERROR_SEVERITY() – Severity of error
ERROR_STATE() – State of error
XACT_STATE() – Transaction state

Enjoy 🙂

Red Gate SQL Refactor Beta released

Red-Gate released a fantastic SQL Refactor Tool which helps you refactoring that horrible SQL Code you have to deal with on a daily basis.

SQL Refactor is an Add-In to Microsoft Management Studio. Therefore you must have Management Studio installed. SQL Refactor’s features are available from the Management Studio menus, which can access both SQL Server 2000 and SQL Server 2005. In this release of SQL Refactor you can use the following features:

  • SQL Lay Out reformats your T-SQL scripts. You can select this feature from the top level SQL Refactor menu. There are over 30 options to control this feature, these you can access from the top level SQL Refactor menu.
  • Smart Rename renames functions, views, stored procedures and tables, and updates all the references to these renamed objects. You can select this feature from the context menu in Management Studio’s Object Explorer.
  • Smart Rename parameters and columns renames parameters of stored procedures and functions, and columns of tables and views. You can select this feature from the context menu in Management Studio’s Object Explorer.
  • Table Split splits a table into two tables, and automatically rewrites the referencing stored procedures, views, and so on. You can also use this refactoring to introduce referential integrity tables. You can select this feature from the context menu in Management Studio’s Object Explorer.
  • Uppercase keywords turns keywords in your script or selection to uppercase.
  • Summarize Script provides you with an overview of your script. By highlighting items in this overview you can see the corresponding statements highlighted in your script.
  • Encapsulate as stored procedure turns your selection into a new stored procedure, and if requested, introduces a reference to it in your script.
  • Expand wildcards expands SELECT * statements to include a full list of columns in the select part.
  • Find unused variables and parameters shows you the variables and parameters in you script that are not used, or that are only assigned to.
  • Qualify Object Names modifies the script so that all object names are qualified. You can select this feature from the top level SQL Refactor menu.

Download it here: ftp://ftp.red-gate.com/sqlrefactorbeta/sqlrefactorsetup.exe

Great Job!

When to use .NET inside SQL Server 2005

Some meaningful scenarios when to use .NET programming inside SQL Server 2005:

  • The developer is not very comfortable with T-SQL, an implementation with .NET seems to be easy and uncomplicated. After some tests the created elements don’t show any performance impacts.
  • The realization of T-SQL seems to be inadequately complex. Especially string manipulation and sequencing are not the strength of T-SQL. However, in .NET it’s rather easy by using existing methods and functions. It’s better to use .NET if it’s faster to develop but also faster or as good as T-SQL in performance.
  • The realization in T-SQL is not possible at all. .NET commands make functions possible which wouldn’t be equivalent to T-SQL. This is mostly the case when accessing external resources.

So in my opinion .NET in SQL Server 2005 is only a real alternative if you have to access external resources or have to deal with complex tasks. It’s still SQL Server 2005 and not .NET Application Server 2005 ;).

ASP.NET: Update one table from another

Here’s how to update one table by selecting the values from another:

UPDATE C
SET C.Phone = O.[Tel],
C.Fax = O.[Fax]
FROM output$ O
          JOIN Contacts C
                   ON C.MemberId = O.MemberId

Maybe this saves someone time..

Updating to vBulletin 3.6 – Update Timeouts

If you’re running in this specific problem you are probably trying to update a pretty large forum. This happens because the post table is too big and the PHP Connection Timeout is being fired.

The browser might just stop loading or showing a blank white page. In some cases this error pops up:

This request takes too long to process, it is timed out by the server. If it should not be timed out, please contact administrator of this web site to increase ‘Connection Timeout’.

..so, whether you increase the Connection Timeout or you manually run the tables by using SSH Access.

If you want to update the big tables manually, here are the queries:

  • ALTER TABLE vb3_post ADD COLUMN infraction smallint(5);
  • ALTER TABLE vb3_thread ADD COLUMN deletedcount smallint(5);
  • ALTER TABLE vb3_post ADD COLUMN reportthreadid int(11);
  • ALTER TABLE vb3_thread ADD COLUMN lastpostid int(11);

The post tables took around 48 minutes here, updating 1,6 million rows of postings.

After running those queries, open up upgrade_360b1.php, find Alter Post table OH NO HERE WE GO! — new system will allow a refresh if it dies at least and comment out the following:

	$upgrade->add_field(
	sprintf($upgrade_phrases['upgrade_300b3.php']['altering_x_table'], 'post', 1, 1),
	'post',
	'infraction',
	'smallint',
	FIELD_DEFAULTS
	);

	$upgrade->add_field(
		sprintf($upgrade_phrases['upgrade_300b3.php']['altering_x_table'], 'thread', 1, 1),
		'thread',
		'deletedcount',
		'smallint',
		FIELD_DEFAULTS
	);

	$upgrade->execute();


	$upgrade->add_field(
		sprintf($upgrade_phrases['upgrade_300b3.php']['altering_x_table'], 'post', 1, 1),
		'post',
		'reportthreadid',
		'int',
		FIELD_DEFAULTS
	);

	$upgrade->add_field(
		sprintf($upgrade_phrases['upgrade_300b3.php']['altering_x_table'], 'thread', 1, 1),
		'thread',
		'lastpostid',
		'int',
		FIELD_DEFAULTS
	);

	$upgrade->execute();

Then run it and you should be fine.. hope that helps!

MySQL and Microsoft Visual Studio Integration

For all you MySQL lovers out there, your dream has come true! Expect the possibility to integrate MySQL directly into your Visual Studio environment very soon. According to MySQL it will be released as plugin in the near future.

The company has paid $3,000 to become a member of Microsoft’s Visual Studio Industry Partner (VSIP) program in a move that will help cement the database’s use on Windows. MySQL joins more than 240 other ISVs also working with Microsoft. MySQL says 40 per cent of its downloads are for Windows, and VSIP membership will provide greater integration between the database and Microsoft’s development environment.

Following the statistics, 40 per cent of the MySQL downloads are for Windows. The new partnership improves the integration of the database in existing Microsoft products.

I’m still using MySQL for some of my projects so these are great news!


Source: The Register

Free Database for .NET Desktop or Mobile Applications

This is great for Desktop Applications and I consider using this Database instead of Access for small Applications in need of a capable SQL Database.

SQL Server Everywhere Overview

Microsoft SQL Server 2005 Everywhere Edition offers essential relational database functionality in a compact footprint ideal for embedding in mobile and desktop applications including a new generation of occasionally connected dynamic applications.

Sharing a familiar SQL syntax and common ADO.NET programming model with other SQL Server Editions, and management via SQL Server 2005 Management Studio, SQL Server Everywhere allows developers and administrators to apply their existing skills and be immediately productive.

In addition, SQL Server Everywhere enables scale-up of an application, either via a robust synchronization with other SQL Server Editions, or by moving to a higher edition of SQL Server. SQL Server Everywhere allows you to extend your integrated data management platform deeper into your enterprise and into your mobile workforce.

When to use:

  • Essential relational database functionality in a compact footprint
  • Ideal for mobile and desktop applications, including occasionally connected
  • Embeddable in applications
  • Free to download, develop, deploy, and redistribute

Don’t use it when you:

  • When you want to run as a service
  • When you need a multi-user database server
  • You need the full functionality of SQL Server

In fact that is something I have been waiting for. Great Release Microsoft!


Download it here: SQL Server 2005 Everywhere Edition (CTP)

Editing large files

Just a quick note because I’m dealing with that kind of files at the moment. Whenever you have a large SQL file which is bigger than 1GB most editors need plenty of time to load such files and we didn’t talk about editing those files yet. UltraEdit for example lags a lot when opening 1gb+ files.

To be able to open, edit and save those files quickly I highly recommend WinHex. It’s not as easy as a usual Text Editor but it’s ultra fast because files are being edited just in time. Check it out, you’ll love it!

Security Advise: Rename your SQL Server 2005 sa Account

Our Webserver which is running SQL Server 2005 was bruteforced a lot lately by several attacks. Those people mostly try to bruteforce your SA Account. An easy way to protect yourself against those attacks is to rename or disable your sa Account! This is how it works:

ALTER LOGIN sa DISABLE;

ALTER LOGIN sa WITH NAME = [systemadmin];

Additionally you could change the SQL Server 2005 Port from 1433 to something else.

And another approach would be using Integrated Security and then turn off Mixed Authentication. An ADO.NET Connection String with Integrated Security would for example look like that:

Data Source=Your_Server_Name;Initial Catalog=Your_Database_Name;Integrated
Security=SSPI;

Hope that helps..

SQL Server 2005 – What is a View?

I explained that a couple of times lately so I decided to write a detailed description of a View in SQL Server 2005. Here we go:

There will be times when you want to group together data from more than one table, or perhaps only allow users to see specific information from a particular table, where some of the columns may contain sensitive or even irrelevant data. A view can take one or more columns from one or more tables and present this information to a user, without the user accessing the actual underlying tables. A view protects the data layer while allowing access to the data. All of these scenarios can be seen as the basis and reason for building a view rather than another method of data extraction. If you are familiar with MS Access, views are similar to Access queries. Because a view represents data as if it was another table, a virtual table in fact, it is also possible to create a view of a view.

Let’s take a look at how a view works. For example we have a customer table that holds information about customers such as their first name, last name, account number, and
balances. There will be times when you want your users to have access to only the first and last names, but not to the other sensitive data. This is where a view comes into play. You would create a view that returns only a customers first and last name but no other information. Creating a view can give a user enough information to satisfy a query he or she may have about data within a database without that user having to know any T-SQL commands. A view actually stores the query that creates it, and when you execute the view, the underlying query is the code that is being executed. The underlying code can be as complex as required, therefore leaving the end user with a simple SELECT * command to run with perhaps a small amount of filtering via a simple WHERE statement.

From a view, in addition to retrieving data, you can also modify the data that is being displayed, delete data, and in some situations insert new data. There are several rules and limitations for deleting, modifying, and inserting data from multitable views, some of which will be covered in the “Indexing a View” section later in the chapter.

However, a view is not a tool for processing data using T-SQL commands, like a stored procedure is. A view is only able to hold one query at a time. Therefore, a view is more like a query than a stored procedure. Just as with a stored procedure or a query within a Query Editor pane, you can include tables from databases that are running on different servers. Providing the user ID has the necessary security credentials, it is possible to include tables from several databases.

So to summarize, a view is a virtual table created by a stored SQL statement that can span multiple tables. Views can be used as a method of security within your database, and they provide a simpler front end to a user querying the data.

Hope that helps..

SQL Server 2005 Introduction to SQL Server Management Studio

I found a nice little tutorial about the new SQL Server Management Studio on aspfree.com.

If you haven’t used it yet, check it out: http://www.aspfree.com/c/a/MS-SQL-Server/SQL-Server-2005-Introduction-to-SQL-Server-Management-Studio/

SQL Server Health and History Tool

Nice and useful tool!

This version of SQLH2 supports SQL Server 2005 RTM collection. SQLH2 collects four main types of information:

1. Feature Usage – What services/features are installed, running and level of workload on the service.
2. Configuration Settings – Machine, OS and SQL configuration settings, SQL instance and database metadata.
3. Uptime of the SQL Server service
4. Performance Counters (optional) – Used to determine performance trends

Get it here at Microsoft:
http://www.microsoft.com/downloads/details.aspx?FamilyID=eedd10d6-75f7-4763-86de-d2347b8b5f89&DisplayLang=en#filelist

ASP.NET Deployment Tool v1.7 released

The updates are flying..
Get it here: ASP.NET Deployment Tool

I’ve rewritten the complete config section and added Profile Support! Check out the readme for more details.
GotDotNet has been rewritten for ASP.NET v2.0 and seems to have various problems at the moment, in fact you can access the project page only through manually navigating to it. However, you can still get the latest bits from sunlab.de.

Kind Regards, Andreas Kraus

MySQL v5.0.15 final released!

Yes, it’s finally out. The new Key Features are:

  • Stored Procedures
  • Triggers
  • Views
  • Information Schema
  • Archieve Storage Engine

Apparently, MSSQL got those features allready, but therefore MySQL is for free and is being used by lots of people. Head over to http://dev.mysql.com and check it out if you like!

Good Evening,
Andreas Kraus