Debit Credit Balance calculating from previous records with Pagination I have a table which contains the following data:

ID      In       Out
1      100.00    0.00
2       10.00    0.00
3        0.00   70.00
4        5.00    0.00
5        0.00   60.00
6       20.00    0.00

Now I need a query which gives me the following result:

ID      In       Out    Balance
1      100.00    0.00   100.00
2       10.00    0.00   110.00
3        0.00   70.00    40.00
4        5.00    0.00    45.00
5        0.00   60.00   -15.00
6       20.00    0.00     5.00

Is it possible to do this with one query, without using a trigger or temp variables or stored procedures? Also will work with pagination on table/grid.

I made this question from this i still don't have the answer for this.

As you have also tagged it with Postgres:

select id,
       "in",
       "out",
       sum("in") over (order by id) - sum("out") over (order by id) as balance
from data
order by id;

The above is not Postgres specific, it's standard ANSI SQL using window functions.

Online example: http://rextester.com/ENBLS73027

To add pagination, use row_number()

select id, "in", "out", balance
from (
  select id,
         "in",
         "out",
         sum("in") over (order by id) - sum("out") over (order by id) as balance,
         row_Number() over (order by id) as rn
  from data
) t
where rn between 3 and 6 -- rows 3,4,5 and 6
order by id;


Get the ID of a next greater/less Value imagine you have follwing table given

id
value

1
5

2
3

3
7

4
1

Depends on your case you need the ID of the next smaller
or greater value.
So the select of ID=2 would give u either 4 or 1.
How would you solve that?
thanks in advice.
Eing

You can just join the table to itself on the id field = the value + 1 and again on value - 1. My example uses an INNER JOIN which will only return the cases where both match, otherwise you'll want to use the appropriate OUTER JOINS or two separate queries, to not filter out.
SELECT * -- Replace with only the columns you need as SELECT * is bad practice
FROM SomeTable ST1
INNER JOIN SomeTable ST2
    ON ST2.id = ST1.value + 1
INNER JOIN SomeTable ST3
    ON ST2.id = ST1.value - 1


sp_DatabaseRestore msg 50000 I used Ola's script to backup my database (SQL Server 2017) and now I'm trying to restore it using Brent's sp_DatabaseRestore. Currently trying to do a Full restore only. Here is my code, written according to the web page:

EXEC dbo.sp_DatabaseRestore
  @Database = 'LOTR_recovery',
  @BackupPathFull = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\OlaBackup\MY-PC\LOTR\FULL\',
  @ContinueLogs = 0,
  @RunRecovery = 1;

I put the SP on the destination database as well as Ola's scripts. Apart from these two, the database is empty. I am getting the following error:

 Getting default data drive for @MoveDataDrive
 Getting default log drive for @MoveLogDrive
 Setting default data drive for @MoveFilestreamDrive
 @ExistingDBAction 0 so do nothing
 @ContinueLogs set to 0
 Msg 50000, Level 16, State 1, Procedure dbo.CommandExecute, Line 98 [Batch Start Line 0]
 The value for the parameter @Command is not supported.

 Date and time: 2019-01-02 13:18:32
 Command: RESTORE DATABASE [LOTR_recovery] WITH RECOVERY

 Msg 3153, Level 16, State 2, Line 1
 The database is already fully recovered.
 Msg 3013, Level 16, State 1, Line 1
 RESTORE DATABASE is terminating abnormally.
 Outcome: Failed
 Duration: 00:00:00
 Date and time: 2019-01-02 13:18:32

I tried to search for msg 50000 and @command but without success.

In addition, must sp_DatabaseRestore be created on the destination server I want to restore on, in order to work?

The documentation that I can find on this procedure is somewhat incomplete, especially with regards to the meaning of the input parameters.

The following are some guesses.

Looking at the output messages in your question and examining the actual stored procedure code, my suspicion is that the database you are attempting the restore already exists.  The stored procedure has a parameter called @ExistingDBAction which seems to control what the procedure is supposed to do if the database already exists.  The default value of this parameter is zero (0), which means do nothing.  You can actually see that in your output.

@ExistingDBAction 0 so do nothing

So, the procedure attempts to restore the backup to an existing database and receives

 Msg 3153, Level 16, State 2, Line 1
 The database is already fully recovered.
 Msg 3013, Level 16, State 1, Line 1
 RESTORE DATABASE is terminating abnormally.
 Outcome: Failed

The procedure apparently accepts values 1, 2 and 3, but the procedure doesn't really have good documentation on exactly what those parameters do.

Option 1 appears to set the existing database to single user mode.
Option 2 tries to kill all existing connections to the existing database
Option 3 tries to drop the existing database.

I'd suggest running the procedure and include the @ExistingDBAction parameter with a value of 3.  This should kill all existing connections to the database and then attempts to drop it.

You could also run your process under SQL Server Management Studio debug.  Stepping through the stored procedure code may give you additional insight regarding the problems you are encountering.

As far as your last question about

  must sp_DatabaseRestore be created on the destination server I want to
  restore in order to work?

That does appear to be the case.

I'm also wondering about the original command in your question.  Specifically, the value you gave for the @Database = 'LOTR_recovery'.  According to the documentation for sp_DatabaseRestore, that parameter should specify the source database.  Is that the actual name of the source database.  If you want to give the restored database a different name from the source database, you should include the parameter @RestoreDatabaseName with a value of the restored database name.

These additional points may or may not be of interest.  I just wanted to make sure you were aware of the parameters and their meanings.


How can I find the user who performed a database transaction? I am using MySQL database. Suppose a database "X". It is used by multiple users. I want to find those users who performed any transaction of data into this database. Is it possible?

Can I find it from MySQL binary log? How?

Yes you can find what you are looking for in mysql log files.
Binary Log keeps track of the transactions that describe database changes such as table creation operations or changes to table data.

Query Log keeps track of all operations including Select and Show statements.

Both log files describe the action with the user who performed it.

This link is a tutorial for how to read binary log file.


ORA-01435: user does not exist I am trying to run the SQL queries coded on my friend's laptop onto mine. I have imported the .sql file into SQL developer and I'm trying to run it.However, I keep getting the error:

01435. 00000 -  "user does not exist"

I have listed all the users:

select * from all_users;

and I get:

OUTLN                                   9 30-MAR-10
SYSTEM                                  5 30-MAR-10
SYS                                     0 30-MAR-10

among other users.
My SQL query is :

  CREATE OR REPLACE TYPE "HOMEWORK1"."INNERTABLE" IS TABLE OF VARCHAR2(128);

I am using Oracle 11g on Win8. Please tell me if I need to post anything else to help you.
Thanks a lot

DazzaL has highlighted the nub of the problem but to give a bit more background, on the assumption you're fairly new to Oracle...

The create table syntax shows that your command is trying to create a table called INNERTABLE in the schema HOMEWORK1. Schema and user are essentially interchangeable in Oracle, so the error saying 'user does not exist' is referring to the HOMEWORK1 schema/user. Unless you've already created that in your database, that won't exist by default.

You can either (a) create the HOMEWORK1 user, (b) update the script to replace HOMEWORK1 with a (non-built-in; see below) user that does exist, or (c) update the script to remove the schema reference which will cause objects to be created as the user you're logged in as.

But who you are logged in as is something to check - it isn't clear from the question, so apologies if you know this and it isn't relevant to you. It's important not to create your own objects under Oracle's built-in schemas, especially SYS and SYSTEM as you could do a lot of damage, and you should not be logged in to a built-in schema routinely - only for specific activities that can't be done otherwise (like shutdown). The only exceptions to that are maybe the sample HR and SCOTT schemas, and even then it'll be better in the long term to use your own account for anything except queries against those.

If this is a fresh install and you're logging in as SYS because that's all you had available, please create a new user, assign it the relevant privileges, and do all further work under that.


Select 2 rows based on datetime value I have a database which is running on  SQL Server 2014.

Activityid = 3 means: log in

Activityid = 4 means: log out

Rownr is not a column (it indicates the number of the row)

Scenario 1)
The database contains a table with the following rows. I would like to select let say row 6 and 7 if the given datetime is 2015-03-07 08:06:53.625. Because the given datetime falls between the StartTime value of row 6 and 7. How can I achieve this?

Scenario 2)
The Activityid 4 is missing at row 5. How can I select rows with missing Activityid 3 or 4?

Rownr   UserName    StartTime               Activityid  Value
1       Anneleen    2015-03-07 07:01:38.707 3           5811
2       Anneleen    2015-03-07 08:52:23.417 4           5811
3       Anneleen    2015-03-07 08:52:34.347 3           5811
4       Anneleen    2015-03-07 14:15:20.140 4           5811
5       Arnold      2015-03-07 11:03:20.420 3           5840
6       Dimitri     2015-03-07 07:02:53.633 3           5817
7       Dimitri     2015-03-07 21:24:20.633 4           5817

Rownr is not a column of the table. I've just added it to the result so I can point out which rows I want as result.

This query returns rows 1-2 and 6-7 because ActivityId 3 is before 2015-03-07 08:06:53.625 and ActivityId 4 is after 2015-03-07 08:06:53.625 for the 2 of them.

Note: Since there is no unique ID column, I supposed that there is no duplicates in [StartTime] for a [UserName].

Query 1 (link):

DECLARE @date datetime = '2015-03-07 08:06:53.625';

WITH list AS (
    SELECT *
        , n = ROW_NUMBER() OVER(PARTITION BY [UserName] ORDER BY [StartTime])
    FROM data
    WHERE [Activityid] IN (3, 4)
)
SELECT *
FROM list l1
INNER JOIN list l2 ON l1.UserName = l2.UserName AND l1.n+1 = l2.n
WHERE l1.Activityid = 3 AND l2.Activityid = 4
    AND l1.StartTime < @date AND l2.StartTime > @date;

Output 1:

Rownr   | UserName  | StartTime                     | Activityid    | Value | n | Rownr | UserName  | StartTime                     | Activityid    | Value | n
1       | Anneleen  | 2015-03-07 07:01:38.7070000   | 3             | 5811  | 1 | 2     | Anneleen  | 2015-03-07 08:52:23.4170000   | 4             | 5811  | 2
6       | Dimitri   | 2015-03-07 07:02:53.6330000   | 3             | 5817  | 1 | 7     | Dimitri   | 2015-03-07 21:24:20.6330000   | 4             | 5817  | 2

Using the same CTE this query returns rows where ActivityId 3 or 4 has no matching ActivityId 4 or 3.

