Rebuild vs Reorganize

Rebuild:

An index ‘rebuild’ creates a fresh, sparkling new structure for the index. If the index is disabled, rebuilding brings it back to life. You can apply a new fillfactor when you rebuild an index. If you cancel a rebuild operation midway, it must roll back (and if it’s being done offline, that can take a while).

Reorganize:

This option is more lightweight. It runs through the leaf level of the index, and as it goes it fixes physical ordering of pages and also compacts pages to apply any previously set fillfactor settings. This operation is always online, and if you cancel it then it’s able to just stop where it is (it doesn’t have a giant operation to rollback).

Cast and Convert

1.CAST and CONVERT are two SQL functions used by programmers to convert one data type to another.

2.The CAST function is ANSI standard and is compatible to use in other databases while the CONVERT function is a specific function of the SQL server.

3.Since the CAST function is compatible with other databases, it is also described as portable though it has fewer features compared to the CONVERT function. The CONVERT function, meanwhile, can do some things that the CAST function cannot.

4.The CAST function is used to convert a data type without a specific format. The CONVERT function does converting and formatting data types at the same time.

5.In terms of syntax, both functions have the optional parameter of length. In the CONVERT function, there is an additional parameter called style which specifies the format of the data type after conversion.

6.The CAST function is often used to preserve decimal values and places while converting them into integers. The function can also truncate the decimal value if needed. The CONVERT function cannot perform this task.

nth highest salary in sql server

SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP n salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary

Difference between beginexecutereader and executereader (beginexecutereader Vs executereader)

The BeginExecuteReader(...) and EndExecuteReader...() are for performing the
ExecuteReader(...) method asynchronously. If you use ExecuteReader(...)
your thread is blocked while that method executes.

Using BeginExecuteReader(...) (and the corresponding EndExecuteReader(...))
ExecuteReader(...) will be executed in a seperate thread, allowing for
yours to continue processing. The BeginExecuteReader(...) method will
require a callback method, and in that callback method, you will make a
call to EndExecuteReader(...) to end the asynchronous operation, and
retreive the return value.

Difference between Correlated subquery and Nested subquery

Correlated subquery

Correlated subquery runs once for each row selected by the outer query. It contains a reference to a value
 from the row selected by the outer query.

Correlated subquery follows down to top approach i.e., main query is executed first(even though parenthesis      
 are present) and then child query. 

We can also say:In a subquery.

Example:

 select e1.empname, e1.basicsal, e1.deptno from emp e1

 where e1.basicsal = (select max(basicsal) from emp e2 where e2.deptno = e1.deptno)

Nested subquery

Nested subquery runs only once for the entire nesting (outer) query. It does not contain any reference to the
 outer query row.

We can also say: in a Correlated subquery,Inner query condition is used in the outer query.

Nested subquery follows top-down approach i.e., child query is executed first and then parent .

Outer query condition is used in the the inner query.

Example:

 select empname, basicsal, deptno from emp

 where (deptno, basicsal) in (select deptno, max(basicsal) from emp group by deptno)

Difference between Where and Having clauses

The WHERE clause selects rows before grouping. The HAVING clause selects rows after grouping.
The WHERE clause cannot contain aggregate functions. The HAVING clause can contain aggregate functions.

A HAVING clause is like a WHERE clause, but applies only to groups as a whole, whereas the WHERE clause applies to individual rows. A query can contain both a WHERE clause and a HAVING clause. The WHERE clause is applied first to the individual rows in the tables . Only the rows that meet the conditions in the WHERE clause are grouped. The HAVING clause is then applied to the rows in the result set.

ISNULL Vs NULLIF

ISNULL:
ISNULL ( expression , replacement)
Replaces NULL with the specified replacement value. It returns the value of expression if it is not null.


NULLIF:
NULLIF( expression , expression)
It returns a null value if both the expression is equal. NULLIF returns the first expression if both the expression is not equal.

Update sql server from excel

