Unable to open SQL Server Config Mgr, get a WMI error

I found the issue. The WMI service had been disabled (hence the error message). I used the properties menu (running as admin) from the services list, and put this service on auto start and then started it. Config Mgr came up.


Are duplicated NVARCHAR values stored as copies in SQL Server?

Under "normal" conditions, no, data in VARCHAR and NVARCHAR columns is not de-duped (although duplicate attribute and/or element names in a single XML value are reduced to a unique instance).
Using one of the Data Compression options is probably your best bet. Here are some things to consider:

Unicode Compression (part of Row Compression) only works on NVARCHAR(1 - 4000), not NVARCHAR(MAX) (please vote for / support: Unicode compression NVARCHAR(MAX)).
Page Compression can work with NVARCHAR(MAX), but only for in-row data. Off-row data (LOB pages) is not compressed.

Since the data won't really be changing, you should look into the Columnstore Index options (also available in Azure SQL Database):

Columnstore indexes: Overview
Columnstore indexes - Design guidance

Columnstore compression should be better than Page compression.

Also, you should probably avoid using the SPARSE option, due to:

The SPARSE option offers no benefit compared to Data Compression.
It is mainly intended for columns sets / wide tables (i.e. up to 30,000 columns).
It mostly helps with fixed-length datatypes (e.g. INT, DATETIME). So, prior to Data Compression being available, it was useful for CHAR and NCHAR, but not for VARCHAR or NVARCHAR as they don't take up space when NULL.
It only benefits columns set to NULL.
It slightly hurts non-NULL values by adding 2 bytes to each one.
You should probably have NULL for 50% - 60% of the rows in order to get enough savings for it to be worth using this option.

For more details on working with character data, please see the following post of mine:
How Many Bytes Per Character in SQL Server: a Completely Complete Guide


I've seen two common approaches to storing historical data: Storing history in a separate _history table, and storing it in the same table as the "current" record, but with a version number and maybe an indicator to show that it's historica data.

If you separate the history to a separate table you'd have something like this:

question
--------
    question_id (PK)
    question_text

question_history
----------------
    question_history_id
    original_question_id (FK to question.question_id)
    question_version_number
    version_timestamp
    question_text
    (PK of this table is composed of question_history_id, original_question_id, and question_version_number)

When you make an edit for a question with some question_id, you do it in two stages:

insert into question_history the question_id (as original_question_id) and text into question_history. question_version_number and version_timestamp and question_history_id can be populated via a trigger.
update the existing record in question based on the data from the user's edit.

In this situation, the "current" version is always in the main question table, and if you want historic data you have to query question_history.

If you want to have everything in a single table, you could do it more simply like this:

question
--------
    question_id (PK)
    question_text
    question_version_number
    version_timestamp

This simplifies your database structure and the user's edit operations, but querying for the most current question will now always require you to sort the questions by version_timestamp (or by question_version_number - truth is, you only really need one of these). If you have lots of history, this could become a performance issue. It's also probably a good idea to include question_version_number or version_timestamp in a composite primary key that includes question_id so that you can't have the same version number for two records for the same question.

As for answers:

You can apply the same pattern to answers that I applied to questions. Additionally, you'll probably want to keep track of which version of a question an answer applied to, so you'll need a question_version_id for each version of an answer. Example:

answers
-------
    answer_id (PK)
    answer_text
    student_id (FK to students - I assume you will need a way to know who wrote which answer)
    answered_question_id (FK to questions.question_id)
    answered_question_version_number (FK to questions.version_number or
                                      question_history.version_number,
                                      depending on how you want to do it
                                       - you could also reference the version_timestamp
                                      if you prefer).

Now for the tests. This is where it could get tricky. You will want to version the tests, but does the version number of the test change every time one of the questions changes? That might be a good way to do it, though if someone makes minor edits to every question on a 50-question test, that might create too many versions. You could also try to only create a version of a test when a user explicitly clicks a "new version" button for the test, but if someone forgets it could screw things up in other ways. Or you could have all questions on one page so one single edit of the test will create one version but with all edits to all questions. That's something that's probably best controlled in the application.

I think the data structures will probably be the same though:

test
----
   test_id
   test_version_number
   version_timestamp

questions
---------
    question_id (PK)
    question_text
    question_version_number
    version_timestamp
    test_id (FK to tests)
    test_version_number (FK to tests.version_number - or you
                        could reference tests.version_timestamp if you prefer)

Now, say you want to get all answers for a particular version of a test for a particular student:

select answers.*
from answers
inner join questions
    on answers.question_id = questions.question_id
    and answers.answered_question_version_number = question.version_number
    and questions.test_id = $TEST_ID
    and answers.student_id = $STUDENT_ID
inner join tests
    on tests.test_id = questions.test_id
    and questions.test_version_number = tests.test_version_number
    and tests.test_version_number = $TEST_VERSION_NUMBER;


If XML child element value contains string return other child element value at same level

Use nodes() and value() to get the names and count in a derived table and filter your rows in the main query.

declare @T table(itemStates xml not null);

insert into @T (itemStates)
values ('<itemStats xmlns="http://www.blahblahblah.com/blah">
           <itemCounts>
             <item>
               <name>Toaster Oven Device</name>
               <count>6</count>
             </item>
             <item>
               <name>Washing Machine</name>
               <count>2</count>
             </item>
             <item>
               <name>Microwave Oven</name>
               <count>4</count>
             </item>
           </itemCounts>
         </itemStats>');

with xmlnamespaces(default 'http://www.blahblahblah.com/blah')
select T.Name,
       T.Count
from (
     select I.X.value('(name/text())[1]', 'nvarchar(max)') as Name,
            I.X.value('(count/text())[1]', 'int') as Count
     from @T as T
       cross apply T.itemStates.nodes('/itemStats/itemCounts/item') as I(X)
     ) as T
where T.Name like '%Oven%';


Should a inherited table have a new surrogate key, or just use parent table's key?

Strategy #2 has a name. It is called Shared Primary Key.  It it widely used with is a type relationships.  There is a tag over in Stackoverflow that groups question related to this concept.

See link


Why SQL Server installs multiple services for AS and RS, but only one for IS?

Integration Services is not "Instance aware". The rest (Analysis, Reporting, DB) are.

Note that SSIS doesn't need to be instance aware either. The service itself is pretty pointless and doesn't need to running. You simply need the SQL Server common binaries installed to run packages. The service is for SQL Server Agent and SSMS to browse packages etc

Links:

http://www.ssistalk.com/2009/11/04/ssis-what-does-the-ssis-service-actually-do-anyway
http://support.microsoft.com/kb/942176


Performance of a=0 and b=0 and ... z=0 vs a+b+c+d=0

In your question, you detail some tests that you've prepared where you "prove" that the addition option is quicker than comparing the discrete columns.  I suspect your test methodology may be flawed in several ways, as @gbn and @srutzky have alluded to.

First, you need to ensure you're not testing SQL Server Management Studio (or whatever client you're using).  For instance, if you are running a SELECT * from a table with 3 million rows, you're mostly testing SSMS's ability to pull rows from SQL Server and render them on-screen.  You're far better off to use something like SELECT COUNT(1) which negates the need to pull millions of rows across the network, and render them on screen.

Second, you need to be aware of SQL Server's data cache.  Typically, we test the speed of reading data from storage, and processing that data, from a cold-cache (i.e. SQL Server's buffers are empty).  Occasionally, it makes sense to do all your testing with a warm-cache, but you need to approach your testing explicitly with that in mind.

For a cold-cache test, you need to run CHECKPOINT and DBCC DROPCLEANBUFFERS prior to each run of the test.

For the test you've asked about in your question, I created the following test-bed:

IF COALESCE(OBJECT_ID('tempdb..#SomeTest'), 0) <> 0
BEGIN
    DROP TABLE #SomeTest;
END
CREATE TABLE #SomeTest
(
    TestID INT NOT NULL
        PRIMARY KEY
        IDENTITY(1,1)
    , A INT NOT NULL
    , B FLOAT NOT NULL
    , C MONEY NOT NULL
    , D BIGINT NOT NULL
);

INSERT INTO #SomeTest (A, B, C, D)
SELECT o1.object_id, o2.object_id, o3.object_id, o4.object_id
FROM sys.objects o1
    , sys.objects o2
    , sys.objects o3
    , sys.objects o4;

SELECT COUNT(1)
FROM #SomeTest;

This returns a count of 260,144,641 on my machine.

To test the "addition" method, I run:

CHECKPOINT 5;
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;

SET STATISTICS IO, TIME ON;
GO
SELECT COUNT(1)
FROM #SomeTest st
WHERE (st.A + st.B + st.C + st.D) = 0;
GO
SET STATISTICS IO, TIME OFF;

The messages tab shows:

  Table '#SomeTest'. Scan count 3, logical reads 1322661, physical reads 0, read-ahead reads 1313877, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  SQL Server Execution Times:
    CPU time = 49047 ms,  elapsed time = 173451 ms.

For the "discrete columns" test:

CHECKPOINT 5;
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;

SET STATISTICS IO, TIME ON;
GO
SELECT COUNT(1)
FROM #SomeTest st
WHERE st.A = 0
    AND st.B = 0
    AND st.C = 0
    AND st.D = 0;
GO

SET STATISTICS IO, TIME OFF;

again, from the messages tab:

  Table '#SomeTest'. Scan count 3, logical reads 1322661, physical reads 0, read-ahead reads 1322661, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  SQL Server Execution Times:
    CPU time = 8938 ms,  elapsed time = 162581 ms.

From the stats above you can see the second variant, with the discrete columns compared to 0, the elapsed time is about 10 seconds shorter, and the CPU time is about 6 times less.  The long durations in my tests above are mostly a result of reading a lot of rows from disk.  If you drop the number of rows to 3 million, you see the ratios remain about the same but the elapsed times drop noticeably, since the disk I/O has much less of an effect.

With the "Addition" method:

  Table '#SomeTest'. Scan count 3, logical reads 15255, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  SQL Server Execution Times:
    CPU time = 499 ms,  elapsed time = 256 ms.

With the "discrete columns" method:

  Table '#SomeTest'. Scan count 3, logical reads 15255, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  SQL Server Execution Times:
    CPU time = 94 ms,  elapsed time = 53 ms.

What will make a really really big difference for this test?  An appropriate index, such as:

CREATE INDEX IX_SomeTest ON #SomeTest(A, B, C, D);

The "addition" method:

  Table '#SomeTest'. Scan count 3, logical reads 14235, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  SQL Server Execution Times:
    CPU time = 546 ms,  elapsed time = 314 ms.

The "discrete columns" method:

  Table '#SomeTest'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  SQL Server Execution Times:
    CPU time = 0 ms,  elapsed time = 0 ms.

The execution plan for each query (with the above index in-place) is quite telling.

The "addition" method, which must perform a scan of the entire index:

and the "discrete columns" method, which can seek to the first row of the index where the leading index column, A, is zero:


Take average of multiple nullable columns

The only correct and scalable solution to that problem is to normalize your model.

However, you can normalize the data "on the fly" and then use standard SQL aggregation on the result.

select name, avg(t.val)
from the_table
  cross join unnest (array["2010","2011","2012"]) with ordinality as t(val)
group by name;

Online example: https://rextester.com/TGTC30399

But I would strongly recommend to fix your data model by properly normalizing it.


You could use a CHECK constraint to enforce that the column always contains uppercase values.

