Adding constraint on data I created a table called customer, which consists of one attribute called email.

Then I would like to add a constraint to email attribute.
Following is my coding:

Create table customer
( email varchar(100));

Here is my constraint:
"@" must be included in the email

However, I don't know how to add this constraint.
I am total new to psql. Thank you very much!

Something like:

ALTER TABLE customer
    ADD CONSTRAINT email_chk CHECK ( email like '%@%' );

should work. If email is mandatory you should make it NOT NULL as well. For a more thorough handling of the email-domain, check out the link:

What is the best way to store an email address in PostgreSQL?

that @MaxVernon posted in his comment.


Overwriting old data in Azure SQL database I`m currently recieving data from sensor and storing it to Azure SQL database through Azure Stream Analytics job.
My question is: how can I overwrite old data from sensor with new data? Sensor has its own ID, so is it possible to overwrite data by sensor ID.

You can use a MERGE statement to perform a conditional INSERT/UPDATE (sometimes called UPSERT) of data in your table.

Example:

MERGE INTO dbo.Output AS Target
USING (SELECT DeviceId, ParkingStatus FROM IoInput) AS Source (Id, ParkingStatus)
    ON Target.Id = Source.Id
WHEN MATCHED THEN
    UPDATE SET ParkingStatus = Source.ParkingStatus
WHEN NOT MATCHED BY TARGET THEN
    INSERT (Id, ParkingStatus) VALUES (Id, ParkingStatus)

This statement will match records in IoInput with existing records in Output. Any matched records will update the ParkingStatus in Output with the value in IoInput. Any unmatched records in IoInput will be inserted into Output.


Orphaned transaction (SPID -2) with UOW ID 00000000-0000-0000-0000-000000000000 SYSTEM:
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
Developer Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1) (Hypervisor)

PROBLEM:
Today I encountered an "orphaned transaction" (SPID -2) with a Unit of work ID of 00000000-0000-0000-0000-000000000000

I tried executing kill '00000000-0000-0000-0000-000000000000', but got a message that this could not be done. (Sorry, I failed to capture the exact message)

There were two transactions open in the MS DTC, but they were not set to in-doubt for a status, so I could not kill them from there.

At length, I resolved this by restarting the MS DTC.

This is a development system, so absolutely anything might have happened to cause this.

QUESTIONS:
I was just wondering if anyone had seen it, and might have any insights into:

A) What might cause it
   B) If there is a more elegant way to resolve

The KILL command cannot kill a UOW of {00000000-0000-0000-0000-000000000000}, so that leaves you hanging on what to do next to get rid of it.

To get rid of spid -2 with the unit of work {00000000-0000-0000-0000-000000000000} you can do something, but it has side-effects:

Exec Sp_configure 'in-doubt xact transaction',2
-- This presumes ALL in-doubt trans are aborted
-- so it should be used with great caution.
GO
DBCC dbrecover('dbname'); -- or Alter database

I ran into this problem when test restoring databases.  We had one database that restored successfully, but failed during the DBCC CHECKDB because of a {00000000-0000-0000-0000-000000000000} UOW.

In that case DTC was not running on the restore server at all, but still the
{00000000-0000-0000-0000-000000000000} UOW was causing us problems as noted above.  So you could say that this was an edge case.

Using the approach above finally cleared the problem for us, but if you use this technique you need to make sure that you are not aborting DTC connections that are in active use.


Foreign Key Constraint fails I have the following tables:

// Base Scans
CREATE TABLE `basescans` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NULL DEFAULT NULL,
    `status_id` INT(10) UNSIGNED NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    CONSTRAINT `status_id_fk` FOREIGN KEY (`status_id`) REFERENCES `statuses` (`id`) ON UPDATE CASCADE ON DELETE SET NULL
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
ROW_FORMAT=COMPACT
AUTO_INCREMENT=29

// Statuses
CREATE TABLE `statuses` (
    `id` INT(10) UNSIGNED NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
ROW_FORMAT=DEFAULT
AUTO_INCREMENT=4

Trying to save the first table fails when I put in that foreign key constraint. Can't figure out why. Both of the columns referenced in the constraint have the same type, size, etc:

INT(10) UNSIGNED NULL

They only have a difference default value. One has a default value of NULL, the other is AUTO_INCREMENT.  I didn't think that made a difference for foreign key constraints but I could be wrong.

Both tables are InnoDB and UFT8. What am I missing here?

UPDATED: My specific error:

/* SQL Error (1452): Cannot add or update a child row: a foreign key constraint fails (`db`.<result 2 when explaining filename '#sql-31c2_22ac1e1'>, CONSTRAINT `status_id_fk` FOREIGN KEY (`status_id`) REFERENCES `statuses` (`id`) ON DELETE SET NULL ON UPDATE CASCADE) */

You need to have already declared the table that the foreign key references, before you can define a foreign key that references it.

Once you declare the second table, you can then declare the first table.  Tested here on MySQL 5.5.27.

If you need to bypass the validation, you can do this:

SET FOREIGN_KEY_CHECKS = 0;
-- declare tables
SET FOREIGN_KEY_CHECKS = 1;

http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_foreign_key_checks


high 'total_worker_time' for stored proc using OPENQUERY in SQL Server 2005 I have a stored procedure in SQL Server 2005 (SP2) which contains a single query like the following (simplified for clarity)

SELECT * FROM OPENQUERY(MYODBC, 'SELECT * FROM MyTable WHERE Option = ''Y'' ')
OPTION (MAXDOP 1)

When this proc is run I can see the plan appear in sys.dm_exec_query_stats with a high 'total_worker_time' value. However, when I run it in SQL Management Studio the statistics show a very low CPU time, as I'd expect. Although the query takes some time to return, because of the slowness of the server it is talking to, it doesn't return many rows.

I'm aware of the issue that parallel queries in SQL Server 2005 can present odd CPU times, which is why I've tried to turn off any parallism with the query hint (though I don't think that there was any in any case).

I don't know how to account for the discrepancy - can anyone give me a steer?

UPDATE: I was assuming that the problem was with the OPENQUERY so I tried looking at times for a long-running query which doesn't use OPENQUERY. In this case the statistics (gained by setting STATISTICS TIME ON) reported the CPU time at 3315ms, whereas the DMV gave it at 0.511ms. The total elapsed times reported by each method agreed.

In sys.dm_exec_query_stats total_worker_time is:

  Total amount of CPU time, in microseconds, that was consumed by
  executions of this plan since it was compiled.

So it is the total CPU time for all executions of the cached plan.  The execution_count column gives the number of times the complete plan has executed, and there is also information about the lowest and highest values recorded.  The figures from the DMV are in microseconds, whereas STATISTICS TIME output shows milliseconds.  Finally, the MAXDOP hint only applies to the portion of the query that runs locally.

I noticed you are running SQL Server 2005 SP2.  There were all sorts of timing issues with that (now unsupported) release, for some detail, see:

SQL Server 2005 SP2 will introduce new messages to the error log related to timing activities.

Consider moving to 2005 SP4.


How do triggers affect last_insert_id()? I am unable to test this right now, however I'm curious about the answer.

If I have a trigger on table A that writes a new record to table B, will LAST_INSERT_ID() tell me the updated ID for A or B?  I'm curious about both MySQL and PHP, but my understanding is that PHP simply calls MySQL's implementation.

How do I get the last inserted ID for B if it normally returns A, and vice-versa?

From mysql documentation

  The effect of a stored routine or trigger upon the value of LAST_INSERT_ID() that is seen by following statements depends on the kind of routine:
    ....
    For stored functions and triggers that change the value, the value is restored when the function or trigger ends, so following statements do not see a changed value. (Before MySQL 5.0.12, the value is not restored and following statements do see a changed value.)

Thus whatever happens inside the trigger[s] on A does not affect value returned by  LAST_INSERT_ID() executed outside of trigger's body. Withing the trigger body LAST_INSERT_ID will reflect inserts you made to another table. To get last id for B you need to store the value of LAST_INSERT_ID() somewhere(new table, or a new column in A), for instance,

delimiter /

CREATE trigger biA before insert on A
for each row

begin
 insert into B(name) values('aaa');
 set new.b_id := (select last_insert_id());
end;
/


How exactly does the one-byte "char" type work in PostgreSQL? I often see people talking about "char". I've never used it. It's defined in the docs as,

  The type "char" (note the quotes) is different from char(1) in that it only uses one byte of storage. It is internally used in the system catalogs as a simplistic enumeration type.

And further,

"char"  1 byte  single-byte internal type

So, if it's one byte, what is the domain and how would you make use of it? Is it signed or unsigned? In this post by @Erwin Brandstetter he lays it out, but I'm still confused. He's using ascii() and chr(), and provides this

SELECT i
     , chr(i)::"char"        AS i_encoded
     , ascii(chr(i)::"char") AS i_decoded
FROM   generate_series(1,256) i;

That's doing something really weird between 10 and 11.

  i  | i_encoded | i_decoded
-----+-----------+-----------
...
   8 | \x08      |         8
   9 |           |         9
  10 |          +|        10
     |           |           -- WTF is going on here.
  11 | \x0B      |        11
  12 | \x0C      |        12
...

It also gets really weird here:

 126 | ~         |       126
 127 | \x7F      |       127
 128 |           |       128
 129 |           |       128
 130 |           |       128
 131 |           |       128

Why is everything north of 128 being decoded as 128? But to take the bizzare up a bit, after 192 there is a switch and they get decoded as 192..

 190 |           |       128
 191 |           |       128
 192 |           |       192
 193 |           |       192
 194 |           |       192
 195 |           |       192
 196 |           |       192
 197 |           |       192

Erwin says

  There are several characters not meant for display. So encode before you store and decode before you display ...

I'm not sure why we should encode at all though if we're doing exactly what that questions asks

CREATE TABLE foo AS
SELECT i::"char"
FROM   generate_series(-128,127) i;

That works fine. We can get the ints back out using

SELECT i::int FROM foo;

So in short,

What is Erwin's code doing between 10-11 where the i goes null?
Why is 128 repeated so many times?
Why is 192 repeated so many times?
How do I trigger the inability to store 0, when Erwin says you cannot encode 0 this way (null character not permitted)

CREATE TABLE foo AS SELECT 0::int::"char" AS x;
SELECT x::int FROM foo;
 x
---
0

1. chr(10)

... produces the LINEFEED character (a.k.a. escape sequence \n) and psql displays the character with a newline (indicated by +). Everything correct there.

2. & 3. ascii() produces 128 or 192?

It starts with a mistake I made. I carelessly assumed "char" would cover the range of an unsigned 1-byte integer (0 to 255) in the referenced answer (now fixed), but it's actually the range of a signed 1-byte integer (-128 to 127) internally.

ascii() takes a text parameter, the implicit cast from "char" to text produces a multibyte-encoded character in unicode, and the function returns (per documentation on ascii()):

  ASCII code of the first character of the argument. For UTF8 returns the Unicode code point of the character. For other multibyte encodings, the argument must be an ASCII character.

So we get a lot of truncated values. 128 and 192 are byte values for the leading byte of multibyte characters.

4. The null byte

The inability to store null bytes only affects regular character types (text, char, varchar), not "char". It applies to my buggy example, because I cast to text as stepping stone. While casting between "char" and integer directly, the limitation does not apply. The manual on chr():

  The NULL (0) character is not allowed because text data types cannot
  store such bytes.

Not so for "char", where 0 is mapped to the empty string '':

SELECT ''::"char"::int  -- 0
     , 0::"char" = '';  -- t

Remember: "char" is still an "internal" type intended for simple and cheap enumeration. Not officially designed for what we are doing here, and not portable to other RDBMS. There are no guarantees by the Postgres project for this.


Error querying postgresql table created in pgAdmin I have successfully created a table in pgAdmin which generated the code:
CREATE TABLE public."Test3"
(
    "PID" integer,
    "Name" character varying(20),
    PRIMARY KEY ("PID")
)

TABLESPACE pg_default;

ALTER TABLE public."Test3"
    OWNER to postgres;

However when I try to query the table with:
Select * from Test3;

I get the error:
ERROR:  relation "test3" does not exist
LINE 1: Select * from Test3;
                      ^
SQL state: 42P01 Character: 15

Same thing happens if I leave out the TABLESPACE line. Same thing happens if I try to query a table after importing rows from a .csv file. Seems like I must be missing something obvious.
Edit: I am trying to create the table in a database named "ABR"

PostgreSQL implicitly converts all the SQL that you enter into lower case, unless you wrap things in double-quotes in which case you have to get the case exactly right.
Wherever possible, you should avoid needing quotes or other wrapper characters, i.e. don't use special characters in identifier names.  It will only come back to bite you.
create table public.test3
( pid integer
, name character varying(20)
, primary key (pid)
)


Maintaining or Re-Gaining access after SQL Server database restore without SYSADMIN rights? I am struggling with maintaining or re-gaining access after restoring an SQL Server database (2008 R2, if that makes any difference). Here is what I am trying:

SQLServer login APPUSER is member of the dbcreator group, but not sysadmin
APPUSER can run CREATE DATABASE and will own the database - fine
SQLServer login APPUSER maps to the database's user APPUSER with dbowner permission if APPUSER runs RESTORE DATABASE using a backup that was created on a different server, then RESTORE DATABASE will complete fine, but APPUSER seems to loose all access to the database
I checked sys.databases/owner_sid of the restored database in
question and it matches sys.server_principals/sid of APPUSER. This makes me believe that APPUSER owns the database
I understand that inside the database, user APPUSER is no longer linked to the current SQLServer login APPUSER because the sid points to a user that was created on the other server and does not match the local APPUSER's sid

What I am trying to find is a way to have APPUSER restore a database and either maintain ownership or re-gain ownership without the need to have sysadmin rights. What does NOT work:

EXEC sp_change_users_login 'Update_One' ... - permission denied for APPUSER, works for SA
EXEC sp_change_users_login 'Auto_fix' ... - permission denies for APPUSER, works for SA
ALTER AUTHORIZATION ... - permission denies for APPUSER, works for SA

So what do I need to do so that my APPUSER can restore a database and use it, even if the DB was from another server without APPUSER having sysadmin rights?

From a permission management perspective, it should be sufficient to grant permission to a login so it can create and restore its own database (dbcreator) but not require sysadmin to restore if the database came from a different PC.

In general, after having restored the database as APPUSER, that login seems to have no access at all to the database. Any SELECT would fail as well.
This is surprising, since APPUSER has performed the restore and the content of sys.databases/owner_sid seems to indicate that APPUSER is indeed the owner. Granted, a different APPUSER (different sid) has originally created the database on another server, but this is what I am trying to fix. I have at least 3 different ways for fixing this with sysadmin permissions, but none through the APPUSER login.

to relink the login to the correct id you will have to run (I assume that the login is appuser, and the databas user appuser):

  alter user appuser with login = appuser ;

From BOL:

To change the name of a user requires ALTER ANY USER on the database. To change the default schema requires ALTER permission on the user. Users can change only their own default schema.
Requires CONTROL permission on the database to remap a user to a login

As you explained that you have the db_owner role on the database, it should work.


How to design a database for search history with multiple facets? I want to show users a history of their searches, which can often be complicated:

any of these terms: [a,b,c]
all of these terms: [d,e,f]
not these colors: [h,i,j]

Since these searches are done through a UI (not a search input where they would type "a OR b etc...") I assume I would need to store each value in a table.

Would it be overkill to create a table for each facet ("search_terms", "search_colors", etc)? or is there a simpler way?

| search_id |   term(_id)  | operator(enum)|
|:---------:|:------------:|:-------------:|
| 1         | blue         |     any       |
| 1         | green        |     any       |
| 1         | red          |     any       |
| 1         | musthave     |     all       |
| 1         | exclude_me   |     not       |
| 1         | exclude_also |     not       |

Some other considerations are:

The web-URL can change over time, so I don't want to store that as an easy way out.
The index/3rd party service I'm searching could change, so I similarly can't save the query I make.

(The terms themselves can be its own table with a reference instead).

What you store should mimic what the user sees in the UI.  That way you can present it in a format that is familiar to him.

This format may, or may not, be anything close to the SQL that has ANDs and ORs.


What techniques to use when designing one or more tables to allow keeping a pending and approved version of objects? I have several objects in my systems that need to go through approvals. Users can create these objects, such as uploading a jpg to the server as a new Media object, and they need to be in a pending state until a Reviewer approves it. Users can also update objects, such as uploading a new jpg to the previous Media object. In this case, I need to store the approved version of this Media object alongside the pending version until the pending version gets approved (or deleted) by a Reviewer.

I don't need to store a complete history of objects, only the approved version and the most recent pending version until it gets approved or deleted.

As far as reading from this table, I need to be able to search for objects, where objects with both a pending and approved versions will only return the pending version. I also need to be able to get only the approved versions of objects.

Objects, for example Media, can be assigned as children of other objects, for example as Slots in a Playlist. I'm hoping that if you assign a Media to a Playlist, as the Media object gets updated with newly approved versions, the association of Media to Playlist doesn't need to be altered to reflect the change in object.

My previous attempt at designing this did not turn out as easy to use as I would like. I created tables for these objects that require approval by have a column for ModerationStatus which held information about whether the object was pending or approved, and used a Primary Key on ID and ModerationStatus. This way I could assign objects as a child of other objects, and keep track of the ID for the child which wouldn't change as the object was subjected to multiple updated and approvals. It turned out clunky, the Slots for Playlists needed to have Foreign Keys to both the ID and ModerationStatus of these objects while I wish I would only need an ID, searching for objects with a priority on their pending versions required using a subquery in the Where clause. However I am no expert on SQL design, so these little quirks which see I see as "clunky" may be fine or expected as from a DBA's standpoint. Regardless I felt the need to look further before committing to a design.

Are there any suggestions or well understood techniques you can recommend for designing such a schema?

It turned out clunky, the Slots for Playlists needed to have Foreign
  Keys to both the ID and ModerationStatus of these objects while I wish
  I would only need an ID

Why you wish to have only ID.you can have composite FK.Suppose only ID was FK then what problem it would solve ?This is not clear.

  searching for objects with a priority on their pending versions
  required using a subquery in the Where clause

This requirement is not clear to me.can you explain with example ?

Also how this table will be populated on daily basis ?

With how many rows will be pending and approved.
Do you soft delete or permanent delete ?

You can keep 2 tables . PendingImage and ApprovedImage.when user upload then record goes to PendingImage.when that record is approved then it goes to ApprovedImage.

Child table will have FK relation with ApprovedImage table.

I think only in few places you will require info from both table.
And very frequently use table in front end will be  ApprovedImage

I will modify my answer after clearing few doubts.


column identifier versus separate table with related data with different purposes Very new to database management here. I'm trying to figure out what rule of thumb I should be following when it comes to making separate tables for data rather than just labeling it as different within the same table

say I have a fleet of robots. I could have tables that differentiate foodmaking robots from cleaner robots, killer robots, etc. or I could have a table of robots with a field that specifies the type of robot it is. which is better and why?

In my opinion, It is very much depends on the details.

Does cleaner robot has different attributes compare to killer robot?
Do you have different type of killer/cleaner robots?

if your answer to any of these questions is yes, Then probably you need a separate table for each type (Normalization). Using different table for different type simplifies your job when you want to extend your application. Attributes with functional dependency (2nd Normal Form) and transitive dependency (3th Normal Form) need to be placed in the same table. You may read more about pros and cons of normalization.


What are the benefits vs. drawbacks to using a clustered index in SQL Server I have scenario where the data will be loaded pretty much in the order it will be grouped by in reports (date, varchar foreign key, int foreign key).  Is there still a benefit to indexing?  I was going to define the run date as the clustered index, and index the two foreign keys.  Since the data will be loaded via a process that will already have the data sorted, I wasn't sure if the benefit to the indexes still outweigh the overhead?  I am really new at this.  The table will probably be defined with a total of 4 fields, a max of 22,000,000 rows per run date, and retained for 60 days.

This is a complicated topic, I will try to be brief.

Benefit: Performance boost if implemented correctly

Drawback: requires understanding of clustered/nonclustered indexes and storage implications

Heaps (tables without clustered indexes) are fine for loading but can lead to poorly performing queries especially when dealing with millions of records as in your case.  Even though you sorted your data prior to inserting, SQL Server does not know this and will simply put new records wherever there is room.
If there are not even any non-clustered indexes, queries against the heap will result in full table scans.

Assuming you are pre-sorting the data by datetime, and assuming that your date column is datetime, clustering on this column will result in extremely fast queries as long as they filter against the date.  There are drawbacks, however, one of which being SQL Server will addding a 4 byte uniquifier to the index.  Also any additional indexes defined will use the clustered index as the key, so you are in effect adding 12 bytes (8 bytes for datetime + 4 bytes for uniquifier) to the storage requirements for every value in the index.

I would add synthetic key instead.  Make it a self incrementing integer value.  Make it the primary key of the table and SQL Server will by default add a clustered index to that column.  No uniquifier will be needed, so additional indexes will only require 4 bytes of storage for the key.

Note:
 varchar foreign keys can lead to poor performance as well.  Change the base table to have an integer primary key instead.


Postgresql: Permission denied to schema to user created based on master with rights I'm trying to write a function that eventually will rotate users. Currently I have the code below which works and creates a new user using the prior user which ultimately has the same rights as the master user, ie, can do everything.

select mysch.dblink('dbname=mydb user=themasteruser password=abc123 connect_timeout=200000 host=localhost', 'CREATE USER newuname WITH PASSWORD ''pass1'' CREATEDB CREATEROLE;GRANT rds_superuser TO newuname;');

DROP SERVER IF EXISTS fs_link_b CASCADE;
CREATE SERVER fs_link_b FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'localhost', port '5432', dbname 'mydb ', connect_timeout '200000');
CREATE USER MAPPING FOR newuname SERVER fs_link_b OPTIONS ("user" 'newuname',password 'pass1');
ALTER SERVER fs_link_b OWNER TO newuname;

However, when I make a connection with the above created user and I try to run the query below:

select * from mysch.dblink('link_b', 'select usename from PG_USER limit 1;') as t(uu text);

I get this error:

  SQL Error [42501]: ERROR: permission denied for schema mysch

If I created the user based on a master user, then should it not have all rights as the master user as created above? Or Do I need to do a bunch of individual GRANTS still?

Grant usage on schema mysch  to newuname;

Grants on a database object doesn't GRANT rights to the schemas.

GRANT


sp_BlitzWho Does Not Complete Just trying out sp_BlitzWho for the first time.  On one specific server it never completes but continues to consume CPU, as observed via sp_whoisactive.

I have played with the SQL generated and isolated it down to the "blocked" outer apply.  If I comment out the WHERE clause in the "blocked" outer apply it completes.  Or if I only execute either of the conditions in the WHERE it completes.

WHERE (s.session_id = sys2.spid
   OR s.session_id = sys2.blocked)

I don't really know how to take this further.  I did consider 2 outer applies rather then the one with the OR in the WHERE.

I am running sp_BlitzWho Version = '5.2'; VersionDate = '20170406'.

As Brent pointed out, this is not the current version from GitHub.

Yeah, that's the older version. We released a new one a couple days ago that has a performance improvement for that query.

The join in the new code should look like this (as well as use a table variable (I know, I know), and also READ UNCOMMITTED):

WHERE (s.session_id = b.session_id
OR s.session_id = b.blocking_session_id)

The newer version should complete in roughly the same time as the lovely and talented sp_WhoIsActive.


Multiple Set Quoted_identifier within one script takes the last one? I'm using SQL Server 2019 and just found a weird behaviour.
Research has not gotten me anywhere further.
Can someone please explain this behaviour?
SET QUOTED_IDENTIFIER ON;
if ((256 & @@options) = 256) print '1- quoted_identifier is on' else print '1- quoted_identifier is off';
BEGIN TRY
    if ((256 & @@options) = 256) print '2- quoted_identifier is on' else print '2- quoted_identifier is off';
END TRY
BEGIN CATCH
    if ((256 & @@options) = 256) print '3- quoted_identifier is on' else print '3- quoted_identifier is off';
    -- SET QUOTED_IDENTIFIER OFF
    -- if ((256 & @@options) = 256) print '4- quoted_identifier is on' else print '4- quoted_identifier is off';
END CATCH

Returns:
1- quoted_identifier is on
2- quoted_identifier is on

but the following code:
SET QUOTED_IDENTIFIER ON;
if ((256 & @@options) = 256) print '1- quoted_identifier is on' else print '1- quoted_identifier is off';
BEGIN TRY
    if ((256 & @@options) = 256) print '2- quoted_identifier is on' else print '2- quoted_identifier is off';
END TRY
BEGIN CATCH
    if ((256 & @@options) = 256) print '3- quoted_identifier is on' else print '3- quoted_identifier is off';
    SET QUOTED_IDENTIFIER OFF
    if ((256 & @@options) = 256) print '4- quoted_identifier is on' else print '4- quoted_identifier is off';
END CATCH

Returns:
1- quoted_identifier is off
2- quoted_identifier is off

Even though it isn't going into the catch!!!
I must be missing something.
I was even able to simplify the code to the most simple :
SET QUOTED_IDENTIFIER ON;
if ((256 & @@options) = 256) print '1- quoted_identifier is on' else print '1- quoted_identifier is off';
SET QUOTED_IDENTIFIER OFF
if ((256 & @@options) = 256) print '2- quoted_identifier is on' else print '2- quoted_identifier is off';

Results:
1- quoted_identifier is off
2- quoted_identifier is off

I have some code that uses FOR XML, which requires me to set quoted Identifier ON, but I need to put it back to OFF, no matter if the XML portion succeeds or fails.  How would you do that?
My tests show that if I put the SET QUOTED_IDENTIFIER to off in the CATCH, the insert fails to say that my quoted identifier is not set properly although it is set at the beginning of the TRY to ON.

This is by design:

For a top-level ad-hoc batch parsing begins using the session's current setting for QUOTED_IDENTIFIER. As the batch is parsed any occurrence of SET QUOTED_IDENTIFIER will change the parsing behavior from that point on, and save that setting for the session. So after the batch is parsed and executed, the session's QUOTED_IDENTIFER setting will be set according to the last occurrence of SET QUOTED_IDENTIFIER in the batch.

The behaviour of QUOTED_IDENTIFIER is therefore dependent on the parsing of the batch, not the execution. The default at the start of parsing the batch comes from the current connection settings.
@@OPTIONS only shows you the current default, that would be used if a batch was parsed. This is why SSMS always puts SET in a separate batch.
The execution of SET is not relevant to the behaviour of @@OPTIONS, except to change later batches' default.

You can see this in action in a db-fiddle
set QUOTED_IDENTIFIER  on;
create table "select"(i int);
drop table "select";
GO

set QUOTED_IDENTIFIER  off;
create table "select"(i int);
drop table "select";
--  Incorrect syntax near 'select'.
GO

set QUOTED_IDENTIFIER  off;
if (1=0)
begin
    set QUOTED_IDENTIFIER  on;
end
create table "select"(i int);
drop table "select";
GO

set QUOTED_IDENTIFIER  on;
if (1=0)
begin
    set QUOTED_IDENTIFIER  off;
end
create table "select"(i int);
drop table "select";
-- Incorrect syntax near 'select'.
GO


Reporting Database Considerations We have a very large database that we want to write almost real time. We want to take data and move it to another instance of the database on top of which will be a GUI and MI tool which will be able to report on the data.

I've got the following so far - does anybody else have any considerations I should bear in mind when spec'ing this:

Data Migration / Retention Period / Legal Requirements of Storage
Tables / Fields that need to be available in reporting database
Permissioning / User Access
Key fields that need to be queried
Disaster Recovery Requirements
Replication Timing

I think what you're looking for is, "What are all the things I need to keep in mind when designing a data warehouse?"

There's a lot of good resources out there on it:

https://www.cu.edu/irm/stds/ddg/dzproces.html
http://www.slideshare.net/markginnebaugh/architecting-a-data-warehouse-a-case-study
http://msdn.microsoft.com/en-us/library/aa902672%28SQL.80%29.aspx


Is columnstore a good option for UPDATES with WHERE clauses? I am developing a database project on SQL Server and I am thinking whether using columnstore index is a good idea.
The project consists of a table (A) that will hold a large number of rows, with many repeated values for a column. Every day, a pack of new rows will be added to the table, with a "DateId" for each pack.
After that, I will need to update a different table (B) joining with A and filtering A for the "DateId" and other columns.
Example in SQL:
CREATE TABLE A (
  [Id] [BIGINT] IDENTITY(1,1) NOT NULL,
  [DateId] [INT] NOT NULL,
  [B_Id] [BIGINT] NOT NULL,
  -- other columns...
  INDEX cci_A CLUSTERED COLUMNSTORE
)

CREATE TABLE B (
  [Id] [BIGINT] IDENTITY(1,1) NOT NULL,
  -- other columns...
  INDEX cci_B CLUSTERED COLUMNSTORE
)

UPDATE B
SET ...
FROM A
INNER JOIN B ON A.B_Id = B.Id
WHERE A.DateId = @myDateId

Is columnstore a good choice in this case?

Modifying a row will cause the old row to be flagged as "deleted" (but its still in the column store index) and the new row to be added to the deltastore (row-based storage which will be compressed when it reaches about 1 million rows). So, as you can imagine, many updates will to some extent degrade your columnstore index over time. You can of course do index maintenance, but a columnstore index on B might not be the best choice...


How to get SQL Server Developer edition for installation in non-production for the organisation Kindly shed some light on this for me please.
Through my free membership of Visual Studio Dev Essentials, I have a free copy of SQL Server Developer edition installed on my personal machine.
How can we get free Developer edition copies for the organisation, we need to use this for a number of non-production instances?
I am aware that each user that accesses the non-production instance, through the application or back-end tool, needs to have a developer license.
Could you please give some guidance on how to go about doing this at the organisation level? Does each developer/tester/dba have to have their own Visual Studio Dev Essentials free subscription? That doesn't sound right to me, could you please guide or point me to an article that can help me?
The client has to use 2012 at this point in time, due to application support compatibility.
Thank you so much.

Licensing questions are generally not applicable to this forum, but this is a pretty high level one that's more about process, and for an edition that technically doesn't require paid licensing.
Developer Edition is freely available right off of Microsoft's website for SQL Server. The direct download link is here. This will be for the latest version, 2019.
From a process perspective, you just need one copy of the installer and your company's System Admin should be able to use it to install Developer Edition on all of the servers you need to test with.


Why is a temp table a more efficient solution to the Halloween Problem than an eager spool? Consider the following query that inserts rows from a source table only if they aren't already in the target table:

INSERT INTO dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR WITH (TABLOCK)
SELECT maybe_new_rows.ID
FROM dbo.A_HEAP_OF_MOSTLY_NEW_ROWS maybe_new_rows
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR halloween
    WHERE maybe_new_rows.ID = halloween.ID
)
OPTION (MAXDOP 1, QUERYTRACEON 7470);

One possible plan shape includes a merge join and an eager spool. The eager spool operator is present to solve the Halloween Problem:

On my machine, the above code executes in about 6900 ms. Repro code to create the tables is included at the bottom of the question. If I'm dissatisfied with performance I might try to load the rows to be inserted into a temp table instead of relying on the eager spool. Here's one possible implementation:

DROP TABLE IF EXISTS #CONSULTANT_RECOMMENDED_TEMP_TABLE;
CREATE TABLE #CONSULTANT_RECOMMENDED_TEMP_TABLE (
    ID BIGINT,
    PRIMARY KEY (ID)
);

INSERT INTO #CONSULTANT_RECOMMENDED_TEMP_TABLE WITH (TABLOCK)
SELECT maybe_new_rows.ID
FROM dbo.A_HEAP_OF_MOSTLY_NEW_ROWS maybe_new_rows
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR halloween
    WHERE maybe_new_rows.ID = halloween.ID
)
OPTION (MAXDOP 1, QUERYTRACEON 7470);

INSERT INTO dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR WITH (TABLOCK)
SELECT new_rows.ID
FROM #CONSULTANT_RECOMMENDED_TEMP_TABLE new_rows
OPTION (MAXDOP 1);

The new code executes in about 4400 ms. I can get actual plans and use Actual Time Statistics™ to examine where time is spent at the operator level. Note that asking for an actual plan adds significant overhead for these queries so totals will not match the previous results.

╔═════════════╦═════════════╦══════════════╗
║  operator   ║ first query ║ second query ║
╠═════════════╬═════════════╬══════════════╣
║ big scan    ║ 1771        ║ 1744         ║
║ little scan ║ 163         ║ 166          ║
║ sort        ║ 531         ║ 530          ║
║ merge join  ║ 709         ║ 669          ║
║ spool       ║ 3202        ║ N/A          ║
║ temp insert ║ N/A         ║ 422          ║
║ temp scan   ║ N/A         ║ 187          ║
║ insert      ║ 3122        ║ 1545         ║
╚═════════════╩═════════════╩══════════════╝

The query plan with the eager spool seems to spend significantly more time on the insert and spool operators compared to the plan that uses the temp table.

Why is the plan with the temp table more efficient? Isn't an eager spool mostly just an internal temp table anyway? I believe I am looking for answers that focus on internals. I'm able to see how the call stacks are different but can't figure out the big picture.

I am on SQL Server 2017 CU 11 in case someone wants to know. Here is code to populate the tables used in the above queries:

DROP TABLE IF EXISTS dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR;

CREATE TABLE dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR (
ID BIGINT NOT NULL,
PRIMARY KEY (ID)
);

INSERT INTO dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR WITH (TABLOCK)
SELECT TOP (20000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
CROSS JOIN master..spt_values t3
OPTION (MAXDOP 1);

DROP TABLE IF EXISTS dbo.A_HEAP_OF_MOSTLY_NEW_ROWS;

CREATE TABLE dbo.A_HEAP_OF_MOSTLY_NEW_ROWS (
ID BIGINT NOT NULL
);

INSERT INTO dbo.A_HEAP_OF_MOSTLY_NEW_ROWS WITH (TABLOCK)
SELECT TOP (1900000) 19999999 + ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

This is what I call Manual Halloween Protection.

You can find an example of it being used with an update statement in my article Optimizing Update Queries. One has to be a bit careful to preserve the same semantics, for example by locking the target table against all concurrent modifications while the separate queries execute, if that is relevant in your scenario.

  Why is the plan with the temp table more efficient? Isn't an eager spool mostly just an internal temp table anyway?

A spool has some of the characteristics of a temporary table, but the two are not exact equivalents. In particular, a spool is essentially a row-by-row unordered insert to a b-tree structure. It does benefit from locking and logging optimizations, but does not support bulk load optimizations.

Consequently, one can often get better performance by splitting the query in a natural way: Bulk loading the new rows into a temporary table or variable, then performing an optimized insert (without explicit Halloween Protection) from the temporary object.

Making this separation also allows you extra freedom to tune the read and write portions of the original statement separately.

As a side note, it is interesting to think about how the Halloween Problem might be addressed using row versions. Perhaps a future version of SQL Server will provide that feature in suitable circumstances.

As Michael Kutz alluded to in a comment, you could also explore the possibility of exploiting the hole-filling optimization to avoid explicit HP. One way to achieve this for the demo is to create a unique index (clustered if you like) on the ID column of A_HEAP_OF_MOSTLY_NEW_ROWS.

CREATE UNIQUE INDEX i ON dbo.A_HEAP_OF_MOSTLY_NEW_ROWS (ID);

With that guarantee in place the optimizer can use hole-filling and rowset sharing:

MERGE dbo.HALLOWEEN_IS_COMING_EARLY_THIS_YEAR WITH (SERIALIZABLE) AS HICETY
USING dbo.A_HEAP_OF_MOSTLY_NEW_ROWS AS AHOMNR
    ON AHOMNR.ID = HICETY.ID
WHEN NOT MATCHED BY TARGET
THEN INSERT (ID) VALUES (AHOMNR.ID);

While interesting, you will still be able to achieve better performance in many cases by employing carefully-implemented Manual Halloween Protection.


What is the best way to apply production changes (DML & DDL) in an environment with thousands of databases? What is the best way to apply production changes (DML & DDL) in an environment with thousands of databases on SQL Server?
I have seen some products out there, but I'd like to know some expecience of you guys.

Thanks

In very high level, I would suggest two points (which I always follow)

Automation: highly recommend to use PowerShell to do the various change implementation steps.
Rollback plan: always have a rollback plan in place, but rollback can be in different ways, such as backup & restore, creating a snapshot, recreating an object using pre-implementation version etc.

But in my experience, some very tricky work is security related issue, such as dropping a view and recreating it, all the previously granted permissions are lost, usually, this is not known until users start to complain.

Anyway, your sql environment and your business requirements will play key roles in your Change Management strategy.


Monitor SQL Server blocked processes I have been using sp_whoisactive and this is very helpful in finding details of running sessions and blocking.

I wanted to check whether there is any option to schedule a job based on this procedure and alert me only when there is active blocking on database and blocking is held for more than configurable number of minutes.

Also, alert should be triggered only when it reaches more than configurable number of blocking on database.

I used the details given at http://www.sqlserver-dba.com/2017/01/how-to-monitor-blocked-processes-with-sql-alert-and-email-sp_whoisactive-report.html however it sends alert on every run and doesn't seem to check regarding blocking. Sends email whether there is only one query running or multiple queries running, whether it is with blocking or without blocking. Not sure, if I am missing anything.

The script you linked to uses SQL Server's built-in Blocked Process Report. It doesn't have any configuration options about the level of blocking - it simply wakes up on an interval (say, every 5 seconds), checks for blocking, and if it finds any, runs the script.

There is a Blocked Process Threshold setting, but that just determines the interval at which the Blocked Process Report runs - like every 5 seconds. (Don't go lower than that.)

If you want to build your own custom Blocked Process Report and put logic in there about how much blocking is occurring, and in which databases, that would be an exercise left for the reader. You can start with Darko's Building a Custom Blocked Process Report.

From my own experience over the years, I've seen people who wanted to build these kinds of reports, and then the very next thing they do is build an Outlook rule to move all of these alerts into a folder, and they read 'em later. If you're going to do that, don't bother sending emails.

Just set up an Extended Events session to look for blocked processes and deadlocks - note that you'll need to change the file locations & names for your server:

CREATE EVENT SESSION [blocked_process] ON SERVER
ADD EVENT sqlserver.blocked_process_report(
    ACTION(sqlserver.client_app_name,
           sqlserver.client_hostname,
           sqlserver.database_name)) ,
ADD EVENT sqlserver.xml_deadlock_report (
    ACTION(sqlserver.client_app_name,
           sqlserver.client_hostname,
           sqlserver.database_name))
ADD TARGET package0.asynchronous_file_target
(SET filename = N'c:\temp\XEventSessions\blocked_process.xel',
     metadatafile = N'c:\temp\XEventSessions\blocked_process.xem',
     max_file_size=(65536),
     max_rollover_files=5)
WITH (MAX_DISPATCH_LATENCY = 5SECONDS)
GO

Enable SQL Server's Blocked Process Report:

EXEC sp_configure ‘show advanced options’, 1 ;
GO
RECONFIGURE ;
GO
/* Enabled the blocked process report */
EXEC sp_configure 'blocked process threshold', '5';
RECONFIGURE
GO
/* Start the Extended Events session */
ALTER EVENT SESSION [blocked_process] ON SERVER
STATE = START;

And when you want to check for blocking, this query reads the files:

WITH events_cte AS (
  SELECT
    xevents.event_data,
    DATEADD(mi,
    DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP),
    xevents.event_data.value(
      '(event/@timestamp)[1]', 'datetime2')) AS [event time] ,
    xevents.event_data.value(
      '(event/action[@name="client_app_name"]/value)[1]', 'nvarchar(128)')
      AS [client app name],
    xevents.event_data.value(
      '(event/action[@name="client_hostname"]/value)[1]', 'nvarchar(max)')
      AS [client host name],
    xevents.event_data.value(
      '(event[@name="blocked_process_report"]/data[@name="database_name"]/value)[1]', 'nvarchar(max)')
      AS [database name],
    xevents.event_data.value(
      '(event[@name="blocked_process_report"]/data[@name="database_id"]/value)[1]', 'int')
      AS [database_id],
    xevents.event_data.value(
      '(event[@name="blocked_process_report"]/data[@name="object_id"]/value)[1]', 'int')
      AS [object_id],
    xevents.event_data.value(
      '(event[@name="blocked_process_report"]/data[@name="index_id"]/value)[1]', 'int')
      AS [index_id],
    xevents.event_data.value(
      '(event[@name="blocked_process_report"]/data[@name="duration"]/value)[1]', 'bigint') / 1000
      AS [duration (ms)],
    xevents.event_data.value(
      '(event[@name="blocked_process_report"]/data[@name="lock_mode"]/text)[1]', 'varchar')
      AS [lock_mode],
    xevents.event_data.value(
      '(event[@name="blocked_process_report"]/data[@name="login_sid"]/value)[1]', 'int')
      AS [login_sid],
    xevents.event_data.query(
      '(event[@name="blocked_process_report"]/data[@name="blocked_process"]/value/blocked-process-report)[1]')
      AS blocked_process_report,
    xevents.event_data.query(
      '(event/data[@name="xml_report"]/value/deadlock)[1]')
      AS deadlock_graph
  FROM    sys.fn_xe_file_target_read_file
    ('C:\temp\XEventSessions\blocked_process*.xel',
     'C:\temp\XEventSessions\blocked_process*.xem',
     null, null)
    CROSS APPLY (SELECT CAST(event_data AS XML) AS event_data) as xevents
)
SELECT
  CASE WHEN blocked_process_report.value('(blocked-process-report[@monitorLoop])[1]', 'nvarchar(max)') IS NULL
       THEN 'Deadlock'
       ELSE 'Blocked Process'
       END AS ReportType,
  [event time],
  CASE [client app name] WHEN '' THEN ' -- N/A -- '
                         ELSE [client app name]
                         END AS [client app _name],
  CASE [client host name] WHEN '' THEN ' -- N/A -- '
                          ELSE [client host name]
                          END AS [client host name],
  [database name],
  COALESCE(OBJECT_SCHEMA_NAME(object_id, database_id), ' -- N/A -- ') AS [schema],
  COALESCE(OBJECT_NAME(object_id, database_id), ' -- N/A -- ') AS [table],
  index_id,
  [duration (ms)],
  lock_mode,
  COALESCE(SUSER_NAME(login_sid), ' -- N/A -- ') AS username,
  CASE WHEN blocked_process_report.value('(blocked-process-report[@monitorLoop])[1]', 'nvarchar(max)') IS NULL
       THEN deadlock_graph
       ELSE blocked_process_report
       END AS Report
FROM events_cte
ORDER BY [event time] DESC ;

Hope that helps get you started. Also, just FYI, I edited your question to remove the part about sp_WhoIsActive being mine - it's not. It's Adam Machanic's. If you ever meet him in person, you should say thank you to him and buy him his drink of choice.


SQL Server not using all CPU cores/threads After upgrading our SQL Server's hardware, we noticed in the Windows Task Manager that the SQL instance is only using half of the threads available to it:

.

The server has the following hardware and software:

Windows 2008 R2 Enterprise 64bit SP1
Intel Xeon E7-4870 - 4 processors (40 cores, 80 threads)
Microsoft SQL Server 2012 Enterprise Edition (64-bit)

Running select cpu_count from sys.dm_os_sys_info returns 40.

The OS sees all 80 threads.

Why is only half the server's processing power being used?

We have the same hardware and software on two servers and they both exhibit the same behavior.

In the SQL Server error log, it should tell you why. I found this on a customer system (really just pasting the message for Google juice):

  SQL Server detected 4 sockets with 8 cores per socket and 16 logical processors per socket, 64 total logical processors; using 40 logical processors based on SQL Server licensing. This is an informational message; no user action is required.

I got the likely explanation from here:

  For customers with Software Assurance on existing SQL EE Server licenses (or access to them under their current Enterprise Agreements during term) a version of Enterprise Edition was created to enable them to upgrade to SQL Server 2012. This version has technical restrictions limiting an instance to using only 20 processor cores (40 CPU threads with Hyperthreading).

Summary: in the error log, the edition will be reported as either Enterprise Edition or Enterprise Edition: Core-based Licensing. If it says the former, as in the case of the aforementioned customer system, you will need to obtain a core-based licence to use all the available cores.

If that isn't the case and you're already licenced for all cores, check your affinity mask settings, particularly if they were set, and the underlying hardware was upgraded.


Availability Group Notification when new DB is added When new DB is added to SQL Server's AG setup, a notification email like below is sent to us.

  The availability group database "XXXX" is changing roles from "SECONDARY" to "SECONDARY" because the mirroring session or availability group failed over due to role synchronization. This is an informational message only.

What is confusing is the phrase

  "changing roles from "SECONDARY" to "SECONDARY" ..."

Can someone please decode the meaning of it?
Is it normal that a notification of such is sent when new DBs are added or am I doing it wrong or any other issues ?

--In 'thoughts'...

...a notification email like below is sent to us

Sounds like you have alerts set up for error number 1480.  This is a good thing to have on systems that use Availability Groups.

  Can someone please decode the meaning of it?

When the message says "SECONDARY" to "SECONDARY" like that, it normally means there was just a brief disconnect between the PRIMARY and the SECONDARY, and the connection was re-established just before the error was raised.  It's essentially bad timing.

  Is it normal that a notification of such is sent when new DBs are added or am I doing it wrong or any other issues ?

I wouldn't call it "normal" but I don't think it's a cause for alarm, or something that you're explicitly causing.  I have heard that this can happen from time to time, especially on VMs with low core counts (1-3 cores).

I've personally experienced this more when adding a database to an AG with automatic seeding turned on.  I haven't seen any problems arise related to this no-op "failover" message.


Why doesn't \COPY work when called from a PL/pqSQL function? I am trying to use the \COPY function to fill the database from text files on the client side. I am using the Postgres interactive terminal (psql) to do this task.

I wrote the following script and saved it to test.sql:

CREATE FUNCTION copy_stuff_to_db(path text, encoding text) RETURNS void
AS $$
BEGIN
    \COPY table1 FROM path || 'table1.asc' WITH ENCODING encoding;
END;
$$ LANGUAGE plpgsql;

PERFORM copy_stuff_to_db('/home/martin', 'ISO 8859-1')

I execute it like this:

psql --port 5432 --host localhost -d mydb --username postgres -f test.sql

But I get an error:

psql:test.sql:12: ERROR:  syntax error at or near "\"
LINE 4: \COPY table1 FROM path || 'table1.asc' WITH DE...
        ^

At first I thought that the terminal doesn't do PL/pgSQL and that I will have to put my files and run the script serverside. However, when I call the function directly (i.e. I modify the previous script to):

\COPY table1 FROM '/home/martin/table1.asc' WITH ENCODING 'ISO 8859-1';

Then everything works. It seems that the \COPY function does not work when called from another function for some reason.

What am I doing wrong?

TLDR: \copy is the psql instruction, not a PL/pgSQL command. It cannot be interpreted by the server, only by the psql client itself.

The psql client, as any other database client software, has very limited capabilities. It cannot by itself read or write table rows (because they are physically located at the database server), execute stored procedures and functions (because, again, they reside on the server and execute on the server) etc. All it can do is send an SQL command to the server and present its result to the user.

As an example, when you type select foo from bar in psql and press Enter, the client opens a cursor for the given query text, lets the server process the query, fetches the result set, and shows it to you formatted in some way.

Going back to \copy, here's what documentation has to say:

  Do not confuse COPY with the psql instruction \copy. \copy invokes COPY FROM STDIN or COPY TO STDOUT, and then fetches/stores the data in a file accessible to the psql client.

Similarly, when you execute with psql a script file that contains your function definition:

CREATE FUNCTION copy_stuff_to_db(path text, encoding text) RETURNS void
AS $$
BEGIN
    \COPY table1 FROM path || 'table1.asc' WITH ENCODING encoding;
END;
$$ LANGUAGE plpgsql;

it (psql) doesn't actually execute any of that; it sends the code as a single statement to the server for processing. The server attempts to parse it as PL/pgSQL code (because of LANGUAGE plpgsql), compile,  and store in the database for future invocations. Since \COPY is not a valid PL/pgSQL command, compilation fails with the error you see.

PS. Others may correct me, but I don't think you can achieve what you want in a function even using a proper COPY command, because it would require dynamic SQL, which I think cannot be used with COPY.


Rebuild Index Task options I have to debug a performance issue on a SQL Server 2016 database. The query was pretty simple - a few temporary tables joined with SQL tables and aggregation. The amount of rows of involved in temporary tables was really small (several tables, hundreds of rows).
I have found that one of the tables PK has 12% index fragmentation. If the table was removed from the query it runs faster. I rebuilt the index and everything was fine.
I believe there must be something wrong with the settings of the SQL Server 2016 instance, because the query is running perfectly fine on SQL Server 2014. The only difference I have found in the settings was in the maintenance plans - the pictures below shows the plans:
SQL Server 2016

SQL Server 2014

I guess there is something not set correctly in the Index Stats Options, which is causing statistics not to be recalculated. Can anyone tell me if am I am right (as I am not DBA) or say if the plan looks OK, so I can search for the issue in another place?

This is longer than a comment .. so putting this as answer ...

  So, I have rebuild the index and everything was fine.

Rebuilding an index will update stats on the columns associated with that index.

There can be a lot of variables which might be different between 2 servers (2014 vs 2016)

sys.configurations settings
compatiblity level of database
Any custom trace flag enabled
max memory setting
cost threshold of parallelism setting
max dop setting

You can run Best practice analyzer script from MSSQLTiger team or just run sp_Blitz from Brent Ozar's team.

As a side note: Use Ola's Index maintenance solution ... much flexible and well tested by a lot of organizations.

From your screenshot, it seems that you are setting the page fullness (FILLFACTOR) to 80% i.e. leave 20% free. You should monitor fragmentation (in your case 12% is nothing + you need to consider how many pages are in the index. I would suggest you to start looking into indexes with 1000+ pages). I would doubt that this will cause a major performance problem.


How do I find out who is the resource hog in SQL Server 2008? We have a SQL Server 2008 installation on a machine with 24+GB of RAM and 16 cores.  Plus fast drives, etc.  A large machine by our standards.

Anyway, that machine is 100% devoted to SQL Server and there are TWO production databases on it.  Each DB is from two different vendors.

This has been running fine for 2 years.

Now, within the last 2 WEEKS, Vendor B is saying that the SQL Server can't handle their 8 GB database in performance.  Nothing has changed on our end in months.  Our DB server hasn't been touched.

Vendor A is working just fine.

So it sounds like Vendor A is taking more resources and slowing down Vendor B.  But I don't suspect Vendor A is to blame because I am familiar with the their DB and its users.

How can I find out how much resources each DB is using?  How I can say Vendor A gets 50% and Vendor B gets 50% of the server?

One way to do this would be through Resource Governor.  You can set up the appropriate Resource Pools and Workload Groups for each vendor/database.  Then you would create a classifier function (basically just a UDF that's registered in Resource Governor as the classifier function) to determine which Workload Group the incoming request would belong to.

To make this easy you could create Windows Groups (one for each vendor/db) and base your classifier function off of this, and set the appropriate workload.  These workloads can be configured according to your specifications above.


does merging tables always increases performance? Consider we have data from visitors in a web site. And you are trying to create a database design for data mining.

There are some common fields which users have such as browser,ip, etc... . But there is a field such as email or something which for example 20% of people give.
My question is at what percent you should merge tables and where we should move the email or custom field to another table?

in this case, is database size the only drawback of merging tables? and is it's performance results better than seperating table?

Without any knowledge about the workload of the database, is almost impossible to answer to a question like this. How often those “extra” data will be used? What are the most common queries? How frequent they are? and how much it is important the speed of the execution?

The general advice is always the best one for me: make things as simple as possible, than optimize if you need to.

So my advice is: create a single table, look at the performances, and only if those performances are not adequate to your objectives, split the table.


How to constraint a foreign key to a subset I have a table employee, and each of them can fulfill different roles, like inspector and constructor. I also have another table with a column where I can store what inspector is on the job.

People can play different roles, but roles don't have to be added dynamic; a column for every role on the employee would be sufficient. This would also be handy, because we are resources-constrained at the moment, and our users use Postico for maintaining data (for the moment), so a simple data-model is preferable. With a foreign key we can use the 1:N selector of Postico.

I tried to create a foreign key constraint to a (materialized) view, but that's not supported by Postgresql. Also the following foreign key is not allowed:

FOREIGN KEY("inspector_id", TRUE) REFERENCES employee("id", "is_inspector")

Do I have any option, other than duplicating data in multiple tables?

First, you don't have to duplicate data.

If you add an inspector table, that's not duplicating. You need an "inspectors" entity in your model, you add an "inspector" table. Only the inspector_id values will be duplicated which is fine. So, that's solution A.

Solution B might be to add a an is_inspector column in the table you want to have the foreign key and restrict that column to TRUE:

is_inspector BOOLEAN DEFAULT TRUE,
CHECK (is_inspector),
FOREIGN KEY (inspector_id, is_inspector)
  REFERENCES employee(id, is_inspector)

I would go for solution A every time but if solution B simplifies your application code for some reason, it still solves the issue and enforces the same requirements. But if you later need other tables to reference "inspectors", you'll need a boolean column in every one of them as well. So, solution A is the more simple approach as it would not require any such boolean columns at all (not even at the employee table, you can find the inspectors by joining to the inspector table).


Availability groups and Linked Servers I have inherited an Availability Group and have found that a linked server was set up between the two nodes of the availability group. It is using sa for authentication unfortunately.

Simultaneously, some of the team's developers use sa to query the live DB. I want this to stop.

My question is:

Are linked servers in any way required by availability groups? I know I have some bespoke moving parts like Agent jobs that use the linked server to sync jobs between the two nodes. I'm more concerned about something internal to SQL Server that I might not be aware of. Anything wrong with just doing normal due diligence and changing the linked server user to use non-sa and also changing the sa password?

Are linked servers in any way required by availability groups?

Absolutely not needed by SQL Server for Availability Groups!

  Anything wrong with just doing normal due diligence and changing the linked server user to use non-sa and also changing the sa password?

No, I agree with your approach as long as it doesn't break the business requirements. However, if the developers are using it to query around then I could argue that they should be changing their software to do this, rather than a linked server.

I would tend to agree that it's most likely used to automagically sync logins and jobs, but again there is no need for an actual linked server to do this though I could see why they would use a highly privileged account so they wouldn't have to worry about permissions.


Unable To Restore Database From .BAK File I am trying to restore my .bak file but I keep getting the error

  Restore of database 'DataWarehouse' failed.  (Microsoft.SqlServer.Management.RelationalEngineTasks)System.Data.SqlClient.SqlError: Restore Database is terminating abnormally (Microsoft.SqlServer.SmoExtended)

Is this a corrupt .bak file I am trying to restore from?

The steps I took were

  1) Right clicked on the server name and chose restore
  2) Navigated to the location of the .bak file
  3) Clicked Ok

Perform the following commands in SQL Server Management Studio (SSMS) on the SQL Server instance you are trying to restore the database to.

Verify BAK File

RESTORE VERIFYONLY FROM DISK = 'T:\DIRECTORY\DATAWAREHOUSE_FULL_20200115_190000.bak'

This should provide you with a result set that looks like this:

The backup set on file 1 is valid.

Possible Errors

If the check terminates abnormally then the cause could be because one of these checks have failed:

  Checks performed by RESTORE VERIFYONLY include:

  That the backup set is complete and all volumes are readable.
  Some header fields of database pages, such as the page ID (as if it were about to write the data).
  Checksum (if present on the media).
  Checking for sufficient space on destination devices.

Reference: RESTORE Statements - VERIFYONLY (Transact-SQL) (Microsoft | SQL Docs)

Check the BAK Fileheader

RESTORE HEADERONLY FROM DISK = 'T:\DIRECTORY\DATAWAREHOUSE_FULL_20200115_190000.bak'

This should provide you with a result set that looks like this:

BackupName  BackupDescription     BackupType ExpirationDate Compressed Position DeviceType UserName                         ServerName    DatabaseName         DatabaseVersion DatabaseCreationDate    BackupSize  FirstLSN              LastLSN               CheckpointLSN         DatabaseBackupLSN     BackupStartDate         BackupFinishDate        ........
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ........
NULL        NULL                  1          NULL           0          1        2          NT SERVICE\SQLSERVERAGENT        NOTHING       StackExchange        782             2019-07-24 09:17:00.000 1688399872  568000013810400037    568000013813600001    568000013810400037    568000013524000037    2020-01-15 19:25:27.000 2020-01-15 19:25:29.000 ........

Check the Contents of the BAK File

RESTORE FILELISTONLY FROM DISK = 'T:\DIRECTORY\DATAWAREHOUSE_FULL_20200115_190000.bak'

This should provide you with a result set that looks like this:

LogicalName              PhysicalName                                   Type FileGroupName    Size         MaxSize         FileId  CreateLSN           DropLSN  UniqueId                             ReadOnlyLSN         ReadWriteLSN       BackupSizeInBytes  SourceBlockSize FileGroupId LogGroupGUID  DifferentialBaseLSN  DifferentialBaseGUID                 IsReadOnly IsPresent TDEThumbprint
------------------------ ---------------------------------------------- ---- ---------------- ------------ --------------- ------- ------------------- -------- ------------------------------------ ------------------- ------------------ ------------------ --------------- ----------- ------------- -------------------- ------------------------------------ ---------- --------- --------------
StackExchange            C:\SQL\SQL_DATA\StackExchange.mdf              D    PRIMARY          2623537152   35184372080640  1       0                   0        725120E9-8DF3-4E09-9A89-94433A6EB49B 0                   0                  1687224320         4096            1           NULL          568000013524000037   707694EC-A36C-444A-8879-C807B2137455 0          1         NULL
StackExchange_DefRO      C:\SQL\SQL_DATA\StackExchangeRO.ndf            D    DEFAULTRO        5242880      35184372080640  3       89000000045300001   0        05C6BA44-123A-4749-8FB5-A0A16EF49A51 102000000014600005  90000000013100004  131072             4096            2           NULL          568000013524000037   707694EC-A36C-444A-8879-C807B2137455 1          1         NULL
StackExchange_PUBL_SNAP  C:\SQL\SQL_DATA\StackExchange_PUBL_SNAP.ndf    D    PUBL_SNAP_DBASE  5242880      35184372080640  4       286000001633700001  0        DCA761C4-6260-4BE3-8280-64099C98911D 0                   0                  131072             4096            3           NULL          568000013524000037   707694EC-A36C-444A-8879-C807B2137455 0          1         NULL
StackExchange_log        C:\SQL\SQL_LOGS\StackExchange_log.ldf          L    NULL             21798322176  2199023255552   2       0                   0        364124B1-081E-45C5-971C-2CD1CBBF13EC 0                   0                  0                  4096            0           NULL          0                    00000000-0000-0000-0000-000000000000 0          1         NULL

If the Steps Fail

If any of the above steps fail, then you will have to determine what the issue is. The RESTORE ...  command should provide enough information to assist you in finding the root cause.

Example Solutions

If the backup file is corrupt, then you will have to find a backup file that works.
If the disk is full, then you will have to add additional space.
If all else fails, then you might have to open a case with Microsoft Support.

Best Practice

(emphasis mine)

  Test your backups!

  You do not have a restore strategy until you have tested your backups. It is very important to thoroughly test your backup strategy for each of your databases by restoring a copy of the database onto a test system. You must test restoring every type of backup that you intend to use. It is also recommended that once you restore the backup, you perform database consistency checks via DBCC CHECKDB of the database to validate the backup media was not damaged.

Reference: Back Up and Restore of SQL Server Databases (Microsoft | SQL Docs)


Is inserting into a table with a foreign key constraint slower than inserting without constraint? When using MySQL (actually, Aurora), will inserting into a table with a foreign key constraint be slower than the same table without the constraint?

INSERT (etc) must check that there are no FOREIGN KEY violations.  Hence it is slower.  Think of it as checking to see if an entry is in an index.
The actual cost depends heavily on whether the block of the other index is currently cached, which depends on size of entire dataset, workingset size, speed of disk, etc. I have not encountered a case where FKs made the difference between the system being 'viable' versus 'too slow'.


Preserving timezones explicitly in postgres I've been doing quite some research on different ways of 'seeing' time and how to properly map it in Postgres but I'm still not certain on what to actually use. Several articles recommend or rather persuade you to store dates as timestamp with time zone and never as just timestamp. I'm especially struggling with daylight saving times.

My use case is a simple end-user-facing application accessed from only folks in the 'Europe/Berlin' timezone. Users write posts that get stored along with timestamps for creation and updates.

Let's say a user publishes a post on 2020-01-01T10:00:00+01. If a user now reads that post on the same day it should display posted on 1st of January at 10 am. If that same article gets clicked in Berlin in July it should still say posted on 1st of January at 10 am regardless of the DST.

My intuition is now to store the time as a timestamp without time zone because otherwise Postgres would convert that time to UTC and store it that way. Later I wouldn't be able to refer to the actual timezone that post was published in. In that case, it would read posted on 1st of January at 11 am (due to Berlin now beeing ahead two hours) which could confuse the author if they were to check the time they initially published the post.

Are my thoughts correct and did I found one of the corner cases where to not use timestamp with time zone or am I missing something crucial here?

My use case is a simple end-user-facing application accessed from only folks in the 'Europe/Berlin' timezone. [...]
My intuition is now to store the time as a timestamp without time zone because [...]
Later I wouldn't be able to refer to the actual timezone that post was published in.

That indicates a misunderstanding stemming from the unfortunate data type names:

timestamp without time zone (= timestamp)
timestamp with time zone ( = timestamptz)

Neither data type stores any time zone information whatsoever. timestamptz just adds input and output logic to take the timezone setting of the current session into account, and projects from / to UTC time accordingly.
Don't be too embarrassed if that had you confused, it has happened to the best of us:

Time zone storage in data type “timestamp with time zone”

In your particular case
If your statement is reliable:

accessed from only folks in the 'Europe/Berlin' timezone.

... then you might as well just use timestamp. You know the accompanying time zone. And there is no conversion between input and output. You can convert to timestamptz on the fly with:
my_column AT TIME ZONE 'Europe/Berlin'

If your app should outgrow it's current scope and you need to switch to timestamptz to easily facilitate input from multiple time zones, convert with:
ALTER TABLE tbl ALTER col TYPE timestamptz
   USING my_column AT TIME ZONE 'Europe/Berlin'

Be sure to use the time zone name 'Europe/Berlin', not a hard-coded offset that would fail to adjust for DST variations, nor time zone abbreviation, which is just a name for a hard-coded offset - another one of those very common misunderstandings.
Or use timestmaptz right away to cover all eventualities.
Either way, to preserve timezones like you asked, you then need to store them explicitly (in an additional column), as that is not saved otherwise - and you can no longer just assume 'Europe/Berlin'.
Related:

Ignoring time zones altogether in Rails and PostgreSQL
Oddities with AT TIME ZONE and UTC offsets
Why does PostgreSQL interpret numeric UTC offset as POSIX and not ISO-8601?


How can I export just the structure of a MySQL database? I use Ubuntu 14.10 and PhpMyAdmin.

I have a 'big' MySQL database, the size is 900MB. I need just the structure of this database, to use it on my local machine. How can I export the structure only?

Since you specifically asked about phpMyAdmin and the previous answers, while correct, are both about doing this on the command line:

Navigate to the database you wish to export. Across the top row of tabs click "Export"

Next, click the Custom radio button

Halfway down the "Format-specific options" area, select "structure" (the default is "structure and data")

Check the other options and press Go to download your .sql file with no data, only the table structure.


How do you roll up header-level values on a fact table with line-item granularity? From what I've read, the recommended granularity for a fact table based on an order header / line item structure is at the line item level. In the process, any additive values that apply only to the order header are repeated for each line item:

Designing Dimensional Models for Parent-Child Applications

Dimensional Modeling Basics

Below (from the second link) is an example of such a design:

In this example, the shipping amount, which applies to the order as a whole, repeats for each line item.

But if you were to roll all the shipping amounts under a specific customer, you would have double the actual value. How do you avoid this doubling of the value?

Is the shipping amount considered a non-additive fact for this
table?
Is some kind of special handling required?
Is there any reason not to simply have two (nearly-identical) fact
tables? One for orders and one for order line-items?

I'd introduce a magic Shipping product, and put the shipping value against this only (leaving open the possibility to have multiple shipping products too - Heavy / Overnight / etc).  I'd probably still keep the ShippingAmount separate from the ProductAmount column though, so you can easily add up product sales only.


How to minimize the need for recalculating index on SQL Server? We have rather big tables (500 mils rows) where we expect frequent inserts. The tables have approx. 2 indexes each. The problem is that we are afraid that, when the index is recalculating the whole table is locked and all queries will time out (during the time of recalculating).

Is there a way how to minimize the occurrence of recalculating the index? For example let the nodes to be bigger when they are created, so more added pointers will fit in the index without the need of the rebalancing the index?

We have SQL Server 2008 R2.

I asume by "recalculating" you are referring to completely rebuilding an index. SQL Server will never completely rebuild the indexes unless you explicitly tell it to, so you will not see random pauses in performance due to it deciding to kick off a rebuild.

Each index does increase the amount of work that needs to be done for a given INSERT or UPDATE operation because a small portion of the index needs to be updated but this is a small portion: something of the order of up-to-10 pages affected for 500M rows (assuming the index is over a small datatype such as an INT, DATETIME or UUID - an index over a long string type for instance will require a little more work as less entries fit in one page, but it will still be similarly little work relative to the whole index's size). During these small updates due to INSERT and UPDATE operations there are no huge locks unless a table lock was required for something else in the transaction (and even if there are, the smallness of the operation per row means you'd only see the lock having significant effect if acting on many rows).

Rebuilding a whole index very rarely needs to be done, and will only be done if you request it explicitly. You can reduce the affect of index rebuilds if you have Enterprise Edition by using online index builds - there will still be a time were the table is locked as the newly generated index is attached to the table but this will be small compared to the time for the whole index build.


Single slave for multiple masters in AWS AuroraDB We are planning to use AWS Aurora(MySQL version) as our relational database. There will be three masters in different regions. We need the data from these masters replicated in a single slave. I think in SQLServer, this is possible using log shipping. Aurora MySQL also has bin log shipping but I am not seeing any way to push the logs from different masters in a single slave.

Please advise if anyone has come across and solved this situation.

I guess what you are looking for is here https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/AuroraMySQL.Replication.MySQL.html


What does: SET @Variable1 = (Select field1 From INSERTED) mean? I'm trying to understand an existing Trigger on one of my tables (DocumentDistribution), that it seems is the bridge table between DocumentInfo and DocumentSource tables. I don’t understand how this line (SET....FROM INSERTED) gets the value from? Is this what it called Dynamic SQL and it gets the value from the front end App for this database based on the user’s selections?

I’m getting errors when I try to test, as the sub-query is returning multiple rows while the syntax is expecting a single row, any advice?
I'm using SQL Server 2008R2.

Alter TRIGGER [dbo].[DocDist_Dup_Check]
ON [dbo].[DocumentDistribution]
AFTER UPDATE, INSERT

AS
BEGIN
SET NOCOUNT ON;
SET ANSI_WARNINGS OFF;
DECLARE @docid int,
        @sourceid int,
        @errstr varchar(255);

SET @errstr = 'The distribution you have attempted to create already exists in the database.' + CHAR(13) +
              'Duplicate distributions are not allowed for any document source except eBinder.';
SET @docid = (SELECT DocumentDistDocID FROM INSERTED);
SET @sourceid = (SELECT DocumentDistSourceID FROM INSERTED);

IF  (SELECT COUNT(*) FROM DocumentDistribution WHERE DocumentDistDocID = @docid AND DocumentDistSourceID = @sourceid) > 1

    AND
    (@sourceid NOT IN(SELECT DocumentSourceID FROM DocumentSource WHERE DocumentSourceName = 'eBinder' OR DocumentSourceName = 'eBinder - West Division' OR DocumentSourceName = 'Consolidated e-Binder' OR DocumentSourceName = 'MA Consolidated eBinder'))
BEGIN
    ROLLBACK TRANSACTION
END

ELSE
BEGIN
    RETURN
END

END

The problem is that the trigger was initially written with very simplistic logic and with minimal testing - it assumed that the update or insert could only ever affect a single row at a time.

The line you are asking about is attempting to assign a single value to a variable from a table that may contain more than one row (triggers in SQL Server fire per action, not per row). Different forms of variable assignment behave differently when there is more than one row to choose from, for example:

DECLARE @foo TABLE(id INT);
INSERT @foo VALUES(1),(2);

DECLARE @id INT;
-- this will succeed, picking a single, may-as-well-be arbitrary row:
SELECT @id = id FROM @foo;
PRINT @id;

-- this will fail:
--SET @id = (SELECT id FROM @foo);
--PRINT @id;

If you run the second version, you will get an error message you've probably already seen from your trigger:

  Msg 512, Level 16, State 1
  Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Not all is lost - your trigger can be re-written to handle multiple rows being affected - I am going to make the assumption that if you insert two rows and only one of them fails, you want the whole transaction rolled back, even though the other row was perfectly fine. (Also, your initial code populates @errstr but never uses it, so I've just left that out for now.)

ALTER TRIGGER [dbo].[DocDist_Dup_Check]
ON [dbo].[DocumentDistribution]
AFTER UPDATE, INSERT
AS
BEGIN
  SET NOCOUNT ON;
  SET ANSI_WARNINGS OFF;

  IF EXISTS
  (
    SELECT DocumentDistDocID, DocumentDistSourceID
    FROM dbo.DocumentDistribution AS dd
    WHERE EXISTS
    (
      SELECT 1 FROM inserted AS i
      WHERE i.DocumentDistDocID = dd.DocumentDistDocID
      AND i.DocumentDistSourceID = dd.DocumentDistSourceID
    )
    GROUP BY DocumentDistDocID, DocumentDistSourceID
    HAVING COUNT(*) > 1
  )
  AND EXISTS
  (
    SELECT 1 FROM inserted
    WHERE DocumentDistSourceID NOT IN
    (
      SELECT DocumentSourceID FROM dbo.DocumentSource
      WHERE DocumentSourceName IN
      ('eBinder','eBinder - West Division',
       'Consolidated e-Binder','MA Consolidated eBinder')
    )
  )
  BEGIN
    ROLLBACK TRANSACTION;
  END
END

I think I have your logic correct; please test on a restored backup or dev system.

You might consider implementing this as an INSTEAD OF trigger - this allows you to either insert or not depending on your business logic (or only insert the rows that don't violate your constraints), instead of always inserting and then sometimes rolling back.

You might also consider implementing a unique constraint (or maybe a filtered unique index or an indexed view, if the duplicates only need to be enforced on a subset) if the primary purpose of this trigger is to prevent duplicates.


Why can I execute this query but not see its execution plan? Consider the following query:

SELECT table_name,
  to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) c from '
  ||owner
  ||'.'
  ||table_name)),'/ROWSET/ROW/C')) AS COUNT
FROM all_tables
WHERE UPPER(owner) = UPPER('my_schema');

This query runs just fine and displays output. But doing EXPLAIN on this query produces:

  ORA-01039: insufficient privileges on underlying objects of the view

As per the Oracle documentation:

  You must have the privileges necessary to execute the SQL
  statement for which you are determining the execution plan. If the SQL statement accesses a view, then you must have privileges to access any tables and views on which the view is based.

  To examine the execution plan produced by an EXPLAIN PLAN statement,
  you must have the privileges necessary to query the output table.

I do have privileges on the table accessed by SQL as the query runs fine.
By output table I assume it means dbms_xplan.display which is to store calculated plan. I think that I have privilege on that aswell as following runs fine:

EXPLAIN PLAN FOR SELECT * FROM myschema.mytable;
SELECT * FROM TABLE(dbms_xplan.display);

What's missing here?

I do have privileges on the table accessed by SQL as the query runs
  fine.

And that is a false assumption. Just because you can query a view, it doesn't necessarily mean you have privileges on the underlying tables. Anyone can query the view ALL_TABLES, but a regular user doesn't have the privileges to query the underlying tables such as OBJ$ or TAB$.

This is how it works, and it is really easy to reproduce. Create two users, one with a table and view, then grant SELECT on that view to another user:

SQL> grant create session, create table, create view to u1 identified by u1;

Grant succeeded.

SQL> grant create session to u2 identified by u2;

Grant succeeded.

SQL> create table u1.t1(id number);

Table created.

SQL> create view u1.v1 as select * from u1.t1;

View created.

SQL> grant select on u1.v1 to u2;

Grant succeeded.

Now connect as u2, and try to select from the view:

SQL> connect u2/u2
Connected.

SQL> select * from u1.v1;

no rows selected

No problem. Now try to explain:

SQL> explain plan for select * from u1.v1;
explain plan for select * from u1.v1
                                  *
ERROR at line 1:
ORA-01039: insufficient privileges on underlying objects of the view


How to tell if Postgres was built with the option to include LLVM technology for JIT? Postgres 11 gained the ability to perform Just-in-Time Compilation (JIT) based on LLVM technology.

The manual notes that this feature depends on Postgres having been built with the --with-llvm option.

➥ How can one verify their build of Postgres includes LLVM?

Assuming LLVM is installed… In Postgres 11, JIT is disabled by default. In Postgres 12, the feature is enabled. That raises the question:

➥ How can one tell if JIT is currently enabled or disabled?

Is LLVM built-in?

You can run pg_config --configure to see with which flags and options your version was compiled.

To quote the documentation:

  Print the options that were given to the configure script when PostgreSQL was configured for building. This can be used to reproduce the identical configuration, or to find out with what options a binary package was built.

Is JIT enabled?

To check if JIT is enabled, examine the configuration variable named jit.

show jit ;

Is JIT available?

Call the system information function pg_jit_available to see if JIT can be used in this session. Returns false if jit is set to false.

SELECT pg_jit_available() ;


Update if exist in other table I want to update column in table A if the value of column A1 exist in one column of B table for example B2

update A
set A1 = 'F'
WHERE  A1 in (SELECT B2 FROM B );

I tried somethig like this but no success, i tried too with exists

It may not be the most efficient method but I can't see a problem with your SQL.

Recommend you check your data..


Surprising results for data types with type modifier While discussing a recursive CTE solution for this question:

Get the last 5 distinct values for each ID

@ypercube stumbled across a surprising exception, which lead us to investigate the handling of type modifiers. We found surprising behavior.
1. Type cast retains the type modifier in some contexts
Even when instructed not to. The most basic example:
SELECT 'vc8'::varchar(8)::varchar

One might expect varchar (no modifier), at least I would. But the result is varchar(8) (with modifier). Many related cases in the fiddle below.
2. Array concatenation loses the type modifier in some contexts
Without need, so this errs on the opposite side:
SELECT ARRAY['vc8']::varchar(8)[]
     , ARRAY['vc8']::varchar(8)[] || 'vc8'::varchar(8)

The 1st expression yields varchar(8)[] as expected.
But the 2nd, after concatenating another varchar(8) is watered down to just varchar[] (no modifier). Similar behavior from array_append(), examples in the fiddle below.
All of this does not matter in most contexts. Postgres does not lose data, and when assigned to a column, the value is coerced to the right type anyway. However, erring in opposite directions culminates in a surprising exception:
3. Recursive CTE demands data types to match exactly
Given this simplified table:
CREATE TABLE a (
  vc8  varchar(8)  -- with modifier
, vc   varchar     -- without
);
INSERT INTO a VALUES  ('a',  'a'), ('bb', 'bb');

While this rCTE works for the varchar column vc, it fails for the varchar(8) column vc8:
WITH RECURSIVE cte AS (
   (
   SELECT ARRAY[vc8] AS arr  -- produces varchar(8)[]
   FROM   a
   ORDER  BY vc8
   LIMIT 1
   )

   UNION ALL
   (
   SELECT a.vc8 || c.arr  -- produces varchar[] !!
   FROM   cte c
   JOIN   a ON a.vc8 > c.arr[1]
   ORDER  BY vc8
   LIMIT 1
   )
   )
TABLE  cte;

ERROR: recursive query "cte" column 1 has type character varying(8)[] in non-recursive term but type character varying[] overall
Hint: Cast the output of the non-recursive term to the correct type. Position: 103

One quick workaround would be to cast to text.
A plain UNION query does not exhibit the same problem: it settles for the type without modifier, which is guaranteed to preserve all information. But the rCTE is more picky.
Also, you wouldn't run into problems with the more commonly used max(vc8) instead of ORDER BY / LIMIT 1, because max() and friends settle for text right away (or the respective base type without modifier).
SQL Fiddle demonstrating 3 things:

A range of example expressions including surprising results.
A simple rCTE that works with varchar (without modifier).
The same rCTE raising an exception for varchar(n) (with modifier).

The fiddle is for pg 9.3. I get the same results locally for pg 9.4.4.
I created tables from the demo expressions to be able to show the exact data type including the modifier. While pgAdmin shows this information out of the box it's not available from sqlfiddle. Remarkably, it's also not available in psql (!). This is known shortcoming in psql and a possible solution has been discussed on pgsql-hackers before - but not implemented, yet. This might be one of the reasons the issue has not been detected and fixed, yet.
On the SQL level, you can use pg_typeof() to get the type (but not the modifier).
Questions
Together, the 3 issues make a mess.
To be precise, issue 1. is not involved directly, but it ruins the seemingly obvious fix with a cast in the non-recursive term: ARRAY[vc8]::varchar[] or similar, which adds to the confusion.
Which of these items is a bug, a glitch or just how it's supposed to be?
Am I missing something or should we report a bug?

This is due to relation attributes (defined in pg_class and pg_attribute, or defined dynamically from a select statement) supporting modifiers (via pg_attribute.atttypmod), whilst function parameters do not. Modifiers are lost when processed through functions, and since all operators are handled via functions, modifiers are lost when processed by operators as well.

Functions with output values, or that return sets of record, or the equivalent returns table(...) are also unable to retain any modifiers included in the definition. However, tables that return setof <type> will retain (actually, probably typecast to) any modifiers defined for type in pg_attribute.


Unique filtered index and surprising estimation When I view the estimated execution plan for this query...

SELECT *
FROM tabela2  x
     LEFT JOIN dbo.tabela1 t with (nolock) on t.Kod = x.Kod

... the Estimated Number of Rows property for [table1] shows 10. Why is this? In the schema (copied below) I have a unique index AKF_tabela1_Kod. Shouldn't this unique index force the row estimation to be 1?

Estimated Execution Plan

Create Database, Tables and Index

-- create database
USE [master]
GO
DROP DATABASE IF EXISTS my_test;
CREATE DATABASE my_test;
GO
ALTER DATABASE my_test SET AUTO_CREATE_STATISTICS OFF;
ALTER AUTHORIZATION ON DATABASE ::my_test TO sa;
USE my_test
GO

-- create dbo.tabela1

CREATE TABLE dbo.[tabela1]
    (
    [Id] int NOT NULL identity(1,1),
    [kol1] varchar(20) NOT NULL,
    [Kod] int NULL,
    CONSTRAINT [PK_tabela1] PRIMARY KEY NONCLUSTERED([id] ASC) WITH (ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON),
    CONSTRAINT [BK_tabela1] UNIQUE CLUSTERED([Kol1] ASC) WITH (ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON),
    );
GO

-- create Index

CREATE UNIQUE NONCLUSTERED INDEX [AKF_tabela1_Kod]
    ON dbo.[tabela1]([Kod] ASC)
    INCLUDE ([id],[kol1])
    WHERE ([Kod] IS NOT NULL)
    WITH (ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, MAXDOP = 0);
GO

-- create dbo.tabela2

IF  OBJECT_ID('dbo.tabela2') IS NULL
  CREATE TABLE tabela2 (Kod int NOT NULL)
GO

Insert Data into Table

-- create data in tabela1
declare @i int=1
    while @i<=100
    begin
      INSERT tabela1 (kol1, kod)
      values (@i, cast(@i+100 as varchar))
      set @i += 1;
    end;
GO

UPDATE STATISTICS tabela1 WITH FULLSCAN;

Here the Execution plan

the Estimated Number of Rows property for [table1] shows 10. Why is
  this? In the schema (copied below) I have a unique index
  AKF_tabela1_Kod. Shouldn't this unique index force the row estimation
  to be 1?

The statistics are created when creating the unique index, but as a result of the unique index being filtered, the statistics are also filtered.
(where kod is not null).

As a result, the query as it is cannot use these statistics.

Why?

The Index can be used, but the statistics are not a match for the query.

Even if you do a select on kod:

 select kod from dbo.tabela1 t
     where kod is not null  option(recompile)

The statistics are still no match:

If you do a select of the id however:

 select id from dbo.tabela1 t
 where kod is not null  option(recompile)

There is a match:

This seems to be buggy with auto create stats:

ALTER DATABASE my_test SET AUTO_CREATE_STATISTICS ON;

 select kod from dbo.tabela1 t
 where kod is not null  option(recompile)

This does not create stats for some reason, but is still able to do the correct estimation.

No new stats:

Re running this:

 ALTER DATABASE my_test SET AUTO_CREATE_STATISTICS OFF;

 select kod from dbo.tabela1 t
 where kod is not null  option(recompile)

Gives the same warning:

This seems to be an issue with auto create statistics being off, even though no stats are created.

I am not sure, but an estimate of 10 might be a 'default' value to use, like when using a table variable.

Workarounds:

1

If you create the next index, the result will be 1 estimated rows

CREATE UNIQUE NONCLUSTERED INDEX [AKF_tabela1_Kod2]
    ON dbo.[tabela1]([Kod] ASC)
    INCLUDE ([id])
    WITH (ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, MAXDOP = 0);
GO

2

Another route you could take is creating your own statistics:

 create statistics ST_Tablea1_Kod on dbo.tabela1(kod)

Retry the query (with a new execution plan):

SELECT *
FROM tabela2  x
     LEFT JOIN dbo.tabela1 t with (nolock) on  t.Kod = x.Kod option(recompile)

3

Or ofcourse, enable auto create statistics and rerun the query:

ALTER DATABASE my_test SET AUTO_CREATE_STATISTICS ON;

Update:

The below queries give a stat missing on the first execution, but not on the other two. A statistic was created by auto create stats.

     SELECT *
FROM tabela2  x
     LEFT JOIN dbo.tabela1 t with (nolock) on t.Kod = x.Kod option(recompile)
      ALTER DATABASE my_test SET AUTO_CREATE_STATISTICS ON;
         SELECT *
FROM tabela2  x
     LEFT JOIN dbo.tabela1 t with (nolock) on t.Kod = x.Kod option(recompile)
           ALTER DATABASE my_test SET AUTO_CREATE_STATISTICS OFF;

             SELECT *
FROM tabela2  x
     LEFT JOIN dbo.tabela1 t with (nolock) on t.Kod = x.Kod option(recompile)

Dropping the stats and retrying with a select:

     drop statistics  dbo.tabela1.[_WA_Sys_00000003_0EA330E9]

 select kod from dbo.tabela1 t
 where kod is not null  option(recompile)

     ALTER DATABASE my_test SET AUTO_CREATE_STATISTICS ON;

 select kod from dbo.tabela1 t
 where kod is not null  option(recompile)

     ALTER DATABASE my_test SET AUTO_CREATE_STATISTICS OFF;

 select kod from dbo.tabela1 t
 where kod is not null  option(recompile)

Does not generate stats, and gives the same missing index hints on the first and third execution:

I thought that this maybe was because of the optimization level being trivial. But this was not the case.

StatementOptmLevel="TRIVIAL"

Non trivial optimization level:

 select kod from dbo.tabela1 t
 where kod is not null
 group by kod  option(recompile)

     ALTER DATABASE my_test SET AUTO_CREATE_STATISTICS ON;

 select kod from dbo.tabela1 t
 where kod is not null     group by kod   option(recompile)

     ALTER DATABASE my_test SET AUTO_CREATE_STATISTICS OFF;

 select kod from dbo.tabela1 t
 where kod is not null    group by kod   option(recompile)

Result:

Query #2 with automatic stat creation:

(I added 100 rows for a total of 200)

Query #3 , after putting automatic stat creation off again:

(180 estimated rows)

As a conclusion, this should have something to do with automatic stat creation being disabled.
My guess is on some sort of temporary stats or the way the plan is created.
I checked if i could find any 'temp'stats that are created at runtime,
but i did not find anything.
    (select * from sys.stats where is_temporary = 1)


Inserting 10000 new rows? Presumably there is a straight forward and easy solution. I'm wanting to create 10000 new rows - that are numbered sequentially with no data per row (except sequentially numbered id).
I have used:

INSERT INTO bins (id)
VALUES (1)

to create a single row with id '1'

How do I create 10000 rows with corresponding id number?

Version: PostgreSQL 9.5.5

You can use the generate_series() set returning function:

INSERT INTO bins (id)
SELECT g.id
FROM generate_series(1, 10000) AS g (id) ;