31.7.08

Drop all tables in a Database

SQL server is providing a nice option to drop all the tables at a stretch. Below is the query to achieve the same.
EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"
The above command will traverse each table and drop it.
sp_MSforeachtable is an undocumented stored procedure.
We can delete single table too.
EXEC sp_MSforeachtable @command1 = "drop table aa"
This sp will take a sql query as parameter and execute it.

Happy Learning!!!
Regards,
Venkatesan Prabu .J

Database Log file size in SQL Server

Below is the query used to find the log size for each database.
DBCC SQLPERF ( LOGSPACE )

Regards,

Venkatesan Prabu .J

Substring in a string in SQL Server

Charindex is a nice function used to check the existence of a substring in a string. Below is the sample code to check the existence of string.
declare @str varchar(10)
declare @substr varchar(5)
set @str = 'Venkat'
set @substr='nak'
if charindex(@substr,@str) >0
Print 'String available'
else
Print 'String not available'

Regards,

Venkatesan Prabu .J

Change row name in SQL Server

We know how to change the column in the result set. It can be achieved using alias name. Have you tried to change row name in SQL server. Check this query,
CREATE TABLE VENKAT1(ID INT,NAM VARCHAR(10))
INSERT INTO VENKAT1 VALUES(1,'VENKAT')
INSERT INTO VENKAT1 VALUES(2,'ARUN')
SELECT * FROM VENKAT1
SELECT
CASE WHEN NAM='VENKAT' THEN 'SANTHI'
ELSE NAM
END
FROM VENKAT1
Happy Learning!!!
Regards,
Venkatesan Prabu .J

Compare two tables in SQL Server

I have come through a nice article to compare two tables, whether its same or different.
Simple and Spectacular article and wish to write the same in my blog.
Below code will be used to compare two tables in SQL Server
CREATE TABLE VENKAT1(ID INT,NAM VARCHAR(10))
CREATE TABLE VENKAT2(ID INT,NAM VARCHAR(10))
INSERT INTO VENKAT1 VALUES(1,'VENKAT')
INSERT INTO VENKAT1 VALUES(2,'ARUN')
INSERT INTO VENKAT2 VALUES(1,'VENKAT1')
INSERT INTO VENKAT2 VALUES(2,'ARUN')
SELECT * FROM VENKAT1
SELECT * FROM VENKAT2
SELECT
CASE WHEN COUNT(*) = 0 THEN 'Same' ELSE 'Different' END
FROM (
(
SELECT * FROM VENKAT1
EXCEPT
SELECT * FROM VENKAT2
)
UNION (
SELECT * FROM VENKAT2
EXCEPT
SELECT * FROM VENKAT1
)
) dv


Happy Learning!!!
Regards,
Venkatesan Prabu .J

30.7.08

Collation in SQL Server

Collation in sql server is an importan property to handle the case sensitiveness of the data.
Collation on the server can be forced in two ways either,
1. While installation or
2. During our operation or business logic execution.
You can check the collation details by right clicking your server properties.
If we want to assign a specific collation for your database. In that case, you can achieve it using the below query,
ALTER DATABASE VenkatDB COLLATE Latin1_General_CI_AS
To know the collation types in your server. You can run the below query,
SELECT * FROM fn_helpcollations()
Microsoft have provided a nice function which will fetch all the collations available in your server.

Happy Learning!!!

Regards,

Venkatesan Prabu .J

27.7.08

Disable trigger to fire other triggers

Sometimes, we will use some trigger to perform a particular operation inturn that trigger may fire another trigger. It's casual problem and lets see how to stop trigger to fire other triggers.
1. Right click server properties->Advanced->Allow triggers to fire others property to false.


Else we can achieve it using T-SQL statement too. Lets see the second method to achieve the same.




Execute the below queries to achieve the same,
Code Snippet

EXEC sp_configure 'nested triggers', 0



Code Snippet
RECONFIGURE

Happy Learning!!!
Regards,
Venkatesan Prabu. J

T-SQL Challenges in SQL Server

Question: I have a table with 1 columns, and I would like to change the predefined value 'NULL' to other value, that can be the same for all rows in the table. How can we achieve this.
Solution:

Code Snippet

create table #a (nam varchar(10))
insert into #a values('aa')
insert into #a values('ba')
insert into #a values('ca')
with cte as
(
select 1 as id
)
update #a set nam = (select * from cte)
select * from #a

Happy Learning!!!

Regards,

Venkatesan Prabu. J

Column filteration based on Data type

I got a peculiar question, Is it possible to fetch the columns which are not varchar or any other datatype. OOPs its new to me and sql server 2005 provides a simple feature name Information schemas. Using this, we can fetch the information. Below is the query,

