Create a view in a user database that can access selected system database tables Using the principle of least privilege, what access should I give to a Role "Checklist_role" that has read permission to a view "MaintenanceJobs_view" in a user database "ChecklistDB", that can access certain columns in a system table within the msdb database?
The following is an example similar to what I would like to do:
USE ChecklistDB;
GO
/* Create View */
CREATE VIEW [MaintenanceJobs_view]
AS
SELECT [name],
       [description],
       [enabled],
       [category_id]
FROM [msdb].[dbo].[sysjobs];
GO
/* Create Role based on principle of least privilege */
CREATE ROLE [CheckList_role];
GO
/* What permissions are required? */

Can this be done by granting certain privileges to the the role(Checklist_role) without giving privileges to any objects in the "msdb" database?
At this stage it is mainly to show a proof of concept in a very limited amount of time. In the long term I would definitely like to use a stored procedure.

Can this be done by granting certain privileges to the the
role(Checklist_role) without giving privileges to any objects in the
"msdb" database?

Rather than a view, you could instead use a multi-statement table-valued function or stored procedure containing the query and sign the module with a certificate to confer additional permissions. This allows you to follow the principle of least privilege since only permissions on the function or stored procedure are needed.
Below is an example script that creates the certificate in both databases along with the needed user and permissions.
USE ChecklistDB;
GO
CREATE FUNCTION MaintenanceJobs_function()
RETURNS @table TABLE(
     name sysname
    ,description nvarchar(1024)
    ,enabled tinyint
    ,category_id int
)
AS
BEGIN
    INSERT @table
    SELECT [name],
           [description],
           [enabled],
           [category_id]
    FROM [msdb].[dbo].[sysjobs];
    RETURN;
END
GO
CREATE ROLE Checklist_role;
GRANT SELECT ON dbo.MaintenanceJobs_function TO Checklist_role;
GO

--Create self-signed cert in ChecklistDB and copy to msdb
USE ChecklistDB;
CREATE CERTIFICATE CrossDatabasePermissionsCertificate
    ENCRYPTION BY PASSWORD = 'temporary password'
    WITH SUBJECT = 'Allow cross-database access';

--Copy cert to another database.
DECLARE @cert_id int = CERT_ID('CrossDatabasePermissionsCertificate');
DECLARE @public_key  varbinary(8000) = CERTENCODED(@cert_id),
        @private_key varbinary(8000) = CERTPRIVATEKEY(@cert_id , 'temporary password', 'temporary password');

--these values should not be NULL
IF @cert_id IS NULL THROW 50000, 'Assertion failed: @cert_id is NULL', 0;
IF @public_key IS NULL THROW 50000, 'Assertion failed: @public_key is NULL', 0;
IF @private_key IS NULL THROW 50000, 'Assertion failed: @private_key is NULL', 0;

