So many time, we will get the scenario to get the history of deleted table, in sql server we can get the history of deleted table like this
Sql Server
Implementing Auto Complete on Sql Server Remote Server
While working on remote server, we donot get auto complete feature on any sql server version. It will very difficult to write sql query.
But there are some alternate approach to implement autocomplete on sql server using sql server extestion like sql complete and Apex Sql Complete
Apex sql complete download Link
This tools really cool and handy for developer who is writing the sql query every day.
Useful shortcut key in Sql Server
There are so many handy short key available in sqlserver. Which will improve the productivity of developer. I have made note on few of them. I hope this will help to other developer too.
1. Open a new query window
Click on >> CTRL +N
2. Toggle between the opened tabs
Click on >> CTRL+TAB
3. Show/Hide result panel
If you have to hide the result window then click on >> CTRL + R
4. Execute highlighted query
There are two option to execute the query that is using
a. F5
b. CTRL + E
5. Cancel the query execution
Click on >> ALT+ BREAK
6. Make the selected Text with Uppercase
Click on >> CTRL + SHIFT + U
7. Make the selected Text with Lowercase
Click on >> CTRL + SHIFT + L
8. Display the estimated execution plan
>> Select the query then click on CTRL + L
9. Intelligence list member and complete word
>> CTRL + SPACE, TAB
10. Go to Line no.
>> CTRL + G
11. To Comment the code
>> Ctrl + K & Ctrl + C;
12. To uncomment the code
>> Ctrl + K & Ctrl + U
13. Showing the table definition
>> Select the table name then click ALT + F1
14. Bookmark in Code
CTRL +K; CTRL+K
Summary
We show that some of the handy shortcut key of Sqlserver. If You know more than this, please let me know by your comment.
What is the CTE in SQL Server ?
1. CTE is the common table expression. This feature comes in sql server 2005 onward.
2. It is the temporary result set and generally it will be the result set of complex Sub Query.
3. CTE offer the same functionality as View.
4. It is defined with WITH Satement.
5. It improves the readability and ease in maintenance of complex queries and sub query.
CTE Simple Example
WITH CTE1 AS ( SELECT 1 as Col1, 2 as Col2 ) Select * from CTE1
Other Example
WITH CTEExample(EmpName,EmpAddress,DeptName) --Column Name for CTE AS ( Select E.EmpName, E.EmpAddress,D.DeptName from tblEmp E Join tblDept D On E.DeptId=D.DeptId ) Select * from CTEExample where CTEExample.DeptName='CS'
When to use CTE ?
1. To simplifying the complex query to simple one like View in sql
2. To maintain more readable code
3. To create recursive query.
4. if you need to reference/join the same data set multiple times you can do so by defining a CTE. Therefore, it can be a form of code re-use.
5. To hold the values as Temp Table.
Point to remember
1. CTE Life will expire after first select statement.
2. In CTE, We can write only one Select query.
What is the difference between Temp Table and Table Variable?
Temp table and Table Variable both are created in TempDB and not in Memory
Syntax for creating Temp Table is exactly same as creating Physical Table
Create table #Emp(Id int, EmpName Varchar(150),EmpAdd Varchar(150)) Insert into #Emp(Id,EmpName,EmpAdd) Values(1,'Ram','Bangalore') Select *from #Emp
The syntax for creating table variables is quite similar to creating either regular or temporary tables. The only differences involve a naming convention unique to variables in general, and the need to declare the table variable as you would any other local variable in Transact SQL:
Declare @Emp Table(Id int,EmpName Varchar(150),EmpAdd Varchar(150)) Insert into @Emp (Id,EmpName,EmpAdd) Values(2,'Ram','Bangalore') Select *from @Emp
• Table variables can not have Non-Clustered Indexes
• You can not create constraints in table variables
• You can not create default values on table variable columns
• Statistics can not be created against table variables
Similarities with temporary tables include:
• Instantiated in tempdb
• Clustered indexes can be created on table variables and temporary tables
• Both are logged in the transaction log
• Just as with temp and regular tables, users can perform all Data Modification Language (DML) queries against a table variable: SELECT, INSERT, UPDATE, and DELETE.
Table variable lifespan is only for the duration of transaction that it runs in. For executing the Table variable we have to execute the complete block code. Otherwise it will throw the exception.
When to use Table variable?
Table variable is mainly used in the function. We cannot use Temp table in function. For example
CREATE FUNCTION dbo.example1 ( ) RETURNS INT AS BEGIN DECLARE @t1 TABLE (i INT) INSERT @t1 VALUES(1) INSERT @t1 VALUES(2) UPDATE @t1 SET i = i + 5 DELETE @t1 WHERE i < 7 DECLARE @max INT SELECT @max = MAX(i) FROM @t1 RETURN @max END GO --Exec example1 --Select dbo.example1()
We cannot create above example using Temp Temple like this
CREATE FUNCTION dbo.example2 ( ) RETURNS INT AS BEGIN CREATE TABLE #t1 (i INT) INSERT #t1 VALUES(1) INSERT #t1 VALUES(2) UPDATE #t1 SET i = i + 5 DELETE #t1 WHERE i < 7 DECLARE @max INT SELECT @max = MAX(i) FROM #t1 RETURN @max END GO
Chandradev's Blog 