Set Login User as database Onwer What's the query to set a login user as db_owner during the creation of database.
Following is the set of queries, which I'm using right now,

Create database Test1;
ALTER AUTHORIZATION ON DATABASE::Test1 TO [DEVELOPMENT\d-testuser];

This servers my purpose, but here I have firstly created a database with default db_owner and then altered it. I need a query which will select [DEVELOPMENT\d-testuser] as my database owner during the creation of database itself.

You could set the owner of model database as [DEVELOPMENT\d-testuser] and then any new databases that are created will automatically be owned by [DEVELOPMENT\d-testuser].

I normally tend to set the database owner to sa. Our applications are not using (should never use) sa :-) so there is no harm in setting the db owner to sa.


Howto combine UNION with EXISTS? I have a query that looks like this:

SELECT PubKey, Title FROM Publication
UNION
SELECT NoteKey, Title, FROM Note

Which works fine. My trouble start when I try to add an EXIST to it:

SELECT PubKey, Title FROM Publication
UNION
SELECT NoteKey, Title, FROM Note
WHERE EXISTS (SELECT * FROM UserPublication WHERE UserPublication.PubKey = Publication.PubKey)

That throws this error: The multi-part identifier "PubKey" could not be bound.

Could it be written another way?

The WHERE clause is specific to each individual SELECT so you would need to move the one you have to the first SELECT like this:

SELECT PubKey, Title FROM Publication
WHERE EXISTS (
    SELECT *
      FROM UserPublication
     WHERE UserPublication.PubKey = Publication.PubKey
)
UNION
SELECT NoteKey, Title FROM Note

However, if you want to specify the WHERE clause across the entire result set. This must be done like so:

SELECT * FROM (
    SELECT PubKey, Title FROM Publication
    UNION
    SELECT NoteKey, Title FROM Note
) AS A
WHERE EXISTS (
    SELECT *
      FROM UserPublication
     WHERE UserPublication.PubKey = A.PubKey
)


PostgreSQL - retrieving max primary key column to insert a new row - any alternatives? We are using a software that needs to request the max primary key column value to add a new row into the table that was requested.

I know that there are some alternatives like:

Sequences, and
GUIDs

in PostgreSQL, but is there something similiar like:

Identity-type in SQL Server or
any auto-increment columns of any other DB (MySQL)

in PostgreSQL, is there a way to achieve this with a (stored) procedure or a trigger and how?

Postgres has the serial datatype which matches SQL Server's IDENTITY or MySQL's AUTO_INCREMENT.

Internally it is shorthand for a SEQUENCE but does that matter? It acts like IDENTITY/AUTO_INCREMENT:

  The data types serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases). In the current implementation, specifying:

CREATE TABLE tablename (
    colname SERIAL
);

  is equivalent to specifying:

CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

Edit,

I think what OP means is "is there SCOPE_IDENTITY or such" in PostgreSQL. Yes.
You'd need currval or another one


Replacing a SQL server involved in Transactional Replication with least amount of downtime I have a physical server that the company wants to move to virtual - currently, it is a subscriber in Transactional Replication on a very large database.  What would be the best way to minimize the downtime?

Initialization from backup will still take ~ 30 minutes for the backup, 2 hours for the restore.  I do have the virtual server available to me - would it be possible to set the database that is being Replicated to Log Ship to the new virtual server (to keep it in sync), then when we get ready to do the cut over somehow just re-create the Replication and have it "pick up" without having new snapshots/etc?

You could add the new virtual server as a subscriber and once all of the data is replicated to the new virtual server you can switch connections and delete the old subscriber server.


SQL Server 2008 Convert String to Datetime question I have a string column in a table that display data as "CXL P/D 08/15/13"

I'm trying to convert this column to datetime, but I can't figure out how to extract only date and change the data type.

Cast(RIGHT(RTRIM(Trade_Date) ,8)as datetime) I'm trying this statement but it doesn't work

Conversion failed when converting date and/or time from character string.

Thank you

Use CONVERT and specify 1 as the date time style 1 = mm/dd/yy.

CONVERT(datetime, RIGHT('CXL P/D 08/15/13', 8), 1)


Designing a database for "not seen" queries I'm building a large collection of items from which I want to query items that a specific user has not seen before. I will need to track, per user, which items s/he has seen.

Currently the items are stored in mongodb and the 'seen items' I am planning to store in a mysql database.

I have two questions:

How should I design the database of items in order to effectively query for items a certain user has not seen?
As each user can have 'seen' several thousand items, should each user have its own mysql table or should I just have one large table?

EDIT: The items the user get are from the collection of items the user has not seen are selected randomly

The data says that you need

CREATE TABLE Seen (
    user_id ...,
    item_id ...,
    PRIMARY KEY(user_id, item_id),
    INDEX      (item_id, user_id)
) ENGINE=InnoDB;

That is in addition to table Users, with PRIMARY KEY (user_id) and Items with PRIMARY KEY (item_id).

Items not seen by user 1234:

SELECT i.item_id
    FROM Items AS i
    LEFT JOIN Seen AS s  ON  s.item_id = i.item_id
                         AND s.user_id = 1234;
    WHERE s.item_id IS NULL ;

You could then JOIN to Users and/or Items to get more info.

Separate tables for separate users -- NO.  This is a common question with the same answer every time.


data driven report --send max 2 report when records return instead of sending every 10 min I have a data-driven report that is scheduled to run every 10 minutes; it sends the report if any records return.

I'd like to change this so the report is sent out at most twice a day.

For example: Currently, let's say the report generates records on the following runs for a given day:

02:10
08:40
12:20
14:40
16:30
21:50

As things stand now, each of those 6 runs will send the report out. If the process I want was in place, it would have sent the 02:10 and the 08:40 reports, and not sent the other 4 (even though records were generated), because it had already sent out 2 reports this day.

The count would restart at 0 reports sent out at midnight every day.

I would create a table

CREATE TABLE ReportRuns
( RunDate Date, RunCounter Int)

The RunDate will contain the Date Portion only.

Everytime you run the report and there is data returning, you Update/Insert into this Table and increment the counter. If counter is already at 2, do nothing and don't send the report. if a record for the current day doesn't exist or does exist and the counter is 1, then update the counter to 2 and send the report.
Next time it runs, the counter will be at 2 and the report won't be sent.

HTH.


Use order from another select clause So what I'm actually trying to achieve is filter the most voted on stories. The stories are saved in one table, and all of the votes in another one.

I have this statement to get the vid column order proper:

SELECT vid, COUNT(id) AS votecnt
  FROM votes
WHERE status = 1
GROUP BY vid
ORDER BY votecnt DESC

And then I have to filter the stories themselves, and I want the to be ordered just as they are in the first clause.

SELECT vid, body, timestamp
  FROM stories
WHERE lv = 1
  AND status = 1

The thing is, it's the the order that I need, because the first statement does not return all of the rows that the second one does. It would be even better, if the first query would sort the table by status = 1 count at first, and then total votes.

Any ideas how to join all this into an SQL query? Or should I use PHP in between?

Unless I'm grossly misunderstanding your structure, a simple join should work fine:

SELECT stories.vid, body, timestamp, COUNT(votes.id) votecnt FROM stories
INNER JOIN votes ON stories.vid=votes.vid
WHERE stories.lv=1 AND stories.status=1
GROUP BY votes.vid
ORDER BY votecnt DESC

I'm not sure what you mean by

  It would be even better, if the first query would sort the table by status = 1 count at first, and then total votes.

since your where statement only uses votes/stories that have status=1.


Is my case statement broken by the use of outer apply? I'm struggling with a case statement not working. Simple enough I need to supply a value of 0 where the question marks are. I have several other case statements which handle separate columns, but this is the simplest. I have used case statements with NULLS before and no issue. My best guess is the OUTER APPLY is screwing with things, but I can't find any documentation to back that up.

SELECT TOP 20
  GIVE_BACK1.CLUB_TOT 'B1_TOT',
  GC.CLUB_USER_VARBL3
FROM
  SUMMIT.CLUB_MBRSP GC
    OUTER APPLY
    (
      SELECT
        CASE
          WHEN B1.CLUB_USER_VARBL3 IS NULL
            THEN '?????????????????????????????'
          ELSE
            B1.CLUB_TOT
        END AS 'CLUB_TOT'
      FROM
        SUMMIT.CLUB_MBRSP B1
      WHERE
        GC.ID = B1.ID
        AND
        (
          (
            GC.CLUB_USER_VARBL3 != 'LT'
            AND B1.CLUB_USER_VARBL3 = LEFT(GC.CLUB_USER_VARBL3,2) + CONVERT(varchar(2),(CONVERT(INT,RIGHT(GC.CLUB_USER_VARBL3,3))-1))
          )
        )
    ) GIVE_BACK1

This query yields this result set

NULL         FY88
NULL         FY10
NULL         FY01
NULL         FY99
NULL         FY97
NULL         FY99
NULL         FY97
NULL         FY02
NULL         FY01
17525.00     FY85
NULL         FY84
...

Any help getting the case statement working, and explanation of the issue or a refactoring would be most appreciated.

The Outer Apply is working like a Left Outer Join so when no row is returned by the Outer Apply a row filled with NULLs is returned.  I would question the need for the Outer Apply but placing a Coalesce(GIVE_BACK1.CLUB_TOT,0) in the SELECT list would fix the immediate issue.


Mysql - Shink table after delete I have some tables with millions of rows (up to 6MLN) and I need to execute a delete to reduce the amount of rows. I created a procedure with a loop that delete 10k record each execution.
What I did see is that after some executions the table starts to respond very slow and the delition no longer respond, for example:

First itaration, 30k records to delete, time 35 seconds
Second iteration, 126k records to delete, time 3.4 mins
Third iteration, 350k records to delete, time 24 mins

Every iteration i place a commit every 10k record. So first iteration need to delete 30k records and commit every 10k records.
I see that every time i execute the procedure the table grow (and does not decrease, what i was expecting...)
I'm trying to find a good solution before this post, with no luck.
I cannot delete and re-create the table with less records, so i need to find a way to lower down the records on the table.
How i can do to delete all this records maintaining the performance? Free space, "well built indexes" etc
I can give you more details if needed

For a "large" delete (more than, say, half the table), do something like:
CREATE TABLE new LIKE real;
INSERT INTO new
    SELECT * FROM real WHERE ...  -- what to keep
RENAME TABLE real TO old,
             new TO real;
