امکان خاموش کردن parameter sniffing در سطح یک دیتابیس به SQL Server 2016 اضافه شدهاست
اطلاعات بیشتر
اطلاعات بیشتر
Following is a chart that shows licensing cost comparison between standard and Enterprise Edition of SQL Server 2012, 2016 and 2017.
Version | Edition | License Cost | 2 Quad core Processors | 4 Quad core Processors |
---|---|---|---|---|
Per Core | 8 Cores | 16 Cores | ||
SQL Server 2012 | Standard | $1,793 | $14,344 | $28,688 |
SQL Server 2012 | Enterprise | $6,874 | $54,992 | $109,984 |
SQL Server 2016\ 2017 | Standard | $1,858 | $14,864 | $29,728 |
SQL Server 2016 \2017 | Enterprise | $7,128 | $57,024 | $114,048 |
using System; using System.Collections.Generic; using System.IO; using System.Linq; using System.Text; using Microsoft.SqlServer.TransactSql.ScriptDom; namespace DbCop { // Microsoft® SQL Server® 2012 Transact-SQL ScriptDom // SQL Server 2012 managed parser, Supports SQL Server 2005+ // SQLDom.msi (redist x86/x64) // http://www.microsoft.com/en-us/download/details.aspx?id=29065 // X86: http://go.microsoft.com/fwlink/?LinkID=239634&clcid=0x409 // X64: http://go.microsoft.com/fwlink/?LinkID=239635&clcid=0x409 // Program Files\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.TransactSql.ScriptDom.dll class Program { static void Main() { const string tSql = @" -- select * in PROCEDURE CREATE PROCEDURE dbo.SelectStarTest AS SELECT * FROM dbo.tbl1 go -- select * in PROCEDURE with TableVar Create PRocedure SelectAll AS Declare @X table(Id integer) Select * from @x go -- select * in PROCEDURE with ctex CREATE PROCEDURE dbo.SelectAllCte AS WITH ctex AS ( SELECT * FROM sys.objects ) SELECT * FROM ctex go -- normal select * select * from tbl1; select * from dbo.tbl2; "; IList<ParseError> errors; TSqlScript sqlFragment; using (var reader = new StringReader(tSql)) { var parser = new TSql110Parser(initialQuotedIdentifiers: true); sqlFragment = (TSqlScript)parser.Parse(reader, out errors); } if (errors != null && errors.Any()) { var sb = new StringBuilder(); foreach (var error in errors) sb.AppendLine(error.Message); throw new InvalidOperationException(sb.ToString()); } var i = 0; foreach (var batch in sqlFragment.Batches) { Console.WriteLine("Batch: {0}, Statement(s): {1}", ++i, batch.Statements.Count); foreach (var statement in batch.Statements) { processStatement(statement); } Console.WriteLine(); } Console.WriteLine("\nPress a key..."); Console.Read(); } private static void processStatement(TSqlStatement statement) { var createProcedureStatement = statement as CreateProcedureStatement; if (createProcedureStatement != null) { var statementList = createProcedureStatement.StatementList; foreach (var procedureStatement in statementList.Statements) { processStatement(procedureStatement); } } var selectStatement = statement as SelectStatement; if (selectStatement != null) { var query = selectStatement.QueryExpression; var selectElements = ((QuerySpecification)query).SelectElements; foreach (var selectElement in selectElements) { var expression = selectElement as SelectStarExpression; if (expression == null) continue; Console.WriteLine( "`Select *` detected @StartOffset:{0}, Line:{1}, T-SQL: {2}", expression.StartOffset, expression.StartLine, statementToString(selectStatement)); } } } private static string statementToString(TSqlFragment selectStatement) { var text = new StringBuilder(); for (var i = selectStatement.FirstTokenIndex; i <= selectStatement.LastTokenIndex; i++) { text.Append(selectStatement.ScriptTokenStream[i].Text); } return text.ToString(); } } }
Batch: 1, Statement(s): 1 `Select *` detected @StartOffset:140, Line:5, T-SQL: SELECT * FROM dbo.tbl1 Batch: 2, Statement(s): 1 `Select *` detected @StartOffset:368, Line:12, T-SQL: Select * from @x Batch: 3, Statement(s): 1 `Select *` detected @StartOffset:659, Line:22, T-SQL: WITH ctex AS ( SELECT * FROM sys.objects ) SELECT * FROM ctex Batch: 4, Statement(s): 2 `Select *` detected @StartOffset:753, Line:26, T-SQL: select * from tbl1; `Select *` detected @StartOffset:791, Line:27, T-SQL: select * from dbo.tbl2;
دستورات زیر در صورتی که در خارج از تریگر فراخوانی بشن عملکردشون درسته ، اما در تریگر خطا میدن :
-- 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;');