Can I provide a default for a left outer join? Suppose I have tables a (with column a1) and b (with columns b1 and b2) and I perform a left outer join

SELECT *
FROM a LEFT OUTER JOIN b
ON a.a1 = b.b1

Then b1 and b2 will be NULL where a value of a1 has no matching value of b1.

Can I provide a default value for b2, instead of NULL?  Note that COALESCE won't work here, because I don't want the default value to override potential NULLs in b2 where there is a value of b1 matching a1.

That is, with a and b as

CREATE TABLE a (a1)
  AS VALUES (1),
            (2),
            (3) ;

CREATE TABLE b (b1,b2)
  AS VALUES (1, 10),
            (3, null) ;

a1     b1 | b2
---    --------
 1      1 | 10
 2      3 | NULL
 3

and a default for b2 of, say, 100, I want to get the result

a1 | b1   | b2
---------------
1  |  1   | 10
2  | NULL | 100
3  |  3   | NULL

In this simple case I could do it "by hand" by looking at whether b1 is NULL in the output.  Is that the best option in general, or is there a more standard and neater way?

SELECT a.a1,b.b1,
    CASE WHEN b.b1 is NULL THEN 5 ELSE b.b2 END AS b2
FROM a LEFT OUTER JOIN b
ON a.a1 = b.b1

OP has asked for an alternative to using the typical case / coalesce / ifnull method for replacing null values by specifying a different 'default value' for left joins instead of null. This is a valid request as it may be cumbersome if the field is used repeatedly or within complex calculations within the query or you want to differentiate between the 'row doesnt exist in joined table' signal and 'row exists but that field is null' result. The correct answer here is "no, that feature does not exist".


MS SQL Server 2000 ODBC driver for Linux Microsoft has official ODBC drivers for MS SQL Server, for Linux. Great! Nice installation instructions for all the major distros, too.

Unfortunately, their ODBC drivers, going back to the oldest version I can find only support SQL Server 2005 and newer:

  Native Data Access APIs – ODBC and OLE DB – for SQL Server 2005 and later.

I need to connect to a very old SQL Server (2000):

SELECT @@VERSION
→
Microsoft SQL Server  2000 - 8.00.2039 (Intel X86)
       May  3 2005 23:18:38
       Copyright (c) 1988-2003 Microsoft Corporation
       Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

I've confirmed that the v13 driver does not work:

$ sqlcmd -U XXXX -S 10.1.2.3
Password:
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : ODBC Driver 13 for SQL Server does not support connections to SQL Server 2000 or earlier versions..
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Client unable to establish connection.

—
Is there an official MS SQL ODBC driver that will run on a modern 64-bit Linux and connect to SQL Server 2000?

Or do I need to go to third party drivers like the open-source FreeTDS?

@Dan, this is by design. SQL Server 2005 is not longer supported. Newly released client drivers support SQL Server 2005+.

That said, can you try this?

wget https://gallery.technet.microsoft.com/scriptcenter/SQLCMD-and-BCP-for-Ubuntu-c88a28cc/file/142121/1/Ubuntu%2014.04%20MSFT%20ODBC%20Driver.sh
sudo bash Ubuntu\ 14.04\ MSFT\ ODBC\ Driver.sh
sqlcmd –S yourServerName.database.windows.net – U yourUsername – P yourPassword

These steps install the older version (ODBC Driver v11).

If the native driver doesn't support this, you should look at our sql server driver. We do support sql server 2000 version and unlike the open source, this is being used by many enterprise and we also have the expertise to troubleshoot any kind of issues. You can download it at: https://www.progress.com/odbc/microsoft-sql-server


What determines whether `pg_dump` includes a varchar length? I work on a project where we regularly dump our Postgres database schema and commit it in the project repository. However, depending on which developer dumps the schema (via pg_dump), varchar columns may be given without a length:

email character varying NOT NULL

or with a length:

email character varying(255) NOT NULL

Is there a configuration option that determines whether the length is included in the schema dump?

From the docco here, it says that

  The notations varchar(n) and char(n) are aliases for character
  varying(n) and character(n), respectively. character without length
  specifier is equivalent to character(1). If character varying is
  used without length specifier, the type accepts strings of any size.
  The latter is a PostgreSQL extension.

Note the bit in bold. Your email fields with CHARACTER VARYING without a length are like TEXT fields. This is not good practice - you really only should set fields to this if you really don't know the longest possible value. From the correct answer to this thread, it's 254 characters. This area is treated well in this thread (also check out the link within the correct answer).

[EDIT]

Of course, as @DanielVérité points out, these are not the same fields from the same table - my answer was predicated on the first being the email in table_x and the second being the email in table_y. If they're meant to be the same field in the same table, someone has made a mistake, perhaps not huge, but there's a problem.

It's better to have CHARACTER VARYING(254) or VARCHAR(254) for all email fields. That way, if you attempt to put more than 254 characters into the email field, PostgreSQL will throw an error:

test=# create table x(y varchar(4));
CREATE TABLE
test=# insert into x values('sadfsadfa');
ERROR:  value too long for type character varying(4)
test=# select * from x;
 y
---
(0 rows)

test=#


What is the best way to get a random ordering? I have a query where I want the resulting records to be ordered randomly. It uses a clustered index, so if I do not include an order by it will likely return records in the order of that index. How can I ensure a random row order?

I understand that it will likely not be "truly" random, pseudo-random is good enough for my needs.

ORDER BY NEWID() will sort the records randomly. An example here

SELECT *
FROM Northwind..Orders
ORDER BY NEWID()

This is an old thread but came across this recently; so updating a method that has worked for me and gives good performance. This assumes your table has an IDENTITY or similar column:

DECLARE @r decimal(8,6) = rand()
SELECT @r

SELECT  TOP 100 *
FROM    TableA
ORDER BY ID % @r


Can I emulate Oracle's autonomous transaction in MySQL? I have a number of tables that have triggers on them that insert records in queueing and logging tables. The problem is that those inserts are then part of the same transaction. That means that any IUD on the former tables, can be locked by other IUD that trigger inserts into the same queue, and that IUD that fails will not be logged, since those inserts will be rolled back as well.

I don't care much for entries in the queue where the triggering IUD was rolled back; the queue is read by some process that invalidates / refreshes cache and similar actions. Should anything have been rolled back, some cache will be refreshed with the same data. No big deal.
The business case here is a website with a great number of sets that consist of single products. Any one product can be in up to 15,000 sets. If the price of a product changes, the prices of all those sets need to be recalculated. I don't want to do that on the same transaction when saving the price of a product, because that would take too much time and lock too much of the database, so I enqueue the recalculation.

For logging, there are some actions I want to log, even (especially) if they fail. I can't do that now.

Were this Oracle, which I used until recently, I would've used PRAGMA AUTONOMOUS_TRANSACTION to handle the insert in a separate transaction, that would always commit no matter what the other transaction did.

Can I emulate this behaviour by using MyISAM tables for the logs and the queues, with concurrent_insert set to ALWAYS, while the rest of my tables are InnoDB? (I expect multiple inserts into them, that's the whole issue, while they're seldom read (logs), or only once (queues)).

If you have access to the Aria engine or another non-transactional engine, then yes.
Very short proof of concept, tested on MariaDB 10.5.4:
CREATE TABLE test_aria_engine ( a int, b varchar(1000) )
ENGINE=Aria DEFAULT CHARSET=utf8 PAGE_CHECKSUM=1 TRANSACTIONAL=1 TABLE_CHECKSUM=1
COMMENT='test-aria_engine with transactional on 1'

The transactional in Aria is not for real transactions, it is only last operation log, still good in case of server crash the data is consistent.
Now create an InnoDB table:
CREATE TABLE test_innodb_engine ( a int , b varchar(1000) )

And test:
BEGIN TRANSACTION;

-- insert in aria table
INSERT INTO test_aria_engine ( a, b) VALUES (4 , 'ddd');

-- insert in innodb table
INSERT INTO test_innodb_engine( a, b) VALUES ( 4 , 'dddd');

-- rollback
ROLLBACK;

After the ROLLBACK has been initiated let's check the table contents of the Aria and InnoDB tables:
SELECT * FROM test_aria_engine;

|a  |b  |
|---|---|
|4  |ddd|

So, ROLLBACK cannot rollback ARIA engine
SELECT * FROM test_innodb_table;

No result.

So in the InnoDB table ROLLBACK has done its job.
This shows how to avoid transaction logic in MariaDB or using a similar engine like MyISAM in MySQL.

If possible, listen to the mantra:  "Don't queue it, just do it."

Otherwise, keep queuing and normalization code outside of the main transactions.

As for logging, I don't think there is a solution other than writing to a MyISAM table for your log.

Back to your question...

There is no way in MySQL to have nested, independent, transactions on the same connection.  Two connections would allow for it, but that could be messy for other reasons.


Switch to InnoDB, and

Set innodb_buffer_pool_size to at least 250M
Show us the tentative SELECTs
SHOW CREATE TABLE (DESC is inadequate)

For Such Projects..and scenarios where Write is more than reads, and you need to handle data in large scale, you should go for NoSQL, Like MongoDB, CouchDB or Hbase etc.


SSMS not coloring tables, columns and variables I use SSMS 2012 v11.0.5058.0.

MSDN says "System table, view, or table-valued function" should have green color. I'm used to have green color in variables and a few other token types too. But my SSMS keeps them black as raw values.

I followed Change Font Color, Size, and Style and tried to set default, but it didn't solve. I also tried to set them manually but didn't find them to select.

Any idea what may be the issue?

This happens in both cases of opening an .sql file or having SSMS create a select query for a table. Even when there are [] around the column name. It remains even after restarting Windows.

Colors are controlled via the options dialog box, which you linked to in your question.

Check the following, to see what it shows in the "Sample" box:

Are you sure that you have intellisense enabled?
You can see general information about it under Tools -> Options. I'd check here for more info.


extended events rpc_completed duration does not matching with rpc_starting Does rpc_completed duration (micro_sec) = rpc_starting.timestamp - rpc_completed.timestamp?
In my case, I saw a big difference between them. Duration is only few millsec, where rpc_starting.timestamp - rpc_completed.timestamp is 6 - 10 sec.

I am on enterprise 2016 sp2-cu14, the duration field is in microsecond. I did similar thing for catching sp_statement_starting and sp_statement_completed, sp_batch_starting/sp_batch_completed inside rpc_starting and rpc_completed. it shows the same situation. My question is "which one tell me how much time the query actually took? Is it Duration in sp_statement_completed or the difference between starting and completed?"

My question is "which one tell me how much time the query actually took? Is it Duration in sp_statement_completed or the difference between starting and completed?"

"Duration" in the sp_statement_completed event is how long the query actually took at the server.  It doesn't include the time spent sending results to the client (usually, see here for details).
I'm not 100% sure why the Extended Events timestamp column shows such a large discrepancy between the "starting" and "completed" events, but that's not where you want to focus your attention.

