Setting up cloud database for syncing to local SQLite database Background
I have a SQL Server database hosted on AWS RDS and there are web applications and WEB APIs that talk to the database. The database is a multi-tenant database and we are currently using SQL Server 2014 although we can upgrade if required.
A third-party developed a local client application on our behalf which has it's own SQLite database. This application is developed in Xamarin so it runs on Windows, iOS and Android. The local SQLite database must be kept in sync with the cloud database. Syncing data up to the cloud database is not a problem, but syncing data down is causing us issues. Currently we sync data down to the local database by asking the WEB API, every minute, to return all changes that have occurred since a particular date. The cloud database has a DateCreated, DateModified and DateDeleted column in every table and these columns are queried to see what data has changed since the last time the client synced data. The local application records the last successful sync date for each table.
Problem
This approach worked when there were few local clients and few tables to sync but as our client base has grown this approach doesn't seem scalable. We are running into performance issues on our cloud database and a lot of the time the sync-down tasks are cancelled due to timeouts or take ages to run. Our customers are complaining about the time it takes for changes they make on the cloud to sync down to the local application.
Potential Solution
Having researched various methods of tracking changes on SQL Server I believe that using the built-in Change Tracking feature is a better approach than using the DateCreated, DateModified and DateDeleted columns for tracking changes. What I am not sure about is how best to set this up.
Things to consider:

Not all columns on the cloud database tables need to sync to the local database - For example, TableA on the cloud database has 20 columns but its corresponding client TableA may only have 5
Not all data relating to a tenant needs to sync to their local database - for example if a record is marked as "inactive" for that tenant it should never be synced locally
A table on the local database may contain data from two or more tables on the cloud database
Not all tenants have the local application yet but they will eventually (this may take a year or more to roll out)

What I am thinking of doing is as follows:

Create a separate database in AWS RDS that exactly matches the local database
Enable change tracking on this database rather than on the main database
Use triggers to keep the main database in sync with the new database
Query the change tracking tables on the new database and return the changes to the local application
Create a new table to track if data has changed or not for each tenant and table - this way we won't need to query the change tracking tables each minute only to find that nothing has changed

The reason for the second database is to reduce the strain on the main database when clients are trying to sync data down and also keeping the schemas in sync reduces the complexity on the queries when a client requests to sync changes. For example, if a record is marked as "inactive" for the tenant in the main database, but that record has been changed, I don't want to have to filter this record out when the client requests to sync the data down. I would prefer to already have those records filtered out so that they would never exist in the second database at all. Hope that makes sense!
I would very much value your feedback on this approach and please feel free to suggest better ways of doing it. If there is something that is not clear please let me know and I'll update the question!

While CDC is one useful methodology in SQL Server, it can also be a little performance heavy too depending on how much data and how frequently it changes.
You might want to consider a 3rd party tool to manage your synchronization between SQL Server and SQLite, which can help minimize the performance and maintenance overhead of a homebrew solution.
For example SQLiteSync by Amplifier is supposed to work well for this kind of use case (note I haven't used it myself though).
https://ampliapps.com/sqlite-sync/


Query returning Union of two tables I'm trying to retrieve data from a table using multi-column subquery but it unions the result from both tables. I'm unable to modify it to get actual results.
Here is my query

select *
from   Recipe, user_plan
where  Recipe.id in (select meal_number
                     from user_plan
                      where week_id=(select max(week_id)
                                     from user_plan
                                     where user_id=:user_id))

Recipe Structure

user_plan structure

It should return 2 rows as per my expectations and data available in tables but it is returning 10 rows.Please help me figure this out. Thanks.
I'm using mysql-server on ubuntu

I think you are missing user id condition in the outer query.

select
* FROM
user_plan inner join receipe
on a.receipe_id = b.id
where a.user_id =:user_id and b.week_id
= ( SELECT max(week_id) FROM user_plan where user_id=:user_id)


MySQL Workbench SSH to Google Compute Fails with "Authentication error opening SSH tunnel" - but Putty works I am trying to connect to MySQL server hosted on google cloud compute server. I believe the issue is not MySQL authentication related, but rather SSH. MySQL workbench seems to be unable to connect on SSH with "Authentication error opening SSH tunnel".

I am able to open a putty SSH connection to the server using the same IP, user and private key on the same computer.

Connection is configured as:

SSH Hostname: [ip of my google cloud server]
SSH Username: [username used in putty ssh]
SSH Key File: [same ssh-rsa ppk file I use in putty]
MySQL Hostname: 127.0.0.1
MySQL Server Port: 3306 Username: [mysql user that works from putty]
Password: [mysql password that works from putty]

wb.log:

09:43:57 [INF][     SSH tunnel]: Existing SSH tunnel not found, opening new one
09:43:57 [INF][     SSH tunnel]: Opening SSH tunnel to 104.197.xxx.xxx
09:43:57 [ERR][sshtunnel.py:notify_exception_error:235]: Traceback (most recent call last):
  File "C:\Program Files\MySQL\MySQL Workbench 6.3 CE\sshtunnel.py", line 303, in _connect_ssh
    look_for_keys=has_key, allow_agent=has_key, timeout=SSH_CONNECTION_TIMEOUT)
  File "C:\Program Files\MySQL\MySQL Workbench 6.3 CE/python/site-packages\paramiko\client.py", line 367, in connect
    look_for_keys, gss_auth, gss_kex, gss_deleg_creds, gss_host)
  File "C:\Program Files\MySQL\MySQL Workbench 6.3 CE/python/site-packages\paramiko\client.py", line 584, in _auth
    raise saved_exception
AuthenticationException: Authentication failed.
09:43:58 [ERR][     SSH tunnel]: Authentication error opening SSH tunnel: Authentication error. Please check that your username and password are correct and try again.
Details (Original exception message):
Authentication failed, please check credentials.
Please refer to logs for details
09:43:59 [INF][      WBContext]: Connection to go-xv01 cancelled by user: Tunnel connection cancelled

Based on mustaccio's comment, the solution was using puttygen 'Conversions'->'Import Key'... Then 'Conversions'->'Export Open SSH Key' to export the key. Open SSH keys begin with "-----BEGIN RSA PRIVATE KEY-----".


Copy scheduling information form local system to remote system  SSRS I    am scheduling some reports locally in my system.
How can I copy this schedule information to remote system to avoid re-doing of scheduling at remote system?

Use the "Reporting Services Scripter" which uses the web service API of SSRS to migrate stuff. I'm not sure how up to date it is, last updated 2009 However, I've foundthis useful in previous jobs.

Copying at the database level won't be enough: scheduled tasks are SQL Server Agent jobs that also need migrated. For more, see "How to: Migrate a Reporting Services Installation"


What to do with duplicate lookup information I have multiple databases that I want to store in one data warehouse database. I am wondering how I design the import process to handle multiple lookup tables.

For example, say I have 5 databases all with the lookup table CustomerState. In one datatabse it could look like this:

In another database it could look like this:

How should I handle this in my enterprise layer of my DW database? Do I add a SourceSystemId to the lookup table, maybe something like this:

And then use the pkyCustomerStateId in my Customer table rather than the CustomerStateId?

This type of thing should be handled by the ETL process that brings the data into the data warehouse. In fact, this process is the T in ETL.

What you need to do first is define the logical key column(s) of the tables, so the business meaning of the rows can be equated between the databases. A multi-column key as you propose would complicate matters, and really doesn't solve the problem.

For this example, I would define CustomerState as the logical key column in the dimension, and when the separate tables are merged together, this column would be unique in the result, with new CustomerStateId values assigned. This ensures the dimension primary key is as narrow as possible, which will carry through to the fact tables and make them as narrow as possible as well.

The ETL process might do something like this (assuming the CustomerStateId column of the target table is an IDENTITY column):

MERGE INTO [dbo].[CustomerState] tgt
    USING [Staging].[CustomerState] src ON src.CustomerState = tgt.CustomerState
    WHEN NOT MATCHED BY TARGET THEN
        INSERT (CustomerState) VALUES (src.CustomerState);

(The reason I used MERGE instead of INSERT is that in other dimensions you may need to handle doing updates as well; not in this case as there are no other columns.)

Then, the fact table loading process would use a lookup mechanism (Lookup Transformation in SSIS) to go from the CustomerState logical value to the newly-assigned CustomerStateid value generated by the above statement.


Passing filter params to a sub query I'd like to create a view from the query below, but the query contains a sub query which I need to apply a filter to (the place is marked with a comment). I'm wondering if it's possible somehow to adjust the query to have the subquery filtered by those params.

The query

select
 form_name,
 nvl(form_pravo,0) as form_pravo,
 nvl(fl_pravo,0) as menu_pravo,
 menu_nazov as menu_name
from(
SELECT
    form_name,
    pravo as form_pravo
  FROM (
    SELECT
        f.ID as form_id,
        f.NAME as form_name,
        f.XF_MODULY_ID as modul_id,
        u.ID as uziv_id,
        f.XF_MODULY_ID
          FROM xf_forms f, xf_uziv u) uf
       LEFT JOIN
        xp_forms_uzivs fu ON form_id  = fu.XF_FORM_ID AND uziv_id = fu.XF_UZIV_ID
            where modul_id = 14 and uziv_id = 1 -- <--HERE
            )
        full join (
            select
                SUBOR,
                FL_PRAVO,
                menu_nazov,
                uziv_id,
                modul_id
            from xv_uziv_menu
        ) um on form_name = um.subor
        where uziv_ID = 1 and modul_id = 14

In MySQL You can try a function

create function modul_id() returns INTEGER DETERMINISTIC NO SQL return @modul_id;
create view v (your select... where modul_id = modul_id()...);
select * from (select @modul_id:=14 modul_id) m, v;

not tested on your query...


Why am I getting an error about truncation when running an SSIS job? hoping someone can help me out. I have no idea what's going on with this job. It runs every week night and always comes back with the same errors.

There are about ten files that are written. We pull the data from our database then convert them to flat files and send it to a third party vendor. Our third party has reported back that all they can view on the error files are the headlines, but no info.

I ran the job manually in debug mode in ssms and there are absolutely no bugs in the package itself. the job succeeded just fine.

Some background - this job has been running with errors since july, which is around the time they set up a new school year as this job is for an educational setting.

If I remember correctly SSIS samples the first 1000 rows of a data set to determine the output datatype. If then row 1001+ exceeds the datatype that SSIS chose, you'll get a truncation error. You will need to update offending column to have a longer data type. Look in the advanced menu in your connection.


In PostgresQL, how do I make a query match exactly one row or fail? I am making an arbitrary DELETE or SELECT query and I know this should effect or return only one row. How can I ensure that the query doesn't execute and I get some signal that something is wrong when more then one row matches?

Using LIMIT and ORDERBY allows the operation to continue, rather than doing a hard abort. Meaning multiple objects may be subject to DELETE with no chance to recover.

Preferably whatever signal results would be distinct from having zero match.

As mentioned by Arkhena, use a scalar subquery to determine the row to access:

DELETE FROM MyTable
WHERE ID = (SELECT ID
            FROM MyTable
            WHERE ...whatever ...);

