sql - खंड में एक एसक्यूएल पैरामीटर




sql-server parameters (25)

In ColdFusion we just do:

<cfset myvalues = "ruby|rails|scruffy|rubyonrails">
    <cfquery name="q">
        select * from sometable where values in <cfqueryparam value="#myvalues#" list="true">
    </cfquery>

मैं तर्कसंगत संख्या के साथ एक IN क्लॉज युक्त क्वेरी को पैरामीटर कैसे करूं, इस तरह की?

SELECT * FROM Tags 
WHERE Name IN ('ruby','rails','scruffy','rubyonrails')
ORDER BY Count DESC

इस प्रश्न में, तर्कों की संख्या 1 से 5 तक कहीं भी हो सकती है।

मैं इस (या एक्सएमएल) के लिए समर्पित संग्रहीत प्रक्रिया का उपयोग नहीं करना चाहूंगा, लेकिन अगर SQL सर्वर 2008 के लिए विशिष्ट कुछ शानदार तरीका है, तो मैं इसके लिए खुला हूं।


I think this is a case when a static query is just not the way to go. Dynamically build the list for your in clause, escape your single quotes, and dynamically build SQL. In this case you probably won't see much of a difference with any method due to the small list, but the most efficient method really is to send the SQL exactly as it is written in your post. I think it is a good habit to write it the most efficient way, rather than to do what makes the prettiest code, or consider it bad practice to dynamically build SQL.

I have seen the split functions take longer to execute than the query themselves in many cases where the parameters get large. A stored procedure with table valued parameters in SQL 2008 is the only other option I would consider, although this will probably be slower in your case. TVP will probably only be faster for large lists if you are searching on the primary key of the TVP, because SQL will build a temporary table for the list anyway (if the list is large). You won't know for sure unless you test it.

I have also seen stored procedures that had 500 parameters with default values of null, and having WHERE Column1 IN (@Param1, @Param2, @Param3, ..., @Param500). This caused SQL to build a temp table, do a sort/distinct, and then do a table scan instead of an index seek. That is essentially what you would be doing by parameterizing that query, although on a small enough scale that it won't make a noticeable difference. I highly recommend against having NULL in your IN lists, as if that gets changed to a NOT IN it will not act as intended. You could dynamically build the parameter list, but the only obvious thing that you would gain is that the objects would escape the single quotes for you. That approach is also slightly slower on the application end since the objects have to parse the query to find the parameters. It may or may not be faster on SQL, as parameterized queries call sp_prepare, sp_execute for as many times you execute the query, followed by sp_unprepare.

The reuse of execution plans for stored procedures or parameterized queries may give you a performance gain, but it will lock you in to one execution plan determined by the first query that is executed. That may be less than ideal for subsequent queries in many cases. In your case, reuse of execution plans will probably be a plus, but it might not make any difference at all as the example is a really simple query.

Cliffs notes:

For your case anything you do, be it parameterization with a fixed number of items in the list (null if not used), dynamically building the query with or without parameters, or using stored procedures with table valued parameters will not make much of a difference. However, my general recommendations are as follows:

Your case/simple queries with few parameters:

Dynamic SQL, maybe with parameters if testing shows better performance.

Queries with reusable execution plans, called multiple times by simply changing the parameters or if the query is complicated:

SQL with dynamic parameters.

Queries with large lists:

Stored procedure with table valued parameters. If the list can vary by a large amount use WITH RECOMPILE on the stored procedure, or simply use dynamic SQL without parameters to generate a new execution plan for each query.


May be we can use XML here:

    declare @x xml
    set @x='<items>
    <item myvalue="29790" />
    <item myvalue="31250" />
    </items>
    ';
    With CTE AS (
         SELECT 
            x.item.value('@myvalue[1]', 'decimal') AS myvalue
        FROM @x.nodes('//items/item') AS x(item) )

    select * from YourTable where tableColumnName in (select myvalue from cte)

Here is another answer to this problem.

(6/4/13 को नया संस्करण पोस्ट किया गया)।

    private static DataSet GetDataSet(SqlConnectionStringBuilder scsb, string strSql, params object[] pars)
    {
        var ds = new DataSet();
        using (var sqlConn = new SqlConnection(scsb.ConnectionString))
        {
            var sqlParameters = new List<SqlParameter>();
            var replacementStrings = new Dictionary<string, string>();
            if (pars != null)
            {
                for (int i = 0; i < pars.Length; i++)
                {
                    if (pars[i] is IEnumerable<object>)
                    {
                        List<object> enumerable = (pars[i] as IEnumerable<object>).ToList();
                        replacementStrings.Add("@" + i, String.Join(",", enumerable.Select((value, pos) => String.Format("@_{0}_{1}", i, pos))));
                        sqlParameters.AddRange(enumerable.Select((value, pos) => new SqlParameter(String.Format("@_{0}_{1}", i, pos), value ?? DBNull.Value)).ToArray());
                    }
                    else
                    {
                        sqlParameters.Add(new SqlParameter(String.Format("@{0}", i), pars[i] ?? DBNull.Value));
                    }
                }
            }
            strSql = replacementStrings.Aggregate(strSql, (current, replacementString) => current.Replace(replacementString.Key, replacementString.Value));
            using (var sqlCommand = new SqlCommand(strSql, sqlConn))
            {
                if (pars != null)
                {
                    sqlCommand.Parameters.AddRange(sqlParameters.ToArray());
                }
                else
                {
                    //Fail-safe, just in case a user intends to pass a single null parameter
                    sqlCommand.Parameters.Add(new SqlParameter("@0", DBNull.Value));
                }
                using (var sqlDataAdapter = new SqlDataAdapter(sqlCommand))
                {
                    sqlDataAdapter.Fill(ds);
                }
            }
        }
        return ds;
    }