I don't believe the duration column represents the difference in time of the timestamp_UTC column between the rpc_starting and rpc_completed events. Assuming you're on any version of SQL Server still relevant (2008 R2+) that column is in units of microseconds (anything older version and it's in milliseconds), and therefore is a very small value in your examples (e.g. 1,872 microseconds = 0.00187 seconds).
My guess based on Microsoft's description is that is literally how long it took that particular event to occur and be recorded.


What are we Doing when we Connect to SQL Server? My apologies if this is simple, or off-topic for this site:
  I just wanted to know what goes on in the background when one connects to
SQL Server, say, within Management Studio. Just what is it we are connecting to,
is it a virtual machine, or some server somewhere?
Thanks for any explanations, refs. etc.

Usually, you are making a TCP connection to a remote host.

SSMS would ask your DNS server for the IP address associated with the hostname of the server, then initiate a TCP connection to that address on remote port 1433.

SSMS and MS SQL Server would then begin a conversation using the binary Tabular Data Stream protocol (TDS).

You type a query. SSMS encodes this query into TDS. SQL Server gets it, parses it, runs it, returns some data (encoded in TDS), back to SSMS, which decodes it and displays it on screen.

However, you can also connect to SQL Server installed on the same OS as SSMS, or running in a virtual machine on the same box.

In simple terms you connect to a Sql Server instance which runs on a Windows machine. This instance may be hosted on the same machine from where you are accessing it or on a remote windows server.


azure sql - create index, does DB have to be offline? New to Azure SQL. Simple example to ask question.
Table Cars.
Want to add an non clustered index to Cars table.
Takes a long time, say 30 min during staging test deploy.

When upgrading production Azure SQL, can we leave the database online while building a table index? During this time while the table is building, can applications send inserts/updates to this table?

Or do we need to take the production DB offline for 30 to build this index?

You should absolutely be able to access the table while the index builds. Now, depending on your service tier in Azure, you might be using a lot of your resources to build this index, and that might impact user access, but otherwise, no. The index will build, but access to the table will continue.

You can consider take advantage of the resumable online index creation feature to distribute the time needed to create the index over many hours, assigning one or two minutes (or X seconds) every X minutes of time to create the index. For more information, please read this resource.

ALTER INDEX test_idx on test_table PAUSE

ALTER INDEX test_idx on test_table RESUME


Does any DBMS have a collation that is both case-sensitive and accent-insensitive? Note this question is vendor/version agnostic

It seems to me, as a speaker (typist, writer) of English, reasonable to expect words to be properly cased but not necessarily have the correct accents going in the right direction:

  as I mused in a tete-a-tete with Chloe the maitre d'hotel at the Champs-Elysees restaurant, while waiting for the garcon to fetch my sauted jalapeno pate...

You get the idea with that.

So today I thought I wanted a search condition to use a case-sensitive but accent-insensitive collation but couldn't find one. Is there a good reason for this or is  mine merely a rare use case?

Here's an example of some documentation I was looking at (though thinking vendor/version agnostic):

SQL Server Collation Name (SQL Server 2008 R2)

TL;DR

There is no such thing as a "vendor-agnostic" view of Collations, nor even "version-agnostic", since their implementations -- including which aspects can be made insensitive and their naming conventions -- are vendor-specific and change over time.

Here is a summary of what I have found, and the details are in the longer section below the line:

RDBMS        Naming-             Combinations    Case-Sensitive and
             convention          of options?     Accent-Insensitive support?
-------      ------------        -------------   -----
SQL Server   _CS, _AI, etc       Yes             Latin1_General_100_CS_AI

DB2          _E{x}, _S{y}, etc   Yes             CLDR181_EO_S1

PostgreSQL   locale: en_US       N/A             unaccent(), not via Collation

MySQL        _cs, maybe _ai      No              No: _cs implies _as & _ci implies _ai
                                                 Yes? Create your own Collation :-)

Oracle       only _CI & _AI      No              No: _AI always implies _CI

SAP ASE      arbitrary: turdict  N/A             No: "AI" always implies "CI"

Informix     locale.codepage     N/A             No: no "AI" via Collations

As you can see in the chart, two of the seven RDBMSs do natively support "Case-sensitive and Accent-insensitive" operations via Collations, though they have different naming conventions (and several other functional differences).

One RDBMS -- PostgreSQL -- doesn't natively support this combination, but you can still achieve it by stripping off the accents with the unaccent() add-on function.

The last four RDBMSs, two of which have a similar naming convention for the options, neither natively support this combination nor does there appear to be a means of accomplishing this without writing your own function to remove the accents / diacritical marks. MySQL allows for creating your own Collations, but that requires that you then add it to source control and incorporate it into your testing & deployment process so that it can be applied to all servers in all environments (but still a very cool and flexible option). SAP ASE mentions that SAP can supply additional Unicode sort orders, but no mention of what they might be willing to supply.

With regards to:

  Is there a good reason for this or is mine merely a rare use case?

I can say that in doing the research for this answer I came across a lot of instances of people wanting case-insensitive and accent-sensitive for MySQL, but few, if any, asking for your desired combination.

  I wanted a search condition to use a case-sensitive but accent-insensitive collation but couldn't find one.
  ...
  this question is vendor/version agnostic

You were unsuccessful in your search because it doesn't really make sense to look for an RDBMS based on a Collation specification. That's just not how Collations work. And while you are wanting to approach this as vendor-agnostic, reality is that Collations -- at least the part that we interact with -- are very much vendor-specific, and don't always fit into the scheme that you were searching on.

String comparison and sorting is highly complex, and there are different ways of performing these rules. One method is to have mappings that take into account one or more rules. Hence the four combinations of Sensitive and Insensitive for Case and Accents would equate to four separate mappings. For example, you saw this on that MSDN page for SQL Server Collation Name. If you scroll down, you will see that the left column of the chart is the Sort Order ID. Each Collation has a different ID: SQL_Latin1_General_Cp1_CI_AS = 52 while SQL_Latin1_General_Cp1_CS_AS = 51, even though the only difference is in the case-sensitivity.

Or, it can be rule-based, such as what Unicode offers through the Unicode Collation Algorithm (UCA). In this approach, every character is given, by default, one or more weights. Then, each culture / locale has the option to override any of those weights, or remove rules, or add rules. The algorithm takes into account any locale-specific rules, and then potentially manipulates those weights based on any options chosen (sensitivity, which case comes first when doing case-sensitive sorts, etc). This is one reason why doing Unicode sorting is a bit slower than non-Unicode sorting.

To get a sense of how many options there really are (i.e. the actual complexity), check out this demo from the ICU (International Components for Unicode) project:

ICU Collation Demo

There are 8 separate options to specify, and some of them get represented in multiple elements of the Collation name specification that you are thinking of (e.g. CS, CI, AS, AI, etc). Given how many variations there are, using the mapping file approach where each combination has its own ID would result in many thousands of files. Many of those files would need to get updated whenever there are changes in those particular languages, or when bugs are found. This is probably why there are only 75 of those type of Collations in SQL Server 2012 (i.e. those with names starting with SQL_). Hence no combination for _CS_AI.

And the reason why you couldn't find that combination for the UCA-based Collations? Well, there are 3810 Collations in SQL Server 2012 that do not begin with SQL_, so 3885 Collations total. That list seems to be too long to be enumerated fully on a web page. But this doesn't fully explain why you couldn't find this combination for other vendors.

Beyond what has already been mentioned (i.e. too many combinations to implement, and too many implementations to list), you still need to contend with vendor-specific implementations. Meaning: not all vendors allow for tailoring all of those options, and there is no standard naming convention for Collations in the first place. Also, not all vendors view the sorting options as being a part of the Collation: PostgreSQL Collations are default ordering for the chosen locale, and you need to use ILIKE to get a case-insensitive comparison. See below for vendor-specific info.

SQL Server (Microsoft)

The distinction between what you are seeing on those two MSDN documentation pages and the query provided by @MartinSmith in a comment on the question (slightly revised below):

SELECT *
FROM   sys.fn_helpcollations()
WHERE  [name] LIKE '%[_]CS[_]AI%';

is that those two MSDN pages are referring specifically to the very deprecated SQL Server Collations, whereas the collations that show up as a result of that query (888 of them as of SQL Server 2012, SP3) are Windows Collations.

Starting in SQL Server 2000, the older SQL Server Collations (created prior to SQL Server being able to tap into the Windows Collations) are deprecated and are not being updated with new rules or functionality. For example, starting in SQL Server 2012, a set of Collations was added that support the proper handling of the built-in functions for Supplementary Characters (i.e. the remaining UTF-16 characters beyond the "base" 65,536 characters initially defined in UCS-2). These newer Collations end in _SC (as in Supplementary Characters).

It is best to not use the SQL Server Collations -- those with names starting with SQL_. Hence you do have access to plenty of Collations that support the combination of options that you are looking for (i.e. Case-Sensitive and Accent-Insensitive). Whenever available, it is also best to use the one end in _SC as long as it has all of the other options you are wanting.

While SQL Server does use the _CS_AI naming convention, there is no list of all 3810 (as of SQL Server 2012) Windows Collations. There is just the Windows Collation Name page that lists all of the locales and versions, and how the naming convention works, but that's it.

SQL Server also supports toggling both Width and Kana sensitivity.

MySQL (bought by Oracle)

The MySQL version 5.7, documentation states that it does support the _ai, _as, _ci, and _cs suffixes (and _bin for completeness), but also states:

  For nonbinary collation names that do not specify accent sensitivity, it is determined by case sensitivity. That is, if a collation name does not contain _ai or _as, _ci in the name implies _ai and _cs in the name implies _as.

  For example, latin1_general_ci is case insensitive (and accent insensitive, implicitly), latin1_general_cs is case sensitive (and accent sensitive, implicitly)

This certainly implies that it is possible to have a latin1_general_cs_ai Collation. However, the MySQL 5.5.50 server that I have access to does not have any collations with more than one suffix, and the only suffixes I see are: _cs, _ci, and _bin across 198 total Collations. I used the SHOW COLLATION command to list them.

So, while it sounds like MySQL uses a similar naming convention (at least as far as those two options go), I cannot find a Collation matching what you are looking for. However, it might be possible to strip off the accents (and other diacritical marks) and use a _cs collation to get what you want (similar to how you would do it in PostgreSQL -- see below). But I am not sure of this option and do not have time at the moment to research further.

OR, you might could create your very own Collation to do exactly what you want. Unlike the other RDBMSs, MySQL appears to make it rather simple to add your own Collations, in which case you are in full control over the weighting of each character. Please see Adding a Simple Collation to an 8-Bit Character Set and Adding a UCA Collation to a Unicode Character Set for more details.

For more info on how MySQL handles different types of Collations, please see their Collation Implementation Types page.

