Hot answers tagged

57

If you are using SQL Server you can use the REVERSE() function to check? SELECT CASE WHEN @string = REVERSE(@String) THEN 1 ELSE 0 END AS Palindrome; Including Martin Smith's comment, if you are on SQL Server 2012+ you can use the IIF() function: SELECT IIF(@string = REVERSE(@String),1,0) AS Palindrome;


54

Index names in PostgreSQL Index names are unique across a single database schema. Index names cannot be the same as any other index, (foreign) table, (materialized) view, sequence or user-defined composite type in the same schema. Two tables in the same schema cannot have an index of the same name. (Follows logically.) If you do not care about the name ...


38

Yes. Failing to specify WITH SCHEMABINDING means SQL Server skips the detailed checks it normally makes on the function body. It simply marks the function as accessing data (as mentioned in the link given in the question). This is a performance optimization. If it did not make this assumption, SQL Server would have to perform the detailed checks on every ...


23

It depends. With SQL functions (LANGUAGE sql), the answer is generally yes. Passed parameters are treated as values and SQL-injection is not possible - as long as you don't call unsafe functions from within and pass parameters. With PL/pgSQL functions (LANGUAGE plpgsql), the answer is normally yes. However, PL/pgSQL allows for dynamic SQL where passed ...


19

In general, procedures should not commit. Those sorts of transaction control decisions should be left to higher-level code that knows when a logical transaction is actually complete. If you commit inside of a stored procedure, you are limiting its reusability because a caller that wants the changes the procedure makes to be part of a larger transaction ...


18

It's ODBC syntax, and the engine knows what its own implementation is, and swaps it out, as you've seen in the execution plan. There are also things, like: SELECT {fn curdate()}, {ts '2016-05-24 15:19:36'}, -- not vulnerable to SET LANGUAGE! {guid 'D08891B4-BC25-4C7C-BAEF-3B756055AC6E'}; See the documentation here, here, and here, but please ...


18

As said in "40.5.3. Executing a Query with a Single-row Result" (emphasis mine): The result of a SQL command yielding a single row (possibly of multiple columns) can be assigned to a record variable, row-type variable, or list of scalar variables. This is done by writing the base SQL command and adding an INTO clause. So this should work: SELECT col1,...


17

Okay so this is what I've been able to find with some digging. For the legacy CE, I see the estimate is for 3.16228 % of the rows – and that is a “magic number” heuristic used for column = literal predicates (there are other heuristics based on predicate construction – but the LEN wrapped around the column for the legacy CE results matches this guess-...


17

Since there are a fair number of solutions I'm going to go with the "critique" part of your question. A couple of notes: I've fixed some typos and noted where I did. If I'm wrong about them being a typo mention it in the comments and I'll explain what's going on. I'm going to point out several things that you may already know, so please don't take offense ...


15

And nothing about the functions. Why is the function information missing in the actual plan? This is by design, for performance reasons. Functions that contain BEGIN and END in the definition create a new T-SQL stack frame for each input row. Put another way, the function body is executed separately for each input row. This single fact explains most ...


15

You could also use a Numbers table approach. If you don't already have an auxiliary numbers table you can create one as follows. This is populated with a million rows and so will be good for string lengths up to 2 million characters. CREATE TABLE dbo.Numbers (number int PRIMARY KEY); INSERT INTO dbo.Numbers (number) SELECT TOP 1000000 ...


14

A function attempts to return something, always, and has several restrictions - for example, you can not have any side effects, so you can't issue DML, call stored procedures, use dynamic SQL, call NEWID(), etc. You also cannot have error handling, transactions, or non-deterministic functions (e.g. GETDATE() in older versions, at least in SQL Server 2000 - ...


13

Quite simply: Null VaLue The function substitutes any NULLs in a given resultset column with the value given as the second parameter.


13

Two queries - two replies: a) Placing business logic to database has strong defenders and strong opponents. Lot of arguments for/against are volatile and valid only for some configurations and environment. Some databases has not good capabilities for stored procedural programming, some companies has not good personal resources for programming in relative ...


12

The way I prefer to do this: put the function in a utility database, and create a synonym to it in each regular database. This way you get the best of both worlds: there is only one copy of the object to maintain the developer doesn't have to provide three- or four-part names e.g. USE UtilityDB; GO CREATE FUNCTION dbo.LastIndexOf(...) ... GO USE otherDB;...


12

