Synchronise data from one table to another & vice-versa using triggers We are migrating from one database structure to another - very slowly. The system is MySQL. There are two databases. For example's sake, we will call the databases old_db and new_db. Both databases have tables that will need to be synchronised for around a year whilst the business's application is migrated from the old db to the new db.

Again to slim down the example, we have one table in the old database, called: old_db . myoldertable, new_db . mynewtable.

Structure:

old_db.myoldertable
primary_id, Date, amount, unneedded_column1, unneedded_column2 ... and so-on

new_db.mynewtable
primary_id, date, amount, old_table_primary_id

They look similar in structure, but this is simplified.

Problem:

If I put an insert/update/delete trigger on both tables, I will end up in a recursion loop. This is because the tables need to be synchronised, so the insert / update / delete triggers need to be on both tables.

Requirements

Must be 100% MySQL. Cannot use a programming language for this.
The new and old database tables must be synchronised immediately if either have rows that are inserted/updated/deleted.
Daily / hourly updates are NOT an option. Again, must be synchronised immediately.

Solutions?

Ideas to break possibility of recursion for all three insert / update / delete scenarios.
Any other ideas.

EDIT :

So far I have looked at

One solution I have explored is replacing the old table with a view. However, our new database has two tables (or more) where the old database had only one table. Views, in MySQL cannot be writeable to more than one base table. Also views cannot have triggers in MySQL.

I also looked at using a UUID that represents the last update. I would create a column in both tables to contain a Unique ID. Making sure that both tables contain the same UUID as part of the triggers. However, this does not stop recursion.

I concur with @NathanJolly, this is a potential data nightmare and seems ill-advised...  but there is a way to implement this that is, at least conceptually, fairly straightforward.
You need not worry about the possibility of recursion in triggers, because this won't happen... MySQL doesn't support it.
I built this fiddle that creates tables t1 and t2, each of which has an AFTER INSERT trigger that tries to insert the record into the other table... then tries to do an insert.  Theoretically, there's a potential for recursion, but MySQL prevents this:
ERROR 1442 (HY000): Can't update table 't1' in stored function/trigger because it is already
                    used by statement which invoked this stored function/trigger.

So it won't run in a loop, but it still throws an exception.  Technically, it's not trigger recursion causing the exception, because we haven't actually gotten that far... it's the fact that the trigger in t2 tries to modify table t1, which was involved in the currently-executing statement.
Then again, technically, it's not just the recursion of the triggers we need to avoid -- we have to stop one step before that -- if the client modifies t1, the t1 trigger modifies t2, then t2's trigger needs to not even try to do anything to t1 (including, but not limited to, firing the trigger again on t1).
Implementing this is actually fairly straightforward... in each trigger, we toggle the value of a session to tell the other trigger not to run.
CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW BEGIN
  IF @__disable_trigger_t1t2 = 1 THEN
    SET @__disable_trigger_t1t2 = NULL;
  ELSE
    SET @__disable_trigger_t1t2 = 1;
    -- trigger logic goes in here
    INSERT INTO t2 (id,things) VALUES (NEW.id,NEW.stuff);
  END IF;
END //

CREATE TRIGGER t2_ai AFTER INSERT ON t2 FOR EACH ROW BEGIN
  IF @__disable_trigger_t1t2 = 1 THEN
    SET @__disable_trigger_t1t2 = NULL;
  ELSE
    SET @__disable_trigger_t1t2 = 1;
    -- trigger logic goes in here
    INSERT INTO t1 (id,stuff) VALUES (NEW.id,NEW.things);
  END IF;
END //

A test case for this logic working can be seen in this fiddle.
Essentially, when a trigger fires, it checks the value of the @__disable_trigger_t1t2 variable (this variable name is, of course, arbitrary).  If '1', it sets it back to NULL (the default for session variables) and does no further processing, because the '1' means this row is being manipulated by the trigger on the other table.  If already NULL, then it sets the value to 1 and executes the trigger logic -- which will update the other table, whose trigger will see the '1', will not execute its own logic, and will reset the variable back to NULL, so that the next execution of either trigger -- whether for the next row in the same statement, or for a subsequent statement -- will see the NULL and execute normally.
In MySQL, triggers are only supported FOR EACH ROW, meaning that we can define a MySQL trigger as "a stored program that is executed before or after each row is inserted, updated, or deleted"... which means that no matter how many rows your query affects, the trigger will run once for each row.  Actions against other tables will cause the triggers on those tables to fire, and the execution of the trigger on t1 for the first row will not complete until all of the triggers it caused to fire have also completed... and this is why we can use this variable like this -- only one row is actually being handled at a time, so we set and clear this variable for each row, always leaving it NULL after each statement terminates.
A significant caveat is that this approach fails if you need to trigger on cascading deletes or updates related to foreign keys, because:

"Currently, cascaded foreign key actions do not activate triggers."
-- http://dev.mysql.com/doc/refman/5.6/en/create-trigger.html

So, while I have misgivings about the long-term viability of maintaining database synchronization with this approach because of the number of things that could go wrong with two applications manipulating each other's data or logical errors in the trigger definitions... it seems technically possible.
You also mentioned:

views cannot have triggers in MySQL

No, but updating an updatable view will fire the triggers on the underlying base table as if the table had been directly manipulated.


Give a “super” user more work_mem One common pattern I’ve noticed over the years is that people tend to keep increasing working memory as their data size increases, so that more complex queries can still take full advantage of memory speed, but for some extreme tasks (e.g., reporting).

It could be advantageous to increase that number drastically only for some outlier queries that are seldom run. For instance, I might want to keep work_mem at eg. 8 MB for normal operations but then run a specific query with a specific client/user having something like 250 MB of working memory so that I can do drastic things entirely in memory during a single session, quickly.

Is this possible? Am I completely off base for thinking of this as a solution?

You have a few possibilities.

To change work_mem for a user is easy:

ALTER ROLE alice SET work_mem TO '5GB';

If you can arrange things like alice is the user running those heavy queries, this might be enough.  If this number is huge (a significant portion of the RAM you have on your server), it makes sense to limit the user to have only a small number of connections (even 1 might make sense) at any time:

ALTER ROLE alice WITH CONNECTION LIMIT 2;

If this is not feasible, you can store your queries as functions.  The advantage of this is that you can set chosen parameters when defining the function, or after it:

ALTER FUNCTION heavy(integer) SET work_mem TO '2GB';
ALTER FUNCTION very_heavy(integer) SET work_mem TO '13GB';


Set up a MySQL slave using mysqldump to get the initial data I have a master database which already has some data in it. The tables in the master database include both MyISAM and InnoDB engines. Now I want use mysqldump to dump the initial data of my slave MySQL server. What parameter I should pass to mysqldump? Currently what I use is listed below.

mysqldump --host=localhost --user=root --password=pa4word --single-transaction --lock-all-tables --master-data=1 mydb > result.sql

Is this command OK? I don't know whether this is suit to my environment(both MyISAM and InnoDB engine tables in one database).

Thanks,

If you are dumping a mysql database that has a mixture of InnoDB and MyISAM and you have scheduled downtime:

mysqldump -u... -p... --master-data=1 --single-transaction --flush-privileges --routines --triggers --all-databases > /root/mydata.sql

Since --opt is enabled by default, the following options are already enabled

--add-drop-table
--add-locks
--create-options
--quick
--lock-tables
--set-charset
--disable-keys

If you are concerned about InnoDB/MyISAM together in the mysqldump and you want the database placed in a read-only state, try putting a read lock across all tables manually.

MYSQL_CONN="-hhostip -uusername -ppassword"
mysql ${MYSQL_CONN} -A -e"FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)" &
SEARCHING_FOR_SLEEP=0
while [ ${SEARCHING_FOR_SLEEP} -eq 0 ]
do
    sleep 3
    SEARCHING_FOR_SLEEP=`${MYSQL} ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep -c "SELECT SLEEP(86400)"`
done
sleep 1
SLEEP_ID=`mysql ${MYSQL_CONN} -A -e"SHOW PROCESSLIST;" | grep "SELECT SLEEP(86400)" | awk '{print $1}'`

mysqldump ${MYSQL_CONN} --master-data=2 --single-transaction --flush-privileges --routines --triggers --all-databases > /root/mydata.sql

mysql ${MYSQL_CONN} -A -e"KILL ${SLEEP_ID}"

The reason this is better to do is that --single-transaction does not protect MyISAM from changing during the dump.

Before launching the mysqldump, make sure binary logging is enabled on the master. If it is not, do the following:

Step 01) Add this to /etc/my.cnf on the master

[mysqld]
log-bin=mysql-bin

Step 02) service mysql restart

This will enable binary logging on the master.

On the new slave, you can run the following command:

CHANGE MASTER TO
MASTER_HOST='IP of the master',
MASTER_PORT=3306,
MASTER_USER='whatever_username',
MASTER_PASSWORD='whatever_password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=107;

Make sure the rep_username exists in the master. If it does not, run this command on the master:

GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT
ON *.* TO 'whatever_username'@'%' IDENTIFIED BY 'whatever_password';

Then do the mysqldump to /root/mydata.sql.

Move the /root/mydata.sql from the master to the slave.

Next, execute in the mysql client this command on the slave:

source /root/mydata.sql

This will load mysqldump in the slave and the set the correct log file and log position in /var/lib/mysql/master.info.

Finally, run START SLAVE; on the slave and mysql replication should get going.

Run SHOW SLAVE STATUS\G to check replication status.

Give it a Try !!!


Best way to optimize an aggregated issues over time query? I have a query that creates an aggregated issues over time report that includes back-filling data from entities with missing dates. I've been using the following query for a few years but it is starting max out my DB CPU on big datasets. I'm a little lost on what to focus on first and I could use some guidance.
SELECT t1.yearmonth, SUM(rt.errors) as errors, SUM(rt.alerts) as alerts FROM
  (SELECT d.yearmonth, r.user_id, MAX(r.date) as date FROM
    (SELECT DISTINCT  date_add(date, interval  -WEEKDAY(date)-1 day) as yearmonth FROM reports) d
  INNER JOIN reports r ON d.yearmonth >=  date_add(r.date, interval  -WEEKDAY(r.date)-1 day)
  GROUP BY d.yearmonth, r.user_id ) t1
INNER JOIN reports r1 ON r1.user_id = t1.user_id AND r1.date = t1.date
INNER JOIN report_totals rt ON rt.report_id = r1.id
GROUP BY t1.yearmonth
ORDER BY t1.yearmonth

The example here creates results based on a weekly interval, but my application supports daily, monthly, quarterly, and yearly as well.
I do have a SQL fiddle setup here for further context
http://sqlfiddle.com/#!9/6c7cc82/7
I think the performance hit may be coming from the date_add and date_format functions, but I'm not sure how to accomplish what I'm doing without them. Maybe a calendar table? Any help would be appreciated, thanks!

Save the aggregation in a "summary table".  Then add a new row(s) each night.  The 'report' would sum the sums and sum the counts.  (Average would be (sum of sums) / (sum of counts).)
I'm confused.  You seem to have a date in the Fact table, yet you want to summarize for one day?  Or a range of days?  Please provide SHOW CREATE TABLE and say approximately how big the tables are.
More discussion:  http://mysql.rjweb.org/doc.php/summarytables