If the subquery returns more than one ID, the query fails.
This requires some unique key (here: ID).

If you do not want to get an error from the query, you can use CTE for the filter, and then check the number of rows in the CTE:

WITH r AS (
  SELECT ID
  FROM MyTable
  WHERE ...whatever...
)
DELETE FROM MyTable
WHERE (SELECT count(*) FROM r) = 1
  AND ID IN (SELECT ID FROM r)
RETURNING ID;

This statement will return an ID value only if one row was actually deleted.


Update queries slower after enabling SQL Server Full-Text Index I have an asp.net website with many insert, update, and delete queries running against my database.

A few days ago I create a Full-Text Index on two columns of one of tables.
After that I realized that when the website runs update queries on that table,  memory and disk usage of the SQL Server Process is jumping and updates are slower. The queries ran without any performance issues before creating the Full-Text index.

I have also realized that update queries that were very simple before, are now complicated, since the execution plan now has things such as Full Text index update. This is a part of a new execution plan that became complicated after enabling Full-Text:

In some hours when I update site contents, I ran 5000 update queries and I think full text indexing process is done each time for each row.

Should I disable full text scanning on start of updating rows and then re enable it (as in this related question)?

Can I tell SQL Server to stop full text indexing for 5 minutes and then start indexing new data?

Is there any better alternative? I am using SQL Server 2012.

In my case I had to change an inefficient UPDATE statement:

Before:

UPDATE Customer
SET Rating = 'Not Rated'
WHERE ...

After:

UPDATE Customer
SET Rating = 'Not Rated'
WHERE ...
AND Rating <> 'Not Rated' -- THIS LINE WAS INSERTED

This improved performance from 4 minutes down to 5 seconds. The problem was that I used to update all rows, even if they already had the value that I wanted to assign.


mysql trigger / stored procedure - update another table row by row I've already, searched and read, many answers about this issue for 3 days, but couldn't get a clear answer on how to do this.

this will be a little long, be patient please.

First of all, I am trying to make a simple lottery app. I hope everyone of you know about the 6/49 lottery thing.

Well, according to game, first, for a week customers play lottery (lotteryTBL). Then 6 numbers between 1-49 are selected as lucky numbers (resultsTBL).

What I want to do is updating lotteryTBL after a new result row is inserted into resultsTBL.

Structure of lotteryTBL:

ID    num1  num2  num3  num4  num5  num6     draw_date      howmanyknew
1       5    6     8    15      18    25     08-21-2015         0
2       7    15    18   30      40    45     08-21-2015         0

Structure of resultsTBL:

ID    num1  num2  num3  num4  num5  num6     draw_date
1      15    18    35    38    40    47     08-21-2015

Now, here is the pseudocode of what i want:

after insert a new row into resultsTBL
   total <- 0
   for each row in the lotteryTBL where lotteryTBL.draw_date = resultsTBL.draw_date
       total <- calculate how many of them are the same with the resultsTBL's row.
   update lotteryTBL set howmanyknew = total where "some condition"

First, I tried to do it with trigger, some said "it is dynamic sql, you cannot use trigger", some said "use stored procedure". And I did it too. But, they never worked.

Anyway, Can someone type a good pseudocode for this? which method do I have to use? what is the logic here?

I've made it in a TRIGGER calling a Stored Procedure inside resultsTBL table.

Table lotteryTBL and resultsTBL:

mysql> SELECT * FROM test.lotteryTBL;
+----+------+------+------+------+------+------+-------------+
| ID | num1 | num2 | num3 | num4 | num5 | num6 | howmanyknew |
+----+------+------+------+------+------+------+-------------+
|  1 |    1 |    5 |   10 |   15 |   20 |   26 |           0 |
|  2 |    4 |    8 |   12 |   16 |   25 |   40 |           0 |
+----+------+------+------+------+------+------+-------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM test.resultsTBL;
Empty set (0.00 sec)

mysql>

Inserting on resultsTBL:

mysql> INSERT INTO test.resultsTBL(id, num1, num2, num3, num4, num5, num6)VALUES('1', '1', '5', '4', '8', '25', '40');
Query OK, 1 row affected (0.00 sec)

mysql>

RESULT:

mysql> SELECT * FROM test.lotteryTBL;
+----+------+------+------+------+------+------+-------------+
| ID | num1 | num2 | num3 | num4 | num5 | num6 | howmanyknew |
+----+------+------+------+------+------+------+-------------+
|  1 |    1 |    5 |   10 |   15 |   20 |   26 |           2 |
|  2 |    4 |    8 |   12 |   16 |   25 |   40 |           4 |
+----+------+------+------+------+------+------+-------------+
2 rows in set (0.00 sec)

mysql>

If you look in the INSERT:

There are 2 values matching for the ID = 1: 1,5.
There are 4 values matching for the ID = 2: 4,8,25,40.

TRIGGER:

USE `test`;

DELIMITER $$

DROP TRIGGER IF EXISTS test.resultsTBL_AFTER_INSERT$$
USE `test`$$
CREATE DEFINER=`root`@`localhost` TRIGGER `test`.`resultsTBL_AFTER_INSERT` AFTER INSERT ON `resultsTBL` FOR EACH ROW
BEGIN
    CALL test.sp_lottery(NEW.id,NEW.num1,NEW.num2,NEW.num3,NEW.num4,NEW.num5,NEW.num6);
END$$
DELIMITER ;

