Store last elements in a dedicated table i'm thinking about something.
Consider a table "messages" which stores messages between member. This table can potentially billions messages.
Looking for all message from a message would become a long query (in term of time)
is it a good idea to put last message in a dedicated table ?
So each entry in the table 'last_message' looks like

id | msg | user_pk

This table is updated each time a user add a new message, the previsous one goes to the table "message"
So each time we look for all last message we just have to look into the last_message table which contains less data than "messages"

To me the main advantage is the request speed, the disadvantage is the data management bewteen "messages" and "last_messages" tables and update requests.

It is likely that you would partition the table by time or region, etc.  Also this is what indexes or caching already do.

So your idea could be appropriate but you need to think about whether you are reinventing the wheel or if your use case can really benefit.


Speeding up a simple select query on Mariadb I have a 100k rows table with the following structure:

+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(11)          | NO   | PRI | NULL    | auto_increment |
| user_id  | int(11)          | NO   | UNI | NULL    |                |
| success  | int(10) unsigned | NO   |     | NULL    |                |
| fail     | int(10) unsigned | NO   |     | NULL    |                |
| points   | int(11)          | NO   | MUL | NULL    |                |
+----------+------------------+------+-----+---------+----------------+

This table holds users' scores in a game. All fields' values other than id's and user_id's are constantly changing on every user event.

A simple select used for displaying the top scoring users on this table runs in 0.3s on production.

MariaDB [db]> select user_id, points from state where points > 100000 ;
...
119 rows in set (0.31 sec)
MariaDB [db]>show profile for query 3;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000090 |
| checking permissions | 0.000018 |
| Opening tables       | 0.000030 |
| After opening tables | 0.000014 |
| System lock          | 0.000015 |
| Table lock           | 0.000013 |
| After table lock     | 0.000016 |
| init                 | 0.000027 |
| optimizing           | 0.000021 |
| statistics           | 0.000029 |
| preparing            | 0.000034 |
| executing            | 0.000012 |
| Sending data         | 0.307494 |
| end                  | 0.000041 |
| query end            | 0.000018 |
| closing tables       | 0.000036 |
| freeing items        | 0.000015 |
| updating status      | 0.000038 |
| logging slow query   | 0.000068 |
| cleaning up          | 0.000034 |
+----------------------+----------+

For the points column is ever changing, I did not want to create an index for it. And this behaviour is expected. But a full select, without a where condition runs for 0.4s either.

MariaDB [db]> select user_id, points from state;
...
126851 rows in set (0.40 sec)

If I create an index for the points column, it will most probably result in great amount of IO operations on the server, but It will speed up the top scores list. If I filter and sort the list in server side code, I can speed up the list, but this time mariadb does not provide the result in a shorter time.

How can I speed up this simple select query?

| Sending data         | 0.307494 |

Most of your time is spend on transporting results from server to your client.

use compression (MYSQLI_CLIENT_COMPRESS)
get faster connection
select less data


Index rebuild suspended due to IO_COMPLETION wait type I am executing the rebuild index script in SSMS to rebuild a specific primary key index of a major table in our database. It has been running for 13 hours now. sp_who2 shows me that it was suspended due to IO_COMPLETION wait type.

Now I don't have any idea to how to deal with the situation.

What is IO_COMPLETION?
How can I make this job runnable from suspended?
Can I kill the reindex job SPID and plan it for some other time? I heard that it's not a good idea as it will take a lot of time to rollback.

When I check to see the percentage of completion of rebuilt index with some query using exec_sql_text and exec_sql_request, it is showing ZERO. Now the physical reads = 310,777,191. A while later the physical reads = 319,741,497.
How long should we wait and let it run? Is there anyway to get/see an estimate?

The table contains 8 billion records. It was fragmented 80 percent.

Community wiki answer:

  What is IO_COMPLETION?

This wait is associated with a sort operator that runs out of memory and has to spill to tempdb. It is not immediately obvious why rebuilding an index would involve sorting, perhaps there are some details missing from the question.

  Is there anyway to get/see an estimate?

If you happen to know how large the table is (page size specifically), that would be roughly the number of reads. If you only know the size in MB (or GB), you'd have to divide by 8192 bytes (the size of a data page) to get a rough estimate of total number of reads.

Rebuilding an index might do at least 2x this number depending on which rebuild options you choose.


Pass values to column to create graphs in excel I'm trying to convert the result of a sentence into columns to create a graph in excel.

I have this sql

select
hss.instance_number INST,
to_char(snap.begin_interval_time,'dd/mm/yyyy hh24:mi:ss') as Snap_Inicial,
hss.pool,hss.name,trunc(hss.bytes/1024/1024,2) SIZE_IN_MB
FROM DBA_HIST_SGASTAT hss,  dba_hist_snapshot snap
WHERE snap.instance_number = hss.instance_number
and hss.snap_id=snap.snap_id
and hss.pool='shared pool'
and hss.instance_number=1
and hss.pool is not null
and BEGIN_INTERVAL_TIME BETWEEN sysdate-5 and sysdate
and hss.name in ('KGLHD','SQLA','KGLS')
ORDER BY INST, BEGIN_INTERVAL_TIME;

and I get this output

INST SNAP_INICIAL        POOL          NAME           SIZE_IN_MB
---- ------------------- ------------- -------------- ----------
   1 03/05/2020 09:00:30 shared pool   SQLA           150
   1 03/05/2020 09:00:30 shared pool   KGLS           23
   1 03/05/2020 09:00:30 shared pool   KGLHD          14
   1 03/05/2020 09:30:32 shared pool   SQLA           160
   1 03/05/2020 09:30:32 shared pool   KGLS           33
   1 03/05/2020 09:30:32 shared pool   KGLHD          10

but I want an output like this

   INST SNAP_INICIAL        POOL          SQLA         KGLS    KGLHD
---- ------------------- ------------- -----------  --------  ---------
   1 03/05/2020 09:00:30 shared pool      150        23        14
   1 03/05/2020 09:30:32 shared pool      160        33        10

Is this posible?

Thanks.

You want an Oracle PIVOT.

SELECT * FROM (
  SELECT hss.instance_number AS inst,
         snap.begin_interval_time AS snap_inicial,
         hss.pool,
         hss.name,
         TRUNC(hss.bytes / 1024 / 1024, 2) AS size_in_mb
    FROM dba_hist_sgastat hss
    JOIN dba_hist_snapshot snap
      ON snap.instance_number = hss.instance_number
     AND snap.snap_id = hss.snap_id
   WHERE hss.pool = 'shared pool'
     AND hss.instance_number = 1
     AND hss.pool IS NOT NULL
     AND snap.begin_interval_time BETWEEN SYSDATE - 5 AND SYSDATE
     AND hss.name IN ('KGLHD', 'SQLA', 'KGLS')
)
PIVOT (
SUM(size_in_mb) FOR name IN ('KGLHD', 'SQLA', 'KGLS')
)
ORDER BY inst ASC, snap_inicial ASC;


Get two row counts from the same table What I am trying to achieve is run both queries (below) at the same time.
From one table rvsessions I need to get the count for the total amount of sessions(which is achieved by the number of times the rvsessions_user_id appears in the table) and the number of sessions correct (correct = 1 and incorrect = 0)

<?php
$query = "SELECT * , count(*) AS rvsession_user_id FROM rvsessions
GROUP BY rvsession_user_id ORDER BY rvsession_user_id DESC";

$query = "SELECT * , count(*) AS rvsession_correct FROM rvsessions
WHERE rvsession_correct = 1 GROUP BY rvsession_user_id";

$select_all_user_sessions = mysqli_query($connection,$query);
$total_user_session_count = mysqli_num_rows($select_all_user_sessions);

while($row = mysqli_fetch_assoc($select_all_user_sessions)) {

$rvsession_username  = $row['rvsession_username'];
$count_user_sessions = $row['rvsession_user_id'];
$count_sessions_correct = $row['rvsession_correct'];

echo "
";
           echo "$rvsession_username	";
           echo "$count_user_sessions	";
           echo "$count_sessions_correct	";
echo "
";
                       }
?>

Each individual query runs fine and produces the correct results.
When I run them together both counts are not producing the correct results.
When I try to embed one query in the other as a nested sub-query, I get a syntax-error. I am at a loss... What would be the correct syntax for this?

SELECT *,
       COUNT(*) AS rvsession_user_id,
       SUM(rvsession_correct=1) AS rvsession_correct
FROM rvsessions
GROUP BY rvsession_user_id


WARNING Error checking mysqldump version Error retrieving version from mysqldump.exe: mysqldump: unknown option '--no-beep' (exit 2) When i go to do a manual export from mysql database to workbench, this error occurs,  i dont know solve

--no-beep is an option for the mysql command-line query tool, not for mysqldump.

I would guess that you have entered the no-beep in your my.cnf configuration file under the [client] section, which applies to both mysql and mysqldump clients. You need to move no-beep to the [mysql] section of the config file, so it applies only to the mysql client.


Which oracle should I download for windows 7 x64? My os is: windows 7 x64

I tried to download oracle and install it in my computer, but I have downloaded 3 different oracle releases(including oracle 11G), they can't be installed.

They reported such error:

Need os version: 5.1, ..., 6.0
But found: 6.1

I have spent a day to download them, that I don't know which one I should try.

Is there any oracle 10G can run in windows 7 x64? If not, oracle 11G would be fine.

I'm re-reading your post, trying to determine if you're having problems installing 10g, 11g or both.  Whichever, I found an article on how to install Oracle 11g on Windows 7, describing the problem that you're having above.  Basically, the installer should present you with an option to override the prerequisite OS check.  Follow the link, go to step #3 and see if that helps you.


Exclude dataset from a query I have 2 tables

EXAM (StudentID, SubjectID, SchoolYear, Period, Mark) and
CANCELED_EXAM (StudentID, SubjectID, SchoolYear, Period).

Primary key for both tables are (StudentID, SubjectID, SchoolYear, Period).

When a student wants to take an exam,  then we write one row in EXAM table.
But if a student passed an exam and is not satisfied, then he wants to cancel the exam and we need to write one row in table CANCELED_EXAM.

My question is: How to select just non-canceled exams from table EXAM.

This is standard SQL

SELECT
   *
FROM
   EXAM E
WHERE
   NOT EXISTS (SELECT *
       FROM
          CANCELED_EXAM CE
       WHERE
          E.StudentID = CE.StudentID AND
          E.SubjectID = CE.SubjectID AND
          E.SchoolYear = CE.SchoolYear AND
          E.Period = CE.Period )


How to extract multiple key value pairs into distinct columns I have a database that is outside my control, and am pulling data from it using Power BI for reporting. The database is a list of pages on a website, where each page is a new row, and the columns all have metadata regarding the page (created by, created by, and so on). Except not all of the metadata is stored in distinct columns, there is a "Primary" and "Secondary" column that stores mulitple key value pairs. For example:

PageName | CreatedBy | Primary | Secondary
page1    | Joe       | [owner:frank,topic:meals] | [topic:drinks]
page2    | Dale      | [owner:joe, topic:drinks, topic:meals] | [topic:appetizers]