--copy certificate in target database
DECLARE @sql nvarchar(MAX) =
      'CREATE CERTIFICATE CrossDatabasePermissionsCertificate
       FROM  BINARY = ' + CONVERT(varchar(MAX), @public_key, 1) + '
       WITH PRIVATE KEY (BINARY = ' +
          CONVERT(varchar(MAX), @private_key, 1)
        + ', DECRYPTION BY PASSWORD = ''temporary password'''
        + ', ENCRYPTION BY PASSWORD = ''temporary password'');'
PRINT @sql; --show CREATE CERTIFICATE DDL

EXEC msdb.sys.sp_executesql @sql;
GO

--create certificate user in msdb and grant permissions needed by the module
USE msdb;
CREATE USER CrossDatabasePermissionsCertificateUser FROM CERTIFICATE CrossDatabasePermissionsCertificate;
GRANT SELECT ON dbo.sysjobs TO CrossDatabasePermissionsCertificateUser;
GO

--add signature to module by the same certificate that exists in both databases
USE ChecklistDB;
ADD SIGNATURE TO dbo.MaintenanceJobs_function BY CERTIFICATE CrossDatabasePermissionsCertificate WITH PASSWORD = 'temporary password';
ALTER CERTIFICATE CrossDatabasePermissionsCertificate REMOVE PRIVATE KEY;
GRANT SELECT ON dbo.MaintenanceJobs_function TO Checklist_role;
GO

--test with minimally priviliged login/user
CREATE LOGIN TestUser1 WITH PASSWORD = 'Test-Permissi0ns';
CREATE USER TestUser1;
ALTER ROLE Checklist_role
    ADD MEMBER TestUser1;
EXECUTE AS LOGIN = 'TestUser1';
SELECT * FROM dbo.MaintenanceJobs_function();
GO
REVERT;
GO


Should I create an index for non key columns? I have a table in a PostGreSql database defined as following:

CREATE TABLE public."MATCH"(
    "ITEM_A_ID" bigint DEFAULT 0,
    "ITEM_B_ID" bigint DEFAULT 0,
    "OWNER_A_ID" bigint DEFAULT 0,
    "OWNER_B_ID" bigint DEFAULT 0,
    "OTHER_DATA" varchar(100) NOT NULL DEFAULT ''
    CONSTRAINT "MATCH_PK" PRIMARY KEY ("ITEM_A_ID","ITEM_B_ID")
);

It will contain a lot of rows. There will be a lot of queries like the following performed on this table:

SELECT * FROM "MATCH" WHERE "OWNER_A_ID" = owner_a_id;
SELECT * FROM "MATCH" WHERE "OWNER_B_ID" = owner_b_id;

I was thinking about creating indexes on OWNER_A_ID and OWNER_B_ID, since these columns are not keys. Is this a good idea, and if yes, how should I create these? Should I create one index with both columns? Should I create two indexes? Should I include other columns?

Please be more specific: "It will contain a lot of rows". How many? Millions, thousands or billions. "Is it a good idea?" It depends.

If your queries are like the ones you mention, you should create two b-tree indexes, one for each field. Instructions are here: http://www.postgresql.org/docs/9.1/static/sql-createindex.html

You should only create one index for both fields, ONLY IF all your queries are like:

SELECT * FROM "MATCH" WHERE "OWNER_A_ID" = owner_a_id AND "OWNER_B_ID"=owner_b_id;

This index will also work for queries like:

SELECT * FROM "MATCH" WHERE "OWNER_A_ID" = owner_a_id;

but NOT for

SELECT * FROM "MATCH" WHERE "OWNER_B_ID" = owner_b_id;


Does the partition key also have to be part of the primary key? I am partitioning a table based on a column that is not a primary key?  I've read some conflicting information today on whether the partition column must be a part of the primary key.  My gut says no, but I am not 100% sure.  So questions...

Must the partition column be part of the primary?  Is it recommended one way or the other?
Do I have to create an index for the partition key, or does the DBMS do it automatically on its own?

Not at all.

One of the most common scenarios for partitioning is to use a date field, which is totally unrelated to your PK.

For instance, if you have a table Orders with the field OrderDate you would most likely partition based on the month and year of OrderDate.

When records age out and are no longer relevant you can move those partitions off to an archive table or database so they are no longer processed.

Partitioning will work with pretty much any field, but in order for it to work WELL the field(s) you partition on should be used in most, if not all, of your queries.  If you don't include your partition keys then you will get essentially an expensive table scan that goes across multiple tables (partitions).

EDIT

For part 2, I think the answer is no as well.  The partition key is used to determine which partition to put the row in, but I don't think an index is maintained.  There may be stats in the back end on it though.


This simple mysql query is taking 2-3 seconds I'm using sqlalchemy and trying to do a query on an m2m table.  I have a tag and want find all the events that match that tag:

SELECT * FROM events
WHERE EXISTS ( SELECT 1 FROM events_tags, tags
               WHERE events.id = events_tags.event_id
               AND tags.id = events_tags.tag_id
               AND tags.id = 617)
LIMIT 50;

This is taking 2-3 seconds on my web server.  On my laptop, it's faster(though my laptop is more powerful and has fewer rows.)  The web server has about 300,000 rows in this table.

Here is an explain:

+------+--------------------+-------------+--------+----------------+---------+---------+--------------------------+--------+-------------+
| id   | select_type        | table       | type   | possible_keys  | key     | key_len | ref                      | rows   | Extra       |
+------+--------------------+-------------+--------+----------------+---------+---------+--------------------------+--------+-------------+
|    1 | PRIMARY            | events      | ALL    | NULL           | NULL    | NULL    | NULL                     | 310172 | Using where |
|    2 | DEPENDENT SUBQUERY | tags        | const  | PRIMARY        | PRIMARY | 8       | const                    |      1 | Using index |
|    2 | DEPENDENT SUBQUERY | events_tags | eq_ref | PRIMARY,tag_id | PRIMARY | 16      | timeline.events.id,const |      1 | Using index |
+------+--------------------+-------------+--------+----------------+---------+---------+--------------------------+--------+-------------+

I have a feeling this is simple, but my google-fu is failing me.

Create Table Syntax:

 | events | CREATE TABLE `events` (
   `id` bigint(20) NOT NULL AUTO_INCREMENT,
   `title` varchar(255) DEFAULT NULL,
   `context` varchar(255) DEFAULT NULL,
   `text` mediumtext,
   `wikidata_id` bigint(20) DEFAULT NULL,
   `start_day` smallint(6) DEFAULT NULL,
   `start_month` smallint(6) DEFAULT NULL,
   `start_year` bigint(20) DEFAULT NULL,
   `end_day` smallint(6) DEFAULT NULL,
   `end_month` smallint(6) DEFAULT NULL,
   `end_year` bigint(20) DEFAULT NULL,
   `is_number` tinyint(1) DEFAULT NULL,
   `version` int(11) DEFAULT NULL,
   `number` bigint(20) DEFAULT NULL,
   `start_name` varchar(255) DEFAULT NULL,
   `end_name` varchar(255) DEFAULT NULL,
   `subject_title` varchar(255) DEFAULT NULL,
   `object_title` varchar(255) DEFAULT NULL,
   `created_at` datetime DEFAULT NULL,
   `updated_at` datetime DEFAULT NULL,
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=327132 DEFAULT CHARSET=utf8 |

edited: my bad, it was missing the "from events"  I guess I'm wondering where I would want an index, I would think on the "id" column, which should have an index.

Lots of simplification and speedup:
SELECT e.*
    FROM ( SELECT DISTINCT event_id
             FROM events_tags
             WHERE tag_id = 617 ) AS et
    JOIN events  AS e  ON e.id = et.event_id
ORDER BY ???  -- else get random 50??
LIMIT 50;

events_tags smells like a many:many mapping table.  It can possibly be improved by following these tips.


Rebuilding indexes for a partitioned table having 300 partitions Scenario

Partitioned table is empty and I am loading data for 1 partition which has 180k rows. I disabled the indexes and loaded the data and rebuilt the indexes after data is loaded.

Issue

While examining the query plan of rebuilt indexes, I can see the 'Estimated number of rows' is 180k but 'Actual number of rows' is 300 partitions * 180,000 rows = 54 Million rows, even though I am loading data for only one partition.

Can you shed some light on this behavior and how to overcome this issue?

Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64) Mar 18 2018 09:11:49 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2012 R2 Standard 6.3  (Build 9600: )

While examining the query plan of rebuilt indexes, I can see the 'Estimated number of rows' is 180k but 'Actual number of rows' is 300 partitions * 180,000 rows = 54 Million rows, even though I am loading data for only one partition.

Your maths are a little off here. The image provided shows an estimated 1,853,250 rows (not 180k) and a total of 674,582,272 rows (not 54 million) over 364 iterations (364 * 1,853,250 = 674,582,272).

Still, the question remains: Why would SQL Server read 674 million rows to rebuild a nonclustered index when the whole table only holds 1,853,250 rows?

The execution plan shown is a colocated join. It performs the following operations:

The Constant Scan holds the partition numbers for each partition of the table.
For each partition:

Fully scan the table (Clustered Index Scan)
Sort the rows into nonclustered index order (Sort)
Filter the rows for the current partition (Filter)
Insert the rows into the nonclustered index (Index Insert)

This is obviously a terribly inefficient way to go about things. But the optimizer is not crazy: the general idea is sound, the problem is that the Filter is applied too late. Normally, the 'current partition' predicate would be pushed down into the Clustered Index Scan, where it would appear as a seek to select the current partition.

How It Should Work

The usual parallel plan generated looks like this:

Notice the lack of a Filter operator. Its predicate has been pushed into the scan:

Yes, this is a scan with a seek. The idea is to fetch rows from only the current partition on each iteration of the loop, so each iteration builds one partition of the nonclustered index.

Your case

I managed to reproduce the execution plan shown in the question by using an float or bigint data type as the partitioning column. Some aspect of the cardinality estimation for the Filter (probably a guess) prevents the optimizer pushing the 'current partition' predicate past the Sort, and into the Clustered Index Scan.

The following demo creates a 300-partition table with a disabled nonclustered index, loads one million rows into a single partition, then rebuilds the nonclustered index. It uses the bigint data type:

DROP TABLE IF EXISTS dbo.Test;

IF EXISTS (SELECT * FROM sys.partition_schemes AS PS WHERE PS.[name] = N'PS')
    DROP PARTITION SCHEME PS;

IF EXISTS (SELECT * FROM sys.partition_functions AS PF WHERE PF.[name] = N'PF')
DROP PARTITION FUNCTION PF;
GO
CREATE PARTITION FUNCTION PF (bigint)
AS RANGE RIGHT
FOR VALUES
(
    0,
    1000000,2000000,3000000,4000000,5000000,6000000,7000000,8000000,9000000,10000000,
    11000000,12000000,13000000,14000000,15000000,16000000,17000000,18000000,19000000,
    20000000,21000000,22000000,23000000,24000000,25000000,26000000,27000000,28000000,
    29000000,30000000,31000000,32000000,33000000,34000000,35000000,36000000,37000000,
    38000000,39000000,40000000,41000000,42000000,43000000,44000000,45000000,46000000,
    47000000,48000000,49000000,50000000,51000000,52000000,53000000,54000000,55000000,
    56000000,57000000,58000000,59000000,60000000,61000000,62000000,63000000,64000000,
    65000000,66000000,67000000,68000000,69000000,70000000,71000000,72000000,73000000,
    74000000,75000000,76000000,77000000,78000000,79000000,80000000,81000000,82000000,
    83000000,84000000,85000000,86000000,87000000,88000000,89000000,90000000,91000000,
    92000000,93000000,94000000,95000000,96000000,97000000,98000000,99000000,100000000,
    101000000,102000000,103000000,104000000,105000000,106000000,107000000,108000000,109000000,
    110000000,111000000,112000000,113000000,114000000,115000000,116000000,117000000,118000000,119000000,
    120000000,121000000,122000000,123000000,124000000,125000000,126000000,127000000,128000000,129000000,
    130000000,131000000,132000000,133000000,134000000,135000000,136000000,137000000,138000000,139000000,
    140000000,141000000,142000000,143000000,144000000,145000000,146000000,147000000,148000000,149000000,
    150000000,151000000,152000000,153000000,154000000,155000000,156000000,157000000,158000000,159000000,
    160000000,161000000,162000000,163000000,164000000,165000000,166000000,167000000,168000000,169000000,
    170000000,171000000,172000000,173000000,174000000,175000000,176000000,177000000,178000000,179000000,
    180000000,181000000,182000000,183000000,184000000,185000000,186000000,187000000,188000000,189000000,
    190000000,191000000,192000000,193000000,194000000,195000000,196000000,197000000,198000000,199000000,
    200000000,201000000,202000000,203000000,204000000,205000000,206000000,207000000,208000000,209000000,
    210000000,211000000,212000000,213000000,214000000,215000000,216000000,217000000,218000000,219000000,
    220000000,221000000,222000000,223000000,224000000,225000000,226000000,227000000,228000000,229000000,
    230000000,231000000,232000000,233000000,234000000,235000000,236000000,237000000,238000000,239000000,
    240000000,241000000,242000000,243000000,244000000,245000000,246000000,247000000,248000000,249000000,
    250000000,251000000,252000000,253000000,254000000,255000000,256000000,257000000,258000000,259000000,
    260000000,261000000,262000000,263000000,264000000,265000000,266000000,267000000,268000000,269000000,
    270000000,271000000,272000000,273000000,274000000,275000000,276000000,277000000,278000000,279000000,
    280000000,281000000,282000000,283000000,284000000,285000000,286000000,287000000,288000000,289000000,
    290000000,291000000,292000000,293000000,294000000,295000000,296000000,297000000,298000000,299000000,
    300000000
);
CREATE PARTITION SCHEME PS
AS PARTITION PF
ALL TO ([PRIMARY]);
GO
CREATE TABLE dbo.Test
(
    c1 bigint NOT NULL,
    c2 bigint NOT NULL,

    CONSTRAINT [PK dbo.Test (c1)]
    PRIMARY KEY CLUSTERED (c1)
    ON PS (c1),

    INDEX [IX dbo.Test (c2)]
    NONCLUSTERED (c2)
    ON PS (c1)
)
ON PS (c1);
GO
ALTER INDEX [IX dbo.Test (c2)]
ON dbo.Test
DISABLE;
GO
INSERT dbo.Test WITH (TABLOCKX)
(
    c1,
    c2
)
SELECT
    CONVERT(bigint, SV1.number * 1000 + SV2.number),
    CONVERT(bigint, (SV1.number * 1000 + SV2.number) * 2)
FROM master.dbo.spt_values AS SV1
CROSS JOIN master.dbo.spt_values AS SV2
WHERE
    SV1.[type] = N'P'
    AND SV2.[type] = N'P'
    AND SV1.number >= 0
    AND SV1.number < 1000
    AND SV2.number >= 0
    AND SV2.number < 1000;
GO
ALTER INDEX [IX dbo.Test (c2)]
ON dbo.Test
REBUILD;

The index rebuild plan takes around 90 seconds to run:

By contrast, when the partitioning column is an integer data type, the rebuild takes two seconds (with the efficient scan-with-a-seek plan previously shown).

This might be because the partition id is natively an integer (so no conversion is necessary in the filter predicate), but the details are unclear at this point.

A workaround

The above only reproduces for me if I use the default (modern) cardinality estimation model. It is not possible to specify a hint directly on an index rebuild statement, so the following uses documented trace flag 9481 to temporarily use the legacy (original) cardinality estimation model instead:

ALTER INDEX [IX dbo.Test (c2)]
ON dbo.Test
DISABLE;

DBCC TRACEON (9481);

ALTER INDEX [IX dbo.Test (c2)]
ON dbo.Test
REBUILD;

DBCC TRACEOFF (9481);

This produces the optimal execution plan and completes in around two seconds.

The real solution

There is a better way to add or load data into a single partition of a table. The idea is to SWITCH the existing partition out into a standalone table, load the data into that, then SWITCH the partition back in:

DROP TABLE IF EXISTS dbo.TestSwitchP2;
GO
-- Table with constraints limiting values to the target partition
CREATE TABLE dbo.TestSwitchP2
(
    c1 float NOT NULL PRIMARY KEY,
    c2 float NOT NULL,

    CHECK (c1 >= 0 AND c1 < 1000000)
)
ON [PRIMARY];
GO
-- Switch existing rows into the working table
ALTER TABLE dbo.Test
SWITCH PARTITION 2
TO dbo.TestSwitchP2;
GO
-- Add new rows
INSERT dbo.TestSwitchP2 WITH (TABLOCKX)
(
    c1,
    c2
)
SELECT
    CONVERT(float, SV1.number * 1000 + SV2.number),
    CONVERT(float, (SV1.number * 1000 + SV2.number) * 2)
FROM master.dbo.spt_values AS SV1
CROSS JOIN master.dbo.spt_values AS SV2
WHERE
    SV1.[type] = N'P'
    AND SV2.[type] = N'P'
    AND SV1.number >= 0
    AND SV1.number < 1000
    AND SV2.number >= 0
    AND SV2.number < 1000;
GO
-- Create a compatible nonclustered index
CREATE INDEX i ON dbo.TestSwitchP2 (c2);
GO
-- Switch the partition back in to the main table
ALTER TABLE dbo.TestSwitchP2
SWITCH TO dbo.Test
PARTITION 2;

Partition switching is a metadata-only operation, and typically completes instantly.

The implementation above loads the one million new rows in less than two seconds. Note there is no need to disable the nonclustered index on the main table.


How to create a table with data from other tables in Postgres? How to create a table that with data from 6 other tables that have the same columns? The table names are: ab1_2, ab3, ab4, ab5 and ab6.

This is a spatial data with the Postgis extension enabled so they have geometry that I need to put in the first and second columns. The output table that I'm looking for is something like:

X coordinate | Y coordinate | field 3 | field 8 | field 20

From the PostgreSQL documentation:

CREATE TABLE new_table AS
SELECT * FROM ab1_2;

Replace the * with the field names (with alias if you need to change the name) you need. You can put in the other 5 tables as UNION:

CREATE TABLE new_table AS
SELECT * FROM ab1_2
UNION
SELECT * FROM ab3
UNION
SELECT * FROM ab4
UNION
SELECT * FROM ab5
UNION
SELECT * FROM ab6;

In the second one only the field names are needed. No use to give them an alias.

Or write 5 SQLs in the form:

INSERT INTO new_table
SELECT * FROM other_table;


Sargability question - order of variables I have a stored procedure that has a few lines:

AND ( @StartDate IS NULL OR @StartDate <= r.ReferralDate )
AND ( @EndDate IS NULL OR @EndDate >= r.ReferralDate )

Should it be re-written as:

AND ( r.ReferralDate >= @StartDate or @Startdate IS NULL  )
AND ( r.ReferralDate <= @EndDate or @EndDate IS NULL )

I tried it both ways and looking at the execution plans. There is a small difference in estimated number of rows, but other than that I do not see a change, so I assumed that the order in the statement did not matter, but was hoping someone could verify.

The short answer is that the order you put these in makes no difference to SARGability. If you want this to be as efficient as possible, you can add a RECOMPILE hint, or use dynamic SQL to generate the appropriate WHERE clause.

Since you seem to understand the concepts involved, I won't beat any dead horses here, I'll just point you to a great source on the subject: Dynamic Search Conditions in T‑SQL


Composite Primary keys and auto increment? i'm developing SaaS app with multi-tenancy, and i've decide to use single DB (MySQL Innodb for now) for client's data. I chose to use composite primary keys like  PK(client_id, id). I have 2 ways here,

1: increment "id" by myself (by trigger or from code)

or

2: Make "id" as auto-increment by mysql.

In first case i will have unique id's for each client, so each client will have id 1, 2, 3 etc..
In second case id's will grow for all clients.
What is the best practice here? My priorities are: performace, security & scaling. Thanks!
Same question at stackoverflow with more answers

The second option should be the fastest. It was made for this. Also it should have no bugs since it is used a lot and already for a long time. If you go for this then you do not even need to use a composite primary key.

In my opinion the only reason to use the first option is if you need a numbering starting from 1 per client.


Getting PAGELATCH_UP:tempdb:1(PFS) with so many waiting queue Getting PAGELATCH_UP:tempdb:1(PFS) with so many waiting queue. What can be solution?

We have two hexacore processors. Ideally how much tempdb can we create to handle this issue?

Kendra Little writes in How to Tell if You Need More Tempdb Files:

First of all, if you just have one tempdb file, consider adding more files as preventive medicine. Don’t go crazy with this. The formula recommended by Microsoft in KB 2154845 to use one tempdb data file for each logical processor up to 8 processors is a good place to start.

and:

Remember: this only applies to tempdb data files. You only need one tempdb log file.
Also remember that the tempdb data files will be used proportionally with their size. This means that you should keep the files equally sized.

Read the full article to learn more.


What are the potential causes for a sudden MySQL slowdown? A few days ago mysql became suddenly very slow with queries taking more than 10s to complete when they used to complete under 100ms... It lasted about an hour and then the speed was normal again. What could cause such a problem?

A few more information: The mysql version is 5.0.51a The mysql database is on a dedicated box with 1.5 GB ram. There was no swap being used when the mysql server was slow.

Here's the mysqlreport from the database http://pastebin.com/au6yMWqQ None of the caches are full..

I've noticed that the DNS server used by the server for resolving hostname was a bit slow sometimes lately. Could this have an impact?

Thanks,
I've crossposted this on serverfault, hope it's ok...

EDIT: Could I try to have a master master replication on ec2 to protect against the problem happening again? Half of the instances connecting to the mysql database are on ec2 (the rest directly from layeredtech)

You could try the following to improve things

skip-host-cache and skip-name-resolve

Please follow these steps:

Step 01) Add the following to /etc/my.cnf

