Concatenate Column Values of a table using COALESCE

DECLARE @ColumnNameList VARCHAR (MAX)

 SELECT   @ColumnNameList   = COALESCE (@ColumnNameList +',' , '') + Column Name

                                                             FROM

                                                               <<Table Name>>

SELECT @ColumnNameList

Eg:

Create table Emp(empid int, ename varchar(100))

Insert into Emp values(1,'Ganga');

Insert into Emp values(1,'Raju');

Insert into Emp values(1,'Kondapalli');

DECLARE @ColumnNameList VARCHAR (MAX)

 SELECT   @ColumnNameList   = COALESCE (@ColumnNameList +',' , '') + ename

                                                                                 FROM

                                                                                   Emp

SELECT @ColumnNameList

Result:

Ganga,Raju,Kondapalli

WHERE And HAVING Clauses in SQL and Differences between them

         WHERE is used in any SELECT query, while HAVING clause is only used in SELECT queries, which contains aggregate function or group by clause, which is correct. Though both WHERE and HAVING clause is used to specify filtering condition in SQL, there is subtle difference between them.

The key difference between WHERE and HAVING clause in SQL is that, condition specified in WHERE clause is used while fetching data (rows) from table, and data which doesn't pass the condition will not be fetched into result set, on the other hand HAVING clause is later used to filter summarized data or grouped data. In short if both WHERE and HAVING clause is used in a SELECT query with aggregate function or GROUP BY clause, it will execute before HAVING clause

The below example gives clear picture of  the use of where and having
SELECT * FROM Employee;

EMP_ID	EMP_NAME	EMP_AGE	EMP_SALARY	DEPT_ID
1	Virat	23	10000	1
2	Rohit	24	7000	2
3	Suresh	25	8000	3
4	Shikhar	27	6000	1
5	Vijay	28	5000	2

SELECT * FROM Department;

DEPT_ID	DEPT_NAME
1	Accounting
2	Marketing
3	Sales

SELECT d.DEPT_NAME, count(e.EMP_NAME) as NUM_EMPLOYEE, avg(e.EMP_SALARY) as AVG_SALARY FROM Employee e,

Department d WHERE e.DEPT_ID=d.DEPT_ID AND EMP_SALARY > 5000 GROUP BY d.DEPT_NAME;

DEPT_NAME	NUM_EMPLOYEE	AVG_SALARY
Accounting	1	8000
Marketing	1	7000
Sales	2	8000

SELECT d.DEPT_NAME, count(e.EMP_NAME) as NUM_EMPLOYEE, avg(e.EMP_SALARY) as AVG_SALARY FROM Employee e, Department d WHERE e.DEPT_ID=d.DEPT_ID AND EMP_SALARY > 5000 GROUP BY d.DEPT_NAME HAVING AVG_SALARY > 7000;

DEPT_NAME	NUM_EMPLOYEE	AVG_SALARY
Accounting	1	8000
Sales	2	8000

Difference between WHERE and HAVING in SQL

1).We can use WHERE clause with UPDATE and DELETE clause but HAVING clause can only be used with SELECT query.

eg:
update DEPARTMENT set DEPT_NAME="NewSales" WHERE DEPT_ID=1 ;  // works fine

Update DEPARTMENT set DEPT_NAME="NewSales" HAVING DEPT_ID=1; // error

Incorrect syntax near the keyword ’HAVING’. update DEPARTMENT set DEPT_NAME='NewSales' HAVING DEPT_ID=1

 2)  WHERE clause is used for filtering rows and it applies on each and every row, while HAVING clause is used to filter groups in SQL.

3) One syntax level difference between WHERE and HAVING clause is that, former is used before GROUP BY clause, while later is used after GROUP BY clause.

4) When WHERE and HAVING clause are used together in a SELECT query with aggregate function,  WHERE clause is applied first on individual rows and only rows which pass the condition is included for creating groups. Once group is created, HAVING clause is used to filter groups based upon condition specified.

Partitioning a SQL Server Database Table

The below article explains very efficiently to partition the table in SQL Server

http://databases.about.com/od/sqlserver/a/partitioning.htm


Hope the above article helps the people you visit my blog

Full-Text Search (SQL Server)

                     Full-Text Search in SQL Server lets users and applications run full-text queries against character-based data in SQL Server tables. Before you can run full-text queries on a table, the database administrator must create a full-text index on the table. The full-text index includes one or more character-based columns in the table. These columns can have any of the following data types: char, varchar, nchar, nvarchar, text, ntext, image, xml, or varbinary(max) and FILESTREAM. Each full-text index indexes one or more columns from the table, and each column can use a specific language

.

Difference Between ‘Like’ and ‘Full-Text Search’

1.      You cannot use the LIKE predicate to query formatted binary data.

2.      LIKE query against a large amount of unstructured text data is much slower than an equivalent full-text query against the same data

Architecture of Full-Text Search

Prerequisites for Full-Text Search

1.  Configure database for Full-Text Search

2. Must be created Unique/Primary Key On that table.

3. Must be created Full text index on the column which we want to be using for Full-Text search.

Example:

1.CREATE TABLE BOOKS(BOOK_ID INT CONSTRAINT PK PRIMARY KEY , BOOK_TYPE VARCHAR(200))

2.CREATE FULLTEXT CATALOG ft AS DEFAULT;

3.CREATE FULLTEXT INDEX ON BOOKS(BOOK_TYPE)

   KEY INDEX pk

   WITH STOPLIST = SYSTEM;

4.INSERT INTO BOOKS VALUES(2,'.NET BOOK')

5.INSERT INTO BOOKS VALUES(2,'.JAVA BOOK')

6.SELECT BOOK_TYPE

FROM BOOKS WHERE CONTAINS(BOOK_NAME, 'JAVA')

Result:

Book_type

Java

Difference between temp table and table variable

* Both are saved in tempDB
* Temp table can't be used in functions.
* Can't impose indexes on table variable.


http://dba.stackexchange.com/questions/16385/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server/16386#16386

SQL Server clustering

http://www.mssqltips.com/sqlservertip/1541/getting-started-with-sql-server-clustering/

Indexed View / Indexes on View

http://www.databasejournal.com/features/mssql/article.php/3867651/SQL-Server-Indexed-Views.htm