Hot answers tagged

21

No, unfortunately table value parameters are read-only and input only. This topic in general is covered very well in How to Share Data between Stored Procedures, which presents all the alternatives. My recommendation would be to use a #temp table.


14

Using local variables prevents sniffing of parameter values, so queries are compiled based on average distribution statistics. This was the workaround for some types of parameter sensitivity problem before OPTION (OPTIMIZE FOR UNKNOWN) and trace flag 4136 became available. From the execution plan provided, this is exactly what happened in your case. When a ...


12

The parameters supplied to a procedure must be constants or variables. A function or the output of a function or an expression cannot be used. You would have to first set a variable with the required expression: declare @investigation varchar(100); set @investigation = 'Investigation is pending for ['+ @investigationidout +'] '; EXEC ...


12

The explanation seems to tied to a combination of: a detail from the linked blog that was not mentioned in this question, the pragmatics of TVPs fitting within how parameters have always been passed in and out, and the nature of table variables. The missing detail contained in the linked blog post is exactly how variables are passed in and out of Stored ...


10

According to the docs PL/pgSQL Under the Hood, you can use the configuration parameter plpgsql.variable_conflict, either before creating the function or in the start of the function definition, declaring how you want such conflicts to be resolved (the 3 possible values are error (the default), use_variable and use_column): CREATE OR REPLACE FUNCTION pg_temp....


7

Basically what happens is, when SQL Server sees a query that it needs to compile, it is going to use the first-time-called parameters to generate the execution plan. This may or may not be a good thing, but it is what happens. For instance, say you have a table of fruit (100 rows). There are 98 rows that are Apple, and only 2 rows that contain the fruit ...


7

Parameter sniffing means that one set of parameters produces a dramatically different execution plan than the other, and that if the wrong plan is cached, you get adverse performance effects. This answer is based on your simplified query - to get accurate advice for your query, you'll need to post the query and the two different plans that resulted from ...


6

"How bad is it?" depends on the degree to which you are suffering now or could suffer with increased workload in the future. One major point of suffering with plan cache pollution could be too many single use plans bloating your plan cache leading to inefficient cache usage. Another point of suffering could be high compilations/second - so in an ...


6

No, there's really no shortcuts here. Examine things in the following order: Check the procedure name. Check the number of parameters. Check the types of the parameters.


6

...but executed quickly when run from SSMS (took 5 seconds) Rather annoyingly, the SSMS default is for SET ARITHABORT ON whereas the majority of client libraries (ADO .Net, ODBC, OLE DB) specify SET ARITHABORT OFF. Likely you had a plan "go bad" but when you attempted to replicate via SSMS, the difference in ARITHABORT resulted in a different plan being ...


6

You simply don't write it in such a way that a user can pass in a structured WHERE clause. This is a recipe for disaster, and I bet at least half of the companies who have been exploited by SQL injection thought they were protected by checking the input for keywords, stripping out semi-colons and comments, etc. They weren't, and there will always be ways ...


5

The SQL Server Customer Advisory Team wrote a blog post that has some information on this setting here. The –E startup flag The SQL Server startup flag –E forces SQL Server to allocate 4 extents at a time to each file, essentially quadrupling the stripe size. In heavy insert scenarios, this drives larger block sizes to the disk. Also, your pages ...


5

The answer is close to your "stats updates automatically cause dependent query plans to be flushed". They don't "stick around" Flushing a plan from cache is determined by memory pressure. Statistics updates cause plan recompilations if AUTO_UPDATE_STATISTICS is on. Updated statistics only mean that individual statements in a batch plan need an optimality ...


4

I'd have 2 separate queries/stored procedures just to avoid dynamic SQL. An IF statement in one stored proc would be enough too


4

You'd need to add IS_SPECIFIED (or ISSPECIFIED) to the where clause, as some hidden parameters may be set by... other things. A parameter can be removed from an spfile by issuing: ALTER SYSTEM RESET "_some_hidden_parameter" scope = spfile; You will likely have to stop and start the instance(s) to have the changes take effect.


4

