What is the best way to represent an album and song entity in an e-commerce schema? I'm designing a simple mp3(music) ecommerce site, customers can either buy a single Track or whole Album, an Album is made up of Tracks, so there is a one to many relationship between an Album and a Track as shown below. I thought it would be a good idea to have one Orders table and one Product table, the idea I had is to have a generic Product table to represent either a Track or an Album as a Product but I'm kinda struggling to design that on my ERD. I was thinking in a inheritance approach.  all Tracks will have the same price but Albums will have an individual prices.

What is the best way to achieve this requirement? in the orders table I need to see if it was a Track or Album purchased and money spent.

I have searched for examples and samples without any luck.

An example will also help a lot.

There really is not a One True Answer, but there are plenty of samples available on the web.  One site is: http://www.databaseanswers.org/data_models/

This is actually a site with many simple data models. I would not call these a complete solution, but it is a good resource for getting started.

For a CD Collection there is a sample model, that focuses on content, not on the commercial side of selling content.

http://www.databaseanswers.org/data_models/cd_collection/index.htm

In addition if you search for something like example database model music you will find other examples.


Find all tables in a DB with column names that contain that substring? Say I have these three columns student_id, name, identify in three different tables in the same database.

The query should search for column names that contain that substring. i.e. id should return tablesstudent_id,identify from those three tables^.

This only works if the strings are equal.

I apologise if this is a repost I couldn't find another question similar, and I've hardly ever worked with databases. Thanks

Login using the MySQL command-line client.

mysql> USE Information_Schema;

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SHOW Tables;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| ENGINES                               |
<rest of tables snipped>

mysql> DESCRIBE Columns;
+--------------------------+---------------------+------+-----+---------+-------+
| Field                    | Type                | Null | Key | Default | Extra |
+--------------------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG            | varchar(512)        | NO   |     |         |       |
| TABLE_SCHEMA             | varchar(64)         | NO   |     |         |       |
| TABLE_NAME               | varchar(64)         | NO   |     |         |       |
| COLUMN_NAME              | varchar(64)         | NO   |     |         |       |
| ORDINAL_POSITION         | bigint(21) unsigned | NO   |     | 0       |       |
| COLUMN_DEFAULT           | longtext            | YES  |     | NULL    |       |
| IS_NULLABLE              | varchar(3)          | NO   |     |         |       |
| DATA_TYPE                | varchar(64)         | NO   |     |         |       |
| CHARACTER_MAXIMUM_LENGTH | bigint(21) unsigned | YES  |     | NULL    |       |
| CHARACTER_OCTET_LENGTH   | bigint(21) unsigned | YES  |     | NULL    |       |
| NUMERIC_PRECISION        | bigint(21) unsigned | YES  |     | NULL    |       |
| NUMERIC_SCALE            | bigint(21) unsigned | YES  |     | NULL    |       |
| DATETIME_PRECISION       | bigint(21) unsigned | YES  |     | NULL    |       |
| CHARACTER_SET_NAME       | varchar(32)         | YES  |     | NULL    |       |
| COLLATION_NAME           | varchar(32)         | YES  |     | NULL    |       |
| COLUMN_TYPE              | longtext            | NO   |     | NULL    |       |
| COLUMN_KEY               | varchar(3)          | NO   |     |         |       |
| EXTRA                    | varchar(30)         | NO   |     |         |       |
| PRIVILEGES               | varchar(80)         | NO   |     |         |       |
| COLUMN_COMMENT           | varchar(1024)       | NO   |     |         |       |
+--------------------------+---------------------+------+-----+---------+-------+
20 rows in set (0.01 sec)

Your desired SQL is something like this:

mysql> SELECT Table_Name, Column_Name FROM Columns WHERE Column_Name LIKE '%My_Search_String%';

My_Search_String could be 'student%' for student_id.
My_Search_String is case sensitive - and be careful, the underscore is a wild-card in SQL for a single character.

[EDIT]

I double checked about My_Search_String being case sensitive. It appears that metadata is searched in a case insensitive fashion.

mysql> CREATE TABLE xyz (x int, `xx` int, XX int);
ERROR 1060 (42S21): Duplicate column name 'XX'
mysql>

Now, if column_names were case sensitive, there should be no duplicate XX column. Just one to watch - it's the data in the fields (assuming VARCHARs) that is case sensitive.


Finding the last time a table was updated The query:

SELECT
    name AS TableName,
    create_date AS CreatedDate,
    modify_date as ModifyDate
FROM sys.tables
order by ModifyDate;

...will tell me the last time a table was created and modified (from a DDL perspective). But I want to know the last time actual data was either inserted or removed from the table.  Is it possible to get this in SQL Server?

You might be able to get an idea from

SELECT last_user_update
FROM   sys.dm_db_index_usage_stats us
       JOIN sys.tables t
         ON t.object_id = us.object_id
WHERE  database_id = db_id()
       AND t.object_id = object_id('dbo.YourTable')

but the data there is not persisted across service restarts and might not be accurate for your requirements (e.g. running DELETE FROM T WHERE 1=0 will update the time even though no rows were actually deleted)


Finding Serial plan from Plan cache My application goes slow sometimes and when i check the query plan plan for a particular operation,i see that serial pans got generated and gets stored in the plan cache and this is being taken for further execution.
So every time i query the plan cache and i see like 50+ plan(i search for particular view in the  query text to find out the queries) and to find the serial plan i have to open Query plan and see whether it is parallel or not.

SELECT
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,cp.creation_time,
cp.execution_count AS ExecutionCount,cp.plan_handle,
st.TEXT AS QueryText,
qp.query_plan AS QueryPlan
FROM sys.dm_exec_query_stats AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
--CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS qn(n)
where
st.text like '%FormSearchModel%'
--AND qp.query_plan.exist('//PhysicalOp="Parallelism"') = 0
--AND n.query('.').exist('//RelOp[@PhysicalOp="Parallelism"]') = 1
order by creation_time desc

It tried using query_plan.exist('//PhysicalOp="Parallelism"') = 0 to find the serial plan and it is not working.
How can i achieve this.?Any suggestion.

Try this out

