یک نکتهی تکمیلی
SQL Server 2016 مفهومی به نام Temporal Tables را پشتیبانی میکند.
SQL Server 2016 مفهومی به نام Temporal Tables را پشتیبانی میکند.
/* A / \ B C | /|\ D E F G | H */
declare @t table ( id char(1) primary key not null, pid char(1) null --references @t ); insert @t values ('A', null), ('B','A'),('C','A'), ('D','B'), ('H','D'),('E','C'),('F','C'),('G','C'); with cte as ( select id from @t where pid = 'A' union all select t.id from cte c join @t t on t.pid = c.id ) select * from cte
var test1 = context.Blogs .Where(blog => String.Compare(blog.Url, "A", StringComparison.Ordinal) > 0) .ToList(); // SELECT [blog].[BlogId], [blog].[Url] // FROM [Blogs] AS [blog]
var test2 = context.Blogs .Where(blog => String.Compare(blog.Url, "B") > 0) .ToList(); // SELECT [blog].[BlogId], [blog].[Url] // FROM [Blogs] AS [blog] // WHERE [blog].[Url] > N'B'
var test3 = context.Blogs .Where(blog => blog.Url.Equals("C", StringComparison.OrdinalIgnoreCase)) .ToList(); // SELECT [blog].[BlogId], [blog].[Url] // FROM [Blogs] AS [blog]
var test3_1 = context.Blogs .Where(blog => blog.Url.Equals("C_1")) .ToList(); // SELECT [blog].[BlogId], [blog].[Url] // FROM [Blogs] AS [blog] // WHERE [blog].[Url] = N'C_1'
var test4 = context.Blogs .Where(blog => blog.Url.StartsWith("D")) .ToList(); // SELECT [blog].[BlogId], [blog].[Url] // FROM [Blogs] AS [blog] // WHERE [blog].[Url] LIKE N'D' + N'%' AND (LEFT([blog].[Url], LEN(N'D')) = N'D')
var test5 = context.Blogs .Where(blog => EF.Functions.Like(blog.Url, "S_i%")) .ToList(); // SELECT [blog].[BlogId], [blog].[Url] // FROM [Blogs] AS [blog] // WHERE [blog].[Url] LIKE N'S_i%'
var test6 = context.Blogs .Where(blog => blog.Url.ToUpper() == "E") .ToList(); // SELECT [blog].[BlogId], [blog].[Url] // FROM [Blogs] AS [blog] // WHERE UPPER([blog].[Url]) = N'E'
var test7 = context.Blogs .Where(blog => blog.Url.ToUpperInvariant() == "F") .ToList(); // SELECT [blog].[BlogId], [blog].[Url] // FROM [Blogs] AS [blog]
همانطور که اطلاع دارید نسخه آزمایشی SQL Server 2016 قرار از تابستان امسال (2015) در دسترس باشد. قابلیتهای جدیدی به این محصول اضافه شده است. تعدادی از آنها عبارتند از
1- امکان استفاده از Clustered Column Store Index در جداول Memory Optmized
2- َAlways Encrypted
3- پشتیبانی از JSON
4- پشتیبانی از زبان R در SQL Server
و ...
دستورات زیر در صورتی که در خارج از تریگر فراخوانی بشن عملکردشون درسته ، اما در تریگر خطا میدن :
-- Insert -- INSERT INTO OPENQUERY(MyLinkServer, 'SELECT * FROM unit') VALUES ( 1, 5, 'fa', '0', GETDATE(), '1', GETDATE(), '1' ); ---------------------------------------------------------------------------------------- -- Update -- --1 UPDATE OPENQUERY(MyLinkServer , 'SELECT * FROM unit WHERE id=4') SET [is_deleted] = '0'; --2 EXEC('UPDATE unit set is_deleted=''1'' where id=4;') AT MyLinkServer ; -- 3 UPDATE OPENQUERY(MyLinkServer , 'SELECT * FROM unit') SET [is_deleted] = '0' WHERE id = 4; ---------------------------------------------------------------------------------------- -- Delete -- DELETE OPENQUERY(MyLinkServer, 'select * from unit where id=4;');
use master; RESTORE DATABASE WideWorldImporters FROM disk='D:\path\WideWorldImporters-Full.bak' WITH MOVE 'WWI_Primary' TO 'D:\SQL_Data\WideWorldImporters.mdf', MOVE 'WWI_Log' TO 'D:\SQL_Data\WideWorldImporters_log.ldf', MOVE 'WWI_UserData' TO 'D:\SQL_Data\WideWorldImporters_UserData.ndf', MOVE 'WWI_InMemory_Data_1' TO 'D:\SQL_Data\WideWorldImporters_InMemory_Data_1'
USE [WideWorldImporters]; GO SELECT [s].[StateProvinceName], [s].[SalesTerritory], [s].[LatestRecordedPopulation], [s].[StateProvinceCode] FROM [Application].[Countries] [c] JOIN [Application].[StateProvinces] [s] ON [s].[CountryID] = [c].[CountryID] WHERE [c].[CountryName] = 'United States'; GO
USE [WideWorldImporters]; GO SET STATISTICS IO ON; GO SET STATISTICS TIME ON; GO SELECT [s].[StateProvinceName], [s].[SalesTerritory], [s].[LatestRecordedPopulation], [s].[StateProvinceCode] FROM [Application].[Countries] [c] JOIN [Application].[StateProvinces] [s] ON [s].[CountryID] = [c].[CountryID] WHERE [c].[CountryName] = 'United States'; GO
SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 504 ms. (53 rows affected) Table 'Countries'. Scan count 0, logical reads 118, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'StateProvinces'. Scan count 1, logical reads 43, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 10 ms.
USE [WideWorldImporters]; GO SET STATISTICS XML ON; GO SELECT [s].[StateProvinceName], [s].[SalesTerritory], [s].[LatestRecordedPopulation], [s].[StateProvinceCode] FROM [Application].[Countries] [c] JOIN [Application].[StateProvinces] [s] ON [s].[CountryID] = [c].[CountryID] WHERE [c].[CountryName] = 'United States'; GO SET STATISTICS XML OFF; GO
SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 7 ms. (53 rows affected) Table 'Countries'. Scan count 0, logical reads 118, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'StateProvinces'. Scan count 1, logical reads 43, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. (1 row affected) SQL Server Execution Times: CPU time = 15 ms, elapsed time = 179 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.
SET STATISTICS IO ON; GO SET STATISTICS TIME ON; GO SELECT [s].[StateProvinceName], [s].[SalesTerritory], [s].[LatestRecordedPopulation], [s].[StateProvinceCode] FROM [Application].[Countries] [c] JOIN [Application].[StateProvinces] [s] ON [s].[CountryID] = [c].[CountryID] WHERE [c].[CountryName] = 'United States'; GO
USE [WideWorldImporters]; GO SELECT [s].[StateProvinceName], [s].[SalesTerritory], [s].[LatestRecordedPopulation], [s].[StateProvinceCode] FROM [Application].[Countries] [c] JOIN [Application].[StateProvinces] [s] ON [s].[CountryID] = [c].[CountryID] WHERE [c].[CountryName] = 'United States'; GO
USE [WideWorldImporters]; GO SELECT [s].[StateProvinceName], [s].[SalesTerritory], [s].[LatestRecordedPopulation], [s].[StateProvinceCode] FROM [Application].[Countries] [c] JOIN [Application].[StateProvinces] [s] ON [s].[CountryID] = [c].[CountryID] WHERE [c].[CountryName] = 'United States'; GO
SELECT [s].[StateProvinceName], [s].[SalesTerritory], [s].[LatestRecordedPopulation], [s].[StateProvinceCode] FROM [Application].[Countries] [c] JOIN [Application].[StateProvinces] [s] ON [s].[CountryID] = [c].[CountryID] WHERE [s].[StateProvinceName] LIKE 'O%'; GO