CREATE TABLE xmlTest
(
 id INT IDENTITY PRIMARY KEY,
 doc XML
)
GO
INSERT xmlTest VALUES('<Person name="Vahid" />')
INSERT xmlTest VALUES('<Person name="Farid" />')
INSERT xmlTest VALUES('<Person name="Mehdi" /><Person name="Hamid" />')
GO

 SELECT doc.query('
for $p in //Person
where $p/@name="Vahid"
return <li>{data($p/@name)} has id = {sql:column("xmlTest.id")}</li>
')
FROM xmlTest

 <li>Vahid has id = 1</li>

 SELECT doc.query('
for $p in //Person
where $p/@name="Vahid"
return <li>{data($p/@name)} has id = {sql:column("xmlTest.id")}</li>
')
FROM xmlTest
WHERE doc.exist('
for $p in //Person
where $p/@name="Vahid"
return <li>{data($p/@name)} has id = {sql:column("xmlTest.id")}</li>
')=1

 SELECT * FROM
(
 (SELECT doc.query('
 for $p in //Person
 where $p/@name="Vahid"
 return <li>{data($p/@name)} has id = {sql:column("xmlTest.id")}</li>
 ') AS col1
 FROM xmlTest)
) A
WHERE CONVERT(VARCHAR(8000), col1)<>''

 DECLARE @number INT = 1
SELECT doc.query('
for $p in //Person
where $p/@name="Vahid"
return <li>{data($p/@name)} has number = {sql:variable("@number")}</li>
')
FROM xmlTest

 SELECT doc.query('/Person') FROM xmlTest

 DECLARE @doc XML
SET @doc = (SELECT * FROM xmlTest FOR XML AUTO, ELEMENTS)
SELECT @doc.query('/xmlTest/doc/Person')

 DECLARE @doc XML ='
<people>
  <person><name>Vahid</name></person>
  <person><name id="2">Farid</name></person>
  <person><name>Mehdi</name></person>
  <person><name>Hooshang</name><name id="1">Hooshi</name></person>
  <person></person>
</people>
'

 SELECT @doc.value('/people/person/name', 'varchar(50)')

 SELECT @doc.value('(/people/person/name)[1]', 'varchar(50)')

 SELECT @doc.query('/people/person/name')

 <name>Vahid</name>
<name id="2">Farid</name>
<name>Mehdi</name>
<name>Hooshang</name>
<name id="1">Hooshi</name>

 SELECT
tab.col.value('text()[1]', 'varchar(50)') AS name,
tab.col.query('.'),
tab.col.query('..')
from @doc.nodes('/people/person/name') AS tab(col)

 tab.col.query('.'),

 SELECT
tab.col.value('.', 'varchar(50)') AS name,
tab.col.query('data(.)'),
tab.col.query('.'),
tab.col.query('..')
from @doc.nodes('/people/person/name') AS tab(col)

 SELECT
tab.col.value('name[.="Farid"][1]', 'varchar(50)') AS name,
tab.col.value('name[.="Farid"][1]/@id', 'varchar(50)') AS id,
tab.col.query('.')
from @doc.nodes('/people/person[name="Farid"]') AS tab(col)

 DECLARE @tblXML TABLE (
 id INT IDENTITY PRIMARY KEY,
 doc XML
 )

INSERT @tblXML VALUES('<person name="Vahid" />')
INSERT @tblXML VALUES('<person name="Farid" />')
INSERT @tblXML VALUES('<person />')
INSERT @tblXML VALUES(NULL)

SELECT
id,
doc.value('(/person/@name)[1]', 'varchar(50)') AS name
FROM @tblXML

 CREATE PROCEDURE LookForPhysicalOps (@op VARCHAR(30))
AS
SELECT sql.text, qs.EXECUTION_COUNT, qs.*, p.*
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) sql
CROSS APPLY sys.dm_exec_query_plan(plan_handle) p
WHERE query_plan.exist('
declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
/ShowPlanXML/BatchSequence/Batch/Statements//RelOp/@PhysicalOp[. = sql:variable("@op")]
') = 1
GO

EXECUTE LookForPhysicalOps 'Table Scan'
EXECUTE LookForPhysicalOps 'Clustered Index Scan'
EXECUTE LookForPhysicalOps 'Hash Match'

 WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT RelOp.op.value(N'../../@NodeId', N'int') AS ParentOperationID,
RelOp.op.value(N'@NodeId', N'int') AS OperationID,
RelOp.op.value(N'@PhysicalOp', N'varchar(50)') AS PhysicalOperator,
RelOp.op.value(N'@LogicalOp', N'varchar(50)') AS LogicalOperator,
RelOp.op.value(N'@EstimatedTotalSubtreeCost ', N'float') AS EstimatedCost,
RelOp.op.value(N'@EstimateIO', N'float') AS EstimatedIO,
RelOp.op.value(N'@EstimateCPU', N'float') AS EstimatedCPU,
RelOp.op.value(N'@EstimateRows', N'float') AS EstimatedRows,
cp.plan_handle AS PlanHandle,
st.TEXT AS QueryText,
qp.query_plan AS QueryPlan,
cp.cacheobjtype AS CacheObjectType,
cp.objtype AS ObjectType
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
CROSS APPLY qp.query_plan.nodes(N'//RelOp') RelOp(op)

DECLARE @doc XML = '
<Invoice>
<InvoiceId>100</InvoiceId>
<CustomerName>Vahid</CustomerName>
<LineItems>
<LineItem>
<Sku>134</Sku>
<Quantity>10</Quantity>
<Description>Item 1</Description>
<UnitPrice>9.5</UnitPrice>
</LineItem>
<LineItem>
<Sku>150</Sku>
<Quantity>5</Quantity>
<Description>Item 2</Description>
<UnitPrice>1.5</UnitPrice>
</LineItem>
</LineItems>
</Invoice>
'

 SET @doc.modify('
insert <InvoiceInfo><InvoiceDate>2014-02-10</InvoiceDate></InvoiceInfo>
after /Invoice[1]/CustomerName[1]
')

SELECT @doc

<Invoice>
  <InvoiceId>100</InvoiceId>
  <CustomerName>Vahid</CustomerName>
  <InvoiceInfo>
        <InvoiceDate>2014-02-10</InvoiceDate>
  </InvoiceInfo>
  <LineItems>
...

 SET @x.modify('insert sql:variable("@x") into /doc[1]')

 The argument 1 of the XML data type method "modify" must be a string literal.

 SET @doc.modify('
insert attribute status{"backorder"}
into /Invoice[1]
')

SELECT @doc

 <Invoice status="backorder">
  <InvoiceId>100</InvoiceId>
....

 SET @doc.modify('delete /Invoice/LineItems/LineItem')
SELECT @doc

 <Invoice status="backorder">
  <InvoiceId>100</InvoiceId>
  <CustomerName>Vahid</CustomerName>
  <InvoiceInfo>
      <InvoiceDate>2014-02-10</InvoiceDate>
  </InvoiceInfo>
  <LineItems />
</Invoice>

 SET @doc.modify('replace value of
  /Invoice[1]/CustomerName[1]/text()[1]
  with "Farid"
')
SELECT @doc

 <Invoice status="backorder">
  <InvoiceId>100</InvoiceId>
  <CustomerName>Farid</CustomerName>
  <InvoiceInfo>
       <InvoiceDate>2014-02-10</InvoiceDate>
  </InvoiceInfo>
  <LineItems />
</Invoice>

 The target of 'replace value of' must be a non-metadata attribute or an element with simple typed content.

 DECLARE @tblTest AS TABLE (xmlField XML)

INSERT INTO @tblTest(xmlField)
VALUES
 (
'<Sample>
   <Node1>Value1</Node1>
   <Node2>Value2</Node2>
   <Node3/>
</Sample>'
)
 
DECLARE @newValue VARCHAR(50) = 'NewValue'

UPDATE @tblTest
SET xmlField.modify(
'insert text{sql:variable("@newValue")} into
  (/Sample/Node3)[1] [not(text())]'
)

SELECT xmlField.value('(/Sample/Node3)[1]','varchar(50)') FROM @tblTest

 UPDATE @tblTest
SET xmlField.modify(
'replace value of (/Sample/Node3/text())[1]
  with sql:variable("@newValue")'
)

ToTable("Users");

Get-Project Iris.DomainClasses, Iris.Datalayer, Iris.Servicelayer, Iris.Web | Install-Package Microsoft.AspNet.Identity.EntityFramework

        public IrisDbContext()
            : base("IrisDbContext")
        {
        }

            modelBuilder.Entity<CustomRole>().ToTable("AspRoles");
            modelBuilder.Entity<CustomUserClaim>().ToTable("UserClaims");
            modelBuilder.Entity<CustomUserRole>().ToTable("UserRoles");
            modelBuilder.Entity<CustomUserLogin>().ToTable("UserLogins");

AddColumn("dbo.Users", "EmailConfirmed", c => c.Boolean(nullable: false, defaultValue:true));

Get-Project Iris.Servicelayer, Iris.Web | Install-Package Microsoft.AspNet.Identity.Owin

                x.For<IIdentity>().Use(() => (HttpContext.Current != null && HttpContext.Current.User != null) ? HttpContext.Current.User.Identity : null);

                x.For<IUnitOfWork>()
                    .HybridHttpOrThreadLocalScoped()
                    .Use<IrisDbContext>();

                x.For<IrisDbContext>().HybridHttpOrThreadLocalScoped()
                   .Use(context => (IrisDbContext)context.GetInstance<IUnitOfWork>());
                x.For<DbContext>().HybridHttpOrThreadLocalScoped()
                   .Use(context => (IrisDbContext)context.GetInstance<IUnitOfWork>());

                x.For<IUserStore<ApplicationUser, int>>()
                    .HybridHttpOrThreadLocalScoped()
                    .Use<CustomUserStore>();

                x.For<IRoleStore<CustomRole, int>>()
                    .HybridHttpOrThreadLocalScoped()
                    .Use<RoleStore<CustomRole, int, CustomUserRole>>();

                x.For<IAuthenticationManager>()
                      .Use(() => HttpContext.Current.GetOwinContext().Authentication);

                x.For<IApplicationSignInManager>()
                      .HybridHttpOrThreadLocalScoped()
                      .Use<ApplicationSignInManager>();

                x.For<IApplicationRoleManager>()
                      .HybridHttpOrThreadLocalScoped()
                      .Use<ApplicationRoleManager>();

                // map same interface to different concrete classes
                x.For<IIdentityMessageService>().Use<SmsService>();
                x.For<IIdentityMessageService>().Use<IdentityEmailService>();

                x.For<IApplicationUserManager>().HybridHttpOrThreadLocalScoped()
                   .Use<ApplicationUserManager>()
                   .Ctor<IIdentityMessageService>("smsService").Is<SmsService>()
                   .Ctor<IIdentityMessageService>("emailService").Is<IdentityEmailService>()
                   .Setter<IIdentityMessageService>(userManager => userManager.SmsService).Is<SmsService>()
                   .Setter<IIdentityMessageService>(userManager => userManager.EmailService).Is<IdentityEmailService>();

                x.For<ApplicationUserManager>().HybridHttpOrThreadLocalScoped()
                   .Use(context => (ApplicationUserManager)context.GetInstance<IApplicationUserManager>());

                x.For<ICustomRoleStore>()
                      .HybridHttpOrThreadLocalScoped()
                      .Use<CustomRoleStore>();

                x.For<ICustomUserStore>()
                      .HybridHttpOrThreadLocalScoped()
                      .Use<CustomUserStore>();

Install-Package Microsoft.Owin.Host.SystemWeb

declare @cntr_value bigint

Select @cntr_value=cntr_value
from sys.dm_os_performance_counters
where instance_name='AdventureWorks' and
counter_name='Write Transactions/sec'

/* ایجاد یک تاخیر مثلاً یک ثانیه */
waitfor delay '00:00:01'

Select cntr_value -@cntr_value
from sys.dm_os_performance_counters
where instance_name='AdventureWorks' and
counter_name='Write Transactions/sec'

EXEC sys.sp_MSforeachtable 'sp_spaceused ''?''';

 USE [AdventureWorksDW2008R2]
GO

CREATE TABLE #TableSpaceUsed(
[name] [nvarchar](120) NULL,
[rows] [nvarchar](120) NULL,
[reserved] [nvarchar](120) NULL,
[data] [nvarchar](120) NULL,
[index_size] [nvarchar](120) NULL,
[unused] [nvarchar](120) NULL
) ON [PRIMARY]

Insert Into #TableSpaceUsed
EXEC sys.sp_MSforeachtable 'sp_spaceused ''?''';

Select * from #TableSpaceUsed
Order by CAST([rows] as int) desc

Drop table #TableSpaceUsed

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

[Serializable()]
[SqlUserDefinedType(Format.Native)]
public struct JalaliDate : INullable
{
    private Int16 m_Year;
    private byte m_Month;
    private byte m_Day;
    private byte m_Hour;
    private byte m_Minute;
    private byte m_Second;
    private bool is_Null;


    public Int16 Year
    {
        get
        {
            return (this.m_Year);
        }
        set
        {
            m_Year = value;
        }
    }

    public byte Month
    {
        get
        {
            return (this.m_Month);
        }
        set
        {
            m_Month = value;
        }
    }

    public byte Day
    {
        get
        {
            return (this.m_Day);
        }
        set
        {
            m_Day = value;
        }
    }

    public byte Hour
    {
        get
        {
            return (this.m_Hour);
        }
        set
        {
            m_Hour = value;
        }
    }

    public byte Minute
    {
        get
        {
            return (this.m_Minute);
        }
        set
        {
            m_Minute = value;
        }
    }

    public byte Second
    {
        get
        {
            return (this.m_Second);
        }
        set
        {
            m_Second = value;
        }
    }

    public bool IsNull
    {
        get
        {
            return is_Null;
        }
    }

    public static JalaliDate Null
    {
        get
        {
            JalaliDate jl = new JalaliDate();
            jl.is_Null = true;
            return (jl);
        }
    }


    public override string ToString()
    {
        if (this.IsNull)
        {
            return "NULL";
        }
        else
        {
            return this.m_Year.ToString("D4") + "/" + this.m_Month.ToString("D2") + "/" + this.m_Day.ToString("D2") + " " + this.Hour.ToString("D2") + ":" + this.Minute.ToString("D2") + ":" + this.Second.ToString("D2");
        }
    }


    public static JalaliDate Parse(SqlString s)
    {
        if (s.IsNull)
        {
            return Null;
        }

        System.Globalization.PersianCalendar pers = new System.Globalization.PersianCalendar();
        string str = Convert.ToString(s);
        string[] JDate = str.Split(' ')[0].Split('/');

        JalaliDate jl = new JalaliDate();

        jl.Year = Convert.ToInt16(JDate[0]);
        byte MonthsInYear = (byte)pers.GetMonthsInYear(jl.Year);
        jl.Month = (byte.Parse(JDate[1]) <= MonthsInYear ? (byte.Parse(JDate[1]) > 0 ? byte.Parse(JDate[1]) : (byte)1) : MonthsInYear);
        byte DaysInMonth = (byte)pers.GetDaysInMonth(jl.Year, jl.Month); ;
        jl.Day = (byte.Parse(JDate[2]) <= DaysInMonth ? (byte.Parse(JDate[2]) > 0 ? byte.Parse(JDate[2]) : (byte)1) : DaysInMonth);
        if (str.Split(' ').Length > 1)
        {
            string[] JTime = str.Split(' ')[1].Split(':');
            jl.Hour = (JTime.Length >= 1 ? (byte.Parse(JTime[0]) < 23 && byte.Parse(JTime[0]) >= (byte)0 ? byte.Parse(JTime[0]) : (byte)0) : (byte)0);
            jl.Minute = (JTime.Length >= 2 ? (byte.Parse(JTime[1]) < 59 && byte.Parse(JTime[1]) >= (byte)0 ? byte.Parse(JTime[1]) : (byte)0) : (byte)0);
            jl.Second = (JTime.Length >= 3 ? (byte.Parse(JTime[2]) < 59 && byte.Parse(JTime[2]) >= (byte)0 ? byte.Parse(JTime[2]) : (byte)0) : (byte)0);
        }
        else { jl.Hour = 0; jl.Minute = 0; jl.Second = 0; }

        return (jl);
    }

    public SqlString GetDate()
    {
        return this.m_Year.ToString("D4") + "/" + this.m_Month.ToString("D2") + "/" + this.m_Day.ToString("D2");
    }

    public SqlString GetTime()
    {
        return this.Hour.ToString("D2") + ":" + this.Minute.ToString("D2") + ":" + this.Second.ToString("D2");
    }

    public SqlDateTime ToGregorianTime()
    {
        System.Globalization.PersianCalendar pers = new System.Globalization.PersianCalendar();
        return SqlDateTime.Parse(pers.ToDateTime(this.Year, this.Month, this.Day, this.Hour, this.Minute, this.Second, 0).ToString());
    }

    public SqlString JalaliDateAdd(SqlString interval, int increment)
    {
        System.Globalization.PersianCalendar pers = new System.Globalization.PersianCalendar();
        DateTime dt = pers.ToDateTime(this.Year, this.Month, this.Day, this.Hour, this.Minute, this.Second, 0);
        string CInterval = interval.ToString();
        bool isConvert = true;
        switch (CInterval)
        {
            case "Year":
                dt = pers.AddYears(dt, increment);
                break;
            case "Month":
                dt = pers.AddMonths(dt, increment);
                break;
            case "Day":
                dt = pers.AddDays(dt, increment);
                break;
            case "Hour":
                dt = pers.AddHours(dt, increment);
                break;
            case "Minute":
                dt = pers.AddMinutes(dt, increment);
                break;
            case "Second":
                dt = pers.AddSeconds(dt, increment);
                break;
            default:
                isConvert = false;
                break;
        }

        if (isConvert == true)
        {
            this.Year = (Int16)pers.GetYear(dt);
            this.Month = (byte)pers.GetMonth(dt);
            this.Day = (byte)pers.GetDayOfMonth(dt);
            this.Hour = (byte)pers.GetHour(dt);
            this.Minute = (byte)pers.GetMinute(dt);
            this.Second = (byte)pers.GetSecond(dt);
        }


        return this.m_Year.ToString("D4") + "/" + this.m_Month.ToString("D2") + "/" + this.m_Day.ToString("D2") + " " + this.Hour.ToString("D2") + ":" + this.Minute.ToString("D2") + ":" + this.Second.ToString("D2");
    }
}

USE Northwind

GO

CREATE TABLE dbo.TestTable
(
Id int NOT NULL IDENTITY (1, 1),
TestDate dbo.JalaliDate NULL
)  ON [PRIMARY]
GO

Insert into TestTable (TestDate) Values ('1392/02/09'),('1392/02/09 22:40'),('1392/12/30 22:40')

sp_configure 'clr enabled', 1
Reconfigure

Insert into TestTable (TestDate) Values ('1392/02/09'),('1392/02/09 22:40'),('1392/12/30 22:40')

Declare @a JalaliDate  = '1392/02/07 00:00:00'
Declare @b JalaliDate = '1392/02/05 00:00:00'

SELECT DATEDIFF("DAY",@b.ToGregorianTime(),@a.ToGregorianTime()) AS DiffDate

Here are some additional changes in SQL Server 2016.

var result = 
    from product in dbContext.Products
    where product.Category.Name == "Toys"
    where product.Price >= 2.50
    select product.Name;

• Fluent Syntax : 

var result = dbContext.Products
    .Where(p => p.Category.Name == "Toys" && p.Price >= 250)
    .Select(p => p.Name);

    public class Student
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Field1 { get; set; }
        public string Field2 { get; set; }
        public string Field3 { get; set; }


        public static IEnumerable<Student> GetStudentSource()
        {
            for (int i = 0; i < 10; i++)
            {
                yield return new Student
                                 {
                                     Id = i,
                                     Name = "Name " + i,
                                     Field1 = "Field1 " + i,
                                     Field2 = "Field2 " + i,
                                     Field3 = "Field3 " + i
                                 };
            }
        }
    }

    public class Program
    {
        private static void Main(string[] args)
        {
            System.Console.WriteLine("Specify the desired fields : ");
            string fields = System.Console.ReadLine();
            IEnumerable<Student> students = Student.GetStudentSource();
            IQueryable output = students.AsQueryable().Select(string.Format("new({0})", fields));
            foreach (object item in output)
            {
                System.Console.WriteLine(item);
            }
          
            System.Console.ReadKey();
        }
  
    }

    public  class SelectBuilder <T>
    {
        public static Func<T, T> CreateNewStatement(string fields)
        {
            // input parameter "o"
            var xParameter = Expression.Parameter(typeof(T), "o");


            // new statement "new T()"
            var xNew = Expression.New(typeof(T));

            // create initializers
            var bindings = fields.Split(',').Select(o => o.Trim())
                .Select(o =>
                {

                    // property "Field1"
                    var property = typeof(T).GetProperty(o);

                    // original value "o.Field1"
                    var xOriginal = Expression.Property(xParameter, property);

                    // set value "Field1 = o.Field1"
                    return Expression.Bind(property, xOriginal);
                }
            ).ToList();

            // initialization "new T { Field1 = o.Field1, Field2 = o.Field2 }"
            var xInit = Expression.MemberInit(xNew, bindings);

            // expression "o => new T { Field1 = o.Field1, Field2 = o.Field2 }"
            var lambda = Expression.Lambda<Func<T, T>>(xInit, xParameter);

            // compile to Func<T, T>
            return lambda.Compile();
        }
    }

       IEnumerable<Student> result = students.Select(SelectBuilder<Student>.CreateNewStatement("Field1, Field2")).ToList();

            foreach (Student student in result)
            {
                System.Console.WriteLine(student.Field1);
            }

ابتدا فیلد‌های انتخابی کاربر که با "," جدا شده اند به ورودی پاس داده می‌شود سپس یک statement  خالی ایجاد می‌شود : 

o=>new Student()

$"Users: {Users.Count()}".Dump();
$"Logins: {Logins.Count()}".Dump();

Users: 1200
Logins: 21000