What logical structure is used for storing rows in Oracle? From the logical data separation point, there are tablespaces that consist of segments that consist of extend that consist of oracle blocks  that consist of OS blocks. What logical structure actually hold data of concrete record (where the data of all fields of the same record reside side by side in filesystem). In the world of MySQL, there's data page (8, 16 KB) that holds one of more records at the same physical address.

Also, how to determine what size if the best? The more such pages exist the more data fragmentation will appear, and that might affect performance.

The block is the structure that holds row data. The Logical Storage Structure documentation has a detailed overview of all this.

Note that data for a single row can reside on more than one block (this is called row chaining), so it need not be contiguous on disk. Row migration can also leave just a "forwarding address" in the row's original block that points to the row's new block of residence.

All I/O to and from the SGA is done in block-sized chunks (possibly more than one block at a time, but, for the SGA, never smaller than a block).
The block size of the SYSTEM tablespace determines the database's "natural" block size. You can have other tablespaces with different block sizes but:

you can't mix block sizes inside a same tablespace
you will need to set up separate cache areas for each block size (you can't have for example 8k blocks and 16k blocks in the same block cache)

So in general, it is simpler to use only one block size, if only to avoid the administrative and tuning overhead of managing the size of multiple block buffers.

How you determine the optimal block size for a database is a compromise just like pretty much everything else.
Smaller blocks means your SGA is more granular, but each block has relatively more overhead. And wide rows in small blocks will be more likely to chain or migrate which can be a source of performance problems.
Larger blocks means less chances of chaining (assuming PCTFREE and PCTUSED are reasonably set). But it also means large single-block I/Os and less granularity in your SGA. Since a change in any row of one block requires the whole block to be written out to disk, larger block sizes could lead to more write I/O volume than smaller ones.
Another factor to take into account is that traditional (not bigfile) datafiles are limited to 4M blocks. That's about 32G for 8k blocks, but only 8G for 2k blocks. Since the number of files per (non-bigfile) tablespace is limited (to 1022), this could be important. (There is also a maximum of 65533 files per database, possibly less. See physical database limits.)

Transactional systems tend to use relatively smaller block sizes than datawarehousing systems, but that really isn't anything like a hard and fast rule. (e.g. current Oracle best practice guidelines on Exadata is 8k blocks regardless of scenarios.).


The query result changes in remote and local database The below table is created in local database and remote databases.

CREATE TABLE EMPLOYEE1 ( EMP_ID INTEGER, EMP_NAME VARCHAR(10), EMP_DEPT VARCHAR(10) );

Insert the below rows in tables created in both the databases.

INSERT INTO EMPLOYEE1 (EMP_ID, EMP_NAME,EMP_DEPT)
VALUES (1,'A','IT'), (2,'B','IT'), (3,'C','SALES'), (4,'D','SALES'), (5,'E','ACCOUNTS'), (6,'F','ACCOUNTS'), (7,'G','HR'), (8,'H','HR');

COMMIT;

If i run the below query in local database of my system then the query result is correct.i.e it is returning all the rows in the table as the query exactly has to do. But the same query if i run in remote database then only 4 rows are returned,which is a wrong result.

SELECT * FROM EMPLOYEE1 WHERE (EMP_DEPT NOT IN ('IT','SALES') OR EMP_DEPT IN ('IT','SALES'));

**Can anyone suggest why the query behavior changes?**I am using DB2 9.7 Express C

It might be that there is an unenforced (ie, informational) constraint on the table. Those may cause the optimizer to ignore the data that violates that constraint. Here is a good blog post about that.


Performance of different precedence pseudocolumn solutions with LIMIT I was wondering about the relative performance of the solutions provided in the answers to a question on stackoverflow, I decided to run some tests.

The OP wanted to get the first matching row given a set of conditions with decreasing precedence.  Both solutions involved a pseudocolumn, but one (mine) involved multiple SELECT statements UNION ALLed together, while the other one constructed a CASE expression.

I share my results in the hope that somebody will find this useful.

Your test design is flawed. You are testing incorrect results.

I added an answer to the question on SO you are referring to.

In your CASE version you can't add ORDER BY col1, col2. Would have to be ORDER BY precedence. But it would still be incorrect. You would have to ORDER BY the sum of scores for individual conditions to get rows fulfilling the most conditions first.

Similarly, your UNION ALL version yields incorrect results.

However, none of this seems necessary, there is a simpler and faster solution with UNION ALL. Refer to my answer to the SO question or use the sqlfiddle to play with


Functional Index LOWER PostgreSQL not working The below SQL query runs in 2s:

SELECT
    *
FROM
    fb_name
    INNER JOIN fb_kg ON mid = subject_mid
    WHERE
        LOWER(alias) = LOWER('Polaski')
        AND fb_kg.relation = 'location/location/containedby'

The below SQL query runs in 2ms:

SELECT
    *
FROM
    fb_name
    INNER JOIN fb_kg ON mid = subject_mid
    WHERE
        alias = 'Polaski'
        AND fb_kg.relation = 'location/location/containedby'

The difference between the two queries is the inclusion of the LOWER function.

There has been an index created for both queries:

CREATE INDEX fb_name_lower_alias ON fb_name (lower(alias));
CREATE INDEX fb_name_mid_lower_alias_index ON fb_name (mid, lower(alias));
CREATE INDEX fb_name_alias ON fb_name (alias);
CREATE INDEX fb_name_mid_alias_index ON fb_name (mid, alias);

EXPLAIN for LOWER:

"Gather  (cost=247515.73..249133.22 rows=5708 width=70)"
"  Workers Planned: 2"
"  ->  Merge Join  (cost=246515.73..247562.42 rows=2378 width=70)"
"        Merge Cond: ((fb_kg.subject_mid)::text = (fb_name.mid)::text)"
"        ->  Sort  (cost=205352.30..205794.73 rows=176972 width=44)"
"              Sort Key: fb_kg.subject_mid"
"              ->  Parallel Bitmap Heap Scan on fb_kg  (cost=14116.24..184480.39 rows=176972 width=44)"
"                    Recheck Cond: ((relation)::text = 'location/location/containedby'::text)"
"                    ->  Bitmap Index Scan on relation_index  (cost=0.00..14010.06 rows=424733 width=0)"
"                          Index Cond: ((relation)::text = 'location/location/containedby'::text)"
"        ->  Sort  (cost=41163.43..41232.45 rows=27611 width=26)"
"              Sort Key: fb_name.mid"
"              ->  Bitmap Heap Scan on fb_name  (cost=750.54..39126.71 rows=27611 width=26)"
"                    Recheck Cond: (lower((alias)::text) = 'polaski'::text)"
"                    ->  Bitmap Index Scan on fb_name_lower_alias_varchar_pattern_ops  (cost=0.00..743.64 rows=27611 width=0)"
"                          Index Cond: (lower((alias)::text) = 'polaski'::text)"

Other EXPLAIN:

"Nested Loop  (cost=11.45..13502.52 rows=2 width=70)"
"  ->  Bitmap Heap Scan on fb_name  (cost=4.64..48.23 rows=11 width=26)"
"        Recheck Cond: ((alias)::text = 'Polaski'::text)"
"        ->  Bitmap Index Scan on fb_name_alias_index  (cost=0.00..4.64 rows=11 width=0)"
"              Index Cond: ((alias)::text = 'Polaski'::text)"
"  ->  Bitmap Heap Scan on fb_kg  (cost=6.81..1223.04 rows=8 width=44)"
"        Recheck Cond: ((subject_mid)::text = (fb_name.mid)::text)"
"        Filter: ((relation)::text = 'location/location/containedby'::text)"
"        ->  Bitmap Index Scan on subject_mid_index  (cost=0.00..6.81 rows=316 width=0)"
"              Index Cond: ((subject_mid)::text = (fb_name.mid)::text)"

CREATE TABLE definitions

CREATE TABLE fb_kg
        (object_mid varchar NOT NULL,
        relation varchar NOT NULL,
        subject_mid varchar NOT NULL,
        PRIMARY KEY(object_mid, relation, subject_mid));

CREATE TABLE fb_name
    (mid varchar NOT NULL,
    alias varchar NOT NULL,
    PRIMARY KEY(mid, alias));

How come with the same indexes the LOWER function is about 60x slower?

Using citext

My suggestion is to move to the citext module.

CREATE EXTENSION citext;
BEGIN;
  --out with the old
  DROP INDEX fb_name_lower_alias;
  DROP INDEX fb_name_mid_lower_alias_index;
  DROP INDEX fb_name_alias;
  DROP INDEX fb_name_mid_alias_index;

  --in with the new
  ALTER TABLE fb_name
    ALTER COLUMN alias
    SET DATA TYPE citext;

  CREATE INDEX ON fb_name(mid,alias);
  CREATE INDEX ON fb_name(alias);
  ANALYZE fb_name;
COMMIT;

Then simply stop using lower() everywhere. Your query should like this,

SELECT *
FROM fb_name
INNER JOIN fb_kg ON mid = subject_mid
WHERE alias = 'Polaski'
  AND fb_kg.relation = 'location/location/containedby';

Everything else should work the same. Paste the EXPLAIN ANALYZE of the new query.

Update on indexes, and case-sensitive searching on citext

If  you need a case-sensitive query on a citext column, you just have to cast the other side to ::text, and in fact using the citext index is usually good enough. For example you can do a case-sensitive index only scan with WHERE col::citext = 'fOo'::citext AND col::text = 'fOo'; or, you could create two indexes on the citext column:

one that's case sensitive ON table(myCITEXT)
one that's case insensitive (I find that wasteful though) ON table(myCITEXT::text)


Database MySQL - Transpoing Table from one form to other SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
         CONCAT(
           'MAX(IF(function_NAME = ', function_NAME,
           ',budget, NULL)) AS ', function_NAME))
INTO @sql
FROM country_report dr JOIN function_master am
    ON dr.function_name = am.func_NAME
 WHERE am.func_CATEGORY = 'FUN';

     SELECT country_location, time_of_day,@sql
               FROM country_report dr
               WHERE daily_param = 1
                AND country_location in ('hong kong','tokyo')
              GROUP BY country_location, time_of_day;

output -

| country_location | time_of_day | @sql |
| hong kong        | sod         | MAX(IF(FUNCTION_NAME = Dance program,budget, NULL)) AS Dance program,MAX(IF(FUNCTION_NAME = singing,budget, NULL)) AS singing
| hong kong        | eod         | MAX(IF(FUNCTION_NAME = Dance program,budget, NULL)) AS Dance program,MAX(IF(FUNCTION_NAME = singing,budget, NULL)) AS singing

but required output is

| country_location | time_of_day | Dance program | singing |
| hong kong        | sod         | 3             | 4       |
| hong kong        | sod         | 4             | 4       |

Please suggest

Please post question correctly. I am not getting your schema & your second query is showing @sql variable value to same because of last record value in first query stored to @sql due to last write. Post schema of table with some dummy data.

I am trying to understand & conclude that you need column sub-query. I am setting example which could help you.

select (select name from demo where id=outer.id limit 1) as 'subquery-1' , (select age from demo where id=outer.id limit 1) as 'subquery-2' from outer;

Now for your query would be like-

