????? ??????? ????? ???? ????????? Ad-Hoc ?? SQL Server

?????? ????????? ?????? ?????????
???? ???????: 243588 - ??? ???????? ???? ????? ????? ??? ???????.
????? ???? | ?? ????

?? ??? ??????

??????

???? ??? ??????? ????? ??????? ????? ???? ???? ?????? ?? ????????? ????? ????????? ?? Microsoft SQL Server. ??? ?? ????? ???? ??????? ???? ? ???? ??????? ??????? ?? ????? ?????Microsoft ??? ????????:
224587????? ??????? ????? ???? ??????? ?? ???? SQL

????? ??? ??????? ??????? 224587 ???????? ?????? ???? ??????? ??? ?? ?????? ??? ????? ???? Windows NT ???? ????? ??????? SQL ???? ??? ???????? ????? ???????? ??????? ????????? ?????.

?????? ??????? ?????? ??????

???? ????? ?????? ?????? ??????? ???????:
  • ????????? ????? ????? ???? ????? ??? ??? ????? ???? ???? ??? ???? ?????? ??? ??? ????? ??? ???? ?? ?????????? ?????????? ???????????.
  • ???? ???? ?? 100 ?????? ??????? ??????? CPU.
  • ?? ???? ???????? ??? ????? ????? ??? ??????.

    ????? ????? ????? ?? ??? ?? ???? ?????? ?? ?????? BLK ?? ????? ????? ?????? ?????? sp_who. ??? ??? ??? ?? ??? ?????? BLK ??? ???? ?? ????? ?????? ?????? (SPID).
  • ?? ??? ???????? stressed ????? ?????? ?? ?? ????? ????? ?????? ??????? ???????:
    701 ???: ???????: ???? 17 ?: 1
    ???? ????? ????? ?????? ??? ?????????.
    - ?? -
    msg 8645 ? ??????? 17 ???? 1 "??" ????? "??" ?? 1
    ??? ?????? ???? ????? ?????? ????? ??????? ?????? ?????????. ????? ????? ?????????.

??????? ?? compilations ?????????

???? ?? ????????? ?? ????? ???? ????? ?? SQL Server 7.0 ???? ??? ?????? ????????? ?? ????? ?????? ?? ??????? ????? ?????? ?????? ????????? ?????? ??????? SQL Server. ????? ???? ?? SQL Server 7.0 ???? ????? ?? ??????? CPU ?? ??????? ???? ??? ????? ????? ??????? SQL Server IO ????. ?????? ??? ????????? ??? ??????? ?????:
  • ???? ??????? ??????
  • ???? ??????? ??????? ?????????
????????? ??????? ?? SQL Server relied ???? ???? ??? ????? ???? ????????? ?????? ????. ???? ???? ????????? ??????? ??????? ??? ???????/???????. ????? ?? SQL Server 7.0 ?? ????? ?????? ??????? ??????. ?????? ??????? ?????? ?????? ?? ??????? ?? ??????? ?????? ?? ?????? ???? ?????????. ????? ???????? ??? CPU ???? ??????? ??????? ???? ??? ??????? ??? ???????? ?????. ????? ?? ????????? ??? ?????? ??????? ?????? ???? ???????? "??? ?????? ?????" ? "??? ?????? ???" ?? SQL Server 7.0 ????????.

????? ???? ??????? ??????? ????????? ???? ?????? ????????? ???? ?? ????????? ???????? ?? ????????? ??????? ?? SQL Server, ??? ?? ??? ?????? ????? ??????? ?????? ????? ????? ?????? ????????? ????????? ?????? ???????. ???? ??? ??????? ?????? ?????? ????????? ?????? ????? ?????? ????? ???????? ?????? ?????????. ??? ???? ????? ??????? ?????? ?????? ??? ?????? ????? ???????? ??? ????????. ?? ???? ??????? CPU ????? ??? ????????? ??????? ??????? ???? ???? ?? ????????? ??????? ?? SQL Server.

