28.4.10

Debugging in SQL Server

In SQL Server 2008, we can debug the procedure step by step. Place a break point by clicking on the left hand side of your query window. A dot will come, which indicates the control will stop at this point. From there, we are allowed to debug the procedure.


We have been provided with quick watch/Add watch options to monitor the value of a variable in the procedure.



Steps for debugging:


Press Alt+f5 or goto Debug option and click "Start debugging". If you are familiar with Visual studio then its too handy to debug in SSMS. F11 to go each and every step inside the function. Where as F10 will jump to the next line control.




How about in SQL Server 2005. Do we have any option to debug a procedure in SQL Server 2005?

SSMS doesn't have an option to debug the procedure but this can be achieved using Visual studio.
Goto Tools -> Connect to Database -> You will get Data connections -> right click it and select Add Connection -> Provide your server, appropriate database and credentials to connect.

Your database will be displayed. Goto ->Stored Procedure and select your stored proc.
Right click -> Step into Stored Procedure -> Your debug window will open.



Provide necessary inputs and you can start debugging your stored procedure



You can see your procedure running in the debug mode.

Cheers,
Venkatesan Prabu .J

26.4.10

Finding the length of the string without STRLEN function

Finding the length of the string without STRLEN function:

I have seen a request in one of the forum on String Lenght function. Finding the string length with out STRLEN built-in function. Nice question right !!!

Solution:
Here is the solution,

1. First -> Replace all the empty character in the string to * or any other special character.
2. Second -> Check for each letter in the string for empty until it reaches the end.
3. Third -> The string will have an empty character in the end(By default)
4. Fourth -> Get the Count of the string.

**********************************************************************************
ALTER PROCEDURE dbo.LEN_WITHOUT_STRLEN_FUNCTION(@String VARCHAR(8000))
AS
BEGIN
DECLARE @CNT INT, @IDX INT, @Flag INT
SELECT @String = REPLACE(@String, ' ','*'), @CNT = 0, @IDX = 1, @Flag = 1
WHILE ( @Flag = 1 )
BEGIN
IF ((SELECT SUBSTRING(@String,@IDX,1)) <> '')
BEGIN
SET @CNT = @CNT + 1
END
ELSE
BEGIN
SET @Flag = 0
END
SET @IDX = @IDX + 1
END
PRINT @CNT
END


**********************************************************************************

EXEC LEN_WITHOUT_STRLEN_FUNCTION 'VENKATESAN PRABU'

**********************************************************************************

Cheers,
Venkatesan Prabu .J

21.4.10

Converting Varchar to Float in SQL Server

NVARCHAR OR VARCHAR TO FLOAT IN SQL SERVER:

For converting the data from one datatype to another, you need to use CONVERT function.

The syntax is CONVERT(DATATYPE,COLUMN NAME)

SAMPLE QUERY:

CREATE TABLE VENKAT_TAB (ID INT, VAL nVARCHAR(100))

INSERT INTO VENKAT_TAB VALUES (1,'10.453425345')


SELECT ID, CONVERT(FLOAT,VAL) AS FLOATVAL FROM VENKAT_TAB

Cheers,
Venkatesan Prabu. J

Cascade delete option in SQL Server - Deleting multiple table records

CASCADE DELETE OPTION IN SQL SERVER:

I have seen lot of SQL developers were struggling to delete multiple tables simultaneously.

Scenario: I have deleted the master table and I would like delete those records in the child tables. Is it possible to delete multiple tables at the same time. SQL Server doesn't have an option to delete multiple tables at the same time. As a work around, there are some other options we can do. Below are the options,

1. Place the delete statements inside a transaction. Delete the child table first followed by master table and afterwards commit the transaction.

2. Create a trigger on the master table which will get invoked on delete operation. It will automatically delete the child tables data.

3. Third option is, Cascade delete option. This will be set during the Foreign key relation creation stage.

Below is a detailed article on Option 3

Primary Key : A column which helps us to uniquely identify a record in the table.

Foreign Key : A column which is referring a primary key on the other table and holds reference of a remote table record.



