Using IDs from multiple tables in a single column One of my co-workers created a schema similar to the following.  This is a simplified schema including only the parts necessary to address this question.

The system rules are as follows:

Departments can have 0 to many divisions.
A division must belong to only one department.
An article can be assigned to either a department, or a division of that department.

The schema is:

Department
----------
DepartmentID (PK) int NOT NULL
DepartmentName varchar(50) NOT NULL

Division
--------
DivisionID (PK) int NOT NULL
DepartmentID (FK) int NOT NULL
DivisonName varchar(50) NOT NULL

Article
-------
ArticleID (PK) int NOT NULL
UniqueID int NOT NULL
ArticleName varchar(50) NOT NULL

He defined the schema using an imaginary rule (for lack of a better term), that all DepartmentIDs would be between 1 and 100, and all DivisionIDs would be between 101 and 200.  He states that when querying the Article table, you will know whether the UniqueID is from the Department table or the Division table based on what range it falls into.

I think this is a poor design and proposed the following alternative schema:

Department
----------
DepartmentID (PK) int NOT NULL
ParentDepartmentID (FK) int NULL /* Self-referencing foreign key.  Divisions have parent departments. */
DepartmentName varchar(50) NOT NULL

Article
-------
ArticleID (PK) int NOT NULL
DepartmentID (FK) int NOT NULL
ArticleName varchar(50) NOT NULL

I believe this is a properly normalized schema and properly enforces relationships and data integrity, while honoring the business rules outlined above.

My specific question is this:

I know using one column to contain values from two domains is poor design, and I can argue the benefits of the foreign key in the Article table.  However, can someone provide a reference to a specific database design article / paper that I can use to backup my position.  If I can point to something concrete, it will make it that much easier.

Your self-reference could introduce a hierarchy of divisions within divisions. You need code (usually a trigger) to ensure that no divisions allow this.

The original schema using encoding is broken too. You have no enforceable FK

Personally, I'd consider this with a check constraint to ensure than only one of DepartmentID and DivisionID per row is populated

Article
-------
ArticleID (PK) int NOT NULL
DepartmentID (FK) int NULL
DivisionID (FK) int NULL
ArticleName varchar(50) NOT NULL

Why?

no code needed
no nested divisions
full DRI via FKs to parent table
enforced "multiple parent, only one at a time"

You could also use computed columns to add a column that says "department" or "division"

Another way: mandate that each department has at least one division...


Apache Cassandra INSERT-only permissions Looking at the Cassandra documentation, it appears that the smallest write-only permission on a table you can grant is MODIFY, which includes UPDATE, DELETE and TRUNCATE.

Is there a proper way to restrict this further to ONLY grant INSERT and no other permission, ideally for the user/role itself, without requiring further logic application side?

I would like for ONLY the DBA to have the ability to alter data in a table, the application user should have INSERT-only rights.

If it's not possible using the permission model only, could triggers be the answer and are there known drawbacks for this approach?

No, there is no such way right now because it's how the Cassandra works.

INSERTS in Cassandra are really upserts, so if you insert data with the same primary key, then they are modified, like you do with UPDATE.  And similarly, if you insert null values, then you're deleting the data.


How safe are PostgreSQL CONCURRENTLY option As I know CONCURRENTLY let DATABASE create an index without any lock though It could incur more CPU usage.

I am planning to create an index in production DATABASE with the option.

Before applying that I want to check for sure.

Is it safe enough to use in production DATABASE regardless of how many rows there and how many INSERT, UPDATE is executed while creating the index?

Yes, that is safe, meaning that it cannot damage your database and that the index will be correct once the statement succeeds.

Creating an index uses I/O and CPU resources, that is unavoidable.

If CREATE INDEX CONCURRENTLY fails, it will leave behind an invalid index that you should drop, since it uses space.

CREATE INDEX CONCURRENTLY will require an ACCESS EXCLUSIVE lock on the table, but differently from a normal CREATE INDEX which locks the table for the whole duration of the operation, it takes only a very short lock at the very end. This is normally not a problem, but it can be a problem if you have long running transactions that use the table: CREATE INDEX CONCURRENTLY will have to queue behind these transactions, and all later statements will have to queue behind it.

So make sure you have no long running transactions.


SQL Server : explicitly create an index on a primary key and unique fields UPDATED:

I'll make the question clearer as the first answer isn't quite what I was looking for.

How can I execute this create table statement and ensure that the two automatically created indexes are stored on MY_INDEX_FILEGROUP as I am assuming that these automatically created indexes will be stored on MY_DATA_FILEGROUP that the table data will be stored in.

CREATE TABLE Product.Product(
    ProductId tinyint identity(1,1) constraint PK_Product_Product_ProductId primary key,
    ProductName varchar(50) unique not null,
    SupportEnded date default null null
) ON MY_DATA_FILEGROUP
GO

is it possible to seperate the automatically generated indexes into a different filegroup, from the data?

And as a seperate but related question is it possible to enforce a constraint like PRIMARY KEY or UNIQUE whilst preventing the automatic creation of the associated index?

How can I execute this create table statement and ensure that the two
  automatically created indexes are stored on MY_INDEX_FILEGROUP as I am
  assuming that these automatically created indexes will be stored on
  MY_DATA_FILEGROUP that the table data will be stored in.

The table will exist on whichever filegroup contains the clustered index, because the clustered index is the data.

By default, a primary key index is clustered, so if you specify a different filegroup for that, the table ends up there.

  is it possible to seperate the automatically generated indexes into a different filegroup, from the data?

Yes, but don't. Bears will eat you.

  And as a seperate but related question is it possible to enforce a constraint like PRIMARY KEY or UNIQUE whilst preventing the automatic creation of the associated index?

No, this is how SQL Server enforces the constraint behind the scenes. Technically, SQL could use any mechanism to enforce constraints; it just makes the most sense to use an index to do it.


Does adding an auto_incrementing number to a MySQL partitioned table that is not part of the primary key, cause consistency issues? I have a table that has a primary key which does not include an auto_incrementing number. The table needs to be partitioned (on that primary key) for the overall strategy of reducing contention points.

I need to add an auto_incrementing number outside the primary key/partitions, but I am concerned about how MySQL will be able to keep the right sequenced number, if under the hood, all partitioned tables are in essence different tables and as that auto_incrementing number cannot be in a separate unique index.

So the question is, will I get consistency issues with the sequence doing this or does MySQL have a way to keep it accurate?

Rarely does an app need the constraint about an AUTO_INCREMENT being UNIQUE.  Uniqueness is not guaranteed.

Sometimes it is best to turn

PRIMARY KEY(id),  -- (id is auto_increment)
INDEX(foo)

Into

PRIMARY KEY(foo, id),
INDEX(id)

The purpose is to cluster the rows based on foo because most of the access is done with WHERE foo = constant.

The INDEX(id) is all that AUTO_INCREMENT requires.  It does not require uniqueness; it does not require PRIMARY KEY.

I added id to the PK on the assumption that foo is not, itself, unique.  The combination foo, id is unique (assuming you are not playing games with id), thereby satisfying the uniqueness requirement of PRIMARY KEY.

Back to your question -- Sure, go ahead and add the id.  It will require

INDEX(id)  or  INDEX(id, ...)

Implementation insight...  When a table is opened, the auto_inc value is initialized by doing SELECT MAX(id) FROM tbl.  If that table is partitioned, the code already knows how to check MAX(id) from each partition; then it can easily get the overall MAX.

MySQL 8.0 has a slight variation on that -- it saves the auto_inc value somewhere else so that after a server restart, you will not reuse any ids that got lost in what might have been a crash that necessitated rollbacks.  DELETEing the last id just before a graceful shutdown is another issue covered.

BTW -- See if you can use the above trick instead of partitioning.  I have found very few valid cases for using partitioning.  Provide more details on your case; I can probably spot whether it will help or not.

Ref:  http://mysql.rjweb.org/doc.php/partitionmaint


Postgresql sudden storage increase at regular interval? I am running Postgresql 11 in production and observing a weird step function kind of thing in storage as follow:

The above graph is for a single day and it is happening everyday at random times. I have checked and there are no bulk inserts from our side. Also, these steps are weird because storage again comes back to its normal value after some time.
I also have read replica and because of this, there is sudden increase in disk usage of read replica which results in high replication times of around 30s, which in further results in conflict with recovery problem. Is there a way to deal with this? Is this common behaviour of Postgres. I am running postgresql on Google Cloud SQL and it has been happening for a long time but I observed today, so I am wondering is it a normal thing or do I need to check something?
One suspicion I have is that it might be because of vaccumm but I am not sure? Please help me with this.
Edit 1:
There are spikes like below whenever this occur for read/write io. Red is the write one and blue is the read one

Since you have no filesystem access you won't get the answer but some guesses only, so here comes mine: if there are no bulk INSERTs, this might be some temporary space created by SELECT, for example big query with "Sort Method: external merge Disk", or some TEMPORARY tables, materialized view REFRESH etc. Such query would obviously take some time, so check your query log for very long duration and EXPLAIN them if found.
The usage sliding down, however, suggests it's WAL-related. SELECT * FROM pg_stat_archiver or pg_stat_replication might show something interesting, e.g. I check for streaming lag as follows:
SELECT active, pg_wal_lsn_diff(pg_current_wal_insert_lsn(), restart_lsn) AS retained_bytes FROM pg_replication_slots
What also might happen is that the space reported itself is skewed compared to datasize. For example (this is not the case, but makes reading such graphs easier) reclaiming disk space from btrfs or after removing many files on ext3/4 filesystem might take some significant time (I've seen like 10 minutes or so on btrfs).


tsql: table data size won't shrink after large row deletion i have a table with 12 months+ data in it.
I backup the table then delete all rows keeping only rows inserted in the last month.
But the table size won't shrink, nor the underlying physical file.

dbcc showcontig ('mytable') with tableresults;

ObjectName  ObjectId    IndexName   IndexId Level   Pages   Rows    MinimumRecordSize   MaximumRecordSize   AverageRecordSize   ForwardedRecords    Extents ExtentSwitches  AverageFreeBytes    AveragePageDensity  ScanDensity BestCount   ActualCount LogicalFragmentation    ExtentFragmentation
mytable 478624748   PK_Log  1   0   21344   467392  94  8033    237 0   2675    12918   2862,346    64,6361996540647    20,6517532316743    2668    12919   77,8392053973013    71,7757009345794

exec sp_spaceused 'mytable', @updateusage = 'TRUE'

name    rows    reserved    data    index_size  unused
LogTrace    467392      8984976 KB  8930056 KB  21984 KB    32936 KB

These values means each row of the table is about 19MB!

I tryed to rebuild the index with no change:
    ALTER INDEX PK_myindex ON mytable REBUILD

Other things i tryed:

select
[FileSizeMB]    =
    convert(numeric(10,2),round(a.size/128.,2)),
[UsedSpaceMB]   =
    convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) ,