-- as new table
CREATE TABLE t1(c1 VARCHAR(200) CONSTRAINT c1_upper CHECK (BINARY UPPER(c1) = BINARY c1));
INSERT INTO t1(c1) VALUES ('a');
INSERT INTO t1(c1) VALUES ('A');
SELECT * FROM t1;

-- altering existing table
CREATE TABLE t2 (c2 VARCHAR(200) NOT NULL);
ALTER TABLE t2 ADD CONSTRAINT c2_upper CHECK (BINARY c2 = BINARY UPPER(c2));
INSERT INTO t2(c2) VALUES ('a');
INSERT INTO t2(c2) VALUES ('A');
SELECT * FROM t2;

SQL Fiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=a4d4e277ea6993d7b66a3b2f4871a320

Older versions of MySQL don't have check constraints. You can work around this by doing DML against a checked view. This works in version 5.5.

CREATE TABLE t1(c1 VARCHAR(200));
CREATE VIEW v1 AS SELECT * FROM t1 WHERE BINARY c1 = BINARY UPPER(c1) WITH CHECK OPTION;
INSERT INTO v1(c1) VALUES ('a');
-- fails
INSERT INTO v1(c1) VALUES ('A');
-- succeeds

SQL fiddle: https://dbfiddle.uk/?rdbms=mysql_5.5&fiddle=7416cfbbbbbd1161d48785dd384d5d30


What is the recommended way to join junction tables for efficient ordering/pagination?

Pagination

For pagination, LIMIT (and OFFSET) are simple, but typically inefficient tools for bigger tables. Your tests with LIMIT 10 only show the tip of the iceberg. Performance is going to degrade with a growing OFFSET, no matter which query you choose.

If you have no or little concurrent write access, the superior solution is a MATERIALIZED VIEW with an added row number, plus index on that. And all your queries select rows by row numbers.

Under concurrent write load, such a MV is outdated quickly (But a compromise like refreshing the MV CONCURRENTLY every N minutes may be acceptable).
LIMIT / OFFSET is not going to work properly at all since "the next page" is a moving target there, and LIMIT / OFFSET cannot cope with that. The best technique depends on undisclosed information.

Related:

Database engine with counting b-trees for efficient paging support
Optimize query with OFFSET on large table

Index

Your indexes generally look good. But your comment indicates that table tag has many rows. Typically, there is very little write load on a table like tag, which is perfect for index-only support. So add a multicolumn ("covering") index:

CREATE INDEX ON tag(id, name);

Related:

Can Postgres use an index-only scan for this query with joined tables?

Just the top N rows

If you don't actually need more pages (which isn't strictly "paging"), then any query style is good that reduces qualifying rows from article before retrieving details from the related tables (expensively). Your "limited subquery" (3.) and "lateral join" (4.) solutions are good. But you can do better:

Use an ARRAY constructor for the LATERAL variant:

SELECT a.id, a.title, a.score, tags.names
FROM   article a
LEFT   JOIN LATERAL (
   SELECT ARRAY (
      SELECT t.name
      FROM   article_tag a_t
      JOIN   tag t ON t.id = a_t.tag_id
      WHERE  a_t.article_id = a.id
   -- ORDER  BY t.id  -- optionally sort array elements
      )
  ) AS tags(names) ON true
ORDER  BY a.score DESC
LIMIT  10;

The LATERAL subquery assembles tags for a single article_id at a time, so GROUP BY article_id is redundant, as well as the join condition ON tags.article_id = article.id, and a basic ARRAY constructor is cheaper than array_agg(tag.name) for the remaining simple case.

Why is array_agg() slower than the non-aggregate ARRAY() constructor?

Or use a lowly correlated subquery, typically even faster, yet:

SELECT a.id, a.title, a.score
     , ARRAY (
         SELECT t.name
         FROM   article_tag a_t
         JOIN   tag t ON t.id = a_t.tag_id
         WHERE  a_t.article_id = a.id
      -- ORDER  BY t.id  -- optionally sort array elements
      ) AS names
FROM   article a
ORDER  BY a.score DESC
LIMIT  10;

db<>fiddle here
SQL Fiddle


If there was a clever person who decided to use index hints in their application's queries, dropping said index will cause the query to fail outright if/when it runs.
Something like a quarter or year-end report might not be showing any index usage due to its infrequency of execution depending on how often the system is restarted.


How can I prevent oracle to create minidumps?

Minidump files

The "minidump files" appear to be a Windows OS thing.

https://support.microsoft.com/en-us/help/307973/how-to-configure-system-failure-and-recovery-options-in-windows

But, if you disable them, you will just be hiding the fact that there is a problem.

I would prefer to hunt down the reason why they are being created and fix that problem.

The first thing to do is check which version you are running. For example, version 11.2.0.1 is known to be buggy.  If that is the case, your first step should be upgrading your database.

After that, I would look at the Oracle Alert logs and go from there.

note:  ORA-600/7554 usually means "Call Oracle Support".


adding an order by to this query returns faster than without, why?

The most obvious cause would be that (select max(ID) from tblR where cID = c.cID and ISNULL(aField,'') <> '') sub-query in the WHERE clause in combination with the TOP 500 is making the order make a difference.

In either case it will probably be running that sub-query individually for every row it might otherwise return until it has found 500 that match the whole WHERE (actually, it will be bright enough to check the other parts of the WHERE first, as they are much less expensive, but still it will be running the sub-query for a portion of the rows it meets). At a guess, more rows near the top when sorted by c.cID match currently so it has to run the sub-query less often before finding 500 matches than it does when running down the results in the order the query planner chooses by default.

