Tag Info

Hot answers tagged

12

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.


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 ...


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

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 ...


5

"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 ...


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

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.


4

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 See MSDN, "Execution Plan Caching and Reuse" Now, it's unclear what your problem is, but do you have parameter sniffing ...


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


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

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

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.


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

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 ...


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

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 ...


2

Figured this out, it's easy, you just add :paramName in your query and it automatically asks your for the parameters when you run the query.


2

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 ...


2

Create a pfile from the spfile: CREATE PFILE FROM SPFILE; Edit the generated pfile and remove the parameters, then recreate the spfile from the edited pfile. Bounce the database & all should be well. The database might need to be down when you recreate the spfile from the pfile.


2

I did a little bit more research and found the answer to the problem. Even though the first parameter for DBMS_OUTPUT.PUT_LINE is listed as item in the 10g documentation for DBMS_OUTPUT. The actual name for the parameter is A. You need to bind the name using DBMS_OUTPUT.PUT_LINE(A => 'random text'). The actual parameter names for stored procedures can ...


2

You could create a function that executes SET ROLE with dynamic SQL, using format to safely insert the role identifier (%I inserts an identifier, placing double quotes around it if necessary, and escaping double quotes in the string by doubling them up if necessary). Something along the lines of CREATE FUNCTION setrole(role text) RETURNS void AS $$ BEGIN ...


2

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 ...


1

Table size is made up of 3 main factors Data Indexes Fragmentation Data size is simply a matter of row size by number of rows. Index size for MyISAM is dealt with here - (key_length+4)/0.67, summed over all keys). A great post explaining fragmentation is here and another good one is here. Fragmentation (indexes and actual data) increases the processing ...


1

I'm going to suggest working to the solution in two phases. The first part is to make sure all the data you need is being returned. The second part is to combine the rows the way that you want. I'm having trouble getting SqlFiddle to work with table types as well, so I'm going to work with the query directly. Here is the base query from above: ;WITH f AS ( ...


1

Typos, always typos. Here is the final working code: drop procedure if exists dividir_tabla_por_usuario; delimiter // create procedure dividir_tabla_por_usuario(tabla varchar(100), columna_usuario varchar(100), columna_fecha varchar(100)) begin set @i = 0; set @q1 = concat('select count(*) from (select distinct ', columna_usuario ,' from ', tabla , ' ...


1

If you really can limit to alphanumeric characters, then yes, that's fine, IF you are limiting to ANSI alphanumeric characters. In Unicode, because every character is more than one byte, many representations of alphanumeric characters are actually unsafe and could lead to injection. You will need to sanitize the data server-side and make sure the encoding ...


1

The normal way is to make sure that the various keywords from SQL aren't permitted in the string the user enters. However there are many cases to check. Quotations can be avoided by something like this: SELECT CHAR(97) + CHAR(100) + CHAR(109) + CHAR(105) + CHAR(110) Returns admin and the technique of adding characters together in that manner can be used ...


1

If you'll input "4.6, 4.7", it could construct sql-query, using IN predicate that will give you something like: where length IN(4.6, 4.7)


1

For an ad hoc batch, you need to type the date values and assign them to the variables: DECLARE @fromperiod varchar(8), @toperiod varchar(8); SELECT @fromperiod = '20130101', @toperiod = '20130131'; An ad hoc T-SQL batch can't prompt you; it's not interactive like VB in a Windows Form. You could do this by using the Debug button on the ...



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