PostgreSQL

Collations in PostgreSQL seem to be far less flexible. You specify only the culture / locale: en_US, de_DE, etc. Please see their documentation page for Collation Support for details. Hence, by default you get the culture-specific overrides, but the Collations are otherwise everything-sensitive (which, by the way, is not the same as a "binary" collation).

You can use ILIKE (section 9.7.1) to get case insensitivity, but they do not have a similar operator for accent sensitivity. However, I found that they do have an unaccent function that can be used to strip off accents and other diacritical marks. Please note that this function is an Additional Supplied Module and hence is not necessarily present in any particular PostgreSQL server to use. That most recently linked documentation states:

  When building from the source distribution, these components are not built automatically, unless you build the "world" target
  ...
  If you are using a pre-packaged version of PostgreSQL, these modules are typically made available as a separate subpackage, such as postgresql-contrib.

Please see that documentation for instructions on how to get that function if you don't have it and want it.

More information can also be found in the following Stack Overflow answer:

Does PostgreSQL support “accent insensitive” collations?

DB2 (IBM)

Similar to Microsoft SQL Server, DB2 has two types of Collations:

"SYSTEM" Collations, which are specified using the following format: SYSTEM_{codepage}_[optional-territory]. These are not very flexible, and do not appear to support tailoring sensitivity to case, accents, or anything. You can find the list of supported Collations here: Supported territory codes and code pages
Unicode Collation Algorithm (UCA)-based Collations. These do support quite a bit of tailoring. Please see their Unicode Collation Algorithm based collations page for details about how to configure the behavior, the naming convention, and the list of valid locales. Please note that in Table 1, the example in the third row ("Case Level") starts with:

  Setting the Case Level attribute to on and the Strength attribute to primary level will ignore accent but not case.

That is exactly what you were looking for. But, the syntax for that is:
CLDR181_EO_S1. And this is why your searching did not find anything related to DB2.

Oracle

Oracle 10g added support for doing accent insensitive comparisons and sorting. However:

they only have the options to denote "insensitive" operations: _CI and _AI
you can only specify one of those options at a time
the case-insensitive option -- _CI -- is still accent sensitive
the accent-insensitive option -- _AI -- "is always case-insensitive as well." (quoted from their documentation which is linked below)

Please see their Linguistic Sorting and String Searching documentation page for more details and examples.

SAP ASE (formerly Sybase ASE, a.k.a. Sybase)

ASE supports one or more of the following combinations of sensitivities per each locale / character set:

case-sensitive, accent-sensitive
case-insensitive, accent-sensitive
case-insensitive, accent-sensitive, order with preference
case-insensitive, accent-insensitive

You can see the relationship between locale, character set, and available sort orders on their Selecting the Default Sort Order page. And you can see the full list of Collations on their Collation Names and IDs page.

Their Collation naming convention is arbitrary in that they are all 4 - 8 characters and try to capture the locale name or code page and and some sense of the sorting. For example:

altnoacc == "CP 850 Alternative – no accent"
rusdict == "Russian dictionary ordering"
dynix == "Chinese phonetic ordering"

There is a note on their Selecting the Default Unicode Sort Order page stating:

  You can add sort orders using external files in the $/collate/Unicode directory. The names and collation IDs are stored in syscharsets. The names of external Unicode sort orders do not have to be in syscharsets before you can set the default Unicode sort order.
  ...
  External Unicode sort orders are provided by SAP. Do not attempt to create external Unicode sort orders.

It is unclear whether or not SAP would supply an external sort order to allow for Case-Sensitive and Accent-Insensitive. Maybe someday I will email them and ask if one could be requested.

In order to get the desired combination of sensitivities, you should be able to create a scalar user-defined function to strip off accents and other diacritical marks.

Informix (bought by IBM)

Informix appears to mostly just support the default sorting and comparison behavior of a Collation. Hence Collations are just the locale and character set. Case-sensitivity is handled at the database-level, and by default they are case-sensitive. You can set a database (not a table, or a column, or a query, or even a predicate) to be case-insensitive by specifying NLSCASE INSENSITIVE in the CREATE DATABASE statement.

While the database Collation -- locale and character set -- can be overridden per client connection, there does not seem to be a way to override the case-sensitivity setting. AND, the NLSCASE option has "NLS" in the name for a reason: it only affects NCHAR and NVARCHAR data; CHAR and VARCHAR are always case-sensitive.

Accent-sensitivity is not addressed, nor is there a built-in function to strip of the accents / diacritical marks.

The Informix Collation naming convention is:

<lang>_<country>.<code set>

where:

<lang> = a 2-letter or 3-letter language code
<country> = a 2-letter country or region code
<code set> = the code page specified in one of the 3 following equivalent ways:

name: 8859-1
decimal value of IBM CCSID number: 819
hexadecimal value of IBM CCSID number: 0333

Hence, the following three locale specifications all refer to the exact same locale:

fr_fr.8859-1
fr_fr.819
fr_fr.0333

For more information, please see:

User Guide for IBM Informix International Language Supplement
The IBM Informix registry file (Windows)

Option Name Description
NLS_LANG    The current language, territory, and database character set, which are determined by session-wide globalization parameters.
NLS_LANGUAGE    The current language for the session.
NLS_SORT    The sequence of character values used when sorting or comparing text.

To check the current NLS settings, type:

select * from v$NLS_PARAMETERS;


How many rows will be locked by SELECT … ORDER BY xxx LIMIT 1 FOR UPDATE? As was asked on stackoverflow but then for MySQL, I was wondering how this works in PostgreSQL. How many rows get locked when I do a "FOR UPDATE" in combination with a "LIMIT", a "ORDER BY" and some "WHERE"'s. And they update the returned row. I was hoping the "FOR UPDATE"-query would only lock ONE row but maybe I miss some implementations issue. (I'm planning to write some stress-test to reproduce this more reliably)

These queries cause a deadlock in my system:

(session1) select field1, field2, ... from documents where transaction_path='some/path' for update
(session2) select field1, field2, ... from documents where (transaction_path is null) and queue_id=2 and next_pickup_ts<now() order by next_pickup_ts limit 1 for update
(session3) select field1, field2, ... from documents where (transaction_path is null) and queue_id=2 and next_pickup_ts<now() order by next_pickup_ts limit 1 for update

But I do not understand why. (There is a unique index on transaction_path.)

If you use EXPLAIN you'll see the ordering. For a random plan I concocted with some throwaway data and similar query (ORDER BY ... LIMIT 1 FOR UPDATE) I got the plan:

 Limit  (cost=33.09..33.10 rows=1 width=26)
   ->  LockRows  (cost=33.09..39.88 rows=543 width=26)
         ->  Sort  (cost=33.09..34.45 rows=543 width=26)
               Sort Key: t_id
               ->  Seq Scan on m  (cost=0.00..30.38 rows=543 width=26)
                     Filter: (b_id <= 33)
(6 rows)

Here, you can see the rows are potentially locked before the limit is applied.

Now, in practice, the first row that gets matched will generally get locked and returned, causing LockRows on the later rows to get skipped ... but there's no guarantee of this. So you shouldn't rely on it.

In general, mixing row-locking and LIMIT is a bad idea that should generally be avoided.

If you must do it, you'll want to do something like:

SELECT *
FROM my_table
WHERE id = (SELECT id FROM my_table
            WHERE mywhereclause
            ORDER BY ... LIMIT 1)
  AND mywhereclause
FOR UPDATE;

where you force a target row to be found, and only then locked.

You should repeat the WHERE clause in the outer query to ensure that the row still matches the predicate after any lock-wait occurs. Otherwise what can happen is that the inner query finds the row, returns the ID, and you try to lock the row with that ID. PostgreSQL sees that someone else has a lock on that row, so it waits for that lock to be released. Then it re-checks the WHERE clause on the outer query to make sure it still matches (in case the row was deleted, or was UPDATEd). Because the inner query is an uncorrelated subquery it won't re-calculate it, though, so the inner WHERE clause doesn't get re-run, and you can get rows that no longer match the inner WHERE clause returned if you don't repeat it in the outer WHERE clause.

Mixing row limits and row locking is hard. Building correct queuing systems that actually perform better than a single worker is even harder. Use an off the shelf queuing system and save yourself a lot of hassle.

(PostgreSQL 9.5 will have SKIP LOCKED, which makes this way easier, but there won't be a stable release of it for over a year from now, so don't hold your breath.)

Postgres will lock only 1 record that return from LIMIT clause.
The reason other concurrent query has to wait because the SELECT ... WHERE ... LIMIT 1 return the same record, that being locked by the first query in other transaction, so It is blocked and waiting.
Correct solution is using SKIP LOCKED clause after FOR UPDATE clause in query, no need to have subquery.


unable to add index to column, duplicate entry I have a table that collects virtual machines usage metrics, I'm trying to add an index to timestamp column, to speed up queries, when selecting time, this is a sample of the table I have, it has other columns, I have only listed 3 here.

    id  timestamp            vm_name
------  -------------------  ---------
     1  2015-05-05 18:21:50  i-2-20-VM
     2  2015-05-05 18:21:50  i-2-21-VM
     3  2015-05-05 18:21:50  i-2-22-VM
     4  2015-05-05 18:21:50  i-2-23-VM
     5  2015-05-05 18:21:50  i-2-24-VM
     6  2015-05-05 18:21:50  i-2-25-VM
     7  2015-05-05 18:21:50  i-2-26-VM
     8  2015-05-05 18:21:50  i-2-27-VM
     9  2015-05-05 18:21:50  i-2-28-VM
    10  2015-05-05 18:21:50  i-2-29-VM

As you can see, my application is inserting duplicate timestamp entry.

When attempting to add an index to timestamp column, I get the following error message.

Duplicate entry '2015-05-05 18:21:50' for key 'PRIMARY'

I'm not sure what do I need to change in my DB design to resolve this issue.

Your suggestions are highly appreciate it.

I think you are trying to create a primary key and not a simple index. (Primary key requires all values to be unique)

To add a index in mysql follow the syntax here: https://dev.mysql.com/doc/refman/5.7/en/create-index.html

You have to remove all the duplicates prior to create the UNIQUE or PRIMARY (that is also UNIQUE in fact)  index. In your case that can be performed via joining the table with itself:

DELETE z.*
  FROM table AS w
  JOIN table AS z
    ON z.vm_name = w.vm_name
   AND z.timestamp = w.timestamp
   AND z.id > w.id
;

Then you have to create multicolumn primary index (vm_name, timestamp) instead of (timestamp).


List all columns for a specified table I'm looking for a precise piece of information in a database which I have no knowledge about. The database is on a separate machine, but I can log into it, and launch a psql command line, with administrator rights.
It's a third-party product, and they are slow to answer questions. I know the data is inside that database, so I want to do a little bit of reverse-engineering.
Given a table name, is it possible to get a list of the names of the columns in that table?
For example, in SQL Server, it's possible to dump a table into a reusable CREATE statement, which textually lists all the columns the table is composed of.