Query 2 (link):

WITH list AS (
    SELECT *
        , n = ROW_NUMBER() OVER(PARTITION BY [UserName] ORDER BY [StartTime])
    FROM data
    WHERE [Activityid] IN (3, 4)
)
SELECT l1.* , *
FROM list l1
LEFT JOIN list l2
    ON l1.UserName = l2.UserName AND l1.n+1 = l2.n AND l2.Activityid = 4
WHERE l1.Activityid = 3 AND l2.Activityid IS NULL
UNION ALL
SELECT l1.* , *
FROM list l1
LEFT JOIN list l2 ON l1.UserName = l2.UserName AND l1.n-1 = l2.n AND l2.Activityid = 3
WHERE l1.Activityid = 4 AND l2.Activityid IS NULL;

Output 2:

Rownr   | UserName  | StartTime                     | Activityid    | Value | n
5       | Arnold    | 2015-03-07 11:03:20.4200000   | 3             | 5840  | 1
8       | Dimitri   | 2015-03-07 22:24:20.6330000   | 4             | 5817  | 3

Data used with an extra row for 4 without 3:

declare @data table
    ([Rownr] int, [UserName] varchar(8), [StartTime] datetime2, [Activityid] int, [Value] int)
;

INSERT INTO @data
    ([Rownr], [UserName], [StartTime], [Activityid], [Value])
VALUES
    (1, 'Anneleen', '2015-03-07 07:01:38.707', 3, 5811),
    (2, 'Anneleen', '2015-03-07 08:52:23.417', 4, 5811),
    (3, 'Anneleen', '2015-03-07 08:52:34.347', 3, 5811),
    (4, 'Anneleen', '2015-03-07 14:15:20.140', 4, 5811),
    (5, 'Arnold', '2015-03-07 11:03:20.420', 3, 5840),
    (6, 'Dimitri', '2015-03-07 07:02:53.633', 3, 5817),
    (7, 'Dimitri', '2015-03-07 21:24:20.633', 4, 5817),
    (8, 'Dimitri', '2015-03-07 22:24:20.633', 4, 5817);


What is an "empty query" in information_schema.CLIENT_STATISTICS? The client stats table has an "empty queries" column.  What does that mean?  Simple googling just tells me "The number of times this client's connections sent empty queries to the server."  /facepalm

Unfortunately this is not documented in the MySQL manual - the page would be this one.  I didn't know the answer, but I did manage to debug it from the 5.5 source:

It is queries to the server that do not produce SQL queries.  i.e.

laptop1113-2:~ morgant$ mysql -e "SHOW GLOBAL status like 'Com_empty_query';"
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Com_empty_query | 2     |
+-----------------+-------+
laptop1113-2:~ morgant$ mysql --comments -e '-- sql comment'
laptop1113-2:~ morgant$ mysql -e "SHOW GLOBAL status like 'com_empty_query';"
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Com_empty_query | 3     |
+-----------------+-------+

(You need to add --comments, because the command line utility by default will strip them.)

How to debug something like this in MySQL:

The COM_ variables mean commands - this is a clue that it's some sort of statement.  The code that generates SHOW GLOBAL STATUS is in ./sql/mysqld.cc (you could have found this if you didn't know by grepping for query and empty on the same line).  At ~line 3025:

{"drop_view",    (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_DROP_VIEW]), SHOW_LONG_STATUS},
{"empty_query",  (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_EMPTY_QUERY]), SHOW_LONG_STATUS},
{"execute_sql",  (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_EXECUTE]), SHOW_LONG_STATUS},

So this tells us that show global status is just reading the value of SQLCOM_EMPTY_QUERY.  We can then grep for this string:

    laptop1113-2:5.5 morgant$ grep -riF 'SQLCOM_EMPTY_QUERY' .
    ./mysql-test/r/implicit_commit.result:# SQLCOM_EMPTY_QUERY
    ./mysql-test/t/implicit_commit.test:--echo # SQLCOM_EMPTY_QUERY
    ./sql/mysqld.cc:  {"empty_query",  (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_EMPTY_QUERY]), SHOW_LONG_STATUS},
    ./sql/sql_lex.h:  SQLCOM_SHOW_WARNS, SQLCOM_EMPTY_QUERY, SQLCOM_SHOW_ERRORS,
    ./sql/sql_parse.cc:  case SQLCOM_EMPTY_QUERY:
    ./sql/sql_yacc.yy:            thd->lex->sql_command= SQLCOM_EMPTY_QUERY;

Ignore the tests, and the file we just looked at.  The only other files mentioned here are those that relate to the parser (yacc).  If we open sql_parse.cc, we just see a return with nothing of interest:

case SQLCOM_EMPTY_QUERY:
my_ok(thd);
break;

If we open sql_yacc.yy, we can see that this somehow relates to being at the END_OF_INPUT, and no query was found:

query:
      END_OF_INPUT
      {
        THD *thd= YYTHD;
        if (!thd->bootstrap &&
          (!(thd->lex->select_lex.options & OPTION_FOUND_COMMENT)))
        {
          my_message(ER_EMPTY_QUERY, ER(ER_EMPTY_QUERY), MYF(0));
          MYSQL_YYABORT;
        }
        thd->lex->sql_command= SQLCOM_EMPTY_QUERY;
        YYLIP->found_semicolon= NULL;
      }
    | verb_clause

From here, I pretty much made a guess.  Everything is handled at the parser, and never enters other components of mysqld.


Covering and overlapping constraints Given that there are 15 entities in E1, 20 entities in E2 and 25 entities in E3, what's the maximum and the minimum number of entities in E0? It's also known that the ISA hierarchy follows the covering constraint but not the overlapping constraint.

I get that the maximum number would be 60, but I'm confused when it comes to the minimum value.

What I'm confused about is if E0 can have 0 entities (or fewer than 15+20+25=60 entities). I understand that unioning all entities in E1, E2, and E3 will give us all entities in E0, but can it be empty too?

Thanks.

The generalization

follows the covering constraint, so: E1 ⋃ E2 ⋃ E3 = E0
does not follow the overlapping 'constraint' ⇒ is disjoint,  so: E1 ⋂ E2 ⋂ E3 = ∅

The maximum and minimum amount of entities in E0 is 60. Since no overlapping is allowed there will be 15 + 20 + 25 = 60 distinct entities, which are all entities of superentity E0 at the same time because of the covering constraint.

E1 ⋃ E2 ⋃ E3 = E0 ⇒  max{|E1|, |E2|, |E3|}  ≤ |E0| ≤ |E1| + |E2| + |E3|

(E1 ⋃ E2 ⋃ E3 = E0  ⋀ E1 ⋂ E2 ⋂ E3 = ∅) ⇒ |E0| = |E1 + E2 + E3|


Cassandra Consistency Level I have 3 virtual Cassandra nodes cluster.
Node1, Node2 & Node3.

I have written a small python code to bulk load data into it.

1st time only 2 nodes out of three are available online, Node1 & Node2 when I execute the python code with

session.default_consistency_level = 2

I got and error:

cassandra.Unavailable: Error from server: code=1000 [Unavailable exception] message="Cannot achieve consistency level TWO" info={'consistency': 'TWO', 'required_replicas': 2, 'alive_replicas': 1}

After that I brought my third node (Node3) online and executed the python program, it executed successfully.

As I understand Consistency Level = 2 means at least 2 nodes out of 3 should be available every time.

Thanks!

Consistency level refers to the number of replicas required, but the number of total replicas that exists is independent of the number of nodes. It is set by your Replication_Strategy. If your strategy for this keyspace only has 2 replicas then there are many situations where a CL of 2 would fail.

For nodes A, B and C

Imagine the data you want has been replicated to A and B.

But only A and C are alive. You can only get one replica even though 2 nodes are alive.


Trans Log fills during delete, conceptual question I'm learning everything I can about backups and transaction logs and such. I want a little feedback on if I'm understanding this, so I have a conceptual question. First, here is what I observed:

I did a massive delete of data (using SQL Management studio, typed the delete command manually), so much that the trans log filled up. The query appeared to keep running even when the trans log got full. I then cancelled the query, and it showed cancelling.

I then did a trans log backup, to clear the trans log, followed by a full backup (while the query was still cancelling). I got the expected error that the trans log was full and backup couldn't happen. I waited until the query finally stopped, then was able to do another trans log backup, and this time the full backup worked.

All of this was done on a test DB, as part of a tutorial on doing backups, to help teach about what the transaction log does. I was told it would behave this way, and my observation matched what I was told as part of the tutorial.

My conceptual question:

The trans log wouldn't backup and clear while the query was cancelling because the data in the trans log was being used to undo the delete, but once the delete was undone, the trans log was freed up to be cleared. Is that correct?

Thanks in advance!

As Dan Guzman originally mentioned in a comment, your understanding of why the active portion of the transaction log couldn't be cleared is correct (it was being used for the large rollback).

There is one thing of interest that I think is worth mentioning related to this:

  The query appeared to keep running even when the trans log got full. I then cancelled the query, and it showed cancelling.

Whenever log records are written to the transaction log, additional space is reserved to accommodate a rollback in case an error is encountered (or an explicit rollback is requested).  This is documented in the MS Docs article SQL Server Transaction Log Architecture and Management Guide, specifically in the section on "Transaction Log Logical Architecture."

When the transaction log looked full, the query execution encountered and error and automatically started rolling back.  Had you waited for this to complete, you would have seen this error message:

  Msg 9002, Level 17, State 4, Line 1
  The transaction log for database 'YourDatabaseName' is full due to 'ACTIVE_TRANSACTION'.


Oracle DBA - what's the difference between the connection strings? I'm working on a .NET application that is supposed to connect to any OracleDB but I'm a bit stumped, trying to get it to work on different versions.

I originally started with Oracle 11g (Express), trying to connect was actually rather easy, I simply used the following Connection String and it worked:

Data Source=hostname:port;User Id=myUsername;Password=myPassword;

I then tried to connect to a 12c and it told me that I didn't specify the service name. This confused the shit out of me because I never had to specify the Service Name in 11 but I looked online and found another Connection String in this format:

SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort))
(CONNECT_DATA=(SERVICE_NAME=MyOracleSID))); uid=myUsername; pwd=myPassword;

It still didn't work. A colleague gave me the tip to use the "Global Database Name" instead of the SID (ORCL.domainname.local instead of ORCL) and it worked after we added ORCL.domainname.local to our Domain Controller's DNS Server's forward lookup zones.

