Deepan's Tech

Good Practices to Write Stored Procedures in SQL Server Use proper indentation for the statements in SQL Server. It will improve the readability. Write the proper comments between the logics. So the others can understand quickly. Write all the SQL Server keywords in the CAPS letter. For example SELECT, FROM and CREATE. Write the stored procedure name with full qualified names. CREATE PROCEDURE [dbo].EmployeeSalaryCalculation   Always try to declare the DECLARATION and initialization at the beginning of the stored procedure. It is not recommended to use more variables in the procedure. It will occupy more space in the memory.   Do not write the stored procedure name beginning with sp_. It is reserved for the system stored procedures in SQL Server and when the request comes to the SQL Server engine, it will be considerd to be a system stored procedure and looks for it in the master database. Afte...

Try this and it should work Go to your project folder and open .vs folder (keep your check hidden item-box checked as this folder may be hidden sometimes) in .vs folder - open config see that applicationhost config file there? Delete that thing.(Do not worry it will regenerate automatically once you recompile the project.) Ref : https://stackoverflow.com/questions/37352603/localhost-refused-to-connect-error-in-visual-studio

 I encountered an error when I was deploying mvc website which targeted .NET 4.5 to Windows 8 os in IIS 8. It was an exception shown in browser whenever I tried to open a web page. The exception said: "Could not load type ‘System.ServiceModel.Activation.HttpModule’ from assembly 'System.ServiceModel, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'" Microsoft officially announced the solution ( http://support.microsoft.com/kb/2015129 ) for Windows Server 2008 plus IIS 7.5: manually running “ aspnet_regiis.exe /iru ” for .NET framework 4.x (in  C:\Windows\Microsoft.NET\Framework\v4.0.30319  or  C:\Windows\Microsoft.NET\Framework64\v4.0.30319 ). However,  aspnet_regiis.exe  is not allowed to run for IIS 8. I tried manually changing  applicationHost.config . I also tried removing and adding features in a good order. But all these solutions did not work. The final solution  was to delete the 3.x module and handler from ...

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