?????? ??? ???? ??????????? ??? ????? ????? ??? ?? ??? ??????? ??????? ?????? ?? ??? ?????. ????? ????? ????? ????????? ???? ?? ????????? ??????? ?? SQL Server. ??? ???? ???? ??????? ???? ?? ????????? ??? OLTP ????? ????, ????? ? ???? ????? ??? ???? ????? ?????? ????. ?????? ??? ??? ??????????? ?? ???? ??? ????? ????? ??? ??????? ???????? ????? ?? ???? ?? ????? ?????????. ????? ????? ?? ????? ????????? ???? ?????? ?? ?? ????????? ??????? ?? SQL Server. ??? ???????? ???? ??????? ?????, ?? ??? ????? ??? ????????? ????????? ????? ??? ??? ??? ?????? ???? ????. ??? ???? ?? ????? ?? ?????? ???????? ??????? CPU ???? ?? ????????? ??????? ?? SQL Server ??? ??? ??? ????? ??? ???? ?? ??????????? ??????? ???? ?????? ?????? ??? ???? ?? ??????????.

????? ????????? ???????

?????? SQL Server 7.0 ??? ?????? ????? ??? ??????? ?????? ????? ??????????? ? parameterization ????????. ??? ???? ??????????? ???? SQL Server 7.0 parameterizes ???????? ?????. ?????? ????? ??????? ?????? ?? ?? ??? ??????? ??? ????? ?????? ???????? ???? ????? ????????? ???? ???? ??????:
  • ?????? ??????? OLE DB ? ODBC APIs ???? ?????? ??? ????? ?? ????? ??????? ????? ???? ?????????? ?????? ?????????. ???? ?? ???? ??? ?????? ?? ?? ????? ???? ?? ??? ????????? ?????? ???????? ???? ???? ??????? ??????? ????? ????????? ??? ???????? ????????? ??????? ??? ?????. ???? ????? ????????? ??? ????????? ???? ?? ?????? ?? ??? ??????????? ???? ????? ??? ?????? ??????? ??? ?? ????? ????? ??? ????????? ??? ??? ?? ????? ??? ?????? ??????. ????? ?? ?????????? ???? ??????? "????? ??????" ?? SQL Server 7.0 ????? ??? ??????.
  • sp_executesql ??? ??????? ??????? sp_executesql ??????? ?????? ???? OLE DB ?? ?????? ????? ODBC ??? ??????? ??????? ?????? ?? ??? ???????. ??? ???? ???? ???? ????? ??????? ?????? ?????? ??????? ?? ?? ????? ???? ??? parameterize ????????? ??????? ???? ????. ???? ?? ???? ??? ?????? ?? ??????? ?? ??????? ??????? ??? ??? ??????? ????? EXECUTE ????? ?????? SQL ?????????. ???? sp_executesql ?? ???? ????? EXECUTE parameterization. ???? ???? ???? ????? ??????? ??? ?????????. ????? ?? ?????????? ???? "sp_executesql (T-SQL)" ? "??????? sp_executesql" ???????? ?? SQL Server 7.0 ????????.
  • ????????? ??????? ????????? ??????? ?? ???? ???? ?????? ?? ??????? ??? ?? ??? ?????? ??? parameterize ????????? ?????? ????????? ??? ???????. ????? ?? ?????????? ???? ???????? "??????? ?????" ? "????? ??????? ?????" ?? SQL Server 7.0 ????????.

??? ?????? "?????? ??????"

??????? ??? "?????? ??????" ?????? ????? ?????? ???? ????? ?? ??????. ??? "?????? ??????" ???? ??? ???? ????? ????? ????????? ?? ??????? ??????? ??? ??? ???????? SQL ???? ????? ???????. ?????? ?????? "?????? ??????" ?? ????? ????? ?? ???? ???? ????? ???? ????? ???? ??????. ????? ?????? ???? ?? ????? ????? ??? ?? ????? ?? ?? ???????? ??????? ?? ?????? ??? ??? ?????? ?? ?????? ???? ??????:
  • ????: ??????
    ??????: ???????
    ????: SQL Server
  • ????: ???????
    ??????: % ???? ???????
    ????: ????? ?? ???? ???????
  • ??????: ????? ?????? ??????: ???? SQL
    ??????: ??????? ??????? ???
  • ??????: ????? ?????? ??????: ???? SQL
    ??????: ???? ??? ???????
  • ??????: ????? ???????: ???? SQL
    ??????? ??????: ??? ???????
  • ??????: ???????? ??????: SQL SQL
    SQL ??????: ???????/???????
