Show Posts

This section allows you to view all posts made by this member. Note that you can only see posts made in areas you currently have access to.


Messages - kaluahir16

Pages: [1] 2
1
SQL Server / What is disadvantages of function in where clause of sql query?
« on: October 10, 2012, 04:02:54 PM »
SQL Server will not be able to use any index there may be on your date column.
This can kill performance on a large table

2
SQL Server / How to find no of page of table in sql server
« on: October 04, 2012, 02:52:40 PM »
We can use following formula for it :

No. of Pages = ((Column1 size + Column2 size+ Column3 size ...) * No. of row) / 4096

Try following query to find no. of page
Code: [Select]
Create table Company
(
Name varchar (50),
Address Varchar(100),
Description Varchar(8000)
)

Insert into Company
select 'Abc','Ahmedabad','test'
Union
select 'qwe','Ahmedabad','test'
Union
select 'poi','Ahmedabad','test'
Union
select 'xyz','Ahmedabad','test'

select ((50 + 100 + 8000) * 3) / 4096  as [No. of page]

3
SQL Server / How to run query with index ?
« on: September 27, 2012, 07:24:18 PM »

We can use following syntax for run query with index.

SELECT *
FROM TableName  WITH (INDEX(IndexName))

Regards,

Kalu Bhuva

4
SQL Server / What is use of COALESCE ?
« on: September 25, 2012, 09:38:40 PM »
COALESCE accepts one or more column names of the same data type. The COALESCE function checks the value of each column in the order in which they are listed and returns the first Not NULL value,

SELECT Name, COALESCE(Office_Phone, Cell_Phone, Home_Phone)  as Phone
FROM EMP;

5
SQL Server / What is Different between Primary key and Unique ?
« on: September 24, 2012, 04:24:26 PM »
Primary Key  and Unique Key  both are enforces uniqueness of the column on which they are defined.  But 
1)  Primary Key creates a clustered index on the column  when Unique Key creates a non-clustered index on the column. 
2) Primary Key does not allow Nulls. Unique Key allows only one NULL Value

6
SQL Server / Limitation of SELCT INTO statement.
« on: September 22, 2012, 02:15:16 PM »
You cannot use SELECT INTO to create a partitioned table even when the source table is partitioned., you must first create the partitioned table and then use the INSERT INTO…SELECT FROM statement.

7
SQL Server / NULL is not zero or empty string but it is use for UNKNOWN.
« on: September 21, 2012, 08:48:00 PM »
NULL is not zero or empty string but it is use for UNKNOWN.

8
SQL Server / Re: Get Columns Along with PrimaryKeys,ForeignKeys
« on: September 21, 2012, 08:44:05 PM »
We can use following command and find out column list query, index list query ,PrimaryKeys and ForeignKeys query.

 sp_helptext  sp_help

9
SQL Server / What is different between Estimated Execution Plan and Actual Execution Plan.
« on: September 20, 2012, 04:15:45 PM »
All most Both are same but there are some cases which create differences in both the execution plans.
For example, if you have old statistics for your index or column, estimated execution plan
selects the wrong path to execute the query, in this situation when query is actually going to
execute, the storage engine changes the path of query execution, to gain performance, which
will be reflected in the actual execution plan.

10
SQL Server / Avoid creating indexes on large string columns.
« on: September 19, 2012, 06:57:41 PM »
Avoid creating indexes on large string columns.

11
SQL Server / Re: VIEW DEFINITION Permission in SQL Server
« on: September 19, 2012, 03:51:30 PM »
It is very usefull.

12
SQL Server / How to remove unused (aged or is out of date) execution plan ?
« on: September 19, 2012, 03:50:45 PM »
it is removed by the Lazy Writer process.

To explain in more detail; if your query plan has a cost factor of four and it is referenced two times, age of plan becomes eight now. The lazywriter process periodically scans the list of objects in the plan cache. The lazywriter then decrements the age field of each object by one on each scan. The age of the query plan is decremented to zero after 8 scans of the plan cache if the same plan is not referenced even a single time in the duration of these eight scans by lazywriter. The lazywriter process deallocates an object if the following conditions are met:

The age field for the object is zero
The memory manager requires memory and all available memory is currently being used

13
SQL Server / A Functions @@CPU_BUSY return time value in Ticks units.
« on: September 18, 2012, 03:57:33 PM »
Functions @@CPU_BUSY return time value in
ticks (A tick is system-dependent time unit) and not in milliseconds or microseconds.
 To retrieve time values in microseconds, multiply the returned values by 31.50

That mens 1 tick = 31.50 microseconds.

14
SQL Server / What is Disadvantages of Cursor?
« on: September 16, 2012, 11:05:38 AM »
suppose you want to retrieve 10000 row.
each and every single row you retrieve will hit the server as a
single select query so performance will be low.

 

15
SQL Server / What is different between datetime and datetim2 ?
« on: September 16, 2012, 10:50:42 AM »
DATETIME2 is all most similar to the older DATETIME data type, but it has a greater range and precision. The format is YYYY-MM-DD hh:mm:ss:nnnnnnnm with a range of 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999, and an accuracy of 100 nanoseconds.

Pages: [1] 2