How do

How do I list all the indexes on a particular table in sqlserver 2005?

USE YourDatabasename;
GO
EXEC sp_helpindex 'Your_Table_Name'
GO

How do we list all the statistics for particular table?

USE URDATABASE;
GO
EXEC sp_helpstats 'YOUR_Table_Name'
GO

Partitioned Views

Partitioned Views

A partitioned view is a view defined by a UNION ALL of member tables structured in the same way, but stored separately as multiple tables in either the same instance of SQL Server or in a group of autonomous instances of SQL Server servers, called federated database servers.

Is it possible to update View?

Is it possible to update View?

Yes, it is possible but we need to update the data of the base table underlying to the view.
But follow some rules:

Restrictions on Updating Data through Views

Restrictions on Updating Data through Views

You can insert, update, and delete rows in a view, subject to the following limitations:
• If the view contains joins between multiple tables, you can only insert and update one table in the view, and you can't delete rows.
• You can't directly modify data in views based on union queries. You can't modify data in views that use GROUP BY or DISTINCT statements.
• All columns being modified are subject to the same restrictions as if the statements were being executed directly against the base table.
• Text and image columns can't be modified through views.
• There is no checking of view criteria. For example, if the view selects all customers who live in Paris, and data is modified to either add or edit a row that does not have City = 'Paris', the data will be modified in the base table but not shown in the view, unless WITH CHECK OPTION is used when defining the view.

Is it possible to roll back truncated data?

Yes it is possible if we are using tractions.

Use of Function in Where clause

Hi,


Let's look at what happens when we use function in Where clause.

There are 2 places where we can use a function, 1st when we want to fetch some value in select clause and 2nd when we want to compare a value using a function in where clause.

Usage of both ways will affect the performance of the query.



Let's see a simple example of function.



Create a function that return Employee LastName, Firstname for passed Employee ID.

CREATE FUNCTION GetEmpName
(
-- Add the parameters for the function here
@EmployeeId int
)
RETURNS NVARCHAR(100)
AS
BEGIN
-- Declare the return variable here
DECLARE @empname NVARCHAR(100)
SELECT @empname = LastName +', ' + FirstName FROM Employees WHERE EmployeeID=@EmployeeId

RETURN @empname
END
GO

Let's see how this works:



SELECT dbo.GetEmpName(9) AS Employee_Name




Dodsworth, Anne

SELECT distinct dbo.GetEmpName(a.EmployeeID), b.TerritoryDescription
FROM dbo.EmployeeTerritories a
INNER JOIN dbo.Territories b
ON a.TerritoryID=b.TerritoryID
WHERE dbo.GetEmpName(a.EmployeeID)='Davolio, Nancy'

Result



Employee TerritoryDescription

Davolio, Nancy Neward

Davolio, Nancy Wilton



Let's look at the profiler to see how the query is executing:

As we can see that the profiler has executed the function so many times, which actually slow down the query performance.



Let's change our function a little:

CREATE FUNCTION dbo.GetEmpNameCrossJoin
(
-- Add the parameters for the function here
@EmpId int
)
RETURNS Table
AS
Return(
SELECT LastName + ', ' + FirstName EName FROM dbo.Employees)
GO


--Now execute this using Cross APPLY

SELECT distinct I.EName, b.TerritoryDescription
FROM dbo.EmployeeTerritories a
INNER JOIN dbo.Territories b
ON a.TerritoryID=b.TerritoryID
CROSS APPLY dbo.GetEmpNameCrossJoin(a.EmployeeID) I

We have changed the function to return table instead of a scalar value and the function call is done using CROSS APPLY.



Now there is only 1 row in profiler.


This way we can improve the performance of the query.


Happy SQL Coding.

What is TempDB database?

What is TempDB database?

In SQL Server 2005, TempDB plays a very important role and some of the best practices have changed and so has the necessity to follow these best practices on a more wide scale basis. In many cases TempDB has been left to default configurations in many of our SQL Server installations. Unfortunately, these configurations are not necessarily ideal in many environments.

Let's see how TempDB can be optimized to improve the overall SQL Server performance.

Responsibilities of TempDB

Global (##temp) or local (#temp) temporary tables, temporary table indexes, temporary stored procedures, table variables, tables returned in table-valued functions or cursors.

Database Engine objects to complete a query such as work tables to store intermediate results for spools or sorting from particular GROUP BY, ORDER BY, or UNION queries.

Row versioning values for online index processes, Multiple Active Result Sets (MARS) sessions, AFTER triggers and index operations (SORT_IN_TEMPDB).

DBCC CHECKDB work tables.

Large object (varchar(max), nvarchar(max), varbinary(max) text, ntext, image, xml) data type variables and parameters.

Best practices for TempDB are:

Do not change collation from the SQL Server instance collation.

Do not change the database owner from sa.

Do not drop the TempDB database.

Do not drop the guest user from the database.

Do not change the recovery model from SIMPLE.

Ensure the disk drives TempDB resides on have RAID protection i.e. 1, 1 + 0 or 5 in order to prevent a single disk failure from shutting down SQL Server. Keep in mind that if TempDB is not available then SQL Server cannot operate.

If SQL Server system databases are installed on the system partition, at a minimum move the TempDB database from the system partition to another set of disks.

Size the TempDB database appropriately. For example, if you use the SORT_IN_TEMPDB option when you rebuild indexes, be sure to have sufficient free space in TempDB to store sorting operations. In addition, if you are running into insufficient space errors in TempDB, be sure to determine the culprit and either expand TempDB or re-code the offending process.

Hope it will help develop some love for TempDB also.