ISNULL , COALESCE in SQLSERVER, NVL in ORACLE, IFNULL() in MYSQL

as a developer and writing querys you offtn encounter in a situation where you want to replace a NULL in your result every RDBMS has its own system function to handle this today we will go through these functions in detail

We will start with SQL Server

In sql Server we have to functions one is ISNULL and other COALESCE

ISNULL is a bult in TSQL function develped by Microsoft , it replaces null value with scpecified value

ISNULL(Check expression (Column/variable/value), replacement expression(Column/Variable/value))

The replacement expression should be impilictly convertable to check expression data type, SQl server implicitly converts

replacement value to the data type of Check expression and returns the value if check expression is null

If we look at new column’s defnition it is a char datatype

However if you switch our replacement expression with check expression it throws an error saying

Msg 245, Level 16, State 1, Line 7

Conversion failed when converting the varchar value ‘a’ to data type int.

Which is not possible according to sqlserver

Sql server data type conversion chart

http://msdn.microsoft.com/en-us/library/ms191530.aspx

It can only have two input expressions

Where as if we look at COALESCE it takes multiple expressions and returns first not null value

The one big dfference between ISNULL and COALESCE is the return data type,

When using ISNULL if check expression is evaluated to NULL and return expression is NOT NULL and is different data type that of check expression SQL Server implictly converts return expression to the data type of check expressions

Where as COALESCE well it returns the first not null value but the data type it returns depends on the SQL Server data type precedence

http://msdn.microsoft.com/en-us/library/ms190309.aspx

Example:

SQL server data type precedence

http://msdn.microsoft.com/en-us/library/ms190309.aspx

about COALESCE

http://msdn.microsoft.com/en-us/library/ms190349(v=sql.105).aspx

NVL equavalent to ISNULL in SQLSERVER

NVL in ORACLE

NVL in Oracle works excatly as ISNULL in sql server

NVL (exp1 , exp2)

Exp1 is check expression and Exp2 is a replacement value

if Exp1 is NULL it returns Exp2 else it returns Exp1

Exp1 and Exp2 might not be same datatype just like in sql server but it should be implicitly convertble  to the datatype of Exp1

 

But this implicit conversion is done in a smarter way when compared to isnull

  • If expr1 is character data, then Oracle Database converts expr2 to the datatype of expr1 before comparing them and returns VARCHAR2 in the character set of expr1.
  • If expr1 is numeric, then Oracle determines which argument has the highest numeric precedence, implicitly converts the other argument to that datatype, and returns that datatype.

Numeric Precedence

Numeric precedence determines, for operations that support numeric datatypes, the datatype Oracle uses if the arguments to the operation have different datatypes. BINARY_DOUBLE has the highest numeric precedence, followed by BINARY_FLOAT, and finally by NUMBER. Therefore, in any operation on multiple numeric values:

  • If any of the operands is BINARY_DOUBLE, then Oracle attempts to convert all the operands implicitly to BINARY_DOUBLE before performing the operation.
  • If none of the operands is BINARY_DOUBLE but any of the operands is BINARY_FLOAT, then Oracle attempts to convert all the operands implicitly to BINARY_FLOAT before performing the operation.
  • Otherwise, Oracle attempts to convert all the operands to NUMBER before performing the operation.

 

 

Table 2-10

 

 

 

Implicit Type Conversion Matrix

 

Table 2-10 Implicit Type Conversion Matrix

CHAR VARCHAR2 NCHAR NVARCHAR2 DATE DATETIME/INTERVAL NUMBER BINARY_FLOAT BINARY_DOUBLE LONG RAW ROWID CLOB BLOB NCLOB
CHAR X X X X X X X X X X X X X
VARCHAR2 X X X X X X X X X X X X X
NCHAR X X X X X X X X X X X X X
NVARCHAR2 X X X X X X X X X X X X X
DATE X X X X
DATETIME/ INTERVAL X X X X X
NUMBER X X X X X X
BINARY_FLOAT X X X X X X
BINARY_DOUBLE X X X X X X
LONG X X X X X X X X
RAW X X X X X X
ROWID X X X
CLOB X X X X X X
BLOB X
NCLOB X X X X X X