??? ??? ??????? CPU ??????? SQL ?? ??????? ? ?????? ??? ?????? ??? ??????? ??????? ??? ????? ?????? ???? ??? ??????? ???????? ?????? ? ???? ??? ??? ?? CPU ??????. ??????? ??? ????? parameterize ?????? ????????? ??? ???? ??????? ????? ??? ????????? ???? ???? ?? ???? ??? "?????? ?????? ???? ????? ??????? SQL ?????? ??? ?????" ?? ??? ???????. ??? "?????? ???" ????????? ??????? SQL ?? ??????? ?????? ???? ????? ?????? ???? ??? ??????? ? ??????? ??? ????? ? SQL Server ????? ???????. ??????? ??? ????? ?? ????????? ??? ??? ??????? ?????? ??????? ? ???? ?? ????? ???? ?????? ??????? "????? ???? ???? ????? ??????? SQL ??? ?????" ??? ?? ??? ???????. ????? ?? ????????? ??? ??? ???????? ??????? ??? ?????? ????? ?? SQL Server 7.0 ????????.

??? ?????? SQL ???? ????? ???????

????? ??? ?? ????? ?????? ? ??? ???? ?????? ???? ???????? SQL ???? ????? ???????. ??? ?? ??????? ?????? ???? ???????? ???? ????? ??????; ???? ??????. ?????? ???? ????? ??????? SQL ???? ???? ??? ???????? ????????. ?? ????? ??????? ????? (?? ??????? ???? ???? ??? ?????) ? ???? ???? ????? ??????? SQL ?????? ???????? ???? ??? ????? ?????? ????? ????? ???????? ?? ??????? ??????? ?? ????? ??? ????? ???????? ? ????? ????? ???????. ????? ????? ???? ????? ?? ???? ???? ?????? ??? ??? ????? ??? (?? ??????? ????? ? ???? ??? ???). ????? ????? ??? ???????? ???? ????? ??????? SQL ??? ???? ???? SQL (?? ??????? ???? ??? ??? ??? ???? ?? ?? ???? ??? ???? ????) ? ?? ?? ?????? ????????? SQL ????? ????.

?????? ???? ?? ????? ??? ???? ?????? ???. ?? ???? ????? ??????? ??????? ??? ???? ??? ?????? ????? ???????? ???? ?? ??????? ??? ??? ????? ??????. ??? ???? ??? ??? ??? ?? ?????? ????? (?? ??????? ???? ???? ??? ??? ????) ? ?? ????? ???? (?? ??????? ???? ???? ??? ???) ??? ????????. ??? ????? ?? ??? ???? ?????? ??????? ?? ???? ???? ?????? ????????; ???????? ??? ?????? ?? ??? ???. ???? ????? ? ????? ????? ?????? ???????? ???????? ?? ????? ?????? ?????.

??? ????? ??????? ??? ????? ???? ??????? ????. ????? ????? ???? ??????? ??????? ??????? ???? ??? ???????? ??? ??? ????. ??? ????? ??? ??????? ???? ????? ????? ????????? ?? ???? ??????? ??? ??????? ???????:
  • ????? ????? ?????????
  • ????? ??? ???? ????? ?????????
??? ???? ??????? ????? ??? ????? ??????? ???? ?? 1.5 ????? ??? ????? 1.2 ?? ?? ???? ?????? ??? ?? ??? ????? ????????? ???? ????? ???? ?????. ??? ???? ?? ???? ????? ????????? ???? ????? ???? ?????? ??? ???? ?? ?????????? ?????????? "? ???? ????? ?????? ????? ????. ???? ?????? ????? ??????? ???? ?? 6 ????? ??? 3 ???? ?? ?? ?????? ????? ??????? ?? ?????? ????? ??? ?????? ?? ??? ????????. ??????? ???????? ?? ???? ????? ??????? SQL ? ?????? ??????? ??????? ???????? ?????? ??????? ??????? ???? ?? ????? ??? ????? ??? ???????. ??????? ??? ????????? ?????? ?????? ????? ??? ?? ???? ????? ???? ??????????? ?????????? ?????? ??? ??? ??? ????? ??? ?? ????? ?????? ???? ???.