I then went back to 11g did the same things there but turns out using the new Connection String there doesn't work.

While we could probably implement some kind of "trying until it works"-feature in our connection string builder (user specifies the data) this seems like a terrible solution and I don't think it's a good idea. Also at this point we really want to know WHY so we can tell if it's a configuration issue or an absolute version issue.

Anyone care to give me some insight?

I have an answer for this right here:

What database name should I use to connect Tomcat with my database

Essentially there's 2 ways you can connect to the DB - with the SID or a Service Name.  In Oracle 12c and up, the option for connecting via SID is essentially gone away.

A SID is not a good way to identify a database, all it really does is identifies a particular Oracle instance running in a particular Oracle Home.

You should be using service names to connect in 11g+.


Oracle 12.1 string literal on varchar2 with trailing space I'm having a very specific issue with Oracle 12.1 concerning the handling of string literal ending with space in SQL WHERE clause applied on a VARCHAR2(2500) column (named 'NOTES').

As I need Case-and-Accent-Insensitive string comparison, I alter the NLS_COMP and NLS_SORT in a Logon Database trigger:

ALTER SESSION SET NLS_COMP='LINGUISTIC';
ALTER SESSION SET NLS_SORT='BINARY_AI';

Then, if i try this (notice the ' ' space at the end of 'cholera', it is needed as the space is present in the table data, and I can't change it as I only have read-only access to the table):

SELECT NOTES FROM DECCODESIDC WHERE NOTES='cholera ';

So far, so good, it returns the only row matching the criteria ('Choléra ')

But if i create a View based on the table, and I apply the same criteria, it doesn't return anything:

SELECT NOTES FROM (SELECT NOTES FROM DECCODESIDC) WHERE NOTES='cholera ';

I noticed that the explain plan is different between the two queries

Here is the first query explain plan:

Notice the 6 last digits of HEXTORAW : 61 20 00   -> 61='a', 20=' ' *(space)*, 00=end

And the second one:

Notice the 6 last digits of HEXTORAW : 72 61 00   -> 72='r', 61='a', 00=end

As we can see, the HEXTORAW function automatically added by Oracle remove the trailing space in the second query, but not in the first...

I'm aware that string literals are considered as CHAR by Oracle and are subject to space padding, but in this case the string literal is compared against a VARCHAR2 type column... and it doesn't explain why the execution plans are differents ...

Am I missing something or is it a bug in Oracle?

Benoit

ps: the fact is that I don't write such queries by hand, but rely on Entity Framework with Oracle Managed Drivers, so I don't have so much options concerning query rewriting :(

ps2: As a temporary workaround, I added a call to the TRIM oracle function on every VARCHAR2 column in my View, but it is suboptimal in term of performance...

For anyone facing the same problem: I applied patch bundle 12.1.0.2.170117 and everything seems to be ok now... :/


Most efficient table structure for multi-tenant database I have a conventional relational database (mySQL) with several entities stored in their own table. Three examples are images, events and organisations.
We are making a change to the way our site works - from now on Clients will upload / add their own data, which will only be accessible to them. We store the Client data in the client table, each client has an id.
There are 3 simple ways I can think of to design this, but I want the most efficient, as some calls will have to select 10,000+ records for that Client (in the case of a large world map of events, for example). So which if these ways should I use to ensure that all data a Client "owns" is recalled with maximum efficiency?

Many to many relation to each type of entity. -

client.id ... | client_id, image_id | image.id ...
(and create a many to many table for each of the dozen entities involved). V simple, but could get in a tangle as we add more and more entity types, many of which have relations of their own. New many to many tables will need to be added regularly

Polymorphic association one table to associate client_id with any type of entity. Most efficient in terms of layout but slow to find all entities belonging to one client.

client.id  ... |  client_id, entity_type, entity_id  |  image.id ...

Add owner (client_id) column directly to each entity table

client_id, image_id, image_name etc... (all in the image table, same for each table)
Is there any reason not to add the client ID directly? It would mean storing a few more integers than the other methods but would be extremely fast to select all images 'owned by' a client (could be tens-of-thousands+)

A typical implementation of "multi-tenant" is to have a database for each client.

Each database has the same few dozen tables.
Security (if you care) is somewhat available at the database level.
If your product becomes insanely popular, sharding is relatively easy to add (each server would have several clients).

A downside is the thousands of tables in the whole system.
There would be an extra database for admin stuff -- list of users, their passwords, etc.
If you implement with a single set of tables, then

Client_id would be in many of the tables.
Most indexes would be composite and start with client_id.
Many:many tables may or may not avoid including client_id, depending on whether the Entities have their own id.

Prototype now; analyze; but plan on a major redesign of the schema 6 months from now.  Without the overhaul, you will forever be swearing at yourself for making the wrong decision today.  And your product will die because it could not flex to meet future needs.


MongoDB Synchronizing multiple Shards I've got the following layout Picture Cluster
RZ West is my first data center A and RZ Ost my second data center B. As you can see one secondary database of each cluster is available in the other data center.
Is it possible to read data out of Secondary 2 OST (B) and add it to the master database Primary WEST (A) automatically?
To understand the question this solved question could be helpful https://stackoverflow.com/questions/22988773/mongodb-multiple-masters-in-replicaset
Thank you very much in advance
Greetings,
Daniel

If it's not possible to do it automatically, can it be done manually? So only entries which are not present in the primary database A will be inserted out of one secondary of database B ?

Little Example: In A we got: 1,2,3 In B we got: 5,6,7

What I want is to add 5,6,7 from database B into database A and vice versa, so it looks like this at the end:

A: 1,2,3,5,6,7

B: 5,6,7,1,2,3


What is the equivalent of a "Certificate of Destruction" when using a cloud-hosted database? We have a Rails application hosted by Heroku and using an Heroku Postgres database. Some of the information we store is considered to be sensitive by our users, and we've been asked if we can provide a Certificate of Destruction in the event they wish to delete their account. In other fields (e.g. law) CODs are apparently common practice, issued for instance after sensitive paper documents are shredded or otherwise destroyed.

So I have three questions:

1) When we issue the ActiveResource::Base#destroy command on a record, is the data truly destroyed? We keep backup snapshots with Heroku's PG Backups, so even if the original command truly destroys the data, its probably still in the backups. Without deleting the backups, is it even possible to destroy completely the client's data?

2) Is there an equivalent to a Certificate of Destruction for this purpose?

3) If there is not an equivalent, how have other people mitigated or otherwise assuaged similar client concerns?

1) When we issue the ActiveResource::Base#destroy command on a record,
  is the data truly destroyed?

Sure not.

  We keep backup snapshots with Heroku's PG
  Backups, so even if the original command truly destroys the data, its
  probably still in the backups. Without deleting the backups, is it
  even possible to destroy completely the client's data?

Sure not.

  2) Is there an equivalent to a Certificate of Destruction for this
  purpose?

No. Ask your cloud provider but 99.99% = no.

  3) If there is not an equivalent, how have other people mitigated or
  otherwise assuaged similar client concerns?

Full data layer encryption.  Tricky but possible.


Oracle Data Pump Export If I want to export all the tables in a schema, can I just specify the schema parameter in the parfile or do I also need to list out all the tables individually in the tables parameters? I tried specifying just the schema parameter but the export keeps failing, so I want to confirm whether there is another mistake I am making or if it's because you need to explicitly specify the tables that you want to export.

Thanks!

It is enough to specify the schemas parameter.

expdp \'/ as sysdba\' schemas=siddhu

If you want to export only the tables, you can also add include=table:

expdp \'/ as sysdba\' schemas=siddhu include=table

The above exports the tables with indexes and triggers, but it skips procedures, sequences and other objects not directly related to tables.


InnoDB Table has backed up INSERT and UPDATE queries, high CPU I am running an MySQL DB with all InnoDB tables. I often have many queries backed up for the same table when I run SHOW PROCESSLIST;

Basically, I keep a table of every logged in customer and update their location periodically. During busy times of the day, I will see this backup where the time to execute is well over 5 minutes. Eventually, they will all process through and the DB CPU and Connections will normalize (CPU will hover around high 90%s).

The queries I run on this table are INSERT and UPDATE only, and those are the ones that will randomly get backed up during a high traffic time of day. I can't seem to figure out why this happens, although it always happens between the 3 hour block of our busiest time.

Here is my database create statement:

CREATE TABLE `active` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`public_post` tinyint(1) NOT NULL,
`customer_id` int(11) NOT NULL,
`order_type` varchar(75) NOT NULL,
`clocked_in` tinyint(1) DEFAULT NULL,
`auto_clock_out` datetime NOT NULL,
`clocked_out` datetime NOT NULL,
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`lat` varchar(60) NOT NULL,
`lng` varchar(60) NOT NULL,
`region_id` int(11) DEFAULT NULL,
`description` varchar(140) DEFAULT NULL,
`date` varchar(15) NOT NULL,
`time` varchar(10) NOT NULL,
PRIMARY KEY (`id`),
KEY `clocked_in` (`clocked_in`),
KEY `lat_lng` (`lat`,`lng`),
KEY `customer_id` (`customer_id`),
KEY `region_id` (`region_id`),
KEY `public_post` (`public_post`),
KEY `auto_clock_out` (`auto_clock_out`),
KEY `order_type` (`order_type`),
KEY `clocked` (`clocked_out`,`clocked_in`)
) ENGINE=InnoDB AUTO_INCREMENT=222952 DEFAULT CHARSET=latin1;

Here are the results of SHOW PROCESSLIST;

104224  proddb  172.31.52.67:55238  api Query   848 update  INSERT INTO active (customer_id, clocked_in) VALUES('188878', 0)
105154  proddb  172.31.52.68:53930  api Query   829 update  INSERT INTO active (customer_id, clocked_in) VALUES('188879', 0)
107401  proddb  172.31.25.4:46032   api Query   784 update  INSERT INTO active (customer_id, clocked_in) VALUES('188880', 0)
108101  proddb  172.31.18.216:54461 api Query   771 update  INSERT INTO active (customer_id, clocked_in) VALUES('188881', 0)
108567  proddb  172.31.52.67:56045  api Query   763 update  INSERT INTO active (customer_id, clocked_in) VALUES('188882', 0)
109002  proddb  172.31.32.221:41667 api Query   755 update  INSERT INTO active (customer_id, clocked_in) VALUES('188883', 0)
114304  proddb  172.31.52.67:57048  api Query   659 update  INSERT INTO active (customer_id, clocked_in) VALUES('188884', 0)
114779  proddb  172.31.52.68:55762  api Query   651 update  INSERT INTO active (customer_id, clocked_in) VALUES('188885', 0)
118410  proddb  172.31.18.216:56459 api Query   588 update  INSERT INTO active (customer_id, clocked_in) VALUES('188886', 0)
119345  proddb  172.31.52.68:56702  api Query   567 update  INSERT INTO active (customer_id, clocked_in) VALUES('188887', 0)