Oracle Implicit Conversion Matrix

IFNULLI() in MY SQL

 

IFNULL() is equavalent to NVL() in oracle and ISNULL() in Microsoft Sql server

 

 

Advertisements

Data Cube: A Relational Aggregation Operator : Microsoft research paper

Very nice article on group by clause , how it works and what are its limitations and how can we overcome with a CUBE operator

Data analysis applications typically aggregate data across many dimensions looking for unusual patterns. The SQL aggregate functions and the GROUP BY operator produce zero-dimensional or 1-dimensional answers. Applications need the N-dimensional generalization of these operators. This paper defines that operator, called the data cube, or simply cube. The cube operator generalizes the histogram, cross-tabulation, drill-down, and sub-total constructs found in most report writers. The cube treats each of the N aggregation attributes as a dimension of N-space. The aggregate of a particular set of attribute values is a point in this space. The set of points form an N-dimensional cube. Super-aggregates are computed by aggregating the N-cube to lower dimensional spaces. Aggregation points are represented by an “infinite value”, ALL. For example, the point would represent the global sum of all items. Each ALL value actually represents the set of values contributing to that aggregation

Source :

http://research.microsoft.com/apps/pubs/default.aspx?id=69578

 

Download PDF file by clicking below link

MIcrosoft cube research

 

How to resolve Reporting Services Catalog Database File Existence Failed or Temporary Database File Existence failed

When you Uninstall SQl server some of the traces will be left behind one of them are Reporting serivces catalogs in order to fix this issue follow the screen shots below

Next to go to the Sql server root directory, by default

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA

z

a

Files to be deleted

  • ReportServer.mdf
  • ReportServer_log.LDF
  • ReportServerTempDB.mdf
  • ReportServerTempDB_log.LDF.

Implimenting Error Handling in SQL Server – Part 1

There are two types of Messages SQL Server returns

  1. Information Message
  2. Error Message

An informational Message as the name suggests SQL server sends the messages to the End user or client,

this can be anything including the output of DBCC commands or status of the server or it can also be a

output from the print command An Error message can be a warning message or error message from

SYS.MESSAGES catalog or from a RAISEEROR statement

As we go ahead we will be concentrating mostly on Error Messages in SQL server

There two types of Error Messages in SQL server

  1. System
  2. User Defined

if there is any exception during the execution,

The database engine picks up the information in the SYS.MESSAGES and sends it to the end user.

As there is need to customize the error messages so that errors are better understood at business

level these are called user defined error.

All the  System error messages and User defined error messages are stored in SYS.MESSAGES

SQL Server stores system and user-defined errors inside a catalog view called sys.messages.

This catalog contains all system error messages, which are available in many languages.

 

Each message has five properties arranged in columns:

■■ message_id:

This column stores the ID of a message and its value.  Together with language_id, this is unique across the instance. Messages with
IDs less than 50000 are system error messages.
■■ language_id:

This column indicates the language used in the message text, for example English or Spanish, as defined in the name column of the
sys.syslanguages system table. This is unique for a specified message_id.
■■ severity:

This column contains the severity level of the message. Whe you are creating errors, keep in mind that the severity must be the same for
all message languages within the same message_id.
■■ is_event_logged:

When you want to log the event when an error israised, set the value of this column equal to1. Like severity, this column must
be the same for all message languages within the same message_id.

■■ text: 

This column stores the text of the message. This text is written in the language indicated by the language_id property.

Severity Levels :  This indicates the severity of the error and this will help us the scope of the problem and how quick you need to respond

these levels are categorized into three main levels

  1.   Information Messages:  Level (0 to 10 )

Errors in this group are purely informational and are not severe. Status information or error reports are returned with a specific error code. SQL Server doesn’t use levels 1–9 these levels are available for user-defined errors.

