docker pull microsoft/mssql-server-linux:2017-latest

 sudo docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=<YourStrong!Passw0rd>' \
-p 1433:1433 --name sql1 \
-d mcr.microsoft.com/mssql/server:2017-latest

Server Host: localhost
Port: 1433
Authentication: SQL Server Authentication
Login: SA
Password: <StrongPasswordYouSet>

sudo docker exec -it sql1 "bash"

/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P '<YourNewStrong!Passw0rd>'

 CREATE DATABASE TestDB

 CREATE TABLE Inventory (id INT, name NVARCHAR(50), quantity INT)

 INSERT INTO Inventory VALUES (1, 'banana', 150); INSERT INTO Inventory VALUES (2, 'orange', 154);

 SELECT Name from sys.Databases

 SELECT * FROM Inventory WHERE quantity > 152;

dotnet new webapi -o dockerapi

dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Design

public class Students
{
       public int Id { get; set; }
       public string Name { get; set; }
       public string Phone { get; set; }
}

 dotnet ef dbcontext scaffold "Server=localhost,1433\\Catalog=tutorial_database;Database=<YOUR_DATABASE_NAME>;User=SA;Password=<StrongPasswordYouSet>;" Microsoft.EntityFrameworkCore.SqlServer

"ConnectionStrings": {
  "TestingDatabase": "Server=localhost:1433\\Database=<YourDatabaseName>;User=SA;Password=<StrongPasswordYouSet>;"
}

 dotnet ef migrations add <NAME_OF_MIGRATION>

ef database update

 SELECT TABLE_NAME FROM dockerdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

