Is there a standard way to use common data and procedures across multiple databases? We have several independent databases that have data and code in common, not in the sense that it is accessed between the databases but in the sense that the data means the same thing in each database, and the code does the same thing.

Examples are:

Configuration settings (e.g. error codes 50xxx, boilerplate text for company name, etc).
Procedures and functions that perform common tasks (e.g. converting a CSV string into a table, logging an error, formatting an error message based on the error code).
Table structures (e.g. table for database version history, table for logging errors). As well as the columns, constraints, and triggers there are also common procedures and functions that read/write the data.
Look-up tables (e.g. date look-up table containing dates between 2000-2100). These are similar to table structures but quite often the data will be the same across the databases. In the case of the date table, the start and end dates are configuration settings, which are read by a function, then the date data is generated by a procedure. All these operations are common between the databases.
User defined types (e.g. types for passing tables to functions).

For maintenance and support reasons I think it makes sense for things like error codes, procedures, functions, and types to have a "single point of truth" across all the databases, not a different truth in each database.

At the moment each database has it's own copy of everything, including source repository, and we maintain them all independently. This is far from ideal because it's too easy to fix a procedure in A and forget to put it in B, or add an error code to A and the same one to B but they mean different things, etc.

The databases are not updated at the same time and they don't necessarily reside on the same hardware. There are cases where they can read data from each other (if the other one exists).

Is there a standard way of having a single point of truth for data/code that is used across more than one database?

In addition to Thomas' answer, for the things like common lookup data and generic functions, you can use synonyms.

USE CentralDB;
GO

CREATE TABLE dbo.Dates(...);
GO

USE OtherDB;
GO

CREATE SYNONYM dbo.Dates
  FOR CentralDB.dbo.Dates;

Now your other databases can treat these like first-class, local objects, but you only need to maintain one copy. Note that some functionality is missing, e.g. you can't set up foreign keys.

In a previous life I managed a system where we would have ~500 databases with nearly identical schema on each instance. We managed common data using synonyms, and deployment for code that had to be local using Red-Gate SQL Compare an SQL Farms Combine (very much like Multi-Script).


View the order of triggers firing We have 2 SQL Server 2008 environments with some triggers against a table containing business logic

Within the 2 environments, we can see that the triggers are firing in a different order (thanks to print statements in the triggers)

How can I VIEW the trigger firing order within each environment? I know I can use sp_settriggerorder to set them but we want to see what the differences are in the two environments

sp_settriggerorder only allows you to set first/last, nothing in between. How SQL Server will fire the middle ones (or all, if you haven't used that) is completely up to SQL Server, it may not even be deterministic, and how it will fire them the next time the table is touched is not stored anywhere. Even the documentation states that the order is "undefined"...

If the order of the trigger firing matters then I strongly recommend consolidating them and not having a bunch of separate triggers in the first place. Since the behavior is undefined, even if you observed one day that triggers were firing a,b,c - nothing would prevent SQL Server from firing them c,a,b the next day. If you have used  sp_settriggerorder to set first/last, you can see those manually using OBJECTPROPERTY as demonstrated in this answer. But if you have more than three, there is no way to predict the order of the ones you haven't marked first or last.


Query problem: there can be only one auto column Can someone please tell me what's wrong with this table definition.
mysql version is 5.1.52-log

root@localhost spoolrdb> create table spoolqueue (
                             queue int,
                             idx bigint not null auto_increment,
                             status smallint,
                             querystring varchar(2048),
                             contenttype varchar(255),
                             characterencoding varchar(16),
                             body text,
                             primary key(queue,idx)
                             );
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

This will apparently work with MyISAM as storage engine, not InnoDB, if you can live with that.

Another way to get it to work is if you swap places of queue and idx in the primary key declaration.


Weird order by behavior on SQL Server 2014 SP2 I am having an issue with a existing query running on a 2008 R2 instance, migrated to 2014 SP2 (No CU).

So far I have tried to use from position, alias, etc. So far I have narrow the event on the DATEPART any ideas?

SELECT DISTINCT CAST(DATEDIFF(HH,DATE_RECEIVED,TEST_DATE)  / 24.0 AS
DECIMAL(5,2))   AS TAT,
DATENAME(MONTH,TEST_DATE) AS MONTHNAME,
DATEPART(WW,TEST_DATE) AS WEEK,
DATEPART(YYYY,TEST_DATE) AS YEAR,
DATEPART(MM,TEST_DATE) AS MONTHNUMBER,
PRODUCT_CODE,TEST_PLAN_NAME,COUNT(PRODUCT_CODE) AS COUNT
FROM CO_SAMPLES
WHERE SAMPLE_TYPE IN ('CA')
AND PROFILE_NAME <> 'Shelf Life' AND RECLASS_NUMBER IS NULL
GROUP BY DATEPART(WW,TEST_DATE),
DATEPART(YYYY,TEST_DATE),
DATEPART(MM,TEST_DATE),
PRODUCT_CODE,TEST_PLAN_NAME,TEST_DATE,DATE_RECEIVED
ORDER BY  DATEPART(YYYY,TEST_DATE)
,DATEPART(MM,TEST_DATE)
,DATEPART(WW,TEST_DATE),PRODUCT_CODE

The return error is as follows

  Msg 145, Level 15, State 1, Line 1 ORDER BY items must appear in the
  select list if SELECT DISTINCT is specified.

It is because you need the entire definition for the [TAT] field in the Group By:

CAST(DATEDIFF(HH, DATE_RECEIVED, TEST_DATE) / 24.0 AS DECIMAL(5, 2))

Something like this:

SELECT DISTINCT
        [TAT] = CAST(DATEDIFF(HH, DATE_RECEIVED, TEST_DATE) / 24.0 AS DECIMAL(5, 2))
        , [MonthName] = DATENAME(MONTH, TEST_DATE)
        , [Week] = DATEPART(WW, TEST_DATE)
        , [Year] = DATEPART(YYYY, TEST_DATE)
        , [MonthNumber] = DATEPART(MM, TEST_DATE)
        , PRODUCT_CODE
        , TEST_PLAN_NAME
        , [Count] = COUNT(PRODUCT_CODE)
FROM    CO_SAMPLES
WHERE   SAMPLE_TYPE IN ( 'CA' )
        AND PROFILE_NAME <> 'Shelf Life'
        AND RECLASS_NUMBER IS NULL
GROUP BY CAST(DATEDIFF(HH, DATE_RECEIVED, TEST_DATE) / 24.0 AS DECIMAL(5, 2)) ,
        DATENAME(MONTH, TEST_DATE) ,
        DATEPART(WW, TEST_DATE) ,
        DATEPART(YYYY, TEST_DATE) ,
        DATEPART(MM, TEST_DATE) ,
        TEST_PLAN_NAME
ORDER BY [Year] ,
        [MonthNumber] ,
        [Week] ,
        PRODUCT_CODE;


Nulling a BLOB column in Oracle 11 impdp I need to import a 50 GB dump file containing three schemas. Over 90% of the file is taken up by a single BLOB column. How could I import the schemas, including the table containing the blobs, but replacing the values in the column with nulls? I could do a full import, null the column manually, do an export, delete the schemas and do a new import, but is there away to exclude a single column's data with impdp parameters?

Another option would be to do a datapump export of a view that has only the columns and data that you want. If you want a column to be null have the view make the column null. Then when you do the import you are only importing the data that you want.

expdp VIEWS_AS_TABLES


Is there a benefit to using FOR SHARE in this PostgreSql simple select? In the following PgSql stored function:

CREATE OR REPLACE FUNCTION get_offer_from_id(
        offer_id     bigint)
RETURNS json AS $$
DECLARE
    res "entity_data_result";
    r   "get_offer_data";
BEGIN
    FOR r IN SELECT * FROM "get_offer_data"
              WHERE "id" = offer_id
              LIMIT 1
              FOR SHARE
        LOOP
            res.found := true;
            res.data := row_to_json(r);
        END LOOP;
    RETURN row_to_json(res);
END; $$
LANGUAGE plpgsql;

is there a real benefit to using FOR SHARE or can one remove it from the query?

That depends on what else happens in the transaction.

With the for share there, nobody can update the selected get_offer_data row until the transaction commits or rolls back.

Without the context, it's really impossible to say what the original intended purpose of adding the lock there was.


PostgreSQL Recursive Descendant Depth I need to calculate the depth of a descendant from it's ancestor.  When a record has object_id = parent_id = ancestor_id, it is considered a root node (the ancestor). I have been trying to get a WITH RECURSIVE query running with PostgreSQL 9.4.

I do not control the data or the columns. The data and table schema comes from an external source. The table is growing continuously. Right now by about 30k records per day. Any node in the tree can be missing and they will be pulled from an external source at some point. They are usually pulled in created_at DESC order but the data is pulled with asynchronous background jobs.

We initially had a code solution to this problem, but now having 5M+ rows, it takes almost 30 minutes to complete.

Example table definition and test data:

CREATE TABLE objects (
  id          serial NOT NULL PRIMARY KEY,
  customer_id integer NOT NULL,
  object_id   integer NOT NULL,
  parent_id   integer,
  ancestor_id integer,
  generation  integer NOT NULL DEFAULT 0
);

INSERT INTO objects(id, customer_id , object_id, parent_id, ancestor_id, generation)
VALUES (2, 1, 2, 1, 1, -1), --no parent yet
       (3, 2, 3, 3, 3, -1), --root node
       (4, 2, 4, 3, 3, -1), --depth 1
       (5, 2, 5, 4, 3, -1), --depth 2
       (6, 2, 6, 5, 3, -1), --depth 3
       (7, 1, 7, 7, 7, -1), --root node
       (8, 1, 8, 7, 7, -1), --depth 1
       (9, 1, 9, 8, 7, -1); --depth 2

Note that object_id is not unique, but the combination (customer_id, object_id) is unique.
Running a query like this:

WITH RECURSIVE descendants(id, customer_id, object_id, parent_id, ancestor_id, depth) AS (
  SELECT id, customer_id, object_id, parent_id, ancestor_id, 0
  FROM objects
  WHERE object_id = parent_id

  UNION

  SELECT o.id, o.customer_id, o.object_id, o.parent_id, o.ancestor_id, d.depth + 1
  FROM objects o
  INNER JOIN descendants d ON d.parent_id = o.object_id
  WHERE
    d.id <> o.id
  AND
    d.customer_id = o.customer_id
) SELECT * FROM descendants d;

I would like the generation column to be set as the depth that was calculated.  When a new record is added, the generation column is set as -1. There are some cases where a parent_id may not have been pulled yet.  If the parent_id does not exist, it should leave the generation column set to -1.

The final data should look like:

id | customer_id | object_id | parent_id | ancestor_id | generation
2    1             2           1           1            -1
3    2             3           3           3             0
4    2             4           3           3             1
5    2             5           4           3             2
6    2             6           5           3             3
7    1             7           7           7             0
8    1             8           7           7             1
9    1             9           8           7             2

The result of the query should be to update the generation column to the correct depth.

I started working from the answers to this related question on SO.

The query you have is basically correct. The only mistake is in the second (recursive) part of the CTE where you have:

INNER JOIN descendants d ON d.parent_id = o.object_id

It should be the other way around:

INNER JOIN descendants d ON d.object_id = o.parent_id

You want to join the objects with their parents (that have already been found).

So the query that calculates depth can be written (nothing else changed, only formatting):

-- calculate generation / depth, no updates
WITH RECURSIVE descendants
  (id, customer_id, object_id, parent_id, ancestor_id, depth) AS
 AS ( SELECT id, customer_id, object_id, parent_id, ancestor_id, 0
      FROM objects
      WHERE object_id = parent_id

      UNION ALL

      SELECT o.id, o.customer_id, o.object_id, o.parent_id, o.ancestor_id, d.depth + 1
      FROM objects o
      INNER JOIN descendants d ON  d.customer_id = o.customer_id
                               AND d.object_id = o.parent_id
      WHERE d.id <> o.id
    )
SELECT *
FROM descendants d
ORDER BY id ;

For the update, you simply replace the last SELECT, with the UPDATE, joining the result of the cte, back to the table:

-- update nodes
WITH RECURSIVE descendants
    -- nothing changes here except
    -- ancestor_id and parent_id
    -- which can be omitted form the select lists
    )