The metadata is even more complex than above with several other keys that can potentially occur in either Primary or Secondary column. But the root problem is, how can I extract these key value pairs using Power BI so that my final table has each key as a unique column with all instances of its values across Primary / Secondary as a list, similar to this:

PageName | CreatedBy | Owner | Topic |
page1    | Joe       | frank | meals |
page2    | Dale      | joe   | drinks, meals, appetizers |

This can be achieved using a few lines of Power Query:

Remove outer brackets ("[" & "]") using Table.ReplaceValue
Merge [Primary] & [Secondary] columns using Table.AddColumns & Text.Combine
Split the new merged column into rows using the key value pair delimiter (",") using Table.ExpandListColumn, Table.TransformColumns, & Splitter.SplitTextByDelimiter
Split the new [KeyValuePairs] column into separate columns using the key value delimiter (":") using Table.SplitColumn
Create groups by [PageName], [CreatedBy], & [Key], while concatenating the [Value] column using Table.Group & Text.Combine
Pivot the table by [Key] for the [KeyValues] using Table.Pivot

Here is a the Power Query I constructed using this method:

let
    Source = #table({"PageName", "CreatedBy", "Primary", "Secondary"}, {{"page1", "Joe", "[owner:frank,topic:meals]", "[topic:drinks]"}, {"page2", "Dale", "[owner:joe,topic:drinks,topic:meals]", "[topic:appetizers]"}}),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PageName", type text}, {"CreatedBy", type text}, {"Primary", type text}, {"Secondary", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","[","",Replacer.ReplaceText,{"Primary", "Secondary"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","]","",Replacer.ReplaceText,{"Primary", "Secondary"}),
    #"Inserted Merged Column" = Table.AddColumn(#"Replaced Value1", "KeyValuePairs", each Text.Combine({[Primary], [Secondary]}, ","), type text),
    #"Removed Columns1" = Table.RemoveColumns(#"Inserted Merged Column",{"Primary", "Secondary"}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Removed Columns1", {{"KeyValuePairs", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "KeyValuePairs"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"KeyValuePairs", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "KeyValuePairs", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Key", "Value"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Key", type text}, {"Value", type text}}),
    //#"Grouped Rows" = Table.Group(#"Changed Type2", {"Index", "KeyValuePairs.1"}, {{"Values", each _, type table}})
    #"Grouped Rows" = Table.Group(#"Changed Type2", {"PageName", "CreatedBy", "Key"}, {{"KeyValues", each Text.Combine(_[Value], ", "), type text}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Key]), "Key", "KeyValues")
in
    #"Pivoted Column"


Replace NULL with empty string Due to precedence set within my organization, I need to replace NULL values in certain columns (roughly 15-20) with the empty string. While the solution comes quite easily in more traditional languages, I'm struggling with how to do this in T-SQL. Can anybody point me in the right direction? Much appreciated.

Not an expert SQL Server guy. However, Since Microsoft haven't made the same mistake as Oracle where the empty string and NULL are considered the same, this should work:

UPDATE TableName
SET My_String = ''
WHERE My_String IS NULL
AND Other_Conditions;


Return sample row if result set is empty What would be the most practical way to write a query, that returns single row in case of empty result set.

Something like (pseudosql)

(SELECT 1 AS c1, 2 AS c2 FROM ... WHERE ...) OR_ELSE(SELECT 0 AS c1, 0 AS c2)

You can use either generate_series function or VALUES Lists with a left join. The sample queries are below;

    CREATE TABLE my_table (a INT, b INT);
    INSERT INTO my_table VALUES (1,2), (2,2), (3,4);

    SELECT a, b FROM generate_series(1,1)
    LEFT JOIN my_table ON b = 5;

    |      a |      b |
    |--------|--------|
    | (null) | (null) |

    SELECT a, b FROM (values (1)) as one
    LEFT JOIN my_table ON b = 5;

    |      a |      b |
    |--------|--------|
    | (null) | (null) |

    SELECT a, b FROM generate_series(1,1)
    LEFT JOIN my_table ON b = 2;

    | a | b |
    |---|---|
    | 1 | 2 |
    | 2 | 2 |


MongoDB vs RDBMS DBA duties What are the differences between the daily tasks/duties of a Mongo DBA compared to a RDBMS DBA?

For example, some sites claim that a MongoDB DBA would not require to do data modelling or designing the database, as that would be done by a developer or application designer.

This would mean some  tasks are no longer needed to be done wrt MongoDB administrations, which was earlier being done by the RDBMS DBAs.

What other tasks would be required, that are not normally in the schedule of a RDBMS DBA?, and also the task which the RDBMS DBAs used to do but is no longer in the schedule of MongoDB DBAs ?

I'm new in MongoDB administration, so I'm trying to identify these task so that I may not commit the mistake of doing things in my daily task that are not needed or I miss something that I need to.

Can any experienced MongoDB DBAs help me out so that I don't do any foolish mistakes in my work ?

DBA is a small acronym but a large role. At various times I have seen a DBA look after

storage
network
VMs
compute nodes
installation and configuration of the above
backups, and testing restores
DR strategy
enterprise data integrity
ETL
data security
data modelling, normalisation and database design
data tier programing
performance tuning
operational availability
replication
failovers
on-call support

.. and others besides. I cannot see any of these disappearing just because Oracle/ SQL Server/ PostgreSQL is replaced by MongoDB/ Cassandra/ CouchDB.

"Oh," but you say, "There's no schema. I don't have to do that modelling & design thing."

"You're deluded," I would reply. If the system is for, say, order processsing (and not order processing and chocolate cake recipies and cataloging selfies etc.), and The Business distinguishes Orders by a 7-digit integer Order Number, and each Order consists of one or more Order Lines (referencing one Product each), then you have a schema. Just because it's enforced in the application layer and not in the DBMS does not make it disappear. And somebody had better be writting those rules down, and confirming them with the users, and communicating them to the test team, and teaching newly-hired employees what they are, and getting them into the end-user documentation, and ensuring old data can still be read by the just-changed application. Just because the rules can be changed in application code alone, rather than application code and DBMS script, does not make any of that stuff any less important.

So, for my two cents' worth, despite its many, many benefits I don't believe this class of software removes any significant task traditionally done by a DBA in the RDBMS world. It may redistribute them onto people holding other job titles, and the tasks may become easier to perform or less intrusive into the SDLC.

As for adding tasks, there is the issue of maintaining compatibility with old data written according to a previous understanding of the business rules. An RDBMS would not have this problem as there is only one acceptable schema at any point in time.


Incorrect datetime value for str_to_date I'm trying to copy a varchar column which contains dates in the format YYYY-MM-DD to a date column using str_to_date.

My query is:

UPDATE `subscribers` SET `srtdt` = STR_TO_DATE(`StartDate`, '%Y%m%d%');

I am getting error 1411: Incorrect datetime value: '2001-09-16'... This seems to be happening on the first row.

I have searched a lot for this but cannot find why this is happening - all other answers point towards the string being in the wrong format etc, and that doesn't seem to be the case with my problem.

Try it:

UPDATE subscribers SET srtdt = STR_TO_DATE(StartDate, '%Y-%m-%d');


Why do I have RID locks if I have a clustered index? I have configured extended_events to capture blocks on my database. Now I can see that I had blocks on one table that have been causing blocks problem. I can see that one UPDATE query can not go through and the detail says:

waitresource="RID: 9:1:6162688:0"

I understand why the UPDATE can not go through, it is because the query has to read the whole table and it blocks when it tries to read the RID. I am guessing that RID has a X lock.

My questions are:

How can I know which query is locking the RID ?
Why do I have locks at the RID level if I have a clustered index?

Use sp_WhoIsActive, a totally free script from MVP Adam Machanic:

sp_WhoIsActive @get_locks = 1

That adds an XML column of lock data for each running query. You can see which indexes, objects, databases, etc are locked, what kind of locks are held, and how many of them are held.


What is the timeout of a query in SQL Server 2008 I was running a query to delete in batches of a huge table. After say around 3 hrs sometimes 4 hrs I get a message saying

  Invalid object name '#table_name#'

After sometime is there a connection lost or something? What is the cause here?

To answer the specific question: by default there is no timeout while running a query against the database engine. It will happily wait or days for an operation to complete whether the delay is due to the actual statement taking a long time to process or due it being sat waiting for other connections to release relevant locks.

Standard connection libraries like ADO implement timeouts, after which they will tell SQL Server to cancel the current operation and drop the connection.

There are timeout settings governing various processes (login processes, or situations where the database engine itself is asking a remote resource to perform some action, and such) but these do not affect queries given direct to the database engine.

For more specific help you'll need to add further details (the code of your query, the exact error message, perhaps some notes on your structure (is "#tablename# the name of an actual table in your DB?) so we can see if there is something there that would cause the error you have seen.


How do I determine what object two SQL process are contending over? When long running Query#1 exclusively locks an object (table, index or page), and then Query#2 is executed by another user and becomes blocked on the lock held by Query:1, how would I get the name of the object which the two queries are contending over?

In management studio you can right click the server node and select Reports -> Standard Reports -> Activity - all Blocking Transactions to see blocking currently ongoing.

The query that report runs can be traced through Profiler and is pasted below.

declare @tab_tran_locks as table(
        l_resource_type nvarchar(60) collate database_default
,       l_resource_subtype nvarchar(60) collate database_default
,       l_resource_associated_entity_id bigint
,       l_blocking_request_spid int
,       l_blocked_request_spid int
,       l_blocking_request_mode nvarchar(60) collate database_default
,       l_blocked_request_mode nvarchar(60) collate database_default
,       l_blocking_tran_id bigint
,       l_blocked_tran_id bigint
);
declare @tab_blocked_tran as table (
        tran_id bigint
,       no_blocked bigint
);
declare @temp_tab table(
        blocking_status int
,       no_blocked int
,       l_resource_type nvarchar(60) collate database_default
,       l_resource_subtype nvarchar(60) collate database_default
,       l_resource_associated_entity_id bigint
,       l_blocking_request_spid int
,       l_blocked_request_spid int
,       l_blocking_request_mode nvarchar(60) collate database_default
,       l_blocked_request_mode nvarchar(60) collate database_default
,       l_blocking_tran_id int
,       l_blocked_tran_id int
,       local1 int
,       local2 int
,       b_tran_id bigint
,       w_tran_id bigint
,       b_name nvarchar(128) collate database_default
,       w_name nvarchar(128) collate database_default
,       b_tran_begin_time datetime
,       w_tran_begin_time datetime
,       b_state nvarchar(60) collate database_default
,       w_state nvarchar(60) collate database_default
,       b_trans_type nvarchar(60) collate database_default
,       w_trans_type nvarchar(60) collate database_default
,       b_text nvarchar(max) collate database_default
,       w_text nvarchar(max) collate database_default
,       db_span_count1 int
,       db_span_count2 int
);

insert into @tab_tran_locks
select  a.resource_type
,               a.resource_subtype
,               a.resource_associated_entity_id
,               a.request_session_id as blocking
,               b.request_session_id as blocked
,               a.request_mode
,               b.request_mode
,               a.request_owner_id
,               b.request_owner_id
from sys.dm_tran_locks a
join sys.dm_tran_locks b on (a.resource_type = b.resource_type and a.resource_subtype = b.resource_subtype and a.resource_associated_entity_id = b.resource_associated_entity_id and a.resource_description = b.resource_description)
where a.request_status = 'GRANT' and (b.request_status = 'WAIT' or b.request_status = 'CONVERT') and a.request_owner_type = 'TRANSACTION' and b.request_owner_type = 'TRANSACTION'

