The real difference between SQL Server and Oracle

  • 0

The real difference between SQL Server and Oracle

Visit : http://www.zonixsoft.com (our Official website)

For years now there’s been a constant war between Microsoft supporters and Oracle supporters. Oracle has these features, SQL Server has these features, etc. But that’s not really where the real importance lies. Sure, functionality is a part of it because your database should be able to do what you need it to do. However, do you want to know what the real difference between the two companies is and why Microsoft has made such a strong impact in the industry?…. Read More….


Bookmark and Share


  • 1

UPDATE Based Off of A Table

visit : http://www.zonixsoft.com (our official website)

 INSERT offers the ability to insert into a table based upon a SELECT statement with the following syntax:

INSERT INTO [TABLEA] ([FIELDA],[FIELDB],[FIELDC])
        SELECT [FIELDA],[FIELDB],[FIELDC]
FROM [TABLEB]

 

there are time where you may want to do a similar approach with UPDATE.
Here is the syntax to do that very task:

UPDATE [TABLEA]
        SET  [TABLEA].[FIELDA] = [TABLEB].[FIELDA]
               [TABLEA].[FIELDB] = [TABLEB].[FIELDB]
               [TABLEA].[FIELDC] = [TABLEB].[FIELDC]
        FROM [TABLEA]
               JOIN [TABLEB]
        ON [TABLEA].[KEYA] = [TABLEB].[KEYA]

 


Bookmark and Share


  • 0

Passing parameters to dynamic query

visit http://www.zonixsoft.com (our official Website)

 

Consider following queries

1. SELECT * FROM @tablename

2. SELECT @colname FROM tbl

3. SELECT * FROM tbl WHERE x IN (@list)

4. SELECT @Cnt=Count(*) FROM tbl WHERE x IN (@list)

 

For the first query, it will give error ‘Must declare the table variable “@tablename”‘ if @tablename is not Table variable.

For Second query it will display all rows with same value contained in @colname rows in ‘tbl’.

For Third query it will display correct result if @listonly one value(no comma separated values).

Fourth one is same as third query but it stores value in a variable @Cnt. The actual problem will come to know after solving third one.

To get correct result, we need to create Dynamic query for all four queries.

 

The first three queries can be execute correctly by using Exec().

EXEC() takes one parameter which is an SQL statement to execute.The parameter can be a concatenation of string variables and string literals, but cannot include calls to functions or other operators.

 

The first three can be written as:

 

1. Exec (‘SELECT * FROM’ + @tablename)

2. Exec (‘SELECT ‘ + @colname +‘ FROM tbl’)

3. Exec (‘SELECT * FROM tbl WHERE x IN (‘+@list+‘)’)

 

But Fourth one can not be written simply using Exec(). Because if @Cnt is declared outside the statement being executed in Exec() like

 

Declare @Cnt int

Exec(‘SELECT @Cnt = Count(*) FROM tbl WHERE x IN (‘+@list+‘)’)

Select @Cnt

 

it will give error

Must declare the scalar variable “@Cnt”.

 

Here is need of sp_executesql instead of Exec().

sp_executesql is a system stored procedure that you can use in place of “exec” to execute your dynamic sql. It takes two pre-defined parameters and any number of user-defined parameters.

This allows you to have parameters in your dynamic query and pass them in. The end result is that SQL Server will try to cache the execution plan for your query giving you some of the advantages of a fully compiled query.

The first parameter @sql is mandatory, and contains a batch of one or more SQL statements. The data type of @sql is ntext in SQL 7 and SQL 2000, and nvarchar(MAX) in SQL 2005. A varchar value won’t do.

The second parameter @params is optional, but you will use it 90% of the time. @params declares the parameters that you refer to in @sql. The syntax of @params is exactly the same as for the parameter list of a stored procedure. The parameters can have default values and they can have the OUTPUT marker.Just like @sql, the data type of @params in SQL 7/2000 is ntext and nvarchar(MAX) in SQL 2005.