UPDATE objects o
SET generation = d.depth
FROM descendants d
WHERE o.id = d.id
  AND o.generation = -1 ;          -- skip unnecessary updates

Tested on SQLfiddle

Additional comments:

the ancestor_id and the parent_id are not needed to be in the select list (ancestor is obvious, parent a bit tricky to figure out why), so you can keep them in the SELECT query if you want but you can safely remove them from the UPDATE.
the (customer_id, object_id) seems like a candidate for a UNIQUE constraint. If your data comply with this, add such a constraint. The joins performed in the recursive CTE would not make sense if it wasn't unique (a node could have 2 parents otherwise).
if you add that constraint, the (customer_id, parent_id) would be a candidate for a FOREIGN KEY constraint that REFERENCES the (unique) (customer_id, object_id). You most probably do not want to add that FK constraint though, since by your description, you are adding new rows and some rows can reference others that haven't been yet added.
There are certainly problems with the efficiency of the query, if it's going to be performed in a big table. Not in the first run, as almost the whole table will be updated anyway. But the second time, you'll want only new rows (and those that were not touched by the 1st run) to be considered for update. The CTE as it is will have to build a big result.
The AND o.generation = -1 in the final update will make sure that the rows that were updated in the 1st run will not be updated again but the CTE is still an expensive part.

The following is an attempt to address these issues: improve the CTE as to consider as few rows as possible and use (customer_id, obejct_id) instead of (id) to identify rows (so id is completely removed from the query.
It can be used as the 1st update or a subsequent:

WITH RECURSIVE descendants
  (customer_id, object_id, depth)
 AS ( SELECT customer_id, object_id, 0
      FROM objects
      WHERE object_id = parent_id
        AND generation = -1

      UNION ALL

      SELECT o.customer_id, o.object_id, p.generation + 1
      FROM objects o
        JOIN objects p ON  p.customer_id = o.customer_id
                       AND p.object_id = o.parent_id
                       AND p.generation > -1
      WHERE o.generation = -1

      UNION ALL

      SELECT o.customer_id, o.object_id, d.depth + 1
      FROM objects o
      INNER JOIN descendants d ON  o.customer_id = d.customer_id
                               AND o.parent_id = d.object_id
      WHERE o.parent_id <> o.object_id
        AND o.generation = -1
    )
UPDATE objects o
SET generation = d.depth
FROM descendants d
WHERE o.customer_id = d.customer_id
  AND o.object_id = d.object_id
  AND o.generation = -1        -- this is not really needed

Note how the CTE has 3 parts. The first two are the stable parts. The 1st part find the root nodes that haven't been updated before and have still generation=-1 so they must be newly added nodes. The 2nd part finds children (with generation=-1) of parent nodes that have previously been updated.
The 3rd, recursive part, finds all the descendants of the first two parts, as before.

Tested on SQLfiddle-2


Trouble with installing additional modules on Postgresql 9.1 on ALT-Linux I'm trying to create extension under postgresql 9.1 (Linux):

CREATE EXTENSION pg_trgm;

but got error:

ERROR:  could not open extension control file "/usr/share/pgsql/extension/pg_trgm.control": No such file or directory

This file exists and has r/w permissions for all users:

[root@postgresql extension]# pwd
/usr/share/pgsql/extension
[root@postgresql extension]# ls -l | grep pg_trgm
-rwxrwxrwx 1 root root  4977 Apr  6  2013 pg_trgm--1.0.sql
-rwxrwxrwx 1 root root  4230 Apr  6  2013 pg_trgm--unpackaged--1.0.sql
-rwxrwxrwx 1 root root   177 Apr  6  2013 pg_trgm.control

How to solve this problem?

In this particular situation what did help me:

It was needed just to copy extension directory from /usr/share/pgsql/extension/ to /var/lib/pgsql-root/usr/lib/pgsql/extension/.

That's all folks.


Scrapy data analyzed with R, mongoDB or other pipeline? I'm scraping data using the Scrapy library in python and i'm planning to make few analysis in the future using R.

In the future it could be a very big database with millions of items, what are the difference between using mongoDB or other databases? I've read the difference between storing in a SQL or NoSQL way but i can't decide which is more easy to process later in R.

I won't tell you what are the differences as there are too many.
I would recommend to go with SQL databases unless NoSQL tightly fits to your use case.
SQL has much more standardized interface and is much easier to integrate and query.

The most easiest to deal with and very performant database from R is sqlite. Package RSQLite ships sqlite libraries so there is no external dependencies required. You only need to install RSQLite which is available in CRAN.


understanding index fragmentation statistics I'm using the query outlined in this stack exchange answer, reproducing it here for convenience...

  Here is a simple query to check fragmentation on your existing
  indexes:

DECLARE @DBNAME VARCHAR(130);
SET @DBNAME = 'MYDBNAME';

DECLARE @DBID INT;
SET @DBID = DB_ID(@DBNAME);

SELECT
OBJECT_ID AS objectID
, index_id AS indexID
, avg_fragmentation_in_percent AS fragmentation
, page_count
INTO #indexDefragList
FROM
sys.dm_db_index_physical_stats
(@DBID, NULL, NULL , NULL, N'Limited')
WHERE
index_id > 0
OPTION (MaxDop 1);

SELECT
i.[name] as indexname,
d.fragmentation,
d.page_count
FROM
#indexDefragList d
INNER JOIN sys.indexes i
ON d.objectid = i.object_id
ORDER BY
d.fragmentation DESC

DROP TABLE #indexDefragList

  This will return a list of all indexes in your current DB with their
  fragmentation in %.

The output from this query is confusing me.

I've limited the results to cover just a single index to simplify my question...

Some indexes are listed once, others appear repeated in varying numbers with different statistics.

What I'm struggling to understand is, why are some indexes returning multiple rows with different fragmentation and page_count statistics.

Is this a result of some implementation detail of MS-SQL indexes, if so how should I reconcile the different fragmentation numbers? Can you explain these results to me please?

Or, is this a problem with my database somehow reproducing multiple versions of indexes when all i need is one? Is this a problem i need to worry about??

Or, is it a problem with the query?

Thanks x

Your query is slightly off, you need to include the index id in your join on sys.indexes

DECLARE @DBNAME VARCHAR(130);
SET @DBNAME = 'MYDBNAME';

DECLARE @DBID INT;
SET @DBID = DB_ID(@DBNAME);

SELECT
OBJECT_ID AS objectID
, index_id AS indexID
, avg_fragmentation_in_percent AS fragmentation
, page_count
INTO #indexDefragList
FROM
sys.dm_db_index_physical_stats
(@DBID, NULL, NULL , NULL, N'Limited')
WHERE
index_id > 0
OPTION (MaxDop 1);

SELECT
i.[name] as indexname,
d.fragmentation,
d.page_count
FROM
#indexDefragList d
INNER JOIN sys.indexes i
ON d.objectid = i.object_id
AND d.indexID = i.index_id
ORDER BY
i.object_id,d.fragmentation DESC

DROP TABLE #indexDefragList


How to prevent crashes connected with dropped tables during mysql import? I have made a mysql dump with mysqldump, tables are dropped and re-created with

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (

However for a read-intensive database I sometimes get crashes connected with table "users" missing completely - this must happen when table is dropped and immediately created again. How do I make mysql wait until table is created?

This is a big problem with the design of your system!

You are apparently performing DDL on a running application. This is never a good idea. The one Open Source server which does support transactional DDL is PostgreSQL - from here:

  You can't recover from an add/drop on a database or tablespace, but
  all other catalog operations are reversible.

MySQL even in its latest version (as of 05/2018) - verion 8, doesn't support tranactional DDL - from here:

  Some statements cannot be rolled back. In general, these include data
  definition language (DDL) statements, such as those that create or
  drop databases, those that create, drop, or alter tables or stored
  routines.

This page goes on to say:

  You should design your transactions not to include such statements. If you issue a statement early in a transaction that
  cannot be rolled back, and then another statement later fails, the
  full effect of the transaction cannot be rolled back in such cases by
  issuing a ROLLBACK statement. [Emphasis mine]

What, exactly, will, or will not, be rolled back isn't specified in detail - bit of a lottery situation which will require lots of testing! I wouldn't open that can of worms if I were you!

Could you possibly expand expand on your question by letting us know why, exactly, you feel that you need to issue such commands against a running application and maybe we can help you find a more suitable alternative solution?


Logging query text without Profiler I'm in the process of porting an old, semi-broken ASP.NET/SQL Server application to PHP/PostgreSQL. Among the torments^H^H^H^H^H^H^H^Hchallenges I'm faced with is that the reporting is done by a series of binary executables, because apparently I've been a bad person and need to be punished. So, I don't have access to the queries in order to port them.

That's fine, I thought. I can run the query profiler and log all SELECT statements that occur during the nightly report generation.

Except it turns out that the original developer shaved the cost down by running SQL Server Workgroup Edition, which doesn't include Profiler.

I was considering using triggers to get these queries, but I think I can't use triggers on SELECT statements. Reverse engineering the queries is possible, but given the complexity of the database (multiple, often recursive, relationships between tables, loads of similarly-named tables where several haven't been updated in years, and so on) I would prefer to have canonical queries to reference.

How should I go about this? Is there any way to get Profiler running in Workgroup Edition? Is there a way to enable query logging without the profiler? I'm not worried about performance because this app doesn't get particularly heavy use and it's running on a machine that's two sizes beefier than the app requires.

Several options:

You could use SQL Server Profiler against the Workgroup Edition server from a different client tools installation of SQL Server Developer, Standard, Enterprise or Evaluation Edition.
Use the semi-documented trace flag 4032, as explained by Tom LaRock.
Evaluate third party tools like the free DataWizard SQL Performance Profiler, xSQL Profiler (free for one instance), or ExpressProfiler (BSD Licence).

My preference would probably be option (1), downloading Evaluation Edition if necessary.


MySQL still holding memory after I shut it down? I used this command to shutdown MySQL, however it seems the amount of free memory on my box has not changed?

That seems to odd.

/usr/local/webserver/mysql/bin/mysqladmin -u ${mysql_username} -p${mysql_password} -S /data0/mysql/mysql.sock shutdown

Does anyone know why the free memory might not change very much after unloading MySQL?

Memory management is a complex beast. If you are on a linux or similar platform, you might be seeing differences because of the page cache. This is a good writeup of what the page cache is.


SQL query: insert values into polygon where features of the polygon intersects another polygon I'm very new to sql so I apologize in advance for my very dummy question...
I'm looking for a way to insert into a table A some values stored in another table B only for the features of table A that intersects features of table B.
I tried this:
insert into schema.table_A(columnKJ1)
select "columnPO4" from schema.table_B
WHERE table_A.geom.STintersects(table_B.geom) = 1

but I get this error message: cross-database references are not implemented
What is the right way to do that please ?

You're not allowed to reference table_A in the insert inside the select. The query needs to work stand-alone.
The following should work (untested):
insert into schema.table_A(columnKJ1)
select "columnPO4"
from schema.table_B
join schema.table_A on st_intersects(table_a.geom, table_B.geom);


Does mysql configuration options accepts - instead of _? Mysql 5.7 Documentation mentions that innodb_buffer_pool_instances can be set in /etc/mysql/mysqld.conf.d/mysqld.cnf as follows:

[mysqld]
innodb_buffer_pool_instances    = 16

However, an existing setup uses mysql configuration as follows (passed in /etc/mysql/mysqld.conf.d/mysqld.cnf):

[mysqld]
innodb-buffer-pool-instances    = 16

And it seems to be working.

Does mysql configuration options accept - instead of _ in variables or am I missing something here?

https://dev.mysql.com/doc/refman/8.0/en/command-line-options.html says:

  Within option names, dash (-) and underscore (_) may be used
  interchangeably. For example, --skip-grant-tables and
  --skip_grant_tables are equivalent. (However, the leading dashes cannot be given as underscores.)

This applies to options when you use them in option files, as well as command-line flags.

However, when using option variables in SQL queries, like in SET GLOBAL read_only=ON, you must use underscores only. Even putting the variable in back-ticks doesn't allow you to use hyphens.

mysql> set global `read_only`=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> set global `read-only`=ON;
ERROR 1193 (HY000): Unknown system variable 'read-only'

mysql> select @@`read_only`;
+---------------+
| @@`read_only` |
+---------------+
|             1 |
+---------------+
1 row in set (0.01 sec)

mysql> select @@`read-only`;
ERROR 1193 (HY000): Unknown system variable 'read-only'


Where to find the installer for SQL Server Native Client 11.0.7462? I would like the exact version mentioned since my application does not support the 11.0.5058 version of the SQL Server Native Client (sqlncli).

Here are the steps.

Download patch Description of the security update for SQL Server 2012 SP4 GDR: January 12, 2018 which is version 11.0.7464.
Only update native client (ignore other component) following these instructions.


Column to Row and Vice Versa I need to create a new table by combining the contents of two different tables. Is there any way to transform columns into rows and rows into columns in MySQL?

I tried using JOIN but I can't seem to transform a column into a row. Please refer to the image below for the task I need to do:

What you can do is to first unpivot your second table and then use conditional aggregation to pivot it again

SELECT c,
       MAX(CASE WHEN column_1 = 'xdata1' THEN value END) xdata1,
       MAX(CASE WHEN column_1 = 'xdata2' THEN value END) xdata2,
       MAX(CASE WHEN column_1 = 'xdata3' THEN value END) xdata3
  FROM
(
  SELECT t1.column_1,
         t2.c_id,
         c,
         CASE c
           WHEN 'c_1' THEN c_1
           WHEN 'c_2' THEN c_2
           WHEN 'c_3' THEN c_3
           WHEN 'c_4' THEN c_4
         END value
    FROM table1 t1 JOIN table2 t2
      ON t1.column_id = t2.c_id CROSS JOIN
  (
    SELECT 'c_1' c UNION ALL
    SELECT 'c_2'   UNION ALL
    SELECT 'c_3'   UNION ALL
    SELECT 'c_4'
  ) n
) q
 GROUP BY c

Output:

|   C |   XDATA1 |   XDATA2 |   XDATA3 |
|-----|----------|----------|----------|
| c_1 | a_data_1 | a_data_2 | a_data_3 |
| c_2 | b_data_1 | b_data_2 | b_data_3 |
| c_3 | c_data_1 | c_data_2 | c_data_3 |
| c_4 | d_data_1 | d_data_2 | d_data_3 |

Here is SQLFiddle demo


Is there any option to enable Window NT logs in SQL Server Logs I'm trying to view SQL Server logs on a specific server and is missing Windows NT section in same.
Though other server are showing that section.
In the screenshot shared below, I highlighted section that I'm not getting for one of the SQL Server instances.
Question

Is there any configuration or any required steps, for enabling Windows NT section in SQL Server Logs?

Server details with the issues:
Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)   Sep 24 2019 13:48:23
Copyright (C) 2019 Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