He seems confused. Using the same value from a local variable should be no different to using it from an input parameter. Is your colleague getting the wrong end of the stick with regard to using parametrised queries (sometimes called prepared statements) instead of full ad-hoc SQL? For example: resultSet = dbconnector.getRS('EXEC sp_foo_GET \''+...


3

No, it can't be queried. These values are stored in sqlnet.ora on either or both of the client and the server. In either case, none of the contents of the network configuration files sqlnet.ora, tnsnames.ora, listener.ora etc (eg protocol.ora, snmp.ora, cman.ora) can be queried. If you really need to read the contents of these files and can't solve this ...


3

Yes it's the average size of each row in your table. row size * N rows = table size Row size = 83 B Rows = 22,000,001 Row Size times Number of rows: 83 * 22000001 = 1826000083 Convert result in Giga Bytes: 1826000083 / 1024 / 1024 / 1024 = 1.7 Total = 1.7 GiB Max


3

This ended up being related to parameter sniffing. It just so happened that some oddly formed versions of this query were being executed RIGHT AFTER the stats were rebuilt. So the cached plan was not representative of the majority of the calls. I used the trick of copying the date parameters to local variables and this is working just fine, with little to no ...


3

For input and output parameters, you can look at sys.parameters, sys.procedures and sys.types. Here is a procedure with various input/output parameters, alias types and even a TVP (2008+ only): USE [tempdb]; GO CREATE TYPE dbo.TVPType AS TABLE(id INT); GO CREATE PROCEDURE dbo.foobar @a INT, @b SYSNAME = N'foo', @c DATETIME = NULL, @d dbo....


3

You should be able to do that using the system view INFORMATION_SCHEMA.PARAMETERS. You'll have there what you need. It "returns one row for each parameter of a user-defined function or stored procedure that can be accessed by the current user in the current database. For functions, this view also returns one row with return value information." PS: if the ...


3

You can't. The return datatype must be specified in a function. It is not an optional part of the grammar. You can return sql_variant though.


3

To elaborate on gbn's answer, two sprocs is the better way to go here; additionally, I'd go one step further and place the query (sans top clause) in a table-valued function and then have the sprocs be nothing more than a shell which calls the TVF and applies the top clause appropriately (by number of rows or percent). The only caveat here being that if you ...


3

The documentation clearly describes the precedence for NLS settings: http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch3globenv.htm#NLSPG188 1 (highest) Explicitly set in SQL functions 2 Set by an ALTER SESSION statement 3 Set as an environment variable 4 Specified in the initialization parameter file 5 Default ...


3

Based on a simple test case I just wrote: @Test public void test() throws SQLException { PreparedStatement ps = conn.prepareStatement("SET ROLE ?"); ps.setString(1, "someuser"); ps.executeUpdate(); } I think the error you refer to is probably: org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1" Position: 10 at org....


3

Stored procedure parameters can only accept literals, constants or variables as parameters. It can't take expressions of any kind. You can say: EXEC dbo.myprocedure @foo = 'bar'; But you can't say: EXEC dbo.myprocedure @foo = 'b' + 'ar'; You should not be converting a date to a string and then back to a date again. Your dynamic SQL can easily take a ...


3

But why the recompile didn't help? Using OPTION (RECOMPILE) is not magic. It does often produce a better-performing execution plan (at the cost of a full compilation each time) because the query is compiled each time for the specific values of any parameters at the time, and also enables parameter embedding, which may produce further benefits. Determining ...


3

No, you would have to use separate parameter files/spfiles for each database. As you've pointed out, you'll need some settings to differ (locations of files, database name, etc.). In many environments, you'd want to adjust memory and other performance settings differently because the development environments often don't have nearly as much RAM as the ...


2

Looking at your use case, it looks as if you want a stored procedure, so here's one I wrote for you: CREATE PROC the_script ( @param1 int = null, @param2 int = null ) AS BEGIN select col_1, col_2, etc from table where cond1 > @param1 and cond2 < @param2 END Now, being in isql, you can run it: exec ...


2

Came across an interesting article this morning which adds the ever present "it depends" to the answer. See this post: Statistics and Recompilations by Erin Stellato ...for details, but it depends on settings for Auto Update Stats. If disabled apparently the answer is no, the query will not recompile. From Execution Plan Caching and Reuse on MSDN: ...



Only top voted, non community-wiki answers of a minimum length are eligible