insert into @tab_blocked_tran
select  ttl.l_blocking_tran_id
,       count(distinct ttl.l_blocked_tran_id)
from @tab_tran_locks ttl
group by ttl.l_blocking_tran_id
order by count( distinct ttl.l_blocked_tran_id) desc

insert into @temp_tab
select  0 as blocking_status
,       tbt.no_blocked
,       ttl.*
,       st1.is_local as local1
,       st2.is_local as local2
,       st1.transaction_id as b_tran_id
,       ttl.l_blocked_tran_id as w_tran_id
,       at1.name as b_name,at2.name as w_name
,       at1.transaction_begin_time as b_tran_begin_time
,       at2.transaction_begin_time as w_tran_begin_time
,       case when at1.transaction_type <> 4
                then case at1.transaction_state
                        when 0 then 'Invalid'
                        when 1 then 'Initialized'
                        when 2 then 'Active'
                        when 3 then 'Ended'
                        when 4 then 'Commit Started'
                        when 5 then 'Prepared'
                        when 6 then 'Committed'
                        when 7 then 'Rolling Back'
                        when 8 then 'Rolled Back'
                end
                else case at1.dtc_state
                        when 1 then 'Active'
                        when 2 then 'Prepared'
                        when 3 then 'Committed'
                        when 4 then 'Aborted'
                        when 5 then 'Recovered'
        end end b_state
,       case when at2.transaction_type <> 4
                then case at2.transaction_state
                        when 0 then 'Invalid'
                        when 1 then 'Initialized'
                        when 2 then 'Active'
                        when 3 then 'Ended'
                        when 4 then 'Commit Started'
                        when 5 then 'Prepared'
                        when 6 then 'Committed'
                        when 7 then 'Rolling Back'
                        when 8 then 'Rolled Back'
                end
                else case at2.dtc_state
                        when 1 then 'Active'
                        when 2 then 'Prepared'
                        when 3 then 'Committed'
                        when 4 then 'Aborted'
                        when 5 then 'Recovered'
                end
        end w_state
,       at1.transaction_type as b_trans_type
,               at2.transaction_type  as w_trans_type
,       case when r1.sql_handle IS NULL then '--' else ( select top 1 substring(text,(r1.statement_start_offset+2)/2, (case when r1.statement_end_offset = -1   then (len(convert(nvarchar(MAX),text))*2) else r1.statement_end_offset  end - r1.statement_start_offset) /2  ) from sys.dm_exec_sql_text(r1.sql_handle)) end as b_text
,       case when r2.sql_handle IS NULL then '--' else ( select top 1 substring(text,(r2.statement_start_offset+2)/2, (case when r2.statement_end_offset =-1 then len(convert(nvarchar(MAX),text))*2  when r2.statement_end_offset =0  then len(convert(nvarchar(MAX),text))*2  else r2.statement_end_offset  end - r2.statement_start_offset) /2  ) from sys.dm_exec_sql_text(r2.sql_handle)) end as w_text
,       ( Select count(distinct database_id) from sys.dm_tran_database_transactions where transaction_id = st1.transaction_id ) as db_span_count1
,       ( Select count(distinct database_id) from sys.dm_tran_database_transactions where transaction_id = st2.transaction_id ) as db_span_count2
from @tab_tran_locks ttl
inner join sys.dm_tran_active_transactions at1 on(at1.transaction_id = ttl.l_blocking_tran_id)
inner join @tab_blocked_tran tbt on(tbt.tran_id = at1.transaction_id)
inner join sys.dm_tran_session_transactions st1 on(at1.transaction_id = st1.transaction_id)
left outer join sys.dm_exec_requests r1 on(at1.transaction_id = r1.transaction_id )
inner join sys.dm_tran_active_transactions at2 on(at2.transaction_id = ttl.l_blocked_tran_id)
left outer join sys.dm_tran_session_transactions st2  on(at2.transaction_id = st2.transaction_id)
left outer join  sys.dm_exec_requests r2 on(at2.transaction_id = r2.transaction_id )
where st1.is_user_transaction = 1
order by tbt.no_blocked desc;

with Blocking(
        blocking_status
,       no_blocked
,       total_blocked
,       l_resource_type
,       l_resource_subtype
,       l_resource_associated_entity_id
,       l_blocking_request_spid
,       l_blocked_request_spid
,       l_blocking_request_mode
,       l_blocked_request_mode
,       local1
,       local2
,       b_tran_id
,       w_tran_id
,       b_name
,       w_name
,       b_tran_begin_time
,       w_tran_begin_time
,       b_state
,       w_state
,       b_trans_type
,       w_trans_type
,       b_text
,       w_text
,       db_span_count1
,       db_span_count2
,       lvl
)
as( select blocking_status
        ,       no_blocked
        ,       no_blocked
        ,       l_resource_type
        ,       l_resource_subtype
        ,       l_resource_associated_entity_id
        ,       l_blocking_request_spid
        ,       l_blocked_request_spid
        ,       l_blocking_request_mode
        ,       l_blocked_request_mode
        ,       local1
        ,       local2
        ,       b_tran_id
        ,       w_tran_id
        ,       b_name
        ,       w_name
        ,       b_tran_begin_time
        ,       w_tran_begin_time
        ,       b_state
        ,       w_state
        ,       b_trans_type
        ,       w_trans_type
        ,       b_text
        ,       w_text
        ,       db_span_count1
        ,       db_span_count2
        ,       0
        from @temp_tab
   union all
        select E.blocking_status
        ,       M.no_blocked
        ,       convert(int,E.no_blocked + total_blocked)
        ,       E.l_resource_type
        ,       E.l_resource_subtype
        ,       E.l_resource_associated_entity_id
        ,       M.l_blocking_request_spid
        ,       E.l_blocked_request_spid
        ,       M.l_blocking_request_mode
        ,       E.l_blocked_request_mode
        ,       M.local1
        ,       E.local2
        ,       M.b_tran_id
        ,       E.w_tran_id
        ,       M.b_name
        ,       E.w_name
        ,       M.b_tran_begin_time
        ,       E.w_tran_begin_time
        ,       M.b_state
        ,       E.w_state
        ,       M.b_trans_type
        ,       E.w_trans_type
        ,       M.b_text
        ,       E.w_text
        ,       M.db_span_count1
        ,       E.db_span_count2
        ,       M.lvl+1
        from @temp_tab as E
    join Blocking as M on E.b_tran_id = M.w_tran_id
)

select (dense_rank() over (order by no_blocked desc,b_tran_id))%2 as l1
,       (dense_rank() over (order by no_blocked desc,b_tran_id,w_tran_id))%2 as l2
,       *
from Blocking
order by no_blocked desc,b_tran_id,w_tran_id ;


How to remove string quotes in MySQL 5.7 for function JSON_EXTRACT? Other than doing string manipulation after each JSON_EXTRACT, is there a simpler or more correct way to have the JSON_EXTRACT return the string WITHOUT the enclosing quotes?
Or should this question be on StachExchange?

If you have MySQL 5.7.13 or later, you may use JSON_UNQUOTE() instead of JSON_EXTRACT() or ->> instead of ->. Example:

SELECT field->>"$.foo.barr" FROM table;


Is it possible to set initial index number in a table? My System database has got a very large amount of data and some tables are really getting very big. I was looking for a way such as when the system will query on those large tables, it will ignore the historical data and will start looking from the index that I will set. Something like I have 20000 rows but I will set 15000 index as the starting point, so the query will think 15001 is the first index and will start operation.
How can I achieve this? I do not want to delete the historical data from my tables.

In most circumstances with optimal queries and good index choices this should not be necessary at all. It would only make a difference when the database engine is scanning the whole table (or in the cases where LIMIT is in effect, scanning the table until  matches are found) and you should be trying to eliminate this sort of query plan anyway.

Indexes are usually implemented as tree structures, usually some variety of b-tree. A lookup for a single value (so a single row) in a binary tree has a complexity of the order of Log2 N so for 1,000,000 rows you can expect "up to 20" page reads (and most of those pages should already be in RAM so that isn't disk reads/seeks). For 1,000,000,000 rows in the table this only grows to "up to 30" page reads. This effect is massive compared to the effect of just ignoring the old rows in your table. The matter is the same for range queries: the initial seek on the index is the same, and from there the reading is sequential with respect to the tree structure anyway.

So instead of trying to ignore old data artificially, instead have good index and query design and it will not be an issue.

Obviously this is a generalism, there obviously are some queries that simply have to scan large chunks of the table, even all of it, that can't be further optimised, and no end of indexing will help the query planner, but these are genuinely rare. Some database systems have features for dealing with this sort of issue over massive tables: for instance MS SQL Server, postgres, and others, support filtered indexes (called partial indexes in postgres' terminology) which can exactly implement what you are asking for in your question, but that is probably not what you actually need at this point. You might also look at data partitioning but again I suspect that is not what you need, partitioning is more about managing the storage of large data than about searching it more effectively.


Listener (lsnrctl) reload vs stop/start Some blogs tell that reload is equal stop and start, but in my test it does not stop the listener (uptime does not change).

What situation can I use only reload and when I must stop/start the listener? Is stop/start for just validate the applied configuration for all current connections? (force the all connections to reconnect in listener)?

reloading your listener will allow listener to take new changes (configuration) without stopping it, and it will un-register\register dynamically registered DBs.

stop will stop listener completely by disallowing new session to connect to the database remotely.

start will start listener and this will allow users to continue working on the database.

for me I usually use listener reload for production to make sure I dont have any downtime during working hours. stop\start incase we need to perform some maintenance


Is there a reason my CTE with a basic subquery in the second stage of the CTE is failing? Ok... anyone aid with this sorry...i've been playing with some of my works reports and i thought id found a way to mix two results into one when i came across a small issue...

if i run my CTE code below without the lower subquery it runs straight away...