SELECT country_location, time_of_day,
                          (select MAX(IF(FUNCTION_NAME = 'Dance program',budget, NULL) from country_report dr JOIN function_master am ON dr.function_name = am.func_NAME and  WHERE am.func_CATEGORY = 'FUN' and dr.id=outer.id ) as 'Dance Program',
                          (select MAX(IF(FUNCTION_NAME = 'singing',budget, NULL) from country_report dr JOIN function_master am ON dr.function_name = am.func_NAME and  WHERE am.func_CATEGORY = 'FUN' and dr.id=outer.id) as 'Singing'
       FROM country_report outer
       WHERE daily_param = 1 AND country_location in ('hong kong','tokyo')
       GROUP BY country_location, time_of_day;

May be inner subquery return more than 1 record & show error. So please set conditions as your need.


Specify Values for Template Parameters -- command When I create a stored procedure in SQL Server 2008 by using the template, the comments specify a hot-key combination that no longer works:

-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.

I can use the keyboard combination ALT+Q, S to get the same results. How can I update the template so that it references the working combination rather than the deprecated one?

I sometimes go for long stretches between working with stored procedures, and I don't always remember the correct combination. It would be nice not to have to search for it each time this happens.

EDIT: I tried Jon's suggestion of opening up the template from the template explorer. I was surprised to find the correct combination listed in the template. Is there a way to force SSMS to use the correct template?

Right-click the template in the Template Explorer and click Edit.

EDIT: If you inspect the path to the template files, they are stored buried in your user profile folder. If your AD profiles are out of sync, or the original machine you installed the management tools had different settings, this could account for the difference. You still should be able to edit the templates, though.


ORA-01045 when attempting to connect to PDB. User has CREATE SESSION privilege, but I Oracle says they don't The user has been granted CREATE SESSION but i can only connect to CDB and not the PDB. Why is this? I'm trying this in SQL developer. I have Oracle 19c.

Why do you want to create a common user instead of creating a user in PDB?
For a common user C##test from CDB
grant create session to c##test container=all;

or
From PDB login as sysdba/dba/pdbadmin
grant create session to c##test;


How to keep long multiple of questions and answers in database? I am planning my database design to have 3 sets of multiple questions for 3 types of different customers (at least 3 at the moment), each set of them contains approx. 100 questions and waiting for their answers to be kept in the database.

These questions are in a required questionnaire that is a very crucial material for my job, then posting them all as an email to myself is not an alternative. I am no expert here in this field. I am not sure what is the best approach to store, retrieve, and manage them.

Please take a look at my database design and advice for a better approach.

TABLE: QUESTIONS

SETID
Q1
Q2
Q3
...

TABLE: QUESTIONNAIRE

CUSTOMERID
SETID
A1
A2
A3
...

I have got 3 main problems:

How to display each set of Q&A on PHP page based on customer profile?
The questions need to be displayed on the web page, if by manually, text display, that would have been a time-consuming job for me if I decide to remove, edit, or delete some questions. What way would you recommend?
All the questions (Q1,Q2, and ...) in QUESTIONS table need to map exactly as the answers (A1,A2,and...) in QUESTIONNAIRE table, is there any better approach?

Assuming question has just one correct answer and possible answers don't have to be different for each set of tests, you can do something like (it's a rough idea, I might under (or over) engineer some concepts):

Question : id, question_text, correct_answer_id (FK to answer.id)
Answer   : id, answer_text
Question_possible_answer (question_id (FK to question), answer_id(FK to anwer))
Test     : id, test_name
Test_question : id, test_id(FK to Test), Question (FK to Question)
QUESTIONNAIRE (id, customer_id, test_id, questionnaire_date)   -- I assume that a customer may pass test more than once, so no unique key on (customer_id, test_id), but rather on
(customer_id, test_id,questionnaire_date)
QUESTIONNAIRE_Answer (questionnaire_id (FK to QUESTIONNAIRE), test_question_id(FK to Test_question), answer_id(FK to Answer))

Now for each question you add some possible answers in Question_possible_answer table, then you create a new test and populate Test_question table with the questions. You create a new QUESTIONNAIRE by linking test and customer, all answers from this customer go to QUESTIONNAIRE_Answer table.


System Stats out of sync I have two databases that are in an availability group, one as the primary replica and the other is the secondary.  I have a store procedure that returns the following error.

DBCC execution completed. If DBCC printed error messages, contact your system
administrator.
Msg 2767, Level 16, State 1, Procedure xxxx.sys.sp_table_statistics2_rowset,
Line 105 [Batch Start Line 2]
Could not locate statistics '_WA_Sys_00000026_0143D405' in the system catalogs.
DBCC execution completed. If DBCC printed error messages, contact your system
administrator.

when I run the following query on my secondary replica(read-only):
SELECT OBJECT_NAME(s.object_id) AS object_name
    , COL_NAME(sc.object_id, sc.column_id) AS column_name
    , s.name AS statistics_name
FROM sys.stats AS s
INNER JOIN sys.stats_columns AS sc ON s.stats_id = sc.stats_id AND s.object_id = sc.object_id
WHERE s.name like  '%_WA_Sys_00000026_0143D405%'
ORDER BY s.name;

the stat appears:
STOJOU QTYPCU_0          _WA_Sys_00000026_0143D405
STOJOU QTYPCU_0          _WA_Sys_00000026_0143D405_readonly_database_statistics

when I run the query on the Primary replica it does not appear in the system tables but when you look under the table STOJOU it does appear.
How do I rebuild and/or resync the stats between my two systems?  I am unable to drop/or create the Stat on my primary.  Do I need to stop my synchronization between the primary and secondary to address this?    Thanks for your assistance.

This is an outstanding bug in SQL Server which has been ignored for quite some time, unfortunately.
You can track down the offending statistic and drop/recreate it, or as some others in the above thread have suggested:

Clearing the userstore_dbmetadata related pools on ASYNC commit resolves the issue.
DBCC FREESYSTEMCACHE (‘dbname’)

Or create a stored procedure to check if the instance is an ASYNC commit, then clear the cache using an agent job.

CREATE PROCEDURE [dbo].[sp_clear_dbmetadatacache]
AS
SET NOCOUNT ON
DECLARE @dbname SYSNAME
DECLARE @linebreak AS VARCHAR
SET @linebreak = CHAR + CHAR

CREATE TABLE #dbcc
(
command VARCHAR
)

DECLARE db_cursor CURSOR FOR
SELECT DISTINCT name
FROM sys.sysdatabases db
INNER JOIN sys.dm_hadr_database_replica_cluster_states dbcs
ON db.name = dbcs.database_name
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO dbname WHILE @FETCH_STATUS = 0
BEGIN
CREATE TABLE #avmode
(
availability_mode_desc VARCHAR
)
DECLARE value AS VARCHAR(20) DECLARE @sqlcmd2 AS NVARCHAR(150) SET @sqlcmd2 = 'SELECT DISTINCT availability_mode_desc FROM sys.availability_replicas WHERE replica_server_name = @SERVERNAME’
INSERT INTO #avmode
EXECUTE sp_executesql @sqlcmd2
SELECT @value = availability_mode_desc
FROM #avmode
BEGIN
IF @value = ‘ASYNCHRONOUS_COMMIT’
GOTO A
ELSE
GOTO B
END
A:
DECLARE @sqlcmd AS NVARCHAR
SET @sqlcmd = ‘DBCC FREESYSTEMCACHE (’‘’ + @dbname +’‘’) ’+ @linebreak + ’GO’
INSERT INTO #dbcc EXECUTE sp_executesql @sqlcmd
—PRINT @sqlcmd
B:
DROP TABLE #avmode
FETCH NEXT FROM db_cursor INTO @dbname;
END
CLOSE db_cursor;
DEALLOCATE db_cursor;

GO


Query over SSRS/RPC:Completed a lot slower than SSMS So, as the title says, I have a query that is run by SQL Server Reporting Services, when it ran by SSRS, I can see in SQL Profiler it's executed using RPC Protocol, and takes about 30 seconds. When I execute the same query (I copied it from Profiler to be sure) over SQL Management Studio, it executes instantly (less than 1 second).

Everything is in the same server, SSRS, Management Studio, Profiler and database instance, so I'm also guessing network latency should not be the culprit

The query is common SQL Text and not a Stored Procedure. I also removed the parametrization of the query, and I hard-coded the Id in the query. The same issue persists, slow on SSRS, fast on SSMS. So I don't think it's a problem of parameter sniffing. My query is very simple, a couple joins and I'm filtering the main table by Primary Key. The only "non-standard" feature it uses, is "WITH XMLNAMESPACES" clause, that I use to select a couple XML values using the syntax s.MyXMLColumn.value('(/Details/MyValueHere)

Using SQL Profiler I compared CPU, Read and Duration, only the Duration column has a higher value on the slow execution. I also tried to compare the executions plans using SQL Profiler and in the diagram they are equal, but when comparing the XML there are some differences, that I am not sure if they matter or not. Also the section RunTimeInformation is repeated several times in several places in the plans

Plan for RPC:

<QueryPlan DegreeOfParallelism="0" MemoryGrant="3040" NonParallelPlanReason="CouldNotGenerateValidParallelPlan" CachedPlanSize="640" CompileTime="475" CompileCPU="471" CompileMemory="12832">
    <MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="3040" RequiredMemory="512" DesiredMemory="3040" RequestedMemory="3040" GrantWaitTime="0" GrantedMemory="3040" MaxUsedMemory="24"/>
    <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="104712" EstimatedPagesCached="209424" EstimatedAvailableDegreeOfParallelism="16"/>
    <RelOp NodeId="0" PhysicalOp="Sort" LogicalOp="Distinct Sort" EstimateRows="31.3203" EstimateIO="0.0112613" EstimateCPU="0.000371601" AvgRowSize="28816" EstimatedTotalSubtreeCost="547.917" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
    ...
    ...
    <RunTimeInformation>
        <RunTimeCountersPerThread Thread="0" ActualRows="25" ActualRebinds="25" ActualRewinds="0" ActualEndOfScans="0" ActualExecutions="25"/>
    </RunTimeInformation>
    ...
    ...

Plan for SQL:BatchCompleted

<QueryPlan NonParallelPlanReason="CouldNotGenerateValidParallelPlan" CachedPlanSize="640" CompileTime="484" CompileCPU="484" CompileMemory="12832">
    <MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="3040"/>
    <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="104712" EstimatedPagesCached="209424" EstimatedAvailableDegreeOfParallelism="16"/>
    <RelOp NodeId="0" PhysicalOp="Sort" LogicalOp="Distinct Sort" EstimateRows="31.3203" EstimateIO="0.0112613" EstimateCPU="0.000371601" AvgRowSize="28816" EstimatedTotalSubtreeCost="547.917" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
    <OutputList>
    ...
    ...
    <RunTimeInformation>
        <RunTimeCountersPerThread Thread="0" ActualRows="25" ActualRebinds="25" ActualRewinds="0" ActualEndOfScans="0" ActualExecutions="25"/>
    </RunTimeInformation>
    ...
    ...

I am no DBA, so I'm unsure where else to look. So the question is, how do I find the cause of performance differences between the same query executed over RPC and SQL Batch?

Edit: Additional things I tried based on answers/comments:

SET ARITHABORT OFF makes no difference

This makes me think it is a parameter sniffing problem, I would recommend reading

Slow in the Application, Fast in SSMS? Understanding Performance MysteriesSQL text by Erland Sommarskog, SQL Server MVP. It talks about the problems you can run into when things run almost instantly in SSMS but not in the application.


What is the performance benefit when using a temporary table to update a regular table? I'm trying to understand what the performance benefit between two different methods of updating multiple table rows.

For the first method we have an explicit update statement for each line to be altered.

i.e.

 update my_table set my_field='A' where id=1;
 update my_table set my_field='B' where id=2;
 update my_table set my_field='C' where id=3;
 update my_table set my_field='D' where id=4;
 update my_table set my_field='E' where id=5;

For the second method we use a temporary table.

i.e.

 create temporary table my_temp_table (id,val);

 insert into my_temp_table value (1,'A'),(2,'B'),(3,'C'),(4,'D'),(5,'E');

 update my_table t, my_temp_table tt set t.val=tt.val where t.id=tt.id;

I've been told that the second method is more efficient and thus superior.  I just don't understand why this is so.

Sure we have 5 queries vs 3 queries but that last query is essentially still doing all those updates, albeit implicitly?  Plus it adds in a join too.

So I'm just curious as to why using a temporary table is better?  Is it simply 3 is less than 5, and as such, is better?

EDIT: We are using InnoDB and not explicitly using transactions.  This is just a one off batch update pushed into MYSQL via a file.

First, let's look at a different aspect.  Is your table ENGINE=InnoDB?  If so, and you have not done anything (yet) about "transactions"?  Then 5 Updates is 5 transactions.  There is a significant overhead for each transactions.  Hence, this would be a lot better:

BEGIN;
UPDATE ...;
UPDATE ...;
...
COMMIT;

You had 5 statements versus 3.  I turned the 5 into 7.

If, instead, there were 500 updates to do, that would be 502 versus 3.  Now the overhead of extra round-trips to the server becomes significant.  At this point (502 statements versus 3), creating the tmp table, etc, becomes significantly better.

(There is no need for BEGIN...COMMIT on the tmp approach; the UPDATE is a 'transaction' unto itself, assuming autocommit=1.)


How to get the Total disk space in ms sql 2008 or below I am using ms sql 2008, I wanted to get the total space and available free space on my drives.I used xp_fixeddrives. Unfortunately I got only free disk Space.So does anyone have any other solution so as to get both total disk space and available disk space in ms sql 2008 or below?

CREATE TABLE #drives (
    drive char,
    [free] int
)