Here are some statistics on the number of temporary tables and threads being created during a high traffic time I found while trying to diagnose.

| Created_tmp_disk_tables | 168686 |
| Created_tmp_tables      | 599149

| Threads_connected | 232 |
| Threads_created   | 21566 |
| Threads_running   | 107 |

Many things can be done to speed things up...

Use FLOAT or DECIMAL, not VARCHAR for latitude and longitude.  (This is one of many things to shrink the record size.)
Do not INDEX boolean values like clocked_in and public_post.  (The optimizer is unlikely to ever use the index.  And it is costly to update.)
Don't split DATE and TIME; use DATETIME instead of VARCHAR.  (Shrink and ease of use.)
Don't have a table with lots of fields, then spend most of your time INSERTing only two columns.  (Perhaps you meant UPDATE?)  Perhaps have a separate table with the log of clock-in/out?
How many different values are there of order_type?  (Consider using an ENUM or "Normalizing" that field.)

Was that the entire PROCESSLIST?  Or was something other than 2-column INSERTs operating on that table?

How much RAM do you have?  What is the value of innodb_buffer_pool_size?


Cannot reproduce client SQL 2008R2 error: subqueries are not allowed EDIT: My question is not "how do I work around the failure?", my question is "why is the failure occurring?"

We have a client that reports error "1046 subqueries are not allowed in this context. Only scalar expressions are allowed" for SQL Server 2008 R2.  We cannot reproduce in house.  Is there a server setting or database configuration option that can cause this?

-- Simplification of tables looks like this

create table foo_type(
    foo_type_id int not null,
    foo_type_name nvarchar(100) not null,
    primary key (foo_type_id)
)

insert into foo_type values (1, 'type one')
insert into foo_type values (2, 'type two')

create table foo(
    foo_id int not null,
    foo_type_id int not null,
    foo_name nvarchar(100) not null,
    primary key (foo_id),
    constraint fk_foo_to_foo_type
    foreign key (foo_type_id) references foo_type
)

insert into foo values (11, 1, 'foo one')

-- and the mysteriously failing insert

insert into foo values (
    (select coalesce(max(foo_id),0) + 1 from foo),
    2,
    'foo two'
)

Are you 100% sure the (select coalesce(max(foo_id),0) + 1 from foo) gives one row? You said it was simplified code so this may not be the case.

This should be an IDENTITY column because 2 processes can insert the same row and you don't require an aggregate over the current data.

Anyway, how to fix it. Change to this (MAX without GROUP BY always gives one row)