Server details having no issue or the screen server details
Microsoft SQL Server 2016 (SP2-CU11-GDR) (KB4535706) - 13.0.5622.0 (X64)   Dec 15 2019 08:03:11
Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2012 R2 Datacenter 6.3 <X64> (Build 9600: ) (Hypervisor)

I think this has the answer you are looking for. See this link to different same question Looks like a user needs membership in the Event Log Readers group or the local Administrators group.


Handling INSERT race condition for generated unique column value I am generating a unique slug during creation of a record like so (based in this answer)

INSERT INTO ks.shares (
    , slugbase
    , slugindex
    , slug
    , userid
)
SELECT
    , Pslugbase
    , COALESCE((SELECT(max(slugindex) + 1)
            FROM  ks.shares s
            WHERE s.slugbase = Pslugbase), 0)
    , Pslugbase
        || COALESCE((SELECT '-'::text || (max(slugindex) + 1)::text
            FROM  ks.shares s
            WHERE s.slugbase = Pslugbase), '')
    , Puserid
RETURNING id, modified, slug INTO Vnewshare;

When testing under load this can raise a unique_violation as two concurrent threads both assess what it he current slug index before one inserts a record with the next available slug, leaving the second thread with a now invalid slug.

I attempted to solve the issue by LOOPING over the insert and catching and unique constraint violations:

LOOP
BEGIN
    INSERT INTO ks.shares (
        , slugbase
        , slugindex
        , slug
        , userid
    )
    SELECT
        , Pslugbase
        , COALESCE((SELECT(max(slugindex) + 1)
                FROM  ks.shares s
                WHERE s.slugbase = Pslugbase), 0)
        , Pslugbase
            || COALESCE((SELECT '-'::text || (max(slugindex) + 1)::text
                FROM  ks.shares s
                WHERE s.slugbase = Pslugbase), '')
        , Puserid
    RETURNING id, modified, slug INTO Vnewshare;

    -- INSERT into two other tables

    RETURN json_build_object (
        'data',json_build_object (
            'id',lpad(Vnewshare.id::text,public.padding_constant(),'0'),
            'modified',Vnewshare.modified,
            'slug',Vnewshare.slug
        )
    );
    EXCEPTION WHEN unique_violation THEN
        -- do nothing, and loop to try the INSERT again
END;
END LOOP;

(I took this pattern from an answer on implementing UPSERT but can't find it!)

However, when I deployed this function my DB instance got stuck twice running long queries that involved this function being called concurrently, so I seemed to have somehow replaced a race condition with a deadlock or an infinite loop

pid  |    duration     |                                      query                                      | state
------+-----------------+---------------------------------------------------------------------------------+--------
8187 | 01:47:35.477316 | select ks.post_share($1::text,$2::text,$3::bigint) | active
1188 | 01:57:56.955747 | select ks.post_share($1::text,$2::text,$3::bigint) | active
(2 rows)

Is the approach to use a LOOP here the wrong way to go?
Is there any way to understand what is causing the query to get stuck? It does not happen during load testing but appeared twice on production (which seems counter intuitive)
Should I just return the conflict to the calling process and let the application handle retry?

Any thoughts would be greatly appreciated.

You don't mention the isolation level, but if it's run under the REPEATABLE READ isolation level, the SELECT part will never see the new values that other transactions inserted concurrently, so this code may be stuck in its loop forever.

  Should I just return the conflict to the calling process and let the
  application handle retry?

Yes, it would make sense. The key is to retry in a different transaction, so it gets a fresh new snapshot, plus it releases whatever locks that may be blocking other transactions.

Another approach would be to not use max(slugindex)+1, but a sequence instead, if the number part of the slug is globally unique, not unique per suffix. The answer you linked is based on the premise that they want abc-1 and xyz-1 as opposed to abc-1 and xyz-2, which is why a sequence is not an option. But it's not obvious that you share that requirement, looking at your SELECT.


Dynamically deciding which operator to use based on the type of the value at hand I'm currently implementing a translation between a proprietary expression language and postgres queries at work. So far a lot of stuff fell in place quite nicely, but now I have trouble with one function.
The source language contains a function (contains) that checks whether the first argument contains the second. That means for

strings: It checks whether the second arg is a substring of the first arg.
arrays: The second arg is an element of the array (first arg)
objects/maps/dicts: The second argument is a key of the object (first arg)

Now comes the issue: The translation doesn't know anything about the DB schema, so the queries need to be generic enough to work on different kinds of schemas. So I don't know whether a given field is of a specific type. It is okay to fail in some cases, but I would at least like the code to work for the types text/varchar , text[] and jsonb. But I'm struggling to get even the first two to work.
Here is the (incomplete) WHERE clause of a query that is generated by the translator:
CASE
    WHEN pg_typeof(entity.field1)::text = ANY('{text,character varying}') THEN ( entity.field1 LIKE '%' || entity.field2|| '%' )
    WHEN pg_typeof(entity.field1)::text = 'text[]' THEN ( entity.field2=ANY(entity.field1))
    [...]
    ELSE false
END

Let's assume (for now) that entity.field2 is a text column. entity.field1 is

a) a text column
b) a text[] column

The query works fine for a), as expected. But it fails for b) with:
operator does not exist: text[] ~~ text

So I broke the query down to:
CASE
    WHEN false THEN ( entity.field1 LIKE '%' || entity.field2 || '%' )
    ELSE false
END

For a) false is returned, as expected. For b) the error is still thrown. So it seems like the operator type check is run, even if there is no way to reach the code in the THEN clause.
I tried to find the reason for that in the docs but I didn't. This paragraph contains some info on early evaluation, but not related to operator type checks.
So, is there a way to get this to work by catching errors, casting (have tried naively casting to text, that works for text[] but not for jsonb), or anything else?
Thanks in advance for your help, it is greatly appreciated.