(I'm assuming that the cs should be xs or vice-versa, otherwise the query would not run as the alias c is not defined anywhere)

It is worth running queries like that in the Management Studio with the "actual query plan" view turned on - then you might see from the diagram which parts are causing the differeing performance.


(POSTGRES) ON CONFLICT ... WHERE condition doesn't seem to be working

With the help of @a_horse_with_no_name, I was able to find the error in my thinking. There is a great example in the PostgresSQL docs that really clarified it for me:

  Insert or update new distributors as appropriate. Assumes a unique index has been defined that constrains values appearing in the did column. Note that the special excluded table is used to reference values originally proposed for insertion:

INSERT INTO distributors (did, dname)
    VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc')
    ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname;

My original thought was that the EXCLUDED table used the same column names as the table I was trying to insert from. In actuality, it references the column names of the table in which I am trying to insert into, but the values are what were originally proposed for insertion.

So, the end of my query should have looked like this:

...
ON CONFLICT (contact_id) DO
  UPDATE SET status = 'updated',
             source_updated_at = EXCLUDED.source_updated_at
  WHERE iic.source_updated_at != EXCLUDED.source_updated_at;

Thank you for being my rubber duck, and I hope this helps someone else in the future!


Permission denied to create "pg_catalog.tablename" during pg_dump targetting another machine

This turns out to be due to a somewhat obscure error message which happens when the schema you are dumping from with pg_dump does not exist on the target database/machine. I will leave this question, in the hope it might help someone else.


Updating a row through Linked Server to MySQL DB

You can use

EXEC('UPDATE product set [index]=''test_value'' where id=12345;') AT b2b

as an alternative syntax, which executes the query in pass-through mode.

The reason why you're getting the optimistic locking error is probably because the query can't be run in pass-through, so SQL Server downloads the whole table, locates the row to update and then pushes the row back to the linked server. Between the moment the row is read and the moment the row is written, the value has changed in the linked server, so you have an optimistic locking violation.


What's the official successor for sp_resetstatus in case of a SUSPECT database?

As of SQL Server 2012, sp_resetstatus is still supported.  However, Paul Randal has some excellent counsel, including do not detach that database.

The first and best answer is to restore a good backup.

If that does not work and you can retrieve the files from somewhere (where?), check out Paul's posts at:

http://www.sqlskills.com/blogs/paul/creating-detaching-re-attaching-and-fixing-a-suspect-database/
http://www.sqlskills.com/blogs/paul/checkdb-from-every-angle-emergency-mode-repair-the-very-very-last-resort/

Since these are detailed discussions, I will not rehash them. But read carefully.


EDU is an abstraction over the underlying OS scheduling unit, a sort of a surrogate key assigned to whatever is doing actual work in the OS. If the OS has true threads, distinct from processes, such as Windows, an EDU will map to a thread. On modern Linux, which doesn't have threads but rather lightweight processes (LWPs), an EDU would map to one of such LWPs and the thread ID value would be artificial.

On some operating systems there is an extra level of abstraction in the form of separately managed user and kernel threads; in such cases an EDU will be mapped to a distinct kernel thread ID (KTID) in addition to the user TID.


SQL Server Transactional Replication - what is sp_MSreleasesnapshotdeliverysessionlock?

It's important to understand where is the bottleneck...

Are the BCP-files for the snapshot actually getting created?
Are you using PULL or PUSH subscriptions?
Remote or Local DIST?
Can you setup a Profiler Trace or XE Session on each component...PUB, DIST and SUB and trace activity...plus maybe look at wait-stats and what SQL is waiting for?
Turn on Verbose-logging for LogReader-Agent and Distribution-Agent as well.
Query the History Replication Sys-Table to get additional information too...
sp_helptext on sp_MSreleasesnapshotdeliverysessionlock - what is in it?


getting records for yesterday day starting till ending PostgreSQL

You need to cast created_at as a date - that will remove the time.

now() also contains a time, so you need to use current_date

And finally your IN (..) is not needed, you can apply that condition directly in the where clause:

select camera_id, count(*) as snapshot_count
from snapshots
where created_at::date = current_date - 1
group by camera_id;

Note that the above will not use an index on created_at. If the performance is not acceptable and the condition would benefit from using an index, you can use something like this:

select camera_id, count(*) as snapshot_count
from snapshots
where created_at >= date_trunc('day', current_timestamp) - interval '1' day
  and create_at < date_trunc('day', current_timestamp)
group by camera_id;


Restore mysql database with some columns content enclosed by " and another not

LOAD DATA INFILE 'x:/folder/filename.csv'
INTO TABLE tablename ( field1,    -- load value into the field directly
                       field2,    -- when it is enclosed, d-quotes are saved
                       ... ,
                       @dummyN,   -- load value with wrapping into UDV
                       ...)
SET fieldN = TRIM(BOTH '"' FROM @dummyN) -- extract value from UDV and store into the field

This allows to remove d-quote chars when they are wrappers and store when they're a part of value, if this occures at the same time (for different fields).


how to script out all the permissions in a folder using powershell?

Get-Acl worked for me to copy permissions from my dev machine DATA folder to a test folder.

$Acl = Get-Acl "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER01\MSSQL\DATA"
Set-Acl "C:\Program Files\Microsoft SQL Server\test" $Acl

This overwrites the permissions on the new folder, handle with care.

(https://blogs.msdn.microsoft.com/johan/2008/10/01/powershell-editing-permissions-on-a-file-or-folder/)


It depends on whether your local server is Enterprise Edition.

If so, you can restore the database backup and leave it in NORECOVERY mode.  You will not be able to directly access the database, but you can create a SNAPSHOT database which will be Read Only.

That snapshot database can be dropped, the new TRL restored in NORECOVERY Mode, and recreate the snapshot.


Warn about table recreation on table change

No, the option is to either always prevent it or to not warn you at all.

What you should do, is merely learn and understand what types of changes will force SSMS to drop the table and re-create it, or stop using the designer UI altogether and use DDL. In some cases you can write DDL such that it doesn't have to drop and re-create the table (SSMS isn't smart enough for all of these cases), but in all cases at least you'll know what's going to happen.


Oracle analytic functions - how to get the highest RANKing row?

I fail to see the point of a requirements such as avoiding WHERE rank_ =, but here it is, without RANK(), or hardcoding a constant (still, hardcoding is done by using FIRST_VALUE):

select distinct grp,
  first_value(result1) over (partition by grp order by mbr desc) as result1,
  first_value(result2) over (partition by grp order by mbr desc) as result2
from (
select
  grp, mbr,
  reading1 - lag(reading2) over (partition by grp order by mbr) result1,
  reading2 - lag(reading1) over (partition by grp order by mbr) result2
from (select unique grp, mbr,
             nvl(reading1, 0) as reading1, nvl(reading2, 0) as reading2
      from test)
);

GRP                 RESULT1    RESULT2
---------------- ---------- ----------
A                        -1        1.3
B                     -20.2      -20.4
C                      -100      100.3

When this is easier to read in my opinion:

select grp, result1, result2 from (
select
  grp,
  reading1 - lag(reading2) over (partition by grp order by mbr) result1,
  reading2 - lag(reading1) over (partition by grp order by mbr) result2,
  rank() over (partition by grp order by mbr desc) as rank_
from (select unique grp, mbr,
             nvl(reading1, 0) as reading1, nvl(reading2, 0) as reading2
      from test)
) where rank_ = 1
;


Transfer row from 'parent' to 'child' without creating duplicate and without knowing all 'parent' columns

No one (or rather very few) uses "table inheritance", like that. That's a half-baked PostgreSQL feature which is used for some internal underpinnings exposed to the user in a fashion likely to confuse them. I'd highly suggest avoiding it. For more information see,

Using table inheritance instead of mapping tables

In your case though you can use a single-table hierarchy or self-referencing hierarchy,

CREATE SCHEMA master;
CREATE TABLE master.entities
(
  entity_id       int  GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  parent_id       int  REFERENCES master.entities,
  entity_name     text,
  entity_data      jsonb
);

INSERT INTO master.entities VALUES
  ( 1, null, 'Master', null),
  ( 2, 1   , 'Corp'  , '{"date_of_birth":"2018-07-05"}');

  I would like to be able to bring down a row that was added to the master.parties table and insert it into any of the parties tables in the other schemas (ie. corporate), without creating a duplicate row in the master table.

I'm not 100% sure what you mean here. Essentially the relations are not meta-data, it's data. So you have to have a duplicate row to have one entity under two parents. If you want to minimize duplication of the JSONB though you could outsource that to another table -- though I would probably not bother with it unless you have a desired answer as to how two parties will manage the data-stored in the jsonb.

Querying the above structure is slightly more complex, but you'll get the hang of it, you have to use a recursive CTE. You can find lots of examples of this under hierarchy,

WITH RECURSIVE t(id,parents,name, data) AS (
  SELECT entity_id, ARRAY[]::int[], entity_name, entity_data
  FROM master.entities
  WHERE parent_id IS NULL
  UNION ALL
    SELECT e.entity_id, parents||parent_id, e.entity_name, e.entity_data
    FROM t
    INNER JOIN master.entities AS e
      ON e.parent_id = t.id
)
TABLE t;

 id | parents |  name  |              data
----+---------+--------+---------------------------------
  1 | {}      | Master |
  2 | {1}     | Corp   | {"date_of_birth": "2018-07-05"}
(2 rows)

Note this schema also permits N-level relationships (you can create an almost infinite amount of children under children -- no additional tables required)


Database revision needed for sports events database

Clearly you need a "Competitor" table if you wish to represent them, which I assume you do! Competitor Name, Age, Sex, Contact etc.

Personally I would create a new table called "Discipline Type" which would differ from "Race Type".  The two are functionally independent - race type could be "Triathlon" while "Discipline Type" could be "Swimming"/"Running"/"Road Cycling".

Edit:
Sorry, since you wish to include teams, a team will represent a group or a single competitor. Therefore you probably want an "Entry" table which represents a person or Team entering a race Entry would have "Race ID", "Competitor ID" (these two your PK, required), "Team ID", "Team Order", "Team Captain" (binary field, last 3 fields not required). This would then be able to represent individual entries AND team entries. The "Team ID" would link to a "Team" table and would have "Team Name", "Support person contact" etc. Note that the team info is NOT required, so that you can have the same Entry table represent a single entrance from a team or a single entrance from 1 person.


Where do you find the citext module in postgres 9.3 on ubuntu 14.04?

Turns out the answer was on Stack overflow. And Daniel commented on it right about the time I was installing it.

sudo apt-get install postgresql-contrib-9.3

https://stackoverflow.com/a/22833812/147562


That's a fairly serious defect.

http://bugs.mysql.com/bug.php?id=55477

The lexer, encountering ! considers that the next character might be a continuation of the != operator, =, and if that happens, everything is fine.  Otherwise, if the next character is of the same class (of characters which might be part of a logical operator, such as <) but doesn't match what's expected... then the next character just... disappears, apparently since ! is a complete logical operator by itself.

The result is that !! resolves to ! when not separated by whitespace...

!!expr resolves to !(expr)

!!!expr resolves to !(!(expr))

!!!!expr also resolves to !(!(expr))


Reduce load off primary server by using log shipping, or mirroring, or something else?

Now that SQL Server 2012 has shipped, don't forget about AlwaysOn, which allows explicitly for reads scale out.

Mirroring works for what you describe, but the orchestration of database snapshots for reporting can get hairy (the mirror database is unreadable, you must report from database snapshots created periodically).

I'm not a fan of transactional replication, but it can be used for your scenarios and others more knowledgeable than me have came out In Defense of Transactional Replication.

Both of AlwaysOn and Transactional Replication offer what you want (scale out reads w/o disruption) and they both achieve near-real-time data latency. They differ though on price point: AlwaysOn is an SQL Server enterprise only feature and requires Windows Clustering which in turn requires Windows enterprise licensing, ie. it costs. Plus it requires an upgrade to SQL Server 2012. Transactional Replication on the other hand costs basically nothing in addition to what you already have (except if your whole setup is on Express Edition instances, which I doubt) but it comes at a cost because it will create friction with your development/deployment cycle, which now has to consider the replication impact (must add new articles to the publications, certain schema change operations require careful planning etc).

And for some out-of-the-box thinking consider how some have resorted to Using Service Broker instead of Replication.


Taking hot backup of slave node in postgres (master-slave config with repmgr)

At least on 9.3, you can run pg_basebackup against a hot standby replica, and that's the approach I would recommend. To do this, you must have postgresql.conf set appropriately:

max_wal_senders = 2
hot_standby = on

You didn't specify your version. If you're on a version that can't do a pg_basebackup from a standby you're pretty much out of luck - you need to do a logical dump, dump from the master, or stop the replica for the backup.


Change transaction isolation level inside running transaction

The syntax you are using is valid, all statements executed after the SET TRANSACTION ISOLATION LEVEL command will use the specified isolation level.

Consider the following table:

CREATE TABLE dbo.test
(
    ID int identity(1,1) Primary key
    , Field nvarchar(10)
)
GO

INSERT INTO dbo.test(Field)
Values ('XXXXXXXXXX')
GO 100

Next, start a transaction and execute 2 statements.
The first statement will use REPEATABLE READ (which won't release its shared locks when the statement completes), the second statement will use SERIALIZABLE (which will take and hold range locks)

BEGIN TRANSACTION

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

SELECT *
FROM dbo.test
WHERE id = 100

-- This will show a shared lock on key 100, the lock is kept during the transaction because repeatable read is used
SELECT *
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID

-- Isolation level 3 = repeatable read
SELECT transaction_isolation_level
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

SELECT *
FROM dbo.test
WHERE id >= 0 and id < 10

-- This will show the previous shared lock on key 100 + additional RangeS-S key locks taken by the serializable statement
SELECT *
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID

-- Isolation level 4 = Serializable
SELECT transaction_isolation_level
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID

COMMIT TRANSACTION

Looking at the output of sys.dm_tran_locks and sys.dm_exec_sessions you can see that the two statements issue different locks and use different isolation levels within the same transaction.
Although it's technically possible, I tend to use a single isolation level within a transaction.

When combining SNAPSHOT isolation with pessimistic locking you could get some unexpected results.

Imagine starting a transaction in SNAPSHOT isolation and reading some data.
Next, a second session updates the data you just read.
The first transaction switches to READ COMMITTED and reads the data again, SQL Server will now return the updated values.
Finally, you switch to SNAPSHOT isolation again and read the data. You'll use the  previously created snapshot to read from and the old values will be returned.

-- Note: you have to set your isolation level to snapshot before starting your transaction otherwise you will get an error

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRANSACTION

-- Returns XXXXXXXXXX
SELECT *
FROM dbo.test
WHERE id = 1

-- Run "UPDATE dbo.test SET Field = 'YYYYYYYYYY' WHERE id = 1" in a separate session

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

-- Returns YYYYYYYYYY
SELECT *
FROM dbo.test
WHERE id = 1

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

-- Returns XXXXXXXXXX
SELECT *
FROM dbo.test
WHERE id = 1

COMMIT


By default I always do my changes at /etc/mysql/mysql.conf.d/mysqld.cnf to do not split my changes too much, although, both of those should work /etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf, just don't forget restarting you mysql service.


SSIS export to flat file - how to configure all DataTypes for columns w/o manual entry?

If your goal is simply to create a Flat File have you considered using BCP.EXE to Bulk Copy your data out. Bulk Import and Export of Data (SQL Server) gives a link to the bcp utility.

You do need to give directions on how to interpret the data.  Here at Create a Format File (SQL Server) you can examine the two Format File type used to define the data format.  (Naturally.)

The format file that I use is a Non-XML Format File.  This is just a text file and is fairly easy to type and make changes within.  For me, this is a quick way to export a flat file. (Of course, if you read further down the page you will see an XML Format File, which might be more your style.)

It is also possible to use BCP to copy data back into a SQL Server and it is pretty fast.  (However BULK INSERT tends to be a bit faster for importing data.)

So BCP is fast, fairly easy, and comes in two flavors.


Obtaining the average of three columns per row in SQL

If the columns aren't nullable then simply using

(col1 + col2 + col3)/3

will work fine (though on some RDBMSs you might need to have a non integer numerator or divisor to avoid integer division).

For nullable columns you might want to use something like

SELECT CASE
         WHEN COALESCE(col1, col2, col3) IS NOT NULL THEN
         ( COALESCE(col1, 0) + COALESCE(col2, 0) + COALESCE(col3, 0) ) /
            (CASE WHEN col1 IS NULL THEN 0 ELSE 1 END +
             CASE WHEN col2 IS NULL THEN 0 ELSE 1 END +
             CASE WHEN col3 IS NULL THEN 0 ELSE 1 END)
       END

On SQL Server you could also use

SELECT *,
       (SELECT AVG(Col)
        FROM   (VALUES(Col1),
                      (Col2),
                      (Col3)) V(Col)) AS col_average
FROM   YourTable


Internal Online Store Database Design Issue

You'll can solve the problem by adding more linking tables. You can alter your design to resemble something like this:

CREATE TABLE Items
(
    itemID INT PRIMARY KEY IDENTITY(1,1)
    ,itemDetails NVARCHAR(100)
)
GO
CREATE TABLE Regions
(
    regionID INT PRIMARY KEY IDENTITY(1,1)
    ,regionName NVARCHAR(100)
)
GO
CREATE TABLE AddOns
(
    addOnID INT PRIMARY KEY IDENTITY(1,1)
    ,addOnDetails NVARCHAR(100)
)
GO
CREATE TABLE ItemAddOns
(
    itemAddOnID INT PRIMARY KEY IDENTITY(1,1)
    ,itemID INT
    ,addOnID INT
    ,CONSTRAINT [FK_ItemAddons_Items] FOREIGN KEY (itemID) REFERENCES dbo.Items (itemID)
    ,CONSTRAINT [FK_ItemAddons_AddOns] FOREIGN KEY (addOnID) REFERENCES dbo.AddOns (addOnID)
)
GO
CREATE TABLE ItemAvailability
(
     itemAvailabilityID INT PRIMARY KEY IDENTITY(1,1)
    ,itemID INT
    ,regionID INT
    ,cost INT
    ,CONSTRAINT [FK_ItemAvailability_Regions] FOREIGN KEY (regionID) REFERENCES dbo.Regions (regionID)
    ,CONSTRAINT [FK_ItemAvailability_Items] FOREIGN KEY (itemID) REFERENCES dbo.Items (itemID)
)
GO
CREATE TABLE AddOnAvailability
(
    addOnAvailabilityID INT PRIMARY KEY IDENTITY(1,1)
    ,addOnID INT
    ,regionID INT
    ,cost INT
    ,CONSTRAINT [FK_AddOnAvailability_Regions] FOREIGN KEY (regionID) REFERENCES dbo.Regions (regionID)
    ,CONSTRAINT [FK_AddOnAvailability_AddOns] FOREIGN KEY (addOnID) REFERENCES dbo.AddOns (addOnID)
)

Something like that setup will allow you to easily query the cost for any Items and AddOns by any region. If AddOns are not restricted to particular Items you can omit the ItemAddOns table.


Select random 10 unique rows from MySQL database?

You can do this by using following query.

SELECT * FROM questions WHERE question_id NOT IN (SELECT question_id FROM student_answers WHERE student_id = '5') ORDER BY RAND() LIMIT 10;

I have assumed that there is 3 tables.

question - Hold all questions
student_answers - hold student specific answers of each question.
student - hold student informations.

I expect that student_id is the primary key of student table and foreign key of student_answer table.

The student_is is brought from session or somewhere.


Failover Availability Group from linked Server

To answer your question, it should be as below provided you are executing below on primary replica.

SET @Query = 'EXEC (''use master; ALTER AVAILABILITY GROUP [' + @CurrentAG + '] FAILOVER'') AT [' + @FailoverServer + '];'
        EXEC sp_executesql @Query

A better solution would be to use dbatools to initiate failover Invoke-DbaAgFailover . This way, you dont have to use any extra server or linked server. It will be much flexible solution as well.


What you can try is the following while your database is in the MOUNTED estate

alter tablespace USERS offline;

and the same for the application tablespace you lost.

recover datafile 1;
alter database open;

NOTE:

If there is no backup and no way to restoring the lost datafiles, what you can do is backup any other important datafile/tablespace and recreate the database. I think it will be the less painful way to get a fully working database.


Dumping and restoring on db server - postgres

There are a bunch of ways of doing this.  My recommendation would be to have a dedicated database for maintenance operations and use something like pg_message_queue to send messages to listening applications.  You could then have a listener which could initiate pg_dump or pg_restore based on what is passed to it.

This would end up being queries like:

SELECT * FROM pg_mq_send_message('backup_db', 'mydbname'::text);

The listener could then be notified in real time (or wake up and poll, both methods are supported), and go ahead and backup bydbname using psql.  You could do the same with restore, with a different queue.

You could try to just use LISTEN and NOTIFY without queue tables, but there is virtually no security on those commands themselves, so that could lead to issues.  Also note this is lighter weight than ssh and API is totally asynchronous.

Disclaimer I am the author of pg_message_queue.


You can ask the Database Administrator for the environment what your SQL Server Login details are for using SQL Server Authentication.

Alternatively, you could connect to the SQL Server Instance via RDP and Windows Authentication and then create a new SQL Server Login for you to use remotely.

To confirm, you cannot use Windows authentication remotely (from outside of the domain the instance resides in) unless you are connecting from a domain with a trust relationship defined.


Multiple tables for different payment transaction

Always, start with the data model, you will get complete picture. I don't get your complete requirements, from my understanding, the following points are written.

Business Requirements:

A user can subscribe more than one course.
A course may subscribed by more than one user.
A user can subscribe more than one membership.
A membership can be subscribed by more than one user.
A user can have more than User Payment.
A user payment must contain atleast one User.

Data Model:

For the above requirements, the logical data model may look like this:

I think now you can keep your common payment info on UserPayment table. Also have a thought about future requirements, in that case, create separate entities for the following entities (examples).

Tax (type, rate, etc).
Price (type, value, etc).
Membership type (fulltime, quaterly, etc).
Course type (technical, design, etc).

Depends on the requirements, the model will change. First understand the requirements completely and start with the conceptual data model. It is a starting point, you will learn more, I hope this will give you an idea. Thanks!.


Either change config to allow SELF_SIGNED_CERT

npm config set strict-ssl false

or change registry url to http:// one

npm config set registry="http://registry.npmjs.org/"


Extract latest entry for each currency type from table

You can use row_number() to do this, with something like this:

select [Currency Code], [Starting Date], [Exchange Rate Amount]
from (
  select *,
  row_number() over (
       partition by [Currency Code]
       order by [Starting Date] desc
    ) as RN
  From myTable
) X where RN = 1;

The row number will number the rows per currency (that's what partition by does) starting from the newest, and then the outer select will just take the first for each currency.


MySQL: Is there any possible way to get notified about mysql-server FOREIGN KEY CONSTRAINT violation during INSERT, if FOREIGN_KEY_CHECKS=0 ?

Check out here - the point about using this system (or session) variable is to load tables in an order which is " different from that required by their parent/child relationships". You switch it off for that reason - if you want warnings of breaches of RI (Referential Integrity), then set it to 1 and you will get warnings. It can be set on the session level.


Load data - fields terminated by character appearing within a field statement

To answer your question yes I'm sure it is possible to employ a scheme like jkavalik suggested where you:

Import the entire file into a single column/cell in a temp table
Parse the malformed csv string using increasingly obtuse trickery as each edge case is discovered.

I'm sure a smarter person than me could write an event driven, real-time, web-enabled, protocol buffer communicating, graphical user interface, autonomous aircraft landing system for use in hurricanes using only SQL, but why would they?  You should use the best tool for the job.  If you can't correct the csv exporter to properly escape the data then you should properly escape the data manually or use a programming language that will make this task easier.

In my opinion, SQL is only going to make your life more difficult, and as such you should choose a different tool.


You should take a look the TIMESTAMPDIFF function.

From the example

mysql> SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55');
        -> 128885

You could use the microsecond unit and divide by 1000 - MySQL doesn't appear
to support milliseconds. HTH.

[EDIT in response to OP]

Take a  look at the code below - notice the 1 millisecond difference in the two
returned values. I tested for up to 50 years and it works fine on my 64 bit fedora linux system.

It is even accurate to 1 microsecond in 50 years - but you can use ROUND to
get suitable values.

mysql> SELECT TIMESTAMPDIFF(MICROSECOND, '2014-12-10 13:00:00.000', '2014-12-11 13:56:00.000')/1000 AS Milliseconds_Elapsed FROM Dual;
+----------------------+
| Milliseconds_Elapsed |
+----------------------+
|        89760000.0000 |
+----------------------+
1 row in set (0.00 sec)

mysql>
mysql> SELECT TIMESTAMPDIFF(MICROSECOND, '2014-12-10 13:00:00.000', '2014-12-11 13:56:00.001')/1000 AS Milliseconds_Elapsed FROM Dual;
+----------------------+
| Milliseconds_Elapsed |
+----------------------+
|        89760001.0000 |
+----------------------+
1 row in set (0.00 sec)

mysql>

50 years (+ 1 microsecond)

mysql> SELECT TIMESTAMPDIFF(MICROSECOND, '2014-12-10 13:00:00.000', '2062-12-11 13:56:00.001')/1000 AS Milliseconds_Elapsed FROM Dual;
+----------------------+
| Milliseconds_Elapsed |
+----------------------+
|   1514854560001.0000 |
+----------------------+
1 row in set (0.00 sec)

mysql>
mysql>
mysql> SELECT TIMESTAMPDIFF(MICROSECOND, '2014-12-10 13:00:00.000', '2062-12-11 13:56:00.000001')/1000 AS Milliseconds_Elapsed FROM Dual;
+----------------------+
| Milliseconds_Elapsed |
+----------------------+
|   1514854560000.0010 |
+----------------------+
1 row in set (0.00 sec)

mysql>


"SQLNCLI11" for linked server "linkedserver" returned message "The transaction manager has disabled its support for remote/network transactions."

SQL Server 2014 Availability Groups does not support distributed transactions.

SQL Server 2016 has limited support for distributed transactions, however, SQL Server 2017 has the best support.

Consider using SQL Server Integration Services or bcp in/out, or some other tool for Extract-Transform-Load, or ETL, operations.

FYI, you may not be aware of this, but doing an INSERT INTO ... into a table on a linked server results in the local SQL Server performing a series of cursor calls for each row inserted into the target table.

To prove this, I've created a linked server and ran the following code to setup a simple test-bed.

First, we'll create a table in tempdb at the linked server:

DECLARE @cmd nvarchar(max);
SET @cmd = 'IF OBJECT_ID(''dbo.InsertTest'', N''U'') IS NOT NULL
DROP TABLE dbo.InsertTest;
CREATE TABLE dbo.InsertTest
(
    id int NOT NULL PRIMARY KEY CLUSTERED
);';

EXEC [LinkedServer].tempdb.sys.sp_executesql @cmd;

Here, I'm creating a local table in tempdb to hold some rows:

IF OBJECT_ID('dbo.InsertTest', N'U') IS NOT NULL
DROP TABLE dbo.InsertTest;CREATE TABLE dbo.InsertTest
(
    id int NOT NULL PRIMARY KEY CLUSTERED
);

INSERT INTO dbo.InsertTest (id)
SELECT ROW_NUMBER() OVER (ORDER BY c.object_id, c.column_id)
FROM sys.columns c;

a quick check to see if we have rows in the source table:

SELECT CountOfRows = COUNT(1)
FROM dbo.InsertTest;

╔═════════════╗
║ CountOfRows ║
╠═════════════╣
║         990 ║
╚═════════════╝

Here, we'll insert rows from the local source table into the remote linked-server table.  I've setup a trace running on the target server so we can see the T-SQL statements issued against it.

INSERT INTO [LinkedServer].tempdb.dbo.InsertTest (ID)
SELECT it.ID
FROM dbo.InsertTest it;

The output:

╔═════════════════════════════════════════════════════════════════════════════════════════════════════════════╗
║                                             SET XACT_ABORT OFF                                              ║
╠═════════════════════════════════════════════════════════════════════════════════════════════════════════════╣
║ declare @p1 int                                                                                ║
║ set @p1=NULL                                                                                ║
║ declare @p2 bigint                                                                                ║
║ set @p2=NULL                                                                                ║
║ exec [sys].sp_getschemalock @p1 output,@p2 output,N'"tempdb"."dbo"."InsertTest"'                            ║
║ select @p1, @p2                                                                                ║
║ declare @p1 int                                                                                ║
║ set @p1=NULL                                                                                ║
║ declare @p3 int                                                                                ║
║ set @p3=229378                                                                                ║
║ declare @p4 int                                                                                ║
║ set @p4=294916                                                                                ║
║ declare @p5 int                                                                                ║
║ set @p5=NULL                                                                                ║
║ exec sp_cursoropen @p1 output,N'select * from "tempdb"."dbo"."InsertTest"',@p3 output,@p4 output,@p5 output ║
║ select @p1, @p3, @p4, @p5                                                                                ║
║ exec sp_cursor 180150027,4,0,N'[tempdb].[dbo].[InsertTest]',@id=1                                           ║
║ exec sp_cursor 180150027,4,0,N'[tempdb].[dbo].[InsertTest]',@id=2                                           ║
║ exec sp_cursor 180150027,4,0,N'[tempdb].[dbo].[InsertTest]',@id=3                                           ║
║ exec sp_cursor 180150027,4,0,N'[tempdb].[dbo].[InsertTest]',@id=4                                           ║
║ exec sp_cursor 180150027,4,0,N'[tempdb].[dbo].[InsertTest]',@id=5
.
.
║ exec sp_cursor 180150027,4,0,N'[tempdb].[dbo].[InsertTest]',@id=987                                         ║
║ exec sp_cursor 180150027,4,0,N'[tempdb].[dbo].[InsertTest]',@id=988                                         ║
║ exec sp_cursor 180150027,4,0,N'[tempdb].[dbo].[InsertTest]',@id=989                                         ║
║ exec sp_cursor 180150027,4,0,N'[tempdb].[dbo].[InsertTest]',@id=990                                         ║
║ exec [sys].sp_releaseschemalock 1                                                                           ║
║ exec sp_cursorclose 180150027                                                                               ║
║ exec sp_reset_connection                                                                                ║
╚═════════════════════════════════════════════════════════════════════════════════════════════════════════════╝

However, if you run the insert from the target server, like this:

INSERT INTO dbo.InsertTest (ID)
SELECT it.ID
FROM [LinkedServer].tempdb.dbo.InsertTest it;

You see this in the trace:

╔════════════════════════╦════════════════════════════════════════════════╗
║        TextData        ║                                                ║
╠════════════════════════╬════════════════════════════════════════════════╣
║ SET STATISTICS XML ON  ║                                                ║
║                        ║ INSERT INTO dbo.InsertTest (ID)                ║
║                        ║ SELECT it.ID                                   ║
║                        ║ FROM [CP708-D377\MV].tempdb.dbo.InsertTest it; ║
║ SET STATISTICS XML OFF ║                                                ║
╚════════════════════════╩════════════════════════════════════════════════╝

Clearly, it pays to run the insert at the target server, instead of the source server.


CRUD is meant to define the characteristics necessary for a database as it relates to persistent storage.  It is not meant to describe everything that could be done by a database engine.

To make a comparison, fundamentally a vehicle is a device used for transport.  While true, this definition certainly doesn't include all the detail entailed in a modern automobile.

A database engine might handle multiple users, transactions, MVCC (Multiversion Concurrency Control), buffers and caches, ACID (atomicity, consistency, isolation, durability), as well as different isolation levels.  A read may pull data from memory, remote databases, and multiple tables on disk processing it using SQL through multiple explicit and/or implicit code paths in order to present it to the requesting application.  A create may allocate storage, provision structures, assign values, and do it's own processing before storing data. Etc.


How to generate missing values for each employee for each day

You must generate all possible (employee, day) pairs, so you must obtain employees list additionally, not only days list.

The query will be something like

SELECT emp_list.emp_Id, day_list.day, COALESCE(EmpWorkCount.daycount, 0) daycount
FROM ( SELECT 0 day
       UNION ALL
       SELECT 1
       UNION ALL
       SELECT 2
       UNION ALL
       SELECT 3 ) day_list
JOIN ( SELECT emp_Id FROM EmpWorkCount
       UNION
       SELECT emp_Id FROM Emp ) emp_list
LEFT JOIN EmpWorkCount ON day_list.day = EmpWorkCount.day
                      AND emp_list.emp_Id = EmpWorkCount.emp_Id
/* ORDER BY 1, 2 */


Oracle Application Express Listener PDF Report

Here's an answer from an Oracle Employee on Oracle Community:

"The problem is when the default page size is set to A4, which translates into points with decimals when rendering the PDF. Please, change the Units to Points, and round up your page width and height."

Reference:

https://community.oracle.com/message/1793888


How to prevent crashes connected with dropped tables during mysql import?

This is a big problem with the design of your system!

You are apparently performing DDL on a running application. This is never a good idea. The one Open Source server which does support transactional DDL is PostgreSQL - from here:

  You can't recover from an add/drop on a database or tablespace, but
  all other catalog operations are reversible.

MySQL even in its latest version (as of 05/2018) - verion 8, doesn't support tranactional DDL - from here:

  Some statements cannot be rolled back. In general, these include data
  definition language (DDL) statements, such as those that create or
  drop databases, those that create, drop, or alter tables or stored
  routines.

This page goes on to say:

  You should design your transactions not to include such statements. If you issue a statement early in a transaction that
  cannot be rolled back, and then another statement later fails, the
  full effect of the transaction cannot be rolled back in such cases by
  issuing a ROLLBACK statement. [Emphasis mine]

What, exactly, will, or will not, be rolled back isn't specified in detail - bit of a lottery situation which will require lots of testing! I wouldn't open that can of worms if I were you!

Could you possibly expand expand on your question by letting us know why, exactly, you feel that you need to issue such commands against a running application and maybe we can help you find a more suitable alternative solution?


The simple answer is No.

In Mysql replication, Master copies the bin log files to slaves, and after that, it's work is over. Now the Slave will run the bin files and execute them, but there won't be any performance on Master.

There might be scenario where you are using full synchronous replication, in which master will wait for the slave to execute the query, but again it won't impact the performance in terms of memory or CPU, but the master will wait for the query to be executed.

Also, for your second question, Phil already answered it, that ssh sends data through encryption which uses a lot of CPU, hence if you want other ways, use the other methods which are described by Phil.


Query looks optimal but logical reads is huge

Sorry for the late response, I've been quite busy.
I tried reordering the columns in the index by adding a new non-clustered index with the following fields and order:

PK_Field3 uniqueidentifier
PK_Field1 datetime
PK_Field2 int

The results are great:

(1 filas afectadas)
Table 'MainTable'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 filas afectadas)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

The actual query plan shows no parallellism.
Now I have a tough work to decide whether to keep it or not, despite the good results, since I don't find quite optimal to keep a 100 MB index for a single query. If I find out it is useful for other usual queries I'll keep it.
Thanks for your suggestions!


SQL over partition -avoid repeated statement in for every column

In standard SQL, you have a clause  where you can name the Partitioning option,but it is currently not supported by any RDBMS .You can see the connect item raised by Itzik Ben-Gan and vote for it

If implemented the Clause would look like this..

     SELECT empid, qty,
     SUM(qty) OVER W1 AS run_sum_qty,
     AVG(qty) OVER W1 AS run_avg_qty,
     FROM Sales.EmpOrders
    WINDOW W1 AS ( PARTITION BY empid
     ORDER BY ordermonth
     ROWS BETWEEN UNBOUNDED PRECEDING
     AND CURRENT ROW );


MDX - Getting the last 6 month Dynamically

Assuming you have properly set the ValueColumn property of your CreatedOn attribute in your dimension (so that MemberValue comes back a date data type) then the following should work:

select {[Measures].[Your Measure Here]} on 0,
[DimAccounts].[AccountPK].[AccountPK].Members on 1
from [Your Cube]
where {
 Filter(
  [DimAccounts].[CreatedOn].[CreatedOn].Members
  ,[DimAccounts].[CreatedOn].CurrentMember.MemberValue > DateAdd("m",-6,Now())
 )
}

Or if you want to create a named set so that any user can just drop it on rows, put this in the calculations tab of your cube designer:

CREATE SET CurrentCube.[Accounts Created In Last 6 Months]
as
Exists(
 [DimAccounts].[AccountPK].[AccountPK].Members,
 Filter(
  [DimAccounts].[CreatedOn].[CreatedOn].Members
  ,[DimAccounts].[CreatedOn].CurrentMember.MemberValue > DateAdd("m",-6,Now())
 )
);


How to run SQL Profiler to monitor 1 specific stored procedure?

Following code will do trace one stored procedure. Check for folder location, duration and like statement for the name of your stored procedure. I also excluded any replication related stored procedure in case you have replication.  Let me know if you run into any issues.

-- Variables
DECLARE @rc       INT,
        @TraceID  INT,
        @filename NVARCHAR(256),
        @maxsize  BIGINT,
        @on       BIT
DECLARE @Folder VARCHAR(1000)

SET @Folder = 'D:\Proc_Trace'

-- Check for other running traces
SELECT @rc = Count(DISTINCT traceid)
FROM   ::fn_trace_getinfo(DEFAULT)

IF ( @rc > 1 )
  GOTO error

-- Create the holding folder
IF RIGHT(@Folder, 1) <> '\'
    SET @Folder = @Folder + '\'

SELECT @Folder = @Folder +
    'output_' +
    CONVERT(VARCHAR(8), GETDATE(), 112) + '_' +
    LEFT(REPLACE(CONVERT(VARCHAR(20), GETDATE(), 108), ':', ''), 4)

EXEC master.dbo.xp_create_subdir @Folder

SELECT @filename = @Folder + '\' + REPLACE(@@SERVERNAME, '\', '_') + '__sp_trace'
SELECT @maxsize=50, @on=1

-- Create a Queue
EXEC @rc = sp_trace_create @TraceID OUTPUT, 2, @filename, @maxsize, NULL
IF (@rc != 0) GOTO error

-- Set the events
EXEC sp_trace_setevent @TraceID, 11, 2, @on  -- Needed for 2008 RML

EXEC sp_trace_setevent @TraceID, 11, 3, @on
EXEC sp_trace_setevent @TraceID, 11, 7, @on
EXEC sp_trace_setevent @TraceID, 11, 15, @on
EXEC sp_trace_setevent @TraceID, 11, 8, @on
EXEC sp_trace_setevent @TraceID, 11, 1, @on
EXEC sp_trace_setevent @TraceID, 11, 14, @on

EXEC sp_trace_setfilter @TraceID, 1, 0, 6, N'%p_FoldersApplicants_sel_All_Candidates_OBy_CreatedDate_DESC%'
EXEC sp_trace_setfilter @TraceID, 6, 0, 7, N'%repl%'

-- Set the trace status to start
EXEC sp_trace_setstatus @TraceID, 1

-- Wait 10 min
WAITFOR DELAY '08:00:00'

-- Stop/Delete the trace

EXEC sp_trace_setstatus @traceID, 0
EXEC sp_trace_setstatus @traceID, 2

GOTO finish

ERROR:
SELECT ErrorCode=@rc

FINISH:

--getting result

SELECT
      StartTime,
      HostName,
      SUBSTRING(TextData, CHARINDEX('EXEC', TextData), 1000) AS ProcCall,
      SUBSTRING(TextData, 103, 1000) AS Parameters
FROM ::fn_trace_gettable('D:\Proc_Trace\output_20111010_1009\filename.trc', DEFAULT)
WHERE EventClass = 11
ORDER BY StartTime


If by "table lock" you mean a "shared for read lock" (*SHRRD)... then it's likely that the cursor has been pseudo closed

  Pseudo closed cursors are a key part of a performance optimization feature of IBM DB2 for i SQL. When an application closes a cursor, DB2 for i normally closes the cursor and closes the file, deleting the ODP (Open Data Path). If the application runs the same statement multiple times, each new execution requires a full open of the target file. The idea behind pseudo closed cursors is to not fully close the cursor and file but rather to cache the cursor for potential future use. The cursor is left in a soft closed (or pseudo closed) state. When the cursor is pseudo closed, the underlying file and ODP are left open. All record locks are released; however, a shared lock still appears on the file. DB2 for i can then reuse the cursor as needed without the overhead of a full open of the file. DB2 for i can also decide to hard close the pseudo closed cursor when needed.

They should not cause a problem unless you have a process that needs exclusive access, such as one that uses Clear Physical File Member (CLRPFM)

Such process should be avoided now-a-days, given the 24/7 nature of most business.

If you have to have an exclusive process, modify the process to request the locks to be released if needed... ALCOBJ OBJ((MYFILE *FILE *EXCL)) CONFLICT(*RQSRLS)


What are the main causes of deadlocks and can they be prevented?

tracking deadlocks is the easier of the two:

  By default, deadlocks are not written in the error log. You can cause SQL to write deadlocks to the error log with trace flags 1204 and 3605.

  Write deadlock info to the SQL Server error log:
  DBCC TRACEON(-1, 1204, 3605)

  Turn it off:
  DBCC TRACEOFF(-1, 1204, 3605)

  See "Troubleshooting Deadlocks" for a discussion of trace flag 1204 and the output you will get when it is turned on.
  https://msdn.microsoft.com/en-us/library/ms178104.aspx

Prevention is more difficult, essentially you have to look out for the following:

Code Block 1 locks resource A, then resource B, in that order.

Code Block 2 locks resource B, then resource A, in that order.

This is the classic condition where a deadlock can occur, if the locking of both the resources is not atomic, the Code Block 1 can lock A and be pre-empted, then Code Block 2 locks B before A gets processing time back.  Now you have deadlock.

To prevent this condition, you can do something like the following

Code Block A (psuedo code)

Lock Shared Resource Z
    Lock Resource A
    Lock Resource B
Unlock Shared Resource Z
...

Code Block B (pseudo code)

Lock Shared Resource Z
    Lock Resource B
    Lock Resource A
Unlock Shared Resource Z
...

not forgetting to unlock A and B when done with them

this would prevent the deadlocking between code block A and code block B

From a database perspective, I'm not sure on how to go about preventing this situation, as locks are handled by the database itself, i.e. row/table locks when updating data.  Where I've seen the most issues occur is where you saw yours, inside a cursor.  Cursors are notoriously inefficient, avoid them if at all possible.


In SQL Server, always store dates using a datetime, datetimeoffset or datetime2 datatype.  Storing them as anything else invariably results in needless CPU spent converting the column into some kind of date.

The possible exception to this might be if you want to store just the year, which could be a tiny integer.  However, since you need hour and timezone precision, use the datetimeoffset data type.  datetimeoffset(0) will give you precision down to the second, which you don't require, however you'll be guaranteed to never have an invalid date stored, and you'll know that the data is time-zone compatible.

datetimeoffset(0) occupies 8 bytes, unless you're storing a lot of dates in memory, this shouldn't be considered wasteful in light of the advantages conferred by using the correct data type.

You may be interested in reading this answer, which talks about some common problems storing date/time data incorrectly.


You must evaluate everybody's rank and select the Username last

SET @rnk=0; SET @rank=0; SET @curscore=0;
SELECT score,Username,rank FROM
(
    SELECT AA.*,BB.Username,
    (@rnk:=@rnk+1) rnk,
    (@rank:=IF(@curscore=score,@rank,@rnk)) rank,
    (@curscore:=score) newscore
    FROM
    (
        SELECT * FROM
        (
            SELECT COUNT(1) scorecount,rating score
            FROM mytable GROUP BY rating
        ) AAA
        ORDER BY score DESC
    ) AA LEFT JOIN scores BB USING (score)
) A WHERE Username='jkz101022';

If you want the original row, then do this

SET @rnk=0; SET @rank=0; SET @curscore=0;
SELECT HEX(B.UUID) as UUID,B.Username,B.Rating,A.rank FROM
(
    SELECT AA.*,BB.Username,
    (@rnk:=@rnk+1) rnk,
    (@rank:=IF(@curscore=score,@rank,@rnk)) rank,
    (@curscore:=score) newscore
    FROM
    (
        SELECT * FROM
        (
            SELECT COUNT(1) scorecount,rating score
            FROM mytable GROUP BY rating
        ) AAA
        ORDER BY score DESC
    ) AA LEFT JOIN scores BB USING (score)
) A INNER JOIN mytable B USING (Username)
WHERE A.Username='jkz101022';

UPDATE 2014-08-25 16:57 EDT

I Fixed My Code

SET @rnk=0; SET @rank=0; SET @curscore=0;
SELECT HEX(B.UUID) as UUID,B.Username,B.Rating,A.rank FROM
(
    SELECT AA.*,BB.Username,
    (@rnk:=@rnk+1) rnk,
    (@rank:=IF(@curscore=rating,@rank,@rnk)) rank,
    (@curscore:=rating) newscore
    FROM
    (
        SELECT * FROM
        (
            SELECT DISTINCT Rating score FROM survivalgamesstats
        ) AAA
        ORDER BY score DESC
    ) AA LEFT JOIN survivalgamesstats BB ON AA.score=BB.Rating
) A INNER JOIN survivalgamesstats B USING (Username)
WHERE A.Username='jkz101022';

I got this outout

mysql> SET @rnk=0; SET @rank=0; SET @curscore=0;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT HEX(B.UUID) as UUID,B.Username,B.Rating,A.rank FROM
    -> (
    ->     SELECT AA.*,BB.Username,
    ->     (@rnk:=@rnk+1) rnk,
    ->     (@rank:=IF(@curscore=rating,@rank,@rnk)) rank,
    ->     (@curscore:=rating) newscore
    ->     FROM
    ->     (
    ->         SELECT * FROM
    ->         (
    ->             SELECT DISTINCT Rating score FROM survivalgamesstats
    ->         ) AAA
    ->         ORDER BY score DESC
    ->     ) AA LEFT JOIN survivalgamesstats BB ON AA.score=BB.Rating
    -> ) A INNER JOIN survivalgamesstats B USING (Username)
    -> WHERE A.Username='jkz101022';
+----------------------------------+-----------+--------+------+
| UUID                             | Username  | Rating | rank |
+----------------------------------+-----------+--------+------+
| 000597056E564BF587DAA1D31E51E29F | jkz101022 |   1600 |    2 |
+----------------------------------+-----------+--------+------+
1 row in set (0.00 sec)

mysql>

UPDATE 2014-08-25 17:15 EDT

I tried it with rob160502 and got this:

mysql> SET @rnk=0; SET @rank=0; SET @curscore=0;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT HEX(B.UUID) as UUID,B.Username,B.Rating,A.rank FROM
    -> (
    ->     SELECT AA.*,BB.Username,
    ->     (@rnk:=@rnk+1) rnk,
    ->     (@rank:=IF(@curscore=rating,@rank,@rnk)) rank,
    ->     (@curscore:=rating) newscore
    ->     FROM
    ->     (
    ->         SELECT * FROM
    ->         (
    ->             SELECT DISTINCT Rating score FROM survivalgamesstats
    ->         ) AAA
    ->         ORDER BY score DESC
    ->     ) AA LEFT JOIN survivalgamesstats BB ON AA.score=BB.Rating
    -> ) A INNER JOIN survivalgamesstats B USING (Username)
    -> WHERE A.Username='rob160502';
+----------------------------------+-----------+--------+------+
| UUID                             | Username  | Rating | rank |
+----------------------------------+-----------+--------+------+
| 00022E3531F44C36AE058B3CF063C02F | rob160502 |   1650 |    1 |
+----------------------------------+-----------+--------+------+
1 row in set (0.00 sec)

mysql>

UPDATE 2014-08-25 17:38 EDT

I even tried HauntedCorpse and got this:

mysql> SET @rnk=0; SET @rank=0; SET @curscore=0;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT HEX(B.UUID) as UUID,B.Username,B.Rating,A.rank FROM
    -> (
    ->     SELECT AA.*,BB.Username,
    ->     (@rnk:=@rnk+1) rnk,
    ->     (@rank:=IF(@curscore=rating,@rank,@rnk)) rank,
    ->     (@curscore:=rating) newscore
    ->     FROM
    ->     (
    ->         SELECT * FROM
    ->         (
    ->             SELECT DISTINCT Rating score FROM survivalgamesstats
    ->         ) AAA
    ->         ORDER BY score DESC
    ->     ) AA LEFT JOIN survivalgamesstats BB ON AA.score=BB.Rating
    -> ) A INNER JOIN survivalgamesstats B USING (Username)
    -> WHERE A.Username='HauntedCorpse';
+----------------------------------+---------------+--------+------+
| UUID                             | Username      | Rating | rank |
+----------------------------------+---------------+--------+------+
| 000399B665D14979B3C0AF309112625F | HauntedCorpse |   1536 |    5 |
+----------------------------------+---------------+--------+------+
1 row in set (0.00 sec)

mysql>

Give it a Try !!!

CAVEAT : Please note that the results come from your actual data in SQLFiddle. I simply loaded the data into a test database and ran my code against it.


OK Brace yourself. You can migrate the table from Windows by copying it and converting it.
STEP 01
Copy those three(3) files into the data folder /usr/var/lib/mydb in Linux
You should have

/usr/var/lib/mydb/region.frm
/usr/var/lib/mydb/region.myd
/usr/var/lib/mydb/region.myi

STEP 02
Change the filename name extensions of two of the files
cd /usr/var/lib/mydb
mv region.myd region.MYD
mv region.myi region.MYI

Required for MyISAM tables in Linux

See my old post Error When Restoring Backed Up Tables
See the chat session that I used to troubleshoot this question

STEP 03
Change the owner of the three files
cd /usr/var/lib/mydb
chown mysql:mysql region.frm
chown mysql:mysql region.MYD
chown mysql:mysql region.MYI

STEP 04
Convert the Table to InnoDB
mysql> use mydb
mysql> ALTER TABLE region ENGINE=InnoDB;

That's it.
GIVE IT A TRY !!!


Azure SQL - is it possible to assign CREATE PROCEDURE permission for the master database?

Not if you are using Azure DB.  Probably if you use Azure Instance.
https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/transact-sql-tsql-differences-sql-server#security


Column Level Encryption in SQL Server 2016

Taking into consideration the remarks of CLOSE SYMMETRIC KEY (highlighting mine)

  Open symmetric keys are bound to the session not to the security
  context. An open key will continue to be available until it is either
  explicitly closed or the session is terminated.

Security best practices usually suggest having a security mechanism (symmetric key) open for the minimum amount of time that is it necessary and only in the scope (stored procedure) necessary.

Referencing the example code in DECRYPTBYKEYAUTOCERT, there are basically two ways to decrypt column encrypted data.

OPTION ONE

Typically what you'd do in a stored procedure where you open the key,
decrypt the data using DecryptByKey and then close the key before
returning control.

OPTION TWO

Can be used in a view that utilizes the DecryptByKeyAutoCert
function to automatically decrypt the data without having to
explicitly open the key.  (Requires VIEW DEFINITION permission on the symmetric key and CONTROL permission on the certificate.)

--Create the keys and certificate.
USE AdventureWorks2012;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mzkvdlk979438teag$$ds987yghn)(*&4fdg^';
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'mzkvdlk979438teag$$ds987yghn)(*&4fdg^';
CREATE CERTIFICATE HumanResources037
   WITH SUBJECT = 'Sammamish HR',
   EXPIRY_DATE = '10/31/2009';
CREATE SYMMETRIC KEY SSN_Key_01 WITH ALGORITHM = DES
    ENCRYPTION BY CERTIFICATE HumanResources037;
GO
----Add a column of encrypted data.
ALTER TABLE HumanResources.Employee
    ADD EncryptedNationalIDNumber varbinary(128);
OPEN SYMMETRIC KEY SSN_Key_01
   DECRYPTION BY CERTIFICATE HumanResources037 ;
UPDATE HumanResources.Employee
SET EncryptedNationalIDNumber
    = EncryptByKey(Key_GUID('SSN_Key_01'), NationalIDNumber);
GO
--
--Close the key used to encrypt the data.
CLOSE SYMMETRIC KEY SSN_Key_01;
--
--There are two ways to decrypt the stored data.
--
--OPTION ONE, using DecryptByKey()
--1. Open the symmetric key
--2. Decrypt the data
--3. Close the symmetric key
OPEN SYMMETRIC KEY SSN_Key_01
   DECRYPTION BY CERTIFICATE HumanResources037;
SELECT NationalIDNumber, EncryptedNationalIDNumber
    AS 'Encrypted ID Number',
    CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
    AS 'Decrypted ID Number'
    FROM HumanResources.Employee;
CLOSE SYMMETRIC KEY SSN_Key_01;
--
--OPTION TWO, using DecryptByKeyAutoCert()
SELECT NationalIDNumber, EncryptedNationalIDNumber
    AS 'Encrypted ID Number',
    CONVERT(nvarchar, DecryptByKeyAutoCert ( cert_ID('HumanResources037') , NULL ,EncryptedNationalIDNumber))
    AS 'Decrypted ID Number'
    FROM HumanResources.Employee;


Can somebody please tell what is the difference between these two date comparisons?

The SQL Server GETDATE() function returns the exact date and time as it at the time of execution including the seconds/milisecond i.e. 2020-07-24 14:08:40.670. When the query executes, the

GETDATE()-3

evaluates the date/time as it at at the time of execution minus 3 days i.e.  2020-07-21 14:08:40.670 and thus only items with an f.Date greater then/equal to  2020-07-21 14:08:40.670 will be returned.
Using CONVERT with a Style of 101 on GETDATE() returns a date in the format of mm/dd/yyyy. In your example above of

CAST(CONVERT(VARCHAR(10),GETDATE() - 3,101) AS DATETIME)

the

CONVERT(VARCHAR(10),GETDATE() - 3,101)

evaluates the date/time as it at at the time of execution minus 3 days without any time portion i.e.  2020-07-21. When this is then CAST to a datetime the time portion is defaulted to 00:00:00 i.e. 2020-07-21 00:00:00.000.
As the derived expression in your second statement is earlier than that in the first you may have additional rows returned.


Why does SQL Server return some rows while still executing the query, and sometimes not?

The answer, as usual (alright, most of the time), lies in the execution plan.

There are certain operators that require all rows to arrive at them before they can start processing those rows, and passing them downstream, for example:

Hash Join (on building the hash table)
Hash Match
Sort (Except Hash Flow Distinct)

They're either called blocking, or stop and go operators because of this, and they're often chosen when the optimizer thinks it'll have to process a whole lot of data to find your data.

There are other operators that are able to begin streaming, or passing any found rows along immediately

Nested Loops
Index supported Merge Joins
Stream Aggregates

When queries start returning data immediately, but don't finish immediately, it's usually a sign that the the optimizer chose a plan to locate and return some rows quickly using operators that have a lower start up cost.

This can happen because of row goals introduced either by you, or by the optimizer.

It can also happen if a bad plan is chosen for some reason (lack of SARGability, parameter sniffing, insufficient statistics, etc.), but that takes more digging to figure out.

For more information, check out Rob Farley's blog here

And Paul White's series on row goals here, here, here, and here.

It should also be noted that, if you're talking about SSMS, rows only appear once an entire buffer has been filled, not just willy-nilly.


Mysql Server version: 5.0.77-log Source distribution

STOP SLAVE; RESET MASTER; is not the right way to do a failover. You can stop a slave if you want, but RESET MASTER will delete your own binary logs. You may be looking for RESET SLAVE; CHANGE MASTER TO MASTER_HOST=''; (RESET SLAVE ALL; since 5.5). The actual failover is not performed by mysql, but by an external entity, be it a proxy or a connector.

As you have resetted the master data and the slave binary logs, you need to perform a clone of the slave (current master) with its binary logs coordinates (with mysqldump or Percona XtraBackup) and set the replication in the other direction.

If you intend to do manual failovers, a master-master replication with one of the nodes in read-only mode is suggested. You also have software like MHA that can simplify the process.


Can you create a table param WITHOUT a pre-defined TVP in a SP?

No, that's not possible at this time.

Microsoft recently added the Create Table As syntax to Azure SQL Data Warehouse, but it has not made it to Azure SQL, or the boxed product yet (AFAIK, it's not even in vNext at the moment). Even if it does, it's not clear if it would be supported for table variables like in your example.

Further, SELECT INTO is also not available for table variables, as it is for temp tables and permanent tables.

SELECT TOP 1000 *
INTO @dingdong --Doesn't work
FROM dbo.Users AS u

SELECT TOP 1000 *
INTO #dingdong --Works
FROM dbo.Users AS u

Edit to answer the edit to your question: The syntax you're talking about is for an inline table valued function, which looks like this (from the docs).

  -- Transact-SQL Inline Table-Valued Function Syntax
  CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name    ( [ {
  @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type
      [ = default ] [ READONLY ] }
      [ ,...n ]     ]   )   RETURNS TABLE
      [ WITH  [ ,...n ] ]
      [ AS ]
      RETURN [ ( ] select_stmt [ ) ]   [ ; ]


Prevent gaps while using Identity/sequence object in sql server

In order to create auto numbers without gaps we can use QUEUE object of MS SQL Server.

So the idea is that Queue supports concurrency and roll back of transactions. We use SEQUENCE object to generate continuous set of numbers and send them to QUEUE. After that we can receive them back from that Queue concurrently and if one of client's requests fails then our auto number stays in queue for the next available client.

So this way we achieve concurrency and unique stream of auto-numbers without gaps.

The only potential issue is that sometimes our auto numbers are going to be in the wrong time order, but it should be ok.


SSMS: possible to script 'file per object' into categorised folders like in object explorer?

You could import the database definition in SSDT using the "Schema Compare" tool.

Simply create an empty database project, right click and select "Schema compare". Select the project as target and the live database as source. Click "Compare" and then "Update": SSDT will script all objects and add them to your project.

SSDT by default creates a folder for each schema and in each folder creates separate folders for tables, stored procedures, views and so on.


The SSIS Send mail task connects directly to a SMTP server - it doesn't use database mail.  If you want to use database mail, you would invoke it through an 'Execute T-SQL Statement' task that calls a stored procedure that wraps the database mail facility, or something similar.


How to query Mirroring RTO & RPO from system objects

sp_dbmmonitorresults will generate the data you're after. You could either periodically log it to a table (if you want to gather historical data), or run it ad-hoc, storing the data in a temp table to be able to query to derive your desired metrics.

You can also use Perfmon counters to track this if you want to integrate it with a monitoring tool that relies on Perfmon.

As an aside, I would avoid using RPO and RTO in this circumstance (despite what Microsoft says). Mirroring/AGs should be treated as high availability, not disaster recovery. A user dropping a table cannot be protected by an availability group or mirror - they will happily replay that mistake on your secondary nodes.

Using these terms with people not familiar with the technology could be misleading.


Plan A:  Clean up the dates as you load the data.  How are you loading it?  INSERT?  LOAD DATA INFILE?  It is possible to do the STR_TO_DATE() in either case.

Plan B:  (This requires "Plan A" for future inserts.)  Devise a script, possibly mostly inside a Stored Routine that would do the following for any given column known to be a VARCHAR that should be a DATE.  (Do something similar for a DATETIME.)  Here is a sketch of what is needed for one $column in one $table:

ALTER TABLE $table ADD COLUMN tmp DATE NOT NULL;
UPDATE $table SET tmp = STR_TO_DATE($column, '%d/%m/%Y');
ALTER TABLE $table DROP COLUMN $column, RENAME COLUMN tmp TO $column;


How to copy a MySQL database which is a mixture of InnoDB and MyISAM to the same server?

Before you give up on mysqldump, have you ever considered performing parallel mysqldumps?

I wrote a script on how to do this: How can I optimize a mysqldump of a large database?

Going back to your question, you could resort to doing rsync of a live server as long as the target DB server will have the exact same version of MySQL that the source DB server has.

Just run rsync against /var/lib/mysql on a master and copy it to /var/lib/mysql on the target server. Of course, I would run rsync several times. Until the final rsync you should run the FLUSH TABLES WITH READ LOCK. Before copying make sure you hose all binary logs and start from scratch.

If you want the target server to have binary logging, please make you have something like the following in /etc/my.cnf:

[mysqld]
log-bin=mysql-bin

Please try running this script in the event you do not want to shutdown MySQL on the master:

RSYNCSTOTRY=10
cd /var/lib/mysql
X=0
while [ ${X} -lt ${RSYNCSTOTRY} ]
do
    X=`echo ${X}+1|bc`
    rsync -r * slaveserver:/var/lib/mysql/.
    sleep 60
done
mysql -u... -p... -e"FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400);"
sleep 60
SLEEPID=`mysql -u... -p... -e"SHOW PROCESSLIST;" | grep "SELECT SLEEP(86400)" | awk '{print $1}'`
rsync -r * slaveserver:/var/lib/mysql/.
mysql -u... -p... -e"KILL ${SLEEPID};"

I am a little more conservative in terms of data and index pages being cached while doing this. Personally, I prefer to shutdown mysql after several rsyncs instead of the FLUSH TABLES WITH READ LOCK. Another alternative to this script would be the following script which shuts down mysql for the final rsync:

mysql -u... -p... -e"RESET MASTER;"
RSYNCSTOTRY=10
cd /var/lib/mysql
X=0
while [ ${X} -lt ${RSYNCSTOTRY} ]
do
    X=`echo ${X}+1|bc`
    rsync -r * slaveserver:/var/lib/mysql/.
    sleep 60
done
service mysql stop
rsync -r * slaveserver:/var/lib/mysql/.
service mysql start

Give it a try !!!

CAVEAT

If you have any InnoDB data, you should set this about 1 hour before attempt to rsync:

SET GLOBAL innodb_max_dirty_pages_pct = 0;

This will cause InnoDB to page out uncommitted data from the InnoDB Buffer Pool faster. In MySQL 5.5, this is no longer necessary.


You can try to use next config for logrotate. I think "copytruncate" does that You need.

/var/log/postgresql/pgstartup.log {
       daily
       rotate 7
       copytruncate
       compress
       delaycompress
       notifempty
       missingok
}


The 'mysql' & 'node' exporters are lightweight processes and shouldn't impact your servers if you're running with default settings. Be careful when enabling the additional stats which use information_schema - these can impact your DB servers (especially when you have a lot of databases / tables)
Nagios is chiefly used for 'Alerting' while Grafana on the other hand is chiefly used for 'Trending'. The alerts that Nagios provides are based on specific thresholds (warning / critical). It is possible to use Prometheus' AlertManager (which collects data for Grafana) in order to receive alerts on specific metrics. With Prometheus you can leverage the time series database to generate more intelligent alerts which look at the rate of change rather than a specific threshold - see https://prometheus.io/docs/alerting/alertmanager/ for more details on how to set this up.


For InnoDB tables, this is both possible and redundant.
It is possible using Percona Server's INNODB_INDEX_STATS table in INFORMATION_SCHEMA. This table shows you the number of pages per index in an InnoDB tables.

Well, apparently InnoDB manages its own pages very well. Upon purging of data it merges back its index nodes. I have done multiple tests to verify that an OPTIMIZE TABLE -- a complete rebuild of the table -- does not actually improve on index fragmentation.

Please see my post on the subject.

By the way, I believe MySQL 5.6 has a similar table built-in.

With regard MyISAM tables, I'm unaware that there's any way of looking at the fragmentation.


Why does 10^37 / 1 throw an arithmetic overflow error?

Understanding Precision and Scale in the context of Arithmetic Operations

Let's break this down and take a close look at the details of the divide arithmetic operator.  This is what MSDN has to say about the result types of the divide operator:

  Result Types

  Returns the data type of the argument with the higher precedence. For more information, see Data Type Precedence (Transact-SQL).

  If an integer dividend is divided by an integer divisor, the result is an integer that has any fractional part of the result truncated.

We know that @big_number is a DECIMAL. What data type does SQL Server cast 1 as?  It casts it to an INT. We can confirm this with the help of SQL_VARIANT_PROPERTY():

SELECT
      SQL_VARIANT_PROPERTY(1, 'BaseType')   AS [BaseType]  -- int
    , SQL_VARIANT_PROPERTY(1, 'Precision')  AS [Precision] -- 10
    , SQL_VARIANT_PROPERTY(1, 'Scale')      AS [Scale]     -- 0
;

For kicks, we can also replace the 1 in the original code block with an explicitly typed value like DECLARE @one INT = 1; and confirm we get the same results.

So we have a DECIMAL and an INT. Since DECIMAL has a higher data type precedence than INT, we know the output of our division will be cast to DECIMAL.

So where's the problem?

The problem is with the scale of the DECIMAL in the output.  Here is a table of rules about how SQL Server determines the precision and scale of results obtained from arithmetic operations:

Operation                              Result precision                       Result scale *
-------------------------------------------------------------------------------------------------
e1 + e2                                max(s1, s2) + max(p1-s1, p2-s2) + 1    max(s1, s2)
e1 - e2                                max(s1, s2) + max(p1-s1, p2-s2) + 1    max(s1, s2)
e1 * e2                                p1 + p2 + 1                            s1 + s2
e1 / e2                                p1 - s1 + s2 + max(6, s1 + p2 + 1)     max(6, s1 + p2 + 1)
e1 { UNION | EXCEPT | INTERSECT } e2   max(s1, s2) + max(p1-s1, p2-s2)        max(s1, s2)
e1 % e2                                min(p1-s1, p2 -s2) + max( s1,s2 )      max(s1, s2)

* The result precision and scale have an absolute maximum of 38. When a result
  precision is greater than 38, the corresponding scale is reduced to prevent the
  integral part of a result from being truncated.

And here's what we have for the variables in this table:

e1: @big_number, a DECIMAL(38, 0)
-> p1: 38
-> s1: 0

e2: 1, an INT
-> p2: 10
-> s2: 0

e1 / e2
-> Result precision: p1 - s1 + s2 + max(6, s1 + p2 + 1) = 38 + max(6, 11) = 49
-> Result scale:                    max(6, s1 + p2 + 1) =      max(6, 11) = 11

Per the asterisk comment on the table above, the maximum precision a DECIMAL can have is 38. So our result precision gets cut down from 49 to 38, and "the corresponding scale is reduced to prevent the integral part of a result from being truncated."  It is not clear from this comment how the scale is reduced, but we do know this:

According to the formula in the table, the minimum possible scale you can have after dividing two DECIMALs is 6.

Thus, we end up with the following results:

e1 / e2
-> Result precision: 49 -> reduced to 38
-> Result scale:     11 -> reduced to 6

Note that 6 is the minimum possible scale it can be reduced to.
It may be between 6 and 11 inclusive.

How this Explains the Arithmetic Overflow

Now the answer is obvious:

The output of our division gets cast to DECIMAL(38, 6), and DECIMAL(38, 6) cannot hold 1037.

With that, we can construct another division that succeeds by making sure the result can fit in DECIMAL(38, 6):

DECLARE @big_number    DECIMAL(38,0) = '1' + REPLICATE(0, 37);
DECLARE @one_million   INT           = '1' + REPLICATE(0, 6);

PRINT @big_number / @one_million;

The result is:

10000000000000000000000000000000.000000

Note the 6 zeros after the decimal. We can confirm the result's data type is DECIMAL(38, 6) by using SQL_VARIANT_PROPERTY() as above:

DECLARE @big_number   DECIMAL(38,0) = '1' + REPLICATE(0, 37);
DECLARE @one_million  INT           = '1' + REPLICATE(0, 6);

SELECT
      SQL_VARIANT_PROPERTY(@big_number / @one_million, 'BaseType')  AS [BaseType]  -- decimal
    , SQL_VARIANT_PROPERTY(@big_number / @one_million, 'Precision') AS [Precision] -- 38
    , SQL_VARIANT_PROPERTY(@big_number / @one_million, 'Scale')     AS [Scale]     -- 6
;

A Dangerous Workaround

So how do we get around this limitation?

Well, that certainly depends on what you're making these calculations for. One solution you may immediately jump to is to convert your numbers to FLOAT for the calculations, and then convert them back to DECIMAL when you're done.

That may work under some circumstances, but you should be careful to understand what those circumstances are. As we all know, converting numbers to and from FLOAT is dangerous and may give you unexpected or incorrect results.

In our case, converting 1037 to and from FLOAT gets a result that is just plain wrong:

DECLARE @big_number     DECIMAL(38,0)  = '1' + REPLICATE(0, 37);
DECLARE @big_number_f   FLOAT          = CAST(@big_number AS FLOAT);

SELECT
      @big_number                           AS big_number      -- 10^37
    , @big_number_f                         AS big_number_f    -- 10^37
    , CAST(@big_number_f AS DECIMAL(38, 0)) AS big_number_f_d  -- 9999999999999999.5 * 10^21
;

And there you have it.  Divide carefully, my children.


Try this query where I've replaced the &lt; for < and &gt; for >. I hope it will help.

SELECT
  src.TABLE_NAME,
  src.COLUMN_NAME,
  src.DATA_TYPE,
  src.CHARACTER_MAXIMUM_LENGTH,
  dst.TABLE_NAME,
  dst.COLUMN_NAME,
  dst.DATA_TYPE,
  dst.CHARACTER_MAXIMUM_LENGTH
FROM originalDB.INFORMATION_SCHEMA.COLUMNS src
  INNER JOIN newDB.INFORMATION_SCHEMA.COLUMNS dst
    ON dst.TABLE_NAME COLLATE DATABASE_DEFAULT = src.TABLE_NAME COLLATE DATABASE_DEFAULT
    AND dst.COLUMN_NAME COLLATE DATABASE_DEFAULT = src.COLUMN_NAME COLLATE DATABASE_DEFAULT
WHERE
  dst.CHARACTER_MAXIMUM_LENGTH < src.CHARACTER_MAXIMUM_LENGTH
  OR
  dst.DATA_TYPE COLLATE DATABASE_DEFAULT <> src.DATA_TYPE COLLATE DATABASE_DEFAULT;


Upgrade SQL Server 2005 Enterprise to SQL Server 2008 R2 Standard

You cannot do an upgrade from enterprise to Standard edition. Instead you can follow below steps :

Install and configure fresh SQL Server standard 2008R2 instance (same configuration as like your 2008R2 ).
Run below script replacing the @destserver with the NEW Server Name on the Old Enterprise Edition instance.

Note: Test below script upfront in your environment. I have assumed that xp_cmdshell is enabled. If not, it needs to be enabled before you run this script.

Edit: As per Aaron's suggestion, you can think of doing backup restore as a good and safe option (the script can be changed to do that).

set nocount on
--  Author  :: Kin
--  Desc    :: Move Logins, Databases from one instance to another
-- Version :: 1.0 for dba.stackexchange.com
--  Date    :: 04/29/2013

  declare @datafile varchar(255),
            @logfile varchar(255),
            @dbid tinyint,
            @SQLText varchar(8000),
            @dbname varchar(255),
            @destserver varchar(255),
            @SQLText2 varchar(8000)
set @destserver ='' --Insert your Destination Server NAme in here.
--1.Transfer Logins
select @SQLText='exec master..xp_cmdshell ''sqlcmd -S'+@@servername+' -E -Q"execute master.dbo.sp_help_revlogin" -oD:\logs\revloginout.sql'''
print @sqltext
--exec (@sqltext)
-- Create on Destination Server.
select @SQLText='exec master..xp_cmdshell ''sqlcmd -S'+@destserver+' -E -iD:\logs\revloginout.sql'''
print @sqltext
--exec (@sqltext)
--2. Detach All Local Databases and prepare for Attach on dest.
if exists(select 1 from tempdb..sysobjects where name like '%#filetable%')
      begin
      drop table #filetable
      end
      create table #filetable (mdf varchar(255),ldf varchar(255),dbid tinyint,dbname varchar(100),fileid tinyint)
      --

      insert #filetable (mdf,dbid,fileid)
      select physical_name,database_id,data_space_id from sys.master_files where data_space_id=1

      insert #filetable (ldf,dbid,fileid)
      select physical_name,database_id,data_space_id from sys.master_files where data_space_id=0

      update u
      set u.dbname = s.name
      from #filetable u
      inner join master..sysdatabases s
      on u.dbid = s.dbid
select @dbid = min(dbid) from #filetable where dbid > 4
while @dbid is not null
begin

      select @SQLText = 'alter database '+ dbname from #filetable where dbid = convert(varchar,@dbid)
      select @SQLText = @SQLText+' set single_user with rollback immediate'
      select @SQLText = @SQLText+' exec master..sp_detach_db ' + dbname from #filetable where dbid = convert(varchar,@dbid)
      print @SQLText
      Exec(@SQLText)

      select @SQLText2 = 'exec opendatasource(''SQLNCLI'',''Datasource='+@destserver+';Persist Security Info=False;Integrated Security=SSPI'').master.dbo.sp_attach_db '''+dbname+'''' from #filetable where dbid = @dbid
      select @SQLText2= @SQLText2+','''+ mdf+'''' from #filetable where dbid = @dbid and mdf is not null
      select @SQLText2=@SQLText2+','''+ ldf+''''  from #filetable where dbid = @dbid and ldf is not null
      print @SQLText2
      Exec(@SQLText)
      select @dbid = min(dbid) from #filetable where dbid > 4 and dbid > @dbid
end
select * from #filetable
drop table #filetable
--Finally Shutdown SQL Server
--shutdown with nowait


What is an advantage or disadvantage for not upgrading database server?

If you don't:

it will be unmaintainable at some point because of OS, version, patch, whatever
hardware failure may force an upgrade at the wrong time

What you gain:

older code can be simplified with new constructs (eg ROW_NUMBER)
far better error handling (TRY/CATCH)
engine improvements: most queries will run quicker on a later version

MS don't support it anymore either


Create a T-sql script JOB SQL-2012 and scheduled

Ok, after yor comment it is clear. I will do the following:

Create a new DB called SERVICE, MAINTANANCE, etc.
Inside it create a stored procedures for different steps - drop old DB, create new DB, populate new DB. You can make all that in one procedure also.
Create one procedure which will execute others - step by step (if you not implemented all logic in one procedure).
Creata a job with permissions - inside it strat main procedure. Or tell DEV team to use main procedure inside new DB (with appropriate permissions).