[UnusedSpaceMB] =
    convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) ,
GrowthPct   =
    case when a.status & 0x00100000 = 1048576 then a.growth else null end,
GrowthMB    =
    convert(int,round(case when a.status & 0x00100000 = 1048576 then null else a.growth /128.000 end,0)),
[DBFileName]    = a.name
from
sysfiles a;

result

FileSizeMB  UsedSpaceMB UnusedSpaceMB   GrowthPct   GrowthMB    DBFileName
9742.38 9740.06 2.31    NULL    1   mytable
72.25   15.45   56.80   NULL    1   mytable_log
3.00    0.06    2.94    NULL    1   mytable_indexes

I don't know what to do more, any idea ?

Try ALTER INDEX ALL ON mytable REBUILD

With the index size value being so high, this points to a non-clustered index. Clustered index size is very small usually.

You can check with the allindexes parameter on the DBCC

dbcc showcontig ('Risk') with allindexes, tableresults;

For example, one of my tables. A billion rows, 70GB table, 450MB index

name      rows       reserved  data     index_size  unused
BigTable  1041302384 71256288  70793880 460832      1576

Edit, Jan 2012

Note that deleted records are not physically removed on DELETE. A "ghost clean up" process does that afterwards.


Having clause question maybe I have 3 tables.

Grades
Disciplines
Students

I want to select only the disciplines that doesn't have grade 2. What am I doing wrong?
SELECT DISTINCT DISC_NAME
FROM STUDENTS, DISCIPLINES, GRADES
WHERE
GRADES.ID= STUDENTS.ID
AND
GRADES.KOD_DISC = DISCIPLINES.DISC_ID
GROUP BY GRADE
HAVING
GRADE != 2

HAVING is for filtering on aggregated results. You could use it to solve your problem if you rewrite it slightly so it's filtering on the number of times a DISC_NAME has a result from your joins which has GRADE=1. For this aggregate, you'd be grouping by the DISC_NAME and you can count matches with a case when
select d.disc_name
from students s
    ,disciplines d
    ,grades g
where g.id= s.id
and   g.kod_disc = d.disc_id
group by d.disc_name
having sum(case when g.grade = 2 then 1 else 0 end) = 0

(assuming the grade column comes from grades and disc_name is part of disciplines)
You could also achieve the same with an anti-join query. Here I've removed the join to students because you don't seem to use it (other than making sure the grade row belongs to a student row, which I would assume isn't necessary). I've also switched to using more modern ansi join syntax - you could use either.
select d.disc_name
from disciplines d
where not exists (select null
                  from   grades g
                  where  d.disc_id = g.disc_id
                  and    g.grade = 2
                 )


Create a Function in sql server without specifying return_data_type I am trying to create a Scalar-valued function in SQL server, without specifying the return_data_type

i want to be able to return any datatype based on a parameter sent by the function caller

e.g.

CREATE FUNCTION GenerateRandomData
(
    @Datatype varchar(20)
)
RETURNS <Return any datatype specified by @Datatype parameter>
AS
BEGIN
    -- Declare the return variable here
    IF @Datatype in ('varchar','char','nchar','nvarchar','text','ntext')
        Begin
            RETURN 'String'
        end
    Else
    IF @Datatype in ('int','bigint','smallint')
        Begin
            RETURN 100
        end
    Else
    IF @Datatype in ('datetime','smalldatetime')
    Begin
        RETURN getdate()
    end

END

You can't.

The return datatype must be specified in a function. It is not an optional part of the grammar. You can return sql_variant though.


Did anybody use sqlcmd mode in practice? SQLServer 2005 introduced something called  SQLCMD Mode msdn link.

At first glance this mode adds variable subsitution from command line batch files,  and some escaping to OS commands.

Is this feature used in your environments, production or test?

I use the sqlcmd mode in SSMS on occasion. Typically when migrating databases from one environment to another.  For example if I need to backup the database from production, then restore it to dev, I'll do this all in one script using SQLCMD mode to switch servers.  I've used to here and there for one thing or another, but I don't use it all that often.


Is it correct, order of where clause doesn't matter when it is used with join? I have question on execution of queries in which join is used with where clauses.
I read this question on join with where conditions.

I tried options like disabling rules and checked actual query execution plan.

select table1.col1, table2.col2
from T1 table1,
T2 table2
where table1.col1 = table2.col1
and table1.col3 = 10

Actual query execution plan in above case was showing join first and then filter when rule is disabled.

But when I changed the order of where clause with where tabel1.col3 = 10 and table1.col1 = table2.col1, it was filtering out the rows before join.

Thus I think in case of rules disabled order of where clause matters but not in case of rules enabled which is default condition.

So is it correct that order doesn't matter with default behavior of SQL Server?

The order of items in the where clause should not make a difference, especially if you use the preferred join syntax as follows:

select a.col1,
       b.col2
 from table1 a
 join table2 b on b.col1 = a.col1
where a.col3 = 10

This keeps the join conditions separated from the filters.


Remote queries at SNAPSHOT isolation level I'm experimenting with snapshot isolation level. Whenever I try to access a linked server, I get an error:

  Remote access is not supported for transaction isolation level "SNAPSHOT"

I wasn't able to find anything meaningful in BOL or Google on this issue.

The limitation seems rather frustrating. Is there a workaround, other than caching all the remote data locally?

If you dig into the internals of how snapshot isolation is implemented in SQL Server, the reason why remote access is not supported becomes clear.

When snapshot isolation is enabled, up to 14 bytes of versioning information are appended to each row that is modified. The additional bytes contain the transaction sequence number of the transaction that modified the row, along with a pointer to the versioned row in tempdb.

A transaction running at snapshot isolation will see the version of a row that existed at the point it started (that exists at the time the first statement is executed, rather than BEGIN TRAN). It is the transaction sequence number that is used to identify the correct version in the version store. As there is no correlation between the sequence numbers on your local and remote server, it's impossible for the remote server to retrieve the correct version.

There are undoubtedly ways a SQL Server to SQL Server connection could manage this first issue, should the SQL development team decide it was worth doing. But there is a second problem, how would you deal with removing versions from the version store?

The background process responsible for cleanup again uses the transaction sequence number to determine if a row version is still required. This requires nothing more than identifying the oldest running transaction sequence number and removing any versions that are older than this. With a remote connection... sounds ugly, which is why it isn't supported.

Could a different approach be taken, perhaps enforcing REPEATABLE READ or SERIALIZABLE on the remote connection? No, not without compromising consistency as the row at the remote server could not be locked at the time your local transaction started. You could not rely on the behaviour, compromising ACID compliance.

Row Versioning Resource Usage and SQL Server 2005 Row Versioning-Based Transaction Isolation contain most of the salient detail.

How best to workaround this will depend on what you're trying to achieve. If you can expand on your specific scenario we can explore the alternative approaches.


Is it possible to lower the CPU priority for a job? I want to run a background job in SQL Agent which will cleanup a lot of data and will run for multiple days.
Basically, 400 million records will be deleted from one single table.
The delete runs in a loop, so always batches of around 100.000 records are deleted.
During the delete process, nothing else will be modified in this table.
Is it possible to lower the CPU priority for such a job, so that it does not slow down the system?

As mentioned in the comments above, the resource governor is the built-in method for managing this, assuming you have an edition which enables this feature. Though unless your DELETE operations perform some complex logic to determine what to delete, changing CPU priority is unlikely to help at all because IO is will be the main bottleneck in such a case and it'll be barely bothering the CPU at all, so look at that MAX_IOPS_PER_VOLUME option when creating the resource pool.
If you don't have enterprise edition so do not have access to the resource governor, you could add sleep states into you loop using WAITFOR DELAY. You would want to work in smaller batches if using this more hacky manual approach, pick a batch size that will complete in a small amount of time. If you work for ~0.1 seconds and `WAITFOR DELAY '00:00:00.1' the task will be running at approximately half resource (very approximately, unless you get clever and have the task monitor itself and adjust batch size and/or sleep delay dynamically).
If what you are deleting represents almost all the data in the table, you might find it quicker to copy what you want to keep to another table, truncate the current table, and copy the kept data back in, though obviously this could get more complex if you have referential integrity to maintain between the remaining data and the rest of the database throughout the process, or the table is subject to any of the other restrictions for TRUNCATE TABLE. If you are deleting the whole table content, again assuming your the is suitable or can be temporarily made so, then just TRUNCATE TABLE.


Worth investing in MS SQL? Not trying to start a war here, but I would like some thoughts.

I work for a small nonprofit that is eligible for generous charity licensing from Microsoft. I am the database/web coder and my background is with open source stacks. I am most comfortable using PHP and MySQL.

That said, we are undertaking some large projects and am wondering whether it'd be worth investing in MS Server 2012 as a data staging and reporting system. It would not be used for any production purposes. I am most interested in the server studio management's advanced capabilities for importing and exporting data and for little productivity boosts like Intellisense.

What do you think? Is it worth the 80% discounted costs, or should we remain open source?

Not worth it for the reasons you gave, especially considering that you're not going to use it for production purposes. Reporting Services is a great platform for reporting, but I'm sure there are Open Source tools and frameworks to help you with that for PHP / MySQL. Ditto Integration Services, especially in 2012 (much better IDE for developing packages) it's a great tool for ETL, data staging and other import / export work, but probably overkill for you.

I'd definitely stick with the open source for now - it's always going to be cheaper for licencing ;-). Especially as the cost model for SQL Server 2012 shifted to a different per core model. We had to disable cores on recent deployments to bring down licencing costs, as we'd bought hardware before-hand (on assumptions of the same cost model as before). Funnily, enough that's how I started out on SQL Server - with a "we must port this to SQL Server, because Oracle licencing just got too expensive" project.

I look after about 16TB of high-transaction throughput SQL Server production databases for international clients. So definitely not anti-SQL Server and make plenty of use of all those tools you mention.


Can I use SQL Server Standard Edition as Primary and Enterprise SQL Server as Secondary node? I have Software Assurance for SQL Server Enterprise Edition and I need to setup High Availability on it.
The issue is my primary SQL Server has a Standard license.
Could I add SQL Server Enterprise edition as secondary replica?

No, this is not possible. Even if you'd manage to do such an installation, it would not be supported by Microsoft.
The documentation explicitly states that the editions must match:

The SQL Server edition you are installing must match across all the
nodes in a SQL Server failover cluster instance. When you add a new
node to an existing SQL Server failover cluster instance, make sure
that you specify that the edition matches the edition of the existing
failover cluster instance.

Although licensing questions are off-topic, a warning about your purposed installation is in order. In case of a licensing audit, the auditor would be quite likely to accuse you of running unlicensed setup. Licenses for Enterprise Edition are not cheap. You don't really have much bargaining power when license auditor has proof that you are not compliant.