WITH XMLNAMESPACES ( 'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p )
SELECT      OBJECT_NAME(st.objectid, st.dbid) AS ObjectName,
            cp.creation_time,
            cp.execution_count AS ExecutionCount,
            cp.plan_handle,
            st.text AS QueryText,
            qp.query_plan AS QueryPlan
FROM        sys.dm_exec_query_stats AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE       1 = 1
--AND st.text LIKE '%FormSearchModel%'
AND         qp.query_plan.exist('//p:RelOp/@Parallel[ .= 0]') = 1
ORDER BY    cp.creation_time DESC;

If you want to find plans where all operations are serial, you'd have to do something like this:

WITH XMLNAMESPACES ( 'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p ),
relop AS
(
SELECT      OBJECT_NAME(st.objectid, st.dbid) AS ObjectName,
            cp.creation_time,
            cp.execution_count AS ExecutionCount,
            cp.plan_handle,
            st.text AS QueryText,
            qp.query_plan AS QueryPlan,
            qp.query_plan.value('count(//p:RelOp)', 'INT') AS total_relop,
            qp.query_plan.value('count(//p:RelOp/@Parallel[ .= 0])', 'INT') AS serial_relop
FROM        sys.dm_exec_query_stats AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE  qp.query_plan.exist('//p:RelOp/@Parallel[ .= 0]') = 1
)
SELECT *
FROM relop
WHERE relop.total_relop = relop.serial_relop


How can I genereate a string from the returns of a SELECT query in Mysql? I need to create a string from data that is extracted from an SQL query, and wish to accomplish it within the SQL Query.

The return is something like

+---------------+
| foo_ids       |
+---------------+
| 63901-63601   |
| 64101-63501   |
| 65101-63501   |
| 65001-63401   |
| 64902-63401   |
| 64901         |
| 62401         |
| 63901         |
+---------------+

I would like to do 2 things to it,

separate strings where there are "-"s
create a string of the ids separated by ","

the strings will only contain numbers and "-"

my final output desired is,

63901,63601,64101,65101,63501,65001,63401,64902,64901,62401

is this possible/realistic or should I have a script or something to fetch it and do some string manipulation in there?

Please try this query:

select group_concat(foo_id separator ',') as ids from
(
select
 case
 when locate('-', foo_ids) > 0 then
  substr(foo_ids, 1, locate('-', foo_ids) - 1)
 else foo_ids
 end as foo_id
from foo_table
union all
select
 case
 when locate('-', foo_ids) > 0 then
  substr(foo_ids, (locate(foo_ids, '-') + 1), locate('-', foo_ids) - 1)
 else null
 end as foo_id
from foo_table
) t
where foo_id is not null

http://sqlfiddle.com/#!2/ce1b0a/7


Columnstore index on multi-tenant database I have a sql azure database that is used only for analytics.

It is constantly being updated with data pushed from the OLTP system.

The OLTP inserts/updates/deletes are pushed via webhooks to a service that propagates the changes into the analytics db in real time.

As a result, we have a db that must sustain both a OLTP and analytics work loads, althought the analytics queries performance is more important.

I haven't used columnstore indexes before, but after doing minimal research it seems like this type of payload can be good fit for nonclustered columnstore indexes. The clustered index would stay rowstore, for fast insert/update/delete.

But, I have an extra requirement that I haven't seem mentioned: multi-tenancy.
When users use our analytics app, they are always associated with a single tenant id, and the analytics queries will always have a WHERE clause on the tenant id. The tenant id column is highly selective. The biggest tenant makes up for less than 5% of all rows and most tenants make up for less than 1% of rows.

Is it still a good idea to use NCCI indexes for multitenants database? If so, should I set up my index in a particular way?

Is it still a good idea to use NCCI indexes for multitenants database?

If the alternative is to have a data structure that efficiently filters by tenant, then no.

If TenantId is the leading column in your clustered index, then single-tenant queries will be pretty fast.  The NCCI row groups will not be segregated by tenant, and so all the row groups will need to be scanned to find rows for a single tenant.

As rows are inserted, they are inserted into the "middle" of the clustered index, typically at the "end" of the rows for the tenant.  But rows are always inserted into the delta store for an CCI/NCCI.  And whenever the delta store has 1 million rows in it, it's rebuilt into a columnar row group.  So each row group will end up with the last million rows inserted across all tenants.

You can fix this with partitioning by TenantID, which could give each tenant (or group of tenants) a separate physical table (or NCCI).  Each partition will have its own delta store, which will fill up with single-tenant rows.  If you do partition, each partition might end up too small for a Columnstore to be useful. You'll want a few million rows per partition at least.

In general with multi-tenant data, you want to avoid interleaving tenant data in a way that requires you to read all tenants data to retrieve a single tenant's data.


What is the cheapest way to mirror/replicate data to multiple servers on SQL Server? I have an SQL Server 2014 Enterprise Edition with a lot of data.  I would like to mirror the data from that to at least 3-4 servers.  The mirrors are all SQL Server 2014 Standard Edition (no more money is available for Enterprise licenses).

How do I mirror the data from my main box (with the Enterprise Edition license) to other boxes?  I tried the mirroring feature, but it seems that it only allows single mirror.  I could you use Always On Availability groups, but that would require that all mirrors also be Enterprise Edition (unless I am reading the docs wrong).

At least one of the mirrors needs to be there almost real-time (1-2 minute delay is fine) data replication.  The other mirrors could have 1-2 hours delay.

So what are my choices?

P.S.  All the secondary servers are just read only.

P.S.  The purpose of the mirrored boxes are partially to off-load readonly queries to them.  These mirrors need to have near real-time data replication.  Another purpose is for analytics, which is a heavy load.  Today everything is on the same box and we are forced to do analytics at night so as not to disrupt users and there is just not enough time.

P.S.  The servers are nearby each other - on the same subnet, connected via a 10Gb link.

P.S.  Our license also allows a no cost upgrade to SQL Server 2016 when it becomes available.  Does that change anything?

Ok here is what I'd suggest:

Transactional replication
If unfamiliar, it can be set up relatively easily using the UI. This link will help: (http://www.codeproject.com/Articles/715550/SQL-Server-Replication-Step-by-Step)

Mirroring + log shipping would work alright also, as others have suggested.

If you have something like automated ETL queries you need to provide for, you can drop and recreate the snapshot of the mirror database prior to the query running. In the past I have added the drop and recreate snapshot as a step before running an SSIS package or whatever. If you are needing to make the mirror available for ad-hoc querying for a number of users, you can always work out a schedule with your users (like say every 30 minutes) to drop and recreate the snapshot such that it is almost completely up to date. Depends on the specific use case but I'm sure you can see what I'm getting at and tailor it to your needs. Make sure you have instant file initialization enabled for the SQL Server service for the snapshot file to be created instantly.

EDIT - Just realized snapshotting isn't supported in Standard edition, so mirroring won't help you.

Ultimately AlwaysOn is a great choice for your needs but if you have issues with editions then I'd just go with replication.


Full-text search migration 2005 -> 2012 (.ndf file?) So I'm trying to figure out the details for migrating from SQL Server 2005 to SQL Server 2012.

I tried to do restore and backup of a DB from 2005 to 2012. This worked well and for the text search catalog created a .ndf file.
I tried to detach and attach a DB from 2005 to 2012, this also seemed to work fine including the text search but I'm wondering where's the catalog files on the file system because I don't see a .ndf file or a dedicated folder for text search catalog (the way we used to have in versions prior to 2008)?

(PS. I atttached only .mdf and .ldf files, not sure how to attach a specific text search catalog file because it's multiple files)

I might have missed something but any suggestions will be helpful, thanks!

There are couple of things to consider :

backup and restore is the safest option that you can use for migration from SQL 2005 to 2012.

Refer here for more details of what you are experiencing.

Full text has undergone major chance in 2008 and up.

from BOL,

Attaching a SQL Server 2005 Database to SQL Server 2012

In SQL Server 2008 and later versions, a full-text catalog is a logical concept that refers to a group of full-text indexes. The full-text catalog is a virtual object that does not belong to any filegroup. However, when you attach a SQL Server 2005 database that contains full-text catalog files onto a SQL Server 2012 server instance, the catalog files are attached from their previous location along with the other database files, the same as in SQL Server 2005.

The state of each attached full-text catalog on SQL Server 2012 is the same as when the database was detached from SQL Server 2005. If any full-text index population was suspended by the detach operation, the population is resumed on SQL Server 2012, and the full-text index becomes available for full-text search.

If SQL Server 2012 cannot find a full-text catalog file or if the full-text file was moved during the attach operation without specifying a new location, the behavior depends on the selected full-text upgrade option. If the full-text upgrade option is Import or Rebuild, the attached full-text catalog is rebuilt. If the full-text upgrade option is Reset, the attached full-text catalog is reset.

EDIT:

You can attach the detached database along with the fulltext catalog using Create database ... FOR ATTACH.

-- Physically move the full text catalog to the new location.
--Attach the AdventureWorks database and specify the new location of the full-text catalog.
CREATE DATABASE AdventureWorks ON
    (FILENAME = 'D:\...\Data\AdventureWorks_Data.mdf'),
    (FILENAME = 'D:\...\log\AdventureWorks_log.ldf'),
    (FILENAME = 'D:\myFTCatalogs\AdvWksFtCat')
FOR ATTACH;
GO


Data Warehouse - Slowly Changing Dimensions with Many to Many Relationships As an example, let's say I have a fact table with two dimensions and one measure

FactMoney table

ProjectKey int

PersonKey int

CashAmount money

The two dimensions are defined like this:

DimProject (a type 0 dimension - i.e. static)

ProjectKey int

ProjectName varchar(50)

DimPerson (a type 2 slowly changing dimension)

PersonKey int

PersonNaturalKey int

PersonName varchar(50)

EffectiveStartDate datetime

EffectiveEndDate datetime

IsCurrent bit

Pretty straightforward so far. Now I'll introduce a Person Category concept.

DimCategory

CategoryKey int

CategoryName varchar(50)

And build an M2M relationship between DimPerson and DimCategory

BridgePersonCategory

PersonKey int

CategoryKey int

So - people can have 1..n categories.

My problem is - as Person is a slowly changing dimension, when a person's name changes, we add a new person row and update our effective dates and is current flags, no big deal.

But what do we do with the person's categories? Do we need to add more rows to the bridge table every time a new person version pops up?

And as a corollary, if a person's categories change, does that mean we need to create a new row in the person table?

(please note I've also asked this question at stack-O and as directed by the first commenter, now posted this question here.)

Does DimCategory have any Type 2 attributes that you are tracking?

If it is not, then you would only need to add a new record to your bridge table based on whenever a Type 2 attribute in DimPerson changes since you will be inserting a new record in DimPerson with a new surrogate key. You would then need to add or update your bridge table with this new surrogate key for whichever categories are assigned.

However, if an attribute on DimCategory changes, you would just update that record and there would be no change in the surrogate key so the bridge table would be unchanged.

Now if you want to track things like when a Category assignment to a Person changes as a Type 2 attribute, then you would need to build the Effective Start and End Dates in your bridge table to be able to tell at what point in time a Person's Categories change.


SQL Server - global temporary table vs normal table I have a stored procedure which I use to load csv files to SQL Server. This will initially bulk insert the file into a global temporary table then at the end it will do:

select cast(column1 as int) [column1], column2, column3... into table from ##temptable

and then proceeds to drop the ##temptable

Before the query select into table from ##temptable I run a series of select queries on the temporary table to determine what type the columns should be (and I'm confident this works properly).

Note that having a table (either normal or temporary) before creating the final output table is essential for the stored procedure.

What I'd like to know is if I'm better off using a global temporary table or if I should use a normal table or does it make no difference?

I've seen several articles about the differences between temporary tables and table variables but I've seen nothing when it comes to comparing global temporary tables to normal tables and it's difficult for me to test this properly in my environment.

A normal table will cause logging in your database, consume space, and require log flush on every commit.  So for temporary data, you should use a temporary table.

But you would normally use a regular (#) temporary table here, not a global (##) temporary table.

Global temporary tables are useful in the (very rare) scenario where multiple, separate sessions need access to the same temporary table.  Another scenario where a global temporary table can be useful is when the temp table is created in dynamic SQL, but referenced in the calling batch,  eg:

exec ('select * into ##foo from sys.objects')
select * from ##foo

But a global temporary table is not necessary to create a temp table in a stored procedure and access it in dynamic SQL, eg:

create table #foo(id int, data varchar(200))
exec ('insert into #foo(id,data) values (1,''hello'')')
select * from #foo

And when you create a regular temporary table in a stored procedure it will automatically be destroyed when the stored procedure ends, and you can run multiple instances of the same procedure without interfering with each other.


How does MOVE COMPRESS affect future data? I'm working with Oracle 11g and a requirement came down the pipeline to "[C]ompress our data [sic]." And I'm wondering how compression will affect future data.

The actual PL/SQL isn't an issue (something along these lines:
ALTER TABLE SOME_TABLE MOVE COMPRESS;), but I'm told by the DBA that MOVE COMPRESS will only compress the existing data, and that any further transactions will require explicit compression. After running that line, looking at the table's storage properties, Table Compression is properly set and everything, but that leads me to believe that this will go on in the background.

I guess my question is this: Do I need to run MOVE COMPRESS (or something similar) after new transactions, or will Oracle compress the data going forward? If not, is there something I can amend to my ALTER TABLE statements that will enforce this?

Your DBA was kind of correct, for the typical scenario.

MOVE COMPRESS compresses existing data with Basic compression.
Any new data inserted with regular insert will be uncompressed. New data inserted with direct-path insert will be compressed.

If you want all new data to be compressed, then you can use OLTP compression. Then syntax for that is COMPRESS FOR OLTP. OLTP compression requires extra license as it is part of the Advanced Compression option.


SQL Trigger to verify account, if not verified dont process stored procedure/transactions Is there a way to create a trigger or function to check a user against the database and if not verfied, the following stored procedure is not ran. I would like this option for verification check before stored procedure is ran

A function or trigger something along these lines

DECLARE @strtoken varchar(30)

IF NOT EXISTS (SELECT userid from users where token = @strtoken)
BEGIN
--stop any further transactions
END

Sorry, yes I meant to trigger the event of checking the token against the user table before any further processing of the stored procedure. The reason I'm doing this is because I'm using cookies and cookies can be changed. So I encrypted the token with MD5, 3DES and salted it. Right now I'm wrapping the stored procedure like this:

DECLARE @strtoken varchar(30)
DECLARE @userid bigint

IF EXISTS (SELECT userid from users where token = @strtoken AND userid=@userid)
BEGIN
--run stored procedure here WHERE userid = @userid
END

Of course, the user has to log in to use the web app, but I don't want anyone fiddling with the token. So I figured there was a way to check against the db with the token and if not verified, stop any further processing of the stored procedure without wrapping the entire stored procedure like I'm doing now

No you cannot use Triggers for this because Triggers do not fire in response to Read-type events (Execute or Select).  Nor should you because, in general, triggers are not an appropriate device for implementing security.

If you want to implement your security token idea on SQL Server(*) there are several general ways to go about it:

Alter your Stored Procedures:  Add an additional parameter to your stored procedures, then have your stored procedures themselves check this.  This is the recommended method.
Wrap your Stored Procedures: If you do not want to alter all of your stored procedures, you could instead do as one of the commenters suggests and write wrappers for all of your stored procedures.  The wrapper has the token as an additional parameter, which it first checks and then calls the actual procedure.  You use SQL-object security to prevent the actual procedures from being executed by anything other than the wrapper procedures.  This approach is not usually recommended because of the extra work and its higher maintenance.  However, this can be alleviated through automation if your database's security, schema and procedure design is structured correctly for it (this is how I do it, though not for this purpose).
A variant of #2 (above) would be to have a single Wrapper Procedure that calls all of the other procedures.  This would require that the first two parameters were the token and the actual procedure name and the rest would be the actual parameters shifted over two places.  The Universal Wrapper would then check the token and if valid, then call the actual procedure.  This is actually surprisingly easy to do (and does not require dynamic SQL either), however, it does also require properly designed security schemas to enable closing other security holes.  This approach is not usually recommended because of it's even higher dependence on properly designed security and schema structures and because auditors do not generally understand it and it makes them nervous.

(* - and this may well be something that is better addressed at the web/app server level, since that seems to be where the weakness is.)


New users not working on MySQL 5.6 in Amazon RDS I'm using the CREATE USER and GRANT commands as mentioned here http://dev.n0ise.net/2012/09/amazon-rds-mysql-add-additional-users-with-limited-previledges/

When I try to connect I get this in MySQL Quer Browser:

Could not connect to host '...sa-east-1.rds.amazonaws.com'.
MySQL Error Nr. 0

Output in MySQL Workbench:

Your connection attempt failed for user 'site' from your host to server at ...sa-east-1.rds.amazonaws.com:3306:
  MySQL server has gone away

Some details:

MySQL version: 5.6.13
Credentials are typed correctly. When I miss them I get the standard 1045 access denied error.
The user created with the instance and another limited user I created some weeks ago are working fine
Same commands worked fine on a non RDS server

Any ideas?

Thanks in advance.

I forgot that I had created a stored procedure to set the time zone after every connection made.

Looking at the server log I found that the problem was that the new users didn't have permission to execute it.


Composite Primary Keys I have a small scenario here and I think using composite primary keys would be a good solution. I'm doing this in SSMS. Here is the code to create the table:

CREATE TABLE [PSDesk].[dbo].[Local](
    [UserID] [varchar](10) NOT NULL,
    [FullName] [varchar](50) NOT NULL,
    [ComputerName] [varchar](15) NOT NULL,
    [IPAddress] [varchar](50) NOT NULL,
    [NETFramework] [varchar](50) NOT NULL,
    [ComputerModel] [varchar](50) NOT NULL,
    [UDrive] [varchar](50) NOT NULL,
    primary key (UserID, ComputerName)
)

I would like the UserID and ComputerName columns to be unique, meaning there will only be one record for each combination of UserID and ComputerName. Is this a good method to get the solution i'm looking for? I don't have much experience so i'm simply looking for advice, I threw this together just using my google-fu

Is that syntax correct? Yes. You could also create the PK afterward and with a few different syntax options, but this will build.

But there are a lot of answers to this question. And a lot of questions in this question. "Is this a good idea?"  Well it depends – and that is sort of opinion and situation based so the question could be closed if not narrowed down a little with a more specific question.

When you define something as the primary key, you are stating that that is the fact or set of facts which describes one row and only one row.

So for what this table looks like the combination of UserID and ComputerName seem appropriate enough from a database design perspective alone.

There are still other questions though. Questions not in the question but things like:

Why UserID as a varchar? Why not an INT that points back to a User(s) table? This would be a potentially more proper normal form.
Same for ComputerName.
What is the purpose of the table – seems like it describes a computer. Why have the user info in there at all? Why not have a User table and a Computer table and then a table which assigns a user to a computer? (Not saying one way is more right just a question.)
The default behavior for SQL Server when creating a primary key is to make the index on the table clustered when doing it through the GUI – this isn't bad, but I like narrower clustered indexes and clustered indexes which are monotonously increasing and less likely to have index fragmentation and page splits. For that reason, I tend to be more often than not in the "composite clustered index" camp. Sometimes that means I make up a PK and let that be clustered like ComputerUserID – and have that be an identity column. Sometimes I just use that as the clustered and still use the natural key as the primary key as you are trying.
If you created a surrogate key to be the PK for this table and be the clustered index, you could still create a unique index on the combination of ComputerName and UserID and enforce the uniqueness.

Just some food for thought. There are likely many answers to this question. Which is why I'm not 100% sure it will last, but here are some thoughts for now at least.


2 doubts in db design I'm studying about db by doing some modeling exercises. But I've two doubts in a exercise.

In the exercise:

  A Event can have only one organizer, and the organizer is a user, and a user can organize and participate in many events.

So this is a one to many relationship between organizer and event. My doubt is about the need to create a third table for the relation, because it's just necessary to have a third table in a many to many relationship, right?

But for example, in this case the event organizer has some attributes that don't belong to the user like "details", so  we need to create a third table for the event organizer, but this table is not a relationship table it's just a normal entity table right?

But to do the relation, that a user, that is an organizer can organize multiple events and an event is organized by a single organizer, that is a user, how should we do this? Is the below correct? Because the "event_organizer" table seems a relationship table but maybe it should be a entity table.

The other doubt is that an event can have a maximum of 3 categories, and one category can have many events. So it is a many to many relationship? Or because an event can have a maximum of 3 categories is not and it needed a different approach?

I'm not familiar with the layout of your database design (there are many layouts in the market). But what I'm figuring out is that you are trying to connect two entities together, directly. Which is wrong. Two entities can only be connected by a relationship (no matter what ER layout you are using).

Your first query can be subdivided into three parts:
1) An event can have only one organizer
2) Organizer is an instance of User
3) There is a one to many relationship between event and organizer.

So you have three entities: Event, Organizer, User.
Then, if you have studied about weak entities, you would want to convince yourself that organizer is a weak entity which is dependent on the entity User. Connect entities User, and Organizer with a relationship "is_instance". Now you are done with the entity User.

Let's now move on to the relationship between entities Organizer, and Event. You already figured out that it has a one to many relationship from Event to Organizer. So great!!

Now coming to your next query: An event can have at most three categories but a category can have any number of events. You can say that this is a kind of many to many relationship. Some ERD allow you to explicitly specify the cardinality between the entity and the relationship (as far as I remember it is done by specifying the number over the arrow joining the entity and relationship). So you can model this as joining the entities Event, and Category by a relationship has_category and specify the range 1..3 over the line joining Event and has_category.

Extra clarification:
There is always a many to one relationship from a weak entity towards its underlying entity. Your inclusion of details to the entity organizer and the dependence of Organizer's ID on User's ID makes it a separate entity.

(Note: due to lack of information, I assumed that there may be more than one value for the attribute "detail" for the same user in the entity Organizer. If this is not so and that there is only one tuple in the entity Organizer for every ID such that every tuple in the entity Organizer is unique on attribute Organizer_id, then you could also choose to make the Organizer entity as a strong entity and have a total participation of entity Organizer in the relationship between User and Organizer)

Additionally, your query also states that the user can participate in many events, so there can be a many to many relationship between entities User, and Events.

Hope this helps.


MySql : Grant privileges for specific session We have the user with all privileges. And while testing/grabbing information, playing with selects, how to prevent accidentally dropping table, delete and etc?

One of the solutions is to create specific user with permissions only to read. But it requires creating it on each DB and let's say you have a hundred of it or you don't have permission to create the user.

Is there equivalent SQL_SAFE_UPDATES, that will cover ALL DDL and modifying data for the specific session/connection?

Another approach is to provide a general read-only user and publicize the password.

To create the hundred GRANTS,

SELECT  CONCAT("GRANT SELECT   ON ",
                schema_name, ".* TO 'read_only_user'@'*'
                IDENTIFIED BY 'foo'"
              )
    FROM  `SCHEMATA`
    WHERE  schema_name NOT IN ('information_schema', 'mysql',
                               'performance_schema');

(Then copy & paste the results into the mysql commandline tool.)  (Or make a Stored Procedure to do the work.)


copy tables and contents of database I have created a database on my remote hosting server. It has the same name as my local database on my laptop. I don't know how to create a script or transaction to copy all tables and tables' contents to my new remote database. This script or transaction should not create a new database, just create the tables and tables' contents the same as the local database. I am using SQL Server 2012.

If this is not needed to perform on each and every day but sometimes, I suggest the following procedure:

Open the source server in Management Studio. Right click the database. Choose "Tasks" and "Generate Scripts". In the wizard choose either "entire database" or "specific objects" (and choose your tables).
Click Next.
Click Advanced.
At the property "Check for existence" choose "TRUE".
At the property "Types of data to script" choose "SCHEMA AND DATA".
At the property "Script Drop and Create" choose "Script Drop and Create" (this (re-)creates the (all|selected) tables with the current structure).
Click OK.
Decide if you want a file created or just the script opened in a new window.
Click OK / OK.
Run the script in your destination server and you are done.


MongoDB store weekly data? I am trying to think of the best way to store my applications data in weekly chunks.

The app is a basketball statistics app, after all the games are finished for example in week one I would like to store the tracked data into a week 1 database.

Right now all the data is stored using a REST api and the app has 2 collections, one for players and one for teams.

The client side pulls in the teams collection then pulls in the players collection.

I am just lost on how I would go about separating data by weekly, so that the main client side still pulls in the teams and players but in week two has a fresh copy, but in another area in my app I can still pull old data by week.

I hope this is clear enough, I would greatly appreciate some insight.

have you looked at having Historic Players and Historic Team collections?

Then if you wanted stats about the players at the time, you'd probably want to copy out the current players on the team in your historic games documents.  You'd probably want to store that as an array in the Historic Games table as the current players list might be different than the players from last week/year.

Keep the position of the players in the document in mind so you can end up using proper indexes.

historic players
_id: jfoi23j8xx
PlayerID:Bo Jackson
weekinfo<array or tags>
[week 1- hits:30, misses:3, bunts:7, TeamID: LA Raiders],
[week2 - hits:17, misses:2, bunts:3, TeamID: Kansas City Royals]

historic teams:
_id:10u40o321js
TeamID: LA Raiders
Weekinfo<array or tags>
[week 1 - consolidated hits: 73, misses: 12],
[week 2 - consolidated hits:65, misses: 16]

Something like that might be a good start.  Does it meet your requirements?  You might want to have your app handle the aggregation but that's up to you.


Please review my first thought-out attempt at a database schema I am researching database design and theory and have finally made a thoughtful attempt at creating a schema that would be based upon SQL.  I would like, if someone has time, to review my layout and make sure that it is logical.  Tips and criticism aimed at bettering my knowledge is of course welcome! :)

Here is my general goal:
Create a database that will allow for a business with multiple stores to be able to track employee training progress by simply marking date trained and date verified.  Basically, this would be the hierarchy:

Business 1
--Store Location (a)
----Employee
----Employee
----Employee
--Store Location (b)
----employee
----employee
----employee

Business 2
--Store Location (a)
----employee
----employee
--Store Location (b)
----employee
----etc...

This schema should allow for unlimited businesses, stores, and employees.

As far as the training goes, there should be this type of hierarchy:

Business 1
--Employee Class           (Manager, Employee, Salesman, Associate, etc)
----Training Category      (Sales, Stocking, Manufacturing, etc)
------Training Activity    (How to sell X product, How to stock Y aisle, etc)
------Training Activity
------Training Activity
----Training Category
------Training Activity
------Training Activity
--Employee Class
----Training Category
----Training Category

Business 2
--Employee Class
----Training Category
------Training Activity
------Training Activity
------Training Activity
----Training Category
------Training Activity
------Training Activity
--Employee Class
----Training Category
----Training Category

The following things should be variable based on business needs and created upon creation of business's account:

 * Number of Stores
 * Number of Employees
 * Employee Classes
 * Training Categories
 * Training Activities

So the grand questions are:

How close did I get?
How can it be done better?

Here is the Diagram (http://i.stack.imgur.com/V38cr.jpg) :

The picture was useful.  I think you did well.

I don't think of things as hierarchies, where stuff like Category gets in the way.  I make tables for the nouns (users, activity) and then adjectives become columns (category, class).

You use the word 'class' for 'employee type' or 'employee group'.  In a training system like this, I would have expected the 'class' table to store your 'activities'.  If your table names match the nouns that your end-users are using, it'll be easier in the long run.

My comments:

Give training_activities its own id, and use that from training_tracker.
An employee (a single employee profile) can't move from one company to another nor work for two companies at the same time.


Avoid Sort on Indexed Group By caused by DATE_TRUNC Is there a way to avoid Sort operation for GROUP BY, caused by the DATE_TRUNC function not being recognized as tstamp, which is a part of the ix index? That function doesn't really affect the order maintained by the ix.

CREATE INDEX ix ON tbl(col, tstamp, num)

  SELECT col,
         DATE_TRUNC('MONTH', tstamp AT TIME ZONE 'UTC') AS trunc,
         SUM(num)
    FROM tbl
   WHERE col IN (?, ?, ...)
     AND tstamp >= ?
     AND tstamp <  ?
GROUP BY col, trunc

Precision parameter for DATE_TRUNC is dynamic, it could also be 'DAY' or 'YEAR'... so I cannot index that function without creating an index for each variation.

The execution plan I get:

Finalize GroupAggregate
  Group Key: col, DATE_TRUNC('MONTH', tstamp AT TIME ZONE 'UTC')
  ->  Gather Merge
        Workers Planned: 2
        Workers Launched: 2
        ->  Partial GroupAggregate
              Group Key: col, DATE_TRUNC('MONTH', tstamp AT TIME ZONE 'UTC')
              ->  Sort
                    Sort Key: col, DATE_TRUNC('MONTH', tstamp AT TIME ZONE 'UTC')
                    Sort Method: external merge  Disk: ...kB
                    -> Parallel Index Only Scan using ix on tbl
                         Index Cond: ((col = ANY (...)) AND (tstamp >= ...) AND (tstamp < ...))
                         Heap Fetches: 0

Execution plan I want would look something like this:

GroupAggregate
  Group Key: col, DATE_TRUNC('MONTH', tstamp AT TIME ZONE 'UTC')
  ->  Index Only Scan using ix on tbl
        Index Cond: ((col = ANY (...)) AND (tstamp >= ...) AND (tstamp < ...))
        Heap Fetches: 0

(I get that if I don't use DATE_TRUNC)

There is no way to avoid the sort with your query, because PostgreSQL does not know if the rows returned by the index scan will be correctly sorted or not, and a group aggregate requires sorted input.

Your only chance is to increase work_mem until you get a hash aggregate, which does not require sorted input.


DB locks being created on temp tables. Can percona ignore temp tables for replication I have a stored procedure that I am using that creates a temp table.  Recently I've noticed the logs are filling up with locks. I think the temp table is trying to be replicated but I am unsure.

*** Victim TRANSACTION:
TRANSACTION 0, ACTIVE 1 sec fetching rows
mysql tables in use 3, locked 3
MySQL thread id 1875027, OS thread handle 140436968662784, query id      41228278 dbserver 192.168.5.5 web_east Sending data
INSERT IGNORE into temp_table select ... from db.table as f inner join   data_temp temp on temp.id =f.id where f.date1 >= "2018-07-01 00:00:00" and f.date2 < "2019-08-01 00:00:00"

*** WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2272 page no 5986916 n bits 288 index   db_name_date_idx of table `db`.`table` trx id 421941145506800 lock mode S  locks gap before rec
2019-12-02T10:25:38.389502Z 10 [Note] WSREP: --------- CONFLICT DETECTED --------
2019-12-02T10:25:38.389506Z 10 [Note] WSREP: cluster conflict due to high  priority abort for threads:
[Note] WSREP: Winning thread:
THD: 10, mode: applier, state: executing, conflict: no conflict, seqno: 7653754
SQL: (null)

Consider trying row based replication ( https://dev.mysql.com/doc/refman/5.7/en/replication-formats.html ) in your case to prevent issues like yours. If your inserts are mostly complex queries, inserting only the resulting rows, rather than reexecuting the full queries may decrese locking and performance. Note this is highly application specific and may have other disadvantages in other queries.


Performance implications of MySQL VARCHAR sizes Is there a performance difference in MySQL between varchar sizes?  For example, varchar(25) and varchar(64000).  If not, is there a reason not to declare all varchars with the max size just to ensure you don't run out of room?

You must realize the tradeoffs of using CHAR vs VARCHAR

With CHAR fields, what you allocate is exactly what you get. For example, CHAR(15) allocates and stores 15 bytes, no matter how characters you place in the field. String manipulation is simple and straightforward since the size of the data field is totally predictable.

With VARCHAR fields, you get a completely different story. For example VARCHAR(15) actually allocates dynamically up to 16 bytes, up to 15 for data and, at least, 1 additional byte to store the the length of the data. If you have the string 'hello' to store that will take 6 bytes, not 5. String manipulation must always perform some form of length checking in all cases.

The tradeoff is more evident when you do two things:
1. Storing millions or billions of rows
2. Indexing columns that are either CHAR or VARCHAR

TRADEOFF #1

Obviously, VARCHAR holds the advantage since variable-length data would produce smaller rows and, thus, smaller physical files.

TRADEOFF #2

Since CHAR fields require less string manipulation because of fixed field widths, index lookups against CHAR field are on average 20% faster than that of VARCHAR fields. This is not any conjecture on my part. The book MySQL Database Design and Tuning performed something marvelous on a MyISAM table to prove this. The example in the book did something like the following:

ALTER TABLE tblname ROW_FORMAT=FIXED;

This directive forces are VARCHARs to behave as CHARs. I did this at my previous job back in 2007 and took a 300GB table and sped up index lookups by 20%, without changing anything else. It worked as published. However, it did produce a table almost double in size, but that simply goes back to tradeoff #1.

You could analyze the data being stored to see what MySQL recommends for column definition. Just run the following against any table:

SELECT * FROM tblname PROCEDURE ANALYSE();

This will traverse the entire table and recommend column definitions for every column based on the data it contains, the minimum field values, maximum field values, and so forth. Sometimes, you just have to use common sense with planning CHAR vs VARCHAR. Here is a good example:

If you are storing IP addresses, the mask for such a column is at most 15 characters (xxx.xxx.xxx.xxx). I would jump right at CHAR(15) in a heartbeat because the lengths of IP addresses will not vary all that much and the added complexity of string manipulation controlled by an additional byte. You could still do a PROCEDURE ANALYSE() against such a column. It may even recommend VARCHAR. My money would still be on CHAR over VARCHAR in this instance.

CHAR vs VARCHAR issues can be resolved only through proper planning. With great power comes great responsibility (cliche but true)


ADD PERIOD FOR SYSTEM_TIME on table failed I have:

table with existing data
SQL Server 2016 SP1
SQL Server Management Studio 17.5

I am using the following statement to make a make my table a temporal one:

ALTER TABLE [dbo].[AnalysisCustomRollupsV2JoinGroups]
ADD [SysStartTime] DATETIME2(0) GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT DF_AnalysisCustomRollupsV2JoinGroups_SysStart DEFAULT GETUTCDATE()
   ,[SysEndTime] DATETIME2(0) GENERATED ALWAYS AS ROW END HIDDEN CONSTRAINT DF_AnalysisCustomRollupsV2JoinGroups_SysEnd DEFAULT CONVERT(DATETIME2(0), '9999-12-31 23:59:59'),
PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime]);

ALTER TABLE [dbo].[AnalysisCustomRollupsV2JoinGroups]
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.AnalysisCustomRollupsV2JoinGroupsChanges));

