CROSS JOIN with table that has no row In definition;

SELECT *
from T1, T2

Cross Join: If T1 has N rows, T2 has M rows, the result set will have N x M rows.

If T2 has no row, then result of the JOIN has no row also.

However, I want N rows (from T1), what can I do?

Any suggestion?

Note: I want cartesian product, there is no shared column between the tables.

You can't use CROSS JOIN if any of both sets has no rows for the output you want. You can simulate this behaviour with a FULL JOIN with an always true condition.

SELECT
    *
FROM
    T1
    FULL JOIN T2 ON true


How do I model data from arbitrary CSV's in a database? I am setting up a web interface for data management.  Users upload CSV or similarly structured files, and I want to store these in the database so they can do manipulations on them -- filtering, sorting, graphing, etc.

I do not know how to model this properly in a database.  I have several ideas, but none seem to be the right way to do it.

Make a new table for each uploaded CSV. This would mean each column could be appropriately typed (integers, strings, dates, etc.) and each record would trivially correspond to a line in the CSV file.  This seems like a natural conceptualization of the problem -- but would performance become an issue if I had to make a new table for each uploaded file?
Make a table where each record represents a dataset (CSV) and have other tables where data points have the id their dataset in their record. This would mean that all the data from a given dataset is spread across different tables and there would be lots of redundancy (since each data point would store the id of the dataset). However, it would mean tables would not have to be created per-dataset.
Other variations on 2. Most of my other thoughts were variations on number 2 with various amounts of indirection.

My question is essentially "How do I model this properly?", that is, with the ability to scale reasonably.

Most of the data will be scientific, so how do I deal with many data sets of sizes varying from trivial (say, 10 columns and 100 rows) to massive (hundreds of columns and thousands/millions of rows)?

tl;dr: How do I model arbitrary data from an arbitrary number of well-formed CSVs in a database, and would a new table for each CSV perform acceptably?

The question is so wide open that it is hard to say much.

And why are you using a database at all? Excel does everything you've described so far.

Seriously though, avoid making an inner system in your database that can store any kind of data. You have one already, called a DATABASE. Make code that creates the tables you need. What's so bad about that? I say option 1.

You know, if you want infinite flexibility how about a square-mile white board?


Unknown column error with an index I created a simple index associated with a timestamp column

CREATE TABLE `data` (
    `id` INT(11) UNSIGNED NOT NULL,
    `ticker` VARCHAR(16) NOT NULL COLLATE 'utf8_unicode_ci',
    `comment` TEXT NOT NULL COLLATE 'utf8_unicode_ci',
    `link` VARCHAR(256) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
    `comment_hash` CHAR(32) NOT NULL COLLATE 'utf8_unicode_ci',
    `time` DATETIME NOT NULL,
    `feed` VARCHAR(16) NOT NULL COLLATE 'utf8_unicode_ci',
    `source` INT(11) NOT NULL,
    `active` TINYINT(1) NOT NULL DEFAULT '1',
    `score` VARCHAR(20) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
    `dttm` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`ticker`, `comment_hash`),
    INDEX `time_index1` (`time`)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB;

However when I try to do anything with the index, it gives me SQL Error (1054): Unknown column 'time_index1' in 'where clause'

Sample queries:

select *
from data
where time_index1 = '2013-10-01 17:18:06'

or

select *
from data
order by time_index1

Your SELECT statement is referencing the name of the index instead of the indexed field.

Use this instead:

select *
from data
where time = '2013-10-01 17:18:06'


force PostgreSQL into recovery mode I have a somewhat strange question.

Is there a way to manually force PostgreSQL into recovery mode?
I've not been able to find anything in the documentation.

I've been working with a script monitoring a PostgreSQL database and need to do some testing.

You should be able to use pg_standby.

I cannot gurantee it, though, as I haven't tried it.


How to use DAY/WEEK/MONTH/YEAR constant as parameter in stored procedure? Assume I have a table looks like this:

Scores {
    Id uniqueidentifier,
    ScoredAt datetime,
    Value int
}

Basically I want to create a stored procedure that works similar to this DATEDIFF(DAY, @day, GETDATE()) which can use DAY/WEEK... as parameter. This is what i did:

CREATE PROCEDURE GetHighScores
    @scope int --- <<== THIS GUY HERE
AS
BEGIN
    SELECT *
    FROM Honor.Scores
    WHERE DATEDIFF(@scope, Honor.Scores.ScoredAt, GETDATE()) = 0
END
GO

What do I have to put into GetHighScores parameter so that I can do this:

EXEC GetHighScores(MONTH)

As far as I know, MONTH/WEEK/DAY.. those are not actually a value but more of a tricky macro. But that guy DATEDIFF can use it, why couldn't I?

Anyone have any idea to get this done?

This assumes that your week starts on a Sunday, and that you only have data from the past. If you need to collect data for this week/month/year/today and exclude data from the future, you need to calculate a start and end range instead of a single cutoff.

CREATE PROCEDURE dbo.GetHighScores -- always use schema prefix!
  @scope VARCHAR(5) -- why an int?
AS
BEGIN
  SET NOCOUNT ON; -- always use this!

  DECLARE @d DATETIME, @cutoff DATETIME;
  SET @d = DATEDIFF(DAY, 0, GETDATE());

  SET @cutoff = DATEADD(DAY, 1-CASE UPPER (@scope)
    WHEN 'MONTH' THEN DAY(@d)
    WHEN 'WEEK'  THEN DATEPART(WEEKDAY, @d)
    WHEN 'YEAR'  THEN DATEPART(DAYOFYEAR, @d)
    WHEN 'DAY'   THEN 1 END, @d);

  SELECT columns -- never use SELECT * in production code!
    FROM Honor.Scores
    WHERE ScoredAt >= @cutoff; -- now you have a chance to use an index
END
GO

Some further reading you may find useful:

Bad habits to kick : avoiding the schema prefix
My stored procedure "best practices" checklist
Bad habits to kick : mis-handling date / range queries
Bad habits to kick : using SELECT * / omitting the column list


How can I represent a relationship of cardinality many to many? I'm trying to design a database for the information shown in the table
below:

In this regard, let's make the following assumptions:

Each Student may take many Courses
Each Course may have many Students
Student Numbers, Course Numbers and Advisor Names are unique
Student Names and Course Descriptions are not unique
Each Student has one Advisor
Each Advisor may advise many Students
Each Advisor has one Office
A Student can visit with his Advisor many times

My current approach

So far, I have come up with the following diagramatic representation:

The question

Being that this contains a many-to-many (M:N) relationship, I am confused as to what the association class (or associative entity type) should be.

What would the correct way to represent this relationship be?

You could have a Course table which would just contain the courses and descriptions.

As stated in the comments, you could then have a StudentCourse table which had either a surrogate PK, with foreign keys to Student and Course or a composite PK (StudentNumber and CourseNumber) with the Grade for the student held in it.

Similarly, you could have an Office table and link the Office back to the Advisor.  This would allow multiple advisors to have the same office without having to hold the office name multiple times (what if the office name changed for example?).

As also mentioned using the Advisor name as the primary key might not be best, their name could change; you might be better to use a Surrogate key (integer perhaps?) for that.


Who created a procedure? (SQL Server 2008 R2 ) Is there a way to list who created certain procure in SQL Server 2008?

In this SO link there are some answers but from 6yrs ago.

I know it's a best pratice to write your name and date, and then start to create the procedure, but I don't see this where I work.

If there is no way to do this, can this task be made with triggers?

And a bonus question. Is this the job, of a dba? to know who created it?

Thank you very much.

Yes there is a way

The table to store the results of the trigger

USE [SOME_DATABASE]
GO