Multi-statement Table Valued Function causing massive PAGELATCH_EX waits We are getting insane PAGELATCH_EX waits because of a Multi-Statement TVF. I've found that the temp table created by the function is on tempdb file #1 and doesn’t ever change (always '#A465FC0D'). It appears to be permanently cached there. The function is very heavily used and all the processes line up waiting to access the same temp table.

The function simply does a string_split, but using string_split directly, or converting the function to an inline TVF is 7x slower. Strings to split are relatively small (max 200 ids, but many are just a few ids)

CREATE FUNCTION [dbo].[IntListToTable] (@list nvarchar(max))
RETURNS @intlist TABLE (val int)
AS
BEGIN
  insert into @intlist
  SELECT
   CAST(value as int) val
  FROM
   string_split(@list, ',')
  return
END

I understand that the page latches are caused by the use of this function, but I'm looking to understand the caching/re-use mechanism here better.

What about this function makes it forever re-usable?
And why is this better than using an inline TVF or STRING_SPLIT() directly?

Other helpful information:

SQL Server 2017 CU2
We have 24 temp db data files
This happens on a couple of dedicated read replicas that have a more narrow workload than our OLTP server. Both have buffer cache hit ratio of 100% and plan cache hit ratio of 88%.
We restarted the instance and a new temp table was created and its been that same one ever since.

Since you are on SQL Server 2017 CU2 - you might be hitting this bug - Heavy tempdb contention occurs in SQL Server 2016 or 2017  .

Update to atleast CU5 since CU5 has fixed above bug.

I would recommend CU12 (latest).


Poor SQL performance with raid10 Maybe I'll try my luck here. Server Fault site was quite hostile for non-professional server user.

I recently bought HP ProLiant ML310e Gen8 v2 server with 8G's RAM and 4 HP 1TB 6G 7.2k rpm SATA (3.5in) Non-Hot Plug MDL HDD disks.

It was surprise to me that HP didn't provide debian drivers for raid controller and I had to go with software raid. I installed Debian with it's own software raid and raid level 10.

After installation I am experiencing huge performance loss and it looks like I managed to track it to MySQL operations.

Server is used mainly as Apache2 server with MySQL database.

Here is simple script that I ran to measure SQL operation times with server and my desktop PC.

$sql = "SELECT id FROM c_event";
$events = $db->getall($sql);
foreach($events as $item)
    $db->query("INSERT table1 set id = {$item['id']}");
foreach($events as $item)
    $db->query("DELETE FROM table1 WHERE id = {$item['id']}");

I compared results with my desktop PC with Intel Q6600, 4GB of ram and 120G SSD disk.

desktop completed in 45 secs
server in 2 hours, 46 mins and 18 secs.

Difference is totally insane.

Server raid performance seems ok to me, as disks ain't top quality ones.

hdparm -t /dev/md1
/dev/md1:
 Timing buffered disk reads: 1024 MB in  3.00 seconds = 341.04 MB/sec

I was wondering if there was something misconfigured in my server or is it totally just lack of disk performance.

For comparsion, I had possibility to run same test on ML350p Gen8 E5-2609 2.4Ghz server with 4 HP 146GB 6G SAS 15K 2.5in SC ENT HDD disks in raid 10 mode. Test took about 10 seconds.

Check your configuration.  Do SHOW VARIABLES; on each machine, then diff the output.  It smells like one of these is different:
innodb_buffer_pool_size
sync_binlog
innodb_flush_binlog_on_trx_commit
or maybe something else.

Now to be hostile...  RAID-10  (or -5) without a battery-backed-write-cache is hardly worth having.  Sure, you gain recovery from a disk crash (a rare event), but meanwhile, you don't get the write speedup of the write cache.


Hi folks, In one of my Agent Job is running by every minute One of my SQL agent Job is running every minute, the job has failed . I need the job history of past five days.

Is there any way we can get the history details?

I have searched in the log but not able to find the details.

Please help me.

Depending on how you have configured the history for the Sql Agent, you may have lost that information.  From this answer:

  The SQL Server Job system limits the total number of job history
  entries both per job and over the whole system. This information is
  stored in the MSDB database.

  Obviously you won't be able to go back and see information that has
  been since discarded, but you can change the SQL Server Agent
  properties and increase the number of entries that will be recorded
  from now on.

  In the SQL Server Agent Properties:

Select the History page
Modify the 'Maximum job history log size (rows)' and 'Maximum job
history rows per job' to suit, or change how historical job data is
deleted based on its age.

  It won't give you back your history, but it'll help with your future
  queries!

You might be able to tweak the following query to give you the information you're looking for (if it still exists).  I got the query from here.

;WITH jobListCTE as
(
SELECT j.name as job_name,
msdb.dbo.agent_datetime(run_date, run_time) AS run_datetime,
RIGHT('000000' + CONVERT(varchar(6), run_duration), 6) AS run_duration,
message
FROM msdb.dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id
WHERE h.step_name = '(Job outcome)'
)
SELECT job_name as [JobStep],
run_datetime as [StartDateTime],
SUBSTRING(run_duration, 1, 2) + ':' +
SUBSTRING(run_duration, 3, 2) + ':' +
SUBSTRING(run_duration, 5, 2) as [Duration],
message
FROM jobListCTE
ORDER BY run_datetime DESC, job_name;


Generate ER Diagram with view permissions Currently my environment that I'm working with is using Oracle DB 12 and I have been granted view permission only.

The issue is that the ER diagram that is given to me is not complete and sometimes retrieving data from different tables can be a challenge and time-consuming.

Therefore, is there any easier way that I could generate the full ER diagram myself or at least the relations with the current permissions I have?

I used SchemaSpy and it worked flawlessly.


What is surface area in SQL Server What is the surface area in SQL Server? or what is surface area in SQL Server consists of?

Does Oracle has similar concepts? If yes what is it called?

It normally stands for Surface Area of Attack and refers to the amount of components installed and or configuration options that are enabled. Normally the more components you install the wider the surface area for attack.

The concept is not specific to SQL Server and refers broadly to the chance that any software can be compromised by a malicious attack.

In the SQL Server world we have the SQL Server Configuration Tool that can be used to minimize the risks, in the Oracle world I believe it is called Configuration Hardening:

SQL Server: Surface Area Configuration

Oracle: Configuration Hardening

As a general best practice when reducing the surface area for an attack is to only install the components that you know will be used/needed and only enable them when they will be used.


How to export a table on postgresql command line Anyone can do tell me how to do that?

(I know maybe is a dumb question but I'm trying to learn how to use command line of postgresql)

~$ psql mybase
psql (9.1.3)
Type "help" for help.

and then

mybase=# \dt

            List of relations
 Schema |        Name         | Type  |  Owner
--------+---------------------+-------+----------
 public | xxxxxx              | table | postgres
 public | xxxxxxx             | table | postgres
 public | xxxxxxxxxxx         | table | postgres
 public | xxxxxxxx            | table | postgres
 public | xxxxxxxxx           | table | postgres
 public | xxxxxxxxx           | table | postgres
 public | xxxxxxxxxxxxxxxx    | table | postgres
 public | xxxxxxxxxxxx        | table | postgres
 public | xxxxxxxxxxxxxxxx    | table | postgres
 public | xxxxxxxxxx          | table | postgres
 public | xxxxxxxxx           | table | postgres
 public | xxxxxxxxxxxxxxxx    | table | postgres
 public | xxxxxxxx            | table | postgres

only want one table to then do a restore =D thanks

If you want to create text files containing the data, use the COPY command (or the psql command \copy which creates the output files on the client, rather than on the server as the COPY command does).

If you want to create SQL statements use pg_dump


How to do Postgresql 9.2 version check in Linux? $ systemctl restart postgresql-9.2.24

Failed to restart postgresql-9.2.24.service: Unit not found.

That means that there is no file named

postgresql-9.2.24.service

in your systemd directory.

The exact location varies between Linux distributions, but it is usually /usr/lib/systemd/system or /lib/systemd/system.


Would it be worthwhile to use the PostGIS extension for custom planets? I need to be able to store latitude and longitude in several different tables. My understanding is that PostGIS has a geography column type that does this.

However, I'm not storing coordinates on Earth, but those on fictional planets (for a game). I'm not a cartographer or any other sort of expert on this thing, knowing only what I'd learned in elementary/high school (90°S to 0° to 90°N for latitude, 180°E to 0° to 180°W for longitude).

Will the PostGIS extension be overkill for something I could manage as a composite type myself? Would I be able to use any of the additional functionality, for instance to be able to determine the distance between two points on the same planet, given that I assume PostGIS makes assumptions about those coordinates being located on our own planet?

Is PostGIS suitable for such uses as mine?

To be able to compute the distance on a sphere, you need the geography type, i.e., you need PostGIS.

For your fictional planets, you need to add custom projections so that PostGIS knows about them. The PostGIS documentation says:

  Although the PostGIS spatial_ref_sys table contains over 3000 of the more commonly used spatial reference system definitions that can be handled by the proj library, it does not contain all known to man and you can define your own custom projection if you are familiar with proj4 constructs.

And if you are not familiar with proj4 constructs, you can do it anyway by taking some existing definition and modifying it. Go to spatialreference.org, and look at the projections defined by the International Astronomical Union, e.g., IAU2000:19900 for longitude/latitude on Mercury. There is an option to show the INSERT command for PostGIS:

INSERT into spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext)
values ( 919900, 'iau2000', 19900, '+proj=longlat +a=2439700 +b=2439700 +no_defs ',
         'GEOGCS["Mercury 2000",DATUM["D_Mercury_2000",SPHEROID["Mercury_2000_IAU_IAG",2439700.0,0.0]],PRIMEM["Greenwich",0],UNIT["Decimal_Degree",0.0174532925199433]]');

919900 is the SRID. User-defined PostGIS SRID values must be between 910000 and 998999; just select any unique number in this range.
iau2000 is the name of the authority that defines this projection. You are not the IAU, so replace it with something like The John O.
19900 is a unique ID for the projection, defined by the authority. For simplicity, just use the same number as the SRID.
2439700 (3×) is the radius of Mercury, in meters. Replace this with your planet's radius.
Mercury 2000 is the name of the projection (here: the planet's name, and the year when it was defined). Also replace D_Mercury_2000 and Mercury_2000_IAU_IAG with some other names.
Greenwich is the name of the prime meridian. I do not think that the Greenwich Royal Observatory exists either on Mercury or on your planet, but these names are not actually used anyway. You need a value different from 0 only if there are different prime meridians.
0.0174532925199433 is the number of degrees per radian, and does not change.

For example, a projection for B-612 would look like this:

INSERT into spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext)
values ( 912345, 'Antoine de Saint-Exupéry', 912345,
         '+proj=longlat +a=1.75 +b=1.75 +no_defs',
         'GEOGCS["B-612",DATUM["B-612",SPHEROID["B-612",1.75,0.0]],PRIMEM["The Rose",0],UNIT["Decimal_Degree",0.0174532925199433]]');