So after some helpful comments here and from my colleagues at work I ended up creating functions (with overloading) to solve the issue:
CREATE OR REPLACE FUNCTION contains(haystack jsonb, needle text) RETURNS boolean AS $$
    DECLARE
      result boolean;
    BEGIN
      CASE jsonb_typeof(haystack)
        WHEN 'array', 'object' THEN
          result := haystack ? needle;
        ELSE
          result := haystack LIKE '%' || needle || '%';
      END CASE;
      RETURN result;
    END;
  $$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION contains(haystack text, needle text) RETURNS boolean AS $$
  BEGIN
    RETURN haystack LIKE '%' || needle || '%';
  END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION contains(haystack text[], needle text) RETURNS boolean AS $$
  BEGIN
    RETURN needle = ANY(haystack);
  END;
$$ LANGUAGE plpgsql;

Then I can just include contains(entity.field1, entity.field2) in my query and it works for all three types I need right now. I can also easily extend it to work for more types.
Thanks for all your help


When Azure recommends an index (part of natural key) with all other columns included, should I switch to a clustered index for this natural key? The table does have an identity key (current CI), but it’s barely used to query. Because the natural key is not ever-increasing I’m afraid of insert performance, fragmentation or other problems that I don’t foresee now.

The table is not wide, with just a few columns. It has about 8 million rows and bringing our site to a halt during peak times. (+1000s concurrent users). The data is not easily cacheable, because it is quite volatile and essential that it’s up to date.

There are a lot of reads on one column of the natural key, but also quite active inserting and updating. Say 8 reads, vs 1 updates vs 1 inserts.

Id (PK)         int
UserId*         int
Key1*           varchar(25)
Key2*           varchar(25)
Key3*           int
LastChanged     datetime2(7)
Value           varchar(25)
Invalid         bit

* this combination is the natural primary key

I need to query the most of the time on:

All rows for one UserId (most queried)
All rows for a list of UserIds (a lot of rows)
All rows for a list of UserIds with Key1 = X
All rows for a list of UserIds with Key2 = X
All rows for a list of UserIds with Key1 = X and Key2 = X

I know the final answer is always “profile it”, but we are quite on a time constraint here so any guidance or experienced opinions in advance would really be appreciated.

Thanks in advance,

Unfortunately selecting a clustered index isn't just a matter of ever increasing. And insert performance and fragmentation aren't your only concerns.  Here are a few things to think about:

Your identity column is ever increasing, your natural key isn't. This will increase your insert time, but inserts are generally pretty quick and in a OLTP system they aren't so much less frequent as less visible.  For example doubling the time of a 4ms insert to 8ms and no one will notice.  Increase the time of a select statement from 30 seconds to a minute and people will see it.

It's a similar problem with fragmentation. It exists, it can cause problems, but it's a manageable problem at worst.

That said, are these your only indexes?  Because the clustered index keys are included in any non-clustered index.  So a wider set of keys will actually increase the size of your non-clustered indexes and slow them down. And your identity column is only 4 bytes wide.  Your natural key is 58 (assuming it's a unique key otherwise add 4 bytes for the uniquifier).

Ordered range scans can be very helpful, and are one of the big bonuses for the right clustered index.  That said, I don't see either of the indexes you mention above as candidates for range scans.  Those would be more for

SELECT * FROM table1 WHERE name BETWEEN 'Bob' and 'Mary'

You see them most with date data types, although queries similar to the above example happen too.

Also if you end up using your natural key as an NCI (non clustered index) then you might make sure it's a covering index for your main query(s).  In other words it contains (generally by using the INCLUDE statement) any extra columns needed by the query.  This means that SQL doesn't have to go back and check the CI (clustered index) and avoids an expensive lookup.

Last but certainly not least, do you have to worry about GDPR?  Do you have any data for people who are in the European Union?  If you do then you need to worry about natural keys in your scripts.  If someone's information ends up in a script then you will need to include that in your plans when you have to remove someone's data.

Here is a summary I wrote a while back.  It's more generic but might be helpful to look at:

https://sqlstudies.com/2016/08/29/natural-vs-artificial-primary-keys/

And a few other related posts:

https://sqlstudies.com/2014/12/01/using-a-date-or-int-column-as-the-clustered-index/
https://sqlstudies.com/2014/10/20/update-to-the-clustered-index-columns-are-in-all-of-the-non-clustered-indexes/

In the end though, you are right. You should test it out. That said, I would personally guess that keeping the identity as your CI is going to be your best bet with the following 2 non clustered indexes (based on the queries you mentioned above):

UserId, Key1, Key2
UserId, Key2

I didn't include Key3 because you didn't mention it and it adds unnecessary width to your index.  Beyond that, given the size of the table, you can include all of the non-key columns. Assuming that you have sufficient space for the indexes (which will be as big as the table is currently) it might improve your queries pretty significantly.  Of course any data you don't have to send to your application shouldn't be sent and will improve your performance (and reduce the size of your index if you don't have to add it to the include).


Most efficient way to cancel or avoid select in union query? I have a query with the following structure. In some specific cases the query will return data only from 1 or 2 of the tables.

For example:

DECLARE @Var1 int --Parameter in store procedure

SELECT several fields FROM T1 WHERE @Var IN(0,1) AND Conditions
 UNION
SELECT several fields FROM T2 WHERE @Var IN(0,1,2) AND Conditions
 UNION
SELECT several fields FROM T3 WHERE @Var IN(0,2) Conditions

My question is regarding performance. What is more efficient, using one scalar variable as in the previous example or using IF sentences and create specific queries as in the example below?

IF @Var = 0
    SELECT several fields FROM T1 WHERE Conditions
     UNION
    SELECT several fields FROM T2 WHERE Conditions
     UNION
    SELECT several fields FROM T3 WHERE Conditions
ELSE
 BEGIN
  IF @Var = 1
     SELECT several fields FROM T1 WHERE Conditions
       UNION
     SELECT several fields FROM T2 WHERE Conditions
  ELSE
     SELECT several fields FROM T2 WHERE Conditions
       UNION
     SELECT several fields FROM T3 WHERE Conditions
 END

I'd rather not to use the IF option because the query is quite big with several Fields and Conditions, however, I did check the execution plan of the first query and it shows an Index Scan.

I will appreciate any suggestion or comment

What is more efficient, using one scalar variable as in the previous example or using IF sentences and create specific queries as in the example below?

In SQL Server a query plan may not vary based on the parameters passed (unless you disable plan caching with OPTION RECOMPILE).  So whenever you want different plans, you need seperate queries.


Syncing a SQL Server table with a CSV file I have recently started a project with sensors that add data to a single CSV file as data the data. I would like to have this data stored in a SQL Server database in a relatively real-time manner. I know that CSV files can be imported but I am looking for a method that will sync data between the file and the database such that data automatically gets imported to the database as it is added to the CSV file.

I have looked around and found this post which suggests using SSIS to accomplish this. Having never used SSIS, I am not sure if it is the best method or if it even meets my requirements. Does someone have a good suggestion for how I can go about doing this and/or good resources for how to do this basic task in SSIS.

If it is relevant, I am using Microsoft SQL Server 2014 Standard edition on a Windows 7 desktop.

I have a partial answer - according to Microsoft's documentation it should work but I have not tested it. As I understand it you have a CSV file which is regularly updated, and you want the data to be available in a SQL Server 2014 database.

SQL Server 2012 onwards supports FILETABLE - this is a special type of table that maps onto folders and files in the file system. Each row in the filetable corresponds to an actual file, and updates to the physical files are reflected by updates to the rows in the filetable, and vice versa. See https://msdn.microsoft.com/en-us/library/ff929144(v=sql.120).aspx that describes FILETABLE. This Microsoft article further states that external to SQL Server, the filetable appears as a file share with files and directories. Another article https://technet.microsoft.com/en-us/library/gg492083(v=sql.110).aspx states you can use Windows explorer, or a command prompt to move and copy files into the filetable.

So assuming this works, you could create a SQL Server filetable that maps to the CSV file you want to load. You still have the problem of extracting the data from the filetable into a usable form in SQL Server. To do this, create a database view that retrieves the row from the filetable, converts it to a varchar(max), then uses a SQL Server split function with APPLY to split the string at line boundaries, then at column boundaries. A split function is discussed here https://stackoverflow.com/questions/20512855/split-function-by-comma-in-sql-server-2008 . SQL Server 2016 has an inbuilt string split function, although you are not using that version.

The result should be you have a database view, which gets the current state of the CSV file from the filetable (file system) and converts it to tabular form. By using the view you should always have an up to date version of the data from the CSV file.

DIsclaimer: As stated I have not tested this, so feel free to amend or delete if it's known to be incorrect.


SSIS package randomly fails with "WriteToServer requires an open and available Connection" error Our SSIS package randomly fails with the following error message:

An exception has occurred during data insertion, the message returned
from the provider is: WriteToServer requires an open and available
Connection. The connection's current state is closed.

Every time it's a different step, sometimes the package runs w/o any issue for a few days in a row, but then suddenly fails. There were no changes introduced in the package itself nor to SQL Server configuration. The only thing which might change is basically the amount of data, it gradually increases. We are running Integration Services in SQL Server 2016 (it's installed on the same machine along with SQL Server). There is nothing suspicious on the SQL Server side, when the SSIS failure occurs the other apps are connecting to SQL Server with no issues, the SQL error log is empty.
Has anyone spotted anything similar? Any tips on how to troubleshoot this? I am not a big fan or expert in SSIS :) so I really count on your expertise!

In my experience I have only removed the package and republished it ...


Detecting Logical Replication Error on Postgres v10 on AWS RDS CloudWatch metrics https://www.postgresql.org/docs/10/logical-replication-restrictions.html
States:
"When the schema is changed on the publisher and replicated data starts arriving at the subscriber but does not fit into the table schema, replication will error until the schema is updated"

How do we detect/query for these errors on Postgres?

I tried

SELECT * FROM pg_stat_subscription;

but it shows that it received the publisher's message. It errored out when I changed publisher's schema and subcriber stopped getting updates.

on AWS,  the RDS cloudwatch metrics related to it is:

OldestReplicationSlotLag (The lagging size of the replica lagging the most in terms of WAL data received. Applies to PostgreSQL)

but I don't think this is the right metric if I want to know if my updates have corrected applied to the subscriber replica database.

Through trial and error I found out that you can use OldestReplicationSlotLag.

If there are replication errors,  it accumlates the slotlag bytes


How to diagnose try_parse being very slow? I had a very large query that ran slower than I thought it should do, but no amount of digging through the query execution plan helped shed any light on the slowness. Eventually I narrowed it down though: try_parse was the culprit!

Normal query:

SELECT CloseDate
FROM MyTable

(4959 row(s) affected)

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 17 ms.

With try_parse:

SELECT try_parse(CloseDate as datetime using 'en-us')
FROM MyTable

(4959 row(s) affected)