INSERT INTO #drives
EXEC master..xp_fixeddrives

For ms sql 2008 R2 and above I can get the total drive space using this query:

WITH core AS (
    SELECT DISTINCT
        s.volume_mount_point [Drive],
        CAST(s.available_bytes / 1048576.0 as decimal(20,2)) [AvailableMBs]
    FROM
        sys.master_files f
        CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) s
)

Since you mentioned you are already using xp_fixeddrives,following code will give you total size. Many shops will not allow this for security reason. I copied the code from here.

There are few more version of this code and example of psinfo.

SET NOCOUNT ON
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20)
DECLARE @MB Numeric ;
SET @MB = 1048576
CREATE TABLE #drives
    (drive char(1) PRIMARY KEY,
     FreeSpace int NULL,
     TotalSize int NULL)

INSERT #drives(drive,FreeSpace)

EXEC master.dbo.xp_fixeddrives

EXEC @hr=sp_OACreate 'Scripting.FileSystemObject', @fso OUT
IF @hr <> 0
EXEC sp_OAGetErrorInfo @fso

DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives ORDER by drive

OPEN dcur FETCH NEXT FROM dcur INTO @drive
WHILE @@FETCH_STATUS=0
BEGIN
EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive

IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso EXEC @hr =
sp_OAGetProperty
@odrive,'TotalSize',
@TotalSize OUT IF @hr <> 0

EXEC sp_OAGetErrorInfo @odrive

UPDATE #drives SET TotalSize=@TotalSize/@MB
WHERE  drive=@drive
FETCH NEXT FROM dcur INTO @drive
End
Close dcur
DEALLOCATE dcur
EXEC @hr=sp_OADestroy @fso IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

SELECT
 drive,
 TotalSize as 'Total(MB)',
 FreeSpace as 'Free(MB)'
FROM #drives
ORDER BY drive
DROP TABLE #drives
GO


PostgreSQL optimistic reads (equivalent to SQL Server's Read Committed Snapshot) Does PostgreSQL provide a built-in optimistic concurrency control mechanism such as SQL Server's Read Committed Snapshot Isolation? Readers can read all the rows without blocking, but writes will block and fail if row was modified during the transaction.

Edit: Writes don't actually 'block and fail' under RCSI but rather just block.

This is how Postgres was designed from ground up (as opposed to SQL Server where this was added very late)

See the manual for details: http://www.postgresql.org/docs/current/static/mvcc-intro.html

You can't actually configure Postgres so that readers would block writers (automatically or implicitly)

The only way to block read access in Postgres is to (manually) lock the complete table exclusively (lock table foo access exclusive).

This will block any access to the table (not only individual rows).

And unlike SQL Server, Postgres also does not have lock escalation where the whole table is locked for writes when a certain threshold of row locks is exceeded.

So just because one transaction updates 95% of all rows in a table (and thus locks them for other writers), that doesn't mean that other transactions can't update the remaining 5% - with SQL Server, you'd most probably wind up with a lock escalation locking the whole table.


How are SQLServer Timestamp/RowVersion Values Created? I am relatively fresh to SQLServer's use of ROWVERSION/TIMESTAMP data, and normally just query for required rows based on modified dates.

How is the ROWVERSION assigned to a new row in a database? Does every new row start with the same rowversion, or is it a unique value? In other words, will a new row in the database have its rowversion set to currentMaxRowVersion+1?

The use case for this is to query for all rows whose data has changed since the last time the database was queried, EG:

SELECT * FROM MyTable WHERE RowVersion > previousHighestRowVersion

Referencing the documentation for rowversion (Transact-SQL) - (highlighting mine)

  Each database has a counter that is incremented for each insert or
  update operation that is performed on a table that contains a
  rowversion column within the database. This counter is the database
  rowversion. This tracks a relative time within a database, not an
  actual time that can be associated with a clock. A table can have only
  one rowversion column. Every time that a row with a rowversion column
  is modified or inserted, the incremented database rowversion value is
  inserted in the rowversion column.

  You can use the rowversion column of a row to easily determine whether
  the row has had an update statement ran against it since the last time
  it was read. If an update statement is ran against the row, the
  rowversion value is updated. If no update statements are ran against
  the row, the rowversion value is the same as when it was previously
  read.

As far as being unique - it 'should' be unique across all tables in a database.  Here is the 'should' part - there is a caveat in the documentation:

  Duplicate rowversion values can be generated by using the SELECT INTO
  statement in which a rowversion column is in the SELECT list. We do
  not recommend using rowversion in this manner.

You can check the current database rowversion by using @@DBTS.  This example shows rowversion in action.

set nocount on
drop table if exists mytest
go
CREATE TABLE MyTest (myKey int PRIMARY KEY
    ,myValue int, RV rowversion);
GO
INSERT INTO MyTest (myKey, myValue) VALUES (1, 0);
GO
select @@DBTS
go
INSERT INTO MyTest (myKey, myValue) VALUES (2, 0);
GO
select @@DBTS
go
update MyTest set myValue = myValue
go
select @@DBTS
go

------------------
0x0000000000009481

------------------
0x0000000000009482

------------------
0x0000000000009484


Another newbie query on 2 tables I'm having trouble working out this apparently simple query, and would appreciate any suggestions for a solution using Sqlite.

I have two tables  t1 and t2 :

Eg.,

Table1 :
--------

Key  Data
A    data11
A    data12
A    data13
B    data21
B    data22
B    data23

Table2 :
--------
Key  Data
A    datax
B    datay

And I want to pad out Table2 to be like this  :

Table2 :
--------
Key  Data
A    datax
A    null
A    null
B    datay
B    null
B    null

Ie., So that both tables have the same number of entries for each Key value.

CREATE TABLE T1(key,data);
CREATE TABLE T2(key,data);
INSERT INTO T1 VALUES ("A","data11"),("A","data12"),("A","data13"), ("B","data21"),("B","data22"),("B","data23");
INSERT INTO T2 VALUES ("A","datax"),("B","datay");

I have tried the following :

CREATE TABLE T1(id integer primary key autoincrement,key,data);
CREATE TABLE T2(id integer primary key autoincrement,key,data);
INSERT INTO T2 SELECT NULL,T1.KEY,NULL FROM T1 LEFT JOIN T2 USING (key,id);

This adds too many rows (6 instead of 4). There should be the same number of rows in both tables.

|1|A|datax
|2|B|datay
|3|A|Null
|4|A|Null
|5|A|Null
|6|B|Null
|7|B|Null
|8|B|Null

The additional records are just padding so that both tables have the same number of rows. This simplifies the task creating the report where there are a variable number of items per key per category. I didn't know how else to do it, and it took quite a while to come up with this solution.

The ID columns are autoincremented as the tables are populated. I have Sqlite 3.13.0. In reality , the 'key' field are project-codes and the 'data' field are object names from 2 repositories. The report is supposed to show what objects are active for a given project and on which repository.

Try:

INSERT INTO T2
SELECT NULL,T1.KEY,NULL
FROM T1
LEFT JOIN T2 USING (key,id)
WHERE T2.KEY IS NULL;

This should filter your join to select only those rows where there is currently no data value in T2.

(This is your original attempt, plus a new WHERE clause).


Help with SQL CASE WHEN using % Query I have two columns in a SQL Query ( Estimate and Billed ) which contain un-formatted numbers.

I am trying to write a CASE WHEN query for when the BILLED column is between 50% and 70% of the BILLED column THEN '1' ELSE '0'

Then the same for 70% to 90% , 90% to 100% and then greater than 110% ( so multiple CASE WHEN ).

I am just not familiar with the arithmetic needed or if this is the best way to achieve this...

Same Query, with the CASE WHEN in plain English to help explain:

SELECT

CaseNumber,
EstimateAmt,
BilledAmt,

CASE WHEN BilledAmt IS BETWEEN 50% and 70% of EstimateAmt THEN '1' ELSE '0' end as 'IsBetween50And70',

CASE WHEN BilledAmt IS BETWEEN 70% and 90% of EstimateAmt THEN '1' ELSE '0' end as 'IsBetween70And90',

CASE WHEN BilledAmt IS BETWEEN 90% and 100% of EstimateAmt THEN '1' ELSE '0' end as 'IsBetween90And100',

CASE WHEN BilledAmt IS > 110% of EstimateAmt THEN '1' ELSE '0' end as 'IsGreaterThan110'

from CaseList

Thank you

Please try below code for your issue:

SELECT
CaseNumber,
EstimateAmt,
BilledAmt,
(CASE WHEN BilledAmt IS BETWEEN ((50 * EstimateAmt)/100) and ((70 * EstimateAmt)/100) THEN '1' ELSE '0' end) as 'IsBetween50And70',
(CASE WHEN BilledAmt IS BETWEEN ((70 * EstimateAmt)/100) and ((90 * EstimateAmt)/100) THEN '1' ELSE '0' end) as 'IsBetween70And90',
(CASE WHEN BilledAmt IS BETWEEN ((90 * EstimateAmt)/100) and ((100 * EstimateAmt)/100) THEN '1' ELSE '0' end) as 'IsBetween90And100',
(CASE WHEN BilledAmt IS > (110* EstimateAmt)/100 THEN '1' ELSE '0' end) as 'IsGreaterThan110'
from CaseList


Reduce space used on C drive I have installed SQL Server 2008 R2 on my laptop, under 32-bit Windows 7.

The SQL Server installation is using approximately 30GB of space on my C: drive. Why is it using so much space, and how can I reduce it?

This is not the installation of SQL Server itself. It is much more likely one of the following:

a log file for a database (or databases) with a lot of activity, in full recovery mode, but you have never taken a backup or a log backup.
a data file that you have populated with a lot of data (perhaps inadvertently, like tempdb) or attached a large database from somewhere. This could also be filestream data; let us know if you have used that feature.
a large backup or a long series of backups, if in fact you are taking backups.

First, identify which file(s) are actually large - they're almost certainly .mdf / .ldf, but also check for .bak and .trn. Start in:

  C:\Program Files\Microsoft SQL Server\<instance>\MSSQL\DATA\
  C:\Program Files\Microsoft SQL Server\<instance>\MSSQL\Backup\

(But you may have to also check other locations you might have told SQL Server to use for data/log files, backups, filestream, etc.)

Then:

If the large files are .mdf / .ndf files, and they are user databases, you need to decide whether you want to keep those databases around, or keep the large tables within them. For databases you know you don't need, you can just drop them; for databases you might need, you can take a full backup and set them offline temporarily until you are certain you don't need them, then drop them. You can also just drop individually large tables inside an existing database, then shrink the data file to reclaim space (please read this entire post and also this one for more details); you can see which tables are largest using DMVs like sys.dm_db_partition_stats.

If they are system databases (like tempdb), then your next course of action depends on which system databases they are, why they are large, and whether you have alternative places to move the ones that are simple to move.
If the large files are .ldf files, and they are for databases you want to keep, you can clean up and reclaim space using a series of operations depending on your recovery model. Please read this post and all of its answers, in full, for a lot more detail.
if the large files are .bak / .trn, then you need to decide how far back you want to keep your backups, and which (if any) are ones you need to keep. Shift+Delete the ones you don't want (to bypass the Recycle Bin; at least I think that's how I remember to do it on Windows).