This can then be used in computations:

CREATE TABLE points_on_b612(name text, point geography(point, 912345));
INSERT INTO points_on_b612 VALUES ('north pole', ST_SetSRID(ST_MakePoint(0, 90), 912345));
INSERT INTO points_on_b612 VALUES ('south pole', ST_SetSRID(ST_MakePoint(0, -90), 912345));

SELECT a.name, b.name, ST_Distance(a.point, b.point)
FROM points_on_b612 a JOIN points_on_b612 b ON a.name < b.name;

    name    |    name    | st_distance
------------+------------+-------------
 north pole | south pole |  5.49778714


Is it normal that occasionally Page Life Expectancy drops to 0 and queries start giving timeouts in SQL Server? Can somebody shed some light on if it's normal that Page Life Expectancy drops occasionally to 0 in SQL Server (2014) resulting in slow queries and timeouts?

I'm facing this on my developer machine when developing a web application and I don't know whether it is because of the development environment or something else. PLE may be over 10000 for one day or a couple of hours (during which queries work fast) until it drops to 0 and all my queries go from 15ms to 40 seconds or more.

I mean is there any way to control the performance of SQL Server in this matter or is this by design of the SQL Server that it may get slow, for example, due to memory pressure until it comes faster again etc.?

To me it looks like queries are read in a cache and at some point they're flushed out and queries start giving timeouts because everything is read from a disk. Is this something that can be avoided?

If your laptop has 4 gigs that means that SQL Server probably has access to 500 megs of RAM at best.

PLE tells you how long SQL Server is able to keep data in memory. If there's no load on the SQL Server then the PLE will just keep climbing. Once you put load on it again the PLE will drop as the SQL Server needs to read data from disk. The more data that it needs to load from disk the longer the queries will take.

If your disk is slow, which it probably is in your laptop the queries which hit the disk will just run slower as you want for the disk to spin.

If you want good performance on your laptop you'll want to ensure that SQL Server has enough RAM to ensure that the working set of your data sits in memory and not on disk. The first time it is accessed it'll need to be loaded from disk but after that you want it to stay in memory. You'll probably want an SSD as well so that the disk is fast enough.


Query hint? for deduplicated CTE in Merge Referring to this link: Optimizing MERGE Statement Performance

The following criteria are given for ideal performance:

Create an index on the join columns in the source table that is unique and covering.
Create a unique clustered index on the join columns in the target table.

I have many large tables (100+ million rows) that I merge 1-5 million rows into on a regular basis. There is a unique clustered index on the target table which matches the keys on the source table, satisfying the second criteria.

There is a bulk import process that populates the source table over time. I then place a non-unique clustered index on it in preparation for merging - this is because there is no way to ensure that duplicate values will not be inserted during the bulk process. This only sorta-half satisfies the first criteria.

I use a CTE with deduplication logic as the source for my merge -

...
USING
    ( SELECT
        cte.Key1,
        cte.Key2,
        cte.Key3,
        cte.RestOfTheColumns
      FROM
        ( SELECT
           Key1,
           Key2,
           Key3,
           RestOfTheColumns,
          ,ROW_NUMBER() OVER (PARTITION BY Key1, Key2, Key3 ORDER BY Key1, Key2, Key3) AS RowNumber
        ) cte
      WHERE cte.RowNumber = 1
    )
...

Ideally, I believe, this would already exist in a uniquely indexed table.

In summary:

I put a clustered index on the source then deduplicate it in-query.
In the query analyzer the Clustered Index Merge takes 91.9% of the plan cost.

Does it matter that the CTE/underlying table does not have an explicit unique constraint? There are no duplicates, but SQL doesn't know that, so I imagine it would check for every line in the upsert.

If you have a clustered (or covering) index starting with (Key1, Key2, Key3, ...) on the table that the CTE is querying, this should be a well-performing query.

Can you add a "Key4", preferably an identity column, in the source table to your non-unique clustered index, so you perhaps can make the index unique? That way, you could also set "Key4" as your ORDER BY in the ROW_NUMBER(). This might improve performance, but it's just a guess.

The fact that the Clustered Index Merge operator is over 90% of your plan doesn't by itself indicate a performance problem, it just says that there's not that much else to do in the query plan apart from the join. In fact, it may indicate that you have a well-optimized plan - when you have the proper indexes, a ROW_NUMBER() can be very efficient.


How to model very flexible referencing relationships between many tables? I have various different entity types in my database. For purposes of example, consider an IMDB database:

Movie
Person
Actor
Director
Translation
and 20 more tables

My application enables free-form many-many relationships between any of these entities.

What is a sane way to model this in a maintainable way?

Currently, my app uses a table with "a_id", "a_type", "b_id", "b_type". With the type identifying the table (e.g. "movie"). This means there is no referential integrity checking, which makes me uneasy.

What are the alternatives? The only one that comes to mind is to expand the linking table with a column for every other table. So I have a_movie_id, a_actor_id, a_director_id and b_movie_id, b_actor_id, b_director_id. However, while this preserves referential integrity, it feels strange (I have not seen such tables before) and I suspect it would be difficult for application code to process it (it would have to have a big if-statement to check what is actually referenced).

Thus I ask for your advice - is there a convenient industry standard way to model such flexible relationships?

My database engine is is SQL Server.

This is opinion-based. I wouldn’t try to suggest there’s a best-practice here.

If you have separate tables for actors, directors, movies, and have a generic EntityID column with separate EntityType column, you’ll always need to use dynamic SQL to query it, because until you know the type, you won’t know which table to join to.

Another option is to have a generic table of entities which contains the type. Your relationships table is then just two columns.

Then you have sparse columns in your entities table for all your properties, as directors have different attributes from movies.

And you index it well, so that you’re not trawling through all the actors when you want a list of movies. Filtered indexes can be particularly handy here, so that you have a dedicated index for movies which includes the movie-related columns.

Views can make querying easier too, but for traversing relationships you can use the generic table.


all_tables and dba_segments giving different space occupied by the same tables When I ran the below queries they gave different space occupied by the same table:

select bytes/1024/1024 as space, owner
from dba_segments
where segment_name='PARAMETER_STATUS';

     SPACE OWNER
---------- ------------------------------
      4988 FLSTD01
      3124 FLSTD04
      6416 FLSTD02
       808 FLSTD03

select blocks*16/1024,owner
from all_tables
where table_name='PARAMETER_STATUS';

BLOCKS*16/1024 OWNER
-------------- ------------------------------
    4979.39063 FLSTD01
      802.1875 FLSTD03
    6405.98438 FLSTD02
    1966.34375 FLSTD04

Why is it giving me different space occupied by the same table?

DB_BLOCK_SIZE=16KB

According to Oracle documentation, dba_segments.bytes is the number of bytes used by the database segment. all_tables.blocks is the number of blocks used inside a table (docs). Tables don't grow block by block, they grow extent by extent. There is also a third interesting field, dba_segments.blocks, which shows how many database blocks are there in a segment.

You can run the following query and see that the number of used blocks in table is generally not the same as the number of blocks in corresponding segments:

select s.segment_name, s.blocks segment_blocks, t.blocks table_used_blocks
from dba_segments s
    inner join dba_tables t on s.owner = t.owner and s.segment_name = t.table_name
where t.table_name = 'PARAMETER_STATUS'

Also note that due to partitioning, a single table may consist of more than one segment.


Trying to optimize a MySQL query that has sub selects I have the following sql

SELECT c.identity, c.firstName, c.lastName, c.email_address, c.startDate,
(SELECT COUNT(identity) FROM ClientLog cll WHERE cll.clientID = c.identity AND cll.type = 2) as loginCount,
(SELECT COUNT(identity) FROM ClientLog clv WHERE clv.clientID = c.identity AND clv.type = 10) as viewCount
FROM client c
LIMIT 50;

This query takes ~45 seconds to run (and I am going to need to run this without a limit) with client having ~80,000 records and ClientLog having ~430,000 records.

Is there anything I can do to the sub selects to increase the performance of this query besides breaking it into separate query calls?

Try runnning the LIMIT 50 against the client table earlier
SELECT c.identity, c.firstName, c.lastName, c.email_address, c.startDate,
(SELECT COUNT(identity) FROM ClientLog cll WHERE cll.clientID = c.identity AND cll.type = 2) as loginCount,
(SELECT COUNT(identity) FROM ClientLog clv WHERE clv.clientID = c.identity AND clv.type = 10) as viewCount
FROM (SELECT * FROM client LIMIT 50) c;

or gather the client keys and join a little differently
SELECT c.identity, c.firstName, c.lastName, c.email_address, c.startDate,
(SELECT COUNT(identity) FROM ClientLog cll WHERE cll.clientID = clientkeys.identity AND cll.type = 2) as loginCount,
(SELECT COUNT(identity) FROM ClientLog clv WHERE clv.clientID = clientkeys.identity AND clv.type = 10) as viewCount
FROM
(SELECT identity FROM client LIMIT 50) clientkeys INNER JOIN client c USING (identity);

You have to control the subselects by giving them as little data as possible. That's why I suggested running LIMIT 50 before joining to the correlated subqueries.
UPDATE 2012-06-15 12:18 EDT
Since this is a full join, remove the LIMIT 50 from your original query:
SELECT c.identity, c.firstName, c.lastName, c.email_address, c.startDate,
(SELECT COUNT(identity) FROM ClientLog cll WHERE cll.clientID = c.identity AND cll.type = 2) as loginCount,
(SELECT COUNT(identity) FROM ClientLog clv WHERE clv.clientID = c.identity AND clv.type = 10) as viewCount
FROM (SELECT * FROM client) c;

and make sure ClientLog has a decent index for the correlated subqueries
ALTER TABLE ClientLog ADD INDEX clientID_type_index (clientID,type);

Give it a Try !!!


PostgreSQL NOT IN array slow query I have a large table with millions of rows. Each row has an array field tags. I also have the proper GIN index on tags.

Counting the rows that have a tag is fast (~7s):

SELECT COUNT(*) FROM "subscriptions" WHERE (tags @> ARRAY['t1']::varchar[]);

However counting the rows that don't have a tag is extremely slow (~70s):

SELECT COUNT(*) FROM "subscriptions" WHERE NOT (tags @> ARRAY['t1']::varchar[]);

I have also tried other variants, but with the same results (~70s):

SELECT COUNT(*) FROM "subscriptions" WHERE NOT ('t1' = ANY (tags));

How can I make the "not in array" operation fast?

I have solved thanks to Jeff Janes on the pgsql-performance mailing list:

The GIN index was not used by PostgreSQL for the "NOT" operation. Creating a Btree index on the whole array solved the problem, allowing an index only scan. Now the query takes only a few milliseconds instead of minutes.