SQL Server Execution Times:
   CPU time = 719 ms,  elapsed time = 718 ms.

The execution plan in the latter case looks innocent enough:

Is there a way I can spot the culprit more easily in the future? The actual source of slowness is completely hidden from view.

CPU time = 719 ms,  elapsed time = 718 ms.

Shows that this is CPU bound.

If you can reproduce the issue on a dev machine one way of seeing what the CPU is spending time on is to use Windows Performance Recorder.

After tracing for a few seconds whilst the following was running concurrently...

SET nocount ON;

DECLARE @d DATETIME

WHILE 1 = 1
  SELECT @d = try_parse(NAME AS DATETIMEusing 'en-us')
  FROM   master..spt_values

... I see (click to embiggen)

SQL Server is attributed 20.88% of total CPU time over that period. Over 75% of that amount is taken up with

SqlAccess.dll!System.Data.SqlServer.Internal.SqlParseIntrinsicImpl::<ParseSsDate>
SqlAccess.dll!System.Data.SqlServer.Internal.SqlAppDomain::ExecuteExtension

With a healthy chunk of that taken up with

clr.dll!IL_Throw
mscorlib.ni.dll!System.DateTimeParse.GetDateTimeParseException(System.DateTimeResult ByRef)

None of the names in master..spt_values happen to be parsed as valid dates so all end up returning null.

The above shows that for some reason TRY_PARSE calls the DateTime.Parse method and catches the exception rather than using the built in TryParse method that would likely perform better in this case.


Merge two rows into one row So I have a table that looks like this:

And here's my query for that table:

select b.Barcode,a.ItemCode,b.Color,b.Size,b.Class,d.Locationcode,c.price,
SUM(case when e.description = 'Selling Quantity' and d.LocationCode = e.LocationCode                 then 1 else 0 end) as 'Selling Qty',
SUM(case when e.description = 'Stock Quantity' then 1 else 0 end) as 'Stock Quantity'
from masterfile.item a
left join masterfile.itemdetail b on a.ItemCode = b.ItemCode
left join masterfile.itemprice c on a.ItemCode = c.Itemcode and
b.Color = c.ItemColor and b.Size = c.ItemSize and b.Class = c.ItemClass
left join masterfile.locationinv d on a.itemcode = d.itemcode and
c.ItemColor = d.Color and c.ItemSize = d.Size and c.ItemClass = d.Class
left join masterfile.location e on d.locationcode = e.locationcode
group by b.Barcode,a.itemcode,b.color,b.size,b.class,d.locationCode,c.price

What I want is to merge these two rows, so what will happen is that selling qty and stock qty will have both 1 value because the two rows were merged.

Locationcode 001 is for selling and 002 is for stock quantity.

I want to make it look like this:

   Barcode       ItemCode      Color Size Class LocCode1 LocCode2 Price  Sell Qty   Stock Qty
   888200000001 W09118-DP28U    N/A   29    A      001      002     200      1          1

I'm kinda clueless with merging stuffs like this, so thanks in advance!

I just removed the locationcode in select and group by clause so they will join eventually and that solved my problem.

select b.Barcode,a.ItemCode,b.Color,b.Size,b.Class,c.price,
SUM(case when e.description = 'Selling Quantity' and d.LocationCode = e.LocationCode         then 1 else 0 end) as 'Selling Qty',
SUM(case when e.description = 'Stock Quantity' then 1 else 0 end) as 'Stock Quantity'
from masterfile.item a
left join masterfile.itemdetail b on a.ItemCode = b.ItemCode
left join masterfile.itemprice c on a.ItemCode = c.Itemcode and
b.Color = c.ItemColor and b.Size = c.ItemSize and b.Class = c.ItemClass
left join masterfile.locationinv d on a.itemcode = d.itemcode and
c.ItemColor = d.Color and c.ItemSize = d.Size and c.ItemClass = d.Class
left join masterfile.location e on d.locationcode = e.locationcode
group by b.Barcode,a.itemcode,b.color,b.size,b.class,c.price


Connection is Using Properly or not in Net Use via SQL Procedure am doing an operation of creating a file and writing in a SAN SAMBA where it will be done using the cmd_shell via SQL Procedure. It's creating and writing it successfully but while am calling that procedure 2 two times at the same time am getting the issue as

  The local device name is already in use.

Is there any options while using this net use command I have 15 different procedures each will be having this type of behavior.

Note: Every Procedure is given a Drive name differently.

  EXEC xp_cmdshell  'net use r: \10.44.11.44\dpsshare Password
  /user:Username /persistent:no'

Am also deleting the Network Connection at the end of the Procedure.

Your NET USE command tells Windows - not just SQL Server - to map a network drive to a letter. When it runs, it makes that drive letter available to the SQL Server process, where it can be used for all connections.

A lot of things in SQL Server are per-connection - like creating a temp table - but what you're doing, mapping a network drive to a letter, is for the entire SQL Server process.

Your first thought might be, "Oh, so I need to check to see if the folder is mapped first - like use xp_dirtree to get the directory contents. If the folder is already mapped, then I can skip that NET USE step."

Unfortunately, that won't work either.

Because you mentioned at the end of your procedures, you're un-mapping those drives. That means you can run into this situation:

Instance A of the stored proc starts, checks to see if the drive is mapped. It isn't, so it maps the drive, and starts working.
Instance B of the stored proc starts, checks to see if the drive is mapped. It IS already mapped, so it just starts working without creating the drive mapping.
Instance A finishes up, and unmaps the drive.
Instance B was still running - but it now can no longer write to the drive. It fails.

So how do you work around it? Don't map drive letters. Access folders by their UNC path instead, like \\10.44.11.44\dpsshare. Grant the necessary permissions to the SQL Server's account rather than hard-coding usernames and passwords inside your application code. (Security is a much bigger discussion too - I get nervous when I see you exposing passwords in your code, and I also get nervous about SQL Server being able to write to a remote file share. You're in an area where application servers are a much better fit.)


How to force users to specify a filegroup when creating tables/indexes I will soon be migrating to SQL Server 2017 and hence am working on beautifying some neglected aspects of our databases. One aspect is filegroups: my DBA predecessor has created multiple filegroups however as no one was really told about most objects just go to the default filegroup (PRIMARY).

Now I would like to enforce that people creating tables and indexes actually name the filegroup where their objects should reside. Meaning: A CREATE TABLE or CREATE INDEX without an "ON [Filegroup]" should roll back with an error that the filegroup is missing. Is there any way to do this an would you recommend proceeding with such an approach? I have just come across that policy based management does not work in preventing objects being created on the default filegroup.

Main point of doing this is that someboy should choose the filegroup for the objects in the first place instead of creating them in the default filegroup and afterwards have to move the objects based on rants by the DBA :-).

I thought about two techniques...however can't get started with either of them because of missing knowledge:

Use database triggers to check if there is an on clause and if not roll back --> this seems to be quite fragile as it would depend on text analysis of the command text
Make the primary filegroup really small in size (100KB?) in order for system tables still being able to fit in but other tables of moderate size would result in an error because there is not enough space left --> probably a bad idea as this could have severe side-effects on database availability and is not precise enough in preventing every user defined table or index to be created within the default filegroup

Thank you very much in advance for your help

Martin

You could possible create a new default filegroup with the Read_Only property set.

Add database file:

USE [StackExchange]
GO
ALTER DATABASE [StackExchange] ADD FILE ( NAME = N'StackExchange_DefRO', FILENAME = N'C:\SQL\SQL_DATA\StackExchangeRO.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) TO FILEGROUP [DEFAULTRO]
GO

Modify the new filegroup to READONLY:

USE [master]
GO
declare @readonly bit
SELECT @readonly=convert(bit, (status & 0x08)) FROM sysfilegroups WHERE groupname=N'DEFAULTRO'
if(@readonly=0)
    ALTER DATABASE [StackExchange] MODIFY FILEGROUP [DEFAULTRO] READONLY
GO

Modify new filegroup to be DEFAULT:

GO
USE [StackExchange]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'DEFAULTRO') ALTER DATABASE [StackExchange] MODIFY FILEGROUP [DEFAULTRO] DEFAULT
GO

If a user now creates an object without specifying a filegroup:

USE [StackExchange]
GO

