Postgresql select max performance I have very simple function:

CREATE OR REPLACE FUNCTION myscheme.get_last_timecode(ptable_ids integer[], ptimecode bigint DEFAULT NULL::bigint)
  RETURNS bigint AS
$BODY$
  SELECT MAX(timecode) AS timecode
  FROM myscheme.event
  WHERE table_id = ANY($1) AND ($2 IS NULL OR timecode <= $2);
$BODY$
  LANGUAGE sql STABLE
  COST 100;

Count of rows:

SELECT COUNT(*) FROM myscheme.event WHERE table_id = 1; -- 120
SELECT COUNT(*) FROM myscheme.event WHERE table_id = 2; -- 18
SELECT COUNT(*) FROM myscheme.event WHERE table_id = 3; -- 839795

The results are quite unexpected:

EXPLAIN ANALYZE SELECT myscheme.get_last_timecode(ARRAY[1], NULL) -- Total runtime: 212.552 ms
EXPLAIN ANALYZE SELECT myscheme.get_last_timecode(ARRAY[2], NULL) -- Total runtime: 213.713 ms
EXPLAIN ANALYZE SELECT myscheme.get_last_timecode(ARRAY[3], NULL) -- Total runtime: 0.186 ms (the fastest!)

When I use plain query, execution time is normal:

EXPLAIN ANALYZE SELECT MAX(timecode) AS timecode FROM myscheme.event
  WHERE table_id = ANY(ARRAY[1]) AND (NULL IS NULL OR timecode <= NULL);

Aggregate  (cost=10.18..10.19 rows=1 width=8) (actual time=0.079..0.079 rows=1 loops=1)
  ->  Index Scan using event_table_id_index on event  (cost=0.42..10.05 rows=51 width=8) (actual time=0.013..0.067 rows=120 loops=1)
        Index Cond: (table_id = ANY ('{1}'::integer[]))
Total runtime: 0.101 ms

but it uses another execution plan for table_id = 3:

EXPLAIN ANALYZE SELECT MAX(timecode) AS timecode FROM poker.event
  WHERE table_id = ANY(ARRAY[3]) AND (NULL IS NULL OR timecode <= NULL);

Result  (cost=0.47..0.48 rows=1 width=0) (actual time=0.018..0.018 rows=1 loops=1)
  InitPlan 1 (returns $0)
    ->  Limit  (cost=0.42..0.47 rows=1 width=8) (actual time=0.015..0.016 rows=1 loops=1)
          ->  Index Scan Backward using event_timecode_key on event  (cost=0.42..35873.11 rows=845301 width=8) (actual time=0.015..0.015 rows=1 loops=1)
                Index Cond: (timecode IS NOT NULL)
                Filter: (table_id = ANY ('{3}'::integer[]))
Total runtime: 0.038 ms

Can anybody explain me how to create a function (or index) whose execution time will not depend on the amount of data?

SELECT version();
PostgreSQL 9.3.14 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit

And the table definition:

CREATE TABLE myscheme.event
(
  id bigserial NOT NULL,
  table_id integer,
  deal_id bigint,
  type_id integer NOT NULL,
  timecode bigserial NOT NULL,
  created timestamp with time zone NOT NULL DEFAULT now(),
  parent_id bigint,
  prev_id bigint,
  CONSTRAINT event_pkey PRIMARY KEY (id),
  CONSTRAINT event_deal_id_fkey FOREIGN KEY (deal_id)
      REFERENCES myscheme.deal (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT event_parent_id_fkey FOREIGN KEY (parent_id)
      REFERENCES myscheme.event (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT event_prev_id_fkey FOREIGN KEY (prev_id)
      REFERENCES myscheme.event (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT event_table_id_fkey FOREIGN KEY (table_id)
      REFERENCES myscheme.tables (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT event_type_id_fkey FOREIGN KEY (type_id)
      REFERENCES myscheme.event_type (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT event_timecode_key UNIQUE (timecode)
)
WITH (
  OIDS=FALSE
);

CREATE INDEX event_deal_id_index ON myscheme.event USING btree (deal_id);
CREATE INDEX event_table_id_index ON myscheme.event  USING btree (table_id);
CREATE INDEX event_type_id_index ON myscheme.event USING btree (type_id);

if you look at the statistics the execution plans make perfect sense.
The table has around 840k tuples almost all of table id 3.
So if you're looking for max(timecode) scanning the timecode index backwards makes perfect sense with table id 3 (almost every line you hit has that id).

But if you're looking for table id 1 - you have only 120 tuples out of 840k, your best bet is to scan these 120 tuples looking for the  max(timecode).

It's like looking for a needle in a hay stack - looking for hay is simpler than looking for the needle.

Hope it made some sense into what the optimizer planned.

Btw, you could try to build a composite index of table id and timecode - I'm not near a db to check but I think PG could use that to skip to the max timecode per table id directly.
- worth a shot :)

Regards
Jony


Communication link failure for some queries to linked server I am seeing the following error in SSMS (server has Windows Server 2008 and Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 installed) when running some queries against the linked server, in particular long running queries.

Simple selects from tables across the linked server work fine. This is a new issue that was noticed when SPs that have worked for years started failing.

I have run a Wireshark capture on the server, capturing for packets to port 1433 on the linked server host. At the tail of the capture, I see many (10) TCP Keep-Alives being issued (after a message regarding bad checksum) and then an RST packet. The RST packet is correlated with the error below being returned to the client.

There are other database servers on our network, where the linked server is configured identically, that don't exhibit this issue.

I have found some articles such as this and this. We are using the implicated Broadcom NICs. The Chimney Offload State setting is enabled on the server.

We will try disabling. Other thoughts on troubleshooting would be much appreciated.

OLE DB provider "SQLNCLI10" for linked server "myServer" returned message "Protocol error in TDS stream".
OLE DB provider "SQLNCLI10" for linked server "myServer" returned message "Communication link failure".
Msg 65535, Level 16, State 1, Line 0
Session Provider: Physical connection is not usable [xFFFFFFFF].
OLE DB provider "SQLNCLI10" for linked server "myServer" returned message "Communication link failure".
Msg 65535, Level 16, State 1, Line 0
Session Provider: Physical connection is not usable [xFFFFFFFF].
OLE DB provider "SQLNCLI10" for linked server "myServer" returned message "Communication link failure".
Msg 64, Level 16, State 1, Line 0
TCP Provider: The specified network name is no longer available.

We disabled TCP Chimney Offload per the Symantec article and rebooted the servers and the issue appears to be resolved.

The problematic SPs are no longer throwing the communication link failure exception.

More info from Microsoft on TCP Offloading


Can I iterate a value depending if the previous is what I want? Suppose that I have this table in PostgreSQL 9.4:

CREATE TABLE trajs (
    id serial NOT NULL PRIMARY KEY,
    obj_id integer,
    traj_id integer,
    geom geometry,
    t timestamp without time zone
);

Here is what the data looks like:

 obj_id, traj_id,  geom,     t
    1       1      point     '2016-04-29 09:35:00'
    1       1      point     '2016-04-29 09:40:00'
    1       1      point     '2016-04-29 09:55:00'
    2       1      point     '2016-04-29 09:35:00'
    2       1      point     '2016-04-29 09:42:00'
    2       1      point     '2016-04-29 09:50:00'
    3       1      point     '2016-04-29 09:35:00'
    3       1      point     '2016-04-29 09:41:00'
    3       1      point     '2016-04-29 09:43:00'

For each 'obj_id', can I iterate 'traj_id' depending on the 't' distance between the previous?

An example: for each 6 minutes of difference (between the first object with a time, like 3rd column and 1st in that table), this should be a new traj_id (+1) - and restart for a new obj_id. Here is what result should look like:

 obj_id, traj_id,  geom,     t
    1       1      point     '2016-04-29 09:35:00'
    1       1      point     '2016-04-29 09:40:00'
    1       2      point     '2016-04-29 09:55:00'
    2       1      point     '2016-04-29 09:35:00'
    2       2      point     '2016-04-29 09:42:00'
    2       3      point     '2016-04-29 09:50:00'
    3       1      point     '2016-04-29 09:35:00'
    3       2      point     '2016-04-29 09:41:00'
    3       2      point     '2016-04-29 09:43:00'

I started doing this, but my solution doesn't work. And I can't see how it can iterate by every time that the condition is met.

update trajs t
set traj_id = traj_id+1
where t.t > (select ant.t + interval '6 minutes'
             from trajs ant
             where  t.obj_id=ant.obj_id);

How to solve this?

For Postgres 9.4+:

UPDATE trajs t
SET    traj_id = upd.traj_id
FROM  (
   SELECT id, 1 + count(*) FILTER (WHERE t >= t0 + interval '6 min')
                           OVER (PARTITION BY obj_id ORDER BY t) AS traj_id
   FROM  (
      SELECT id, obj_id, t, lag(t) OVER (PARTITION BY obj_id ORDER BY t) AS t0
      FROM   trajs
      ) sub
   ) upd
WHERE t.id = upd.id
AND   t.traj_id IS DISTINCT FROM upd.traj_id;

Increases traj_id only if the previous row is 6 minutes or more earlier - exactly like requested.

About the last condition I added to optimize performance (last paragraph):

How do I (or can I) SELECT DISTINCT on multiple columns?

The way I formulated it, it also works for the corner case of the first row per obj_id, where lag() returns NULL.

About the aggregate FILTER I combined with the second window function:

Return counts for multiple ranges in a single SELECT statement


Bulk insert and fast ordered search large data 3B+ records Scenario

We want to study behavior of some similar entities that changes based on time.

Currently I'm using MS SQL Server 2016

Table Structure

CREATE TABLE [dbo].[BehaviorHistory](
    [EntityId] [uniqueidentifier] NOT NULL,
    [BehaveFactor] [bigint] NOT NULL,
    [TimeOffset] [bigint] NOT NULL,
 CONSTRAINT [PK_BehaviorHistory] PRIMARY KEY CLUSTERED
(
    [EntityId] ASC,
    [BehaveFactor] ASC,
    [TimeOffset] ASC
)

Sample Query

We want to see this similar entities how often repeat same behavior as our test subject

SELECT *
FROM
    (
        SELECT
        EntityId,
        BehaveFactor,
        (TmpOffset.PerviousMainTimeOffset - TmpOffset.CurrentMainTimeOffset) MainDeltaTime ,
        (TmpOffset.PerviousSubTimeOffset - TmpOffset.CurrentSubTimeOffset) SubDeltaTime
        FROM
            (
                SELECT
                LAG(main.TimeOffset) OVER (ORDER BY main.EntityId ,history.TimeOffset,testSub.TimeOffset) As PerviousMainTimeOffset,
                LAG(testSub.TimeOffset) OVER (ORDER BY main.EntityId ,history.TimeOffset,testSub.TimeOffset) As PerviousSubTimeOffset,
                main.EntityId AS EntityId,
                main.BehaveFactor as BehaveFactor,
                main.TimeOffset AS CurrentMainTimeOffset,
                testSub.TimeOffset AS CurrentSubTimeOffset
                FROM BehaviorHistory AS main
                INNER JOIN SampleBehaviorHistory AS testSub ON main.BehaveFactor = testSub.BehaveFactor
            ) as TmpOffset
    )AS MatchTable

Where
MainDeltaTime = SubDeltaTime --repeated behavior in same time order
AND MainDeltaTime <> 0 --exclude repeated behavior at the same time

Problem

We currently inserting large amount of data into BehaviorHistory table
its size is like 100 GB more with more than 3 Billions records in last 8 months or so.

The sample query performance is good (like 300ms) if we have index but when we create non clustered index on BehaveFactor (includes TimeOffset) the insert operation timeout on this table, so currently I have two DB one without index used for inserting data and another that every day get copy of inserted DB and create index on it and used for reports.

Error

With no index its works fine, when we try to insert in a indexed table after a couples of bulk insert (eg 2000 records) we got this error

[fnInsertBehaviors(EntityId : 1705438)]
#     1 : The underlying provider failed on Open.
#     2 : Connection Timeout Expired.  The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement.  This could be because the pre-login handshake failed or the server was unable to respond back in time.  The duration spent while attempting to connect to this server was - [Pre-Login] initialization=58416; handshake=9;
#     3 : The wait operation timed out
#

Looking for solution to provide real time reports (not based on yesterday data) and avoid two DB and scheduled backup restore to create indexed table.

~600 records inserted per second in a certain period of time in a day.

With non clustered index, insert query timeout and without it performance is out of question. Insert fails in normal mode with no lock on table, normal insert fails when we have no query running against that table.

Also I need to consider growing database maybe next year its size is 300 GB so I have to find a solution that can handle that much data before it gets too late and system fails.

It looks like this question will be closed soon, but you could consider using a nonclustered columnstore index. The business case that you are describing with this question seems to match Microsoft's vision for it:

  Real-time analytics uses an updateable columnstore index on a rowstore table. The columnstore index maintains a copy of the data, so the OLTP and analytics workloads run against separate copies of the data. This minimizes the performance impact of both workloads running at the same time. SQL Server automatically maintains index changes so OLTP changes are always up-to-date for analytics. With this design, it is possible and practical to run analytics in real-time on up-to-date data. This works for both disk-based and memory-optimized tables.

There's a lot of information contained in that link and it links out to a bunch of other blog posts, so it's difficult to summarize it all. You might experience the following benefits:

CCIs have a typical compression ratio of 10:1, so it might be smaller than the nonclustered index that you want to build. It'll certainly be smaller than a separate copy of the table.
You can use the compression delay option discussed here to possibly lighten the load on the server when inserting data.
Converting the table to a CCI will enable batch mode for your query. You may see performance benefits from this, especially because the window functions are eligible for batch mode as of SQL Server 2016.

However, it also might not be helpful for your workload. The only way to know is to test. If you really need to constantly run a query against a 3 billion row table without any filters perhaps a data model change is needed. The insert timeout that you saw also hints at a deeper problem, but I don't have the knowledge or additional context needed to to troubleshoot that.


Assign the result of current_setting('the_setting') to a variable in PL/pgSQL I have a function where I need to get a configuration parameter and assign it to a variable that I will use later in the function. The problem is that when the configuration parameter is not recognized (because it does not exist yet) the function dies. I want to evaluate if the variable was able to be assigned and if not set a null value to it.

This is what I tried:

   DECLARE
      conf_param text;
      num integer;

   BEGIN
      SELECT current_setting('the_setting') INTO conf_param;
         -- here is where dies when the_setting is not recognized!
      IF FOUND THEN
         num := conf_param::integer;
      ELSE
         num := NULL;
      END IF;
      -- more stuff

I am not sure if I am using Found the way is needed.

In Postgres 9.6 or later you can have this much simpler. Like a_horse commented, there is a new variant of current_setting() that takes a second parameter missing_ok, to suppress exceptions if the configuration parameter (poor man's "global variable") is not set. So no expensive error trapping required any more.

And you don't need another variable conf_param, you can cast the result of the function call directly:

DECLARE
   num integer;
BEGIN
   num := current_setting('the_setting', true)::int;
   -- more stuff
END;

This is assuming the config parameter is always a valid numeric string if set. If you are not completely sure about that, you can either add an EXCEPTION clause to trap possible casting errors after all. Or test the value before casting to rule out exceptions. Typically much cheaper. Detailed instructions:

Filter strings with regex before casting to numeric


Does psql \dt cache schema? When I have a session running in psql it does not show newly created tables (from another connection but for the current owner role).

If I re-login they show up. Do I need to refresh a psql cache somehow or is this related to transaction/sessions or cached permissions?

I think I found the problem, the other session did not only create those tables but also modified the Schema search path (which was not active in the existing session of course)


Netezza: What schema contains all the system tables / views? Is there a schema I can look in or a query that I can run to see all the Netezza system or metadata tables?

I know about _v_table, _v_view, _v_relation_column, and _v_groupusers.
I know there are many others and I would like the definitive list.
Documentation on each would be nice too, but I will settle for just a list.

I looked at How list of netezza system tables/views? but I am looking for a list of system tables. The author of that question is looking to pull specific metadata about the tables on his system.

I don't know of a way to access such a list via a query but IBM provide a list of user views and system views in their Knowledge Centre. I assume those pages are public because their website tells me I'm not currently logged in (but it's not the most user-friendly of sites in terms of authentication so I may be wrong).

As CraigBoyd pointed out in a comment, SELECT * FROM _V_SYS_VIEW; will give you the list of system views. SELECT * FROM _V_VIEW; will also list some of the system views plus any user-defined views. I can't tell why some views are included in _V_SYS_VIEW but not in _V_VIEW.


Cant connect to Server using SSMS on my desktop SQL SERVER 2008 R2
When attempting to connect to the default instance of sqlserver on our local server from SSMS on my desktop I'm receiving the following error.

  A network-related or instance-specific error occurred while
  establishing a connection to SQL Server. The server was not found or
  was not accessible. Verify that the instance name is correct and that
  SQL Server is configured to allow remote connections. (provider: Named
  Pipes Provider, error: 40 - Could not open a connection to SQL Server)
  (Microsoft SQL Server, Error: 5)

I've verified that the server is set for remote connections inside
the instance - properties - connections.
I've verified that TCP\IP and Named Pipes are enabled under
Configuration Manager - sql server network configuration - protocols
for MSSQLSERVER
I've verified that the browser service is running and that the sql
server service is running.
I've verified that the 1433 port is opened in the windows firewall.

I'm not sure what else to check.  I'm using the correct servername. If I attempt to connect using IP address I get the following error.

  A network-related or instance-specific error occurred while
  establishing a connection to SQL Server. The server was not found or
  was not accessible. Verify that the instance name is correct and that
  SQL Server is configured to allow remote connections. (provider: Named
  Pipes Provider, error: 40 - Could not open a connection to SQL Server)
  (Microsoft SQL Server, Error: 5)

Any help would be appreciated.

Try to run telnet from your local machine and use the following from command line

telnet [servername/ip] port

ex:> telnet sql01 1433

This will at least verify that you can hit the server on the specified port. As well your local machine could have a firewall enabled or perhaps a 3rd party application that could be blocking your access?

You can consider it a successful test if after pressing ENTER your command window goes blank. (It has successfully started the telnet session - type some random characters to go back to your normal DOS prompt).

A failure would look like this:

  Connecting To sql01...Could not open connection to the host, on port
  1433: Connect failed


Store equations/formula in mysql db table I want to know what is there a way for storing equations or formula in a DB column.
Below is the table where I am storing the machines details(master table). But the last column is what I am worrying.

Columns and values for the table would be

id,name,col1,col2,col3,col4,formula
1,mc1,546,459,1.24,3.9,(col1 * col2 * 0.0016)/(col3 * col4)

The formula changes for each entry in the table. Currently I am hard-coding the formula's in my program like

if(macid == 1) {
 // apply formula1
} else if(macid == 2) {
 // apply formula2
} .....

Now if any new machine gets added in the table I need to make changes int the code. So I want to store the formula in the table itself, so that I can provide UI interface to change the formula.

How can I achieve this. Plese help.

You can try to leverage dynamic SQL.

If you need to get a calculated value for an id

DELIMITER $$
CREATE PROCEDURE get_value(IN _id INT)
BEGIN
  SET @sql = NULL;

  SELECT CONCAT('SELECT ', formula, ' value FROM table1 WHERE id = ', 1)
    INTO @sql
    FROM table1
   WHERE id = 1;

  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
  SET @sql = NULL;
END$$
DELIMITER ;

Note: You can of course use OUT parameter instead of returning the resultset if you want to.

Sample usage:

CALL get_value(1);

Sample output:

|           VALUE |
|-----------------|
| 82.916129032258 |

Here is how a procedure might look like to get all values calculated by formulas

DELIMITER $$
CREATE PROCEDURE get_values()
BEGIN
  SET @sql = NULL;

  SELECT GROUP_CONCAT(CONCAT(
    'SELECT id, ', formula, ' value FROM table1 WHERE id = ', id)
    ORDER BY id SEPARATOR ' UNION ALL ')
    INTO @sql
    FROM table1;

  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
  SET @sql = NULL;
END$$
DELIMITER ;

Sample usage:

CALL get_value(1);

Sample output:

| ID |           VALUE |
|----|-----------------|
|  1 | 82.916129032258 |
|  2 |    0.0000109375 |

Here is SQLFiddle demo for both procedures


High Availability with Transactional Replication We are using SQL 2017 Enterprise and setting up a new DR site. One of our servers (Server B) is used for reporting and has many databases, one of which has tables populated through Transactional Replication from a different database on a different server (Server A). The Publisher is on Server A. The Distributer and Subscriber databases are both on Server B.

Server A is part of a cluster with an Availability Group which includes a DR server. Server B isn't part of a cluster, but I need a DR solution that allows for a failover if we lose a server or our entire primary site.

I'm planning to make a new cluster with Server B and a duplicate for DR (Server B2), but would like to know the best way to deal with the Distributer and Subscriber databases. Can they be part of an Always On Availability Group and will the Transactional Replication continue if there's a failover?

Are there any recommendations from anyone who has experience in this area?

Yes. See the documentation for a detailed list of steps.

  SQL Server Replication, change data capture (CDC), and change tracking (CT) are supported on Always On availability groups. Always On availability groups helps provide high availability and additional database recovery capabilities.


Sql failover cluster issue with different edition setup ( enterprise with standard) I have come across one client who has setup like this where dba has installed
sql server 2014 instance  with enterprise edition setup on node1 and on node2 he has added mssqlserver 2014 in failover cluster with setup of standard edition .
They have never tested the failover before last night they did the patching on passive node and tried to failover active node( node1 enterprise) to passive( node2 standard) but it got failed and services doesn't come up on both the nodes. In the last they had to remove the node2 from cluster and end up repairing sql services on node1.
May i know the cause of it? can it be due to edition mismatch
setup detail abcdxyz sql instance (enterprise) running on node1
added the node to failover cluster on noode2 with standard setup .

to solve the issue do an edition upgrade of sql server on the node 2.
https://docs.microsoft.com/en-us/sql/database-engine/install-windows/upgrade-to-a-different-edition-of-sql-server-setup?view=sql-server-2017
It should not even require the service to be stopped.
At the end you have to restart the node 2


How is the 2 Node Cluster Limit Enforced for SQL Server Standard Edition? I know Books Online says that standard edition only supports 2 nodes but how is this actually enforced?

Will it:

Not let you install a new instance on a cluster with more than 2 nodes?
Not let you add a 3rd node to a failover cluster that has at least 1 SQL Server standard edition instance?
Not let you select more than 2 possible owners for the resource group / cluster role?

Or will it behave in some other way?

I'm curious because one of the admins would like to expand an existing cluster from 2 nodes to 3 nodes but it has 2 standard edition instances of SQL Server on it so I don't know what will happen.

Of course licensing is a necessary part of Microsoft's business, so it is not surprising that the installation would enforce the limits that the purchaser is granted.

Setting up a SQL Server Failover cluster for SQL Server 2012 (Standard as well as Enterprise editions) you must first:

Create a New SQL Server Failover Cluster (Setup)

Option 1: Integration Installation with Add Node (i.e. only 2 nodes permitted.)

Create and configure a single-node SQL Server failover cluster instance. ...
On each node to be added to the SQL Server failover cluster, run Setup with Add Node functionality to add that node.

A notice says: For more information on the different possible scenarios during Add Node operations, see Add or Remove Nodes in a SQL Server Failover Cluster (Setup)

Option 2: Advanced/Enterprise Installation

Add or Remove Nodes in a SQL Server Failover Cluster (Setup)

This option continues on to link to other pages that provide more detailed installation instructions for Advanced/Enterprise Installations. The warning is not explicitly called out for these installations.

This suggests that (1) the Add or Remove Nodes process and (2) the license or, if you prefer, the Standard edition, provides the tools to protect both the purchaser and the provider by controlling, in this case, how many failover nodes can be installed.

Looking forward to SQL Server 2016 SP1 which will bring some good benefits on Standard Edition, by allowing many features to run on Standard.  But it will not allow for multi-node SQL Server failover cluster installations.

Clarification: For SQL Server Standard Edition (from 2000-2016) the failover cluster can only support 2 nodes per instance.  If you are using SQL Server Enterprise Edition you can support more nodes both synchronous and asynchronous, the exact numbers depending on the release you are using.

True, it is also possible to install another instance of SQL Server, even a failover cluster, but it would not be part of the first cluster. And if this was installed on the same physical server, there would likely be some resource contention.

If you want a Third Server in your landscape you can install another server and implement log shipping from the active Cluster Node to the Third Server.  Since this has some latency issues the Third Server will not always be fully up-to-date with the data being shipped to it.


2NF decomposition + database normalization If anyone knows about 2NF I'd greatly appreciate if you could tell me if my understanding of it correct my book does not even mention it (except to say is of "historical significance") and I haven't been able to find a really good example online. I am studying for a test and would like to know if my reasoning on how to accomplish a 2NF decomposition is correct

R = {a, b, c, d, e, f, g}
F = {AB --> C, A --> DE, B --> F, F --> GH, D --> IJ }

The first thing I did was to find the super key which was easy to see (AB)+ = R, However I am not sure if this is what 2NF definition means when it uses the term "key"

2nd I used A.A. and combined some terms in F (just to make it more managable)

F = {AB --> C, A --> DEIJ, B --> FGH}

3rd I remove and partial functional dependencies I am not really sure ( I did look it up to no eval) what a PFD is I think its when you have a FD where the LHS is a proper subset of the superkey in this case

A --> DEIJ and B --> FGH

4th I break it into relations that remove the violations from step 3

R1 = AB --> C

R2 = A --> DEIJ

R3 = B --> FGH

I'm really looking for someone to give me feedback on if I am understanding the concept correctly any help would be greatly appreciated

Thanks

There's a little aphorism that goes:

The key (1NF)
The whole key (2NF)
And nothing but the key (3NF)

. . . So help me Codd

In your example we can assume 1NF to begin with as the relational structure doesn't imply any repeating groups within the row (i.e. no D1, D2, D3  etc.).

  R = {a, b, c, d, e, f, g} F = {AB --> C, A --> DE, B --> F, F --> GH,
  D --> IJ }

2NF deals with 'the whole key' - if you have a composite key and some members of the relation are dependent on a part of that key then they should be split off into their own relations.  In your case:

R1 = AB --> C is in 2NF as 'AB' is 'the whole key'
R2 = A --> DEIJ is 2NF but not 3NF as A --> DE, but A --> IJ is not 3NF as the real dependency of I and J is D --> IJ.  D is not a key of this relation (A --> DEIJ), so A --> IJ violates the 'nothing but the key' princple of 3NF.
R3 = B --> FGH is in 2NF but not 3NF as B --> F is a correct relation and F is dependent on the entirety of the key 'B'.  However, B --> GH is not in 3NF as the correct relation is F --> GH.  B --> GH violates the 'nothing but the key' principle, as G ahd H are correctly dependent on the non-key attribute F.

Your decomposition to 2NF is correct.  Decomposition to 3NF requires taking the non-key attributes that have their own dependencies into separate relations.

The relation in 3NF would look like:

R1 = AB --> C
R2 = A --> DE (I and J are dependent on the non-key attribute D)
R3 = B --> F (G and H are dependent on the non-key attribute F)
R4 = D --> IJ
R5 = F --> GH


Quorum question for SQL Cluster (Always On) We are looking at setting up an SQL Always On High Availability environment at our organisation.
Our organisation has two datacenters of with one much smaller one but this one's being created to create some high availability.

At our primary site we are looking at installing three servers and one at the secondary site. These server will then be added to a cluster (and we'll add the DB's in some Availability Groups accross these servers)

But here comes the problem. When the primary site is down due to a power outage for example, the whole cluster will be down because the one server doesn't have a majority even with a default quorum. We did also look at adding a quorum to cloud-storage (disk-only quorum setup) but when the internet connection is down (happens sometimes here in Belgium) we also loose the entire cluster.

Does anyone have any experience or a solution for this?

Thanks in advance!

At our primary site we are looking at installing three servers and one at the secondary site. These server will then be added to a cluster (and we'll add the DB's in some Availability Groups accross these servers)

With the current configuration there is no way you can keep WSFC running if primary site, which has 3 nodes out of 4 nodes WSFC, goes down due to power outage or some other disaster. With such configuration adding Fileshare witness would be useless because even if you add one there will be 5 voting members and for WSFC to be online there should be at least 3 online and in your case when primary DC goes down 3 voting members will be down bringing WSFC down.

  Does anyone have any experience or a solution for this?

The solution would be to change the architecture, IF you do not want WSFC to come down when primary DC goes down. What you can do is move the 3rd node from primary to secondary DC and configure FC witness in cloud or at some third location which is not secondary DC or Primary DC but accessible to both, so even if primary DC is down there are 3 voting members 2 from secondary DC and one FS witness to keep WSFC up and running.
The other solution is, if you can afford WSFC downtime, let the architecture be as it is and when disaster strikes at primary DC use Forcequorum to bring WSFC online. This seems like bad choice at first but believe me with little practice it works like charm and within 10-15 mins or even less WSFC would be online on DR node and with failover with data loss you could bring database instance online as well. This method is quick with little practice all you need is few commands to be run to bring WSFC and databases online.

Similar readings

Force Start a Windows Server Failover Cluster without a Quorum to bring a SQL Server Failover Clustered Instance Online
Recover WSFC using Forced Quorum for SQL Server AlwaysOn Availability Group


ORA-01422 Error Occurred When I Call Procedure with Number Parameter I want to write PL/SQL Procedure that updates salary which is less than 2000. I wrote this procedure.And when i call it with integer id 'ORA-01422: exact fetch returns more than requested number of rows' error is thrown by TOAD. My procedure is like below:

DECLARE
PROCEDURE update_salary(ID customers.id%type) is
   c_sal  customers.salary%type;
BEGIN
  SELECT salary
  INTO c_sal
  FROM customers
  WHERE id = ID;
   dbms_output.put_line ('Before update operation salary is:' || c_sal);

  --dbms_output.put_line ('Parameter :' || ID);

   IF (c_sal <= 2000) THEN
      UPDATE customers
      SET salary =  salary + 1000
      WHERE id = ID;
      dbms_output.put_line ('Salary updated');
   END IF;

  SELECT salary
  INTO c_sal
  FROM customers
  WHERE id=ID;
   dbms_output.put_line ('After update operation salary is:' || c_sal);
END;

BEGIN
update_salary(1);
END;
/

I print parameter id with dbms_output. The parameter is coming correctly. How can i fix this error !

Naming your parameter the same way you named your column is quite unfortunate.

  SELECT salary
  INTO c_sal
  FROM customers
  WHERE id = ID;

In the above statement, typing id in lowercase or ID in uppercase makes no difference, it is case insensitive. Just because you named your PL/SQL parameter ID (in uppercase), does not mean the database will use it when you type ID (in uppercase) in the above. Even if the id in customers has unique values, the above SELECT returns all* rows from the table, because the id column precedes the ID parameter, and id always* equals ID (*except for NULLs).

You could just simply use a different name for your parameter, for example I usually use the p_ prefix for them, so your ID parameter could be changed to P_ID:

DECLARE
PROCEDURE update_salary(P_ID customers.id%type) is
   c_sal  customers.salary%type;
BEGIN
  SELECT salary
  INTO c_sal
  FROM customers
  WHERE id = P_ID;
   dbms_output.put_line ('Before update operation salary is:' || c_sal);

  --dbms_output.put_line ('Parameter :' || ID);

   SELECT  salary
   INTO  c_sal
   FROM customers
   WHERE id = P_ID;
   IF (c_sal <= 2000) THEN
      UPDATE customers
      SET salary =  salary + 1000
      WHERE id = P_ID;
      dbms_output.put_line ('Salary updated');
   END IF;

  SELECT salary
  INTO c_sal
  FROM customers
  WHERE id=P_ID;
   dbms_output.put_line ('After update operation salary is:' || c_sal);
END;

BEGIN
update_salary(1);
END;
/


MySQL Enterprise Backup error: Log scan was only able to reach xxxx I use MySQL Enterprise Backup to make incremental backups, but when I apply a restore (copy-back), I can't remake a backup.

I get this error:

mysqlbackup: ERROR: Log scan was only able to reach 1667072,
           but a checkpoint was at 1667505.
           This means that the database server has overwritten
           a part of the circular InnoDB log files before ibbackup
           was able to read it.
           To solve this problem either:
           1) re-run the backup when the database has less load, or
           2) re-configure the database with bigger InnoDB log files.

Config:

--------------------------------------------------------------------
                       Server Repository Options:
--------------------------------------------------------------------
datadir = /common/mysql/xxx/data/
innodb_data_home_dir = /common/mysql/xxx/data/
innodb_data_file_path = ibdata1:50M:autoextend
innodb_log_group_home_dir = /common/mysql/xxx/data/
innodb_log_files_in_group = 2
innodb_log_file_size = 26214400

--------------------------------------------------------------------
                       Backup Config Options:
--------------------------------------------------------------------
datadir = /common/mysql/testb/full/latest/datadir
innodb_data_home_dir = /common/mysql/testb/full/latest/datadir
innodb_data_file_path = ibdata1:50M:autoextend
innodb_log_group_home_dir = /common/mysql/testb/full/latest/datadir
innodb_log_files_in_group = 2
innodb_log_file_size = 26214400

I'm afraid your chance to do the incremental has passed.
You need to do two things:
Resize the InnoDB transaction logs
From the messages above, the logs look like 25600K.
You need much bigger logs because writes to the logs are going so fast that incremental backups will lose it place while backing up as you just experienced. You barely have about 512M with ib_logfile0 and ib_logfile1. The fact that an incremental fails shows you are writing more than 512M of transaction info during an incremental backup cycle.
In MySQL, the largest log allowed is 2047M.
Please do the following
STEP01) Run this in the mysql client:
SET GLOBAL innodb_fast_shutdown = 0;

This will cause all transactions to be completely committed to disk. This will shorten mysql startup time because InnoDB Crash Recovery is not needed (or at least drastically minimized).
STEP02) service mysql stop
STEP03) Edit my.cnf with this
[mysqld]
innodb_log_file_size = 2047M
innodb_fast_shutdown = 0

STEP04) Move the logs away
cd /var/lib/mysql
mv ib_logilfe0 ib_logilfe0.bak
mv ib_logilfe0 ib_logilfe0.bak

STEP05) service mysql start (Transaction Logs get recreated sized 2047M)
Please see How to safely change MySQL innodb variable 'innodb_log_file_size'?
Take a fresh Full Backup
Once you take this backup, you now have transaction logs big enough to endure a long-running incremental backup. It is still advisable to do increment backups during offpeak times.
Even with the transaction logs resized to 2047M, the only way an incremental backup's restoration could fail is if more than 4094M of transaction info is written to the log files during the incremental backup cycle.
UPDATE 2012-12-17 11:39 EDT
DISCLAIMER : Not Familiar with MySQL Enterprise Backup
I learned about 1.5 years ago that Percona XtraBackup was similar to MySQL Enteprise Backup. According to the Documentation on XtraBackup's full backup, you are supposed to prepare the Full Backup for restoration. This step is necessary because MySQL may have been writing transactions that are probably present in the double-write buffer that have not been accounted for in any other parts of the InnoDB infrastructure. That way, the following must be done:

transactions written during the backup are extracted from the double-write buffer
those transaction are executed
InnoDB files for the Backups are updated with proper Log Sequence Numbers

As for XtraBackup's Incremental Backup, the Full Backup must be prepared as well.
If the claim that Percona XtraBackup was similar to MySQL Enteprise Backup is indeed true, then there must be a set of preparatory steps that needs to be done once or twice to the Full Backup. Please read in the MySQL Enterprise Backup Documentation and find any reference possible to preparing Full Backups.
UPDATE 2012-12-17 12:55 EDT
After reading the MySQL Documentation on MySQL Enterprise Backup, I noticed something that may help. You stated that you do this:
mysqlbackup --backup-dir=/mnt/testb/full/latest --user=dba backup
mysqlbackup --backup-dir=backupDir/latest apply-log

I think you have a typo:
mysqlbackup --backup-dir=/mnt/testb/full/latest --user=dba backup
mysqlbackup --backup-dir=backupDir/latest --apply-log

I do not see apply-log in the Docs, but I do see these in the Docs

--apply-log
--backup-and-apply-log

Please try one of these options and see if it corrects matters

mysqlbackup --backup-dir=/mnt/testb/full/latest --user=dba backup-and-apply-log
mysqlbackup --backup-dir=backupDir/latest --apply-log