-- Am creating a table with ID as primary key.

This table holds primary key on the column ID.


CREATE TABLE [dbo].[VENKAT_TABLE](
[ID] [int] NOT NULL,
[NAM] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
[NAME1] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
CONSTRAINT [PK_VENKAT_TABLE] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

-------------------------------------------------------------------------

--Creating second table with ID column in the previous table as reference

This table mapped to a foreign key which is a primary key in another table with option Cascade delete.


CREATE TABLE [dbo].[VENKAT_TABLE1](
[ID] [int] NOT NULL,
[NAM] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
CONSTRAINT [PK_VENKAT_TABLE1] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [master]
GO
ALTER TABLE [dbo].[VENKAT_TABLE1] WITH CHECK ADD CONSTRAINT [FK_VENKAT_TABLE1_VENKAT_TABLE] FOREIGN KEY([ID])
REFERENCES [dbo].[VENKAT_TABLE] ([ID])

ON DELETE CASCADE

-------------------------------------------------------------------------

-- Create third table with ID in the Frist table as reference

This table mapped to a foreign key which is a primary key in another table with option Cascade delete.


GO
CREATE TABLE [dbo].[VENKAT_TABLE2](
[ID] [int] NOT NULL,
[NAM] [varchar](100) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [master]
GO
ALTER TABLE [dbo].[VENKAT_TABLE2] WITH CHECK ADD CONSTRAINT [FK_VENKAT_TABLE2_VENKAT_TABLE] FOREIGN KEY([ID])
REFERENCES [dbo].[VENKAT_TABLE] ([ID])
ON DELETE CASCADE

-------------------------------------------------------------------------

OOPS, in the third SQL Statement, I got the below error. Why?

"The ALTER TABLE statement conflicted with the FOREIGN KEY constraint"

Reason: Third table has some data whereas First table doesn't have any data. So, we can't create any reference at this stage. I have deleted all the records and recreated the relation ship. Yes, am able to create it.

----------------------------------------------------------------------------

INSERT INTO VENKAT_TABLE VALUES(1,'VENKAT','JAYAKANTHAM')
INSERT INTO VENKAT_TABLE VALUES(2,'SUBA','SUNDARESAN')
INSERT INTO VENKAT_TABLE VALUES(3,'KRISHIV','VENKAT')


INSERT INTO VENKAT_TABLE1 VALUES(1,'VENKAT')
INSERT INTO VENKAT_TABLE1 VALUES(2,'SUBA')
INSERT INTO VENKAT_TABLE1 VALUES(3,'KRISHIV')

INSERT INTO VENKAT_TABLE2 VALUES(1,'VENKAT')
INSERT INTO VENKAT_TABLE2 VALUES(2,'SUBA')
INSERT INTO VENKAT_TABLE2 VALUES(3,'KRISHIV')


--- JOINING THE TABLE USING THEIR RELATIONSHIPS --------------------
SELECT * FROM VENKAT_TABLE A INNER JOIN VENKAT_TABLE1 B ON A.ID=B.ID
INNER JOIN VENKAT_TABLE2 C ON B.ID=C.ID


------------ DELETE MASTER TABLE WILL DELETE THE OTHER TABLE DUE TO CASCADE DELETE OPTION IN FOREIGN KEY CONSTRAINT CREATION.------------

DELETE VENKAT_TABLE
FROM VENKAT_TABLE A INNER JOIN VENKAT_TABLE1 B ON A.ID=B.ID
INNER JOIN VENKAT_TABLE2 C ON B.ID=C.ID



select * from VENKAT_TABLE -- Will provide empty records
select * from VENKAT_TABLE1 -- Will provide empty records
select * from VENKAT_TABLE2 -- Will provide empty records

----------------------------------------------------------------------------
Similarly, we can do this for update operations too. Updating the master table will get reflected in the child table.

Cheers,
Venkatesan Prabu .J

20.4.10

Order by in the Varchar column

I have seen a request in one of the popular forum to order by a varchar column (Only on the integers and not on the varchar data).

Below is the sequence of queries to do an order by on the varchar column.

-- Drop the table, If we have anything in the table

DROP TABLE VENKAT_TABLE


-- Create a table named VENKAT_TABLE

CREATE TABLE VENKAT_TABLE (ID VARCHAR(10))
INSERT INTO VENKAT_TABLE VALUES('a')
INSERT INTO VENKAT_TABLE VALUES('d')
INSERT INTO VENKAT_TABLE VALUES('e')
INSERT INTO VENKAT_TABLE VALUES('y')
INSERT INTO VENKAT_TABLE VALUES('1')
INSERT INTO VENKAT_TABLE VALUES('2')
INSERT INTO VENKAT_TABLE VALUES('3')
INSERT INTO VENKAT_TABLE VALUES('4')
INSERT INTO VENKAT_TABLE VALUES('5')
INSERT INTO VENKAT_TABLE VALUES('9')


--Below select statement will get all the records from the table.

SELECT * FROM VENKAT_TABLE




--Below select statement will get all the records from the table with order by on the column.


SELECT * FROM VENKAT_TABLE
ORDER BY ID DESC



--Below select statement will get all the records from the table with order by on the column.


SELECT * FROM VENKAT_TABLE
ORDER BY ISNUMERIC(ID) ASC


Cheers,
Venkatesan Prabu .J

Finding the n th Highest value in SQL Server

Finding the n th Highest value in SQL Server:

In most of the SQL interviews, you will face this question frequently. So, I would like to write an article on this.

Scenario: Find the 6th highest ID in a table


Possible Solutions:

Am creating a table variable @VenkatTable,

declare @VenkatTable table(ID int)
Insert into @VenkatTable values(100)
Insert into @VenkatTable values(200)
Insert into @VenkatTable values(1400)
Insert into @VenkatTable values(2500)
Insert into @VenkatTable values(300)
Insert into @VenkatTable values(900)
Insert into @VenkatTable values(906)
Insert into @VenkatTable values(30000)
Insert into @VenkatTable values(12300)
Insert into @VenkatTable values(20000)

--USING GROUP BY STATEMENT:

First Option is by using group by operator. I have used SelfJoin to and fetch the count of records received and the count is equal to 6.

SELECT t1.ID FROM @VenkatTable t1 INNER JOIN @VenkatTable t2 ON t1.ID<=t2.ID GROUP BY t1.ID HAVING COUNT(t1.ID)=6

--USING TOP STATEMENT:

Second option is to use the Top operator. Get the Top 6 by desc and Fetch the first one by desc.

SELECT TOP 1 ID FROM ( SELECT TOP 6 ID FROM @VenkatTable ORDER BY ID DESC ) T ORDER BY ID ASC

--USING ROW_NUMBER

In SQL Server 2005, you can use row_number to fetch the record.

SELECT ID FROM (SELECT ROW_NUMBER() OVER (ORDER BY ID DESC) T ,* FROM @VenkatTable)TEMP WHERE TEMP.T=6

--USING RANK

As an alternative to the row_number, we are having rank function to fetch the record.

SELECT ID FROM ( SELECT RANK() OVER (ORDER BY ID DESC) R,* FROM @VenkatTable)TEMP WHERE TEMP.R=6

-- USING DENSERANK

If the values are too close or same. In that case, we can opt DenseRank function.

SELECT ID FROM ( SELECT DENSE_RANK() OVER (ORDER BY ID DESC) R,* FROM @VenkatTable)TEMP WHERE TEMP.R=6

-- USING WHERE CONDITION

A normal select statement in a correlated query will provide you the output.

SELECT ID FROM @VenkatTable as V WHERE (SELECT COUNT (*) FROM @VenkatTable where ID>=v.ID)=6



Cheers,

Venkatesan Prabu .J

16.4.10

Generate XSD from XML

I got an opportunity to read XML through SSIS. While reading the xml process, I have to create XSD. OOPs, I don't have an option to achieve the same. Even, SSIS holds a button to generate XSD. Unfortunately, this is not working for me.

Searched in Google and found the option of creating XSD. Microsoft is providing xsd tool to convert XML to XSD.

1. Once you have Visual studio installed, search for XSD.exe
2. Start ->Run -> goto the XSD path and execute the command > XSD xml_filename
3. You XSD will be created in the Command prompt specified path.

----------------------------------------------------------------------------------
XML:

XML to XSD using Command prompt:

XSD Created:


Cheers,
Venkatesan Prabu .J

15.4.10

Unpivot in SQL Server 2005



DROP TABLE VENKAT_SAMPLE
CREATE TABLE VENKAT_SAMPLE(PART1 VARCHAR(100),PART2 VARCHAR(100),
PART3 VARCHAR(100),PART4 VARCHAR(100),PART5 VARCHAR(100),[DATE] DATETIME)
INSERT INTO VENKAT_SAMPLE VALUES('XYZ','ABC','PQR','EFG','PQR','11/04/2010')
INSERT INTO VENKAT_SAMPLE VALUES('','XYZ','PQR','ABC','','12/04/2010')
INSERT INTO VENKAT_SAMPLE VALUES('ABC','PQR','','XYZ','EFG','4/13/2010')
INSERT INTO VENKAT_SAMPLE VALUES('ABC','PQR','','XYZ','EFG','4/14/2010')


SELECT * FROM VENKAT_SAMPLE;


WITH VENKAT_CTE AS
(
SELECT [DATE], PART,Orders
FROM
(SELECT [DATE], PART1,PART2,PART3,PART4,PART5
FROM VENKAT_SAMPLE ) p
UNPIVOT
(Orders FOR PART IN
(PART1,PART2,PART3,PART4,PART5)
)AS unpvt
)
SELECT COUNT(1) AS COUNT,ORDERS FROM VENKAT_CTE
WHERE [DATE] >= '6/14/2010' GROUP BY ORDERS


Cheers,
Venkatesan Prabu .J

Data from one database to another database

Copy data from One database to Another database:

There are various options to copy data from one database to another database.

1. First Option -> We will think about is - Using DTS wizard.
2. Second Option -> We can go for SSIS packages.
3. Thirst Option -> A very simple query as below,

Table and data created in VenkatDB will be moved to SubaDB

USE VENKATDB
CREATE TABLE VENKAT_TABLE(ID INT,NAME VARCHAR(100))
INSERT INTO VENKAT_TABLE VALUES(1,'VENKAT')
INSERT INTO VENKAT_TABLE VALUES(2,'SUBA')
SELECT * FROM VENKAT_TABLE


USE SubaDB
SELECT * INTO VENKAT_TABLE1 FROM VenkatDB.dbo.VENKAT_TABLE


A new table venkat_table1 will be created in te second DB with all the records populated in the second table in the second DB.

Cheers,
Venkatesan Prabu .J

14.4.10

Defaults in sql server

Default:
Default is one of the nice concept in SQL Server. It enables us to assign a default value to the table. While inserting a record in the table, if we didnt specify any values to a particular column the data will be null (If the column is a nullable column). If there is any default value for this column, it will assigned to that column.

Scenario:

I am having a scenario like, Instead of a null value, empty( '') should be populated. Is it possible?

Script:

DROP TABLE VENKATTABLE
CREATE TABLE VENKATTABLE(ID INT, VAL VARCHAR(100))
ALTER TABLE VENKATTABLE
ADD CONSTRAINT VENKATDEFAULT_VAL
DEFAULT '' FOR VAL
INSERT INTO VENKATTABLE (ID) VALUES (1)
SELECT * FROM VENKATTABLE


Instead of altering the table, we can provide the default value in the table creation itself.


DROP TABLE VENKATTABLE
CREATE TABLE VENKATTABLE(ID INT, VAL VARCHAR(100) DEFAULT '')


Cheers,
Venkatesan Prabu .J

12.4.10

Disallowed implicit conversion from data type varchar to data type binary

Disallowed implicit conversion from data type varchar to data type binary, table name....

The above error message will be popped up while trying to store the binary object into a varchar column. For example,

CREATE TABLE mytest (ImageName varchar(100), ImagePath varchar(500))

In the above created table, If we try to push the image it will throw an error in converting the datatype. Because, the above wont do implicit conversion. The column type should be image datatype.

CREATE TABLE mytest (ImageName varchar(100), ImagePath image null)

Cheers,
Venkatesan Prabu .J

Service packs in SQL Server 2005

Microsoft have release three service packs with SQL Server 2005.

1. SP1 (SQL Server Service pack1)
2. SP2 (SQL Server Service pack2)
3. SP3 (SQL Server Service pack3)

You can get these service packs from the below link
http://support.microsoft.com/kb/913089

Cheers,
Venkatesan Prabu .J

9.4.10

Compare two table data using Like Operator - Avoided cursors


Compare two table data using Like Operator:

Scenario: I am having two tables and I need to compare the table data
This one is very very interesting topic, Usually we used to go for cursors to loop through the table data to identify the matching data.

Planned step for this scenario is,
Open a cursor or some loop -> get the data and put it in a dynamic sql -> use like
operator for each row and get the output.


OOPS, I got some different solution/fantastic solution for this problem. We can use inner join to achieve it.

Let's see, how can we achieve it?

drop table VenkatCity
drop table VenkatMainCity
create table VenkatCity(cities varchar(100))
insert into VenkatCity values('Sydney,Melbourne')
insert into VenkatCity values('Chennai,Delhi')
insert into VenkatCity values('Goa,Bombay')
create table VenkatMainCity(cities varchar(100))
insert into VenkatMainCity values('Sydney')
insert into VenkatMainCity values('Chennai')

select * from VenkatCity a
inner join VenkatMainCity b on a.cities like '%'+b.cities+'%'


The above query will take care of matching the records and obtain the desired output.


Cheers,
Venkatesan Prabu .J

Delete data permanently in SQL Server

Delete data permanently in SQL Server:

Scenario: I want to delete records from my table.

Query:
drop table venkattable
create table venkattable(id int identity, program_id varchar(10), subprogram_id varchar(10))
insert into venkattable(program_id,subprogram_id) values('a','a1')
insert into venkattable(program_id,subprogram_id) values('a','a2')
insert into venkattable(program_id,subprogram_id) values('a','a3')


Option 1:

delete from venkattable
-- The above statement will delete the record but maintain the identity value.
insert into venkattable(program_id,subprogram_id) values('a','a3')
select * from venkattable


Above statement will insert a record with id 4. The data is removed but the identity value retains.

To resolve this problem we can use reseed option.

DBCC CHECKIDENT (venkattable, RESEED, 0)

Option 2:

We can go for truncate option.
TRUNCATE TABLE venkattable

Cheers,
Venkatesan Prabu .J

Get Last updated record in a table group by a specific column name

Scenario: I want to fetch a last/latest record in a group.

Creating and inserting data into the table:

create table venkattable(id int identity, program_id varchar(10), subprogram_id varchar(10))

insert into venkattable(program_id,subprogram_id) values('a','a1')
insert into venkattable(program_id,subprogram_id) values('a','a2')
insert into venkattable(program_id,subprogram_id) values('a','a3')
insert into venkattable(program_id,subprogram_id) values('b','b1')
select * from venkattable

Option 1:

with cte
as
(
select max(id) as id from venkattable group by program_id
)
select * from venkattable where id in (select id from cte)

Option 2:

select a.id, a.program_id,a.subprogram_id from venkattable a inner join
(
select max(id) as id from venkattable group by program_id
) b
on a.id=b.id


Cheers,
Venkatesan Prabu .J

Cursors in SQL Server

Cursor in SQL Server:


Cursor is one of an important topic in SQL Server. This will enable us to loop through the result set.


Considering a scenario like, I need to loop through a table and based on the value from the table, I need to do some operation in the second table. In that case, we will go for cursors. Although, cursors are heavy weight objects and it is used in ages from sql server 7.0 version (may be before that too. Am not sure).


Let's go the implementation phase,


Am creating two tables one secondary table and the other is a reference table(Source table)


create table venkat (id int, id1 int)
create table venkatSecondTable (id int)
insert into venkat values(1,2)



The syntax is like,


1. Declare the cursor
2. Open the cursor.
3. Fetch the first record and store it into the cursor.
4. Do the operation until your cursor fetch status is empty.
5. Fetch the next record(A loop)
6. Close the cursor
7. Deallocate the cursor.



Let's see a small example query to check the working functionality of a cursor. Am creating a procedure venkatproc and trying into the insert the record into the secondary table by looping through the first table.

create procedure venkatproc
as
begin
declare @val int
declare cur cursor for select ID from venkat
open cur
fetch next from cur into @val
while @@FETCH_STATUS =0
begin
insert into venkatSecondTable(id) select id1 from venkat where id=@val
fetch next from cur into @val
end
close cur
deallocate cur
end



Most important points:


1. Cursor needs to be opened before using it.
2. We need to monitor memory leakages. If we didnt properly close or deallocate the cursor. It will cause serious memory issues(Holding the memory)
3. As a well known info, cursor is a heavy weight object. Instead we can opt while loop or cte's in sql server 2005.



Cheers,

Venkatesan Prabu. J

Insert records from other table using insert statement

Insert records from other table using insert statement:


Scenario: I need to insert records into the table after fetching records from other table.

Query:


-- Creating the table and inserting records.
create table venkat (id int, id1 int)
create table venkatSecondTable (id int, id1 int)
insert into venkat values(1,2)
-- Check whether data is there
insert into venkatSecondTable select ID,id1 from venkat
select * from venkatSecondTable

Cheers,

Venkatesan Prabu .J

Swapping column data in SQL Server

Swapping the column data between adjacent columns:

Scenario: I need to swap the column data in a table like column1 data to be moved to column2 and column2 data needs to be moved to column1
How can we achieve it?

Queries:

-- Creating the table and inserting records.
create table venkat (id int, id1 int)
insert into venkat values(1,2)
-- Check whether data is there
select * from venkat
-- Swapping the data
update venkat set id=id1,id1=id


In the above case, SQL Server will hold the data in memory until commit operation is commenced.
So, there is no need to hold the data in the third variable for swapping.

Cheers,
Venkatesan Prabu .J

8.4.10

ExceptionTemplates failed in SQL Server

Error message in SQL Server:

Sometimes, If we try to restore a database backup. We will get the below error,

Restore failed for Server 'SQLDB'. (Microsoft.SqlServer.Smo)For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)------------------------------
The backup set holds a backup of a database other than the existing 'Venkat' database.
------------------------------------

Reason for this issue:

This issue arises due to improper installation of SQL Server or improper software . We need to install SQL Server from proper installation disk or As a work around, we can execute the below query to restore your backup.

Solution for this issue:

If you face the above issue, execute the below query.

RESTORE DATABASE [Venkat]
FROM DISK = N'D:\Venkat.BAK'
WITH REPLACE

Cheers,
Venkatesan Prabu .J

SQL Server 2008 - Part 1 - Activity Monitor

Hi Readers,



How are you all? Atlast, I got some time to start my articles on SQL Server 2008. Let's see how it goes?



There are lot of fantastic features introduced in SQL Server 2008. Starting from Intellisense to advanced features like handling objects. One important change done by Microsoft in SQL Server 2008 is, storage architecture. Storing the data in this new version is entirely different to the older one. You can see all the features in my future articles. Let me start with a small part "Activity Monitor".



In SQL Server 2008, Activity Monitor is more flexible and it will give most of the details about a server. Goto Server -> Right click-> Activity Monitor, your activity monitor will be opened.







There are three major items,



1. Overview - Pictorial representation of your server details.

2. Processes - User processes and other details.

3. Resource waits - Deadlocks and other resource waits details.

4. Data file I/O - I/O details.

5. Recent Expesive queries - Long running queries.



Overview Tab in Acitivity Monitor:


This tab will provide a overall graphical view for the other tabs below + Overall nature of the datbase.



1. This tab will provide you the processor time - Time taken for the processor to respond.

2. Any waiting tasks in the server.

3. Database I/O - How the database I/O looks like?

4. Batch requests/sec - Nature and load of the batch requests.






Processes tab:



This tab will list down all the processes available in the server(SPID), the application used in accessing the server, wait time, wait type, wait resource, login information, blocked by etc..,



Resource Waits Tab:


This tab will listdown all the available resources and average time to use the resource.


DataFile I/O:
This tab will list down the available databases and their usages (Read/Write and Response time)


Most Expesive Queries:
This tab will list down the expesive queries available in the server.

This will tell you the expensive query with read/write/response time details.
Cheers,
Venkatesan Prabu .J


Failed to Access IIS Metabase in ASP.net

Failed to Access IIS Metabase error:

If ASP.net is installed before IIS, this issue will occur. In that case we need to register our IIS to the Dotnet framework. This can be achieved by the below command.

Goto Run -> type the below command and click "OK"

%windir%\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis.exe -i

Cheers,
Venkatesan Prabu .J

6.4.10

Microsoft Wiki

TechNet Wiki Beta Arrives!

Microsoft started allowing us to post our views/articles and knowledge in their site.

The TechNet Wiki is a library of information about Microsoft technologies written by the community for the community. Whether you write code, manage servers, keep mission-critical sites up and running, or just enjoy digging into details, we think you will be at home in the TechNet Wiki. For more information including how you can participate see:

http://social.technet.microsoft.com/wiki/

Happy writing !!!

Cheers,
Venkatesan Prabu .J

Malicious software removal tool from windows

Microsoft have recently released a fantastic software tool to detect the malicious softwares available in your system. This will detect the unwanted malicious software(which is harmful to your PC) and highlight all the red alert softwares.

In addition to the above said check, it will check your registry tampers, general PC checkup.

Microsoft Windows Malicious Software Removal Tool (KB890830):


This tool checks your computer for infection by specific, prevalent malicious software (including Blaster, Sasser, and Mydoom) and helps to remove the infection if it is found. Microsoft will release an updated version of this tool on the second Tuesday of each month.

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=ad724ae0-e72d-4f54-9ab3-75b8eb148356

Microsoft Windows Malicious Software Removal Tool (KB890830) x64:

This tool checks your computer for infection by specific, prevalent malicious software (including Blaster, Sasser, and Mydoom) and helps to remove the infection if it is found. Microsoft will release an updated version of this tool on the second Tuesday of each month.

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=585d2bde-367f-495e-94e7-6349f4effc74

Last important point about this software : It's free of cost

Cheers,
Venkatesan Prabu .J

4.4.10

Database Backups in SQL Server

Database Backup:

Database backup is a very important activity in day to day activities. Backup's will be taken for safety purposes. Considering a server got crashed, In that case we can restore the backup taken. To take backup of the database, right click on the database and goto tasks -> backup



Backup database window will pop-ups. Two types of backups will be displayed in the drop down list.

1. Full Backup - This backup type will take the entire database backup.

2. Differential Backup - This backup type will take the last difference of data between now and the last backup taken.

In addition to that, we can take the file or file group backup too.

To avoid more backups to occupy the disk space, we can set an options like when these backup's will expire. This enable us to identify the invalid backups.
Now, our next option is to select the location of the backup + backup name.

Backup window provides an option to locate the backup path through "Browse" option.

Selecting the backup name and location.



OOps, I got a peculiar OS error,
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
TITLE: Microsoft SQL Server Management Studio Express------------------------------
Backup failed for Server 'VENKAT\SQLEXPRESS1'. (Microsoft.SqlServer.Express.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Backup+Server&LinkId=20476
------------------------------ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Backup\aa.bak'. Operating system error 5(error not found). (Microsoft.SqlServer.Express.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&LinkId=20476
------------------------------BUTTONS:
OK------------------------------
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$


On analysing the above issue, the reason behind is -> Access issue. The SQL server service doesn't have proper access to write a backup file in the specified location. I have given full access to the sql server user to the path and tried the backup option. OOPS, my guess is correct and its working fine.


Backup operation completed successfully.


Happy Learning!!!
Cheers,
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