In addition to the command line \d+ <table_name> you already found, you could also use the Information Schema to look up the column data, using information_schema.columns:

SELECT *
  FROM information_schema.columns
 WHERE table_schema = 'your_schema'
   AND table_name   = 'your_table'
     ;

Note: As per the example above, make sure the values are enclosed within quotes.

PostgreSQL only

This is somewhat hokey but could be a contender if you are looking for the shortest possible SQL:

SELECT json_object_keys(to_json(json_populate_record(NULL::schema_name.table_name, '{}'::JSON)))

or even shorter (assuming there is at least one row present in the table)

SELECT json_object_keys(to_json((SELECT t FROM schema_name.table_name t LIMIT 1)))

The listing preserves the order. In case you don't care about the order and have hstore extension installed you can do even shorter

SELECT skeys(hstore(NULL::schema_name.table_name))


PIVOT data type conversion error How do I name the columns of a PIVOT table to something that is not a number?

Is it a requirement that the names of the spreading columns be a number?

I'm working my way thought the book Querying Microsoft SQL Server 2012 in preparation for the 70-461 exam. I am currently on the lesson for PIVOT tables (Chapter 5, Lesson 2, Page 163). The following SQL executes successfully.

WITH Pivot234
AS
(
select  ss.companyname,                         --Grouping column
        datepart(dw, so.orderdate) as DayNumber, --Spreading column
        so.freight                              --Aggregation Column
FROM Sales.Orders so
LEFT OUTER JOIN sales.Shippers ss ON so.shipperid = ss.shipperid
)

SELECT  companyname, [1],[2],[3],[4],[5],[6],[7]
FROM Pivot234
PIVOT (AVG(FREIGHT)
       FOR DayNumber IN ([1],[2],[3],[4],[5],[6],[7])
       ) AS p;

I want the name of my attributes to be the days of the week not numbers. When I change the numbers in the PIVOT table operator to the days of the week I get the following...

WITH Pivot234
AS
(
select  ss.companyname,                         --Grouping column
        datepart(dw, so.orderdate) as DayNumber, --Spreading column
        so.freight                              --Aggregation Column
FROM Sales.Orders so
LEFT OUTER JOIN sales.Shippers ss ON so.shipperid = ss.shipperid
)

SELECT  companyname, [Mon],[Tues],[Wed],[Thurs],[Fri],[Sat],[Sun]
FROM Pivot234
PIVOT (AVG(FREIGHT)
       FOR DayNumber IN ([Mon],[Tues],[Wed],[Thurs],[Fri],[Sat],[Sun])
       ) AS p;

The second SQL query has the following error...

  Msg 8114, Level 16, State 1, Line 36
  Error converting data type nvarchar to int.
  Msg 473, Level 16, State 1, Line 36
  The incorrect value "Mon" is supplied in the PIVOT operator.

  Line 36 is FOR DayNumber IN ([Mon],[Tues],[Wed],[Thurs],[Fri],[Sat],[Sun])

What am I not understanding about PIVOT tables?

Alias the columns in the select. The values in the In (...) part of the Pivot need to be the exact field values observed in the data.

Select CompanyName, [1] As Mon, [2] As Tues, [3] As Wed...
  From Pivot234
  Pivot (Avg(Freight) For DayNumber In ([1], [2], [3], [4], [5], [6], [7])) As p;

  Can you explain why it is necessary to alias the spreading columns? Why can I not just simply state the columns as I did in the PIVOT table operator and in the SELECT statement?

The Pivot(Sum([ValueField]) For [CategoryField] In ('Category A', 'Category B' ... )) syntax is looking for the literal values that you supply in the In () to be in the [CategoryField]. It isn't mapping number 1 to position 1, it is searching the field for those exact values.

Both DayNumber and Freight are required to be of the same data type


SQL Query Declaration problem I have a view as shown below.The View is good running.When I attempt to run view,I got the following the error.What might the problem?

Incorrect syntax near the keyword 'Declare'

SQL Query:

create View [dbo].StokMaliyet
AS
Declare @Dolar decimal(18,3),@Euro decimal(18,3)

set @Dolar=(select Dolar from GunlukDoviz
set @Euro=(select Euro from GunlukDoviz)

 select Stok_Kodu,
 (case
 when dovtip='3' then net_maliyet
 when dovtip='2' then ((net_maliyet)*@Dolar)/@Euro
 When dovtip='0' then (net_maliyet)/@Euro
 end )  as Maliyet
 from ib_mlyt

You cannot use declare and any code manipulation in view (select statement ONLY at SQL Server of course).

CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ]
[ WITH <view_attribute> [ ,...n ] ]
AS select_statement
[ WITH CHECK OPTION ] [ ; ]

<view_attribute> ::=
{
    [ ENCRYPTION ]
    [ SCHEMABINDING ]
    [ VIEW_METADATA ]     }

See for more details

Use stored procedure instead of view

You can do Cartesian product using CTE as our CTE would be only one row .

create View [dbo].StokMaliyet
AS
WITH
    CTEDolar AS (select top 1 cast (Dolar  as decimal(8,7) )Dolar from GunlukDoviz),
    CTEEuro AS (select top 1 cast (Euro  as decimal(8,7) ) Euro  from GunlukDoviz)
 select Stok_Kodu,
 (case
 **strong text**when dovtip='3' then net_maliyet
 when dovtip='2' then ((net_maliyet)*Dolar)/Euro
 When dovtip='0' then (net_maliyet)/Euro
 end )  as Maliyet
 from ib_mlyt,CTEDolar as CTEDolar ,CTEEuro as CTEEuro

GO


MSSQL peer to peer replication what is the best practice to drop and create multiple table I have a Transactional Replication server in peer to peer topology with 3 server node.
My development strategy was Agile, so that will be a frequency change on the database etc.
Scenario: I need to drop 20 tables out of 100 tables, and create another 30 tables at a time.
May I know what is the best/common practice to do it?
What I can think of was,

Drop all replication article and subscription for 3 server node.
Drop the 20 tables and create the 30 tables on each server node.
Resetup back the peer to peer topology replication to the 3 server node.

(P/S: I am new in this replication technology, do guide me if my step is wrong.)

Yeah, this is the problem with Transactional Replication: every time you change something you need to recreate the publisher and the subscription.
I think your three steps are right.


Calculating the percentage change from the previous row I just entered the world of window functions. I have a similar problem to solve as in this question.

I have this table:

The last column (%Grown) is given by:

-(1 - price of row 2 / price of row 1) * 100

All the information I have found so far was related to sums, avg for all rows. I don't know how to bound two rows so I can calculate the percentage.

How do I translate this into SQL?

The best way I think would be to use the LAG() or LEAD() functions:

SELECT *,
       - 100.0 * (1 - LEAD(Price) OVER (ORDER BY t.Id) / Price) AS Grown
FROM table_name AS t
ORDER BY t.Id ;

With LEAD(Price) OVER (ORDER BY t.Id) you have access to the next Price when the rows are ordered by Id. It's not clear what the order should be. Based on the data in the question, you may want (ORDER BY Price DESC).

You can use Common Table expression,

WITH Data as (select  ROW_NUMBER() OVER (ORDER BY applicable_date) as RowId, date, qty, price, [value] from tbl
)
SELECT
CurrentRow.date, CurrentRow.qty, CurrentRow.price, CurrentRow.[value], 'Your calculation here for Growth'
FROM Data as CurrentRow
LEFT OUTER JOIN Data as NextRow ON NextRow.RowId = CurrentRow.RowId + 1
WHERE NextSlice.RowId IS NOT NULL

I Hope it helps.


Is it possible to prevent data loss with Postgres WAL archiving? As I understand from the docs, a WAL segment is archived when the segment file reaches some size or due to archive_timeout.