WITH CTE_RealAudit (UserName,WorkstationName,LoginDate,LoginTime,"Days","Hours","Minutes","Seconds")
AS
(
SELECT UserName AS 'User',WorkstationName AS 'Workstation',
RIGHT('0'+CONVERT(varchar(2),DATEPART(dd, DATEADD(ss, StartTime + DATEDIFF(ss, GETUTCDATE(), GETDATE()),'19700101'))),2)+'/'+RIGHT('0'+CONVERT(varchar(2),DATEPART(m,dateadd(ss, StartTime + DATEDIFF(ss, GETUTCDATE(), GETDATE()),'19700101'))),2)+'/'+CONVERT(varchar(4),DATEPART(yy,dateadd(ss, StartTime + DATEDIFF(ss, GETUTCDATE(), GETDATE()),'19700101'))) AS 'Login Date',
RIGHT('0'+CONVERT(varchar(2),DATEPART(hh,DATEADD(ss, StartTime + DATEDIFF(ss, GETUTCDATE(), GETDATE()),'19700101'))),2)+':'+RIGHT('0'+CONVERT(varchar(2),DATEPART(mi,dateadd(ss, StartTime + DATEDIFF(ss, GETUTCDATE(), GETDATE()),'19700101'))),2)+':'+RIGHT('0'+CONVERT(varchar(2),DATEPART(ss,dateadd(ss, StartTime + DATEDIFF(ss, GETUTCDATE(), GETDATE()),'19700101'))),2) AS "Login Time",
CONVERT(varchar(2), SUM(EndTime-StartTime)/(3600*24)) AS 'Days',
CONVERT(varchar(2), SUM(EndTime-StartTime)/3600) AS 'Hours',
CONVERT(varchar(2), (SUM(EndTime-StartTime) % 3600) / 60) AS 'Minutes',
CONVERT(varchar(2), (SUM(EndTime-StartTime) % 60 )) AS 'Seconds'
    FROM IMuse.vAuditUsageAndNames
        WHERE StartTime BETWEEN DATEDIFF(ss,'19700101',DATEADD(dd,-3000,CONVERT(date,SYSDATETIME())))
        AND DATEDIFF(ss,'19700101',CONVERT(date,SYSDATETIME()))
        AND LEFT(UserName,2) <> N'P-'
        AND LEFT(UserName,3) <> N'EP-'
        AND EndTime <> StartTime
    GROUP BY UserName,WorkstationName,StartTime
)

SELECT * FROM CTE_RealAudit
    WHERE "Hours" <> N'0'
    AND "Minutes" <> N'0

but if i add a subquery to the last select such as

SELECT * FROM CTE_RealAudit
    WHERE "Hours" <> N'0'
    AND "Minutes" IN
    (select Minutes from cte_realaudit)

or my goal

SELECT * FROM CTE_RealAudit
    WHERE "Hours" <> N'0'
    AND "Minutes" IN
    (SELECT "Minutes" from CTE_RealAudit
        where "Hours" >= N'0'
        and "Minutes" >= N'0')

it completely freaks out and takes 20+ minutes? anyone aid in why this is occurring?

newbie here so sorry if im doing something totally stupid.

thanks

Let's pretend for a moment that CTE_RealAudit is a normal table...

You're querying it twice! Once to get the list of Minutes values, and to get all the other fields for those rows that have a Minutes value (oh, and surely it always will have, unless you have NULLs) in the list. So yes, that's going to take a lot longer than querying it once.

So that's why it's taking much longer. But I don't think you need to query it twice at all - have a good think about the logic you actually want, especially as it's not a normal table, but rather a complex subquery over a view.


How to get Feature Wise Count Per User? Table_1
Name   |   Feature
A      |    F1
A      |    F1
A      |    F1
A      |    F2
A      |    F3
B      |    F1
B      |    F1
C      |    F3

Desired Result
Name |   F1  |  F2  |   F3
A    |   3   |  1   |    1
B    |   2   |  0   |    0
B    |   0   |  0   |    1

I tried below query

SELECT Name,Feature,COUNT(Feature) FeatureCount FROM Table_1 GROUP BY Feature,Name

But it is not giving as Desired
Instead it is giving

Name | Feature | FeatureCount
A    |   F1    |     3
A    |   F2    |     1
A    |   F3    |     1
B    |   F1    |     2
C    |   F3    |     1

Confuse on How to get Feature Wise Count Per User

Here is a solution using dynamic SQL

--Demo setup
DROP TABLE IF EXISTS Table1;

CREATE TABLE Table1
    ([Name] varchar(1), [Feature] varchar(2))
;

INSERT INTO Table1
    ([Name], [Feature])
VALUES
    ('A', 'F1'),
    ('A', 'F1'),
    ('A', 'F1'),
    ('A', 'F2'),
    ('A', 'F3'),
    ('B', 'F1'),
    ('B', 'F1'),
    ('C', 'F3')
;

--Solution using dynamic SQL
Declare @sql nvarchar(4000)
Declare @col varchar(max)
Declare @colCoalesceNull varchar(max)
SELECT @col =
        Coalesce(@col + ', ', '') + QUOTENAME([Feature])
    ,@colCoalesceNull =
        Coalesce(@colCoalesceNull + ', ', '') + 'coalesce(' + QUOTENAME([Feature]) + ',0) as ' + QUOTENAME([Feature])
FROM (
    SELECT DISTINCT
        [Feature] AS [Feature]
    FROM Table1
    ) AS Table1;

PRINT @col
PRINT @colcoalescenull

SET @sql = N'
with _cte as
(
SELECT Name,Feature,COUNT(Feature) FeatureCount FROM Table1 GROUP BY Feature,Name
)
SELECT Name, ' + @colCoalesceNull + ' FROM _cte
    PIVOT(SUM(FeatureCount)
    FOR Feature IN (' + @col + ')) AS PivotData';

print @sql
EXEC sp_executesql @sql;

| Name | F1 | F2 | F3 |
|------|----|----|----|
| A    | 3  | 1  | 1  |
| B    | 2  | 0  | 0  |
| C    | 0  | 0  | 1  |


IndexOptimize - Configuration We recently switched to Ola Hallengren's maintenance script and automated the deployment of MaintenanceSolution.sql to our customers's SQL Server instance.

We need to set these parameters for the job IndexOptimize - USER_DATABASES:

@UpdateStatistics = 'ALL'
@OnlyModifiedStatistics = 'Y'

I see that these parameters are present in MaintenanceSolution.sql:

After I change the values of the above-mentioned parameters in MaintenanceSolution.sql to

@UpdateStatistics nvarchar(max) = 'ALL'

@OnlyModifiedStatistics nvarchar(max) = 'Y'

then execute, I don't see either @UpdateStatistics = 'ALL' or @OnlyModifiedStatistics = 'Y' added in Job Step properties - IndexOptimize - USER_DATABASES:

My questions are:

Why don't "statistics" options appear in the job's command?
Is it wrong to edit MaintenanceSolution.sql directly for this?
Is there a way to add theses parameters to the job using a query?

Why don't "statistics" options appear in the job's command?

Because you updated the default parameter in the procedure call. This means that if you call the procedure without these parameters, E.G.

EXECUTE dbo.IndexOptimize
 @Databases ='USER_DATABASES',
 @LogToTable='Y'

that these default parameters  @UpdateStatistics = 'ALL' &  @OnlyModifiedStatistics = 'Y' will be used.

It is by design that these job steps are not changed.

  Is it wrong to edit MaintenanceSolution.sql directly for this?

Depends on your requirements.

By default no statistics will be updated when calling the procedure without the parameters specified.

If you change these @OnlyModifiedStatistics & @UpdateStatistics parameters in the procedure, all modified statistics will be updated when calling the procedure without these parameters added.

  Is there a way to add theses parameters to the job using a query?

Sure, change the jobstep to this:

 EXECUTE dbo.IndexOptimize
 @Databases ='USER_DATABASES',
 @LogToTable='Y',
 @UpdateStatistics = 'ALL',
 @OnlyModifiedStatistics = 'Y';

EDIT

  So even if i don't see "@UpdateStatistics = 'ALL'" &
  "@OnlyModifiedStatistics = 'Y'" in the jobstep and that i have updated
  it as the default parameters in the MaintenanceSolution.sql ...
  Parameters will still be used ? –

Sure, executing below procedure with the default NULL parameter for the @UpdateStatistics parameter runs instantly

EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL
--,@UpdateStatistics = 'ALL'

Date and time start

Date and time: 2019-08-21 14:49:22

Date and time end:

Date and time: 2019-08-21 14:49:22

No stat updates statements found.

This statement will do no index optimization and no update stats if no default parameters are changed on the procedure itself.

Changing the @UpdateStatistics parameter in the procedure and rerunning it, on a small database

It takes too long on all user databases ;)

@UpdateStatistics nvarchar(max) = 'ALL',

Rerunning the procedure on one small database

Date and time start:

Date and time: 2019-08-21 14:50:16

Date and time end:

Date and time: 2019-08-21 14:50:23

One of the statements in the messages tab:

 UPDATE STATISTICS [Database].[dbo].[test] [UIX_dbo_test_RecordType]


Catching slow queries with prepared statements I use extended events to identify slow queries, defined as queries taking more than 2 seconds to complete, like this:

CREATE EVENT SESSION SlowQueries ON SERVER
ADD EVENT sqlserver.sql_statement_completed
    (
    ACTION
        (
        sqlserver.client_hostname,
        sqlserver.sql_text,
        sqlserver.database_id,
        sqlserver.tsql_stack,
        sqlserver.username
        )
    WHERE
        (
        duration > 2000000
        )
    )
ADD TARGET package0.asynchronous_file_target
    (
    SET filename='D:\SlowQueries.xel'
    )
WITH
    (
    MAX_MEMORY=256 KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=30 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=OFF,
    STARTUP_STATE=ON
    );

However, I noticed from the SQL Server Profiles that a PHP/Laravel application is not sending statements directly, but using RPC:Completed: they look like prepared statements, receiving the argument at run time. How do I expand my SlowQueries to include statements executed via RPC:Completed?
I am 100% sure that slow queries are happening, but not reported by my event.

It's In There

Extended events has the rpc completed class:

SELECT * FROM sys.dm_xe_objects AS dxo WHERE dxo.name = 'rpc_completed';

If you need to show the parameters used, you may need to try one of these events:

CREATE EVENT SESSION what_do_i_get
    ON SERVER
    ADD EVENT sqlserver.module_end
    ( SET collect_statement = ( 1 )
WHERE ( sqlserver.session_id = ( 55 ))),
    ADD EVENT sqlserver.rpc_completed
    ( WHERE ( sqlserver.session_id = ( 55 ))),
    ADD EVENT sqlserver.sp_statement_completed
    ( WHERE ( sqlserver.session_id = ( 55 ))),
    ADD EVENT sqlserver.sql_batch_completed
    ( WHERE ( sqlserver.session_id = ( 55 ))),
    ADD EVENT sqlserver.sql_statement_completed
    ( WHERE ( sqlserver.session_id = ( 55 )))
    ADD TARGET package0.event_file
    ( SET filename = N'what_do_i_get' )
    WITH ( MAX_MEMORY = 4096KB,
           EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
           MAX_DISPATCH_LATENCY = 30 SECONDS,
           MAX_EVENT_SIZE = 0KB,
           MEMORY_PARTITION_MODE = NONE,
           TRACK_CAUSALITY = ON,
           STARTUP_STATE = OFF );
GO

The sql_text column won't help you though, you'll wanna use the statement column I believe, depending on how the statements are executed.

As a side note, you may want to be careful if you're using sp_prepare.

See my post here:

Why sp_prepare Isn’t as “Good” as sp_executesql for Performance


Is there a way to get a count about grouped rows? I am trying to get a count about a grouped rows. Here is a description of the table of relevance:

column_name     data_type
-------------   ---------
id              integer
title           character varying
release_year    integer
country         character varying
duration        integer
language        character varying
certification   character varying
gross           bigint
budget          bigint

I am to trying to determine how many different years exist during which more than 200 movies were released.

Here is the query I have tried so far:

SELECT count(DISTINCT release_year)
FROM films
GROUP BY release_year
HAVING COUNT(title) > 200;