The issue:

On my local SQL instance I have many databases; it's very strange that the query runs successfully on some of them, and on some of them it gives me the following error:

  Msg 13542, Level 16, State 0, Line 51 ADD PERIOD FOR SYSTEM_TIME on
  table 'dbo.AnalysisCustomRollupsV2JoinGroups’ failed because there are
  open records with start of period set to a value in the future.

Sometimes, when I am debugging/executing the query, the initial query runs successfully.

I read, that this could be because I have existing data in the table. So, I have change the logic like this:

create buffer table and populated it with all records
delete the records from the original table
create the temporal table
move the records back and drop the buffer table

and again, on some databases it is OK, and on other is not. Trying to resolve the issue I have found the following:

  For the StartDate I’ve specified the current UTC date – this could be
  any date and time that’s not in the future, though note it should be a
  UTC one. If I’d tried to use GETDATE, as I’m currently on British
  Summer Time, I would get the following error: Msg 13542, Level 16,
  State 0, Line 51 ADD PERIOD FOR SYSTEM_TIME on table
  ‘TestAudit.dbo.SomeData’ failed because there are open records with
  start of period set to a value in the future.

What does the above means? I need to change the machine time? Or because my local machine is not on UTC time I am getting this error sometimes?

I think I have found how to fix my issue, but I am not going to accept this as answer as I am not able to explain what is causing the problem and guarantee this will work anytime. It's fix found after a lot of testing and I will be glad if someone can bring more light here.