Spooling Oracle XML data results in 1 line and missing data? I am trying to spool Oracle XML data to a file but the resulting file ends up with 1 line which is stops after a certain amount of characters so doesn't contain all of the data?

set colsep ,
set feedback off
set headsep off
set linesize 32767
set pagesize 0
set trimspool on

spool $FILE

select
  xmlelement("apiRequest",
    xmlelement("orders",
      xmlagg(
        xmlelement("order",
          xmlelement("no", orders.order_no),
          xmlelement("date", orders.date),
          xmlelement("orderItems",
            xmlagg(
              xmlelement("orderItem",
                xmlelement("position", order_items.item_position)
              )
            )
          )
        )
      )
    )
  )
  as xml
from
  ...

You're probably hitting the default "long" data type display limit of 80 characters:

SET LONG {80 | n}
Sets maximum width (in bytes) for displaying CLOB, LONG, NCLOB and XMLType values; and for copying LONG values.


Oracle schema object name when fire trigger I need to get the object name and user name from oracle schema after fire a trigger. It executes on DDL statements. I need to get executed user name and object name which trigger executed on. Below is my PL/SQL code.

CREATE OR REPLACE TRIGGER orders_changes
after drop or alter or create on schema

DECLARE
 v_username varchar2(10);
 obj_name varchar2(300);

 BEGIN

  -- Find username and object name
  SELECT user, object_name INTO v_username, obj_name
  FROM dual;

  insert into table1 values (v_username, obj_name);

END;

Seems issue is with the way I access the object name. It gives an error. Please any one can help me to solve this.

Simply like this:

CREATE OR REPLACE TRIGGER orders_changes
after drop or alter or create on schema

BEGIN
  insert into table1 values (USER, ora_dict_obj_name);
END;

See Event Attribute Functions for predefined variables inside triggers.


what shortcomings does couchbase have, compared to mongodb? I see a massive gap of popularity between MongoDB and Couchbase.

What features\advantages does mongo have over couchbase?

I'm deploying DB instances in a cluster, between datacenters, where all servers accept incoming requests.
Is it not simpler to do this with couchbase, that has only one server role, and less configuration?

You are indeed correct!

It is very simple to deploy Couchbase instances and cluster them.  This is because Couchbase has a single node type which minimizes configuration, like you've mentioned.

What you might not know is that Couchbase has a built in caching layer, eliminating the need to install other caching services to sit next to your database.  This puts an emphasis on performance, which is critical in modern database.

UPDATE

I am a Developer Advocate at Couchbase, and listed a few advantages for you.


How to Configure PostgreSQL Hotstandby Automatic Failover I have configured two PostgreSQL servers to have hot standby by streaming.

How can I configure it so that when the master server goes down, the slave server will become the new master automatically?

Failover - automatic or otherwise - isn't provided directly by PostgreSQL.

You'll need external tools like repmgr.

The newly released repmgr 2.0 supports autofailover. However, I recommend thinking very hard about whether you should actually use it. Automated manually-triggered failover is usually a LOT safer.

If you do choose to use autofailover, you should make sure to force autofailovers as part of normal operation to make sure it's really working in your environment. Otherwise it's just a false sense of security, and when something actually breaks it'll probably bring the whole system down in a mess.

You should test any failover setup regularly, of course, but at least with manually triggered failover you're aware it's going to happen and ready to step in if there are problems.

It's not just a matter of promoting the server and rebuilding the old master as a replica, either. You've also got to deal with the need to change client connections over to the new server (using something like PgBouncer, PgPool-II, or HAProxy). You also need to reliably terminate the old server so that you don't get divergence, where both servers think they're masters and some clients are writing to each of them.

There's a fair bit of info on all this in the PostgreSQL documentation and on the wiki, so I suggest you start there.