...which gives 13 rows of 1s.

The question is: how to get a number 13?

You GROUP BY release_year but you count release years so you will get 1s, nothing else. If you want to count them, you can enclose in a derived table and do another aggregation:

SELECT
    COUNT(*) AS release_years_with_200_titles
FROM
  (
    SELECT 1
    FROM films
    GROUP BY release_year
    HAVING COUNT(title) > 200
  ) AS dt ;

It can be done without a derived table but it's not very clear how it works:

SELECT
  COUNT(*) OVER () AS release_years_with_200_titles
FROM films
GROUP BY release_year
HAVING COUNT(title) > 200
LIMIT 1 ;

The COUNT(*) OVER () will count the number of rows (after the GROUP BY and HAVING). We then can use SELECT DISTINCT or even better LIMIT 1 to get a single 13 in the result and not thirteen rows with 13.


Importing dbatools module into Azure Automation Account Has anyone managed to import dbatools module into Azure Automation Account? I tried to do it in several ways, but every time the installation fails:

different versions checked from 0.9.* to the latest 1.0.9
from portal using Automation Accounts -> Automation modules -> Browse Gallery
from portal using Automation Accounts -> Automation modules -> import from file

Every time I got:

  Error importing the module dbatools. Import failed with the following
  error: Orchestrator.Shared.AsyncModuleImport.ModuleImportException: An
  error occurred during module validation. When importing the module to
  an internal PowerShell session, it was not able to be loaded by
  PowerShell. There is likely an issue with the contents of the module
  that results in PowerShell's not being able to load it. Please verify
  that the module imports successfully in a local PowerShell session,
  correct any issues, and then try importing again.

As you can see the error message doesn't tell much (and obviously I am able to import it locally), thus I wonder if anyone managed to go over it somehow.

The issue with importing dbatools into Azure Automation Accounts has been resolved as of release 1.0.66...

https://twitter.com/wsmelton/status/1191370349390303234


MySQL Plugin failed to install due to "undefined symbol" For several years now I have used the MariaDB Audit Plugin on my MySQL databases.

I am now looking to start Upgrading my databases to MySQL 8, and so have just tried installing it onto MySQL 8.0.19, but am getting the following error:

Error Code: 1126. Can't open shared library '/usr/lib/mysql/plugin/server_audit.so' (errno: 2 /usr/lib/mysql/plugin/server_audit.so: undefined symbol: fn_format)

I tried downloading the latest version (audit 1.4.7 from the MariaDB 10.4.7 binaries) but it made no difference.
Downloading older versions of the plugin also made no difference.

Is this likely to be a MySQL problem or a Mariadb problem? And is there a way to get round this. Most of my databases are installed on Debian 9 or 10 servers.

It's not really an answer, but I found a workaround, which is to use the Percona Audit Plugin instead (which I used to use anyway, but stopped working when I moved from 5.5 to to 5.7, but strangely now works again with 8.0). In my case I already have the support code and infrastructure to switch back to this so it's not a big task.


Login Error while connecting SQL Database through Power BI? I get the error message displayed in Power BI Online when using an enterprise gateway connected to an on-premise SQL Server

Unable to connect: We encountered an error while trying to connect.

  Details: Microsoft SQL: A connection was successfully established with
  the server, but then an error occurred during the login process.
  (provider: ssl provider, error: 0 - The signature of the certificate
  cannot be verified)

I have the Login credentials (SysAdmin) on server.

What is the cause of getting Login error? Any suggestions and resolution steps?

I found resolution for this error:

By default, the Encrypt connection check box is selected to signify that Power BI Desktop connects to database using an encrypted connection. If a connection to your SQL Server is not established using an encrypted connection, that Power BI Desktop prompts to connect using an unencrypted connection.

Once you click OK, it will be directly prompted to turn off encryption. Or you can go to

  File->Options and Settings -> Data Source Settings, choose your SQL
  Server data source and select Edit and uncheck "Encrypt Connections"
  option.

In addition, when we connect to SQL Server in Power BI Desktop, need to select the authentication mode to connect to the SQL Server database.

1) Windows: This is the default selection. Select this if you want to connect using Windows authentication.

2) Database: Select this if you want to connect using SQL Server authentication. After you select this, specify a user name and password to connect to your SQL Server instance.

Note: I know it's foolish and so simple but works for me anyway @sepupic Thanks for useful comment.


First Response Kit is missing sp_AskBrent I just downloaded the First Responder kit. Unfortunately, the 'sp_AskBrent' was not included in the download.  How can I get it to complete the kit?

sp_AskBrent was renamed to sp_BlitzFirst.

https://www.brentozar.com/archive/2016/06/first-responder-kit-updated-sp_askbrent-now-sp_blitzfirst/


Any possible way to get a progress estimate for ALTER COLUMN TYPE in Postgres? I know that there is currently no officially supported way, but I'm nearly two hours in of 100% CPU on a 15,000,000 row table changing an INTEGER to a SMALLINT and I have absolutely no idea how long this will keep going on for. Is there maybe some file that I can watch growing on the filesystem? Does it rewrite the table in place, or create a new file? Is there a way to pause this so that I can run a benchmark on the same machine with fewer rows, so I can get an estimate of rows per second for this operation?

PostgreSQL rewrites the table:

postgres=# \d t;
                   Table "public.t"
 Column |            Type             |   Modifiers
--------+-----------------------------+---------------
 x      | integer                     |
 y      | text                        |
 z      | timestamp without time zone | default now()
Indexes:
    "idx_only_scan" btree (x, y, z)
postgres=# select relfilenode, relname from pg_class where relname='t';
 relfilenode | relname
-------------+---------
       16760 | t
(1 row)

postgres=# alter table t alter column x set data type smallint;
ALTER TABLE
postgres=# select relfilenode, relname from pg_class where relname='t';
 relfilenode | relname
-------------+---------
       16792 | t
(1 row)

There should be new database files in $PGDATA/base/"database_oid"  but I don't know if it is possible to get the new relation oid, to monitor the rewrite or to pause it:

$ ls -rtl  $PGDATA/base/13297/16792*
-rw-------. 1 postgres postgres 24576 Apr  9 08:02 /var/lib/pgsql/95/data/base/13297/16792_fsm
-rw-------. 1 postgres postgres  8192 Apr  9 08:02 /var/lib/pgsql/95/data/base/13297/16792_vm
-rw-------. 1 postgres postgres  8192 Apr  9 08:02 /var/lib/pgsql/95/data/base/13297/16792


Problem structuring database for an assessment rubric app Hello everyone, I'm really troubled about how to design a database and I could use some help. I've done my research and found nothing about it.

I'm trying to build a simple app for my class that creates academic assessment rubrics so they are available to use them on my students and finally get grades.

I thought of using mysql for my database. The student part is done. But I can not think of a way to make the rubrics thing work.

I need to be able to create rubrics and inside that rubric create criterias, sub-criterias, sub-sub-criterias, levels and points. Here's and example of a rubric.

Could anyone give me some suggestion about how to model this database or any idea about it? Is a relational database a good approach for this problem? I'm kinda burnt with this and would appreciate
any recommendation.

Sorry for my English, not my native.
Regards.

So you'll have students with an primary id student_id. Assume courses have a primary id course_id.

So each course will have its own assessment as per the first column in the rubric.

 create table courses_info (
   course_id int unsigned not null,
   assessment_id int unsigned not null,
   assessment varchar(80) not null,
   primary key (course_id, assessment_id)
 )

Student assessments take student_id, course_id, assessment_id and store an assessment_result (assumed to be uniform over the entire institution):

 create table student_assessment (
   course_id int unsigned not null,
   assessment_id int unsigned not null,
   student_id int unsigned not null,
   assessment_result enum('Emergent',..., 'Excellent'),
   primary key (course_id, assessment_id, student_id)
 )

The rubric is the mapping between an assessment_id for each course_id with the learning_outcome being the element in the lookup table:

 create table rubric (
   course_id int unsigned not null,
   assessment_id int unsigned not null,
   assessment_result enum('Emergent',..., 'Excellent'),
   learning_outcomes text,
   primary key (course_id, assessment_id, assessment_result)
 )

To get the results you query and JOIN on the required columns:


How to call from a shell program a postgresql COPY FROM FILE command, passing the path of the FILE as an argument? I am trying to run, via a linux shell script, a Postgresql command that reads a CSV and updates a table with it.
The difficulty I am facing, is that this shell script has to pass an argument to the SQL program, argument that contains the path where the CSV is stored.
I just can't make it...
The PSQL command in my shell script is:
PSQL -f ${UNXSQLPATH}/new_nomenclature.sql - v v1=${UNXDATAPATH}

And the new_nomenclature.sql program contains the following line:
COPY nomenclature FROM :v1||'new_nomenclature.csv' CSV DELIMITER ';' HEADER ENCODING 'LATIN9';

Note: when I hardcopy the path in the SQL program, everything works fine.

Utility commands like COPY and many others do not accept expressions where literals are expected, so the file name must be given verbatim.
You may use \set in psql to build the filename by concatenating a path with a name, as in:
$ cat script.sql
\set path :v1 '/new_nomenclature.csv'
COPY nomenclature FROM :'path' CSV ....

$ psql -f script.sql -v v1="/tmp"

When \set is not suitable, another way is to call SELECT to build the string, get the result into a psql variable, and feed it back to COPY (this might come handy if building the path requires more than a mere concatenation)
$ cat script.sql
select :'v1' || '/new_nomenclature.csv' AS path \gset
COPY nomenclature FROM :'path' CSV ....


When should extended events be used instead of SQL Profiler/perfmon? The extended events seem like a better technology and less stress on the server, but the SQL Profiler/perfmon has better tooling.  Also the extended events seem to have a steeper learning curve.  In which context should each be used?  Is it worth getting past the steep learning curve to take advantage of the extended events?

Is it worth getting past the steep learning curve to take advantage of the extended events?

Definitely yes. Extended Events is a new platform with better performance, scalability than SQL Trace and the functionality that is in SQL Server 2008 is kind of limited when compared to SQL Trace and also there is some learning curve. Rest assured, Extended Events is getting a major over haul in SQL Server DENALI and there will be lot of options & use cases available in Extended Events that won't be in SQL Trace. Extended Events is the future of monitoring & troubleshooting going forward and some day in the future SQL Trace will be removed.

Note that MSFT won't deprecate a feature right away when a new platform is introduced. Extended Events is in Version 1 (SQL Server 2008) and there are lot of things that need to be improved, modified before removing SQL Trace which was there in the product for a long time.

To get over the learning curve, practice examples from blog posts written by Jonathan Kehayias and few of mine too (shameless plug).

http://www.sqlskills.com/blogs/jonathan/category/Extended-Events.aspx

http://sankarreddy.com/tag/extended-events/

To Sandy:
a
I don't think Michael has asked too many different questions on various topics in this thread. Yes there are many questions but they are on the same topic. I don't think he should open up several threads to split this one.


DB Permission: User can drop only their created db's? I want to set permission against a user, which can create database and drop on their created databases neither any database. Any idea/suggestion ?

to grant use privliges to create database you can use the following command:

use [master]
GO
GRANT CREATE ANY DATABASE TO [LoginName]
GO

To drop it you need to make sure the user has owner privileges on the database, you can use the command ALTER AUTHORIZATION ON DATABASE::db TO [LoginName];


Copy+Pasting currency value from SSMS 2012 to excel does not complete or calculate new value So I'm trying to copy and paste some query results into Excel, and for some reason it seems to be not properly completing the first value.

Let me explain what I mean by this.

In Excel the cells I am copying into are formatted as Currency. Normally when you copy in value(s) or type it in and then press enter, it formats it properly with commas, dollar signs, decimals, etc, and then any formulas that use this cell are recalculated instantly.

However, when I copy in a set of values, the very first one does not get formatted, and it does not get calculated in a formula that uses that cell. The next 4 values work properly though, and if I try doing just one value at a time the problem still occurs.

At first I tried casting and converting the values to different SQL types like decimal, money, float, as I thought it was an issue with that, but it makes no difference.

In my actual query these values are SUMs of decimal fields, but here is an example query that does the same thing:

SELECT 123456.78, 9012.34, 567890.12, 345678.90, 123456.78;

Does anyone know what is causing this?

I'm pretty sure this is something to do with SSMS 2012 and not Excel, as it never happened to me in 2008. Not to mention there was another copy and paste issue I ran into earlier with SSMS 2012 involving carriage returns and line feeds that caused new rows when pasted into Excel that never happened in 2008.

Thanks

If I do a Paste Special, I get two options, Text and Unicode, and selecting Text seems to work properly, where as Unicode does not.
as per comment by @JonSeigel


How pt-online-schema-change ensures consistency during data copy I have read pt-online-schema-change documentation and understood that it works by creating triggers and copying data in chunks with in chunk-time seconds(0.5 seconds in default)

Let us consider this below case.

    We have a table TBL1 of 5 GB and is being altered with PT-ONLINE-SCHEMA-CHANGE

  PT-ONLINE-SCHEMA-CHANGE created new table with the desired change, added trigggers on
     TBL1 and started copying.

    copied 10 chunks of data and started copying 11th chunk which is some 'X' MB in size.
       As per tool, this chunk be copied in a max of 500 milliseconds or 0.5 seconds

  During the 11th chunk copying, application issued an UPDATE which has to modify effect
     rows that are part of this 11th chunk of data.

My question is that which of the below things happen now.original table now ?

A) Update will be blocked till the chunk be copied and then applied on original table
   which gets updated through triggers on new one.

B) Update will be performed on original table and data chunk will be re copied all
   over again.

If Option A is the answer, how PT-ONLINE-SCHEMA-CHANGE can be lock free ?

If Option B is the answer, how it knows the chunk being copied is modified ?

Thanks in advance.

pt-online-schema-change takes advantage of the consistency nature of relational databases at table level. Let's see what happens when we write to the original table, one of three cases:

The chunk has already been copied. No problem, the trigger will overwrite the values of the accessory table within the transaction
The chunk has not yet been copied. The trigger will probably fail, but the original modification will be done thanks to the IGNORE option on the INSERT/UPDATE/DELETE of the trigger. No problem, when the pt-osc process reaches to it, it will copy the newer values.
Your question. The chunk is being copied. There are 2 subcases, depending on which process starts first.

a) If the modification of the original table starts first, either the whole table (in the case of MyISAM) or the set of rows (in the case of InnoDB), will be locked for other modifications, so pt-osc will have to wait for the write to finish. You should avoid very time-consuming write operations while this tool is working, or relying on it to pause if the load is too high, but in the unfortunate case that you are doing in concurrency another large update, the session variable innodb_lock_wait_timout (configured by the parameter lock-wait-timeout, by default 1 second) for pt-osc should kick in and revert/abort the copy of that chunk. In that case, the tool will retry that chunk again --retries times (default, 3), or the whole process will abort, in theory gracefully. In most cases, once the lock disappears (because the concurrent write finishes), you will be essentially on case 2.

b) If pt-osc starts first, it will essentially lock again the whole table (in MyISAM) or those specific rows (in InnoDB) for writing (a READ LOCK), preventing concurrent writes. This will only be hold for, ideally, up to 0.5 seconds, as the chunk size has been calculated so that the chunk copy should not take more than that. Once it has finished, you are essentially on case 1.

So, yes, there is not 100% lock free tool, but no such a tool can exist if you want to guarantee consistency. Even the rename table process takes microseconds (after all, a/several file(s) must be renamed on disk), and for those microseconds, the table will be locked. The idea is to have a minimum amount of locking. For MyISAM, every time you write a row (except for concurrent inserts) the whole table is locked, and for InnoDB, at least that particular row (sometimes more). You cannot change that unless you sacrifice the atomicy and durability of changes. However, you can take into acount some tricks to minimize its problems:

Use InnoDB instead of MyISAM for extra write concurrency
Avoid slow/large writing transactions on that table during the copy process
Lower the value of chunk-time parameter. In an extreme case, you could set chunk-size to one, copying and locking one row at a time, but that will be extremely slow. The chunk-time will be an heuristic worse-case scenario of the maximum locking time when you happen to write the exact rows that you are copying at that time.
MySQL 5.6 allows for concurrent writes while certain DDLs are running. It is less flexible, but it should work faster as it has access to the underlying logic. In some cases, this may work better, but it will suffer from similar problems when doing too many concurrent writes (as those will be buffered).

Reading the code of the tool is not that hard, I invite you to read it for more information.


SQL GUID or Identity in the Cloud Follow up from my previous question SQL Server Identity Column in the Cloud:

Are there any general guidelines? Should a person use Clustered Index Identity or GUID in the cloud for SQL Server 2016? Our company many deploy into Cloud (Amazon AWS, Google Cloudcloud), in any type (PaaS, DBaas, VM), not sure which. 'Company Strategy is be able to deploy anywhere.'
We only utilize 5million (70GB) rows per year.

Note: If were ever horizontally elastically scale databases, we can manage identity columns (DB1 gets 1Trillion-2T, DB2 gets 2Trillion-3T) using range identities or location/storage ids.
Guids have higher write slowness and fragmentation than Identity, but are mergable in horizontal scale databases. With Identity, 8 bit, Incremental so faster insertions. If using Guid, our API will generate Newid(), not NewSequentialId.

Oh goodness, you have a lot of questions in here. I'll try to unpack them all.

Q: What is "SQL Server in the cloud"?

The term "the cloud" is too generic - you might as well be saying "SQL Server on the network." There are lots of different ways to deploy SQL Server in the cloud, some of which function exactly like on-premises servers (it's just that someone else is managing the server for you.)

If you can, try to avoid generic sweeping statements like "SQL Server in the cloud" because you won't get good answers. The more specific your question can be, the more relevant advice you can get. (Not to mention the less downvotes, ha ha ho ho.)

Q: Is there a universal rule for clustering keys?

Generally, you want your clustering keys to follow the SUN-E principles:

Static - fields that don't change, so you don't have to update your nonclustered indexes
Unique - so SQL Server doesn't have to add a uniqueifier behind the scenes
Narrow - so lots of data isn't copied into your nonclustered indexes
Ever-increasing - creating a hotspot at the end, guaranteeing that where you're inserting will usually be in memory (although this is one rule that folks violate on purpose during extremely heavy concurrency, like tens of thousands of inserts per second)

Identity fields match all 4 (SUN-E), while GUID fields only match the SU portions. One could argue that a single GUID is kinda narrow compared to, say, multiple GUIDs chained together or an NVARCHAR(250), but it's got nothin' on the narrowness of an INT.

Q: Do data modeling guidelines change in IaaS?

Infrastructure-as-a-Service providers like Amazon EC2, Google Compute Engine, and Microsoft Azure VMs are just VMs running on someone else's computer. Data modeling rules do not change here.

Q: Do data modeling guidelines change in PaaS?

Platform-as-a-Service providers are a little different.

Amazon RDS is just SQL Server hosted and managed by someone else, so no data modeling changes required there.

Microsoft Azure SQL DB isn't exactly "just SQL Server hosted and managed by someone else" - it has some strengths and weaknesses that differ from the traditional boxed product. However, if you're only building your app for a single database in Azure SQL DB (not scaling out to multiple databases), then the conventional rules apply.

Q: When does "the cloud" affect data modeling guidelines?

When you want to scale a single application across multiple databases and/or servers. This design pattern is called sharding, splitting a single identical table (or sets of tables) across hosts.

To be clear, you can do this same design pattern on-premises - it's just more trendy these days in the cloud because it's so much easier to spin up multiple servers or databases.

You noted that your database only uses 70GB per year - that's well below the numbers where I'd even remotely consider sharding.

Q: When sharding, what data modeling concerns will I have?

First, your application has to know which shard to hit in order to get the relevant data. (You don't want the app hitting the database to discover this data for every query - after all, you'd be right back up against the scalability limits of a single database server.)

You could shard by customer location - like one shard for North America, another for Europe, another for Asia. However, your customers may move between locations, or they may have locations spread across contents.

You could shard by customer name - like A, B, C, D - but you'll have some shards that are much hotter than others due to load. (Say one of your customers runs a World Cup sale - that server might fall over while the rest are idle.) And of course, customers can change names.

You could shard by activity date - like the date of sale - but of course today's shard will be ridiculously hot compared to the other shards.

By now, you're starting to see now why your question is so broad, and so difficult to answer. Your best bet to get an actionable, useful answer will be to provide as many specifics as you can. However, since you asked for a design for 70GB of data per year, keep it simple: stick with the SUN-E principles.


temporary table inside procedure oracle I'm in a situation right now. I'm migrating several procedures from Mysql 5.0 to Oracle 11g.
Mysql procedures allow me to:
1. Create temporary tables
2. Insert data into temporary tables / Query these tables / Do some processes
3. Drop temporary tables

I've been searching and found I can not create DDL Statements within procedures without EXECUTE IMMEDIATE statement. I tried to create the temporary table with that statement, and immediately insert some values on it but it does not work 'cause the table does not already exist. I tried to create a function which creates a temporary table, then call it from the procedure but the same happens.

I need to achieve the same steps I'm doing with MySQL. This situation happens very often (there are so many procedures with this kind of "issue") and they are quite large.

What options do I have, what do you recommend ?

It depends on why you are creating the temporary tables in MySQL.

Frequently, people that are creating temporary tables in other databases are doing so in order to work around limitations that don't exist in Oracle where readers don't block writers and writers don't block readers.  In other databases, you commonly copy data from a permanent table to a temporary table so that your process doesn't block some other process that needs the same data.  Since Oracle provides multi-version read consistency, however, this isn't necessary (or beneficial) in Oracle-- your process can process the data sitting in the real tables without worrying that it is going to block someone else.  If that's the situation you're in, the proper response is simply to remove the temporary tables and process the data from the permanent tables.

Assuming that you really need a temporary copy of the data, you can create global temporary tables.  You would create these tables outside of your code just like a permanent table and use them just like a permanent table inside your code.  The global temporary table ensures that each session can only see the data that session has inserted.  The only difference is that you're not dropping and recreating the structure of the table inside your procedure.

Another alternative would be to pull the data into a PL/SQL collection that you work with rather than using a temporary table.  PL/SQL collections are stored in the server's PGA (one of Oracle's memory structures) so you generally want to limit the size of the collection particularly if there is a lot of data to process or there could be many sessions processing data simultaneously.  You can do something like