Or:

Move SQL Server (or the large databases) to a bigger drive (add a drive to your system, use an external drive with more capacity, etc). This may be easiest to accomplish by just installing a new instance and putting as many of the things away from C:\ as you can.


Setup SQL Server SSL client certificate I have created a certificate authority on my Linux server using OpenSSL. I have added that certificate authority to the Windows Trusted Root Certificate Authorities, which it now recognises.

What do I need to do, from this point, to create a valid SSL certificate that SQL Server will recognise and be able to use? Assuming that my CA key is named root.key, its certificate named root.pem, and its equivalent certificate signing request named root.csr.

No guide / tutorial has been able to tell me this. Every guide seems to be concerned with using the database server to create a certificate authority and generate keys, or something or other - I do not want this. All I want is for my Linux server to generate the SSL certificates, and for those certificates to be used by my client database machines (possibly without going through Microsoft Management Console and other unnecessarily complicated bullsh*t), such that I can connect to them via TCP/IP from my Linux machine (it can connect fine when using no encryption). The computer running the MSSQL server is not part of a domain, and its computer name / hostname is WORKSTATION (if this matters at all).

I'm assuming that the certificate has been created for Server Authentication using your linux server. What you are essentially describing is the same concept that public CAs use to provision their certificates. The CA, or Certificate Authority, is the service or application used to attest to the identity of users, computers, and organizations as well as generate, manage and renew certificates. I would recommend removing the CA service from your SQL Server. You won't need it and if you do use it to create a certificate for the SQL Server, you will be creating a self-signed certificate on your SQL Server, which will leave you open to the risk of a man in the middle attack. It is also possible to encrypt all sessions by simply setting Force Encryption to true, but this will cause the SQL Server to internally generate a self-signed certificate, which is not advised for the same reason.

As long as your SQL Server, linux system and client system can communicate with each other and you use a certificate from the CA on the linux system, what should happen is that the client initiates a session with the SQL Server, which then responds with the request for a secure session and presents a certificate generated from the linux server to the client for validation. Then the client should validate the certificate in two ways, by verifying with the linux system that it is a valid certificate and by using the digital signature to verify that it has not been modified. The client system will also need a way to verify that the certificate can be trusted, which will mean adding it or a root certificate from the linux CA to the trust store of the client.

The basics of enabling SSL on your SQL Server are to import the certificate and private key into the certificate store of the service account running the database engine and then set the Force Encryption property to yes in the properties of the server protocols using SQL Server Configuration Manager. In your case, you may need to convert your pem and key files into a format that Windows Server supports. The question Convert a CERT/PEM certificate to a PFX certificate specifically addresses using OpenSSL to accomplish this.


MySQL: JOIN empty tables with a constant value column, COUNT(*) returns a 0, but adding a GROUP BY on the constant value returns nothing? Can someone explain why, when these tables are empty, the following query returns a COUNT(*) of 0 and a MAX(Timsestamp) of null...

SELECT 'Combined', COUNT(*), MAX(Timestamp)
FROM TableA
JOIN TableB USING (Id)

...while adding a "GROUP BY" on the constant value (first column) returns no rows at all?

SELECT 'Combined', COUNT(*), MAX(Timestamp)
FROM TableA
JOIN TableB USING (Id)
GROUP BY 1

I wasn't able to find anything in the MySQL documentation explaining this behavior. (Re-posted from https://stackoverflow.com/questions/35438668/mysql-join-with-empty-tables-and-count-returns-a-row-with-0-count-but-addin)

It does not matter what you group by, try for example TableA.id. The reason is that MAX and COUNT returns a scalar even for the empty set (your first query). If you add a group by (no matter what attribute you use), you get one scalar per group. But since there are no groups, you get an empty set.

It may be illustrative to add an outer query that count from your second query:

SELECT COUNT(*)
FROM (
    SELECT 'Combined', COUNT(*), MAX(Timestamp)
    FROM TableA
    JOIN TableB USING (Id)
    GROUP BY 1
) AS T


2 tables with foreign keys to 3rd table with primary key This might be a silly question.
But can you have the following:

1 Table with Primary Key,
2 different tables each with a foreign key referencing the first table Primary key?

I know its possible to have 2 foreign keys in the same table, but the request I received was for the the below scenario:

Why shouldn't it be possible? Suppose table1 holds all countries, table2 all cities and table3 has another foreign key to a table with all international organisations. They both need a foreign key to a country. You can even have a table4 that holds all regions. It also needs a foreign key to table1.


How long does a failover for SQL Server Availability Groups take? In trying to determine the most appropriate High Availability option we are focused on reducing downtime (either planned or unplanned).   I've been able to gather stats (from reading lots of MSDN documentation and blogs) on Failover Cluster Instances (FCI).
What I haven't found is documentation around the failover stats/times of Availability Groups.
As a comparison (FCI):
Failing over a 2008R2 cluster can range from 30 seconds to 5 minutes (depending on traffic volumes as well as hardware/network setup):

When doing a manual failover, it is completing its writes to the LUNs,
switching the LUNs to the new Active node and starting up the SQL
Server instance on the new Active node.
When doing an automatic
failover, after starting up the node it will do a consistency check on
the databases and roll back any in-flight transactions.

Availability Groups
I understand that Availability Groups should be able to fail over quicker, when you compare the same hardware and traffic volumes.
What I haven't been able to find is any real world actual metrics comparing the two.
Specifically does anyone have any metrics on how long it takes to fail over the primary write node in an Availability Group that is being actively used?
(fail over to a synchronous secondary)
Ideally this would include references to any Microsoft or trustworthy sources.
Not opinion based please, only metrics.

Personal observation from experience with both FCI and AG failovers, with reasonable high volume transactional system (40k trx/sec). For each consider 6 databases ranging in size from 500MB to 4TB in size. Times listed for failover are what it takes for the database to up and in a writeable state on the new node. Your mileage can, and will vary, but this is at least a data point for you.

Cluster failover: 47 seconds (avg)

AG Failover: 10 seconds


Mysqld process not showing all options on command line in CentOs 7 Mysqld process showed all config file options on command line in CentOs 5 and CentOs 6. But it's not showing the same in CentOs 7. I am using mysql 5.7.21 on CentOs 7 example stated below and 5.7.19 on CentOs 6 example stated below.

centos 6:

mysql@xxxxxxxx ==> ps -ef | grep 3306
mysql    23084     1  0 Jan16 ?        00:00:00 /bin/sh /usr/bin/mysqld_safe --socket=/datadata/mysql.sock --port=3306 --max_allowed_packet=100M --datadir=/datadata --pid-file=/datadata/mysqld-qa.pid --max_connections=500 --open-files-limit=8192 --thread_cache_size=64 --delayed_insert_limit=4096 --query_cache_limit=1M --query_cache_size=32M --query_cache_type=1 --log_queries_not_using_indexes=off --log-error=/datalogs/mysql-error.log --expire_logs_days=3 --sort_buffer_size=2M --join_buffer_size=4M --read_buffer_size=2M --read_rnd_buffer_size=2M --tmp_table_size=64M --max_heap_table_size=64M --lower_case_table_names=0 --myisam_sort_buffer_size=16M --innodb_file_per_table --innodb_buffer_pool_size=1G --innodb_open_files=600 --innodb_max_dirty_pages_pct=10 --innodb_flush_log_at_trx_commit=0 --innodb_log_file_size=64M --innodb_log_buffer_size=4M --innodb_log_files_in_group=3 --innodb_flush_method=O_DIRECT --binlog_cache_size=64K --transaction_isolation=REPEATABLE-READ --server-id=3664 --log-bin=mysqldb01.bin --log-slave-updates --binlog_format=statement --sysdate-is-now=1 --sync-binlog=1 --sync-master-info=1 --log-queries-not-using-indexes=1 --innodb_buffer_pool_instances=5 --innodb_concurrency_tickets=1000000 --auto_increment_increment=10 --auto_increment_offset=2 --binlog_checksum=none --sql_mode=STRICT_ALL_TABLES --explicit_defaults_for_timestamp --open_files_limit=10000 --max_connections=4000 --table_open_cache=400 --binlog_format=statement --log_timestamps=SYSTEM --explicit_defaults_for_timestamp --master_info_repository=table --relay_log_info_repository=table --relay-log=dbtemp02-relay-bin --mysqld_safe_log_timestamps=SYSTEM --slave_net_timeout=3600 --default_password_lifetime=0 --relay_log_recovery=ON
mysql    27230 23084  0 Jan16 ?        00:32:37 /usr/sbin/mysqld --basedir=/usr --datadir=/datadata --plugin-dir=/usr/lib64/mysql/plugin --max-allowed-packet=100M --max-connections=500 --thread-cache-size=64 --delayed-insert-limit=4096 --query-cache-limit=1M --query-cache-size=32M --query-cache-type=1 --log-queries-not-using-indexes=off --expire-logs-days=3 --sort-buffer-size=2M --join-buffer-size=4M --read-buffer-size=2M --read-rnd-buffer-size=2M --tmp-table-size=64M --max-heap-table-size=64M --lower-case-table-names=0 --myisam-sort-buffer-size=16M --innodb-file-per-table --innodb-buffer-pool-size=1G --innodb-open-files=600 --innodb-max-dirty-pages-pct=10 --innodb-flush-log-at-trx-commit=0 --innodb-log-file-size=64M --innodb-log-buffer-size=4M --innodb-log-files-in-group=3 --innodb-flush-method=O_DIRECT --binlog-cache-size=64K --transaction-isolation=REPEATABLE-READ --server-id=3664 --log-bin=mysqldb01.bin --log-slave-updates --binlog-format=statement --sysdate-is-now=1 --sync-binlog=1 --sync-master-info=1 --log-queries-not-using-indexes=1 --innodb-buffer-pool-instances=5 --innodb-concurrency-tickets=1000000 --auto-increment-increment=10 --auto-increment-offset=2 --binlog-checksum=none --sql-mode=STRICT_ALL_TABLES --explicit-defaults-for-timestamp --max-connections=4000 --table-open-cache=400 --binlog-format=statement --log-timestamps=SYSTEM --explicit-defaults-for-timestamp --master-info-repository=table --relay-log-info-repository=table --relay-log=dbtemp02-relay-bin --slave-net-timeout=3600 --default-password-lifetime=0 --relay-log-recovery=ON --log-error=/datalogs/mysql-error.log --open-files-limit=10000 --pid-file=/datadata/mysqld-qa.pid --socket=/datadata/mysql.sock --port=3306

