Detect if value is number in MySQL
You can use Regular Expression too… it would be like: SELECT * FROM myTable WHERE col1 REGEXP ‘^[0-9]+$’; Reference: http://dev.mysql.com/doc/refman/5.1/en/regexp.html
sql
Multi-statement Table Valued Function vs Inline Table Valued Function
In researching Matt’s comment, I have revised my original statement. He is correct, there will be a difference in performance between an inline table valued function (ITVF) and a multi-statement table valued function (MSTVF) even if they both simply execute a SELECT statement. SQL Server will treat an ITVF somewhat like a VIEW in that … Read more
How do I use ROW_NUMBER()?
For the first question, why not just use? SELECT COUNT(*) FROM myTable to get the count. And for the second question, the primary key of the row is what should be used to identify a particular row. Don’t try and use the row number for that. If you returned Row_Number() in your main query, SELECT … Read more
Is SQL or even TSQL Turing Complete?
It turns out that SQL can be Turing Complete even without a true ‘scripting’ extension such as PL/SQL or PSM (which are designed to be true programming languages, so that’s kinda cheating). In this set of slides Andrew Gierth proves that with CTE and Windowing SQL is Turing Complete, by constructing a cyclic tag system, … Read more
Effect of NOLOCK hint in SELECT statements
1) Yes, a select with NOLOCK will complete faster than a normal select. 2) Yes, a select with NOLOCK will allow other queries against the effected table to complete faster than a normal select. Why would this be? NOLOCK typically (depending on your DB engine) means give me your data, and I don’t care what … Read more
Saving results with headers in SQL Server Management Studio
Tools > Options > Query Results > SQL Server > Results to Text (or Grid if you want) > Include columns headers in the result set You might need to close and reopen SSMS after changing this option. On the SQL Editor Toolbar you can select save to file without having to restart SSMS
Return 0 if field is null in MySQL
Use IFNULL: IFNULL(expr1, 0) From the documentation: If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2. IFNULL() returns a numeric or string value, depending on the context in which it is used.
How to check which locks are held on a table
This is not exactly showing you which rows are locked, but this may helpful to you. You can check which statements are blocked by running this: select cmd,* from sys.sysprocesses where blocked > 0 It will also tell you what each block is waiting on. So you can trace that all the way up to … Read more
“where 1=1” statement [duplicate]
It’s usually when folks build up SQL statements. When you add and value = “Toyota” you don’t have to worry about whether there is a condition before or just WHERE. The optimiser should ignore it No magic, just practical Example Code: commandText = “select * from car_table where 1=1”; if (modelYear <> 0) commandText += … Read more
Check if MySQL table exists without using “select from” syntax?
If you want to be correct, use INFORMATION_SCHEMA. SELECT * FROM information_schema.tables WHERE table_schema=”yourdb” AND table_name=”testtable” LIMIT 1; Alternatively, you can use SHOW TABLES SHOW TABLES LIKE ‘yourtable’; If there is a row in the resultset, table exists.