2.     User Errors Level (11- 16)

These do not effect service or System or Server, most of these are corrected by the user and these are errors are raised when database Engine parses the query or Tsql statement for execution

3.  Software Errors (Levels 17–19):

Errors in this group are severe and require system administrator attention as well as yours. They are related to
problems in the Database Engine service and can’t be solved by users.

4.System Errors (Levels 20–25):

Errors in this group are critical since they indicate system problems. They are fatal errors that force the end of
the statement or batch in the Database Engine. Errors in this group record information about what occurred and then terminates. You must be aware that these errors can close the application connection to the instance of SQL Server. Error messages in this severity level group are written to the error log.

Mr  Pinal Dave had explained it in detail take a look at this post

http://blog.sqlauthority.com/2007/04/25/sql-server-error-messages-sysmessages-error-severity-level/

More detailed explanation from BOL

Severity Levels 0 through 19

Error messages with a severity level of 10 are informational. Error messages with severity levels from 11 through 16 are generated by user and can be corrected by the user. Severity levels from 17 and 18 are generated by resource or system errors; the user’s session is not interrupted.

Using sp_addmessage, user-defined messages with severities from 1 through 25 can be added to sysmessages. Only the system administrator can add messages with severities from 19 through 25.

Error messages with severity levels 17 and higher should be reported to the system administrator.

Severity Level 10: Status Information

Severity level 10 is an informational message and indicates a problem caused by mistakes in the information you have entered. Severity level 10 is not visible in SQL Server 7.0.

Severity Levels 11 through 16

These messages indicate errors that can be corrected by the user.

Severity Level 17: Insufficient Resources

These messages indicate that the statement caused SQL Server to run out of resources (such as locks or disk space for the database) or to exceed some limit set by the system administrator.

Severity Level 18: Nonfatal Internal Error Detected

These messages indicate that there is some type of internal software problem, but the statement finishes, and the connection to SQL Server is maintained. For example, a level 18 message occurs when the SQL Server query processor detects an internal error during query optimization.The system administrator should be informed every time a severity level 18 message occurs.

Severity Level 19: SQL Server Error in Resource

These messages indicate that some nonconfigurable internal limit has been exceeded and the current batch process is terminated. Level 19 errors occur rarely; however, they must be corrected by the system administrator or your primary support provider.The administrator should be informed every time a severity level 19 message occurs.

Severity Levels 20 through 25

Severity levels from 20 through 25 indicate system problems. These are fatal errors, which means that the process (the program code that accomplishes the task specified in your statement) is no longer running. The process freezes before it stops, records information about what occurred, and then terminates. The client connection to SQL Server closes, and depending on the problem, the client might not be able to reconnect.

Error messages with a severity level of 19 or higher stop the current batch. Errors messages with a severity level of 20 or higher are considered fatal errors and terminate the client connection. Errors messages in this range may affect all of the processes in the database, and may indicate that a database or object is damaged. Error messages with a severity level from 19 through 25 are written to the error log.

Severity Level 20: SQL Server Fatal Error in Current Process

These messages indicate that a statement has encountered a problem. Because the problem has affected only the current process, it is unlikely that the database itself has been damaged.

Severity Level 21: SQL Server Fatal Error in Database (dbid) Processes

These messages indicate that you have encountered a problem that affects all processes in the current database. However, it is unlikely that the database itself has been damaged.

Severity Level 22: SQL Server Fatal Error Table Integrity Suspect

These messages indicate that the table or index specified in the message has been damaged by a software or hardware problem.Level 22 errors occur rarely; however, if you should encounter one, run DBCC CHECKDB to determine if other objects in the database are also damaged. It is possible that the problem is in the cache only and not on the disk itself. If so, restarting SQL Server corrects the problem. To continue working, you must reconnect to SQL Server. Otherwise, use DBCC to repair the problem. In some cases, it may be necessary to restore the database.If restarting does not help, the problem is on the disk. Sometimes it can be solved by destroying the object specified in the error message. For example, if the message tells you that SQL Server has found a row with a length of 0 in a nonclustered index, delete the index and rebuild it.