CREATE TABLE [dbo].[ddl_objects_log](
    [date] [datetime] NULL DEFAULT (getdate()),
    [login_name] [nvarchar](128) NULL,
    [nt_user_name] [nvarchar](128) NULL,
    [program_name] [nvarchar](128) NULL,
    [host_name] [nvarchar](128) NULL,
    [text] [xml] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

The trigger

USE [SOME_DATABASE]
GO

/****** Object:  DdlTrigger [ddl_db_trigger]    Script Date: 22/01/2015 13:41:38 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [ddl_db_trigger]
ON DATABASE
FOR CREATE_FUNCTION,
    CREATE_PROCEDURE,
    CREATE_TABLE,
    CREATE_TRIGGER,
    CREATE_VIEW,
    ALTER_FUNCTION,
    ALTER_PROCEDURE,
    ALTER_TABLE,
    ALTER_TRIGGER,
    ALTER_VIEW,
    DROP_FUNCTION,
    DROP_PROCEDURE,
    DROP_TABLE,
    DROP_TRIGGER,
    DROP_VIEW,
    CREATE_INDEX,
    ALTER_INDEX,
    DROP_INDEX
AS
BEGIN
set nocount ON
    insert into ddl_objects_log(login_name, nt_user_name, program_name, host_name, text)
    select login_name, nt_user_name, program_name, host_name, EVENTDATA() from sys.dm_exec_sessions WITH(NOLOCK) where session_id=@@SPID
set nocount OFF
END

GO

ENABLE TRIGGER [ddl_db_trigger] ON DATABASE
GO

The Query to Audit the results of the trigger

USE [SOME_DATABASE]
GO

SELECT top 10
REPLACE(CONVERT(VARCHAR(250), text.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' ') as modify_datetime,
CONVERT(VARCHAR(215), text.query('data(/EVENT_INSTANCE/EventType)')) as event_type,
CONVERT(VARCHAR(225), text.query('data(/EVENT_INSTANCE/ServerName)')) as server_name,
CONVERT(VARCHAR(225), text.query('data(/EVENT_INSTANCE/DatabaseName)')) as database_name,
CONVERT(VARCHAR(225), text.query('data(/EVENT_INSTANCE/ObjectType)')) as object_type,
CONVERT(VARCHAR(225), text.query('data(/EVENT_INSTANCE/ObjectName)')) as object_name,
CONVERT(VARCHAR(215), text.query('data(/EVENT_INSTANCE/UserName)')) as user_name,
Login_name,
CONVERT(VARCHAR(MAX), text.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)')) as command_text
FROM [SOME_DATABASE].[dbo].[ddl_objects_log]
where CONVERT(VARCHAR(225), text.query('data(/EVENT_INSTANCE/ObjectName)')) like '%SOME_STORED_PROCEDURE%'
order by date desc
GO

Another DBA might have a different opinion, but I consider having this information readily available as important for a DBA.


Problem using pg_dump in Windows 7 I am trying to copy a remote database onto my local machine using the following command

C:\Program Files\PostgreSQL\9.1\bin>pg_dump -h remote_server -U remote_user_name -c database_name | psql -a -h localhost -d local_database -U local_user_name

While doing so, it encounters an error saying data for the column not found.

Based on information from this thread I would guess that presence of characters like new line, ellipsis, right arrow in character varying column just before the column on which it errors out could be one of the reason.

So what could I do to circumvent this problem? One option is I rewrite that column, but there could many such data rows and I am not fond of this band aid type solution. I am a database noob so not sure which other documentations should I look into.

Thanks

You are using plain text dump and that's why it break on those characters.
Try with custom format and use pg_restore for restoring the database.

check this tutorial

Be aware that you need to copy also user rights or recreate them on new machine related with the database.


Eliminate filter operator before columnstore index scan operator I have a large fact table with millions of rows called MyLargeFactTable,
and its a clustered column store table.
There is a composite primary key constraint on it there as well
(customer_id,location_id,order_date columns).
I also have a temp table #my_keys_to_filter_MyLargeFactTable,
with the very same 3 columns,
and it contains few thousand UNIQUE combination of these 3 key values.
The following query gives me back the desired result set
...
FROM #my_keys_to_filter_MyLargeFactTable AS t
JOIN dbo.MyLargeFactTable AS m
ON m.customer_id = t.customer_id
AND m.location_id = t.location_id
AND m.order_date = t.order_date

but i notice that the Index Scan Operator on the fact table returns more rows than it should (about a million) and feed it into a Filter operator, which further reduce the result set to the desired few thousand rows.

Index Scan operator reads way to much rows (they quite wide rows) increasing IO, and significantly slows down the whole query.
Are my parameters not sargable?
How could I remove the Filter operator and somehow force the Index Scan operator to read only the few thousand rows?
Table definitions:
create table #my_keys_to_filter_MyLargeFactTable
(
customer_id varchar(96) not null,
location_id varchar(96) not null,
order_date date not null,
primary key clustered (customer_id,location_id,order_date)
)

create table MyLargeFactTable
(
customer_id varchar(96) not null,
location_id varchar(96) not null,
order_date date not null,
...
lot of wide decimal typed columns, and even large varchars
...
PRIMARY KEY NONCLUSTERED  (customer_id,location_id,order_date),
INDEX cci CLUSTERED COLUMNSTORE
)

How could I remove the Filter operator and somehow force the Index Scan operator to read only the few thousand rows?

The Filter operator is applying the bitmap built on the join columns at the hash join.
Of the three join predicates, only order_date has a data type that is supported for bitmap pushdown to the column store scan. If you look at the Predicate on the scan you should see this as something like:
PROBE([Opt_Bitmap1005],[dbo].[MyLargeFactTable].[order_date])

The remaining join predicates are strings and so will appear at the Filter as part of the full bitmap test:
PROBE([Opt_Bitmap1005],
    [dbo].[MyLargeFactTable].[customer_id],
    [dbo].[MyLargeFactTable].[location_id],
    [dbo].[MyLargeFactTable].[order_date])

Pushing (parts of) the bitmap test down into the column store scan is an optimization that is only available for data types that can fit in 64 bits (like date in your example). Note join bitmap pushdown is different from string predicate pushdown (e.g. pushing customer_id LIKE '%XYZ%').
There are several ways you could look to work around this limitation. Redesigning the schema such that long strings are moved to a dimension table and referenced using an integer key is one option.
Somewhat less intrusively, you might be able to add an integer checksum to the column store (sadly not as a persisted computed column) and temporary table, then add that into the join - e.g an integer computed from CHECKSUM(customer_id, location_id, order_date) or the like.
There would still be a Filter, but the bitmap would include the checksum column, which could be pushed into the scan. This ought to significantly reduce the number of rows passed into the Filter.


First, you need to make your query SARGable. Fortunately, in your case, that is simple.

SELECT date_provided as d,available as a
FROM parking_data
WHERE code = 'E03'
AND date_provided > NOW() - INTERVAL '20' HOUR

Your version of the query required the optimizer to add 20 hours to every record's date_provided, and then compare each to the value provided by NOW(). This new version of your query modifies NOW() by 20 hours, and so can use an index to find exactly where the records start that have that timestamp.

If your query is still too slow, you can add a composite index.

ALTER TABLE parking_data
ADD INDEX (`code`,`date_provided`)

This should yield extremely quick results.

You can also drop the single column index on code if you add this index, as that single column index will be redundant at that point.


Is using a relational DB for storing votes and (accessed resources) overkill? I am writing a forum component for a site and would like to keep track of what topics/questions a user accesses so that other topics/questions can be recommended based on said users previous choices.

I'd imagine one individual data/row is not very important, where it'd absolutely necessitate ACID properties on every single entry; not to mention when the user-base grows large(on the order of millions, or tens of millions), this data set will surely explode. Is this then a case for using say, MongoDB? What is the advisable general approach for something like this or votes?

In general, clustered-NoSQL databases offer you better horizontal scalability. So, as you scale you can get higher capacity (both memory & compute power) by simply adding new nodes. So, it will be a good insurance for the future.

When it comes to ACID properties, NoSQL databases offer flexibility. So, it boils down to how much ACIDness you want and how much performance you are willing to give up for it. Seems that you do not need very high level of ACID. I am sure you can get good performance in lieu of that.

PS: I do not want to comment specifically about MongoDB. You should do your research and choose the NoSQL db that fits your performance & functional needs.


How would I model the Twitter lists functionality? I'm working on an application which would mimic the Twitter Lists functionality in which someone can follow someone else. Each user can also create objects which can be followed by users. The Twitter Lists functionality is the best example of the functionality I am wanting to create.

What would this database model look like? I was planning to use Parse.com, but would be open to any representation of the domain model.

Edit: I'll try to add to why I'm asking.
Right now I have this structure (only showing the relevant relationship fields):

User
- username
- activities : Relation<Activity>
Activity
- name
- owner : Pointer<User>
- followers : Relation<User>
Follow
- from : Pointer<User>
- to : Pointer<User>
Subscription
- from : Pointer<User>
- to : Pointer<Activity>
ActivityPost
- activity : Pointer<Activity>
- from : Pointer<User>
- to : Relation<User>

I read through the Parse Anypic tutorial found here: Anypic tutorial. They did something strange to me by combining three relationships into a single table. This makes me think I've perhaps gone overboard with my join tables.

I'll try to explain why they merge all relationships into one table.

Whether a users follows, likes or comments a photo all these activities are somewhat similar they involve a user and photo. Note that the comment also requires some text.

So instead of having 3 tables like your data model they went with one.

I don't like that approach though because it merges data that may is similar in its appearance but differs in meaning. Also the comments require extra data (content) that is empty for likes and follows. Having the type described as a string as in their example is also bad (better make an extra table).

A database model would like this for your problem

User(**UserID**, Name, ....)
Post(**PostID**, UserID[FK]Data, Timestamp, ...)
Follow_Users(**UserID[FK]**, **FollowingUserID[FK]**)
Follow_Posts(**UserID[FK]**, **PostID[FK]**)

However this is a database model and not a Data model.
A Data model that is kept in memory within some application will be different because it features pointers to other objects and arrays...


MySQL groupby time time difference I have a data like this

id  timestamp       ticket_id
2   6/16/16 8:14    1466089815
10  6/27/16 7:15    1466089815
3   6/16/16 8:14    1466089816
11  6/27/16 7:15    1466089816
13  6/27/16 7:50    1466089816

I am trying to find the time taken by tiket_id. Is there a simple function which can be used to a groupby and take a time difference.

Here I am trying to find the time taken by the ticket_id.
Eg:

ticket_id    time_taken
1466089816   10.93

Appreciate the support

Best
Jagan

If you want to find the date/time difference between the first and the last timestamp of each ticket, there are several functions that can help you:

select
    ticket_id,
    timediff(max(timestamp), min(timestamp))              as diff,
    timestampdiff(hour, min(timestamp), max(timestamp))   as diff_in_hours,
    timestampdiff(minute, min(timestamp), max(timestamp)) as diff_in_minutes,
    timestampdiff(second, min(timestamp), max(timestamp)) as diff_in_seconds
from tickets
group by ticket_id ;

Test in dbfiddle.uk:

select ticket_id,
       timediff(max(timestamp), min(timestamp)) as diff,
       timestampdiff(hour, min(timestamp), max(timestamp)) as diff_in_hours,
       timestampdiff(minute, min(timestamp), max(timestamp)) as diff_in_minutes,
       timestampdiff(second, min(timestamp), max(timestamp)) as diff_in_seconds
from tickets
group by ticket_id ;

 ticket_id | diff      | diff_in_hours | diff_in_minutes | diff_in_seconds
---------: | :-------- | ------------: | --------------: | --------------:
1466089815 | 263:01:00 |           263 |           15781 |          946860
1466089816 | 263:36:00 |           263 |           15816 |          948960


Cannot connect remotely to sql server 2012 instance using SSMS I have installed sql server 2012 Express on a local network machine, then added a new login: "sqlUser" mapped to "database1", and configured the instance to accept the remote access with both Windows and SQL Server authentication.

In the configuration manager -> SQL Server Network configuration -> Protocols for SQLEXPRESS -> TCP/IP properties, I have configured the IP to use TCP dynamic port: 12345 and TCP static ports is left blank, the IP2 have the static local IP address of the local network.

In the firewall I have added a new rule to allow the sql server program (exe) to accept remote connection.

Locally I can run SSMS and open database1 with both authentications using sqlUser account.

ping from the internet is successful. http site in the same sql server machine is reachable.

But when I want to access sql server instance from an internet machine using SSMS and sql server authentication:

Server:   123.123.123.123\SQLEXPRESS:12345
Login:    sqlUser
Password: pass123

I get the following error:

  Cannot connect to 123.123.123.123\SQLEXPRESS.

  A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

Any suggestion please ?

You should not need to specify the port for a named instance (that's kind of the point of the name - and since it's dynamic you won't want to hard-code a port into your connection strings etc. unless you want to change your config files every time the port changes). Try connecting to:

123.123.123.123\SQLEXPRESS

Also make sure that your server is exposed to the Internet on that port (firewalls may be preventing this) and that the SQL Browser service is running.

PS Ping means nothing. You need to try to telnet to the port that SQL Server is exposed on.


Is there's another function to optimize this query in MySQL? I want to use a function that returns for me the same result in one line is that's possible??

MIN(
    CASE
       WHEN (data.pro LIKE '1 %'   )  THEN 1
       WHEN (data.pro LIKE '2 %'   )  THEN 2
       WHEN (data.pro LIKE '3 %'   )  THEN 3
       WHEN (data.pro LIKE '4 %'   )  THEN 4
       WHEN (data.pro LIKE '5 %'   )  THEN 5
       WHEN (data.pro LIKE '6 %'   )  THEN 6
    END) as products

Many thanks for any help.

You can use a regular expression to match '1 ' to '6 ' at the beginning of the string:

data.pro rlike '^([1-6] )'

Now put this in a CASE:

case when data.pro rlike '^([1-6] )'
     then cast(substring(data.pro from 1 for 1) as unsigned)
end

See the result in MySQL 5.5 on SQLFiddle.


Is naming an index required? I am a little bit confused about naming an index if it's required or not. In stackoverflow and here, I saw adding an index like this: (through coding)

    ADD INDEX index_name (column_name, ...)
    //whether a per-column or multiple-column index

But, when I tried to add an index using phpMyadmin interface, I got this:

As you can see there is no name while adding the index here, so my problem here is naming an index required? If so, does the image above gives a default name for an index if nothing is given by an administrator? And what it's default name?

Using the ALTER TABLE .. ADD INDEX there is no requirement to add a name.

If you look at the MySQL documentation for ALTER TABLE you can see that the name parameter is optional (between square brackets), just like the type and options:

  | ADD {INDEX|KEY} [index_name]
          [index_type] (index_col_name,...) [index_option] ...

When you add an index without specifying a name MySQL will generate a name based on the name of the first field in the index. If multiple indexes start with the same field the index name will be suffixed by a sequential number.

For example running this code:

CREATE TABLE `index_demo` (
  `IdField` int(11) DEFAULT NULL,
  `DateField` date DEFAULT NULL,
  `GroupField` char(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `index_demo` ADD INDEX (`IdField`, `DateField`);

ALTER TABLE `index_demo` ADD INDEX (`IdField`, `GroupField`);

Will result in a table with 2 indexes, IdField and IdField_2 as evidenced by the resulting create table statements:

CREATE TABLE `index_demo` (
  `IdField` int(11) DEFAULT NULL,
  `DateField` date DEFAULT NULL,
  `GroupField` char(1) DEFAULT NULL,
  KEY `IdField` (`IdField`,`DateField`),
  KEY `IdField_2` (`IdField`,`GroupField`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Please note, the name parameter is only optional when using the ALTER TABLE syntax and not the CREATE INDEX syntax as again documented in the documentation (notice the lack of square brackets)

  CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name

I would however consider it a good practice to still name your indexes instead of relying on the default naming scheme.


How to report on sparse areas of sparse fact table A source system tracks student attendance for a school district by reporting absence events. Attendance on any particular day can be determined by examining three datasets: school calendar, student enrollment, and absence.

On any given school day, the number of enrolled students in attendance is usually much larger than the number that are absent, so this approach reduces the number of records stored to track attendance significantly.

I am trying to determine the proper way to represent daily attendance in a dimensional model. The most obvious way is to create a factless table with a grain per school day per student, and an attendance dimension that has values for both attendance and absence reasons. This is quite straightforward to work with OLAP, but the downside is the size of the fact table.

For example, for 30,000 students and 188 school days means that there are approximately 0.5 million records per year (if this doesn't seem large enough to be an issue, then consider an example in which attendance must be reported on per period rather than per day). Contrast this to a fact table that records only absences and the number is considerably smaller. However, if I do this, then I am not sure how to build cubes that aggregate daily attendance facts.

The specific OLAP technology being used is SQL Server Analysis Services 2008 R2. Any thoughts?

This is a common design pattern for HR systems, so there's any amount of precedent for this type of approach.

You will find that the format used by SSAS stores this type of data quite efficiently, so data volumes won't be a significant problem.  I've seen this done on a HR cube with a row count around 300 million on fairly old hardware using AS2000.  2008R2 on modern hardware shouldn't have a problem with it.


How to scatter/arrange all the tables in database model (workbench)? I have around 71 tables and if i open them for creating a model in workbench all overlap as below

If i use the auto layout under arrange section then max i can get is as below

How to auto arrange them without any overlapping ?

There's no perfect layout tool. What you get is the best you can get automatically. After that fine tune your layout manually. Btw, make sure you have enough space for all objects by adding more pages if necessary (see menu -> Model -> Diagram Properties and Size...) before you run the auto layout.


Create failed for Index 'ClusteredColumnStoreIndex'. Timeout expired I am creating Clustered Column Store Index in SQL Server 2014.

I am getting error as

  "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (Microsoft SQL Server)".

I set

EXEC sp_configure 'remote query timeout', 60000;
reconfigure
EXEC sp_configure

Row Count = 304969603
Data space = 88,812.266 MB

In Management Studio for SQL 2014, the timeout for the designers is held in Tools > Options > Designers > Table and Database Designers with a default of 30 seconds.

However, as the syntax is so simple for creating a clustered columnstore, as already suggested, you should script it rather than using the designer, eg

CREATE CLUSTERED COLUMNSTORE INDEX IX_MyTable ON dbo.MyTable ON [PRIMARY]
GO

This option won't time out, but may struggle if you don't have enough memory.

In order to get the best out of your compression, you might also consider creating a clustered index on the table to pre-sort it, then drop it prior to creating the clustered columnstore.  YMMV.


Only a single WITH MOVE clause should be specified for any logical file name I'm trying to do a copy database by scripting a backup and restore.  I have the backup procedure working correctly but I keep getting the error

  Conflicting file relocations have been specified for file. Only a
  single WITH MOVE clause should be specified for any logical file name.

when I run the procedure to do the restore.

Here is the procedure code.  Can anyone spot what my error is?

ALTER PROCEDURE spDBRestore (@BackupName NVARCHAR(50), @RestoreName NVARCHAR(50))
AS
BEGIN
DECLARE @BackupPath NVARCHAR(4000) = 'S:\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\TemplateCopy\'+@BackupName+'.BAK',
@NewDBFileName NVARCHAR(4000) = 'S:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\'+@RestoreName+'.mdf',
@NewLFFileName NVARCHAR(4000) = 'L:\MSSQL10_50.MSSQLSERVER\MSSQL\Data\'+@RestoreName+'_1.ldf'
select @BackupName, @RestoreName, @NewDBFileName, @newlffilename
RESTORE DATABASE @RestoreName
FROM DISK = @BackupPath
WITH FILE = 1,
MOVE @BackupName TO @NewDBFileName,
MOVE @BackupName TO @NewLFFileName,
NOUNLOAD,
REPLACE;

END

You shouldn't be moving the backup file, the MOVE clause takes the logical database file name as the first parameter, and then the TO ... is the destination physical filename for the corresponding database file.
BOL reference on RESTORE

MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name' [ ...n ]
Specifies that the data or log file whose logical name is specified by logical_file_name_in_backup should be moved by restoring it to the location specified by operating_system_file_name. The logical file name of a data or log file in a backup set matches its logical name in the database when the backup set was created.

You can retrieve the logical file name of the database files from RESTORE FILELISTONLY (note, the LogicalName column in the result set).


Getting the row before a row with a certain value in SQL I have a table like below where user actions are stored with a timestamp. My goal is to identify the action that happened before a specific action (named reference_action) and count the number of those actions to see which actions happens before the specific actions and how they are distributed.
I am aware of window functions like LAG() where I can get the row before a certain row but can't figure out how to include a constraint like WHERE action_name = "reference_action".
The query engine is Presto and the tables are Hive tables but I'm mostly interested in the general SQL approach, therefore that shouldn't matter much.

session
action_name
timestamp

1
"some_action"
1970-01-01 00:01:00

1
"some_action"
1970-01-01 00:02:00

1
"some_action"
1970-01-01 00:03:00

1
"desired_action1"
1970-01-01 00:04:00

1
"reference_action"
1970-01-01 00:05:00

1
"some_action"
1970-01-01 00:06:00

1
"some_action"
1970-01-01 00:07:00

2
"some_action"
1970-01-01 01:23:00

2
"some_action"
1970-01-01 02:34:00

2
"desired_action1"
1970-01-01 03:45:00

2
"reference_action"
1970-01-01 04:56:00

2
"some_action"
1970-01-01 05:58:00

3
"some_action"
1970-01-01 01:23:00

3
"some_action"
1970-01-01 02:34:00

3
"desired_action2"
1970-01-01 03:45:00

3
"reference_action"
1970-01-01 04:56:00

3
"some_action"
1970-01-01 05:58:00

The result should look like:

action
count

"desired_action1"
2

"desired_action2"
1

There are two rows where "desired_action1" is directly followed by a row with "reference_action", when ordered by timestamp, hence the count being 2. The same logic applies for why the count is 1 for "desired_action2".
The goal is to know what a user did before he made a purchase (purchase = reference_action). To understand what he did before, I want to look up the action that happened before a purchase. Therefore I need to know the action_name in the row before a reference_action. desired_actions have to be counted, reference_actions are just the rows after the actions I want to count and used to determine which values should be counted.

The steps to do this are actually pretty simple in any SQL variant that supports LAG:

Pull out the previous action_name using LAG, in a derived table or CTE.
Filter on the outside to only rows which contain 'reference_action', these rows will also have the previous action from the first step. You want to do this on the outside, otherwise you won't get the correct LAG values.
Group by the previous action, and count.

SELECT
    prevAction AS action,
    COUNT(*) AS [count]
FROM (
    SELECT *,
        LAG(action_name) OVER (ORDER BY timestamp) AS prevAction -- you may want a PARTITION also
    FROM YourTable
) AS t
WHERE action_name = 'reference_action'
GROUP BY prevAction;


Pair of 2-tuple composite FKs pointing to same table (closure table pattern) I don't have much experience with DB design, and having a hard time figuring out what's wrong with the following:

Table father has a composite PK (col1, col2)
Table closure has ancestor(a-col1, a-col2), descendant(d-col1, d-col2) and length. Ancestor and Descendant are composite FKs, each pointing to the PK of father. The 4 columns (a-col1, a-col2,d-col1, d-col2) are a composite PK.

Look at the following fiddle: http://sqlfiddle.com/#!2/71ad1/1

I am working with MySQL.

I have constraints in CASCADE, but when I try to update the father table, the constraints give me errors. I can't do the following update to father table row:

UPDATE father
SET `col1` = "UPDATE_MY_ID_PLEASE"
WHERE `col1` = 'A' AND `col2` = 1;

Is the design wrong? How should this be done?

I don't think this will work in MySQL. It has to do with how MySQL checks constraints (after each and every row update) and not at the end of statement or at the end of transaction. In combination with the double cascade path from father to closure, make this impossible in MySQL with Referential Integrity constraints alone (again, I think it is, if someone posts a solution, I'll be happy to remove my answer.)

A solution in MySQL will probably include some procedure that handles the cascaded updates and deletes itself and with great care, so the constraints hold during every baby step (i.e. every row update.)

In Postgres, it just works though: SQL-Fiddle


Optimization problem: compound clustered keys, flag conditions and index-merge Three tables:

product: with columns: ( a, g, ...a_lot_more... )

a: PK, clustered
g: bit-column

main: with columns: ( c, f, a, b, ...a_lot_more... )

c: PK, clustered
f: bit-column
(a, b): UQ

lookup with columns: ( a, b, c, i )

(a, b): PK, clustered
a: FK to product(a)
c: UQ, FK to main(c)
i: bit-column

I can't find good indexes for the join:

FROM
    product
  JOIN
    lookup
      ON  lookup.a = product.a
  JOIN
    main
      ON  main.c = lookup.c
WHERE
      product.g = 1
  AND
      main.f = 1
  AND
      lookup.i = 1
  AND lookup.b = 17

I tried a covering index on product (g, a, ...) and it's used but not with spectacular results.

Some combinations of indexes on the lookup table produce execution plans with index-merge, with slight efficiency gain over the previous plan.

Is there some obvious combination that I am missing?

Could a re-design of the structure help?

The DBMS is MySQL 5.5 and all tables are using InnoDB.

Table sizes:

product: 67K   ,  g applied:    64K

main:   420K   ,  f applied:   190K

lookup:  12M   ,  b,i applied:  67K

This pains me...

I've had to use temp tables with InnoDB before. Load them with filters, create an index, join these temp table.

The problem as I reckon is if that InnoDB only has Nested Join algorithm: the grown-up RDBMS query optimisers have more to use. This is based on trying to run Data Warehouse type loads on InnoDB.

Temp tables drags the overall complexity down the MySQL query optimiser's level...


Sqlite - Limit Characters for different parts of one String with Check I have to make sure that any inserted Email has the format: "X@Y.Z"

X and Y may only contain letters and numbers and Z can only have Letters.

I tried something like this, but i cant figure out how to limit the Characters for X,Y and Z separately.

Create Table User(
Mail varchar check(Mail NOT GLOB '*[^A-Za-z0-9@.]*' AND Mail LIKE '%_@%_._%'));

So i figured out how to do exactly what i wanted to do.
I used substrings and limited the characters for each individual part of the EMail.

CREATE TABLE User(
EMail varchar NOT NULL COLLATE NOCASE check( (substr(EMail, INSTR(EMail,'.')+1) NOT GLOB '*[^A-Za-z]*')
AND (substr(EMail,1, INSTR(EMail,'@')-1) NOT GLOB '*[^A-Za-z0-9]*')
AND (substr(EMail, INSTR(EMail,'@')+1, ((INSTR(EMail,'.')-1) - (INSTR(EMail,'@')+1))+1 ) NOT GLOB '*[^A-Za-z0-9]*')
AND EMAIL LIKE '%_@%_.%_')


How to setup db credentials for `sqlcmd` using a config file? When I'm using mysql client, I can configure my db credentials in ~/.my.ini.

How can I achieve similar thing with sqlcmd on Linux? Where and what filename I should create?

My goal is to avoid keep repeating hostname, user and passwords for every command.

The odbc.ini for mssql doesn't support the user and password.

Server parameter is used to specify port with syntax [protocol:]Server[,port]

Check /etc/odbcinst.ini for what driver name to use.

$ cat /etc/odbcinst.ini
[ODBC Driver 17 for SQL Server] # must match the 'Driver' in odbc.ini
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.3.so.1.1
UsageCount=1

$ cat /etc/odbc.ini
[ExampleDataSourceName]
Driver=ODBC Driver 17 for SQL Server
Database=example_database_name
Server=tcp:10.1.2.3,50001

$ sqlcmd -D -S ExampleDataSourceName -U user


How to generate auto increment values for my duplicate column in my sql? How to display when i was run to find  duplicate values from my table at this time i want to add new column in select statement  with auto increment values

SELECT * FROM tutorials_tbl
WHERE Lscno IN (
SELECT Lscno
FROM tutorials_tbl
GROUP BY Lscno
HAVING COUNT(Lscno) > 1
)ORDER BY Lscno

ID       Title          LSCNo
10       titl_14    000000/06/3/1944
19       titl_13    000000/06/3/19
561     titl_1      000000/10/125/1955
671     titl_1      000000/10/125/1955
1765    titl_1      000000/06/65/1976
1834    titl_1      000000/06/65/1976
1909    titl_1      000000/06/3/1971
1910    titl_1      000000/06/3/1971

 **Excepted output like** in MY SQL ?

 Add
NewCol    ID    Title   LSCNo
  1       10    titl_14 000000/06/3/1944
  2       19    titl_13 000000/06/3/19
  3       561   titl_1  000000/10/125/1955
  4       671   titl_1  000000/10/125/1955
  5       1765  titl_1  000000/06/65/1976
  6       1834  titl_1  000000/06/65/1976
  7       1909  titl_1  000000/06/3/1971
  8       1910  titl_1  000000/06/3/1971

Try this one, execute one at a time set variable and select statement.

SET @Variable:=0;
SELECT @Variable:=@Variable+1 AS AddNewColumn,ID,Title,Lscno
FROM tutorials_tbl
WHERE Lscno IN (
    SELECT Lscno
    FROM tutorials_tbl
    GROUP BY Lscno
    HAVING COUNT(Lscno) > 1
)ORDER BY Lscno


Data Consistency Between Two MySQL Databases Running Different Versions I need to migrate an old web application using old PHP and MySQL versions.

I am intending to start by migrating the code that interacts with the database to a new server with the latest versions and allow the old server to use an API in the new server. Ideally I would like to have the data consistent between the database in the new server and the one in the old server.

It seems that replication only works only when the master is only one version behind the slave. In this case the difference is a way more than one version. I also want changes to the database on the new server to reflect on the database on the old server.

Are there any tools/techniques to help with this situation other than ensuring the data consistency programmatically with cron jobs?

Plan A:  Do it one step at a time.

Plan B:  Set up a long replication chain:  5.5 -> 5.6 -> 5.7

Plan C:  Take some downtime to do it via a dump.

Plan D:  "Update in place".  That is, install the next version on the existing version, keeping the data intact.  Be sure to run mysql_upgrade.  Repeat.

Caution:  The upgrade from 5.7 to 8.0 may be the trickiest.

Caution:  If you are using PHP's mysql_* interface, it would probably be better to switch to mysqli_* first.  mysql_* has been removed from php 7.

  I also want changes to the database on the new server to reflect on the database on the old server

Abandon that goal.  There are too many risks in using Dual Master with different versions -- primarily because the "Slave" needs to be no older than the "Master".

Get a separate machine to practice on.


What can data cache contain besides pages from physical disk? While setting statistics io option ON, I see this result

physical_reads are 0 and read-ahead reads are 276 which means that 276 pages of that table which are on the disk drive were in data cache, but the whole number of reads from data cache is 794 as indicated in logical reads. So what is kept in data cache besides table pages from disk drive?
Thanks.

There are couple of things here so let me explain

Logical reads: A logical read occurs every time the Database Engine requests a page from the buffer cache. If the page is not currently in the buffer cache, a physical read first copies the page from disk into the cache.

Read aheads: I would suggest you to read Reading Pages in SQL Server

  The Database Engine supports a performance optimization mechanism called read-ahead. Read-ahead anticipates the data and index pages needed to fulfill a query execution plan and brings the pages into the buffer cache before they are actually used by the query. This allows computation and I/O to overlap, taking full advantage of both the CPU and the disk.

  The read-ahead mechanism allows the Database Engine to read up to 64
  contiguous pages (512KB) from one file. The read is performed as a
  single scatter-gather read to the appropriate number of (probably
  non-contiguous) buffers in the buffer cache. If any of the pages in
  the range are already present in the buffer cache, the corresponding
  page from the read will be discarded when the read completes. The
  range of pages may also be "trimmed" from either end if the
  corresponding pages are already present in the cache.

Now to your question

  physical_reads are 0 and read-ahead reads are 276 which means that 276 pages of that table which are on the disk drive were in data cache,

No that does not means just the 276 pages it can be much more than 276 actually it is always more than that. Read aheads read *more than required pages *and bring that pages into memory this is done for performance optimization to make sure all the pages are in cache and physical I/O can be avoided. A single read may bring upto 64 pages and this would be seen as one read.From Old BOL document Read-ahead requests are generally 128 pages for each group but can be as many as 1,024 pages when running Microsoft SQL Server Enterprise Edition.

From this Old Technet Article, for enterprise edition Read Ahead  can be up to 1024 KB.

Logical reads are counted for single pages while read aheads are counted as one for chunk of pages.


Com_commit and Com_rollback count are almost same. What doest it mean? SHOW GLOBAL STATUS has Com_* variables: The Com_xxx statement counter variables indicate the number of times each xxx statement has been executed.
When I compare Com_commit to Com_rollback, both are almost equal. I do not understand how is that possible. My understanding is that you commit transactions and any rollback is done only when required. Then, why these values are so close?
We have only one database that is used for application (Jira) and it uses READ-COMMITTED Transaction Isolation. There are no other procedures in the entire mysql that does any operations.

com_* status variables are a pure count of what the database server receives from the application.
As those are the numbers from JIRA, you can conclude that's what JIRA is issuing to the database.
I'd suggest a JIRA forum maybe able to diagnose why it is issuing so many rollbacks. It does look very suspicious.


How do I allow users to see grants, view definitions, PL/SQL, etc in a database without granting them access to change those objects As a developer, I need to be able to access the design of the database/database objects from production so I can ensure I'm working with the latest and greatest version of the objects I'm developing changes for (helps to avoid unforeseen problems at deployment).

Also as a developer, I should not have access to modify, execute, update, delete, etc those objects - that's the job of the production deployment team.

As a DBA, how can I provide access to the design of the database so developers can validate the changes they are working on without also granting them carte blanche access to the database objects?

As a specific example, our DBA's have sufficiently locked down the production environment so that, within TOAD, I can't see the PL/SQL body of a package, can't see all of the grants providing access to an object, etc.  The user account being used just has limited access to use some of the objects but can't view the extended details a developer would need to develop changes.

I'd love to have things set up so we have dictionary view access that allows us to see all the grants, the view scripts, the PL/SQL package bodies, table definitions, etc without also giving us more access than we should have in production (ie, we shouldn't be able to change any of those things)

Is there a role that provides this access, a privilege or series of privileges we would need?  If there isn't one, what's a good alternative method for getting this kind of information?

The SELECT ANY DICTIONARY privilege (or, in earlier versions the SELECT_CATALOG_ROLE role) gives a user privileges to select from any data dictionary table.

The SELECT ANY DICTIONARY privilege would give a developer privileges to write whatever queries they'd like against DBA_SOURCE to see the source for any object, DBA_VIEWS to see view definitions, etc. But there is no guarantee that a particular front-end would actually leverage those privileges correctly.  Far too many GUIs simply select from the ALL_* data dictionary tables to display the objects that a user has object privileges on rather than recognizing that the user has permission to select from the DBA_* data dictionary tables.  Personally, I'm more than happy to query the DBA_SOURCE view (or use the DBMS_METADATA package) when I want to look at object source in a production database but lots of folks get antsy without the GUI schema browser.


Performance of primary key IDs with gaps (but in sequential order) I know that having non-sequential IDs is bad for index performance.  But assuming all my IDs are created in correct order, but with large gaps: i.e:

154300000
283700000
351300000
464200000

...will the performance be any worse than having gapless auto_increment IDs?

I'll be using MySQL, or perhaps PostgreSQL.  The gaps between the IDs would not be even.  They'll be BIGINTs with a unix timestamp at the start (left side) of the number, and the rest of the numbers mostly being random, as discussed in another question I asked here:

https://stackoverflow.com/questions/6338956/mysql-primary-keys-uuid-guid-vs-bigint-timestamprandom/6339581

As far as PostgreSQL is concerned, your assumption

  having non-sequential IDs is bad for index performance

is generally not true. There are special use cases, where sequential numbers help somewhat, but not with index performance. In normal operation it does not make any difference whatsoever.

Operations on UUIDs are a somewhat slower than on int or bigint, because the are 16 bytes long (as opposed to 4 / 8 bytes).

Generally, having gaps in the numbering is the rule, not the exception.

For your special case, if you have a small number of instances and control over them, you could set aside separate ranges of a bigint or even just int: sequences with pre-set START value, for instance. Since you don't have mass inserts, there should be plenty of numbers.

For more instances or if you don't have control, I would strongly advice to use UUID instead of any home-grown solution. It's an established, well-tested technique and performs well.


Import table data into a text file I'm new to powershell. I need to write a script which will import data for each table into separate text file.For sample table, I tried with below script:

    bcp -u sa por.dbo.audit out D:\tbackup\audit.dat

and I received below error:

Copy direction must be either 'in', 'out' or 'format'.
usage: C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\bcp.exe {dbtable | query} {in | out | queryout | format} datafile

  [-m maxerrors]            [-f formatfile]          [-e errfile]
  [-F firstrow]             [-L lastrow]             [-b batchsize]
  [-n native type]          [-c character type]      [-w wide character type]
  [-N keep non-text native] [-V file format version] [-q quoted identifier]
  [-C code page specifier]  [-t field terminator]    [-r row terminator]
  [-i inputfile]            [-o outfile]             [-a packetsize]
  [-S server name]          [-U username]            [-P password]
  [-T trusted connection]   [-v version]             [-R regional enable]
  [-k keep null values]     [-E keep identity values]
  [-h "load hints"]         [-x generate xml format file]
  [-d database name]        [-K application intent]  [-l login timeout]

Might be it's a silly question. But, any immediate help will be appreciated

If you are using Windows Authentication to connect you sql server, use following command.

bcp "por.dbo.audit" out "D:\tbackup\audit.dat" -S "SQLServerName/IP" -T -t -n

If using SQL Server authentication, use this one

bcp "por.dbo.audit" out "D:\tbackup\audit.dat" -S "SQLServerName/IP" -T -U Login -P password -t -n

Edit:1

The error appears because, giving variable as an input to 'bcp` accepted as a value. So if I use

bcp $Database+'.'+$Schema+'.'+$Table out $BCP_To+$Table+$ExporttoFile -S $Server -T -t -n

here $Database+'.'+$Schema+'.'+$Table assumed as single value Database.dbo.TableName (a table) and $BCP_To+$Table+$ExporttoFile is another value. (not taking as path).

So, instead of using above command in powershell. I prepared command to a variable and executed it using Invoke-Expression. You can say it dynamic powershell command execution.

Here is complete power-shell command to export you all tables of a database into .txt file.

[reflection.assembly]::loadwithpartialname('Microsoft.SQLServer.SMO') | out-null

$Server = 'SQLServer'
$Database = "Database"
$BCP_To = "D:\tbackup\"
$ExporttoFile =".txt"

[string]$Table
[string]$Schema

$Server = New-Object('Microsoft.SQLServer.Management.SMO.Server') $Server

foreach ($Tables in $Server.Databases[$Database].Tables | SELECT Schema,Name)
{
$Schema = $Tables.Schema
$Table = $Tables.Name

$BCP = 'bcp "'+$Database+'.'+$Schema+'.'+$Table+'" out "'+$BCP_To+$Table+$ExporttoFile+'" -S '+$Server+' -T -t -n'

$BCP = $BCP.Replace('[','')

$BCP = $BCP.Replace(']','')

Invoke-Expression $BCP
}

Hope it'll help you.

Thanks!


Difference of two columns with possible NULL cells I need to sort a table by the difference of two columns as

SELECT (first - second) AS dif
    FROM table1
ORDER BY dif DESC

but the two columns can be NULL. In this case, it returns the error:

BIGINT UNSIGNED value is out of range

The solution to this error is to use IFNULL(col,0), but the problem is that I need dif to be NULL if any of the columns is NULL

Many ways of doing this, but I find this readable:

SELECT CASE WHEN first IS NULL OR second IS NULL THEN NULL
       ELSE first - second END AS dif
    FROM table1
ORDER BY 1 DESC


Data inconsistency prohibition if a table refers to another via two many-to-many relationships I have following database design (by => foreign key constraints are depicted):

Company[id]
CompanyRealm[id, company_id=>Company.id]many2many

Project[id, company_id=>Company.id]
ProjectRealm[id, project_id=>Project.id, company_realm_id=>CompanyRealm.id]many2many

Problem is that provided database design allows inconsistent data. For example:

Company1(id=1)
Company2(id=2)
CompanyRealm(id=11, company_id=1)
Project(id=33, company_id=2)
ProjectRealm(id=44, project_id=33, company_realm_id=11)

(Company can have many Realms, Project belong to Company and  may concern any number of company's Realms)

ProjectRealm refers to two different companies:

Company1 (via CompanyRealm); and
Company2 (via Project).

Is something wrong with my DB design?
If yes - which prescriptions are violated?
If no - how to prevent incorrect data insertion (via constraint? trigger with exception?)

You are right, the design allows inconsistencies, exactly what you notice. A ProjectRealm may be referring through Project to a company and through CompanyRealm to another company.

This is not uncommon, it appears when there is a triangular or a "diamond" shape in the relationships:

Realm          Company
   \           /     \
    \         /       \
     \       /         \
    CompanyRealm     Project
             \         /
              \       /
               \     /
             ProjectRealm

The common solution, if you want to enforce this via DRI (Declarative Referential Integrity), ie. through usual UNIQUE, FOREIGN KEY and CHECK constraints - and not though triggers - is to use composite keys for the ProjectRealm REFERENCES CompanyRealm and for the ProjectRealm REFERENCES Project foreign keys.

You will need to replace the company_realm_id with 2 columns (company_id, realm_id) and add a composite UNIQUE constraint on each of the two referenced tables (or modify their PRIMARY KEY):

Company [id]

Realm [id]

CompanyRealm [id, realm_id=>Realm.id, company_id=>Company.id,
              UNIQUE(company_id, realm_id)]

Project [id, company_id=>Company.id,
         UNIQUE(company_id, id)]

ProjectRealm [id, project_id, company_id, realm_id,
              (company_id, project_id) => Project (company_id, id),
              (company_id, realm_id) => CompanyRealm (company_id, realm_id)]

See also several other questions, where the same (or quite similar) problem appears. The diamond pattern is obvious in all:

Best data modelling approach to handle redundant foreign keys in relational model
and the nice answer and logical models and diagrams by MDCCL.
Foreign Key Constraint over two tables
How to represent it with UNIQUE indexes and foreign keys?
Many to Many and Weak Entities
Constrains relationship


TSQL Re-Enabling Contraints take a long time Is there any way I can speed up the re-enabling of constraints?

As part of a job to update several tables in our Warehouse, which is being done out of hours, the re-enabling of constraints on said tables can take over an hour.  The rest of the job, the updates, take about 5 mins. Disabling the same constraints before the updates begin is sub second.

EDIT
The job in question is to update the PKs and FKs on rows of data before a given date so to prevent those records appearing in reports. A way to soft delete records` if you like.

Most of the table row sizes are >130m rows each except for one which is over 370m rows. The other two are smaller.

TableName   rows
Table_1     371255778
Table_2     131703902
Table_3     131665535
Table_4     131665535
Table_5     131665535
Table_6     131665535
Table_7     19364988
Table_8     1458800

I am using the following the command to disable the constraints on each table

ALTER TABLE database.dbo.table_name        NOCHECK CONSTRAINT ALL;

And the following to re-enable them again

ALTER TABLE database.dbo.table_name        WITH CHECK CHECK CONSTRAINT ALL;

there are no reports or other jobs running at the same time.

Is taking so long to re-enable constraints expected behaviour or is there something that can be done to speed it up?

Thanks in advance

As expected.

For example, CHECK constraints: every row must be checked for every check constraint.

If you have 3 CHECK constraints and 370 million rows, that's 1.11 billion checks to be done in a single ALTER.

Each UNIQUE constraint must check all 370 million rows, in the same ALTER.

This is why I don't normally disable constraints in my DWs.
It takes too much time. Especially when you get to billions of rows.


Why use multiple full & differential backups when I can use transaction log backups? I'm new to DB backup. I just learned about backup types from this question(Link). After I read the differential backup is cumulative since the last full, I got curious if multiple full and differential backups are not mandatory.

For example, I have backups like this:

full_backup_2018_05_09_000000.bak
tran_backup_2018_05_09_000500.trn
tran_backup_2018_05_09_001000.trn
diff_backup_2018_05_09_001500.bak
tran_backup_2018_05_09_002000.trn
tran_backup_2018_05_09_002500.trn
diff_backup_2018_05_09_003000.bak
tran_backup_2018_05_09_003500.trn
tran_backup_2018_05_09_004000.trn
diff_backup_2018_05_09_004500.bak
tran_backup_2018_05_09_005000.trn
tran_backup_2018_05_09_005500.trn
full_backup_2018_05_09_010000.bak
...

What's the difference between this and using one full backup and transaction log backups like this?

full_backup_2018_05_09_000000.bak
tran_backup_2018_05_09_000500.trn
tran_backup_2018_05_09_001000.trn
tran_backup_2018_05_09_001500.trn
tran_backup_2018_05_09_002000.trn
tran_backup_2018_05_09_002500.trn
tran_backup_2018_05_09_003000.trn
tran_backup_2018_05_09_003500.trn
tran_backup_2018_05_09_004000.trn
tran_backup_2018_05_09_004500.trn
tran_backup_2018_05_09_005000.trn
tran_backup_2018_05_09_005500.trn
tran_backup_2018_05_09_010000.trn
...

The difference in functionality is minimal - the difference in what happens when you come to actually do a restore is tremendous.

Log backups are sequential, so in the above scenario to restore to 00:55 you would need to restore

full_backup_2018_05_09_000000.bak
diff_backup_2018_05_09_004500.bak
tran_backup_2018_05_09_005000.trn
tran_backup_2018_05_09_005500.trn

VS

full_backup_2018_05_09_000000.bak
tran_backup_2018_05_09_000500.trn
tran_backup_2018_05_09_001000.trn
tran_backup_2018_05_09_001500.trn
tran_backup_2018_05_09_002000.trn
tran_backup_2018_05_09_002500.trn
tran_backup_2018_05_09_003000.trn
tran_backup_2018_05_09_003500.trn
tran_backup_2018_05_09_004000.trn
tran_backup_2018_05_09_004500.trn
tran_backup_2018_05_09_005000.trn
tran_backup_2018_05_09_005500.trn

Utilizing DIFFs and FULLS means you don't need to replay through all those log files, which can be time consuming and potentially put a huge dent into your RTO (Recovery Time Objective)

You have to ask yourself in the event of a disaster, or any recovery scenario, how long are you willing to wait for the database to become available again - this normally dictates what methods you can then use.

Alternative answer: If you're a masochist, use just log backups.


Oracle unpivot of table data Given the table PerformerTours with the columns:
FirstName, LastName, Date_1, City_1, State_1, Date_2, City_2, State_2, Date_3, City_3, State_3, Date_4, City_4, State_4

and the data
Donna, Summers, 01/01/20, Atlanta, Georgia
James, Taylor, 02/04/20, Miami, Florida, 04/11/20, Seattle, Washington
Bob, Dylan, 01/11/20, Buffalo, New York, 09/24/20, Nashville, Tennessee, 10/22/20, Chicago, Illinois, 12/12/20, Denver, Colorado
Kenny, Rankin, 06/30/20, Las Vegas, Nevada, 07/04/20, Boston, Massachusetts, 08/18/20, Cincinnati, Ohio

how can I unpivot the data to be as defined below using just Oracle SQL:
Donna, Summers, 01/01/20, Atlanta, Georgia
James, Taylor, 02/04/20, Miami, Florida
James, Taylor, 04/11/20, Seattle, Washington
Bob, Dylan, 01/11/20, Buffalo, New York
Bob, Dylan, 09/24/20, Nashville, Tennessee
Bob, Dylan, 10/22/20, Chicago, Illinois
Bob, Dylan, 12/12/20, Denver, Colorado
Kenny, Rankin, 06/30/20, Las Vegas, Nevada
Kenny, Rankin, 07/04/20, Boston, Massachusetts
Kenny, Rankin, 08/18/20, Cincinnati, Ohio

Select FirstName, LastName, Date_1 as date, City_1 as city, State_1 as state
  From performertours
Union all
Select FirstName, LastName, Date_2, City_2, State_2
  From performertours where date_2 is not null
Union all
Select FirstName, LastName, Date_3, City_3, State_3
  From performertours where date_3 is not null
Union all
Select FirstName, LastName, Date_4, City_4, State_4
  From performertours where date_4 is not null


Should a table have a clustered index even if it doesn't have appropriate fields for it? If you have a table without a good candidate field or fields for a clustered index (stable, sequential), is it better to have a clustered index on a bad field or is it better to make all table indices non-clustered?  What's the rule of thumb?

UPDATE

As per feedback, here is a very specific example to make the question more concrete.  Assume I have a 'PlayerStatsView' table, that looks like the following:

CREATE TABLE [PlayerStatsView](
    [PlayerId] [uniqueidentifier] NOT NULL,
    [TeamId] [uniqueidentifier] NOT NULL,
    [FirstName] [nvarchar](255) NOT NULL,
    [LastName] [nvarchar](255) NOT NULL,
    [Status] [nvarchar](255) NOT NULL,
    [BattingAvg] [int] NOT NULL,
    [RBIs] [int] NOT NULL,
    [HomeRuns] [int] NOT NULL,
    [PercentageOnBase] [int] NOT NULL,
    CONSTRAINT [PK_PlayerStatsView] PRIMARY KEY NONCLUSTERED ([PlayerId] ASC)
)

So in more concrete terms...

PlayerId, the primary key, is a GUID, as is TeamId.  FirstName and LastName combined are unique (or just about), but will be nowhere near sequential, not to mention they're too big to both include in a single index.  (For the purpose of this exercise, assume that player names change occasionally - not often, but from time to time.)  The rest of the fields will be updated with every write.

I'm mainly going to be querying by PlayerId and TeamId, I will have a non-clustered index on each.

The table currently contains tens of thousands of records, and will eventually hit hundreds of thousands.

Back to the question:

Am I better off without a clustered index, or should I add a clustered index even though there are no fields that are really suitable for one?

1) IF PlayerId is assigned with NEWSEQUENTIALID, you could consider that as the clustered index.

2) Otherwise, you can add an IDENTITY and make that clustered (questionable benefit, since all access will be through the PK you have already established).

3) Or you can leave it as a heap - with appropriate non-clustered indexes.

My order of preference would be 1, 3, 2 assuming you can't change the uniqueidentifier to an IDENTITY instead.

Can you explain why you are using uniqueidentifier in the first place? - that may have some bearing on this.


Database Normal Form (financial statement) I would like some advice on how to design the database for financial statement data which objectively addresses the tradeoffs between usability, performance, maintenance and adherence to normal form. Also, what about my proposed design(s) violate normal form?

Database (in NF3?)

TICKER TABLE

ticker |
wmt
...

YEAR TABLE

year |
2014
...

QUARTER TABLE

quarter |
q1
q2
q3
q4

YEAR_QUARTER TABLE

year    | quarter    |
year_fk | quarter_fk
2014_fk | q1_fk
...

TICKER_QUARTER TABLE (necessary because fiscal/calendar dates are not the same for all companies)

ticker    | quarter         | date
ticker_fk | year_quarter_fk | e.g. 2014-11-11
...

ITEM TABLE

item    |
revenue
cogs
...

Final Table Options

TABLE 1

ticker    | date                 | item       | value
ticker_fk | ticker_quarter_fk    | item_fk    | 100.00
wmt_fk    | ticker_quarter_fk    | cogs_fk    |  65.00
...

QUESTION: Does this Table 1 option properly adhere to normal form?

TABLE 2

ticker | date              | revenue | cogs  | ...
wmt_fk | ticker_quarter_fk | 100.00  | 65.00 | ...
...

I don't like this option because if I add a new item I need to append a new column.

Or

I scrap the item_fk reference table and have a table for EACH AND EVERY item, e.g.

TABLE REVENUE

ticker | date              | value
wmt_fk | ticker_quarter_fk | 100.00

TABLE 1...N (cogs, sg&a, ...N)

ticker | date              | value
wmt_fk | ticker_quarter_fk | n

I don't like this option because I have to create a new table for each item and I often have use for multiple items in a single query so this option would require a lot of joins.

My team once had to build a database for reporting financials with alternative time windows, including fiscal years, fiscal quarters, and fiscal months.  The relationship between dates and these units was documented, but really messy.  So here is what we did.

We created a table, call it Almanac, with one row per date.  (In reality, we had one row per work shift, but I'm leaving out that complexity for the sake of clarity).  We wrote an Almanac generating program that applied the strange rules for fiscal years, quarters and months to each date over a ten year period.  The PK for the Almanac table was the Date, although we couldn't use the name Date because it was a keyword.  The other columns were items like the fiscal year, the fiscal quarter, and flags like "Company Holiday". Notice that this program had one output, but no inputs.

Once the almanac was built, joining any other table with the almanac over a date field made it trivial to slice and dice by fiscal time periods, regardless of how obscure the rules were.  This worked like a charm.

(If you must know, the Almanac generator was written in COBOL+SQL.  This was a long, long, time ago, when Oracle was very young.)


Audit user queries on SQL Server 2005 I'm in an unpleasant situation where I have a requirement to audit any queries executed by users via SSMS, SQLCMD, etc. on a SQL Server 2005 database.

The situation is made worse by the inability to upgrade the version of SQL Server, change server permissions, etc.

I'm currently auditting user logons under priveleged accounts (previous question: Audit 'sa' login in SQL Server 2005).

I'm concerned that now I have a requirement to audit what queries are being run that I will need to set up server-side traces to a table, which will introduce a higher performance cost.

I've hit up Google for some recommendations but can't seem to find anything other than 'run server-side traces / profiler'.

What other options (if any) do I have here? Will I have to bite the bullet and set up highly filtered server side traces to capture this data?

Unfortunately there isn't any Change Data Capture, Change Tracking (both coming only from SQL 2008 +) or even enough Trace Events or Trace Event Groups for Use with Event Notifications.

You're stuck with server side traces for saving all the user queries.


Struggling to design addresses table for fast querying I have two tables: addresses and locations.

Locations
zipcode
city
county
state

Addresses
line1
line2
city
county
state
zipcode

Posts
Id
Address_id
Body

All my users are required to provide at least a personal address and some need to provide the business address.

My users can create two types of posts: first requires to have a full address and the second requires just the location (state, city, zipcode).

Now, the issue comes because I need to allow searches for posts based on location. The search is always based on city state and zipcode (not street numbers).

Does it make sense to place all the addresses inside the Addresses table and for the posts that need just the state, city and zipcode to leave the other fields blank? Would this slow down posts searches based on addresses?

Would having in my DB 5.000-10.000 users and maybe 200.000 addresses really slow down the process of searching for first type of posts based on the address?

A single address table will be a clean and efficient solution to maintain and execute queries against. From the information you've given the main reason you might not want this is you have two well defined use cases, one of which expects additional fields to be completed while the other accepts a significant number of blank fields.

Handle this in your application logic - users should never be allowed to progress or commit data if all required fields are not validated in the front end.

If you do not already have a design that makes the address/user type clear (e.g. FK to user with a user type) you could always add a bit flag to your address table that defines the address type. If you store both a personal and a business address you may need this anyway.

AddressIsPersonal BIT NOT NULL

This will allow you to ensure a unique PK and to query Address table directly for data checks and searches. For example if AddressIsPersonal = FALSE means we expect to see complete addresses returned and we may only be searching for business posts.


SQL Server AD groups membership seems cached - how to flush to test? I am configuring a new, fully AD group based security scheme on an existing SQL Server 2008.

When I change the group to which I belong to test my configs, it seems that there is quite a delay between the change and it appearing in SQL Server.

To try and improve this I do:

repadmin /syncall /Ade (to force full domain syncing)
DBCC FREESYSTEMCACHE(‘TokenAndPermUserStore’)

I've also tried disconnecting and reconnecting the user in SSMS.

Any idea how I can get the AD group permissions to apply faster? Testing once every ten minutes is painful!

AG Groups are only updated when the user gets a new token from the domain controller.  The only way to speed this process up that I know of is to log off the machine and log back in.

I would recommend using a Virtual Machine to do this so that you don't want to kill yourself.


Oracle - how to find what caused CPU to spike 3 weeks ago On August 22, 2017 at 11:15 on our Oracle 11.2.0.4 (32 cpus, 200GB) we had a cpu spike from 10 to 85% (as seen on AWs cloudwatch).  At 11:25 it went to 100%

Now 3 weeks later we need to understand what sql was running at 11:15 that caused the issue i.e. how much cpu they consumed.  not the ones at 11:25 but ones at 11:15

is that possible

If you have your AWR settings were set up to retention other than default 8 days, for example 30 days, then answer is yes. Or if you have backup of database of that time.

Check settings by script:

SQL> @?/rdbms/admin/awrinfo.sql

At the start of output check for row:
"Snapshot interval is 60 minutes and Retention is 30 days"

If AWR retention is enough and if you have license on Oracle Diagnostic Pack then you can run ashrpt script to see the approximate load on this interval.

SQL> @?/rdbms/admin/ashrpt.sql


Inventory database maintaining remaining qty of an item In my inventory database there is a sale table.

This table contains fields sold qty and balance qty of an item after each sale.

While implementing 'edit sale' feature, I need to update the sold qty field of a sale done in the past. This will affect the balance qty field of all the records past that sale of that particular item. I would need to run a query to update all the sale records past this sale. Assuming the total no. of records in this table < 10 K, is this a good design?

Should I calculate balance qty on the fly when needed for report/visualization and not storing it in the sale table?

Sale table structure
====================
Sale Id  | Item ID |  Sold QTY | Balance QTY    | Date
1          1            10            1000        2018-01-19
2          1            50             950        2018-01-20
3          1           100             850        2018-01-21
4          1           200             650        2018-01-22
...

This kind of table is useful for a report where I need to show how the items sold over a period.
Now I need to give edit feature where a user can edit a past sale for example (sale id = 2) He can change the sold qty to 100 from 50.

I'm not a sqlite expert, but I definitely think your database should be normalized further and you should store Quantity for an item on a separate table than the Sales table.

Even though you've tagged your RDBMS as sqlite, I'd recommend having a look at the AdventureWorks table definitions to get an idea of how you might split your data attributes out to reduce data redundancy.  In the AdventureWorks database, the Quantity is stored in ProductInventory table.

At a minimum, you could start with:

Customer Table - Contains current customer information. Customers are categorized by type: individual consumer or retail store.
SalesOrderHeader Table - Contains the general, or parent, sales order information. The specific products associated with the sales order are stored in SalesOrderDetail Table.
SalesOrderDetail Table - Contains individual products associated with a specific sales order. A sales order may include orders for multiple products. The general, or parent, information for each sales order is stored in SalesOrderHeader. Each product that is ordered, or child, is stored in SalesOrderDetail.
Product Table - Contains the products sold or used in the manufacturing of sold products.
ProductInventory Table - Contains the product inventory information.

Be sure to check out the other AdventureWorks table definitions as they may give you additional ideas about splitting out your data.


SQL sort by sum of two different where clauses in a join table1

ID | Name
1    ID1
2    ID2
3    ID3

table2

ID | table1ID | Values
1    1          3
2    2          3
3    3          2
4    1          3
5    2          1
6    3          2
7    1          1
8    2          3
9    2          2
10   1          1
11   2          1

I need as columns, table1s name, table2s Count of Values being 1, values = 2 and values = 3. These also need to be sorted by the total count of values = 1 followed by total count of values = 2

So Basically

Name | 1s | 2s | 3s
ID2    2    1    2
ID1    2    0    2
ID3    0    2    0

My existing code is

$sql2 = "SELECT * FROM table1
INNER JOIN table2 b ON
(table1.ID= b.table1ID)
WHERE b.Values = '1'
GROUP BY b.table1ID
ORDER BY count(*) DESC";

Works for the single sort by but not the second.

i would be tempted to write something like below to pivot sum.

Query

SELECT
   table1.Name
 , SUM(CASE WHEN table2.Values = 1 THEN 1 ELSE 0 END) AS "s1"
 , SUM(CASE WHEN table2.Values = 2 THEN 1 ELSE 0 END) AS "s2"
 , SUM(CASE WHEN table2.Values = 3 THEN 1 ELSE 0 END) AS "s3"
FROM
 table1
INNER JOIN
 table2
ON
 table1.ID = table2.table1ID
GROUP BY
 table1.Name


Upgrade Postgres 9.3 to 9.4 after Ubuntu 14.04 to 14.10 upgrade I've just recently upgraded my Ubuntu install from 14.04 to 14.10. This automatically pushed Postgres 9.4 onto the system. I'm trying to migrate my cluster like this:

sudo pg_dropcluster 9.4 main --stop
sudo pg_upgradecluster 9.3 main
sudo pg_dropcluster 9.3 main

However, after running the second command I get the following error:

Error: could not get cluster locales.

How do I fix this?
(PS Sorry if this is a stupid question, and please let me know what additional information is required)

PS: locale (lc_collate) returns en_US.UTF-8

I had the same error, I figured that the error was happening during shutdown of old cluster, it actually caused the cluster not to shut down fully.

What worked for me was stopping the old cluster manually and only then performing an upgrade.

sudo service postgresql stop
sudo pg_upgradecluster 9.3 main


Why does a SELECT (JOIN on regex) gets 30x slower with +1 row (query plan stays the same)? Lack of memory? Here is my query, it takes 2.7 sec:

SELECT t2.id, t1.id FROM sample t1
INNER JOIN (select * from regex_set limit 33) t2 ON t1.data ~* t2.regex;

The associated EXPLAIN (ANALYSE,BUFFERS) output:

Nested Loop (cost=0.00..1108.30 rows=356 width=8) (actual time=56.130..2740.906 rows=33 loops=1)
 Join Filter: (t1.data ~* regex_set.regex)
 Rows Removed by Join Filter: 65967
 Buffers: local hit=18
 -> Seq Scan on sample t1 (cost=0.00..38.59 rows=2159 width=36) (actual time=0.014..1.534 rows=2000 loops=1)
    Buffers: local hit=17
 -> Materialize (cost=0.00..1.08 rows=33 width=36) (actual time=0.000..0.004 rows=33 loops=2000)
    Buffers: local hit=1
    -> Limit (cost=0.00..0.59 rows=33 width=36) (actual time=0.005..0.014 rows=33 loops=1)
       Buffers: local hit=1
       -> Seq Scan on regex_set (cost=0.00..22.70 rows=1270 width=36) (actual time=0.004..0.008 rows=33 loops=1)
          Buffers: local hit=1
Planning time: 0.129 ms
Execution time: 2740.952 ms

The same query with limit set to 32 takes only 0.25 sec with same plan (there is nothing special with row 33: tested limit 32 offset 1, had the same result as below):

Nested Loop (cost=0.00..1075.88 rows=345 width=8) (actual time=5.871..255.315 rows=32 loops=1)
 Join Filter: (t1.data ~* regex_set.regex)
 Rows Removed by Join Filter: 63968
 Buffers: local hit=18
 -> Seq Scan on sample t1 (cost=0.00..38.59 rows=2159 width=36) (actual time=0.008..0.498 rows=2000 loops=1)
    Buffers: local hit=17
 -> Materialize (cost=0.00..1.05 rows=32 width=36) (actual time=0.000..0.002 rows=32 loops=2000)
    Buffers: local hit=1
    -> Limit (cost=0.00..0.57 rows=32 width=36) (actual time=0.003..0.013 rows=32 loops=1)
       Buffers: local hit=1
       -> Seq Scan on regex_set (cost=0.00..22.70 rows=1270 width=36) (actual time=0.003..0.006 rows=32 loops=1)
          Buffers: local hit=1
Planning time: 0.109 ms
Execution time: 255.374 ms

Here is the initialisation script I used for these tests.

DO $$
  DECLARE s_length INT=2000;
  DECLARE r_length INT=50;
BEGIN
    DROP TABLE IF EXISTS sample;
    CREATE TEMP TABLE sample AS
      SELECT generate_series(1, s_length) id, md5(random() :: text) AS data;

    DROP TABLE IF EXISTS regex_set;
    CREATE TEMP TABLE regex_set AS
      SELECT g.id, s.data regex
      FROM generate_series(1, r_length) g (id),
           sample s
      WHERE s.id = round(g.id * s_length / r_length);
END $$;

With my real data, I had a delay at exactly the same 33 limit (the time difference was 1 to 30).

I'm running Postgres 10 on Docker container (2 cpu / 2GB RAM) with following (arbitrary) edits to default postgres.conf (didn't have much effect):

shared_buffers = 512MB
temp_buffers = 32MB
work_mem = 16MB (also set to 64MB, had no effect)

Equivalent query is a lot faster on default MySQL 5.8 container.

Is there any Postgres master that could explain what is happening?

Thanks for the full example.  With that and a little help from "perf" and "gdb", I traced the problem down to this:

src/backend/utils/adt/regexp.c:#define MAX_CACHED_RES   32

Once you are trying to hold 33 regexps in working memory and accessing them in a predictable cycle, each one pushes out of the cache the one that will be needed next, and so every regexp is recompiled every time it is needed.  This recompilation is slow.

I don't know why 32 was chosen for that value, but clearly the cache can't be made unlimited in size.  Maybe it would be nice of this were user-settable rather than compiled in.

With this knowledge, it is easy to tweak the query to change the order of the join execution so that one regexp is tested to completion before moving on to the next one:

SELECT t2.id, t1.id FROM sample t1
right JOIN (select * from regex_set limit 33) t2 ON t1.data ~* t2.regex

However, if there are regexp in t2 which match no rows in t1, then this query will start returning null-extended results for those rows, which is a change in behavior from your existing query.  And if you try to add a simple WHERE clause to filter out those extra rows, then the planner will see through your trick and go back to using the slow join order.  You could try to use a non-transparent equivalent to filter them out:

SELECT t2.id, t1.id FROM sample t1
right JOIN (select * from regex_set limit 33) t2 ON t1.data ~* t2.regex
where coalesce(t1.id,-5) != -5

(assuming real t1.id are always positive)

But perhaps the best solution to this problem is to build a speciality index which supports regexp queries:

create extension pg_trgm ;
create index on sample using gin (data gin_trgm_ops);

The prospect of using that index will inherently require the faster join order to be used, and the actual use of the index will make the query faster as well.


How can I make SQL Server Agent job history available after failing over an AG? I am implementing a new HA/DR architecture using Always On AGs for use in both a HA a DR scenario.

I have mastered a mechanism to ensure that jobs are disabled on the secondary replica and enabled when a failover takes place but now realise that, once this happens, no job history will be available. Job history is essential to determine what hadn't run or failed on the now defunct primary and needs to be run on the new primary.

I am struggling to find any documentation about how to overcome this and make that data available on the new primary.

Has anyone been confronted with this and how did you get around it?

I have mastered a mechanism to ensure that jobs are disabled on the secondary replica and enabled when a failover takes place […]

Hopefully that's using sys.fn_hadr_is_primary_replica in the job/jobstep and not actually enabling and disabling jobs.

  […] but now realise that, once this happens, no job history will be available.

You are correct. Job history is stored in MSDB which is a system database, by default on SQL Server 2017 and below system databases cannot be part of an availability group. This means the job history will only be local.

The only way, currently, to change that is to either write your own code to insert the job history into a user database (such as a dba tools database) or run the jobs from their own independent job server (which may be some enterprise job scheduler). There is nothing built in to do this automatically.

  Job history is essential to determine what hadn't run or failed on the now defunct primary and needs to be run on the new primary.

I would suggest building your own job logging table and writing the necessary logging as part of the jobsteps in each job so that you can know. This isn't just an AG thing, it's a where should I start and stop my jobs based on what I've already done thing and that's the root issue. Creating your own logging in a user database will solve this problem, though it's not the only way (such as using job scheduling software on another server).

Edit: I debated whether or not to put this in because while it is officially announced, all of the details behind the feature aren't public and are subject to change. However, in SQL Server 2019 (which isn't out yet, CTP 2.3 as of this writing) there are System AGs which do have master and model as part of the availability group. Since this functionality isn't currently available, the public documentation isn't available, and some items are subject to change, I hate to put this as an option but included it for completeness.


How to coordinate two sessions writing the same row to a MySQL table I am using MySQL 5.6 and have an append-only "lookup" table that's used to store reference data:

CREATE TABLE my_table (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  my_column VARCHAR(32) NOT NULL,

  PRIMARY KEY (id),
  UNIQUE KEY my_column_uk (my_column)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

Rows are inserted, but never updated or deleted. The table is small (thousands of rows at most), so query performance is not a concern.

I have 2 different processes (running identical code) which insert rows into this table. Each process has an in-memory write-through cache of the my_column -> id value. Whenever a new entry is put into the cache that doesn't already exist, it's synchronously inserted into the database table, then added to the cache.

How do I handle the scenario where 2 (or more) processes try to insert the same my_column value at the same time?

Since the table has a unique key constraint on my_column, my thought is to have both processes try to insert the new row and get the new PK id value. One of them will succeed, and can add the new entry to its cache. The other one will fail, but then it can fall back to querying for the id of the row that the other process inserted. I call this the "write-read" approach.

Is there a better way of handling this contention between the two processes? Would a "read-write" approach be better, potentially with a different transaction isolation level ("read uncommitted" e.g.)?

The one potential downside I see with the "write-read" approach is that it involves catching a duplicate key exception, and using that as an indicator to query for the existing row. This is perhaps a misuse of an exception, because this scenario is expected to occasionally happen, and is not really exceptional.

The problem with the "read-write" approach is that I'm not sure there's a good way to have process 2 "see" the row that process 1 added, until it finishes its transaction. So this might need to be a "read-write-read", in which case I'm better off just starting with the INSERT like in the "write-read" approach.

Some more constraints:

I need to get the resulting PK ID, regardless of whether the row was inserted or already exists. However I don't want to burn through auto-increment values, so I'm not going to use the UPDATE id = LAST_INSERT_ID(id) kludge. I'd rather just execute a second SQL statement to SELECT the id value, because I'm only inserting a row every ~5 minutes into a table with ~100k rows, so query performance is not a major concern.
The reason why I'm not using the VARCHAR my_column column as the PRIMARY KEY column is that this lookup table has a FK reference from another much bigger (500M rows, 20 cols) table. I'm using this lookup table pattern to store reference data - e.g. failure codes ("eligibility.failure.not_enough_foobar_data") or time zones ("America/Los_Angeles") - so that the much larger main table can use a short FK id to reference that value (e.g. failure_code_id or time_zone_id), instead of repeating the long string.

Here's the best solution I've been able to come up with, using a write-read approach:

INSERT using ON DUPLICATE KEY UPDATE; (without first checking to see if the row exists)
if the value returned != 0, then that's the PK of the new row;
else a row must already exist with that value, so SELECT it;
if the SELECT can't find the row, then throw an exception because this is unrecoverable.

This has the following advantages:

it doesn't require any transactionality or a different isolation level, because the insert-if-not-exists statement is already an atomic operation;
it executes at most 2 SQL statements (insert-select) instead of 3 (select-insert-select), making the calling code simpler;
since we're not using INSERT IGNORE, if the INSERT statement throws an exception it will be for a legit reason - e.g. auto_increment has reached the max value of id column - and thus we don't have to worry about needlessly logging duplicate key exceptions or ignoring actual database problems.


How to customize Deploy feature in VS2010 for SQL CLR projects? The default deployment script for SQL CLR objects in VS2010 seems to require one to set the target database to SET TRUSTWORTHY ON and tries to enable vardecimal storage at the database level.

On our landscape we prefer to SET TRUSTWORTHY OFF on our databases.  Also, one cannot enable vardecimal storage on system databases. We have some TVFs that we deploy to master.

It would be nice to be able to have the script be properly generated instead of having to modify it with each build.  For now, I have abandoned the Deploy feature and manually deploy my CLR objects.

How do I customize the autogenerated deployment script?  Is there a way?

for the trustworthy setting you should go to your project and:
Right click on the project - Properties - Go to tab Project Settings -> Catalog properties file - Edit -> in the middle there will be option for set trustworthy on/off.

If you don't need to change the database properties at all at deploy time, then go to the Deploy tab - Deploy configuration file -> uncheck the option 'Deploy database properties' - first option in the list. This should not apply the settings from the 'Catalog Properties file' to the database your deploying on.

As for Vardecimal storage option I remember I saw it somewhere in the deploy settings.. but no clue for the moment, sorry. If I find it I'll get back with solution.
Hope this helps, at least a bit.


Is there a way to determine if a certain query need to have an index? I'd like to know if there is a command in mysql (phpmyadmin) to determine if a certain query needs an index.

For example I have this kind of query:

SELECT name FROM table_name WHERE lastname = 'jobs' ORDER BY date_joined DESC

Then, when I run this query, a message will pop up and say "You can add an index to column_name to increase the speed".

Query:

SELECT name
FROM table_name
WHERE lastname = 'jobs'
ORDER BY date_joined DESC ;

Indexes:

(lastname)
normal index for this type of queries, a simple index on the column in WHERE. It covers all queries that have WHERE last_name = 'some_vale' and will be quite efficient, assuming that the condition is "selective" enough, i.e. that you don't have millions of people with last name 'Jobs':
(lastname, date_joined)
a slightly different index, has the column in the ORDER BY as well. It will perform almost identically to the above index, except it will not require a sort. This is unlikely to be helpful, in most cases.
(lastname, date_joined, name)
a slightly better index, has the column from the ORDER BY and the SELECT lists. It will be slightly more efficient than the normal index, as it is "covering" completely the query. It won't need any sorting and all the values needed will be read from the index, without additional lookups in the main table. Whether this is needed, depends on the requirements about efficiency (will reducing the run time from 20 ms to 5 ms make any difference?).
It will however not be better when the query changes, for example by adding another column in the SELECTlist. So it is an index directed for a very narrow case.

What to do?

As a conclusion, you most probably only need a simple index on (last_name).

  Do I need an index?

Yes, if these queries are common, the difference between not having an index and having one will be the difference between a full table scan and an index seek (with a few additional lookups in the table).
In terms of complexity, that's O(n) vs O(logn), where n is the size of the table, assuming the default B-tree indexes.


Line-breaks in Text column of CSV file I have come across an awkward situation where I have got CSV files with | as the column delimiter. In these files, I don't have Text column qualifier. (i.e. ").
My problem is when I am importing these CSV files using BULK INSERT, if the last column of a row has a line break in it, the text continues into the next row.
I am unable to use SSIS so I am looking for a pure T-SQL solution.

It would be great if someone could guide me in the right direction. I tried reading files using openrowset but that's not changing anything either.

This is probably due to the fact that the text has Carriage Returns in it and that you are using the default row delimiter which is the Carriage Return.

This will be tough to fix since you can't know when a Carriage Return is part of a column or actually a new line. the file needs to be fixed manually.

Unless you know where the file came from...  If there's a possibility  for you to reprocess the file, then I would reprocess it, replacing all Carriage Returns found in columns by blanks ('').

I hope this helps.


innodb_force_recovery I did an innodb force recovery. I went from 1-> and on 5 MySQL booted up. I was able to do mydqldump.
Customer is now saying some of data is wrong. (References to another table)
Is this possible?

I did an innodb force recovery.
Customer is now saying some of data is wrong.
Is this possible?

Short answer: Yes.
Any form of recovery can result in data loss.
Using innodb force recovery, you are telling the DBMS to get that database back up and running any way it can, no matter what that means to the data [that used to be] inside it.
Presumably, you did the force recovery because you had a problem with the database or the server that it runs on?
Your first thought should have been to restore your database from your backups (which, after all, only exist to allow you to do exactly that).
Trying to get MySQL to try and piece together something that may or may not be on the disk any more is a Fool's Errand.


Finding rows for a specified date range I have a table which stores which teacher (teacherid) works for which group (groupid) of pupils starting from the date startdate:

CREATE TABLE `pupilgroupteacher` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `groupid` smallint(5) unsigned NOT NULL,
  `startdate` date NOT NULL,
  `teacherid` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `date` (`groupid`,`startdate`),
  KEY `teacher` (`teacherid`),
  KEY `group` (`groupid`),
  CONSTRAINT `fk_pupilgroupteacher_2` FOREIGN KEY (`groupid`) REFERENCES `pupilgroup` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_pupilgroupteacher_1` FOREIGN KEY (`teacherid`) REFERENCES `employee` (`personid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

Having a teacher ID and a month, I need to find all groups for which this teacher worked (or is going to work) at least one day in the given month.

Note: For a given teacherid there may be more than one startdate (if, for example, a teacher worked for a group, was replaced with another teacher and then replaced back on a later startdate).

It seems that the following code solves my problem:

SELECT DISTINCT groupid
FROM pupilgroupteacher x
WHERE teacherid=@teacher AND startdate<@month+INTERVAL 1 MONTH AND
NOT EXISTS(
SELECT *
FROM pupilgroupteacher
WHERE groupid=x.groupid AND teacherid!=@teacher AND startdate>x.startdate AND startdate<@month
)

(Here @month is the date of the first day of the specified month and @teacher is the teacher ID.)

But:

is this code error-free? Please help to check it.
can it be optimized to use fewer server resources?


Logon error to readonly db getting error:

"The target database ('MyDB') is in an availability group and is currently accessible for connections when the application intent is set to read only. For more information about application intent, see SQL Server Books Online."

in the SQL error logs with no information who is trying to log in.

This is on SQL 2017 version 14.0.3026.27 with Always On High Availability

So on the SQL server, I was able to go to the "Event Viewer -> Windows Logs -> Application" and was able to match the event time with my time stamp in the SQL error log.
Then i was able to find the user that was causing the error.
Since we have a newly introduced AlwaysOn environment we went through his options to make sure it was set up correctly, that didn't help.

The issue was that he was on an old verision of SSMS that when he tried to connect seemed to cause the error. We updated his SSMS to latest version and it stopped.


SQL Server FCI won't start after OS Upgrade I have a two node sql server 2008 r2 cluster that we just did an OS upgrade from windows 2008 r2 to windows 2012 r2, the OS upgrade was done in place so the SQL Server Installation stayed in tack.  The cluster role won't come back online saying it's couldn't find any IP address that this instance depends on.

Everything comes on line except SQL Server resource and looking in the sql server log file it shows that error sql server is starting up as I can see master and model get started up.

From the sql log:

  Could not find any IP address that this SQL Server instance depends upon.  Make sure that the cluster service is running, that the dependency relationship between SQL Server and Network Name resources is correct, and that the IP addresses on which this SQL Server instance depends are available.  Error code: 0x5.

Are there additional steps that need to be taken with Windows Server 2012 r2 that need to be done to get SQL Server 2008 r2 to be able to run in a failover cluster?

Prior to Windows Server 2012 R2, doing an in-place upgrade of Windows for an FCI is not supported. Per Microsoft's documentation:

  Upgrading the Windows operating system of a failover cluster is not
  supported for operating systems before Windows Server 2012 R2. To
  upgrade a cluster node running on Windows Server 2012 R2, see
  Cluster Operating System Rolling Upgrade.

Because your starting OS was Windows Server 2008 R2, you are unable to do an in-place upgrade to Windows Server 2012 R2. Instead, you will need to build a new Windows Cluster, do a fresh SQL install for a new FCI, then migrate to that new FCI.

You can do this using existing hardware by removing a node from the cluster, doing the fresh install of both Windows & SQL, then repeating the process one node at a time to move servers from the old cluster to the new cluster.

Once you are upgraded to Windows Server 2012 R2, you will be able to do in-place OS upgrades in the future.

Given that you've already upgraded the OS, your best bet is going to be to uninstall SQL Server, and re-install the FCI from scratch.