/****** Object:  Table [dbo].[NewTable]    Script Date: 21.12.2017 14:12:11 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[NewTable](
    [ID] [int] NULL,
    [Test] [nchar](10) NULL
)

GO

They will receive the error message:

Msg 1924, Level 16, State 2, Line 11
Filegroup 'DEFAULTRO' is read-only.


Is there a way to set bind_address from command line in mysql 5.5 without restarting? I made a modification to bind_address in my.cnf that knocked down a monitor. It has been repaired since, firewall allowing the monitor IP in. However, mysql is still running in its previous state since restart.

In MySQL 5.5, there does not seem to be a bind_address variable available from CLI (unless I'm missing something). Is there a way I can set bind_address from CLI so I don't have to restart mysql?

In a word, no. Why?

MySQL Documentation on bind_address says right in the beginning

Command-Line Format --bind-address=addr
System Variable (>= 5.6.1)  Name    bind_address
Variable Scope  Global
Dynamic Variable    No
Permitted Values (<= 5.6.5) Type    string
Default 0.0.0.0
Permitted Values (>= 5.6.6) Type    string
Default *

It's a global variable in MySQL 5.6, but it is not dynamic.

Since you are using MySQL 5.5, it is only a startup option.

Therefore, you must bite the bullet and restart mysqld.


What is the order when SELECT-ing unordered set from an ordered SELECT? What does the SQL standard and its mainstream DB implementations (SQL Server, Oracle, DB2, PostgreSQL, Firebird, MySQL, etc.) say about the final order in the following general case:

SELECT * FROM (SELECT * FROM Table t ORDER BY t.Field)

Can I trust that the inner order is respected by the outer projection as well or do I need to state the ORDER BY in the outer statement explicitly, to be sure?

I understand the recommendation to "explicitly list it". However, explicit listing can cause problems in certain cases. PostgreSQL, for one, requires that all fields listed in ORDER BY be listed in SELECT DISTINCT as well. That's no good in some cases, because it changes the expected result set, which could no longer be distinct in light of the single column I'm interested in.

Can I trust that the inner order is respected by the outer projection as well ...?

No, you cannot.

  ... or do I need to state the ORDER BY in the outer statement explicitly, to be sure?

Yes, you do need to state the ORDER BY you need in the outer statement explicitly, if you want to be sure that the order that the rows are returned is what you want.

While you may - more often in old versions of some DBMS - sometimes find that the order of the rows returned is the one specified in the internal ORDER BY, there is no guarantee about it. If you want a specific order, you need to be explicit.

Related / similar questions:

Why is SSMS inserting new rows at the top of a table not the bottom?
What is the default order of records for a SELECT statement in MySQL?
In SQL Server, can I guarantee an order without an explicit ORDER BY clause when an index seek is forced on a table with only a clustered index?
Is it REALLY possible that the order will not be guaranteed for this particular redundant derived table?

Quotes from the SQL standard:

  Cursors
  ...
  A cursor in the open state identifies a result set and a position relative to the ordering of that result set. If the cursor's <cursor specification> does not simply contain an <order by clause>, or simply contains an <order by clause> that does not specify the order of the rows completely, then the rows of the result set have an order that is defined only to the extent that the <order by clause> specifies an order and is otherwise implementation-dependent.

  <query expression>
  ...
  5) The rows of T are ordered as follows:

  a) If QE does not immediately contain an <order by clause>, then the ordering of rows in T is implementation-dependent.


Does SQL Server automatically update the foreign key relationships between tables when one of those tables is renamed? What happens to the foreign key relationships on a table if that table is renamed?

Renaming a table does not change the underlying object_id for that table.  Foreign Key relationships are tracked using object_id's.

Take a look at the information in select * from sys.foreign_keys.

Assuming the following parent/child tables

DROP TABLE IF EXISTS [dbo].[QueueDatabase];
DROP TABLE if exists [dbo].[Queue];

GO
CREATE TABLE [dbo].[Queue](
    [QueueID] [int] IDENTITY(1,1) NOT NULL,
    [SchemaName] [sysname] NOT NULL,
    [ObjectName] [sysname] NOT NULL,
    [Parameters] [nvarchar](max) NOT NULL,
    [QueueStartTime] [datetime] NULL,
    [SessionID] [smallint] NULL,
    [RequestID] [int] NULL,
    [RequestStartTime] [datetime] NULL,
 CONSTRAINT [PK_Queue] PRIMARY KEY CLUSTERED
(
    [QueueID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[QueueDatabase](
    [QueueID] [int] NOT NULL,
    [DatabaseName] [sysname] NOT NULL,
    [DatabaseOrder] [int] NULL,
    [DatabaseStartTime] [datetime] NULL,
    [DatabaseEndTime] [datetime] NULL,
    [SessionID] [smallint] NULL,
    [RequestID] [int] NULL,
    [RequestStartTime] [datetime] NULL,
 CONSTRAINT [PK_QueueDatabase] PRIMARY KEY CLUSTERED
(
    [QueueID] ASC,
    [DatabaseName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[QueueDatabase]  WITH CHECK ADD  CONSTRAINT [FK_QueueDatabase_Queue] FOREIGN KEY([QueueID])
REFERENCES [dbo].[Queue] ([QueueID])
GO

ALTER TABLE [dbo].[QueueDatabase] CHECK CONSTRAINT [FK_QueueDatabase_Queue]
GO

Now, begin a transaction.  Select object_id's and object_names before and after executing sp_rename.  The object_names change, but the underlying object_id's remain the same.

begin transaction
--select object_id's and names before table rename
SELECT parent_object_id AS child_table_id
    ,object_name(parent_object_id) AS child_table
    ,referenced_object_id AS parent_table_id
    ,object_name(referenced_object_id) AS parent_table
FROM sys.foreign_keys

--rename the table
exec sp_rename 'dbo.queuedatabase','newqueuedatabase'

--select object_id's and names after table rename
SELECT parent_object_id AS child_table_id
    ,object_name(parent_object_id) AS child_table
    ,referenced_object_id AS parent_table_id
    ,object_name(referenced_object_id) AS parent_table
FROM sys.foreign_keys

--rollback

--before

| child_table_id | child_table   | parent_table_id | parent_table |
|----------------|---------------|-----------------|--------------|
| 1100687119     | QueueDatabase | 1068687005      | Queue        |

--after

| child_table_id | child_table      | parent_table_id | parent_table |
|----------------|------------------|-----------------|--------------|
| 1100687119     | newqueuedatabase | 1068687005      | Queue        |

Additionally, it should be noted that nothing magical happens to the name of the foreign key (assuming you used table names as part of the foreign key name)

Selecting from sys.foreign_keys reveals that the name remains the same even though it's now a little confusing since there is no table called QueueDatabase after the rename.

FK_QueueDatabase_Queue


What is an orphan incarnation? Incarnations are explained in an answer to another question on this site. The answer mentions 'orphaned' incarnations:

  …There are other factors that result in ORPHANED incarnations and OBSOLETE backups…

I see from the Oracle docs that V$DATABASE_INCARNATION includes a STATUS column which can have values of ORPHAN, CURRENT or PARENT, which must be related.

What are 'orphaned' incarnations, and what steps would result in a row with STATUS=ORPHAN in V$DATABASE_INCARNATION?

Following is a short graphic which I will be using to explain when orphans are created in the incarnations of a database. It is a variation of the graphic I used to explain incarnations in my answer to the question Can anyone explain to me the concept “incarnation” in Oracle database in an easy-to-understand way?

I hope you enjoy the journey.

                                          restore db    +-----+     +-----+     +-----+
                                          recover db    | 2>3 | --> |  3  | --> |  3  | -->  ...
                                          resetlogs     +-----+     +-----+     +-----+  ^
                                                            ^ Incarn   3           3     |    3
                                                           /  SCN #   500         600    |   700
                                                          /                              |
                                                         /                               |
             restore db    +-----+          +-----+     +-----+                          |
             recover db    | 1>2 | -------> |  2  | --> |  2  | -->  ...                 |
             resetlogs     +-----+          +-----+     +-----+  ^                       |
                           ^       Incarn.     2 \         2     |    2                  |
                          /        SCN #      300 \       400    |   500                 |
                         /                         \             |                       |
                        /                           + --------------------+              |
        +-----+     +-----+     +-----+                          |         \    +-----+  |  +-----+
    --> |  1  | --> |  1  | --> |  1  | -->   ...                |          +-> | 2>4 | --> |  4  |
        +-----+     +-----+     +-----+  ^                       |   restore db +-----+  |  +-----+
Incarn.    1           1           1     |     1           2     |   recover db          |     4
SCN #     100         200         300    |    400         400    |   resetlogs           |    400
                                         |                       |                       |
Backup   11:00 ----- 12:00 ----- 13:00 ----- 14:00 ----- 15:00 ----- 16:00 ----- 17:00 ----- 18:00
                                         |                       |                       |
Restore/                                (1)                     (2)                     (3)
Recovery

Restoring the Database to Point in Time (1)

Somewhere slightly after 13:00 (1pm)  somebody decides that the database has to be restored to 12:00 (12 o'clock midday). The DBA either sets off a bunch of RMAN commands to restore the database to that point in time or clicks his way through a fantastic GUI to initiate a restore/recovery from a 3rd-party vendor.

RMAN retrieves the FULL backup of the database and all Archive Log backups from disk/tape and restores them to the disk. In the recovery phase RMAN will check that all relevant information is available and roll forward all finished transactions to the Point in Time and roll back all unfinished transactions to the Point in Time, to ensure the database is in a consistent state.

Before the database can be opened to the general public, the database has to ensure that all future backups don't conflict with the previous backups. This is when a new incarnation should be created and it happens when you execute the following command to open the database:

ALTER DATABASE OPEN RESETLOGS;

You can run the following script against your instance to retrieve a hierarchical view of your (current) incarnations:

set pages 50               --- repeat header every 50 records
set lines 230              --- set lines(ize) length to 230
column path format a40     --- set column path to alpha-numeric 40
alter sessiosn set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
                           --- set date format of date columns to something more detailed
select
    INCARNATION#,
    PRIOR_INCARNATION#,
    RESETLOGS_CHANGE#,
    RESETLOGS_TIME,
    STATUS,
    SYS_CONNECT_BY_PATH(INCARNATION#, ' -> ') Path
    FROM v$database_incarnation
    WHERE LEVEL >=1 START WITH INCARNATION# = '1'
        CONNECT BY PRIOR INCARNATION# = PRIOR_INCARNATION#
    ORDER BY LEVEL, Path, RESETLOGS_TIME;

The current incarnation of the database will be similar to this:

INCARNATION# PRIOR_INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME      STATUS  PATH
------------ ------------------ ----------------- ------------------- ------- --------------------
           1                  0                 1 2017-03-08 15:57:31 PARENT   -> 1
           2                  1               200 2018-07-27 13:20:00 CURRENT  -> 1 -> 2

Using the graphic we can see that we have moved from the path containing the incarnation 1 to the path with the incarnation 2, because we have opened the database with RESETLOGS and the database has created a new incarnation.

Restoring the Database to Point in Time (2)

Let's again assume the database keeps on running after the first restore/recovery action and slightly after 15:00 (3pm) somebody decides there needs to ba a new restore/recovery back to full hour at 15:00 (3pm) the same day.

RMAN will restore the files, recover the database and set off an ALTER DATABASE OPEN RESETLOGS to bring the database back online. The INCARNATION# will be now set to 3 and the first backup at 16:00 will contain the information:

INCARNATION#    3
SCN#           500
Time......... 16:00

If we query the incarnations in the database using the above script we will get something like this:

INCARNATION# PRIOR_INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME      STATUS  PATH
------------ ------------------ ----------------- ------------------- ------- --------------------
           1                  0                 1 2017-03-08 15:57:31 PARENT   -> 1
           2                  1               200 2018-07-27 13:20:00 PARENT   -> 1 -> 2
           3                  2               400 2018-07-27 15:20:00 CURRENT  -> 1 -> 2 -> 3

Restoring the Database to Point in Time (3)

Let's again assume the database keeps on running after the second restore/recovery action and slightly after 17:00 (5pm) somebody decides there needs to be a new restore/recovery back to 14:00 (2pm) the same day.

RMAN will restore the files, recovery the database and set off an ALTER DATABASE OPEN RESETLOGS to bring the database back online. The INCARNATION# will be now set to 4 and the first backup at 18:00 will contain the information:

INCARNATION#    4
SCN#           400
Time......... 18:00

If we query the incarnations in the database using the above script we will get something like this:

INCARNATION# PRIOR_INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME      STATUS  PATH
------------ ------------------ ----------------- ------------------- ------- --------------------
           1                  0                 1 2017-03-08 15:57:31 PARENT   -> 1
           2                  1               200 2018-07-16 13:20:00 PARENT   -> 1 -> 2
           3                  2               400 2018-07-17 15:20:00 ORPHAN   -> 1 -> 2 -> 3
           4                  2               300 2018-07-17 17:20:00 CURRENT  -> 1 -> 2 -> 4

What has happened? We have an orphan!

Orphaned Incarnations...

If you look at the graphic, we are currently standing on the square at 18:00 (6pm) with the Incarnation 4 and the SCN 400. Now if you follow that line back to the beginning, you can see that we would go from incarnation 4 back up to incarnation 2 and then back down to incarnation 1, which is when the database was created.

This also corresponds to the (simplified) output of my scripts.

INCARNATION# PRIOR_INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME      STATUS  PATH
------------ ------------------ ----------------- ------------------- ------- --------------------
           4                  2               300 2018-07-17 17:20:00 CURRENT  -> 1 -> 2 -> 4

So what happened with incarnation 3? Is the Incarnation 3 bad or stale or what gives?

Answer

No, the incarnation 3 isn't bad, it's just orphaned.

On a larger scale with more time between backups and restores, you could still restore/recover the database to a point in time in the lineage of incarnation 3. You would set off the following command:

RESET DATABASE TO INCARNATION 3;

...and then restore/recover the database to that point in time like you would other restore/recover a database.

What the ORPHAN status does tell you, is that the incarnation 3 is no longer related to the current state of the database with the current incarnation 4. The orphaned incarnation 3 is no longer required to restore/recover the database along the current timeline.

...Result in Obsolete Backups

Now looking at the database backups in relation to the orphaned incarnation, well RMAN determines that the orphaned incarnation's backups are OBSOLETE. But that is a story for a different Q & A...


Indexed View - how to verify if it is worthy? I have the following indexed view:

CREATE view VProcessStatus
WITH SCHEMABINDING
AS
select processstatus,
      StatusCount = COUNT_BIG(*)
      FROM DBO.tbltaxtransaction
      GROUP BY processstatus

CREATE UNIQUE CLUSTERED INDEX IDX_VProcessStatus ON VProcessStatus (processstatus)

I have created it on a different database file, different filegroup.

This database is a subscriber, this table is highly updated.
I can tell this by running the following select:

select * from sys.objects
where object_id = object_id ('VProcessStatus')
go
select *
from sys.dm_db_index_usage_stats
where OBJECT_ID = object_id ('VProcessStatus')

How can I calculate the cost of having this indexed view?

The overload on inserts/updates/deletes?

The stats that I could look at so that I can decide if is it worth having it?

This Script contain few columns

 1. UserSeek : Number of seeks by user queries
 2. UserScans: Number of scans by user queries
 3. UserLookups: Number of bookmark lookups by user queries
 4. UserUpdates: Number of updates by user queries

on the basis these column stats you can figure out either index is useful or not.

Actually Index are inserted to, update to, or deleted on every DML statement so don’t worry if index show updates for a particular index. Typically don't add index unless it respond more than 20% \ enough fast for the query.

SELECT DB_name() DBName,
                        '[' + Sch.name + '].[' + vi.[name] + ']' AS vileName,
                        Ind.type_desc,
                        Ind.[name] AS IndexName,
                        SUBSTRING(( SELECT  ', ' + AC.name
                                    FROM    sys.[views] AS v
                                            INNER JOIN sys.[indexes] I ON v.[object_id] = I.[object_id]
                                            INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id]
                                                                                AND I.[index_id] = IC.[index_id]
                                            INNER JOIN sys.[all_columns] AC ON v.[object_id] = AC.[object_id]
                                                                               AND IC.[column_id] = AC.[column_id]
                                    WHERE   Ind.[object_id] = I.[object_id]
                                            AND Ind.index_id = I.index_id
                                            AND IC.is_included_column = 0
                                    ORDER BY IC.key_ordinal
                                  FOR
                                    XML PATH('') ), 2, 8000) AS KeyCols,
                        SUBSTRING(( SELECT  ', ' + AC.name
                                    FROM    sys.[views] AS v
                                            INNER JOIN sys.[indexes] I ON v.[object_id] = I.[object_id]
                                            INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id]
                                                                                AND I.[index_id] = IC.[index_id]
                                            INNER JOIN sys.[all_columns] AC ON v.[object_id] = AC.[object_id]
                                                                               AND IC.[column_id] = AC.[column_id]
                                    WHERE   Ind.[object_id] = I.[object_id]
                                            AND Ind.index_id = I.index_id
                                            AND IC.is_included_column = 1
                                    ORDER BY IC.key_ordinal
                                  FOR
                                    XML PATH('') ), 2, 8000) AS IncludeCols ,
                        usg_stats.user_seeks AS UserSeek,
                        usg_stats.user_scans AS UserScans,
                        usg_stats.user_lookups AS UserLookups,
                        usg_stats.user_updates AS UserUpdates,
                        getdate()  StatsTimestamp
                FROM    sys.[indexes] AS Ind
                        INNER JOIN sys.[views] AS vi ON vi.[object_id] = Ind.[object_id]
                        INNER JOIN sys.[schemas] AS Sch ON Sch.[schema_id] = vi.[schema_id]
                        LEFT OUTER JOIN sys.dm_db_index_usage_stats AS usg_stats ON  Ind.index_id = usg_stats.index_id
                                                    AND Ind.[OBJECT_ID] = usg_stats.[OBJECT_ID]  and usg_stats.database_id = DB_ID(''
                              + db_name() + '' )
                WHERE  Ind.type_desc <>'HEAP'
                and vi.[name] ='VProcessStatus'
                and Ind.[name]='IDX_VProcessStatus'

Verify script :

select * from dbo.VProcessStatus WITH (NOEXPAND) where processstatus='active'

More on How to determine if an Index is required or necessary


How to set a NOT NULL column into NULL in PostgreSQL? I'm new to PostgreSQL and that is why I'm asking this question. I have a table app_client_users and a column named client_id. Initially I set it as NOT NULL and now I would like to change it to allow NULL. I've tried

alter table app_client_users alter column client_id int  NULL ;

and

alter table app_client_users alter column client_id   NULL ;

which is not working. How can I fix it?

You can simply drop NOT NULL

ALTER TABLE app_client_users ALTER COLUMN client_id DROP NOT NULL;


Use twice DISTINCT ON ina multiple join situation I'm using PostgreSQL 10. I have an entity Product.

The product has images, so I have a Foreign key from Product to Image.

A Product have 1 ON 1 relationship, with a Video entity

A product can have multiple categories and a category can have multiple products, so I have M2M relationship from Product to Category

--- Product ---
id | name |

--- Image ---
id | image | product_id

--- Video ---
id | channel | url |product_id

--- CategoryProduct ---
id | category_id |product_id

--- Category ---
id | name

I need to get a list of Product(s) with their first/one Image and Video and also the first Category

I use DISTINCT ON to get just one image, in the query bellow:

SELECT DISTINCT ON(p.id)  p.id,  p.name, i.image, coalesce(v.channel, v.url) as video
                  FROM product AS p
                  INNER JOIN  image as i on p.id = i.product_id
                  LEFT JOIN video as v on p.id = v.product_id"

My issue is how do I get also the first Category for the Product ?

If I understand correctly a product can have more than one image/video an of course more than one category.

In this case you can use a LATERAL JOIN and limit the result to only one row.

I've set up one small fiddle just to show you:

select p.id,
       p.name,
       i.image,
       v.channel,
       v.url,
       c.name
from   products p
left join lateral (select id, image
                   from   image
                   where  product_id = p.id
                   limit  1) i on true
left join lateral (select id, channel, url
                   from video
                   where product_id = p.id
                   limit 1) v on true
left join lateral (select c.id, c.name
                   from   categoryproduct cp
                   join   category c
                   on     cp.category_id = c.id
                   where  cp.product_id = p.id
                   limit 1) c on true;

id | name  | image | channel | url  | name
-: | :---- | :---- | :------ | :--- | :---
 1 | prod1 | img1  | c1      | url1 | cat1
 2 | prod2 | img3  | c3      | url3 | cat3

db<>fiddle here


How to change schema so that account_id reference is unique among 3 tables Original image:

Updated to have more-correct terminology and an 'is_debit' column:

I am designing a (PostgreSQL) schema for a lottery website that uses double-entry accounting.

'jackpots', 'users', and 'house' are each tables which reference an account_id, which in turn is used in the journal to record a credit or debit of money 'amount'.

I want to assure that the account_id is only in one table -- and that it is unique in that table.

What is the best way to refactor the tables so that account_id is guaranteed to appear only 1x in all of them?

What I've considered:

Creating another table, 'account_type' that has different codes for user accounts, jackpot accounts, and the house's account.  Then add two colums to 'accounts', ('account_type' and 'ref_id', the latter of which will reference the id of 'jackpots', 'users', or 'house').  This seems somewhat inelegant since I wouldn't know how to link the table type to the table name using SQL, alone.  I don't mind using triggers, though, if this is the only way for an elegant, foolproof solution.
Looking for some sort of built in constraint that says account_id cannot be used more than once among 3 tables.  Would guess that this doesn't exist, however.

Help is appreciated!

Start with:

Create a ChartOfAccounts table with the Account code as Primary Key.
Add a Foreign Key constraint to ChartOfAccounts on all tables with an AccountCode field.
Use an IsDebit field, not the numeric sign, to distinguish Debits from Credits and reserve negative signs for transaction reversals (if used at all). This is necessary in order to generate T-Balances and Trial Balances properly from your Journal and Ledger
Create a Journal table with Primary Key: TransactionType, PostingDate, Account, SubledgerCode, IsDebit and minimum attributes of: Amount, CreatedDate, CreatedBy, DocumentReference
Design and spec a stored procedure (or type of routine suitable for where your business logic is located) for each type of transaction to be handled by the system. For your system these might be:

Ticket Purchase for Cash
Ticket Purchase on Account
Prize Payout in Cash
etc.

Please note that I am CGA, CPA in addition to being primarily a professional developer.

Update - Terminology:

A Journal is a chronological list of the details of all transactions of a given type, such as Cash Receipts, Cash Disbursements, Sales, etc.
A Ledger is a listing By Account of the aggregates of all transactions in a given time period.

It is occasionally necessary or expedient, when a wide variety of transaction types will be supported by the system (or to increase parallelism, as when many clerks need to be working at once Bob Cratchit style), to have multiple Journal files with different structure.

In a modern SQL Server system with only one Journal the Ledger could be defined as an Indexed View on the Journal. This would eliminate the need for either a trigger on the Journal to update the Ledger, or a batch-processing design.

Also, it is acceptable to have separate DrAmount and CrAmount columns in place of an IsDebit flag and single Amount column.


Foreign keys without any checking or index I have a problem on SQL Server that led me to the question of whether it is possible to define a foreign key that is not enforced, not backed by an index, and does not need the referenced columns to constitute an (enforced) key.

Foreign keys do two things:

They enforce integrity - which I am willing to give up in my scenario - and
they can be used by the query planner to drop joins that are guaranteed to
not change the result set's number of rows.

The last thing is what I'd still get - the integrity I'll enforce at the application layer.

(Note that what you can have the database enforce is more limited that what you could have as a droppable join condition. A frequent case is that where you'd want to specify a foreign key with ...foreign key (some_constant, almost_key) references... where some_constant is an illegal immediate constant.)

The reason why I need this is I have a SQL Server specific problem in that it does bad query plans in the presence of such an index when the clustered index is a columnstore index (at least I saw on case of such a bad query plan). Since the feature of combining a clustered columnstore indexes with nonclustered btree indexes is brand-new, I take it that there are still some issues they need to iron out.

However, I'm also curious about whether SQL databases generally always demand foreign keys to be backed by indexes on the foreign and/or the domestic side, or whether there are some that take them as hints but allow the user to enforce.

Have you tried the NOCHECK option?

https://technet.microsoft.com/en-us/library/ms177463(v=sql.105).aspx


Merging a .bak to an existing SQL database I have a SQL Server database with data from 1990-2018 in a .mdf file.
I recently downloaded data for 2018-2020 and it's in a .bak file.
When I went to "Restore" the database, it wiped out all the data from 1990-2018 and just replaced it with the new one (2018-2020).
How do I merge an existing .mdf and a new .bak file? Or am I just thinking about this wrong?

A single database restore is not the operation you need if your goal is the merge data. You are looking for a data level solution not a database level solution.
SQL tooling can help you with this task, but it is not required (it depends on knowledge and skill level). The 2 data compare tools I have used Red Gate SQL Data Compare and Apex SQL Data Diff have the ability to compare data using a .bak file. After the comparison the tool can generate the 'deployment' or 'change' script that you could use to "merge" in your changes.
TSQL solution: Craft a merge statement for each table. This would require restoring your backup file to another Database name.  This will result in 2 databases (source and target) living on the SQL Server.
TSQL Merge docs:
https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver15
To avoid a database overwrite specify a new destination (non-existing) database name in your restore command. (included SSMS screen shot)


Migration from DB2 to MySQL Does any one have experience on migration from DB2 to MySQL ?

Please share with me.

I tried googling, but I have not received much information on this.

Years ago, I saw versions of MySQL available for certain operating systems. One of them had a version MySQL for IBM DB2.

http://dev.mysql.com/doc/refman/5.1/en/se-db2.html

http://www.redbooks.ibm.com/redbooks/pdfs/sg247705.pdf

http://www.docstoc.com/docs/9314480/IBM-DB2-Storage-Engine-for-MySQL--Presentation

If you install MySQL with DB2 Storage Engine, you could migrate the data you want from DB2 into MyISAM tables or InnoDB tables. Then mysqldump every MyISAM or InnoDB table to SQL Text Files.

I hope this helps !!!

UPDATE : I read the first link page. It says as of MySQL 5.1.54, that storage engine is gone. If you can get a hold of MySQL 5.1.35 - 5.1.53 for any IBM OS, you can proceed from there. At least, you can contact MySQL (eh, Oracle [I hate saying that, it just does not roll off the tongue]) and see if you can get those older binaries.


mysql fulltext - exact match or nothing I have mysql table with column tags, and would like to search exact match.
For this data:

 proj7 invoice delivery
 proj7 exchange

and this query

 MATCH(tags) AGAINST ('+proj7 +(invoice report)')

should be

 (proj7 AND (invoice OR report))

So i want only first record in result, but there are both, second with low score due to proj7 existence. How can i query exact matches only.

sqlfiddle http://sqlfiddle.com/#!9/26524/3

PROPOSED QUERY

SELECT * FROM test WHERE MATCH(tags)
AGAINST ('"+proj_7" "(invoice report)"' IN BOOLEAN MODE);

YOUR SAMPLE DATA LOADED

mysql> USE test
Database changed
mysql> DROP TABLE IF EXISTS test;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `test` (
    ->   `tags` text NOT NULL,
    ->   FULLTEXT KEY `tags` (`tags`)
    -> );
Query OK, 0 rows affected (0.20 sec)

mysql> INSERT INTO test (tags) VALUES ("proj_7 type_invdtl year_15 month_04");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test (tags) VALUES ("proj_8 type_invdtl year_15 month_04");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test (tags) VALUES ("proj_10 type_invdtl year_15 month_04");
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM test;
+--------------------------------------+
| tags                                 |
+--------------------------------------+
| proj_7 type_invdtl year_15 month_04  |
| proj_8 type_invdtl year_15 month_04  |
| proj_10 type_invdtl year_15 month_04 |
+--------------------------------------+
3 rows in set (0.00 sec)

mysql>

PROPOSED QUERY EXECUTED

mysql> SELECT * FROM test WHERE MATCH(tags)
    -> AGAINST ('"+proj_7" "(invoice report)"' IN BOOLEAN MODE);
+-------------------------------------+
| tags                                |
+-------------------------------------+
| proj_7 type_invdtl year_15 month_04 |
+-------------------------------------+
1 row in set (0.00 sec)

mysql>

OTHER EXAMPLES

mysql> SELECT * FROM test WHERE MATCH(tags)
    -> AGAINST ('"+proj_7" "(type_invdtl year_15)"' IN BOOLEAN MODE);
+--------------------------------------+
| tags                                 |
+--------------------------------------+
| proj_7 type_invdtl year_15 month_04  |
| proj_8 type_invdtl year_15 month_04  |
| proj_10 type_invdtl year_15 month_04 |
+--------------------------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM test WHERE MATCH(tags)
    -> AGAINST ('"+proj_7" "(type_invdt2 year_15)"' IN BOOLEAN MODE);
+-------------------------------------+
| tags                                |
+-------------------------------------+
| proj_7 type_invdtl year_15 month_04 |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test WHERE MATCH(tags) AGAINST ('"+proj_7 +type_invdtl" "+proj_7 +year15"' IN BOOLEAN MODE);
+-------------------------------------+
| tags                                |
+-------------------------------------+
| proj_7 type_invdtl year_15 month_04 |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test WHERE MATCH(tags) AGAINST ('"+proj_7 +type_invdtl" "+proj_7 +year16"' IN BOOLEAN MODE);
+-------------------------------------+
| tags                                |
+-------------------------------------+
| proj_7 type_invdtl year_15 month_04 |
+-------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test WHERE MATCH(tags) AGAINST ('"+proj_7 +type_invdt2" "+proj_7 +year16"' IN BOOLEAN MODE);
Empty set (0.00 sec)

mysql>

GIVE IT A TRY !!!


Trying to Build TreeView Query in T-SQL I have parent records in two tables, Table1 and Table2.  Some records in Table3 are children of Table1, and some are children of Table2.

I would like to build a query that I can use to populate a TreeView control with the data from Table3 displaying either under Table1 if it only has a parent in Table1 and not Table2, or Table2 if it has parent records in both Table1 and Table2.

Is it possible that if the table3's column(Child) is only related to table 1 to show it under table 1 and not under table 2, but at the same time another (Child) has a parent in table 2 (which usually is the case) it will show under table 2 as its currently doing.

How can I output that in a query for a TreeView? I am assuming that I will have to program the output in C#, with 3 FOR loops.  In the second loop I can check if the column is grandchild or Child and make that as a second row or 2nd node of the TreeView, but I am having a problem building a query in SQL.

The query below shows all Parent, then child then grandchild, which is working, but what is desired is at times child takes place of a father.

DECLARE @x AS XML
SET @x =
(
    SELECT DISTINCT
        Table1.AssetSysID
        , Table1.Asset_ID
        , Table1.FromLR
        , Table1.Asset_ID + ', ' + Table1.[Desc2] AS GarndFather
        , Table2.ACISysID
        , Table2.PAssetSysID
        , Table2.FeatureName + ', ' + Table2.[DESC] AS Father
        , Table3.ITMSysID
        , Table3.Item_ID + ',' + Table3.[DESC] AS Child
    FROM Table1
        LEFT OUTER JOIN Table2 ON Table1.AssetSysID = Table2.PAssetSysID
        LEFT OUTER JOIN Table3 ON Table1.AssetSysID = Table3.AssetSysID
                                AND Table2.ACISysID = Table3.ACISysID
    WHERE (Table1.AssetType = @AssetType)
    FOR XML AUTO,root('xml')
);

I've modified the code above to reflect some changes:

DECLARE @x AS XML
SET @x =
(
    SELECT
        Table1.AssetObjID AS "@AssetObjID"
        , Table1.Asset_ID AS "@Asset_ID"
        , Table1.FromLR AS "@FromLR"
        , Table1.AssetType + ', ' + Table1.StreetName + ', ' +  Table1.FromMunicNo   AS "@FirstRow"
        , (
            SELECT
                Table2.ACIObjID AS "@ACIObjID"
                , Table2.PAssetObjID AS "@PAssetObjID"
                , Table2.Feature_ID + ', ' + Table2.FeatureName   AS "@ChildOfFirstRow"
                , (
                    SELECT
                        Table3.ITMObjID AS "@ITMObjID"
                        , Table3.Item_ID + ',' + Table3.[DESC] AS "@GrandChildOfFirstRow"
                    FROM Table3
                    WHERE Table1.AssetObjID = Table3.AssetObjID
                        AND Table2.ACIObjID = Table3.ACIObjID
                    FOR XML PATH('Table3'), TYPE
                    )
                FROM Table2
                WHERE
                    Table1.AssetObjID = Table2.PAssetObjID
                FOR XML PATH('Table2'), TYPE
            )
        , (
            SELECT
                Table3.ITMObjID AS "@ITMObjID"
                , Table3.Item_ID + ',' + Table3.[DESC] AS "@GrandChildOfFirstRow"
            FROM Table3
            WHERE Table1.AssetObjID = Table3.AssetObjID
                AND Table2.ACIObjID <> Table3.ACIObjID
            FOR XML PATH('Table3'), TYPE
        )
        FROM Table1
        WHERE Table1.AssetType = 'xxxx'
        FOR XML PATH('Table1'), root('xml')
);

Got an Idea from Mikael Eriksson and a help from a team lead @ work

  select Table1.AssetObjID as "@AssetObjID",
           Table1.Asset_ID as "@Asset_ID",
           Table1.FromLR as "@FromLR",
           Table1.AssetType + ', ' + Table1.StreetName + ', ' +  Table1.FromMunicNo   as "@FirstRow",
           (
           select Table2.ACIObjID as "@ACIObjID",
                  Table2.PAssetObjID as "@PAssetObjID",
                  Table2.Feature_ID + ', ' + Table2.FeatureName   AS "@ChildOfFirstRow",
                  (
                  select Table3.ITMObjID as "@ITMObjID",
                         Table3.Item_ID + ',' + Table3.[DESC] as "@GrandChildOfFirstRow"
                  from Table3
                  where Table1.AssetObjID = Table3.AssetObjID and
                        Table2.ACIObjID = Table3.ACIObjID
                  for xml path('Table3'), type
                  )
           from Table2
           where Table1.AssetObjID = Table2.PAssetObjID
           for xml path('Table2'), type
           ),
(
           select Table3.ITMObjID as "@ITMObjID",
                         Table3.Item_ID + ',' + Table3.[DESC] as "@GrandChildOfFirstRow"
                  from Table3
                  where Table1.AssetObjID = Table3.AssetObjID and
                        Table2.ACIObjID <> Table3.ACIObjID
                  for xml path('Table3'), type
)
    from Table1
    where Table1.AssetType = 'xxxx'
    for xml path('Table1'), root('xml')

So It goes something like this:

<Table1 atrributes......... FirstRow=".........."/>
<Table2 atrributes......... ChildOfFirstRow="..........">
<Table3 atrributes......... GrandChildOfFirstRow=".........."/>
</Table2>
<Table3 atrributes......... GrandChildOfFirstRow="This GrandChild took the importance as equal to ChildOfFirstRow"/>