30.4.11

DIFFERENCE BETWEEN DROP AND TRUNCATE

----- DIFF BETWEEN DROP AND TRUNCATE


1. DROP WILL REMOVE THE OBJECT WHERE ARE DELETE WILL REMOVE ONLY THE DATA DELETE WILL DELETE EACH RECORD


2. NO LOGGING IN TRUNCATE ... LOGGING WILL HAPPEN IN DROP STATEMENT


Cheers,
Venkatesan Prabu .J
Head - www.kaashivinfotech.com
http://venkattechnicalblog.blogspot.com/

DIFF BETWEEN TRUNCATE AND DELETE

----- DIFF BETWEEN TRUNCATE AND DELETE


1. TRUNCATE WILL DELETE THE ENTIRE TABLE @ A STRETCH DELETE WILL DELETE EACH RECORD
2. NO LOGGING IN TRUNCATE ... LOGGING DELETE
3. PROCESS IS TOO FAST AND WE CAN RECOVER THE DATA IN TRUNCATE


Cheers,
Venkatesan Prabu .J
Head - www.kaashivinfotech.com
http://venkattechnicalblog.blogspot.com/

Concatenate row data into a single data in SQL Server

How to concatenate the row data into a single data?


[CODE]
DECLARE @NAME VARCHAR(100) =''
SELECT @NAME= @NAME + ',' + NAME FROM VENKAT_NAME_TABLE
SELECT SUBSTRING(@NAME,1,LEN(@NAME))
[/CODE]

Cheers,
Venkatesan Prabu .J
Head - www.kaashivinfotech.com
http://venkattechnicalblog.blogspot.com/

Converting 0 to 1 and 1 to 0 in SQL Server table

Here is a code snippet to swap the values in SQL Server Table.

DROP TABLE VENKAT_SAMPLETABLE



CREATE TABLE VENKAT_SAMPLETABLE(ID INT)
INSERT INTO VENKAT_SAMPLETABLE VALUES(1)
INSERT INTO VENKAT_SAMPLETABLE VALUES(0)
INSERT INTO VENKAT_SAMPLETABLE VALUES(0)
INSERT INTO VENKAT_SAMPLETABLE VALUES(1)


SELECT * FROM VENKAT_SAMPLETABLE

UPDATE VENKAT_SAMPLETABLE SET ID=
       ( CASE
                   WHEN ID=1 THEN 0
                    WHEN ID=0 THEN 1
            END
        )
SELECT * FROM VENKAT_SAMPLETABLE

Cheers,

Venkatesan Prabu .J
Head - www.kaashivinfotech.com
http://venkattechnicalblog.blogspot.com/

25.4.11

Distributed Database Management System

Distributed Database management system is nothing but the data will be distributed among different databases and may be located in different servers in different locations. But, they are logically interdependent.




If we think about an advanced concepts like Database Mirroring, yes it will considered as distributed databases. Because, the data will be distributed to different locations.



Cheers,
Venkatesan Prabu .J
Head - http://www.kaashivinfotech.com/
http://venkattechnicalblog.blogspot.com/

Confused Nation - A Social thought

Confused Nation ???

WORTH A QUICK READ !!!


A nation where Rice is Rs.40/- per kg and a SIM Card is free•


Where a pizza you have ordered reaches home faster than an ambulance or police, even if you were being murdered or having a heart attack•


Where a car loan is charged at 5% but an education loan, so necessary for our youth is charged an interest of 12%•


A nation where students with 45% get into elite institutions through the quota system and those with 90% are sent away because of merit.•

Where a millionaire buys a cricket team, spending crores instead of donating the money to any charity. Where two IPL teams were auctioned at 3300 crores, yet still a poor country where people starve for two square meals per day.•


A country where footwear is sold in AC showrooms, but the vegetables we eat, are sold on the footpath and very often next to garbage dumps.•


Where everybody wants to be famous, not by doing good for others, but by looting others and finally getting their names in the newspapers through some scam or other!•


It’s a strange nation we live in, where complex buildings get ready within a year while public bridges, flyovers and sea links take several years even to get off the drawing board and another decade to be completed•

We are a nation where two brothers fight with each other for the nation’s spoils, but the nation doesn’t know that the two of them are cleverly looting the nation while we watch their mock battles.•


We have malls, and sky-rises, with slums forming their boundary wall.•


A country where men and women squat on railway tracks, with no where else to go while watching them from windows, are couples with three bathrooms and one for the guests•


A country where politicians who are supposed to serve the people accept money from the same people they are supposed to serve, then take a salary from the government for their services to the people•


