Few tips for 'Plugging holes in your app'

These are few mistakes we've come across while working on performance enhancement for an app.

If you are kind of guy who stores GUID in char(36) (weird), use Unique Identifier please, GUID requires 36 bytes of data but if you use UniqueIdentifier type, then we require only 16 bytes of data.

Also, also the N '' prefix, while encoding your Unicode data (to aviod SQL injection etc), if the fields are char or varchar type (check link for the comparison sheet) it will prove costly too.

And its easy to replicate this black hole which leaks your app,

With a test table containing 100,000 rows with a char(36) field containing guids,
The following SQL took 56 ms to run:
SELECT * FROM testtable WHERE guid = N'00008999-A3B3-41FE-BF23-40465CF14147'

Where as the following sql:
SELECT * FROM testtable WHERE guid = '00008999-A3B3-41FE-BF23-40465CF14147'
took 0 ms (less than 1).

I am feeling.....

I came across this site quite accidently and 'i feel' this one is really cool, not because of a great UI or features, but appreciate the brains behind this idea.

To know more about what i am talking, land in http://www.wefeelfine.org URL, by the time you are reading this, wefeelfine.org would have sucked this blog's header and made a particle out of it and someone might be reading :)

SQL search on float value, using string wild cards

Do convert the float value to bigint and then to nvarchar to search using wild cards and like
e.g
CONVERT(nvarchar, CONVERT(bigint, ))

SQL Server does not exist or access denied.

SQL Server does not exist or access denied. (with SQL 2005 Server)

Troubleshooting steps are as below:

First, telnet 1433
If connection could not be established then

Issue seems to be that you have not enabled the remote connections on the SQL 2005, to do this
1. Go to Surface area COnfiguration from Start - Programs - Microsoft SQL 2005 - Configuration Tools - SQL Server Surface Area Configuration

2. Select Surface area configuration for services and connections

3. Select Remote Connections from Database Engine dropdown list and enable 'Local and Remote Connection'

else if you are able to telnet, check the remote db user privilage you are trying to connect.