STORED PROCEDURE:

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_lottery`(
IN eID INT(5),
IN eBLAH01 INT(5),
IN eBLAH02 INT(5),
IN eBLAH03 INT(5),
IN eBLAH04 INT(5),
IN eBLAH05 INT(5),
IN eBLAH06 INT(5))
BEGIN
    # [ lotteryTBL ]
    SET @ID=1;
    # [ ITERATE FOR EVERY ROW IN lotteryTBL ]
    WHILE @ID IS NOT NULL DO
        SET @NUM1=(SELECT num1 FROM test.lotteryTBL AS lt WHERE lt.ID=@ID LIMIT 0,1);
        SET @NUM2=(SELECT num2 FROM test.lotteryTBL AS lt WHERE lt.ID=@ID LIMIT 0,1);
        SET @NUM3=(SELECT num3 FROM test.lotteryTBL AS lt WHERE lt.ID=@ID LIMIT 0,1);
        SET @NUM4=(SELECT num4 FROM test.lotteryTBL AS lt WHERE lt.ID=@ID LIMIT 0,1);
        SET @NUM5=(SELECT num5 FROM test.lotteryTBL AS lt WHERE lt.ID=@ID LIMIT 0,1);
        SET @NUM6=(SELECT num6 FROM test.lotteryTBL AS lt WHERE lt.ID=@ID LIMIT 0,1);
        SET @howmanyknew=0;
        # [ NUM1 ]
        IF (eBLAH01=@NUM1 || eBLAH02=@NUM1 || eBLAH03=@NUM1 || eBLAH04=@NUM1 || eBLAH05=@NUM1 || eBLAH06=@NUM1 ) THEN
            SET @howmanyknew=@howmanyknew+1;
        END IF;
         # [ NUM2 ]
        IF (eBLAH01=@NUM2 || eBLAH02=@NUM2 || eBLAH03=@NUM2 || eBLAH04=@NUM2 || eBLAH05=@NUM2 || eBLAH06=@NUM2 ) THEN
            SET @howmanyknew=@howmanyknew+1;
        END IF;
        # [ NUM3 ]
        IF (eBLAH01=@NUM3 || eBLAH02=@NUM3 || eBLAH03=@NUM3 || eBLAH04=@NUM3 || eBLAH05=@NUM3 || eBLAH06=@NUM3 ) THEN
            SET @howmanyknew=@howmanyknew+1;
        END IF;
        # [ NUM4 ]
        IF (eBLAH01=@NUM4 || eBLAH02=@NUM4 || eBLAH03=@NUM4 || eBLAH04=@NUM4 || eBLAH05=@NUM4 || eBLAH06=@NUM4 ) THEN
            SET @howmanyknew=@howmanyknew+1;
        END IF;
        # [ NUM5 ]
        IF (eBLAH01=@NUM5 || eBLAH02=@NUM5 || eBLAH03=@NUM5 || eBLAH04=@NUM5 || eBLAH05=@NUM5 || eBLAH06=@NUM5 ) THEN
            SET @howmanyknew=@howmanyknew+1;
        END IF;
        # [ NUM6 ]
        IF (eBLAH01=@NUM6 || eBLAH02=@NUM6 || eBLAH03=@NUM6 || eBLAH04=@NUM6 || eBLAH05=@NUM6 || eBLAH06=@NUM6 ) THEN
            SET @howmanyknew=@howmanyknew+1;
        END IF;
        # [ UPDATE ]
        IF @howmanyknew>0 THEN
            UPDATE test.lotteryTBL SET howmanyknew=@howmanyknew WHERE ID=@ID;
        END IF;
    SET @ID=(SELECT ID FROM test.lotteryTBL AS lt WHERE lt.ID>@ID ORDER BY ID ASC LIMIT 0,1);
    END WHILE;
END


SQL Server edition update We have a database in SQL Server 2012 with data of 1.3 TB in size. Our current edition is Standard and configured in a failover cluster mode. We are planning to upgrade our edition from Standard to Enterprise.

So my questions are:

Is it possible to upgrade without downtime? As I know maintenance plan need to execute in one node. In my case can I execute it on the passive node without downtime?
What is the best backup method for 1 TB database. Backup/ Restore is enough for this size?
Does it necessary to take backup and restore after up-gradation? I mean the possibility of data corrupt during the update of edition.
Our server has 16 core processor and we have 4 licenses of 4 core server (4 x 4 = 16). Should we put only one license copy?

Is it possible to upgrade without downtime? As I know maintenance plan
  need to execute in one node. In my case can I execute it on the
  passive node without downtime?

The services will need to be restarted to pick up the new settings. You will also likely be instructed to restart your server. However, upgrading the secondary node and failing over is an option.

  What is the best backup method for 1 TB database. Backup/ Restore is
  enough for this size?

1TB is large, but large is relative. Following normal backup and restore procedures is the way to go. You want to make sure you are using page verification = CHECKSUM, backup verification & checksum, and if you are worried about the size of the backup then compression.

  Does it necessary to take backup and restore after up-gradation? I
  mean the possibility of data corrupt during the update of edition.

Dozens of things could go wrong. I could caution against an in-place upgrade which is what you are proposing. If something goes wrong, there is no "back button". It would be much wiser to set up new servers, cluster, etc and restore your backup to the new instance after testing is complete.

  Our server has 16 core processor and we have 4 licenses of 4 core
  server (4 x 4 = 16). Should we put only one license copy?

This question depends on what HA/DR you are going to, and what you plan to use the secondary for if you are using AGs. I say this because I assume there is a Enterprise level feature you want to use, or a few of them. However, licensing questions should be discussed with the vendor.


Is it possible to drop tablespace CASCADE in MySQL? I am newbie with MySQL. I have created a tablespace with a datafile, and I have tried to drop this tablespace without drop any object in that tablespace by using that SQL query:

DROP TABESPACE TBS_TEST;

  ERROR 1529 (HY000): Failed to drop TABLESPACE

DROP TABESPACE TBS_TEST CASCADE ; ---also got ERROR

So, is it possible to drop tablespace CASCADE in MySQL?

According to the documentation it is not possible:

https://dev.mysql.com/doc/refman/5.6/en/drop-tablespace.html

Important
The tablespace to be dropped must not contain any data files; in other
words, before you can drop a tablespace, you must first drop each of its
data files using ALTER TABLESPACE ... DROP DATAFILE (see Section 13.1.8,
“ALTER TABLESPACE Syntax”).

You can however locate the files that has to be dropped first, via INFORMATION_SCHEMA.FILES:

select ...
from INFORMATION_SCHEMA.FILES
where TABLESPACE_NAME = ?

I'm not able to test this since I don't use any of the engines that support tablespaces.


MDX measure to DAX conversion I needed a little help on working with MDX and DAX. Recently I have just started working with these two terms and am having a little trouble figuring out the differences and possible examples of how they work.

The articles I have read online are giving me a little trouble and I am finding it hard to understand. It would be great if someone could, maybe, kindly explain it in simple words what they are and what they do?

Thank you so much for the help!

MDX & DAX are query languages for OLAP cubes.

MDX is used on multidimensional OLAP cubes and is used by many vendors.

DAX is Microsofts own query language and can only be used in a tabular OLAP cube or within Excel.

Assuming you are using Microsoft  SSAS you must choose which type of cube you want when you install SSAS.

If you (or someone else) has already installed SSAS then you need to find out   if its Multidimensional or Tabular, and then use the appropriate query language.


Select distinct values of two columns as single For starters: yes, I've seen there are lots of questions on combining two/multiple columns - but so far I neither found the answer to my question nor something to get me started trying further (which I did).

I have: a table consisting of two INT fields.
I want: a single column holding the distinct values of both fields.

Examle table:

| a | b |
|---|---|
| 1 | 2 |
| 1 | 3 |
| 1 | 7 |
| 2 | 3 |
| 3 | 5 |
| 5 | 7 |

My desired result would in that case be:

| 1 |
| 2 |
| 3 |
| 5 |
| 7 |

Can this be done with a single query? What would that look like?

// EDIT: some side info (which I think is irrelevant, however, I don't want to hide it):
b is always greater than a.

SELECT a AS result
FROM tableX

UNION DISTINCT

SELECT b
FROM tableX ;


Calculate average days between orders I would like to calculate the average days between several order dates from a table called Orders. For each CustomerID, what is the average days between orders. The sample table is as below (img):

CREATE TABLE #Orders(CustomerID int, OrderDate datetime);

INSERT #Orders(CustomerID, OrderDate) VALUES
    (100,'20170114'),(100,'20170123'),(100,'20170129'),
    (101,'20170202'),(101,'20170212');

I tried this query:

SELECT  CustomerID, AVG(OrderDate - PriorDate)
FROM (SELECT CustomerID, OrderDate
           , LAG(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate) as PriorDate
      FROM #Orders where CustomerID = 100)

However, this yields:

  Msg 102, Level 15, State 1
  Incorrect syntax near ')'.

Several issues:

You are trying to take an average per customer, but you're not grouping by customer.
Implicit math for datetimes (OrderDate-PriorDate) is not a good idea (try that with date or datetime2) - use DATEDIFF.
Integer division will not give you an average like you think - try SELECT 5/2;. You need to convert at least one input to a decimal, either implicitly (*1.0) or explicitly (CONVERT(), TRY_CONVERT(), CAST(), etc.). Explicit allows you to control decimal places in some cases.
LAG() was not complete - while the default is 1, I think it is good to be explicit that you want the previous row.
The incorrect syntax error is actually coming from the end of the query - when you have something like SELECT ... FROM (<subquery>), that subquery needs to be named, so you need to use something like SELECT ... FROM (<subquery>) AS x; for example.

Try the following:

SELECT
  CustomerID,
  AvgLag = AVG(CONVERT(decimal(7,2), DATEDIFF(DAY, PriorDate, OrderDate)))
FROM
(
  SELECT CustomerID, OrderDate, PriorDate = LAG(OrderDate,1)
    OVER (PARTITION BY CustomerID ORDER BY OrderDate)
  FROM #Orders
  WHERE CustomerID = 100
) AS lagged
GROUP BY CustomerID;

Results:

CustomerID    AvgLag
----------    ------
100           7.50

Of course, if you want the average for all customers, just leave out the WHERE clause. But if you really only ever want a single customer, you don't really need the customer in the output, so you can adjust the query a little bit to get rid of the GROUP BY (let's parameterize the customer ID while we're at it):

DECLARE @CustomerID int = 100;

SELECT AvgLag = AVG(Lag), CustomerID = @CustomerID -- you may not need this
FROM
(
  SELECT Lag = CONVERT(decimal(7,2), DATEDIFF(DAY, LAG(OrderDate,1)
    OVER (PARTITION BY CustomerID ORDER BY OrderDate), OrderDate))
  FROM #Orders
  WHERE CustomerID = @CustomerID
) AS Lagged;


What is the best way to store product size and color for an Ecommerce database? I am trying to find the best way to store product size, colour for an sports store website, something similar to what amazon does, where you can choose a shoe colour and number. The problem is that a product may be a shoe shirt ball bag etc, so the sizing charts/systems to be used will all be different for different types of products. Also one product may have different sizes and different colours and some colours may not have a certain sizes or vice-versa? I am a rookie in database designing so I don't know how to approach this problem.

You will need to implement exclusive subtypes to handle the attributes for each product type.  A (very) abbreviated example:
CREATE TABLE ProductType
(
  ProductTypeCd  CHAR(4)      NOT NULL /* Something human readable - SHOE, SHRT, BAG, BALL, etc */
 ,Name           VARCHAR(50)  NOT NULL
 ,CONSTRAINT PK_ProductType PRIMARY KEY (ProductTypeCd)
 ,CONSTRAINT AK_ProductType UNIQUE (Name)
)
;

CREATE TABLE Product
(
  ProductId      INT           NOT NULL /* If you have a code to track inventory/items, use that instead */
 ,ProductTypeCd  CHAR(4)       NOT NULL
 ,Name           VARCHAR(50)   NOT NULL
 ,Description    VARCHAR(200)  NOT NULL
 ,Price          DECIMAL(9,2)  NOT NULL
 ,CONSTRAINT FK_Product_Discriminated_By_ProductType FOREIGN KEY (ProductTypeCd) REFERENCES ProductType (ProductTypeCd)
 ,CONSTRAINT PK_Product PRIMARY KEY (ProductId)
 ,CONSTRAINT AK_Product UNIQUE (/* Whatever columns are necessary to prevent duplicates - if you are using a product code/inventory code for your PK this may be unnecessary */)
)
;

In this case ProductTypeCd works as a discriminator to define the subtype of Product, which we would then define like so:
CREATE TABLE ProductShoe
(
  ProductShoeId  INT NOT NULL
 ,GenderCd       CHAR(1)  NOT NULL /* M/W/U, should control through FK reference */
 ,ShoeSizeCd     CHAR(4)  NOT NULL /* 9/9D/9DD,12E, etc., should control through FK reference */
 ,ColorCd        CHAR(3)  NOT NULL /* Something human readable, RED, BLK, YEL, GRN, BLU, etc., again enforce through FK reference */
 ,CONSTRAINT FK_ProductShoe_Is_Product FOREIGN KEY (ProductShoeId) REFERENCES Product (ProductId)
 ,CONSTRAINT PK_ProductShoe PRIMARY KEY (ProductShoeId)
)
;

CREATE TABLE ProductShirt
(
  ProductShirtId   INT      NOT NULL
 ,GenderCd         CHAR(1)  NOT NULL /* Same treatment as for Shoe */
 ,ShirtTypeCd      CHAR(4)  NOT NULL /* TEE, POLO, etc. */
 ,ShitSizeCd       CHAR(4)  NOT NULL /* XS/S/M/L/XL/XXL/etc., again, use FK */
 ,ColorCd          CHAR(3)  NOT NULL /* Same treatment as for Shoe */
 ,CONSTRAINT FK_ProductShirt_Is_Product FOREIGN KEY (ProductShirtId) REFERENCES Product (ProductId)
 ,CONSTRAINT PK_ProductShirt PRIMARY KEY (ProductShirtId)
)
;

There is some care that must be taken to ensure the tables align with the proper ProductType.  Depending on the DB engine this would have to be handled via a trigger instead of a check constraint.  This SO answer handles it in depth: https://stackoverflow.com/a/4898314/13942986
From comments:

@Eniola so currently I am on the planning/design phase and I will be
using a relational sql database thinking of using SqlLite or Mysql

SQLite is not a relational database, MySQL barely so.  Between the two, use MySQL.


How to delete duplicate records in my database? I joined about 12 databases into 1 db and in each database I have some duplicate records.

How can I delete all duplicate records in my joined database?

For example, I want to delete duplicate records when data in column "phone" is the same.

I'm assuming that you have no foreign key constraints on the phone column and you have a Primary Key defined:

DELETE FROM foo_schema.bar_table
WHERE primary_key_column NOT IN (SELECT MAX(primary_key_column)
                                        FROM foo_schema.bar_table
                                        GROUP BY phone)

This should group all records by the column phone, and drop all the duplicates but the one with the max PK. Hope that helps.

Please try this on a dummy table before attempting this on your actual table.


How to transfer data using expdp and impdp commands? I'm an Oracle noob, and my intention is to transfer all data and metadata from one schema to another schema within an Oracle database. I'm planning to use datapump's expdp and impdp commands. I have questions regarding this:

Can I create a target schema without a user or should I create a user first (which creates a schema also)?
Can I execute expdp and impdp commands using SYS (as sysdba)
account? Is that a preferred method?
Does this statement take all the objects (data and metadata) from a schema and move these into a different schema?

expdp \"/ as sysdba\" schemas=<schemaname> directory=dumpdir dumpfile=<schemaname>.dmp logfile=expdp_<schemaname>.log

So is the target schema an exact copy of the source schema after impdp command?

impdp will create the user if it's not present yet, so you don't have to worry about it unless that's not what you want.

Do not run impdb or expdp as sysdba, only do that if Oracle support requests it in specific circumstances. Use an ordinary user for that - one that has been granted the dba role for instance. (There are the [IMPORT|EXPORT]_FULL_DATABASE privileges specifically for this type of thing, you'll need to grant access to the Oracle directory object(s) too.)

A full schema export (metadata and contents) would indeed look like:

expdp user/pass schemas=<schemaname> directory=dumpdir \
      dumpfile=<schemaname>.dmp \
      logfile=expdp_<schemaname>.log

If you want to import to a different user/schema (the target database can be the same as the source), you can use:

impdp user/pass schemas=schema1 directory=dumpdir \
      remap_schema=schema1:schema2 \
      dumpfile=schema1.dmp \
      logfile=impdp_schema2.log

If you don't want a complete import, you can set some filters both on data and metadata. See Filtering During Import Operations.

The Utilities Guide has all the details, I strongly recommend reading at least the overview part.


Wrong data type while using linked tables in MS Access I have a SQL server database which needs to be linked to MS Access forms.

I have a table with one column as both Identity and is a also a primary key and is big int

But after linking the table , if i open the design window of table in MS access , it is converted as text .

Why it is converting the Identity column to Text while linking.

Bigint is not a datatype supported by access. If you wanted the value to stay as a numeric value in access you would have to change the source column in the SQL server table to numeric(19,0).

https://msdn.microsoft.com/en-us/library/ms714540%28v=vs.85%29.aspx


MySQL connection logs backtrack Is there a way to backtrack who connects to MySQL server? Aside from show processlist;? I want to see who connects last day. Thank you.

There are multiple audit logging products available. If you are using Percona or MariaDB flavour of MySQL you have the option of their plugin. If you are using Oracle MySQL you can pay for their enterprise version of audit plugin (as part of Enterprise Edition). There is also an audit plugin from McAfee that will fill this requirement and is generally available cross-alternative and from 5.1+. These products permit you to log both logins and queries. Finally there's a plugin to track logins only from a community contributor. Links to all below.

http://www.percona.com/doc/percona-server/5.5/management/audit_log_plugin.html
https://mariadb.com/kb/en/mariadb/documentation/plugins/mariadb-audit-plugin/
http://dev.mysql.com/doc/refman/5.5/en/audit-log-plugin.html
http://code.openark.org/blog/mysql/introducing-audit_login-simple-mysql-login-logfile-based-auditing


Microsoft Baseline Configuration Analyser on Production I came across an useful tool that Microsoft has released for configuration analysis. It's called Microsoft Baseline Configuration Analyser 2.0.

I downloaded and ran it on my local instance and got a number of errors and warnings. In installation process, it warns you that you should accept some changes happen on you power shell to install the tool.

Considering that, do you think it is safe to be installed on Production server for regular checkings?

Have anyone used this tool before and recommend it?

Thanks.

We had the same debate at our office before installing them as we also use the Best Practice Analyzer and the Baseline Security Analyzer as well. We use them once a year to baseline our systems and to check for problems/changes compared to previous baselines that we have taken.

I have never had a problem with them personally, but we never run it on an active Production machine - we normally run it against the servers once we have performed our annual DR test so they are technically not in use at the time.

They are useful tools, and can give you a good hint into what you should be looking for. However, they should not be taken as gospel and anything it comes up with really shouldn't be much of a surprise to you.

Sometimes you will get systems that require a precise configuration to get an application to work, sometimes this requires to not follow a best practice - but you should have documented this anyway so that when the analyzer raises it you already know why it is like that.

Even if you do decide to run the tools - don't go changing things before you fully understand the impact that it will have - sometimes something that seems so small can have a very large impact. Remember, anything that is comes up with is really just a recommendation and even though in the main they are good practices to follow ultimately it is you who decides whether it is feasible to do so or not.

Also you mention running these on a regular basis - these tools are just a guideline and should not be used as your sole way of checking - on-going monitoring of your systems, permissions and access in conjunction with a well implemented change management process will give you a better indication of problems before they arise.


Using SQL Server on separate networks I have a bunch of SQL Servers installed on Windows2008R2.  Currently, each windows server has two NIC's, each connecting to separate networks.  In other words, each server appears on each network.  One network  allows the applications to access the database, the other network is intended to be used for large volumes of data being migrated between the servers without impacting the network the applications use.

I have a job that pulls in a large chunk of data from one SQL server to another.  I would like that job to take advantage of the second network as well, but I am stumped.

Is there a way to tell SQL to use a specific network device when communicating to another server?

Thanks!

Check on the TCP/IP listening address in SQL Server Manager, both servers have to be listening on the same network (probably both server will be listening on both networks)
When connecting each server to another, you will have to specify the IP address and that should do it.


Adding a date and the time portion of a datetime2 together I have a date field [load_date] and a datetime2 field [load_time] that I want to combine into a datetime2.  I want to combine load_date with the time portion of load_time and create a datetime2.

It's in an existing table, and I want to SET the load_time to this new value.  Below is a SELECT that appears to work, so I can turn this into an UPDATE statement.  However, I wanted to know if there is a more elegant or less verbose way.

SELECT
    [load_date]
    , [load_time]
    , DATEADD(DAY, DATEDIFF(DAY, CAST(CAST([load_time] AS TIME) AS DATETIME2), CAST([load_date] AS DATETIME2)), CAST(CAST([load_time] AS TIME) AS DATETIME2))

You can use CONCAT and rely on implicit conversion to do it less verbosely.  Both queries use 0 CPU, so you may have to run a lot of iterations to see if one is significantly more efficient than the other.

SELECT
    [load_date],
    [load_time],
    CONCAT([load_date], ' ', CAST([load_time] AS TIME))
FROM [test_table_1]

You aren't required to explicitly convert it to DATETIME2 because it will be converted implicitly when it updates the load_time column.  Note that having the shortest code is not always the best.  Using comments to explain what the code is doing will prevent anyone from having to figure out what it's doing.


unique constraint on a column with duplicate values How to create unique constraint on a column with duplicate values,without disturbing the existing data.i have already tried the method below,

To create a normal index first and then create a unique constraint.
But in this I won't be able to drop the unused index created first.i don't want that .

Any other clean methods which can be applied in production env?

I'm sure this has already been asked before, but .. from the Oracle documentation
Further down the page, when you look for what each constraint state is:

VALIDATE | NOVALIDATE The behavior of VALIDATE and NOVALIDATE always depends on whether the constraint is enabled or disabled, either explicitly or by default. Therefore they are described in the context of "ENABLE Clause" and "DISABLE Clause".
ENABLE Clause Specify ENABLE if you want the constraint to be applied to the data in the table.
If you enable a unique or primary key constraint, and if no index exists on the key, then Oracle Database creates a unique index. Unless you specify KEEP INDEX when subsequently disabling the constraint, this index is dropped and the database rebuilds the index every time the constraint is reenabled.
You can also avoid rebuilding the index and eliminate redundant indexes by creating new primary key and unique constraints initially disabled. Then create (or use existing) nonunique indexes to enforce the constraint. Oracle does not drop a nonunique index when the constraint is disabled, so subsequent ENABLE operations are facilitated.
ENABLE VALIDATE specifies that all old and new data also complies with the constraint. An enabled validated constraint guarantees that all data is and will continue to be valid.
If any row in the table violates the integrity constraint, then the constraint remains disabled and Oracle returns an error. If all rows comply with the constraint, then Oracle enables the constraint. Subsequently, if new data violates the constraint, then Oracle does not execute the statement and returns an error indicating the integrity constraint violation.
If you place a primary key constraint in ENABLE VALIDATE mode, then the validation process will verify that the primary key columns contain no nulls. To avoid this overhead, mark each column in the primary key NOT NULL before entering data into the column and before enabling the primary key constraint of the table.
ENABLE NOVALIDATE ensures that all new DML operations on the constrained data comply with the constraint. This clause does not ensure that existing data in the table complies with the constraint.

So you would want to specify at the end of the constraint that it is ENABLE NOVALIDATE.  Here is an example
ALTER TABLE my_table ADD CONSTRAINT my_constraint UNIQUE (my_columns) ENABLE NOVALIDATE;

Apparently this alone will still throw an ORA-2299 because Oracle is still attempting to back the UNIQUE constraint with a UNIQUE index upon creation.  The most simple way to get around that is to create the (non unique) index first.
CREATE INDEX my_index ON my_table (my_columns);
ALTER TABLE my_table ADD CONSTRAINT my_constraint UNIQUE (my_columns) ENABLE NOVALIDATE;


MySQL using too much CPU I'm studying to be a DBA right now because I have a massive live database that is very sensitive that I manage. Here's the current system stats of my dedicated database server:

CentOS 5.9
24gb Ram
8 Core CPU
50gb FusionIO disk for /var/lib/mysql
MySQL 5.5.23

top - 18:40:27 up 14 days,  4:43,  1 user,  load average: 19.72, 22.62, 24.04

Tasks: 183 total,   3 running, 180 sleeping,   0 stopped,   0 zombie

Cpu(s): 69.9%us,  0.4%sy,  0.0%ni, 29.2%id,  0.0%wa,  0.1%hi,  0.4%si,  0.0%st

Mem:  24685224k total, 20172096k used,  4513128k free,   343420k buffers

Swap:  2007284k total,        0k used,  2007284k free,   729004k cached

PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND

5446 mysql     15   0 18.4g  17g 6176 R 765.8 76.2 114437:38 mysqld

I'm currently using 43gb of that 50gb FusionIO slice. MySQL averages around 700 QPS and 75-90% CPU usage. Here's my my.cnf file:

[mysqld]
user=mysql
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

#innodb
#innodb_log_file_size = 256M
#innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout=50
innodb_file_per_table
innodb_buffer_pool_size=16G
innodb_buffer_pool_instances=4
#eliminating double buffering
innodb_flush_method = O_DIRECT
flush_time=86400

skip-name-resolve
query_cache_limit=4M
query_cache_size=256M
sort_buffer_size=8M
read_rnd_buffer_size=1M
max_connections=5000
interactive_timeout=60
wait_timeout=300
connect_timeout=30
thread_cache_size=32
key_buffer=124M
tmp_table_size=4096M
max_heap_table_size=256M
join_buffer=16M
max_connect_errors=2000
table_cache=2048
thread_concurrency=12
long_query_time=5
log-slow-queries=/var/log/mysql-slow.log
#table_definition_cache=384
max_allowed_packet=1024M
#server-id=20
#log-bin=mysql-bin
#expire_logs_days=10

event_scheduler=ON

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

can you guys recommend any config changes I should make to MySQL or CentOS that can greatly reduce my CPU usage? Or for that matter any other resource improvements?

You need to unleash two things

innodb_read_io_threads (Default is 4, Max is 64, raise it 8 or 16)
innodb_write_io_threads (Default is 4, Max is 64, raise it 8 or 16)
innodb_thread_concurrency (Default is 0. Since you don't have it, 0 is just fine)

I have this discussed this over the years. Please see some of my earlier posts on getting InnoDB to engage more CPU/Core activity.

May 26, 2011 : About single threaded versus multithreaded databases performance
Sep 12, 2011 : Possible to make MySQL use more than one core?
Sep 20, 2011 : Multi cores and MySQL Performance
Apr 26, 2012 : Is the CPU performance relevant for a database server?

Give it a Try !!!
UPDATE 2013-06-20 02:39 EDT
Your last comment

Hey Rolando, I added read_io and write_io at 8 and read through your other posts but it doesn't seem to be bringing down the CPU usage. I even dropped the 4 buffer pool instances down to 1 in hopes that it would have better performance, but still no go. Do you have any more suggestions that I could implement that will help drop CPU usage?

I would suggest raising innodb_buffer_pool_instances to 8 to match the number of cores. The MySQL Documentation suggests 1G per buffer pool instance. Then, you would raise to 16 (since you have innodb_buffer_pool_size at 16G). Try 8 first, then 16.
Even with this, I have some rather distressing news: Here is a Press Release From FusionIO from 2 years ago. Paragraph 2 says:

The StatSoft white paper concluded that Fusion ioDrives significantly increased the I/O performance in the STATISTICA suite of analytics software products and solutions, greatly increasing CPU utilization and efficiency. With ioMemory, StatSoft achieved 300 and 500 percent data performance and latency reduction improvements when compared to legacy disk-based storage. With the increased I/O performance enabled by the Fusion ioDrives, CPU utilization increased to 90 percent in tests of large data sets, versus the 32 percent CPU utilization observed with the disk-based technology.

If you were using RAID 10 SAS or even SSDs for /var/lib/mysql, InnoDB would have its day with the CPUs. However, from the looks of this Press Release, InnoDB is competing with FusionIO for CPU Utilization.
SUGGESTION
Find out what the IOPs are for the FusionIO disk and set innodb_io_capacity to that number. The default value for innodb_io_capacity is 200. If the IOPs are greater than 200, then please set it. If the IOPs are in the 1000's, then InnoDB has a chance to be on a level playing field with the FusionIO.
Give it a Try !!!


One column link to others I always search my problem with different search terms but the problem I am facing now I can't express this in search keyword. So instead I came here to ask directly from humans as machines were unable to what I exactly means.

So coming to scenario:

I have database with having table_item and table_item_linked. table_item has many items. User come and add item(s). Later other user came and link one item with different item via a form with two dropdown.

What I did so far is:

Structure of `table_item`:
- item_id (Primary)
- others..

.

Structure of `table_item_linked`:
- linked_id (Primary)
- item_id (Foreign key referencing -> item_id of table_item)
- linked_items (here I need to store ids of linked items)
- linked_by (referencing to user_id of user_table)
- linked_timestamp (timestamp)

If I have items like:
A B C D E F G H

When I link D with G

I can successfully fetch G when I am fetching D or vice versa. But problem came when I

Link H with G

So I must fetch D H while fetching G.

It is like a multiple relation.

Guys I know there must be professional way to do it. I will like to have any guidance. I can even change my database structure.

PS:
Please don't suggest to add #tag as one item is exactly similar to the other linked.

Decide which you type of "relationship" each "link" is:  "1:1", "1:many", or "many:many".

1:1 should be represented by having the columns in the same table.  (There are exceptions, but you aren't there yet.

1:many is implemented via an id of the "1" in the "many" table.  Example:  A person owns "many" cars.  The cars table has a column person_id to say who it is owned by.

many:many needs an extra table with ids of both of the tables.  Example:  students and classes:

CREATE TABLE WhoTakesWhat (
     student_id INT UNSIGNED NOT NULL,
     class_id INT UNSIGNED NOT NULL,
     PRIMARY KEY(student_id, class_id),
     INDEX(class_id, student_id)
) ENGINE=InnoDB

I encourage you to be thinking in terms of SQL; you will eventually have to use it.

Meanwhile:

A "database" contains several "tables".
A "table" is a set of "rows" and "columns".
"Columns" tend to be dissimilar (id, name, date, ...)
"Rows" are different instances of what is being stored in the table (various "students" in the table named students)


One-to-many relationship on a “‘junction’ table” I have two entity's - Workout and Exercise. These will have a 'many-to-many' (M:N) relationship through the junction table WorkoutExercise. Things start to get complicated when I need to have a 'one-to-many' (1:M) relationship on this table from the table called Sets.

I have provided the entity-relationship diagram below to help illustrate the scenario:

Is this poor design? i.e. is it bad for performance? is there a better way?

You can build a unique/primary key in the Workout Exercise table that can be referenced by Sets.  That will maintain referential integrity and minimize redundancy, assuming that the surrogate key made up of key values from workout and exercise is unique, and you can create one primary key on sets that has that foreign key referential integrity to the associative (junction) table.


BACKUP MASTER KEY failing with cannot find symmetric master key because it does not exist I'm trying to backup the master key for a TDE database, but SQL Server says there isn't one. It's a bit weird, but I'm sure I'm just doing something wrong. I'm sysadmin on the server, so I should be able to see everything.

This is the statement that is failing:

USE [my_db];
BACKUP MASTER KEY
TO FILE = 'C:\master_key'
ENCRYPTION BY PASSWORD = 'some_killer_password';

And the error message returned:

  Msg 15151, Level 16, State 1, Line 11
  Cannot find the symmetric key 'master key', because it does not exist or you do not have permission.

I've used the following to see the details about the database encryption key, and the associated certificate, however the certificate details from the sys.certificates table is empty.

USE [my_db];
SELECT DatabaseName = d.name
    , ddek.encryptor_type
    , ddek.opened_date
    , c.name
    , c.cert_serial_number
    , c.pvt_key_encryption_type_desc
    , c.subject
FROM sys.dm_database_encryption_keys ddek
    INNER JOIN sys.databases d ON ddek.database_id = d.database_id
    LEFT JOIN sys.certificates c ON ddek.encryptor_thumbprint = c.thumbprint
WHERE d.name <> 'tempdb' /* tempdb is auto-encrypted by SQL Server */

╔══════════════╦════════════════╦═════════════════════════╦══════╦════════════════════╦══════════════════════════════╦═════════╗
║ DatabaseName ║ encryptor_type ║ opened_date             ║ name ║ cert_serial_number ║ pvt_key_encryption_type_desc ║ subject ║
╠══════════════╬════════════════╬═════════════════════════╬══════╬════════════════════╬══════════════════════════════╬═════════╣
║ my_db        ║ CERTIFICATE    ║ 2017-09-20 11:24:13.590 ║ NULL ║ NULL               ║ NULL                         ║ NULL    ║
╚══════════════╩════════════════╩═════════════════════════╩══════╩════════════════════╩══════════════════════════════╩═════════╝

So, I can see the database encryption key in [my_db], and I can see it's encrypted by a certificate, but the certificate doesn't exist?

The certificate used by TDE to encrypt the database encryption key is actually stored in the master database, which is in turn encrypted by the database master key stored in the master database.

The query to see which certificate is used to decrypt the my_db TDE-encrypted database should be:

SELECT DatabaseName = d.name
    , ddek.encryptor_type
    , ddek.opened_date
    , c.name
    , c.cert_serial_number
    , c.pvt_key_encryption_type_desc
    , c.subject
FROM sys.dm_database_encryption_keys ddek
    INNER JOIN sys.databases d ON ddek.database_id = d.database_id
    LEFT JOIN master.sys.certificates c ON ddek.encryptor_thumbprint = c.thumbprint
WHERE d.name <> 'tempdb' /* tempdb is auto-encrypted by SQL Server */

Note, the only change is to reference sys.certificates in the master database.

The results of that query shows:

╔══════════════╦════════════════╦═════════════════════════╦════════════════════╦════════════════════╦══════════════════════════════╦═══════════════════════════╗
║ DatabaseName ║ encryptor_type ║ opened_date             ║ CertName           ║ cert_serial_number ║ pvt_key_encryption_type_desc ║ Certsubject               ║
╠══════════════╬════════════════╬═════════════════════════╬════════════════════╬════════════════════╬══════════════════════════════╬═══════════════════════════╣
║ my_db        ║ CERTIFICATE    ║ 2017-09-20 11:24:13.590 ║ db_encryption_cert ║ <redacted>         ║ ENCRYPTED_BY_MASTER_KEY      ║ DB Encryption Certificate ║
╚══════════════╩════════════════╩═════════════════════════╩════════════════════╩════════════════════╩══════════════════════════════╩═══════════════════════════╝

Note the query shows the certificate is ENCRYPTED_BY_MASTER_KEY - the master key referenced here is the master key for the master database.

In order to restore my_db onto another server, you'll need to backup the the certificate (with its private key) used to encrypt the database, then restore it onto the target SQL Server.

This should happen on the source SQL Server:

BACKUP CERTIFICATE db_encryption_cert
TO FILE = 'C:\db_encryption_cert'
WITH PRIVATE KEY (
    FILE = 'C:\db_encryption_cert_private_key'
    , ENCRYPTION BY PASSWORD = 'new private key password'
    );

Store the resulting certificate file and private key and it's password on a secure file system offsite.

This should happen on the target SQL Server:

CREATE CERTIFICATE db_encryption_cert
FROM FILE = 'C:\db_encryption_cert'
WITH PRIVATE KEY (
    FILE = 'C:\db_encryption_cert_private_key'
    , DECRYPTION BY PASSWORD = 'new private key password'
    );

Once you've created the certificate on the target server, you should be able to restore the database without issue.

If you are preparing for disaster recovery, and intend on being able to restore the source SQL Server's master database, etc, you should also backup the service master key and the master database master key:

USE master;
GO
BACKUP SERVICE MASTER KEY
TO FILE = 'C:\service_master_key'
ENCRYPTION BY PASSWORD = 'new service master key password';

BACKUP MASTER KEY
TO FILE = 'C:\master_database_master_key'
ENCRYPTION BY PASSWORD = 'new master database master key password';

These keys, and their associated encryption passwords, should be stored in a secure location off-site.


data warehouse design help I'm struggling with the design phase for a data warehouse.  It's not the traditional sales example you find on MSDN or all books etc.  I've been tasked with creating an education data warehouse, that tracks student progress over time.  There is nothing that quite matches it in Kimball's books either.  This is what I've come up with so far. I'm following best practices i.e. Creating primary identity keys on each table and including the business key which I may attempt to make an unique key depending on the quality of data (whether referential integrity can be kept)

DimStudent

StudentDwKey
StudentBizKey

DimSubject

SubjectDwKey
SubjectBizKey

DimTime or date

DateKey

Different format dates, quarters etc. etc.

DimCollege

CollegeDwKey
CollegeBizKey

DimTeacher or tutor

TeacherDwKey
TeacherBizKey

FactGrades

FK_StudentDwKey
FK_SubjectDwKey
FK_CollegeDwKey
FK_TeacherDwKey
Grades

(Obviously there is many more fields in the dimension tables, but just for simplicity)

Student and Subject both have the same business key.  Now I'm really struggling with the fact table.  The exam grades are stored in the Subject business table for some reason.  The ETL I've created splits the grades from the subject table so DimSubject just contains subjects and FactGrades just contains Grades with foreign keys from other tables.  The problem I'm having is the relationships and whether or not I need to create multiple fact tables.  There are other grades they want analysing that are stored in different places for example i.e. Predicted grades, or admission exam grade. So should I create a fact table for each one, or try and merge them all into one fact table?  Also if the relationships are defined by the business key, should I not be creating foreign key relationships between the business keys and not the surrogate keys?  So in short.

Do I define relationships by newly created keys or business keys.
Do I create multiple fact tables for each type of grade if they are in different parts of the business database or put them all in one table.
Can I link one dimension to multiple fact tables or does this cause duplication issues.
How do I define relationship to date dimension, should this relationship be between the fact table and what do I join on?  I have an exam start date and end date but then I will have different dates for other grade types.

Here are my answers:

Define relationships based on your surrogate keys and not your business keys. This is because this is what the SQL engine will use to optimize queries and this is the reason you have surrogate keys. Since surrogate keys are often integers, this simplifies the joins from using different data types such as strings which are less efficient or even using composite keys. It might be worth defining nonclustered indexes on your business keys for lookups during your ETL process, but queries against the data warehouse should utilize your surrogate keys.
Here's how I would approach your fact tables. If the different grades are the same granularity and have most if not all of the same dimensions in common, I would put them in the same fact table. Otherwise, I would put them in separate fact tables. You can always define cubes with multiple measure groups or put a view in place that combines the different fact tables into one that makes sense down the road.
You definitely can link one dimension to multiple fact tables. This is referred to as a conformed dimension and is usually more common that linking a dimension to a single fact table.
Typically, I create a date dimension that is at a single day granularity and link it to any fact table that has a date field in it. However, in an education context, you might have the need for a different granularity such as just assigning a fact to a quarter dimension instead. If that's the case, you could build out a separate dimension table at the desired granularity. However, for your example of an exam, you would just link that exam record to the date dimension based on that specific day. Furthermore, you should build out your date dimension to have fields such as quarter, school year, etc. One other thing to consider in your example is that it might be useful to calculate the duration of the exam based on the start and end dates if they are in a date time format and you want to know how long it took student to complete a specific exam and to see if duration correlates with results in some way.

Let me know if you have any follow up questions!


Slow Index scan on PostgreSQL I have a table named foo_partition_20210501, which has a size of ~10GB and contains ~100M rows. As you may guess it is a partition of a partitioned table, but I don't think has something to do with the problem I'm encountering, as I'm only working on one partition at a time.
The table has multiple columns, but three are of interest:

item_id: integer
item_date: date
code: custom type enum_code

Many of the records of the table have a code = 'A', and I often filter on the tuple (item_id, item_date). So I've created the following partial index:
CREATE INDEX ix_foo_partition_20210501_partial_index
ON public.foo_partition_20210501
USING btree (item_date, item_id)
WHERE (code <> 'A'::enum_code)

The partial index is ~50MB large.
When I query the table such as
SELECT *
FROM foo_partition_20210501
WHERE item_date='20210521'
AND item_id=137415
AND code IN ('B', 'C')

The resulting plan (explain analyze+buffers) contains this:
->  Index Scan using ix_foo_partition_20210501_partial_index on foo_partition_20210501  (cost=0.43..18.00 rows=8 width=62) (actual time=460.775..20533.036 rows=63 loops=1)

Index Cond: ((item_date = '2021-05-21'::date) AND (item_id = 137415))
"Filter: (code = ANY ('{B,C}'::enum_code[]))"
Rows Removed by Filter: 1841
Buffers: shared hit=2 read=1124 dirtied=76
I/O Timings: read=20418.129

So the query plan is correct and does use my partial index, but is very slow (20s). It does read the disk instead of the cache, but I still don't understand why it would be so slow. The index is fairly small (only 50MB), and the number of overall rows fetched is less than 2000 with only 63 being kept.
Do you have any insight on why this is slow ? is it normal disk read performance for PostgreSQL ?
Details of the platform:

PostgreSQL 12.5 on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
SAN Disk

Running the same query multiple times:
When hitting the cache, it's very fast.
Index Scan using ix_foo_partition_20210501_partial_index on foo_partition_20210501  (cost=0.43..18.00 rows=8 width=62) (actual time=0.080..4.672 rows=63 loops=1)
Index Cond: ((foo_date = '2021-05-21'::date) AND (foo_id = 137415))
"Filter: (code = ANY ('{B,C}'::enum_code[]))"
Rows Removed by Filter: 1843
Buffers: shared hit=1128

Some times hitting the disk is still slow but only 8s here:
Index Scan using ix_foo_partition_20210501_partial_index on foo_partition_20210501  (cost=0.43..18.00 rows=8 width=62) (actual time=30.511..8135.828 rows=63 loops=1)
Index Cond: ((foo_date = '2021-05-21'::date) AND (foo_id = 137415))
"Filter: (code = ANY ('{B,C}'::enum_code[]))"
Rows Removed by Filter: 1843
Buffers: shared hit=4 read=1124
I/O Timings: read=8084.544

Reading 1124 blocks to get 1904 rows is unfortunate, but normal.
What is not normal is the I/O time: almost 20 ms for a single 8kB block. Either your I/O system is really overloaded, or the disk is amazingly slow.


How to force the use of row locks? I am trying to get my table to use row locks. I have already disabled lock escalation. The isolation  level is READ_COMMITTED_SNAPSHOT

alter table <TABLE_NAME> SET (LOCK_ESCALATION=DISABLE)
go
alter index <INDEX_NAME> ON <TABLE_NAME> SET (ALLOW_PAGE_LOCKS=OFF)
go

It's still not working after setting this.

Do I need to rebuild my table in order for it to not use page locking?

The setting to disable page locking applies per index, so applying this change to the clustered index only affects execution plans that access the data via that index.  If there are nonclustered indexes on the table, you may have to disable page locking for them as well.  The following script demonstrates this:

CREATE TABLE dbo.LockTest
(
    col1    integer IDENTITY NOT NULL,
    col2    integer NOT NULL,
    col3    integer NOT NULL,

    CONSTRAINT PK_LockTest
        PRIMARY KEY CLUSTERED (col1)
);
GO
CREATE UNIQUE NONCLUSTERED INDEX UQ_LockTest
ON dbo.LockTest (col2);
GO
ALTER TABLE dbo.LockTest SET (LOCK_ESCALATION=DISABLE);
ALTER INDEX PK_LockTest ON dbo.LockTest SET (ALLOW_PAGE_LOCKS=OFF);

Using the clustered index as the access method:

-- Error 651:
-- Cannot use the PAGE granularity hint on the table "dbo.LockTest"
-- because locking at the specified granularity is inhibited.
SELECT col1
FROM dbo.LockTest WITH (INDEX(PK_LockTest), PAGLOCK);

Using the nonclustered index:

-- Succeeds: page locks allowed on index UQ_LockTest
SELECT col2 FROM dbo.LockTest AS lt WITH (PAGLOCK);

-- Succeeds: UQ_LockTest also includes col1
-- NC indexes always include the clustering key
SELECT col1 FROM dbo.LockTest AS lt WITH (PAGLOCK);

-- Fails: cannot retrieve col3 without touching the clustered index
SELECT col3 FROM dbo.LockTest AS lt WITH (PAGLOCK);

Note that disabling page locking can have unexpected side-effects, such as preventing index reorganization (since this process works at the page level):

-- Error 1943:
-- The index "PK_LockTest" on table "LockTest" cannot
-- be reorganized because page level locking is disabled.
ALTER INDEX PK_LockTest ON dbo.LockTest REORGANIZE;

-- Succeeds (page locking available):
ALTER INDEX UQ_LockTest ON dbo.LockTest REORGANIZE;


basic question about user-/role concept in postgres-databases I hope this question is specific enough to be allowed and not doomed as opinion-based:

I'm fairly new at administrating postgres databases. My basic approach was to create a new user / role for each real-world user. I thought it would be advantageous for use-cases, where users simultaneously access the same database. However, I realised this has some disadvantages in terms of granting access-right to future tables.

I am now suspecting that my concept (separate user-account for each real-word user ) is not the "usual way". I could imagine, that it is sufficient to have just a generic user / role, and each real-world user uses that same generic role to connect, create, read, write, modify data and tables?

Can postgres handle such a setup just fine even in the case of concurrent read/write-actions? Is this second concept (one user-account for many users) just fine and frequently applied?

Thanks in advance for your help (or for pointing out appropriate www-sources I have missed).

There are basically two choices:

Your application does the user and permission management itself. Then it typically has a single “application user” in the database.
Your application has per-user or per-group database users and handles user and permission management with database means.

Both solutions are fine. The second solution is maybe less commendable if you have lots of users; also, connection pooling usually works better with the first technique.

If you use database users like in the second solution, you should use NOLOGIN roles (user groups) in PostgreSQL to carry the permissions, and you assign the named users to the groups as appropriate. That way you don't have to GRANT and REVOKE a lot of permissions whenever you add or remove a user or change a user's role in the application.

To answer your question, there are no restrictions on concurrency with different database users.


How to write a check constraint to disallow empty varchar fields? On SQL Server not nullable varchar columns can hold empty strings.

Under ORACLE they can't, because varchar2 treat '' as equivalent to NULL.

If you design a database schema suitable for both RDBMS it seems to be a good idea to add to each not nullable varchar column on SQL Server a constraint which disallows empty strings.

But which is the best way to formulate such a constraint?

I started with

if OBJECT_ID('varchar_without_empty_cols') > 0 drop table varchar_without_empty_cols
go

create table varchar_without_empty_cols (
id int ,
val varchar(10) not null CHECK (val <> '')
)
go

insert into varchar_without_empty_cols values (1, ' ')  -- this ought be OK
go
insert into varchar_without_empty_cols values (2, '')   -- this has to violate the check
go
insert into varchar_without_empty_cols values (3, null) -- this violates the not null
go

select * from varchar_without_empty_cols

But this constraint not only inhibits empty strings, which is intended, but it also inhibits strings consisting of a single character, and that is not what I intend.

To allow strings containing only spaces but disallow empty strings you can use

CREATE TABLE varchar_without_empty_cols
  (
     id  INT,
     val VARCHAR(10) NOT NULL CHECK (DATALENGTH(val) > 0)
  )


Group by multiple columns with different members of the same hierarchy? I have customer data in a table structure like this:

customerID | productLine1 | productLine2 | productLine3
1          | 1            | null         | null
2          | 2            | 1            | null
3          | 4            | 2            | 1

Each customer is assigned to at least 1 product line but a maximum of three product lines.

I would now like to see how many customers I have for each product line.
Any ideas on how to solve this?

Important: I do not wish to turn columns into rows (which would be a solution).
So the result would be:

productLine "1" --> 3 customers
productLine "2" --> 2 customers
productLine "4" --> 1 customer

Try this ( replace tbl with your table name):

WITH Unpivoted
AS (
    SELECT customerID
        ,ProductLineSlot
        ,ProductLine
    FROM (
        SELECT customerID
            ,productLine1
            ,productLine2
            ,productLine3
        FROM tbl
        ) p
    unpivot(ProductLine FOR ProductLineSlot IN (
                productLine1
                ,productLine2
                ,productLine3
                )) AS unpvt
    )
SELECT ProductLine
    ,count(*) AS [Number Of Customers]
FROM Unpivoted
GROUP BY ProductLine;

Or you can rewrite that without CTEs or derived tables:

SELECT
    ProductLine,
    COUNT(*) AS [Number Of Customers]
FROM
    tbl AS t
    UNPIVOT
    (
      ProductLine
      FOR ProductLineSlot IN (productLine1, productLine2, productLine3)
    ) AS u
GROUP BY
    ProductLine
;

To elaborate a little on how the query works, the UNPIVOT clause turns your table into a row set like this:

customerID  ProductLineSlot  ProductLine
----------  ---------------  -----------
1           productLine1     1
2           productLine1     2
2           productLine2     1
3           productLine1     4
3           productLine2     2
3           productLine3     1

Rows where ProductLine is null are automatically omitted by UNPIVOT. You can see that the above row set can be grouped by ProductLine to get row counts per group for the final result:

ProductLine  Number Of Customers
-----------  -------------------
1            3
2            2
4            1

And that is what the query does.


SQL Server Replication with Historical Tables Is it possible to replicate a SQL Server database and for the replica to have temporal/historical tables?

Details:
I have a production system, running SQL Server 2014. I want to have a replica database and I also want to track historical changes.

It would be ideal to replicate the production Database to a SQL Server 2016 instance and to track changes with temporal/historical tables.

Is this possible?
Is this possible on Azure?

Edit: Part 2

After David Browne's excellent answer and doing some further reading, I have more direction and more questions.
I will be using an Azure SQL Database / Logical Server.
I will be using transactional replication because
1. it is supported on Azure SQL Database and
2. we can have temporal tables disabled on the publisher (Sql Server 2014) and temporal tables enabled on the subscriber (Azure SQL Database / Logical Server) As per David Browne's excellent answer.

Part 2 Questions:
How much additional load is placed on the "publisher" database when it becomes a publisher?

How can I avoid downtime and locks on the publisher when setting this up?

I have multiple "publisher" databases. Can I have multiple subscriber databases on one Azure SQL Database / Logical Server instance?

Is it possible to replicate a SQL Server database and for the replica to have temporal/historical tables?

Yes.  If you use an AG Replica, the primary database and replicas will all have the same Temporal Tables.
If you use Transactional Replication, you can have a Temporal Table at the subscriber, where the table at the publisher is non-temporal.
If you use Change Tracking or Change Data Capture you can use the change data to ship and apply to a temporal table in a reporting database.
See

Usage of replication technologies is limited.
Always On: Fully supported
Change Data Capture and Change Data Tracking: Supported
only on the current table
Snapshot and transactional replication:
Only supported for a single publisher without temporal being enabled
and one subscriber with temporal enabled. In this case, the publisher
is used for an OLTP workload while subscriber serves for offloading
reporting (including 'AS OF' querying). Use of multiple subscribers is
not supported since this scenario may lead to inconsistent temporal
data as each of them would depend on the local system clock.
Merge
replication: Not supported for temporal tables

Temporal Table Considerations and Limitations


Are SQL Server Database writes slower with Snapshot Isolation? I have a lot of deadlocks going on in my system.

I would like to use Snapshot Isolation to fix them, but my DBA has reserves about it.

One of his concerns is that Snapshot Isolation slows down writes.  This is because it has to write to cache and then to the TempDb (row version) and then it can return to the caller.

A "normal" write can just write to the cache and then be done.

Is this how the row versioning works?  Or is it more complex than that?  Does it somehow do these in parallel?

Or are writes slower with Snapshot Isolation?

is because it has to write to cache and then to the TempDb (row version) and then it can return to the caller.

No, this is incorrect. It somehow implies that writes in the presence of versioning have higher latency as each write has to touch the disk (for tempdb) which is not true. The write into the tempdb is also a write into 'cache'. The only 'wait' occurs at COMMIT time when the log has to be hardened. Is true that with versioning both the DB log and the tempdb log have to be hardened, but this does not necessarily implies higher latency (the IO should be paralel on different storage paths, the tempdb is stored on separate drive from your heavily used LDF, right?). For a full blown explanation read How It Works: Bob Dorr's SQL Server I/O Presentation I really hope your DBA understands this better than you are conveying it here.

As I mentioned in your other post: snapshot has no cost for INSERTS and the cost for updates and deletes can easily be mitigated. Row Versioning Resource Usage explains the trade offs. At this moment you should be probably testing with a realistic workload, which is the only way to properly asses the impact you would experience.


Windows 10, SQLServer Express (2019), Excel 2016 import to SQLServer error Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine Environment:

Workstation: 16GB RAM. 500MB SSD

Windows 10 Pro (64-bit)

Microsoft Office 2010? 2016? (Home/Student?) Not sure how to tell exactly. Office reports the version as 18.2005.1191.0 ; Excel 2016

SQL Server 2019 (Express Edition) Just installed 2 weeks ago.

Not available on workstation (though some supporting libraries may be present, e.g. .NET framework 3.5 && 4.8 for Windows 10):

Visual Studio

C# compiler

etc.
Task:

Import a USA-state-government-supplied .csv (converted by me to an Excel spreadsheet) into SQLServer.

The spreadsheet ~20 columns and ~500K rows of data to be imported into 1 fairly simple SQL Server table. No stored procedures. No constraints, 1 Primary (Identity) Key.
Microsoft SQL Server Management Studio query windows limits an insert statement to 1,000 records/rows at a time. I did load 2,000 rows successfully. I do not want to break the Excel spreadsheet into 500 pieces to load the entire data set. So, I thought it would be best to use the database Import and Export Wizard to load the spreadsheet.
Error/roadblock (steps to reproduce):

Within Microsoft SQL Server Management Studio,

I select the database.

I select Tasks > Import Data ...

I follow the wizard's prompts, selecting Data Source -> Microsoft Excel; Excel file path (path to local spreadsheet file); Excel version -> Microsoft Excel 97-2003 (or Microsoft Excel 2007-2010 or Microsoft Excel 2013 or Excel 2016); First row has column names; Next

An error is displayed:

TITLE: SQL Server Import and Export Wizard
The operation could not be completed.
ADDITIONAL INFORMATION:
The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. (System.Data)

How can I fix this?
Additional info:

ODBC Data Source Administrator (64-bit) Drivers tab has entries for

Microsoft Excel Driver 16.00.4513.1000 (ACEODBC.DLL)

SQL Server 10.00.18362.01 (SQLSVRV32.DLL)

ODBC Data Source Administrator (32-bit) Drivers tab has entries  for

Microsoft Excel Driver 10.00.18362.01 (ODBCJT32.DLL)

SQL Server 10.00.18362.01 (SQLSVRV32.DLL)

Finally:
I saw this: https://datasavvy.me/2017/07/20/installing-the-microsoft-ace-oledb-12-0-provider-for-both-64-bit-and-32-bit-processing/
I tried the steps for Option B, but they did not change my outcome.

Microsoft SQL Server Management Studio launched the 32-bit version of the import wizard.
I found the 64-bit version of the import wizard already installed as a standalone App. Launching that standalone App worked.


How to partition a table by timestamp, where data from every i'th day goes to partition i? I'm looking into partitioning a table in my InnoDB database.  I have a column corresponding to a UTC timestamp, and I want to partition around that.

The basic idea that I want to capture is a partitioning scheme where, with a fixed number of partitions, we cycle through them as follows:

Given 3 partitions (3 for simplicity)

  Data with timestamp column from day 1 goes into partition 1
  Data with timestamp column from day 2 goes into partition 2
  Data with timestamp column from day 3 goes into partition 3
  Data with timestamp column from day 4 goes into partition 1
  ..... day 5 .... partition 2
  ... rinse and repeat

Basically, extract the day out of the timestamp and put the row into partition DAY MOD N where DAY is the day that the timestamp corresponds to (filtering out hours/minutes/seconds) and N is the number of partitions.

This is probably easily done, but I can't find a similar example to emulate this with.  What would the ALTER TABLE query be to partition in this fashion?

Update

By atxdba's suggestion, I tried partitioning by hash.  I tried the following statement:

ALTER TABLE table_to_partition PARTITION BY HASH(DAYOFMONTH(FROM_UNIXTIME(my_timestamp))) partitions 8;

This results in error code 1564: This partition function is not allowed.  Looking at this list of limitations for partitioning, it doesn't appear that FROM_UNIXTIMESTAMP is supported for partioning a table, so a different transformation from timestamp to date is required.

You can't use FROM_UNIXTIME() because hash partitions must be based on an integer expression.  But assuming your timestamp is stored as an integer, you can use DIV to return an integer.

Here's a demo of partitioning in the way you describe:

mysql> create table table_to_partition (
  my_timestamp int unsigned primary key
) partition by hash(my_timestamp DIV (60*60*24)) partitions 3;

mysql> insert into table_to_partition values (unix_timestamp(now()));
mysql> insert into table_to_partition values (unix_timestamp(now()-interval 1 day));
mysql> insert into table_to_partition values (unix_timestamp(now()-interval 2 day));
mysql> insert into table_to_partition values (unix_timestamp(now()-interval 3 day));
mysql> insert into table_to_partition values (unix_timestamp(now()-interval 4 day));
mysql> insert into table_to_partition values (unix_timestamp(now()-interval 5 day));

mysql> select table_name, partition_name, table_rows
from information_schema.partitions where table_name='table_to_partition';

+--------------------+----------------+------------+
| table_name         | partition_name | table_rows |
+--------------------+----------------+------------+
| table_to_partition | p0             |          2 |
| table_to_partition | p1             |          2 |
| table_to_partition | p2             |          2 |
+--------------------+----------------+------------+


SQL Server Multiple Installations? I have been using SQL Server 2012 on my laptop, but now I want to install SQL Server 2014 trial version as I am following an online course on it.

Can I go ahead with the installation of SQL Server 2014 trial with SQL Server Data Tools installation separately?  Is it advisable to do that or will it create issues and errors if I try to do so?

You should have no problem running multiple versions of SQL Server on the same box.  By default, they will install into separate folders, and won't interfere with each other.

Having said that, I wouldn't bother with the trial version; just use the free Developer Edition assuming you're not using production data.


How to design DB for statistics service? I'm going to create statistics on RoR. I have 3 models: User, Project and Channel. User owns Projects, whereas Projects have many Channels. Now I'm designing all these models using MySQL.

The goal of the service is to provide user the variation in time of different parameters of each Channel. E.g.: Channel Google Adwords has 1000 show/day, 100 clicks/day and 2 people which came from Google Adwords bought something that day. So I could have just stored all the information in columns, like: Channel "Google Adwords", shows: 1000, clicks: 100, buyings: 2. But I need to make distribution by days, please, give me an advice how to design such database? Maybe I should use another solution, maybe NoSQL?

Upd.

Input: User daily fills in data about ads from different sources (I call them channels, e.g.: Google Adwords): number of impressions and clicks (later data will be imported automatically usiing API).
Output: Various stats like graphs and CTR of different channels, all channels and so on.

I'd recommend you to make a separate table for each attribute. Like so

table impressions
-channel_id
-date timestamp
-other attributes like ad_id

table clicks
-channel_id
-date timestamp
-ad_id

For every event you just make an insert, to show stats it is also very efficient - it is just index scan with count or aggregate function.
Also this approach scales well - you can easily shard such tables. Or replace it with nosql solution. For such a task i'd recommend HBase - this datamodel naturally translates in HBase schema, and HBase is very good at range scans.

UPD: I see you updated qustion, so i need to clarify.
Basic tables which still usefull to store Facts. Over them you can immediately make graphs and reports. If you concern about performance, people usually preaggregate data. So if your people need 5/10/30 minutes resolution, you aggregate into 5/10/30_minute_table for fast reporting.

This batch processing of old data takes time. If it doesn't fit your SLA, modern guys take CEP engines like twitter Storm to analyze and aggregate this table in real time. Or you can just buffer events for a minute, aggregate them and then store. I don't know how RoR fits this role and i don't know your requirements. But i think thats another story.


Querying a view that is defined as "select * from" I am trying to understand how columns/indexes are used when querying a view that is defined as

select * from someTable where someColumn=someValue

Will using this view always be bad, or will SQL Server take into account the actual columns selected and use appropriate indexes in a query like

select column1, column2 from theView

I think this would actually be a good exercise for you.  Create a table with a bunch of columns.  Create several different indexes (including a clustered index) on the table.  Try several variations of SELECT's against the table and tell SSMS to include the actual execution plan.  You will be able to see first-hand what indexes (if any) are used and what columns are returned.  Then, you won't have to take someone's word for it.

As far as SELECT * in general, there is some excellent information, courtesy of Dave Markle, here.

To summarize the linked post:

  Here are really three major reasons why SELECT * can be problematic:

Inefficiency in moving data to the consumer. When you SELECT *, you're often retrieving more columns from the database than your application really needs to function. This causes more data to move from the database server to the client, slowing access and increasing load on your machines, as well as taking more time to travel across the network. This is especially true when someone adds new columns to underlying tables that didn't exist and weren't needed when the original consumers coded their data access.
Indexing issues. Consider a scenario where you want to tune a query to a high level of performance. If you were to use *, and it returned more columns than you actually needed, the server would often have to perform more expensive methods to retrieve your data than it otherwise might. For example, you wouldn't be able to create an index which simply covered the columns in your SELECT list, and even if you did (including all columns [shudder]), the next guy who came around and added a column to the underlying table would cause the optimizer to ignore your optimized covering index, and you'd likely find that the performance of your query would drop substantially for no readily apparent reason.
Binding Problems. When you SELECT *, it's possible to retrieve two columns of the same name from two different tables. This can often crash your data consumer. Imagine a query that joins two tables, both of which contain a column called "ID". How would a consumer know which was which? SELECT * can also confuse views (at least in some versions SQL Server) when underlying table structures change -- the view is not rebuilt, and the data which comes back can be nonsense. And the worst part of it is that you can take care to name your columns whatever you want, but the next guy who comes along might have no way of knowing that he has to worry about adding a column which will collide with your already-developed names.

But it's not all bad for SELECT *. It is often used it liberally for these use cases:

Ad-hoc queries. When trying to debug something, especially off a narrow table I might not be familiar with, SELECT * is often my best friend. It helps me just see what's going on without having to do a boatload of research as to what the underlying column names are. This gets to be a bigger "plus" the longer the column names get.
When * means "a row". In the following use cases, SELECT * is just fine, and rumors that it's a performance killer are just urban legends which may have had some validity many years ago, but don't now:


Oracle 11g max login fail attempts workaround My problem with database starts with situation where I cant really modify anything in database. My project specialist has limited time to help me. Here is the thing:
My user in Oracle database has older schema than actual production one. My section is working on stable and older version. After every release we are keep getting this issue, that something is set (maybe on Jenkins, maybe not) automatically to update our database to version, which we dont want. We tried to resolve it by changing password to user, but it produce new issue. Automat is trying to log in and when it gets wrong pass error, it is trying again. Oracle 11g has this limit 10 failed login attempts, after which it is locking the whole user account, which we use to connect do db by our application server.

We can not investigate this by turning on auditing failed logins, because it takes place on database space and our db-guy has not allowed us to do it, because if we exceed the space limit (which is about 11GB) the whole database will be dead (our project is not as important to do it). Another thing is that person who probably set the scripts which are our problem doesnt work anymore here.

Our workaround was to manually unlock account to get the connection by application server, and then wait a few secs to get locked again (but the connection of app server was stable). It is stupid, you must admit and the problem is when the connection drops by any reason - app server will not get it automatically, we have to do it manually which is not a solution.

I have reconsidered it all again, my db-guy has no time to help me, I have no tools and access rights to investigate where this script or whatever other problem causing thing is beeing executed, so I started to thinking: what if we set limit of failed login attempts to unlimited? Will this decrease the performance of database? Will this generate any special new problems? Maybe the solution would be change the PASSWORD_LOCK_TIME to small value?

I am asking you to some arguments that I could provide to my db-guy to convince him to use this new workarounds so I can start working again with code and not this database problems.

The account that your application server uses to connect to the database should not lock out under any circumstances. It's a very simple security issue - it makes for the easiest denial-of-service attack in the world. Send n db login requests with random password -> application can't connect. You should definitely set this to unlimited.

(You should also talk to the manager of the DBA if he is really being so willfully unhelpful, but that's a non-technical issue that I don't think Stack Exchange can help you with. :D )


Is there a tuning parameter for MySQL that allows you to set an on-disk gap between non-sequential primary keys? I understand that one of the primary issues with using a non-sequential primary key is that every disk/memory write that does not come after the last known key causes a re-write of everything from the point of insertion on.

I'm looking at using a modified uuid that will be semi-sequential--ie, a scope-related number will be prepended to the uuid in order to provide some gap optimization for data in a common scope.

What would be ideal is to force InnoDB to write rows a certain distance apart based on A) the sequence of known keys, and B) table growth estimates.

A simplified example is this:

Disk w/o this tuning:

|< start of disk
| row 2592
| row 33093
| row 34928
| row 50983
| row 390853
| row 391985
|
| free space ...
|
|< end of disk

Disk with this tuning:

|< start of disk
| row 2592
|
|
|
| row 33093
|
| row 34928
|
|
| row 50983
|
|
|
| row 390853
|
| row 391985
|
|< configurable end of allocated disk
|
| free space ...
|
|< end of disk

In the second case, most writes would not require a re-write. Even if you run out of space between, we could then do a full re-alocation and cut the number of row re-writes down by a considerable factor.

Does something like this exist?

Your understanding is completely wrong, so the question does not really make sense. Rows beyond the value of the point of insertion are not rewritten with insertion. Rows aren't even re-ordered within the page level for insertion; insertion happens into empty space within the page, and the rows are linked together in ascending order through a singly-linked list.

It may be informative to check out this series of blog posts I wrote: http://blog.jcole.us/innodb/


Set MySql read-only from K8s cluster I have a Kubernetes cluster running with MySQL app (5.7.32 MySQL version). I'm trying to make MySQL app be in a consistency mode, using this command:
FLUSH TABLES WITH READ LOCK; SET_GLOBAL read_only = ON;

When I run this command from inside the app - it works (getting error when trying to write when the DB is locked):
kubectl exec -ti mysql-0 -c mysql -- mysql

mysql> FLUSH TABLES WITH READ LOCK; SET_GLOBAL read_only = ON; INSERT INTO mydb.mytable VALUES('hello');

But When I run this command using kubectl exec command - it doesn't work (this command doesn't fail, and it is able to write to the DB):
kubectl exec -ti mysql-0 -c mysql -- mysql -h mysql \
-e "FLUSH TABLES WITH READ LOCK; SET_GLOBAL read_only = ON; INSERT INTO mydb.mytable VALUES('hello'); "

I also try using a grant privileged user, it didn't help:
kubectl exec -ti mysql-0 -c mysql -- mysql -h mysql \
-e "CREATE USER $USER@'$HOST' IDENTIFIED BY '$PASS'; GRANT ALL PRIVILEGES ON * . * TO $USER@'$HOST';"

kubectl exec -ti mysql-0 -c mysql -- mysql -h mysql -u $USER -p$PASS \
-e "FLUSH TABLES WITH READ LOCK; SET_GLOBAL read_only = ON; INSERT INTO mydb.mytable VALUES('hello'); "

How can I bring my MySQL app to be in a read-only state without running the command from inside the application?

I think that the SUPER privilege allows the user to write to a read_only server.
Do NOT use such a user for anything other than admin purposes.  Use less-privileged user(s) for application actions.  Usually, an "app" user should be granted only ... ON dbname.* ....


mariabackup of database running in docker I have MariaDB running in a Docker container with the container's data directory /var/lib/mysql/ mapped to the host directory /mnt/data/.

I want to run mariabackup from the host to create a physical backup of the data directory (which is /mnt/data as far as the host is concerned and /var/lib/mysql/ from the container's perspective).

I get an error, however, running mariabackup that says it cannot find the data directory:

$ mariabackup --backup --user=backup --stream=xbstream -h /mnt/data/ | gzip > foo.gz
Connecting to MySQL server host: 127.0.0.1, user: backup, password: set, port: 3306 ...
Using server version 10.3.10-MariaDB-1:10.3.10+maria~bionic
Warning: MySQL variable 'datadir' points to nonexistent directory '/var/lib/mysql/'
Warning: option 'datadir' has different balues:
    '/mnt/data/' in defaults file
    '/var/lib/mysql/' in SHOW VARIABLES
mariabackup based on MariaDB server 10.1.29-MariaDB debian-linux-gnu (x86_54)
mariabackup: uses posix_fadvise().
mariabackup: Can't change dir to '/var/lib/mysql/ (Errcode: 2 "No such file or directory")
mariabackup: cannot my_setwd /var/lib/mysql/

Something similar happens when I don't specify the data directory with the -h flag. I expected mariabackup to cd to /mnt/data/, execute the commands it needs to run on the server, and everything to simply work because it doesn't seem like it should matter what SHOW VARIABLES says if there's a valid data directory at /mnt/data/.

I understand the warning that mariabackup sees two different paths for datadir, one that I provided via -h and another on the server.
Why, however, does mariabackup try using /var/lib/mysql/ when I explicity said (via the -h flag) that the data directory is /mnt/data/? Can I get mariabackup to ignore what the server says and cd to the directory I provided?
If my approach is all wrong, what's the best way to do physical backups of a server running in a container with data directory mapped to a host directory?

Create a symlink on your host so that Mariabackup thinks /var/lib/mysql exists. Then delete the symlink once you're done.

$ sudo ln -s /mnt/data /var/lib/mysql


Can't create user in postgres When I run CREATE USER username WITH PASSWORD 'test' in sudo -u postgres psql and run \du only postgres user appears... I'm not prompted anything when running commands like ALTER USER and I know USER ALTERED should appear, or something similar.

Did you end the command with a semicolon?

CREATE USER username WITH PASSWORD 'test';


How to deploy SSRS reports build through Report Builder? I would be building new reports, or correcting the already built SSRS reports through report builder. Once the change is done in Development side, then how do you publish the reports on test server/UAT or on production.

What methodology is supposed to be followed?

More information: SSRS would be used of SQL Server 2008 R2 edition, Report builder version is 3.0 and database is also SQL Server 2008 R2.

Let me know if more information is required.

Please Guide Me! Thanks!!

With RB (and sufficient privilege), users can save reports straight to the Report Manager environment. At that point, they're deployed. But if you want to have them save those reports to a Dev/Test box, and want to push them live, there's a few options.

One easy option is to move reports into a known folder 'For Deployment'. You can then have a script which grabs the RDL for reports in here and uploads them to the proper server. You'll probably want to make sure the data sources are mapped appropriately as this method can annoyingly lose references. You can use rs.exe for your scripts, or just use the web references. PowerShell can be very useful here, as it happily talks WebService, and you can schedule PowerShell scripts using SQL Agent jobs.