Severity Level 23: SQL Server Fatal Error: Database Integrity Suspect

These messages indicate that the integrity of the entire database is suspect due to damage caused by a hardware or software problem.Level 23 errors occur rarely; however, if you should encounter one, run DBCC CHECKDB to determine the extent of the damage. It is possible that the problem is in the cache only and not on the disk itself. If so, restarting SQL Server corrects the problem. To continue working, you must reconnect to SQL Server. Otherwise, use DBCC to repair the problem. In some cases, it may be necessary to restore the database.

Severity Level 24: Hardware Error

These messages indicate some type of media failure. The system administrator might have to reload the database. It might also be necessary to call your hardware vendor.

Implementing CLR User defined Aggregates in SQL Server

In this post we will learn how to implement UDF CLR Aggregates ,

In order to implement CLR UDF aggegate assembly we need to follow set of rules  or requirements in technical terms, if any of rules are broken then it is not a valid

CLR UDF

  1. Like any other CLR assembles in SQL Server, a CLR UDF  Aggregate should have a attribute called SqlUserDefinedAggregat

  2.    In order to save the Intermediate state of Aggregation we use Four methods in the Aggregation

    1. Init
    2. Accumulate
    3. Merge
    4. Terminate

Init : 

Syntax :  public void Init();

Once this method is invoked the Query processor Initializes the aggregation , for every aggregation we need to invoke this method.

Accumulate:

Syntax: public void Accumulate ( input-type value[, input-type value, …]);

Input type is any managed sql server data types

This where actual data aggregation occurs , it may be summing or multiplying , counting.

Merge:

This method can be used to merge another instance of this aggregate class with the current instance. The query processor uses this method to merge multiple partial computations of an aggregation.

Terminate:

public return_type Terminate();

This method completes the aggregate computation and returns the result of the aggregation. The return_type should be a managed SQL Server data type.

The basic aggregate also has four attributes which are described in Andy Novak’s article.

Attribute Comment
Format This is a compulsory enum with options Native, Unknown and UserDefined.
IsInvariantToNulls Setting this to true tells the aggregate that it doesn’t care whether or not values are null or not, it will always produce the same result.
An example of an aggregate where the value for this is false is COUNT(field) which only counts instances that are NOT NULL
IsInvariantToDuplicates Setting this to true tells the aggregate that it will produce the same results even if there are duplicate entries.
MAX() is a good example of this as no matter how many duplicates there are the MAX value will still be the MAX value.
IsInvariantToOrder Setting this to true affects how SQL Server calls the Merge method of the aggregate.
If you had a string concatenation aggregate that was parallelised then it is vitally important that the streams merge back together in the correct order.

 

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

[Serializable]
[SqlUserDefinedAggregate(Format.Native)]
public struct CountVowels
{
    // count only the vowels in the passed-in strings
    private SqlInt32 countOfVowels;

    public void Init()
    {
        countOfVowels = 0;
    }

    public void Accumulate(SqlString value)
    {
        // list of vowels to look for
        string vowels = "aeiou";

        // for each character in the given parameter
        for (int i=0; i < value.ToString().Length; i++)
        {
            // for each character in the vowels string
            for (int j=0; j < vowels.Length; j++)
            {
                // convert parameter character to lowercase and compare to vowel
                if (value.Value.Substring(i,1).ToLower() == vowels.Substring(j,1))
                {
                    // it is a vowel, increment the count
                    countOfVowels+=1;
                }
            }
        }
    }

    public void Merge(CountVowels value)
    {
        Accumulate(value.Terminate());
    }

    public SqlString Terminate()
    {
        return countOfVowels.ToString();
    }
}

After deploying the aggregate, test it by executing it on the SQL Server and verifying the correct data is returned.