चीयर्स।


SQL Server 2016+ आप SPLIT_STRING फ़ंक्शन का उपयोग कर सकते हैं:

DECLARE @names NVARCHAR(MAX) = 'ruby,rails,scruffy,rubyonrails';

SELECT * 
FROM Tags
WHERE Name IN (SELECT [value] FROM STRING_SPLIT(@names, ','))
ORDER BY Count DESC;

या:

DECLARE @names NVARCHAR(MAX) = 'ruby,rails,scruffy,rubyonrails';

SELECT t.*
FROM Tags t
JOIN STRING_SPLIT(@names,',')
  ON t.Name = [value]
ORDER BY Count DESC;

LiveDemo

स्वीकार्य उत्तर निश्चित रूप से काम करेगा और यह जाने का एक तरीका है, लेकिन यह विरोधी पैटर्न है।

ई। मूल्यों की सूची से पंक्तियां खोजें

यह सामान्य एंटी-पैटर्न के लिए प्रतिस्थापन है जैसे अनुप्रयोग परत या ट्रांजैक्ट-एसक्यूएल में गतिशील एसक्यूएल स्ट्रिंग बनाना, या LIKE ऑपरेटर का उपयोग करके:

SELECT ProductId, Name, Tags
FROM Product
WHERE ',1,2,3,' LIKE '%,' + CAST(ProductId AS VARCHAR(20)) + ',%';

मूल प्रश्न के लिए SQL Server 2008 की आवश्यकता है। चूंकि इस प्रश्न को अक्सर डुप्लिकेट के रूप में उपयोग किया जाता है, इसलिए मैंने इस उत्तर को संदर्भ के रूप में जोड़ा है।


हमारे पास ऐसा फ़ंक्शन है जो एक टेबल वैरिएबल बनाता है जिसमें आप शामिल हो सकते हैं:

ALTER FUNCTION [dbo].[Fn_sqllist_to_table](@list  AS VARCHAR(8000),
                                           @delim AS VARCHAR(10))
RETURNS @listTable TABLE(
  Position INT,
  Value    VARCHAR(8000))
AS
  BEGIN
      DECLARE @myPos INT

      SET @myPos = 1

      WHILE Charindex(@delim, @list) > 0
        BEGIN
            INSERT INTO @listTable
                        (Position,Value)
            VALUES     (@myPos,LEFT(@list, Charindex(@delim, @list) - 1))

            SET @myPos = @myPos + 1

            IF Charindex(@delim, @list) = Len(@list)
              INSERT INTO @listTable
                          (Position,Value)
              VALUES     (@myPos,'')

            SET @list = RIGHT(@list, Len(@list) - Charindex(@delim, @list))
        END

      IF Len(@list) > 0
        INSERT INTO @listTable
                    (Position,Value)
        VALUES     (@myPos,@list)

      RETURN
  END 

इसलिए:

@Name varchar(8000) = null // parameter for search values    

select * from Tags 
where Name in (SELECT value From fn_sqllist_to_table(@Name,',')))
order by Count desc

I use a more concise version of the top voted answer :

List<SqlParameter> parameters = tags.Select((s, i) => new SqlParameter("@tag" + i.ToString(), SqlDbType.NVarChar(50)) { Value = s}).ToList();

var whereCondition = string.Format("tags in ({0})", String.Join(",",parameters.Select(s => s.ParameterName)));

It does loop through the tag parameters twice; but that doesn't matter most of the time (it won't be your bottleneck; if it is, unroll the loop).

If you're really interested in performance and don't want to iterate through the loop twice, here's a less beautiful version:

var parameters = new List<SqlParameter>();
var paramNames = new List<string>();
for (var i = 0; i < tags.Length; i++)  
{
    var paramName = "@tag" + i;

    //Include size and set value explicitly (not AddWithValue)
    //Because SQL Server may use an implicit conversion if it doesn't know
    //the actual size.
    var p = new SqlParameter(paramName, SqlDbType.NVarChar(50) { Value = tags[i]; } 
    paramNames.Add(paramName);
    parameters.Add(p);
}

var inClause = string.Join(",", paramNames);

मैं डिफ़ॉल्ट रूप से एक तालिका मूल्यवान फ़ंक्शन (जो एक स्ट्रिंग से एक तालिका लौटाता है) को पास करने के साथ डिफ़ॉल्ट रूप से संपर्क करता हूं।

यहां यूडीएफ के लिए कोड है (मुझे इसे स्टैक ओवरफ़्लो से कहीं मिला है, मुझे अभी स्रोत नहीं मिल रहा है)

CREATE FUNCTION [dbo].[Split] (@sep char(1), @s varchar(8000))
RETURNS table
AS
RETURN (
    WITH Pieces(pn, start, stop) AS (
      SELECT 1, 1, CHARINDEX(@sep, @s)
      UNION ALL
      SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
      FROM Pieces
      WHERE stop > 0
    )
    SELECT 
      SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
    FROM Pieces
  )

एक बार जब आप इसे प्राप्त कर लेंगे तो आपका कोड उतना आसान होगा जितना:

select * from Tags 
where Name in (select s from dbo.split(';','ruby;rails;scruffy;rubyonrails'))
order by Count desc

जब तक आप एक हास्यास्पद लंबी स्ट्रिंग नहीं है, यह तालिका सूचकांक के साथ अच्छी तरह से काम करना चाहिए।

If needed you can insert it into a temp table, index it, then run a join...


एक ही समस्या के समाधान के लिए यहां एक क्रॉस-पोस्ट है। आरक्षित डिलीमीटर से अधिक मजबूत - इसमें भागने और घोंसले वाले सरणी शामिल हैं, और एनयूएलएल और खाली सरणी को समझते हैं।

सी # और टी-एसक्यूएल स्ट्रिंग [] पैक / अनपैक उपयोगिता कार्यों

फिर आप तालिका-मूल्यवान फ़ंक्शन में शामिल हो सकते हैं।


Here is another alternative. Just pass a comma-delimited list as a string parameter to the stored procedure and:

CREATE PROCEDURE [dbo].[sp_myproc]
    @UnitList varchar(MAX) = '1,2,3'
AS
select column from table
where ph.UnitID in (select * from CsvToInt(@UnitList))

और समारोह:

CREATE Function [dbo].[CsvToInt] ( @Array varchar(MAX))
returns @IntTable table
(IntValue int)
AS
begin
    declare @separator char(1)
    set @separator = ','
    declare @separator_position int
    declare @array_value varchar(MAX)

    set @array = @array + ','

    while patindex('%,%' , @array) <> 0
    begin

        select @separator_position = patindex('%,%' , @array)
        select @array_value = left(@array, @separator_position - 1)

        Insert @IntTable
        Values (Cast(@array_value as int))
        select @array = stuff(@array, 1, @separator_position, '')
    end
    return
end

Another possible solution is instead of passing a variable number of arguments to a stored procedure, pass a single string containing the names you're after, but make them unique by surrounding them with '<>'. Then use PATINDEX to find the names:

SELECT * 
FROM Tags 
WHERE PATINDEX('%<' + Name + '>%','<jo>,<john>,<scruffy>,<rubyonrails>') > 0

मूल सवाल यह था कि "मैं क्वेरी को पैरामीटर कैसे करूं ..."

मुझे यहां बताएं, कि यह मूल प्रश्न का उत्तर नहीं है । अन्य अच्छे उत्तरों में पहले से ही कुछ प्रदर्शन हैं।

इसके साथ, आगे बढ़ें और इस जवाब को ध्वजांकित करें, इसे कम करें, इसे उत्तर के रूप में चिह्नित करें ... जो भी आपको विश्वास है वह सही है।

पसंदीदा ब्रैकेट से उत्तर ब्रैकेट से जवाब देखें कि मैं (और 231 अन्य) अपवित्र हूं। उनके उत्तर में दिया गया दृष्टिकोण 1) बाध्य चर के प्रभावी उपयोग के लिए अनुमति देता है, और 2) भविष्यवाणी करने के लिए कि वह योग्य हैं।

चयनित उत्तर

जो मैं यहां संबोधित करना चाहता हूं वो जोएल स्पॉल्स्की के जवाब में दिया गया दृष्टिकोण है, उत्तर "चयनित" सही जवाब के रूप में दिया गया है।

जोएल स्पॉल्स्की का दृष्टिकोण चालाक है। और यह उचित रूप से काम करता है, यह अनुमानित व्यवहार और अनुमानित प्रदर्शन प्रदर्शित करने जा रहा है, "सामान्य" मान दिए गए हैं, और मानक किनारे के मामलों जैसे कि न्यूल और खाली स्ट्रिंग के साथ। और यह एक विशेष आवेदन के लिए पर्याप्त हो सकता है।

लेकिन इस दृष्टिकोण को सामान्यीकृत करने के मामले में, आइए अधिक अस्पष्ट कोने के मामलों पर विचार करें, जैसे कि Name कॉलम में वाइल्डकार्ड वर्ण होता है (जैसा कि LIKE predicate द्वारा पहचाना जाता है।) वाइल्डकार्ड वर्ण जो मैं आमतौर पर उपयोग करता हूं वह % (एक प्रतिशत चिह्न) होता है। । तो आइए अब यहां उससे निपटें, और बाद में अन्य मामलों पर जाएं।

% चरित्र के साथ कुछ समस्याएं

'pe%ter' नाम मान पर विचार करें। (यहां उदाहरणों के लिए, मैं कॉलम नाम के स्थान पर एक शाब्दिक स्ट्रिंग मान का उपयोग करता हूं।) 'Pe% ter' के नाम मान वाले एक पंक्ति को फ़ॉर्म की क्वेरी द्वारा वापस किया जाएगा:

select ...
 where '|peanut|butter|' like '%|' + 'pe%ter' + '|%'

लेकिन यदि खोज शब्द का क्रम उलट दिया जाता है तो वही पंक्ति वापस नहीं की जाएगी:

select ...
 where '|butter|peanut|' like '%|' + 'pe%ter' + '|%'

जो व्यवहार हम देखते हैं वह अजीब तरह का है। सूची में खोज शब्दों के क्रम को बदलने से परिणाम सेट बदल जाता है।

यह लगभग बिना कहने के चला जाता है कि हम पेनट मक्खन से मिलान करने के लिए pe%ter नहीं चाहते हैं, इससे कोई फर्क नहीं पड़ता कि उसे कितना पसंद है।

अस्पष्ट कोने का मामला