PGAdmin4 Browser: Autocomplete not working I am using PGAdmin4 on my Firefox Browser(Mac). I am looking for auto-complete feature but could not get it work.

The documentation says that the shortcut for auto complete is "Ctrl+Space" but it does not work.

Has anyone figured out how to make it work?

It works if you deactivate the default MacOS shortcut for switching the input sources (keyboard layouts)


ON DELETE CASCADE on pg_class in PostgreSQL does not work I added a foreign key on my table which references pg_class.

ALTER TABLE summary_table
ADD FOREIGN KEY (table_oid_column) REFERENCES pg_class (oid) ON DELETE CASCADE;

I have a summary table which I keep some information about tables with their relational ids. When I drop a table I want to delete automatically related row from my summary table, so I added constraint above. But in my case, when I drop a table nothing happens.

I tried it with a test table, when I delete related row from test table, it works as expected. Such as with this foreign key constraint:

ALTER TABLE summary_table
ADD FOREIGN KEY (table_oid_column) REFERENCES test (id) ON DELETE CASCADE;

What can be the problem? Can it be related with being a catalog table(pg_class) or a hidden system column(oid)?

Constraints are only checked when you execute SQL-level DML commands such as DELETE or UPDATE.  They are not checked when you effect a system catalog data change using a DDL command such as DROP TABLE.  Those take a different code path internally, even though they end up modifying the same data.

As a consequence, you currently cannot do what you are trying to do.  In PostgreSQL 9.3, there will be event triggers, which will address the space your problem is in.


What's the difference between a temp table and table variable in SQL Server? This seems to be an area with quite a few myths and conflicting views.

So what is the difference between a table variable and a local temporary table in SQL Server?

Contents

Caveat
This answer discusses "classic" table variables introduced in SQL Server 2000. SQL Server 2014 in memory OLTP introduces Memory-Optimized Table Types. Table variable instances of those are different in many respects to the ones discussed below! (more details).
Storage Location
No difference. Both are stored in tempdb.
I've seen it suggested that for table variables this is not always the case but this can be verified from the below
DECLARE @T TABLE(X INT)

INSERT INTO @T VALUES(1),(2)

SELECT sys.fn_PhysLocFormatter(%%physloc%%) AS [File:Page:Slot]
FROM @T

Example Results (showing location in tempdb the 2 rows are stored)
File:Page:Slot
----------------
(1:148:0)
(1:148:1)

Logical Location
@table_variables behave more as though they were part of the current database than #temp tables do. For table variables (since 2005) column collations if not specified explicitly will be that of the current database whereas for #temp tables it will use the default collation of tempdb (More details). Additionally User-defined data types and XML collections must be in tempdb to use for #temp tables but table variables can use them from the current database (Source).
SQL Server 2012 introduces contained databases. the behavior of temporary tables in these differs (h/t Aaron)

In a contained database temporary table data is collated in the collation of the contained database.

All metadata associated with temporary tables (for example, table and column names, indexes, and so on) will be in the catalog collation.
Named constraints may not be used in temporary tables.
Temporary tables may not refer to user-defined types, XML schema collections, or user-defined functions.

Visibility to different scopes
@table_variables can only be accessed within the batch and scope in which they are declared. #temp_tables are accessible within child batches (nested triggers, procedure, exec calls). #temp_tables created at the outer scope (@@NESTLEVEL=0) can span batches too as they persist until the session ends. Neither type of object can be created in a child batch and accessed in the calling scope however as discussed next (global ##temp tables can be though).
Lifetime
@table_variables are created implicitly when a batch containing a DECLARE @.. TABLE statement is executed (before any user code in that batch runs) and are dropped implicitly at the end.
Although the parser will not allow you to try and use the table variable before the DECLARE statement the implicit creation can be seen below.
IF (1 = 0)
BEGIN
DECLARE @T TABLE(X INT)
END

--Works fine
SELECT *
FROM @T

#temp_tables are created explicitly when the TSQL CREATE TABLE statement is encountered and can be dropped explicitly with DROP TABLE or will be dropped implicitly when the batch ends (if created in a child batch with @@NESTLEVEL > 0) or when the session ends otherwise.
NB: Within stored routines both types of object can be cached rather than repeatedly creating and dropping new tables. There are restrictions on when this caching can occur however that are possible to violate for #temp_tables but which the restrictions on @table_variables prevent anyway. The maintenance overhead for cached #temp tables is slightly greater than for table variables as illustrated here.
Object Metadata
This is essentially the same for both types of object. It is stored in the system base tables in tempdb. It is more straightforward to see for a #temp table however as  OBJECT_ID('tempdb..#T') can be used to key into the system tables and the internally generated name is more closely correlated with the name defined in the CREATE TABLE statement. For table variables the object_id function does not work and the internal name is entirely system generated with no relationship to the variable name. The below demonstrates the metadata is still there however by keying in on a (hopefully unique) column name. For tables without unique column names the object_id can be determined using DBCC PAGE as long as they are not empty.
/*Declare a table variable with some unusual options.*/
DECLARE @T TABLE
(
[dba.se] INT IDENTITY PRIMARY KEY NONCLUSTERED,
A INT CHECK (A > 0),
B INT DEFAULT 1,
InRowFiller char(1000) DEFAULT REPLICATE('A',1000),
OffRowFiller varchar(8000) DEFAULT REPLICATE('B',8000),
LOBFiller varchar(max) DEFAULT REPLICATE(cast('C' as varchar(max)),10000),
UNIQUE CLUSTERED (A,B)
    WITH (FILLFACTOR = 80,
         IGNORE_DUP_KEY = ON,
         DATA_COMPRESSION = PAGE,
         ALLOW_ROW_LOCKS=ON,
         ALLOW_PAGE_LOCKS=ON)
)

INSERT INTO @T (A)
VALUES (1),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13)

SELECT t.object_id,
       t.name,
       p.rows,
       a.type_desc,
       a.total_pages,
       a.used_pages,
       a.data_pages,
       p.data_compression_desc
FROM   tempdb.sys.partitions AS p
       INNER JOIN tempdb.sys.system_internals_allocation_units AS a
         ON p.hobt_id = a.container_id
       INNER JOIN tempdb.sys.tables AS t
         ON t.object_id = p.object_id
       INNER JOIN tempdb.sys.columns AS c
         ON c.object_id = p.object_id
WHERE  c.name = 'dba.se'

Output
Duplicate key was ignored.

object_id
name
rows
type_desc
total_pages
used_pages
data_pages
data_compression_desc

574625090
#22401542
13
IN_ROW_DATA
2
2
1
PAGE

574625090
#22401542
13
LOB_DATA
24
19
0
PAGE

574625090
#22401542
13
ROW_OVERFLOW_DATA
16
14
0
PAGE

574625090
#22401542
13
IN_ROW_DATA
2
2
1
NONE

Transactions
Operations on @table_variables are carried out as system transactions, independent of any outer user transaction, whereas the equivalent #temp table operations would be carried out as part of the user transaction itself. For this reason a ROLLBACK command will affect a #temp table but leave the @table_variable untouched.
DECLARE @T TABLE(X INT)
CREATE TABLE #T(X INT)