This query returns a result set of the vowel count for all the values in the LastNames column in the Contact table.

SELECT LastName, COUNT(LastName) AS CountOfLastName, dbo.CountVowels(LastName) AS CountOfVowels
FROM Person.Contact
GROUP BY LastName
ORDER BY LastName

Implementing SQL CLR objects in SQL Server

Using  CLR integration we can create

  • CLR Stored Procedure
  • CLR User Defined Function (Scalar and Table valued)
  • CLR Trigger
  • CLR User defined data types
  • CLR Aggregates

Let see how to create a CLR object using Visual studio

Before even developing a CLR object  , we have to know what version of .Net framework is sql server is using for the CLR environment

I using the Sql sever 2008 , we can see here CLR runs in .Net framework v2

With the .net framework 4.0 we now have the ability to load two or more distinct versions of the CLR within a single process. In previous releases of the .net framework, a process could only load a single instance of the CLR. Given this restriction the CLR team recommended that hosts, such as SQL Server, use the LockClrVersion function to determine the version of the CLR to load prior to initialization. So although we are developing our objects in .Net framework v3.5 we will be still able add the assembly to the sql server

 

 

 

 

 

Creating SQL CLR project

or

CREATE ASSEMBLY [SQL CLR]
AUTHORIZATION [dbo]
FROM ‘C:\Users\akhil\Documents\Visual Studio 2010\Projects\SQL CLR\SQL CLR\bin\Debug\SQL CLR.dll’
WITH PERMISSION_SET = EXTERNAL_ACCESS

GO;

 

 

 

 

 

 

 

 

 

 

 

 

 

TITLE: Microsoft SQL Server Management Studio
——————————

Create failed for SqlAssembly ‘SQL CLR’. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.2500.0+((KJ_PCU_Main).110617-0026+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+SqlAssembly&LinkId=20476

——————————
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

——————————

CREATE ASSEMBLY for assembly ‘SQL CLR’ failed because assembly ‘SQL CLR’ is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission. (Microsoft SQL Server, Error: 10327)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.1600&EvtSrc=MSSQLServer&EvtID=10327&LinkId=20476

How does the Buffer manager writes the data in the sql server

How does sql server writes the data , how does it enforces ACID property ,  why does  it use a LOG file ? what is write a head? What is a Dirty page ?

Are you looking answers for the above questions , then you don’t need to worry because you came to right place

 

Before answering the above questions , you need to know what is logical write and physical write

What is Logical write?

When the page is modified in the Buffer cache it is called a Logical write

What is Physical write?

When a page is written from a Buffer cache to a physical disk it is called a Physical write

 

How it works

when a user runs an update query after it is parsed successfully the query engine finds the efficient way to access the data by comparing the different execution plans, then these keys are passed to the  storage engine which finds the exact pages in the file and brings these pages into the buffer cache(Physical read) , once the pages are in the buffer cache , the data inside the page/s will be updated in the buffer cache(logical write), once these pages inside the buffer cache are updated the data is not directly written into the database file , first the updated pages are marked as dirty pages, a dirty page can under go any number of logical writes and for each logical write the logrecord is sent to the log cache as a log record , the log record is then inserted into the log file , after log record written into the disk(log file) the dirty page is then moved from buffer cache into the disk(data file) this is called flushing the page. SQL Server uses a technique known as write-ahead logging that prevents writing a dirty page before the associated log record is written to disk(data file)

 

When the buffer manager writes a page, it searches for adjacent dirty pages that can be included in a single gather-write operation. Adjacent pages have consecutive page IDs and are from the same file; the pages do not have to be contiguous in memory. The search continues both forward and backward until one of the following events occurs:

  • A clean page is found.
  • 32 pages have been found.
  • A dirty page is found whose log sequence number (LSN) has not yet been flushed in the log.
  • A page is found that cannot be immediately latched.

    In this way, the entire set of pages can be written to disk with a single gather-write operation.