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/

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/

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

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

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)

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/

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/

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/