There is no way to define stored procedures or stored functions (or events) that are global. One approach is to create a shared common schema and then qualify the calls to the functions and procedures with the name of that schema (CALL shared.the_procedure();). This is something I do with my collection of custom date/time calculation functions (e.g., ...


11

You should only ever manipulate system catalogs directly, if you know exactly what you are doing. It may have unexpected side effects. Or you can corrupt the database (or the whole database cluster) beyond repair. @Jeremy's answer, while basically doing the trick, is not advisable for the general public. It unconditionally changes all functions in a schema. ...


11

There are two problems here: For Multistatement TVFs, you just need RETURN; instead of RETURN @variable;. It does not appear as though you can use a User-Defined Table Type (UDTT) as the return table type. That will need to be specified explicitly (i.e. each column name and datatype). If this were a Scalar UDF, then the syntax of specifying only the ...


11

The REVERSE() method "improved", i.e. reversing only half of the string: SELECT CASE WHEN RIGHT(@string, LEN(@string)/2) = REVERSE(LEFT(@string, LEN(@string)/2)) THEN 1 ELSE 0 END AS Palindrome; I don't expect anything weird to happen if the string has an odd number of characters; the middle character doesn't have to be ...


10

Using T-SQL scalar functions will frequently lead to performance problems* because SQL Server makes a separate function call (using a whole new T-SQL context) for each row. In addition, parallel execution is disallowed for the whole query. T-SQL scalar functions can also make it difficult to troubleshoot performance problems (whether those problems are ...


10

Is there an explanation for the cardinality estimate of 1.0003 for SQL 2014 while SQL 2012 estimates 31,622 rows? I think @Zane's answer covers this part pretty well. Is there a good workaround? You could try creating a Non-Persisted Computed Column for LEN(cust_nbr) and then create a Non-Clustered Index on that Computed Column. That should only take ...


9

It is true that you cannot grant EXEC permissions on a function that returns a table. This type of function is effectively more of a view than a function. You need to grant SELECT instead, e.g.: GRANT SELECT ON dbo.Table_Valued_Function TO [testuser]; So your script would look more like this (sorry, but I absolutely loathe INFORMATION_SCHEMA and much ...


9

\df *crypt in psql reveals the argument types of the pgcrypto encrypt and decrypt functions (as do the PgCrypto docs): List of functions Schema | Name | Result data type | Argument data types | Type --------+-----------------+------------------+--------------------------+-------- ... public | decrypt ...


9

You can do this: UPDATE table_name SET column=lower(column) Refer to www.postgresql.org/docs/9.1/static/functions-string.html


9

This is easy if you have a numbers table. The following example is cut and pasted from Erland Sommarskog's site: CREATE FUNCTION fixbinary_single(@str varbinary(MAX)) RETURNS TABLE AS RETURN(SELECT listpos = n.Number, n = convert(int, substring(@str, 4 * (n.Number - 1) + 1, 4)) FROM Numbers n WHERE n.Number <= datalength(@...


9

It is fairly well documented that UDFs force an overall serial plan. I'm not certain it is all that well documented :) A scalar T-SQL function indeed prevents parallelism anywhere in the plan. A scalar CLR function can be executed in parallel, so long as it does not access the database. A multi-statement table-valued T-SQL function forces a serial zone ...


9

Stop using REAL and use DECIMAL instead. REAL and FLOAT are approximate data types and can't represent all numbers in the nice, rounded way you are used to. Compare these: DECLARE @a REAL = 0.23, @b REAL = 0.24; SELECT AVG(r) FROM (SELECT r = @a UNION SELECT r = @b) AS x; Result: 0.234999999403954 Now using DECIMAL instead: DECLARE @a DECIMAL(10,4) = ...


9

A LANGUAGE sql function must be a complete SQL statement (or more than one). Yours is just an expression. Just prepend SELECT, like you would when running it stand-alone. $BODY$ SELECT (SELECT ... The reason it complains at the + is that, surprisingly, it's legal to parenthesise a top-level query. This, for example, is a valid query: regress=> (...


9

I was lying when I said, I'm not able to recode it into SQL. I was just too lazy. Here is the code with an example of usage. The Code is based on a TheiSen perl library, using QuickMedian. Let's define a new table type to easily pass our data to the procedure. CREATE TYPE dbo.TheilSenInputDataTableType AS TABLE ( ID INT IDENTITY(1,1), x REAL, ...


9

Do I need to create a store procedure first that way the inputs are recognized? You are missing @ symbols at the beginning of each parameter. Find out more about writing functions here Once you know the basics this article by Jeremiah Peschka explains the benefits of inline functions and much more. Basically if your table valued function is not ...



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