BEGIN TRAN

INSERT #T
OUTPUT INSERTED.X INTO @T
VALUES(1),(2),(3)

/*Both have 3 rows*/
SELECT * FROM #T
SELECT * FROM @T

ROLLBACK

/*Only table variable now has rows*/
SELECT * FROM #T
SELECT * FROM @T
DROP TABLE #T

Logging
Both generate log records to the tempdb transaction log. A common misconception is that this is not the case for table variables so a script demonstrating this is below, it declares a table variable, adds a couple of rows then updates them and deletes them.
Because the table variable is created and dropped implicitly at the start and the end of the batch it is necessary to use multiple batches in order to see the full logging.
USE tempdb;

/*
Don't run this on a busy server.
Ideally should be no concurrent activity at all
*/
CHECKPOINT;

GO

/*
The 2nd column is binary to allow easier correlation with log output shown later*/
DECLARE @T TABLE ([C71ACF0B-47E9-4CAD-9A1E-0C687A8F9CF3] INT, B BINARY(10))

INSERT INTO @T
VALUES (1, 0x41414141414141414141),
       (2, 0x41414141414141414141)

UPDATE @T
SET    B = 0x42424242424242424242

DELETE FROM @T

/*Put allocation_unit_id into CONTEXT_INFO to access in next batch*/
DECLARE @allocId BIGINT, @Context_Info VARBINARY(128)

SELECT @Context_Info = allocation_unit_id,
       @allocId = a.allocation_unit_id
FROM   sys.system_internals_allocation_units a
       INNER JOIN sys.partitions p
         ON p.hobt_id = a.container_id
       INNER JOIN sys.columns c
         ON c.object_id = p.object_id
WHERE  ( c.name = 'C71ACF0B-47E9-4CAD-9A1E-0C687A8F9CF3' )

SET CONTEXT_INFO @Context_Info

/*Check log for records related to modifications of table variable itself*/
SELECT Operation,
       Context,
       AllocUnitName,
       [RowLog Contents 0],
       [Log Record Length]
FROM   fn_dblog(NULL, NULL)
WHERE  AllocUnitId = @allocId

GO

/*Check total log usage including updates against system tables*/
DECLARE @allocId BIGINT = CAST(CONTEXT_INFO() AS BINARY(8));

WITH T
     AS (SELECT Operation,
                Context,
                CASE
                  WHEN AllocUnitId = @allocId THEN 'Table Variable'
                  WHEN AllocUnitName LIKE 'sys.%' THEN 'System Base Table'
                  ELSE AllocUnitName
                END AS AllocUnitName,
                [Log Record Length]
         FROM   fn_dblog(NULL, NULL) AS D)
SELECT Operation = CASE
                     WHEN GROUPING(Operation) = 1 THEN 'Total'
                     ELSE Operation
                   END,
       Context,
       AllocUnitName,
       [Size in Bytes] = COALESCE(SUM([Log Record Length]), 0),
       Cnt = COUNT(*)
FROM   T
GROUP  BY GROUPING SETS( ( Operation, Context, AllocUnitName ), ( ) )
ORDER  BY GROUPING(Operation),
          AllocUnitName

Returns
###Detailed view

###Summary View (includes logging for implicit drop and system base tables)

As far as I've been able to discern operations on both generate roughly equal amounts of logging.
Whilst the quantity of logging is very similar one important difference is that log records related to #temp tables can not be cleared out until any containing user transaction finishes so a long running transaction that at some point writes to #temp tables will prevent log truncation in tempdb whereas the autonomous transactions spawned for table variables do not.
Table variables do not support TRUNCATE so can be at a logging disadvantage when the requirement is to remove all rows from a table (though for very small tables DELETE can work out better anyway)
Cardinality
Many of the execution plans involving table variables will show a single row estimated as the output from them. Inspecting the table variable properties shows that SQL Server believes the table variable has zero rows (Why it estimates 1 row will be emitted from a zero row table is explained by @Paul White here).
However the results shown in the previous section do show an accurate rows count in sys.partitions. The issue is that on most occasions the statements referencing table variables are compiled while the table is empty. If the statement is (re)compiled after @table_variable is populated then this will be used for the table cardinality instead (This might happen due to an explicit recompile or perhaps because the statement also references another object that causes a deferred compile or a recompile.)
DECLARE @T TABLE(I INT);

INSERT INTO @T VALUES(1),(2),(3),(4),(5)

CREATE TABLE #T(I INT)