The rest of the parameters are simply the parameters that you declared in @params, and you pass them as you pass parameters to a stored procedure, either positional or named. To get a value back from your output parameter, you must specify OUTPUT with the parameter, just like when you call a stored procedure.

 

Let’s write the fourth query using sp_executesql.

Declare @CntRow int

Declare @sql nvarchar(200)

Declare @params nvarchar(200)

set @sql = ‘SELECT @Cnt=Count(*) FROM tbl WHERE x IN (@list)’

set @params = ‘@list varchar(100),@Cnt int output’

exec sp_executesql @sql,@params,‘1,2,3’,@CntRow output

Select @ CntRow

‘1,2,3’ will be passed to @list and result of @Cnt will be set to @CntRow.


Bookmark and Share


  • 0

MERGE Command in SQL Server

visit: http://www.zonixsoft.com (our official website)

In SQL Server 2008, Microsoft introduces the MERGE functionality through the MERGE command. The MERGE command inserts rows that don’t exist and updates the rows that do exist.

    What MERGE Command works as,

        IF FOUND

           THEN UPDATE

        ELSE

           INSERT;

    Until now, this MERGE functionality could only be made available in SQL Server through stored procedures and cursors, etc.

    This article illustrates how to user MERGE command in SQL Server 2008.

    Suppose we have 2 tables with same field structure as ID, ProductName and SKU,

        MERGE INTO ProductTable1 AS Target

        USING  AS ProductTable2 AS Source

                ON Target.ID = Source.ID

        WHEN MATCHED THEN

                UPDATE SET Target.ProductName = Source.ProductName,

                Target.SKU = Source.SKU

        WHEN NOT MATCHED THEN

                INSERT (ID,ProductName,SKU) VALUES (Source.ID,Source.ProductName,Source.SKU);

 


Bookmark and Share


  • 0

CASE (Transact SQL)

Evaluates a list of conditions and returns one of multiple possible result expressions.

CASE has two formats:

· The simple CASE function compares an expression to a set of simple expressions to determine the result.

· The searched CASE function evaluates a set of Boolean expressions to determine the result.

Examples:

A. Using a SELECT statement with a simple CASE function

Within a SELECT statement, a simple CASE function allows for only an equality check; no other comparisons are made. The following example uses the CASE function to change the display of product line categories to make them more understandable.

USE AdventureWorks;
GO
SELECT   ProductNumber, Category =
CASE ProductLine
WHEN ‘R’ THEN ‘Road’
WHEN ‘M’ THEN ‘Mountain’
WHEN ‘T’ THEN ‘Touring’
WHEN ‘S’ THEN ‘Other sale items’
ELSE ‘Not for sale’
END,
Name
FROM Production.Product
ORDER BY ProductNumber;
GO

B. Using a SELECT statement with a searched CASE function

Within a SELECT statement, the searched CASE function allows for values to be replaced in the result set based on comparison values. The following example displays the list price as a text comment based on the price range for a product.

USE AdventureWorks;
GO
SELECT   ProductNumber, Name, ‘Price Range’ =
CASE
WHEN ListPrice =  0 THEN ‘Mfg item – not for resale’
WHEN ListPrice < 50 THEN ‘Under $50’
WHEN ListPrice >= 50 and ListPrice < 250 THEN ‘Under $250’
WHEN ListPrice >= 250 and ListPrice < 1000 THEN ‘Under $1000’
ELSE ‘Over $1000’
END
FROM Production.Product
ORDER BY ProductNumber ;
GO


Bookmark and Share


  • 0

Paging in SQL Server

Visit: http://www.zonixsoft.com (our official Website)

Web Application Performance (Paging in Sql Server)

As a Web developer, you know by now that using the default paging capabilities of ASP.NET Webcontrols like DataGrid and GridView cause the Performance issue when we have thousands and thousands of records in our database; because with every roundtrip to the data-server, you get ALL the records ALL of the time. This is fine perhaps for very small databases. But in web application, you always have to count on its Performance.