[mysqld]
skip-host-cache
skip-name-resolve

Step 02) service mysql restart

Step 03) Stop using mysql users that have DNS names. Run thus query:

SELECT user,host FROM mysql.user;

Any user that has a DNS name, simply replace it with IP Address (such as 10.85.115.73) or a netblock (such as 10.85.115.%)

You may also want to run this command intermittently (say one an hour in a crontab)

FLUSH HOSTS;

Give it a Try !!!


Why does my database structure and SELECT operations generate NULLs? I'm not too experienced with database problems, so please excuse the wall of text, I believe the context is important to understand the problem and goal.

I receive a CSV file from another system every day that I want to retain in a database running on Microsoft SQL Server. To accomplish this, I have a PowerShell-script scheduled daily that uses the bcp utility to "load" the CSV (accounts.csv) into a table (accounts_stage) that has the "exact" layout of the CSV file. In addition, I "write" the timestamp into another table (import_stage). The accounts_stage table has a trigger set, and bcp is configured to fire that trigger.

After the import is finished, I want to "normalize" the data. For a basic example, assume this concerns only the contract number (in the CSV file a field called "Vnr"), which is a string. I have another table (contracts), which gets filled by the trigger:

INSERT INTO dzp.contractid(vnr)
SELECT DISTINCT
    a.Vnr
FROM dzp.accounts_stage a
WHERE NOT EXISTS(
    SELECT id.vnr
    FROM dzp.contractid id
)

This should, and seems to accomplish, add all contract numbers that are ever imported to the table contractids. contractids.contractId is a BIGINT PRIMARY KEY, IDENTITY, and auto increment.

I add the import-metadata to my table like this:

   CREATE TABLE #IMPORTMETADATA
            ([importid] bigint
            ,[generated_timestamp] datetime
            ,[imported_timestamp] datetime
            )
    INSERT INTO #IMPORTMETADATA
        SELECT TOP 1 [importid], [timestamp_import], [generated_timestamp]
    FROM
            [QGTAA].[dzp].[import]
        ORDER BY
            [timestamp_import] DESC

Next, the trigger should INSERT the data FROM accounts_stage INTO accounts, such that the contractId is replaced (from the original string representation to the PRIMARY KEY of the contracts table).

INSERT INTO [dzp].[accounts]
      ([Udnr]
      ,[ContractId]
      -- snip
      ,[generated_timestamp]
      ,[importid]
      )
SELECT
    a.[Udnr],
    cid.contractId,
    -- snip
    meta.[generated_timestamp],
    meta.[importid]
FROM [QGTAA].[dzp].[accounts_stage] a
LEFT JOIN dzp.contractid cid ON cid.vnr = a.Vnr
CROSS JOIN #IMPORTMETADATA meta

However, this last step fails, because the SELECT operation in the last block yields rows where contractId IS NULL, which I did not expect. Now I believe the issue is somewhere with the LEFT JOIN, but I'm not sure why.

Questions

Can someone please point me in the right direction on this specific problem?
I am not too experienced with databases, is this even a sane way of accomplishing what I intend, or is this a completely insane approach? Is there a better way?

Thank you!

There's a problem with your first NOT EXISTS. Let's take a look at your current query:

INSERT INTO dzp.contractid(vnr)
SELECT DISTINCT
    a.Vnr
FROM dzp.accounts_stage a
WHERE NOT EXISTS(
    SELECT id.vnr
    FROM dzp.contractid id
)

So, what's happening here is that the NOT EXISTS that you are using will return true only when the dzp.contractid table is empty. Once it has one row (or more), the NOT EXISTS will return false for every row of your main table, since SELECT id.vnr FROM dzp.contractid has a result set, and you end up inserting duplicated values in your table.

What you need to do is correlate this subquery with the main table:

INSERT INTO dzp.contractid(vnr)
SELECT DISTINCT
    a.Vnr
FROM dzp.accounts_stage a
WHERE NOT EXISTS(
    SELECT id.vnr
    FROM dzp.contractid id
    WHERE id.vnr = a.vnr
);

Also, the CROSS JOIN with the data from the latest import doesn't make much sense to me, and neither the fact that you aren't checking if the data exists already in the accounts table.


Should I use an index or a hash function on a primary key if I have 100000 rows of data? I have a table named PRODUCT that has 100,000 rows. I was wondering if either indexing, or a hash function on the primary key would result in better performance. I was also hoping someone could explain why either of these would be better.

With 100k records it shouldn't matter.  Even with 100M records it probably won't matter.  I've had queries on tables with billions of rows using good old traditional indexes perform just fine.

It's all about how much memory you have and how fast the disks are.

Granted you'll probably get some more specific information if you tell us what database you are using and provide more specifics.


MyISAM vs InnoDB for Forums I want to design a forum script with MySql. Which database engine would have better performance? MyISAM or InnoDB ?

Always use InnoDB by default.

InnoDB is durable and crash-safe.  Your database will not be corrupted in a crash.  MyISAM is very susceptible to data corruption.  This should be a more important consideration than performance -- unless you don't really care if you lose your data.

InnoDB caches both data and indexes in RAM.  MyISAM caches only indexes in RAM.  So if you have enough RAM, queries will almost certainly be faster in InnoDB.

InnoDB is the default.  It has been MySQL's default storage engine since MySQL 5.5 (circa 2010).

InnoDB continues to improve.  MyISAM is not being improved.  All of the storage engine engineering work is going into InnoDB.

InnoDB is faster anyway.  Here's a performance comparison from way back in 2007 that shows InnoDB beats MyISAM in most tests:  http://www.mysqlperformanceblog.com/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1/
Of course, benchmarks results show the performance of the benchmark, not the performance of your application.  If you want a more authoritative answer, you should run sysbench to test your database on your hardware.

There are cases where MyISAM is faster, but these cases are becoming more and more rare.  For instance, doing table-scans.  Or SELECT COUNT(*) FROM Table.  But you should treat InnoDB as the default storage engine choice until you can demonstrate a case where MyISAM beats it.


Trigger to drop table only if there are no records How do I write a DDL DROP_TABLE trigger that will only drop the table if there are no records?