We are a nation where we talk in hushed whispers about the corruption in the country and then dig into our pockets to bribe a cop when we are caught cutting a red light


Cheers,
Venkatesan Prabu .J
Head - http://www.kaashivinfotech.com/

22.4.11

Converting RGB image or psd to CMYK

Usually, we will face a problem while printing the images(For instance, visiting cards).



They will advise us to convert the image from RGB(Default one) to CMYK color.



Here is an option in Photoshop to convert your image into required format.



Cheers,
Venkatesan Prabu .J
Head - www.KaaShivinfotech.com
http://venkattechnicalblog.blogspot.com/

List down all the collations available in SQL Server


Below query is used to find all the collations available in SQL Server

select * from ::fn_helpcollations()



Cheers,
Venkatesan Prabu .J
Head -
WWW.Kaashivinfotech.com
http://venkattechnicalblog.blogspot.com/

Puzzle question in SQL Server


I have tried to execute the query and got a different result,

CREATE TABLE VENKAT_TABLE (ID INT IDENTITY(1,1))

INSERT INTO VENKAT_TABLE DEFAULT VALUES
GO 100
 
Detailed steps:
 
   1. Created table
   2. Insert records in the table with default values
   3. Execute the statement for 100 times.
 
Oops.. One record inserted and for second am getting the error as.... the table is already exists.
 
Reason,
 
    The "GO" statement is considering both the statements above it and it's trying to create the table and insert the record into it.
 
      CREATE TABLE VENKAT_TABLE (ID INT IDENTITY(1,1))

      GO  -- Should have seperate GO here.
      INSERT INTO VENKAT_TABLE DEFAULT VALUES
      GO 100

Cheers,
Venkatesan Prabu .J
Head - WWW.Kaashivinfotech.com
http://venkattechnicalblog.blogspot.com/

Generating Alpha numeric values in SQL Server like Identity property

My application has three atributes id, name, sal and a create button. but the task is the user should not enter id according to his choice..
it should be generated automatically..... when new user is registering...
the no's should be like that a1,a2,a2.... b1,b2,..... no's should be alphanumeric...
s there any solution for this... help me out....

Answer for the above query,

DECLARE @ALPHA_VAL CHAR(1) , @INT_VAL VARCHAR(1000)

DECLARE @VAL INT =97, @VAL1 INT =1
WHILE @VAL <= 122
BEGIN


      SET @VAL1=1

      WHILE @VAL1 < 1000

       BEGIN

                SET @ALPHA_VAL = CHAR(@VAL)
                SET @INT_VAL = CONVERT(CHAR,@VAL1)
                SET @INT_VAL= @ALPHA_VAL + @INT_VAL

                PRINT @INT_VAL
                SET @VAL1 = @VAL1+1

        END
SET @VAL=@VAL+1
END

The output is

a1
a2
a3
a4
....b1,b2,....b1000...c1,c2.......etc... to z1000

Cheers,

Venkatesan Prabu .J
Head - WWW.Kaashivinfotech.com
http://venkattechnicalblog.blogspot.com/

Inline Declaration and value assignation in SQL Server 2008

In older environment, we need to declare the variable and set the value for the varaible as below,


DECLARE @VAL INT , @VAL1 INT
SET @VAL=10
SET @VAL=20
 
In the newer version of SQL Serve 2008, we are having inline declaration and value assignation.
 

DECLARE @VAL INT =97, @VAL1 INT =1
Cheers,
Venkatesan Prabu .J
Head - WWW.Kaashivinfotech.com
http://venkattechnicalblog.blogspot.com/

CHAR function in SQL Server


Char is a data type which everyone knows. Do you know char is a function which is a reverse of ASCII function in SQL Server?

Select Char(65)

will return the output as "A"

Cheers,

Venkatesan Prabu .J
Head - WWW.Kaashivinfotech.com
http://venkattechnicalblog.blogspot.com/

SQL Server User Group meet at Chennai

Dear Blog readers,

    We are planning for SQL Server User group monthly meeting @ KaashivInfoTech chennai (My Office).

             http://sql-articles.com/cssug/april-2011-ug-scheduled

I am presenting a sesssion on DBA track (Management DataWarehouse).

It's free for everyone. Please register it ASAP.

Cheers,

Venkatesan Prabu .J
Head - WWW.Kaashivinfotech.com
http://venkattechnicalblog.blogspot.com/

Varchar to Float in SQL Server

Convert function can be used to convert your varchar value to Float.

declare @abc varchar(30)
set @abc = '10.5'
select convert(float,@abc)

Cast function will perform the same operation.
 declare @abc varchar(30)
set @abc = '10.5'
select cast(@abc as float)
Cheers,