centos 7:

[mysql@xxxxxx binlogs]$ ps -ef | grep mysql
mysql    16343     1 44 00:42 ?        00:53:00 /usr/sbin/mysqld --defaults-group-suffix=@0 --daemonize --pid-file=/var/run/mysqld/mysqld-0.pid

Any idea how to display all variables in CentOs 7.

You shouldn't be relying on the OS processlist to display mysqld options in the events other versions of the OS do not display them (as your questions points out)

What other methods can you use that are OS-independent ???

METHOD #1

If you enabled show_compatibility_56, then login to MySQL and run

SELECT * FROM information_schema.global_variables;

METHOD #2

If you did not enable show_compatibility_56, then login to MySQL and run

SELECT * FROM performance_schema.global_variables;

METHOD #3

From the OS command line, use mysqladmin utility with variables command

mysqladmin -h127.0.0.1 -P3306 --protocol=tcp -uroot -p variables

GIVE IT A TRY !!!


Upgrade path - Oracle Standard to Oracle Enterprise Could I start on Oracle Standard edition and easily upgrade to Oracle Enterprise?

Or do I need to rebuild the entire server? how does the upgrade path work?

It's easy because internal database structure almost identical:

Backup all database files and /network/admin (just for sure);
Uninstall Standard Edition;
Install Enterprise Edition;
Point ORACLE_SID to old database;
Startup database and run catalog.sql and catproc.sql from new version;
Recompile all invalid objects.

Note that software version, release number and patch level must be identical.


SQL Server grant permissions to STANDBY database So, first: the setup. We have SQL Server 2012 (ServerA) running in domain A. We have SQL Server 2012 (ServerB) running in domain B, separate subnet, no trusts. These are completely separate domains for security reasons and they cannot be joined/trusted/etc. We need to be able to query the data directly from domain B via Windows Authenticated logins. I was able to use this guide to set up transaction log shipping to get the databases from ServerA to ServerB (summary: create the transaction log shipping config, use WinSCP to copy the logs to the remote server, manually create the secondary using SQL script). So now we have the two databases running in STANDBY/read-only on ServerB.

Now, the problem: we cannot grant access to these databases because they are in read-only so we cannot modify the permissions. How can I grant read-only access to these databases (either at the server level or DB level) to a domain group from DomainB on ServerB? I've found several references to creating a SQL login on the sending side, but I can't find any way to replicate it with a Windows Auth Login.

Below steps should work :

Create windows login on ServerA
Create user in database mapped to login
Drop login on ServerA - (OPTIONAL -if you want to have that login intact then leave it, else drop it).
Grant any required permissions to user in database
Create login on log shipping ServerB

a. If using SQL authentication then create login while specifying SID from ServerA
b. If using Windows authentication, create login without specifying SID

 ---To query for the SID on the ServerA:

  Select SID
  From sys.database_principals
  Where name = 'Test'

 ---To create the login on the ServerB :

  Create Login [Test]
  With SID = '<put SID from query here>',
  Password = '<add password here>'

Make sure log shipping jobs have completed one full run since completion of step 4


Executing query is too slow when accessing SQL Server located in an other country I'am doing an investigation about a slow running query (see below info) when accessing our production SQL Server located in an other country. Now I'm looking for tweaks on how to solve this and make it fast.

See below information:

Database Transaction Table Records: 17,000 +
SQL Server(2005), Web Server, Report Server (where reports are on our ASP.NET Web Application) location: Illinois, USA (no jobs or any linked server on the Database)
Query executed: select * from TransactionTable

Test results (all locations have only one domain):

|  Location       | Time Elapse  | Executed Using
   Illinois         00:00:00       SQL Management Studio,Web App,RS(Prod Server,local)
   Brazil           00:2:21 +      SQL Management Studio,Web App,RS
   Philippines      00:2:21 +      SQL Management Studio,Web App,RS
   Others           00:2:21 +      SQL Management Studio,Web App,RS

This is what I did: restart production SQL Server, stop Web and Report Server then I tried to query the SQL Server on different locations with the same result.

I found that the problem is not the query but it is caused by network

Anyone of you guys encountered this? What have you done? I need some recommendation.

Any reason why you need to download the entire table every time to every location? select * from table w/o filters across geo locations is an uphill battle you're bound to loose. Filter the data remotely before download. Or make local read-only copies (eg. via log shipping).


Where is located sp_msreplcheck_publish stored procedure? sp_msreplcheck_publish is a sp that checks if you have sufficient rights to run a statement to alter a published table (transactional replication).

Trying to alter a published table without elevated rights fires trigger tr_MStran_altertable that calls sp_MStran_ddlrepl that execute sp_msreplcheck_publish. It returns correctly :

  Only members of the sysadmin fixed server role or db_owner fixed
  database role can perform this operation.

The mystery for me  is that I cannot find this sp in published database or in master. Is it generated on demand?

If is hidden from general view but, if you connect using a Dedicated Administrator Connection, you can see it in master via:

USE [master];
GO

SELECT OBJECT_DEFINITION(OBJECT_ID(N'sys.sp_msreplcheck_publish'));

Not sure what you need to see the definition for, though, or why knowing where it is stored will help you accomplish anything. It's really this simple, and you can't change it, regardless of where it is stored:

create procedure sys.sp_MSreplcheck_publish
as
begin
    --
    -- Do the sysadmin check first as it is 4 times more efficient
    -- than is_member call and reduces performance overhead in case
    -- the user has sysadmin privileges
    --
    if    is_srvrolemember('sysadmin') = 1 or
        is_member ('db_owner') = 1
    begin
        return (0)
    end
    else
    begin
        raiserror (21050, 14, -1)
        return (1)
    end
end


Normalizing/combing mutliple tables with similar data I'm working on setting up a database of vehicle records based on data from a third party vendor. There are 3 models: Vehicles, Transmissions, and Adaptors.

Both Vehicles and Transmissions are 1:n Adaptors. Technically Transmissions are just Vehicles with additional transmission columns, while Vehicles contain fuel system columns in addition to the vehicle columns. I am having trouble determining the best way of combining my data into one normalized set.

Here are some samples of my data:

Transmissions table

Vehicles Table

Pivot Tables

As you can see, Transmissions and Vehicles are basically the same up to the trans_type/fuel_system columns (respectively) and beyond. For example, the Vehicle I have highlighted is the same as the 2nd Transmission shown with engine_code LML.

Ideally, I am hoping to end up with just one table of vehicle data. For instance, if I were to merge the highlighted records, I should end up with only 2 records for a 2013 GMC Sierra 3500 HD:
One with a 6.0L V8 engine and the other with a 6.6L V8 engine, along with the respective fuel system and transmission columns for each.

The pivot tables would also be combined as well which means I need to replace the old IDs with new IDs from the combined data, somehow.

Here are my thoughts on how to approach this:

Normalize the similar columns (makes, models, engine, transmission)
Simply split off the fuel_system and trans_type columns into their own tables (but this does nothing to help me combine, it just makes things a bit more manageable until I can figure out how to combine)
Make a new table containing all the columns from both tables, and insert the data from Vehicles and Transmissions, fill in the blanks (i.e. update fuel_system where records match and vice versa for transmission columns), then begin to clean up duplicates.

I apologize for the lengthy post, but I have yet to find anything in my searches that really outlines what this process may look like. Any advice or suggestions are welcome, and thank you in advance.

Functional dependencies and normalization
In order to carry out a normalization exercise involving second and further normal forms —as per the relational model of data by Dr. E. F Codd—, one first has to know what are the relevant functional dependencies (FDs for brevity) between the attributes (usually depicted as columns) of an adapted mathematical relation (usually portrayed as a table). This kind of exercise belongs to the logical-level of abstraction of a database. That is why, in order to address your stated intention of obtaining a normalized set, I requested information about the applicable FDs via comments.
For instance, a FD involving the hypothetical attributes Foo and Bar can be depicted as Foo → Bar, which may in turn be read as “attribute Foo determines attribute Bar” or “attribute Bar is determined by attribute Foo”. In this way one can distinguish (a) one or more attributes, or one or more combinations of attributes, that are the key, or the keys, of a relation and (b) distinguish the attributes that are not, or are not part of, the key or keys.
Regarding the scenario you describe, let us suppose that the grid entitled Transmissions is a concrete representation of a mathematical relation. With respect to the values of the columns labeled as submodel, body_code, engine_code, engine, desplacement, cylinders, trans_type, trans_code and qualifier, one can say that:

Some (engine_code and trans_code) appear to be determined by the values of what would be the key of a relation called, let us say, Vehicle — i.e., the combination of (model, make, year).

Some (transmission_type and qualifier) appear to be determined by the values of what would be the key of a relation called, let us say, TransmissionAdaptor — i.e., trans_code.

Some (engine_type, displacement and cylinders) appear to be determined by the values of what would be the key of a relation called, let us say, Engine — i.e., engine_code.

But those are simple assumptions, based on my personal interpretation of the information contained in the aforementioned grid, and evidently I am not familiar at all with the business context in question. Therefore, in order to get rid of those kinds of needless and problematic assumptions you have to interview the business experts, and they will help you to identify the FDs, which would in turn guide you in performing proper normalization and laying out the database structure with the precision that the database administration profession requires. If there are no business experts who you can resort to, then you will have to dive into the data sets, observe data usage and meaning, and analyze carefully the interconnections between the pieces of information of interest to determine the significant FDs on your own.
A normalized set
Having a normalized database helps to avoid update/modification anomalies (affecting INSERT, UPDATE and DELETE operations) that  arise eventually when there exist undesirable dependencies among the attributes (columns) of the relations (tables) under consideration. In cases in which, e.g., there are attributes of a relation that (i) depend on non-key attributes, or that (ii) depend on parts of a composite —i.e., multi-attribute— key, the designer has to decompose the concerning relation into two or more.
Thus, most of the time, a normalized set is made up of various relations, each of which is meant to contain exactly one particular type of fact in its tuples (rows). Contrarily, unnormalized and non-fully-normalized sets consist of one or more relations that contain more than one type of fact in their tuples.
The design of a database starting from the conceptual level of abstraction
On the other hand, you can design the relevant database starting from a different perspective, analyzing first the structure and associations —or relationshipsa or connections— of the types of things of interest from a purely conceptual point of view, without thinking about relations (tables), attributes (columns), constraints and normalization yet. Of course, this approach also entails close communication between the database designer(s) and the business experts or, in absence of business experts, database designer(s) with in-depth knowledge of the informational needs and characteristics of the business environment.
Sample business rules
So I will put together some hypothetical business rules that will help in the creation of an expository conceptual schema based on mere assumptions about the information contained in your question.
A Vehicle is:

