Problem : In my previous project I was been asked call web service from sql server stored procedure. So I get it done using SQL CLR. So using CLR we can run managed code inside sql server. Code that runs within the CLR is referred to as managed code. We can create stored procedures, triggers, user defined types, and user-defined aggregates in managed code. We can achieve significant performance increases because managed code compiles to native code prior to execution. We can use SQL CLR in in SQL Server 2005 and later. Why Sql CLR in sql server: In some cases some tasks are not possible by T-SQL as my requirement. So in that point we can go with SQL CLR. What tools I have used in this post Visual Studio 2015 Sql Server 2014 In Action: Create SQL Server Database project in VS 2015 Add SQL CLR C# Stored Procedure Name stored procedure As CallWebService 3. Add C# codes
From Sql server 2012 we can merge two tables. We can map data from one table to another table. If record exists can do a update and if not insert operation can be done. Syntax Create two tables and Insert some records Then do Merge Now view TargetTable Data
CHOOSE Returns a list item based on its location First parameter is index Next parameters are list IIF Instant if Three parameters Boolean expression Return value if true Return value if false
What is subquery? Subquery is just a query inside of a query. Why Subquery? Break down complex logic Simplify reading Subqueries can be replaced by joins. But some times Joins can be complex. Other thing is we are not allowed to put aggregates in WHERE. But we can put it aggreagates inside subquery . Where to Use? We can put sub queries in three main places in query. SELECT FROM WHERE Using in SELECT Access informations from tables withing select query Query must return a single(scalar) value Creates a dynamic table. Useful for breaking down queries. Query must be aliased. Note : I have used Adventureworks 2012 database for following queries Using with FROM With WHERE Useful for comparing values from other tables Predicates used with subqueries IN Confirm column value exist in subquery Similar to inner join EXISTS Returns true if subquery returns value Used with correlated queries Correlated subquery Pass outer query column into subquery ALL Compares column
It is best idea to do paging in server side. From Sql Server 2012 we have useful paging mechanism We can use FETCH and OFFSET to paging in sql server. FETCH -> indicates number of rows to retrieve OFFSET-> indicates the number of rows to skip Syntax for Paging x means number of record to skip and y means number of record to retrieve Example In this it skips first 20 records and fetch next 20 rows But in this some restrictions are there ORDER BY is required TOP is not allowed
CTE stands for Common Table Expression. It is introduced in sql server 2005. Actualy it is not a terrible thing as we heard. We can consider it as inline view or temporary table. CTE allows us to do exact same things like what we doing using views. Benefits: Breakdown complex queries Avoid sub queires Simply certain syntax Syntax for CTE Example Then we can do any operations to SalesData CTE as we doing to a View. Like : SELECT * FROM SalesData Note: CTE column specifying is optional . if not specified it takes select query result columns We can use it for short hand for sub-query. More over there are many other benefits in CTE. I noted a here very basic usage.