How to add new Column to existing table in SQL Server

     I know it's simple statement and most of the people known this query, but some times we forgot the syntax so that I got a thought why should not post this? ... :) :) 

Syntax: 

ALTER TABLE <Table Name> ADD <Column Name> <Data Type>

Example

ALTER TABLE Emp2 ADD E_Addres INT

 Please See below Screenshot 

User Defined Table Type

SQL Server  providing the User Defined Table Type, as of my knowledge this is useful when we need to pass parameter to the Stored Proc or Functions as a table values.

Here we can create different data types of columns like INT, VARCHAR etc.

we can create User Type Table  in two ways, here I am giving only one type simply

/* Create a user-defined table type */
CREATE TYPE LocationTableType AS TABLE
    ( LocationName VARCHAR(50)
    , CostRate INT )
GO

It would create User Defined Table Type

Stored Procedures

Stored Procedure

     Stored Procedures, these are most important or common things in every Data Base these will play main role in lot of cases, now I am going to tell you some points

     Stored Procedures, as of known everyone it's a collection of SQL Statements, Yes its exactly right.

     Stored Procedure takes the multiple parameters and will returns the output as result set to the calling program.

Advantages of Stored Procedure

Reduce the Network Traffic, Yes it will reduce the network traffic you may get a doubt on this, how the network traffic would reduce using the Stored Procedure?, Yes it will reduce, for example if we are calling 10 statements and calculating these 10 statements in application level then passing to the server it takes much time so instead of calling 10 statements we can call just single statement that simply pass to the server and will fire at server level, these 10 statements will execute as batch, I hope you got the logic here.

Security, using stored procedures user can't find the database level objects, because in application level we are calling only stored procedure only, so if any third person (malicious users) in between application and server they cannot see the objects of Data Base

These everyone knows I hope

Re-usability of Code, Yes we can use multiple times which we created as Stored Procedure or collection of SQL statements, no need to write these statement multiple times, if any changes are required we need to Alter the Stored Procedure.

 which may you don't know if you know more information or if anything wrong in below just mail me.

Clustered Indexes

CLUSTERED INDEX

     Yes, Here I am going to explain about Indexes in SQL Server  as of my knowledge, I hope every one heard about Indexes but don't have a complete idea about it.

Ok, Let's start

     Basically Indexes are using for Query performance in SQL Server (actually not in SQL Server in all other Data Bases also using for performance only) , these are on-disk structure, and I read some where we can call as row store Index.

     Clustered Index will creates an Index Key and stored the data rows of the tables in order to clustered Index Key in sorting order (ascending or descending) , flow of the clustered index follows B-Tree structure.

    We can create using SQL Server Management Studio or T-SQL, a clustered Index can be rebuild or reorganize on demand to manage the Index Key order.

     When we create a table with the Primary Key that table will create along with Index Key, if table having the Clustered Index then those kind of tables called Clustered Tables, if that table doesn't have any Clustered Index then that we call as Heap Tables

    There can be only one Clustered Index per table, Table doesn't allow more than one Clustered Index

Syntax:

CREATE CLUSTERED INDEX <Index Name> ON <Table Name> (<Column Name>);   

Example:

CREATE CLUSTERED INDEX INDX_Emp_Tbl_Emp_Num ON dbo.Emp (Emp_Num);   

In Generally If table have more than 8 MB then only it goes to Index to get the data else it directly fetch the data from the table it self.

Soon I will update more Information on this

What is the difference between OLTP and OLAP?

I found this information some where, it's very easy to understand, and am sharing the original link below of this.

- OLTP (On-line Transaction Processing) is characterized by a large number of short on-line transactions (INSERT, UPDATE, DELETE). The main emphasis for OLTP systems is put on very fast query processing, maintaining data integrity in multi-access environments and an effectiveness measured by number of transactions per second. In OLTP database there is detailed and current data, and schema used to store transactional databases is the entity model (usually 3NF). 

- OLAP (On-line Analytical Processing) is characterized by relatively low volume of transactions. Queries are often very complex and involve aggregations. For OLAP systems a response time is an effectiveness measure. OLAP applications are widely used by Data Mining techniques. In OLAP database there is aggregated, historical data, stored in multi-dimensional schemas (usually star schema). 


The following table summarizes the major differences between OLTP and OLAP system design.

OLTP System - Online Transaction Processing (Operational System)
OLAP System - Online Analytical Processing (Data Warehouse)

Source of data
OLTP: Operational data; OLTPs are the original source of the data.
OLAP: Consolidation data; OLAP data comes from the various OLTP Databases

Purpose of data
OLTP: To control and run fundamental business tasks
OLAP: To help with planning, problem solving, and decision support

