Harshal Mistry

I was going through this article on difference between select and set on vyas's website
here is an small extract of it which would be usefull:
When using a query to populate a variable, SET will fail with an error, if the query returns more than one value. But SELECT will assign one of the returned rows and mask the fact that the query returned more than one row. As a result, bugs in your code could go unnoticed with SELECT, and this type of bugs are hard to track down too. Here is an example:

/* Consider the following table with two rows */
SET NOCOUNT ON
CREATE TABLE #Test (i int, j varchar(10))
INSERT INTO #Test (i, j) VALUES (1, 'First Row')
INSERT INTO #Test (i, j) VALUES (1, 'Second Row')
GO

/* Following SELECT will return two rows, but the variable gets its value from one of those rows, without an error.
This may not be what you were expecting. Since no error is returned,
you will never know that two rows existed for the condition, WHERE i = 1 */
DECLARE @j varchar(10)
SELECT @j = j FROM #Test WHERE i = 1
SELECT @j
GO

/* If you rewrite the same query, but use SET instead, for variable initialization, you will see the following error */
DECLARE @j varchar(10)
SET @j = (SELECT j FROM #Test WHERE i = 1)
SELECT @j

Server: Msg 512, Level 16, State 1, Line -1074284106
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

---------
the whole article can be found here.

Many times I have read comments that its better to have a pointer to the image be saved in the database and the image be managed by the operating system.
Here is some insight about  the pros and cons of managing images in the database.
Here are some of the Pros for storing the images in the database as a better choice and the application in which the images will be used can count on the benefits of a database system.

The benefits of storing the images in the database include:

Scalability: Although file systems are designed to handle a large number of objects of varying sizes, file systems usually are not optimized for a huge number of small files.
Database systems are optimized for such cases.

Availability: SQL server has availability features that extend beyond those provided by the file system.

SQL Server replication is a set of solutions that allow you to copy, distribute and potentially modify data in a distributed environment.

Log Shipping provides a way of keeping a stand by copy of a database in case the primary system fails.

As far as the cons for it are concerned
Storing the image in the database increases the database size dramatically and obviously the overall performance of the database is degraded.

Any more insights are welcome.

you may find more detailed info on how to link to the images in sql server here

There are a few system functions for returning the number of connections but,they are many times misunderstood.

@@CONNECTIONS : Returns login attempts since the server was started.

@@MAX_CONNECTIONS: Returns maximum number of connections supported.

The simplest way to list all users and processes is to use the system stored procedure:  sp_who
To list all active users:  sp_who 'active'
to list details about a particular user:    sp_who 'login-name'
Now to obtain the current number of connections to the server, you can use:

SELECT COUNT(*) AS CONNECTIONS FROM master..sysprocesses

 And just to get the user connections, omitting the system processes, use:

SELECT cntr_value AS User_Connections FROM master..sysperfinfo as p
WHERE p.object_name = 'SQLServer:General Statistics' And p.counter_name = 'User Connections'

 

 

Recently I downloaded and installed the Best Practices Analyzer tool.

It scans through the database schemas and associated SQL code using a rules-based engine to identify any deviations from the so called accepted best practices, I was wondering what these best practices were so after some search I found out what these were and uploaded them here.

            The report generate by BPA is very precise. It points what line of the code is not compliant to the standards it is following, what database is not being backedup, checks the tables without primary/ unique keys etc. Very useful in the Production environment.

The only drawback I found was it shoots up the cpu 100% when scanning.

Hey Hello everyone. My first blog. Thanks to my friend Enigma for introducing me to blog world and to Donny for setting me up on sqlJunkies. Hope I can post some interesting and useful stuff here.