I have never used datetime2 with precision. So, I went back to the source of this format datetime2(0) - Alter Non-Temporal Table to be System-Versioned Temporal Table. The only difference with the script I have been using was the date time function. I used GETUTCDATE() as I do not need to be so precised with datetime(0)(2018-03-15 07:21:02 for example) and in the example it is  SYSUTCDATETIME(). So, I have changed it.

I have create a script which is dropping a database if exists, restoring a database from backup and then executing my code in a loop (as I said I get the error sometimes and it was very hard to reproduce it).

I have run a the script a lot of times and I was getting different number of fails (sometimes 70%, sometimes 50%, sometimes below):

I have found this Why is GETUTCDATE earlier than SYSDATETIMEOFFSET? discussion about differences between old and new date time functions. Then build the following query:

DECLARE @UTC DATETIME2(0) = GETUTCDATE();
DECLARE @SYSUTC DATETIME2(0) = SYSUTCDATETIME();

WHILE DATEPART(SECOND, @UTC) = DATEPART(SECOND, @SYSUTC)
BEGIN;
    SET @UTC  = GETUTCDATE();
    SET @SYSUTC  = SYSUTCDATETIME();
END;

SELECT @UTC AS [UTC]
      ,@SYSUTC AS [SYS UTC]
      ,DATEPART(SECOND, @UTC) AS [UTC sec]
      ,DATEPART(SECOND, @SYSUTC) AS [SYS UTC sec]
      ,CASE WHEN @UTC < @SYSUTC THEN 1 ELSE 0 END AS [TimeTravelPossible]
      ,CONVERT(DATETIME2(0), @UTC) AS [UTC date]
      ,CONVERT(DATETIME2(0), @SYSUTC) AS [SYS UTC date]
      ,IIF(CONVERT(DATETIME2(0), @UTC) = CONVERT(DATETIME2(0), @SYSUTC), 1, 0) AS [Are The Same];