So if the database crashes, all the data from the incomplete, currently active WAL segment would be lost (assuming we don't have any access to that incomplete segment in the Postgres directory).

Do I get it right and if so, is there a way to avoid this data loss?

You are correct.

The way to avoid this is to use pg_receivewal, which streams WAL information from the database server and persists it in WAL segment copies immediately.

By default this is asynchronous, so you could lose some split seconds in case of a crash. It you need to avoid that, you'd have to use synchronous replication with pg_receivewal, but be warned that

This makes DML statements slower, since COMMIT cannot succeed until it has heard back from pg_receivewal.
you shouldn't set synchronous_commit = remote_apply, because pg_receivewal never applies the changes, it just saves them to a WAL file.

PostgreSQL uses Write-Ahead Logging (WAL) as its approach to transaction logging. WAL's central concept is that changes to data files (where tables and indexes reside) must be written only after those changes have been logged, that is, when log records describing the changes have been flushed to permanent storage. If we follow this procedure, we do not need to flush data pages to disk on every transaction commit, because we know that in the event of a crash we will be able to recover the database using the log: any changes that have not been applied to the data pages can be redone from the log records. (This is roll-forward recovery, also known as REDO.)
This is from link below:

https://wiki.postgresql.org/wiki/PostgreSQL_for_Oracle_DBAs

But anything coming from memory to disk will start at redo plus databases only guarantee committed transactions. Anything which is only in memory is not committed and non-recoverable. Some HA mechanisms do exist for saving transaction in memory but not PostgreSQL.


No Maintenance Plans Folder in SQL Studio 2008 R2 A client has SQL Server 2008 R2 installed on a Windows 2003 R2 32-bit server.

Under Management in SQL Studio 2008 R2, there is no maintenance plans folder. It is not under Management --> Legacy either, as it would be if there were legacy-mode databases on the server. I am certain this is SQL Server 2008 R2 and not Express. I have checked the version and verified the splash screen when studio starts are not Express. I am logging in as the SQL account sa, and the account does have the sysadmin server role.

Lastly, right clicking on a database in SSMS and navigating to "Tasks" does not show any Maintenance Plan option.

Clearly, I'm missing something, and Google is coming up empty considering the scenario I've outlined. Any ideas? Thanks.

EDIT: select @@version returns "Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)   Apr 22 2011 11:57:00   Copyright (c) Microsoft Corporation  Express Edition with Advanced Services on Windows NT 5.2  (Build 3790: Service Pack 2)"

Express Edition (the free version of SQL Server) doesn't have maintenance plans because it has no SQL Server Agent.  Agent is the constantly-running-scheduler that kicks off jobs like maintenance plans.

For me the problem was that the SQL Server Agent wasn't running on the server. Open the SQL Server Configuration Manager and make sure SQL Server Agent is running. Before doing that you may have to open it's properties and set the Start Mode to Automatic and save the changes.


Execute Sql statement and send result to email I have to execute SQL statement every day and send result to dba by email.I configured database email.

Below is query:

 SELECT
        account.accountID,
        account.name
    FROM
        account
        LEFT OUTER JOIN accountfeaturesetting afs
            ON afs.accountid = account.accountid
                and afs.featureid = 'Schedules'
                and afs.settingid = 'EditReasons'
    WHERE
        ISNULL(afs.Value, '0') = '1'
        AND NOT EXISTS
 (SELECT 1 FROM program
WHERE program.AccountID = account.AccountID
 AND program.Active = 1 AND
(program.ScheduleEditReasonFlags <> 0 OR
 program.ScheduleEditReasonFields <> 0))
        AND account.IsMaster = 0

For this I created Sql job having 2 steps:

execute select statement
send result:

Use MSDB
    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'abc',
        @recipients = 'recipients@company.com',
        @subject = 'queryresultset',
        @body='testmail',
        @query_attachment_filename='warning.csv'

It is throwing an error and the result set is not attached with query.

  Error: Parameter @attach_query_result_as_file cannot be 1 (true) when no value is specified for parameter @query.
  A query must be specified to attach the results of the query.
  [SQLSTATE 42000] (Error 14625)

Edit:

Use MSDB
    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'abc',
        @recipients = 'recipients@company.com',
        @subject = 'queryresultset',
        @body='testmail',
        @query_attachment_filename='warning.csv'
    DECLARE @msg VARCHAR(250);
    declare @query varchar(2048);
    SELECT @msg = 'Please refer to the attached spread sheet for the report.';

    set @query='
        SELECT
        account.accountID,
        account.name
    FROM
        account
        LEFT OUTER JOIN accountfeaturesetting afs
            ON afs.accountid = account.accountid
                and afs.featureid = 'Schedules'
                and afs.settingid = 'EditReasons'
    WHERE
        ISNULL(afs.Value, '0') = '1'
        AND NOT EXISTS
 (SELECT 1 FROM program
WHERE program.AccountID = account.AccountID
 AND program.Active = 1 AND
(program.ScheduleEditReasonFlags <> 0 OR
 program.ScheduleEditReasonFields <> 0))
        AND account.IsMaster = 0
        go';
        exec (@query)

Above query is not taking schedules and editreasons value column.  I am still getting a syntax error after declaring all variables like:

declare @featureid varchar(100);
declare @settingid varchar(100);
declare @value varchar(100);

Your code is backwards.  You are sending the email, then generating the query results.  The sp_send_dbmail stored procedure will run the query for you, and append the results either in the body of the email or as an attachment.

In case you aren't aware, Microsoft has extensive, comprehensive, and free documentation on their website for SQL Server.  For instance, sp_send_dbmail is available in great detail at:  https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-send-dbmail-transact-sql

The @query parameter is documented as:

  [ @query= ] 'query'
  Is a query to execute. The results of the query can be attached as a file, or included in the body of the e-mail message. The query is of type nvarchar(max), and can contain any valid Transact-SQL statements. Note that the query is executed in a separate session, so local variables in the script calling sp_send_dbmail are not available to the query.

They also have several examples of calling sp_send_dbmail, including this salient point:

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Adventure Works Administrator',
    @recipients = 'yourfriend@Adventure-Works.com',
    @query = 'SELECT COUNT(*) FROM AdventureWorks2012.Production.WorkOrder
                  WHERE DueDate > ''2004-04-30''
                  AND  DATEDIFF(dd, ''2004-04-30'', DueDate) < 2' ,
    @subject = 'Work Order Count',
    @attach_query_result_as_file = 1 ;

So, you need to pass the query definition into msdb.dbo.sp_send_dbmail like this:

DECLARE @msg VARCHAR(250);
DECLARE @query varchar(2048);

SET @msg = 'Please refer to the attached spread sheet for the report.';

SET @query='
    SELECT
        account.accountID,
        account.name
    FROM
        dbo.account
        LEFT OUTER JOIN dbo.accountfeaturesetting afs
            ON afs.accountid = account.accountid
                AND afs.featureid = ''Schedules''
                AND afs.settingid = ''EditReasons''
    WHERE
        ISNULL(afs.Value, ''0'') = ''1''
        AND NOT EXISTS
            (
                SELECT 1
                FROM dbo.program
                WHERE program.AccountID = account.AccountID
                    AND program.Active = 1
                    AND (
                        program.ScheduleEditReasonFlags <> 0
                        OR program.ScheduleEditReasonFields <> 0
                        )
            )
        AND account.IsMaster = 0;';

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'abc'
    , @recipients = 'recipients@company.com'
    , @subject = 'queryresultset'
    , @body= @msg
    , @body_format = 'TEXT'
    , @query = @query
    , @execute_query_database = 'MyDB'
    , @attach_query_result_as_file = 1
    , @query_attachment_filename='warning.csv'
    , @query_result_header = 1
    , @query_result_width = 80
    , @query_result_separator = ' '
    , @exclude_query_output = 0
    , @append_query_error = 1
    , @query_no_truncate = 0
    , @query_result_no_padding = 0;

Also note the extra parameters included, which control various output factors such as how the query is formatted etc.

Note in the @query text above, I've "escaped" the single quotes to prevent SQL Server from considering them as literal delimiters.  Essentially, any single-quotes appearing inside the @query parameter need to be turned into two single-quotes.  Erland Sommarskog has an excellent piece on Dynamic SQL and nested strings.

DECLARE @Account_SQL_QUERY NVARCHAR(MAX)
DECLARE @Account_FILE_NAME varchar(MAX)
SET @Account_FILE_NAME='Account_RejectedRecords_'+Replace(Replace(replace(Substring(convert(varchar, getdate(),120),1,16),'-',''),' ',''),':','')+'.csv'

                IF OBJECT_ID('tempdb..##RejectedAccountRecord') is not NULL
                BEGIN
                    Drop table ##RejectedAccountRecord
                END
                Select *  Into ##RejectedAccountRecord
                from [blk].[account_reconciliation_rejected] Where loaddate=convert(varchar, getdate(), 23)

SELECT @sub=Sub,@to=Email_to,@cc=Email_cc,@bcc=Email_bcc,@body=Email_body FROM dbo.EmailConfiguration
                WHERE project = 'Blackline' and report_id = 'B1'

        IF (Select Count(*) from ##RejectedAccountRecord) >0
        BEGIN
            SET @Account_SQL_QUERY='Select * from ##RejectedAccountRecord'
            SET NOCOUNT ON

                EXEC msdb.dbo.sp_send_dbmail
                        @profile_name           = 'AjeetDBMail'
                       ,@recipients             = @to
                       ,@copy_recipients        = @cc
                       ,@Query                  =@Account_SQL_QUERY
                       ,@subject                = @sub
                       ,@body                   = @HTMLBody
                       ,@body_format            ='HTML'
                       ,@query_attachment_filename =@Account_FILE_NAME
                       ,@attach_query_result_as_file = 1
                       ,@query_result_separator='   '
                       ,@query_result_width =10000
                       ,@query_result_no_padding=1


trigger_file doesn't work I set streaming replication. Replication works just fine but it doesn't care about trigger_file. I have nothing fancy there except all what many of tutorials advices:

recovery.conf:

standby_mode = on
primary_conninfo = 'host=69.69.69.69 port=5432 user=repl password=some_pass_here'
trigger_file = '/var/lib/postgresql/9.3/main/failover_trigger'

I've checked that the postgres user has access to this file, I've tried to change the location to /tmp, set file owner as postgres or root and nothing helped.

Any ideas ? Thanks in advance.

As a starting point to troubleshoot this, you may check what's read from recovery.conf with log_min_messages set to debug2 in postgresql.conf on the slave.

On server start, the trigger file should be shown in the log within a set of entries like this:

 DEBUG:  standby_mode = 'on'
 DEBUG:  primary_conninfo = 'host=69.69.69.69 port=5432 user=repl password=some_pass_here'
 DEBUG:  trigger_file = '/var/lib/postgresql/9.3/main/failover_trigger'
 LOG:  entering standby mode

If the trigger_file entry doesn't show up, the most plausible explanation would be that you're editing a recovery.conf at a wrong location.

If on the other hand it's found at startup, when later creating the trigger file to fail over, this entry should appear:

 LOG:  trigger file found: /var/lib/postgresql/9.3/main/failover_trigger

once the trigger is created on the master machine

standby_mode = on
primary_conninfo = 'host=69.69.69.69 port=5432 user=repl password=some_pass_here'
trigger_file = '/var/lib/postgresql/9.3/main/failover_trigger'

you need to create the file in the slave machine (replica) in-order to reconfigure itself to lunch your trigger

EDIT

lets check the configuration after the installation is completed:

1) you need a ssh key,if you don't have one you can generate one for testing, but it will give you certificate error later by using ssh-keygen

2)copy the certificate you had in step one to the other server: ssh-copy-id IP_address_of_opposite_server

3) create replica user by using psql -c "CREATE USER rep REPLICATION LOGIN CONNECTION LIMIT 1 ENCRYPTED PASSWORD ' enter_your_password';

**Note the following steps need to be done while the service is down if you want to do it at the sometime otherwise you can repeat the steps for replica machine (slave).

4)open PostgreSQL directory

5)edit (pg_hba.conf) file note: take a backup from (pg_hba.conf) first

6)  add the following line but make sure you font add it to the bottom of the file: host    replication  rep  IP_address_of_slave/32   md5

7) save and close

8) edit (postgresql.conf) note  you need to take backup first

9) search for the following parameters and um-comment them

listen_addresses = 'localhost,IP_address_of_THIS_host'
wal_level = 'hot_standby'
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 1
hot_standby = on

10) save and close

11) start the master machine (main server that has the database make sure the replica service still down)

12) now you need to take backup from the master machine by using

psql -p $PORT -c "select pg_start_backup('$BACKUP_LABEL');"
rsync -cva --inplace --exclude=*pg_xlog* $PGDATA/ <IP_OF_OTHER_HOST>:$PGDATA/
psql -p $PORT -c "select pg_stop_backup();"

13) now open recovery.conf on the replica server

standby_mode = 'on'
primary_conninfo = 'host=master_IP_address port=[unter port number] user=rep password=[enter rep password]'
trigger_file = '/tmp/postgresql.trigger.port'

14) start the replica server

15) make sure the ports are opened in the firewall.

16) to open the turn over you need to touch the trigger file that mentioned in the recovery.conf


Which is the redundant relationship in this ER Diagram and why? Which is the redundant relationship in this ER Diagram and why? A few of my friends and I have been trying to figure it out but keep going in circles.

where the attributes of each entity are as follows:

Employee: Employee_No, Name, Address and Telephone_No.
Department: Department_No, Name and Location.
Training Course: Course Code, Title, Start_Date and Description.

None of them are redundant. It's ok to have a loop.

You seem to be missing a many-many relationship (Employee Course) table, though.

