Constraint on existing table fails I want to add the below constraint to an existing table:

ALTER TABLE ONLY daily_summary_202004
  ADD CONSTRAINT odo_no_overlapping_202004
  EXCLUDE USING gist (((vehicle_gid)::text) WITH =, numrange(odo_start, odo_end) WITH &&)
WHERE (start_date >= '2020-04-01'
   AND start_date < ' 2020-05-01'
   AND active) ;

But there are few entries in the table that fail the constraint in production.

Is there an SQL query that I can write to get all the faulty rows so I can delete them before I add the constraint?

The DDL for the table

create table if not exists ifta.daily_summary_202004
(
    id                 uuid           default uuid_generate_v4() not null primary key,
    distance           numeric(10, 3) default 0                  not null,
    fuel_code          varchar(128)                              not null,
    state              varchar                                   not null,
    odo_start          numeric(10, 3)                            not null,
    odo_end            numeric(10, 3),
    start_date         timestamp                                 not null,
    end_date           timestamp,
    vehicle_gid        uuid
);

Essentially, there should not be overlapping odo_start and odo_end entries, hence the constraint. But because of an existing bug, we've some overlapping entries, I want to find them and get rid of them.

I think that the previously given query is closed to what you need:

select *
from daily_summary_202004 ds1
WHERE ds1.start_date >= date '2020-04-01'
  AND ds1.start_date < date '2020-05-01'
  AND ds1.active
  AND exists (select *
                  from daily_summary_202004 ds2
                  where ds1.vehicle_gid = ds2.vehicle_gid
                    and numrange(ds1.odo_start, ds1.odo_end) &&
                        numrange(ds2.odo_start, ds2.odo_end)
                    and ds1.id <> ds2.id
                    );

It selects the rows that cannot exist in the table with the given constraint. However it cannot give the one(s) that should be rejected: it gives only the candidates. You need to choose.

If I simulate with some data. I get:

select * from daily_summary_202004 ;
 id | vehicle_gid | odo_start | odo_end |     start_date      | active
----+-------------+-----------+---------+---------------------+--------
  3 |          10 |        10 |      20 | 2020-04-15 00:00:00 | t
  2 |          10 |         3 |       4 | 2020-04-15 00:00:00 | t
  1 |          10 |         2 |       6 | 2020-04-15 00:00:00 | t
(3 rows)

---
select *
from daily_summary_202004 ds1
WHERE ds1.start_date >= date '2020-04-01'
  AND ds1.start_date < date '2020-05-01'
  AND ds1.active
  AND exists (select *
                  from daily_summary_202004 ds2
                  where ds1.vehicle_gid = ds2.vehicle_gid
                    and numrange(ds1.odo_start, ds1.odo_end) && numrange(ds2.odo_start, ds2.odo_end)
                    and ds1.id <> ds2.id
                    );
 id | vehicle_gid | odo_start | odo_end |     start_date      | active
----+-------------+-----------+---------+---------------------+--------
  2 |          10 |         3 |       4 | 2020-04-15 00:00:00 | t
  1 |          10 |         2 |       6 | 2020-04-15 00:00:00 | t
(2 rows)

alter table only daily_summary_202004
add constraint odo_no_overlapping
exclude using gist
   (
   ((vehicle_gid)::text) WITH =,
     numrange(odo_start, odo_end) WITH &&)
     WHERE (start_date >= '2020-04-01'
      AND start_date < ' 2020-05-01'
    AND active
) ;
psql:tc.sql:39: ERROR:  could not create exclusion constraint "odo_no_overlapping"
DETAIL:  Key ((vehicle_gid::text), numrange(odo_start::numeric, odo_end::numeric))=(10, [3,4)) conflicts with key ((vehicle_gid::text), numrange(odo_start::numeric, odo_end::numeric))=(10, [2,6)).

Here PostgreSQL reports the involved rows but you also have to choose which one to keep and which one to reject.


Why does Informatica hang and say "Not Responding" when connecting to ODBC Data Source Informatica just hangs when trying to open this dialog.  The ODBC connection exists in Control Panel > Administrative Tools > ODBC Data Sources.

Source Analyzer > Sources (menu) > Import from Database

There are technically two windows for ODBC Data Sources in Windows.  The 64 bit window pops up when navigating to Control Panel > Administrative Tools > ODBC.  That connection for NETEZZA_I2B2_ETL worked.

However, the 32 bit dialog also had this data source.  But the connection failed there.  So after I fixed the connection, Informatica worked, and it created the source table.  Informatica must use the 32 bit ODBC connections, not the 64 bit ODBC connections.  "SysWOW64" means 32 bit.  Microsoft decided to keep the old folder names in Windows (System32 for example) and make them 64 bit folders in Windows 7.

C:\Windows\SysWOW64\odbcad32.exe

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI

See this:

https://superuser.com/questions/713258/how-to-remove-odbc-data-source-if-driver-is-removed


Can I index all columns of a table in MySQL safely? I have one table with 4 columns. One is primary key and other three columns are references to columns of other tables. I am using join on these four tables for a search filter. These joins are taking time.

So I was thinking to have indexes on columns because I read that I should create indexes on columns of join conditions. My question is will there be problem if all columns of the table are indexed? Is there any other way to decrease time complexity of these search filters.

More Hint:

Table1(main search)-1000 million entries primary_key fk1 fk2 fk3
Table2-800 million  entries pk1 ..(8-9 columns)
Table3-700 million entries pk2 ..(10-12 columns)
Table2-850 million entries pk3 ..(7-8 columns)

I am using MyISAM engine.

Indexing all columns (index on each column) may or may not improve overall performance. That depends index selectivity. Consider creating covering index for your typical query. For instance, if you usually join all 3 tables, the query will mostly benefit on composite index (f1,f2,f3) . Also, if combination of these 3 fields is unique, you can define primary key on f1,f2,f3, so you don't need surrogate PK (assuming there are no other fields on this table)


Disable log ERROR: duplicate key value violates unique constraint Running Postgresql in a better ask for forgiveness than permission mode during the inserts.

Works great, only problem: The logs are flooded with the following message

duplicate key value violates unique constraint

Is there a possibility to explictly disable this log?

You could use

INSERT ... ON CONFLICT DO NOTHING

and check if a row was inserted or not.


Is it a good idea to have an index when the column value changes several times over record lifetime? I have a table where one of the columns represents an object state - let it be State of type int. The State value starts with 1 and changes to 7 with values in between. Over a row lifetime there're maybe three to seven such changes. Meanwhile I have thousands of queries that SELECT or UPDATE rows with specific State values. One rarely run UPDATE changes all rows that have 4 to 5.

I feel like adding an index on State column would be a good idea. Yet since State can change it means the index will have to be changed on every such change.

Do I add an index or not? How do I make a decision?

If the query against this column is rare, then will you really gain from it? Depending on cardinality, the index might not really help anyway, or even be chosen to satisfy the query. And remember that it has to be maintained 100% of the time.


best practice either create multiple databases or multiple users I have several application which currently running on my local. I have installed oracle 11g and create a global database name as oracle11. Now should i create again different database for each application using Database configuration assistance or should i create different users for each application under that global database? What would be the best practice? If i create different users its also behave same as creating different databases right?

What if in a production environment where server house for couple of application? Do i need to do the same as my local by creating one database and different users for each application?

Databases use server resources: CPU, RAM, disk I/O.  Unless your server is physically partitioned (e.g. Solaris Zones) or you are using VMs it is a really bad idea to host multiple databases on the same server.  You are just creating an environment where contention will be rife and tuning will be even trickier than normal.

Oracle RDBMS has always been designed to host multiple schemas in the one database, and for different applications that is the way to go.  This is one of the main differences between Oracle and (say) MS SQL Server or MySQL.


Postgres requires commit or rollback after exception Just trying to understand the thinking here...

This bug explains that with other databases, you are allowed to run additional commands after a SQLException occurs. It was written in 2001 but has never been touched.  We're working with Postgres and SQL Server, and so now we're bumping into this issue.

Suppose you have:

start transaction;
create table test(id int primary key);
insert into test values (1);
commit;

-- Following statement throws a SQLException(duplicate key) in
-- PG, SS and ORacle
insert into test values (1);

-- Following statement behaves differently for different DBMS:
-- SS and OR: No error...statement runs fine
-- PG: Another SQLException thrown...must rollback or commit
insert into test values (99);

Can anyone help me understand why Postgres disallows this?  Or even better, is there a connection flag to change the behavior?  (I looked and could not find anything.)

I added the "start transaction" bit above to indicate that it was not an auto-commit tx.

This is a Java app, so we're using the PG 9.0 JDBC driver. I have tested the above through a query tool using the same driver to take our application out of the picture.  The second insert statement above yields:

  ERROR: current transaction is aborted, commands ignored until end of transaction block
  [SQL State: 25P02]

The problem I have with your example is that you're talking about JDBC behaviour, but also using explicit "start transaction" etc commands, which seems a bit of a clash, since I'd expect you'd use JDBC's auto-commit mode to manage transactions.

If you are in auto-commit mode, then the two inserts will each be in their own transaction, and the throw of a SQLException for the first one will not affect the second.

If you are not in auto-commit mode, then an implicit "start transaction" is generated before the first insert, and the second insert cannot be processed until the transaction is rolled back. This behaviour is quite different from if you were executing the script with psql.

(JDBC does not specify whether drivers/connections should default to auto-commit on or off, you should always explicitly set it)

Postgresql treats any error processing a statement as immediately aborting the transaction-- essentially like the XACT_ABORT mode in SQL Server. The intent being that if you submit a sequence of commands as a transaction, each one is dependent on the previous ones, so the failure of any one invalidates all the subsequent ones.

If this isn't the behaviour you want inside a transaction, you need to surround the potentially-aborting updates with creating a savepoint, and rolling back to that savepoint in case of an error.

Beware of looking at very old discussions of behaviour (bugs over ten years old definitely count), as at some point in Postgresql's history, there was a session variable called autocommit, and the behaviour could have been quite different. That variable is gone now, replaced (as I understand it) with the concepts of the database or the JDBC driver automatically wrapping commands inside transactions (so in fact there is not really any such thing as non-transactional interaction with postgresql).

Here is what happens when you execute the script you suggest with psql:

steve@steve@[local] =# start transaction;
START TRANSACTION
steve@steve@[local] *=# create table test(id int primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
CREATE TABLE
steve@steve@[local] *=# insert into test values (1);
INSERT 0 1
steve@steve@[local] *=# commit;
COMMIT
steve@steve@[local] =#
steve@steve@[local] =# -- Following statement throws a SQLException(duplicate key) in
steve@steve@[local] =# -- PG, SS and ORacle
steve@steve@[local] =# insert into test values (1);
ERROR:  duplicate key value violates unique constraint "test_pkey"
DETAIL:  Key (id)=(1) already exists.
steve@steve@[local] =#
steve@steve@[local] =# -- Following statement behaves differently for different DBMS:
steve@steve@[local] =# -- SS and OR: No error...statement runs fine
steve@steve@[local] =# -- PG: Another SQLException thrown...must rollback or commit
steve@steve@[local] =# insert into test values (99);
INSERT 0 1

In order to get the same behaviour as you wrote in the script, you'd have to turn off auto-commit before doing the insert- that stops the JDBC driver from issuing an implicit "start transaction" before it executes the next statement. If you put that implicitly-generated transaction into the psql script, it produces the error you describe:

steve@steve@[local] =# start transaction; -- generated by JDBC driver
START TRANSACTION
steve@steve@[local] *=# -- Following statement throws a SQLException(duplicate key) in
steve@steve@[local] *=# -- PG, SS and ORacle
steve@steve@[local] *=# insert into test values (1);
ERROR:  duplicate key value violates unique constraint "test_pkey"
DETAIL:  Key (id)=(1) already exists.
steve@steve@[local] !=#
steve@steve@[local] !=# -- Following statement behaves differently for different DBMS:
steve@steve@[local] !=# -- SS and OR: No error...statement runs fine
steve@steve@[local] !=# -- PG: Another SQLException thrown...must rollback or commit
steve@steve@[local] !=# insert into test values (99);
ERROR:  current transaction is aborted, commands ignored until end of transaction block

As an illustration of why this behaviour exists, consider what happens if I run the first transaction again. The intent is "create the table and populate it with a single row":

steve@steve@[local] =# start transaction;
START TRANSACTION
steve@steve@[local] *=# create table test(id int primary key);
ERROR:  relation "test" already exists
steve@steve@[local] !=# insert into test values (1);
ERROR:  current transaction is aborted, commands ignored until end of transaction block
steve@steve@[local] !=# commit;
ROLLBACK

So as soon as a problem is detected ("test" already exists), the remaining data manipulation isn't appropriate (the row already existed too, anyway)