??? ??? ???? ???? ????? ??????? SQL ??? ??? ?? ???? ????? ??? ?????? ?? ???? ????? ??????? SQL ?? ?????? ?????????. ?????? ?????? ???? ????? ??????? SQL ???? ??????? ???????:
  • ????? ???? ???? ????? ??????? SQL ??? ?????:
    1. ?? ??????? ???? ???? ??? ?????.
    2. ???? ??? ????? ??????? ????? ???????? ??? ?? ??? ??????? ???? ??? ????? ???? ?????. ???? ??? ????? ??? ????? ???? ??????? ??????.
    3. ???? ??? ????? ??????? ??????? ?? ?? ?????? ???? ??????? ???????? SQL:StmtCompleted TSQL ? RPC TSQL: ?????. ???? ??? ??????? ??? ??????????? ???? ?? ??? ???????.
    4. ???? ??? ?????.
    ??????? ??? ????? ???? ?????? ???? SQL ?????? ????? ?????????? ???? ???? ????? ??????? slowest. ?????? ?????? ??? ???? ??????? ?????? ???? ????? ?????. ????? ??????? ??? ??? ????? ??? ?????? ??? ????? ????? ???? ???? ??????? ???? ? ?????? ??? ????? ??????. ???? ?? ??????????? ?? ??? ???? ????? ???? ???? ???. ??? ?? ?????? ???????? ????? ???????. ??? ?????? ????? ???? ?????? ??? ????????? ???? ??? ????? ????? ????? ??????? ??????? ??? ?????? ????? ??????.

    ?????? ??? ??????? ??????????? ?? ???? ?? ????? ????? ???? ?????. ??? ??? ??? ??? ????????? ??? ????? ???? ??????? ????? ????????? JOIN LOOP ??? ??? ??????? ???? ?????????. ?? ???? ??? ????? ????????? ???????? ??? ???? ??? ?? ?? ????? ?? ???? ?????? ??? ??? ????? ?? ??? ??????? ? ?? ??? ???? ???? ???? ??? ??? ????????? ????? ??????? ?????? ???????? CPU. ?? ???? ????? ????? ??? ?????? ?????? (??? CPU ????????) ? ????? ????? ???? ?????? ???. ?????? ??? ???? ?? ????????? ??? JOIN LOOP ????????? ?????, ???? ??????? "SELECT (T-SQL)" ?? SQL Server 7.0 ????????.
  • ????? ???? ???? ????? ??????? SQL ??? ??? ?????:
    1. ?? ??????? ???? ???? ??? ?????.
    2. ???? ??? ????? ??????? ????? ???????? ??? ?? ??? ??????? "?????????" ? ???? ??? ????? ???? ??? ????? "?" ?? ?? ??? ????? ?? ??????. ???? ??? ????? ??? ????? ???? ????? ???? ??? ????? ?????????.
    3. ???? ??? ????? ??????? ??????? ?? ???? ?? ?? ??? ????? ???? ???????.
    4. ???? ??? ?????.

????? ???????