I just wanted to check if I can get different datetime2(0) dates using sys and not sys date time function. And of course it is possible.

Maybe, the checks that the engine is making is doing something like this, comparing its current date with my newer date and this is causing the error - open records with start of period set to a value in the future.

I have change the script like this and executed 1 000 times last night - no errors were generated. So, I believe I have fixed this particular issue, but I can't be sure.

ALTER TABLE [dbo].[AnalysisCustomRollupsV2JoinGroups]
ADD [SysStartTime] DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT DF_AnalysisCustomRollupsV2JoinGroups_SysStart DEFAULT SYSUTCDATETIME()
   ,[SysEndTime] DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN CONSTRAINT DF_AnalysisCustomRollupsV2JoinGroups_SysEnd DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),
PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime]);

ALTER TABLE [dbo].[AnalysisCustomRollupsV2JoinGroups]
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.AnalysisCustomRollupsV2JoinGroupsChanges));


I want to reset a column of the table after a month I have a table e.g T1

I want to reset all rows where status = 0 and store these data (where status=0) in another table T2, but I want to do this after a month.
Kindly suggest what to do.

I am doing this in FEE Management system where a student will pay fee once in a month and after a month status should be "0" because I will use this table again for the next month. and i want to copy all data where status=1 in another table before reset the status column.

Try using a transaction,

BEGIN;
  INSERT INTO t2
    SELECT *
    FROM t1
    WHERE status=0
    FOR UPDATE;

  DELETE FROM t1
    WHERE status=0;

COMMIT;


Upserting 50K records into big table (app. 350 columns) in postgresql Using C# I'm trying to upsert from csv file (~ 55000 records) to a postgresql table with 350 columns. I'm just building the upsert statements from header and record which are comma separated. Like this:
INSERT INTO public.table(field1,fields2,field3, ..... ,field350) VALUES(?,?,?,?,?,.......,?) ON CONFLICT(field1) DO UPDATE SET ...;
INSERT INTO public.table(field1,fields2,field3, ..... ,field350) VALUES(?,?,?,?,?,.......,?) ON CONFLICT(field1) DO UPDATE SET ...;
INSERT INTO public.table(field1,fields2,field3, ..... ,field350) VALUES(?,?,?,?,?,.......,?) ON CONFLICT(field1) DO UPDATE SET ...;
INSERT INTO public.table(field1,fields2,field3, ..... ,field350) VALUES(?,?,?,?,?,.......,?) ON CONFLICT(field1) DO UPDATE SET ...;
....etc

I know about the bulk insert Copy operation which gave me for the inserts a very good peformance, but as mentioned, I need to run upserts and I didn't find anyway to run these upserts using this COPY thing.
Moreover, I can't run these in transcations because we want to check line by line and if a line fails, we don't want to fail the whole process, but log exception and continue with next line.
Using a stored procedure also didn't work because of the big number of columns that we have (350) which exceeds the allowed (100).
With my C# script i'm achieving 50K in 2 minutes, I know that this can be optimized. So is there any effecient way that you can suggest it to me?

Some suggestions:

Use prepared statements. That will speed up execution, because the statement doesn't have to be planned every time.

Use savepoints. That way, you can have a single big transaction and still recover from errors:

Set a savepoint every 100 rows or so.

If a row fails, rollback to the savepoint and redo everything up to the failing row, then skip that and start a new savepoint.

If you have many errors, perhaps fewer than 100 rows would be preferable, so that you don't have to redo so much work. But setting a savepoint for every single row will also not be good for performance.

COPY the data to a temporary table and then
INSERT INTO finaltable
SELECT * FROM temptable
ON CONFLICT ...;


SQL Server CPU spikes - possibly non-query related - suggested indicators I'm looking for suggestions for indicators I could monitor to discover what is causing these cpu "blunted spikes".

Some known information:

sqlserver.exe is confirmed as the process consuming the CPU (2008 R2 Standard edition).
It's very intermittent in nature.  Can't pin it down to a particular query. Profiler traces don't seem to show any correlation. In fact, it looks as though all queries are being made to wait during the spike, but interestingly there seems to be a correlation with there being a service broker queue with a backlog of messages currently being processed.
Service broker is being used purely for asynchronous intra-database stored procedure execution, with heavy use of CLR functions.

I'm suspecting some sort of background thread or process that is "catching up", but haven't been able to pin it down yet.

The first thing I would suggest you check is your "max degree of parallelism" via sp_configure.  By default this is set to 0, so a parallel query will consume all CPU on the box (up to 64 threads) until it finishes if this setting is unchanged.  I've seen this occur on more systems than I care to admit over my career, and it tends to be even more frustrating on the larger servers (with a higher number of CPUs).  If this is set to the default of 0, the recommendation from MS on what this should be can be found here: http://support.microsoft.com/kb/2806535

If it's a smaller server with very few CPUs (e.g. 1 or 2) it could be something as trivial as auto-update statistics kicking in, though that has been reportedly rare... but rare still happens.  You'd have to check if that is the culprit via a profiler trace to be certain though.


connect to oracle db server remotely I have Oracle 9i installed on machine connected to the internet behind NAT, and the Oracle DB server is listening on default port 1521, when I connect over the LAN IP to the database server it works perfectly, and I did  port forwarding on port 1521 to the private IP address in tp-link router to make the Oracle client machine connect to Oracle DB server over public IP, for example: 1521-192.168.0.108 forwarding port and its successful on canyouseeme.org but when I use this command:

SQL>connect username/password@GLOBAL_IP/service_name
Error: ORA_12170: TNS : Connect timeout occurred

*note: when I try to connect to the Oracle database server using Teamviewer vpn, it also  works and connected so how to deal with this ?

What is probably happening is that you have a router that doesn't support NAT loopback or the router configuration isn't properly set up.

You can connect from the outside as proven by the fact that canyouseeme.org sees you, and you can VPN into it because TeamViewer connections come from TeamViewer servers (hence the outside), and you can also connect to the internal IP.

If you want transparent connections from the inside and the outside you would need to either set up DNS, so that in your internal LAN the hostname.yourdomain.org points to the internal IP and make sure that from the outside the same hostname points to your external IP or get a router that supports NAT loopback.

There is plenty to find if you google 'nat loopback connect to external ip from lan' or you might find this post on serverfault interesting: Loopback to forwarded Public IP address from local network - Hairpin NAT


Difference between UPSERT and MERGE? From the PostgreSQL wiki,

  MERGE is typically used to merge two tables, and was introduced in the 2003 SQL standard. The REPLACE statement (a MySQL extension) or UPSERT sequence attempts an UPDATE, or on failure, INSERT. This is similar to UPDATE, then for unmatched rows, INSERT. Whether concurrent access allows modifications which could cause row loss is implementation independent.

Further PostgreSQL's INSERT ... ON CONFLICT DO NOTHING/UPDATE is marketed as UPSERT and was added in 9.5

What then is MERGE? And how does it fit into the mix?

Generally,

UPSERT is built off of INSERT
MERGE focuses on merging/synchronizing tables and provides

conditionality (where clauses)
DELETE support.

UPSERT lacks conditionality, In PostgreSQL, you had some ability to specify conditions, by proxy of the index that was being violated, for instance

ON CONFLICT ON CONSTRAINT countries_pkey DO NOTHING;
ON CONFLICT (country) DO NOTHING;

But it didn't provide the ability to specify multiple conditions nor the ability to DELETE in any condition, both of which permit a more rich set of rules making it possible to "synchronize tables with minimal work" which seems to be the goal of MERGE.

As another matter, from Peter Geoghegan's post highlighting some of the differences "SQL MERGE is quite distinct from UPSERT"

UPSERT is atomic in its insert-or-update
SQL MERGE doesn't technically require a UNIQUE INDEX.

See also

PostgreSQL patch returned 9/11
PostgreSQL 11 MERGE syntax (since removed)