• هرگاه در اجرای Count ،هدفتان بدست آوردن تعداد ستون خاصی است، از (Count(column استفاده نمایید.

• روش اول 

(a+b)+ABS(a-b)

0.5(a+b)+ABS(a-b)

DECLARE @Value1 DECIMAL(5,2) = 80.22
DECLARE @Value2 DECIMAL(5,2) =90.34
SELECT (0.5 * ((@Value1 + @Value2) + ABS(@Value1 - @Value2))) AS MaxColumn  

• روش دوم

در این روش اگر مقدار یکی از اعداد Null باشد،ماکزیمم بین دو عدد، عدد غیر Null می‌باشد.

5- مشاهده مشخصات کلیه دیتابیس‌های موجود در SQL Server با استفاده از Sys.Databases .

Select * From sys.databases

خروجی بصورت زیر خواهد بود:

6- بوسیله دستور OUTPUT می‌توان خروجی Query‌های Delete،Update و Insert را مشاهده نمود:

مثال اول برای Query Delete :

در شکل، تک رکورد حذف شده را مشاهده می‌نمایید.

مثال دوم برای Query Update بصورت زیر میباشد:

در شکل، مقدار A، مقدار جدیدی است که بروز رسانی شده است و مقدار B مقداری است که مربوط به قبل از بروز رسانی می‌باشد.

مثال سوم برای Query Insert بصورت زیر میباشد:

موفق باشید.

Split Buttons: Definition 

USE [WideWorldImporters];
GO

SET STATISTICS IO ON;
GO


/*
Query with a hash join
*/
SELECT
    [ol].[OrderID],
    [ol].[OrderLineID],
    [ol].[StockItemID],
    [ol].[PickedQuantity],
    [si].[StockItemName],
    [si].[UnitPrice]
FROM [Warehouse].[StockItems] [si]
    JOIN [Sales].[OrderLines] [ol]
    ON [si].[StockItemID] = [ol].[StockItemID];
GO

ALTER TABLE [Warehouse].[StockItems] ADD  CONSTRAINT [PK_Warehouse_StockItems] PRIMARY KEY CLUSTERED
(
   [StockItemID] ASC
)

CREATE NONCLUSTERED COLUMNSTORE INDEX [NCCX_Sales_OrderLines] ON [Sales].[OrderLines]
(
[OrderID],
[StockItemID],
[Description],
[Quantity],
[UnitPrice],
[PickedQuantity]
)

CREATE NONCLUSTERED INDEX [IX_OrderLines_StockItemID]
ON [Sales].[OrderLines](
[StockItemID] ASC,
[PickedQuantity] ASC,
[OrderID])
ON [PRIMARY];
GO

SELECT
    [ol].[OrderID],
    [ol].[OrderLineID],
    [ol].[StockItemID],
    [ol].[PickedQuantity],
    [si].[StockItemName],
    [si].[UnitPrice]
FROM [Sales].[OrderLines] [ol]
    JOIN [Warehouse].[StockItems] [si]
    ON [ol].[StockItemID] = [si].[StockItemID]
OPTION
(RECOMPILE);
GO

CREATE NONCLUSTERED INDEX [IX_OrderLines_StockItemID_Compressed]
ON [Sales].[OrderLines](
[StockItemID] ASC,
[PickedQuantity] ASC,
[OrderID])
WITH (DATA_COMPRESSION = PAGE)
ON [PRIMARY];
GO

SELECT
    [ol].[OrderID],
    [ol].[OrderLineID],
    [ol].[StockItemID],
    [ol].[PickedQuantity],
    [si].[StockItemName],
    [si].[UnitPrice]
FROM [Sales].[OrderLines] [ol]
    JOIN [Warehouse].[StockItems] [si]
    ON [ol].[StockItemID] = [si].[StockItemID]
OPTION
(RECOMPILE);
GO

SELECT
    [ol].[OrderID],
    [ol].[OrderLineID],
    [ol].[StockItemID],
    [ol].[PickedQuantity],
    [si].[StockItemName],
    [si].[UnitPrice]
FROM [Sales].[OrderLines] [ol] WITH (INDEX (IX_Sales_OrderLines_Perf_20160301_02))
    JOIN [Warehouse].[StockItems] [si]
    ON [ol].[StockItemID] = [si].[StockItemID];
GO

SELECT
    [ol].[OrderID],
    [ol].[OrderLineID],
    [ol].[StockItemID],
    [ol].[PickedQuantity],
    [si].[StockItemName],
    [si].[UnitPrice]
FROM [Sales].[OrderLines] [ol] WITH (INDEX (IX_OrderLines_StockItemID))
    JOIN [Warehouse].[StockItems] [si]
    ON [ol].[StockItemID] = [si].[StockItemID];
GO

SELECT
    [ol].[OrderID],
    [ol].[OrderLineID],
    [ol].[StockItemID],
    [ol].[PickedQuantity],
    [si].[StockItemName],
    [si].[UnitPrice]
FROM [Sales].[OrderLines] [ol] WITH (INDEX (IX_OrderLines_StockItemID_Compressed))
    JOIN [Warehouse].[StockItems] [si]
    ON [ol].[StockItemID] = [si].[StockItemID];
GO

SELECT COUNT(*)
FROM [Sales].[Orders];

SELECT COUNT_BIG (*)
FROM [Sales].[Orders];

CREATE FUNCTION dbo.CountProductsSold (
@SalesPersonID INT
) RETURNS INT

AS

BEGIN
    DECLARE @SoldCount INT;

    SELECT @SoldCount = COUNT(DISTINCT [ol].[StockItemID])
    FROM [Sales].[Orders] [o]
        JOIN [Sales].[OrderLines] [ol]
        ON [o].[OrderID] = [ol].[OrderID]
    WHERE [o].[SalespersonPersonID] = @SalesPersonID

    RETURN (@SoldCount);

END

SELECT
    [FullName] AS [SalesPerson],
    [dbo].[CountProductsSold]([PersonID]) AS [NumberOfProductsSold]
FROM [Application].[People]
WHERE [IsSalesperson] = 1;

SELECT
    [fs].[last_execution_time],
    [fs].[execution_count],
    [fs].[total_logical_reads]/[fs].[execution_count] [AvgLogicalReads],
    [fs].[max_logical_reads],
    [t].[text],
    [p].[query_plan]
FROM sys.dm_exec_function_stats [fs]
CROSS APPLY sys.dm_exec_sql_text([fs].sql_handle) [t]
CROSS APPLY sys.dm_exec_query_plan([fs].[plan_handle]) [p];