Assuming that employees only attend training courses for their department, you could derive the department an employee works in according to the training courses they've taken.


Predicate not resulting in expected scan So I have this query that I'm trying to tune and am running into an issue that I'm having trouble understanding. First of this is the query I'm using.

SELECT
    si.LoanNbr AS [LoanNumber],
    fi.[SvcClientNbr] AS ClientID,
    si.LoanMasterID,
    si.LoanSrcCode AS [LoanSourceCode],
    fi.LoanPurpCode,
    fi.[PropState] AS [Property State],
    im.ImagedocumentID AS [Image Document ID],
    -- im.requestID AS [Request ID],
    CONVERT(VARCHAR(10),im.[ImageDate],101) AS ImageDate,
    im.[PageCount],
    im.[SignatureInd]
FROM dbo.NotMybaseTable Si
INNER JOIN dbo.NotMyTableName fi
    ON si.LoanMasterID = fi.LoanMasterID
INNER JOIN [dbo].[ImagedDocument] im
    ON si.loanmasterid = im.loanmasterid
    AND im.[DocTypeCode] = '10112'
WHERE CASE WHEN si.loansrccode = 'CORE' AND  Im.[SignatureInd] IN ('Y') THEN 1
        WHEN si.FundingSysCode = 'LIS' and CASE WHEN si.loansrccode = 'CORE' THEN 0 ELSE 1 END = 1 THEN 1
    ELSE 0 END = 1
    AND [ImageDate] BETWEEN DATEADD(WK, DATEDIFF(WK, 0, GETDATE()) - 4, -30) AND DATEADD(WK, DATEDIFF(WK, 0, GETDATE()) - 4, 0) + 5

When I run this query the execution plan I get looks like this.

So the query is doing a very large scan on this large table returning 30 million rows just to pare it down to 3K. The index it's using looks like this.

CREATE NONCLUSTERED INDEX [IX_ImageDoc_DocType] ON [dbo].[ImageDoc]
(
    [LoanMasterID] ASC,
    [ImageDate] ASC,
    [ImageDocType] ASC
)

I altered to query in a way that gives it much better performance by moving the date portion of the predicate outside of the initial query like so.

select
    *
from (
    SELECT
        si.LoanNbr AS [LoanNumber],
        fi.[SvcClientNbr] AS ClientID,
        si.LoanMasterID,
        si.LoanSrcCode AS [LoanSourceCode],
        fi.LoanPurpCode,
        fi.[PropState] AS [Property State],
        im.ImagedocumentID AS [Image Document ID],
        CONVERT(VARCHAR(10),im.[ImageDate],101) AS ImageDate,
        im.[PageCount],
        im.[SignatureInd]
    FROM dbo.NotMybaseTable Si
    INNER JOIN dbo.NotMyTableName fi
      ON si.LoanMasterID = fi.LoanMasterID
    INNER JOIN [dbo].[ImagedDocument] im
      ON si.loanmasterid = im.loanmasterid
      AND im.[DocTypeCode] = '10112'
    WHERE CASE WHEN si.loansrccode = 'CORE' AND  Im.[SignatureInd] IN ('Y') THEN 1
            WHEN si.FundingSysCode = 'LIS' and CASE WHEN si.loansrccode = 'CORE' THEN 0 ELSE 1 END = 1 THEN 1
        ELSE 0 END = 1
        ) as F
WHERE [ImageDate] BETWEEN DATEADD(WK, DATEDIFF(WK, 0, GETDATE()) - 4, -30) AND DATEADD(WK, DATEDIFF(WK, 0, GETDATE()) - 4, 0) + 5

This resulted in much better performance and a query plan that is obviously superior.

The index it's scanning now looks like this.

CREATE NONCLUSTERED INDEX [IX_ImageLoanDoc_SystemName_ICMPDocTypeCode] ON [dbo].[ImageDoc]
(
    [SystemName] ASC,
    [ICMPDocTypeCode] ASC,
    [LoanMasterID] ASC,
    [ImageDate] ASC
)
INCLUDE (   [ImageDocumentID],
    [ImageDocType],
    [BatchName],
    [SignatureInd],
    [ICMPDocCategoryCode],
    [ICMPDocSubTypeCode])

So here's my question. With ImageDate being in this index why does having it in my query hurt performance so badly? Shouldn't having that predicate included make it easier to eliminate rows? What am I doing/thinking incorrectly?

Your query is looking for values between 2015-04-25 AND 2015-05-30.

Of which it looks like you have about 30 million.

Unfortunately there is a bug with the cardinality estimations where DATEDIFF is involved where the components get reversed.

SELECT DATEADD(WK, DATEDIFF(WK, GETDATE(), 0) - 4, -30) ,
       DATEADD(WK, DATEDIFF(WK, GETDATE(), 0) - 4, 0) + 5

Returns a range of 1784-05-15 to 1784-06-19. Extremely likely you have no dates in that range in the table (or statistics histogram) so SQL Server will estimate that no rows will be returned out of the scan, and certainly no need for 30 million lookups.

This is a fixed bug but requires trace flag 4199 enabled.

If that isn't possible you could try rephrasing the predicate in a different way that avoids the use of DATEDIFF or assigning the values to variables and using OPTION (RECOMPILE)

ImageDate in the index is not used because your condition over ImageDate concerns a function ("DATEADD"). In general systems do not use indexes over attributes if the condition contains a function which is evaluated at query execution time.


Encryption in PostgreSQL Does PostgreSQL support data encryption features at transactional and the table level of database? If yes, how?

Oracle provides this via database and network encryption. Oracle provides encryption at table, tablespace level. Does PostgreSQL also supports this?

This is a complex question and you will get a complex answer.

There is no reason you can't put a tablespace on an encrypted filesystem.  Additionally pgcrypto gives you a toolkit for doing data encryption but data encryption in the db is hard and there are too many ways keys can be disclosed to do this without review and assistance.  This isn't at the table level but rather at the value level, and pgcrypto gives you a plethora of options there.  Please see the pgcrypto documentation for details.

There is a whole doc page with all of this If you're concerned about the tablespace/filesystem level, you want Data Partition Encryption Storage.

18.8. Encryption Options

  PostgreSQL offers encryption at several levels, and provides
  flexibility in protecting data from disclosure due to database server
  theft, unscrupulous administrators, and insecure networks. Encryption
  might also be required to secure sensitive data such as medical
  records or financial transactions.

  Password Storage Encryption By default, database user passwords are
  stored as MD5 hashes, so the administrator cannot determine the actual
  password assigned to the user. If MD5 encryption is used for client
  authentication, the unencrypted password is never even temporarily
  present on the server because the client MD5-encrypts it before being
  sent across the network.
  Encryption For Specific Columns The pgcrypto module allows certain
  fields to be stored encrypted. This is useful if only some of the data
  is sensitive. The client supplies the decryption key and the data is
  decrypted on the server and then sent to the client.

  The decrypted data and the decryption key are present on the server
  for a brief time while it is being decrypted and communicated between
  the client and server. This presents a brief moment where the data and
  keys can be intercepted by someone with complete access to the
  database server, such as the system administrator.
  Data Partition Encryption Storage encryption can be performed at the
  file system level or the block level. Linux file system encryption
  options include eCryptfs and EncFS, while FreeBSD uses PEFS. Block
  level or full disk encryption options include dm-crypt + LUKS on Linux
  and GEOM modules geli and gbde on FreeBSD. Many other operating
  systems support this functionality, including Windows.

  This mechanism prevents unencrypted data from being read from the
  drives if the drives or the entire computer is stolen. This does not
  protect against attacks while the file system is mounted, because when
  mounted, the operating system provides an unencrypted view of the
  data. However, to mount the file system, you need some way for the
  encryption key to be passed to the operating system, and sometimes the
  key is stored somewhere on the host that mounts the disk.
  Encrypting Passwords Across A Network The MD5 authentication method
  double-encrypts the password on the client before sending it to the
  server. It first MD5-encrypts it based on the user name, and then
  encrypts it based on a random salt sent by the server when the
  database connection was made. It is this double-encrypted value that
  is sent over the network to the server. Double-encryption not only
  prevents the password from being discovered, it also prevents another
  connection from using the same encrypted password to connect to the
  database server at a later time.
  Encrypting Data Across A Network SSL connections encrypt all data sent
  across the network: the password, the queries, and the data returned.
  The pg_hba.conf file allows administrators to specify which hosts can
  use non-encrypted connections (host) and which require SSL-encrypted
  connections (hostssl). Also, clients can specify that they connect to
  servers only via SSL. Stunnel or SSH can also be used to encrypt
  transmissions.
  SSL Host Authentication It is possible for both the client and server
  to provide SSL certificates to each other. It takes some extra
  configuration on each side, but this provides stronger verification of
  identity than the mere use of passwords. It prevents a computer from
  pretending to be the server just long enough to read the password sent
  by the client. It also helps prevent “man in the middle” attacks where
  a computer between the client and server pretends to be the server and
  reads and passes all data between the client and server.
  Client-Side Encryption If the system administrator for the server's
  machine cannot be trusted, it is necessary for the client to encrypt
  the data; this way, unencrypted data never appears on the database
  server. Data is encrypted on the client before being sent to the
  server, and database results have to be decrypted on the client before
  being used.


Cannot rebuild index, but there's no reason why not? I've created a process whereby I am able to only rebuild indexes that need rebuilding(the process takes an hour and a half if I rebuild them all), and while it works beautifully, it gets stuck on one particular index and I see no reason why I should.
It fails with the following message:

Msg 2725, Level 16, State 2, Line 1
An online operation cannot be performed for index 'I_520CUSTVENDRELIDX' because the index contains column 'MEMO' of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml, or large CLR type. For a non-clustered index, the column could be an include column of the index. For a clustered index, the column could be any column of the table. If DROP_EXISTING is used, the column could be part of a new or old index. The operation must be performed offline.

However, when I run the query based on a suggestion by this chap, shown below, I get no results:
SELECT *
FROM sys.index_columns AS ic
INNER JOIN sys.columns AS c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
AND ((c.system_type_id IN (34,35,99,241)) -- image, text, ntext, xml
 OR (c.system_type_id IN (167,231,165) -- varchar, nvarchar, varbinary
     AND max_length = -1))
INNER JOIN sys.indexes as si
on si.object_id = ic.object_id
AND ic.index_id = si.index_id
inner join sys.tables t
on t.object_id = ic.object_id
where t.name = 'CONTACTPERSON'
and si.name = 'I_520CUSTVENDRELIDX'

On top of that, a manual inspection of the index in question shows no text, ntext, image, xml or varchar(MAX), nvarchar(MAX) or varbinary(MAX).  Could there be something I'm missing here?
For the record, this is a clustered index.

I skipped the code example, and jumped to what seems to be the real question here

  On top of that, a manual inspection of the index in question shows no text, ntext, image, xml or varchar(MAX), nvarchar(MAX) or varbinary(MAX). Could there be something I'm missing here?

  For the record, this is a clustered index.