Code Snippet

select * from INFORMATION_SCHEMA.COLUMNS where DATA_TYPE not in ('varchar') and table_name='venkat'

Regards,

Venkatesan Prabu. J

26.7.08

Columns into rows in sql server

SQL Server 2005 is providing a cool feature Unpivot to convert columns data into rows. But, in our legacy system we dont have this feature. Inturn, we have to create some temporary tables to achieve the same. I have tried it to convert columns into rows. There are thousands of ways to achieve this task. I have tried one among them,

Code Snippet

create table aaaa(id int,nam varchar(10),city varchar(10))
insert into aaaa values(1,'aa','sydney')
insert into aaaa values(2,'bb','delhi')
insert into aaaa values(3,'cc','chennai')
select * from aaaa
create table #temp1(id int, nam varchar(10))
insert into #temp1
select id,nam from aaaa
create table #temp2(id int, nam varchar(10))
insert into #temp2
select id,city from aaaa

Below is the ouput we are getting for the table aaaa


We are trying to convert it into rows, so the output should resemble similar to the below screenshot.

Code Snippet

select id,nam from #temp1 union all select * from #temp2
order by #temp1.id

Happy Learning!!!.
Regards,
Venkatesan Prabu. J

Rows to Columns in SQL Server

In this article, Lets see how to convert your table rows into column wise. We can achieve it using pivot operation in SQL Server 2005. I achieved the same with some other logics.

Code Snippet
create table VenkatTable (id int, question varchar(20),answer varchar(20))
insert into VenkatTable values(1,'Name','Manoj')
insert into VenkatTable values(1,'Qualification','BE')
insert into VenkatTable values(1,'Nationality','Indian')
insert into VenkatTable values(2,'Name','Venkat')
insert into VenkatTable values(2,'Qualification','BE')
insert into VenkatTable values(3,'Qualification','BE')
select * from VenkatTable



The above queries will create a table as below,



I need a result set as below screen shot. It can be achieved using pivot operator in SQL Server 2005. I have tried with some other logic to achieve the same. Lets see how to achieve the below output.



Code Snippet

SELECT id,
[Name]= (SELECT answer FROM VenkatTable WHERE question = 'Name' and id=m.id),
[Qualification]= (SELECT answer FROM VenkatTable WHERE question = 'Qualification' and id=m.id) ,
[Nationality]= (SELECT answer FROM VenkatTable WHERE question = 'Nationality' and id=m.id)
FROM VenkatTable m
GROUP BY id

Happy Learning!!!
Regards,
Venkatesan Prabu .J

DDL Triggers in SQL Server 2005

DDL Trigger -> Its a new concept introduced in SQL Server 2005.Its used to control and monitor the DDL statements executed in our database.Consider the below example,I have created a DDL trigger for drop statement. Now, if a user tried to drop my tables. It will be automatically throw an error message.


Code Snippet

CREATE TRIGGER DDLTriggertoSaveTable ON DATABASE FOR DROP_TABLE

AS PRINT 'M No way to delete the tables in the Database?'


Now, execute the below statement,

Code Snippet
Drop Table VenkatTestDLLTriggers



You will get a message indicating "Cannot drop the tables in the database.

Happy Learning!!!

Regards,

Venkatesan Prabu .J

Installation problem in SQL Server

I am having SQL Server installed in my machine and i need to uninstall the same for some of the following reasons,

1. To move to an upgraded versions of sql server

2. Problem in SQL Server

In that case, we will find some problem in upgrading or repairing the existing server. In that case, Microsoft have given a nice article to resolve our problems. Please check the below KB URL,

http://support.microsoft.com/kb/909967

Happy Learning!!!
Regards,
Venkatesan prabu. J

To fetch all the constraints in SQL Server

Lets discuss one of the interesting features in SQL Server 2005 - Information_Schema.
Its a cool feature available in SQL Server 2005. Inorder to avoid direct access on the system tables. Microsoft have intoduced a layered structure to access the system related information. They have used views to achieve the same.
In the below query, I am trying to fetch all the Primary key and foreign key constriants available in the database.
Code Snippet
select CONSTRAINT_NAME,TABLE_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS

Happy Learning!!!

Regards,

Venkatesan prabu .J

Retrieve Owner of the database

Let me list down, how to retrieve the database owner of your database.
1. Right click database properties and -> General -> owner you can view the database owner of this particular datbase.

2. Else we can write a T-SQL Statement to acheive the same.
Code Snippet
select suser_sname(owner_sid) from sys.databases

Happy Learning!!!

Regards,

