Archive

Posts Tagged ‘Stored Procedures’

Remove Not Null Constraint on a column using Alter Table statement

October 8, 2010 3 comments

Many times , it may be necessary to remove Not Null constraint used for a column in  a table. It is usually necessary to overcome unexpected integrity constraints ( rarely occurs, if designed well) against a table. We can remove the Not Null constaint by using  ALTER TABLE …. ALTER COLUMN … option.

A sample query to is as below

Continue Reading …

Advertisements

SQL Interview Questions – Basics 2

October 8, 2010 Leave a comment

What are the advantages and disadvantages of Surrogate Key ?

Pros:

  1. Business Logic is not in the keys.
  2. Small 4-byte key (the surrogate key will most likely be an integer and SQL Server for example requires only 4 bytes to store it, if a bigint, then 8 bytes).
  3. Joins are very fast.
  4. No locking contentions because of unique constraint (this refers to the waits that get developed when two sessions are trying to insert the same unique business key) as the surrogates get generated by the DB and are cached – very scalable.

Cons:

  1. An additional index is needed.  In SQL Server, the PK constraint will always creates a unique index, in Oracle, if an index already exists, PK creation will use that index for uniqueness enforcement (not a con in Oracle).
  2. Cannot be used as a search key.
  3. If it is database controlled, for products that support multiple databases, different implementations are needed, example: identity in SS2k, before triggers and sequences in Oracle, identity/sequence in DB2 UDB.
  4. Always requires a join when browsing the child table(s).

 

What are  identity columns?

An Identity column is a column (field ) in a database table that is made up of values generated by the database.

Continue Reading …

Search for a pattern in sql server using patindex() and Charindex()

October 8, 2010 2 comments

Although these functions are not commonly used, these are really powerful in searching a pattern and returning the location of text/ string/ pattern. By understanding the functionality of both the string functions, we will be able to understand the difference as well.

PATINDEX()

The function searches for a pattern in a string / expression and returns the starting position of the first occurrence of the pattern. It works similar to the LIKE operator in any query.

The general syntax is

PATINDEX ( ‘%pattern%’ , expression )

You might have already noticed the ‘%’ symbols used in the pattern. These are important, as I mentioned earlier, use the same syntax as used for searching by  LIKE operator.

Continue Reading …

Reindex the database with high fragmentation using “DBCC DBREINDEX”

October 8, 2010 Leave a comment

DBCC is one of the most commonly used commands by DBAs across the globe. Today I will introduce a simple way to reindex a specific or all indexes in a table using DBCC.

The command used for this is

DBCC DBREINDEX

General Sytax

DBCC DBREINDEX

(

table_name

[ , index_name [ , fillfactor ] ]

)

The best feature of this command is that,

Continue Reading …

 

 

SSIS Error : Resolve transformation errors in 64-bit version of SSIS in debug mode

October 8, 2010 Leave a comment

In 64 bit operating systems, SSIS transformations ( especially excel) and tasks throws errors that could be annoying. You will often come across errors as shown below.

SSIS package “Package.dtsx” starting.

Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.

Error: 0xC00F9304 at Package, Connection manager “Excel Connection Manager“: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.

Error: 0xC020801C at Data Flow Task, Excel Source [1]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager “Excel Connection Manager” failed with error code 0xC00F9304.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Error: 0xC0047017 at Data Flow Task, SSIS.Pipeline: component “Excel Source” (1) failed validation and returned error code 0xC020801C.

Error: 0xC004700C at Data Flow Task, SSIS.Pipeline: One or more component failed validation.

Error: 0xC0024107 at Data Flow Task: There were errors during task validation.

SSIS package “Package.dtsx” finished: Failure.

In Debug mode it is really easy to overcome this by doing a simple change in settings.

Continue Reading …

SQL Interview Questions – Basics 1

October 8, 2010 Leave a comment
What is RDBMS (Relational database management system)?

RDBMS is a database management system (DBMS) that is based on the relational model. Relationships may be created and maintained across and among the data and tables. In a relational database, relationships between data items are expressed by means of tables. Interdependencies among these tables are expressed by data values

What is the Difference between DBMS and RDBMS?

A DBMS has to be persistent, that is it should be accessible when the program created the data ceases to exist or even the application that created the data restarted. A DBMS also has to provide some uniform methods independent of a specific application for accessing the information that is stored.

RDBMS is a Relational Data Base Management System Relational DBMS. This adds the additional condition that the system supports a tabular structure for the data, with enforced relationships between the tables. This excludes the databases that don’t support a tabular structure or don’t enforce relationships between tables.

 What are the properties of the RDBMS tables?

1         Values are atomic.

2         Column values are of the same kind.

3         Each row is unique.

4          Each column must have a unique name.

5         The sequence of columns is insignificant.

6         The sequence of rows is insignificant.

Continue Reading …

what does NULLIF() do ? how is it different from ISNULL()?

October 7, 2010 3 comments

NULLIF( ) : Returns a null value if the two expressions specified in the function are equal.

The syntax used is

NULLIF ( expression1 ,expression2 )

If expression1 = expression2, then the function will return NULL.

ISNULL( ): The function replaces a null expressions with a specified value.

The syntax used is

Continue Reading …