??????? ??? ???? ?? ?? ????? ??????? ??? ????????? ???? ???? ?????? SQL Server ? ???? ??????? ???? ???? ??????? ??? ?????? ??? ?????. ????? ?? ??? ?????? ?? ??????? ???????:
  • MISC: ????? SQL ? Exec ????? SQL; ????????: Cursorprepare ????? ????? SQL ???? ??? ??? ?? ??????? ????? SQL ?????????? ?? ?????? ????? ??????? (???? ?? ???? ??????) ???????? SQLPrepare/SQLExecute (?? ODBC) ?? ICommandText::Prepare/ICommandText::Execute (?? OLE DB) ?? ?????? ??? ?????? ?????????: ?????? ???? ??????? ???, ??? ?????? ?????? = 1. ??? Cursorprepare ???? ??? ?? ???? ?? ???? ?????? ?? ?? ??????? ??? SQL ????? ??????? ??????? SQLPrepare/SQLExecute (?? ODBC) ?? ICommandText::Prepare/ICommandText::Execute (?? OLE DB) ?? ???? ?????? ??? ?????? ?????? ??? ???? ??? ???????. ??????? ??? ??? SQL ????? Exec ?? ????? ?? ?? ??????? ??????? ?? ?????? ?????? ????????. ??? ??? ??? ?????? ??????? ??? ??????? ??????? ????? ?? ?????? ???? ?????/??????? ??? ???? ??????? ???????. ??? ??? ????? ????? ??? ???? ????? ??? ??? ?????? ???? ?????/??????? ???? ????.

    ????? ?????? ????? ?????? ????? SQL ??? ????? ?? ????? ??? ???? ???? ???? ?? ???? ?????? ????? ???? ???? ?? ??? ??? ????? ???????. ?? ??? ???? ???? ?????? ????? ???? ??? ????? ??????? ??????? ?????????. ??? ??? ???? ??? ????? ??????? ??? ????? ???? Microsoft ?? ?? ??????? ????. ??? ???? ??????? ???????? ????? SQL ????? ???? ?????? roundtrips: ??? ?????? ??????? ??? ????? ??????? ? ??? unprepare ???????. ??????? ?????? ?? ???? ?????? ????? ??? ????? trips ??????: ???? ?????? ?????? ?? ???? ????? ?? ???? ?? ???? ?? ???? ????? ??? ? ??????? ???? unprepare ????. ????? ????????? ????? ?????? ?? ???? ???.

    ??????? ????? ??????? ??????? ????? ?? ?????? ???? ?????/??????? ???? ??? ???? ??? ??????? ??? (????? ????????) ????. ??? ?? ???? ??? ??????? SQL ????? Exec ???? ?? ?????? ????? SQL ???????? CursorPrepare (????? ??? ????? ??? ??? ???? ???? ????? ???). ???? ??? ??? ?? ???? ?????? ?????? ??? ??? ????????? ???? ????? ????? ????? ?? ????? ????? ????? ???????. ?? ???? ??????? ?????? ??? ??? ??????? SQL ????? Exec ??? ??????? ????? SQL ? CursorPrepare ?? ???? ??? ??? ?? ??????? ?? ??????? ?????? ???? ?????/???????. ???? ????? ????? ??? ????? ?? ????? ???????? ??? ???????. ????? ????? ????? ??????? ????? ?? ????? ?????? ??? ????? ?????? ????????? ??? ????????.

    ??? ????? ??????? ???? ??? ???????? ???? ?????/??????? ???? ????. ?????? ??? ???? ??? ????? ???? ????? ????? ?? HSTMT ?????? ?? ODBC "??" ???? ICommandText ?? OLE DB. ?????? ??????? ???? ?? ?????? ??? HSTMT ????? ????? SQL ????? ???? ?????? ??? ?? ????? HSTMT ?? ????? ?????? ??? ??????. ?? ???? ?????? ????? ?? ???? ??? ????? ?? ?????? ?????/???????. ?? ???????? ?? ??? ????? ???? ????? ??? ????? roundtrips ???? ???????. ??? ?? ????? ??????? ??? ????? ????? HSTMT ?? ???? ?????? ???? ????? ?????? ?????? ????? ?????? ?????????. ?????? ??????? ?? ?????? ?? ??? ????????; ??????? ?????? ?? ????? ?????? ??? ??????? ??? ?????????. ??? ??? ??????? ?? ???? ?????? ????? ????? ??????? ?????? ??????? ????? ?? ????? ?????/???????.
  • ???????? ?????? ??????? ???? ????????? ??????? ?????? ??????? ?????? ??????? ????? SQL ???????? ???? ??? ???? ?? ????? ?????? ???? ???????. ?? ????? ?????? ???? ??? ????? ??????? ??? ????? ???????? ?????? ? SQL Server ?????? ?????? ??? ??? ?????? ?????????. ????????? ??????? ??? ????????? ???????? ??? ???????? ??? ?? ?????? SQL Server ?? ??? ?????? ???? ??????? ????; ???? SQL Server ?? ????? ??????? ??? ????????? ???????? ?? ???? ??????? ?????? ???????.

    ??? ??? ??????? ?????? ??????? ?????? ?? ??????? SQLExecDirect (?? ODBC) ?? ICommandText::Execute (?? OLE DB) ? ?????? ??????? ?? ???? ???????? ??? ????? SQL ?? ????? ?????? sp_executesql. ??? ??? ??????? ????? ???? ??????? ???? ?????. ????? ????? SQL Server ??????? sp_executesql ? ???? ???????? ??????? ?? ????? ??????? ?????? ??????? ??? ?????? reuses ??? ????? ?? ?????? ???? ????.

    ?????? ?? ??? ??? ??????? ?????? ?????? ??????? ?????? ????? ???? ?? ?? ???? ???? ????? ??????? SQL ?????? ??? "sp_executesql." ??? ???? ???? ?? ??? ??????? sp_executesql ??????, ?? ???? ???????? ???? ??? ??????? ?????? ???????.

    ?????? ??? ???? ?? ????????? ??? ????? ?????/??????? ???? ??????? "????? ????? ??? ??????? ?????? ?? ???????" ?? SQL Server 7.0 ????????. ????? ?? ????????? ??? ?????? ??????? ???? ??????? "????? ??????" ?? SQL Server 7.0 ????????.
  • SP: ????? ?????? SQL ????????? ??????? ?????? ????? EXECUTE ???? ?? SP: ????? ????? ?? ???? "SQL ?????????." ?? ?????? SP: ????? ????? ??? ?? ???? ?? ?? ????????? ???? ??? "SQL ????????" ??? ????. ??? ??? ???? ?????? ?? ??? ???????, ?? ????? ????? ???? ??????? ???????? sp_executesql ????? ?? ????? EXECUTE. ???? ??????? sp_executesql ????? SQL Server ????? ??????? ??? ??????? ?? ???? ????? ??? ????????? ??? ???? ???????? ?????? ??????. ??? ??????? ????? EXECUTE ??? ?????? ????? ?? ?? ??? ?? ????????? ??? ??? ?? ????? ????????? ??? ???? ???????? ??? ?????????.

    ?????? ??????????? ?? ????????? ???? ?????? ????? SQL ????????? ??????? ????? "?" ?????? ???? ??????? "?" ??? ??? ??? ???. ?? ????? ?????? (????? ??? ????? ??? ?? ????? ?????????). ??? ?? ????? ?????? ??? ??? ?? ????? ????. ????? ?????? ?????? "???? ???????" (??? ????? ??????? ?????) ????? ?????? ?? ?????? ???? ???? ??????? ???? ??? SP: ??? ? SP: ?????? ????? ????? ?????? ???????. ?? ????? ????? ?? "??? ???" ????? (?? ??????? ????? ? ???? ??? ???). ????? ??????? ??? ??? ????? SQL ?????????. ??? ??? ????? ?? ????? ???? ???? ????? ??? SP: ??? ? SP: ????? ????? ??? ???????. ??? ?? ???? ????? ?? ????? ???? ? ?? ????? ???????? ????? ????? ??????? ?????? ??????? ?????? (??? ?????????? ?????? NT ? ???????) ?????? ??? ?? ????? ?????. ?????? ?? ????? ??????? ??? ?? ????? ??? ???? ????? ???? ??????? ??? SQL:BatchCompletedSQL:RPCCompleted.

    ??? ????? ?? ??? ??????? ????? EXECUTE ?? ?? ???????? ??????? ??????? sp_executesql. ??? ???? ??????? ???? ??? ????????? ?????? ??? EXECUTE ??????? ????? ?? SQL ?????????. ????? ???? ??? ?????? "?" ???? "?" idValue ??????? ??????? ??? ?? ????? ????? SELECT ?? ?????? ???????? ??? ???? ?????. ???????? ????? EXECUTE ? ??????? ???? ??????? ???????? ???????? ???????:
    drop proc dynamicUsingEXECUTE
    		  go create proc dynamicUsingEXECUTE @table sysname, @idName varchar(10),
    		  @idValue varchar(10) as declare @query nvarchar(4000) -- Build query string
    		  with parameter. -- Notice the use of escape quotes. select @query = 'select *
    		  from ' + @table + ' where ' + @idName + ' = ''' + @idValue + '''' exec (@query)
    		  go
    ?? ?????? ?? ??? ????????? ??? ???????? ?????? ? ??? ????? ??? ??????? ????? ???? titles (????????) ?? ????? ?????? ????? pubs (????????) ????? ?? ??? ?????? @ idValue ??????? SQL Server ??? ????? ??? ??????? ????? ??? ???????. ??? ???? ??????:
    exec dynamicUsingEXECUTE
    		  'titles', 'title_id', 'MC2222' go exec dynamicUsingEXECUTE 'titles',
    		  'title_id', 'BU7832'
    ?????? ?? ??? ??????? ????????? ????? ?????? ??? ???? ?? SQL Server ???? ???????? parameterize ?????? ?????? ??? ???????. ??? ???? ??? ??? ??? ??????? ???? ?? parameterize SQL Server ???????? SQL Server ?? ?? ????? ??? ??????? ?????? ??? @ idValue ?? ????? ?????. ??????? ???? ??????? ???? ????? ??????.

    ???? ????? ????? ??? ??????? ???????? sp_executesql ????? ?? ????? EXECUTE. ???? ??? ??????? ??????? sp_executesql ???? ?????? ??? ???? ?????? ??? ??????? ?? ???? ?? ??????? ????? ??????? ?????? SQL Server. ??? ???? ??????:
    drop proc dynamicUsingSP_EXECUTESQL go create proc
    		  dynamicUsingSP_EXECUTESQL @table sysname, @idName varchar(10), @idValue
    		  varchar(10) as declare @query nvarchar(4000) -- Build query string with
    		  parameter select @query = 'select * from ' + @table + ' where ' + @idName + ' =
    		  @idValue' -- Now execute with parameter exec sp_executesql @query, N'@idValue
    		  varchar(10)', @idValue go exec dynamicUsingSP_EXECUTESQL 'titles', 'title_id',
    		  'MC2222' go exec dynamicUsingSP_EXECUTESQL 'titles', 'title_id',
    		  'BU7832'
    ?? ??? ??????? ?? ????? ?????? ????? ????? sp_executesql SQL Server ?????? ??? ??? ?????? ????? SELECT ?? ???????? ???????? title_id (????_???????) ??????. ??????? ?????? reuses SQL Server ??? ?????? ???? ???????. ????? ?? ????????? ??? sp_executesql ???? "sp_executesql (T-SQL)" ? "??????? sp_executesql" ???????? ?? SQL Server 7.0 ????????.
  • SP:RECOMPILES ???? ??? ????? ??? ??? ?? ????? ???? recompiled ????? ???????. ????? ????? ????? ???? ??? ?? SQL Server ?????? ????? ?????? ??????? ????? ?? ????? ?????????.