Deferrable unique index in postgres Looking into postgres documentation for alter table, it seems regular constrains can be marked as DEFERRABLE (more concretely, INITIALLY DEFERRED, which is what I'm interested in).

Indexes can also be associated with a constraint, as long as:

  The index cannot have expression columns nor be a partial index

Which leads me to believe there is currently no way to have a unique index with conditions, like:

CREATE UNIQUE INDEX unique_booking
  ON public.booking
  USING btree
  (check_in, check_out)
  WHERE booking_status = 1;

To be INITIALLY DEFERRED, meaning, that the uniqueness 'constraint' will only be verified on the end of the transaction (if SET CONSTRAINTS ALL DEFERRED; is used).

Is my assumption correct, and if so, is there any way to achieve the intended behaviour?

Thanks

A index cannot be deferred - doesn't matter if it is UNIQUE or not, partial or not, only a UNIQUE constraint. Other types of constraints (FOREIGN KEY, PRIMARY KEY, EXCLUDE) are also deferrable - but not CHECK constraints.

So the unique partial index (and the implicit constraint it implements) will be checked at every statement (and in fact after every row insert/update in current implementation), not at the end of transaction.

What you could do, if you want to implement this constraint as deferrable, is to add one more table in the design. Something like this:

CREATE TABLE public.booking_status
  ( booking_id int NOT NULL,               -- same types
    check_in timestamp NOT NULL,           -- as in
    check_out timestamp NOT NULL,          -- booking
    CONSTRAINT unique_booking
        UNIQUE (check_in, check_out)
        DEFERRABLE INITIALLY DEFERRED,
    CONSTRAINT unique_booking_fk
        FOREIGN KEY (booking_id, check_in, check_out)
        REFERENCES public.booking (booking_id, check_in, check_out)
        DEFERRABLE INITIALLY DEFERRED
  ) ;

With this design and assuming that booking_status has only 2 possible options (0 and 1), you could remove it entirely from booking (if there is a row at booking_status, it's 1, if not is 0).

Another way would be to (ab)use an EXCLUDE constraint:

ALTER TABLE booking
    ADD CONSTRAINT unique_booking
        EXCLUDE
          ( check_in  WITH =,
            check_out WITH =,
            (CASE WHEN booking_status = 1 THEN TRUE END) WITH =
          )
        DEFERRABLE INITIALLY DEFERRED ;

Tested at dbfiddle.

What the above does:

The CASE expression becomes NULL when booking_status is null or different than 1. We could write (CASE WHEN booking_status = 1 THEN TRUE END) as (booking_status = 1 OR NULL) if that makes it any more clear.
Unique and exclude constraints accept rows where one or more of the expressions is NULL. So it acts as a filtered index with WHERE booking_status = 1.
All the WITH operators are = so it acts as a UNIQUE constraint.
These two combined make the constraint act as a filtered unique index.
But it's a constraint and EXCLUDE constraints can be deferred.


It seems you didn't reference the /*+ FULL(t) */ hint alias while calling target table. You created the table with the same name as the alias. I'm not sure if that might be the problem... But the docs example follow this structure:

SELECT /*+ FULL(e) */ employee_id, last_name
  FROM employees e
  WHERE last_name LIKE :b1;

So, what happens if you instead try:

SELECT /*+ full(t) */  Count(1) n
FROM t t WHERE start_time BETWEEN '2015-07-01 00:00:00' AND '2015-07-31 23:59:59'

?


Unique constraint on multiple nullable columns Sql Server We have a PriceCombo table with the following columns: id (not null), a_id(null),b_id(null),c_id(null), discount(not null).
We are using this table to figure out which price to assign various combos of merchandize.

a_id = id of region
b_id = id of merchandize category
c_id = id of customer category

so basically, we always know a, b and c, but we have a flexible discounting scheme such that every sale is discounted by the most specific rule we can find.

Our problem is our inability to guard against duplicates in the database, we would ideally want to guard against two rows having the same values in a_id, b_id AND c_id, for instance

a_id=1, b_id=1, c_id=1    and
a_id=1, b_id=1, c_id=1

are not to be allowed and

a_id=1, b_id=1, c_id=null   and
a_id=1, b_id=1, c_id=null

are not to be allowed  but

a_id=1, b_id=1, c_id=null  and
a_id=1, b_id=null, c_id=null

ARE to be allowed as these are not identical in respects to these 3 columns.

I have not found an answer anywhere to how to create a unique constraint that BOTH spans multiple columns AND allows nulls BUT disallows "identical" (at least in respect to the columns specified in the constraint) rows

There's nothing special that would prevent you from using a standard unique constraint here, unless there's something you aren't telling us.

When a unique constraint is created on a set of nullable columns, NULL is treated (in terms of uniqueness) like any other of the distinct values in the set. (I believe this behaviour varies across RDBMSes.)

So I think the only case you might have to protect against here is the where all three columns contain NULL at the same time. If this is invalid for your business logic, also create a CHECK constraint to disallow this combination.


Minimal Permissions to Create an Application Database and Database Owner I wish to write (or borrow if you have one) a script template for creating an application database and associated logins. The script is to be run in CI pipelines and on developer desktops.
Possibly influenced by postgres but also by MS maybe-saying that assigning ownership to a disabled login is best practice I wish to assign ownership of the database to some insignificant login created for the purpose:
Create Login appname_owner With Password = 'randomlygeneratedpassword';
Alter Login appname_owner Disable
Create Database appname  ;
Alter Authorization On database::appname To appname_owner ;

Question: What is the minimal set of permissions that will allow a CI login to do this?

I believe that Create Any Database and Alter Any Login are minimal for create login & database. [ I can't say I'm happy with CI scripts needing Alter Any Login. It would be nicer to restrict alterable Logins to some group or role defined for the purpose.]
But I'm stumped on Alter Authorization. It seems I need  Grant Impersonate On Login::appname_owner To CILogin, which the CILogin can't grant to itself, not even for a login it has just created.

How can I resolve this,

without the CI scripts using an sa login and
preferably, without granting the CILogin Impersonation rights on all logins to the server

?

That can be achieved by creating a user based on a certificate and Signing a stored procedure by using a certificate:
USE master;
GO

CREATE CERTIFICATE MyInpersonationCert
   ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'
   WITH SUBJECT = 'Certificate to do a test',
   EXPIRY_DATE = '20261031';
GO

CREATE LOGIN login_from_cert FROM CERTIFICATE MyInpersonationCert;
GO

--It might be a little bit too much permission, but I'm tired and it's late at night.
--You'll figure out the proper privileges for this login ;)
ALTER SERVER ROLE sysadmin ADD MEMBER login_from_cert;
GO

With the login from the certificate having the privileges to create and manipulate the objects you need you can create the procedure.
CREATE PROCEDURE impersonate_procedure
    @database_name nvarchar(128),
    @login_name nvarchar(128),
    @login_password nvarchar(128)
AS
BEGIN

    --add some verification on the received parameters to avoid SQL Injection (still tired)

    DECLARE @sql nvarchar(max);

    SET @sql = '
        CREATE LOGIN ' + @login_name + ' WITH PASSWORD = ''' + @login_password + ''';
        ALTER LOGIN ' + @login_name + ' DISABLE;
        CREATE DATABASE ' + @database_name + ';
        ALTER AUTHORIZATION ON database::' + @database_name + ' TO ' + @login_name + ';';

    EXECUTE sp_executesql @sql;

END;
GO

ADD SIGNATURE TO dbo.impersonate_procedure
    BY CERTIFICATE MyInpersonationCert
    WITH PASSWORD = 'pGFD4bb925DGvbd2439587y';
GO

Now you can grant execute to the login used for the CI scripts on dbo.impersonate_procedure. Your login will be able to execute the procedure, but not to explicitly issue a CREATE DATABASE or ALTER AUTHORIZATION.


Multiple Distinct Columns I have a table named 'DATAENTRY' with the following columns

Title
FName
MName
LName
Add1
Add2
Add3
City

Now I want to select records which have different (FName, MName ,LName) so I tried

SELECT DISTINCT FName, MName, LName FROM dataentry

The problem is that I want full records with all columns but they should be unique across  (FName, MName, LName)

I can't use

SELECT DISTINCT Title, FName, MName, LName, Add1, Add2, Add3, city FROM dataentry

because two records with same values in (FName, MName, LName) but with different values in Title or Add1 will be treated as distinct records.

Assuming your RDBMS supports windowing functions, something like this will give you what you are asking for:

select *
from( select dataentry.*,
             row_number() over (partition by fname, mname, lname order by title) n
      from dataentry )
where n=1;

But note that I have arbitrarily chosen to order by title when discarding 'duplicates' - and even more arbitrarily chosen to overlook that if two matching rows also have matching titles the database will order them in an undefined and possibly unpredictable way. You will need to adjust that to meet your requirements.


How to use CTE to Compare Values I need to return a 0 or 1 dependent on previous records.  Example table:

DECLARE @x TABLE(ProductID INT, Failed bit, SampleDate date, LevelCode int);
INSERT @x VALUES
(101, 0, '20151201', 1),
(101, 1, '20151205', 2),
(101, 0, '20151206', 3),
(101, 1, '20151208', 2),
(102, 1, '20151202', 1),
(102, 0, '20151204', 2),
(102, 0, '20151205', 3),
(103, 0, '20160101', 1),
(103, 1, '20160102', 2),
(103, 0, '20160103', 2),
(104, 0, '20160101', 1),
(104, 0, '20160102', 2),
(104, 0, '20160103', 3);

The only thing we care about on the last record is the LevelCode (i.e. last record of each ProductID). Whether the last record passed/failed doesnt matter. We then look at all other records for that ProductID (so all records before the last record) and if there was a failure with the same LevelCode as the last record we set IsLastRunSameLevelAsPreviousRun to 1 else 0:

 ProductID     IsLastRunSameLevelAsPreviousRun
 101           1
 102           0
 103           1
 104           0

If there are no failures for a ProductID the IsLastRunSameLevelAsPreviousRun should return 0.

Any help or tips are very much appreciate.

Using LAG you can achieve your goal without any joins:

WITH cte AS
  (
    SELECT
      ProductID,
      SampleDate,
      MaxSampleDate = MAX(SampleDate) OVER (PARTITION BY ProductID),
      PrevFailed    = LAG(Failed, 1, 0) OVER (PARTITION BY ProductID, LevelCode
                                              ORDER BY SampleDate)
    FROM
      @x
  )
SELECT
  ProductID,
  IsLastRunSameLevelAsPreviousRun = PrevFailed
FROM
  cte
WHERE
  SampleDate = MaxSampleDate
;

The cte obtains the previous Failed state for the same LevelCode for each row within each ProductID group, returning 0 when there is no matching row. It also calculates the last date in each group to use it later to determine the last row in the group.

This is what it returns:

ProductID  SampleDate  MaxSampleDate  PrevFailed
---------  ----------  -------------  ----------
101        2015-12-01  2015-12-08     0
101        2015-12-05  2015-12-08     0
101        2015-12-08  2015-12-08     1
101        2015-12-06  2015-12-08     0
102        2015-12-02  2015-12-05     0
102        2015-12-04  2015-12-05     0
102        2015-12-05  2015-12-05     0
103        2016-01-01  2016-01-03     0
103        2016-01-02  2016-01-03     0
103        2016-01-03  2016-01-03     1
104        2016-01-01  2016-01-03     0
104        2016-01-02  2016-01-03     0
104        2016-01-03  2016-01-03     0

The main SELECT essentially just takes the last row of each group using the SampleDate = MaxSampleDate filter, pulling only ProductID and PrevFailed and also renaming the latter to IsLastRunSameLevelAsPreviousRun, so that the final output becomes what you want:

ProductID  IsLastRunSameLevelAsPreviousRun
---------  -------------------------------
101        1
102        0
103        1
104        0


Except duplicates from duplicates based on columns Here is what I am trying to do,

Get duplicates based on 2 columns (let say returns 500 rows)
Get duplicates based on above 2 columns + another column (let say returns 100 rows)

Now I want to get remaining 400 rows. In simple words, I want all duplicates where there are not duplicates because of columnC...

-- get duplicates based on ColumnA, ColumnB
SELECT '-'
    ,ColumnA
    ,ColumnB
    ,ColumnC
    ,COUNT(*)
FROM MainTable
     ...SOME joins(INNER AND left)
WHERE ColumnA IS NOT NULL
GROUP BY ColumnA
    ,ColumnB
    ,ColumnC
HAVING COUNT(*) > 1

EXCEPT

-- get duplicates based on ColumnA, ColumnB, ColumnC
SELECT '-'
    ,ColumnA
    ,ColumnB
    ,ColumnC
    ,COUNT(*)
FROM MainTable
     ...SOME joins(INNER AND left)
WHERE ColumnA IS NOT NULL
GROUP BY ColumnA
    ,ColumnB
    ,ColumnC
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC

I am just not able to complete this query :(

Using window functions will likely be simpler here:

DB<>Fiddle

WITH cte AS
(
    SELECT
        ColumnA,
        ColumnB,
        ColumnC,
        COUNT(*) OVER (PARTITION BY ColumnA, ColumnB)
          AS count_ab,
        COUNT(*) OVER (PARTITION BY ColumnA, ColumnB, ColumnC)
          AS count_abc
    FROM MainTable
         ...SOME joins(INNER AND left)
    WHERE ColumnA IS NOT NULL
)
SELECT
    ColumnA,
    ColumnB,
    ColumnC,
    count_ab
FROM
    cte
WHERE
    count_ab > 1
  AND
    count_abc = 1 ;


What are some consequences of putting quotes in a WHERE clause for an integer column? I have a table with a column some_column of data type integer.
I've noticed that my PostgreSQL 10.6 instance is smart enough to interpret both of the queries below as same:

select * from my_table where some_column = 5; -- no quotes

select * from my_table where some_column = '5'; -- quotes added

What are some repercussions of adding quotes in a where clause for an integer column?
Would it have performance effects on a large table of, say, 20 million rows?
(There is an index for some_column.)

Both do the same and you won't be able to measure any difference in performance.

Details

This is a string literal or string constant: '5'

The manual:

  A string constant in SQL is an arbitrary sequence of characters
  bounded by single quotes ('), for example 'This is a string'. To
  include a single-quote character within a string constant, write two
  adjacent single quotes, e.g., 'Dianne''s horse'. Note that this is not
  the same as a double-quote character (").

If there is no context from which a type can be derived, a string literal is initially assumed to be type text. (Not the case in your example.)

The manual once more:

  The explicit type cast can be omitted if there is no ambiguity as to
  the type the constant must be (for example, when it is assigned
  directly to a table column), in which case it is automatically coerced.

This is a numeric literal or numeric constant: 5

The manual:

  A numeric constant that contains neither a decimal point nor an
  exponent is initially presumed to be type integer if its value fits in
  type integer (32 bits); otherwise it is presumed to be type bigint if
  its value fits in type bigint (64 bits); otherwise it is taken to be
  type numeric. Constants that contain decimal points and/or exponents
  are always initially presumed to be type numeric.

So a numeric literal starts out with a specific type. It may then be cast to a different type as the context requires - if such a cast is defined. That's a subtle, but important difference - which makes no effective difference in your case, since 5 is initially integer, which is exactly the type it needs to be.

But it matters in other cases. Try this:

CREATE TEMP TABLE tbl1 (t int);
SELECT * FROM tbl1 where t = '0';         -- works!
SELECT * FROM tbl1 where t = int '0';     -- works!
SELECT * FROM tbl1 where t = int2 '0';    -- works!
SELECT * FROM tbl1 where t = 0;           -- works!

CREATE TEMP TABLE tbl2 (t text);
SELECT * FROM tbl2 where t = '0';         -- works
SELECT * FROM tbl2 where t = text '0';    -- works
SELECT * FROM tbl2 where t = varchar '0'; -- works
SELECT * FROM tbl2 where t = 0;           -- fails !!!

ERROR:  operator does not exist: text = integer

Because the numeric literal starts out as integer and there is no assignment cast defined for integer --> text. (Any type can be cast to text with an explicit cast (0::text) but that cast is not assumed here.)


Is this package missing and if so, how do I fix? I am following the given link.
https://oracle-base.com/articles/10g/oracle-data-pump-10g

I'm an Oracle newbie. Oracle 12c is the target version. I have a Development database running in a docker image and I'm logged in as sys user to make sure that I can see everything. I'm using DataGrip as a client. I'm looking for the DBMS_DATAPUMP package but I don't see it in the GUI and I've also tried SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('FUNCTION','PROCEDURE','package') order by OBJECT_NAME;, but I still don't see it. If it isn't installed, where do I download it from? I believe I've already figured out how to install it.

Our objective is as follows. We want to try creating a master schema in CI that is kept up to date on a weekly or monthly basis and then we want to copy that schema as our starting point for integration tests. Right now we are suffering through a painfully long flyway based migration process.

Data Pump is a fully integrated feature of Oracle Database 11g. Data
  Pump is installed automatically during database creation and database
  upgrade...more

It should be installed during the database creation. I found logical error in your query to check whether or not the data pump package is installed. The object type PACKAGE should be in upper case to be selected.

Demo:

You query has returned the following only.

SQL> SELECT object_name FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('FUNCTION','PROCEDURE','package') and object_name like '%DATA%' order by OBJECT_NAME;

OBJECT_NAME
------------------------------
DATABASE_NAME
DBMS_FEATURE_DATABASE_VAULT
DBMS_FEATURE_DATA_GUARD
DBMS_FEATURE_DATA_REDACTION
DBMS_FEATURE_EXADATA
EXF$VALIDATEDATAITEM
ODCIANYDATADUMP
XDB_DATASTORE_PROC

8 rows selected.

And the query with object type PACKAGE in upper case has returned the following. You can see the DATAPUMP package is there.

SQL> SELECT object_name FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('FUNCTION','PROCEDURE','PACKAGE') and object_name like '%DATA%' order by OBJECT_NAME;

OBJECT_NAME
------------------------------
...
CWM1_OLAP_VALIDATE_METADATA
CWM2_OLAP_METADATA_REFRESH
CWM2_OLAP_VALIDATE_METADATA
DATABASE_NAME
DBMS_DATAPUMP
DBMS_DATAPUMP_UTL
DBMS_DATA_MINING
DBMS_DATA_MINING_INTERNAL
...


Default Users for Postgres I’m trying to secure my Databases and wanted to know what are the default or built in users for Postgres databases?
Am I able to change the usernames or remove these users?

The default User is created during installation of the server instance. But you can query for a list of all current Users using this query:
SELECT usename AS role_name,
  CASE
     WHEN usesuper AND usecreatedb THEN
       CAST('superuser, create database' AS pg_catalog.text)
     WHEN usesuper THEN
        CAST('superuser' AS pg_catalog.text)
     WHEN usecreatedb THEN
        CAST('create database' AS pg_catalog.text)
     ELSE
        CAST('' AS pg_catalog.text)
  END role_attributes
FROM pg_catalog.pg_user
ORDER BY role_name desc;

For more information please see this article.
You can change the Users and their passwords using ALTER USER, as discussed here. You can also disable the login for the default User as stated here.


Installation of Microsoft SQL Server Data Tools Hangs I'm attempting to install SQL Server Data Tools, but the installation is hanging.  It had an error the first attempt; and I've restarted the machine (as that seems to help in those scenarios).  Now it just sits there on the tools for Visual Studio not installing (I've left it running over night).  Any suggestions on what to do here?

I experienced exactly the same error, right down to the pixel.  I figured out the solution by continuing the broken installation using the standard VS 2017 installer (it will recognize that the SSDT installer error'd out).  Resuming this installer reported more information via error logs.  The errors were folder/file access permissions being restricted by McAfee Anti-virus

tldr; disable your anti-virus.


Varbinary startswith query using index I've got this tree-like key (think object OIDs--it's very similar) to store and index in a table, for which queries want to select subtrees thereof. The most straightforward way to do this on a btree is to use the tree path as the single key and descend the key with a starts-with operation to find the row blocks.
So the obvious representation is VARBINARY(n)*. I cannot think of a way to express starts with in a way that SQL Server can figure out to use the index thereof.
The next-most obvious is to pack the data into some VARCHAR with a binary collation and use a like operation; however I've discovered that like @n + '%' will never use an index, but only like 'literal%. Adding the WITH INDEX hint still scans the index in its entirety because SQL server just doesn't understand. I really don't want to think about how to escape arguments in SQL so I can build an EXEC. That's just madness and a security disaster waiting to happen.
Expressing this in ordinary recursive tables and querying with recursive SQL is out of the question. The recursive query will power-sink the database server.
I actually have the data in a long string of long variables right now and can select my serialization form. As I said, the natural form is VARBINARY and it really would look like 0x000000100004A010000000D0000A000. The most common query is of the form "give me everything starting with 0x000000100004A01" or in natural model, the first n values from the string of long values. I could write them in the form a.b.c.d.e.f... but the individual numbers are long and come from clicking on things on the GUI.
Roughly speaking I'm looking at a plausible form of
CREATE TABLE Record (
   RecordId BIGINT NOT NULL IDENTITY(1,1),
   RecordedDate DATETIME NOT NULL,
   RecordClass CHAR(1) NOT NULL,
   UserId INT NOT NULL,
   ObjectId VARBINARY(384) NOT NULL,
   RecordValue NVARCHAR(100) NULL,
   OwnerId BIGINT NULL, -- Joins to another table
   SubOwnerId BIGINT NULL, -- and yet another table
   PRIMARY KEY (RecordId)
)
CREATE INDEX name ON Record(RecordedDate);
CREATE INDEX name ON Record(OwnerId);
CREATE INDEX name ON Record(SubOwnerId);
CREATE INDEX name ON Record(ObjectId);

What is the best way to do a starts-with index?
*I've computed the largest possible n and it's less than 400

So as a matter of fact, starts with is not so hard to write as it would first seem. Given the initial key of 0x000000100004, obviously equals won't work and there is no starts with operation, but we can write ObjectId >= 0x000000100004 AND ObjectId < 0x000000100005.
By forcing the string of long values to VARBINARY conversion to be big-endian the natural form actually works correctly. We want >= (value0 & value1 & ... & lastvalue) AND < (value0 & value1 & ... & (lastvalue + 1)). As far as I know, the conversion to VARBINARY has to be written in application code, which is fine for me.


Adding indices to views I have a table that contains XML data in a varchar type field.  This field is used to store data from a variety of different XML schemas some of which are related via a record stored in another table.

I recently created some views which parse the XML and in some cases make a pivot of some of the values.  The views provide excellent data for my reports but come with a serious performance hit.  I'd like to find out if I can improve performance with indices.

Here is an example of a view:

create view StudentHours as
  select
      x.TableRecordId as Id
    , x.RecordXml.value('(/TableRecord/LOGDate)[1]', 'DateTime') as LogDate
    , x.RecordXml.value('(/TableRecord/LOGHours)[1]', 'float') as Hours
    , x.RecordXml.value('(/TableRecord/LOGApproved)[1]', 'varchar(10)') as Approved
    , y.PKTableRecordId as CourseId
  from
    (select TableRecordId, Cast([Schema] as Xml) as RecordXml
     from TableRecords where TableSchemaId = 1857) as x
  join TableRecordRelations as y on x.TableRecordId = y.FKTableRecordId

I then will use this view in other views which do aggregates and such.

The table called TableRecords has an index on its unique id TableRecordId and the TableRecordRelations also has indices on its important fields as well.

Will adding an index or two help this view's performance? Is more data needed to determine this?

I believe that the cast to XML is killing performance for you and what is happening is described by Paul White Compute Scalars, Expressions and Execution Plan Performance

The cast to XML is deferred to where you actually use the XML column so in your query the cast happens three times for each row returned.


sp_blitzindex output I have latest version of sp_blitzindex.  Would like to store results in table. Runs no output, anyone doing this already?

Running with parameters below:

EXEC dbo.sp_BlitzIndex @databasename = 'ngprod',
    @OutputDatabaseName = 'dbaadmin',
    @OutputSchemaName = 'dbo',
    @OutputTableName ='BlitzIndexStats'

Table structure is below:

USE [DBAAdmin]
GO

/****** Object:  Table [dbo].[BlitzIndexStats]    Script Date: 6/7/2017 9:08:21 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[BlitzIndexStats](
    [Priority] [tinyint] NULL,
    [Finding] [varchar](255) NULL,
    [DatabaseName] [nvarchar](128) NULL,
    [Details] [varchar](2000) NULL,
    [Definition] [varchar](4000) NULL,
    [SecretColumns] [varchar](512) NULL,
    [Usage] [varchar](255) NULL,
    [Size] [varchar](255) NULL,
    [CaptureDate] [datetime] NOT NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[BlitzIndexStats] ADD  DEFAULT (sysdatetime()) FOR [CaptureDate]
GO

I downloaded the latest sp_BlitzIndex and in the comments in the SP i found this:

  The @OutputDatabaseName parameters are not functional yet. To check the    status of this enhancement request, visit:
  https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/221

So you are not able to use that parameter.


What Columns to create Indexes on? I have a table , which contains upto 20 columns. The data is selected, inserted, and deleted by 3 columns: Username, StartDate, and EndDate.

These 3 columns are NOT unique.

The table is a standalone table - no joins to other tables.

Do I create a clustered index on these 3 columns? Or do I create a UniqueID that autoincrements as a PK, and then create a non-clustered index on those 3 columns? Or is a Primary Key required at all?

SELECT StartDate, EndDate, Username, Value1, Value2, Value3, Value4, Value5, Value6
FROM  myTbl
WHERE Username = @Username
AND StartDate = @StartDate
AND EndDate = @EndDate;

DELETE FROM  myTbl
WHERE Username = @Username
AND StartDate = @StartDate
AND EndDate = @EndDate

From the limited information given, a suitable non-unique clustered index is:

CREATE CLUSTERED INDEX CX_dbo_myTbl_StartDate_EndDate_Username
ON dbo.myTbl (StartDate, EndDate, UserName);

That said, there are all sorts of other considerations that might mean a different choice would be optimal for the workload as a whole. If you improve your question with additional detail, it will be possible to give a better answer.

Also, in general, a PRIMARY KEY (clustered or not) is highly desirable. It is a fundamental principle of relational design that each row (tuple) of a table should be capable of being uniquely identified.


Use MySQL to match input against saved queries What I'm trying to do:
Create a database that, instead of generating results from a criteria, matches an input against a database of criteria.

Sometimes we can save our search results and have a site mail us whenever a new item comes up that meets our criteria.

Example:

Bob is interested in blue widgets, red widgets, or widgets with size > 5
Alice is interested in blue widgets or green widgets
Tom is interested in green widgets or widgets size between 7 and 10

Results

If we receive a green widget with size 10, everyone will see
it.
If we receive a red widget with size 3, only Bob will see it.
If we receive a purple widget with size 9, Bob and Tom will see it, but
not Alice.

What I've Done

Created a list of queries and run them each against the records.
Problem with this is that I could end up with tens of thousands of
saved queries (search profiles).
Researched RETE algorithm based solutions such as expert systems.
However, I'd rather have this be saved in a database.

Questions:
Is this possible to solve with mySQL other than saving SQL statements and running them over and over? Is there some feature of mySQL that I'm missing - that could show what saved queries match a particular input?

Invert.  Hmmm...  Indexing is about inverting.  Let's see if we can apply that concept here.

I see two types of tests in your example -- "set" operations (color) and "range" operations (size).

Suppose we could build the following into some kind of table?

blue  --> Bob, Alice
red   --> Bob
green -->      Alice, Tom

size <= 5 -->      Alice
     5-7  --> Bob, Alice
     7-10 --> Bob, Alice, Tom
     >10  --> Bob, Alice

Range.  I have a hammer; I want your problem to be a nail.  My hammer is a way to index ip-address ranges for very efficient lookup.  It is so efficient that the "size" test becomes a single hit to the table that will find the set of persons that match.  This, in my mind, solves the "range" part of your problem.

Set.  This part is simply to map one color to a collection of persons.

Collection of persions

Plan A:  the datatype SET is limited to 64 persons.  Not acceptable for "tens of thousands".

Plan B:  a BLOB of bits, each bit representing one person.  This has the distinct advantage of AND and OR now become bitwise operations.  Drawback:  bitwise operations only work with SET and BIGINT.  That is, until MySQL 8.0, when it works on BLOB.  Also, I have implemented bitwise operations on `BLOB.  Another issue:  the bit strings may be very sparse?  So, Plan B is a "maybe".

Plan C:  an ordinary table:  (blue,Bob),(blue,Alice),(red,Bob), ...  Plan C may be the best.  Necessarily, the PRIMARY KEY should be (color, person) to take advantage of 'clustering'.

References

Efficient IP address lookup - for your ranges

Unique Users - someday I may publish bitwise manipulations on BLOB here.

Bottom line.  I have given you some food for thought.  And there is a size limit on Answers in this venue.  And this is a bigger task than I am willing to do for free.


lot of relay log formation in mysql5.6 Reason for lot of small relay log file formation.
I Have setup a slave and it relay log rotates to soon and creates thousands of relay log file.whats the reason for more relay log formation.

Two possible reasons seem to stick out,  ensure your server_id is different on both the master and the slave.  This is to be defined at startup in the cnf file.

Another option is you specified a very tiny max_binlog_size or max_relay_log_size, ensure the values are what you want and not off by an order of magnitude (eg. 512k instead of 512M)


Invalid use of side-effecting operator 'INSERT EXEC' within a function I'm trying to create a scalar function that will return a delimited list from some sql that would be passed in.  Unfortuantly where I work the previous people responsible for the database thought it would be a good idea to put comma delimited id lists inside of some columns.  This function is the first step for me to rectify the problem without breaking the developers UI.  Can anyone tell me how to fix this error?  My code is below.

 CREATE FUNCTION fnSQLToDelimitedList ( @SQL NVARCHAR(MAX), @Delimiter NVARCHAR(3))
RETURNS NVARCHAR(MAX)
AS
BEGIN

DECLARE @OUTPUT NVARCHAR(MAX) = ''
DECLARE @VALUE NVARCHAR(MAX)
DECLARE @TEMPVALUES TABLE (VALUE NVARCHAR(MAX) NOT NULL)

INSERT INTO @TEMPVALUES (VALUE)
EXECUTE sp_executeSQL @SQL

WHILE EXISTS (SELECT 1 FROM @TEMPVALUES)
BEGIN

SET @VALUE = (SELECT TOP 1 value
                FROM @TEMPVALUES)
SET @OUTPUT = @OUTPUT + ' ' +  @VALUE + @Delimiter

DELETE FROM @TEMPVALUES WHERE VALUE = @VALUE
END

SET @OUTPUT = SUBSTRING(@OUTPUT,0, LEN(@OUTPUT) )

    -- Return the result of the function
    RETURN @OUTPUT

END
GO

use this code to join rowset to delimited single value

sql,

use this code instead:

    DECLARE @IDs NVARCHAR(max)=''

    SELECT @IDs += ID +','
    FROM dbo.SomeTable

select @IDs

you can add where clause and design your query to return  rowset with CTE and UNION ALL


Should I use XML for data storage on client side? I have to create a supplementary program that runs offline on a client machine (normally windows 7 or higher laptop). Of course, I don't want to create a pre-requisite for this program. Since this runs offline, so I can not use a database server either.

This application will store about 500-1000 entry per record and a total of max 100 records.

So, I am trying to decide on the DB tech that works offline, and does not require extra installation on the client's side. Currently I am thinking of using XML to save the data. But I am sure, there are better solutions that I am missing.

This also depends on what kind of records you want to be stored, whether it is semi-structured data that is best formed using XML, or plain key-value based records. SQL Server Express can store both kinds, but involves an additional install. However, to avoid extra installs, you can check out SQL Server Compact and SQLite, both of which are installed as a part of the program you will be distributing and will run embedded with it. Also, several NoSQL databases may cure your needs - just give Google a hint and check the answers. Some of them are also compatible with Linq or whatever means you are using to store and extract data from your code.

SQL Server Compact may be downloaded here:
http://www.microsoft.com/en-us/download/details.aspx?id=30709
SQLite
is available here: https://www.sqlite.org/download.html


A view to show worked time per task per user? I have written this fiddle

CREATE TABLE task
    (`taskid` int, `description` varchar(255),
    PRIMARY KEY (`taskid`))
;

INSERT INTO task
    (`taskid`, `description`)
VALUES
    (1, 'Platform for building enterprise provisioning solutions'),
    (2,'Full-featured access management'),
    (3, 'Robust LDAP server for Java')
;

CREATE TABLE worktime
    (`taskid` int, `user` varchar(7), `startwork` datetime , `endwork` datetime)
;

INSERT INTO worktime
    (`taskid`, `user`, `startwork`, `endwork`)
VALUES
    (1, 'dirk', '2015-07-26 15:19:46', '2015-07-26 15:20:46'),
    (1, 'dirk', '2015-07-26 17:19:46', '2015-07-26 18:11:46'),
    (2, 'dirk', '2011-02-26 11:19:46', '2011-02-26 12:11:46'),
    (2, 'tim', '2011-02-26 11:19:46', '2011-02-26 12:11:46')
;

What I want is : Find the total time a user spent on a task.

E.g. say for task 1 and user dirk this would be 01:01 hours:minutes for task 1.

How can i create this view?

it's better to add a userID to your worketime table instead of User and also the name user is reserved word so don't use it. The view you are looking for is made like this

CREATE VIEW user_task_time as select `user`,taskid,sec_to_time(sum(TIME_TO_SEC(TIMEDIFF(endwork,startwork)))) as timeWorked FROM worktime GROUP BY user,taskid;

TIMEDIFF() return a time value and it is converted to second and back to time because SUM() function does not return correct results when used by time data type.


How to Identify Multiple Insert Rows from Single Transaction Is there method to identify multiple rows from single insert transaction?

IdentityId will uniquely identify each row. If 2000 rows were inserted from one insert transaction, how would I identify this? Is there like a BatchId() Identifier or TransactionId() reserved word? Using ETL default getdate() will not work, sometimes a transaction could span over milliseconds.

EG:

begin transaction
insert into dbo.Test2
select * from Test where ColumnA = 'etc...;
commit transaction

Refraining from using pattern below, may be inefficient, is there a  reserved keyword for batch transaction identifier?

select @MaxTransactionId = (select Max(transactionId) from dbo.Test2),
TransactionId = @MaxTransactionId + 1,

I would suggest using SEQUENCE
It is simple and the sequence value always increases in one way

-- you only need to create it once
create sequence seq as int start with 1
go

-- you can then do this
BEGIN TRANSACTION;
DECLARE @BatchID int = next value for seq
INSERT INTO dbo.Test2
       SELECT *, @BatchID AS [batch_id]
       FROM Test;
COMMIT TRANSACTION;


Enforce Data Integrity without a direct Foreign Key relationship I have a new table that will have a column named County_Codes.  I would like to enforce that data to a list of County_Codes from another table named Institutions.  However, due to this being a legacy, poorly designed table I do not currently have the option of re-writing the database and this is the best source of this data.  The values of County_Codes in the Institutions table can appear multiple times, or be NULL.  I can generate a query to create a unique list of values from this table:

select distinct County_Codes from dbo.Institutions

Is there a way to enforce records in the new table to one of those county codes in the database?

Example of County codes (they're char(5)):

00001
00101
10101
11000
90563
etc...

If I am understanding the situation correctly, then is there any reason why you can't extract that distinct list into a table to be a lookup table,  CountyCodes, create a PK on those "codes" (the CountryCode column), and then add a Foreign Key to the new table referencing that PK of the new CountryCodes lookup table?

CREATE TABLE dbo.CountryCodes
(
  CountryCode CHAR(5) NOT NULL
                CONSTRAINT [PK_CountryCodes],
  CountryName NVARCHAR(50) NULL
);

INSERT INTO dbo.CountryCodes (CountryCode)
  SELECT DISTINCT inst.Country_Codes
  FROM   dbo.Institutions inst
  WHERE  inst.Country_Codes IS NOT NULL;

ALTER TABLE dbo.NewTable
  ADD CONSTRAINT [FK_NewTable_CountryCodes]
  FOREIGN KEY ([CountryCode])
  REFERENCES dbo.CountryCodes ([CountryCode]);

-- optional FK for existing table:
ALTER TABLE dbo.Institutions
  ADD CONSTRAINT [FK_Institutions_CountryCodes]
  FOREIGN KEY ([CountryCode])
  REFERENCES dbo.CountryCodes ([CountryCode]);

And at that point, you could then add a Foreign Key on the existing table's County_Codes column to the PK of the new lookup table, right?

I am assuming that the values are 2 or 3 letter ISO country codes, and that each row of the existing table -- Institutions -- has one code in the Country_Codes column, even though the column's name is in the plural form.

IF for some reason the suggestion above will not work, then you can always create an AFTER INSERT, UPDATE Trigger on the new table to ensure that all values in INSERTED.CountryCode are IN (SELECT DISTINCT Country_Codes FROM dbo.Institutions WHERE Country_Codes IS NOT NULL) or are NULL. But doing that lookup each time can be expensive, hence the suggestion above to extract the list one time into a table.

The other benefit of extracting the distinct list of Country_Codes into a lookup table is that the values currently in the Institutions table might not be ALL possible values of valid Country Codes, so adding a new CountryCode to the new table could fail if only checking the values in the Institutions table. By having a separate lookup table, you can add values that are valid but have not yet been used. And if they are the 2 or 3 character ISO codes, you can get the full list from the ISO website and pre-populate the lookup table.


How to insert values in mysql taking previous row into account I have this table, players, with the following columns:

id
name
player_number
round

and I would like to auto fill the player_number (1 and 2) and increment the round when inserting a new player 1.

How can I do this without having to retrieve last player and make a new query ?

When I insert a new player, if it has no player, make round 1 and player 1. When adding a new player it makes him player 2 and round 1. After this adding a new player makes player 1 and round 2 and so on...

In any case you'll need some sort of sequence. As MySQL does not provide such things, as far as i know, the only resort is an AUTO_INCREMENT column. Put some in a table abused as a sequence. Than have a BEFORE INSERT FOR EACH COLUMN trigger on your game table. Inside it, insert into the sequence table, so that a new incremented value is created. Select the maximum value of the sequence table. You can now calculate your values for player end round:

Let v be the selected value from the sequence table. Then the player p = (v - 1) mod 2 + 1 and the round r = ceil(v / 2).

But be aware, that deleting rows from your games table will not reset the sequence table. The pattern will continue on where it left the last time an insert into the games table was made.

Example:

CREATE TABLE game (id INTEGER AUTO_INCREMENT,
                   player INTEGER,
                   round INTEGER,
                   PRIMARY KEY (id));

CREATE TABLE game_sequence
             (value INTEGER AUTO_INCREMENT,
              PRIMARY KEY (value));

DELIMITER $$
CREATE TRIGGER game_ai
               BEFORE INSERT
               ON game
               FOR EACH ROW
BEGIN
  DECLARE _value
          INTEGER;

  INSERT INTO game_sequence
              VALUES ();

  SELECT max(value) INTO _value
         FROM game_sequence;
  SET NEW.player = (_value - 1) % 2 + 1;
  SET NEW.round = ceil(_value / 2);
END;
$$

DELIMITER ;