I posted the question as I had just spent 2 days working this out and have found the answer:

    /****** Object:  DdlTrigger [TR_ProtectCriticalTables]    Script Date: 07/12/2015 09:02:05 ******/
    DROP TRIGGER [TR_ProtectCriticalTables] ON DATABASE
    GO

    /****** Object:  DdlTrigger [TR_ProtectCriticalTables]    Script Date: 07/12/2015 09:02:05 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TRIGGER [TR_ProtectCriticalTables]
    ON DATABASE
    FOR
     DROP_TABLE

    AS
    DECLARE @eventData XML,
            @uname NVARCHAR(50),
            @oname NVARCHAR(255),
            @sname nvarchar(255),
            @TABLEName nvarchar(255),
            @otext VARCHAR(MAX),
            @etype NVARCHAR(100),
            @edate DATETIME,
            @sql varchar(max),
            @RowCount bigint
    SET @eventData = eventdata()
    SELECT
            @edate=GETDATE(),
            @uname=@eventData.value('data(/EVENT_INSTANCE/UserName)[1]', 'SYSNAME'),
            @oname=@eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME'),
            @otext=@eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
                    'VARCHAR(MAX)'),
            @etype=@eventData.value('data(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
            @sname=@eventdata.value('data(/EVENT_INSTANCE/SchemaName)[1]', 'SYSNAME')

    ROLLBACK -- have to undo the drop to be able to get row count

    set @RowCount = (SELECT i.rowcnt
                        FROM sysindexes AS i
                            INNER JOIN sysobjects AS o ON i.id = o.id
                            inner join sys.tables as t on o.id = t.object_id
                            inner join sys.schemas as s on t.schema_id = s.schema_id
                        WHERE i.indid < 2  AND OBJECTPROPERTY(o.id, 'IsMSShipped') = 0
                        and o.name = @oname
                        and s.name = @sname)
    IF @RowCount>0
    BEGIN
        DECLARE @err varchar(1000)
        SET @err = 'Table ' + @sname +'.' + @oname  + ' has rows and cannot be dropped. Rows: ' + convert(varchar(255),isnull(@RowCount,-1))+ '. '
        RAISERROR (@err, 16, 1)
        return
    END
    else
    begin

        set @sql = '
        disable trigger [TR_ProtectCriticalTables] on database
        drop table [' + @sname + '].[' + @oname + ']'
        exec(@sql)
        set @sql = '
        enable trigger [TR_ProtectCriticalTables] on database'
        exec(@sql)
    exec(@sql)
    end

    GO

    SET ANSI_NULLS OFF
    GO

    SET QUOTED_IDENTIFIER OFF
    GO

    ENABLE TRIGGER [TR_ProtectCriticalTables] ON DATABASE
    GO


mysql 4 masters in different location Because internet is not always available i cannot just use one central database.
So there are is a DB in every location.
I need to have them all in sync, but there can be a delay of 30 minutes or maybe even 1 hour between syncing.
I use mysql 5.5.62 but i could probably upgrade if needed.
I could also have a server that every location can reach in a different place. Would that help if one DB goes down?
What would be my best strategy?
Thank you very much

There is no "perfect" solution to your requirements, but see if MariaDB with Galera is "good enough":

Have 4 or more geographically separated nodes.
If a "quorum" of the nodes (more than 50%) stay connected to each other, they stay in sync -- usually within seconds.
If, say, 3 nodes are talking to each other, but you are connecting to the 4th node (which can't see the other 3), you won't be able to write.  Having a proxy try to reach a writable node will sometimes solve this situation.
If there are multiple network problems, the "cluster" will fix itself after the network is healed.  Meanwhile you can't write.

NDB Cluster replicates via "eventual consistency".  This allows different nodes to be written independently, as you have with a flakey network.


How to put result of SQL into bind variable I'm trying to put the result of the query into a bind variable like this, but doesn't work:

var teste number;
exec :teste := (select trunc(avg(e.salary),2) from hr.employees e);

How I make it to work? I don't want to use a PL/SQL block.

exec is a PL/SQL block:

SQL> var teste number
SQL> exec select trunc(avg(user_id)) into :teste from dba_users;

PL/SQL procedure successfully completed.

SQL> print :teste

     TESTE
----------
 447392453

It is the same as:

begin
  select trunc(avg(user_id)) into :teste from dba_users;
end;
/

SQL*Plus substitution variable:

SQL> column my_value new_value teste
SQL> select trunc(avg(user_id)) my_value from dba_users;

  MY_VALUE
----------
 447392453

SQL> define teste
DEFINE TESTE           =  447392453 (NUMBER)
SQL>


Can you create a primary xml index in the CREATE TABLE statement (SQL Server)? I'm creating a temporary table and would like to create a primary and secondary XML index on a contained xml column within the CREATE TABLE statement since that is the only way I can create an index without throwing an error under snapshot isolation.
I know the syntax for creating standard (non-XML) clustered and nonclustered indexes inline, and it works fine, but I don't seem to be able to find a syntax that works for XML indexes either in documentation or via experimentation.
Does anyone know whether this is possible?

I just tried using the XML index creation commands in-lined with a table creation, and it didn't work.
I stumbled upon this article that makes it look like trying to create non-relational indexes (XML, columnstore, or spatial) doesn't work with in-lining the index creation statement in the table definition.


only last row is inserting in table when having "instead of insert" trigger on that table I have this trigger

CREATE TRIGGER [dbo].[intrigger] ON [dbo].[test]
 instead of insert
AS
begin
declare @name nvarchar (50),@city nvarchar (11),
@tdate datetime , @fid int,@fopd datetime;
select @name =i.name from inserted i;
select @city =i.city from inserted i;
select @tdate =i.date from inserted i;
select @fid =i.fid from inserted i;
select @fopd = Faculty.fopd from Faculty
    where fid= @fid;
if (@fopd > @tdate )
begin
    RAISERROR('Cannot insert ',16,1);
    ROLLBACK TRANSACTION
    RETURN;
    -- stop on error
end
insert into test
values (@name,@city,@tdate,@fid);
end

and the insert :

insert into test
values('a','h','2014-10-13',1),
('s','h','2014-10-14',2),
('y','d','2014-10-15',2),
('s','h','2014-10-18',1),
('m','a','2014-10-20',1),
('a','h','2014-10-28',2)

then I select * from test .. only the last values are inserted ('a','h','2014-10-28',2)

I have to do one insert at a time !

I used for trigger .. all lines were inserted BUT in another test it accepted the wrong values :

insert into test
values('a','h','2014-10-13',1),
('s','h','2014-10-14',2),
('y','d','2014-10-15',2),
('s','h','1990-10-18',1), -- this is wrong must not be inserted or stop all
('m','a','2014-10-20',1),
('a','h','2014-10-28',2)

what went wrong !?

The issue is your comparison is working against a single record but the INSERTED pseudo table contains multiple rows (all of the rows being inserted in the transaction). You can get around this using something like the following:

CREATE TRIGGER inTrigger ON Data
INSTEAD OF INSERT
AS
BEGIN

  IF EXISTS(
    SELECT *
    FROM INSERTED i
    INNER JOIN Faculty f ON f.Id = i.Fid
    WHERE f.fopd > i.Date
  )
  BEGIN
    RAISERROR('Cannot insert ',16,1);
  END

  INSERT INTO test (id, fid, date)
  SELECT i.*
  FROM INSERTED i
  INNER JOIN Faculty f ON f.Id = i.Fid
  WHERE f.fopd <= i.Date

END

This trigger identifies any records in the INSERTED table that match the failing criteria and if encountered it raises an error. It then proceeds with the insertion of the records that do not violate the condition. You could easily amend this to rollback the whole transaction if that is preferred.


FK on NON Primary Keys Is there any performance problem / difference if I have one Foreign Key that points to another table Indexed Fields that is NOT the PK ?

Something like

DECLARE @T1 TABLE
(
    T1_ID INT IDENTITY PRIMARY KEY,
    T1_SOMEID INT NOT NULL,
    OfficeID VARCHAR(3) NOT NULL,
    Data VARCHAR(200) NOT NULL
);

DECLARE @T2 TABLE
(
    T2_ID INT IDENTITY PRIMARY KEY,
    T1_SOMEID_FK INT NOT NULL,
    OfficeID VARCHAR(3) NOT NULL,
    OtherData VARCHAR(100) NOT NULL
);

QUESTION:
Is there a performance drop when I run a query that looks like this:

SELECT t1.T1_ID, t1.Data, t2.T2_ID, t2.OtherData
FROM @T1 t1
    INNER JOIN @T2
       ON t1.T1_SOMEID = t2.T1_SOMEID_FK
          AND t1.OfficeID = t2.OfficeID

Two things:

1 - You still need to have a UNIQUE constraint to use a field as a foreign key.

2 - In your example you WILL have a performance decrease, because by default a PK is also a clustered index.  A non-clustered index on your FK field will speed the JOIN but you will still need to pay the cost for a bookmark lookup since the other fields aren't at the leaf level of the index.  If you INCLUDE(otherdata) in your NC index then it should work fine (assuming that ID is your clustered index.


MySQL Bug reference in release notes, but where? This may be a daft question, but on the MySQL release notes, a lot of them are followed by BUG Reference numbers. But most of these BUGS don't exist when you search for them at bugs.mysql.com.

example: InnoDB: A full-text query raised an assertion. Under certain circumstances, DDL operations such as ALTER TABLE ... RENAME caused full-text auxiliary tables to be removed on server restart. (Bug #13651665)

Despite my best efforts I can't find out where to find these BUGS. Can anyone point me at the right location?

Those big-numbered bugs are internal to Oracle.  We mere mortals are not allowed to see them.

The visible bugs are numbered under about 82000 (so far), and visible at bugs.mysql.com.


Can I partially invalidate the MySQL table cache? I want to set a fairly large table to MyISAM mode to keep it cached proactively.  The table file is approximately 3GB.

After several hours, MySQL invalidates the entire table from cache, while < 0.1% of the table actually changed in the meantime.

Sometimes we need to do ±100,000 SELECT statements very quickly, which causes a cache stampede. When cached, very nice. When not cached, terrible.

What can I do to ensure the table is kept in memory?

The only thing that is definitively cached for MyISAM is an index.

The key_buffer_size variables sets up how large the MyISAM Key Cache will be.

There are two suggestions I can offer

SUGGESTION #1 : Use a Dedicated Key Cache for the Table

Did you know you could create a keycache dedicated to one or more MyISAM tables? Suppose you have a table called mydb.mytable and you want to load the entire mytable.MYI file into it. Here are the steps to set this up:

STEP 01) Get the physical size of the table's .MYI file

Run this query

SELECT index_length MYISize FROM information_schema.tables
WHERE table_schema='mydb' AND table_name='mytable';

Suppose the query returns 1234567890 for MYISize. That's 1.114978 GB = 1177.3757 MB. Round that up to 1280M (that 1.25 GB).

STEP 02) Create the Buffer with that size

SET GLOBAL mykeycache.key_buffer_size = 1280 * 1024 * 1024;

STEP 03) Associate the cache with the indexes for mydb.mytable

CACHE INDEX mydb.mytable INTO mykeycache;

STEP 04) Load the Index Pages from the .MYI into the Dedicated Cache

LOAD INDEX INTO CACHE mykeycache;

That's all. All indexes for mydb.mytable are now in that cache only. Running these commands also removes the same index entries from the general MyISAM keycache.

You could create a startup script for mysql to preload the indexes.

I have written about this subject is past posts

Aug 28, 2012 : MySQL InnoDB Index in swap
Jun 09, 2012 : Can we assign mysql Key_buffer_size value for particular table?
Mar 20, 2012 : optimal table design mysql with primay key and varchar value
Jan 26, 2012 : Less RAM than Index_length MyISAM
Dec 30, 2011 : Is splitting a 'users' table for authentication purposes a good idea?

As a sidenote, I would like to suggestion something optional. You could speed up the access time of MyISAM data by changing the ROW_FORMAT of the MyISAM you want. For the same MyISAM table mydb.mytable, here is what you do:

ALTER TABLE mydb.mytable ROW_FORMAT=Fixed;

That's it. This a good and bad side to changing ROW_FORMAT to Fixed

GOOD SIDE : Queries hitting mydb.mytable will run 20-30% faster without any additional enhancements
BAD SIDE : This bloats all VARCHAR columns and treats them as CHAR. This will bloat indexes that have VARCHAR columns as well. This could increase table and index sizes 80-100%
My Past Posts Where I Discussed This

May 10, 2011 : What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?
Jan 03, 2012 : Optimized my.cnf for high-end and busy server
May 03, 2012 : Which is faster, InnoDB or MyISAM?

SUGGESTION #2 : Switch the table's Storage Engine to InnoDB

If you switch that table to InnoDB, you set up the InnoDB Buffer Pool in such a way that data and indexes that enter the Buffer Pool will linger a lot longer. This will simulate what your original question is asking: Partially invalidating a cache. That way, mysqldumps, full table scans, and queries like these will have roadblocks to cache stampedes. The options to tweak are innodb_old_blocks_pct and innodb_old_blocks_time.

According to the Configuration Options section of the MySQL Documentation for the InnoDB Buffer Pool:

  Setting innodb_old_blocks_time greater than 0 prevents one-time table scans from flooding the new sublist with blocks used only for
  the scan. Rows in a block read in for a scan are accessed many times
  in rapid succession, but the block is unused after that. If
  innodb_old_blocks_time is set to a value greater than time to process
  the block, the block remains in the “old” sublist and ages to the tail
  of the list to be evicted quickly. This way, blocks used only for a
  one-time scan do not act to the detriment of heavily used blocks in
  the new sublist.

  innodb_old_blocks_time can be set at runtime, so you can change it temporarily while performing operations such as table scans and dumps:

SET GLOBAL innodb_old_blocks_time = 1000;... perform queries that scan tables ...
SET GLOBAL innodb_old_blocks_time = 0;

  This strategy does not apply if your intent is to “warm up” the buffer pool by filling it with a table's content. For example,
  benchmark tests often perform a table or index scan at server startup,
  because that data would normally be in the buffer pool after a period
  of normal use. In this case, leave innodb_old_blocks_time set to 0, at
  least until the warmup phase is complete.


Is it advisable to add indexes to a data warehouse's staging area? We have a data warehouse built in MSSQL 2012, Staging area, EDW, cubes and reports.

In the staging area, the tables are a 1:1 copy of those supporting the systems used to fill the DWH with. The staging area tables are TRUNC'ed and refilled daily (nightly). Then views on those tables provides input for the ETL process towards the EDW.

Besides providing a copy for the EDW to work with, these staging tables are also queried using MSSQL Management Studio by some of our analists, as not to put pressure on the source systems with their queries.

For both the ETL towards EDW as for the analist's queries, I get the idea that having an index on (some of) these tables might aid performance (greatly). Is it wise to have indexes on (large) tables that get TRUNC'ed daily. If not, why? And what kind of index (Clustered vs Non-clusterd vs someting else entirely)?

We have a similar situation as the one you described, except that these copied tables are only used for ETL (by select queries). I'll describe how we approached it, but you have to evaluate if it works for your environment (see Jon Seigel's comment at your question).

The concept is straightforward:

We index these "copy" tables in the staging area to support queries as much as possible. Most of indexes (especially on the large tables which get hit by scans) are columnstore indexes (we're on 2012 as well), some are covering nonclustered indexes. As I said, these tables are (for the time being) only used for select queries in the ETL, so we can make sure every query is supported either by a columnstore index or a covering nonclustered index.
Before the data pump (copy) from source systems into the staging area, we drop the indexes, to make bulk loads as fast as possible and to ensure we're touching source systems for the minimal amount of time. After the data pump is complete, we recreate them - the logic is set up so that the recreation is done "dynamically" (as in, indexes are not hardcoded - every index that gets dropped is then recreated exactly as it was), which allows us to change indexing without having to maintain the drop/create code.

This approach works for us, drop/recreate is faster than loading into indexed tables; well in the case of a columnstore index you don't have many other options anyway apart from partitioning & using partition switch, but we're not doing that for these tables (EDW fact tables are a different story).


json_array_elemnts() in FROM in postgresql 9.3 acting as where clause I am trying to extract array out of json in the FROM statement But issue occurs when certain json doesn't have that array in it and then query just ignores that row and doesn't get counted in the total count.

Instead the behaviour should be like if there's no array element in the particular json than it should still be counted in the total count value. Since I am using json_array_elemnts() in FROM clause and not WHERE clause.

Example:

SELECT count(*)
FROM incidents_incident
   , json_array_elements(incidents_incident.incident_data -> 'field_52ca447762ba2')
WHERE resort_id = 1
AND dt_created >= '2015-06-01 00:00:00'
AND dt_created <= '2015-08-10 00:00:00';

This query requests the cartesian product between incidents_incident and json_array_elements(...).

When one of the elements of a product is empty, the result is empty.

This result doesn't depend on the WHERE clause or the fact that one the product comes from json_array_elements(). An empty table would have the same effect.

Presumably you want an unconstrained LEFT JOIN instead of the cartesian product:

SELECT count(*)
FROM incidents_incident
LEFT JOIN json_array_elements(incidents_incident.incident_data -> 'field_52ca447762ba2')
  ON (true)
WHERE resort_id = 1
AND dt_created >= '2015-06-01 00:00:00'
AND dt_created <= '2015-08-10 00:00:00';


When to use a multidimensional relationship in an ERD? When to use a multidimensional relationship in an ERD? My textbook is in Dutch, and the two paragraphs that explain about multidimensional relationships is too vague and brief. I don't understand when it is applicable to use it, and I can't find any information on this subject on the internet.
My guess is that my textbook is naming the concept different from what it's real name is, since I can't find any information on it. In my textbook it shows the following diagram:

Unfortunately, this is the cardinality notation my textbook and teacher are using, I don't know how it translates to any different notations, but to explain how it works: T = mandatory, O = optionality, n, m, k all mean many.
It works in the opposite direction as most notations: All teachers can teach 0 or many subjects and classes, all subjects are teached 1 to many times by teachers and to classes, all classes are teached 1 to many subjects by 1 to many teachers.
Maybe if someone knows its true name I can do some research on this concept...

I'm going to share some terminology with you that may help in your research.

The relationship you pictured is what is called a "ternary" relationship.  It involves three entities.  The simple relationships we usually deal with involve only two entities, and could be called "binary".  There are "quaternary" relatinships, and so on.  The general term is "n-ary".

You may also want to look up the following:  Star schema, Ralph Kimball, Data warehousing, and multidimensional data model.  A star schema is basically the intersection of relational tables and multidimensional  modeling.  There's too much here for me to do the subject justice.  Happy hunting!


Oracle 10 column name resolution For example (just to illustrate the problem),

CREATE TABLE test_join(ID INT NOT NULL);
--1
SELECT ID FROM
test_join A
INNER JOIN test_join b ON 1=1;
--2
SELECT ID FROM
test_join A
INNER JOIN test_join b ON 1=1
INNER JOIN test_join c ON 1=1;

The first one gives expected "ORA-00918: column ambiguously defined". The second one executes without any complaints (Oracle 10g).
I wonder if it's a bug or I'm missing something obvious in name resolution rules. Oracle 11 gives error in both cases.

Thank you

It's realistically a bug in whatever version of 10g you're using that would appear to be fixed in whatever version of 11g you're seeing an error on both statements.

I don't have a 10g database in front of me to test this.  I would wager, through, that you'd get an error in both cases if you used the old-style join syntax rather than the SQL 99 syntax.  That would imply that the problem is that Oracle wasn't translating from SQL 99 syntax to old-style syntax correctly and was missing the fact that the column was ambiguously defined.


sp_getapplock not working in multiple Connection I wrote below script and run it in two tab of SQL Server management studio:

DECLARE @RC INT
Begin TRAN
    Exec @RC =sp_getapplock @Resource='MyLock', @LockMode='Exclusive', @LockOwner='Transaction', @LockTimeout = 15000

    SELECT @@SPID [session_id], @RC [return code], GETDATE()
    WAITFOR DELAY '00:00:08'
COMMIT

One of the execution wait 8 seconds and another wait 16 seconds. I was expecting it and every things is fine.
But when I open two SQL Sever management studio which connected same instance, and run this script, It is not working and both executing wait 8 seconds!

I test my scenario in two Management Studios in two PCs that connected to same SQL Server instance.

Do sp_getapplock not working in multiple Connection?

Detailing the answer added to the question, according to the sp_getapplock documentation, (emphasis added):

  The lock resource created by sp_getapplock is created in the current
  database for the session. Each lock resource is identified by the
  combined values of:

  The database ID of the database containing the lock resource.

  The database principle specified in the @DbPrincipal parameter.

  The lock name specified in the @Resource parameter.


Should I exclude mysql database from multi master replication? Running mysql in a two master replication setup. There are many databases and they are all replicating correctly including the mysql database itself.

Is it safe to replicate mysql or should I exclude it via the my.cnf ?

Since the mysql schema is usually manipulated with GRANT and REVOKE commands, there is no need to let the mysql schema replicate. Why ?

Someone could hack privileges in by means of INSERT commands into mysql.user.

Such hacks are possible because I wrote about it before

Oct 28, 2011 : MySQL: trying to create root user
Sep 12, 2011 : How do you gain access to embedded MySQL database via localhost command line?
Mar 06, 2011 : Installing default MySQL Tables in Windows (mysql_install_db)

If these hacks were done on a Master, a Slave might intercept them. The new grants on the Slave would not be active until someone restarted mysqld on the Slave or ran FLUSH PRIVILEGES; on the Slave.

So, to answer your question: exclude it via the my.cnf


Optimizing query for large table with `rand()` ordering I have a MySQL database running for a multiplayer game server and I've been trying to identify a performance bottleneck. I got the slow query logs and the slowest queries by time are like this (the matchmaking system):

select `accountID` from GameAccountProfile where `active`=1 and
`lastActiveTime`<1482549810 and `inTutorial`=0 and `defenseRating`>0 and
`league`=4 and `protectedPeriod`>1482550190 and `lastActiveTime`>1481945090 and
`accountID`!=986702 and `accountID`!=10 and `accountID`!=10 and
`accountID`!=463126 and `accountID`!=184374 and `defenseRating`>=3.3530639 and
`defenseRating`<=6.2271186 and `level`>=-8 and `level`<=2
order by rand() limit 1;

There are currently about 1.3 million rows in this table. I read that the rand() sorting may be a serious performance hit and when I ran an explain on it in MySQL Workshop on the query, it did highlight it in red as being a performance hit.

Also, I realized that the only index for any of these where conditions is on the account ID column. I'm considering adding a composite index on the "active", "inTutorial", "level", and "league" columns as they don't change often (arranged starting with the least often changed).

How can I get a random account ID with these conditions without using rand() in an order clause? I need to ensure all accounts get targeted equally but rand() seems to be a clear performance hit.

Is there any other way I can improve the performance of this query?

There is no good way to do ORDER BY RAND() LIMIT 1, but this has several suggestions.  Some depend on AUTO_INCREMENT, some based on an extra column.


What are the benefits of storing columns in JSON instead of traditional tables? Are there any benefits in using JSON(B) over traditional table structures?

Imagine having a table structure like this:

 create table table1 (
        t_id int,
        first_name varchar(20),
        last_name varchar(20),
        age int
    )

What if you stored the same columns inside a JSON(B) field like this:

{
    "first_name":"name",
    "last_name":"name",
    "age":2
}

and have a table like this:

create table table2 (
    t_id int,
    attribute jsonb
)

Correct me if I'm wrong, but since both variants are causing a row to be completely rewritten if there have been any updates or deletes on that row, then both variants are identical in that regard.

If you really want to be able to add as many fields as you want with no limitation (other than an arbitrary document size limit), consider a NoSQL solution such as MongoDB.

For relational databases: use one column per value. Putting a JSON blob in a column makes it virtually impossible to query (and painfully slow when you actually find a query that works).

Relational databases take advantage of data types when indexing, and are intended to be implemented with a normalized structure.

As a side note: this isn't to say you should never store JSON in a relational database. If you're adding true metadata, or if your JSON is describing the information that does not need to be queried and is only used for display, it may be overkill to create a separate column for all of the data points.
Check this one for detailed explanation


Mysterious missing transactional replication tokens I am looking at implementing Transactional Replication subscriptions in an AlwaysOn AvailabilityGroup. I tried to use this as a guide:

https://www.simple-talk.com/sql/backup-and-recovery/expanding-alwayson-availability-groups-with-replication-subscribers/

The reason I say tried is not in anyway besmirching the author - in this case he is dealing w/ push subscriptions and we are doing pull. There was a spectacular flame out trying to Initialize from LSN (The transactions required for synchronizing the subscription with the specified log sequence number (LSN) are unavailable at the Distributor. Specify a higher LSN.
) (perhaps b/c this is a pull subscription and it moved on, whereas in the example the push agent was disabled and then brought up on the now Primary?). Following this I just 're-initialized' the subscription (it is not that big a publication).

Anyhow, now Replication Monitor, which from experience can be an unreliable narrator up there with the greats of literature, tells me this subscription's performance is 'Excellent' and that it was last synchronized 1 minute ago as of this writing, yet when I try to insert Tracer Tokens to get a feel for latency this subscription is nowhere to be seen. In other areas of Replication monitor or checking out the SQL Server Agent jobs there are no big oddities.

What is going on? Is using pull as opposed to push subscriptions not advised in this scenario? I am somewhat befuddled at the moment and any enlightenment would be welcomed and appreciated.

This type of behavior is usually caused by an uninitialized subscription. Make sure the snapshot agent ran successfully and the distribution agent is running too.

Check out level 10 of my Stairway to SQL Server Replication for more help on troubleshooting replication.


Running a UPDATE on 3 million records I have two actors in this transaction.  One actor is a table "update_assets" is a set of records with data that is up-to-date and new. The second actor is a table "application_assets" which is a table used by an application and needs to be updated.  The problem is that the update on this is taking far too long.  The UPDATE is now going on 4 hours.  The transaction is running on a vm with 3 amd cores allocated to it. 8 gb of ram.  The application is not running in the background.

Tables:

update_assets
~3.3 million records
18 columns
1 index on amsid

application_assets
~7 million records
5 columns
1 index on id

Query:

UPDATE application_assets as ams SET number = amu.num, letter = amu.letter
FROM update_assets amu
WHERE ams.id = amu.amsid
AND ams.company_id = '4';

Explain output:

Update on application_assets ams  (cost=219382.14..747965.42 rows=3303562 width=85)
  ->  Hash Join  (cost=219382.14..747965.42 rows=3303562 width=85)
        Hash Cond: ((ams.id)::text = (amu.amsid)::text)
        ->  Seq Scan on application_assets ams  (cost=0.00..244642.25 rows=7883425 width=63)
        ->  Hash  (cost=145825.62..145825.62 rows=3303562 width=55)
              ->  Seq Scan on update_assets amu  (cost=0.00..145825.62 rows=3303562 width=55)

If I removed all of the columns on the update_assets table, except for the pertinent columns for this transaction, will that speed up the transaction?  I noticed that in the Hash Cond portion of the transaction that it does a :: casting operation on each of the columns.  If the columns were already in the text data type format, would the data be updated quicker?  Does postgresql always change data to text data for these types of joins?  What else can I do to expedite this process?

First, 4 hours seems like a long time for this.  I have done big updates and would not expect it to take this long were an inner join is involved (antijoins are more troublesome).

First to your questions:

If I removed all of the columns on the update_assets table, except for the pertinent columns for this transaction, will that speed up the transaction?   Maybe, if you rewrote the table after (using cluster, or select into).
Does postgresql always change data to text data for these types of joins?  Nope.  So turning everything to text will not help.

So what you can do is to look and see what is happening.  Initial troubleshooting steps need to be:

Run top and watch the query.  Is it I/O bound or CPU bound?   What is going on memory-usage-wise?
Does your db fit comfortably in RAM?  If not consider allocating more RAM to this vm.
Check the data types for the join columns.  Make sure these are the same.  Bad things can happen when it is casting to text for a join like this.
Check for locks.  Maybe the process is continuing slowly during a transactional period and waiting for locks periodically?


FoxPro DBF Index Corruption We are running a pretty popular application in Russia and other CIS countries through a pretty old accounting system called BEST. It's all built on FoxPro (not Visual Foxpro) DBF/CDX tables. It's open but it doesn't have API for interacting with other software. So we have to use direct access to the tables and data.

We use Sybase Advantage Database Server (ADS Internet Connector) to connect our online store to BEST. During developing, testing and running it for the first three months everything was fine. But almost half year ago we started to get index corruption errors. We tried almost everything - replaced network cables and NICs, RAID controller with HDDs, memory, reinstalled windows server. We rebuilt the tables and indexes, looked into the logs - but everything is useless. Once in two days we get index corruption error, so we have to stop the server, reindex tables and start it again and the whole company is waiting for 10 minutes.

We have problems with only 2 tables - orders and their contents. All other 300 tables that are used are never harmed. One more problem that is making the case harder is that the problem is not immediate. When the index file is broken - it's not seen. Users continue to work, until one of them starts a new session BEST or a client makes an order. So it's impossible to catch the moment when it happenes.

Right now we are blaming ADS. Does anyone know about such problems and their solutions in ADS. I've surfed all the internet looking for answers, but found none.

Thanks, if you know something.

There are two basic types of corruption that can occur with CDX indexes: Logical corruption or physical corruption. Based on the OP description that says it is not detected for a while, it sounds like logical corruption.  A common type of logical corruption is when keys either don't  exist for records, or they exist but have incorrect key values.  Physical corruption in CDX indexes typically manifests itself with index pages that are simply invalid (e.g., mismatch between key count and actual keys) or have pointers to other pages that do not exist.

Physical corruption is usually detected sooner because the application will often produce an error when attempting to process a physically corrupt page.  Logical corruption can go undetected for a longer period of time because it is quite possible to use a logically corrupt index without any errors being reported (e.g., not finding a key in a seek operation is a "normal" situation).

Assuming it is logical corruption being described in the OP, one possible reason for this is if Advantage Database Server is using a different collation than the FoxPro application with which it is sharing the data. The older style CDX indexes do not contain any information in them that defines the collation.  One quick check you might use to see if this is the case is to download the checkindex utility.  It is a simple utility that will scan a table and verify that the keys and records are in agreement with each other.  If you reindex a table with FoxPro, you could then run this utility (which will use Advantage) and determine if they are using the same collation. More information about collations here.


How to disable ldf file or limit its size Possible Duplicate:
  Why Does the Transaction Log Keep Growing or Run Out of Space?

My .ldf file size keeps increasing. I already tried to shrink it but after that it just grows again.
How can I permanently limit the filesize or perhaps even disable it entirely (since I don't think I'll ever use it).

You cannot disable/remove the LDF file. It has to exist, as it stores database transactions.
What you can do is:

Set the database recovery model to Simple

This should keep your LDF file from growing, and is set via the Options page of the Database Properties dialog.

For additional reducing, which I don't think is necessary, you can:

2 Set Auto Shrink to True

This is also done from the Options page of the Database Properties dialog:

3 Turn auto-grow off

This is done from the Files page of the Database Properties dialog:

Be aware that when a database is in the Simple recovery model, you will not be able to recover to a point in time

Or

Set the database recovery model to Full (I guess it's already set this way)
Create a full database backup
Start creating transaction log backups regularly. It's best to schedule a SQL Server job to do this

The latter scenario provides better disaster recovery as it enables recovery to a point in time
It's up to  you to decide how much data you can afford to lose


Database design help. many-to-many relationship I have four columns. All columns are varchar fields. Three of them are independent and distinct fields having many to many relation between them and the fourth one is dependent on the combination of first three. so for all the combinations of three columns there is different value for the fourth column. Now I want a design for data like this. Any help is appreciated.

Just have a table with a composite primary key on value1, value2, value2

If any of the 3 are restricted then have FK relationship on the column


How to get the current date without the time part In SQL Server 2005 how do I get the current date without the time part? I have been using GETDATE() but would like it to have a time of 00:00:00.0

The fastest if you have to iterate over a recordset and don't have date in SQL Server 2008

SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

Two different and excellent answers on StackOverflow bear this out: One, Two

Varchar conversions are one of the worst ways to do it. Of course, for one value it may not matter but it's a good habit to get into.

This way is also deterministic, say if you want to index a computed column. Even folk who write books about SQL Server get caught out with datetime conversions

This technique is also extendable.

yesterday: DATEADD(day, DATEDIFF(day, 0, GETDATE()), -1)
start of month: DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
end of last month: DATEADD(month, DATEDIFF(month, 0, GETDATE()), -1)
start of next month: DATEADD(month, DATEDIFF(month, 0, GETDATE()), 31)

Edit:

As I mentioned about determinism, varchar methods are not safe unless you use style 112.

Other answers here point out that you'd never apply this to a column. This is correct, but you may want to select a 100k rows or add a computed column or GROUP BY dateonly. Then you have to use this method.

The other answer also mentions style 110. This is not language or SET DATEFORMAT safe and fails with "british" language setting. See the ultimate guide to the datetime datatypes by Tibor Karaszi.


Access Data that is stored in Primary FileGroup On SQL Server through DBLinks on Oracle I am using dbLinks to fetch data from SQL Server 2008 to Oracle (10g)database. When I fire the command : select * from "test_table"@dbLink

Oracle throws error :

  select * from "test_table"@dbLink
  Query Execution started at Thu Mar 08 17:57:50 IST 2012
  ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
  ORA-02063: preceding line from DBLINK
  ORA-00600: internal error code, arguments: [HO define: Long fetch], [], [], [], [], [], [], []`

On further analysis I realized this happens only for tables which have TEXTIMAGE_ON [Primary] (Specifying that text, ntext, and image columns are stored on PRIMARY filegroup)

Then I went over the create command for this table which is

CREATE TABLE [dbo].[test_table](
[ADJ_ID] [numeric](19, 6) NULL,
[ADJ_TYPE] [numeric](19, 6) NULL,
[PK_HASH] [nvarchar](255) NULL,
[WH_HASH] [nvarchar](255) NULL,
[ADJ_REVISION] [numeric](19, 6) NULL,
[LAST_MODIFIED_BY] [nvarchar](255) NULL,
[LAST_MODIFIED_DATE] [datetime] NULL,
[COMMENTFIELD] [nvarchar](2000) NULL,
[DATA] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

The same error is not thrown for tables which do not have filegroup specified as PRIMARY or if I have the same table without clause TEXTIMAGE_ON [PRIMARY]

Can any one suggest a solution to overcome this error?

Thanks

The trace content is

` 1.exec sp_unprepare 1

2.SELECT "A1"."ADJ_ID" AS c000, "A1"."ADJ_TYPE" AS c001, "A1"."PK_HASH" AS c002, "A1"."WH_HASH" AS c003, "A1"."ADJ_REVISION" AS c004, "A1"."LAST_MODIFIED_BY" AS c005, "A1"."LAST_MODIFIED_DATE" AS c006, "A1"."COMMENTFIELD" AS c007, "A1"."COMMENTFIELD" AS c009, "A1"."LAST_MODIFIED_DATE" AS c010, "A1"."LAST_MODIFIED_BY" AS c011, "A1"."ADJ_REVISION" AS c012, "A1"."WH_HASH" AS c013, "A1"."PK_HASH" AS c014, "A1"."ADJ_TYPE" AS c015, "A1"."ADJ_ID" AS c016 FROM "test_table" "A1"

3.declare @p1 int
set @p1=1
exec sp_prepare @p1 output,NULL,N' SELECT "A1"."ADJ_ID" AS c000, "A1"."ADJ_TYPE" AS c001, "A1"."PK_HASH" AS c002, "A1"."WH_HASH" AS c003, "A1"."ADJ_REVISION" AS c004, "A1"."LAST_MODIFIED_BY" AS c005, "A1"."LAST_MODIFIED_DATE" AS c006, "A1"."COMMENTFIELD" AS c007, "A1"."COMMENTFIELD" AS c009, "A1"."LAST_MODIFIED_DATE" AS c010, "A1"."LAST_MODIFIED_BY" AS c011, "A1"."ADJ_REVISION" AS c012, "A1"."WH_HASH" AS c013, "A1"."PK_HASH" AS c014, "A1"."ADJ_TYPE" AS c015, "A1"."ADJ_ID" AS c016 FROM  "test_table" "A1"',1
select @p1`

Thanks.

Filegroup is completely irrelevant: a table doesn't know where it is and internal storage allocation or placement won't affect a query.

If you have a single MDF you most likely only have one PRIMARY (or default) filegroup anyway

image datatype is deprecated too: use varbinary(max).

Have you run SQL Profiler to see what Oracle is sending to SQL Server? Is there a call actually being made?

Edit:  why does the 2nd trace use a table called "P44730584C4264287A49ABEAF_ADJ"


Does changing "sa" password require a SQL restart (in mixed mode)? We discovered a SQL "sa" account being used in a way it should not have been, so we are changing sa passwords on all our SQL instances.

(We have SQL 2005 through 2017 servers running in mixed authentication mode. All users and applications should be using either domain accounts or non-sa SQL accounts to connect. I have been monitoring, but have not found any other apps, users, or non-internal spids using the sa account.)

A few questions:

Q1: Does changing the sa password require a SQL restart?

I found a few references that say a SQL service restart is required after changing the sa account password:

DBA SE: Changing sa password
SQLAuthority: Change Password of SA Login Using Management Studio

Is that true? Or only if I'm changing the authentication mode? Or only if I routinely log on as sa?

This SQL Server Central thread even suggests changing it could impact existing SQL agent jobs and other stuff; is that a concern? Or only if someone has hard-coded the SA account into an SSIS package or something?

(In case it matters, we use domain accounts for the SQL service and SQL agent service, and domain proxy accounts for jobs that call SSIS packages or PowerShell scripts.)

Q2: Can I change the sa password the "normal" way?

Can I reset it like I would any other account? Using SSMS, or more likely via:

ALTER LOGIN sa WITH PASSWORD = 'newpass';

Or would I have to enter single-user mode or something that would require planned downtime? (Note that I'd be running this from a domain account, not while connected as "sa".)

Q3: Should we try to do this password rotation on a regular basis? Or only when we find an issue?

Is this a recommended "best practice"?

Q1: Does changing the sa password require a SQL restart?

No, but changing the authentication mode does. Since you're just changing the password and the authentication mode is already set to mixed, you're good to go with just changing the password.

  Q2: Can I change the sa password the "normal" way?

Yes, it's just another SQL Login account.

  Q3: Should we try to do this password rotation on a regular basis? Or only when we find an issue?

To be quite honest, I would disable and rename the SA login. This way it won't be used, at all and if you need a highly privileged login, then you can make one as needed.


How to grant all privileges to a user from root user in MySQL on command line I want to execute a command as root to grant privileges to a certain user to a certain "empty" database.

I have tried many attempts and cannot get the syntax to work:

mysql --host=localhost --user=root --password={root password} --execute="GRANT ALL PRIVILEGES ON `otft_oms_dev_userID-001.*` TO `otft_omsUser`@`localhost` IDENTIFIED BY `{user password}` WITH GRANT OPTION"

-bash: {part of user password}`: event not found

mysql --host=localhost --user=root --password={root password} --execute='GRANT ALL PRIVILEGES ON `otft_oms_dev_userID-001.*` TO `otft_omsUser`@`localhost` IDENTIFIED BY `{user password}` WITH GRANT OPTION'

ERROR 1046 (3D000) at line 1: No database selected

mysql --host=localhost --user=root --password={root password} --execute="GRANT ALL PRIVILEGES ON 'otft_oms_dev_userID-001.*' TO 'otft_omsUser'@'localhost' IDENTIFIED BY '{user password}' WITH GRANT OPTION"

-bash: {part of user password}`: event not found

mysql --host=localhost --user=root --password={root password} --execute="GRANT ALL PRIVILEGES ON 'otft_oms_dev_userID-001.*' TO 'otft_omsUser'@'localhost' WITH GRANT OPTION"

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''otft_oms_dev_userID-001.*' TO 'otft_omsUser'@'localhost' WITH GRANT OPTION' at line 1046

mysql --host=localhost --user=root --password={root password} --execute="GRANT ALL PRIVILEGES ON otft_oms_dev_userID-001.* TO 'otft_omsUser'@'localhost' WITH GRANT OPTION"

ERROR 1046 (3D000) at line 1: No database selected

mysql --host=localhost --user=root --password={root password} --execute="GRANT ALL PRIVILEGES ON `otft_oms_dev_userID-001.*` TO 'otft_omsUser'@'localhost' WITH GRANT OPTION"

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TO 'otft_omsUser'@'localhost' WITH GRANT OPTION' at line 1

This worked for me:

mysql test -e "grant all privileges on \`test\` to 'testy'@'%' identified by 'pass\!word' with grant option"

Name a database to connect to. In this example I named test before the -e flag. Many SQL statements won't run if you don't "use" a default database.
If you grant the privileges to a database that has special characters (e.g. yours has a dash - character, you need to delimit the database name in back-ticks, not single-quotes.
Back-ticks also have meaning to the shell, that is command-substitution. You need to use backslash to escape the back-ticks so the shell won't do command substitution. The shell will strip off the backslashes before it passes the SQL command to mysql.
If you grant to all the tables in a database, you don't need to use database.*. You can just name the database.
If the password string contains some characters that are special to the shell, you need to backslash those too. For example, ! is the shell character for history substitution.


Selecting data from one table via categoryID field and displaying in different areas whilst excluding categoryID for one area I have made a very simple CMS for the company I work for. We need to display articles on our index page as well as headlines above in a carousel.

All articles are setup with a table for articles and art_categories table to link articles to their respective categories. I have been able to create a query that selects all articles and that exclude the headlines like below.

SELECT SQL_CALC_FOUND_ROWS articles.*, art_categories.*
FROM articles, art_categories
WHERE articles.categoryID=art_categories.categoryID AND articles.categoryID NOT IN(8) AND art_categories.categoryID NOT IN (8)
ORDER BY publishdate DESC LIMIT {$start},5

{$start} is used for pagination. The above query functions 100% and excludes the headlines category from the general article display area.

Now the problem I have is not being able to display the headlines on the same page by selecting them from the same table via their respective categoryID

SELECT articles.*, art_categories.*
FROM articles, art_categories
WHERE articles.categoryID=art_categories.categoryID AND articles.categoryID=8 AND art_categories.categoryID=8
ORDER BY publishdate DESC LIMIT 5

Any advice would be greatly appreciated.

Thanks the issue has been resolved and question is closed

You can use LEFT JOIN for this:

SELECT
     articles.*,
     art_categories.*
FROM art_categories LEFT JOIN articles
ON articles.categoryID=art_categories.categoryID
WHERE art_categories.categoryID=8
ORDER BY publishdate DESC LIMIT 5

as advice - avoid use *, better to list all columns by name, it also allow avoid duplicates and/or arrange aliases for them


How can I build a risk matrix in databse tables? I'd like to build a risk matrix in a SQL database.  A very basic one looks like so:

------------------------------------
|          |Consequence            |
|Likelihood|Low    |Medium |High   |
|Low       |Level 1|Level 1|Level 2|
|Medium    |Level 1|Level 2|Level 3|
|High      |Level 2|Level 3|Level 4|
------------------------------------

My current ERD looks like this:

I will have a software program that will read from another table (it exists out of frame immediately to the left of that screenshot).  This other table contains values for likelihood and consequence associated with a particular entry in that table.  The program will then query the risk matrix and return a risk level (eg. Having likelihood = Low and consequence = High would return a risk level = Level 1).

As you can see in the diagram, on the risk matrix table I have foreign keys linking likelihoodid and consequenceid to their respective tables.  I also have a composite primary key across likelihoodid and consequenceid.  Currently there is a composite unique key across likelihoodid and consequenceid also because some research I did suggested I needed this, but it seems wrong to me.

Is this design OK, completely wrong, or needs some tweaking?

Edit

DDL as requested:

CREATE TABLE IF NOT EXISTS `likelihoods` (
  `likelihoodid` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `shortname` varchar(50) NOT NULL,
  `longname` tinytext DEFAULT NULL,
  PRIMARY KEY (`likelihoodid`)

CREATE TABLE IF NOT EXISTS `consequences` (
  `consequenceid` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `shortname` varchar(50) NOT NULL,
  `longname` tinytext DEFAULT NULL,
  PRIMARY KEY (`consequenceid`)

CREATE TABLE IF NOT EXISTS `riskmatrix` (
  `likelihoodid` tinyint(3) unsigned NOT NULL,
  `consequenceid` tinyint(3) unsigned NOT NULL,
  `levelid` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`likelihoodid`,`consequenceid`),
  UNIQUE KEY `UK_likelihoodid_consequenceid` (`likelihoodid`,`consequenceid`),
  KEY `FK_riskmatrix_consequences` (`consequenceid`),
  KEY `FK_riskmatrix_risklevels` (`levelid`),
  KEY `FK_riskmatrix_likelihoods` (`likelihoodid`),
  CONSTRAINT `FK_riskmatrix_consequences` FOREIGN KEY (`consequenceid`) REFERENCES `consequences` (`consequenceid`),
  CONSTRAINT `FK_riskmatrix_likelihoods` FOREIGN KEY (`likelihoodid`) REFERENCES `likelihoods` (`likelihoodid`),
  CONSTRAINT `FK_riskmatrix_risklevels` FOREIGN KEY (`levelid`) REFERENCES `risklevels` (`levelid`)

OK based on @Lennart's feedback, this is a pretty good design for a risk matrix in a database, to suit my needs as described in the question.  I've removed the Unique Key, which was duplicating the Primary Key.

CREATE TABLE IF NOT EXISTS `likelihoods` (
  `likelihoodid` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `shortname` varchar(50) NOT NULL,
  `longname` tinytext DEFAULT NULL,
  PRIMARY KEY (`likelihoodid`)

CREATE TABLE IF NOT EXISTS `consequences` (
  `consequenceid` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `shortname` varchar(50) NOT NULL,
  `longname` tinytext DEFAULT NULL,
  PRIMARY KEY (`consequenceid`)

CREATE TABLE IF NOT EXISTS `riskmatrix` (
  `likelihoodid` tinyint(3) unsigned NOT NULL,
  `consequenceid` tinyint(3) unsigned NOT NULL,
  `levelid` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`likelihoodid`,`consequenceid`),
  KEY `FK_riskmatrix_consequences` (`consequenceid`),
  KEY `FK_riskmatrix_risklevels` (`levelid`),
  KEY `FK_riskmatrix_likelihoods` (`likelihoodid`),
  CONSTRAINT `FK_riskmatrix_consequences` FOREIGN KEY (`consequenceid`) REFERENCES `consequences` (`consequenceid`),
  CONSTRAINT `FK_riskmatrix_likelihoods` FOREIGN KEY (`likelihoodid`) REFERENCES `likelihoods` (`likelihoodid`),
  CONSTRAINT `FK_riskmatrix_risklevels` FOREIGN KEY (`levelid`) REFERENCES `risklevels` (`levelid`)


Scrubbing sensitive data I am looking for an automated solution to scrub sensitive data from my prod environment to my DEV and DEVINT environments so that I don't have to write lots of code to get this done. Does anyone know if Data Quality Services and a data cleansing step in SSIS can do the trick for me? Or, does anyone else have any suggestions to scrub my data without having to write TSQL to do it?

Scrubbing sensitive data is a vast topic. You basically need to define as per your company's data protection policy - what is considered PII (Personally Identifiable Information) or what is sensitive that you don't want other people to see ?

SQL Server 2012* does not have any native tools to mask or scrub sensitive data.

In my company, we have developed in-house tools that will mask client sensitive data like email, phone, names, and many more.

If you have to do it on a repeating basis, I would suggest you to take the schema from PROD and then use 3rd party tools like data generator from RedGate to generate test data.

There are several methods that will be useful to mask sensitive data (once you have identified - what is considered sensitive) like :

Masking Personal Identifiable SQL Server Data
Obfuscating your SQL Server Data

Brent has written a good blog post on How Do You Mask Data for Secure Testing?

* In SQL Server 2016, Microsoft has introduced Dynamic data Masking

  It’s a data protection feature that hides the sensitive data in the result set of a query over designated database fields, while the data in the database is not changed. Dynamic data masking is easy to use with existing applications, since masking rules are applied in the query results.


Howto reliably ship WAL log files between master and slave I have a replication master and a slave on two different computers. Actually both databases are running in docker containers, and their WAL archive directories are exposed to the host machine using docker's bind mount. They are also connected with streaming replication, but the network connection between them is not reliable. Sometimes the network is down for a minute or so. Usually the speed of the network is fine, but there are outages a couple of times per day.

I just can't mount the slave's directory on the master, because of the unreliable network. The master postgres should be able to execute the archive command quickly, even if the network is down. I need a good utility that allows me to reliably ship log files between the two computers? E.g. start the transfer of a file, retry until it succeeds, then delete the original file after the transfer has been completed. rsync is not an option, because it does not move files, only copies them. (At least I don't know how to use it for this purpose.)

Does anyone know a solution that can be used out of the box? I can write my own program for this, but I don't want to reinvent the wheel.

I'd use streaming replica "out of the box".  It will restart itself after a network hiccup just fine.  If you are afraid the WAL will get recycled before it is streamed, use a replication slot to prevent that from happening.


Oracle to T-SQL OPENQUERY special character conversion issues I'm struggling to figure out where the character encoding issue on my Linked server may be coming from here. The ZPDT_PAT_ALPHA column should have a degrees symbol at the end, as shown by the DUMP.

SELECT *, CHAR(176)
FROM  OPENQUERY([R_Pronto], 'SELECT DUMP(ZPDT_PAT_ALPHA), ZPDT_PAT_ALPHA
            FROM LINKED.ZCUSPRODATTR0')

Here are the NLS parameters. NLS_LANG is set in the registry "AMERICAN_AMERICA.WE8MSWIN1252"

And the linked server settings using Microsoft OLE DB Provider for ODBC Drivers

I appreciate any pointers as to what I can adjust to make the special characters appear correctly. I unfortunately don't have access to the Oracle DB to make any changes.

The application that sits on top of this DB is able to show the character - I believe it interfaces directly with Oracle.

Thanks

Edit: Showing the TRANSLATE USING NCHAR_CS

Edit2:

The solution was to change the NLS_LANG value in the registry to "AMERICAN_AMERICA.US7ASCII" because of reasons described in these answers:

If we have US7ASCII characterset why does it let us store non-ascii characters?
difference between NLS_NCHAR_CHARACTERSET and NLS_CHARACTERSET for Oracle

Now the characters are coming through correctly.

Thanks @wernfried-domscheit !


If positive, sum all items. If negative, return each one I am needing to find a way to SUM() all of the positive values for num and return the SUM() of all positive numbers and an individual row for each negative number.  Below is a sample DDL:

Create Table #Be
(
    id int
    , salesid int
    , num decimal(16,4)
)

Insert Into #BE Values
    (1, 1, 12.32), (2, 1, -13.00), (3, 1, 14.00)
    , (4, 2, 12.12), (5, 2, 14.00), (6, 2, 21.23)
    , (7, 3, -12.32), (8,3, -43.23), (9, 3, -2.32)

And this is my desired output (positive numbers for each salesid SUM() and negatives get an individual line returned):

salesid    num
1          26.32
1          -13.00
2          47.35
3          -12.32
3          -43.23
3          -2.32

Try this:

SELECT   salesid, sum(num) as num
FROM     #BE
WHERE    num > 0
GROUP BY salesid
UNION ALL
SELECT   salesid, num
FROM     #BE
WHERE    num < 0;

If you want both the sum values in one row then you must create a maxValue (and minValue) function and use this as sum(maxValue(0, num)) and sum(minValue(0, num)). This is described in: Is there a Max function in SQL Server that takes two values like Math.Max in .NET?


How to query data in MySQL and be able to save in SQL Server? (Python) Good day I have a doubt, I have a code generated in Python which he does is to query a MySQL table named INFORMATION_SCHEMA.PROCESSLIST as follows:

SELECT *, IF (time> = 10, "Bad", "Good") AS State FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id <> connection_id ();

Once this fulfills my conditions by means of python code I tell you to save those processes and also kill them with the command KILL, but the data that I send to make them in a table that creates in MySQL.

Now what I want is to continue browsing in MySQl, but I want to know if there is any way to save the queried data to a table but to SQL Server, basically query MySQL and save it to SQL Server.

Annex the code:

#! / Usr / bin / python
# - * - coding: UTF-8 - * -
Import os, sys
Import signal, os, sys
Import MySQLdb
connection_mysql = MySQLdb.connect (host = '10 .248.204.43 ', user =' root ', passwd =' sahc ', db =' Library ')

cur = conexion_mysql.cursor ()
query = ('SELECT *, IF (time> = 10,' Bad ',' Good ') AS State FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id <> connection_id ()')
cur.execute (query)
for row in cur.fetchall ():
    result = row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8]
    sql = "INSERT INTO test VALUES (" + str (row [0]) + "," + "'" + (row [1] or' ') + "'" + "," + " [2] or "+", "+", "+", "+ (row [3] or '') +" '"+", "+" ) + "," + "," + "," + ", +", "+", "(Row [6] or '') [7] or '') + "'" + "," + "now ()" + ")"
    cur.execute (sql)
    connection_mysql.commit ()
    print "Successfully Saved Data"
    if row [0] == row [0]:
        kill = "KILL" + str (row [0])
        cur.execute
        connection_mysql.commit ()
        print "Deleted data"

As you consult and save in MySQL, I want to continue browsing in MySQL but save the data in SQL Server.

I would appreciate the help :)

There is an option in MSSQL called linked server, where you can connect to MySQL server by using MSSQL queries. So you can query data from MSSQL linked server (MySQL data) and insert data into MSSQL.

I hope this works for your query. Give a try.


SQL Server Filetable document properties I'm using SQL Server 2012's Filetable to store documents and search them with Semantic Search.

I was wondering if there is a way to list all the document properties (meta data). There is a way to let Full-Text Search index and search through document properties. I can make a list of properties SQL Server Indexes with the following statement:

 SELECT * FROM sys.registered_search_properties;

I can also extend this list with SQL or using a program.

What I didn't find is a way to list the actual information. What I'm looking for is a list like:

Author: Ruud van de Beeten
Title:  Test document
custom property: custom value

Can anyone point me in the right direction?

Edit: Bob Beauchemin created a ticket describing my problem. As the DMV is not listing the property values, I can't use it in my project.

I ended up using C# to list custom properties with the OleDocumentProperties object. This object can read properties from an Office document. I'll keep watching this question, hoping for a better solution.

It looks like (at this time) the best you are going to be able to do is use the keywords on the property, join them up to the doc and cross your fingers it is enough.

SELECT
    keyword,
    display_term,
    column_id, document_id, property_id
FROM sys.dm_fts_index_keywords_by_property
(
    DB_ID('FileTableDB'),
    OBJECT_ID('FileTableTb')
);

MSDN on that DMV - http://msdn.microsoft.com/en-us/library/ee677646.aspx

Here is a discussion with Bob Beauchemin and I on the topic:
https://twitter.com/bobbeauch/status/275101491084292096

Bob told me about the DMV mentioned above and pretty much confirmed that it is the most likely way.

Here is the Connect ticket Bob created for this topic:
https://connect.microsoft.com/SQLServer/feedback/details/773212/provide-complete-fts-property-values-in-a-dmv-side-table

We also go on to talk about how it might be better to have a preprocessor of sorts to pull the meta info out, store it, (not sure if you normalize format of your docs or not), and then reprocess if modified. Bob suggests perhaps being able to pull this off with a SQLCLR trigger/proc which would be a rather clever solution and get your hands all kinds of dirty.


Oracle: Creating Case Insensitive Database with NLS_COMP and NLS_SORT I try to create an Oracle 12c Database (Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production) which ist case insensitive. My first try is just a try to test my aims:

select count(1) from dual where 'a' = 'A';
--0 case sensitive

alter session set nls_sort = 'BINARY_CI';
alter session set nls_comp = 'LINGUISTIC';

select count(1) from dual where 'a' = 'A';
--1 --> Case insensitive

alter session works fine but leads to massive problems with tablescans. Oracle ignores all indexes, which didn't created as BINARY_CI / LINGUISTIC.

As a solution for the tablescan problem I try to create a Database, which has the apropriate nls_sort and nls_comp setted as I needed. I started DBCA (Database Configuration Assitent) and created a Database with the two INI parameters:

nls_comp=LINGUISTIC
nls_sort=BINARY_CI

The Database created but didn't work as expected:

select count(1) from dual where 'a' = 'A';
--0 case sensitive

After some investigation I found:

select * from NLS_DATABASE_PARAMETERS where parameter in ('NLS_COMP', 'NLS_SORT');
/*
NLS_COMP    BINARY
NLS_SORT    BINARY
*/