primarily identified by exactly one combination of Model, Make and Year
moved by exactly one Engine
equipped with exactly one TransmissionAdaptor
…

An Engine:

is primarily identified by exactly one EngineCode
is catalogued by exactly one EngineType
has exactly one Displacement
holds exactly one type of Cylinders
is provided with exactly one FuelSystem
is installed on zero, one or more Vehicles
…

A TransmissionAdaptor is:

primarily identified by exactly one TransmissionCode
categorized by exactly one TransmissionType
installed on zero, one or more Vehicles
…

A FuelSystem:

is primarily identified by exactly one Name
is classified by exactly one FuelSystemType
has exactly one Pressure
is installed on zero, one or more Vehicles
…

As demonstrated, (a) the hypothetical association types between the properties of the possible entity types of relevance, and (b) the hypothetical association types between the possible entity types themselves, have been stated in a relatively clear manner.
Clearly, these rules are exclusively a medium to expound a method you could follow to design your database. Being devised as personal interpretations, they of course ought to be confirmed, refuted or adapted in accordance with real business environment features.
Sometimes, a database designer draws a diagram portraying the conceptual schema definitions so as to supply a graphical tool that assists in the communication among all the interested —technical and non-technical— parties.
When the results of this stage are stable to a certain degree, one can start thinking about representing these conceptual aspects by means of logical level constructs more confidently.
a By the way, it is opportune to point out that a conceptual relationship is very different from a logical relation.
Illustrative SQL-DDL logical-level design
Successively, I have created the following four tables that represent the conceptual-level formulations elaborated above:
CREATE TABLE TransmissionAdaptor (
    TransmissionAdaptorCode CHAR(10) NOT NULL,
    TransmissionType        CHAR(30) NOT NULL,
    --
    CONSTRAINT TransmissionAdaptor_PK PRIMARY KEY (TransmissionAdaptorCode)
);

CREATE TABLE FuelSystem (
    Name           CHAR(6)  NOT NULL,
    FuelSystemType CHAR(10) NOT NULL,
    Pressure       CHAR(10) NOT NULL,
    FuelSystemName CHAR(30) NOT NULL,
    --
    CONSTRAINT FuelSystem_PK PRIMARY KEY (Name)
);

CREATE TABLE MyEngine (
    EngineCode     CHAR(3) NOT NULL,
    EngineType     CHAR(8) NOT NULL,
    Displacement   CHAR(3) NOT NULL,
    Cylinders      CHAR(3) NOT NULL,
    FuelSystemName CHAR(6) NOT NULL,
    --
    CONSTRAINT Engine_PK               PRIMARY KEY (EngineCode),
    CONSTRAINT Engine_to_FuelSystem_FK FOREIGN KEY (FuelSystemName)
        REFERENCES FuelSystem (Name)
);

CREATE TABLE Vehicle (
    Model                   CHAR(20) NOT NULL,
    Make                    CHAR(10) NOT NULL,
    MyYear                  SMALLINT NOT NULL,
    EngineCode              CHAR(3)  NOT NULL,
    TransmissionAdaptorCode CHAR(10) NOT NULL,
    --
    CONSTRAINT Vehicle_PK                 PRIMARY KEY (Model, Make, MyYear),
    CONSTRAINT Vehicle_to_Engine_FK       FOREIGN KEY (EngineCode)
        REFERENCES MyEngine (EngineCode),
    CONSTRAINT Vehicle_to_Transmission_FK FOREIGN KEY (TransmissionAdaptorCode)
        REFERENCES TransmissionAdaptor (TransmissionAdaptorCode)
);

Since now one would be dealing with logical-level elements, with tables that have clearly declared keys that are based on a clear conceptual schema, a proper normal forms assessment exercise would be called for in order to test the soundness of the design.
Personally, I find that the design sequence described above, i.e.,

(1) conceptual formulations ⟷ (2) logical representation ⟷ (3) testing via normal forms

is more natural than directly working with “isolated” functional dependencies, since it aids the designer(s) in understanding the scenario from different levels of abstraction in a thorough way.
Further considerations
System-assigned surrogates
As you can notice, I have not attached an extra Id column (commonly added to contain system-generated surrogate key values) to any of these base tables, since doing so only hinders the (conceptual) modelling and (logical) normalization tasks. Once you have a stable structure with the corresponding constraints, it would be suitable to evaluate the specific cases where the addition of that non-data artifact is beneficial.
Column datatypes
An important factor that was not discussed in detail is the particular domain associated with each column. So, when you continue working on the design of the database, you have to determine what are the most fitting datatypes and sizes of each column.
Data derivation
Having a logical layout like the one above, you have to make use of derived tables (e.g., tables declared by means of SELECT operations that gather columns FROM one or more base or —other— derived tables) to obtain the information as shown, e.g., in your Transmission grid.
Of course, a derived table can be defined as a view that can be queried on further, so as to, e.g., ease writing the code of future data manipulation operations.
It might be useful to study this point to see if you can accomplish your stated objective of, ideally, ending up with just one table of Vehicle data.
Physical-level aspects
The physical level of abstraction is another point that decidedly requires attention, since the configuration of the most convenient indexes is directly associated with the optimal functioning of the database (e.g., reading and writing speed, scalability). Needless to say, you have to take into account the tendencies of the data manipulation operations (or queries) in this respect. Usually, you fix indexes that support columns that are involved, e.g., in WHERE and JOIN clauses.
One of the numerous shortcomings and disadvantages of MySQL as a database management system is that it does not offer built-in support for “indexed” or “materialized” views, so this factor is decidedly worth taking into account if you want to construct a database that works efficiently.
Other physical-level aspects of significance are, e.g., setting up the involved hardware (hard drive, memory, processor, etc.), the database management system, the operating system, the network bandwidth, etc., in an optimal way.

Addendum
Evidence of the importance of possessing full knowledge of the business domain of concern when designing a database arose in a recent comment interaction, in which @Rick James brought up a pertinent consideration about some entity types, their corresponding association types and cardinality ratios in your scenario:

It looks like transmissions:adaptors is many:many, not 1:many. Please clarify.

…to which you responded as follows:

Yes, this is correct. Transmissions have their own adaptors and FuelSystems have their own adaptors. A transmission belongs to zero, one or more transmission adaptor(s) and a transmission adaptor can belong to zero, one or more transmissions.

Consequently, as you know, it is necessary to model those conceptual entity types, i.e.:

Transmission, TransmissionAdaptor, FuelSystem and FuelSystemAdaptor,

and the connecting association types, i.e.:

Transmission-TransmissionAdaptor and FuelSystem-FuelSystemAdaptor,

so that they reflect your business domain characteristics with the demanded precision.
Once all the related aspects are represented in a logical-level DDL design with the respective tables (puttin up one for each entity/association type), columns, datatypes and constraints, you may like to assess the involved functional dependencies to deliver a strong, normalized, system.


Creating table space steps In order for me to create space in the database is this all I need to do?
alter tablespace USER_DATA_TBS add datafile '/oradata/bosv/user_data04.dbf' size 10m;

Or are there other steps I need to take?
I looked at this guide.
I would appreciate if you could point me in the correct direction.
This is what I have after I created  a datafile:

What you did, was to add another datafile (quite tiny one, though) to an existing tablespace. If that was your intention, then I'd say you succeeded.
However, as title says "CREATE" and if you wanted to create another tablespace, then you should have used CREATE TABLESPACE command (which is also described in document whose link you posted).


How can I have SQL Server send Job Notification alerts to an API I am trying to get the notifications for a job out of my email and into a chat client(HipChat).  So far my googling has been less than successful so I will put the question to the community:  Is there a way to send notifications from SQL Server to an API?

My first thought is that I need to add an additional job step that would be a powershell routine to execute the api call.

So you need SQL Server to make an HTTP call. There's nothing in T-SQL to do this directly. You will need to engage one of the general-purpose programming embeddings it supports.
Your own suggestion of calling PowerShell from a new job step will work. If there are parameters to pass from the job step that does the work to the one that makes the HTTP call this becomes a little awkward but there are ways to achieve it.
Since SQL Server 2005 stored procedures can be written in any .Net language. The code can implement anything any other .Net program can, including HTTP calls. There are a few things to be wary of, though. It is possible to write code which can crash SQL Server itself. An HTTP request can take a long time to complete. If performed synchronously this will hold open the current transaction and any active locks. This can affect concurrency badly. It may be better to use SQL CLR to write to a queue which then asynchronously calls the API.
More recently support has been added for code written in Python, R and Java. Any of these should be able to make HTTP requests.
For the truly brave COM objects can be instantiated directly from T-SQL using the sp_OA* procedures. Here's an implemetation. It has all the ease of assembly combined with the clarity of linear B. I've used this once in my career. That was enough.
For my two cent's - if the use-case is a simple "this job has ended" I'd go with the job step / PowerShell combination.


How to merge these 3 queries into 1 I am new to sql (using mysql) and I have the following problem. I need to create a table where first column is some months, and the next three are :
first column: total amount of previous months (from the one stated in 1st column)
second: total amount of current month
third: total amount of the following months.

Now I have done it in three seperate queries, and what I want to do next, is combine them somehow. I am totally new to sql, so my initial idea was to create three tables and inner join them, but I guess this is not neat approach. Is there any way I can do that properly?

I am attaching my 3 queries, plus my output:

# 1 column
 select month(rdate),
(SELECT SUM(ramaount)
 FROM my_table
 WHERE (month(rdate) <(month(a.rdate)))
 and year(rdate)=2015
) as total_previous
FROM my_table a
where year(rdate)=2015
group by month(rdate)
order by year(rdate),month(rdate);

# 2 column
select
month(rdate) as month_2015,
sum(ramaount)
from     my_table
where    year(rdate) in (2015)
group by   month(rdate);

# 3h colum
SELECT month(rdate),
(SELECT SUM(ramaount)
 FROM my_table
 WHERE (month(rdate) >(month(a.rdate)))
 and year(rdate)=2015
) as total_next
FROM my_table a
where year(rdate)=2015
group by month(rdate);

My three results currently are:

1,
3, 375
5, 1325
6, 3055
7, 4255
8, 10255
9, 10555
10, 10755
12, 10935

1, 375
3, 950
5, 1730
6, 1200
7, 6000
8, 300
9, 200
10, 180
12, 1200

1, 11760
3, 10810
5, 9080
6, 7880
7, 1880
8, 1580
9, 1380
10, 1200
12,