What the data
OLTP: Reveals a snapshot of ongoing business processes
OLAP: Multi-dimensional views of various kinds of business activities

Inserts and Updates
OLTP: Short and fast inserts and updates initiated by end users
OLAP: Periodic long-running batch jobs refresh the data

Queries
OLTP: Relatively standardized and simple queries Returning relatively few records
OLAP: Often complex queries involving aggregations

Processing Speed
OLTP: Typically very fast
OLAP: Depends on the amount of data involved; batch data refreshes and complex queries may take many hours; query speed can be improved by creating indexes

Space Requirements
OLTP: Can be relatively small if historical data is archived
OLAP: Larger due to the existence of aggregation structures and history data; requires more indexes than OLTP

DatabaseDesign
OLTP: Highly normalized with many tables
OLAP: Typically de-normalized with fewer tables; use of star and/or snowflake schemas

Backup and Recovery
OLTP: Backup religiously; operational data is critical to run the business, data loss is likely to entail significant monetary loss and legal liability
OLAP: Instead of regular backups, some environments may consider simply reloading the OLTP data as a recovery methodsource: 

original source: http://datawarehouse4u.info/OLTP-vs-OLAP.html

Stored Procedures - Auto Executing

You can designate stored procedures to execute every time the SQL Server is started. These types of procedures cannot accept any input parameters and have to be owned by a member of SYSADMIN fixed server role. To designate stored procedures for automatic execution use the sp_procoption system stored procedure.

The only option allowed by this procedure is 'startup'. The procedure to be started automatically MUST reside in the Master database. The following example makes the procedure execute automatically every time the server starts up:

sp_procoption my_procedure, 'startup', 'on'

This option could be useful if you have specific processing requirements or tasks that need to be perform at server startup - for instance you might wish to backup all of your user databases every time SQL Server is started.

How to find the No. of tables list in SQL Server Database

I know few way to find the no. of tables in SQL Server Database.

1. Using  INFORMATION_SCHEMA.TABLES

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

SELECT *FROMINFORMATION_SCHEMA.TABLESwhereTABLE_CATALOG='Thirmal'

2. Using sys.tables SELECT *FROM sys.tables

3. Using sysobjects

SELECT *FROMsysobjects WHERE xtype = 'U'

Computed Columns in SQL Server

Computed columns are derived columns that are bound to values of other columns. However the datatype of these computed columns depends on the nature of the end result. They may be bound to columns of either the same datatype or they could be bound to columns of different datatypes.

Consider the following set of data

drop table #Temp_Computed

create table #Temp_Computed (Column1 int, computed_Column1 as Column1, computed_date as dateadd(day,Column1,getdate()))

insert into #Temp_Computed (Column1)

select 5

select * from #Temp_Computed

Output

As you can see, the datatype of computed_id will be same as that of the ID as ID is directly used in the computed column definition. However the datatype of the columncomputed_date will be datetime because the expression dateadd(day,id,getdate()) will do an implicit conversion to datetime datatype as getdate() is used in the definition.

Let us consider another set of data

drop table #Temp_Computed

create table #Temp_Computed

(

  Column1 int

, computed_Column1 as Column1/2.0

, computed_date as (Column1*3300000000000)

)

insert into #Temp_Computed (Column1)

select 5

select * from #Temp_Computed

Output

As you can see, the datatype of computed_id will be of decimal type because of the expression id/2.0 which results to decimal number. The datatype of the columncomputed_numberf will be BIGINT because the expression id*300000000000 will do an implicit conversion to the BIGINT datatype as the result won't fit into a INT datatype

So the datatpye of computed column differs based on the expression and if you want to update the value returned by a computed column to another table, you need to make sure that the datatypes match each other. 

Keep these points in mind while using Computed columns in SQL Server

Watch Videos Here

Please give your valuable Comments below :) :) 

How to Display a variable value using Script Task in SSIS

Some time in order to debug variables in SSIS you want to see what’s the values that the variable is holding or you want to check the Result set which is stored in the variable.

If you want to show or check the variable value or want to show the value inside a Message box than it can be done through Script task.

Below I will create a test table and will insert a row into the table and will display the ColB value i.e. Hello How are You in SSIS using SQL Execute Task and Script task.

Table script

create table tbl (ColA varchar(50), ColB varchar(50))

insert into tbl  values('Test Message','Hello How are You')

Now we will open SSIS and will drop a SQL Execute task and a Script task in the Package

Follow the below steps 

Double click on SQL execute task

Create a data connection to the database where above table created

In SQL Statement add -select * from tbl

Select Resultset as Single Row as below Screenshot 

Go to Result tab in the Right side