Can Oracle TimesTen (or 12c's In-Memory) benefit from GPU? I have looked into TimesTen and In-Memory recently. They claim they use SIMD (Single instruction, multiple data) to speed up queries. We use HP DL380 G9 Servers for our DB. Since This series are limited to the Intel Core architecture, It can't benefit from more than 64 CPU cores (32 physical w/ hyperthreading) which Linux kernel 4 (Oracle Linux 7) can provide, opposite to the AMD64's HA which scales fine.

The question is: Can Oracle utilize some sort of GPU parallelism on the box, as OpenCL2 allows shared memory architecture? Is there a way to define GPU cores or compute units as processing units for Oracle?

Any application that uses GPUs for processing has to be custom-written to access the GPU's processing power.  Where GPUs shine are in floating point arithmetic, something that is low on the list of database operations, so no, no database software is likely to harness GPUs for processing.


How to restore postgreSQL schema dump to schema with different name I know I can alter schema name after restoring but the problem is the name might already exist and I don't want to touch that existing schema.

The dump type is "custom".

So effectively I want something like:

pg_dump -U postgres --schema "source_schema" --format "c" --create --file "source_schema.bak" my_db
pg_restore -U postgres --exit-on-error --dbname "my_db" "source_schema.bak" --destination-schema "destination_schema"

If that is possible it would be fine if I could copy (and rename) source schema before backup.

If the data is smallish, I would just do the pg_dump, restore it to a temporary just-for-this-purpose database server, rename the schema within that temporary server (ALTER SCHEMA...RENAME...), and dump it out of that temporary server to do the final restore.


Standard naming for a credential systems Is there a standard naming convention in regards to credential oriented systems? (Logins) The only one I could really come up with (in a shorthand form) is using the word "Gateway". Representing a gateway to further functionality behind a log in form.

Please note. I am NOT asking for suggestions on what I should name my schema objects. I am asking if there is an established standard for naming such things. If there is not then an adequate answer would outline this fact. Please do not post opinions on what you believe is the right way

If I understand your question correctly, the mechanism you are describing is an authentication system.

Basically speaking, this kind of mechanism allows the identification of a certain party (e.g., a person or a machine process) that is attempting to access a particular system. Said party provides a certain set of credentials that are compared with information stored (or retrieved) by the authentication mechanism, and if such set of credentials matches the information stored (or retrieved) by the authentication tool, the involved party receives successful access to the system, otherwise access is denied.

Examples of authentication instruments are the ones created for web applications by means of:

ASP.NET Membership
ASP.NET Identity

If you are interested in authentication processes carried out by concrete database management systems, you might find of help:

The IBM DB2 Authentication Methods For Your Server
The Microsoft SQL Server Logins and Authentication Modes
The Oracle Authentication Methods

Authentication systems are closely related to authorization components which, to put it short, take care of determining whether a specific party has permissions to perform a precise action within a given system.

A platform that assists in the development of both authentication and authorization elements pertaining to different types of application programs is:

The Java Authentication and Authorization Service (JAAS)


TSQL speedup partition by query CREATE TABLE entries(
  uuid            BINARY(16) PRIMARY KEY,
  status          VARCHAR(20),
  client_id       VARCHAR(32),
  creation_date   DATETIME,
);

status can be NEW, IN_PROGRESS, DONE

I need one entry with status != DONE for each client_id. I want maximum 500 of such entries and with oldest creation date.

I ended with query:

SELECT TOP (500) *
FROM (SELECT
          *,
          ROW_NUMBER() OVER (PARTITION BY client_id
                             ORDER BY creation_date) rn
      FROM entries
      WHERE status <> 'DONE') v
WHERE rn = 1

We have around 1000 clients and around 1 milion entries.
Currently query executes ~90 seconds on our Azure SQL database.

Azure SQL Server suggested creating following index:

CREATE NONCLUSTERED INDEX [nci_wi_entries_0A6FBEBB67742F72F54C5F86FD2C5023]
ON [dbo].[entries] ([status])
INCLUDE ([client_id], [creation_date])
WITH (ONLINE = ON)

After that, query executes around 20 seconds, but that is still too slow. I must have results in less than second.

Do you have any ideas how to optimize it (in any way, either rebuilding table or optimizing query)?

If you have a separate table Clients with the list of all 1000 client IDs, it may be faster to seek 500 times into 1M rows table instead of reading all 1M rows. This is another common variant to write a top-n-per-group query. The following question discussed it in great details: Retrieving n rows per group.

Also, use filtered index to match your query:

CREATE NONCLUSTERED INDEX [IX] ON [dbo].[entries]
(
    [client_id] ASC,
    [creation_date] ASC
)
INCLUDE
(
    [status],
    [uuid]
)
WHERE (status <> 'DONE')

The query (assuming you have Clients table):

SELECT TOP(500)
    CA.*
FROM
    dbo.Clients
    CROSS APPLY
    (
        SELECT TOP(1)
            dbo.entries.*
        FROM
            dbo.entries
        WHERE
            dbo.entries.client_id = dbo.Clients.client_id
            AND dbo.entries.status <> 'DONE'
        ORDER BY dbo.entries.creation_date
    ) AS CA
;


Make encrypted differential backup to same media I create encrypted backup as follow:

BACKUP DATABASE XXX
TO DISK = 'C:\Backup\XXX.bak'
WITH INIT, FORMAT,
     ENCRYPTION  (ALGORITHM = AES_256, SERVER CERTIFICATE = MyCertificate)

Then I try to add differential backup to same file:

BACKUP DATABASE XXX TO DISK = 'C:\Backup\XXX.bak'
WITH DIFFERENTIAL,
     ENCRYPTION  (ALGORITHM = AES_256, SERVER CERTIFICATE = MyCertificate)

but get error:

  The backup cannot be performed because 'ENCRYPTION' was requested after the media was formatted with an incompatible structure. To append to this media set, either omit 'ENCRYPTION' or create a new media set by using WITH FORMAT in your BACKUP statement. If you use WITH FORMAT on an existing media set, all its backup sets will be overwritten.

What should be done to store encrypted differential backups in a single file?

Upd:
I know how to get diff backup in another file. If it is not possible to get diff backup in the same file, where I can find documentation on this behaviour?

As Aaron noted, there's no reason to retain multiple differential backups, as you would only use the latest, and only as an optimization.

The limitation on one encrypted backup per file is in the main doc page for Encrypted Backups:

  The following are restrictions that apply to the encryption options:

  . . .

  Appending to an existing backup set option is not supported for
  encrypted backups.

Backup Encryption


How can I use all the selected values of a column, one by one, in another query? Use column values returned from the statement below:

SELECT DISTINCT(column_name) FROM Table1;

into the following query:

SELECT dif_col_1,
       dif_col_2
  FROM Table2
 WHERE key_in_table = (SELECT key_in_table
                         FROM Table3
                        WHERE UPPER(some_column) LIKE '%each_value_from_the_first_query%');

Let's start from the inner query and work our way out. You want to return rows from Table3 for which a column matches (using the LIKE operator) any column from Table1, right? I would use the EXISTS operator for that. We can reword your problem as "return all rows from table3 for which there exists at least one matching row from Table1". Here's one implementation:

SELECT key_in_table
FROM Table3
WHERE EXISTS
(
    SELECT 1
    FROM Table1
    WHERE UPPER(Table3.some_column) LIKE '%' || Table1.column_name || '%'
);

You can run that query separately so you can be satisfied that it returns the results that you're after. The final query is as simple as integrating that subquery into what you already had:

SELECT dif_col_1, dif_col_2
FROM Table2
WHERE key_in_table =
(
    SELECT key_in_table
    FROM Table3
    WHERE EXISTS
    (
        SELECT 1
        FROM Table1
        WHERE UPPER(Table3.some_column) LIKE '%' || Table1.column_name || '%'
    )
);


How to review Database schema? I am green been software engineer, my team lead give me a task in PostgreSQL database, I need to determine the relationship of the all 405 tables, but the problem is the database doesn't have any documentation, the foreign keys and primary keys naming are so different for example.

In table a the primary key is userId and the foreign key is accountId and in other table foreign key is employeeId and supervisorId.

How to approach this kind of database without documentation? is there any API for PostgreSQL to show the table relation ships?

any tutorial link or document is acceptable,

All the information you need is in the catalog, have a look at the tables:

information_schema.table_constraints
information_schema.key_column_usage
information_schema.constraint_column_usage

However, if you want something more visual have a look at:

http://schemaspy.sourceforge.net/

It will reverse engineer your schema and produce html output


How to resolve "InnoDB data dictionary has tablespace id N" I recently messsed up a migration to table-per-file for my dbs.
In the end I (thought) I dropped all dbs, deleted any left over files, and then started over from scratch, importing only dbs I needed as I went.

However, today I find loads of these in my log:

InnoDB: Error: table 'db/table'
InnoDB: in InnoDB data dictionary has tablespace id 384,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.

Going to the URL, I find near the end.

 If this occurs, try the following procedure to resolve the problem:

    Create a matching .frm file in some other database directory and copy it to the database directory where the orphan table is located.

    Issue DROP TABLE for the original table. That should successfully drop the table and InnoDB should print a warning to the error log that the .ibd file was missing.

What does "Create a matching .frm file" mean? Since it directly speaks of creating a "file", I would assume this is something like touch [file], but then it doesn't make any sense that I should copy it from one directory to another.

I tried it anyway and mysql complained about the table not existing, so that's not it.

Creating a table in a new database with the same name as the "missing" table, and copying the corresponding file was the solution.

Hopefully someone can post a better solution, but here's the hack I put together to sort this out for myself. Really ugly, but still might benefit someone in the future.

grep "InnoDB: Error: table " /var/log/mysql/error.log
  | grep 141028 # Today's date
  | grep [problematic-database]
  | sort -u
  | awk '{ print $6 }'
  | awk -F/ '{ print $2 }'
  | sed "s/'$//"
  | while read table;
  do
    echo "use temp; create table if not exists $table (random varchar(2))"
    | mysql
    cp -v temp/"$table".frm [problematic-database]/;
    echo "drop table temp.$table; drop table [problematic-database].$table"
    | mysql
  done


How to deal with unavoidable circular references I'll start of with an example database design to give you an idea what I'm currently dealing with.

I have a table which holds all the makes of vehicles and another table which holds the models for each make. This would result in the following db design:

Makes

Id
Name

Models

Id
Name
MakeId

So far it's pretty straight forward, but now comes that somewhat hard part which introduces the 'unavoidable' circular reference in my scenario. I'm introducing another table named Vehicles, which is used to configure a particular vehicle.

Vehicles

Id
MakeId
ModelId
Variant (for example 3.0 TDI, just a plain text field).

The reason I came to this design is because I have a frontend web application which allows the user to fill in a form to configure a Vehicle. Inside the form the user gets presented with two select boxes, one for the make and one for the model. In this case the models belonging to a make are dynamically loaded once a make has been selected by the user, hence the FK relationship between Models and Makes.

Possible solutions:

1) Remove the FK relationships from the Vehicles table and just store the make name and model name, but if for some reason these values change, already inserted Vehicle records won't show the updated name.

2) Store only the ModelId in Vehicles

3) Create a many-to-many table between Vehicles and Makes and Models and store the Id of the many-to-many table as the FK in the Vehicles table. This however would require the frontend to perform a lookup based on MakeId and ModelId to get the id of the many-to-many table.

Which approach is recommended? Are there better alternatives?

A reasonable solution is the second one, that is to remove the MakeId FK from Vehicles, for two reasons:

The information about the Maker of a certain Model is already contained in the Model record, so storing it inside Vehicle is redundant.
Having both attributes in Vehicle is prone to errors, since one could have inconsistent information inside a Vehicle.


