SQL Server does not cache the query results, but it caches the data pages it reads in memory. The data from these pages is then used to produce the query result. You can easily see if the data was read from memory or from disk by setting SET STATISTICS IO ON Which returns the following … Read more
MySQL allows comments on tables and rows. PostgreSQL does as well. From other answers, Oracle and MSSQL have comments too. For me, a combination of UML diagram for a quick refresher on field names, types, and constraints, and an external document (TeX, but could be any format) with extended description of everything database-related – special … Read more
You can check out this blog post. It had solved my problem. http://dotnetguts.blogspot.com/2010/06/restore-failed-for-server-restore.html Select @@Version It had given me following output Microsoft SQL Server 2005 – 9.00.4053.00 (Intel X86) May 26 2009 14:24:20 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 6.0 (Build 6002: Service Pack 2) You will need to re-install to … Read more
You don’t say what version of SQL Server. If SQL Server 2008 you can use MERGE NB: It is usual to use Merge for an Upsert which is what I originally thought the question was asking but it is valid without the WHEN MATCHED clause and just with a WHEN NOT MATCHED clause so does … Read more
In SQL the AND operator takes “precedence” over OR operator. PostgreSQL adheres to the spec here. You can the exact precedence in PostgreSQL in the docs Lexical Structure: Operator Precedence. So in your case, the result will be the same. However, it’s much easier, and cleaner to simply use the parentheses.
It sounds like you might need to create and query linked database servers in SQL Server At the moment you’ve created a query that’s going between different databases using a 3 part name mydatabase.dbo.mytable but you need to go up a level and use a 4 part name myserver.mydatabase.dbo.mytable, see this post on four part … Read more
There is an equivalent. It’s called a CASE statement. There are two forms of CASE: Simple CASE: CASE search-expression WHEN expression [, expression [ … ]] THEN statements [ WHEN expression [, expression [ … ]] THEN statements … ] [ ELSE statements ] END CASE; Searched CASE: CASE WHEN boolean-expression THEN statements [ WHEN … Read more
Generally speaking, for boolean or bit data types, you would use 0 or 1 like so: UPDATE tbl SET bitCol = 1 WHERE bitCol = 0 See also: Which MySQL data type to use for storing boolean values How do you create a yes/no boolean field in SQL server?
This generates a query with all columns ordered alphabetically in the select statement. DECLARE @QUERY VARCHAR(2000) DECLARE @TABLENAME VARCHAR(50) = ‘<YOU_TABLE>’ SET @QUERY = ‘SELECT ‘ SELECT @QUERY = @QUERY + Column_name + ‘, ‘ FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TABLENAME ORDER BY Column_name SET @QUERY = LEFT(@QUERY, LEN(@QUERY) – 4) + ‘ FROM ‘+ … Read more
Use the HSTORE column type. HSTORE stores key/value pairs. You can use null values if you only care about checking if a key exists. See https://www.postgresql.org/docs/current/static/hstore.html. For example, to ask Is ‘x’ in my hstore?, do CREATE EXTENSION HSTORE; –create extension only has to be done once SELECT * FROM ‘x=>null,y=>null,z=>null’::HSTORE ? ‘x’; I believe … Read more