Select distance between cities in two directions I have a distance table and a cities table.
I have joined them in a way where i now have: cityA_id,city_A,cityB_id,cityB, distance in km
i need to get the distance betweens cityA and CITY B and
then get the distance between cityB and CityA in the same query result.
(meaning if Albany to Auburn is 36 Km i want to also check Auburn to Albany distance (where cityA will be Auburn and cityB Albany)
sample data:

how can i write this affectingly?
what i have so far:
with A as (
SELECT  [CITY_1],
[CITY_NAME]
,[CITY_2]

,[DIS_IN_KM]

FROM [DB].[dbo].[DISTANCE] d
join cities c
on d.CITY_1 = c.CITY_NO
),
first_direction as
(
select A.[CITY_1] as cityA_id,A.[CITY_NAME] as [CityA],A.[CITY_2] as CityB_id,  c.CITY_NAME as CITYB, A.[DIS_IN_KM] as [distance in km],
c.CITY_NAME as CITYB2, A.[CITY_NAME] as CITYA2--, B.DIS_IN_KM as [distance in km opposite]

from A join CITIES c on A.CITY_2 = c.CITY_NO

)
select * from first_direction

tables schema:
CREATE TABLE [dbo].[DISTANCE](
    [CITY_1] [char](6) NOT NULL,
    [CITY_2] [char](6) NOT NULL,
    [DRIVE_TIME] [decimal](9, 2) NOT NULL,
    [DIS_IN_KM] [smallint] NOT NULL,
    [HARD_DIST] [smallint] NOT NULL,
    [BUS_FARE] [decimal](9, 3) NOT NULL,
    [CODE1] [smallint] NOT NULL,
    [CODE2] [smallint] NOT NULL,
    [CODE3] [smallint] NOT NULL,
    [DATE] [datetime] NOT NULL,
    [TEMP1] [decimal](9, 3) NOT NULL,
    [TEMP2] [decimal](9, 3) NOT NULL,
    [TEMP3] [decimal](9, 3) NOT NULL,
 CONSTRAINT [PK_DISTANCE2] PRIMARY KEY NONCLUSTERED
(
    [CITY_1] ASC,
    [CITY_2] ASC
)

CREATE TABLE [dbo].[CITIES](
    [CITY_NO] [char](6) NOT NULL,
    [CITY_NAME] [char](40) NOT NULL,
    [BASE_CODE] [tinyint] NOT NULL,
    [ZONE_CODE] [tinyint] NOT NULL,
    [NAME_2] [char](40) NOT NULL,
 CONSTRAINT [PK_CITIES_NUMBER] PRIMARY KEY NONCLUSTERED
(
    [CITY_NO] ASC
)

Thanks in advance!

I believe your CTE already gives you the list of all distances between any two Cities, so you just need to join the CTE to itself on reversed City fields to get the reverse results like so:
WITH CityDistances AS
(
    SELECT
        CITYA_id,
        CITYA,
        CITYB_id,
        CityB,
        DIS_IN_KM
    FROM DB.dbo.DISTANCE d
    INNER JOIN cities c
        ON d.CITY_1 = c.CITY_NO
)

SELECT
    CD1.CITYA_id,
    CD1.CITYA AS [starting point],
    CD1.CITYB_id,
    CD1.CITYB as destination,
    CD1.DIS_IN_KM AS [distance in km],
    CD2.CITYA_id AS [CITYA_id 2], -- Note you can call this CITYB_id instead if that's clearer to you (CD2.CITYA_id = CD1.CITYB_id)
    CD2.CITYA AS [starting point 2],
    CD2.CITYB_id AS [CITYB_id 2], -- Note you can call this CITYA_id instead if that's clearer to you (CD2.CITYB_id = CD1.CITYA_id)
    CD2.CITYB AS [destination 2],
    CD2.DIS_IN_KM AS [distance in km 2],
FROM CityDistances AS CD1
INNER JOIN CityDistances CD2
    ON CD1.CITYA_id = CD2.CITYB_id
    AND CD1.CITYB_id = CD2.CITYA_id


Upgrade older SQL 2008 to 2008 R2 on active/passive cluster I have an active/Passive SQL Server 2008 cluster in production that I need to upgrade to SQL Server 2008 R2. I just want to make sure I understand all the steps before starting this in production.

Run the SQL installer on the passive node and get through the setup files. Exit the installer and reboot. fail the cluster to the passive node.
Run the installer on the active node and update the shared components and SQL Setup files. Exit the installer and reboot. Fail the cluster back.
Run the installer on the passive node and select the Upgrade option.
Reboot and patch (service pack, etc) until no new patches are available on the passive node.
Fail the cluster to the passive node.
Upgrade and patch the original active node.
Fail the cluster back to the original Active node.

Did I miss anything? Are there any major issues I should be aware of?

I am only running the SQL instance and full text search. SSAS and SSRS are not installed.

I am using this KB as my guide - http://msdn.microsoft.com/en-us/library/ms191295.aspx

It shouldn't be that complex.

On the passive node upgrade the instance to SQL 2008 R2.  Then reboot that node.  Then failover.  At this point the database will be upgraded to SQL 2008 R2.

On the now passive node upgrade the instance to SQL 2008 R2.

There's an article I wrote about upgrading a SQL 2005 cluster to SQL 2008.  It's basically the same process.


Big table size in PostgreSQL while number of rows is small Tables' size in the PostgreSQL DB are big while the number of rows is relatively small (less then 500K rows).
Vacuum of the specific table did not solve the problem.
Only by deleting and importing the table from a previous dump file did solve the problem: the tables' size are back to normal. Consequently, all the queries on these tables became very fast while they were lasting for long before the import operation.
Note: auto vacuum is on by default

Autovacuum doesn't try to release space back to the OS. That's often counterproductive to do, since PostgreSQL would just have to allocate it again later, and allocating new storage is slower than re-using already allocated storage.

If you want to release space back to the operating system you can use VACUUM FULL on the table(s) in question, or the whole database. This will actually make tables with lots of inserts/updates slower unless you also set a FILLFACTOR of less than 100 first so there's space reserved.


Is dimensional modelling useful for modelling tables of calculations? I have a set of tables that look a bit like this:

Key 1 | Key 2 | Key 3 | Var 1 | Var 2 | Var 3 | ... | Var 200
-------------------------------------------------------------
   X  |   Y   |   Z   |   2   |   3   |   5   | ... |    8

The first three columns form a compound key. The remaining columns are populated by a series of calculations such as:

Var 3 = Var 1 + Var 2
Var 4 = Var 3 + Var 2
Var 5 = Var 3 + Var 4
....

(This is a huge simplification of the formulae. But, in essence, there is a chain of calculations that builds on previously calculated items.)

So, when considering how to build a dimensional model, I can identify the components of the key as a dimension (or two), and the variables seem to be measures, but I am struggling to identify what the fact is. I wonder if there is a key insight into the dimensional modelling process that I'm missing, or whether it's just inappropriate for my situation.

EDIT: I am not certain what I would achieve by dimensional modelling either! The context is an existing application which processes 3NF tables, does calculations, and spits out 3NF tables. It is proposed that these tables are migrated into a data warehouse which is being built using the dimensional modelling methodology. I am struggling to understand how this might be of benefit, since the calculations are abstract mathematical and statistical computations, while dimensional modelling seems to be oriented around identifying the "business process" that gives rise to facts.

If you could meaningfully slice the data by key1, key2 or key3 then you could make dimensions out of them.  In that case you would have separate dimension tables that had all the values of each key, and any relevant attributes (even just a human-readable description of what the value represents) or groupings (roll-ups of the key values).

Each dimension table would have the key value and its attributes, and would link to the fact table on its key.

If your values (Var X) are additive across rows (i.e. all the values added together for Key1='Foo' mean something) then you can use the keys to slice the data.

I don't think that relationships between the Var columns are really relevant to dimensional modelling unless you want to unpivot them and indentify them by a 'variable type' dimension rather than separate columns

Beyond that, the question is a bit vague to really get a clear view of what you are trying to achieve and why you want to make a dimensional model of your data in the first place.  Maybe if you can clarify your goals a bit we can give you a more meaningful answer.

EDIT:
Dimensional modelling is just identifying the axes by which you want to slice data in aggregate.  Sometimes a dimension might be very simple - just a code.  Sometimes it may be complex with lots of sub-attributes - such as an insurance policy.  A dimension lets you slice data in aggregate, either by its key or by rolling up the data by some attribute of the dimension.

If the VarX columns are additive between rows - i.e it makes sense to aggregate (for example) var2 for two or more rows - then a dimensional structure might make sense for reporting.  One key point of a dimensional model is that it tends to facilitate efficient queries in aggregate, and plays nicely with OLAP tools.

So, if I had values 'Foo', 'Bar', 'Wibble', 'Blarg' for key1 and values 'A', 'B', 'C' for key2 then I might have a few rows that looked a bit like:

Key1      Key2       Var1       [. . .]
--------- ---------- ---------- ----------
Foo       A          10         . . .
Bar       A          20         . . .
Foo       B          50         . . .

If I wanted to see the total of Var1 for all Key2='A' then the reporting tool would allow me to slice on Key2 and add up all the rows.  If I wanted to see the total of Var1 for Key1='Foo' then I could do something similar.  In this instance the total of Var1 for Key2 = 'A' would be 30.

If this is the sort of thing you want to do the for data then a dimensional model could help, and would allow you to put up a statistical reporting facility with an off-the-shelf reporting tool such as Report Builder.

A dimension table for Key1 might look like

Key1    Key1Desc                 Key1Grouping
------- ------------------------ -------------
Foo     Description of 'Foo'     Group A
Bar     Description of 'Bar'     Group A
Wibble  Description of 'Wibble'  Group A
Blarg   Description of 'Blarg'   Group B

By joining this dimension table to the fact table you can then roll up by Key1Grouping and see stats for (say) 'Group A.'  You can calculate ratios of Vars after the base additive measures have been aggregated.

Again - I'm still a bit unsure of quite what you want to know.


Cannot find .bak in a backup file My database backup works well or you can see the backup in the backup file. But the backup has no format of a backup file. I mean you cannot see the .bak associated with it, therefore unusable by users even though they can navigate to the backup file but unable to restore as it has no .bak.

How do I get it to show .bak, so the file is usable?

Alter the name of the file so it ends in .bak or select 'all files' when browsing to the backup file.


What is the search_path for a given database and user? I can see the current search_path with:

show search_path ;

And I can set the search_path for the current session with:

set search_path = "$user", public, postgis;

As well, I can permanently set the search_path for a given database with:

alter database mydb set search_path = "$user", public, postgis ;

And I can permanently set the search_path for a given role (user) with:

alter role johnny set search_path = "$user", public, postgis ;

But I would like to know how to determine what the database and role settings are (with respect to search_path) prior to altering them?

You can find configuration settings for roles and databases in the catalog table pg_db_role_setting.

This query retrieves any settings for a given role or database:

SELECT r.rolname, d.datname, rs.setconfig
FROM   pg_db_role_setting rs
LEFT   JOIN pg_roles      r ON r.oid = rs.setrole
LEFT   JOIN pg_database   d ON d.oid = rs.setdatabase
WHERE  r.rolname = 'myrole' OR d.datname = 'mydb';

If nothing is set, the next lower instance determines the default state of the search_path, which is postgresql.conf in this case or command-line options at server start. Related:

How does the search_path influence identifier resolution and the “current schema”

To unset any settings of a role or database - the search_path in this particular example:

ALTER ROLE myrole RESET search_path;

Or:

ALTER DATABASE mydb RESET search_path;

Or:

ALTER ROLE myrole in DATABASE mydb RESET search_path;

Never manipulate data in the system catalog (pg_catalog.*) manually. Use DDL commands as instructed in the manual for ALTER ROLE and ALTER DATABASE.
Essentially, the RESET command deletes a row from pg_db_role_setting allowing the base setting to take effect again. I wouldn't call that convoluted.


First, your answer is "Yes" but without any proof, only a single example. You have proven nothing about the general case. If I were to mark your answer, you'd get a straight 0 (OK, maybe a 1 because you understand what relational division is and how to perform it) and a note to read up on mathematical proofs.

For the actual question, the answer is: No, it is not correct. Now to prove that, we do only need a single counter-example. Here it is:

 R                        S          ==>    T=R÷S
 ------------         ---------             -----
| A | B | C |         | A | B |             | C |
| 1 | 2 | 4 |         | 1 | 2 |             | 4 |
| 1 | 2 | 9 |                               | 9 |
| 3 | 3 | 4 |
| 3 | 3 | 8 |
| 3 | 3 | 9 |
| 4 | 1 | 2 |

But:

 R                      T      ==>       R÷T != S
 ------------         -----           ---------
| A | B | C |         | C |           | A | B |
| 1 | 2 | 4 |         | 4 |           | 1 | 2 |
| 1 | 2 | 9 |         | 9 |           | 3 | 3 |
| 3 | 3 | 4 |
| 3 | 3 | 8 |
| 3 | 3 | 9 |
| 4 | 1 | 2 |

This is actually similar to integer division (when we discard the remainder). Sometimes it's Yes:

37 / 7 = 5 (the remainder 2 is discarded) and
37 / 5 = 7 (the remainder 2 is discarded)

and sometimes is No:

37 / 10 = 3 (the remainder 7 is discarded) but
37 / 3 = 12 (the remainder 1 is discarded)

and of course 12 != 10, and the single counter-example disproves the general case.

What can be proven is this:

If R÷S = T, then S is a (not necessarily strict) subset of R÷T.


Does SentryOne Plan Explorer work? Does SentryOne Plan Explorer work as advertised and is it legitimate? Are there any gotchas or something to be concerned about?

It looks like it shows the hot path in color commpared to SSMS's nightmare of a view for the estimated execution plan.

My concerns is - does it modify any data maliciously or otherwise?

edit: I had just heard of it and never heard of the company before.

I use it. It isn't malware.

On the SQL Server forums its one of three useful tools/SSMS plug-ins that are widely used (and thus implicitly reviewed) by the community.

SentryOne Plan Explorer
SSMS Tools Pack
Red Gate SQL Search


Do not add a comma in front of the string if value is null or empty If my result is null or empty I would like it not to add a comma in front of the string. How can I achieve that?

  Update Companies
    set address4 = concat(address4,','+ t.Eircode)
    From companies c
    Inner Join Temptbl1 t
    on c.comp_id = t.Comp_ID
    OR address4 = ''

Update :

Just to make it clear when I make this update

   ,V05 TTX1
    Waterford,X01 B234
    ,B90 E902
    Co Wexford,TD2 PVE2

It adds a comma regardless ,if it was empty or not

What I want to get back is this  :

    V05 TTX1
    Waterford,X01 B234
    B90 E902
    Co Wexford,TD2 PVE2

If there is no value then just add the string without the comma ,in our case V05 TTX1 without , at the start of the string

CREATE TABLE [dbo].[companies](
    [comp_id] [int] NOT NULL,
    [address4] [varchar](32) NOT NULL
)

INSERT INTO [dbo].[companies]
           ([comp_id]
            ,[address4])

Since you are already combining CONCAT and + to concatenate strings you could just turn them around (although I agree Lennert's solution shows the intent a lot clearer).

Update Companies
-- set address4 = concat(address4,','+ t.Eircode)
set address4 = concat(address4 + ',', t.Eircode)
From companies c
Inner Join Temptbl1 t
on c.comp_id = t.Comp_ID
OR address4 = ''

This works because CONCAT converts NULL into an empty string of type varchar(1), but as long as CONCAT_NULL_YIELDS_NULL is on concatenating a string with NULL will yield NULL.

See this dbfiddle for an example


Export SSRS report to CSV I would like to export an SSRS report to a CSV file. Sounds simple, but I do not know how to add double quotes to the column headers in the exported CSV file. For example:

"UserName"|"UserAdd"
"Mike"|"Some Address"

The only way I have seen this work is by adding the double quotes inside the report yourself, but that can be a bit tedious if you have many textboxes and many reports.

Something like this should work though:

=IIF(Globals!RenderFormat.Name = "CSV",  """" & CStr(Fields!MyField.Value) & """", Fields!MyField.Value)

This code checks to see if it's being rendered to csv and adds the quotes if that's the case.


TSQL Agent jobs owned by users I'm new to Brent Ozar's sp_blitz and am trying to do some cleanup in my new job.  I get tons of security findings about agent jobs owned by users.  Most of these hits are system-type accounts (reportexec, webexec, sqlservice), rather than employee-associated accounts.  Is there a problem with this approach?  I'd love to reduce the number of hits I'm getting with sp_blitz, but am not sure it's worth changing some of these things that were purposely set up like this.  Thanks for any help.

You should be fine as sp_blitz will highlight if jobs are owned by users since if their login is disabled or not available due to Active Directory problems, the job will stop working.

you can create your skip table and specify that to the sp_blitz @SkipChecksTable to skip checks.

BTW, sp_blitz is on github and you can view the code as well.


PostgreSQL: Non-continious replication of new data with COPY I have a database tables with millions of measurements. New data is coming in every day. For analysis, I want to replicate the data to my laptop (local postgres db). No need for automatic replication. It's OK to start a script for this.

I feel like the standard replication solutions are inadequate and
overengineered for my use because

I need asynchron replication,
some solutions are inefficient because they replicate row-wise
(yet data on the server is bulk-inserted),
and they need too much configuration.

I would like to use COPY TO STDOUT | COPY FROM STDIN but here I
have to select only the new data. How can I do that?

The table has this form:

   Column   |           Type
------------+--------------------------
 devicename | character varying(30)
 id         | integer
 timestamp  | timestamp with time zone
 value      | numeric
 variable   | text

PK would be (devicename, id). Note that id alone is not unique because
the data is coming from multiple devices.

How can I select only new data for COPY?
Any other approaches for this replication requirements?

I see the following possible solutions:

Change the source table

Change the source table to include a column that you can use to (uniquely) identify the data you have already synchronized. A column based on a sequence (serial or identity in Postgres 10) or column defined as timestamp that is populated through a trigger.

If that column is indexed, this is probably the most efficient solution.

Keep track of copied data

Create a table in the source database that contains the already synchronized rows.

create table synchronized
(
  devicename  character varying(30),
  id          integer,
  primary key (id, devicename)
);

The following statement would return all rows that are not yet synchronized and "marks" them as transferred in a single query. It should be possible to use that as the source for the copy statement (but I haven't tested it).

with todo as (
  select *
  from data
  where (id,devicename) not in (select id,devicename from synchronized)
), done as (
  insert into synchronized (id, devicename)
  select id, devicename
  from todo
)
select *
from todo;

Create a foreign table and use that for synchronization.

Using a foreign data wrapper you create a foreign table in your local Postgres server that connects to the remote server. Then whenever you want to synchronize the data, you can do that with a single statement:

insert into local_table
select *
from remote_fdw_table
on conflict do update
    set "timestamp" = excluded.timestamp,
        value = excluded.value,
        variable = excluded.variable;

That is probably the least efficient solution as it will always transfer the complete table to your local computer and overwrite/insert as needed. But this requires no changes on the source database and other than the I/O needed for the SELECT, has not impact on the source.

The connection that is used by the FDW does not need to be valid all the time. It only needs to work, when you run the synchronization. As long as you don't access the foreign table, it doesn't matter if your computer can connect to the source or not.

Copy everything into a staging table, then use "upsert"

A variation of the solution with the foreign table is to simple export the whole source table, import that into a staging table with the same structure, then use the above INSERT ON CONFLICT approach to insert/update the real table.

That requires one more step for synchronization but might be easier to handle depending on your environment.


Postgresql Streaming Replication on Windows failover Hello i have setup streaming replication using PostgreSQL 11 on Windows working OK.
How can i manually use the replica ( it is hot_standby=on ) in case Master fails ?
What are the steps i have to perform to the replica ?

Like the documentation says, there are three ways to promote a standby server:

run pg_ctl promote on the command line.
create a trigger file (if you have configured the standby server accordingly).
from v12 on, you can also execute

SELECT pg_promote();


Oracle Advanced Queuing LIFO With Oracle's Advanced Queuing is there a way to dequeue messages in a LIFO (last in first out) order?  There is an indication through a lack of information that this is not an option, but perhaps there is a way to do this such as queuing in a different order.

Assuming that you are using PL/SQL to dequeue the message (there may be additional JMS limitations-- I don't know enough about the interplay between JMS and AQ), you should be able to use a priority queue to implement LIFO (though it would be a bit hokey).  When you enqueue a message, you can specify a priority

  The priority attribute specifies the
  priority of the message. It can be any
  number, including negative numbers. A
  smaller number indicates higher
  priority.

If you create a sequence that counts back from some really large number, you could assign an ever increasing priority.  Then your prioritized dequeue would get messages in a LIFO order.


Estimated Rows Higher than Actual Despite Updating Statistics I have a (anonymised) query plan

There is a table (Object8) which has a high variation on the number of Actual Rows vs Estimated Rows on two columns (Column2 and Column6) which can be seen in the hash match and sort operators

I have updated all the statistics on the Object8 table (they were only a few days out of date and the table doesn't change much)

What else could the problem be?

Cardinality estimates are just numbers. They don't matter unless you have a performance problem. You state in your question that there's a difference between the actual number of rows and estimated number of rows at some steps in the query plan. That's certainly true, but it's pretty uncommon to see non-trivial query plans for which estimates match the actual number of rows exactly. I wouldn't focus on a difference of a thousand rows unless you've identified that difference to be the root cause of a query performance issue.

Let's consider updating statistics: why might you see inaccurate cardinality estimation even after updating statistics? Statistics often don't fully describe the column's data. They could be sampled, 201 steps could be insufficient for some data sets, density isn't a good metric if there's skew in the data, and so on. Join cardinality estimation and combining filters on multiple columns is difficult even with perfect statistics objects because histograms are only available on one column. SQL Server often doesn't have information about the level of correlation between columns, so it needs to make a guess.

If you'd like to learn more I recommend taking a look at Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator by Joe Sack. Here's a quote on one of the many differences between the legacy CE and the new CE:

  Multiple Join Conditions

  For joins with a conjunction of equality predicates, the legacy CE
  computes the selectivity of each equality predicate, assumes
  independence, and combines them. In contrast, the new CE estimates the
  join cardinality based on multi-column frequencies computed on the
  join columns.

The CEs use different models. One model may be a better fit than the other for your data or even for just a single join. In this case, it has very little to do with statistics. Updating statistics just isn't enough to result in good estimates for all cases.

On the other hand, if you have a specific performance that you'd like help with, you'll need to give more information. I don't see anything alarming in the query plan that you posted. What is the performance problem that you have with the query?


Is it possible to record incoming parameter values in a procedure call while tracing in SQL Server Profiler? Using SQL Server Profiler (I'm on SQL Server 2012), I'm trying to generate a useful trace that shows the parameter values, not just the SQL with variable names. The stored procedure walks through a gross amount of Inventory data to generate some extremely valuable results, and I'm trying to document the existing behaviour, so I can unit test it, define it exactly, and then refactor it into something sane.

I have a stored procedure that does a 54-parameter sub-procedure execute, inside a loop where the stored procedure creates a cursor then does a while loop. Here's a simplified view:

CREATE PROCEDURE
   [dbo].[OuterProcedure]
   (  @ProductCode varchar(8),
     -- 41 more parameters omitted
   )
AS
  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
  SET NOCOUNT ON
 DECLARE @AboutFourHundredLocalvariables -- omit about 400 local variable declarations.
 -- OMIT ABOUT 10 temporary table declarations.
 DECLARE  aCursor CURSOR FAST_FORWARD FOR
   SELECT [ID],bkno,  -- about 40 fields omitted.
              FROM vwVeryComplexViewThatDoesALotOfVeryBrutalJoins
              WHERE  (about_80_boolean_expressions AND omitted_here)
        ORDER BY some,keys,like,this

OPEN aCursor
FETCH NEXT FROM aCursor /* Get First Record */
    INTO @ID, @about_40_fields,....
WHILE (@@FETCH_STATUS = 0) AND
          ( @About80MoreBooleanExpressionsHere)
BEGIN   /* 1 */
     -- about 700 lines of logic, math and if-parameter-this-then-that
     -- stuff omitted
            EXEC  @ConsiderItem =
                      InnerProcedureCallWithinLoop
                                            @from_locn,
                        @About53PARAMSOMITTED,
                                                ...

    FETCH NEXT FROM CurInventory /* Get Next Record */
       INTO @ID,@MoreStuff,...
END
CLOSE CurInventory
DEALLOCATE CurInventory

How would I get a trace to show me all the parameter values passed to
InnerProcedureCallWithinLoop? There are 54 parameters. Do I have to write
essentially "54 lines of debug-printfs" inside my SQL or can I dump all the parameter values of a procedure call while doing an SQL trace some how?

When I get a trace right now, I get this output:

EXEC  @ConsiderItem = InnerProcedureCallWithinLoop  @from_locn,
                        @About53ParmsOmitted

What I'd like to know is that @from_locn = 1 and @About53ParmsOmitted = 'hello world' and so on.

This doesn't tell me the actual value of the parameter @from_locn. In the case of that first parameter, it's passed in to my top level stored procedure, so I know it's 0, or 1, as the case may be.  However about 40 of the 43 params in that inner procedure come from the FETCH NEXT FROM aCursor operation inside a WHILE loop.

Right now tracing tells me How many times InnerProcedureCallWithinLoop gets invoked,  and how long each one took, but not what the values of the parameters to that call were. If I could somehow get "runnable standalone SQL scripts" that replicate some corner case I find within my code, while tracing through these scripts, setting up these gross functions (I know, 54 parameters, that's really gross, but I didn't write them!) could take me an hour of typing just to build an SQL script that lets me invoke this corner-case myself, outside this big gargantuan snarl of SQL Server stored procedures.

This is all part of an effort to drill down to an SQL expression and build scripts that can probe  these complex  stored procedures.

Update I found an RPC "Output Param" recording option, but not an "RPC IN PARAM" recording option.

I'll bite the bullet and tell you that such a trace cannot be set up, because it is not the [perceived] purpose of traces. I have always done it this way:

  WHILE (@@FETCH_STATUS = 0) AND
               ( @About80MoreBooleanExpressionsHere)
   BEGIN   /* 1 */
       -- about 700 lines of logic, math and if-parameter-this-then-that
       -- stuff omitted
        INSERT InnerProcedureCallWithinLoop__TraceTable
                                          VALUES (@from_locn, @About53PARAMSOMITTED
        EXEC  @ConsiderItem =
              InnerProcedureCallWithinLoop
                    @from_locn,
                          @About53PARAMSOMITTED,
                                                      ...

If I know that it is only ever called from one location.  Otherwise, I do it in the callee instead of the caller.

ALTER PROC InnerProcedureCallWithinLoop
    @from_locn int,
    @About53PARAMSOMITTED ...
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET NOCOUNT ON;
INSERT InnerProcedureCallWithinLoop__TraceTable VALUES (@from_locn, @prm2, @prm3....
--- rest of proc

This is obviously different from using a trace, which is able to capture events even if they started and never finished (faulty parameters, rolled back transactions).  If that is your problem, you need to look at CLR or email methods to externalise the captured output.


How to answer why suddenly we need indexes or query needs to be changed I am junior DBA with 3 years of experience. Our job is to fine-tune queries or advise developers that particular code should be re-written or indexes are needed.

One simple question the dev team asks frequently is: "Yesterday it ran fine, what changed suddenly?" and we will be asked to check the infrastructure side. The first reaction on any problem always appears to be to put maximum blame on the infrastructure which is always the first thing being validated.

How should we answer to "what changed" questions by the development team? Did you guys ever faced the same situation? If so, please share your experience.

How to answer what changed question by dev ?

This is a very common question not only with DEV, it applies to every team in IT and business.

What changed ? ==> can be answered by facts and figures.

Facts refer to for example

increase the amount of users that are accessing the database ?
Any change in server configuration parameter ?
Database Maintenance - update stats, reorg/rebuild of indexes not being performed ? Due to this the plans are being incorrectly generated !
Amount of data has increased ?
Changes were made on the network side, OS was patched and/or a new service pack or CU for sql server was deployed - without doing a full regression testing of your application business cycle ?
The underlying SAN has become suddenly slow ?

Figures can be derived if you have data to show. For example :

Baselining your server is critical during this situation. This will alleviate the blame game since you can support the facts with solid figures.
Start collecting data using DMVs or sp_whoisactive to a table, so that the data gets persisted after a sql server reboot.

(you have to workout based on your environment and needs, on how often to collect the data/ what data to collect and how much will be the retention period) or (you can invest in a third party software like sqlsentry or idera's diagnostic manager that will do the above work for you).


why is my trigger called even when the underlying table didn't change? I have a quite basic trigger defined as such:

CREATE TRIGGER dbo.foo
ON dbo.bar
FOR INSERT, UPDATE, DELETE
AS
   ...

I also have a stored procedure which at some point does something like this:

Insert into dbo.bar (a) select a from @bar

When @bar contains some data, my trigger is called as expected, but when it doesn't, the trigger is called even though dbo.bar obviously didn't change.

This is confirmed by both SQL profiler and the contents of sys.dm_exec_trigger_stats.

SQL Profiler reports a very low footprint (3 reads, 0 write), but since I have a lot of triggers, this happens a lot, and I end up with significant total_reads figures.

Any hints on why this is happening? Is there a way to change this behavior?

This is the expected behaviour of a statement trigger. It executes every time a statement (INSERT in this case) is executed and once (per statement, not per row.) The behaviour is similar in other DBMS (like Oracle.)

The MSDN says it in the first paragraph in the CREATE TRIGGER page:

  These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected.

I don't think there is a way to change this. There are ways to check though - inside the trigger, once it fires - whether there are rows affected or not and act accordingly.

For example you can use

IF ( @@ROWCOUNT = 0 )
  RETURN;

Or a somewhat more robust alternative in the presence of MERGE

IF NOT EXISTS(SELECT *
              FROM   DELETED)
   AND NOT EXISTS(SELECT *
                  FROM   INSERTED)
  RETURN;


Is there any way to save the execution plan in a file in PostgreSQL? By saving a plan in a file, I don't mean to view it, but force the optimizer to use the saved plan to execute some query.

For example, if the optimizer chooses to do a hash join for a two-table query and I manage to force the optimizer to use a nest loop join, is there any way to save the nest loop join plan and let the optimizer use it next time when I execute the same query?

No, there is no way to save a query plan to disk, force a query plan, etc.

Fix your optimizer parameters and make sure your stats are accurate. You didn't bother to mention your version, show explain analyze output, etc so I can't help you with that in detail. Start here:

http://wiki.postgresql.org/wiki/Slow_Query_Questions

Check your rowcount estimates vs actual results in explain analyze output. http://explain.depesz.com/ makes this easier.

Check that cost estimates reflect reality. Consider adjusting random_page_cost / seq_page_cost if your random I/O is faster or slower than PostgreSQL's estimates. Also ensure effective_page_size reasonably reflects the amount of RAM available for caching.

If you continue to have issues after making appropriate changes per the manual and tuning guides, please report a detailed description of the problem and a self-contained test case to the pgsql-peform mailing list.

See:

Why PostgreSQL doesn't have query hints
Hinting at PostgreSQL
https://stackoverflow.com/questions/309786/how-do-i-force-postgres-to-use-a-particular-index
Why PostgreSQL actually already has hints
The wiki page on hints
a somewhat topical frothing rant on the topic

My personal opinion is that PostgreSQL's policy of having no hints (except actually there are hints, they're just uglier hacks than actual hints would be) is user-hostile and pointless. Most users will use whatever hacks they can to avoid actually understanding cost-based optimizers, so we're just forcing them to use uglier hacks. Users who want to understand and do it right will still find times the optimizer produces inappropriate results, and while it's good to fix those, it's also good for those users to have a workaround in the mean time. And users who're cost based optimizer purists don't have to use any hinting features that are there, including the not-hints that already exist.

This drives me especially nuts with common table expressions, where PostgreSQL's quirky interpretation of them as optimization fences is going to cause us pain and suffering down the road when we want to start optimizing into them, but discover everyone's been using them as a sort of query hint workaround for the query hints we don't have.

Examples of not-hints include:

The enable_ parameters
OFFSET 0
Optimization fence of CTE terms


SQL Server Trigger does not update all table records This is my first question so please go easy on me. We are using Access 2013 as a front-end and SQL Server 2012 with MS SQL Server Management Studio 11.0.2100.60 backend with ODBC connections.

I am trying to write a series of triggers to update a couple of different tables. When I paste the data manually in Access (our front-end) the trigger only updates 71 of 80 records of the sample data. If I disable all triggers and pull out only the query portion all of the records (80) are updated. Why is my trigger not updating the entire table?

Here is the trigger:

ALTER TRIGGER [dbo].[TRIG_Update_Inventory_Ind_Trees_Abs_Plot_Numb]
   ON  [dbo].[Plot_Location]
   AFTER INSERT
AS
    if @@rowcount = 0
RETURN
    UPDATE Inventory_Ind_Trees
    SET Inventory_Ind_Trees.Abs_Plot_Numb = Plot_Location.Abs_Plot_Numb
    FROM Plot_Location
    WHERE Plot_Location.Invent_Plot_ID = Inventory_Ind_Trees.Invent_Plot_ID

The portion I run as a stand alone query that updates the entire table is here:

UPDATE Inventory_Ind_Trees
SET Inventory_Ind_Trees.Abs_Plot_Numb = Plot_Location.Abs_Plot_Numb
FROM Plot_Location
WHERE Plot_Location.Invent_Plot_ID = Inventory_Ind_Trees.Invent_Plot_ID

The records that are not updated are left as NULL. I am able to change them manually but I want this to be an automatic process.

Any suggestions where to look for the answer, or any help pointing me in the right direction would be greatly appreciated.

I only want to update the new records that have been put into the Inventory_Ind_Trees table with the corresponding Abs_Plot_Numb values from the Plot_Location table, but I was not quite sure how to do that.

Currently, your trigger is updating all rows.  Modify it to use the inserted virtual table, something like this:

ALTER TRIGGER [dbo].[TRIG_Update_Inventory_Ind_Trees_Abs_Plot_Numb]
ON [dbo].[Plot_Location]
AFTER INSERT
AS
BEGIN
    UPDATE Inventory_Ind_Trees
    SET Inventory_Ind_Trees.Abs_Plot_Numb = Plot_Location.Abs_Plot_Numb
    FROM Plot_Location
    WHERE EXISTS (
            SELECT 1
            FROM inserted i
            WHERE i.Invent_Plot_ID = Plot_Location.Invent_Plot_ID
        )
        AND Plot_Location.Invent_Plot_ID = Inventory_Ind_Trees.Invent_Plot_ID;
END

This may not be exactly correct; it's hard to tell exactly where to join the inserted virtual table with your other tables.  Essentially, the idea is the inserted virtual table only contains rows that were either inserted or updated in the Plot_Location table.

You shouldn't check the @@ROWCOUNT system function at the start.  It may not reflect the  rowcount you think it does.  Instead you should select the count of rows in the inserted table.  Like this:

DECLARE @rc INT;
SELECT @rc = COUNT(1)
FROM inserted;

IF @rc > 0
BEGIN
    --body of trigger  goes here.
END


Anonymous and Public User Table Design in twitter like application I have schema design problem which I am not 100% sure if my assumptions are correct.
So the requirements are:

I need to have 2 different type of user, anonymous and public.
A user can have both anonymous and public profile.
A public and anonymous user can follow anonymous and public.
Anonymous user and public user must not be identifiable by clients. Which means if I know public user I shouldn't be able to fetch anonymous profile details vice versa.

I first come up with idea:
User (id, anon_id,public_id)
AnonymousUser(id(PK,FK),nickname)
PublicUser(id(PK,FK),nickname,fullname,phone,is_phone_registered)
RelationShip(id,follower_id(FK-User),following_id(FK-User))
Tweet(id, author_id(FK-User),text)

In this design I could split users into two. However, if we know the tweet's author id we are not able to understand if it's send by public or anonymous account. And also the same problem exist in Relationship too, we are not able to understand if follower/following is anonymous or public. The solution might be adding 2 more fields to Relation clarify follower/following type ie. follower_type/following_type and 1 more field to Tweet author_type.
Another solution which I felt more correct is
User(id(PK),nickname,fullname,phone,is_phone_registered,public)

In this design I can have both anonymous and public profiles in same table. And records would look like:

id
nickname
fullname
phone
is_phone_registered
public

123
slayer
John Doe
+1 90 123 123 12 12
true
NULL

312
vendetta
NULL
NULL
false
123

And public field should not be visible to clients so I am pretty much achieving my goals on security side of things.
Sorry for my ignorance I am having NoSql background and in sql world I feel like I need achieve:

Single responsibility in tables
Get rid of a lot of redundant null's
Feels like self referencing column is an anti-pattern, which is public field is referencing to other rows id.

All suggestions are welcomed.

At first glance of your initial schema design, I liked the normalization of it, but I understand the problem you've run into. So my recommendation to solve that problem was to denormalize a little which essentially you did in your second solution. One table for all types of Users, self-referencing to link an Anonymous User to Public User. I don't know if it's necessarily considered an anti-pattern but it's definitely a common design solution in relational databases to use self-referencing tables.
My only recommendation on top of that is to add a dedicated field, such as a boolean, whose purpose is to define if the record is Anonymous vs Public (e.g. isPublic). That way, you can even have an Anonymous User who doesn't necessarily link to a Public User and that it's explicit it's an Anonymous User record (when the public field is NULL).


cant truncate tables with a pk/ fk relationship? I have a table called Folder with 3 fields: Id (PK), Name, UserId (FK)

and another table called User with two fields: Id (PK), UserName

i am trying to run this sql:

truncate table Folder
truncate table User

i get the following error

Cannot truncate table 'Folder' because it is being referenced by a FOREIGN KEY constraint.
Cannot truncate table 'user'   because it is being referenced by a FOREIGN KEY constraint.

...even after deleting all the records in both tables i get this. why am i not allowed to truncate my tables?

how can i reset the seeds of the PK's easily without using truncate if there is now way to run a truncate on pk/fk relationship tables?

Edit:
DB diagram added

To reseed, use DBCC CHECKIDENT

DBCC CHECKIDENT ('table', RESEED, 1)

Or you'd have to drop the FK, truncate, then recreate the FK.

This is by design: TRUNCATE TABLE is simple page deallocation and constraints are not checked row by row. This limitation means you can't truncate when there are FKs.. even if you disable the FK.


SQL Server 2008: use AVG() and an inner join Here's what I have so far. I know this works, but I need to get the average of the pulse and depressionlevel instead of returning actual values for each row:

SELECT
  Patients.LastName
  , PatientVisit.Pulse AS [avg Pulse]
  , PatientVisit.DepressionLevel AS [avg Deplevel]
FROM Patients
    INNER JOIN PatientVisit on Patients.PatientKey = PatientVisit.PatientKey
ORDER BY
  Patients.LastName

SELECT Patients.LastName,
AVG(PatientVisit.Pulse) as "avg Pulse",
AVG(PatientVisit.DepressionLevel) as "avg Deplevel"
from Patients
inner join
PatientVisit
on Patients.PatientKey = PatientVisit.PatientKey
GROUP BY Patients.PatientKey, Patients.LastName
order by Patients.LastName;

Important to group by PatientKey as well, so you don't have a single record for Smiths and Nguyens. It also allows extra optimisations, as it (should be) marked as unique (being the Primary Key).


Where can I read about the behavior of the postgress casting operators? I want to do something like this:

alter table foos
alter column bar type bool using bar::boolean;

bar is currently a text column. Some experimentation shows that postgres will do things like convert a string "t" to boolean true. In my case this is great, but I'd like to read the full behavior before using this. I can't seem to find where this documentation is.

This is in the PostgreSQL manual section on the boolean data type:

Boolean constants can be represented in SQL queries by the SQL key words TRUE, FALSE, and NULL.
The datatype input function for type boolean accepts these string representations for the “true” state:
true
yes
on
1

and these representations for the “false” state:
false
no
off
0

Unique prefixes of these strings are also accepted, for example t or n. Leading or trailing whitespace is ignored, and case does not matter.
The datatype output function for type boolean always emits either t or f […]


MySQL Shutdown on Windows Folks,

I am running MySQL on Windows8 for my personal (casual) use. I am trying to determine if there is a way to automatically and cleanly shutdown MySQL whenever I shutdown my Windows8 computer. Ideally, Windows is supposed to automatically stop the MySQL service as part of OS shutdown sequence. But I am not sure if it actually happens though. Any thoughts/ideas/recommendations around this subject will be appreciated!

Additional Information: Currently I am using a .bat script to do a "net stop" on the mysql Windows service. I manually invoke this .bat script just before I shutdown my computer.

Thank You.

If you would like to manually control this, here are some suggestions
Login to Window Command Line Shell as Administrator and run this
C:\> net stop mysql

When the prompt comes back, MySQL is down.
If you shutdown Windows, the only evidence for MySQL's shutdown would be in the error log.
EXAMPLE
On my laptop

I have MySQL 5.5.37 for Windows
It's been up since Dec 17, 2014
The error log filename says 12/17/2014  11:09 AM            83,010 ROLAED3573-NYTD.err
I just ran net stop mysql
The error log filename says 01/09/2015  02:02 PM            83,304 ROLAED3573-NYTD.err

From my actions, MySQL shutdown wrote 294 bytes at the end of the error log. What did it write ?
150109 14:02:33 [Note] MySQL: Normal shutdown

150109 14:02:33 [Note] Event Scheduler: Purging the queue. 0 events
150109 14:02:33  InnoDB: Starting shutdown...
150109 14:02:34  InnoDB: Shutdown completed; log sequence number 10001663889
150109 14:02:34 [Note] MySQL: Shutdown complete

Given this output, I would expect a shutdown to have these lines at the end of the error log.
UPDATE 2015-01-09 14:47 EST
I just ran this
C:\MySQL_5.5.37\data>net start mysql
The MySQL service is starting.
The MySQL service was started successfully.

C:\MySQL_5.5.37\data>

The error log says 01/09/2015  02:34 PM            84,289 ROLAED3573-NYTD.err
This landed at the bottom of the file
150109 14:34:48 [Note] Plugin 'FEDERATED' is disabled.
150109 14:34:48 InnoDB: The InnoDB memory heap is disabled
150109 14:34:48 InnoDB: Mutexes and rw_locks use Windows interlocked functions
150109 14:34:48 InnoDB: Compressed tables use zlib 1.2.3
150109 14:34:48 InnoDB: Initializing buffer pool, size = 1.0G
150109 14:34:48 InnoDB: Completed initialization of buffer pool
150109 14:34:48 InnoDB: highest supported file format is Barracuda.
150109 14:34:48  InnoDB: Waiting for the background threads to start
150109 14:34:49 InnoDB: 5.5.37 started; log sequence number 10001663889
150109 14:34:49 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
150109 14:34:49 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
150109 14:34:49 [Note] Server socket created on IP: '0.0.0.0'.
150109 14:34:50 [Note] Event Scheduler: Loaded 0 events
150109 14:34:50 [Note] MySQL: ready for connections.
Version: '5.5.37-log'  socket: ''  port: 3306  MySQL Community Server (GPL)

I have shutdown my laptop
I booted up my laptop
The error log says 01/09/2015  02:40 PM            85,568 ROLAED3573-NYTD.err
What did it write this time ?
150109 14:39:13 [Note] MySQL: Normal shutdown

150109 14:39:13 [Note] Event Scheduler: Purging the queue. 0 events
150109 14:39:13  InnoDB: Starting shutdown...
150109 14:39:13  InnoDB: Shutdown completed; log sequence number 10001663889
150109 14:39:13 [Note] MySQL: Shutdown complete

150109 14:40:04 [Note] Plugin 'FEDERATED' is disabled.
150109 14:40:04 InnoDB: The InnoDB memory heap is disabled
150109 14:40:04 InnoDB: Mutexes and rw_locks use Windows interlocked functions
150109 14:40:04 InnoDB: Compressed tables use zlib 1.2.3
150109 14:40:04 InnoDB: Initializing buffer pool, size = 1.0G
150109 14:40:04 InnoDB: Completed initialization of buffer pool
150109 14:40:04 InnoDB: highest supported file format is Barracuda.
150109 14:40:05  InnoDB: Waiting for the background threads to start
150109 14:40:06 InnoDB: 5.5.37 started; log sequence number 10001663889
150109 14:40:06 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
150109 14:40:06 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
150109 14:40:06 [Note] Server socket created on IP: '0.0.0.0'.
150109 14:40:06 [Note] Event Scheduler: Loaded 0 events
150109 14:40:06 [Note] MySQL: ready for connections.
Version: '5.5.37-log'  socket: ''  port: 3306  MySQL Community Server (GPL)

As shown from the end of the error log, it performed a clean shutdown at 2:39 PM. MySQL came back up when Windows was started.


innodb_force_recovery when InnoDB corruption When I start mysqld (in /etc/init.d), it failed with

InnoDB: corruption in the InnoDB tablespace.

What's the best innodb_force_recovery value to force mysqld to start? I have tried 4 and 6, but corruption error message is the same. I already have a full dump.

According to MySQL's documentation, if you use innodb_force_recovery=1, the server will start even if it detects a corrupt page.

http://dev.mysql.com/doc/refman/5.0/en/forcing-innodb-recovery.html

Still, there are a couple of things you may want to check.

Are there any relevant errors in mysql's error log?  cat /var/log/mysqld.log

Is the file system OK, can you create files with touch in the same partition ?  ex. touch /var/somefile.txt

If you have a full backup of your databases, as you mention in your question, you can delete the ibdata and ib_logfile(s) and recreate them, then restore your databases.  This will resolve any corruption issues you may have in the innodb files.

The steps are:

Stop mysql
delete the ibdata and ib_logfile files.

ex : rm -f /var/lib/mysql/ibdata1

ex : rm -f /var/lib/mysql/ib_logfile*
Start MySQL (which recreates the innodb files)
Restore the dump  mysql -u root -p < mydump.sql

As long as you only delete the innodb files(ibdata and ib_logfiles), your users and their access rights to the databases will still be in tact.


Can't help the engine to choose the correct execution plan The stuff are pretty complex to share the original code (a lot of routines, a lot of tables), so I will try to summarize.

Environment:

SQL Server 2016
standard edition

Objects:

wide table with the following columns:

ID BIGINT PK IDENTITY
Filter01
Filter02
Filter03
.. and many columns

stored procedure returning visible ID from the given table depending on filter parameters
the table has the following indexes:

PK on ID
NCI on Filter01 INCLUDE(Filter02, Filter03)
NCI on Filter02 INCLUDE(Filter01, Filter03)

Basically, in the routine I am creating three temporary tables - each holding current filtering values and then join them with the main table. In some cases, Filter02 values are not specified (so the join with this table is skipped) - the other tables are always joined. So, I have something like this:

SELECT *
FROM maintable
INNER JOIN #Filter01Values -- always exists
INNER JOIN #Filter02Values -- sometimes skipped
INNER JOIN #Filter03Values -- always exists

So, how the IDs are distributed - in 99% of the cases it will be best to filter by Filter02Value and I guess, because of this, the engine is using the NCI on Filter02 INCLUDE(Filter01, Filter03) index.

The issue is that in the rest 1% the query fails badly:

In green is the Filter02 values table and you can see that filtering on this does not reduce the read rows at all. Then when the filtering by Filter01 is done (in red) about 100 rows are returned.

So, this is happening only when the stored procedure is executed. If I execute its code with these parameters I nice execution plan:

In such case, the engine is filtering by Filter01 first and Filter02 third.

I am building and executing dynamic T-SQL statement and I add OPTION(RECOMPILE) at at the end, but it does not change anything. If I add WITH RECOMPILE on the stored procedure level, everything is fine.

Note, the values in the temporary tables for filtering are not populating in the dynamic-tsql statement. The tables are defined, populated and then the statement is built.

So, my questions are:

is the engine building a new plan for my dynamic statement as I have OPTION(recompile) - if yes, why is wrong
is the engine using the values populated in my filter02 temporary table to build the initial plan - maybe yes, that's why it is choosing the wrong plan
using recompile on procedure level feels very hard/lazy fix - do you have any ideas how I can assist the engine further and skip this option - new indexes for examples (I have try a lot)

From your plans I see the problem in this statement:

INSERT INTO #Analysis_ClientAccess_SurveyInstances_SurveyInstances ([SurveyInstanceID])
select SI.[SurveyInstanceID]
from [dbo].[SurveyInstances] SI
    inner JOIN #Analysis_ClientAccess_SurveyInstances_ProtoSurveysFiltered PSF
        on SI.[ProtoSurveyID] = PSF.[ProtoSurveyID]
    inner JOIN #Analysis_ClientAccess_SurveyInstances_ClientAccessStatusIDs CAS
        on SI.[ClientAccessStatusID] = CAS.[ClientAccessStatusID]
    inner JOIN #Analysis_ClientAccess_SurveyInstances_CampaignsFiltered CF
        on SI.[CampaignID] = CF.[CampaignID]
where SI.[IsOkForExport] = 1 AND SI.[HoldExport] = 0 AND ( PSF.[AllowedBy] = 'C' )
option(RECOMPILE)

There is no need in recompile here since there is no parameters at all, you have only constants here.
The difference in two plans is in the JOIN ORDER:

The correct join order is first

[dbo].[SurveyInstances] SI
    inner JOIN #Analysis_ClientAccess_SurveyInstances_ProtoSurveysFiltered PSF
        on SI.[ProtoSurveyID] = PSF.[ProtoSurveyID]

and then everything else, this join applies all the filters and produces only 104 rows that go to final resultset.

Your "slow" plan shows different join order:

[dbo].[SurveyInstances] SI
    inner JOIN #Analysis_ClientAccess_SurveyInstances_CampaignsFiltered CF
        on SI.[CampaignID] = CF.[CampaignID]

that produces 17 mln of rows.

You don't want this join order so your choices are to use FORCE ORDER hint or to simply save the result of the correct join to another temp table and then to join it with the rest of your query:

select SI.[SurveyInstanceID]
into #si_psf
from [dbo].[SurveyInstances] SI
    inner JOIN #Analysis_ClientAccess_SurveyInstances_ProtoSurveysFiltered PSF
        on SI.[ProtoSurveyID] = PSF.[ProtoSurveyID]
where SI.[IsOkForExport] = 1 AND SI.[HoldExport] = 0 AND ( PSF.[AllowedBy] = 'C' );

INSERT INTO #Analysis_ClientAccess_SurveyInstances_SurveyInstances ([SurveyInstanceID])
select SI.[SurveyInstanceID]
from #si_psf SI
    inner JOIN #Analysis_ClientAccess_SurveyInstances_ClientAccessStatusIDs CAS
        on SI.[ClientAccessStatusID] = CAS.[ClientAccessStatusID]
    inner JOIN #Analysis_ClientAccess_SurveyInstances_CampaignsFiltered CF
        on SI.[CampaignID] = CF.[CampaignID];

I suggest you to use the second method.


WHERE IN subquery too slow I have a FULLTEXT search on the database, with the WHERE IN condition which checks that search results exists in another table. The query takes more than 1 hour to complete (companies table has 67k rows, and employees table has 82k rows).

Desired result: get search results for "company ABC" in table A only if we have employees data for that company in table B.

The query I have is:

SELECT *,
        MATCH (name, keywords, description) AGAINST ('+searchKeyword*') as relevance,
        (SELECT COUNT(customerID) FROM customers WHERE MATCH (name, keywords, description) AGAINST ('+searchKeyword*' IN BOOLEAN MODE) AND customerID IN(SELECT customerID FROM customersPersonelData)) as totalResults
FROM customers
WHERE MATCH (name, keywords, description) AGAINST ('+searchKeyword*' IN BOOLEAN MODE)
    AND customerID IN(SELECT customerID FROM customersPersonelData)
ORDER BY relevance DESC, name ASC
LIMIT 0,10

For testing I removed "relevance" and "totalResults" subqueries, and this query then takes 72 seconds:

SELECT *
FROM customers
WHERE MATCH (name, keywords, description) AGAINST ('+searchKeyword*' IN BOOLEAN MODE)
    AND customerID IN(SELECT customerID FROM customersPersonelData)
ORDER BY name ASC
LIMIT 0,10

Query explanation:

I tried using EXIST and JOIN, the result is the same.
How can I speed this query up?

If I understand correctly, you are getting count to satisfy your Pagination requirements. Generally, two separate queries are executed; one to get the 10 records, and another to get the total count.

The problem in your approach is that you are calculating the Count in a subquery inside the SELECT clause. For every row returned by the SELECT clause, is going to be executing the same count again and again. You can move the count calculation part to a Derived Table instead (Subquery inside the FROM clause), and CROSS JOIN it.

Another noteworthy thing is that MySQL is (unfortunately) generally inefficient when optimizing subqueries inside the IN(...) condition. You can convert this into EXISTS based Correlated Subquery instead (JOIN may result in Duplicates).

SELECT c.*,
       MATCH (c.name, c.keywords, c.description) AGAINST ('+searchKeyword*') as relevance,
       dt.totalResults
FROM customers c
JOIN customersPersonelData cpd ON cpd.customerID = c.customerID
CROSS JOIN
(
  SELECT COUNT(c1.customerID) AS totalResults
  FROM customers c1
  WHERE MATCH (c1.name, c1.keywords, c1.description) AGAINST ('+searchKeyword*' IN BOOLEAN MODE)
    AND EXISTS (SELECT 1 FROM customersPersonelData cpd1
                WHERE cpd1.customerID = c1.customerID)
) dt
WHERE
  MATCH (c.name, c.keywords, c.description) AGAINST ('+searchKeyword*' IN BOOLEAN MODE)
    AND EXISTS (SELECT 1 FROM customersPersonelData cpd
                WHERE cpd.customerID = c.customerID)
ORDER BY relevance DESC, c.name ASC
LIMIT 0,10

For good performance, you would need an index on customerID in customersPersonelData table. Also, a composite Full Text Index should exists on (name, keywords, description) columns in the customers table.

Now, if I were you, and this was a Pagination problem, I would look at this article to rethink how pagination should be done: https://mariadb.com/kb/en/library/pagination-optimization/


updating single row in MYSQL We have a situation where we need to update on a single row several thousands time a second and this requirement lasts continuously for 5 hours every day due to the nature of the business. Design cannot be changed so we are looking for solutions. Usually we use very fast SSD drives for faster performance and so far we are able to cope up with the requests.
But our traffic is increasing every day and we need another solution that can replace our current method and help us update same row many more thousands of time a second.

Any suggestion would be great. We use MYSQL 5.6 currently

Consider using the MEMORY storage engine, so the changes will happen in memory. In case of a crash, you can rely on the binlog to recover the latest value. And if you have a slave, you can use InnoDB on the slave.

If you want to stay on InnoDB, consider the HandlerSocket plugin. It provides an SQL-less interface to InnoDB, and it should be much faster in your scenario.


Instant File Initialization on Backup drive I know IFI (Instant File Initialization) is good to have enabled on SQL Servers. How about backup archive locations? We backup our DBs to a local SSD drive (so it backs up quickly), then throughout the day we slowly move the backups over to an archive location and perform restores and DBCCs on a non-prod server. I was curious if that archive location we copy the backups to should have IFI enabled?

If the archive location is part of same server and if you are performing restores to same server,then you will have IFI enabled.You can always enable Instant file initialization on server you are performing restores too ,I dont see an issue enabling it on prod server and not enabling it on test server.you can further check using below trace flags if IFI is enabled or not.

DBCC TRACEON(3004,-1);
DBCC TRACEON(3605,-1);


Windows Authentication Doesn't Work Locally I have a box running SQL Server 7 (I know) on Windows XP (I know).

All of the sudden, I'm unable to use Windows authentication to connect to the database from the local machine.

I can connect using SQL authentication on the local machine.

I can connect using both SQL and Windows authentication from another machine.

Here's the error I get when using Query Analyzer to connect to the local database using Windows authentication.

I've tried connecting using both the machine name, . and localhost.

Any ideas?

Thanks!

Update:  Added Screenshot of server protocols

I never figured out the root cause of the problem, but installing SQL Server 7 SP4 fixed the issue.


Transpose Query result in sql server I have a query that returns several rows (one to many relationship), I need to be able to obtain as a result a single row that has concatenated these values.
This is my query:

SELECT I.[ID]
      ,D.[CODEID]
      ,D.[DESCRIPTION]
      ,D.[PARENT]
      ,D.[LEVEL]
  FROM [wde].[INTERACCION] AS I
  LEFT JOIN [wde].[TIPIFICACION] AS t
  ON T.ID = I.ID
  INNER JOIN  [wde].[DICCIONARIO] AS D
  ON D.CODIGOID = T.CODIGO

Result:

And I nedd this:

Note: The number of rows returned by the query are variables per record

I've created a quick model:

USE tempdb;
IF EXISTS (SELECT 1 FROM sys.schemas s WHERE s.name = 'wde')
BEGIN
    DROP TABLE wde.INTERACCION;
    DROP TABLE wde.TIPIFICACION;
    DROP TABLE wde.DICCIONARIO;
    DROP SCHEMA wde;
END
GO
CREATE SCHEMA wde;
GO
CREATE TABLE wde.INTERACCION (ID int);
CREATE TABLE wde.TIPIFICACION (ID int, CODIGO int);
CREATE TABLE wde.DICCIONARIO (CODEID int, [DESCRIPTION] varchar(100), [PARENT] varchar(100), [LEVEL] int, CODIGOID int);

INSERT INTO wde.INTERACCION (ID) VALUES (59);
INSERT INTO wde.TIPIFICACION (ID, CODIGO) VALUES (59,1);
INSERT INTO wde.DICCIONARIO(CODEID, DESCRIPTION,PARENT,LEVEL,CODIGOID)
VALUES (100, 'MARCA VEHICULO'       , 0  , 1, 1)
    , (101, 'SUCURSAL'              , 0  , 1, 1)
    , (102, 'CONTACTADO'            , 0  , 1, 1)
    , (105, 'FIAT'                  , 100, 2, 1)
    , (117, 'ECUADOR'               , 101, 2, 1)
    , (122, 'CONTACTO TELEFONICO'   , 102, 2, 1);

This query gets the results you want, but may only be valid for your limited set of test data shown.  Without more specific requirements from you, it's difficult to be concise.

SELECT i.ID
    , Level1 = STUFF((SELECT ', ' + d.DESCRIPTION
        FROM wde.DICCIONARIO d
            INNER JOIN wde.TIPIFICACION t ON d.CODIGOID = t.CODIGO
        WHERE t.ID = i.ID AND d.PARENT = 0 FOR XML PATH('')),  1, 2, '')
    , Level2 = STUFF((SELECT ', ' + d.DESCRIPTION
        FROM wde.DICCIONARIO d
            INNER JOIN wde.TIPIFICACION t ON d.CODIGOID = t.CODIGO
        WHERE t.ID = i.ID AND d.PARENT <> 0 FOR XML PATH('')),  1, 2, '')
FROM wde.INTERACCION i;

Results:

╔════╦══════════════════════════════════════╦════════════════════════════════════╗
║ ID ║                Level1                ║               Level2               ║
╠════╬══════════════════════════════════════╬════════════════════════════════════╣
║ 59 ║ MARCA VEHICULO, SUCURSAL, CONTACTADO ║ FIAT, ECUADOR, CONTACTO TELEFONICO ║
╚════╩══════════════════════════════════════╩════════════════════════════════════╝


How to name table-variable function unique constraint? I am renaming some unique constraints to match our database objects naming convention. Strangely, there are several multi-line table valued function which returned table has unique constraints as follows:

CREATE FUNCTION [dbo].[fn_name] (...)
RETURNS @Result
TABLE
(
    ID BIGINT PRIMARY KEY,
    ...
    RowNum BIGINT UNIQUE
)
BEGIN
    ...
    RETURN
END
GO

I have try to name it like this, but is not working:

CREATE FUNCTION [dbo].[fn_name] (...)
RETURNS @Result
TABLE
(
    ID BIGINT PRIMARY KEY,
    ...
    RowNum BIGINT
    ,CONSTRAINT UC_fn_name_RowNum UNIQUE([RowNum])
)
BEGIN
    ...
    RETURN
END
GO

Is it possible to set the name of a unique constraint when it is part of table variable function definition?

Table Variables, whether created from a DECLARE or from a RETURNS in a CREATE FUNCTION statement, as well as User-Defined Table Types (UDTTs), do not allow for constraints to be named. According to the MSDN page for CREATE FUNCTION:

  Arguments
  ...
          < column_constraint >::= and < table_constraint>::=
          ... Named constraints are not allowed.

A "stated" exception to this rule is found on the MSDN page for DECLARE @local_variable which states (under the explanation of DEFAULT):

  To maintain compatibility with earlier versions of SQL Server, a constraint name can be assigned to a DEFAULT.

However, it appears that this is an error with the documentation as I am unable to assign a name to a DEFAULT Constraint, there is no description of the syntax to accomplish this, and there are no examples of doing this in the documentation (I looked as far back as SQL Server 2000).

When it comes to Temporary Tables the issue is a bit more straight forward: if you explicitly name any constraints, that would disallow the code from running in two different sessions at the same time. Constraints are objects (unlike Indexes) and need to have unique names within the same schema. Hence, if some code creates a local Temporary Table that has constraints that are given names, then if two sessions run that code at the same time, the second session would get an error when it went to create its Temporary Table due to the name conflict (which occurs in tempdb, where Temporary Tables exist). For example, run the following in one query tab in SSMS:

CREATE TABLE #Session1 (Col1 INT NOT NULL CONSTRAINT [UQ_DifferentSession] UNIQUE);

And in another query tab, run the following:

CREATE TABLE #Session2 (Col1 INT NOT NULL CONSTRAINT [UQ_DifferentSession] UNIQUE);

Even with the Temporary Tables having different names in addition to being local Temporary Tables created in different sessions, you will still receive the following error:

  Msg 2714, Level 16, State 5, Line 1
  There is already an object named 'UQ_DifferentSession' in the database.
  Msg 1750, Level 16, State 0, Line 1
  Could not create constraint. See previous errors.

Similar to Temporary Tables, Table Variables also do not have any definition until they are "declared" and then that definition only resides in tempdb. But we can't test for name collisions on Table Variables since you cannot name their constraints.

Returned tables in Multi-statement TVFs, and User-Defined Table Types, on the other hand, exist a little differently than Temporary Tables in that they store their definition in the database where they exist. But the meta-data they store in their local database is never actually used to store data; their meta-data is only used as a template for what will get created (when needed) in tempdb. And when that meta-data "template" is used, the named objects in tempdb (Primary Keys, Unique Constraints, Default Constraints, and Check Constraints) get dynamically generated names in the form of UQ__#BC4FB52__A259EE56FAB8BB6F.

Regarding the desire to name the constraints in order to be in line with naming conventions (as stated in a comment on the Question):

While it wouldn't hurt anything to use sp_rename (as pointed out in @Peter's answer), it would just be a superficial change. One of the main reasons to have a naming convention is for maintenance, so that if you need to ALTER or DROP the object later, you won't need to write queries to discover what is there and then concatenate that into Dynamic SQL. But that concern isn't relevant here since there is no way to ALTER or DROP them anyway.

Also, any name change done via sp_rename would be short-lived since any ALTER FUNCTION will drop and recreate the Constraints, and hence give them newly generated object names. This might could be handled programmatically with a DDL Trigger that captures CREATE FUNCTION, ALTER FUNCTION, and CREATE TYPE statements, but the end-result of that effort won't be that your life is any easier ;-). It would just be that selecting from sys.objects, sys.indexes, and sys.key_constraints looks "cleaner".


Missing data after attaching .mdf file Recently, one of my customers had a server crash. Unfortunately, the 3th party who was responsible for the backup was 'forgotten' to schedule a backup of the DB (I know, I should have notice that).

After a professional recovery, I have the .mdf and .ldf files back. I was able to attach them back to SQL Server 2005 Express. At first glance, everything looked ok.

However, the most recent changes (say from one month before crash) are missing. Has anyone seen that before?  Does anyone knows a solution for that? Is it possible that it is still in the .ldf?  I think the recovery model was Simple.

As a side note, I'm sure that the data was there since there is a printed version. I'm also sure that it is the most recent .mdf / .ldf that is recovered and sent.

I did already several DBCC checkDB with different parameters (every time on a fresh .mdf/.ldf combination of course). I know there is a consistency error somewhere but it doesn't block me to query the last 20000 rows. So expect to find the last records there (or I am wrong here?). The fix of this consistency error is lower prio since it occurs in the older data region. Worst case scenario, we can remove that part.

So, please help me with the missing data.

Thanks in advance!

Did you run a DBCC CHECKDB on the 'recovered' database? Is is consistent?

Your case doesn't hold water though. In theory the most you could had lost was the transactions present in the log (LDF) but not flushed in the data (MDF). That would amount, at most, to the dirty pages not flushed since the last checkpoint, which would be minutes of data loss, definitely not days.

  I'm also sure that it is the most recent .mdf / .ldf that is recovered and sent.

Being wrong on this point would be, by far, the simplest explanation. Other explanation is that you deleted the data after attach, which is kind of silly. Or the recovery returned a corrupted MDF that is missing data, but that would fail with corruption errors when trying to query it. I would double/triple check again that is indeed the latest mdf/ldf that was recovered.


The SQL Server Network Interface library could not deregister the Service Principal Name After windows patching (windows 2008 R2 , SQL Server 2012 Sp2). I could not start SQL Server Service . I get error 1067 and this from the error log* The SQL Server Network Interface library could not deregister the Service Principal Name (SPN) for the SQL Server service. Error: 0x13, state: 2. Administrator should deregister this SPN manually to avoid client authentication errors."
Yes it is a production system and any help is welcome. I tried using domain account that has sysadmin and local admin privilege.

Thanks

I ran in to same issue and I wander for about hours to fix it, finally I ended up repairing the existing database server 2016.

download SQL Server Installation setup package from this link:
SQL Server 2016_x64_ENU
Go to setup.exe >> Maintenance >> Repair

While repairing, error prompted saying it could not restart the servers, yu can cancel that and let the repair finish. Once finished, start the SQL Servers

Right click on start and go to Computer Management >> Services and Applications >> SQL Server Configuration Manager >> SQL Server Services >> right click on all the servers and hit on start.

This fixed my issue and was back on track. I am still looking for a better answer on why or what can cause that but for now, here's the fix!

Good luck guys!


SQL Server - How are data pages stored when using a clustered index I recently heard that the data pages in a clustered index aren't stored contiguously. Is this true?

Perhaps data pages are normally stored contiguously with some exceptions to the rule? Or perhaps I heard wrong and data page are always stored contiguously.

Many thanks.

Data pages are stored contiguously when the index is created and when the index is rebuilt. Otherwise, SQL Server will attempt to keep the pages in physical order. That not being possible, logical order is attempted. You can get gaps in a table because of other writes that are happening in the database. SQL Server uses B+ trees for indexes - the leaf (data) level of the index contains the data but is also a doubly linked list that contains pointers to the next and previous pages. This makes it less important that the table be 100% physically contiguous.


You can use a SUM(CASE) statement and a GROUP BY in this way:

select
    date,
    sum(case when category = 1 then price else 0 end) as blue,
    sum(case when category = 2 then price else 0 end) as red
from
    data d
group by
    date


SQL Server 2014: any explanation for inconsistent self join cardinality estimate? Consider the following query plan in SQL Server 2014:

In the query plan, a self-join ar.fId = ar.fId yields an estimate of 1 row. However, this is a logically inconsistent estimate: ar has 20,608 rows and just one distinct value of fId (accurately reflected in the statistics). Therefore, this join produces the full cross product of rows (~424MM rows), causing the query to run for several hours.

I am having a hard time understanding why SQL Server would come up with an estimate that can be so easily proven to be inconsistent with the statistics. Any ideas?

Initial investigation and additional detail

Based on Paul's answer here, it seems like both the SQL 2012 and SQL 2014 heuristics for estimating join cardinality should easily handle a situation where two identical histograms need to be compared.

I started with output from trace flag 2363, but was not able to understand that easily. Does the following snippet mean that SQL Server is comparing histograms for fId and bId in order to estimate the selectivity of a join that uses only fId? If so, that would obviously not be correct. Or am I misreading the trace flag output?

Plan for computation:
  CSelCalcExpressionComparedToExpression( QCOL: [ar].fId x_cmpEq QCOL: [ar].fId )
Loaded histogram for column QCOL: [ar].bId from stats with id 3
Loaded histogram for column QCOL: [ar].fId from stats with id 1
Selectivity: 0

Note that I have come up with several workarounds, which are include in the full repro script and bring this query down to milliseconds. This question is focused on understanding the behavior, how to avoid it in future queries, and determining whether it is a bug that should be filed with Microsoft.

Here is a full repro script, here is the full output from trace flag 2363, and here is the query and table definitions in case you want to quickly look at them without opening the full script:

WITH cte AS (
    SELECT ar.fId,
        ar.bId,
        MIN(CONVERT(INT, ar.isT)) AS isT,
        MAX(CONVERT(INT, tcr.isS)) AS isS
    FROM  #SQL2014MinMaxAggregateCardinalityBug_ar ar
    LEFT OUTER JOIN #SQL2014MinMaxAggregateCardinalityBug_tcr tcr
        ON tcr.rId = 508
        AND tcr.fId = ar.fId
        AND tcr.bId = ar.bId
    GROUP BY ar.fId, ar.bId
)
SELECT s.fId, s.bId, s.isS, t.isS
FROM cte s
JOIN cte t
    ON t.fId = s.fId
    AND t.isT = 1

CREATE TABLE #SQL2014MinMaxAggregateCardinalityBug_ar (
    fId INT NOT NULL,
    bId INT NOT NULL,
    isT BIT NOT NULL
    PRIMARY KEY (fId, bId)
)

CREATE TABLE #SQL2014MinMaxAggregateCardinalityBug_tcr (
    rId INT NOT NULL,
    fId INT NOT NULL,
    bId INT NOT NULL,
    isS BIT NOT NULL
    PRIMARY KEY (rId, fId, bId, isS)
)

I am having a hard time understanding why SQL Server would come up with an estimate that can be so easily proven to be inconsistent with the statistics.

Consistency
There is no general guarantee of consistency. Estimates may be calculated on different (but logically equivalent) subtrees at different times, using different statistical methods.
There is nothing wrong with the logic that says joining those two identical subtrees ought to produce a cross product, but there is equally nothing to say that choice of reasoning is more sound than any other.
Initial estimation
In your specific case, the initial cardinality estimation for the join is not performed on two identical subtrees. The tree shape at that time is:

  LogOp_Join
     LogOp_GbAgg
        LogOp_LeftOuterJoin
           LogOp_Get TBL: ar
           LogOp_Select
              LogOp_Get TBL: tcr
              ScaOp_Comp x_cmpEq
                 ScaOp_Identifier [tcr].rId
                 ScaOp_Const Value=508
           ScaOp_Logical x_lopAnd
              ScaOp_Comp x_cmpEq
                 ScaOp_Identifier [ar].fId
                 ScaOp_Identifier [tcr].fId
              ScaOp_Comp x_cmpEq
                 ScaOp_Identifier [ar].bId
                 ScaOp_Identifier [tcr].bId
        AncOp_PrjList
           AncOp_PrjEl Expr1003
              ScaOp_AggFunc stopMax
                 ScaOp_Convert int
                    ScaOp_Identifier [tcr].isS
     LogOp_Select
        LogOp_GbAgg
           LogOp_LeftOuterJoin
              LogOp_Get TBL: ar
              LogOp_Select
                 LogOp_Get TBL: tcr
                 ScaOp_Comp x_cmpEq
                    ScaOp_Identifier [tcr].rId
                    ScaOp_Const Value=508
              ScaOp_Logical x_lopAnd
                 ScaOp_Comp x_cmpEq
                    ScaOp_Identifier [ar].fId
                    ScaOp_Identifier [tcr].fId
                 ScaOp_Comp x_cmpEq
                    ScaOp_Identifier [ar].bId
                    ScaOp_Identifier [tcr].bId
           AncOp_PrjList
              AncOp_PrjEl Expr1006
                 ScaOp_AggFunc stopMin
                    ScaOp_Convert int
                       ScaOp_Identifier [ar].isT
              AncOp_PrjEl Expr1007
                 ScaOp_AggFunc stopMax
                    ScaOp_Convert int
                       ScaOp_Identifier [tcr].isS
        ScaOp_Comp x_cmpEq
           ScaOp_Identifier Expr1006
           ScaOp_Const Value=1
     ScaOp_Comp x_cmpEq
        ScaOp_Identifier QCOL: [ar].fId
        ScaOp_Identifier QCOL: [ar].fId

The first join input has had an unprojected aggregate simplified away, and the second join input has the predicate t.isT = 1 pushed below it, where t.isT is MIN(CONVERT(INT, ar.isT)). Despite this, the selectivity calculation for the isT predicate is able to use CSelCalcColumnInInterval on a histogram:

  CSelCalcColumnInInterval
      Column: COL: Expr1006

Loaded histogram for column QCOL: [ar].isT from stats with id 3

Selectivity: 4.85248e-005

Stats collection generated:
  CStCollFilter(ID=11, CARD=1)
      CStCollGroupBy(ID=10, CARD=20608)
          CStCollOuterJoin(ID=9, CARD=20608 x_jtLeftOuter)
              CStCollBaseTable(ID=3, CARD=20608 TBL: ar)
              CStCollFilter(ID=8, CARD=1)
                  CStCollBaseTable(ID=4, CARD=28 TBL: tcr)

The (correct) expectation is for 20,608 rows to be reduced to 1 row by this predicate.
Join estimation
The question now becomes how the 20,608 rows from the other join input will match up with this one row:

  LogOp_Join
      CStCollGroupBy(ID=7, CARD=20608)
          CStCollOuterJoin(ID=6, CARD=20608 x_jtLeftOuter)
              ...

      CStCollFilter(ID=11, CARD=1)
          CStCollGroupBy(ID=10, CARD=20608)
              ...

      ScaOp_Comp x_cmpEq
          ScaOp_Identifier QCOL: [ar].fId
          ScaOp_Identifier QCOL: [ar].fId

There are several different ways to estimate the join in general. We could, for example:

Derive new histograms at each plan operator in each subtree, align them at the join (interpolating step values as necessary), and see how they match up; or
Perform a simpler 'coarse' alignment of the histograms (using minimum and maximum values, not step-by-step); or
Compute separate selectivities for the join columns alone (from the base table, and without any filtering), then add in the selectivity effect of the non-join predicate(s).
...

Depending on the cardinality estimator in use, and some heuristics, any of those (or a variation) could be used. See the Microsoft White Paper Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator for more.
Bug?
Now, as noted in the question, in this case the 'simple' single-column join (on fId) uses the CSelCalcExpressionComparedToExpression calculator:

Plan for computation:

  CSelCalcExpressionComparedToExpression [ar].fId x_cmpEq [ar].fId

Loaded histogram for column QCOL: [ar].bId from stats with id 2
Loaded histogram for column QCOL: [ar].fId from stats with id 1

Selectivity: 0

This calculation assesses that joining the 20,608 rows with the 1 filtered row will have a zero selectivity: no rows will match (reported as one row in final plans). Is this wrong? Yes, probably there is a bug in the new CE here. One could argue that 1 row will match all rows or none, so the result might be reasonable, but there is reason to believe otherwise.
The details are actually rather tricky, but the expectation for the estimate to be based on unfiltered fId histograms, modified by the selectivity of the filter, giving 20608 * 20608 * 4.85248e-005  = 20608 rows is very reasonable.
Following this calculation would mean using the calculator CSelCalcSimpleJoinWithDistinctCounts instead of CSelCalcExpressionComparedToExpression. There is no documented way to do this, but if you are curious, you can enable undocumented trace flag 9479:

Note the final join produces 20,608 rows from two single-row inputs, but that should not be a surprise. It is the same plan produced by the original CE under TF 9481.
I mentioned the details are tricky (and time-consuming to investigate), but as far as I can tell, the root cause of the problem is related to the predicate rId = 508, with a zero selectivity. This zero estimate is raised to one row in the normal way, which appears to contribute to the zero selectivity estimate at the join in question when it accounts for lower predicates in the input tree (hence loading statistics for bId).
Allowing the outer join to keep a zero-row inner-side estimate (instead of raising to one row) (so all outer rows qualify) gives a 'bug-free' join estimation with either calculator. If you're interested in exploring this, the undocumented trace flag is 9473 (alone):

The behaviour of the join cardinality estimation with CSelCalcExpressionComparedToExpression can also be modified to not account for bId with another undocumented variation flag (9494). I mention all these because I know you have an interest in such things; not because they offer a solution. Until you report the issue to Microsoft, and they address it (or not), expressing the query differently is probably the best way forward. Regardless of whether the behaviour is intentional or not, they should be interested to hear about the regression.
Finally, to tidy up one other thing mentioned in the reproduction script: the final position of the Filter in the question plan is the result of a cost-based exploration GbAggAfterJoinSel moving the aggregate and filter above the join, since the join output has such a small number of rows. The filter was initially below the join, as you expected.