SQL Server Database Not Shrinking I have an SQL Database with a lot of unused space.
However when I run
Tasks | Shrink | Database
And
Tasks | Shrink | Files
from Management studio it isn't freeing up any space.
Unfortunately the machine that it is running on is running out of hard disk space. Any ideas on why this space isn't freeing up.

Also recovery mode on DB is set to simple.

Here is the unused dialog that comes up when I go to shrink files:

I've also tried doing this via T-SQL

  DBCC SHRINKDATABASE (myDatabase, 5);

I get following response:

DBCC SHRINKDATABASE: File ID 1 of database ID 6 was skipped because the file does not have enough free space to reclaim.
DBCC SHRINKDATABASE: File ID 2 of database ID 6 was skipped because the file does not have enough free space to reclaim.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I'm going to make a wild guess and say that your database is set up to use FULL recovery.  You have never taken a log back up (let alone a full one) based on the image above.  If this is the case then you log file is growing and growing and can't clear out because you haven't taken a backup.

If you have the room take a full backup, then a log backup and try your shrink again.  Should work fine.

Worst case (and I do mean worst, particularly if this is a production database) is to set the database to SIMPLE recovery, shrink and then take a backup.  This should work even though you don't have enough space for a shrink normally.

At this point make sure you set up regular backups for your database.

You can go here for more detail.

EDIT:

Can you tell us what % free the log is vs the data file?
Also you might look at the log_reuse_wait_desc in sys.databases.

SELECT name, log_reuse_wait_desc FROM sys.databases

And last but certainly not least have you tried the TRUNCATEONLY option of shrink?

DBCC SHRINKFILE (Adventureworks2008R2_Log, TRUNCATEONLY)


Accessing the Oracle cloud with MySQL workbench I want to connect the Oracle instances with MySQL workbench.
I do not know if it's possible or not.

The correct tool to use for this is Oracle SQL Developer.

You can download it here.

A guide to setting up a connection to a cloud DB is here.


SQL.EXPIRE_TIME in sqlnet.ora I have set sql.expire_time parameter to 10 which recommends me to keep in my two node RAC database. At times, my application gets the error ORA-3135, therefore I suspect that this parameter could have some role to play here..

What exactly does this parameter do? Does it have anything to do with the error im facing?

I do not have any firewalls between my database and app server.

That is for DCD (Dead Connection Detection).

Dead Connection Detection

SQLNET.EXPIRE_TIME

If a client suddenly "disappears", e.g it's powered off, or loses network, the connection still remains open on the database server, with the corresponding session as well.

When the above parameter is set, the database probes the connections to check if they are still alive. The above parameter controls the frequency of this check (in minutes).

It is a common misconception that setting this parameter to 10 results in any dead session being cleaned up in at most 10 minutes, but that is not how it works, read the above PDF for explanation.


Different results for logically same, but textually different queries Is there any reason why the following queries would return different results?

select count(id)
from mytable
where
  status='active'
  and ((textfield1 ilike '%needle%') or (textfield2 ilike '%needle%'));

vs

select count(id)
from mytable
where
  status='active'
  and ((textfield1 || textfield2) ilike '%needle%');

When I'm running these queries, the former is returning 26 and the latter 19.

What idiotic mistake am I making?

Do you have null values? If yes, the second form will be evaluated as null.

For example:

NULL || 'needle' = NULL

This way the second form can have less values than first one.

Try this:

coalesce(textfield1 ,'') || coalesce(textfield2,'')


Join on with modified ON I am trying to create table with summary of few other tables. All solutions i came up with are very slow and clearly not optimal. Initially I tried with multitude of subqueries but that was very slow. Now I have JOIN but I am not fully grasping which join I should make.

2 tables are like following

Table 1: recent_items

ID, timestamp, Text1...TextN

Table 2: insider_trades

ID, timestamp, Text1...TextM

Where all columns but ID are not related at all and # of cols is not the same. And no IDs are not foreign keys, long story short DB has different collations and i wasn't able to unite them yet. (I didnt create the DB itself)

My best attempt with join so far is:

SELECT
    DATE(recent_items.time),
    SUM(CASE WHEN crit1 = "twitter" THEN 1 ELSE 0 END) Social,  # one that works
FROM
    recent_items
JOIN
    (SELECT crit2, crit3, DATE(filedate) AS fdate
     FROM insider_trades
    ) AS InInfo ON DATE(recent_items.time) = DATE(InInfo.fdate)
GROUP BY
    DATE(recent_items .time)

I need to do 2 things

Sum from table 1 (done)
Sum from table 2, irrelevant of ID but based on date specified by
    SELECT date(table1.time) . Ex. total value of crit2 on certain
    date or something like SUM(crit2 where crit3 = "A" and date(recent_items.time) = date(InInfo.fdate))

For 2 I tried passing user defined variable but with not much luck either

This should work if you want to join two tables by day and do counts on different conditions of the columns.

SELECT *
FROM (
SELECT
    SUM(CASE WHEN crit1 = "AAA" THEN 1 ELSE 0 END) as TheAs,
    SUM(CASE WHEN crit1 = "BBB" THEN 1 ELSE 0 END) as TheBs,
    SUM(CASE WHEN crit3 = "CCC" THEN 1 ELSE 0 END) as TheCs,
    date(time) someDay
    FROM recent_items
    GROUP BY date(time)
) as recitems
JOIN (
    SELECT
    SUM(CASE WHEN crit1 = "AAA" THEN 1 ELSE 0 END) as TheAs,
    SUM(CASE WHEN crit1 = "BBB" THEN 1 ELSE 0 END) as TheBs,
    SUM(CASE WHEN crit3 = "CCC" THEN 1 ELSE 0 END) as TheCs,
    date(time) someDay
    FROM insider_trades
    GROUP BY [timestamp]
) as InInfo ON recitems.someDay = InInfo.someDay

This may be slow depending on the size of the data that is being queried. Limiting the number of days you are trying to get data for by adding where clauses to the inner queries will help.


Backup sqlserver 2019 and restore to Sqlserver 2017 I have only 1 table in SqlServer 2019 that I want to backup and restore into SqlServer 2017 in different server.
To SqlServer 2019 I have access to server and with SSMS but to SqlServer2017 I have access only with SSMS
How can I do that?

A backup is overkill for only one table and I'm not sure what compatibility issues you can run into restoring to a lower version of SQL Server (since backups occur at the database level).
Instead you should look to just copy the data between servers. There are a multitude of ways and tools out there to help you do this, but I've always found the SQL Examiner Suite simple enough for such a task. They offer a fully functional 30 day free trial, so you can utilize it as you need.
Alternatively you can setup a Linked Server between the two instances and then can easily pull the data across the linked server to fill the table on your other instance, as needed.
Finally, you can also export the data to a flat file from your SQL Server 2019 instance, and import that flat file using the same tool in SSMS on your SQL Server 2017 instance.
Outside of those options, you can also use BCP as recommended by Bob Klimes, or you can generate scripts with the data as this article walks you through.
Here's the key steps you need, to include the data with the schema scripts when you use the Generate Scripts feature:

(On the Set Scripting Options page) Click on the 'Advanced' button.

Scroll down and select 'Schema and Data' from the 'Types of data to script' dropdown.


SQL Server Error 8632 due to over 100,000 entries in WHERE clause My problem (or at least the error message) is very similar to query processor ran out of internal resources - extremely long sql query.

My customer is working with an SQL select-query, containing a where-clause with exactly 100,000 entries.

The query is failing with error 8632 and error message

  Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.)

I find it very peculiar that this error message is thrown, exactly at 100,000 entries, so I wonder if this is a configurable value. Is this the case and in case yes, how can I increase this value to a higher one?

On MSDN, there is the proposal to re-rewrite the query, but I'd like to avoid this.

Meanwhile I've found out that the list of entries I'm talking about contains natural numbers, quite some of them seem to be sequential (something like (1,2,3,6,7,8,9,10,12,13,15,16,17,18,19,20).

This makes the SQL where-clause something like:

where entry in (1,2,3,6,7,8,9,10,12,13,15,16,17,18,19,20)

I could transform this into:

where (entry between 1 and 3) OR
      (entry between 6 and 10) OR
      (entry between 12 and 13) OR
      (entry between 15 and 20)

Can this be shortened by:

where entry in (1,...,3,6,...,10,12,13,15,...,20)

...or something similar? (I know it's a long shot, but it would make software updates easier and more readable)

For your information: the data in the where-clause are the result of a calculation, done on another table: first the entries of that table are read and filtered at the beginning, then some extra processing is done (which is impossible to do using SQL), the result of that extra processing is more filtering and the result of that is used in the where-clause. As it was impossible to write the complete filtering in SQL, the mentioned method has been used. Obviously the content of the where-clause might change at every processing, hence the need of a dynamic solution.

To search for more than 100,000 values, put them in a temp table instead, one row per value that you're searching for. Then, join your query to that temp table for filtering.

Something with more than 100,000 values isn't a parameter - it's a table. Rather than thinking about raising the limit, consider Swart's Ten Percent Rule: if you're approaching 10% of a SQL Server limit, you're probably going to have a bad time.


Double FOREIGN KEY, i.e. how to create a table with two different FOREIGN KEYs that reference a single PRIMARY KEY in another table - DDL I have an issue with my SQL syntax when creating a table with a set of Foreign Key (Orig_Dept_ID & New_Dept_ID) that references Dept_ID in the Department Table.

Below is my sql syntax:

CREATE TABLE DEPT_TRANSIT
    (Orig_Dept_ID int,
    New_Dept_ID int,
    CONSTRAINT department_fk foreign key (Orig_Dept_ID, New_Dept_ID)
             REFERENCES Department(Dept_ID)
    );

Could someone please advise on how I should go about doing this?

Thanks alot!

There are two possibilities here:

1) is that it is a case of 2 fields in the same table being FOREIGN KEYs pointing to the same field in the parent table.
2) is that this is an example of an Associative Entity. These are also called joining, linking or many-to-many tables.

1. Double FOREIGN KEY.

This is quite simple to resolve.

You have two tables - department and dept_transit (see fiddle):

CREATE TABLE department
(
  department_id INTEGER NOT NULL AUTO_INCREMENT,
  -- other fields,
  -- other fields,
  CONSTRAINT department_pk PRIMARY KEY (department_id)
);

CREATE TABLE dept_transit
(
  dt_original_dept_id INTEGER NOT NULL,
  dt_new_dept_id      INTEGER NOT NULL,
  CONSTRAINT dt_original_dept_id_fk FOREIGN KEY (dt_original_dept_id) REFERENCES department (department_id),
  CONSTRAINT dt_new_dept_id_fk      FOREIGN KEY (dt_new_dept_id)      REFERENCES department (department_id)
  -- optional here CONSTRAINT dept_transit_pk PRIMARY KEY (dt_original_dept_id, dt_new_dept_id)
);