DECLARE
  TYPE emp_tbl IS TABLE OF emp%rowtype;
  l_emps emp_tbl;

  CURSOR emp_cur
      IS SELECT *
           FROM emp;
BEGIN
  OPEN emp_cur;
  LOOP
    -- Fetch 10 rows at a time from the cursor into the collection.
    -- You'd realistically want a larger limit, something between 100 and 1000 generally
    FETCH emp_cur
     BULK COLLECT INTO l_emps
    LIMIT 10;

    EXIT WHEN l_emps.COUNT = 0;

    -- An example of manipulating the collection in memory
    FOR i IN 1 .. l_emps.COUNT
    LOOP
      l_emps(i).sal := l_emps(i).sal * 2;
    END LOOP;

    -- And an example of using the collection to update a table
    FORALL i IN 1 .. l_emps.COUNT
      UPDATE emp
         SET sal = l_emps(i).sal
       WHERE empno = l_emps(i).empno;
  END LOOP;
END;

Of course, in the example above, it would be far simpler (and more efficient) to simply issue an UPDATE statement that would double every employee's salary.


How to deal with datetime ranges in a table with separate columns for date and time? In a table that stores events, the date and time are two separate columns:

CREATE TABLE events (
    pk int serial,
    detail text,
    ev_date date,
    ev time without time zone
);

If I filter for dates:

ev_date BETWEEN start_date::date AND end_date::date AND
ev_time BETWEEN start_date::time AND end_date::time

I am missing all events within the two dates that occurred outside the time range of every day.

Thus, the start time is only relevant for the start date and the same for the end date.

Anyone with an advice on how to do it efficiently for millions of events?

You can use the simple condition:

WHERE (ev_date, ev_time) BETWEEN (start_date, start_time)
                             AND (end_date, end_time)

or this one:

WHERE ev_date + ev_time BETWEEN start_date + start_time
                            AND end_date + end_time

What's the best of these two depends on what indexes you have on the table. If you have an index on (ev_date, ev_time) use the first. If you can add a filtered index on (ev_date + ev_time), use the second.

*: I'm assuming that all these are parameters: start_date, start_time, end_date, end_time.


How do I safely generate scripts for an existing MS SQL instance? I've inherited a web application that talks to an MS SQL Server instance. It uses multiple databases and database users.

I'm setting up a new staging environment for this application. It must match the existing database schema, but use "dummy" data. I want the process to be repeatable, so I'm scripting it. The scripts will be kept in version control alongside the app, and therefore cannot contain things like hardcoded passwords.

So far I have something like this:

In the existing environment, use the "generate scripts" wizard to script all database objects in all databases except table data. Concatenate the results and start with that.
Script the creation of any necessary service logins, their permissions, and their passwords. Since I can't include passwords in version control, presumably I'll need to use a storedproc that asks for them.
Create dummy table data in .tsv files, and import them with a BULK INSERT loop or a batch file that calls bcp.exe

I haven't done anything like this before and want to be sure I'm not missing something important.

Am I doing this in the right order? I suspect logins may need to be created before schema initialization. But before schema initialization, any stored procs are unavailable.
Is there anything I need to leave out of the "generate scripts" step, to avoid dumping passwords or instance-specific settings that should not be cloned in this way?
Is there anything I need to include that I'm missing, for the new SQL instance to have everything it needs? (I am thinking of things like the system databases; I don't think I should carry those over, but I'm not sure, and I'm worried about the users-vs-logins distinction).

Look into dbatools (which uses PowerShell to do everything imaginable)

simplifying disaster recovery with dbatools – dbatools

getting started – dbatools

command index – dbatools

As for dummy data - do you need to only copy schema/objects, or data inside as well and scrub/randomize?


How can I represent a list of words and related words in a relational structure? I have a list of header words and words as below and I am trying to understand how best to represent these in a relational table:

Header      Other
Words       Words

abandon     abandoned, abandoning, abandonment, abandons
abstract    abstraction, abstractions, abstractly, abstracts
access      accessed, accesses, accessibility, accessible, accessing, inaccessible  access
accommodate accommodated, accommodates, accommodating, accommodation    accommodate
accompany   accompanied, accompanies, accompaniment, accompanying, unaccompanied    accompany
accumulate  accumulated, accumulating, accumulation, accumulates

The problem is that for every word I have additional information such as pronunciation.

What I am thinking of is two tables:

CREATE TABLE [dbo].[Word] (
    [WordId]         INT           IDENTITY (1, 1) NOT NULL,
    [Word]           NVARCHAR(50)  NOT NULL,
    [Pronounciation] NVARCHAR(100) NULL,
    [Other columns follow ...
)

CREATE TABLE [dbo].[WordRelationship] (
    [WordRelationshipId]  INT IDENTITY (1, 1) NOT NULL,
    [HeaderWordId]        INT NOT NULL,
    [OtherWordId]         INT NOT NULL,
)

I would like to get some suggestions from the members here

That is pretty much how you have to do it. The relationship table for a many-to-many link like that is often called a junction table (though goes by many other names on occasion: map table, mapping table, bridge table, ...).

If you have more than one class of relationship between words (abbreviation, antonym, synonim, ..) than that should be in the junction table too.

If you don't have multiple relationship types then there may be no need to have WordRelationshipId: the most efficient way to store what you currently have in SQL Server is just HeaderWordId, WordID with the two columns together being the primary key and that key being clustered (though as a matter of style/consistency some people prefer to have a separate surrogate key for all entities, and some frameworks baulk at composite PKs, so YMMV).


Extract certain rows from table and import I have a requirement to export / extract certain rows (matching some query) of some tables from one oracle database and import those rows in the respective tables of other database.

both source and target tables have same schema.

My question is, .Whats the best way to implement this requirement.?

Heard of oracle export data pump utility but I have to extract only certain rows.Or Is there any other way to achive this programmatically.

thanks in advance.

You can specify your queries per table with the QUERY parameter with Data Pump export. For example:

QUERY=employees:"WHERE department_id > 10 AND salary > 10000"

With the above, only those rows are exported from the employees table, that have department_id > 10 AND salary > 10000.

More information in the official documentation:

http://docs.oracle.com/database/121/SUTIL/dp_export.htm#SUTIL860

Another way to transfer the data: create a database link, and just simply run an INSERT ... SELECT statement.

For example, on the target database:

create database link source connect to username identified by password using 'SOURCE_TNS';
insert into table1 select * from table1@source where ...;


How to handle "reference tables" in CouchDB? What's the best way to handle "reference tables", tables that contain static data, for example a list with various kinds of ingredients for food?

Multiple documents, one for each ingredient
One document per reference

{
  "_id" = "ingredients",
  "ingredient": [{ "name":"Apple", ... }]
}
One document with all references

{ "_id" = "references", "ingredients": [{"id": "apple", "name": "Apple", ...}], "spices": [{"id": "cinnamon", "name": "Cinnamon", ...}]
}

As a MySQL DBA staring at this, I think in relational terms. I also think in terms of doing JOINs. However, from a NoSQL standpoint, JOINs would have to be done programmatically.

Multiple documents, one for each ingredient : This will make smallest amount of static data to place in collections. Yet, you should be prepared for getting references, building temporary collections for yourself with CouchDB or sending data straight to your app.

One document with all references : If you are content with mining Collections out of Collections out of Collections ... (inifinity), you are willing to construct such collections, and you are comfortable with it, then storing the data this way would be OK. Please just make sure you are not the only CouchDB Jedi around (unless it's a matter of job security), since you might want your code understandable and maintainable by others.

One document per reference : Now, this is a nice middle ground. It will provide some flexibility in the event there are changes in the Collection layout. Simple retrievals for ingredients can be independent of retrievals for spices or utensils. Performing programmatic JOINs is still needed but not all that deep. Those who are a novice to NoSQL paradigms (like myself) would be comfortable understanding and retrieving data without the fear of breaking deeply-entrenched complex data representations.

My personal choice would be option #2.


SQL Server 2016 Standard or Enterprise for SharePoint 2016 I know that for SharePoint 2016 version we can go for either SQL server 2016 standard or enterprise. But just wanted to know are there any disadvantages of going for SQL server standard edition instead of enterprise?

My question is in particular to the SharePoint features we might be missing if we go for Standard SQL server.

Are there any disadvantages of going for SQL server standard edition
  instead of enterprise

The disadvantage is you can't use enterprise features, like AlwaysOn AG among others (see below). If you aren't going to use these features, then you don't need Enterprise generally speaking. The advantage is you'd save some money. Both are licensed per core, and enterprise costs more per core.... like 4X more than standard.

Here are features that come in Enterprise that don't come in Standard:

Unlimited memory (max of OS)
Online Page restore
Online index rebuilds
Mirrored backups
Automatic tuning
Transparent database encryption
Parallel index operations
etc...

See the data sheets, and the full comparisons for the differences. Generally speaking, unless you really need those enterprise features, I'd would opt for standard for the HUGE cost savings.

Microsoft also has a guide to help you decide which one you need.


Is timestamptz preferred when timezone logic is performed by the client? I'm in the process of migrating a web application from SqlServer to PostgreSQL and I'm trying to figure out which type to replace datetime2 with.

The general advice seems to be always use timestamptz, and never use timestamp. The reasons given tend to be along the lines that timestamp and timestamptz are stored the same regardless (so no performance penalty) and timestamptz auto-converts to the timezone of the connection. Ignoring timezones altogether in Rails and PostgreSQL | Stack Overflow

Unfortunately my legacy .NET codebase is very inconsistent with datetimes and we usually render in UTC regardless of the users timezone. More recent code has been using NodaTime and it's Instant class, but we rarely have to deal with times and displaying just date has been "close enough". My understanding of using NodaTime properly, however, is to convert an Instant to LocalDateTime as late as possible - and not in the database.

In addition to this, I'm not entirely sure how Postgres knows the correct timezone of the "current user". I know you can set the timezone specifically as a session parameter SET TIME ZONE 'UTC';, are you expected to do this for every connection as appropriate for the "current user"? If so, is this reset whenever the connection is retrieved from the connection pool? I also see that Npgsql has the ability to set a timezone for a connection string, presumably this isn't appropriate if it's per user?

All this leads me to think the best option is to use timestamp for all datetimes, and use application logic to convert to local datetime. I guess another option is to use timestamptz for all datetimes, force the connection to use UTC in the connection string, and use application logic to convert to local datetime. However I worry that Postgres will perform extra work in doing a no-op conversion between UTC and UTC.

TLDR: Is timestamptz still preferred if the application always inserts/reads UTC and converts to local datetime itself?

Is timestamptz still preferred if the application always inserts/reads UTC and converts to local datetime itself?

Well, no. Then it's very slightly simpler / faster to use timestamp.

But is "the application" the only client accessing the database? And is it going to stay this way for the lifetime of the DB?

If doubts remain, I would still use timestamptz, it's the safe choice. You already found my related answer on SO I would suggest as reference for basic information:

Ignoring time zones altogether in Rails and PostgreSQL

Also, timestamptz literally is the "preferred" type among "Date/time types" in Postgres (tagged typeispreferred in pg_type), which can make a difference, but probably not to your particular question, even though the question title almost sounds like you might be asking for that. Related:

Generating time series between two dates in PostgreSQL


Script to estimate row sizes for any table I am trying to estimate the space requirements for a central database server that will be collecting data from about 200 identical field databases. I have the average daily row count for each table and now need to estimate the row size, including indexes, for each table.

Is there such an animal in existence or do I need to roll my own? If I do need to roll my own, can you suggest a good approach?

TIA

Script selected as an asnwer has some drawbacks:

Does not take into account some SQL Server datatypes:

image, text, uniqueidentifier, sql_variant, ntext, hierarchyid, geometry, geography, xml, sysname

and (max) data structures structures such as varchar(max) to name a few.

Takes into account computed columns which should not affect record's size.
Does not take into account the fact table has NULLable columns.
Does not take into account the fact that some datatypes have different size depending on specified scale
(for example time(0) and time(7) should have different column length)

The following script shows the size of the record as it is on a data page.
It shows maximum size that may be taken by the record, minimum size
(in case one all values in the record are NULL if permitted by table structure).
It shows table type (CLUSTERED/NONCLUSTERED), total number of data columns and schema where table belongs.

Note this script does not take into account indexes.

Update: Script counts each bit column as 1/8th of a byte. Minimum and maximum sizes are adjusted to ensure that records cannot be smaller than the forwarding stub size.

  SELECT
     -- record cannot be smaller than the forwarding stub size =9 Bytes
     CASE WHEN [Max Size]>=9
     THEN [Max Size]
     ELSE 9
     END AS [Max Size]
     -- record cannot be smaller than the forwarding stub size =9 Bytes
    , CASE WHEN [Min Size]>=9
     THEN [Min Size]
     ELSE 9
     END AS [Min Size]
    , [Table Name]
    , [Table Type]
    , [Total Number of Columns]
    , [Schema]
    FROM
    (
SELECT
    DISTINCT
    -- Overhead for row header of a data row
    4
    +
    -- Overhead for NULL bitmap
    2+cast(([Total Number of Columns]+7)/8 AS BIGINT)+
    -- overhead for variable length
    CASE WHEN [IsVariableLength]>0
    THEN
    2
    ELSE
    0
    END
    +
    --- Sum is on record level
    SUM(
    a1.[max_length]
    +
    -- Overhead for variable-length columns
    CASE WHEN
    -- varchar
    [System Type]='varchar'
    --(([system_type_id]=167) AND ([user_type_id]=167))
    OR
    -- nvarchar
    [System Type]='nvarchar'
    --(([system_type_id]=231) AND ([user_type_id]=231))
    OR
    -- IMAGE
    (([system_type_id]=34) OR ([user_type_id]=34))
    OR
    -- TEXT
    (([system_type_id]=35) OR ([user_type_id]=35))
    OR
    --  NTEXT
    (([system_type_id]=99) OR ([user_type_id]=99))
    OR
    --  SQLVARIANT
    (([system_type_id]=98) OR ([user_type_id]=98))
    OR
    -- hierarchyid geometry geography
    (([system_type_id]=240))
    THEN 2
    ELSE 0
    END
    )
    OVER (PARTITION BY a1.[Schema], a1.[Table Name]) AS [Max Size]

    , -- Overhead for row header of a data row
    4
    +
    -- Overhead for NULL bitmap
    2+cast(([Total Number of Columns]+7)/8 AS BIGINT)+
    -- overhead for variable length
    CASE WHEN ([IsVariableLength]>0) AND ([AnyFixedColumn]=0)
    THEN
    2
    ELSE
    0
    END
    +
    --- Sum is on record level
    SUM(
    -- overhead for variable length depending on number of variable columns
    CASE WHEN
    -- varchar
    --[System Type]='varchar'
    (([system_type_id]=167) OR ([user_type_id]=167))
    OR
    -- nvarchar
    --[System Type]='nvarchar'
    (([system_type_id]=231) OR ([user_type_id]=231))
    OR
    -- IMAGE
    (([system_type_id]=34) OR ([user_type_id]=34))
    OR
    -- TEXT
    (([system_type_id]=35) OR ([user_type_id]=35))
    OR
    --  NTEXT
    (([system_type_id]=99) OR ([user_type_id]=99))
    --  VARBINARY
    OR
    (([system_type_id]=165) OR ([user_type_id]=165))
    OR
    --  SQLVARIANT
    (([system_type_id]=98) OR ([user_type_id]=98))
    OR
    -- hierarchyid geometry geography
    (([system_type_id]=240))
    OR
    -- xml
    (([system_type_id]=241))
    THEN
        CASE WHEN [Is Nullable]=1
        THEN 0
        ELSE
        1
        END
    ELSE
        CASE
        WHEN
        -- bit
        (([system_type_id]=104) OR ([user_type_id]=104))
        and [Is Nullable]=1
        THEN 0
        ELSE
        a1.[max_length]
        END
    END
    --

    )
    OVER (PARTITION BY a1.[Schema], a1.[Table Name]) AS [Min Size]
    , a1.[Table Name]
    , [Table Type]
    , [Total Number of Columns]
    , a1.[Schema]
    FROM
    -- Start a1
    (   SELECT
        (SELECT [name] FROM [sys].[schemas]
        WHERE [sys].[schemas].[schema_id]=[sys].[objects].[schema_id])
        AS [Schema]
        , [sys].[objects].[name] AS [Table Name]
        , [sys].[all_columns].[name] AS [Column Name]
        , [sys].[all_columns].[system_type_id]
        , (
            SELECT name FROM [sys].[types]
            WHERE [sys].[types].[system_type_id]=[sys].[all_columns].[system_type_id]
            AND
                    [sys].[types].[user_type_id]=[sys].[all_columns].[user_type_id]
            ) AS [System Type]
        , [sys].[all_columns].[user_type_id]
        ,
        CASE
        WHEN
        -- IMAGE
        (([system_type_id]=34) OR ([user_type_id]=34))
        THEN 2147483647
        -- TEXT
        WHEN (([system_type_id]=35) OR ([user_type_id]=35))
        THEN 2147483647
        --  NTEXT
        WHEN (([system_type_id]=99) OR ([user_type_id]=99))
        THEN 1073741823
        -- varchar(max)
        WHEN (([system_type_id]=167) OR ([user_type_id]=167)) AND ([sys].[all_columns].[max_length]=-1)
        THEN 2147483647
        -- nvarchar(max)
        WHEN (([system_type_id]=231) OR ([user_type_id]=231)) AND ([sys].[all_columns].[max_length]=-1)
        THEN 1073741823
        -- varbinary(max)
        WHEN (([system_type_id]=165) OR ([user_type_id]=165)) AND ([sys].[all_columns].[max_length]=-1)
        THEN 2147483647
        -- hierarchyid geometry geography
        WHEN (([system_type_id]=240))
        THEN 2147483647
        -- xml
        WHEN (([system_type_id]=241) AND ([sys].[all_columns].[max_length]=-1))
        THEN 2147483647
        -- bit
        WHEN (([system_type_id]=104) OR ([user_type_id]=104))
        THEN 1/8
        ELSE
        CAST([sys].[all_columns].[max_length] AS BIGINT)
        END [max_length]
        , [sys].[all_columns].[is_nullable] AS [Is Nullable]
        ,
        CASE
        WHEN EXISTS
            (
                SELECT type_desc FROM sys.indexes
                WHERE type_desc='CLUSTERED'
                AND [sys].[objects].[object_id]=[sys].[indexes].[object_id]
            )
        THEN 'CLUSTERED'
        ELSE 'HEAP'
        END AS [Table Type]
        , COUNT([sys].[all_columns].[name]) OVER (PARTITION BY [sys].[objects].[object_id]) AS [Total Number of Columns]
        ,SUM (CASE WHEN
        -- varchar
        (
        (([system_type_id]=167) AND ([user_type_id]=167))
        OR
        -- nvarchar
        (([system_type_id]=231) AND ([user_type_id]=231))
        )
        AND [sys].[all_columns].[is_nullable]=0
        THEN 1
        ELSE 0
        END) OVER (PARTITION BY [sys].[objects].[name]) AS [IsNonNullableVariableLength]
        ,SUM (
        CASE WHEN
        -- varchar
        (([system_type_id]=167) OR ([user_type_id]=167))
        OR
        -- nvarchar
        (([system_type_id]=231) OR ([user_type_id]=231))
        OR
        -- IMAGE
        (([system_type_id]=34) OR ([user_type_id]=34))
        OR
        -- TEXT
        (([system_type_id]=35) OR ([user_type_id]=35))
        OR
        --  NTEXT
        (([system_type_id]=99) OR ([user_type_id]=99))
        --  VARBINARY
        OR
        (([system_type_id]=165) OR ([user_type_id]=165))
        OR
        --  SQLVARIANT
        (([system_type_id]=98) OR ([user_type_id]=98))
        OR
        -- hierarchyid geometry geography
        (([system_type_id]=240))
        OR
        -- xml
        (([system_type_id]=241))
        THEN 1
        ELSE 0
        END) OVER (PARTITION BY [sys].[objects].[name])
        AS [IsVariableLength]
        ,SUM (
        CASE WHEN
        -- varchar
        (([system_type_id]=167) OR ([user_type_id]=167))
        OR
        -- nvarchar
        (([system_type_id]=231) OR ([user_type_id]=231))
        OR
        -- IMAGE
        (([system_type_id]=34) OR ([user_type_id]=34))
        OR
        -- TEXT
        (([system_type_id]=35) OR ([user_type_id]=35))
        OR
        --  NTEXT
        (([system_type_id]=99) OR ([user_type_id]=99))
        --  VARBINARY
        OR
        (([system_type_id]=165) OR ([user_type_id]=165))
        OR
        --  SQLVARIANT
        (([system_type_id]=98) OR ([user_type_id]=98))
        OR
        -- hierarchyid geometry geography
        (([system_type_id]=240))
        OR
        -- xml
        (([system_type_id]=241))
        THEN 0
        ELSE 1
        END) OVER (PARTITION BY [sys].[objects].[name])
        AS [AnyFixedColumn]
    FROM [sys].[objects]
    INNER JOIN sys.all_columns
    ON [sys].[objects].[object_id]=[sys].[all_columns].[object_id]
    WHERE type_desc='USER_TABLE'
    ) a1
    ) a2


Storing time with milliseconds in database Just a quick question regarding storing "time" in a database. I'm logging the time of users runs which are in the format 00:00:00:00 (hours, minutes, seconds, milliseconds).

I was originally going to store it as TIME but then I realised MySQL doesn't support milliseconds in this type.

I will be storing the actual time taken from the users stopwatch, not the time when they ran and finished (this will be calculated before being inserted).

What would be the best field type to use? I was thinking either double or float, but I'm not sure that would work?

Personally, I'd use integer types for duration

Example: 340,000 milliseconds is 340000 in an unsigned int column.

To me, datetime, time, date etc are for explicit points in time. 340 seconds is meaningless in that context. Having a start date/time allows the duration to be added of course