One alternative is to trade the correlated subqueries and, potentially, joins for using variables as well as a lot of nesting and sorting.

The starting point of the approach I am suggesting will be this query:

SELECT
  MONTH(rdate)  AS month_2015,
  SUM(ramaount) AS this_amount
FROM
  my_table
WHERE
  YEAR(rdate) = 2015
GROUP BY
  MONTH(rdate)

which is essentially your second query. According to your example, the output would be this:

month_2015  this_amount
----------  -----------
1           375
3           950
5           1730
6           1200
7           6000
8           300
9           200
10          180
12          1200

The following query uses that as a derived table to calculate the running total from the first to the last month with the help of a variable:

SELECT
  month_2015,
  this_amount,
  @past_amount := @past_amount + this_amount AS past_amount
FROM
  (SELECT @past_amount := 0) AS x,
  (
    SELECT
      MONTH(rdate)  AS month_2015,
      SUM(ramaount) AS this_amount
    FROM
      my_table
    WHERE
      YEAR(rdate) = 2015
    GROUP BY
      MONTH(rdate)
  ) AS sub
ORDER BY
  month_2015 ASC

In your case the returned rows would look like this:

month_2015  this_amount  past_amount
----------  -----------  -----------
1           375          375
3           950          1325
5           1730         3055
6           1200         4255
7           6000         10255
8           300          10555
9           200          10755
10          180          10935
12          1200         12135

Never mind that the past_amount value includes the current month whereas you seem to require otherwise – that will be rectified later.

The next step would be to use the above as a derived table again and use the same method to calculate future_amount while sorting the rows in the opposite order:

SELECT
  month_2015,
  this_amount,
  past_amount,
  @future_amount := @future_amount + this_amount AS future_amount
FROM
  (SELECT @future_amount := 0) AS x,
  (
    SELECT
      month_2015,
      this_amount,
      @past_amount := @past_amount + this_amount AS past_amount
    FROM
      (SELECT @past_amount := 0) AS x,
      (
        SELECT
          MONTH(rdate)  AS month_2015,
          SUM(ramaount) AS this_amount
        FROM
          my_table
        WHERE
          YEAR(rdate) = 2015
        GROUP BY
          MONTH(rdate)
      ) AS sub
    ORDER BY
      month_2015 ASC
  ) AS sub
ORDER BY
  month_2015 DESC

This would be the result:

month_2015  this_amount  past_amount  future_amount
----------  -----------  -----------  -------------
12          1200         12135        1200
10          180          10935        1380
9           200          10755        1580
8           300          10555        1880
7           6000         10255        7880
6           1200         4255         9080
5           1730         3055         10810
3           950          1325         11760
1           375          375          12135

Again, do not worry at this point about the future_amount results including the current month's amount.

Your next, and last, step will be to nest the previous query one level more in order to sort the results back in the ascending order of months as well as to adjust past_amount and future_amount as past_amount - this_amount and future_amount - this_amount, respectively:

SELECT
  month_2015,
  NULLIF(past_amount   - this_amount, 0) AS past_amount,
  this_amount,
  NULLIF(future_amount - this_amount, 0) AS future_amount
FROM
  (
    SELECT
      month_2015,
      this_amount,
      past_amount,
      @future_amount := @future_amount + this_amount AS future_amount
    FROM
      (SELECT @future_amount := 0) AS x,
      (
        SELECT
          month_2015,
          this_amount,
          @past_amount := @past_amount + this_amount AS past_amount
        FROM
          (SELECT @past_amount := 0) AS x,
          (
            SELECT
              MONTH(rdate)  AS month_2015,
              SUM(ramaount) AS this_amount
            FROM
              my_table
            WHERE
              YEAR(rdate) = 2015
            GROUP BY
              MONTH(rdate)
          ) AS sub
        ORDER BY
          month_2015 ASC
      ) AS sub
    ORDER BY
      month_2015 DESC
  ) AS sub
ORDER BY
  month_2015 ASC
;

And this is would you should get as the result:

month_2015  past_amount  this_amount  future_amount
----------  -----------  -----------  -------------
1           NULL         375          11760
3           375          950          10810
5           1325         1730         9080
6           3055         1200         7880
7           4255         6000         1880
8           10255        300          1580
9           10555        200          1380
10          10755        180          1200
12          10935        1200         NULL

I additionally used the NULLIF function on the subtractions to substitute NULLs for what otherwise would be 0s in the first row's past_amount and the last row's future_amount, because that was what your results seemed to suggest to me you wanted. If a 0 in both cases would do just fine, then do not use NULLIF.

A demo of this approach is available at Rextester and SQL Fiddle.

We could also simplify by calculating the total first, so we don't have to do running sums, only one forward:

SELECT
  month_2015,
  @past_amount := @past_amount + @this_amount AS past_amount,
  @this_amount := this_amount                 AS this_amount,
  total_amount - @past_amount - @this_amount  AS future_amount
FROM
  (SELECT @past_amount := 0, @this_amount := 0) AS x,

  (
    SELECT
      SUM(ramaount) AS total_amount
    FROM
      my_table
    WHERE
      YEAR(rdate) = 2015
  ) AS t,

  (
    SELECT
      MONTH(rdate)  AS month_2015,
      SUM(ramaount) AS this_amount
    FROM
      my_table
    WHERE
      YEAR(rdate) = 2015
    GROUP BY
      MONTH(rdate)
  ) AS sub
;

An extra logical table scan in the form of the total amount across the year has been added in exchange for extra sorts of the result set.

Both solutions at Rextester.


Can I grant multiple remote LocalService accounts access to a SQL Server database? I am designing a multi-environment Windows-based software product that will extensively employ Windows services running as LocalService to perform data access operations. All of the data access endpoints are .NET applications that use Entity Framework to access the databases.

I don't want to use SQL Server authentication if I can avoid it; I want to use Windows authentication everywhere. Currently there are no roles or users at the database level for any of the system's databases. My current plan is to grant SELECT/INSERT/UPDATE/EXECUTE privileges to the LocalService accounts associated with each of the service host environments.

First, is that approach even possible? After a dizzying amount of research, I can't make sense of how SQL Server will distinguish between the LocalService account on two remote environments.

Second, am I missing a serious security implication? I understand the principle that a role should be limited and narrow in scope. I also understand that any other applications on the servers running as LocalService -- including malware -- will have database access using the current plan. The challenge that I'm facing is that the product is licensed and will be deployed on customer site, and I'm not sure how to tell our customers that our software needs to commission its own domain accounts or, even worse, that they need to commission them.

If it makes the most sense, I will just abandon the idea of using LocalService to access databases and plan on creating purpose-specific service accounts. My expertise is stretched beyond its limit for this particular problem. Thanks in advance for sharing your thoughts.

Machine accounts such as LocalService cannot be trusted over the network.

There is essentially no way to authenticate the remote machine without domain authentication of some kind.

Use SQL Server authentication; that's what it is meant for.

Alternately, you could provide a setup procedure that allows your clients to provide a domain account for each service to access the SQL Server database.  You'd need to provide a mechanism where they can enter domain credentials for each service when they are setting up the server end.


Change all varchar date formats with a single query |date(varchar) |
|--------------|
|2016-02-16    |
| -------------|
|03-12-2015    |
|--------------|
|07-07-2007    |
|--------------|
|2016-03-14    |

Dates are stored in different formats and type is varchar. How to change all formats with single query in MySQL?

The formats are d/m/y, d-m-y, and y/m/d.

I need to copy these to a new column in date format y-m-d

Create the new column as type DATE.

Then run 3 queries, one for each conversion needed.  Sure, they could be combined into a single query, but why bother; it just makes it more complex and likely to break.

And finally drop the old column and rename the new on into place?

The queries would be something like:

ALTER TABLE add COLUMN new_date DATE;
UPDATE tbl SET new_date = STR_TO_DATE(date,'%d-%m-%Y') WHERE substring(date,3,1) = '-';
UPDATE tbl SET new_date = STR_TO_DATE(date,'%d/%m/%Y') WHERE substring(date,3,1) = '/';
UPDATE tbl SET new_date = STR_TO_DATE(date,'%Y/%m/%d') WHERE substring(date,5,1) = '-';

Then do a SELECT to see if any rows still have a DEFAULT value for new_date;


Is there any reasonable way to determine what executed a query? I have an S3 SQL Server database in Azure, and I'm having an issue with a specific Entity Framework generated query and how often it is being run.

As the numbers below show, I'm getting 160K+ executions per hour, and this number is unexpected (for the time periods illustrated I would have expected less than 10K executions per hour).

The number of executions also doesn't correlate with the number of requests to the API methods that issue this query (they were called about 50 times in the corresponding period).

So my question is: using Azure analytics is there any way I can trace the query to determine who/what made the call? Can I somehow get an IP address? This is clearly a code problem, is there any helpful way to determine where the calls are coming from?

Use SQL Auditing to track from where those statements are coming.

Set-AzureRmSqlDatabaseAuditing -ResourceGroupName "resourceGroup"
 -ServerName "SQL Server Name" -DatabaseName "AdventureWorksLT"
 -StorageAccountName "storageAccount"
-AuditActionGroup "SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP", "FAILED_DATABASE_AUTHENTICATION_GROUP", "BATCH_COMPLETED_GROUP"
 -AuditAction "UPDATE ON database::[AdventureWorksLT] BY [public]"
 -RetentionInDays 60

A sample audit record for the BATCH_COMPLETED_GROUP will show the following information:

As you can see it gives you the IP address, the principal name and the application name that generated the SQL statement.


Orderable representation of multi-part values Consider a table for construction projects.
Each project requires resources, e.g. steel, wood, copper, etc.
Suppose we want the following types of queries:

All projects requiring less than 3 units of wood.
All projects requiring only wood and steel.
All projects requiring at least four units of copper but no steel.

What schema design supports queries like this?

Here's one idea:

CREATE TABLE projects (
    id INTEGER NOT NULL AUTO_INCREMENT,
    name VARCHAR(32) NOT NULL,
    UNIQUE (name),
    PRIMARY KEY (id)
);
CREATE TABLE materials (
    id INTEGER NOT NULL AUTO_INCREMENT,
    name VARCHAR(32) NOT NULL,
    UNIQUE (name),
    PRIMARY KEY (id)
);
CREATE TABLE projects_and_materials (
    project_id INTEGER,
    material_id INTEGER,
    value INTEGER,
    FOREIGN KEY(project_id) REFERENCES projects (id),
    FOREIGN KEY(material_id) REFERENCES material (id)
);

The value column tells us how much of the material referenced by material_id is needed for the project referenced by project_id.
This schema can represent the data, but it's not clear to me that it's good for queries.

Your tables look adequate to me.  I'd just have a "project_materials" table with columns including "project_id", "material_name", and "material_quantity", but your teacher may not like it.  Also, is a "unit" a quantity, or distinct thing, e.g. copper tube, copper screws, pieces of wood in various sizes and shapes?

So anyway, for instance:

SELECT p.name FROM projects AS p
INNER JOIN project_materials AS m1
ON ( m1.project_id = p.id AND m1.material_name = 'Copper' )
LEFT OUTER JOIN project_materials AS m2
ON ( m2.project_id = p.id AND m2.material_name = 'Steel' )
WHERE ( m1.material_quantity > 4 AND m2.material_quantity IS NULL )