update tablename set column=t2.column1 from
(
Select ID,column1,column2  FROM
OPENROWSET
('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\test.xls;HDR=YES', 'SELECT  * FROM [SheetName$]'))t2
Where
tablename.ID = t2.ID

Import excel data in sql server 2005

Run the below Query in Sql Server query window


Select * FROM
OPENROWSET
('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\test.xls;HDR=YES', 'SELECT  * FROM [SheetName$]')

SheetName: Sheet which data will be read.
C:\test.xls: Location with name of excel file.

Permission for MYSQL'S user on particular IP

Permission for MYSQL'S user on particular IP.
Run the below query :
Root=user
IP=IP address
mysql=Password

GRANT ALL PRIVILEGES ON *.* TO 'root’@'IP' IDENTIFIED BY 'mysql';

Create MYSQL users with privileges

Create MYSQL users with privileges

INSERT INTO user VALUES('localhost','user',PASSWORD('some_pass'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');

Retrieve Mysql users list and its privilege

Run the below query through command prompt of my-sql

select * from mysql.user\G;

MySQL error 1236:MySQL replication problem

Reasons: the master’s binary log is corrupted, a network problem, or a bug in the master’s or slave’s MySQL code. If you want to check the master’s binary log or slave’s relay log, you will be able to know their names by issuing ‘SHOW SLAVE STATUS’ on this slave’, which was quite logical since the end of the bin-log had been corrupted due to external circumstances.

Solution:

STOP SLAVE;

CHANGE MASTER TO

MASTER_LOG_FILE=’bin.000nnn’,

MASTER_LOG_POS=1;

START SLAVE;

Here check your MASTER_LOG_FILE is your log file name.

Rows count for all tables

Use below query for table name with rows count for specific database.

SELECT
    [TableName] = so.name,
    [RowCount] = MAX(si.rows)
FROM
    sysobjects so,
    sysindexes si
WHERE
    so.xtype = 'U'
    AND
    si.id = OBJECT_ID(so.name)
GROUP BY
    so.name
ORDER BY
    2 DESC

Column data in to single row with comma seperated

Below SQl statement is used for convert single column data in a single row.

declare @testdata varchar(8000)
set @testdata=''

select @testdata=@testdata+NAME+', ' from User where ID in('1','2','3','4','5')
print @testdata

select substring(@testdata,0,len(@testdata))


In above User is a table
NAME is column of user which data will be manuplate in row
ID is column of user
Actual data format was:

After query the data format will be:

Tablespace in sql server

we can check used space by a table with below sql-statement in which table name will be passed with sp_spaceused.

sp_spaceus​ed 'Tablename​'

Disable Trigger in SQL-Server

The syntax for disable trigger is:

DISABLE TRIGGER triggerName ON TableName

Odd / Even rows in SQL Server

For get even rows:

select * from table_1 where id%2=0

For get odd rows

select * from table_1 where id%2<>0


Note: Here table_1 is table name and id is autogenerated id for the column table_1.

Last Run Query in SQL Server

Query:

SELECT conn.session_id, sson.host_name, sson.login_name,
 sqltxt.text, sson.login_time,  sson.status
FROM sys.dm_exec_connections conn
INNER JOIN sys.dm_exec_sessions sson
ON conn.session_id = sson.session_id
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS sqltxt
ORDER BY conn.session_id

Term used in Query:

sys.dm_exec_connections - Returns information about the connections established to this instance of SQL Server and the details of each connection

sys.dm_exec_sessions - Returns one row per authenticated session on SQL Server. sys.dm_exec_sessions is a server-scope view that shows information about all active user connections and internal tasks. This information includes client version, client program name, client login time, login user, current session setting, and more

sys.dm_exec_sql_text - Returns the text of the SQL batch that is identified by the specified sql_handle

@@rowcount in SQL Server

@@rowcount:

Returns the number of rows affected by the last statement.

Example:

print @@rowcount--It will return 0

select top 10 * from user--It will return 10 records of user table.

print @@rowcount--It will return 10

print @@rowcount--It will return 0