One alternate approach to this scenario is to serve “On Demand” records. That is; for example; retrieving 1-100 records, then 101-200 records result set based on some event rather than retrieving all the records all of the time. I am talking about “Paging in Sql Server”.

My-SQL

The simplest solution is provided by My-SQL using LIMIT keyword. Check out the below Query:

Select * From Product Limits 15,5

It will retrieve results from 16 to 20 records. In above Query, 15 if the offset from where you want the result set records and 5 is the number of records you want to retrieve.

Sql Server 2005

Unfortunately, SQL Server does not have an equivalent to “Limit” keyword. Its nearest is TOP N, which returns the first N rows. Still there are two alternative than we can use for “Paging”.

Using ROW_NUMBER()

Sql 2005 includes the ROW_NUMBER() function, which adds an integer field to each record. In other words, it adds the record’s position within the result set as an additional field so that the first record has a 1, the second a 2, etc.

To ensure the numbering is consistent, however, SQL Server needs to know how to sort the data. Because of this, ROW_NUMBER() must immediately be followed by the OVER() function. OVER() has one required parameter, which is an ORDER BY clause. The basic syntax is:

SELECT ROW_NUMBER() OVER(ORDER BY [Name]) as RowNum,

[Name], [SKU], [ManufacturerPartNumber]

FROM Product

The Product data in result set will be appeared sorted by Name, and it has an extra column indicating each record’s position within the results.

If we want to limit the results displayed to a certain range, we need to nest this SELECT inside another one. To limit our results to records 5 to 10, we can use the following query:

SELECT * FROM

(SELECT ROW_NUMBER() OVER(ORDER BY [Name]) AS RowNum,

[Name], [SKU], [ManufacturerPartNumber] FROM Product) AS Product1

WHERE RowNum >= 5 AND RowNum <= 10

With Nested Queries

There is one another solution that consists only of 1 sql-statement, and so is efficient and quick, even with large databases. The good news is : it will work ! Always ! it’s looking rather a bit complicated; but its not!

Let us see the Example directly to limit our results to records 6 through 10,

SELECT P.[Name], P.[SKU], P.[ManufacturerPartNumber] FROM (

SELECT TOP 5 [ProductID],[Name] FROM (

SELECT TOP 10 [ProductID],[Name]

FROM Product

ORDER BY Product.[Name] ASC) as Product1

ORDER by Product1.[Name] DESC) as Product2

INNER JOIN Product P ON Product2.ProductID = P.ProductID

ORDER by Product2.[Name] ASC

Where 5 is the number of rows you want to retrieve and 10 is the offset.

The innermost Sql Statement will fetch 10 records (1 to 10) Order by Product Name in Ascending Order.

This means that if you have a table with 10,000 records, and you want to have the last 10 records, the innermost SELECT statement will indeed retrieve 10,000 records. This of course has it’s ramifications on performance, but since it’s only the primary key fields (which are indexed) and optionally some sort fields, the impact will be minimal.

The middle Sql Statement will fetch 5 records (10 to 6) Order by Product Name in Descending Order. And Finally the Outer Sql Stateement is simply select statement that will fetch the result in Ascending Order (6 to 10). In Innermost and Midddle Sql Statement only primary key fields or the fields which are indexed are used.

After looking at this in Query Analyzer, it appears that the extra nesting does not add very much to the load. The SQL statement plays with the sorting orders to limit the records, thus resulting in paged recordsets. This means that there should be at least ONE field to be sorted. If there isn’t any, sort on the primary key field(s) !


Bookmark and Share


  • 0

Passing lists to SQL server stored procedures

Visit : http://www.zonixsoft.com (our official wbsite)

Article is about:

The ability to pass “a list of values” from .Net as a parameter to a T-SQL based stored procedure.

Scenarios:

There are lots of scenarios where we need to pass a list of values to save in database. Here’s a couple of obvious ones:

· INSERT a list of values into the database in one “chunky” call (e.g. some IDs from a CheckBoxList)

· SELECT rows where IDs are IN (<list of IDs>)

Some general Approaches:

Taking the INSERT statements as an example, there are various general approaches that we adopt to achieve the desired result:

· Use dynamic / Inline SQL!  But ideally say, dynamic / Inline SQL is rarely the ideal solution for obvious reasons.

· Make a stored proc call for each ID to insert. This is the most common approach we see in various projects, mainly because it is the easiest to implement. The drawback of course is if we were to insert 60 values, it would result in 60 “chatty” calls to the database.

· Pass comma separated values via a VARCHAR (or similar) parameter. This works fine but has messy “string splitting” in the stored procedure to extract the IDs and then build the SQL statement in the procedure itself. Prone to SQL injection and not the best performance.

· Pass the list as an XML parameter. This is nicer and is the point of this article.

Coming to the main Point, Using XML:

Using XML for “list passing” has a number of benefits, in particular the ability to pass lists of more “complex types” rather than just single values.

Lets take an example. Suppose we are having 2 CheckedListBox; one is list of Users and another is the list of tasks / roles that can be assigned to Users. We want to store the values in Table which is having Fields UserID and TaskID. The Stored Procedure will accept Parameter with XML Datatype as,

CREATE PROCEDURE [dbo].[usp_InsertUserTask]
@UserTaskXML XML
AS
BEGIN
INSERT INTO UserTasks (UserID,TaskID)
SELECT
UserTaskTab.UserTaskCol.value(‘UserID[1]’,’int’) AS UserID,
UserTaskTab.UserTaskCol.value(‘TaskID[1]’,’int’) AS TaskID
FROM @UserTaskXML.nodes(‘//UserTaskList/UserTaskData’) AS UserTaskTab(UserTaskCol)
END

To call this in Stored Procedure, you would have something like this:

EXEC    [dbo].[usp_InsertUserTask]
@UserTaskXML = ‘<UserTaskList>
<UserTaskData>
<UserID>1</UserID>
<TaskID>100</TaskID>
</UserTaskData>
<UserTaskData>
<UserID>2</UserID>
<TaskID>200</TaskID>
</UserTaskData>
</UserTaskList>’

In your application, your C# calling code could be:

SqlConnection sqlCN = new SqlConnection();
sqlCN.ConnectionString = ConfigurationManager.AppSettings[“DBConn”].ToString();
string strQuery = “usp_InsertUserTask”;
SqlParameter[] sqlParams = new SqlParameter[1];
sqlParams[0] = new SqlParameter(“@UserTaskXML”, GetStudyDataXMLString());
SqlHelper.ExecuteNonQuery(sqlCN, CommandType.StoredProcedure, strQuery, sqlParams);
if (sqlCN.State == ConnectionState.Open)
sqlCN.Close();
sqlCN.Dispose();

which calls the method below to translate the UserID and TaskID from CheckBoxLists into an XML String:

private string GetUserTaskListXML()
{
try
{
StringBuilder XMLString = new StringBuilder();
XMLString.AppendFormat(“<UserTaskList>”);
for (int iUserCount = 0; iUserCount < UserCheckBoxList.Items.Count; iUserCount++)
{
if(UserCheckBoxList.Items[iUserCount].Selected)
{
for (int iTaskCount = 0; iTaskCount < TaskCheckBoxList.Items.Count; iTaskCount++)
{
if(TaskCheckBoxList.Items[iTaskCount].Selected)
{
XMLString.AppendFormat(“<UserTaskData>”);
XMLString.AppendFormat(“<UserID>{0}</UserID>”, UserCheckBoxList.Items[iUserCount].value);
XMLString.AppendFormat(“<TaskID>{0}</TaskID>”, UserCheckBoxList.Items[iUserCount].value);
XMLString.AppendFormat(“</UserTaskData>”);
}
}
}
}
XMLString.AppendFormat(“</UserTaskList>”);
}
catch (Exception Ex)
{
throw Ex;
}
return XMLString.ToString();
}

Here, StringBuilder is used for the xml concatenation as in this case I think it fits the bill but purists might prefer an XmlTextWriter approach. In summary, it performs very well and is adaptable for various lists of objects and more complex structures.


Bookmark and Share