??? ?? ????? ?? ?? ??? ??????? ??????? ????? ????????? ????? ????? SQL Server. ????? ?? ??????? ??? ????? SQL Server ????? ??? ????? ????? ?? ?? ???? SQL Server ???? ???? parameterize ??? ??????????? ???????? ?? ??? ?? ??????? ??? ????????? ???? ?????. ??? ??? ????? ???? ???? SQL ????? ??????? ?????? ?? SQL ?? ???????. ???? ?? ???? ??? CPU ??????? ?????? ?? ?????????? ?????????. ??????? ?????? ??? ??? ??????? ???? ?? ???? ??????????? ???? ?? ??????? ???? ????? ?? ????? ????????? ??????? ??? ??????????? ???????? ?????? ??????? ?? ???????? sp_executesql.

?????

????? ?? ????????? ??? ???????? ???????? ??????? ???????? ????? ?????? ?? SQL Server "? ???? ??? ????? ???????? ??????? ?????? ??" ????? ??????? ?? Microsoft:
224587????? ??????? ????? ???? ??????? ?? ???? SQL
224453??? INF: ??? ????? ??? 2000 ?? SQL Server 7.0
243586??????? ??????? ???????? recompilation ????? ????
243589????? ??????? ????? ??????????? ????? ???? ??? SQL Server 7.0 ?? ??????? ??????
251004INF: ????? ?????? ??? SQL Server 7.0

???????

???? ???????: 243588 - ????? ??? ??????: 06/?? ??????/1426 - ??????: 5.4
????? ???
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 64-bit Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Standard Edition
????? ??????: 
kbmt kbhowtomaster kbhowto kbinfo KB243588 KbMtar
????? ????
???: ??? ????? ??? ?????? ???????? ?????? ????? ???? ????? ?????????? ????? ?? ????????? ?????? ????. ???? ???? ?????????? ???? ?? ???????? ???????? ?????? ????????? ????? ????????? ???????? ????? ???????? ?????? ?? ?????? ??? ?? ???????? ???????? ?? ????? ??????? ?????? ??? ??????? ?????? ??. ?????? ?? ???? ??? ??????? ???????? ????? ?? ???? ????? ?????? ??? ????? ??? ????? ??????? ?? ????? ?? ?????? ??? ??? ??????? ??????? ?? ????? ????? ????? ????? ?????. ?? ????? ???? ?????????? ??????? ??? ????? ?? ??????? ?? ????? ?????? ?? ??? ????? ?? ????? ??????? ?? ???????? ?? ??? ???????. ???? ???? ?????????? ???????? ??? ????? ?????? ??????? ??????
???? ??? ????? ??????? ?????? ??????????243588

????? ???????

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com