You certainly are missing something, and the answer is in the body of the error message:

  For a clustered index, the column could be any column of the table

For Online Index Operations there is a difference between a Clustered and a Non-Clustered index.
both may not contain a BLOB column in order to rebuild online,
but while a Non-Clustered Index "contains" the columns in it's definition (and included columns),
a Clustered Index "contains" the entire table with all of the columns.

in your case there is a column named MEMO which is of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml, or large CLR type.
this column is part of the table, and thus part of the Clustered index.

Your options are:

Perform the REBUILD operation offline
Upgrade to SQL Server 2012
Change the column's data type

Guidelines for Performing Online Index Operations

Dear My friend this Issue i faced it and i know why this problem is Coming and i think it's bug in Microsoft SQL Server , I Use SQL Server 2012 SP1 Last Updated patch
the Problem :
When you Create any table and make any Column as datatype one of this data types (text, ntext, image or FILESTREAM, varchar(max), nvarchar(max), varbinary(max)) at the first this Column with this Data type you cannot create index on it in SQL 2012 or include it in Index in 2008 you can but now no
the Problem coming when you Create Clustered Index in any Column with any data type like INT when you Need to rebuild this Clustered index With online = on if the table have column With this any one from this data type ((text, ntext, image or FILESTREAM, varchar(max), nvarchar(max), varbinary(max))
The rebuild will Fail by this Error

Msg 2725, Level 16, State 2, Line 1
An online operation cannot be performed for index 'PK_Tickets' because the index contains column 'TicketBody' of data type text, ntext, image or FILESTREAM. For a non-clustered index, the column could be an include column of the index. For a clustered index, the column could be any column of the table. If DROP_EXISTING is used, the column could be part of a new or old index. The operation must be performed offline.

So the salutation

Don't make rebuild to the Clustered index on the table that's have any Column with data type one of this data type (text, ntext, image or FILESTREAM, varchar(max), nvarchar(max), varbinary(max))

Example :

----Create Table
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Table_3](
    [ID] [int] NOT NULL,
    [TicketBody] [ntext] NULL,
    [TicketChannel] [int] NULL,
 CONSTRAINT [PK_Table_3] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

--- rebuild Index

ALTER INDEX PK_Table_3 ON Table_3 rebuild With
(online = on)

TRY TO CREATE NON-CLUSTERED INDEX IT WILL SUCCEED BUT CLUSTERED INDEX WILL FAIL

THANKS

ENG.MOSTAFA ELMASRY
DATABASE ADMINISTRATOR / DB ANALYSIS


Why you want to avoid Dynamic SQL in stored procedure? I have heard one said you do not want to use Dynamic SQL. Can you give some concrete example or real-life example? Personally, I code it a few times in my database. I think it is OK because it's flexibility. My guess is about SQL Injection or Performance. Anything else?

There is nothing wrong with using dynamic SQL if you must. In fact in some circumstances it is the only option that you have. It is more of a recommendation not to use it as yes it can lead to a SQL injection if your input is not sanitized, and yes using dynamic SQL in modules that get called often can be detrimental to it's performance.

I don't think there is a concrete example as per se but I would say this: Try to achieve what you are after using regular queries and statements first - only then once you have exhausted all other avenues do it dynamically. Just remember that executing a dynamic SQL string is done in a separate user session to the module that is calling it - so you may encounter permissions issues where you are not expecting any.

If your worried about performance; test it. If your worried about security; validate your input. There is no right or wrong - only that you use your best judgement based on the information and tools you have available to you at the time.

Do not use Dynamic SQL.
99% of the time Dynamic SQL is used because of the lack of knowledge on how to use optional parameters for stored procedures, the rest 1% of the time it's used to create a highly complex query for a report that the customer doesn't even understand. The Curse and Blessings of Dynamic SQL doesn't show an example of why it would be a good idea to use it but instead suggests that it's problematic because of the increased complexity of debugging, maintaining, without mentioning the security risks of SQL Injection, poor performance not because of the difficulties for the optimizer to cache the query but instead the bad practices that come along like using temporary tables and cursors which of course a lazy and naive programmer would abuse of. There is no such thing as the flexibility to write queries in that way, it's actually all the opposite, SQL is a declarative language with lots of syntactic language tools like CTE's, Window Functions, Query Hints, etc; if you are using it as imperative like writing conditional statements (IF-ELSE) then you are already doing it WRONG which is something that most of the stupid-non-aware-lazy programmers do up to this day.


Count is slow Oracle this is my query :

SELECT Count(last_upd)
FROM S_CONTACT
WHERE last_upd_by = '0-1' AND LAST_NAME <> 'Wait' AND last_upd  +  1/24 > SYSDATE - (1 / (24 * 60));

It takes 84 seconds to return me this result :

COUNT(LAST_UPD)
---------------
43

I don't understand why it's too much slow for only 43 returned row...

This is the execution plan :

Can anyone help me? this is the V$STATNAME

active txn count during cleanout    1
bytes received via SQL*Net from client  497
bytes sent via SQL*Net to client    14803
calls to get snapshot scn: kcmgss   23
calls to kcmgas 45
cleanout - number of ktugct calls   12
cleanouts and rollbacks - consistent read gets  2
cleanouts only - consistent read gets   10
commit txn count during cleanout    11
consistent changes  450
consistent gets 1458915
consistent gets - examination   458
consistent gets from cache  1458915
CPU used by this session    4485
CPU used when call started  4493
CR blocks created   44
current blocks converted for CR 1
cursor authentications  1
data blocks consistent reads - undo records applied 59
db block changes    12
DB time 8383
dirty buffers inspected 31
enqueue releases    1
enqueue requests    1
execute count   2
free buffer inspected   1442014
free buffer requested   1436612
heap block compress 12
hot buffers moved to head of LRU    25764
immediate (CR) block cleanout applications  12
no work - consistent read gets  1458380
opened cursors cumulative   2
OS Involuntary context switches 18069
OS Maximum resident set size    1956
OS Page faults  4
OS Page reclaims    410
OS System time used 1868
OS User time used   2625
OS Voluntary context switches   63594
parse count (hard)  1
parse count (total) 2
physical read bytes 11768365056
physical read IO requests   55724
physical read total bytes   11768365056
physical read total IO requests 55724
physical read total multi block requests    54675
physical reads  1436568
physical reads cache    1436568
physical reads cache prefetch   1380844
prefetched blocks aged out before use   8
recursive calls 1
redo entries    12
redo size   1272
redo subscn max counts  8
rollbacks only - consistent read gets   40
session logical reads   1458915
session pga memory  -393216
session pga memory max  262144
session uga memory max  299256
shared hash latch upgrades - no wait    4
sorts (memory)  2
sorts (rows)    760
SQL*Net roundtrips to/from client   9
table scan blocks gotten    1458432
table scan rows gotten  16656961
table scans (long tables)   1
transaction tables consistent read rollbacks    4
transaction tables consistent reads - undo records applied  391
user calls  11
user I/O wait time  6172
workarea executions - optimal   5

I'm using Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bits

Thank you

the query does not return 43 rows but 1 row that contains the number 43
the execution time of a query does not depend on the number of rows it return but on the number of rows it inspects.
from your query plan you can see your query makes a full table scan. This means it reads the table from begin to the end and reads all 16 millions of rows of the table (table scan rows gotten 16656961).
for this it has to read about 11G data (physical read total bytes 11768365056) and executes 55,000 read operations (physical read total IO requests 55724). 55,000 reads in 60 seconds (user I/O wait time 6172) means 1.1 ms per read. This is a good value.

You have to decrease the number of row your query has to process. An index may help Oracle to count efficiently the number of rowa you are interested in.

The most selective subclause of your where-clause will be the last_upd subclause. So make an index on this column.
You should add the columns from the other where-clauses (and the select-clause) to the index, too. So Oracle does not have to lookup each fitting row in that is found by the index in the table to get the last_upd_by and last_name values.
The last_upd_by subclause is the next selective clause so it schould be the next column in the index. So you should make an inex on the columns (last_upd, last_upd_by, last_name) in this order.

If your table was ever larger at some point and you did a delete, but did not rebuild the indexes or truncated the table, your queries are going to be slower.

Try rebuilding the indexes, if that does not work export the data and truncate the table.


general memory requirements for sql server 2008 r2 I'm not experienced with DBA work, but I'm trying to make a case for requesting additional resources for our sql server and was hoping I could get some smart folks to provide a rough rough estimate of what we should be running.  I'm suspecting that the allocation of resources IT has given to our production sql server is low.

Hardware & Software:

Database: sql server 2008 r2 enterprise database

Windows: Windows 2008 r2 Enterprise 64 bit, pretty sure running on VMware.

Processor: Intel(R) Xeon(R) CPU E7-4860 @ 2.27GHz  2.26 GHz (2 processors)

Installed memory: 4GB

Hard drive for Database files: 300GB

Hard drive for backups: 150GB

Hard drive for logs: 100GB

Application:

We have 3 main databases that add up to about 170GB in data, a Reporting Services database (SSRS) on the same server that houses maybe 10 different reports (comprising average of 700k records each) that get generated daily.  Our user base is about 20 simultaneous users, maybe 5 of those could be considered "resource intensive" with generating data-crunching large reports.  Majority of users interact with database through asp.net website and Report server website.  Additionally, our developers use SSIS in BIDS extensively by remoting directly onto the server (2 remote connections max).  Finally, we have a fairly involved data warehousing operation that probably brings in 3 million records per day by way of SSIS packages that also run on the server.

Current Problems:

We have chronic sever server timeouts and the response time to the website is pretty bad.  I suspect the amount of memory we have (4GB) is probably a large bottleneck.  Our previous requests for additional memory has been denied with the common response that we need to perform more query optimizations.  While we aren't sql pros or (as I'm sure you can tell by our setup) db admin pros, I want to make sure I'm not expending all my time trying to squeeze out little potential performance if the hardware is the bottleneck.

Thanks all for the tl;dr avoidance!

... was hoping I could get ... a rough rough estimate of what we should be running.

Without more information about your queries and data sizes, it's really difficult to give you any kind of estimate, let alone an accurate estimate.

  Database: sql server 2008 r2 enterprise database

  Windows: Windows 2008 r2 Enterprise 64 bit, pretty sure running on
  VMware.

  Processor: Intel(R) Xeon(R) CPU E7-4860 @ 2.27GHz 2.26 GHz (2
  processors)

  Installed memory: 4GB

