update column with incremental value using sql

update column with incremental value using sql

DECLARE @NewID INT;

SET @NewID = 0;

UPDATE myTable
SET @NewID = ID = @NewID + 1

Case in where clause with parameters in sql

Case in where clause with parameters in SQL:

Suppose you have a table named "testTable" having column Col1, Col2, Col3, Col4.

If you want to match @Parameter1 with Column Col1 and @Parameter2 with Column Col2. Either of two parameters may or may not be '' (Blank). For this situation you have make procedure with if..else. In Order to avoid if.. else in Store procedure, you can use Case in where as given below:

By If...Else:
If (@Parameter1 ='' and @Parameter2 ='')
Begin
Select Col1, Col2, Col3, Col4 from testTable
End
If (@Parameter1 <>'' and @Parameter2 ='')
Begin
Select Col1, Col2, Col3, Col4 from testTable where Col1=@Parameter1
End
If (@Parameter1 ='' and @Parameter2 <>'')
Begin
Select Col1, Col2, Col3, Col4 from testTable where Col2=@Parameter2
End
If (@Parameter1 <>'' and @Parameter2 <>'')
Begin
Select Col1, Col2, Col3, Col4 from testTable where Col1=@Parameter1 and Col2=@Parameter2
End

By Case in Where Clause:

Create Procedure [dbo].[Proc_GetData]
(
@Parameter1 varchar(50) ='',
@Parameter2 varchar(50) =''
)
as
Select Col1, Col2, Col3, Col4
from testTable
where
case when @Parameter1 ='' and @Parameter2 ='' Then 1
        when @Parameter1 <>'' and @Parameter2 ='' and  Col1=@Parameter1 Then 1
        when @Parameter1 ='' and @Parameter2 <>'' and Col2=@Parameter2  Then 1
        when @Parameter1 <>'' and @Parameter2 <>'' and  Col1=@Parameter1                     and Col2=@Parameter2  Then 1
else 0
end=1

Shrink Log file in SQL

Shrink Log file in SQL

Run the below command in sql:

ALTER DATABASE DatabaseName SET RECOVERY full  
GO
ALTER DATABASE DatabaseName SET RECOVERY SIMPLE WITH NO_WAIT;
GO
USE DatabaseName 
GO
DBCC SHRINKFILE('DatabaseName _log', 0, TRUNCATEONLY)

SQL Interview Questions Links

For sql question follow these links-

http://blog.sqlauthority.com/2007/04/21/sql-server-interview-questions-and-answers-complete-list-download/

http://www.techrepublic.com/article/questions-to-ask-a-sql-server-database-developer-applicant/6126230

SQL Server 2005 Database optimization Interview questions
http://www.questpond.com/DatabaseOptimization.zip

SQL Server 2005 DTS Interview questions
http://www.questpond.com/DTSInterviewQuestions.zip

• http://www.codeproject.com/KB/database/SQL2008InterviewQueAns.aspx
• http://www.indiabix.com/technical/sql-server-2008/
• http://www.pinaldave.com/sql-download/SQLServer2008InterviewQuestionsAnswers.pdf
• http://www.careerride.com/SQLServer-Interview-Questions.aspx
• http://blog.sqlauthority.com/2008/09/20/sql-server-2008-interview-questions-and-answers-complete-list-download/
• http://obuyacricketacademy.com/admin/reports/SQLServer2008InterviewQuestionsAnswers.pdf
• http://www.sqlserver-training.com/sql-server-2008-interview-questions/-
• http://dotnetkicks.com/database/SQL_SERVER_2008_Interview_Questions_and_Answers_PDF_Download
• http://dotnetshoutout.com/SQL-SERVER-2008-Interview-Questions-and-Answers-PDF-Download

Free text search in sql

Free text search in sql

FreeText:

• Separates the string into individual words based on word boundaries (word-breaking).
• Generates inflectional forms of the words (stemming).
• Identifies a list of expansions or replacements for the terms based on matches in the thesaurus.

Syntax:

FREETEXT ( { column_name | (column_list) | * } 
          , 'freetext_string' [ , LANGUAGE language_term ] )

Example:

The following example searches for all column2 values containing the words related to Test, Data, New.

SELECT Column1
FROM Table
WHERE FREETEXT (Column2, 'New Test Data' );

Use with variable:

DECLARE @SearchWord nvarchar(300);
SET @SearchWord = N'New Test Data';

SELECT Column1
FROM Table
WHERE FREETEXT(Column2, @SearchWord);

Using a parameter in Group by clause gives error "GROUP BY expression must contain at least one column that is not an outer reference"

Suppose you have a table 'tblTest' with column named 'Col1', 'Col2'.

And you have a parameter 

Declare @param nvarchar(50)

Suppose you want to group by column with parameter.

Select @param as Parameter, Col1, Col2 , Count (Col1) from tblTest

group by @param, Col1, Col2

Above query will give error:

Using a parameter in Group by clause gives error "GROUP BY expression must contain at least one column that is not an outer reference"

Solution : You just remove @param from group by clause.

Select @param as Parameter, Col1, Col2 , Count (Col1) from tblTest

group by  Col1, Col2

Difference between Select and set in sql

Difference between Select and set in sql

1. SET is the ANSI standard for variable assignment, SELECT is not.
2. SET can only assign one variable at a time, SELECT can make multiple assignments at once.
3. If assigning from a query, SET can only assign a scalar value. If the query returns multiple values/rows then SET will raise an error. SELECT will assign one of the values to the variable and hide the fact that multiple values were returned (so you'd likely never know why something was going wrong elsewhere - have fun troubleshooting that one)
4. When assigning from a query if there is no value returned then SET will assign NULL, where SELECT will not make the assignment at all (so the variable will not be changed from it's previous value)
5. As far as speed differences - there are no direct differences between SET and SELECT. However SELECT's ability to make multiple assignments in one shot does give it a slight speed advantage over SET.

Select the top 1 into a variable

Declare @Column int

SELECT TOP 1 @Column = Column1 

FROM tbl_Test 
WHERE Column2 = 'test' 

In above query Column1, Column2 are two columns of table 'tbl_Test'.