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

Types of Functions

SQL Server 2008 supports user-defined functions and built-in, system, functions.

Scalar Functions

User-defined scalar functions return a single data value of the type defined in the RETURNS clause. For an inline scalar function, there is no function body; the scalar value is the result of a single statement. For a multistatement scalar function, the function body, defined in a BEGIN...END block, contains a series of Transact-SQL statements that return the single value. The return type can be any data type except text, ntext, image, cursor, and timestamp.

The following examples creates a multistatement scalar function. The function takes one input value, a ProductID, and returns a single data value, the aggregated quantity of the specified product in inventory.

Transact-SQL

USE AdventureWorks2008R2;
GO
IF OBJECT_ID (N'dbo.ufnGetInventoryStock', N'FN') IS NOT NULL
    DROP FUNCTION ufnGetInventoryStock;
GO
CREATE FUNCTION dbo.ufnGetInventoryStock(@ProductID int)
RETURNS int 
AS 
-- Returns the stock level for the product.
BEGIN
    DECLARE @ret int;
    SELECT @ret = SUM(p.Quantity) 
    FROM Production.ProductInventory p 
    WHERE p.ProductID = @ProductID 
        AND p.LocationID = '6';
     IF (@ret IS NULL) 
        SET @ret = 0;
    RETURN @ret;
END;
GO

The following example uses the ufnGetInventoryStock function to return the current inventory quantity for products that have a ProductModelID between 75 and 80.

Transact-SQL

USE AdventureWorks2008R2;
GO
SELECT ProductModelID, Name, dbo.ufnGetInventoryStock(ProductID)AS CurrentSupply
FROM Production.Product
WHERE ProductModelID BETWEEN 75 and 80;
GO

Table-Valued Functions

User-defined table-valued functions return a table data type. For an inline table-valued function, there is no function body; the table is the result set of a single SELECT statement.

The following example creates an inline table-valued function. The function takes one input parameter, a customer (store) ID, and returns the columns ProductID, Name, and the aggregate of year-to-date sales as YTD Total for each product sold to the store.

Transact-SQL

USE AdventureWorks2008R2;
GO
IF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL
    DROP FUNCTION Sales.ufn_SalesByStore;
GO
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
RETURNS TABLE
AS
RETURN 
(
    SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total'
    FROM Production.Product AS P 
    JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
    JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
    JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID
    WHERE C.StoreID = @storeid
    GROUP BY P.ProductID, P.Name
);
GO

The following example invokes the function and specifies customer ID 602.

Transact-SQL

SELECT * FROM Sales.ufn_SalesByStore (602);

For a multistatement table-valued function, the function body, defined in a BEGIN...END block, contains a series of Transact-SQL statements that build and insert rows into the table that will be returned.

The following example creates a table-valued function. The function takes a single input parameter, an EmployeeID and returns a list of all the employees who report to the specified employee directly or indirectly. The function is then invoked specifying employee ID 109.

Transact-SQL

USE AdventureWorks2008R2;
GO
IF OBJECT_ID (N'dbo.ufn_FindReports', N'TF') IS NOT NULL
    DROP FUNCTION dbo.ufn_FindReports;
GO
CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER)
RETURNS @retFindReports TABLE 
(
    EmployeeID int primary key NOT NULL,
    FirstName nvarchar(255) NOT NULL,
    LastName nvarchar(255) NOT NULL,
    JobTitle nvarchar(50) NOT NULL,
    RecursionLevel int NOT NULL
)
--Returns a result set that lists all the employees who report to the 
--specific employee directly or indirectly.*/
AS
BEGIN
WITH EMP_cte(EmployeeID, OrganizationNode, FirstName, LastName, JobTitle, RecursionLevel) -- CTE name and columns
    AS (
        SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, 0 -- Get the initial list of Employees for Manager n
        FROM HumanResources.Employee e 
   INNER JOIN Person.Person p 
   ON p.BusinessEntityID = e.BusinessEntityID
        WHERE e.BusinessEntityID = @InEmpID
        UNION ALL
        SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, RecursionLevel + 1 -- Join recursive member to anchor
        FROM HumanResources.Employee e 
            INNER JOIN EMP_cte
            ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode
   INNER JOIN Person.Person p 
   ON p.BusinessEntityID = e.BusinessEntityID
        )
-- copy the required columns to the result of the function 
   INSERT @retFindReports
   SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel
   FROM EMP_cte 
   RETURN
END;
GO
-- Example invocation
SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel
FROM dbo.ufn_FindReports(1); 

GO

Built-in Functions

Built-in functions are provided by SQL Server to help you perform a variety of operations. They cannot be modified. You can use built-in functions in Transact-SQL statements to:

• Access information from SQL Server system tables without accessing the system tables directly. For more information, see Using System Functions.
• Perform common tasks such as SUM, GETDATE, or IDENTITY. For more information, see Built-in Functions (Transact-SQL).

Built-in functions return either scalar or table data types. For example, @@ERROR returns 0 if the last Transact-SQL statement executed successfully. If the statement generated an error, @@ERROR returns the error number. And the function SUM(parameter) returns the sum of all the values for the parameter.

Difference Between Heap table and Clustered Table

