sql server2005
Interview Questions of SQLServer
Do you know what are User Defined functions. Scalar, Inline Table-Valued and Multi-statement Table-valued. A Scalar user-defined function returns one of the scalar data types. Text, ntext, image and timestamp data types are not supported. These are the...
Sqlserver - Query to Get Size of the data
Query to get the size of a column data for each row. Select DATALENGTH(<
Query to remove the matching expression SQLServer
A simple query to remove the particular patreen from the entire string, its just like reggular expression in the ASP.Net Query to strip the matching expression charectors from string , Declare @myExpression varchar(50) Declare @queryString varchar(max)...
FOR XML - SQL Server
Introduction In Microsoft SQL Server 2000, we introduced the FOR XML clause to the SELECT statement. This clause provides the ability to aggregate the relational rowset returned by the SELECT statement into XML. FOR XML on the server supports three modes—RAW,...
SQL Server Rank functions, RANK, NTILE, DENSE_RANK, ROW_NUMBER
Transact-SQL provides the following ranking functions: -RANK -NTILE -DENSE_RANK -ROW_NUMBER Rank() - Applies to monotonically increasing number for each value in the set Row_Number() - simply assigns sequential numbering to the records of a result-set...
Time Delay to Execute a T-SQL Query
A New feature in SQL Server is Time delay to execute a T-SQL Query SQL Server will wait for perticular time to excute the query. Ex : i) SELECT GETDATE() CurrentTime WAITFOR DELAY '00:00:20' ---- 5 Second Delay SELECT GETDATE() CurrentTime Ex: ii) DECLARE...
Defragmenting Indexes in SQL Server
When data is inserted, deleted, or updated in a SQL Server tables, the indexes which are defined for that tables are automatically updated to reflect those changes. When these indexes are modified, the information stored in them becomes fragmented, resulting...
Should we make SQL queries case sensitive?
Should we make SQL queries case sensitive? Yes, we should. We have so many ways to get the data from the SqlServer with Case Sensitive. Ex : Create table mytab (id int, Name varchar(50)) Insert into mytab (1,'aaaa') Insert into mytab (1,'AAaa') Insert...
CASE Statement in Update Queries
We can implement CASE statement in Update Queries. Instead of multiple update queries, you can implement multile condition by CASE statement to update a table or get the customized result. Ex : We have a table called Tmp_Tab with column as (PRIORITY,IsUpdated...
Best Tips to write a Stored Procedure
Good Tips to write stored procedure, to improve the performance. Below are the general rules to write good stored procedure. The sp_ prefix : Don't use the "sp_" prefix in a User created stored procedure name as the "sp_" prefix is reserved for system...