Venkatesan Prabu .J
Head - WWW.Kaashivinfotech.com
http://venkattechnicalblog.blogspot.com/

Constraints on the whole table in SQL Server

One of my blog reader have asked me a query, Is it possible to give constraints to all the columns in a table in a single shot?

CREATE TABLE dbo.Venkat_Table



(
   id int primary key,
   Name varchar(50) unique,
   Age int Unique
)



Yes it's possible. We can provide the constraints.

Applying terms and conditions -> The constraints should not conflict with one another. Primary key constraints can be given only once for a table.
Cheers,




Venkatesan Prabu .J
Head - WWW.Kaashivinfotech.com
http://venkattechnicalblog.blogspot.com/

combining column names in sql server



        Consider am having column's as FirstName and another column as SecondName. If your result needs the output Name which is the combination of FirstName and SecondName or LastName.

   Select FirstName +' '+ LastName as Name from YourTable

Cheers,

Venkatesan Prabu .J
Head - WWW.Kaashivinfotech.com
http://venkattechnicalblog.blogspot.com

21.4.11

Replace function in SQL Server

Replace is one of the nice function in SQL Server. It's used to replace a particular string in the string.

The Syntax is,


Replace("Actual String","String to be replaced","String to replace")


-------- REPLACES THE STRING WITH THE PROVIDED STRING

SELECT REPLACE('VENKAT STRING','VENKAT','SANTHOSH')

The output is SANTHOSH STRING.

Cheers,

Venkatesan Prabu .J
Head - WWW.Kaashivinfotech.com
http://venkattechnicalblog.blogspot.com/

Changing the path of the MDF and LDF file for the database

One of my friend have asked a query to me. How can I change my database files from one drive to another drive. Is it possible to achieve it?


Right Click on the database -> Properties -> Files - >Path

Here you can change the path your MDF and LDF files.




Cheers,
Venkatesan Prabu .J
Head - KaaShivInfotech
http://www.kaashivinfotech.com/

Some inbuilt functions in SQL Server


Let's see some of the Sytem inbuilt functions in SQL Server

------- RETURNS ASCII VALUE FOR THE CHARACTER

SELECT ASCII('A')



------- RETURNS ASCII VALUE FOR THE FIRST CHARACTER OF THE STRING

SELECT ASCII('ASK')



------- CONVERT YOUR CAPITAL LETTERS TO SMALL LETTERS

SELECT LOWER('ASK')



------- CONVERT YOUR LOWER LETTERS TO UPPER LETTERS

SELECT UPPER('ask')



------- GIVES YOU THE LENGTH OF THE STRING

SELECT LEN('ask')

Cheers,
Venkatesan Prabu .J
Head - KaaShivInfotech
www.KaaShivinfotech.com

SQL Cache dependency


SQL Cache dependency is a fantastic concept introduced with SQL Server 2005. It's nothing but maintaining the table data in the cache until the table is getting altered.

Conserdering am having a table named Venkat_table in the database Venkat_database.


The first step is to execute the below command in the command prompt,


aspnet_regsql.exe -Saa -Uaa -Paa -ed -d Venkat_database -et -t Venkat_table


(Search your machine for aspnet_regsql.exe and go to that path for executing the above command)

Once if you execute the above command AspNet_SqlCacheTablesForChangeNotification table will be created in the database and the above Venkat_table will be added in the Cache list.

This will increase the performance of data retrieval for this table.

Cheers,
Venkatesan Prabu .J
Head - KaaShivInfoTech


18.4.11

Identity insert in SQL Server table


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

Usually, we will get this error while inserting the records/data into the identity column.

The reason for this error is,

  "We can't explicitly insert data into the identity. If we want to achieve the same, we should first execute the command Identify insert on the table is ON"

Here is a small code snippet,

CREATE TABLE VENKAT_TABLE(ID INT IDENTITY(1,1),NAME VARCHAR(100),AGE INT)



GO


SP_HELP VENKAT_TABLE


GO


SET IDENTITY_INSERT VENKAT_TABLE ON


-- WE CAN INSERT THE IDENTITY VALUES EXPLICITLY


INSERT INTO VENKAT_TABLE(ID,NAME,AGE) VALUES(1,'VENKAT',20)


INSERT INTO VENKAT_TABLE VALUES(2,'SANTHOSH',20)


INSERT INTO VENKAT_TABLE VALUES(3,'DEEPAN',20)
 
OOPs, still am getting the same error. The reason is Microsoft SQL Server is insisting us to insert the records with the column specified in the query. Let's sse the actual solution to resolve the same.

INSERT