Problem
One very important design aspect when creating a new table is the decision to create or not create a clustered index.  A table that does not have a clustered index is referred to as a HEAP and a table that has a clustered index is referred to as a clustered table.  A clustered table provides a few benefits over a heap such as physically storing the data based on the clustered index, the ability to use the index to find the rows quickly and the ability to reorganize the data by rebuilding the clustered index.  Depending on the INSERT, UPDATE and DELETE activity against your tables your physical data can become very fragmented.  This fragmentation can lead to wasted space in your database, because of partly full pages as well as the need to read several more pages in order to satisfy the query.  So what can be done?

SolutionThe primary issue that we want to address is the fragmentation that occurs with normal database activity.  Depending on whether your table has a clustered index or not will determine if you can easily address the fragmentation problem down to the physical data level.  Because a heap or a clustered index determines the physical storage of your table data, there can only be one of these per table.  So a table can either have one heap or one clustered index.

Let's take a look at the differences between a heap and clustered table.

HEAP

• Data is not stored in any particular order
• Specific data can not be retrieved quickly, unless there are also non-clustered indexes
• Data pages are not linked, so sequential access needs to refer back to the index allocation map (IAM) pages
• Since there is no clustered index, additional time is not needed to maintain the index
• Since there is no clustered index, there is not the need for additional space to store the clustered index tree
• These tables have a index_id value of 0 in the sys.indexes catalog view

source: SQL Server 2005 books online

Clustered Table

• Data is stored in order based on the clustered index key
• Data can be retrieved quickly based on the clustered index key, if the query uses the indexed columns
• Data pages are linked for faster sequential access
• Additional time is needed to maintain clustered index based on INSERTS, UPDATES and DELETES
• Additional space is needed to store clustered index tree
• These tables have a index_id value of 1 in the sys.indexes catalog view

source: SQL Server 2005 books online

So based on the above you can see there are a few fundamental differences on whether a table has a clustered index or not.

Fragmentation A problem that occurs on all tables is the issue of becoming fragmented.  Depending on the activity performed such as DELETES, INSERTS and UPDATES, your heap tables and clustered tables can become fragmented.  A lot of this depends on the activity as well as the key values that are used for your clustered index. 

• If your heap table only has INSERTS occurring, your table will not become fragmented, since only new data is written.
• If your clustered index key is sequential, such as an identity value, and you only have INSERTS, again this will not become fragmented since the new data is always written at the end of the clustered index.
• But if your table is either a heap or a clustered table and there are a lot of INSERTS, UPDATES and DELETES the data pages can become very fragmented.  This results in wasted space as well as additional data pages to read to satisfy the queries. 
  • When a table is created as a heap, SQL Server does not force where the new data pages are written.  Whenever new data is written this data is always written at the end of the table or on the next available page that is assigned to this table.  When data is deleted the space becomes free in the data pages, but it is not reused because new data is always written to the next available page.
  • With a clustered index, depending on the index key, new records may be written to existing pages where free space exists or there may be need to split a page into multiple pages in order to insert the new data.  When deletes occur the same issue occurs as with a heap, but this free space may be used again if data needs to be inserted into one of the existing pages that has free space.
  • So based on this, your heap table could become more fragmented then your clustered table.

Identifying FragmentationTo identify whether your clustered table or heap table is fragmented you need to either run DBCC SHOWCONTIG (2000 or 2005) or use the new DMV sys.dm_db_index_physical_stats (2005).  These commands will give you insight into the fragmentation problems that may exist in your table.  For further information on this take a look at this past tip: SQL Server 2000 to 2005 Crosswalk - Database Fragmentation.

Resolving FragmentationClustered Tables
Resolving the fragmentation for a clustered table can be done easily by rebuilding or reorganizing your clustered index.  This was shown in this previous tip: SQL Server 2000 to 2005 Crosswalk - Index Rebuilds.

Heap Tables
For heap tables this is not as easy.  The following are different options you can take to resolve the fragmentation:

1. Create a clustered index
2. Create a new table and insert data from the heap table into the new table based on some sort order
3. Export the data, truncate the table and import the data back into the table

Additional Info
When creating a new table via Enterprise Manager or Management Studio when you specify a primary key for the table, the management tools automatically make this a clustered index, but this can be overridden.  When creating a new table via scripts you need to identify that the table be created with a clustered index.   So based on this most of your tables are going to have a clustered index, because of the primary key, but if you do not specify a primary key or build a clustered index the data will be stored as a heap.

Next Steps

• Keeping table and index fragmentation under control is a key process to maintain optimum performance out of your database.  Now that you can see how a heap vs a clustered table differs and what needs to be done to address the fragmentation, take a look at your table structures to see if you need to address these issues.
• Even if you are doing a complete index rebuild on all of your tables once a week or whenever, your heap tables will never be de-fragmented, so you will need to come up with another strategy to handle fragmentation issues with these tables.
• Take a look at these other related tips:
  • SQL Server 2000 to 2005 Crosswalk - Database Fragmentation.
  • SQL Server 2000 to 2005 Crosswalk - Index Rebuilds.
• Based on the above it seems that all tables should have a clustered index. For the most part this is the case, but there may be some reason that you do not want to have a clustered index.  One reason could be a table that only has INSERTS, such as a log file.  But if in doubt, it would be better to have a clustered index then to not have one.

Ref: http://www.mssqltips.com/sqlservertip/1254/clustered-tables-vs-heap-tables/