Venkatesan Prabu .J

20.7.08

SSIS error - Part 8

[OLE DB Destination [70]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005
Description: "Unspecified error".
[OLE DB Destination [70]] Error: There was an error with input column "id" (410) on input "OLE DB Destination Input" (83). The column status returned was: "The value
violated the integrity constraints for the column.".
[OLE DB Destination [70]] Error: The "input "OLE DB Destination Input" (83)" failed because error code 0xC020907D occurred, and the error row disposition on "input "OLE DB
Destination Input" (83)" specifies failure on error. An error occurred on the specified object of the specified component.
[DTS.Pipeline] Error: The ProcessInput method on component "OLE DB Destination" (70) failed with error code 0xC0209029. The identified component returned an error from the
ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

Solution : The problem is due to insertion of already existing data into the primary key column.
Please delete those existing data or insert some valid data to resolve this problem
Regards,
Venkatesan Prabu .J

SSIS error - Part 7

[OLE DB Destination [1313]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult:
0x80004005 Description: "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Cannot
insert the value NULL into column 'create_date', table 'Master.dbo.VenkatTable'; column does not allow nulls. INSERT fails.".

Problem: The problem is due to insertion of invalid data into the column.
Solution: Since, the column wont allow null value, either you have to change the type of the column by using alter statement else provide some valid integer/character during data insertion.
Happy learning!!!
Regards,
Venkatesan prabu .J

SSIS Error - Part 6

[Execute SQL Task] Error: Executing the query "Your query " failed with the following error:
"Multiple identity columns specified for table 'Tmp_VenkatTable'. Only one identity column per table is allowed.". Possible failure reasons: Problems with the query,
"ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

The error is due to the identity column. SQL wont allow more than one identity column in the table.
Solution:
Please remove the excessive identity column from the table.
Happy Learning!!!
Regards,
Venkatesan Prabu .J

SSIS Errors - Part 5

While transferring data from source to destination, we used to see the below warnings in our SSIS packages,
[DTS.Pipeline] Warning: The output column "A" (6643) on output "Union All Output 1" (2177) and component "Joining the Table" (2175) is not subsequently used in the Data Flow
task. Removing this unused output column can increase Data Flow task performance.

Solution: I have used a "Union All" task which will fetch more data and am not using all the data in the destination inturn, am using some selected columns in the destination. SSIS indicating us to remove the unused output column from Union All task. Inturn, this will increase the performance of our SSIS package.
Happy Learning!!!
Regards,
Venkatesan Prabu .J

19.7.08

SSIS Errors - Part 4

If we try to connect Sybase database, we will get following warnings,
[OLE DB Source [1]] Warning: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.
Its because of default code page file which should be available in "C:\Program Files\Microsoft SQL Server\90\DTS\MappingFiles" for that particular server.
If the code page file is not available then, your SSIS package will show the warning "Unable to load the file.

Below screen shot shows the default code page available in your machine.










To resolve this problem, Right click your OLEDB Source or Detination(Sybase) and enable the property "AlwaysuseDefaultcodepage" to true.

Happy Learning!!!

Regards,
Venkatesan prabu .J

SSIS errors - Part 3

While transferring data from source to destination, we used this get this error.
[OLE DB Destination [2357]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object
Reason and Solution:
The problem is due to invalid insertion of data which affect the integrity property of the data. Suppose, If the user tries to insert the data which already exists in the column(primary key column). Then we will get the error.
To resolve this, either we need to remove the existing or new data or we need to modify the data before inserting into the primary key column.
Happy Learning!!!
Regards,
Venkatesan prabu .J

Unknown things in Identity_insert statement

Unknown things about Identity Insert:
Considering, am having a requirement to insert the data in the identity column as below,


Code Snippet

create table VenkatTable (id int identity,[name] varchar(100))

insert into VenkatTable values (10,'Santhi')

You will get an error,
"An explicit value for the identity column in table 'VenkatTable' can only be specified when a column list is used and IDENTITY_INSERT is ON."
Solution:
The problem is with the identity column. It won't allow us to insert the data explicitly inturn we need to switch off the identity property to implement our requirement.

Code Snippet

set identity_insert VenkatTable on

insert into VenkatTable values (10,'Santhi')

On Executing the above statement, am getting the same error.. OOOOPS!!!

"An explicit value for the identity column in table 'VenkatTable' can only be specified when a column list is used and IDENTITY_INSERT is ON."

Let's analyze the problem once again. I've tried with the below statement.

Code Snippet
insert into VenkatTable(id,[name]) values(10,'Santhi')

Yahoooo, its inserting the data now. SQL request us to specifically pointout the column names in the insert statement.
After our requirement, we need to Switch off the identity_insert property by using the below statement.
set identity_insert VenkatTable off
Happy Learning!!!
Regards,
Venkatesan prabu .J

SSIS Errors - Part 2

While transfering the data from Source to Destination, if there is data type mismatch we used to get the following error.
Messages
* Error 0xc0202009: Data Flow Task: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification".

The problem is with the matching columns between source and destination

Considering am having id column in source as varchar(10) and id column in the destination is int. Then if we try to execute the package.Obviously, we will get an error.
Solution: 1. We need to change the data type in the source table.
2. We need to write some conversion scripts for varchar to int type for the id column. You can prefer Derived column task to do this conversion.
Happy Learning!!!
Regards,
Venkatesan Prabu .J

9.7.08

SSIS Error

Sometimes, On executing Execute SQL task with huge tables as input will throw the following exception,


"The query has been cancelled because the estimated cost of this query (Integer) exceeds the configured threshold of (Integer). Contact the system administrator.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."


Reason:


It's because the current query execution exceeds the threshold server execution time.


Solution:


You can opt different solutions for this problem. Let me explain two types of solutions among them,
1. Setting the Query governor value to higher limit.
2. Right click the database properties - >connections -> use query governor to prevent long running queries.









3. Increase the value if already exists.
4. Else, you can write a T-SQL query to achieve the same,
SET QUERY_GOVERNOR_COST_LIMIT 1000(I have given 1000 as sample)


2. If you dont have administrative right to achieve the same you can opt less number of queries or less number of accessing tables.
Regards,
Venkatesan prabu .J

4.7.08

Microsoft MVP

Wish to share a great news..

On July 1, I have got a nice news from microsoft regarding my MVP award. I have been selected as Microsoft MVP for the year 2008-2009 on SQL Server.

It's really shocking and i am wondering on seeing myself as a great MVP.

Happy learning!!!

Sybase to SQL Server using SQL Server Integration services(SSIS)



While connecting with a remote sybase database, we used to find some problems in connecting the table to retrieve the data. I have tried to connect my sybase database by opening a OLEDB connection manager. Check the screen shot below,










SSIS doesn't allow to preview the data. I have tried to click "preview button. But, am getting a dataflow task error.

I tried to click "Ok" button, am unable to create a OLEDB source task ended up with an error.


Reason for the error: I started exploring the reason for the error and found that the problem is with the "dbo" string which gets appended with my sybase table. While contacting the server, my ssis couldn't find the table like "dbo.tableA". Below is the error string which i have got for this problem.






"Error at Data Flow Task [OLE DB Source [211]]: An OLE DB error has occurred. Error code: 0x80040E37.An OLE DB record is available. Source: "ASE OLE DB Provider" Hresult: 0x80040E37 Description: "[Native Error code: 102][DataDirect ADO Sybase Provider] Incorrect syntax near '.'.".
Error at Data Flow Task [OLE DB Source [211]]: Opening a rowset for ""dbo"."TableA"" failed. Check that the object exists in the database. "

Solution:
Change the dataaccess mode to SQL Command, Just type your queries parsed it.
Click "Ok". It will show a validation error. "No worries".

You SSIS OLEDB source is ready.
Happy Learning!!!

Regards,
Venkatesan Prabu .J

My T-SQL Gallery @code.msdn.microsoft


Created my own T-SQL Gallery in Microsoft site. Do visit the same and share your feedback,

http://code.msdn.microsoft.com/VenkatSQLSample/Thread/List.aspx

Thanks and Regards,
Venkatesan Prabu .J

SQL Server Interview questions - Part 1

What is the significance of NULL value and why should we avoid permitting null values?
Null means no entry has been made. It implies that the value is either unknown or undefined.We should avoid permitting null values because Column with NULL values can't have PRIMARY KEY constraints. Certain calculations can be inaccurate if NULL columns are involved.

What is SQL whats its uses and its component ?
The Structured Query Language (SQL) is foundation for all relational database systems. Most of the large-scale databases use the SQL to define all user and administrator interactions. It enable us to retrieve the data from based on our exact requirement. We will be given a flexibility to store the data in our own format.


The DML component of SQL comprises four basic statements:
* SELECT to get rows from tables
* UPDATE to update the rows of tables
* DELETE to remove rows from tables
* INSERT to add new rows to tables


What is DTS in SQL Server ?
Data Transformation Services is used to transfer the data from one source to our required destination. Considering am having some data in sql server and I need to transfer the data to Excel destination. Its highly possible with dialogue based tool called Data Transformation services. More customization can be achieved using SSIS. A specialized tool used to do such migration works.


What is the difference between SQL and Pl/Sql ?

Straight forward. SQL is a single statement to finish up our work.Considering, I need some data from a particular table. “Select * from table” will fetch the necessary information. Where as I need to do some row by row processing. In that case, we need to go for Procedural Logic / SQL.

What is the significance of NULL value and why should we avoid permitting null values?
Null means no entry has been made. It implies that the value is either unknown or undefined.We should avoid permitting null values because Column with NULL values can't have PRIMARY KEY constraints. Certain calculations can be inaccurate if NULL columns are involved.

Difference between primary key and Unique key?
Both constraints will share a common property called uniqueness. The data in the column should be unique. The basic difference is,
· Primary key won’t allow null value. Whereas, unique key will accept null value but only one null value.
· On creating primary key, it will automatically format the data inturn creates clustered index on the table. Whereas, this characteristics is not associated with unique key.
· Only one primary key can be created for the table. Any number of Unique key can be created for the table.

Select Statement in SQL Server

Select Statement in SQL Server

String Functions in sql server

String Functions in sql server
Substring/Len/replace/Ltrim/Rtrim

SQL Server Interview Question - Part 2

What is normalization?

Normalization is the basic concept used in designing a database. Its nothing but, an advise given to the database to have minimal repetition of data, highly structured, highly secured, easy to retrieve. In high level definition, the Process of organizing data into tables is referred to as normalization.


What is a stored procedure:
Stored procedures are precompiled T-SQL statements combined to perform a single task of several tasks. Its basically like a Macro so when you invoke the Stored procedure, you actually run a set of statements. As, its precompiled statement, execution of Stored procedure is compatatively high when compared to an ordinary T-SQL statement.


What is the difference between UNION ALL Statement and UNION ?
The main difference between UNION ALL statement and UNION is UNION All statement is much faster than UNION,the reason behind this is that because UNION ALL statement does not look for duplicate rows, but on the other hand UNION statement does look for duplicate rows, whether or not they exist.

Example for Stored Procedure?
They are three kinds of stored procedures,1.System stored procedure – Start with sp_2. User defined stored procedure – SP created by the user.3. Extended stored procedure – SP used to invoke a process in the external systems.Example for system stored proceduresp_helpdb - Database and its propertiessp_who2 – Gives details about the current user connected to your system. sp_renamedb – Enable you to rename your database


What is a trigger?

Triggers are precompiled statements similar to Stored Procedure. It will automatically invoke for a particular operation. Triggers are basically used to implement business rules.


What is a view?
If we have several tables in a db and we want to view only specific columns from specific tables we can go for views. It would also suffice the needs of security some times allowing specfic users to see only specific columns based on the permission that we can configure on the view. Views also reduce the effort that is required for writing queries to access specific columns every time.


What is an Index?
When queries are run against a db, an index on that db basically helps in the way the data is sorted to process the query for faster and data retrievals are much faster when we have an index.


What are the types of indexes available with SQL Server?

There are basically two types of indexes that we use with the SQL ServerClustered -

1. It will format the entire table, inturn physically sort the table.

2. Only one clustered index can be created for a table.

3. Data will be located in the leaf level.

4. By default, primary key will create clustered index on the table.

Non-Clustered Index

1. It wont touch the structure of the table.

2. It forms an index table as reference to the exact data.

3. A reference to the data will be located in the leaf level.

4. For a table, we can create 249 non clustered index.

Happy Learning!!!
Regards,
Venkatesan Prabu .J

SQL Interview question

Extent Vs Page?

Pages are low level unit to store the exact data in sql server. Basically, the data will be stored in the mdf, ldf, ndf files. Inturn, pages are logical units available in sql server.The size of the page is 8KB.

Eight consecutive pages will form an extent 8 * 8KB = 64KB.

Thus I/O level operation will be happening at pages level.The pages will hold a template information at the start of each page (header of the page).

They are,

1. page number,

2. page type,

3. the amount of free space on the page,

4. the allocation unit ID of the object that owns the page.

Extents will be classifed into two types,

1. Uniform extents

2. Mixed extents

Uniform Extents:It occupied or used by a single object. Inturn, a single object will hold the entire 8 pages.Mixed

Extents:Mulitple objects will use the same extent. SQL Server will allow a max of eight objects to use a shared extent.

Property of SQL Server :Initally if an object is created, sql server will allocate the object to the mixed extent and once if the size reaches 8 pages and more... immediately, a new uniform extent will be provided for that particular object.

Herecomes, our fragmentation and reindexing concepts.



Best Joke - Enjoy it

Best Joke - Enjoy it