DROP TABLE old;

The INSERT..SELECT is slow; the other steps are fast.  No need to "shrink" unless the original table was created with innodb_file_per_table=OFF, in which case the disk usage will increase with this process.
More big-delete tips for more cases:  http://mysql.rjweb.org/doc.php/deletebig


Backup a remote database, from my machine, to another server (SQL Server 2008) I'm trying to backup a database, of a remote server, from my machine, to another server.

 SERVER1   >>>  BACKUPSERVER
      \             /
       \           /
        \         /
         My Machine

I have SERVER1 with some databases. I'm connected on it from my machine, and I'm using:

BACKUP DATABASE X
TO DISK ='BACKUPSERVER\Database.bak'

And I'm getting this error:

  Msg 3201, Level 16, State 1, Line 1
  Cannot open backup device 'BACKUPSERVER\BACKUP LOCAL'.
  Operating system error 1265(The system detected a possible attempt to
  compromise security. Please ensure that you can contact the server
  that authenticated you.).

OBS: I will not change anything, because I contact the guys from infrastructure, and told them to change the Service Login (just to test), and they told me that they will migrate this server to another one and I should not be worried about it (I'm DBA jr, so I'm learning the server maping from here).

But I'm curious about that error. And if a face this again, how could I solve it?

Thanks guys.

This problem occurs because the network firewall filters Kerberos traffic.

  To resolve this problem, configure the network firewall so that TCP port 88 and UDP port 88 are not blocked for either domain.

http://support.microsoft.com/kb/938457

Sorry for just quoting...

Another thing to check is to make sure that your Service Startup account for SQL Server has the rights to access the file share that you are backing up to.

In SQL Server Configuration in the instance name > Properties > Log On > Log On as Account Type.


Aggregate rows for each group of rows I am trying to solve a problem which is quite easy to solve in a procedural language, but I fail to solve it in SQL in an efficient manner.
Let me first explain the problem. I have a series of events which happen on a certain point in time. To keep it simple, let's assume that each event happens on a distinct point in time. An event is represented by a number. Take for example the following data:
create table event
(
    time   time,
    status integer
);

insert into event
values
('12:00', 0),
('13:00', 8),
('14:00', 4),
('15:00', 2),
('16:00', 0),
('17:00', 9),
('18:00', 5),
('19:00', 8),
('20:00', 0),
('21:00', 1),
('22:00', 3),
('23:00', 0);

Now, a cycle is defined as a sequence of events happening between two events with status 0. So, for the data above, I have the following cycles:
cycle 1: 0 -> 8 -> 4 -> 2 -> 0
cycle 2: 0 -> 9 -> 5 -> 8 -> 0
cycle 3: 0 -> 1 -> 3 -> 0

The goal is to find these cycles.
I have a working solution (see fiddle), and it goes as following:
with
cycle_boundary(begin_time, end_time) as
(
    select begin_time, end_time
    from   (
             select    time, lead(time) over(order by time)
             from      event
             where     status = 0
           ) as cycle(begin_time, end_time)
    where  end_time is not null
)
select     begin_time, end_time, array_agg(row(time, status)) as events
from       cycle_boundary
cross join event
where      cycle_boundary.begin_time < event.time and
           cycle_boundary.end_time > event.time
group by   cycle_boundary.begin_time,
           cycle_boundary.end_time;

This outputs:
begin_time  end_time    events
12:00:00    16:00:00    {"(13:00:00,8)","(14:00:00,4)","(15:00:00,2)"}
16:00:00    20:00:00    {"(17:00:00,9)","(18:00:00,5)","(19:00:00,8)"}
20:00:00    23:00:00    {"(21:00:00,1)","(22:00:00,3)"}

The problem is that this solution is quite inefficient. First, I scan through the complete events to find the boundaries (which are two subsequent events with status 0), and then, I scan through these boundaries to find the containing events. This is basically a nested loop, so O(n^2).
In a procedural language, this can be easily solved in O(n) under the pre-condition that the events are sorted (which we can achieve in a database as well if there is an index on event(time)): loop through the ordered events and collect the events (in a temporary collection) as long we do not encounter an event with status 0; once we encounter such event, we output the collected events so far and clear this temporary collection.
So my question boils down to: how can we solve this in O(n) in SQL? I believe that one of the problems is that the FILTER clause for aggregate window functions is not implemented in PostgreSQL, but that might be irrelevant here.

You can use status to set groups, and then get min and max time of each group.
If there is a serial id (PK), and it can be used to set an order, maybe you can get a better performance.
Due each intermediate status=0 belongs to two groups, I've added a new column with the time of the next row to get max(time).
with ev as
(
  select
      time, status,
      lead(time) over (order by time) as next_time,
      sum(case when status = 0 then 1 else 0 end) over (order by id) as grp
  from
      event
)
select
    min(time) as min_time,
    max(next_time) as max_time,
    array_agg(row(time, status)) filter (where status <> 0) as events
from
    ev
group by
    grp
order by
    grp;

min_time | max_time | events
:------- | :------- | :---------------------------------------------
12:00:00 | 16:00:00 | {"(13:00:00,8)","(14:00:00,4)","(15:00:00,2)"}
16:00:00 | 20:00:00 | {"(17:00:00,9)","(18:00:00,5)","(19:00:00,8)"}
20:00:00 | 23:00:00 | {"(21:00:00,1)","(22:00:00,3)"}
23:00:00 | null     | null

db<>fiddle here


Oracle Database 19.3 on Windows: Howto impdp or expdp from a UNC path in Windows? With oracle database release 18.3 it is possible to use a unc path as a directory in windows and pump a database pump from this directory with impdp.

Example:

C:\>sqlplus system/passwd@exampledb
C:\>create directory DUMPS as '\\storage\dir';
C:\>exit
C:\>impdp system/passwd@exampledb directory=DUMPS dumpfile=example.dmp logfile=example-imp.log
... just works as expected

With oracle database release 19.3, the same impdp instruction produces the following output:

c:\>impdp system/passwd@exampledb directory=DUMPS dumpfile=example.dmp logfile=example-imp.log

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39155: error expanding dump file name "\\storage\dir\example.dmp"
ORA-48128: opening of a symbolic link is disallowed

I use the same account to run the oracle database service in the example for 18.3 and 19.3. Therefore, I assume that network or credential issues are not an explanation for my observation.

Is this a changed behaviour between Oracle Database Release 18.3 and 19.3, and did I miss the description of this changed behaviour in the release notes?
Or am I missing a setting introduced with 19.3 to enable the use of UNC shares in a DIRECTORY? Or something else I did wrong?

I tried to use the share as drive letter as follows:

C:\>net use p: \\storage\dir /persistent:yes /user:myuser
dir p:
... list of files // network access works well

C:\>sqlplus system/passwd@exampledb
create directory DUMPS as 'P:\';
exit
C:\>impdp system/passwd@exampledb directory=DUMPS dumpfile=example.dmp logfile=example-imp.log

... ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation

Now impdp complains about the log file. The problem also appears without using the impdp logfile= parameter.

You may find the below interesting:

http://christian-gohmann.de/2019/05/19/symbolic-links-in-directory-objects-not-permitted-with-oracle-18c-19c/

Long story short, I had the same issue, then after setting the parameter below and restarting the database, Data Pump was able to use the network share.

ALTER SYSTEM SET "_disable_directory_link_check" = TRUE SCOPE=SPFILE;


In a recruiting website, should I force a choice between one table for jobseekers and employers or 2 separate tables? I'm working on a recruiting web site and I was wondering what would be best between:

A User table, the employer accounts are linked to a company
(therefore when companyID is null then it's a job seeker)
Complete separate tables one for job seekers, one for employers.
That way on the long run, it's possible to optimize queries a bit
more if it becomes hugely successful.

Both approach have pros and cons:

With 1. it would be possible for exemple to create only one login form which would handle both job seekers and employers.

The data is the same but the entity is different.

I have mixed feelings. What do you think?

From a user experience point of view, it would be much easier to support only one "login method" without having to select which type of login a user wants to do (e.g. a dropdown for job seeker/employer). This would also mean all usernames would need to be unique.

If you separate the tables, you can still have optimal queries, they just might have an extremely small join associated with them (or just create a denormalized view of both job seekers & employers and you have best of both worlds!).

I think the real question you should ask is:  Do job seekers & employers have fundamentally different data associated with them or is there a large amount of overlap?


Is clustering a table beneficial, given this update pattern? I have a table essentially equivalent to this example:

create table my_table(i integer, x text, y integer, z integer);

create index idx_i_x on my_table(i, x);
create index idx_i_y on my_table(i, y);
create index idx_i_z on my_table(i, z);
create index idx_i_x on my_table(i, x, y, z);

All queries have column i in the where clause.

It seems that this table is a good candidate to benefit from
table clustering (https://www.postgresql.org/docs/9.6/static/sql-cluster.html)

Stats on my_table: for each value of i, there will be from 5 to 10 million rows.

Updates on the table follow the pattern:

update my_table set i = 4 where i = 0;

delete from my_table where i = 6;

All updates or deletes are "bulk", i.e. they
involve all rows for a given value of i.

Given what is written in the docs (https://www.postgresql.org/docs/9.6/static/sql-cluster.html) :

  Clustering is a one-time operation: when the table is subsequently
  updated, the changes are not clustered.

it seems as if it doesn't matter that rows are not reclustered for updates of i, since when I execute this update (for example) :

update my_table set i = 4 where i = 0;

all rows that had i = 0 are still clustered together.

CLUSTER helps read performance where actual table rows have to be read (from the main relation). Index-only scans don't care about the physical order of rows in the underlying table. If your table layout is that simple, you might cover almost all SELECT queries with index-only scans (Might require aggressive autovacuum settings for the table to support that.)
UPDATE performance may even suffer from CLUSTER. First of all, CLUSTER rewrites the table without dead tuples, thereby removing "wiggle-room" for H.O.T. updates. A lower FILLFACTOR might help, but not in your case. While updating indexed columns, H.O.T. updates are not possible anyway.

PostgreSQL Initial Database Size
PostgreSQL unexplained table bloat

While you update millions of rows in one command (or transaction), clustered together, new row versions will hardly ever find room on the same data page, which makes it a bit more expensive to begin with.
And since you have multiple indexes including the updated columns, your updates are particularly expensive, since all of these indexes need to be updated as well.
Dead rows have to be reclaimed eventually. So new rows are not always written "en bloc", which inevitably leads to (slow in your case) fragmentation over time.
More efficient approach?

for each value of i, there will be from 5 to 10 million rows.
...
All updates or deletes are "bulk", i.e. they involve all rows for a given value of i.

Seems like you should not be updating millions of rows to begin with. Instead, create a second table (1:n) with a single row for every distinct i in my_table:
CREATE TABLE current_i (
   org_i     integer PRIMARY KEY
 , current_i integer NOT NULL  -- UNIQUE?
);

Should be a very small table, given your specifications. Now you only update a single row in current_i instead of millions in my_table, saving a lot of bloat and vacuuming in my_table and its indexes - in addition to the much faster updates - which should make everything else faster, too.
To enforce referential integrity, you may want to add a FOREIGN KEY constraint to my_table.i:
REFERENCES current_i(i)

In queries, just join to current_i. You might provide a VIEW as drop-in replacement for the current table in SELECT queries:
CREATE VIEW my_view AS
SELECT i.current_i AS i, m.x, m.y, m.z
FROM   my_table  m
JOIN   current_i i ON i.org_i = m.i;

Should be much faster overall. All of this may come down to a simple case of normalization.


What are Objective Reasons to Prefer SQL Server 2016 over Earlier Versions? As Microsoft makes SQL Server version upgrades more frequently since the SQL Server 2005 or 2008 days, a lot of companies find it difficult to determine when an upgrade is a "must have!" and when an upgrade is a "nice to have"

In the spirit of a few earlier questions asking about reasons to prefer various newer releases of SQL Server over previous ones, what are some objective technical or business reasons a company might consider for an upgrade to SQL Server 2016 over an earlier version, even a later release such as SQL Server 2014?

(This question about SQL Server 2012 compared to SQL Server 2008, Or this one about SQL Server 2012 compared to SQL Server 2005 come to mind as examples of the spirit of this question. The answers to them also expand on some of the reasons here for companies starting on SQL Server 2008 or SQL Server 2005)

There are a lot of reasons to consider an upgrade to SQL Server 2016. Most of the time, these reasons vary depending on the version of SQL Server one is coming from. This won't be an exhaustive list, but I'll give a few reasons that come to mind. Including one very big reason we just learned about with SQL Server 2016 SP1's release in November of 2016.  Looking forward to see other answers for expansion or features I missed.
Big Reason: Many Features Available in Standard Edition Now
On November 16, 2016 Microsoft announced the release of Service Pack 1 for SQL Server 2016. Along with this announcement, they also announced that many features previously reserved to Enterprise Edition customers would be available in all editions of SQL Server - Standard, Express (with a few exceptions), Web in addition to Enterprise Edition.
This should serve to be a very compelling reason to upgrade for Software Vendors who develop and sell software which have a SQL Server backend – many features can now be available to their customers regardless of their licensed edition of SQL Server. This is also compelling for companies in sectors which must maintain auditing or other security enhancements that have been added to the product over time.
In my humble opinion, in over 17 years of working with SQL Server this makes SQL Server 2016 SP1 quite possibly the most compelling upgrade yet since I've started working with SQL Server in version 6.5. (Don't get me wrong, the 2000, 2005 and 2012 had their pros for their times also!)
Look at the list below, but just as a quick example of some of the features you can now use in Standard Edition, you have these: Compression, Change Data Capture, Fine Grained Auditing, Row-Level Security, Always Encrypted, Partitioning, Database Snapshots, In-Memory OLTP, Columnstore.
Specific Reasons For SQL Server 2016 New Features
I'm trying to keep the reasons here in this section to features introduced in SQL Server 2016 only. I'm also only describing core engine enhancements in this answer, not reporting, analytics, data integration, etc.
Security Related
Row Level Security - I have many clients looking to be able to implement security on a row by row basis. "User x can view data on this row, but not that row". Many of those clients have created functions and/or complicated view based structures to accomplish this. In SQL Server 2016 there are built in predicates and security policies available to aid here. You can read more about RLS here.
Dynamic Data Masking - This one sounds great on paper, but I wonder if it will prove to be less useful than the other two changes in the security area. Basically, you can dynamically mask, or obfuscate, data at query time based on the permissions of the caller. The amount of data to be obfuscated is something that is programmable. For instance, in the example Microsoft uses, you could mask all but the final four digits of a Credit Card number so a call taker in a call center could verify those final four digits. You can read more about that here.
Always Encrypted - Perhaps you have heard about some of the flaws or concerns over Transparent Data Encryption. Perhaps you don't want to use TDE for fear that someone with SA access can definitely decrypt if they wanted. Always Encrypted was released as a feature that puts the decrypt/encrypt ability in the hands of the application calling to SQL Server. Data at rest in SQL Server is encrypted. Data in flight heading to SQL Server is encrypted as a result of this approach also. You can read more about Always Encrypted here.
HA/DR Features
A few features came out here. One is the ability to use Always On Availability Groups in SQL Server Standard - the Basic Availability Group.  This was necessary as Mirroring is deprecated and had no replacement. The functionality is limited to the number of replicas and the approach for synchronization. You can read more here.
That isn't the only change to Availability Groups (which, by the way, were introduced in SQL Server 2012). There are also a few other changes (you can see the exhaustive list with links to other changes here):

Group Managed Service Accounts are supported.
Distributed Transactions are supported (with some caveats).
Read intent connections can be load balanced among read only replicas.
Three replicas can be involved in automatic failovers now.
Encrypted databases can now participate in an Always On Availability Group.
Performance Improvements - part of the theme of SQL Server 2016 is "it's just faster" - that's the Microsoft Marketing spin, but it is proving out in the marketplace. Several improvements were made to Availability Group performance.

Installer Fixes Some Common Mistakes
The SQL Server installer starting in SQL Server 2016 fixes some common mistakes made when configuring TempDB. The setup GUI guides you through the best practice configuration now.
Query Store
A favorite among SQL Server MVPs and tuners. This feature is a tool to give you insights into how queries are executed by the engine. Especially useful in tracking performance issues caused by changes in query plans over time. This can be a tool to view, but also a tool to fix. Helps simplify a few performance tuning concepts. You can read more about that here.
Temporal Tables
I often see a need to ask a question like "What did this data look like as of such and such a date". Temporal Tables are one method for doing that. Definitely have some warehousing use cases. See more here.
Columnstore Improvements
Three big to me improvements here among a couple others. One is updateable nonclustered columnstore indexes. SQL Server 2014 gave us updateable clustered, but now we have updateable nonclusted. SQL Server 2016 also allows columnstore indexes on In-Memory OLTP tables. And the ability to place a traditional B-tree nonclustered index on a clustered columnstore index now exists in SQL Server 2016.
Additional Feature Improvements in In-Memory OLTP
Several of the features that weren't supported inside of In-Memory tables or in natively compiled procedures are now available.  Some additions (but see this article for more): UNIQUE Constraints, Check Constraints, OUTPUT clause in DML, Outer Join, Subqueries in the select list, SELECT DISTINCT, UNION/UNION ALL, and more.
General Performance Improvements
SQL Server 2014 made some improvements to performance in several key areas as each release does. SQL Server 2016 made additional key enhancements throughout the product. That includes eliminating for many customers some of the pain sometimes encountered when upgrading to SQL Server 2014 due to the cardinality estimator changes. Several changes up and down the engine were made to improve everyday performance. Microsoft's CSS team has been blogging about this on their blog. You can just search for "SQL Server 2016 it just runs faster". An example blog post for this series is here.
I've seen this played out in clients who have upgraded, and expect to see it more and more.
Some Cons
There are cons obviously to any upgrade. It could be because I do this for a living as a consultant, but I think the cons are all incredibly manageable and really just consider them "count the cost first" items to be aware of and manage around.
Upgrades In General - In general upgrades mean you are introducing new functionality. You need to be able to test them. Between the versions of SQL Server various features are deprecated. This doesn't mean they go away right away but it means they can in future releases, often three versions later. For instance the TEXT data type, or doing the outer joins in the where clause with the *= or =* syntax are not supported if you are coming from a database running in SQL Server 2000 compatibility mode. Speaking of compatibility modes - they only go back three versions as well. So if you have a database running in 80 (SQL Server 2000) or 90 (SQL Server 2005) compatibility modes, they can't go over into SQL Server 2016 like that. So if you've been avoiding doing that test and sneaking compatibility levels, it's time for a reckoning.
Again. That is easy stuff. There are tools out there to help capture and test data to see what breaks. There are perfmon counters that can help look for deprecated features.
Licensing - Two things here:

Core Based - if you are used to pre SQL Server 2012 licensing terms, CPU licenses were just that - CPU licenses. Since SQL Server 2012, CPU based licenses have been based on the core. So there is an adjustment period there. Also Enterprise for new licenses (with some exceptions that were made when 2012 first rolled out) must be licensed at the core level - no server + CAL for enterprise. The shining silver lining on this cloud here is what I said about SP1 allowing Enterprise features in non-enterprise SKUs. If you need more than 128GB of RAM. If you need the advanced scans performance improvements. If you need more than 32GB of RAM for a columnstore or In-Memory then you are looking at Enterprise. If you don't? And you don't need online index rebuilds? Standard may just be for you, and it may be more for you now with those features newly available to you.
Software Assurance (SA) - if you didn't buy SA, you have to buy new. So if you are running SQL Server 2005 and you are worried that you are out of mainstream support, not terribly far away from the end of extended support and wish you had the features now available, you can't just upgrade like a Service Pack. If you never purchased SA, then your upgrade is a new purchase. That's okay, it really is worth it. But it is something that has to hit the budget.

Summary
There are many more reasons, like JSON support, introduction of R, etc. But these are just some of the reasons I would consider an upgrade for. The most important item, again, being the ability to use many previously Enterprise Edition-only features in Standard, or even Express. I have seen great success stories among my clients and I'm expecting to see more.
And if you are using SQL Server 2005 or 2008, the list is even larger. AlwaysOn Availability Groups came out in 2012, Columnstore Indexes in SQL Server 2012, In-Memory OLTP in SQL Server 2014, etc.
Not to mention if you are still on SQL Server 2005 or 2008, you are out of mainstream support.


Are indexes unnecessary for columnar database? In a columnar database, are indexes unnecessary or useless?

If you want to scan a subset of all data stored you need some kind of indexing structure. It does not have to be a B-tree. In a certain sense, partitioning or segment elimination is also a kind of indexing.

There is nothing intrinsically preventing a columnar database to be stored in a sorted (or roughly sorted way). This would allow seeks to happen.

That said, not all columnar databases provide the ability to scan a subset of the data stored selected by some predicate. This is an implementation issue, not a limitation of the columnar model.


XID wraparound metrics are unclear Recently, I had an issue with one of my database which got XID wraparound issue. So, I have started with single-user mode and ran vacuum. Everything was fine. But, when I checked datfrozenxid its ~1.5billon where as my txid_current is ~70billon. I feel like, its huge different and something is wrong.

The below query, I have used to see the datfrozenxid from pg_database

select datname, datfrozenxid, age(datfrozenxid) from pg_database order by datname;

I have read a XID wraparound scenario from http://www.rummandba.com/2014/02/understanding-xid-wrap-around.html

It make sense to me as per the Example given in above link. But, I have calculated xidWrapLimit, xidStopLimit, xidWarnLimit, xidVacLimit with my statistics.

xidWrapLimit = oldest_datfrozenxid + (MaxTransactionId >> 1);
xidStopLimit = xidWrapLimit - 1000000;
xidWarnLimit = xidStopLimit - 10000000;
(I haven't given all the values here since I am looking only for xidWarnLimit)

So, the xidWarnLimit is 3.747 billon where my current txid_current>70 billion. And strange thing is txid_current is greater than xidWrapLimit which is 3.758 billon.

How to find out, when it will raise the Warning about wraparound warn limit.

(using Pg9.5)

So, the xidWarnLimit is 3.747 billon where my current txid_current>70 billion. And strange thing is txid_current is greater than xidWrapLimit which is 3.758 billon.

You are presumably looking at the raw return of txid_current(), which is not a 32-bit value like the XID Limits you are looking at. Instead, it is

  a 64-bit format that is extended with an "epoch" counter so it will not wrap around during the life of an installation.

(ref). You can coax a 32-bit value out of txid_current() which you can compare against your xidWrapLimit, xidStopLimit, etc. like this:

SELECT txid_current() % (2^32)::bigint;

H/T to Erwin for this tip. Remember that these XID values wrap around at 2^32.

For what it's worth, I think that blog post you linked to is excessively complicating this topic. To watch out for XID wraparound, you really just need to check:

SELECT max(age(datfrozenxid)) FROM pg_database;

and raise an alarm if that max. age is over 1 Billion or so. And if it gets close to 2 Billion you are in imminent danger! You can use a tool like check_postgres.pl which will handle this check for you with some configurable thresholds.


Data compression with in memory OLTP Will having data compression enabled decrease the amount of memory used by an in memory OLTP table?

If yes I assume the CPU workload will increase.

Looking at the CREATE TABLE definition for 2014 and scrolling down to the box headed "--Memory optimized CREATE TABLE Syntax", there is no mention of compression.  Ergo, compression is not available for in-memory tables.

But yes, compression uses more CPU.  Our experience is that query elapsed times are still shorter because the extra compute time is more than compensated for by the reduced IO latency.

Update: in memory tables now support columnstore indexes which employ compression.


Replacing an aggregation system of statistics We currently have a database under MySQL, storing aggregated statistics in different tables (recent hours, hours, days, months).
The tables are updated by workers running at different rates depending on the freshness required for the data.
Then those aggregates are queried by the applications with usually queries involving more even more aggregation.

This solution is showing limits in performance, scaling and flexibility when it comes to queries the data.

Our goal is to replace it with a system based on events sourcing.

Our first prototype use Dataflow (a bit like MapReduce but working in streaming) to pre-compute aggregates for a part of the data and put those in BigTable and put raw events (partitioned) in BigQuery for the aggregations we can’t pre-compute.

The system is globally working but its cost is prohibitive, with an estimated of 25K$/month for BigQuery only.
The cost is mainly due to the high number of queries for which we cannot pre-compute an efficient aggregate (usually we cannot pre-compute an aggregate because we need an earlier event that dataflow doesn’t have at the processing time of the event) .

As alternate back end of BigQuery we tested few other options like kudu, clickhouse , spanner…
So far only clickhouse actually performed very well with sub second response time, but maintaining a clickhouse cluster seems to be a bit hazardous.

From there where else could we look? Did we do a fundamental error in the design that would explain the poor performance? Is it even possible to balance cost usability of such system
I have the feeling that the most used solution is still big hadoop clusters.

Few technical info under normal load:

~300 event/s (with regular pick at 800 event/s)
~34000000 event/day
an event encoded in protobuf weight ~200B
an event has 20 properties
computing the last state can require 3-10 events spread over a range time of 3 months

Disclosure: I am the product manager of Google Cloud Bigtable.

Is it fair to assume that these statistics relate somehow to a globally-unique identifier (user id, device id, something else)?

If so, you might be able to do everything you need in Bigtable, depending on the specific set of queries you need to issue. For example WePay does something quite similar, and they presented their solution and schema design in Bigtable for flexible aggregation of metrics data.

Since Bigtable is a wide-column NoSQL database, you can create a number of columns for any given key and store raw data in one column (with time series for events), partial aggregations for minutes, days, weeks, months, etc. each in separate columns, and then when you need a particular aggregation over an arbitrary time interval, read the partial aggregations as needed.

You can also do this in two different modes, depending on the trade-offs between cost and time:

if only some of your aggregations are accessed, compute them lazily on first access, and write them to Bigtable for storage and querying
if many of the aggregations are needed, and latency is important even for first access, compute eagerly what you can with Dataflow live, and post-compute what cannot be computed live with a background process

As an additional data point, Fastly recently posted how they moved their historical statistics from MySQL to Bigtable, but I don't have any further information on their schema design; hopefully, they will post a follow-up to the announcement with more technical details of their implementations.

If you'd like to discuss your specific use case in more detail privately, let's chat further.


SQL Server 2012 Client Statistics Incorrect When I run my query, I get the following Client Statistics Results :-

Number of SELECT statements = 2

The query being ran is only 1 SELECT statement :-

SELECT TOP 1000000 * FROM [DATABASE].[SCHEMA].[TABLE]

My client statistics returned on the server claiming 2 SELECT statements looks like this :-

Can someone explain why it would show as 2 SELECT statements ?

I have also used SQL Profiler to trace this as its being ran, and there was no second query

Retrieving the execution plan is a call as well (this is why you can see an additional 1 row(s) affected message in the messages pane as well). Try again without the execution plan option.


Many to Many and Weak Entities I have an entity that cannot exist without being defined by another, and I want this entity to participate in a many-to-many relationship.

Example: An artist has an album (the album cannot exist without an artist), the album also has many tracks, but the same track can exist in many albums.

So we have a many-to-many relationship between the album and the tracks.

If the album is a weak entity, then its primary key is a foreign key referencing the artist, thus it cannot be a foreign key to another table representing a many-to-many relationship.

The question is: is it possible to have this kind of relationship in SQL, and if so, how do I express it?

I think you can, using a "diamond" relationship diagram:

CREATE TABLE Artist
( artistID INT NOT NULL
, name VARCHAR(100) NOT NULL
, PRIMARY KEY (artistID)
) ;

CREATE TABLE Album
( artistID INT NOT NULL
, albumID INT NOT NULL
, title VARCHAR(100) NOT NULL
, PRIMARY KEY (artistID, albumID)
, FOREIGN KEY (artistID)
    REFERENCES Artist (artistID)
) ;

CREATE TABLE Track
( artistID INT NOT NULL
, trackID INT NOT NULL
, title VARCHAR(100) NOT NULL
, PRIMARY KEY (artistID, trackID)
, FOREIGN KEY (artistID)
    REFERENCES Artist (artistID)
) ;

CREATE TABLE AlbumTrack
( artistID INT NOT NULL
, albumID INT NOT NULL
, trackID INT NOT NULL
, trackNo INT NOT NULL
, PRIMARY KEY (albumID, trackNo)
, FOREIGN KEY (artistID, albumID)
    REFERENCES Album (artistID, albumID)
, FOREIGN KEY (artistID, trackID)
    REFERENCES Track (artistID, trackID)
, UNIQUE (trackID, albumID)               -- this Unique constraint should be added
                                          -- if no track is allowed twice in an album
) ;


Benchmarking procedures or Upgrade tests to be done after upgrading to SQL Server 2016 from SQL Server 2012 I need suggestions on how to benchmark performance or create baselines to test the upgrade from SQL Server 2012 to SQL Server 2016 (both standard edition), also if any post upgrade tasks that needs to be checked to ensure smooth performance after upgradation.

P.S : I have recently created a test snapshot of my 2012 SQL Server database(also have separate snapshot of 2012 SQL Server as well) and have upgraded it to 2016 SQL Server (both standard) in AWS RDS. I want to test the performance and functionality of both the instances to check if the upgrade went smooth and  I can proceed it for our main production Database.

Please help me on all the checks to be performed.

I have done replaying a trace file from the current server on the new server.
Below are the results:

When I did SQL 2005 to 2012 I used the Distributed Replay Controller feature. You run a profiler trace with the DRC template. Then run the command on a few servers and it runs those trace files against a target server.

During the process you check whatever performance monitoring app you use and make a note of the times


Oracle Wallet Error in Red Hat High Availability Resource Environment: Running Red Hat 7.2, using Pacemaker 1.1.13-10.el7_2.2 and Corosync 2.3.4-7.el7_2.1 to implement cluster failover of an Oracle 12c Enterprise Edition Release 12.1.0.2.0 Database.

Background: When I start the database outside the cluster, everything works correctly and outside queries from other hosts can execute.  The database has an encryption wallet which is set to auto-open, and which worked seamlessly before this problem cropped up.

Problem: When I add the oracle instance to the High Availability resource group, the instance starts up without any errors (that I can see).  However, when outside connections are attempted from other hosts, the database responds

ERROR at line 1:
ORA-28365: wallet is not open

If I connect to the database on the host server and execute any queries, no errors are reported at the console, and the queries from remote hosts suddenly start to succeed again.  The cluster then works fine until the resource fails over to another node, then the problem reappears with the same symptoms / temporary solution.  The rejected queries cause trace files to be generated for the database SID containing:

kcbtse_get_tbskey: decrypting encrypted key for pdb 0 tablespace 6 without opening the wallet
kcbtse_get_tbskey: wallet is not opened (ts 0/6)
kcbtse_encdec_tbsblk: DIAG DUMP tsn 0/6 rdba 25165987, afn 6, mode 4

Has anyone seen this sort of issue before?  What is missing from the HA environment that is present outside of HA?

In case anyone is interested in the solution that was identified:

The sqlnet.ora in question contained the following:

ENCRYPTION_WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
       (METHOD_DATA =
        (DIRECTORY = ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet)
        )
   )

Which worked fine with the oracle user profile on the server.  However, when running inside the HA resource, the environment variables were overridden, and ORACLE_BASE was not defined.  Thus when oracle started, the system attempted to locate the "/admin/${ORACLE_SID}/wallet" directory, and upon not finding it, defaulted to a "wallet closed" state.  Queries about the wallet when the database is in this state returned a null record.

When we attempted to troubleshoot the issue, we logged into the host server as oracle, got the user profile from the shell, and thus when we started sqlplus and ran queries it was able to resolve the ENCRYPTION_WALLET_LOCATION and populate the record in the table.  Thus all local queries worked, and suddenly all remote queries "started working" again with little indication in the logs of what had taken place behind the scenes.


How do I validate all my constraints in postgresql? It seems like I have been bitten by the first bug described in the release notes of postgresql 9.3.4: http://www.postgresql.org/docs/devel/static/release-9-3-4.html

I now have e.g. duplicate primary keys. What is the best approach on doing a loop of re-checking of all my constraints (pkeys, fkeys) -> fixing issues -> checking again, to make sure that my data is OK?

UPDATE

I decided to go with an approach on fixing the issue by deleting all constraints, and then recreating them using the following method http://blog.hagander.net/archives/131-Automatically-dropping-and-creating-constraints.html. However I'm currently stuck on an error message, trying to recreate a pkey:

ERROR:  failed to find parent tuple for heap-only tuple at (1192248,5) in table "fruits"
CONTEXT:  SQL statement "ALTER TABLE "pm"."fruits" ADD CONSTRAINT "fruits_pkey" PRIMARY KEY (id)"

What does this mean, and how do I fix that one (I can live with deleting it, if that's an option)?

Another question: Would my data actually be all good, if I just got rid of any duplicate rows by deleting them, and then did a pg_dump, and restored the database from that dump. It would rebuild the data structure - right?

Well, if you need a way to check if all the foreign keys in your table are valid, this might help ( it just validates all foreign keys in your schema )

do $$
  declare r record;
BEGIN
FOR r IN  (
  SELECT FORMAT(
    'ALTER TABLE %I VALIDATE CONSTRAINT %I;',
    tc.table_name,
    tc.constraint_name
  ) AS x
  FROM information_schema.table_constraints AS tc
  JOIN information_schema.tables t ON t.table_name = tc.table_name and t.table_type = 'BASE TABLE'
  JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
  JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name
  WHERE  constraint_type = 'FOREIGN KEY'
    AND tc.constraint_schema = 'public'
)
  LOOP
    EXECUTE (r.x);
  END LOOP;
END;
$$;


using tnsnames.ora and sqlplus client without installing oracle 11g server on linux client I want to create a connection to remote Oracle server using SQLplus client and tnsnames.ora without installing Oracle 11g server on the Linux client. I used debian and bash script to generate reports from remote Oracle servers. Is this possible?

I tried installing SQLplus client but I can't find the tnsnames.ora files.

If possible, can you please give me an example of configuring SQLplus client to use tnsnames.ora files?

I have installed the "basic instant client" and the "sqlplus addition" package. I can connect normally with sqlplus user/password@host:port/servicenames. I want to create bash scripts to execute *.sql files on more than 10 instances. So, if I can just connect with sqlplus user/pw@instance It will look simple. it's is possible?

Update :
Solved by copying tnsnames.ora files to /etc.

refer to this page on post #3

Thanks to :
Wernfried Domscheit, EdStevens, Paul White

tnsnames.ora doesn't "come with" anything.  The key is not that file, as it is nothing but a names resolution file. And sqlplus is nothing but a CLI application. If you want to connect to an Oracle database (or ANY database) you must have, at a minimum, the client software for that particular database product. For oracle you need to download and install the Oracle client. The easiest would be the "instant client", which you can download from here: http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html.  If you want the full "Admin" or "Runtime" client, download from edelivery.com and select just the client software. Pay attention to the license terms.


"TRUNCATE doesn't run ON DELETE trigger" true or false I was searching on the topic of why TRUNCATE is placed in DDL instead of DML for which I found following answer here
Why is truncate DDL?

I this answer I found a sentence as given below

"The fact that TRUNCATE doesn't run ON DELETE triggers also sets it apart from normal DML operations"

This conflicts with MYSQL Reference Manual which says

"If the FOREIGN KEY constraint specifies DELETE CASCADE, rows from the child (referenced) table are deleted, and the truncated table becomes empty."

I am in a confused state. Please elaborate on this issue.

Thank you

Your first link (Why is truncate DDL?) is about Oracle, not MySQL. While relevant, different DBMS implement same features differently. In Oracle and SQL Server TRUNCATE is DDL, not DML.

In MySQL, it matter which version you use:

Until version 5.1, the documentation says:

  Logically, TRUNCATE TABLE is equivalent to a DELETE statement that deletes all rows, but there are practical differences under some circumstances.

  For an InnoDB table:

  If there are no FOREIGN KEY constraints, InnoDB performs fast truncation by dropping the original table and creating an empty one with the same definition, which is much faster than deleting rows one by one.
  When you use this fast truncation technique with the innodb_file_per_table option enabled, the operating system can reuse the freed disk space. For users of the InnoDB Plugin, the space is reclaimed automatically, as described in Reclaiming Disk Space with TRUNCATE TABLE. If you do not have the InnoDB Plugin installed, issue the OPTIMIZE TABLE statement to free the disk space for the table.
  If there are any FOREIGN KEY constraints that reference the table, InnoDB processes TRUNCATE TABLE by deleting rows one by one, processing the constraints as it proceeds. If the FOREIGN KEY constraint specifies DELETE CASCADE, rows from the child (referenced) table are deleted, and the truncated table becomes empty. If the FOREIGN KEY constraint does not specify CASCADE, the TRUNCATE TABLE statement deletes rows one by one and stops if it encounters a parent row that is referenced by the child, returning this error:

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign
key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1`
FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))

But for versions 5.5 and later, the documentation says:

  Logically, TRUNCATE TABLE is similar to a DELETE statement that deletes all rows, or a sequence of DROP TABLE and CREATE TABLE statements. To achieve high performance, it bypasses the DML method of deleting data. Thus, it cannot be rolled back, it does not cause ON DELETE triggers to fire, and it cannot be performed for InnoDB tables with parent-child foreign key relationships.

  Although TRUNCATE TABLE is similar to DELETE, it is classified as a DDL statement rather than a DML statement. It differs from DELETE in the following ways in MySQL 5.5:

  ...


Alter DB modify log file for all DBs I can run this command to make changes for 1 DB

USE [master]
GO
ALTER DATABASE [DBName] MODIFY FILE ( NAME = N'Name_log', FILEGROWTH = 10000KB )
GO

Is it possible to create a script which changes log file size to let's say 100MB for all DBs in all servers instead of running the above command by logging into each server? We have about 200 servers and close to 3000 DBs.

You can generate a script to do that using dynamic TSQL and then run it

SET NOCOUNT ON;
SELECT  'USE [MASTER] ' + CHAR(13) + 'ALTER DATABASE [' + d.name + N'] '
    + 'MODIFY FILE (NAME = N''' + mf.name + N''', FILEGROWTH = 10000 KB)'
    + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
FROM    sys.master_files mf
    JOIN sys.databases d ON mf.database_id = d.database_id
WHERE   d.database_id > 1
    AND d.state_desc <> 'offline'
    AND mf.type_desc = 'LOG';

Original script from another answer here.


What is the purpose of the keyword “||” in PostgreSQL? I'm new to PostgreSQL and I'm trying to figure out what does the operator || mean?

Google didn't help with that...

String concatenation as you can see here: http://www.postgresql.org/docs/9.4/static/functions-string.html

Array concatenation: http://www.postgresql.org/docs/9.4/static/functions-array.html

And others... It means: concatenation.


min(column) with nulls? I am new to databases, but I hope you can help me to understand something.

Take part of this query:

  select min(date) as date...

Assume that some of the dates are null. What effect would it have? Would the query break?

MIN will return the smallest non-NULL value.  The query won't throw an error, it will simply ignore the NULL values

SQL> with x as (
  2    select null dt from dual union all
  3    select sysdate from dual
  4  )
  5  select min(dt)
  6    from x;

MIN(DT)
---------
21-OCT-11


SSIS - Iterative Data Flow Task with Schema Differences I am new to SSIS and am trying to change a current process that moves data from one server to another using a linked server to use an SSIS package instead.

Currently, I have am getting a list of the 160 tables and setting the output to an Object variable.  Then I am using a Foreach Loop Container to iterate through each of the 160 tables along with a Data Flow Task to move the data from the source to the destination.

The part I am having issues with is that some of the 160 tables have schema differences i.e. the destination table does not have all of the columns contained in the source table.

For each table, I can get a list of the columns from the destination table and assign the output to an Object variable but I'm stuck at that point and am not sure to proceed conceptually.

So far, I have eliminated the option of converting the Object variable into a string by using an expression.  I read that expressions can't handle Object variables.

I was thinking of using another Foreach Loop Container to iterate through the column names in an effort to build a comma-separated string which I could use in an expression to turn into a SELECT statement but I'm having difficulties building the expression to concatenate the column names to same string variable.

Does anyone have conceptual suggestions on how I can solve the schema differences issue?

I am developing the SSIS package inside of Visual Studio 2013 for use on a server running 64-bit SQL Server 2014 Standard Edition.

I was able to solve my problem by using the information on this site:

http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/70875/

This showed me how to build a comma-delimited string from an Object variable by using a Foreach Loop Container.


Could not continue scan with NOLOCK due to data movement SQL Server 2008 R2 I have SQL Server 2008 R2 installed on a Windows 7 laptop. I create a new database via SQL Management Studio and when I right click and select Properties, I get the following message.

Cannot show requested dialog.

Additional information:

|-> Cannot show requested dialog (SqlMgmt)
    |-> An exception occurred while executing a Transact-SQL statement or batch
        |-> Could not continue scan with NOLOCK due to data movement. (Microsoft SQL Server, Error: 601)

Any idea what is happening and what is the possible fix?

UPDATE

I am not entirely sure if this is a fix:

I deleted all of the user databases
Did a Repair on the SQL installation
Created my databases again and it seemed to work without any issues.

However, I still do not what the root cause of the issue is. I had to fix it as soon as possible as the Sales Rep needed the laptop as quickly as possible. I really would like to hear what caused it to happen in the first place.

There's at least three possibilities:

SQL Server Management Studio was running a query that it shouldn't have
Something else was running a query it shouldn't have (like an SSMS add-in, which isn't likely based on the sounds of the question)
The engine was experiencing difficulties executing a normal SSMS query (that it shouldn't have)

I'd troubleshoot #1 by running SSMS from another machine, pointed at this particular laptop. If it works okay, then the laptop's SSMS is the problem - reinstall that.

If the other machine's SSMS gets the same error when connecting to the laptop's engine, then uninstall and reinstall the SQL Server engine on the affected laptop. (It's some type of corruption issue, and not worth the $500 MS support call to troubleshoot - if it was a production server, I'd answer otherwise.)


SELECT last date from multiples values I have this table:

I want to make a query that allows me to show the last value in fecha_gestion
grouped by NitSec. It would be something like this:

SELECT id_log, fecha_gestion, nitsec
FROM table
GROUP BY nitsec
ORDER BY fecha_gestion DESC

The result I want is this:

ID_log | fecha_gestion    | nitsec
6      | 2020-01-03 09:52 | 5443
11     | 2020-01-03 10:12 | 1
15     | 2020-01-03 10:23 | 3286

After doing this I need an inner join (but I can do it easily). Thanks in advance.

Because no sample data was given, the next query is untested:

SELECT
    ID_log,
    fecha_gestion,
    nitsec
FROM table t1
WHERE NOT EXISTS (SELECT 1 FROM table t2 WHERE t2.nitsec = t1.nitsec and t2.fecha_gestion < t1.fecha_gestion)


Understand PostgreSQL's statistics about rows and times I'm trying to find out how much database load an aplication causes. Therefore I'm looking at the statistics of a single database (there's only a single user working with it). The numbers I get are partially redundant and massively different. Here's the query I use to get the data:

with pss as (
    select pd.datname,
        sum(calls) as query_count,   -- Number of executed queries
        sum(total_time)::integer as time,   -- Total time spent for queries, in milliseconds
        sum(rows) as rows,   -- Number of rows retrieved or affected by queries
        sum(blk_read_time)::integer as read_time,   -- Total time queries spent reading blocks, in milliseconds
        sum(blk_write_time)::integer as write_time   -- Total time queries spent writing blocks, in milliseconds
    from pg_stat_statements as pss
        left join pg_database as pd on (pd.oid = pss.dbid)
    group by pd.datname
)
select psd.numbackends,   -- Currently connected clients
    pss.query_count,
    psd.xact_commit + psd.xact_rollback as xact_count,   -- Transaction count
    psd.blks_read,   -- Disk blocks read
    psd.blks_hit,   -- Disk block found in buffer cache
    pss.rows,
    psd.tup_returned,   -- Rows returned by queries
    psd.tup_fetched,   -- Rows fetched by queries
    psd.tup_inserted + psd.tup_updated + psd.tup_deleted as tup_written,   -- Rows written by queries
    psd.blk_read_time::integer,   -- Time spent reading data file blocks by backends in this database, in milliseconds
    psd.blk_write_time::integer,   -- Time spent writing data file blocks by backends in this database, in milliseconds
    pss.time,
    pss.read_time,
    pss.write_time
from pg_stat_database as psd
    join pss on (pss.datname = psd.datname)
where psd.datname = 'myappdb';

I have loaded and installed the pg_stat_statements extension and enabled the track_io_timing option. I've also reset both statistics at the same time a while ago as a superuser in this database:

select pg_stat_reset(), pg_stat_statements_reset();

The result currently looks like this:

numbackends:            28
query_count:       270,302
xact_count:        270,313
blks_read:      17,666,658
blks_hit:        5,063,072
rows:              462,105
tup_returned:   24,494,192
tup_fetched: 1,131,085,600
tup_written:        17,509
blk_read_time:      39,337
blk_write_time:      9,257
time:              229,049
read_time:          50,303
write_time:          9,261

My questions are these:

Why are the numbers in rows, tup_returned and tup_fetched so different? At least rows and tup_returned should be the same, right?
Why are blk_read_time and read_time a bit off while blk_write_time and write_time are so close?

The "rows" field only reports rows returned by the query in question.  The pg_stat_database fields also count rows that had to be fetched from system catalogs in order to plan and execute your queries.  pg_stat_statements doesn't track these, not even with pg_stat_statements.track=all.  Since the catalog data is generally cached within a session, this can give you massive expansion in the number in pg_stat_database relative to pg_stat_statements if you only execute one small query in each session and then disconnect.  These catalog reads can also explain the difference in read times.

There is more to the story than that, like whether they count input rows or output rows for aggregations and joins.  Frankly I find tup_returned and tup_fetched to be useless.

What problem are you trying to solve?


What is the most efficient way to make a Locality Entity (Town, City, Village etc) with a Country Entity? I am new to Database.

I have a database with a UserProfile Entity. I need to add a Country and Locality(City, Town, Village) to the UserProfile.

What is the most efficient way of doing so?

This is what I have:

Locality

PK UN LocalityID
UN LocalityName

Country

PK UN CountryID
UN CountryName

UserProfile

Username...Password...etc
FK    CountryID
FK    LocalityID

In Relational Database terms efficiency revolves around avoiding the duplication of data.  The 3 tables that you show in your question seem reasonable provided:

You can have a Locality without having a Country
You can have a Country without having a Locality

This is actually quite a viable design, since in some parts of the world they may not define locations as you describe.  (There are a lot of unique decisions made around the planet.) Of course you can generate an artificial name or  description for your tracking purpose should that be necessary.

For example: There is no named locality, you could create a Locality like: "Island NW of Salty Creek"

If you intend for each Locality to belong to a single Country then you might benefit from a small change to add a CountryLocality table:

Locality

PK UN LocalityID
UN LocalityName

Country

PK UN CountryID
UN CountryName

CountryLocality

PK UN CountryLocalityID
FK    CountryID
FK    LocalityID

UserProfile

Username...Password...etc
FK    CountryLocalityID

This is definitely a design and usage issue for you to decide on since the CountryLocality table adds some overhead. It depends on how strong the Country-Locality connection should be and whether this extra step is worth it for your application.


How to find statement executed by sp_executesql in [sys].[dm_exec_query_stats]? I have a function which is generating dynamic T-SQL statement. The generated code is executed then in a stored procedure.

I want to log some statistics about how long the execution of the generated code is and some IO statistics (logical/physical reads and stuff).

In order to do that, in the function which is building the dynamic T-SQL statement I append the following line to the final statement:

SET @A = @A + '--' + REPLACE('F2B287FD-1419-48DB-9881-4AA77B99450E', '-', '');

Then I am searching for statistics using the following query:

SELECT *
FROM [sys].[dm_exec_query_stats] QS
CROSS APPLY [sys].[dm_exec_sql_text] (QS.[sql_handle]) ST
WHERE ST.[dbid] = DB_ID()
    AND ST.[text] LIKE '%F2B287FD141948DB98814AA77B99450E%';

but it finds nothing.

The code is executed via sp_executesql procedure. Could anyone tell what's wrong with my query or alternative way of detecting IO for specific query.

Your problem is caused by the ST.[dbid] = DB_ID() in your WHERE clause.

I've taken a basic example of what I believe you are trying to do from AdventureWorks:

USE AdventureWorks;
GO

DECLARE @sql NVARCHAR(2000);

SET @SQL = 'SELECT TOP 10 * FROM Person.Person';

SET @SQL = @SQL + '--' + REPLACE('F2B287FD-1419-48DB-9881-4AA77B99450E', '-', '');

EXEC sys.sp_executesql @sql;

When you try and get the dbid value from the dm_exec_sql_text() DMV, it's actually NULL for this query, so you are immediately filtering it out.

However, by using the dm_exec_plan_attributes() DMV, WHERE attribute = 'dbid' you can get the correct DBID() value for your current database.

This is my current go-to query to retrieve this information.

  SELECT
    deqs.plan_handle AS PlanHandle
  ,deqs.query_hash AS QueryHash
  ,dest.dbid
  ,DB_NAME(CAST(depa.value AS SMALLINT)) AS DatabaseName
  ,OBJECT_SCHEMA_NAME(dest.objectid, CAST(depa.value AS INT)) AS SchemaName
  ,OBJECT_NAME(dest.objectid, CAST(depa.value AS INT)) AS ObjectName
  ,MAX(last_execution_time) AS LastExecuted
  ,deqs.creation_time AS PlanCreationTime
  ,SUM(deqs.execution_count) AS Executions
  ,SUM(total_elapsed_time) AS SumDuration
  ,CAST(100.0 * SUM(total_elapsed_time)
              / SUM(SUM(total_elapsed_time)) OVER() AS NUMERIC(5,2)) AS PctDuration
  ,SUM(total_worker_time) AS SumCPU
  ,CAST(100.0 * SUM(total_worker_time)
              / SUM(SUM(total_worker_time)) OVER() AS NUMERIC(5,2)) AS PctCPU
  ,SUM(total_logical_reads + total_logical_writes + total_physical_reads) AS [SumIO]
  ,CAST(100.0 * SUM(total_logical_reads + total_logical_writes + total_physical_reads)
              / SUM(SUM(total_logical_reads + total_logical_writes + total_physical_reads)) OVER() AS NUMERIC(5,2)) AS PctIO
  ,Q.statementtext
  ,deqs.query_plan_hash AS QueryPlanHash
  ,SUM(total_rows) AS RowsAffected
  ,MAX(statement_start_offset) statement_start_offset
  ,MAX(statement_end_offset) statement_end_offset
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
CROSS APPLY sys.dm_exec_plan_attributes(plan_handle) AS depa
CROSS APPLY (VALUES (SUBSTRING(dest.text, (deqs.statement_start_offset/2)+1,
                          ((CASE deqs.statement_end_offset WHEN -1 THEN DATALENGTH(dest.text)
                                ELSE deqs.statement_end_offset
                              END - deqs.statement_start_offset)/2)+1))) AS Q(statementtext)
WHERE deqs.last_execution_time > DATEADD(dd, -1, GETDATE())
      AND depa.attribute = 'dbid'
      AND Q.statementtext LIKE '%F2B287FD141948DB98814AA77B99450E%'
GROUP BY
      dest.text, Q.statementtext, deqs.plan_handle, deqs.creation_time,
        dest.objectid, deqs.query_hash, deqs.query_plan_hash, depa.value, dest.dbid;


Clustered index performance consideration in MySQL & PostgreSQL In MySQL/InnoDB, the clustered index is synonymous with the primary key, so picking up a poor primary key affect your db performance, i.e. use of UUID as PK is a performance killer for database write.

Now, in PostgreSQL, there is no such thing as clustered limitation as in MySQL. What are the impact if I pick  UUID as the PK? Does the database write performance killer also exists in PostgreSQL like in MySQL?

MySQL

While the MySQL Documentation literally says Typically, the clustered index is synonymous with the primary key, they are not one and the same. Please keep in mind that the clustered index (called gen_clust_index) was created in such a way that the index pages for the PRIMARY KEY and the table's row data coexist in the same pages. Having wide PRIMARY KEY values, such as a UUID, would make BTREE pages much wider. It could even cause data pages to split. Since the default innodb_page_size is 16KB in MySQL (this was a fixed compiled-in value in MySQL 5.5 and back), you must expect data pages to have fewer rows and less room for PRIMARY KEY navigation per 16KB page.

I have discussed PRIMARY KEY implications before : See my post InnoDB primary key efficiency

PostgreSQL

A StackOverflow post from Peter Eisentraut says The maximum length for a value in a B-tree index, which includes primary keys, is one third of the size of a buffer page, by default floor(8192/3) = 2730 bytes.

According to the PostgreSQL Wiki

  The maximum table size, row size, and maximum number of columns can be quadrupled by increasing the default block size to 32k. The maximum table size can also be increased using table partitioning.

From this, let's say you use 32K blocks instead of the default 8K. You could fit 4 times more info, but there is still a limit of some kind.

Fortunately, UUID is just 16 bytes. I would not expect earth-shattering drawbacks from it.

Analysis

InnoDB's use of the Clustered Index, order inflexible as it may be, would benefit from smaller keys and would lend itself to fast writes because of not having to manage as much space for distributing keys within the Clustered Index.

While PostgreSQL's storage engine is not bounded or tethered like that of MySQL's InnoDB, smaller keys definitely have to process faster and consume less space. This would increase read and write performance for PostgreSQL, MySQL or any other RDBMS.

To demonstrate how structural changes can make a difference, let's use MySQL other storage engine MyISAM (which is non-transactional and does not have Clustered Index). I once took a MyISAM table and changed its row format from Dynamic to Fixed-Length and got a 20% increase in performance without touching anything else. I made the data bigger to get better read performance. The write performance increased for the better as well because there were fewer mechanisms to trigger any space management (See my post What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?).

Just reading through MySQL Documentation on Optimizing Data Size you get phrases like

  Smaller tables normally require less main memory while their contents are being actively processed during query execution.

  Any space reduction for table data also results in smaller indexes that can be processed faster.

  Use the most efficient (smallest) data types possible. MySQL has many specialized types that save disk space and memory. For example, use the smaller integer types if possible to get smaller tables. MEDIUMINT is often a better choice than INT because a MEDIUMINT column uses 25% less space.

To further my point on smaller datatypes, I mention MySQL's SELECT ... PROCEDURE ANALYSE();. When you run SELECT * FROM tablename PROCEDURE ANALYSE();, the output is an analysis of your data, min values, max values, avg values, STD of values, and (here is the main point) the recommended data type for each column.

If you apply the ALTER TABLE commands to apply the recommended data types, the table has to end up smaller.

Even PostgreSQL would have to benefit from smaller data types. How ?

Please recall the PostgreSQL has this mechanism called TOAST (The Outside Attribute Storage Technique) that I have discussed before (See my post Proposal: MySQL blob handling revision has to juggle row data if there are large columns. Obviously, this mechanism would never be triggered is all row was small and many rows would fit comfortably in the 8K blocks PostgreSQL has.

Conclusion

Since your question seems to be more focused on PostgreSQL, let me answer your question like this:

  What is the impact if I pick UUID as the PK? Does the database write performance killer also exists in PostgreSQL like in MySQL?

PostgreSQL would process write faster with smaller column values. UUID is 16 bytes. Using an 8-byte integer as a PRIMARY KEY would be faster to write and process than a UUID. A 4-byte integer is even faster than that. Lesson from all this ? Don't slow yourself down with wider PRIMARY KEY values if you don't have to.


Tracking stored procedure usage Besides using SQL Server Profiler, is there any way to track which stored procedures are being used, or at least when they were last executed?

You can look in the plan cache to get a pretty good idea of Stored Procedure usage.  Take this query, for instance:

select
    db_name(st.dbid) as database_name,
    object_name(st.objectid) as name,
    p.size_in_bytes / 1024 as size_in_kb,
    p.usecounts,
    st.text
from sys.dm_exec_cached_plans p
cross apply sys.dm_exec_sql_text(p.plan_handle) st
where p.objtype = 'proc'
and st.dbid = db_id('SomeDatabase')
order by p.usecounts desc

This will give you the usecounts of the stored procedures that are cached pertaining to SomeDB.

Note: the plan cache contains the execution plans.  This retention of these plans has many factors involved.  Whereas this will give you a good idea of what is being used and how often, it's definitely not the running total of stored procedures and how often/when they were executed.

BOL Reference about the Plan Cache


Does the order for clustered index have the same constraints as non-clustered index on search? I have a table filled with a bunch of data, and I want to use the following clustered index:

[account_id, group_id, run_id, page_id, date]

For a non-clustered index, if I were to to use WHERE filters involving account_id, page_id, and date_time, I know that the index won't work because I'm not also including group_id and run_id. Does the same thing apply when using a clustered index? I often want to make a query in the form:

SELECT *
FROM my_table
WHERE ad_account_id = %d
  AND page_id = %d
  AND date >= DATE(%s)
  AND date <= DATE(%s)

But sometimes I have queries such as:

SELECT *
FROM my_table
WHERE ad_account_id = %d
  AND group_id = %d
  AND date >= DATE(%s)
  AND date <= DATE(%s)

or

SELECT *
FROM my_table
WHERE ad_account_id = %d
  AND group_id = %d
  AND run_id = %d
  AND date >= DATE(%s)
  AND date <= DATE(%s)

It's not obvious whether any of these take advantage of the clustered index past the one given on the account_id (I'm pretty sure this isn't doing a full table scan but even there not sure).

Just think left-to-right, until you hit a gap.

For Q1, SQL server can search for ad_account_id (assuming that was in the index and you had a typo in the index definition), and then scan those with that value.

For Q2, SQL server can search for ad_account_id and group_id and then scan those.

For Q3, SQL server can seek for all columns in the WHERE clause.

So, yes, the same principals applies to a clustered index as for a non-clustered index.


What's the deal with transactions in a stored procedure called by an SSRS report? Consider the following:

CREATE PROCEDURE dbo.usp_trantest AS
SELECT @@TRANCOUNT as trancount;
GO

When I call usp_trantest manually from within SSMS, the trancount is 0. If I run an SSRS report that contains a dataset that queries the same stored procedure, the trancount is logged as 1.

Doing a T-SQL trace of the SSRS method shows a trace event for the stored procedure call that is identical to the one I did from SSMS.

Is there some behavior that could change based on the SSRS context? Like, an implicit transaction being opened for the dynamic sql call or something, or SSRS creating a transaction context outside of T-SQL?

Edit:

In a now-deleted answer from someone (thanks, stranger!) it was suggested that the SSRS report's dataset might have had "Use single transaction when processing the queries" checked. This was indeed the case!

I did some further testing, and with this setting unchecked, the @@TRANCOUNT is the same whether running in SSMS or from an SSRS report.

So it appears we can draw the conclusion that this Data Source setting does cause the SSRS report to create a transaction context on the database before running the queries. Since this extra transaction does not appear in the T-SQL trace, we can probably assume that it is being opened using an API method, instead of a T-SQL statement.

On the data source of an SSRS report, there is a property for "Use single transaction when processing the queries". There are a couple different behaviors tied to marking this checkbox:

1) As you noticed, SSRS will use an explicit transaction to process the data sets using this data source, if this checkbox is marked. When it is not marked, there is no explicit transaction.

2) When this checkbox is not marked, data sets using the data source will execute in parallel, using separate connections to the server. If the checkbox is marked, then the data sets use a single connection and are processed serially.

Additionally, there may be different motivations for using this checkbox. The two major ones are:

1) Read consistency: If you are using Snapshot Isolation, you may want all data sets to be consistent to a single point in time. In this case, using a single explicit transaction and using the Snapshot isolation level can ensure all statements within the transaction are consistent to a single point in time.

2) Performance: Depending on a number of factors (which are really a separate topic), you may see dramatic performance difference between running the queries for different data sets in parallel vs running them serially.


Can't connect to Oracle 12c Release 1 in docker container from host I pulled the Oracle 12.1.0.2 docker image from the Oracle Container Registry (container-registry.oracle.com/database/standard:12.1.0.2) and created a container as per the instructions, leaving the environment file blank so as to keep all the parameters as their defaults (I am aware that I should change the password etc but right now I just want to get it working).

$ docker run -d --env-file ./db_env.dat -p 1527:1521 -p 5507:5500 -it --name dockerDB --shm-size="8g" container-registry.oracle.com/database/standard

I was able to successfully start the container, and can access it via docker exec. I was also able to verify that the Oracle instance was running correctly by executing some sqlplus against it from the shell inside the container.

However, I am not able to connect to the Oracle instance from my host machine, either through SQL Developer:

or through sqlplus:

I get no errors, but the connection process just hangs forever. Do I need to change some setup somewhere or have I got the details wrong?

I'm running Oracle 11g Personal edition docker container

$ docker ps
CONTAINER ID        IMAGE                                    COMMAND                  CREATED             STATUS              PORTS                                                                     NAMES
d84af138d77a        oracleinanutshell/oracle-xe-11g:latest   "/bin/sh -c '/usr/sb…"   22 minutes ago      Up 22 minutes       0.0.0.0:32777->22/tcp, 0.0.0.0:32776->1521/tcp, 0.0.0.0:32775->8080/tcp   oracle-xe-11g

As you can see port 1521 was  mapped to 32776.

Replace 0.0.0.0 with ipaddress of docker vm running on Virtualbox i.e 192.168.99.100

 sqlplus test/test@192.168.99.100:32776/XE

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Aug 9 13:12:19 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

If you're trying to login as sys user add sysdba at the end


Manipulating a Column Containing Key/Value Pairs I'm accessing and creating reports from a vendor via a replicated SQL Server database. They've done some absolutely insane things that I've been trying to solve for, but this one takes the cake.

They have a table that has many standard columns. But this table also has a column called "Data". The column is a legacy "text" data type, and it contains a giant (hundreds) list of key/value pairs. Each pair is separated by a CRLF, and the key and value are separated by an equal sign. Example:

select myTable.[data] from myTable where tblKey = 123

Result:

Key 1=Value 1
Key 2=Value 2
Key 3=Value 3
...
Key 500=Value 500

I'm trying to determine the most efficient way to break that column out into a usable table of data. The end goal would be to be able to query the table in a way that returns the table key along with specified key/values as column/fields as such:

tblKey | [Key 1] | [Key 3] | [Key 243]
-------|---------|---------|-----------
 123     Value 1   Value 3   Value 243
 124     Value 1   Value 3   Value 243
 125     Value 1   Value 3   Value 243

Is there a way to mold that column into a View? I can't imagine that a Function would be particularly efficient, but I'm sure I could parse things out that way using a string_split or something of that sort. Has anyone run into this type of atrocity before and found a good way to manipulate it into usable data?

Edit to add dbfiddle sample data.

The data is replicated from a vendor's source, so I can't create new tables. I can create views, procedures and functions. That's what I'm looking for advice for a decent way to accomplish.

UPDATE

If as you posted in your own answer you're able to use a UDF to get specific key values let me suggest this one: (You don't need to split all key/values and you don't need to read the table again, you can get it by using text functions.)

CREATE FUNCTION fnGetKey(@Data text, @Key varchar(20))
RETURNS varchar(100)
AS
BEGIN

  RETURN
  (
  SELECT
      SUBSTRING (
                  @Data,
                  /* Position of first '=' after key + 1 */
                  CHARINDEX('=', @Data, PATINDEX('%' + @key + '%', @Data)) + 1,
                  /* Lenght, Position of first chr(13) after key less previuos value - 1 */
                  (CHARINDEX(CHAR(13), @Data, PATINDEX('%' + @key + '%', @Data))
                  -
                  CHARINDEX('=', @Data, PATINDEX('%' + @key + '%', @Data))) - 1
                )
  )

END

SELECT
    FruitID, Name, Description,
    dbo.fnGetKey([Data], 'key 2') as [key 2],
    dbo.fnGetKey([Data], 'key 4') as [key 4]
FROM
    [Fruit];

FruitID | Name   | Description | key 2   | key 4
------: | :----- | :---------- | :------ | :------
      1 | Banana | Delicious   | value 2 | value 4
      2 | Pear   | Rotton      | value 2 | value 4
      3 | Kiwi   | Okay        | value 2 | value 4

db<>fiddle here

Original answer

The only solution I can figure out is by splitting key/values and then pivot it to obtain the desired result.

Unfortunately there are some inconveniences:

STRING_SPLIT doesn't works with text columns. Hence you must cast it to varchar before you are able to manipulate it.
STRING_SPLIT requires a nchar(1) or nvarchar(1), ergo you should replace CHAR(3)+CHAR(10) by a single character.
Aggregate function on PIVOT works better with numeric values, then you should cast Value to some numeric data type.
PIVOT needs a well-known number of columns, in my example I have used a few of them but you should write the whole sequence unless you'd rather deal with dynamic queries.

This is what I've got using your sample data:

WITH KP AS
(
    SELECT FruitID, Name, Description, value as KPair
    FROM   Fruit
    CROSS APPLY STRING_SPLIT(REPLACE(CAST(Data AS varchar(max)), CHAR(13)+CHAR(10), ','), ',') /* STRING_SPLIT only allows nchar(1),  varchar(1) */
)
, KP1 AS
(
  SELECT
      FruitID,
      SUBSTRING(KPair, 5, CHARINDEX('=', KPair) - 5) AS [Key],
      SUBSTRING(KPair, CHARINDEX('=', KPair) + 7, LEN(KPair) - CHARINDEX('=', KPair) - 6) AS [Value]
  FROM
      KP
)
SELECT [FruitID], [1],[2],[3],[4],[5]
FROM   KP1
PIVOT (MAX([Value]) FOR [Key] IN ([1],[2],[3],[4],[5])) AS PVT;

First CTE split every Key X=Value Y. The second one cut this value to obtain each [Key] and [Value]. And the final PIVOT compose the final result in columns.

FruitID | 1  | 2  | 3  | 4  | 5
------: | :- | :- | :- | :- | :-
      1 | 1  | 2  | 3  | 4  | 5
      2 | 1  | 2  | 3  | 4  | 5
      3 | 1  | 2  | 3  | 4  | 5

db<>fiddle here

NOTE: I'm not sure if I should maintain [Key 1] & [Value 1] or it should be converted as a column named [Key] & [Value].

A different approach

When I work with 3rd party databases I usually add a new database, on the same server/instance if possible, and then I use it for my own purposes, just to avoid conflicts with the DB owners.

In this case you could add a new table and periodically throw a process to update it with the new values.

You could use a table with all columns:

CREATE TABLE [FruitKeys]
(
    [FruitID] int NOT NULL PRIMARY KEY,
    [V1]      int NULL,
    [V2]      int NULL,
    [V3]      int NULL,
    [V4]      int NULL,
    [V5]      int NULL
);

or a table with Key/Value pairs and use a pivot to obtain the final result:

CREATE TABLE [FruitKeys]
(
    [FruitID] int NOT NULL,
    [Key]     int NOT NULL,
    [Value]   int NOT NULL,
    CONSTRAINT [PK_FruitKeys] PRIMARY KEY ([FruitID], [Key])
);


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

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

So, I wrote a simple test as follows:

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

But PostgreSQL (9.3) is complaining about

  syntax error at or near "AS"

My questions are:

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

Thanks in advance.

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

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

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

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


SQL log file 2TB is not enough, now what? This is a hypothetical question, that flows from the answer by sepupic where they explain that 2TB is the physical limit for SQL log file.

  if you need it more than 2Tb you add the second log file.

I have long held the belief that multiple log files are bad as outlined in the post Multiple Transaction Log files and performance impact

I just can't imagine a scenario where having 2TB of log files even occurs, but if it does, and for some reason more frequent log backups will not cure it (multiple scenarios implied), what do you do?

Do you add a second log file, or is there something else?

multiple log files are bad

It's not that multiple log files are bad... It's that multiple log files are totally unnecessary and provide no benefit ... unless you require a log file that is larger than 2TB.

With data files, SQL Server can benefit from multiple files because it will perform parallel I/O to/from both files at the same time. However, with log files, SQL Server will only write to one or the other. The circular nature of the log file means that if you create two files, SQL Server will write to one, get to the end, then write to the other, get to the end, then go back to the first, etc.

There is no performance gain from having multiple log files.

However, because of the capacity limitation that any log file can be at most 2TB in size, if you need more than 2TB of log file, you would need to create multiple log files. This is the only reason to create multiple log files.

Why do you need a 2TB log file?

  I just can't imagine a scenario where having 2TB of log files even occurs

Because your transaction log will grow during full backups, if you have a very large database that takes a log time to back up, and is very busy during backups, you may generate more than 2TB of transaction log during the backup.

Similarly, if you have a very large, log-running transaction (such as an index rebuild on a very large table), you may generate 2TB of transaction log before it can be re-used.

Of course, your transaction log can grow for a number of reasons. These aren't necessarily normal to make your log grow very large, but they do contribute to growth. If your transaction log backups are not completing successfully, or your AG is not synchronizing, or replication is not reading transaction from your log....... your log file will grow. If those problems continue, your log file will eventually reach 2TB.

That said, the need for 2+TB of log file is very rare.


Should I use utf8_unicode_ci or utf8_slovak_ci? I was using utf8_slovak_ci collation until now, but I read that utf8_unicode_ci sorts very wide range of languages. Does it mean, I do not need to use separate collation for every language and I can simply always use utf8_unicode_ci?

Lets say all content on my website is in Slovak language - is there difference between using utf8_unicode_ci or utf8_slovak_ci?

I use MariaDB 5.5 (they say it is functionaly identical to MySQL 5.5).

I am not going to answer you because I do not know utf8_slovak_ci nor the slovak language, but I will tell you how I would test it myself:

Take an extensive list of words containing "problematic" (as in, non-existant in the English alphabet) letter representations (as far as I know, letters in your language containing ˇ, ´, ¨ and ˆ, maybe others)- Please forgive my ignorance of your language.

Sort them and compare them using the 2 potential collations, like this:

SELECT word FROM test_table ORDER BY word COLLATE utf8_slovak_ci;
SELECT word FROM test_table ORDER BY word COLLATE utf8_unicode_ci;

SELECT word FROM test_table WHERE word like 'ž%' COLLATE utf8_slovak_ci;
SELECT word FROM test_table WHERE word like 'ž%' COLLATE utf8_unicode_ci;

Check for differences between them, and with the common orthography rules.

If you get similar results, they should be equivalent, and you should think whether you want to also use the collation rules of the other languages (even if your site is only Slovak, I may register there as "jynus from Logroño", which contains a "problematic" character). In that case, use the unicode version, and remember that at any time you can change the collation of comparisons for particular queries. For example:

mysql> SELECT 'a' = 'á' COLLATE utf8_unicode_ci, 'a' = 'á' COLLATE utf8_bin;
+------------------------------------+-----------------------------+
| 'a' = 'á' COLLATE utf8_unicode_ci  | 'a' = 'á' COLLATE utf8_bin  |
+------------------------------------+-----------------------------+
|                                  1 |                           0 |
+------------------------------------+-----------------------------+
1 row in set (0.01 sec)


What is the Difference between "Internal" and "External" Source Systems in Data Warehousing? I am reading about data warehousing and I run into the concept of internal and external source systems.

  Data are extracted from various internal and external source system files and databases.

I couldn't clearly distinguish between the concept of internal and external.

Any explanation of the key difference?

Thank you,

You could get data from data providers - for example, geoip data from Maxmind, rating data from Moody's, device data from BlueCava. Those are all external systems. Systems used by the organization are internal systems.