(हां, मैं इस बात से सहमत हूं कि यह एक अस्पष्ट मामला है। शायद एक जिसे परीक्षण करने की संभावना नहीं है। हम कॉलम वैल्यू में वाइल्डकार्ड की अपेक्षा नहीं करेंगे। हम मान सकते हैं कि एप्लिकेशन इस तरह के मूल्य को संग्रहीत होने से रोकता है। लेकिन मेरे अनुभव में, मैंने शायद ही कभी डेटाबेस की बाधा देखी है जो विशेष रूप से वर्णित वर्णों या पैटर्नों को अस्वीकार करती है जिन्हें एक LIKE तुलना ऑपरेटर के दाईं ओर वाइल्डकार्ड माना जाएगा।

एक छेद पैचिंग

इस छेद को पकड़ने के लिए एक दृष्टिकोण % वाइल्डकार्ड चरित्र से बचने के लिए है। (ऑपरेटर पर एस्केप क्लॉज से परिचित किसी के लिए, यहां SQL सर्वर दस्तावेज़ों का एक लिंक है।

select ...
 where '|peanut|butter|'
  like '%|' + 'pe\%ter' + '|%' escape '\'

अब हम शाब्दिक% से मेल खा सकते हैं। बेशक, जब हमारे पास कॉलम नाम होता है, तो हमें वाइल्डकार्ड से गतिशील रूप से बचने की आवश्यकता होगी। हम % वर्ण की घटनाओं को खोजने के लिए REPLACE फ़ंक्शन का उपयोग कर सकते हैं और प्रत्येक के सामने बैकस्लैश वर्ण डाल सकते हैं, जैसे:

select ...
 where '|pe%ter|'
  like '%|' + REPLACE( 'pe%ter' ,'%','\%') + '|%' escape '\'

तो यह% वाइल्डकार्ड के साथ समस्या हल करता है। लगभग।

भागने से बचें

हम मानते हैं कि हमारे समाधान ने एक और समस्या पेश की है। बचने का चरित्र। हम देखते हैं कि हमें भी बचने वाले चरित्र की किसी भी घटना से बचने की आवश्यकता होगी। इस बार, हम इसका उपयोग करते हैं! बचने के चरित्र के रूप में:

select ...
 where '|pe%t!r|'
  like '%|' + REPLACE(REPLACE( 'pe%t!r' ,'!','!!'),'%','!%') + '|%' escape '!'

अंडरस्कोर भी

अब जब हम रोल पर हैं, हम अंडरस्कोर वाइल्डकार्ड को एक और REPLACE हैंडल जोड़ सकते हैं। और बस मस्ती के लिए, इस बार, हम भागने वाले चरित्र के रूप में $ का उपयोग करेंगे।

select ...
 where '|p_%t!r|'
  like '%|' + REPLACE(REPLACE(REPLACE( 'p_%t!r' ,'$','$$'),'%','$%'),'_','$_') + '|%' escape '$'

मैं भागने के लिए इस दृष्टिकोण को प्राथमिकता देता हूं क्योंकि यह ओरेकल और MySQL के साथ-साथ SQL सर्वर में भी काम करता है। (मैं आमतौर पर बचपन के चरित्र के रूप में \ बैकस्लैश का उपयोग करता हूं, क्योंकि यह वह चरित्र है जिसे हम नियमित अभिव्यक्तियों में उपयोग करते हैं। लेकिन सम्मेलन से क्यों बाध्य होना चाहिए!

उन अजीब ब्रैकेट्स

एसक्यूएल सर्वर वाइल्डकार्ड वर्णों को ब्रैकेट [] में संलग्न करके शाब्दिक के रूप में माना जा सकता है। तो हम कम से कम SQL सर्वर के लिए, अभी तक फिक्सिंग नहीं कर रहे हैं। चूंकि ब्रैकेट के जोड़े का विशेष अर्थ होता है, इसलिए हमें उनसे बचने की भी आवश्यकता होगी। यदि हम ब्रैकेट से ठीक से बचने का प्रबंधन करते हैं, तो कम से कम हमें हाइफ़न - और कैरेट ^ ब्रैकेट के भीतर परेशान नहीं होना पड़ेगा। और हम बचने वाले ब्रैकेट के अंदर कोई भी % और _ अक्षर छोड़ सकते हैं, क्योंकि हम मूल रूप से ब्रैकेट के विशेष अर्थ को अक्षम कर देंगे।

ब्रैकेट के मेल खाने वाले जोड़े ढूंढना मुश्किल नहीं होना चाहिए। यह सिंगलटन% और _ की घटनाओं को संभालने से थोड़ा मुश्किल है। (ध्यान दें कि ब्रैकेट की सभी घटनाओं से बचने के लिए पर्याप्त नहीं है, क्योंकि सिंगलटन ब्रैकेट को शाब्दिक माना जाता है, और इससे बचने की आवश्यकता नहीं होती है। तर्क अधिक मामूली हो रहा है जितना मैं अधिक टेस्ट केस चलाने के बिना संभाल सकता हूं ।)

इनलाइन अभिव्यक्ति गन्दा हो जाता है

एसक्यूएल में इनलाइन अभिव्यक्ति लंबे और उलझन में हो रही है। हम शायद इसे काम कर सकते हैं, लेकिन स्वर्ग गरीब आत्मा की मदद करता है जो पीछे आता है और इसे समझना है। एक प्रशंसक के रूप में मैं इनलाइन अभिव्यक्तियों के लिए हूं, मैं यहां एक का उपयोग नहीं कर रहा हूं, मुख्य रूप से क्योंकि मैं गड़बड़ी के कारण को समझाते हुए एक टिप्पणी छोड़ना नहीं चाहता, और इसके लिए क्षमा मांगना चाहता हूं।

एक समारोह कहाँ?

ठीक है, इसलिए, यदि हम SQL में इनलाइन अभिव्यक्ति के रूप में इसे संभाल नहीं पाते हैं, तो हमारे पास निकटतम विकल्प उपयोगकर्ता परिभाषित फ़ंक्शन है। और हम जानते हैं कि किसी चीज को गति नहीं देगा (जब तक हम उस पर एक इंडेक्स परिभाषित नहीं कर सकते, जैसे हम ओरेकल के साथ कर सकते हैं।) अगर हमें कोई फ़ंक्शन बनाना है, तो हम SQL कोड को कॉल करने वाले कोड में बेहतर कर सकते हैं बयान।

और उस कार्य में व्यवहार में कुछ अंतर हो सकते हैं, जो डीबीएमएस और संस्करण पर निर्भर है। (आपके सभी जावा डेवलपर्स के लिए एक चिल्लाओ जो किसी भी डेटाबेस इंजन को एक दूसरे के रूप में उपयोग करने में सक्षम होने के लिए उत्सुक हैं।)

डोमेन की जानकारी

हमारे पास कॉलम के लिए डोमेन का विशेष ज्ञान हो सकता है, (यानी, कॉलम के लिए लागू स्वीकार्य मानों का सेट हो सकता है। हम एक प्राथमिकता को जान सकते हैं कि कॉलम में संग्रहीत मानों में कभी भी प्रतिशत चिह्न, अंडरस्कोर या ब्रैकेट नहीं होगा जोड़े। उस मामले में, हम सिर्फ एक त्वरित टिप्पणी शामिल करते हैं कि उन मामलों को कवर किया गया है।

कॉलम में संग्रहीत मान% या _ वर्णों की अनुमति दे सकते हैं, लेकिन एक बाधा को उन मानों से बचने की आवश्यकता हो सकती है, शायद परिभाषित वर्ण का उपयोग करना, जैसे कि मान तुलना "सुरक्षित" की तरह हैं। फिर, मूल्यों के अनुमत सेट के बारे में एक त्वरित टिप्पणी, और विशेष रूप से कौन सा चरित्र एक बचने वाले चरित्र के रूप में उपयोग किया जाता है, और जोएल स्पॉल्स्की के दृष्टिकोण के साथ जाता है।

लेकिन, विशेष ज्ञान और गारंटी अनुपस्थित है, कम से कम उन अस्पष्ट कोने के मामलों को संभालने पर विचार करना महत्वपूर्ण है, और विचार करें कि व्यवहार उचित है और "विनिर्देश के अनुसार"।

अन्य मुद्दों को दोबारा शुरू किया गया

मेरा मानना ​​है कि दूसरों ने पहले ही चिंता के अन्य सामान्य क्षेत्रों में से कुछ को पर्याप्त रूप से इंगित किया है:

  • एसक्यूएल इंजेक्शन (जो उपयोगकर्ता द्वारा आपूर्ति की गई जानकारी के रूप में दिखाई देगा, और इसमें बाध्य चर के माध्यम से उन्हें आपूर्ति करने के बजाय एसक्यूएल टेक्स्ट में शामिल होगा। बाइंड चर का उपयोग करने की आवश्यकता नहीं है, यह एसक्यूएल इंजेक्शन के साथ विफल होने के लिए सिर्फ एक सुविधाजनक दृष्टिकोण है। इससे निपटने के तरीके:

  • सूचकांक की बजाय सूचकांक स्कैन का उपयोग कर अनुकूलक योजना, वाइल्डकार्ड से बचने के लिए अभिव्यक्ति या फ़ंक्शन की संभावित आवश्यकता (अभिव्यक्ति या फ़ंक्शन पर संभावित अनुक्रमणिका)

  • बाध्य चर के स्थान पर शाब्दिक मूल्यों का उपयोग स्केलेबिलिटी को प्रभावित करता है

निष्कर्ष

मुझे जोएल स्पॉल्स्की का दृष्टिकोण पसंद है। यह चालाक है। और यह काम करता है।

लेकिन जैसे ही मैंने इसे देखा, मैंने तुरंत इसके साथ एक संभावित समस्या देखी, और यह मेरी प्रकृति नहीं है कि इसे स्लाइड करें। मेरा मतलब दूसरों के प्रयासों की आलोचना करने का नहीं है। मुझे पता है कि कई डेवलपर्स अपना काम बहुत व्यक्तिगत रूप से लेते हैं, क्योंकि वे इसमें बहुत निवेश करते हैं और वे इसके बारे में बहुत अधिक परवाह करते हैं। तो कृपया समझें, यह व्यक्तिगत हमला नहीं है। जो मैं यहां पहचान रहा हूं वह समस्या का प्रकार है जो परीक्षण के बजाय उत्पादन में फसल है।

हां, मैं मूल प्रश्न से बहुत दूर चला गया हूं। लेकिन एक प्रश्न के लिए "चुने गए" उत्तर के साथ मैं एक महत्वपूर्ण मुद्दा मानने के बारे में इस नोट को कहां छोड़ सकता हूं?


आप पैरामीटर को स्ट्रिंग के रूप में पास कर सकते हैं

तो आपके पास स्ट्रिंग है

DECLARE @tags

SET @tags = ‘ruby|rails|scruffy|rubyonrails’

select * from Tags 
where Name in (SELECT item from fnSplit(@tags, ‘|’))
order by Count desc

तो आपको केवल 1 पैरामीटर के रूप में स्ट्रिंग पास करना है।

यहां उपयोग किए जाने वाले विभाजन कार्य हैं।

CREATE FUNCTION [dbo].[fnSplit](
    @sInputList VARCHAR(8000) -- List of delimited items
  , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))

 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem
 END

IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END

Use the following stored procedure. It uses a custom split function, which can be found here .

 create stored procedure GetSearchMachingTagNames 
    @PipeDelimitedTagNames varchar(max), 
    @delimiter char(1) 
    as  
    begin
         select * from Tags 
         where Name in (select data from [dbo].[Split](@PipeDelimitedTagNames,@delimiter) 
    end

मेरी राय में, इस समस्या को हल करने का सबसे अच्छा स्रोत, इस साइट पर पोस्ट किया गया है:

Syscomments। दीनाकर नेठी

CREATE FUNCTION dbo.fnParseArray (@Array VARCHAR(1000),@separator CHAR(1))
RETURNS @T Table (col1 varchar(50))
AS 
BEGIN
 --DECLARE @T Table (col1 varchar(50))  
 -- @Array is the array we wish to parse
 -- @Separator is the separator charactor such as a comma
 DECLARE @separator_position INT -- This is used to locate each separator character
 DECLARE @array_value VARCHAR(1000) -- this holds each array value as it is returned
 -- For my loop to work I need an extra separator at the end. I always look to the
 -- left of the separator character for each array value

 SET @array = @array + @separator

 -- Loop through the string searching for separtor characters
 WHILE PATINDEX('%' + @separator + '%', @array) <> 0 
 BEGIN
    -- patindex matches the a pattern against a string
    SELECT @separator_position = PATINDEX('%' + @separator + '%',@array)
    SELECT @array_value = LEFT(@array, @separator_position - 1)
    -- This is where you process the values passed.
    INSERT into @T VALUES (@array_value)    
    -- Replace this select statement with your processing
    -- @array_value holds the value of this element of the array
    -- This replaces what we just processed with and empty string
    SELECT @array = STUFF(@array, 1, @separator_position, '')
 END
 RETURN 
END

उपयोग:

SELECT * FROM dbo.fnParseArray('a,b,c,d,e,f', ',')

क्रेडिट्स: दीनाकर नेठी


यदि आपके पास SQL Server 2008 है या बाद में मैं एक तालिका मानित पैरामीटर का उपयोग करूंगा।

यदि आप SQL Server 2005 पर फंसने के लिए पर्याप्त भाग्यशाली हैं तो आप इस तरह एक CLR फ़ंक्शन जोड़ सकते हैं,

[SqlFunction(
    DataAccessKind.None,
    IsDeterministic = true,
    SystemDataAccess = SystemDataAccessKind.None,
    IsPrecise = true,
    FillRowMethodName = "SplitFillRow",
    TableDefinintion = "s NVARCHAR(MAX)"]
public static IEnumerable Split(SqlChars seperator, SqlString s)
{
    if (s.IsNull)
        return new string[0];

    return s.ToString().Split(seperator.Buffer);
}

public static void SplitFillRow(object row, out SqlString s)
{
    s = new SqlString(row.ToString());
}

आप इस तरह का उपयोग कर सकते हैं,

declare @desiredTags nvarchar(MAX);
set @desiredTags = 'ruby,rails,scruffy,rubyonrails';

select * from Tags
where Name in [dbo].[Split] (',', @desiredTags)
order by Count desc

आप प्रत्येक मान को पैरामीटर कर सकते हैं, इसलिए कुछ ऐसा है:

string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };
string cmdText = "SELECT * FROM Tags WHERE Name IN ({0})";

string[] paramNames = tags.Select(
    (s, i) => "@tag" + i.ToString()
).ToArray();

string inClause = string.Join(", ", paramNames);
using (SqlCommand cmd = new SqlCommand(string.Format(cmdText, inClause))) {
    for(int i = 0; i < paramNames.Length; i++) {
       cmd.Parameters.AddWithValue(paramNames[i], tags[i]);
    }
}

जो आपको देगा:

cmd.CommandText = "SELECT * FROM Tags WHERE Name IN (@tag0, @tag1, @tag2, @tag3)"
cmd.Parameters["@tag0"] = "ruby"
cmd.Parameters["@tag1"] = "rails"
cmd.Parameters["@tag2"] = "scruffy"
cmd.Parameters["@tag3"] = "rubyonrails"

नहीं, यह एसक्यूएल इंजेक्शन के लिए खुला नहीं है। कमांडटेक्स्ट में एकमात्र इंजेक्शन टेक्स्ट उपयोगकर्ता इनपुट पर आधारित नहीं है। यह पूरी तरह से हार्डकोडेड "@tag" उपसर्ग, और एक सरणी के सूचकांक पर आधारित है। सूचकांक हमेशा एक पूर्णांक होगा, उपयोगकर्ता उत्पन्न नहीं होता है, और सुरक्षित है।

उपयोगकर्ता द्वारा इनपुट किए गए मान अभी भी पैरामीटर में भरे हुए हैं, इसलिए वहां कोई भेद्यता नहीं है।

संपादित करें:

इंजेक्शन चिंताएं अलग-अलग हैं, ध्यान दें कि पैरामीटर की चरम संख्या (ऊपर के रूप में) को निष्क्रिय करने के लिए कमांड टेक्स्ट का निर्माण करना, कैद किए गए प्रश्नों का लाभ उठाने के लिए एसक्यूएल सर्वर की क्षमता को बाधित करता है। शुद्ध परिणाम यह है कि आप लगभग पहले स्थान पर पैरामीटर का उपयोग करने के मूल्य को निश्चित रूप से खो देते हैं (जैसा कि केवल एसक्यूएल में अनुमानित तारों को डालने का विरोध करता है)।

नहीं कि कैश की गई क्वेरी योजनाएं मूल्यवान नहीं हैं, लेकिन आईएमओ इस क्वेरी से काफी लाभ देखने के लिए लगभग जटिल नहीं है। जबकि संकलन लागत निष्पादन लागत (या उससे भी अधिक) तक पहुंच सकती है, फिर भी आप मिलीसेकंड की बात कर रहे हैं।

यदि आपके पास पर्याप्त रैम है, तो मुझे उम्मीद है कि SQL सर्वर शायद पैरामीटर की सामान्य गणना के लिए भी एक योजना को कैश करेगा। मुझे लगता है कि आप हमेशा पांच पैरामीटर जोड़ सकते हैं, और अनिर्दिष्ट टैग नल हो सकते हैं - क्वेरी प्लान एक जैसा होना चाहिए, लेकिन यह मेरे लिए बहुत बदसूरत लगता है और मुझे यकीन नहीं है कि यह माइक्रो-ऑप्टिमाइज़ेशन के लायक होगा (हालांकि, स्टैक ओवरफ़्लो पर - यह बहुत लायक हो सकता है)।

साथ ही, SQL सर्वर 7 और बाद में प्रश्नों को स्वत: पैरामीटर कर देगा, इसलिए मानकों का उपयोग करना प्रदर्शन दृष्टिकोण से वास्तव में आवश्यक नहीं है - हालांकि, यह सुरक्षा दृष्टिकोण से महत्वपूर्ण है - विशेष रूप से इस तरह के उपयोगकर्ता इनपुट डेटा के साथ।


For a variable number of arguments like this the only way I'm aware of is to either generate the SQL explicitly or do something that involves populating a temporary table with the items you want and joining against the temp table.


SQL सर्वर 2008 के लिए, आप एक तालिका मूल्यवान पैरामीटर का उपयोग कर सकते हैं। यह थोड़ा सा काम है, लेकिन यह मेरी दूसरी विधि से तर्कसंगत रूप से क्लीनर है।

सबसे पहले, आपको एक प्रकार बनाना है

CREATE TYPE dbo.TagNamesTableType AS TABLE ( Name nvarchar(50) )

फिर, आपका ADO.NET कोड इस तरह दिखता है:

string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };
cmd.CommandText = "SELECT Tags.* FROM Tags JOIN @tagNames as P ON Tags.Name = P.Name";

// value must be IEnumerable<SqlDataRecord>
cmd.Parameters.AddWithValue("@tagNames", tags.AsSqlDataRecord("Name")).SqlDbType = SqlDbType.Structured;
cmd.Parameters["@tagNames"].TypeName = "dbo.TagNamesTableType";

// Extension method for converting IEnumerable<string> to IEnumerable<SqlDataRecord>
public static IEnumerable<SqlDataRecord> AsSqlDataRecord(this IEnumerable<string> values, string columnName) {
    if (values == null || !values.Any()) return null; // Annoying, but SqlClient wants null instead of 0 rows
    var firstRecord = values.First();
    var metadata = SqlMetaData.InferFromValue(firstRecord, columnName);
    return values.Select(v => 
    {
       var r = new SqlDataRecord(metadata);
       r.SetValues(v);
       return r;
    });
}

If we have strings stored inside the IN clause with the comma(,) delimited, we can use the charindex function to get the values. If you use .NET, then you can map with SqlParameters.

DDL Script:

CREATE TABLE Tags
    ([ID] int, [Name] varchar(20))
;

INSERT INTO Tags
    ([ID], [Name])
VALUES
    (1, 'ruby'),
    (2, 'rails'),
    (3, 'scruffy'),
    (4, 'rubyonrails')
;

T-SQL:

DECLARE @Param nvarchar(max)

SET @Param = 'ruby,rails,scruffy,rubyonrails'

SELECT * FROM Tags
WHERE CharIndex(Name,@Param)>0

You can use the above statement in your .NET code and map the parameter with SqlParameter.

Fiddler demo

EDIT: Create the table called SelectedTags using the following script.

DDL Script:

Create table SelectedTags
(Name nvarchar(20));

INSERT INTO SelectedTags values ('ruby'),('rails')

T-SQL:

DECLARE @list nvarchar(max)
SELECT @list=coalesce(@list+',','')+st.Name FROM SelectedTags st

SELECT * FROM Tags
WHERE CharIndex(Name,@Param)>0

यहां एक त्वरित और गंदे तकनीक है जिसका मैंने उपयोग किया है:

SELECT * FROM Tags
WHERE '|ruby|rails|scruffy|rubyonrails|'
LIKE '%|' + Name + '|%'

तो यहां सी # कोड है:

string[] tags = new string[] { "ruby", "rails", "scruffy", "rubyonrails" };
const string cmdText = "select * from tags where '|' + @tags + '|' like '%|' + Name + '|%'";

using (SqlCommand cmd = new SqlCommand(cmdText)) {
   cmd.Parameters.AddWithValue("@tags", string.Join("|", tags);
}

दो चेतावनी:

  • प्रदर्शन भयानक है। LIKE "%...%" क्वेरी अनुक्रमित नहीं हैं।
  • सुनिश्चित करें कि आपके पास कोई नहीं है , खाली, या शून्य टैग या यह काम नहीं करेगा

इसे पूरा करने के अन्य तरीके हैं कि कुछ लोग क्लीनर पर विचार कर सकते हैं, इसलिए कृपया पढ़ना जारी रखें।


यदि आप .NET से कॉल कर रहे हैं, तो आप डैपर डॉट नेट का उपयोग कर सकते हैं:

string[] names = new string[] {"ruby","rails","scruffy","rubyonrails"};
var tags = dataContext.Query<Tags>(@"
select * from Tags 
where Name in @names
order by Count desc", new {names});

यहां डैपर सोचता है, इसलिए आपको यह नहीं करना है। LINQ to SQL साथ कुछ समान है, ज़ाहिर है:

string[] names = new string[] {"ruby","rails","scruffy","rubyonrails"};
var tags = from tag in dataContext.Tags
           where names.Contains(tag.Name)
           orderby tag.Count descending
           select tag;

मैं एक टेबल प्रकार पैरामीटर (क्योंकि यह SQL सर्वर 2008 है ) पास करेगा, और where exists , या आंतरिक शामिल हों। आप sp_xml_preparedocument का उपयोग करके एक्सएमएल का भी उपयोग कर sp_xml_preparedocument , और फिर उस अस्थायी तालिका को भी इंडेक्स कर सकते हैं।


In SQL Server 2016+ another possibility is to use the OPENJSON function.

This approach is blogged about in OPENJSON - one of best ways to select rows by list of ids .

A full worked example below

CREATE TABLE dbo.Tags
  (
     Name  VARCHAR(50),
     Count INT
  )

INSERT INTO dbo.Tags
VALUES      ('VB',982), ('ruby',1306), ('rails',1478), ('scruffy',1), ('C#',1784)

GO

CREATE PROC dbo.SomeProc
@Tags VARCHAR(MAX)
AS
SELECT T.*
FROM   dbo.Tags T
WHERE  T.Name IN (SELECT J.Value COLLATE Latin1_General_CI_AS
                  FROM   OPENJSON(CONCAT('[', @Tags, ']')) J)
ORDER  BY T.Count DESC

GO

EXEC dbo.SomeProc @Tags = '"ruby","rails","scruffy","rubyonrails"'

DROP TABLE dbo.Tags 

मुझे लगता है कि अगर कोई PHP और MySQL या कुछ अन्य डेटा का उपयोग करना चाहता हैबेस सर्वर:

  1. PDO (PHP डेटा ऑब्जेक्ट्स) सीखने के बारे में सोचें - यह एक डाटाबेस एक्सेस लेयर है जो एकाधिक डेटाबेस तक पहुंच की एक समान विधि प्रदान करता है।
  2. MySQLi सीखने के बारे में सोचोMySQLi
  3. मूल PHP फ़ंक्शंस का उपयोग करें जैसे: strip_tags , mysql_real_escape_string() या यदि वेरिएबल न्यूमेरिक, बस (int)$foohere PHP में चर के प्रकार के बारे में और पढ़ें । यदि आप पीडीओ या MySQLi जैसे पुस्तकालयों का उपयोग कर रहे हैं, तो हमेशा PDO::quote() और mysqli_real_escape_string()

पुस्तकालय उदाहरण:

---- पीडीओ

----- कोई प्लेसहोल्डर नहीं - एसक्यूएल इंजेक्शन के लिए परिपक्व! यह बुरा है

$request = $pdoConnection->("INSERT INTO parents (name, addr, city) values ($name, $addr, $city)");

----- अनाम प्लेसहोल्डर्स

$request = $pdoConnection->("INSERT INTO parents (name, addr, city) values (?, ?, ?);

----- नामित प्लेसहोल्डर्स

$request = $pdoConnection->("INSERT INTO parents (name, addr, city) value (:name, :addr, :city)");

--- MySQLi

$request = $mysqliConnection->prepare('
       SELECT * FROM trainers
       WHERE name = ?
       AND email = ?
       AND last_login > ?');

    $query->bind_param('first_param', 'second_param', $mail, time() - 3600);
    $query->execute();

पीएस :

पीडीओ आसानी से इस लड़ाई जीतता है। बारह अलग डेटाबेस ड्राइवरों और नामित पैरामीटर के समर्थन के साथ, हम छोटे प्रदर्शन हानि को अनदेखा कर सकते हैं, और इसके एपीआई में उपयोग कर सकते हैं। एक सुरक्षा दृष्टिकोण से, दोनों तब तक सुरक्षित हैं जब तक डेवलपर उन्हें उपयोग करने के तरीके का उपयोग करता है

लेकिन पीडीओ और माईस्क्लुली दोनों तेजी से हैं, जबकि MySQLi मानक में तेजी से तेज़ी से प्रदर्शन करता है - गैर-तैयार बयानों के लिए ~ 2.5%, और तैयार लोगों के लिए ~ 6.5%।

और कृपया अपने डेटाबेस में प्रत्येक क्वेरी का परीक्षण करें - इंजेक्शन को रोकने के लिए यह एक बेहतर तरीका है।







sql sql-server parameters