Two processors (I'm assuming this is exposed in the VM as 2 cores) may or may not be under-provisioned. The cores assigned to a VM aren't necessarily mapped directly to physical cores (or even allowed to use 100% of a single core when it's needed!), so you may find this is a more flexible resource than memory. Without any more information about your workload or hardware/virtualization configuration, I would say increasing this to 4 would be nice-to-have.

Memory allocation. Oh boy. This is grossly under-provisioned for the workload. Windows itself needs a bare minimum of 2-3 GB to stay happy, and each of the 2 users running BIDS on the box will require at least 500 MB each. And with that, the box is maxed out already, and I didn't even start figuring out how much the database is going to need.

  Majority of users interact with database through asp.net website and Report server website.

You didn't say, but if these are running on the same box, memory requirements for them need to be taken into account as well.

  Finally, we have a fairly involved data warehousing operation that probably brings in 3 million records per day by way of SSIS packages that also run on the server.

Assuming this runs at night when there are no live users on the system, I don't see this as a problem unless it's taking too long to run. This part of things is the least of your worries; live users are more important.

  Our previous requests for additional memory has been denied with the common response that we need to perform more query optimizations.

As I demonstrated above, the current amount of memory that's been provisioned is completely inadequate. At the same time, though, at the other end of the spectrum, it's exceedingly unlikely you'll be able to get enough memory provisioned to be able to keep the entire database in memory at once.

Even though you got a blanket response like that (which, by the way, probably had more to do with how persuasive your justification for additional resources was, and not the actual resource usage itself), it's highly likely the efficiency of the database could be improved. Yet there's no amount of tuning alone that can fix the issues you're experiencing now; the suggestion of that is a complete non-starter to me.

I would take the overall approach that the amount of memory currently provisioned is below the minimum required (which should be corrected ASAP), and additional resources may be required to improve the user experience to a usable level while improvements are made to increase the efficiency of the systems.

Here are a few thoughts (in order of attack):

You will win if you can prove how much performance improves every time you get more resources provisioned. Keep track of performance metrics using Performance Monitor logging (note: the logging part is very important), including website response times if you can. Start doing this now, before doing anything else. When you do finally get to the minimum amount of memory (you aren't going to get 32 GB right away), suddenly you now have evidence that the added memory improved things... which means adding even more would probably help, too! If you don't collect a baseline on the current configuration, you're going to miss the boat when things are bumped up to the minimum recommended level.
Analyze your server's wait statistics. This will tell you what the biggest bottleneck in the system is. You'll probably have PAGEIOLATCH_XX as the most common/highest wait time, which indicates too much I/O is being done to fetch pages from disk. This can be alleviated by adding memory, so the physical I/O's become less frequent as the needed data is already in memory. While this analysis is pretty much a foregone conclusion, the fact you've gathered these stats at all gives you more ammo when justifying the need for resources.
As I mentioned above, the bare minimum requirement for memory is not being met. Collect the set of recommended hardware requirements for all the software you're running, and maybe also grab screenshots of Task Manager. This alone should be enough to justify at least 4-8 GB more, on the spot. If they still refuse, try to convince them to allow you to try it out for a week, and give it back after that (you're collecting performance stats, so you won't need to give it back because mid-week you'll be able to prove how much it's improved the situation). If they still refuse, you're being set up to fail; URLT.
If you can offload some of the workload (in particular, avoid remoting in if at all possible), this will increase the amount of memory available for the database, which is more critical.
You won't be able to fit the entire database in memory at once, which means you need to set SQL Server's max memory setting very carefully to prevent memory over-commit, which kills performance like nothing else. Over-commit is actually even worse than simply not being able to fit all the data in memory. It's highly likely you're in this scenario right now simply because there's just no memory available at all, and it's probable that the max memory setting is set to the default (unlimited).
Since you're running SQL Server Enterprise Edition, and memory is at a premium, I would strongly consider implementing data compression. This will trade off an increase in CPU usage for space-savings of memory (and hence reduced disk accesses, which are comparatively very slow).
Tune the database. It's likely the structures and queries could use improvements as far as indexing and access patterns go. Also, if a lot of data is being frequently scanned and aggregated, creating indexed views, summary tables, or precomputed reports may be very helpful.
This might be a longshot because it probably means more hardware provisioning, but implement a caching solution. The fastest query is the one you never make.

Those are just a few ideas. The bottom line is that tuning alone will not solve the problems here, nor will hardware alone, even though the latter probably will alleviate the majority of the immediate issues. That's really how it goes: throw hardware at the problem in the short-term to put out the fire, and throw tuning at the problem in the long-term to fix the root cause as best you can.

I am running 2008 R2 with 46GB of Ram. No virtual machines. SQL Server 2008.

Databases are about 300GB.

I put the databases on a solid state drive recently and tripled my data output.

The server is currently using 45GB of Ram and running good.

FC Sata Raids and SAS SCSI Raids. 26 logical drives in all. 144 Spinning disks.

Yesterday I was only using 24GB of ram when I only had 50TB of full hard drives connected and transferring data. Today I have 160TB of full hard drives and I'm using 45GB of ram.

My application does not use over 400MB of ram.

I would suspect you need the databases on a fast solid state drive or ram drive. Basically though you need a lot more ram.


SQL Sum with MAX condition I have two tables named Claim

EmployeeClaimId
      1144
      3481
      3452
      5760
      7296

and ClaimStatus

EmployeeClaimId   Amount   Version
       7            0.00      1
       7          300.00      3
       7          150.00      3
       7          400.00      1
       7          300.00      7
      14         1860.00      1
      14          541.00      7
      14          530.00      1
      14         2490.00      1

I want the sum of amount from second table where version is max for each claimid.

Can someone help?

What is the Claim table used for?

Well, you can try that:

select EmployeeClaimId, sum(Amount)
from ClaimStatus cs
join (
    select EmployeeClaimId, max(version) as version
    from ClaimStatus
    group by EmployeeClaimId
    ) c on cs.EmployeeClaimId = c.EmployeeClaimId
        and cs.version = c.version

Here you go:

SELECT SUM( Amount )
FROM ClaimStatus
WHERE Version IN
(
  SELECT MAX( Version )
  FROM ClaimStatus
  GROUP BY EmployeeClaimId
)
GROUP BY EmployeeClaimId;


Newdatabase option is not available in sql server management studio 2014 I connect to analysis server using windows authentication. But I can't create the new database.

For some reason, SSMS doesn't provide this New Database menu option for SSAS Tabular. But you can easily create a new empty or shell database with the following XMLA script. Just click the XMLA button in the toolbar and then paste in the following. Edit the ID and Name property to the desired database name. And edit the DOMAIN\GroupName to set the group that has admin permission. This will allow members of that group to deploy over this database without being SSAS admins for the whole instance.

The following script is for SSAS 2012 SP1 or 2014 Tabular models:

<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <ObjectDefinition>
        <Database xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400">
            <ID>ShellDatabase</ID>
            <Name>ShellDatabase</Name>
            <ddl200:CompatibilityLevel>1103</ddl200:CompatibilityLevel>
            <ddl200_200:StorageEngineUsed>InMemory</ddl200_200:StorageEngineUsed>
            <Language>1033</Language>
            <Collation>Latin1_General_CI_AS</Collation>
            <DataSourceImpersonationInfo>
                <ImpersonationMode>Default</ImpersonationMode>
            </DataSourceImpersonationInfo>
            <Roles>
                <Role>
                    <ID>Role</ID>
                    <Name>Administrators</Name>
                    <Members>
                        <Member>
                            <Name>DOMAIN\GroupName</Name>
                        </Member>
                    </Members>
                </Role>
            </Roles>
            <DatabasePermissions>
                <DatabasePermission>
                    <ID>DatabasePermission</ID>
                    <Name>DatabasePermission</Name>
                    <RoleID>Role</RoleID>
                    <Process>true</Process>
                    <Read>Allowed</Read>
                    <Administer>true</Administer>
                </DatabasePermission>
            </DatabasePermissions>
        </Database>
    </ObjectDefinition>
</Create>

And for SQL 2016 Tabular models using the latest 1200 compatibility level, use the following script. Just change the name to the desired database name and the DOMAIN\GroupName to your administrators group.

{
  "create": {
    "database": {
      "name": "ShellDatabase",
      "compatibilityLevel": 1200,
      "model": {
        "culture": "en-US",
        "roles": [
          {
            "name": "Admins",
            "modelPermission": "administrator",
            "members": [
              {
                "memberName": "DOMAIN\\GroupName"
              }
            ]
          }
        ]
      }
    }
  }
}

Your Windows account probably does not have sufficient permissions assigned to it in Analysis Server. This may help: https://stackoverflow.com/questions/22402528/how-to-change-permissions-on-ssas


How to create a temporary table using VALUES in PostgreSQL I am learning PostgreSQL and trying to figure out how to create a temporary table or a WITH declaration that can be used in place of regular table, for debugging purposes.

I looked at the documentation for CREATE TABLE and it says VALUES can be used as a query but gives no example; the documentation for the VALUES clause linked therein does not have an example either?

So, I wrote a simple test as follows:

DROP TABLE IF EXISTS lookup;
CREATE TEMP TABLE lookup (
  key integer,
  val numeric
) AS
VALUES (0,-99999), (1,100);

But PostgreSQL (9.3) is complaining about

  syntax error at or near "AS"

My questions are:

How can I fix the statement above?
How can I adapt it to be used in a WITH block?

Thanks in advance.

EDIT: I am leaving the original accepted answer as it is, but please note that the edit below, as suggested by a_horse_with_no_name, is the preferred method for creating a temporary table using VALUES.
If you just want to select from some values, rather than just creating a table and inserting into it, you can do something like:
WITH  vals (k,v) AS (VALUES (0,-9999), (1, 100))
SELECT * FROM vals;

To actually create a temporary table in a similar fashion, use:
WITH  vals (k,v) AS (VALUES (0,-9999), (1, 100))
SELECT * INTO temporary table temp_table FROM vals;

EDIT: As pointed out by a_horse_with_no_name, in the docs it states that CREATE TABLE AS... is functionally similar to SELECT INTO ..., but that the former is a superset of the latter and that SELECT INTO is used in plpgslq for assigning a value to a temporary variable -- so it would fail in that case. Therefore, while the above examples are valid for plain SQL, the CREATE TABLE form should be preferred.
CREATE TEMP TABLE temp_table AS
WITH t (k, v) AS (
 VALUES
 (0::int,-99999::numeric),
 (1::int,100::numeric)
)
SELECT * FROM t;

Note, also from the comments by a_horse_with_no_name, and in the OP's original question, this includes a cast to the correct datatypes inside the values list and uses a CTE (WITH) statement.
Also, as pointed out in Evan Carrol's answer, in Postgres prior to version 12 a CTE query is always an optimization fence, ie, the CTE is always materialized. There are many good reasons for using CTEs, but there can be quite a significant performance hit, if not used carefully. There are, however, many instances where the optimization fence can actually enhance performance, so this is something to be aware of, not to blindly avoid.

WITH u AS (
    SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS account (id,name)
)
SELECT id, name, length(name) from u;