insert into foo values (
select
    coalesce(max(foo_id),0) + 1,
    2,
    'foo two'
from
    foo WITH (ROWLOCK, UPDLOCK, HOLDLOCK)

To test on the client, add this immediately after

SELECT @@ROWCOUNT

Note: the hints reduce (not remove) the chances of the same value being inserted.


Get devices with not driver assigned I have this related tables:

And I need to get those devices with no driver assigned, in other words the device returned on this query shouldn't be on driver_has_device table since it'll not have driver assigned, can any give me some help on this query?

In SQL Server, I would write the following query:

SELECT *
FROM dbo.driver d
    LEFT JOIN dbo.driver_has_device dhd ON d.id = dhd.driver_id
WHERE dhd.driver_id IS NULL;

Essentially, this returns all rows from drivers that do not have a corresponding row in driver_has_device.


What are each of the separate mongodb-org packages for ubuntu? I am installing mongodb on a local ubuntu vm and I'm using the official Mongo supported packages per the official instructions. However, I don't know what each of these packages contain. I know this is a merely informational question, but I've googled and not found the answer.

mongodb-org
mongodb-org-server
mongodb-org-shell
mongodb-org-mongos
mongodb-org-tools

What do each of these packages provide? Thanks!

Please find below the details of each item from MongoDB official documentation.


Apply SQLServer Service Pack to a Single Node Cluster I have to apply a SQL Server 2008 R2 Service Pack to a Single Node Cluster.

The other node died and new hardware has been ordered.

Is there anything weird to worry about?

Normally we'd apply the service pack to the passive node first, then failover, and apply the service pack to the newly passive node, however this clust only has 1 node. We're loading Service Pack 2 so we can upgrade to SQL Server 2014.

Nothing particular to worry about, but you will incur downtime as the SP runs its stuff in "script mode" during which users cannot connect.

It might go without saying, but I'd ensure you have a valid (i.e. tested) backup of the databases (including the system databases) on the affected system prior to doing any patching, whether on a single-node cluster, a multi-node cluster, or even a stand-alone instance.  You can never have too many backups.


To find out last activity/access date and date of expiration for all SQL Server 2012 User login Apart from enabling the Audit on the SQL server, is there any other way we could find out the last activity/access date and date of expiration for all SQL Server User login ?
Also using DMVs wont work as the information provided by them are limited.

For logins, only things that are like server audits.

A SQL trace to capture logins. This is more intensive than using an audit though.
An extended event session to capture the logins.
You could cobble up a login trigger to record logins to a table, but this is probably the worst of them all.

For expiries you can use Loginproperty('login_name', 'DaysUntilExpiration') but I think this is for SQL logins only. You may need to run a separate PowerShell script to determine the same for AD logins.

Neither of these will help when it comes to an AD account access through an AD group. For those it's much more complicated, either through impersonating each login and selecting the list of activate groups from sys.login_token and recording it all to a table; or cycling through AD with PowerShell again.


I want to find out how data is getting inserted to a particular table? I want to find out how data is getting inserted to a particular table.

I mean to say I want to find out the job or Stored Proc which is running on my SQL Server and pushing data regularly into one of my tables.

Copied from a Stack Overflow answer by Damian Leszczyński - Vash, could not find a duplicate here:

You can't set filter to trace specific table directly.
What you can do is specify a filter for Text Data:

File -> Properties -> Event Selection -> Column Filters -> TextData -> Like -> [Table Name]

Your other option is to use Extended Events, but it has a much higher learning curve.  Be careful using Profiler in production. The way it reads the data has a very high overhead and it could also cause the client you're using to time out.
You could use a 'server side trace' but that also has a higher learning overhead.  Use Profiler in prod at your own risk, and make sure you don't run the client on an important server.


Can a column be referenced from multiple table as a foreign key Suppose I have a table named Service.
But I have other tables that have a similarities with that table
like RoomService, ConsultatonService, etc.

Can the Id of RoomService and ConsultationService keep a foreign key reference to Service table's ServiceId column but being identifiable by the Type column in the Service table?

Yes, you can do it. Your services table will store all the common attributes between your sub-classes.

If you intend to avoid 1:1 relationships, you'll have to create, for example, an ServiceId column and do the relationship with it, using Id as PK of RoomService. By the way, there is no problems with 1:1 relationships, specially in your case, where you are creating specialized sub-classes.

In addition, the type column will be used by your application to determine which subclass the object is. Also no problem on that.


How to see today compiled object in Oracle? Is there a way to find objects that were compiled today?

I think the timestamp column of dba_objects is what you are after:

create table t(id integer);
create function f return integer as
  n integer;
begin
  select count(*) into n from t;
  return n;
end;
/

select status, timestamp from user_objects where object_name='F';
STATUS  TIMESTAMP
------- -------------------
VALID   2011-09-05:11:01:52

insert into t(id) values (1);
select f() from dual;             -- ***no recompile needed***

select status, timestamp from user_objects where object_name='F';
STATUS  TIMESTAMP
------- -------------------
VALID   2011-09-05:11:01:52

alter table t add dummy integer;  -- ***invalidates f()***

select status, timestamp from user_objects where object_name='F';
STATUS  TIMESTAMP
------- -------------------
INVALID 2011-09-05:11:01:52

select f() from dual;             -- ***forces recompile***
select status, timestamp from user_objects where object_name='F';
STATUS  TIMESTAMP
------- -------------------
VALID   2011-09-05:11:01:53       -- ***note the different timestamp***

so to get all functions compiled today for example:

select *
from dba_objects
where to_date(substr(timestamp,1,10), 'yyyy-mm-dd')>=trunc(sysdate)
      and object_type='FUNCTION';

--edit

it may be safer to use last_ddl_time instead (see @Alex's comment below), but the caveat is that last_ddl_time is also changed on grants and revokes.


MongoDB: The collections metadata lock is taken Running a mongodb 2.6 cluster on localhost with four shards and four replication sets.

Using a for loop with 100 iterations that pre-splits chunks among the existing shards. I am getting the error The collections metadata lock is taken after a couple of minutes

mongos> for( var i=0; i < 100; i++) { sh.splitAt("test_cluster.presplit_demo", { x:1000000 * i / 100 } ); }
{
    "cause" : {
        "who" : {
            "_id" : "test_cluster.presplit_demo",
            "state" : 2,
            "who" : "localhost.localdomain:27000:1402761131:1123537020:conn19:1622303703",
            "process" : "localhost.localdomain:27000:1402761131:1123537020",
            mongos&gt; for( var i=0; i &lt; 100; i++) { sh.splitAt("test_cluster.presplit_demo", { x:1000000 * i / 100 } ); }
{
    "cause" : {
        "who" : {
            "_id" : "test_cluster.presplit_demo",
            "state" : 2,
            "who" : "localhost.localdomain:27000:1402761131:1123537020:conn19:1622303703",
            "process" : "localhost.localdomain:27000:1402761131:1123537020",
            "when" : ISODate("2014-06-14T15:56:16.439Z"),
            "why" : "migrate-{ x: 360000.0 }",
            "ts" : ObjectId("539c70a07b296ac1c721eca4")
        },
        "ok" : 0,
        "errmsg" : "the collection's metadata lock is taken"
    },
    "ok" : 0,
    "errmsg" : "split failed"
}

I see the lock in config: db.locks.find(), removed the lock but it just comes back. If I reduce the number of iterations to 50, the for loop completes successfully. There is more than enough disk space.

Question

Anyone else run into this issue with pre-splitting and what did you do to resolve it?

Turn off the balancer first, you are likely seeing it trying to balance the collections while you are attempting to split it.  You can do this with the sh.stopBalancer() command.  If you take a look at this answer I go into the necessary steps for pre-splitting in more detail.


Group multiple rows with different column values to a single row I have 3 records with 3 column values for the same key.
PFB sample data.

CREATE TABLE student
(
    student varchar(100),
    maths int,
    physics int,
    chemistry int
)
PRIMARY INDEX (student)
ON COMMIT PRESERVE ROWS;

INSERT INTO student('Student_100', 90,   ,   );
INSERT INTO student('Student_100',   , 80,   );
INSERT INTO student('Student_100',   ,   , 70);

Now, for student_100, there are 3 records with 3 column values.
I want to get output like below

StudentID   | Maths | Physics | Chemistry
------------+-------+---------+----------
Student_100 |  90   |   80    |    70

A single record with all column values. I don't want to use SUM function, because I have columns with Date type as well. So I am looking for a solution that fits all datatypes.

You should be able to get the desired result GROUPING BY student and getting max value of each subject.

You can find some examples of MAX function on TERADATA here or here.

SELECT   student as StudentID,
         MAX(maths) as Maths,
         MAX(physics) as Physics,
         MAX(chemistry) as Chemistry
FROM     student
GROUP BY student;


How do I get only those values that has related elements from other table? I have three tables for a social network: companies, users and countries. Users belong to Companies and Companies have a Country. All tables have their own id column.

Table users has a company_id FK column
Tables companies has a country_id FK column

The thing is that because of some administrative stuff (Duplicated companies, different names for the same company, etc.) the database has companies with no users (we are actually cleaning all this moving the guys to the companies they really belong to).

I need to get only those companies that actually has users related to it.

Something like

select c.id, c.name, count(co.country_id) as amount from countries as c
left join companies as co on co.country_id = c.id
left join users as u on u.company_id = co.id
where "COMPANY HAS USERS RELATED TO IT"
order by amount desc";

For now I had no problems getting the amounts for each country. But now I need those amounts to only count companies with users related to it. Something that can allow me to avoid having to use a second query inside the "while" with an "if > 0".

By the way, the query starts like that because I'm actually making a list that goes like:

Country (amount of companies)

Do it as GROUP BY aggregation

select
    c.id, c.name,
    count(distinct co.country_id) as amount,
    count(distinct u.id) usercount
from countries as c
left join companies as co on co.country_id = c.id
left join users as u on u.company_id = co.id
group by c.id, c.name
order by amount desc;


DB2 TRUNCATE command - DROP STORAGE option I performed action TRUNCATE TABLE TABLENAME IMMEDIATE; but I did not used DROP STORAGE option. I read that it is default option if it is not put.
It was not so big table regarding table space but I am still interested if command I executed released database disk space on the server or not? I am not able to conculde that based on current disk space.

I would like confirmation for this?
Also I would like to know did I have to do DB2 REORG for this table after the TRUNCATE?

You can use LIST TABLESPACES SHOW DETAIL to check the High Water Mark. If it still appears high, you can always issue an ALTER TABLESPACE <tablespace> REDUCE MAX (assuming you are on automatic storage). The commands for old-style DMS are a litte different.

I have another answer where I list that information


Is it safe to pg_dump and pg_restore a new postgres database that has malware? I'm pretty sure there is a crypto bot eating up my CPU through a postgres script. I would like to create an entirely new VM, and move my database with it using pg_dump and pg_restore. I already checked my postgres for new users, tables, databases; couldn't find anything odd there which could comprise me if I move my data. I'm a little worried however because the bot is some how getting access to my postgres, and nothing else on my VM.
Thank you for the help.
EDIT:
I managed to move my db with pg-dump and pg-dumpall. It's been a few days since I destroyed my last VM and made a new db with better security. It looks like I'm in the clear at least for this same attack.

Probably.  But if you just recreate the same vulnerable setup, you will just get infected again from scratch.  They probably got in by guessing the weak password on a super-user account.  So set up all new passwords on the new system and make then strong ones. (pg_dump will not carry over your old passwords anyway, but pg_dumpall will.). It would also be good to lock down your new pg_hba.conf so that superusers can only connect locally, or at least only from white-listed IP addresses.
They probably created a new function which gives them shell access, and pg_dump will carry over that function definition.  But if they can't log in in the first place they can't use it anymore; and it probably relies on a dropped binary .so file, which pg_dump will not carry over.


Do Log Backups depend on Differential Backups? I have the following backup strategy set up:

My understanding so far, was:
Differential backups do not truncate the transaction log, so, there's no relation between Diff and Log Backups and both could co-exist in parallel without affecting each other.
Based on this understanding, I thought that I would have two alternatives to perform a complete restore to the most recent state.
Alternative #1:

This alternative #1 works as expected with no issues.
Alternative #2:

This alternative #2 shows the following error.

Msg 4305, Level 16, State 1, Line 53 The log in this backup set begins
at LSN 144000000122400001, which is too recent to apply to the
database. An earlier log backup that includes LSN 73000000188800001
can be restored.

After analyzing the backup history, I can conclude that if there are Differential backups prior to Log Backups, the latest Differential backup MUST be restored before restoring backups.

So far, I have always thought that since Full/Diff backups do not affect the chain log, Differential backups were mostly meant to shorten recovery time by reducing the number of log backups to be restored.
However, as shown here, perform a complete restoration based only in log backups is not being allowed if DIFF backup hast not been restored, so, my question is:
What is the reason behind DIFF backup being required to allow LOG restores in Restore Sequence #2?

Differential backups do not truncate the transaction log, so, there's no relation between Diff and Log Backups and both could co-exist in parallel without affecting each other.

This is correct understanding. In full recovery, ONLY a transaction log backup will  allow log truncation.

I have always thought that since Full/Diff backups do not affect the chain log, Differential backups were mostly meant to shorten recovery time by reducing the number of log backups to be restored.

Yes, this is correct as well. Infact, when a log shipping is broken (e.g. some process takes a NON COPY_ONLY log backup), the fastest way to rebuild log shipping is to take a differential backup and restore it to secondary and then take a log backup to establish the log chain.

there's indeed a strong dependency between transaction logs and differential backups. In fact, I wouldn't be able to restore everything If I had lost the latest Differential backup

NO. This is not 100% correct. Diff and t-log backups are independent.
Diff will allow you to skip the log backups that you will need to restore. But to do a Point-in-time restore, you will need a log backup (with STOP AT command to do a point-in-time restore).
For more details, refer to my answer - Log backup vs differential backup if latter is broken
sp_RestoreGene will automatically construct the right sequence for you.

What is the reason behind DIFF backup being required to allow log restores in the Restore Sequence #2?

This is the minimal repo that proves that you do not need a diff backup to allow log restores in the restore sequence.


Does mysqldump use in memory pages during backup? I mean when backup command is issued to database, does backup process read all data pages from disk or use in memory pages?

By "in memory", I assume you are referring to cached data and index blocks.

mysqldump issues a bunch of SELECTs, which use the caching that is available to any other SELECTs.  Since it does not dump the indexes, most cached index blocks do not help the the dump process.

Xtrabackup has some extra hooks into InnoDB to help achieve a 'consistent' dump in a less intrusive way.  But still the caching is in effect.


How to do a junction table referencing constraint? I want to create a questionnaire which matches some questions together.
CREATE TABLE questionnaire(
 question_id int  PRIMARY KEY
 question_name varchar
 question type int
 -- some other columns
)
CREATE TABLE mapping(
 question_id_type1 int
 question_id_type2 int
 CONSTRAINT fk_qid_type1
  FOREIGN KEY (question_id_type1)
  REFERENCES questionnaire(question_id)
 CONSTRAINT fk_qid_type2
  FOREIGN KEY (question_id_type2)
  REFERENCES questionnaire(question_id)
)

How do I enforce that question_id_type1 only has values in questionnaire that has a question_type = 1 and question_id_type2 only has values in questionnaire that has a question_type = 2.
I know that creating two tables to replace questionnaire i.e.
CREATE TABLE questionnaire_type1(...)
CREATE TABLE questionnaire_type2(...)

would solve the problem however i have some other columns/referencing keys in questionnaire that would be repeated in both tables. May i know what is the best way to solve this problem?

In questionnaire, add a key that includes the ID and the type so that it can be referenced.
CREATE TABLE questionnaire
             (question_id integer,
              question_type integer,
              ...
              PRIMARY KEY (question_id),
              UNIQUE (question_id,
                      question_type));

In mapping add columns for the types. Use check constraints to enforce they are 1 or 2 respectively. In the foreign key constraints, let the pair of ID and type reference the additional key of questionnaire.
CREATE TABLE mapping
             (question_id_type1 integer,
              question_type_type1 integer
                                  DEFAULT 1,
              question_id_type2 integer,
              question_type_type2 integer
                                  DEFAULT 2,
              ...
              CHECK (question_type_type1 = 1),
              CHECK (question_type_type2 = 2),
              FOREIGN KEY (question_id_type1
                           question_type_type1)
                          REFERENCES questionnaire
                                     (question_id,
                                      question_type),
              FOREIGN KEY (question_id_type2
                           question_type_type2)
                          REFERENCES questionnaire
                                     (question_id,
                                      question_type));


Always On Availability Groups as a Secondary Read Only Database I am working on setting up a secondary copy of our prod DB for Data Warehousing and even something we can possibly use to offload other processes and reporting on. Basically just a read only copy of prod. Issues is, I am running into options that breaks our point in time backup strategies and that can reliably handle 20 TB of data.

After reading this post, Always On Availability Groups looks promising. But I have a few questions and I am new to DBA and most of Microsoft's documentation on the subject is another language to me at this point.

Does Always On Availability Groups mean the database will basically be mirrored and that I can use that server to offload the heavy reporting to?
Does that break point in time transaction logs backup of the prod server?
Can this cause a strain on the prod server?
Will this cause prod traffic to be redirected to this server? I do not want this to become HA DB server, just want a read only copy of a few prod DBs.

The secondaries can be used as read only. Keep in mind data is still being added to the secondaries in near real time.
Transaction log backups are conducted against the primary AG instance.
If the secondary can not keep up with the volume of transactions coming across, it can cause the transaction logs on the primary to fill and continue to grow.
In the event the primary becomes unavailable, traffic would then transfer to the secondary. This is the point of Availability Groups.

If you just want a copy of the database to run reports against, you might look into setting up log shipping to another instance. It's a cheaper, and simpler solution than AGs.


Spatial query to get enclosing radius/polygon of result set Is it possible in SQL to run a query of spatial data, and then just return a single polygon, or a radius, that encloses, aggregates, all of the spatial data that was in that query into one feature?

Yes, using SQL Server's

"Static Aggregate Geometry Methods"
"Static Aggregate Geography Methods"

You essentially have three options

ConvexHull: returns a polygon that essentially wraps the input with a rubber band,
EnvelopeAggregate: returns a bounding box (polygon) that encapsulates the input (box = lines in a cartesian project, arc segments in a 3d/spheroid/geographic projection)
UnionAggregate: takes all of the inputs and composes them into a single complex-polygon

Note: you mention radius, a radius is a buffer over an input in GIS.

If you need more features, you should check out the free PostgreSQL and PostGIS which supports even more GIS functionality.


Is each query batch in a dynamic SQL statement executed sequentially or can it be parallelized, when using sp_ExecuteSql? I have about 500 databases that are all structured the same but the data is unique in each database. Sometimes I write dynamic SQL statements to hit all (or a subset) of those databases and will execute it with sp_ExecuteSql.

Is SQL Server able to paraellelize each batch of queries and is there a way I can tell when it will parallelize them? (I guess I could print my dynamic SQL, and copy the query batches into a new worksheet and look at the execution plan to verify if the optimizer is planning on parallelizing them.)

Typically I'm trying to aggregate the data from each database into one temp table I declare outside of the dynamic SQL. Here's an example:

DECLARE @DynamicSql AS NVARCHAR(MAX) = ''

DROP TABLE IF EXISTS #Results
SELECT TOP 0 -1 AS DatabaseId, -1 AS ResultId, -1 AS ResultValue
INTO #Results

SELECT @DynamicSql = @DynamicSql +
'
    USE [' + T.DatabaseName + '];

    INSERT INTO #Results
    SELECT ' + CAST(T.DatabaseId AS VARCHAR(10)) + ' AS DatabaseId, T.ResultId, T.ResultValue
    FROM SomeTable AS T
    WHERE T.SomeCondition = ''SomeValue'';
'
FROM MyToolkit.dbo.vwDatabaseInstances

--PRINT @DynamicSql
EXEC sp_ExecuteSql @DynamicSql

SELECT *
FROM #Results

Looking at the execution plan, you can see that every query is done as a different query. Again, if you want to see if those query are using parallelism, you will see it in the execution plan.

If each of the query can go parallel, then they will.

Running this query agains StackOverFlow2013 DB:

  create table #tbl (tbl varchar(30));
  insert into #tbl values ('Comments'),('Comments')

  DECLARE @DynamicSql AS NVARCHAR(MAX) = ''

  DROP TABLE IF EXISTS #Results
  SELECT TOP 0 -1 AS DatabaseId, -1 AS ResultId, -1 AS ResultValue
  INTO #Results

  SELECT @DynamicSql = @DynamicSql +
  'select top 5000 * from [dbo].['+tbl+'] where text like ''%err%'''
  FROM #tbl t

  EXEC sp_ExecuteSql @DynamicSql

  SELECT *FROM #Results

  drop table #Results
  drop table #tbl

you get an execution plan that does both select with parallelism.


Cursor Replacement for Newbies I'd like to know what the general replacement is for a cursor.  The general implementation of a cursor I see out and about is

DECLARE @variable INT, @sqlstr NVARCHAR(MAX)

DECLARE cursor_name CURSOR
FOR select_statement --essentially to get an array for @variable
                     --usually it's a subset of unique ids for accounts, clients, parts, etc

OPEN cursor_name
FETCH NEXT FROM cursor_name INTO @variable
WHILE @@FETCH_STATUS = 0
BEGIN
     SET @sqlstr = N'
     /* some query that uses '+ str(@variable) +' to do dirty work
     such as: go through all our accounts, if it''s some subset (possible new cursor),
     go through those accounts and connect this way,
     map those fields and add it to our big uniform table */
     '

     EXEC sp_executesql @sqlstr
FETCH NEXT FROM cursor_name INTO @variable
END

CLOSE cursor_name
DEALLOCATE cursor_name

Since so many people are anti-cursor (with a nod to SO: Why do people hate cursors) what is the general replacement for the general implementation (preferably SQL Server)?

There is no "general replacement" - you hid all the "dirty work" here so it's hard to tell if there is even a specific replacement in this case. There are certainly some specific cases where you're processing a set of rows one row at a time, whether using a cursor, or while loop, or any other iterative process, where converting to a set-based process that processes all the rows at once is much better. But there are other things that just have to be done one row at a time, like executing a stored procedure or some dynamic SQL per row, the same query across multiple databases, etc.

Cursor or not, the problems you're alluding to and linking are the same whether you use declare cursor or some other looping struct (see this post), and are irrelevant when the thing you have to do has to be done one row at a time anyway. So if you provide some specific details about what this cursor is doing, you might get some advice about how to remove the cursor (or that you can't), but your search for a magical eliminate-all-cursors approach that you can apply to all scenarios is going to be pretty frustrating for you.

The general advice for new people entering the language, IMHO, should be to always think about what you need to do to a set of rows, as opposed to what you need to do to each row in a set. The difference in the language is subtle, but crucial. If people think about the problem as a set of data instead of a bunch of individual rows, they might be less likely to use a cursor by default. But if they come from different types of programming - where iterative is the best/only way - other than simply teaching them that SQL Server isn't optimized to work that way, I don't know that there's any way to make that obvious or automatic.

Your question still asks for a general replacement, and I still believe there is no such thing.


how to judge sql performance through these two explain? If I give you 2 explain results from MySQL,how do you know which query is better?
the first sql explain
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sbtest2
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 700100
        Extra:
1 row in set (0.00 sec)

and the second sql explain
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 100
        Extra:
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: a
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: b.id
         rows: 1
        Extra:
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: sbtest2
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 700100
        Extra: Using index
3 rows in set (1.46 sec)

I think I will choose the first query because there are "a lot of" things to do in the second explain,but actually the second query is much faster than the first query , so my question is if I don't see the response time,how to judge their performance through explain ?
the first query's Duration: 4.43704325
select * from sbtest.sbtest2
order by id
limit 700000,100;

after restart mysql to clean cache and the second one is Duration: 2.08889400 (faster)
select * from sbtest.sbtest2 a
inner join
(select id from sbtest.sbtest2  order by id limit 700000,100 ) as b
on a.id=b.id;

table structure is above
mysql> show create table sbtest.sbtest1\G
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=5265529 DEFAULT CHARSET=utf8 MAX_ROWS=1000000
1 row in set (0.00 sec)

can anybody help me ?

additional
mysql> explain select id from sbtest.sbtest2  order by id limit 700000,100\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sbtest2
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 700100
        Extra: Using index
1 row in set (0.00 sec)

time is 4.28456200

EXPLAIN estimates row counts, often poorly.  Even so, the counts do not reflect how much stuff is hauled around.  If it is only id, that is not much.  If it is all of * (as in SELECT *), that is a lot more.  This is especially the case when it has to reach into the data to get all the columns.

Using index means that it is cruising through the index BTree.  Without that phrase, it is bouncing between the index BTree and the data BTree (using the PRIMARY KEY).  This bouncing is costly, and that cost is not adequately reflected in EXPLAIN.

Using index may be your only clue, but it cannot be trusted.  Sometimes it helps only a little, sometimes it helps a lot.

limit 700000,100 is an awful thing to do.  It must fetch 700100 rows (of just id or of *) but throw away nearly all of them.

Your complex query is the trick to make OFFSET perform somewhat better.  There is no way to say skip over 700000 rows without looking at them (which is what everyone wants).

Other notes:

Don't use CHAR, especially not with utf8; switch to VARCHAR.  char(120) consumes a fixed 360 bytes, regardless of the text.  The unnecessary bulk is a performance issue, especially if the table is bigger than can be cached.

More discussion of OFFSET.


How to get the first two octets in an IP address Oracle SQL E.g., IP = '10.11.12.13'
I want to extract '10.11.' or everything except what comes after the 2nd '.'

I know you have an accepted answer but just for completeness here's an option without REGEX;

SELECT SUBSTR(your_field,1,INSTR(your_field,'.',1,2)-1)
FROM dual


Publishing database changes with SSDT that include altering data types of columns I have a SQL Server Data Tools (VS2012) project that's published automatically during the build process. A column was recently updated from an int to decimal(18,4). As a result of this change, the publishing fails with the error

  (49,1): SQL72014: .Net SqlClient Data Provider: Msg 50000, Level 16,
  State 127, Line 6 Rows were detected. The schema update is terminating
  because data loss might occur. (44,0): SQL72045: Script execution
  error.  The executed script: /* The type for column QuantityReceived
  in table [dbo].[Reconciliation_Receiving] is currently  INT NOT NULL
  but is being changed to  DECIMAL (18, 4) NOT NULL. Data loss could
  occur.
  */

  IF EXISTS (select top 1 1 from [dbo].[Reconciliation_Receiving])
      RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127) WITH NOWAIT An error
  occurred while the batch was being executed.

I understand why I'm receiving that error, and I know it could be resolved by disabling the "Block Incremental Deploy if Data Loss May Occur" flag. However, there's very strong opposition to disabling that feature, so it's not going to be an acceptable solution.

The only other solution I can think of is to do the following:

Make a temporary table and copy the existing table's contents into the temporary table
Truncate the existing table
Let SSDT update the data type
Fill the data back in from the temporary table

That seems horribly clunky and inefficient, though.

Is there a better alternative?

I've been tempted to bypass that flag also but have come down on the side of your co-workers and now try to deal with these issues "correctly". The (marginally) less clunky route is to use pre and post deployment scripts to do the work with a rename.

Rename the existing table in a pre-deployment script.
With the existing table missing the table in focus will be created as per the new schema definition.
In a post-deployment script copy from the renamed original table to the new version.

Depending on the nature of the target you may of course need to take care of dropping and recreating foreign key constraints.


Would refreshing a materialized view in a transaction allow me to have test-only specific data on it? I have created a postgresql database used in integration tests for my code.
Its schema is consisted with some materialized views and the code I want to test uses them, therefore I need once I place the test data to refresh them.

But because refreshing them makes the tests slow I need to run the tests in parallel with the materialized view to contain data required only for the test.

Hence, I thought the following approach:

START TRANSACTION

INSERT INTO students (name,class) values ('MIZUKI MINOHARA','A1'),('DIMITRIOS DESYLLAS', 'C3'),('YAMAGUCHI SUZUKI','B7');

REFRESH MATERIALIZED VIEW sempai_list;
-- RUN MY TESTS

ROLLBACK

But would this approach make my tests look on a separate "version" of the materialized views. What I want to achieve is to have the tests look on different test-only data from the materialized view sempai_list.

Do I achieve that by using and rollback transactions?

Not sure what you are after here the questions has some contradictions in it

Materialized views work like tables so all the clients will being looking at the same data defined by the Query that created it.
You want the clients to look at different data sets for the test.

The problem is wanting the clients to look at different data sets when there looking at same data  as Materialized views are not private to the connection

Rollback is not going to help as it going to cause locking issues and conflicts from multiple clients trying to create the same Materialized View.

If the desire is to have the clients not share the data for the test,  use temporary tables instead...

Select * into temporary table students from students

this creates a identical temporary table of the students table in the private schema for this connection and will populate data into table based on the where clause

also can use

Create temporary table students like students.

Nice thing about private_name/schema_space is this is search first prior to searching public or any other schemas defined in search path.

So all the commands sent by the client will use this table.

INSERT INTO students (name,class, test5) values ('MIZUKI MINOHARA','A1'),
 ('DIMITRIOS DESYLLAS', 'C3'),('YAMAGUCHI SUZUKI','B7');

-- RUN TESTS

There is a gotcha here and that is if  queries specifies the schema this will not work

select * from public.student

will always match to public.student and not match the temporary table that was created.

Another big benefit using temporary tables is postgresql cleans up and deletes all the temp tables once the connection is closed or transaction ends depends on the command that created the temp table.


Sql Query with In Operator optmization I have a very common query where i need to find the list of Users that have same or less than the brokers the user currently have

I want to find the colleague users which have same set of BROKERS or a subset of the user and not any Extra Broker

ID  SiteUserID  BrokerID
5009    19437   765
5008    19437   891
5419    26846   891
5421    26846   765
5117    40348   765
5914    40389   765
5915    40574   891

So for UserID 40574 - it return same 40574

For    UserID 40389 - it return 40389 and 40348 as both have same broker

BUT for UserID 19437 - it will return 19437, 26846, 40348, 40349 and 40574 as the brokers in all the users are same set or subset

I am not able to achive this with simple In operator

Select SiteUserID from SiteUserBroker (nolock) where BrokerID in (
 Select BrokerID from SiteUserBroker (nolock) where SiteUserID = @SiteUserID)

which obviously didn't worked as expected with any simple tweak.

I came up with

Select SUB.SiteUserID from SiteUserBroker SUB (nolock)
inner join (Select Count(*) as TotalBrokers , SiteUserID from SiteUserBroker group by
SiteUserID)Nested on Sub.SiteUserID = Nested.SiteUserID
where BrokerID in (
Select BrokerID from SiteUserBroker (nolock) where SiteUserID = @SiteUserID)
group by SUB.SiteUserID
Having Count(*) - MIN(TotalBrokers) >= 0

Is there a more simpler way!

any ideas are appreciated

Table create - to validate your results

    create table #SiteInfo (ID int, SiteUserID INT, BrokerID int )
      insert into #SiteInfo values (
      5009,    19437,   765),
      (5008,    19437,   891),
      (5419,    26846,   891),
      (5421,    26846,   765),
      (5117,    40348,   765),
       (5914,    40389,   765),
       (5915,    40574,   891)

   DECLARE @SiteUserId INT
         SET @SiteUserId = '19437'

Look at

SELECT DISTINCT SiteUserID
FROM SiteUserBroker

EXCEPT

SELECT t1.SiteUserID
FROM SiteUserBroker t1
LEFT JOIN SiteUserBroker t2 ON  t1.BrokerID = t2.BrokerID
                            AND t2.SiteUserID = @SiteUserID
WHERE t2.SiteUserID IS NULL


300 inserts per minute, 6 million rows per month I have a Django application using Postgresql 9.3 that is about to grow in load to about 300 inserts per minute (peak load) and about 6 million rows per month in one table. Also there should be a lot of queries on that same table, nothing complicated, just a sum grouping by and indexed field.

The table looks like this:

        Column        |           Type           | Modifiers
----------------------+--------------------------+-----------
 id                   | integer                  | not null default nextval('seq'::regclass)
 commercial_entity_id | integer                  | not null
 commercial_branch_id | integer                  |
 when                 | timestamp with time zone | not null
 currency_id          | character varying(3)     | not null
 amount               | numeric(10,2)            | not null
 loyalty_account_id   | integer                  | not null
 code                 | character varying(16)    | not null

Can anyone tell me how much hardware should I provide for this load or what would be a good way to handle this?

Right now it is running on an Amazon AWS S3 m3.large with 2 vCPU and 7.5 GB of RAM. I guess this would not be enough but I don't have any real world experience to know better.

Update:

After some help from Meta I reformulate my question as this:

How you should go about determining what sort of hardware would I need to handle this load? Where can I learn another changes that could be done to the database to improve its performance?

There are basically two ways to answer your question: speculation and simulation.

For the former you still have not provided much useful information, such as:

What is your current workload and how does your server behave today?
What are your current data volumes?
What are you planning to do with your 6 million new rows a month? Keep them indefinitely? Archive them after 3 months?

As for simulation, it would likely take you only a day or two to clone your existing database, creating a test system; download and install Apache JMeter; write a simple test plan generating your 300 inserts/second plus your queries; and run it to see how the system actually behaves under your projected workload. This will let you (or a temporary consultant, as suggested by others) to identify performance bottlenecks and determine whether they can be resolved by throwing additional hardware at them.


Efficiently storing sets of key-value pairs with wildly different keys I inherited an application that associates many different types of activities with a site.  There are roughly 100 different activity types, and each one has differing set of 3-10 fields.  However, all activities have at least one date field (could be any combination of date, start date, end date, scheduled start date, etc.), and one responsible person field.  All other fields vary widely and a start date field will not necessarily be called "Start Date".

Making one subtype table for each activity type would result in a schema with 100 different subtype tables, which would be too unwieldly to deal with.  The current solution to this problem is to store the activity values as key-value pairs.  This is a greatly simplified schema of the current system to get the point across.

Each Activity has multiple ActivityFields; each Site has multiple Activities, and the SiteActivityData table stores the KVPs for each SiteActivity.

This makes the (web-based) application very easy to code because all you really need to do is loop over the records in SiteActivityData for a given activity and add a label and input control for each row to a form.  But there are lots of problems:

Integrity is bad; it's possible to put a field in SiteActivityData that does not belong to the activity type, and DataValue is a varchar field so numbers and dates need to be constantly cast.
Reporting and ad-hoc querying of this data is difficult, error prone, and slow.  For example, getting a list of all activities of a certain type that have an End Date within a specified range requires pivots and casting varchars to dates.  The report writers HATE this schema, and I don't blame them.

So what I am looking for is a way to store a large number of activities that have almost no fields in common in a way that makes reporting easier.  What I have come up with so far is to use XML to store the activity data in a pseudo-noSQL format:

The Activity table would contain the XSD for each activity, eliminating the need for the ActivityField table.  SiteActivity would contain the key-value XML so each activity for a site would now be in a single row.

An activity would look something like this (but I haven't fleshed it out fully):

<SomeActivityType>
  <SomeDateField type="StartDate">2000-01-01</SomeDateField>
  <AnotherDateField type="EndDate">2011-01-01</AnotherDateField>
  <EmployeeId type="ResponsiblePerson">1234</EmployeeId>
  <SomeTextField>blah blah</SomeTextField>
  ...

Advantages:

The XSD would validate the XML, catching errors like putting a string in a number field at the database level, something that was impossible with the old schema that stored everything in varchar.
The recordset of KVPs that is used to build the web forms could easily be reproduced using select ... from ActivityXML.nodes('/SomeActivityType/*') as T(r)
An xpath subquery of the XML could be used to produce a result set that has columns for start date, end date, etc without using a pivot, something like select ActivityXML.value('.[@type=StartDate]', 'datetime') as StartDate, ActivityXML.value('.[@type=EndDate]', 'datetime') as EndDate from SiteActivity where...

Does this seem like a good idea?  I can't think of other ways to store such a large number of differing sets of properties.  Another thought I had was keep the existing schema and translate it into something more easily queryable in a data warehouse, but I have never designed a star schema before and would have no idea where to begin.

Additional question: If I define a tag as having a date data type in the XSD using xs:date, is SQL Server going to index it as a date value?  I'm concerned if I query by date it will need to cast the date string to a date value and blow any chance of using an index.

So what I am looking for is a way to store a large number of
  activities that have almost no fields in common in a way that makes
  reporting easier.

Not enough rep to comment first, so here we go!

If the primary purpose is reporting and you have a DW (even if it isn't star schema) I'd recommend attempting to get this into a star schema. The benefits are fast, simple queries. The downside is ETL, but you're already considering moving the data to a new design and ETL to star schema is likely simpler to build and maintain than an XML wrapper solution (and SSIS is included in your SQL Server licensing). Plus it starts the process of a recognized reporting/analytics design.

So how to do that... It sounds like you have what is known as a Factless Fact. This is an intersection of attributes that define an event with no associated measure (such as a sales price).
You have dates available for some or all of your activities? Likely you should really have an intersection of an Activity, Site, and Date(s).

DimActivity - I'm guessing there is a pattern, something that can allow you to break these down into at least relatively shared columns. If so, you may have three? five? dimensions for classes of activities. At worst you have a couple consistent columns, such as activity name, you can filter on, and you leave general headings such as "Attribute1" etc. for the remaining random details.

You don't need everything in the dimension - there (likely) shouldn't be any dates in the Activity dimension - they should all be in the fact, as Surrogate Key references to the Date dimension. As an example, a Date that would stay in a person dimension would be a date of birth because it's an attribute of a person. A hospital visit date would reside in a fact, as it is a point in time event associated with a person, among other things, but it is not an attribute of the person visiting the hospital. More date discussion in the fact.

DimSite - seems straight forward, so we'll describe Surrogate Keys here. Essentially this is just an incrementing, unique ID. Integer Identity column is common. This allows separation of DW and source systems and ensures optimal joins in the data warehouse. Your Natural Key or Business Key is usually kept, but for maintenance/design not analysis and joins.
Example schema:

CREATE TABLE [DIM].[Site]
(
 SiteSK INT NOT NULL IDENTITY PRIMARY KEY
,SiteNK INT NOT NULL --source system key
,SiteName VARCHAR(500) NOT NULL
)

DimDate - date attributes. Make a "smart key" instead of an Identity. This means you can type a meaningful integer that relates to a date for queries such as WHERE DateSK = 20150708. There are lots of free scripts to load DimDate and most have this smart key included. (one option)

DimEmployee - your XML included this, if it is more general change to DimPerson, and fill with relevant person attributes as they are available and pertinent to reporting.

And your fact is:

FactActivitySite
DimSiteSK - FK to DimSite
DimActivitySK - FK to DimActivity
DimEmployee - FK to DimEmployee
DimDateSK - FK to DimDate

You can Rename these in the Fact, and you can have multiple date keys per event. Facts are typically very large so avoiding updates is typically good... if you have multiple date updates to a single event you may want to try a Delete/Insert design by adding a SK to the fact which allows selection of "update" rows to be deleted then inserting latest data.

Expand your Fact dates to whatever you need:
StartDateSK, EndDateSK, ScheduledStartDateSK.

All dimensions should have an Unknown row typically with a hardcoded -1 SK. When you load the fact, and an activity doesn't have any of the included Dates it should simply load a -1.

The fact is a collection of integer references to your attributes stored in the dimensions, join them together and you get all your details, in a very clean join pattern, and the fact, due to it's data types, is exceptionally small and fast. Since you are in SQL Server, add a columnstore index to increase performance further. You can just drop it and rebuild during ETL. Once you get to SQL 2014+ you can write to columnstore indexes.

If you go this route research Dimensional Modelling. I'd recommend Kimball methodology. There are lots of free guides out there too, but if this will be anything other than a one off solution, the investment is likely worth it.


Why do I need to reset user mapping always after restoring my DB I have created a user using SQL Server 2014 management studio, I have mapped user to a couple of tables as db_owner. But after restoring my databases the mapping again cleared for the user and I need to set the User Mapping. How to fix it?

I found the fix. I need to set my user as sysadmin then it does not get cleared user mappings after a db is restored.


Passing an array of integers as a parameter of an (inline) user defined function (SQL Server) I have TBV function . Like this

ALTER FUNCTION [dbo].[fn_Functiont]
(
 @accessibleIds   ListAccesableIds READONLY
)
RETURNS TABLE
AS
RETURN
(
SELECT d.*, b.Name AS Name, ps.Name AS PaymentSystemName, c.UserName AS UserName, c.FirstName AS ClientFirstName, c.LastName AS LastName, c.Number AS DocumentNumber, c.Id
FROM Document AS d
JOIN System AS ps ON d.SystemId = ps.Id
JOIN Client AS c ON c.Id = d.ClientId
LEFT JOIN Shop AS b ON b.Id = d.ShopId
WHERE d.OperationTypeId IN (2, 4, 5) AND c.Type = 1
)

I want to pass to this function List containing integers.I was bale to create table valued type but here is the question how could i pass List input parameter to the function?I know that i could pass lists via ADO.Net to the SP very easly but my problem regards to the Entity Framework and TBF functions

I am calling this function through Entity Framework 6 the approach which we used was database first.

On SQL-Server 2014 you can take advantatge of TYPE (alias data type), and pass it as a parameter of an Inline UDF simulating an array of integer, or any other tabular data.

Keep in mind you must use READONLY.

IF OBJECT_ID('dbo.MyTest') IS NOT NULL
    DROP TABLE dbo.MyTest;
GO

IF OBJECT_ID('dbo.MyFunction') IS NOT NULL
    DROP FUNCTION dbo.MyFunction;
GO

IF TYPE_ID ('dbo.Mytype') IS NOT NULL
    DROP TYPE dbo.Mytype;
GO

CREATE TYPE dbo.Mytype AS TABLE (Id int);
GO

CREATE TABLE dbo.MyTest (Id int, Val char(3));
INSERT INTO dbo.MyTest VALUES (1,'V1'),(2,'V2'),(10,'V10'),(11,'V11');
GO

CREATE FUNCTION dbo.MyFunction(@Table MyType READONLY)
RETURNS TABLE
AS
RETURN
(
    SELECT *
    FROM   MyTest
    WHERE  Id IN (SELECT Id FROM @Table)
);
GO

DECLARE @myTable Mytype;
INSERT INTO @myTable(Id) VALUES(1),(2),(3),(4),(5);

SELECT * FROM MyFunction(@myTable);

| Id | Status |
|:--:|:------:|
|  1 |   V1   |
|  2 |   V2   |

dbfiddle here


Bulk update an address column where the address was duplicated with a delimiter separating the duplicate? Sorry if my title doesn't make much sense, I wasn't sure how to word it though.

Basically I imported a file containing 500+ of stores and information about them. For some reason the [Address] column imported as [Address ; Address]. Is there a way to remove everything after the first Address, including the ';' in one query?

I thought about the REPLACE function, but wasn't sure how to update every record at once since each store has a different Address. I'll include a screenshot so my question makes more sense. Any suggestions? I was thinking since there's a delimiter it might not be too hard to update, but I'm lost as to how.

Any help is greatly appreciated. Thank you.

Ignore the first record as it was previously setup.

Technically using Progress ODBC Query Tools. I can update using SQL Server 2016 though.

You can do this by using the CHARINDEX() function to find the position of the semicolon, coupled with SUBSTRING() to update the field with the data prior to the semicolon.

UPDATE test_table SET addr=SUBSTRING(addr, 0, CHARINDEX(';',addr))

If there are any legitimate semicolons in the data then it will of course remove some of the address, so please check the data first to ensure there is only one semicolon in each record.


N'Șc' considered duplicate key of N'C' using Latin1_General_CI_AS collation I have a table with a unique key that includes an NVARCHAR(50) column (correct or not, but is there). So, when trying to insert Șc or C (doesn't matter the order of the insert) it breaks on the 2nd insert due to collation issues. Here is the error:

  (1 row(s) affected) Msg 2601, Level 14, State 1, Line 16
  Cannot insert duplicate key row in object 'dbo.testT' with unique index 'IX_TestT'. The duplicate key value is (C).

Select returns:

Database default collation is Latin1_General_CI_AS. Spent some time looking on how to solve it, without changing too much the already existing structure, but can't find a way to getting working. Tried different collations and combinations, everything fails. Read (here and here) about character expansions and so on, still stuck. Here is a sample code that I'm using to replicate the issue, feel free to modify and recommend anything that could help solve this.

CREATE TABLE testT (
    [Default_Collation]     [NVARCHAR] (50) COLLATE DATABASE_DEFAULT,
    [Latin1_General_CI_AS]  [NVARCHAR] (50) COLLATE Latin1_General_CI_AS,
    [Latin1_General_CI_AI]  [NVARCHAR] (50) COLLATE Latin1_General_CI_AI,
    [SQL_Collation]         [NVARCHAR] (50) COLLATE SQL_Latin1_General_CP1_CI_AS);
CREATE UNIQUE CLUSTERED INDEX [IX_TestT] ON [dbo].[testT] ([Default_Collation])
ON [PRIMARY]
GO

INSERT INTO testT
SELECT  N'Șc',  --COLLATE Latin1_General_CI_AS
        N'Șc',  --COLLATE Latin1_General_CI_AS
        N'Șc',  --COLLATE Latin1_General_CI_AS
        N'Șc'   --COLLATE Latin1_General_CI_AS

INSERT INTO testT
SELECT  N'C'    --COLLATE Latin1_General_CI_AS
        ,N'C'   --COLLATE Latin1_General_CI_AS
        ,N'C'   --COLLATE Latin1_General_CI_AS
        ,N'C'   --COLLATE SQL_Latin1_General_CP1_CI_AS

SELECT * FROM testT;

DROP TABLE testT;

The problem is that the old SQL Server Collations (i.e. those with names starting with SQL_) and the first two versions of the Windows Collations (the 80 series which came with SQL Server 2000 and do not have a version number in the name, and the 90 series which came with SQL Server 2005) are missing the sort weights for a great many characters. This was mostly corrected starting with the 100 series Collations that came with SQL Server 2008.

As you can see in the below examples, the Ș character matches an empty string when using non-binary, version 80 or 90 Collations (and SQL Server Collations) since they both have the same sort weight: 0. Nothing. Nada. This means that when comparing N'Șc' with N'C' (using pre-series 100 Collations), you are really comparing N'c' with N'C' (test #1):

SELECT 1 WHERE N'Șc' = N'C' COLLATE Latin1_General_CS_AS;
-- no result (due to "c" and "C" being different case)

SELECT 2 WHERE N'Ș' = N'' COLLATE SQL_Latin1_General_CP1_CI_AS;
SELECT 3 WHERE N'Ș' = N'' COLLATE Latin1_General_CI_AS;

SELECT 4 WHERE N'Ș' = N'' COLLATE Latin1_General_BIN2;
-- no result (due to "Ș" still being a code point and empty string has no code points)

SELECT 5 WHERE N'Ș' = N'' COLLATE Latin1_General_100_CI_AS;
-- no result (due to "Ș" finally having a sort weight in 100 series Collations)

SELECT 6 WHERE N'Ș' = N'' COLLATE Chinese_PRC_CI_AI;
SELECT 7 WHERE N'Ș' = N'' COLLATE Chinese_PRC_90_CI_AI;

SELECT 8 WHERE N'Ș' = N'' COLLATE Indic_General_90_CI_AI;
SELECT 9 WHERE N'Ș' = N'' COLLATE Indic_General_100_CI_AI;
-- no result (due to "Ș" finally having a sort weight in 100 series Collations)

So, unfortunately you will need to drop the PK, alter the column to have a 100-level Collation (e.g. Latin1_General_100_CI_AS_SC), and then recreate the PK. Please note that the difference in that suggested Collation from the current Collation is both the 100 and the _SC at the end, which allows it to properly handle supplementary characters.

This does not mean that you need to:

change the Collations of other tables (unless they have the same setup of NVARCHAR in the PK)
change the Database's default Collation. The main issue with not changing the DB's Collation is that there will be a behavior difference between doing table.column = N'Ș' and @variable = N'Ș' since variables and string literals use the Database's default Collation.

For more examples of this behavior, please see the "Supplementary Characters" section of the following blog post of mine:

The Uni-Code: The Search for the True List of Valid Characters for T-SQL Identifiers, Part 3 of 2 (Delimited Identifiers)