Add a variable with message and ResultName make it as 1 (Index of your column) Since we will show ColB value in the Message box as below screenshot

Next connect the SQL Execute task to script task

Now, double click on Script task

Select Read only variable as User Message which we created above as below screenshot

      

Click on edit script and add Message box (MessageBox.Show(Dts.Variables["Message"].Value.ToString());) inside Main Function as below Screenshot

Save and Click ok 

Run your Package this will display your variable value in the Message box as below. 

Please write in Comments If you'r stuck with any step or need any help.

Facebook Rewards 10-year-old With $10,000 for Finding Instagram Bug

New York: The social networking giant has paid $10,000 to a 10-year-old boy for spotting a bug in Facebook-owned photo-sharing platform Instagram.

According to a report in technology website VentureBeat.com, Jani from Finland discovered the security flaw in Instagram on his own.

He found a bug in Instagram which requires you to be at least 13 before even signing up, that let him delete any comment on the social network.

"He reported the bug by email, offered proof by deleting a message on one of Facebook's test Instagram accounts and it was fixed in February. Facebook paid him the bug bounty in March," the report added.

"I would have been able to remove anyone, even Justin Bieber," the report quoted Jani as saying.

The Finnish boy wishes to become a security researcher. "It would be my dream job. Security is very important," he was quoted as saying.

He used the reward money to buy a new bike, football gear and computers for his two brothers.

Like Google and Microsoft, Facebook also has a bug bounty programme.

In February, Facebook announced that it had paid $4.3 million in rewards to more than 800 security researchers for over 2,400 submissions since launching its bug bounty programme in 2011.

In 2015, 210 researchers received $936,000 with an average payout of $1,780.

A jobless man applied for the position of 'office boy' at Microsoft. The HR manager interviewed him, then gave him a test: clean the floor. The man

A jobless man applied for the position of 'office boy' at Microsoft.

The HR manager interviewed him, then gave him a test: clean the floor. The man passed the test with flying colors.

"You are hired," HR manager informed the applicant, "give me your e-mail address, and I'll send you the application for employment, as well as the date you should report for work.

The man replied " I don't have a computer, or an email!"

"I'm sorry," said the HR manager. "If you don't have an email, that means you do not exist. And we cannot hire persons who do not exist."

The man was very disappointed.

He didn't know what to do. He only had $10 with him. Once that is spent, he won't have any money to buy any food.

He went to the supermarket and bought a crate of tomatoes with his $10.

He went from door to door and sold the tomatoes in less than two hours. He doubled his money.

He repeated the operation three times, and returned home with $60. He realized that he can survive

this way. He started to go everyday earlier, and return late.

He doubled or tripled his money every day. Soon, he bought a cart, then a truck. In a very short time, he had his own fleet of delivery vehicles.

Five years later, the man became one of the biggest food retailers in the U. S. He started to plan his family's future, and decided to have a life insurance.

He called an insurance broker, and chose a protection plan.

At the end of the conversation, the broker asked him for his email address.

The man replied: ' I don't have an email.'

The broker was dumbfounded. "You don't have an email, and yet have succeeded in building an empire. Can you imagine what you could have been if you had an email?," he exclaimed.

The man thought for a while, and replied, "an office boy at Microsoft!"

If you just lost your Job or Just failed an Interview Don't worry be Optimistic..... Good days are on the way and something better is reserved for you.

In this page i Request you to have a look at the inspiration. Sometimes they encourage us to seek for our dreams, trust life and ourselves and never give up.

They teach us to notice the magical beauty of the world that we live in and that surround you every day, as well as they show, what are the true values that are worth aiming for in our lives.

Also they tell about God's caring and the power of unconditional love.

We get inspired by the strength of the human spirit and we learn, how to be a better person, more sensitive, supportive, kind and loving.

Written By: Nishanth Varathakumar

SQL – Stuff Function

SQL - STUFF() Function

This is the most amazing function of T-SQL which is used to delete a specified length of characters within a string and replace with another set of characters.

Syntax:- STUFF (Character Expression, Start, Length, Replace With Expression) Arguments: This function uses the following parameters. Character Expression: Is an expression of character data. Character Expression can be a constant, variable, or column of either character or binary data. Start: Is an integer value that specifies the location to start deletion and insertion. If start or length is negative, a null string is returned. If start is longer than the first Character Expression, a null string is returned. Start can be of type bigint. Length: Is an integer that specifies the number of characters to delete. If length is longer than the first Character Expression, deletion occurs up to the last character in the last Character Expression. Length can be of type bigint. Replace With Expression: Is an expression of character data. Replace With Expression can be a constant, variable, or column of either character or binary data. This expression will replace length characters of Character Expression beginning at start.