Cheers,
Venkatesan Prabu. J
Head - WWW.Kaashivinfotech.com
INTO VENKAT_TABLE(ID,NAME,AGE) VALUES(1,'VENKAT',20)

6.4.11

ASCII function in SQL Server

ASCII function is used to return the ascii value of the character which is provided as input.

------- RETURNS ASCII VALUE FOR THE CHARACTER
SELECT ASCII('A')

The output is 65.

Cheers,
Venkatesan Prabu
http://kaashivinfotech.com/

Fetch random records from the table using NewID() function

Query: Am having 10000 records and I need some 2000 random records. How can I fetch the same? Answer:


This can be acheived using NEWID() function. NewID() will try to generate a unique id of 32 bit length. (Ex value A1E7AB10-3E0C-4685-9FEE-00239FB80F4F)



---------Am creating a sample table and insert the records into the table DROP TABLE VENKAT_TABLE GO CREATE TABLE VENKAT_TABLE (ID INT IDENTITY(0,1)) GO INSERT INTO VENKAT_TABLE DEFAULT VALUES GO 1000 ------- Inserting 1000 records into the table SELECT * FROM VENKAT_TABLE


--------- Query to fetch random 100 records from the table.

SELECT TOP 100 * FROM VENKAT_TABLE ORDER BY NEWID()


Cheers,


Venkatesan Prabu .J



http://kaashivinfotech.com/

4.4.11

Change Blog name in WordPress.com

Recetly, I have created another blog in wordpress.com. Thought of changing the blog name. Struggled to find the option in the bunch of options available. Goto Setting - > General, you will get the text box to get the new name for your blog. Reconfirm the name in the second text box. Thats why, you are ready to go. Cheers, Venkatesan Prabu .J http://kaashivinfotech.com/ http://venkattechnicalblog.blogspot.com/

1.4.11

Creating script for SQL Server 2000 from SQL Server 2005

I got a peculiar question like, "Can we create a script for downgraded version in the current higher version environment. For instance, am having SQL Server 2008 database and I would like to create the database script for SQL Server 2005. Is it possible? "
Yes, it's possible with an option. It's hidden safely somewhere and we need to dig the same to resolve the problem.


1) Right-click over the desired Database at 2005, Choose Tasks and the Generate Scripts (Option).


2) At the pop-up Dialog Box click at the Script All Objects in the selected Databases check box, to activate it and then Click the Next Button.


3) Set the following Elements to the following Values


a. Script Collation , set to TRUE


b. Script Database Create, set to TRUE


c. Script of SQL Version, set to SQL SERVER 2000


d. Script foreign keys, set to TRUE


e. Script Triggers, set to TRUE Then Hit the Next button


4) Select the way the generated scripts should be saved (There are different selections. The most common one is Clipboard). Finally click the Next button till you reach the end.


5) Click Finish

Cheers, Venkatesan Prabu .J http://www.kaashivinfotech.com/ http://venkattechnicalblog.blogspot.com/

Prevent read and write permissions to app.config file

I started thinking about an option of restricting the read/write permission on my app.config file. Oops, as a typical programmer. I am things "inside the box" in the dotnet environment. Let me clear out the exact problem. Can we restrict the programmer to modify the config file? Because, all my connections are sitting in my config file and I would like to keep it safe. Afterwards came out from Dotnet world and started thinking as a windows user. It's really too simple, you can achieve the same through your windows Security property.Right click on the file -> Properties -> Security -> Click on the specific user and click the edit button, you can change the options by selecting or deselecting the check boxes. Cheers, Venkatesan Prabu .J http://www.kaashivinfotech.com/ http://venkattechnicalblog.blogspot.com/

Installer issue in Dotnet application

I have an installer that runs seamlessly in any 32bit -x86 OS. But if I tried to run the exe(installer) to any 64bit OS, yeah it runs and creates a database, but does not create a tables on it... The installer is compiled in a platform 32bit OS. Do you have any idea about this? Do I need to recompile the application in a 64bit OS? I got the below issue: "SQL Server 2005 Setup cannot continue because the installation package could not be opened. The installation package has a missing file, or you are running a 32-bit only Setup program on a 64-bit computer. To continue, verify the SQL Server 2005 package you are running is supported on WOW64. If you are certain that you are running a supported SQL Server 2005 package, and you continue to see this message, contact the application vendor." Answer: Obviously, you need to provide two types of installers 1. One for 64 bit - x64 platform 2. One for 32 bit - x86 platform. While creating the installer, you need to change the targerplatform property. Cheers, Venkatesan Prabu .J http://www.kaashivinfotech.com/ http://venkattechnicalblog.blogspot.com/

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