Deepan's Tech

Microsoft have introduced the TRY & CATCH syntax (TAC) for error handling is T-SQL, in SQL server 2005. The .NET must have used this syntax before also, as this TAC was initially introduced and now they are introducing this in SQL also How it works As I have mentioned this TAC block is very similar to what be use in other.net languages. The idea is fairly simple When an error condition is detected in a Transact-SQL statement contained in a TRY block, control is passed to a CATCH block where it can be processed. Begin Try       The_Query_for_which_we_need_to_do_the_ Error_Handling  End Try  Begin Catch      I f there is some error in the query within the Try block, this flow    will be passed to this Catch block.  End catch   If there are no errors inside the TRY block, control passes to the statement immediately after the associated END CATCH statement. If the END CATCH statement is the last statement i...

SET STATISTICS PROFILE : This set statements used to get the query output (result set) and query plan execution information with row count value. There are other set statements such as  SHOWPLAN_TEXT ,  SHOWPLAN_XML  and  SHOWPLAN_ALL  are also used to get the query execution plan but it will not return the result set as part of the output. This is very useful when we need both result set and execution plan information for performance tuning. Usage SET STATISTICS PROFILE ON or SET STATISTICS PROFILE OFF Example Executing a query when SET STATISTICS PROFILE is ON.

SQL Server provides the concept of temporary table which helps the developer in a great way. These tables can be created at runtime and can do the all kinds of operations that one normal table can do.  But, based on the table types, the scope is limited.  These tables are created inside tempdb database Different Types of Temporary Tables SQL Server provides two types of temp tables based on the behavior and scope of the table. These are: Local Temp Table Global Temp Table Local Temp Table Local temp tables are only available to the current connection for the user; and they are automatically deleted when the user disconnects from instances. Local temporary table name is stared with hash ("#") sign. Global Temp Table Global Temporary tables name starts with a double hash ("##"). Once this table has been created by a connection, like a permanent table it is then available to any user by any connection. It can only be deleted once all c...

Index is a database object, which can be created on one or more columns (16 Max column combination). When creating the index will read the column(s) and forms a relevant data structure to minimize the number of data comparisons.   The index will improve the performance of data retrieval and adds some overhead on data modification such as create, delete and modify. So it depends on how much data retrieval can be performed on table versus how much of DML (Insert, Delete and Update) operations. Clustered Index The primary key created for the   StudId  column will create a clustered index for the   Studid  column. A table can have only one clustered index on it. When creating the clustered index, SQL server 2005 reads the  Studid  column and forms a Binary tree on it. This binary tree information is then stored separately in the disc. Expand the table  Student  and then expand the  Index es. You will see the following index creat...

CREATE TABLE [DupEmployee]( id int identity(1,1), [Name] [varchar](50) NULL, [Age] [int] NULL, [Designation] [varchar](50) NULL,  )  insert into [DupEmployee] values('Raj',32,'TL')  insert into [DupEmployee] values('Deena',28,'SS')  insert into [DupEmployee] values('Raj',32,'TL')  insert into [DupEmployee] values('Gow',27,'TA')  insert into [DupEmployee] values('Raj',32,'TL')  insert into [DupEmployee] values('Siva',27,'TTL')  insert into [DupEmployee] values('Gow',27,'TA') DELETE FROM DupEmployee WHERE ID NOT IN ( SELECT MIN(id) FROM DupEmployee GROUP BY Name,Age,Designation) Result of select query is  2 4 1 6 We can do this using CTE also... ref http://deepanstech.blogspot.in/2017/04/sql-ctecommon-table-expressions.html

SQL Server 2005 and on wards, a very powerful feather has been added for the programmers' benefit called CTE. CTE is again a temporary result set derived from the underling definition.  Common Table Expressions offer the same functionality as a view, but are ideal for one-off usages where you don't necessarily need a view defined for the system. CTE offers the advantages of improved readability and ease in maintenance of complex queries . The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated. Common Table Expression Syntax A Common Table Expression contains three core parts: The CTE name (this is what follows the WITH keyword) The column list (optional) The query (appears within parentheses after the AS keyword) The query using the CTE must be the first query appearing after the CTE. Syntax : WITH expression_name [ ( column_n...

This article gives an idea about the situation called interface clash. This situation occurs when two interfaces have functions with the same name and signature and one base class implements these interfaces. Background In earlier languages like C++, there was the concept of Multiple Inheritance. But in C#, we do not have any such feature. This was something in the list of features which is available in C++ but not in C#. Due to this feature in C++, developers were facing the problem of Diamond Inheritance. That’s why Virtual Functions came into the picture. Using the Code In C#, when two interfaces have functions with the same name and a class implements these interfaces, then we have to specifically handle this situation. We have to tell the compiler which class function we want to implement. For such cases, we have to use the name of the interface during function implementation. Have a look at the following example: Blocks of code should be set as style  Formatted ...