Important points to remember: There are some basic points always keep in mind as given below- 

1. If the start position or the length is negative, or if the starting position is larger than length of the first string, a null string is returned. 
2. If the start position is 0, a null value is returned. 
3. If the length to delete is longer than the first string, it is deleted to the first character in the first string.

Important Facts - Unfortunately the stuff function only works on "strings" (char, nchar, varchar, nvarchar). If you need to use it on a numeric data type you will have to convert it to a string and back again.

Example 1: Generate a Comma-Separated List

Stuff function is very useful if we want to add comma-separated list. If we want to capture all comments against any particular topic then stuff function comes into the picture such as given below-

--- declare table variable to store the comments

DECLARE @UserInputs TABLE

(  

    PollId Int, PollSubject Varchar(250), UserComments Varchar(250)

)

----- Insert Values into table variable

INSERT INTO @UserInputs (  PollId, PollSubject, UserComments)

VALUES

(1, 'Most favourite  super hero?', 'Superman' ),

(1, 'Most favourite  super hero?' ,'Batman' ),

(1, 'Most favourite  super hero?' ,'Ironman'),

(1, 'Most favourite  super hero?' ,'Wolverine'),

(2, 'Most favourite  movie?', 'Titanic' ),

(2, 'Most favourite  movie?' ,'The Note Book' ),

(3, 'Most favourite  Game?' ,'Cricket'),

(3, 'Most favourite  Game?' ,'Football')

----- Table Variable output

SELECT PollId, PollSubject, UserComments FROM @UserInputs

PollId	PollSubject	UserComments
1	Most favourite  super hero?	Superman
1	Most favourite  super hero?	Batman
1	Most favourite  super hero?	Ironman
1	Most favourite  super hero?	Wolverine
2	Most favourite  movie?	Titanic
2	Most favourite  movie?	The Note Book
3	Most favourite  Game?	Cricket
3	Most favourite  Game?	Football

----- Comments by using stuff function

SELECT DISTINCT PollId, PollSubject,

UserInput=STUFF((SELECT ',' + UserComments

                                                        FROM @UserInputs

                                                        Where PollId=UI.PollId

                                                        ORDER BY PollSubject

                                                        FOR XML PATH('')), 1, 1, '')

from @UserInputs UI

ORDER BY UI.PollId

PollId	PollSubject	Output
1	Most favourite super hero?	Superman,Batman,Ironman,Wolverine
2	Most favourite  movie?	Titanic,The Note Book
3	Most favourite  Game?	Cricket,Football

We are aware that all that STUFF is doing is trimming the leading , off of the text that FOR XML PATH is generating.

Example 2: Insert One String Into Another String at a Specific Location

We can use the stuff function to replace or insert new string into the existing string as given below:

---- declare table variable to store the comments DECLARE @UserInputs TABLE (       PollId Int, PollSubject Varchar(250), UserComments Varchar(250) ) ----- Insert Values into table variable INSERT INTO @UserInputs (  PollId, PollSubject, UserComments) VALUES (1, 'Most favourite  super hero is ? for kids', 'Superman' ), (2, 'Most favourite  movie is ? in Cinema', 'Titanic'), (3, 'Most favourite  Game is ? in the world.' ,'Cricket') ----- Table Variable output SELECT PollId, PollSubject, UserComments FROM @UserInputs PollId PollSubject UserComments 1 Most favourite  super hero is ? for kids Superman 2 Most favourite  movie is ? in Cinema Titanic 3 Most favourite  Game is ? in the world. Cricket ----- Insert One String Into Another String at a Specific Location ----- by using stuff function SELECT PollId ,UserInputs=STUFF(PollSubject, CHARINDEX('?', PollSubject), 1, UserComments)  FROM @UserInputs  ORDER BY PollId PollId UserInputs 1 Most favourite  super hero is Superman for kids 2 Most favourite  movie is Titanic in Cinema 3 Most favourite  Game is Cricket in the world.

SQL Stuff () Vs REPLACE()

Stuff () - This function can be used for delete a certain length of the string and insert a new string in the deleted place.

STUFF Syntax: STUFF (String, StartPos, LengthofReplaceChar, ReplaceString)

String - String to be overwritten

StartPos - Starting Position for overwriting

LengthofReplaceChar - Length of replacement string

ReplaceString - String to overwrite 

REPLACE()- This function replaces all the occurrences of a string expression with a new string within an input string.  

REPLACE Syntax: REPLACE (String, StringToReplace, StringTobeReplaced)

String - Input String

StringToReplace - The portion of string to replace

StringTobeReplaced - String to overwrite

Conclusion

The STUFF string function inserts a string into another string.  It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.