A few things to notice:

my solution uses long_variable_names! Many years of experience have taught me that you are far better off making your variable names (table_names, field_names and PKs, FKs &c.) as long and as meaningful as necessary. This makes life much easier for debugging and more than outweighs the trouble of the extra typing! Also, note the singular name for department! YMMV :-)
I always try to keep fieldnames unique within a schema (e.g. new_dept_id, dt_new_dept_id) - this helps greatly with logic when joining tables. I make exceptions for NATURAL KEYs, but not surrogate ones as is the case here.
My own personal preference is for SQL keywords to be in upper-case and for the variables (tables, fields &c.) to be in lower_snake-case. This allows the eye to readily pick out different elements when debugging &c. If you look up SQL coding standards on the web, you will find lots of debate about this (i.e. here). The main thing is to pick a standard (for yourself, company, whatever) and STICK TO IT.
Not entirely sure of requirements, but I think that (for MySQL) a TRIGGER will be required for any requirement that original_dept_id and new_dept_id be different (which makes sense - I think).
Unfortunately, MySQL still (Amazingly) doesn't allow for CHECK CONSTRAINTs. See the MUCH nicer solution possible with PostgreSQL here. A good reason to switch to the far superior PostgreSQL if you're not too invested in MySQL! :-)

2. Joining Table.

If this is the case, I would do something like the following (fiddle here):

CREATE TABLE original_dept
(
  original_dept_id INTEGER NOT NULL AUTO_INCREMENT,
  -- other fields,
  -- other fields,
  CONSTRAINT original_dept_pk PRIMARY KEY (original_dept_id)
);

CREATE TABLE new_dept
(
  new_dept_id INTEGER NOT NULL AUTO_INCREMENT,
  -- other fields,
  -- other fields,
  CONSTRAINT new_dept_pk PRIMARY KEY (new_dept_id)
);

CREATE TABLE dept_transit
(
  dt_original_dept_id INTEGER NOT NULL,
  dt_new_dept_id      INTEGER NOT NULL,
  CONSTRAINT dt_original_dept_id_fk FOREIGN KEY (dt_original_dept_id) REFERENCES original_dept (original_dept_id),
  CONSTRAINT dt_new_dept_id_fk      FOREIGN KEY (dt_new_dept_id)      REFERENCES new_dept (new_dept_id),
  CONSTRAINT dept_transit_pk PRIMARY KEY (dt_original_dept_id, dt_new_dept_id)
);

The PRIMARY KEY of the joining table is a natural one, make up of the PKs of both of the entities that comprise it.


How can I pivot this data using a cte or the pivot syntax? Let's say I have these two tables in an MSSQL-Database:

Is there any way to create a query that result in a table like this?

There is no case where there are multiple Leader/Assistant in the TEAMS-Table. Both of these only occur once per project.

There are multiple ways you could do this, including those suggested by the question title, but I find a conditional MAX() the simplest to grok (and most likely to only ever access any table once). You want one row per idproject/nameproject, and then you need to find the team row that has the specific task associated with it. You could just as easily use MIN() instead of MAX(), if you trust your data it will be the same, if you don't trust your data that will just determine how to break ties.

SELECT
  p.idproject,
  p.nameproject,
  leader    = MAX(CASE t.taskTeam WHEN 'leader'    THEN t.nameTeam END),
  assistant = MAX(CASE t.taskTeam WHEN 'assistant' THEN t.nameTeam END)
FROM dbo.PROJECT AS p
INNER JOIN dbo.TEAMS AS t -- plural but project is not?
ON p.idproject = t.idproject
WHERE t.taskTeam IN ('leader', 'assistant')
GROUP BY p.idproject, p.nameproject
ORDER BY p.idproject;

Note: I assumed assistent was a typo, and that taskTeam is varchar.


How to search for an ID and its descendants in this query? I have the following tables:

Project                Unit
-------------          ---------------
ID  | IDUnit           ID   | IdParent

I have to select all projects that have the IDUnit equal to X and all its descendants.

I am doing the following:

SELECT
    p.*
FROM Project p
WHERE
(

  P_Id    IS NULL

  OR (
    p.IDUnit IN (SELECT ID FROM Unit
    START WITH ID =   P_Id
    CONNECT BY PRIOR ID = IdParent)
  )
)

It works, but the table Unit has too many rows, and the query takes over 5 seconds to complete when there is value on P_Id.

How can I optimize this query?

How about something like this:

select id, idunit
from (select id as idunit from unit start with id=p_id connect by prior id = idparent)
     join project using(idunit)
where p_id is not null
union all
select id, idunit from project where p_id is null


Where can I download older versions of Oracle Instantclient? Version 10.2 is linked from here, but I need 10.1.  Any clues where to get older copies of the instantclient packages?

http://www.oracle.com/technetwork/database/features/instant-client/index.html

Here's a link that lets you download 10.1.0.5:

http://www.oracle.com/technetwork/topics/winsoft-085727.html

This particular link is for 32-bit Windows.  Hope this helps.


is Master-Master replication ok for me? I have two separated locations connected by a not too reliable VPN. I have a common system that depends on MYSQL that read/write tables.
Will master-master replication keep both locations in sync?
I don't care that tables might not look exactly the same in time on both servers (for example, rows on both masters when VPN fails, might get a different order after both locations write transactions...)

Note: Not doing auto-increment / offset settings

I have a third party app that is using a master-master MySQL system and if  your network/communication is not good you may have some issue with your replication.

Types of errors you may encounter is 1062 (duplicate key).

Right now to avoid this error we put it in the slave_skip_errors in themy.cnf file.

Also if your network/communication is not good is not good, your instances may keep and failing back and forth.

It can work but you will need a lot of fine tuning and monitoring.


SQL Server cursor and foreachdb loop I want to create a cursor (without creating a temp table) to get all orphan users in my SQL Server instances. Here is what I've come up with:

Declare @Orphan_users table (username sysname)
DECLARE @orphan_users_cursor CURSOR
set @orphan_users_cursor = cursor
FOR
select * from (
insert into @Orphan_users
set EXEC SP_MSFOREACHDB' USE [?]
SELECT dp.name
FROM sys.database_principals AS dp
LEFT JOIN sys.server_principals AS sp
    ON dp.SID = sp.SID
WHERE sp.SID IS NULL
    AND authentication_type_desc = ''INSTANCE'''--)

OPEN @orphan_users_cursor
FETCH NEXT FROM @orphan_users_cursor INTO  @Orphan_users

I think your table should also include the database name, otherwise once you identify a user that is abandoned in some database on your system, how are you going to find them?

Anyway here is a way to do it by building a single query rather than executing a query database by database (and relying on a system procedure that is undocumented, unsupported, and has community replacements because it's so buggy). It isn't better or faster, just easier to understand and troubleshoot, IMHO.

DECLARE @AbandonedUsers TABLE(db sysname, username sysname);

DECLARE @sql  nvarchar(max) = N'SELECT db = NULL, username = NULL WHERE 1 = 0',
        @base nvarchar(max) = N'
UNION ALL SELECT PARSENAME(N''$qn$'',1), dp.name COLLATE SQL_Latin1_General_CP1_CI_AS
  FROM $qn$.sys.database_principals AS dp
  WHERE dp.authentication_type = 1
    AND NOT EXISTS (SELECT 1 FROM sys.server_principals AS sp
      WHERE sp.SID = dp.SID)';

SELECT @sql += REPLACE(@base,N'$qn$',QUOTENAME(name))
  FROM sys.databases
  WHERE state = 0; -- might also add AND database_id > 4 to avoid common system DBs

INSERT @AbandonedUsers(db, username) EXEC sys.sp_executesql @sql;

SELECT db, username FROM @AbandonedUsers;


SQL Server: Interplay of personal login and group membership I created a login for a user using his windows user then I created a login for an AD group in which the same user is a member. I did NOT give any access to the personal login any access to the DB and did not add this login to any server or DB fixed roles. I gave the AD group access to the DB. I have then DISABLED the 1st login (the one using the Windows user) and you know what? The user lost access to the DB!!! When I re-enabled the login (the one using the Windows user) the user again had access to the DB. From this observation I conclude: .) For the authentication when connection to the server, SQL Server either gives precedence to the personal login over the AD group login OR it simply scans the syslogins table to find the first login (which is either the user's personal login or an AD group in which the user is a member) .) After the successful authentication on the instance level, the resulting permissions on the DB is the cumulative permissions of the personal login + of all the AD groups the user is member of.

The first part of this observation does not seem right to me at all. Is this a bug?

Has anyone observed the same behaviour?

TO CLARIFY: The personal login was NOT mapped to the DB at all. The login based on the AD group WAS mapped to the DB and had db_datareader fixed DB role assigned to the user. Still, when I disabled the personal login the user lost all access to the DB. This is IMHO an important point. I did not assign DENY permission to the personal login, I simply disabled it. Therefore my expectation was that the user would continue to have access to the DB through his group membership. But that wasn't the case and that doesn't seem right to me.

SQL Server will deny access to a login that has been disabled. This is by design. If it is your intention to allow the person access through the group login, you should simply remove the login that is linked to the Windows account.

SQL Server limits principals' rights such that any rights that have been explicitly denied override rights that have been explicitly or implicitly granted.  For instance, if a Windows Account has a login, and is also a member of an Active Directory group that also has a login, the set of rights granted at connection time actually reflect the rights for both the Account login and the Group login.  If the group login permits access to a specific resource, but the account login denies access to that resource, then access to that resource is denied.

You may be interested in seeing the SQL Server Database Engine Permissions Poster.


How to Extract SubString between two positions I have a text column (emailbody) in a table (notification) which keeps a record of the HTML that went out for a particular email. Somewhere inside that HTML file, there is a line of text that contains the following line:
Delayed ${shipmentId} for Carrier

Where ${shipmentId} represents the specific Id that was generated for that particular record. Example: 12345
Basically I want to query the column in the table for all the shipmentId values, which unfortunately will involve getting the string positions between Delayed and  for Carrier. I understand this will likely involve using one of Postgres' regular expression functions but I'm not quite sure how to do it, or if there's something a little simpler.

The SUBSTRING function has regex support, so the following would work:

SELECT SUBSTRING(emailbody FROM 'Delayed (.*?) for Carrier') FROM notification

The so-called capturing group () is to make sure you capture only the shipment ID and not the entire string "Delayed 12345 for Carrier". The ? makes the capture non-greedy, to make sure the first 'for Carrier' is matched.