Posts Tagged ‘SQL’

View query generated in Linq

Posted: August 19, 2014 in c#, debug, entity framework, linq, sql
Tags: , , ,
///Code below:
var result = from x in appEntities
             where x.id = 32
             select x;

var sql = ((System.Data.Objects.ObjectQuery)result).ToTraceString();

Code:
http://stackoverflow.com/a/1412902

Advertisements
Caution: Only do this if you’re unsure. You can lose data from a table in case it needs to be rebuilt to make the desired changes.
Hi, i hope this tip hel you…
Management Studio Error : “Saving Changes in not permitted.”
Screen say: "Saving Changes in not permitted."

Screen alert error

The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created

The reason for SSMS to go unresponsive is that when some operations (i.e. adding column, changing datatype of any column, etc) happen, it has to do much more work than the actual operation. Let us now examine an operation where we will add a new column to an already existing table.

  • Transaction starts.
  • When a new column is added SSMS renames the existing table to temp table.
  • After that, it creates a new table with new specification.
  • Now, SSMS has two tables: copy of  previous table with data and empty table with new structure.
  • SSMS now starts moving older data from older table to newer table.
  • If there is any error, it rolls back transaction.
  • If there is no error, it commits all the previous operations.
  • Transaction ends.

This feature can be turned off by going to Menu >> Tools >> Options >> Designers >> Uncheck “Prevent Saving changes that

require table re-creation”.
Menu >> Tools >> Options >> Designers >> Uncheck “Prevent Saving changes that require table re-creation”.

Menu Tools Options

Enjoy it.
Reference Link

Query to search in database object contents

Posted: June 5, 2014 in sql
Tags: ,
DECLARE @Search varchar(255)
SET @Search='user'

SELECT DISTINCT
    o.name AS Object_Name,o.type_desc
    FROM sys.sql_modules        m 
        INNER JOIN sys.objects  o ON m.object_id=o.object_id
    WHERE m.definition Like '%'+@Search+'%'
    ORDER BY 2,1

Source

select t.sp_name, sum(t.lines_of_code) - 1 as lines_ofcode, t.type_desc
from
(
 select o.name as sp_name,
 (len(c.text) - len(replace(c.text, char(10), ''))) as lines_of_code,
 case when o.xtype = 'P' then 'Stored Procedure'
 when o.xtype in ('FN', 'IF', 'TF') then 'Function'
 end as type_desc
 from sysobjects o
 inner join syscomments c
 on c.id = o.id
 where o.xtype in ('P', 'FN', 'IF', 'TF')
-- and o.category = 0
 and o.name not in ('fn_diagramobjects', 'sp_alterdiagram', 'sp_creatediagram', 'sp_dropdiagram', 'sp_helpdiagramdefinition', 'sp_helpdiagrams', 'sp_renamediagram', 'sp_upgraddiagrams', 'sysdiagrams')
) t
group by t.sp_name, t.type_desc
order by 1

Source: StackOverflow

How do I view the SQL generated by the entity framework?

var result = from x in appEntities
             where x.id = 32
             select x;

var sql = ((System.Data.Objects.ObjectQuery)result).ToTraceString();

Source: http://stackoverflow.com/questions/1412863/how-do-i-view-the-sql-generated-by-the-entity-framework

I create a extension…

public static class IQueryableExtension
{
     public static String ToTraceSQL(this IQueryable obj) 
     {             
         return ((System.Data.Objects.ObjectQuery)obj).ToTraceString();
     }
}

Using this:

var iQuerableResult = dataBase.BiServico.Take(10);
string myResustSQL = iQuerableResult.ToTraceSQL();

Shazan! =)

hope I have helped!

What is the best way (performance wise) to paginate results in SQL Server 2000, 2005, 2008, 2012 if you also want to get the total number of results (before paginating)?

Microsoft SQL Server 2008 and previous

Getting the total number of results and paginating are two different operations. For the sake of this example, let’s assume that the query you’re dealing with is

SELECT * FROM Orders WHERE OrderDate >= '1980-01-01' ORDER BY OrderDate

In this case, you would determine the total number of results using:

SELECT COUNT(*) FROM Orders WHERE OrderDate >= '1980-01-01'

…which may seem inefficient, but is actually pretty performant, assuming all indexes etc. are properly set up.

Next, to get actual results back in a paged fashion, the following query would be most efficient:

SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
          FROM      Orders
          WHERE     OrderDate >= '1980-01-01'
        ) AS RowConstrainedResult
WHERE   RowNum >= 1
    AND RowNum < 20
ORDER BY RowNum

This will return rows 1-19 of the original query. The cool thing here, especially for web apps, is that you don’t have to keep any state, except the row numbers to be returned.

Microsoft SQL Server 2012

SELECT * FROM TableName ORDER BY id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

source: What is the best way to paginate results in SQL Server