/*Reference to #T means this statement is subject to deferred compile*/
SELECT * FROM @T WHERE NOT EXISTS(SELECT * FROM #T)

DROP TABLE #T

Plan shows accurate estimated row count following deferred compile.

In SQL Server 2012 SP2, trace flag 2453 is introduced. More details are under "Relational Engine" here.
When this trace flag is enabled it can cause automatic recompiles to take account of changed cardinality as discussed further very shortly.
NB: On Azure in compatibility level 150 compilation of the statement is now deferred until first execution. This means that it will no longer be subject to the zero row estimate problem.
No column statistics
Having a more accurate table cardinality doesn't mean the estimated row count will be any more accurate however (unless doing an operation on all rows in the table). SQL Server does not maintain column statistics for table variables at all so will fall back on guesses based upon the comparison predicate (e.g. that 10% of the table will be returned for an = against a non unique column or 30% for a > comparison). In contrast column statistics are maintained for #temp tables.
SQL Server maintains a count of the number of modifications made to each column. If the number of modifications since the plan was compiled exceeds the recompilation threshold (RT) then the plan will be recompiled and statistics updated. The RT depends on table type and size.
From Plan Caching in SQL Server 2008

RT is calculated as follows. (n refers to a table's cardinality when a query plan is compiled.)

Permanent table

If n <= 500, RT = 500.
If n > 500, RT = 500 + 0.20 * n.

Temporary table

If n < 6, RT = 6.
If 6 <= n <= 500, RT = 500.
If n > 500, RT = 500 + 0.20 * n.
Table variable
RT does not exist. Therefore, recompilations do not happen because of changes in  cardinalities of table variables.
(But see note about TF 2453 below)

the KEEP PLAN hint can be used to set the RT for #temp tables the same as for permanent tables.
The net effect of all this is that often the execution plans generated for #temp tables are orders of magnitudes better than for @table_variables when many rows are involved as SQL Server has better information to work with.
NB1: Table variables do not have statistics but can still incur a "Statistics Changed" recompile event under trace flag 2453 (does not apply for "trivial" plans) This appears to occur under the same recompile thresholds as shown for temp tables above with an additional one that if N=0 -> RT = 1. i.e. all statements compiled when the table variable is empty will end up getting a recompile and corrected TableCardinality the first time they are executed when non empty. The compile time table cardinality is stored in the plan and if the statement is executed again with the same cardinality (either due to flow of control statements or reuse of a cached plan) no recompile occurs.
NB2: For cached temporary tables in stored procedures the recompilation story is much more complicated than described above. See Temporary Tables in Stored Procedures for all the gory details.
Recompiles
As well as the modification based recompiles described above #temp tables can also be associated with additional compiles simply because they allow operations that are prohibited for table variables that trigger a compile (e.g. DDL changes CREATE INDEX, ALTER TABLE)
Locking
It has been stated that table variables do not participate in locking. This is not the case. Running the below outputs to the SSMS messages tab the details of locks taken and released for an insert statement.
DECLARE @tv_target TABLE (c11 int, c22 char(100))

DBCC TRACEON(1200,-1,3604)

INSERT INTO @tv_target (c11, c22)

VALUES (1, REPLICATE('A',100)), (2, REPLICATE('A',100))

DBCC TRACEOFF(1200,-1,3604)

For queries that SELECT from table variables Paul White points out in the comments that these automatically come with an implicit NOLOCK hint. This is shown below
DECLARE @T TABLE(X INT);

SELECT X
FROM @T
OPTION (RECOMPILE, QUERYTRACEON 3604, QUERYTRACEON 8607)

###Output
*** Output Tree: (trivial plan) ***

        PhyOp_TableScan TBL: @T Bmk ( Bmk1000) IsRow: COL: IsBaseRow1002  Hints( NOLOCK )

The impact of this on locking might be quite minor however.
SET NOCOUNT ON;

CREATE TABLE #T( [ID] [int] IDENTITY NOT NULL,
                 [Filler] [char](8000) NULL,
                 PRIMARY KEY CLUSTERED ([ID] DESC))

DECLARE @T TABLE ( [ID] [int] IDENTITY NOT NULL,
                 [Filler] [char](8000) NULL,
                 PRIMARY KEY CLUSTERED ([ID] DESC))

DECLARE @I INT = 0

WHILE (@I < 10000)
BEGIN
INSERT INTO #T DEFAULT VALUES
INSERT INTO @T DEFAULT VALUES
SET @I += 1
END

/*Run once so compilation output doesn't appear in lock output*/
EXEC('SELECT *, sys.fn_PhysLocFormatter(%%physloc%%) FROM #T')

DBCC TRACEON(1200,3604,-1)
SELECT *, sys.fn_PhysLocFormatter(%%physloc%%)
FROM @T

PRINT '--*--'

EXEC('SELECT *, sys.fn_PhysLocFormatter(%%physloc%%) FROM #T')

DBCC TRACEOFF(1200,3604,-1)

DROP TABLE #T

Neither of these return results in index key order indicating that SQL Server used an allocation ordered scan for both.
I ran the above script twice and the results for the second run are below
Process 58 acquiring Sch-S lock on OBJECT: 2:-1325894110:0  (class bit0 ref1) result: OK

--*--
Process 58 acquiring IS lock on OBJECT: 2:-1293893996:0  (class bit0 ref1) result: OK

Process 58 acquiring S lock on OBJECT: 2:-1293893996:0  (class bit0 ref1) result: OK

Process 58 releasing lock on OBJECT: 2:-1293893996:0

The locking output for the table variable is indeed extremely minimal as SQL Server just acquires a schema stability lock on the object. But for a #temp table it is nearly as light in that it takes out an object level S lock. A NOLOCK hint or READ UNCOMMITTED isolation level can of course be specified explicitly when working with #temp tables as well.
Similarly to the issue with logging a surrounding user transaction can mean that the locks are held longer for #temp tables. With the script below
    --BEGIN TRAN;

    CREATE TABLE #T (X INT,Y CHAR(4000) NULL);

    INSERT INTO #T (X) VALUES(1)

    SELECT CASE resource_type
             WHEN  'OBJECT' THEN OBJECT_NAME(resource_associated_entity_id, 2)
             WHEN  'ALLOCATION_UNIT' THEN (SELECT OBJECT_NAME(object_id, 2)
                                           FROM  tempdb.sys.allocation_units a
                                           JOIN tempdb.sys.partitions p ON a.container_id = p.hobt_id
                                           WHERE  a.allocation_unit_id = resource_associated_entity_id)
             WHEN 'DATABASE' THEN DB_NAME(resource_database_id)
             ELSE (SELECT OBJECT_NAME(object_id, 2)
                   FROM   tempdb.sys.partitions
                   WHERE  partition_id = resource_associated_entity_id)
           END AS object_name,
           *
    FROM   sys.dm_tran_locks
    WHERE  request_session_id = @@SPID

    DROP TABLE #T

   -- ROLLBACK

when run outside of an explicit user transaction for both cases the only lock returned when checking sys.dm_tran_locks is a shared lock on the DATABASE.
On uncommenting the BEGIN TRAN ... ROLLBACK 26 rows are returned showing that locks are held both on the object itself and on system table rows to allow for rollback and prevent other transactions from reading uncommitted data. The equivalent table variable operation is not subject to rollback with the user transaction and has no need to hold these locks for us to check in the next statement but tracing locks acquired and released in Profiler or using trace flag 1200 shows plenty of locking events do still occur.
Indexes
For versions prior to SQL Server 2014 indexes can only be created implicitly on table variables as a side effect of adding a unique constraint or primary key. This does of course mean that only unique indexes are supported. A non unique non clustered index on a table with a unique clustered index can be simulated however by simply declaring it UNIQUE NONCLUSTERED and adding the CI key to the end of the desired NCI key (SQL Server would do this behind the scenes anyway even if a non unique NCI could be specified)
As demonstrated earlier various index_options can be specified in the constraint declaration including DATA_COMPRESSION, IGNORE_DUP_KEY, and FILLFACTOR (though there is no point in setting that one as it would only make any difference on index rebuild and you can't rebuild indexes on table variables!)
Additionally table variables do not support INCLUDEd columns, filtered indexes (until 2016) or partitioning, #temp tables do (the partition scheme must be created in tempdb).
Indexes in SQL Server 2014
Non unique indexes can be declared inline in the table variable definition in SQL Server 2014. Example syntax for this is below.
DECLARE @T TABLE (
C1 INT INDEX IX1 CLUSTERED, /*Single column indexes can be declared next to the column*/
C2 INT INDEX IX2 NONCLUSTERED,
       INDEX IX3 NONCLUSTERED(C1,C2) /*Example composite index*/
);

Indexes in SQL Server 2016
From CTP 3.1 it is now possible to declare filtered indexes for table variables. By RTM it may be the case that included columns are also allowed albeit they will likely not make it into SQL16 due to resource constraints
DECLARE @T TABLE
(
c1 INT NULL INDEX ix UNIQUE WHERE c1 IS NOT NULL /*Unique ignoring nulls*/
)

Parallelism
Queries that insert into (or otherwise modify) @table_variables cannot have a parallel plan, #temp_tables are not restricted in this manner.
There is an apparent workaround in that rewriting as follows does allow the SELECT part to take place in parallel but that ends up using a hidden temporary table (behind the scenes)
INSERT INTO @DATA ( ... )
EXEC('SELECT .. FROM ...')

There is no such limitation in queries that select from table variables as illustrated in my answer here
Other Functional Differences

#temp_tables cannot be used inside a function. @table_variables can be used inside scalar or multi-statement table UDFs.
@table_variables cannot have named constraints.
@table_variables cannot be SELECT-ed INTO, ALTER-ed, TRUNCATEd or be the target of DBCC commands such as DBCC CHECKIDENT or of SET IDENTITY INSERT and do not support table hints such as WITH (FORCESCAN)
CHECK constraints on table variables are not considered by the optimizer for simplification, implied predicates or contradiction detection.
Table variables don't seem to qualify for the rowset sharing optimisation meaning that delete and update plans against these can encounter more overhead and PAGELATCH_EX waits. (Example)

Memory Only?
As stated at the beginning both get stored on pages in tempdb. However I didn't address whether there was any difference in behaviour when it comes to writing these pages to disc.
I've done a small amount of testing on this now and so far have seen no such difference. In the specific test I did on my instance of SQL Server 250 pages seems to be the cut off point before the data file gets written to.

NB: The behavior below no longer occurs in SQL Server 2014 or SQL
Server 2012 SP1/CU10 or SP2/CU1 the eager writer is no longer as
eager to write pages to disc. More details on that change at SQL
Server 2014: tempdb Hidden Performance Gem.

Running the below script
CREATE TABLE #T(X INT, Filler char(8000) NULL)
INSERT INTO #T(X)
SELECT TOP 250 ROW_NUMBER() OVER (ORDER BY @@SPID)
FROM master..spt_values
DROP TABLE #T

And monitoring writes to the tempdb data file with Process Monitor I saw none (except occasionally ones to the database boot page at offset 73,728). After changing 250 to 251I began to see writes as below.

The screenshot above shows 5 * 32 page writes and one single page write indicating that 161 of the pages were written to disc. I got the same cut off point of 250 pages when testing with table variables too. The script below shows it a different way by looking at sys.dm_os_buffer_descriptors
DECLARE @T TABLE (
  X        INT,
  [dba.se] CHAR(8000) NULL)

INSERT INTO @T
            (X)
SELECT TOP 251 Row_number() OVER (ORDER BY (SELECT 0))
FROM   master..spt_values

SELECT is_modified,
       Count(*) AS page_count
FROM   sys.dm_os_buffer_descriptors
WHERE  database_id = 2
       AND allocation_unit_id = (SELECT a.allocation_unit_id
                                 FROM   tempdb.sys.partitions AS p
                               INNER JOIN tempdb.sys.system_internals_allocation_units AS a
                                          ON p.hobt_id = a.container_id
                                        INNER JOIN tempdb.sys.columns AS c
                                          ON c.object_id = p.object_id
                                 WHERE  c.name = 'dba.se')
GROUP  BY is_modified

###Results
is_modified page_count
----------- -----------
0           192
1           61

Showing that 192 pages were written to disc and the dirty flag cleared. It also shows that being written to disc doesn't mean that pages will be evicted from the buffer pool immediately. The queries against this table variable could still be satisfied entirely from memory.
On an idle server with max server memory set to 2000 MB and DBCC MEMORYSTATUS reporting Buffer Pool Pages Allocated as approx 1,843,000 KB (c. 23,000 pages) I inserted to the tables above in batches of 1,000 rows/pages and for each iteration recorded.
SELECT Count(*)
FROM   sys.dm_os_buffer_descriptors
WHERE  database_id = 2
       AND allocation_unit_id = @allocId
       AND page_type = 'DATA_PAGE'

Both the table variable and the #temp table gave nearly identical graphs and managed to pretty much max out the buffer pool before getting to the point that they weren't entirely held in memory so there doesn't seem to be any particular limitation on how much memory either can consume.


Why is SQL Server spliting it's (JSON) response into multiple rows? I am trying to build a query that results in a JSON object generated by SQL Server. I've found that I can use subqueries to populate a field (in this case the questions field) with a JSON string that that contains a list of questions.
Below is the query:
SELECT
    quizzes.id AS 'id',
    quizzes.name AS 'name',
    quizzes.description AS 'description',
    quizzes.instructions AS 'instructions',
    author.id AS 'author.id',
    author.midas AS 'author.midas',
    author.first_name AS 'author.first_name',
    author.last_name AS 'author.last_name',
    author.email AS 'author.email',
    author.tel AS 'author.tel',
    author.department_name AS 'author.department_name',
    author.created_at AS 'author.created_at',
    author.last_updated AS 'author.last_updated',
    course.id AS 'course.id',
    course.name AS 'course.name',
    course.description AS 'course.description',
    course.crn AS 'course.crn',
    instructor.id AS 'course.instructor.id',
    instructor.midas AS 'course.instructor.midas',
    instructor.first_name AS 'course.instructor.first_name',
    instructor.last_name AS 'course.instructor.last_name',
    instructor.email AS 'course.instructor.email',
    instructor.tel AS 'course.instructor.tel',
    instructor.department_name AS 'course.instructor.department_name',
    instructor.created_at AS 'course.instructor.created_at',
    instructor.last_updated AS 'course.instructor.last_updated',
    course.created_at AS 'course.created_at',
    course.last_updated AS 'course.last_updated',

    (
        SELECT
            questions.id AS 'id',
            questions.text AS 'text',
            question_types.id AS 'type.id',
            question_types.name AS 'type.name',
            question_types.created_at AS 'type.created_at',
            question_types.description AS 'type.description',
            question_author.id AS 'author.id',
            question_author.midas AS 'author.midas',
            question_author.first_name AS 'author.first_name',
            question_author.last_name AS 'author.last_name',
            question_author.email AS 'author.email',
            question_author.tel AS 'author.tel',
            question_author.department_name AS 'author.department_name',
            question_author.created_at AS 'author.created_at',
            question_author.last_updated AS 'author.last_updated',
            questions.is_graded AS 'is_graded',
            questions.score_value AS 'score_value',
            questions.created_at AS 'created_at',
            questions.last_updated AS 'last_updated'
        FROM
            questions
        LEFT JOIN users AS question_author ON question_author.id = questions.author
        LEFT JOIN question_types ON question_types.id = questions.type
        WHERE
            questions.quiz = quizzes.id FOR JSON PATH, INCLUDE_NULL_VALUES
    ) AS 'questions',

    quizzes.created_at AS 'created_at',
    quizzes.last_updated AS 'last_updated'
FROM
    quizzes
    LEFT JOIN users AS author ON quizzes.author = author.id
    LEFT JOIN courses AS course ON quizzes.course = course.id
    LEFT JOIN users AS instructor ON course.instructor = instructor.id FOR JSON PATH,
    INCLUDE_NULL_VALUES;

The problem is: when I execute this query it responds with two rows where the resulting JSON string is split in two. Obviously this is undesirable.
After investigation, I found that if I remove the LEFT JOIN's, then the query responds as it should (only one row with the entire string intact).

Below is an example of the following behavior:
SELECT n = sc1.name FROM sys.syscolumns sc1 FOR JSON AUTO

As can be seen above, 11 rows are being returned.

And the length of the JSON output is about 20,000 characters.

I am using the following version of SQL Server
Microsoft SQL Server 2019 - 15.0.4073.23 (X64)
Developer Edition (64-bit) on Linux (Ubuntu 18.04.5 LTS) <X64>

Why is this happening? How can I fix it?

After further research, I have discovered from this StackOverflow post that SQL Server breaks FOR JSON queries into "~2kb chunks".

Sql Server splits result of FOR JSON query into ~2KB chunk, so you should either concatenate fragments like on the MSDN page or you can stream results into some output stream.

This means that only ~2000 characters can be sent per chunk.

UPDATE:
With the help of Max Vernon and Andriy M we have found a fairly simple solution to this problem.
DECLARE @json nvarchar(max);

;WITH src (n) AS
(
    SELECT n = sc1.name
    FROM sys.syscolumns sc1
    FOR JSON AUTO
)
SELECT @json = src.n
FROM src

SELECT @json, LEN(@json);

The chats that lead up to this can be found here.
The above query returns two columns.

The fully assembled JSON string
The length of that string

Ideally, you would replace the query between the two parentheses with your own query.
Why does this work?
According to Microsoft's Documentation

SQL Server uses a predefined column name for this rowset with one column of type NTEXT - “XML_F52E2B61-18A1-11d1-B105-00805F49916B” – to indicate chunked XML rowset in UTF-16 encoding. This requires special handling of the XML chunk rowset by the APIs to expose it as a single XML instance on the client side. In ADO.Net, one needs to use ExecuteXmlReader, and in ADO/OLEDB one should use the ICommandStream interface.

(While it is true that the above refers to XML, the same is true for JSON as well.)
The reason that JSON and XML responses are returned in chunks, to begin with, are for performance reasons:

For maximum XML [JSON] publishing performance FOR XML [JSON] does steaming XML formatting of the resulting rowset and directly sends its output to the server side TDS code in small chunks without buffering whole XML in the server space. The chunk size is 2033 UCS-2 characters. Thus, XML larger than 2033 UCS-2 characters is sent to the client side in multiple rows each containing a chunk of the XML.

The above solution circumvents this by setting the result of FOR JSON to a variable first, then sending the value of the variable, which results in SQL Server returning the response to the client as one row.
It should be noted that some database clients (specifically SQL Server Management Studio) are capable of "rebuilding" the chunked response, but if you are working with PHP (PDO) or operating on a Free Trial client (such as TablePlus for Mac) you will see the raw, chunked response.

Performance
As far as performance goes, I have not done any extensive testing, but I can provide the following data from the limited testing I have done:
Using a MacBook Pro running the latest OS, I have found that on average the following query
SELECT n = sc1.name
    FROM sys.syscolumns sc1
    FOR JSON AUTO

Will process in about 23,300μs
While the query
DECLARE
    @json nvarchar (max);

;WITH src (n) AS
(
    SELECT n = sc1.name
    FROM sys.syscolumns sc1
    FOR JSON AUTO
)
SELECT @json = src.n
FROM src

SELECT @json, LEN(@json);

Spent on average 137.8μs processing.
This seems to directly conflict with what the documentation says, so I am unsure how trustworthy these results are. However, it may be worth testing this yourself.


How do I backup SQL Server Agent configuration? I've tried googling for this.  The problem is that the all the results are about using SQL Server Agent to schedule automatic backups.

How do I backup the SQL Server Agent configuration itself?  If my database machine fails, I will need to recreate all the SQL Server Agent jobs that are currently defined.  Is all this information stored in the Master database and is a restore of that database all that is required to get the SQl Server Agent jobs back?

They are actually all in the msdb database. However, it's not advisable to simply restored msdb over the existing one. The best solution is to script the jobs out on a weekly baseis. Take a look at this for some example steps on how to backup/restore jobs

http://www.sqlservercentral.com/Forums/Topic749226-146-1.aspx#bm749280


Changing char(10) to char(3) grew table even after cleantable and shrinkfile In a largely experimental attempt to reduce table space usage I altered a char(10) column to char(3)  (The column's contents are never more than three characters long)

Steps (Not carried out in a single batch)

use thedatabase;
alter table thetable
alter column thecolumn char(3) null

DBCC CLEANTABLE (thedatabase,'thetable', 500000);
/* Apparently not needed for fixed length column but just in case*/

DBCC SHRINKFILE (1);

The operation completed successfully, but the table actually grew in size.    I know this can happen due to the way the sql engine modifies a column (creates a copy alongside and then copies the column contents) so I ran dbcc cleantable on the table, and then dbcc shrinkfile on the database file.  None of these operations made any difference to the table size.

The table has no index,  so not possible to run a rebuild index operation.

So my question is,  why did the table grow, even after commands to shrink it.

This has most likely to do with forwarded records, the new char(3) value is copied to a new allocation unit (page) leaving a forwarding pointer in its place. You can check if they exist like this.

SELECT
    OBJECT_NAME(ps.object_id) as TableName,
    i.name as IndexName,
    ps.index_type_desc,
    ps.page_count,
    ps.avg_fragmentation_in_percent,
    ps.forwarded_record_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'DETAILED') AS ps
INNER JOIN sys.indexes AS i
    ON ps.OBJECT_ID = i.OBJECT_ID
    AND ps.index_id = i.index_id
-- WHERE forwarded_record_count > 0
   WHERE ps.object_id = object_id(<TABLENAME>)

The only way to fix this since you are on SQL Server 2005 would be to create a clustered index on the table and then to drop it. SQL 2008 would allows you to do ALTER TABLE <TABLE> REBUILD.

DBCC CLEANTABLE only cleans up variable length columns typed as varchar and nvarchar, see: DBCC CleanTable by Steve Stedman.


Database email to know the history of backups I need to send an email by listing different types of backups, time of backups, summary of these backups, backup file path and name of backup with the help of database email. Can anyone help me with a script for the same.

Thanks in advance.

There are various SQL scripts available online, for eg: one can be found here to retrieve the backup history and then use sp_send_dbmail to send the email for the retrieved backup history as needed by scheduling it via SQL agent job.

However, the method that i have been using is to make use of Powershell to send such reports as described here Use PowerShell to Report SQL Server Backup Status

You can save the above script and execute it manually whenever you need to. However, to get the most out of it, its recommended scheduling it daily by using SQL Server Agent. Doing this will give you early notification of issues and allow you to act quickly to get those databases backed up.


Set variable with dynamic name in trigger (MySQL) I need to create some kind of generic trigger that will alter value of columns which names are stored in another table. So basically I wonder if is possible to do something like this:

delimiter ;;

CREATE TRIGGER my_trigger BEFORE INSERT ON my_table
FOR EACH ROW
BEGIN
    DECLARE columnName VARCHAR(20);
    SET columnName = 'my_column';
    SET NEW.{columnName} = 'my value'; -- pseudo-code
END;;

delimiter ;

Depending on what you are trying to achieve with this, you may be out of luck.

First of all, names cannot be parametrised in MySQL otherwise than using a prepared statement.

– So, can we use a prepared statement here?

No. Prepared statements are not allowed in triggers, according to the manual:

  SQL syntax for prepared statements can be used within stored procedures, but not in stored functions or triggers.

– All right, prepared statements can be used in stored procedures. Can we create a stored procedure that would use a prepared statement to do the job, and call the procedure from the trigger?

Nope. A stored procedure cannot reference NEW and OLD columns – those namespaces are available only in triggers.

The only option appears to be an IF ... ELSEIF ... statement comparing columnName against a fixed set of names and assigning values to corresponding columns.

IF columnName = 'colname1' THEN
   SET NEW.colname1 = '...';
ELSEIF columnName = 'colname2' THEN
   SET NEW.colname2 = '...';
ELSEIF
   ...
END IF;

This method will not work if you are trying to include non-existent names (in order to cover columns that might be added later, for instance) in the checkings. And, of course, this method will just be unsuitable for you if you are trying to stipulate for unknown names.

But then, perhaps, what you are trying to do is not what you should be doing to begin with. Perhaps, you should revisit your schema and consider a different data model (at least for some portion of your data), like EAV, for instance.


Why does this query take 32 sec to execute? I keep my history records in one table and my pertinent data in another. I want to grab only the files from myfiles that currently have a given status recorded in statuses (ie, the latest status is the one I want to filter by). I'm trying a subquery which may be a mistake, but I can't really think of another way to do it because there are many statuses for a given file in myfiles.

MySQL 5.0.96, column schema on both tables involved is fewer than 15.

< 500 records in myfiles, 68k records in statuses:

SELECT myfiles.*,
(SELECT status FROM statuses WHERE statuses.itemtype=2 AND statuses.id=myfiles.id ORDER BY historyid DESC LIMIT 1) AS actualStatus
FROM myfiles
HAVING actualStatus=0

Here's a fiddle with the actual table schemas involved.
Thank you!

I'd actually think about adding a status column to the table tblstockingorders and having a trigger on that table that injects the status into the tblstatushistory.

Granted you'd have to do a 1-time update to all the rows in tblstockingorders (something similar to your query above) and set their last status, but this would give you best overall performance as these two tables grow.


Cube with 2 measure groups (with different relationships to dimensions) returns too many dimension members in reports I have a cube that contains stock-take data from retail operations. It has 2 measure groups - one containing the number of stock units counted in each stock-take batch (which is related to the Product, Batch and Time dimensions), and another containing product prices (which is just related to the Product dimension).

The measures in the prices measure group use the Min and Max operators (i.e. they show the minimum or maximum price across the selected members of the Products dimension).

I seem to have configured the cube and dimensions correctly (the price measures display as expected) ... except in the following case:

If I query the cube in Excel and get Units by Time and Batch, I see the expected result (see image below, filtered on Time=2015, and hiding rows with no data), i.e. just the batches with Units fact data for the selected Time dimension members.

If I now add one of the price measures (without changing any filters), the Units total is unchanged (as expected) but I now see every member in the Batch dimension (see image below). This is a problem for end-users, because all they want is to see the prices for the product in the batches previously selected.

From a technical point of view, it kind of makes sense - the price facts have no relationship to Time, so cannot be filtered by my Time filter the way the Units were.

Is there anything I can do (in the cube design or Excel) to eliminate the Batch members that have no Units for the given filters? Note: I can't get the users to write MDX queries - report building needs to remain "point and click"!

EDIT 27/1 (adding extra screenshot to illustrate strange side-effect of setting Ignore Unrelated Dimensions = False per TomV's answer)

As you correctly noted this is what happens when you try to display measures across a dimension to which they don't have a relation.

You basically have 2 options

Use IgnoreUnrelatedDimensions
Use an MDX solution

I would suggest you try the IgnoreUnRelatedDimensions first, as the measures would be aggregated better, and NonEmptyCrossJoins would be able to benefit from the Bitmap Indexes used when Attribute Relationships are defined.

The MDX solution is more a last resort because of possible performance issues, and you would also need to define a calculated measure for each measure you have. The MDX solution would most likely be either complex or mess up your totals/show incorrect data.

IgnoreUnrelatedDimensions

When looking at the Adventureworks 2012 cube you have this situation where Employees are not linked to the 'Internet sales' but they are linked to the 'Reseller sales'.

When browsing the cube the result is the same as yours when you add measures from both measure groups across the employee dimension.

You can solve this by setting the IgnoreUnrelatedDimensions property to False on the Measure Group.

After reprocessing the cube and refreshing the browser the result now looks like this:

There are a few caveats with this though, such as an issue with setting DefaultMember

If the DefaultMember is not the All level on one of the attributes of the unrelated dimensions you don't get aggregated data since the default member still acts as a filter even if it's not included in your pivot table:

For example after setting the DefaultMemberproperty on the gender attribute of the employee dimension like this.

This is the result when no slicers are included in the report. Of course, you would want to see the total here:

I seem to recall some other edge cases with calculated measures but I don't remember the specific situation.

MDX Solution

If you run into any of the edge cases you would have to resort to an MDX solution such posted by @GregGalloway, but that could end up being tedious and very specific to a single report or use case:

CREATE MEMBER CURRENTCUBE .[Measures].[Max Current CP With Units] AS
  IIf (
    NOT IsEmpty ( [Measures].[Units] ),
    [Measures].[Max Current CP],
    NULL
  ) ;

The drawback of that solution would be that you would be messing up the way totals look and possibly present incorrect data but that might be acceptable in your specific case of price information with MIN/MAX aggregation but would totally break for sums, and will probably show false data to your users.

For example when running this query

WITH MEMBER [Measures].[Filtered internet order count] as
'
IIf (
    NOT IsEmpty ([Measures].[Reseller Order Count] ),
    [Measures].[Internet Order Count],
    NULL)
'
SELECT {[Measures].[Filtered internet order count],[Measures].[Reseller Order Count]} ON 0,
[Employee].[Employees].allmembers ON 1
FROM [Adventure Works]

Produces this result

Which looks equally strange, since the total is the same as each individual member. But that may be acceptable in your specific case of min/max pricing, I think it's mostly showing false data as there are no internet sales for those employees.

I guess if you control the reports it would be possible. For the added pivot table in your edit I guess it would produce OK results.


Executing a stored procedure which selects and inserts into tables in SQL Server I am a bit confused on the permissions required to execute stored procedure. For example, if you have 2 tables and a stored procedure which reads data from Table1 and inserts data into Table2, will execute permission on the stored procedure and select permission on the tables be enough? In theory I am finding that this is given as a solution but practically I am finding that a user would need execute permission on the stored procedure, select permission on the tables and insert permission on Table2.

No, you don't need to grant explicit permission on Table1 and Table2, that's one of the objective of embedding code in stored procedure and that's where encapsulation feature comes into effect.

Please check below link from Microsoft:

Managing Permissions with Stored Procedures in SQL Server

  Stored Procedure Execution

  Stored procedures take advantage of ownership chaining to provide access to data so that users do not need to have explicit permission to access database objects. An ownership chain exists when objects that access each other sequentially are owned by the same user. For example, a stored procedure can call other stored procedures, or a stored procedure can access multiple tables. If all objects in the chain of execution have the same owner, then SQL Server only checks the EXECUTE permission for the caller, not the caller's permissions on other objects. Therefore you need to grant only EXECUTE permissions on stored procedures; you can revoke or deny all permissions on the underlying tables.

Use the code below to grant execute permission:

USE database_name
GO
GRANT EXECUTE ON USP_NAME
    TO User_name;
GO

Hope above helps.


One table to save all records vs create one table each day One vendor need to store in a database multiple records. There are 5 tables (a table per element - elements are: pressure, flow, volume, temperature and distance). Each table have the following columns: ID, Timestamp, Value;
Each day, a table will have approximately 50.000 records.

Database used is SQL Server 2017, on a Windows Server 2016 machine.
There will be a lot of analytics to make (charts, reports, etc.) (daily, weekly, monthly, yearly)

So, the vendor wants to create these 5 tables every day for each day of one year (example: "Table_yyyyMMdd_Pressure") and store into it values from that day only; this approach will get the database to have something like 1825 tables per year.

Our request was to create only 5 tables and store all records there - which will get to have approximately 18 000 000 records in one year per table.

From my point of view, our approach is better taking into account:

queries will be run only on those 5 tables once (per table) instead of running queries multiple times per each table per each day;
easier to create reports on a single table than on multiple tables
easier to change the structure of the tables if will be necessary
less complex to write code/procedure

Can you please give me more pro's and con's regarding these 2 approaches?

Thank you!

Avoiding separate tables by date would be best. Besides the benefits you mentioned, performance will likely be better too.

If each table has a row for the same ID and Timestamp (e.g. a device that reports all 5 readings at the same time), you could have a single table with ID, Timestamp, pressure, flow, volume, temperature, and distance.

You might also consider using columnstore and partitioning by date for maximum performance and manageability (respectively).


What is PROC FREQ equivalent in SQL? I want to use the functionality that PROC FREQ provides in SAS. PROC FREQ helps in giving the statistics such as frequency, percent, cumulative frequency etc. of a variable or variables. The link attached explains the function in more detail. I am aware that I can use PIVOT in SQL Server to achieve somewhat the same objective. However, there are certain feature of PROC FREQ that I cannot find SQL equivalent. To be precise:

table x/norow nocol nocum nopercent missing

https://support.sas.com/documentation/cdl/en/statug/63033/HTML/default/viewer.htm#statug_freq_sect010.htm

This link helped me figure out that table is basically a cross tabulation technique, so how can I use pivot in SQL for this? Next, the missing treats missing values as non-missing. How can I do the same in SQL?

Any thoughts/advice/help much appreciated.

I recreated a SAS PROQ FREQ Missing values example due to lack of more information.

Example:
https://support.sas.com/documentation/cdl/en/statug/63033/HTML/default/viewer.htm#statug_freq_sect016.htm

Sas data set:

   data one;
      input A Freq;
      datalines;
   1 2
   2 2
   . 2
   ;

Sas Query:

   proc freq data=one;
      tables A / missing;
      weight Freq;
      title 'MISSING Option';
   run;

Sas result set

SQL Server Data Set --> Using two tables to number duplicate missing values

DECLARE  @A TABLE (id INT,
      Freq INT);
      DECLARE  @B TABLE (id INT,
      Freq INT,
      rownum int);

      INSERT INTO @A
   VALUES( 1, 2),
   (2,2),
   (NULL, 2),
   (NULL, 2)
   ;
   insert into  @B(rownum,id,Freq)
   select ROW_NUMBER() OVER (ORDER BY  id) , id, freq
   from @A

SQL Server Query: (compatible with SQL Server 2008)

SELECT   B.id,
B.Freq as Frequency,
        (cast(B.Freq as DECIMAL(2,1)) / cast(sum(B2.freq) over() as DECIMAL(2,1)))  * 100 as Percentage ,
        sum(B2.Freq) as cumulative_frequency,
        ( (cast(sum(B2.freq) as DECIMAL(2,1))) / cast(B.Freq as DECIMAL(2,1)) *2) / cast(sum(B2.freq) over() as DECIMAL(2,1))  * 100 as CumulativePercentage
FROM @B B
inner join @B B2 on B.rownum >= B2.rownum
GROUP by B.rownum,B.id,B.Freq,B2.Freq

SQL Server result set:

id      Frequency   Percentage  cumulative_frequency    CumulativePercentage
NULL    2          25.000000    2                       25.000000000
NULL    2          25.000000    4                       50.000000000
1   2              25.000000    6                       75.000000000
2   2              25.000000    8                       100.000000000


Can I add a unique constraint that ignores existing violations? I have a table which currently has duplicate values in a column.

I cannot remove these erroneous duplicates but I would like to prevent additional non-unique values from being added.

Can I create a UNIQUE that doesn't check for existing compliance?

I have tried using NOCHECK but was unsuccessful.

In this case I have a table which ties licensing information to "CompanyName"

EDIT: Having multiple rows with the same "CompanyName" is bad data, but we can't remove or update those duplicates at this time. One approach is to have the INSERTs use a stored procedure which will fail for duplicates... If it was possible to have SQL check the uniqueness on its own, that would be preferable.

This data is queried by company name. For the few existing duplicates this will mean that multiple rows are returned and displayed... While this is wrong, it's acceptable in our use case. The goal is to prevent it in the future. It seems to me from the comments that I have to do this logic in the stored procedures.

The answer is "yes".  You can do this with a filtered index (see here for documentation).

For instance, you can do:

create unique index t_col on t(col) where id > 1000;

This creates a unique index, only on new rows, rather than on the old rows.  This particular formulation would allow duplicates with existing values.

If you have just a handful of duplicates, you could do something like:

create unique index t_col on t(col) where id not in (<list of ids for duplicate values here>);


Truncate parent table in Oracle when child table is empty Suppose I have a parent table parent referenced by a child table child. The table parent is populated but child is not. Attempting to truncate parent results in

 ORA-02449: unique/primary keys in table referenced by foreign keys

Is there a way to hint to the DBMS that child is empty, so that the foreign key constraint doesn't need to be disabled?

No, there is no such hint.
What you can do is

alter table {child_table} disable constraint {fk_constraint_name};
truncate table {parent_table};
alter table {child_table} enable constraint {fk_constraint_name};


SQL Server 2008 R2 sys.dm_exec_sql_text question If i run the following query on our production server:

SELECT DISTINCT TOP 10
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time / 100000 AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / s.execution_count, 0) / 100000 AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / DATEDIFF(s, s.creation_time, GETDATE()), 0) AS FrequencyPerSec
,s.plan_handle, s.plan_generation_num
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
ORDER BY
s.max_elapsed_time / 100000 DESC

My top queries are "create procedure ......." does this mean the sql server recompiles this stored procedure more then once?

No, regarding your query that means, that this procedure takes much time than others

And

Periodically sql server recompiles procedures or its inner statements, it is an intended behavior depending from server activity, memory pressure, user commands, RECOMPILE option, amount of underlying data changes and many more.

Update:

The queries is started from CREATE PROCEDURE because sql server presents the SP's body in such a way.

You may even drill down into procedure's inner statements by this query:

Select
    s3.name as [Obj Name],
    s3.type as [Obj Type],
    (select top 1 substring(text,(s1.statement_start_offset+2)/2,
    (CASE when s1.statement_end_offset = -1 then len(convert(nvarchar(max),text))*2
    else s1.statement_end_offset end - s1.statement_start_offset) /2 ) FROM sys.dm_exec_sql_text(s1.sql_handle)) as [SQL Statement],
    execution_count,
    plan_generation_num,
    last_execution_time,
    ((total_worker_time+0.0)/execution_count)/1000 as [avg_worker_time],
    total_worker_time/1000.0 total_worker_time,
    last_worker_time/1000.0 last_worker_time,
    min_worker_time/1000.0 min_worker_time,
    max_worker_time/1000.0 max_worker_time,
    ((total_logical_reads+0.0)/execution_count) as [avg_logical_reads],
    total_logical_reads+0.0 total_logical_reads,
    last_logical_reads+0.0 last_logical_reads,
    min_logical_reads+0.0 min_logical_reads,
    max_logical_reads+0.0 max_logical_reads,
    ((total_logical_writes+0.0)/execution_count) as [avg_logical_writes],
    total_logical_writes+0.0 total_logical_writes,
    last_logical_writes+0.0 last_logical_writes,
    min_logical_writes+0.0 min_logical_writes,
    max_logical_writes+0.0 max_logical_writes,
    ((total_logical_writes+0.0)/execution_count + (total_logical_reads+0.0)/execution_count) as [avg_logical_IO],
    total_logical_writes + total_logical_reads+0.0 total_logical_IO,
    last_logical_writes +last_logical_reads+0.0 last_logical_IO,
    min_logical_writes +min_logical_reads+0.0 min_logical_IO,
    max_logical_writes + max_logical_reads+0.0 max_logical_IO
from sys.dm_exec_query_stats s1
cross apply sys.dm_exec_sql_text(sql_handle) as s2
join sys.objects s3 on ( s2.objectid = s3.object_id )
left join sys.schemas sch on(s3.schema_id = sch.schema_id)
where s2.dbid = db_id()
order by s3.name, s1.sql_handle