select * from NLS_INSTANCE_PARAMETERS where parameter in ('NLS_COMP', 'NLS_SORT');
/*
NLS_SORT    BINARY_CI
NLS_COMP    LINGUISTIC
*/

Which means, somehow the NLS Parameter vanished from my Database. I wonder if the reason is the seed Database, which DBCA used as a template. My NLS Parameter were used by the instance but there they have no effect on my query.

Should I create a Database without DBCA? How? ( https://docs.oracle.com/cloud/latest/db121/ADMIN/create.htm#ADMIN11073 ) Or can I change somehow the NLS-Parameter inside the Database?

Regards
  Dezsö

Even if you manage to create a database with nls_comp=LINGUISTIC, nls_sort=BINARY_CI (from scratch (Custom Database or manually), not seed or template) , the indexes will be still created without these settings and you have to take specify them manually.

SQL> select * from NLS_DATABASE_PARAMETERS where parameter in ('NLS_COMP', 'NLS_SORT');

PARAMETER       VALUE
--------------- ---------------
NLS_COMP        LINGUISTIC
NLS_SORT        BINARY_CI

SQL> select * from NLS_INSTANCE_PARAMETERS where parameter in ('NLS_COMP', 'NLS_SORT');

PARAMETER       VALUE
--------------- ---------------
NLS_SORT        BINARY_CI
NLS_COMP        LINGUISTIC

SQL> create table t1(id varchar2(1), something char(2000));

Table created.

SQL> insert into t1 select 'a', 'a' from dual connect by level <= 10000;

10000 rows created.

SQL> insert into t1 values ('b', 'b');

1 row created.

SQL> commit;

Commit complete.

SQL> create index t1_i1 on t1(id);

Index created.

SQL> exec dbms_stats.gather_table_stats(user, 'T1', cascade=>true, estimate_percent=>100, method_opt=>'for all columns size auto for columns id size 2');

PL/SQL procedure successfully completed.

SQL> select /*+ index(t1 t1_i1) */ count(*) from t1 where id = 'B';

  COUNT(*)
----------
         1

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  34cypvvhnwczh, child number 0
-------------------------------------
select /*+ index(t1 t1_i1) */ count(*) from t1 where id = 'B'

Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   907 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     2 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |  5001 | 10002 |   907   (1)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(NLSSORT("ID",'nls_sort=''BINARY_CI''')=HEXTORAW('6200'))

Index can not be used, hint does not help, and the estimated cardinality is just simply CEIL (NUM_ROWS / NUM_DISTINCT). This time with the proper index:

SQL> create index t1_i2 on t1(NLSSORT("ID",'nls_sort=''BINARY_CI'''));

Index created.

SQL> exec dbms_stats.gather_table_stats(user, 'T1', cascade=>true, estimate_percent=>100, method_opt=>'for all columns size auto for columns id size 2, SYS_NC00003$ size 2');

PL/SQL procedure successfully completed.

SQL> select count(*) from t1 where id = 'B';

  COUNT(*)
----------
         1

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9pvkbfkycz6q4, child number 0
-------------------------------------
select count(*) from t1 where id = 'B'

Plan hash value: 3225262789

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE   |       |     1 |     3 |            |          |
|*  2 |   INDEX RANGE SCAN| T1_I2 |     1 |     3 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."SYS_NC00003$"=HEXTORAW('6200'))