Adding Last 12 Months to a row in a table (SQL Server)

Perhaps you want something like this?

INSERT INTO dbo.Test (
    Department
    , Month12
    , Month11
    , Month10
    , Month9
    , Month8
    , Month7
    , Month6
    , Month5
    , Month4
    , Month3
    , Month2
    , Month1
    )
VALUES ('Test'
    , DATEADD(MONTH, -12, GETDATE())
    , DATEADD(MONTH, -11, GETDATE())
    , DATEADD(MONTH, -10, GETDATE())
    , DATEADD(MONTH, -9, GETDATE())
    , DATEADD(MONTH, -8, GETDATE())
    , DATEADD(MONTH, -7, GETDATE())
    , DATEADD(MONTH, -6, GETDATE())
    , DATEADD(MONTH, -5, GETDATE())
    , DATEADD(MONTH, -4, GETDATE())
    , DATEADD(MONTH, -3, GETDATE())
    , DATEADD(MONTH, -2, GETDATE())
    , DATEADD(MONTH, -1, GETDATE())
);

This assumes several things.

You have DATE (or perhaps DATETIME, etc) as the data type for the 12 columns for each month.
You want each month to reflect the current day.  For instance, since it is currently Jan 2nd, this code would insert 2014-01-02, 2014-02-02, ... 2014-12-02 into the table.

If you are using SQL Server 2012 and above, you can modify this:

DATEADD(MONTH, -1, GETDATE())

to this:

FORMAT(DATEADD(MONTH, -1, GETDATE()), 'MMM-yyyy')

To get the date as Jan-2014.  There are also a lot of other ways that are probably more efficient.


sp_BlitzCache gives a "divide by zero error encountered."

Clearly, without a doubt, that's a bug. You'll want to post it there.

https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues

I'm guessing MaxCompileMemory is 0 for you? This is the only division I see,

CASE WHEN CompileMemory > 1024 AND ((CompileMemory) / (1 * MaxCompileMemory) * 100.) >= 10. THEN ', High Compile Memory' ELSE '' END, 3, 200000)

Update

Filed here and fixed with this patch.


Since SQL Server can skip NULL rows to start the range scan, the cost of either index is identical, so this is basically a coin toss for the optimizer. Look at the plans in SQL Sentry Plan Explorer* by default and when you hint the index (click to enlarge):

Since it's a toss-up, I don't know what benefit you'd get out of forcing SQL Server to choose one of two equally valid options.
* Disclaimer: I work for SQL Sentry.


Why my query works on VIEW and doesn't work on a similar table?

As you have absolutely no filtering clauses (no WHERE clauses or JOINs that would filter the rows from the base table) the only reason you should get zero rows from that query is that there are zero rows in the table for it to return.

SELECT COUNT(*) FROM OpenStreetMapView will confirm or contradict this.

If a process that should have populated the table has not yet completed other transactions may not be able to see the data yet (until that transaction is complete), though I believe in this case the default locking and transaction isolation settings would cause either an error to be raised or a sleep to be imposed for the checking query until the other process is complete (rather than returning nothing).


First time using an Index - SQLite / Android

WHERE c = ?

This lookup can be optimized with a simple index on the c column:

CREATE INDEX T_c on T(c);

WHERE a LIKE ? || '%'

There are several restrictions on indexes that can be used for LIKE searches.
In this case, you need a case-insensitive index:

CREATE INDEX T_a_ci ON T(a COLLATE NOCASE);

WHERE b LIKE ? || '%'

same here:

CREATE INDEX T_b_ci ON T(b COLLATE NOCASE);

WHERE a LIKE ? || '%' AND b LIKE ? || '%'

The database can use at most one index per table in a query, so it will use one of the indexes above.
If you have run ANALYZE, it might be able to choose the better one.

To check which indexes are actually being used, use EXPLAIN QUERY PLAN.

Whenever you change tables, any indexes must be updated too.
When you're filling an empty table, it is usually a better idea to create indexes afterwards.

When your application does many updates to some table, it might be worthwhile omitting some index(es) that are useful only for seldomly-used queries.


Expiration date on a user in a specific database

To the best of my knowledge, there's no way to set an expiration date on a database user.

I believe the best solution would be what Edgar Allan Bayron suggested:

  you can set a sql server agent job to run daily which compares with today's date. If that date is equal to the "expiry date", then execute a query where it will disable the specific user

Here's a slightly more fleshed out version of that idea:

Create a new database for administrative use (if you don't already have one). We'll assume it's called dba_utilities.
In the dba_utilities database, create a table named db_user_expiration, with columns database (nvarchar(128)), username (nvarchar(128)), and expiration_date (datetime) (plus an id, integer, IDENTITY). You may want to have a removed bit or char(1) column as well.
In the same database, create a procedure to:

identify rows from the table where the user account has expired;
for each row, create a dynamic SQL statement to DROP the user from the indicated database
If the drop is successful, remove the row from the table (or, mark the row as removed).

If there will only be a few of these at a time, I'd simply use a STATIC LOCAL cursor to loop through the records; if the numbers will be very large, you could generate a single SQL statement to do all the accounts at once, but that's more complicated.

NOTE: If you don't create your users with a default schema, then they'll wind up having a schema with the same name as the user created for them. The schema must either be dropped or moved to a different owner before the user can be dropped. Unless your users need a schema of their own, I'd assign them to a default schema owned by the database owner ([dbo] may work well, in most cases), so this is not a problem.
Run this procedure from a SQL Server job as often as you like (from once a day to once an hour, if appropriate). The expiration_date column I suggest is a datetime, so you can specify the time of day when the expiration should take effect, and the next run after that time should see the user dropped.


How to query from a linked server via a linked server in sql server?

Unfortunately, with the conditions you've placed on your question, there is no way to accomplish what you want.
SQL Server would need to support 5 part naming such as:
SELECT *
FROM server1.server2.database.schema.table;

Which is clearly not going to work.
If you're not afraid of a little dynamic SQL, you could use something like this:
DECLARE @cmd nvarchar(max) = N'
DECLARE @cmd nvarchar(max) = N''
SELECT @@SERVERNAME; --this would be where your query goes.
'';
EXEC (@cmd) AT linked_server_2;
';
EXEC (@cmd) AT linked_server_1;

Essentially, the above code executes a dynamic SQL string over linked_server_1, but the dynamic SQL is actually an embedded dynamic SQL string, which executes a query at linked_server_2.


Find which SESSION_CONTEXT key-value pairs was set

I've checked the definition of many context-related system views, functions, and stored procedures, and cannot locate any further details covering session context variables.

For instance, the sys.dm_os_memory_cache_counters dynamic management view referenced in your question has the following definition:

CREATE VIEW sys.dm_os_memory_cache_counters AS
    SELECT *
    FROM OpenRowSet(TABLE SYSMEMORYCACHECOUNTERS)

The definition of SYSMEMORYCACHECOUNTERS system table is not visible directly, even from the dedicated administrator connection, or DAC, which is therefore a dead-end.

I used this query to look for context-related system objects:

;WITH types AS
(
    SELECT v.xType
        , v.TypeDescription
    FROM (VALUES
          ('AF', 'Aggregate function (CLR)')
        , ('C', 'CHECK constraint')
        , ('D', 'Default or DEFAULT constraint')
        , ('F', 'FOREIGN KEY constraint')
        , ('L', 'Log')
        , ('FN', 'Scalar function')
        , ('FS', 'Assembly (CLR) scalar-function')
        , ('FT', 'Assembly (CLR) table-valued function')
        , ('IF', 'In-lined table-function')
        , ('IT', 'Internal table')
        , ('P', 'Stored procedure')
        , ('PC', 'Assembly (CLR) stored-procedure')
        , ('PK', 'PRIMARY KEY constraint (type is K)')
        , ('RF', 'Replication filter stored procedure')
        , ('S', 'System table')
        , ('SN', 'Synonym')
        , ('SQ', 'Service queue')
        , ('TA', 'Assembly (CLR) DML trigger')
        , ('TF', 'Table function')
        , ('TR', 'SQL DML Trigger')
        , ('TT', 'Table type')
        , ('U', 'User table')
        , ('UQ', 'UNIQUE constraint (type is K)')
        , ('V', 'View')
        , ('X', 'Extended stored procedure')
    )v(xType,TypeDescription)
)
, sc AS
(
    SELECT so.id
        , ColumnList = STUFF(((SELECT ', ' + sc.name FROM sys.syscolumns sc WHERE sc.id = so.id FOR XML PATH(''))), 1, 2, '')
    FROM sys.sysobjects so
)
SELECT t.TypeDescription
    , so.name
    --, sc.ColumnList
FROM sys.sysobjects so
    LEFT JOIN sc ON so.id = sc.id
    LEFT JOIN types t ON so.xtype = t.xType
WHERE so.name LIKE '%context%'
    OR sc.ColumnList LIKE '%context%'
ORDER BY t.TypeDescription
    , so.name;

in SQL Server 2016, the result of the query is:

╔═══════════════════════════╦════════════════════════════════════════╗
║ Extended stored procedure ║        sp_reset_session_context        ║
╠═══════════════════════════╬════════════════════════════════════════╣
║ Extended stored procedure ║ sp_set_session_context                 ║
║ Extended stored procedure ║ sp_try_set_session_context             ║
║ In-lined table-function   ║ dm_exec_cursors                        ║
║ In-lined table-function   ║ fn_dblog                               ║
║ In-lined table-function   ║ fn_dblog_xtp                           ║
║ In-lined table-function   ║ fn_dump_dblog                          ║
║ In-lined table-function   ║ fn_dump_dblog_xtp                      ║
║ Internal table            ║ plan_persist_context_settings          ║
║ Internal table            ║ plan_persist_query                     ║
║ Stored procedure          ║ sp_MSadd_repl_error                    ║
║ Stored procedure          ║ sp_MSsetcontext_bypasswholeddleventbit ║
║ Stored procedure          ║ sp_MSsetcontext_replagent              ║
║ Stored procedure          ║ sp_sqlagent_verify_database_context    ║
║ View                      ║ dm_db_task_space_usage                 ║
║ View                      ║ dm_exec_query_stats                    ║
║ View                      ║ dm_exec_requests                       ║
║ View                      ║ dm_exec_sessions                       ║
║ View                      ║ dm_filestream_file_io_handles          ║
║ View                      ║ dm_filestream_file_io_requests         ║
║ View                      ║ dm_filestream_non_transacted_handles   ║
║ View                      ║ dm_os_memory_cache_entries             ║
║ View                      ║ dm_os_schedulers                       ║
║ View                      ║ dm_os_tasks                            ║
║ View                      ║ dm_os_threads                          ║
║ View                      ║ dm_os_waiting_tasks                    ║
║ View                      ║ dm_os_workers                          ║
║ View                      ║ dm_tran_locks                          ║
║ View                      ║ query_context_settings                 ║
║ View                      ║ query_store_query                      ║
║ View                      ║ sysprocesses                           ║
╚═══════════════════════════╩════════════════════════════════════════╝

Extended stored procedures are implemented in various SQL Server DLLs, and are not easily viewable without reverse-engineering; I've considered them to be black boxes for the purpose of this question.
sys.dm_exec_cursors, while pretty clearly not directly related to the "context" we're interested in, has the following definition:

CREATE FUNCTION sys.dm_exec_cursors (@spid int)
RETURNS table
AS
    RETURN SELECT *
    FROM OpenRowSet(TABLE DM_EXEC_CURSORS, @spid)

As you can see above, this function is really just a wrapper around the system-table DM_EXEC_CURSORS; the definition of system tables like this one are again implemented inside the SQL Server engine and not easily inspected.

The other in-lined table-functions listed in the output of the query above all utilize the same OpenRowSet(TABLE ...) functionality.
The sys.sp_MSsetcontext_bypasswholeddleventbit looks intriguing:

create procedure sys.sp_MSsetcontext_bypasswholeddleventbit @onoff bit -- 1 to turn on
as
begin
    declare @cur_context varbinary(128)
    declare @cur_context_first_byte binary(1)
    declare @bitmask tinyint
    declare @retcode int

    /*
    ** Security Check
    */
    EXEC @retcode = sys.sp_MSreplcheck_subscribe_withddladmin
    IF @@ERROR <> 0 or @retcode <> 0
        return (1)

    -- bit to set: snapshot=1, logreader=2, distrib=4, merge=8,
    -- replication_agent=16, merge_identityrange_alterconstraint=32
    -- merge_bypasswholeddleventbit=64
    if @onoff=1
        set @bitmask=64
    else
        set @bitmask=255-64

    -- get the current context_info. remember we only want to modify a bit without changing the rest of the info
    select @cur_context = isnull(context_info(),0x00)

    -- get the first byte out. the replication agent flags are set in the first byte.
    select @cur_context_first_byte = substring(@cur_context, 1, 1)
    -- set the appropriate bit in this one byte (leaving other bits unchanged).
    if @onoff=1
        select @cur_context_first_byte = (convert(tinyint,@cur_context_first_byte) | @bitmask)
    else
        select @cur_context_first_byte = (convert(tinyint,@cur_context_first_byte) & @bitmask)

    -- replace the first byte of the 128 byte binary variable, so that now it has the appropriate bit set.
    select @cur_context = convert(varbinary(128),stuff (@cur_context, 1, 1, @cur_context_first_byte))
    -- set the context_info again with the new binary(128) value.
    set context_info @cur_context

    if @@error <> 0
        return 1

    return 0
end

However, it only uses SET CONTEXT_INFO and CONTEXT_INFO() to inspect and modify the context info.  No luck here.
Perhaps sys.query_context_settings contains some gold?  Nope:

CREATE VIEW sys.query_context_settings AS
    SELECT
        context_settings_id,
        CONVERT(varbinary(8), set_options) AS 'set_options',
        language_id,
        date_format,
        date_first,
        CONVERT(varbinary(2), status) AS 'status',
        required_cursor_options,
        acceptable_cursor_options,
        merge_action_type,
        default_schema_id,
        is_replication_specific,
        CONVERT(varbinary(1), status2) AS 'is_contained'
    FROM (
        SELECT * FROM sys.plan_persist_context_settings
        UNION ALL
        SELECT TOP 0 * FROM OpenRowSet(TABLE QUERY_STORE_CONTEXT_SETTINGS)
    ) AS ContextSettings

I also checked the definitions of SQL modules, such as stored procedures, functions, etc, with this query:

SELECT so.name
    , asm.definition
FROM sys.all_sql_modules asm
    INNER JOIN sys.sysobjects so ON asm.object_id = so.id
WHERE asm.definition LIKE '%context%'
ORDER BY so.name;

None of the items listed in that query contain anything relevant, either.

In short, I could find nothing related to session context.  I expect this is by design since the session context could easily be used to store sensitive data for the duration of a session.


As with the linked answer, you should be able to do this by using TRY/CATCH; the catch (no pun intended) with the other answer is that, because the name of the linked server is validated at compile time, in the event the test fails, the batch doesn't execute. So instead, you can put the linked server test inside dynamic SQL:

DECLARE @srv SYSNAME = N'linked_server_name';

BEGIN TRY
  EXEC master.sys.sp_executesql
      N'EXEC master.sys.sp_testlinkedserver @srv;',
      N'@srv SYSNAME', @srv;
END TRY
BEGIN CATCH
  SELECT ERROR_NUMBER(), '|', ERROR_MESSAGE();
END CATCH

This should allow you to capture a variety of errors, including:

If @srv is not populated:

  214 | Procedure expects parameter '@servername' of type 'sysname'.

If @srv is not a defined linked server:

  7202 | Could not find server 'whatever' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

If a connection to @srv failed at the time of linked server creation:

  7303 | Cannot initialize the data source object of OLE DB provider "SQLNCLI11" for linked server "whatever".

If a connection to @srv1 times out (e.g. the server is offline) - this was using a local linked server, so the text may be a bit different when using named pipes or TCP/IP:

  2 | Shared Memory Provider: Could not open a connection to SQL Server [2].

(With TCP/IP, Kin says the error message is:

  TCP Provider: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.

If @srv is not configured for data access:

  7411 | Server 'whatever' is not configured for DATA ACCESS.

If the linked server was configured with the wrong username/password (or passes the current credentials and they fail):

  18456 | Login failed for user 'foo'.

You can also start the batch with EXECUTE AS if you are trying to test the linked server under specific credentials.

If the test of the linked server succeeds, you could continue inside the TRY block to do other things, like OPENQUERY, queries using 4-part names, etc., and there should be a whole slew of other errors you could capture and report on verbosely...


Recreating mat. view in Oracle failing due to dba_summaries entry, how to prevent?

If drop materialized view is leaving an entry in dba_summaries, then you are hitting a bug - perhaps the one described here

As you do not have a support contract, you can't raise a TAR. However the first question Oracle Support would ask you is whether you are running the latest patchset, 11.2.0.3 - I suggest you consider this option first as the root problem is an Oracle bug. Note that "it is a full installation"

If that doesn't solve the problem, you'll need to try and work around it because as far as I know there is no alternative to sysdba for drop summary. The linked article suggested dropping indexes before dropping the MV which would perhaps be a good place to start.

  Any pointers to documentation and if somebody can explain what the logic behind DBA_SUMMARIES

dba_summaries and drop summary are undocumented.


How to get status start_date and end_date from status_track table?

Of course, my previous answer was just to answer your immediate question.  As I mentioned in my comments, your data model itself is flawed.  Not only should you not keep both 'status_from' (prevous_status) and 'status_to' in the history table, you should also NOT keep status at all in the THINGS table.  This violates standard data normalization rules.

What you should do is drop status from the THINGS table, establish a FK relationship between the two tables, and use a proper query to derive the current status when needed.

First, create and populate the THINGS table.  I've added a couple of columns just to indicate the kinds of things that should be in this table:

S

QL> create table things (thing_id number,
  2                       attrib_a varchar2(10),
  3                       attrib_b varchar2(10),
  4                         constraint thing_pk primary key (thing_id)
  5                      )
  6  ;

Table created.

SQL> insert into things
  2     values (7770,
  3             'red',
  4             'white'
  5            )
  6  ;

1 row created.

SQL> insert into things
  2     values (8880,
  3             'blue',
  4             'green'
  5            )
  6  ;

1 row created.

Then create and populate the history tracking table:

SQL> create table thing_status
  2       (thing_id number,
  3        new_status  varchar2(10),
  4        status_date date,
  5          constraint fk_things
  6            foreign key (thing_id)
  7            references things(thing_id)
  8        )
  9  ;

Table created.

SQL> -- -----------------------------------------------------
SQL> insert into thing_status
  2     values (7770,
  3             'incomplete',
  4             to_date('2016-08-09 00:00:00','yyyy-mm-dd hh24:mi:ss')
  5             )
  6  ;

1 row created.

SQL> insert into thing_status
  2     values (7770,
  3             'inactive',
  4             to_date('2016-08-10 16:26:22','yyyy-mm-dd hh24:mi:ss')
  5             )
  6  ;

1 row created.

SQL> insert into thing_status
  2     values (7770,
  3             'active',
  4             to_date('2016-08-11 12:32:04','yyyy-mm-dd hh24:mi:ss')
  5             )
  6  ;

1 row created.

SQL> -- -----------------------------------------------------
SQL> insert into thing_status
  2     values (8880,
  3             'incomplete',
  4             to_date('2016-08-12 00:00:00','yyyy-mm-dd hh24:mi:ss')
  5             )
  6  ;

1 row created.

SQL> insert into thing_status
  2     values (8880,
  3             'inactive',
  4             to_date('2016-08-13 16:26:22','yyyy-mm-dd hh24:mi:ss')
  5             )
  6  ;

1 row created.

SQL> insert into thing_status
  2     values (8880,
  3             'active',
  4             to_date('2016-08-14 12:32:04','yyyy-mm-dd hh24:mi:ss')
  5             )
  6  ;

1 row created.

SQL> insert into thing_status
  2     values (8880,
  3             'expired',
  4             to_date('2016-08-15 12:32:04','yyyy-mm-dd hh24:mi:ss')
  5             )
  6  ;

1 row created.

And your history report (as before)

SQL> -- -----------------------------------------------------
SQL> --  Select status history
SQL> --
SQL> select thing_id,
  2         new_status,
  3         to_char(status_date,'dd-Mon-yyyy hh24:mi:ss') status_start_date,
  4         lead(to_char(status_date,'yyyy-mm-dd hh24:mi:ss'),1)
  5           over (partition by thing_id order by status_date) status_end_date
  6  from thing_status
  7  order by thing_id,
  8           status_date
  9  ;

  THING_ID NEW_STATUS STATUS_START_DATE    STATUS_END_DATE
---------- ---------- -------------------- -------------------
      7770 incomplete 09-Aug-2016 00:00:00 2016-08-10 16:26:22
      7770 inactive   10-Aug-2016 16:26:22 2016-08-11 12:32:04
      7770 active     11-Aug-2016 12:32:04
      8880 incomplete 12-Aug-2016 00:00:00 2016-08-13 16:26:22
      8880 inactive   13-Aug-2016 16:26:22 2016-08-14 12:32:04
      8880 active     14-Aug-2016 12:32:04 2016-08-15 12:32:04
      8880 expired    15-Aug-2016 12:32:04

7 rows selected.

And your current status report

SQL> -- -----------------------------------------------------
SQL> --  Select current status
SQL> --
SQL> select t.thing_id,
  2         t.attrib_a,
  3         t.attrib_b,
  4         s.new_status curr_status,
  5         to_char(s.status_date,'yyyy-mm-dd hh24:mi:ss') status_date
  6  from things t
  7   join thing_status s
  8     on t.thing_id = s.thing_id
  9  where s.status_date = (select max(status_date)
 10                         from thing_status x
 11                         where s.thing_id = x.thing_id
 12                         group by thing_id
 13                        )
 14  order by t.thing_id
 15  ;

  THING_ID ATTRIB_A   ATTRIB_B   CURR_STATU STATUS_DATE
---------- ---------- ---------- ---------- -------------------
      7770 red        white      active     2016-08-11 12:32:04
      8880 blue       green      expired    2016-08-15 12:32:04


dividing quatity INT value into unique separate rows for each item

To prevent all existing order_items rows for being added to items_new, you have to exclude rows that have already been processed from the cursor.

There are several ways to do this; the best option depends on things we don't know, at this point.

You could pass order_id into the procedure, so you're only processing the order_items from a specific order each time through.
If there's a date attached to orders or order_items, you could store the procedure's last run date, and only process orders with a date between the last run date and the current run date.
If you have some control over the database structure, you could add a bit field to order_items to indicate whether it had been processed or not, and only process the unprocessed rows.
Similarly, if the items_new records are retained as long as order_items, you could add order_id and line_item_id to items_new, and only insert records if there are no records with those two values already in the table.

NOTE: Working off dates could create a race condition, where a new order has been assigned its date, but hasn't actually been saved when the procedure runs - in that case, it might possibly be missed, and never have the ticket numbers generated.

UPDATE: - If you modify your procedure to accept 1 argument, @proc_order_id, which would be the order_id of the order you want to create ticket numbers for, then your cursor would be:

# Declare the cursor
DECLARE item_cur CURSOR FOR
    SELECT oi.MerchID, m.MerchName, oi.quantity FROM orders o
        JOIN order_items oi ON o.id = oi.order_id
        JOIN merchandise m ON m.MerchID = oi.MerchID
            WHERE o.customer_id = 2 AND
            MerchCatID = '7' OR MerchCatID = '8' AND
            o.order_id = @proc_order_id;

On the other hand, if you add order_id and line_item_id to items_new, the cursor would be:

DECLARE item_cur CURSOR FOR
    SELECT oi.MerchID, m.MerchName, o.order_id, oi.line_item_id, oi.quantity FROM orders o
        JOIN order_items oi ON o.id = oi.order_id
        JOIN merchandise m ON m.MerchID = oi.MerchID
            WHERE o.customer_id = 2 AND
            MerchCatID = '7' OR MerchCatID = '8' AND
            NOT EXISTS (SELECT 1 FROM items_new
                         WHERE order_id = o.order_id
                           AND line_item_id = oi.line_item_id);


Are multiple Primary Keys in MySQL allowed?

Only one primary key per table is permitted. But a primary key can consist of any number of columns. Having many columns in a primary key however is often (not always) a bad choice.

Some notes about this...

InnoDB tables are organized by primary key.
As a consequence, all secondary indexes contain the primary key. Having a big primary key means having big indexes.
Another consequence is that rows should be inserted in the order of the primary key (this happens automatically if you use an autoincrmeental id). Otherwise, InnoDB will have to do additional work to maintain the physical order of rows.
Yet another consequence is that updating the primary key values is a slow operation that shouldn't happen normally.
Lastly, if you don't keep in mind that the primary key is contained by all secondary indexes, with InnoDB it is easy to create indexes with duplicated columns.
A query can use a whole index or a contiguous leftmost part. In your case, for example, a query that does not mention id cannot possibly take advantage of the primary key.


I've done that on ubuntu, I don't use apparmor but these are the settings I've changed, all under [mysqld] (I think mysql_safe also uses these settings)

datadir = /mysql_data
innodb_data_home_dir = /mysql_data
innodb_log_group_home_dir = /mysql_data

I've also put my log files there but that is not needed.


How should I distribute my tables for better performance from fragmentation point of view

Moving 400k records per month into and out of tables shouldn't cause all that many problems from a performance perspective.  As SQL removes all the data from pages those pages will be reused for something else.

You shouldn't be getting inserts or updates in the middle of pages all that often as SQL should just be putting the new rows at the end of the table and deleting the old rows from where ever they are. (I'm assuming you are just deleting the oldest records when you delete records.)

If it is that much of a worry, I would put the historical tables into one filegroup and the other tables into another file group.


Installing Oracle 12c on Red Hat Enterprise Linux 6

Extracting the Installation Files

  Oracle RDBMS software is available as two archive files. Ensure that
  you extract both the archive files to the same directory.

Not just install/.oui, everything.


PRINT output is buffered.  If you replace PRINT ... with RAISERROR ... WITH NOWAIT you will see results as they happen.

DECLARE @msg VARCHAR(MAX);
DECLARE @counter INT;
WHILE (@counter < 50)
BEGIN
    BEGIN TRANSACTION
    SET @msg = 'Counter = ' + CONVERT(VARCHAR(4), @counter);
    RAISERROR (@msg, 0, 1) WITH NOWAIT;
    ;WITH CTE
    AS (
        SELECT TOP(50000) *
        FROM MySchema.MyTable
        )
    DELETE FROM CTE OPTION (MAXDOP 1);
    SET @counter += 1;
    COMMIT TRAN
END


Does cancelling (stopping early) a CLUSTER command cause a full rollback in Postgres 9.5?

If you called CLUSTER without parameters you cannot ROLLBACK at all since that is only applicable to transactions. Quoting the manual:

  This form of CLUSTER cannot be executed inside a transaction block.

But you talk about "CLUSTER a table", so assuming you mean the form:

CLUSTER table_name [ USING index_name ]

Which is transactional. Cancelling this command should not do any harm. Postgres builds the new table (and indexes) in the background and only updates references to physical files when done. The manual again:

  When an index scan is used, a temporary copy of the table is created
  that contains the table data in the index order. Temporary copies of
  each index on the table are created as well. Therefore, you need free
  space on disk at least equal to the sum of the table size and the
  index sizes.

  When a sequential scan and sort is used, a temporary sort file is also
  created, so that the peak temporary space requirement is as much as
  double the table size, plus the index sizes. [...]

Bold emphasis mine. But the work that went into the temp tables is lost when you cancel the command before it can finish.

CLUSTER does not even update table statistics, which is why you should probably run ANALYZE on the table after CLUSTER.

Concerning:

  I need the data so I cancel the CLUSTER command.

You might be interested in pg_repack, which can do the same as CLUSTER without exclusive locks, so you can use it without painting yourself into a corner - or even when you cannot afford downtime at all.

The extension is not shipped with Postgres, though. You have to install it yourself. It's not available on many cloud services.

Related:

Is a clustered index on column A the same as creating a table ordered by A?


What is the difference between a SQL Login and a Database User in terms of security?

You can have a login to the SQL Server instance, but no access to a given database. When your login is granted access to a DB, you become a user in that DB.

As for granting max perms via roles to all logins, this is very risky, and not common, or default. Best practice is to use the very lowest perms possible that still allows users to complete their work.


The MongoDB server actually does not search any locations for config files, and there is (as at MongoDB 3.2) no way to specify multiple config files or include config file fragments. There was a feature suggestion for includes discussed (see: SERVER-14911 in the MongoDB issue tracker), but it was decided that includes are not part of standard YAML and configuration management systems are likely the best option.

  From searching I know it checks /usr/local/etc/mongod.conf by default.

This is the default provided in the service recipe for the mongodb Homebrew package. The official Linux packages generally use a convention of /etc/mongod.conf.


Entity-Relationship Conceptual Data Model

Green rectangles are tables, red eclipses are attributes, blue triangles are tables that connect tables and a relationship descriptors so you could understand their relationship. Do not name triangle like tables as referred on the diagram, such as 'contains' , give them meaningful names.

Purple eclipses are additional attributes of tables that connect other tables

Numbers and letters next to triangles are telling you what kind of relationship it is

Whether is :

One to many - example Table Account has 1 and table Character has N which means one account can have many characters , but character can have only 1 account

Many to many - example Character can "Ran into" many creeps and many creeps can ran into many characters

What does it means?

It means that depending on a relationship type, you will create foreign keys and constrains accordingly. If you are still in doubt what the foreign keys are, i suggest you to read some books/articles before you actually start designing database. Databases are base for any project, and it is not something to be taken lightly


How can I rebuild an index on table with 100 million rows without a wait time out?

Community Wiki answer for the solution originally provided in a question comment:

  Martin Smith: Run the script through a new query window in SSMS. Not through the visual designer.


Return "parent" rows where only one "child" record exists in one-to-many relationship

Setup sample data:

create table accounts (account_id int, von datetime);
insert into  accounts (account_id, von) values (1,'2017-08-01'), (2,'2017-09-01'), (3,'2017-10-01');

create table accounts_prop (account_id int, propname varchar(30));
insert into  accounts_prop (account_id, propname) values (1,'pname1'), (2,'pname1'), (2,'pname2'), (3,'pname1');

Sample data joined together:

select a.account_id,
       a.von,
       ap.propname
from   accounts a
join   accounts_prop ap
on     a.account_id = ap.account_id
order by 1,2,3;

account_id | von                 | propname
---------- | ------------------- | --------
         1 | 2017-08-01 00:00:00 | pname1
         2 | 2017-09-01 00:00:00 | pname1
         2 | 2017-09-01 00:00:00 | pname2
         3 | 2017-10-01 00:00:00 | pname1

Proposed query:

select a.*
from   accounts a
where  a.von >= '2017-08-25'
and    (select count(*)
        from   accounts_prop ap
        where  ap.account_id = a.account_id) = 1
order by 1,2;

account_id | von
---------- | -------------------
         3 | 2017-10-01 00:00:00

Here's a dbfiddle for the above.


ERROR: improper qualified name (too many dotted names) when trying ALTER FUNCTION

Response from AWS Support:

On further researching, I was able to find that this was a known issue in Aurora Version 2.0 (compatible with 10.4) and the issue has been fixed on Aurora Version 2.1 ( compatible with PostgreSQL 10.5). I tried testing on my Aurora PostgreSQL 10.5 test cluster to confirm if the issue has been fixed and I was able to successfully execute the statement without any error message.

tl;dr Bug in Aurora 2.0 (PostgreSQL 10.4), fixed in Aurora 2.1 (PostgreSQL 10.5).

EDIT: Along with JohnC, I saw this error arise again in Aurora 3.0.0 (PostgreSQL 11.4). Support response Jan 11, 2020:

Upon further researching, I can confirm that this issue is also affecting Aurora PostgreSQL 11.4 engine version. After a thorough internal research around this error, I was able to find out that the Aurora PostgreSQL team is aware of this specific issue with 11.4 version as well and please be assured that we are already working on a fix for this that should be available in a later release.
Having said that, at the moment, I do not have an exact ETA for when this issue will be resolved for Aurora PostgreSQL 11.4.
Since the error is seen for ALTER FUNCTION command, the suggested workaround by the internal team is to recreate the function with the intended owner.


Transposing hierachical data from one VARCHAR to two INTs

As your data looks a bit like hierarchyId I thought about using that.  Initial versions did not scale well up to 1 million rows, however a few indexing choices on the main temp table have helped.  However issues could also be due to my test data, so can you tell me a bit more about your hierarchy?  For example how many top-level parents are there, how many levels are there on average, how many children might each level have?

In the meantime, here is a version so you can have a look through it and see if hierarchyId could work for you.

The basic idea is to create a temp table with the original ids converted into hierarchyIds, then walk the hierarchy to work out the lineage.  This temp table (which could be permanent) could then be used for the migration:

USE tempdb
GO

SET NOCOUNT ON
GO

IF OBJECT_ID('[dbo].[Almoxarifado]') IS NOT NULL DROP TABLE [dbo].[Almoxarifado]
IF OBJECT_ID('[dbo].[Almoxarifado2]') IS NOT NULL DROP TABLE [dbo].[Almoxarifado2]
GO

CREATE TABLE [dbo].[Almoxarifado](
    [idAlmoxarifado] [varchar](20) NOT NULL,
    [tipoAlmoxarifadoId] [varchar](30) NOT NULL,
    [entidadeId] [bigint] NOT NULL,
    [dtInclusao] [smalldatetime] NOT NULL,
    [dtUltimaAlteracao] [smalldatetime] NULL,
    [descricao] [varchar](255) NOT NULL,
    [terceiro] [bit] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Almoxarifado2](
    [idMaster] [bigint] IDENTITY(1,1) NOT NULL,
    [idAlmoxarifado] [int] NOT NULL,
    [idAlmoxPai] [int] NOT NULL DEFAULT ((0)),
    [entidadeId] [bigint] NOT NULL,
    [tipoAlmoxarifadoId] [varchar](30) NOT NULL ,
    [dtInclusao] [datetime] NULL DEFAULT (getdate()),
    [dtUltimaAlteracao] [datetime] NULL,
    [descricao] [varchar](255) NOT NULL,
    [terceiro] [bit] NULL DEFAULT ((0)),
    PRIMARY KEY ([idMaster])
)
GO

/*
-- Test data
INSERT INTO [dbo].[Almoxarifado] ( idAlmoxarifado, tipoAlmoxarifadoId, entidadeId, dtInclusao, dtUltimaAlteracao, descricao, terceiro )
VALUES
    ( '1', 'TESTE', 12, '29 Apr 2016', NULL, '0000000', 0 ),
    ( '1.1', 'TESTE', 12, '29 Apr 2016', NULL, '0000001', 0 ),
    ( '1.1.01', 'TESTE', 12, '29 Apr 2016', NULL, '0000002', 0 ),
    ( '1.1.01.01', 'TESTE', 12, '29 Apr 2016', NULL, '0000003', 0 ),
    ( '1.1.01.01.001', 'TESTE', 12, '29 Apr 2016', NULL, '0000004', 0 ),

    ( '1.1.01.01.002', 'TESTE', 12, '29 Apr 2016', NULL, '0000005', 0 ),
    ( '1.1.01.01.003', 'TESTE', 12, '29 Apr 2016', NULL, '0000006', 0 ),
    ( '1.1.01.01.004', 'TESTE', 12, '29 Apr 2016', NULL, '0000007', 0 ),
    ( '1.1.01.01.005', 'TESTE', 12, '29 Apr 2016', NULL, '0000008', 0 ),
    ( '1.1.01.01.006', 'TESTE', 12, '29 Apr 2016', NULL, '0000009', 0 )
GO
*/

-- Add 10 parent levels
INSERT INTO [dbo].[Almoxarifado] ( idAlmoxarifado, tipoAlmoxarifadoId, entidadeId, dtInclusao, dtUltimaAlteracao, descricao, terceiro )
VALUES
    ( '1', 'TESTE', 1, '1 Jan 2016', NULL, '00000001', 0 ),
    ( '2', 'TESTE', 1, '1 Feb 2016', NULL, '00000002', 0 ),
    ( '3', 'TESTE', 1, '1 Mar 2016', NULL, '00000003', 0 ),
    ( '4', 'TESTE', 1, '1 Apr 2016', NULL, '00000004', 0 ),
    ( '5', 'TESTE', 1, '1 May 2016', NULL, '00000005', 0 ),
    ( '6', 'TESTE', 1, '1 Jun 2016', NULL, '00000006', 0 ),
    ( '7', 'TESTE', 1, '1 Jul 2016', NULL, '00000007', 0 ),
    ( '8', 'TESTE', 1, '1 Aug 2016', NULL, '00000008', 0 ),
    ( '9', 'TESTE', 1, '1 Sep 2016', NULL, '00000008', 0 ),
    ( '10', 'TESTE', 1, '1 Oct 2016', NULL, '00000010', 0 )

-- For each parent, add 3 sublevels
INSERT INTO [dbo].[Almoxarifado] ( idAlmoxarifado, tipoAlmoxarifadoId, entidadeId, dtInclusao, dtUltimaAlteracao, descricao, terceiro )
SELECT
    idAlmoxarifado + '.' + CAST( x.y AS VARCHAR(10) ),
    tipoAlmoxarifadoId, entidadeId, dtInclusao, dtUltimaAlteracao, descricao, terceiro
FROM [dbo].[Almoxarifado] a
    CROSS JOIN ( SELECT TOP 5 idAlmoxarifado y FROM [dbo].[Almoxarifado] ) x

-- add n sublevels
INSERT INTO [dbo].[Almoxarifado] ( idAlmoxarifado, tipoAlmoxarifadoId, entidadeId, dtInclusao, dtUltimaAlteracao, descricao, terceiro )
SELECT
    idAlmoxarifado + '.0' + CAST( x.y AS VARCHAR(10) ),
    tipoAlmoxarifadoId, entidadeId, dtInclusao, dtUltimaAlteracao, descricao, terceiro
FROM [dbo].[Almoxarifado] a
    CROSS JOIN ( SELECT TOP 8 idAlmoxarifado y FROM [dbo].[Almoxarifado] WHERE idAlmoxarifado Not Like '%.%' ) x
WHERE idAlmoxarifado Like '%.%'

-- Add 8 sublevels
INSERT INTO [dbo].[Almoxarifado] ( idAlmoxarifado, tipoAlmoxarifadoId, entidadeId, dtInclusao, dtUltimaAlteracao, descricao, terceiro )
SELECT
    idAlmoxarifado + '.0' + CAST( x.y AS VARCHAR(10) ),
    tipoAlmoxarifadoId, entidadeId, dtInclusao, dtUltimaAlteracao, descricao, terceiro
FROM [dbo].[Almoxarifado] a
    CROSS JOIN ( SELECT TOP 13 ROW_NUMBER() OVER( ORDER BY idAlmoxarifado ) y FROM [dbo].[Almoxarifado] ) x
WHERE idAlmoxarifado Like '%.%.%'

-- Add 9 sublevels
INSERT INTO [dbo].[Almoxarifado] ( idAlmoxarifado, tipoAlmoxarifadoId, entidadeId, dtInclusao, dtUltimaAlteracao, descricao, terceiro )
SELECT
    idAlmoxarifado + '.0' + CAST( x.y AS VARCHAR(10) ),
    tipoAlmoxarifadoId, entidadeId, dtInclusao, dtUltimaAlteracao, descricao, terceiro
FROM [dbo].[Almoxarifado] a
    CROSS JOIN ( SELECT TOP 21 ROW_NUMBER() OVER( ORDER BY idAlmoxarifado ) y FROM [dbo].[Almoxarifado] ) x
WHERE idAlmoxarifado Like '%.%.%.%'
GO

INSERT INTO [dbo].[Almoxarifado] ( idAlmoxarifado, tipoAlmoxarifadoId, entidadeId, dtInclusao, dtUltimaAlteracao, descricao, terceiro )
SELECT
    idAlmoxarifado + '.0' + CAST( x.y AS VARCHAR(10) ),
    tipoAlmoxarifadoId, entidadeId, dtInclusao, dtUltimaAlteracao, descricao, terceiro
FROM [dbo].[Almoxarifado] a
    CROSS JOIN ( SELECT TOP 9 ROW_NUMBER() OVER( ORDER BY idAlmoxarifado ) y FROM [dbo].[Almoxarifado] ) x
WHERE idAlmoxarifado Like '%.%.%.%.%'
GO

-- Main hierarchyId processing
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp

SELECT
    IDENTITY( INT, 1, 1 ) AS idMaster,
    idAlmoxarifado,
    CAST( '/' + REPLACE( REPLACE( REPLACE( idAlmoxarifado, '.0', '.' ), '.0', '.' ), '.', '/' ) + '/' AS hierarchyid ) hId,
    CAST( '/' + REPLACE( REPLACE( REPLACE( idAlmoxarifado, '.0', '.' ), '.0', '.' ), '.', '/' ) + '/' AS hierarchyid ).ToString() hString,
    CAST( NULL AS INT ) AS idAlmoxarifado2,
    CAST( NULL AS INT ) AS idAlmoxPai

INTO #tmp
FROM [dbo].[Almoxarifado]
GO

-- Index temp table to help with recursive CTE
ALTER TABLE #tmp ADD PRIMARY KEY ( idMaster )
ALTER TABLE #tmp ALTER COLUMN hId hierarchyId NOT NULL
CREATE UNIQUE INDEX _idx ON #tmp ( hId ) INCLUDE ( idAlmoxarifado, hString )
GO

-- Walk the hierarchy
;WITH cte AS (
SELECT 1 AS xlevel, idMaster, idAlmoxarifado, hId, hString, hId.GetLevel() getLevel, 0 AS parentId
FROM #tmp
WHERE hId.GetLevel() = 1

UNION ALL

SELECT xlevel + 1, t.idMaster, t.idAlmoxarifado, t.hId, t.hString, t.hId.GetLevel() getLevel, c.idMaster AS parentId
FROM cte c
    INNER JOIN #tmp t ON c.hId  = t.hId.GetAncestor(1)
)
UPDATE t
SET
    -- Parse out the individual node, by taking its parent and stuffing it in front
    t.idAlmoxarifado2 = CAST( REPLACE( STUFF( c.hString, 1, LEN(c.hId.GetAncestor(1).ToString()), '' ), '/', '' ) AS INT ),
    t.idAlmoxPai = parentId
FROM cte c
    INNER JOIN #tmp t ON c.idMaster = T.idMaster

-- Check results
SELECT *
FROM #tmp

This whole script (including test data creation) runs in only a few minutes on my laptop.


Sum of different values on the same row in a table

select *, Score1 + Score2 + ... + Score50 as TotalScore
from table

You can fill in the rest of the scores :-)

By the way, you really should normalise this table. It will make your life much easier.


how can I call nzsql or nzload from script without disclosing sensitive password?

Three options:

export it to the environment.
put it in the script
use nzpassword

the examples are off the top of my head, I don't have an instance to test it out right now, you might need to tweak them a bit.

exporting it to the environment:

NZ_PASSWD_ENCRYPTED=29TY20T98=
export NZ_PASSWORD=`DecryptFunc $NZ_PASSWD_ENCRYPTED`
NzResult=$(nzsql -db ${NZ_DATABASE}  -u ${NZ_USER} -h $NZ_HOST \
              -qc "select 'hi there folks;'")
unset NZ_PASSWORD

Anyone who can read the environment variables set for your shell can still retrieve the password, but it won't show in the process list visible to everyone on the system

putting it in a script:

put the query in a sql file, add something like this at the top of it:

\set STOP_ON_ERROR TRUE
\echo *** connecting to MY_DATABASE ***
\connect MY_DATABASE MY_USER MY_PASSWORD
select 'hi there folks;'

Then use nzsql to execute this file.
You may need to specify a login user to nzsql as well, but any user should do (even one that can only has no other rights besides log in)
Anyone who can read the sql file will be able to see the password, but I don't think it will show up anywhere else

using nzpassword

There is a tool to store passwords encrypted in a hidden ~/.nzpassword file, for later use, with:

nzpassword add -u user -pw password -host hostname

see: http://www-01.ibm.com/support/knowledgecenter/SSULQD_7.1.0/com.ibm.nz.adm.doc/c_sysadm_encrypted_passwords.html


Get list of excluded databases from command line by asking user

Please look back at my post again.

DATABASES_TO_EXCLUDE

not supposed to be comma separated
supposed to be space separated

EXCLUSION_LIST

supposed to be a comma-separated list of single-quote enclosed database names
this is for the sake of forming the SQL to extract names from information_schema.schemata

If you made DATABASES_TO_EXCLUDE="db1,db2,db3", add the following lines to remove the commas

DBX=${DATABASES_TO_EXCLUDE}
DATABASES_TO_EXCLUDE=`echo "${DBX}" | sed 's/,/ /g'`

Your filename should look at little different

FILENAME="mysqlbck"-`date +"%Y%m%d-%H%M%S"`.sql

As for "Access Denied", you need to make sure you have the proper grants to the database.


MAX function and group by usage - postgresql

This might get you started:

select name,
       (reviews_count / reviews_count + max_reviews_count) * rating + (max_reviews_count / (reviews_count+max_reviews_count) * avg_rating) AS ranking
from (
  SELECT name,
         reviews_count,
         rating,
         MAX(reviews_count) over () as max_reviews_count,
         AVG(rating) over() as avg_rating
  FROM businesses
) t;

Note that I removed the nullif to make it easier to read.

I'm not sure the use of the window functions is totally correct. I guess you need to do some grouping in there. If that is the case, then add this to the over() clause, e.g. over(partition by name)

The partition by in a window function is essentially a group by without the need to group the whole result.

For more details on window functions, please see the manual: http://www.postgresql.org/docs/current/static/tutorial-window.html


Column as an Included column or as a key column

Currently i have classID as an included column in an index idx, so i
  think that the idx will be used when classID is in select statement.

No, not really. The index that is selected in the query plan depends on the query optimiser deciding which index best satisfies the query requirements. If your index key columns match, or are very similar to, your query predicates then it will likely select that index.

Having ClassID in the INCLUDE columns simply means the query plan will not require key lookups to retrieve this additional data about the rows returned in the query. If ClassID is a predicate, having it in the INCLUDE columns of an index will not cause the optimiser to definitely select that index.

  Do i have to create a a non clustered index for the classID as a key
  column when the query has where condition for classID.?

You need to create indexes that best satisfy the most common data access. For example, if you create a bunch of indexes with single key columns and many include columns, but your queries have many predicates and few select columns, then your indexing strategy is unlikely to be as efficient as it should be.

A good starting point/rule of thumb while you're getting started with index tuning\design is no more than 5 indexes per table, and no more than 5 columns per index. This helps you to be more selective about what indexes you create and how you design them to satisfy the most queries.

It is not a hard and fast rule, it just helps you to think about your index strategy relative to your queries.

  Confused on this whether to create separate non-clustered indexes for
  all the where clause columns.?

You don't create separate indexes for each predicate because the optimiser will likely only use one of them and then do a bunch of key lookups. Design your indexes with this basic guideline:

WHERE clause and JOIN columns in your query should be Key Columns in your Index.
SELECT columns in your query should be Include columns in your index.

These aren't hard and fast rules, and again don't design your indexes to satisfy a single query. You need to profile your data access characteristics and develop an indexing strategy to satisfy the most queries with the fewest indexes for maximum efficiency.

Some good resources:

SQL Server Index Basics

SQL Server Index Architecture and Design Guide

SQL Server Index Basic Design and Guidelines

Designing Effective Non-Clustered Indexes


How to deploy MongoDB config servers to achieve maximum availability during network partitions?

If you lose one of the three config servers, you just halt migrations, so in a balanced system where you're not exceeding 80% capacity, you should have hours and hours of time to page the duty DBA and bring back that one server.

You could virtualize them or place them on management band / highly reliable servers if you really can't afford to have down time for the cluster to perform chunk migration over a long weekend or other situation where you're not attentive to the config servers.

Most of your planning will be to make sure you understand the network partition scenarios and that the primary stays running and you have enough secondaries to serve read loads as needed. It's hard to generalize other than:

Add arbiters to keep the voting members an odd number.
Test your outage scenarios
Scale the system so you can actually perform rolling outages and verify that things work well without killing your application in practice. You might not test at peak load, but you should be able to take rolling outages routinely several times a week until you're confident in your tools, scripts and configuration.


How to sort strings with special characters in Hibernate?

I'm not an expert when it comes to collations but the following does provide the sorting your require. It may cause other characters to be sorted unexpectedly so please test.

WITH cte AS
(
    SELECT  '_zname_9' AS SortVal
    UNION ALL
    SELECT  '1_aname_' AS SortVal
)

SELECT  *

FROM    cte

--ORDER BY  SortVal
ORDER BY SortVal COLLATE SQL_Latin1_General_Cp437_BIN2

I've used a BIN2 collation to cover that fact that your name column may be a  unicode data type.


Replication with Global Transaction Identifiers across different MySQL versions

GTID was implemented as feature of MySQL 5.6, so short answer for 5.0.67 <-> 5.7.17 - NO

Long answer - even if 2 minor different versions support GTID, better and correct from points of view - have same versions for all servers in network, it help made Your life much more easy.


Extended Event doesn't capture every rpc_completed

Things may not be in Extended Events data when you look for a few reasons

Something in your XE session filtering excluded the data. This would be hard to diagnose without your XE session code, and the code that didn't make it in there. I'm not asking for those details, mind you -- that's a tip of the iceberg thing that isn't a good fit for Q&A here.
When you set up XE sessions, you have the choice to lose single, multiple or no events. The GUI even warns you against choosing to not lose any.
Events may not have been dispatched to the file in the time specified. For instance, if you run a query to see if it goes in there, you may have to wait at least 30 seconds from it finishing until it's in the XE session data.

Depending on the target, and how much data is allowed to go into the target, the data you're looking for may have expired from the XE data.

For rpc_completed, it's possible that the rpc did not complete-d. To tackle that, you'd need to get rpc_starting, and then track causality in the XE session settings.

I'd probably want to cover sp_statement_starting and sql_batch_starting as well. As David Browne mentioned, catching things in the right place can be tricky:

  An RPC is a query sent from the client, not embedded in a TSQL batch. A stored procedure can be run from a TSQL batch, or from another stored procedure and not appear as an RPC.

This isn't stuff that's easy to troubleshoot without looking at a server directly, but it should be enough for you to start your own investigation.


How to access objects created by a different user

Now we need to restrict his access to his previously created objects
  and make some arrangements so that other team mates from previous
  project can access his objects and work normally

It may be the case that your user was db_owner, in this case all the objects he created are already owned by dbo, in this case you should do nothing at all and simply drop the user:

drop user A;

There may be a situation when the user owns some schema and in this case database engine will prevent you from dropping user:

  The database principal owns a schema in the database, and cannot be
  dropped.

To be able to drop a user-owner of a schema you should first change the ownership of a schema:

alter authorization on schema::UserAPriv to dbo

Now you can try to drop the user and he will have no access to your database at all.

If the principal was also a grantor of permissions, database engine will prevent you from dropping user again:

  The database principal has granted or denied permissions to objects in
  the database and cannot be dropped.

in this case you should first find all the permissions where he is a grantor:

    select user_name(grantee_principal_id) as user_,
           object_name(major_id),
           permission_name,
           state_desc
    from sys.database_permissions
    where grantor_principal_id = user_id('A');

Then grant all these permissions as dbo, and then revoke them as User A:

grant select on UserAPriv.t1 to SomeUser as dbo;
revoke select on UserAPriv.t1 to SomeUser as A;

At this point you can drop User A.


Can sp_BlitzFirst pass the @ExpertMode parameter to sp_BlitzWho

You're watching a 4-year-old video on YouTube, and sp_BlitzFirst no longer works that way.

If you want to run sp_BlitzWho with @ExpertMode = 1, your best bet is to go ahead and call it directly.


MySQL High Performance for Lots of SELECTs/INSERTs/UPDATEs/DELETEs

If that's a bad solution would depend on many things. Does this data need to be persistent? Otherwise maybe a solution that simply keeps this data in memory would work better.

"A lot of users" isn't really helping anybody. MySQL will most likely be fine if "a lot" would mean a few hundreds. (Though depending on what else your database has to handle. Several thousand should most likely work too.)

After all, it doesn't matter that much, if you write those records to stay or delete them after a few seconds to minutes. Deleting just makes two operations out of one. And MySQL can for sure handle a very large amount of creating and deleting records. Make sure you use a simple index to find those records again for deletion.

But without actual numbers and some information about the hardware your database server uses, that can't be answered with much precision.

Best thing would be to write some small application, that simply simulates the amount of load you think you will get without doing much real processing, just drop a lot of records against the server, delete them, at the same rate run some queries like the rest of your program would generate. Have a look at your server and see, if that affects it in any way.

Not sure, but there are options to set for MySQL that would allow it to cache a table in memory completely.It does this anyway in many situationsand most likely you won't have to change much. But if you talk about a really very large amount of users and records, you can maybe tweak a few parameters to optimize caching for your special needs.


Why do we have to use SWITCH in table partitioning?

Switch is an extremely efficient way to add or remove data to your table.

You can switch in data, which is the preferred way to upload data in an ETL process. By preparing the data in a staging table (completely unrelated to your actual data) you can prepare it w/o concurrency issues (your ETL won't block reporting) and present the new data in one single switch, thus becoming visible all at once.

You can switch out data, which is the preferred way of removing data past the mandated retention period. This allows to TRUNCATE the data after switch out, efficiently and minimally logged. In many environments this is the only way to delete data due to high concurrency.

Basically any operation for which enabling partitioning makes sense requires SWITCH. If you have already enabled partitioning and still look for an explanation for SWITCH then is is almost certain you did not need partitioning to begin with.


Unable to Mask data after applying mask function in Azure Synapse table

It seems that the user has full rights to the database or at least she/he can get unmasked data.

Make sure that this user is not one of the 'SQL users excluded from masking'.

Quoted from docs:

Dynamic data masking permissions
Dynamic data masking can be configured by the Azure SQL Database admin, server admin, or SQL Security Manager roles.

Dynamic data masking policy

SQL users excluded from masking - A set of SQL users or Azure AD identities that get unmasked data in the SQL query results. Users
with administrator privileges are always excluded from masking, and
see the original data without any mask.
Masking rules - A set of rules that define the designated fields to be masked and the masking function that is used. The designated
fields can be defined using a database schema name, table name, and
column name.
Masking functions - A set of methods that control the exposure of data for different scenarios.


SELECTing min, max (maybe length) of continuous id ranges in a table

You can apply a GROUPING AND WINDOW solution.

select min(id) as min,
       max(id) as max,
       cust,
       load_dt,
       grp
from (
      select id, cust, load_dt,
             sum(rst) over (order by id) as grp
      from
      (
          select id, cust, load_dt,
                 case when coalesce(lag(cust) over (order by id), '') <> cust then 1 end as rst
          from   foo
      ) t
     ) t
group by grp, cust, load_dt
order by 1;

This is the result:

min | max | cust  | load_dt    | grp
--: | --: | :---- | :--------- | --:
  1 |   3 | cust1 | null       |   1
  4 |   5 | cust2 | 2018-05-15 |   2
  6 |   7 | cust1 | null       |   3
  8 |   8 | cust3 | 2018-05-17 |   4

db<>fiddle here


Why does SQL Server run a subquery for each row of the table it's qualifying?

The slow plan isn't calculating the MAX for each row in the outer query.

In fact it never explicitly calculates it at all.

It gives a plan similar to

WITH CTE
     AS (SELECT TOP(1) WITH TIES *
         FROM   SubqueryTest
         WHERE year IS NOT NULL
         ORDER  BY year desc)
SELECT month,
       count(*)
FROM   CTE
GROUP  BY month

Slow Plan (Estimated Row Counts)

You have a non covering index on year asc so it scans that backwards to get the rows in the first year (shows as a seek because of the implicit IS NOT NULL predicate).

Unfortunately it doesn't seem to differentiate between TOP 1 and TOP 1 WITH TIES when estimating row counts.

In this case it makes a huge difference. (estimated 2 key lookup vs actual 4,424,803) so you get an inappropriate plan.

Slow Plan (Actual Row Counts)

You could consider adding month into the index on year either as a key or included column to make the index covering. The benefit of adding it as a secondary key column would be that it could then feed into a stream aggregate without an additional sort (though for only 12 distinct values a hash aggregate would be fine anyway).

A non covering index on such a non selective column is really pretty useless for the vast majority of queries. The index is totally ignored by the "fast" plan which ends up doing a parallel scan on the whole table and evaluating the predicate on all 27,445,400 rows (in preference to performing the huge number of lookups).


ROWNUM equivalent in Centura SQLBase (aka Gupta)

One way to emulate row numbers in DBMS that do not have window function is using a triangular self join. It's usually not very efficient though.

Given that the primary key is (employee_no, date, group_code):

SELECT
    a.employee_no, a.date, a.group_code,
    COUNT(*) AS group_running_number
FROM
    tableX AS a
  JOIN
    tableX AS b
      ON  a.group_code = b.group_code
      AND ( a.date > b.date
         OR a.date = b.date AND a.employee_no >= b.employee_no
          )
GROUP BY
    a.group_code, a.date, a.employee_no ;

The order used for the assigning of row numbers above is: (date ASC, employee_no ASC)

An index on (group_code, date, employee_no) will improve efficiency I think.


You should restore it from your backup (which you can do by right clicking on "Databases" and selecting restore database)


Should partitioning increase performance of indexed accesses and updates?

Partitioning (at least from the perspective of SQL Server, but probably applies to most RDBMS) is not meant to be a tool for improving DQL query performance. E.g. your bread and butter SELECT statements won't see any impactful performance gains from partitioning, as you mentioned because of the Big O of B-Tree indexing. Rather it's meant to improve the performance (mostly be reducing contention) of DDL and some DML queries, for better database management.
I highly recommend this Brent Ozar article on partitioning (by Kendra Little) where she talks more theoretical implementation (as opposed to technical and specific SQL Server implementation).
One of the key points it mentions is partition switching which in 5 seconds is a methodology of partitioning on a key where your data is rolling (such as on a date field) and allows you to manage old partitions without causing locks or contention on the entire Table. By breaking up your Table into multiple partitions, you can archive your oldest partition without causing downtime on the rest of the Table that is active.
The same idea applies to other operations such as as index rebuilds / reorgs,  and other index and table maintenance, by allowing you to only affect certain partitions at a time and reduce the overall contention and locking on the Table at any given time. You can more smartly manage your Tables and their indexes accordingly.


Determining if auto stats update is in progress

SQL Server 2019 has introduced a wait stat to track this. On earlier versions, you're left to traces/XE. See my posts about the new wait stat:

SQL Server 2019: WAIT_ON_SYNC_STATISTICS_REFRESH
SQL Server 2019: WAIT_ON_SYNC_STATISTICS_REFRESH Redux

To use XE on prior versions, the event you want to look for is auto_stats. A bare minimum session to get you started would look like this:

CREATE EVENT SESSION auto_stats
    ON SERVER
    ADD EVENT sqlserver.auto_stats
    ADD TARGET package0.event_file
    ( SET filename = N'auto_stats' );

Though you'd probably want to configure it with some specificity to databases or tables that you care about, and I'd definitely want to filter on duration (WHERE [duration] > 1000000 would be one second) or something, because it'd be pretty noisy otherwise.


This might work.  Basically it's a ROW_NUMBER function that you will have to identify a key for (you Mentioned InvoiceNumber).  Once you do, it will return an ordered value where all your "duplicates" will be 2+.  Simply adding a where clause where ROWNUM =1 should get you the first record (ordered by the CreatedDate).

SELECT main.* FROM
(
SELECT
Upper(WFI.COMPANYID) as Company,
WFI.USERID as UID,
INF.NAME as Approver,
CONVERT(varchar(10),duedatetime,4) as Due,
right(left(Document,20),10) as InvoiceDate,
SUBSTRING(DOCUMENT,22,charindex(' ',RIGHT(DOCUMENT,len(DOCUMENT)-21))) as         InvoiceNumber,
Right(document,len(Document)-(20+charindex(' ',RIGHT(DOCUMENT,len(DOCUMENT)-21)))) as Vendor,
wfi.CREATEDDATETIME as Created,
--Added RowNumber Function Below
ROW_NUMBER() OVER (PARTITION BY **InsertYourKeyToAUniqueRecordHere** ORDER BY wfi.CREATEDDATETIME DESC) AS ROWNUM

FROM
[TEST].[tst].[WORKFLOWWORKITEMTABLE] WFI
INNER JOIN [TEST].[tst].[Workflowtrackingstatustable] WFS ON WFI.CORRELATIONID=WFS.CORRELATIONID
INNER JOIN [TEST].[tst].[HCMWORKER] HCM on WFI.USERID=HCM.PERSONNELNUMBER
INNER JOIN [TEST].[tst].[DIRPERSONNAME] DPN ON DPN.PERSON=HCM.PERSON
INNER JOIN [TEST].[tst].[LEDGERJOURNALTABLE] LJT ON WFI.REFRECID = LJT.RECID
INNER JOIN [TEST].[tst].[USERINFO] INF ON WFI.USERID = INF.ID

WHERE
DATASOURCENAME Like 'Ledgerjourna%'
AND Datediff(day,Duedatetime,getdate())>3
AND WFS.DOCUMENTTYPE='Special'

)main

WHERE main.ROWNUM =1 --Add this clause to only return the first record
ORDER BY main.Company asc

Feel free to comment out the WHERE main.ROWNUM=1 clause so that you can see your Rownum in action.

If you don't want your ROWNUM column to show in your final result set, then just replace the first SELECT * with the actual columns you want to select (using their aliases).


Check for blocked queries periodically and cancel them

You can do this by querying pg_stat_activity on newer PostgreSQL versions that record the query start time.

See pg_stat_activity in the PostgreSQL manual.

Use pg_cancel_backend to cancel the query, or pg_terminate_backend to close the connection running the query.

However, I strongly recommend that you instead fix the problem so that your queries don't get blocked. Look at pg_locks when it's stuck, determine why it's getting stuck, and fix the problem. Read the PostgreSQL manual chapter on explicit locking, and the wiki entries on lock monitoring.


How can you have a Buffer cache hit ratio of 100% with PLE of 103?

Buffer cache hit ratio and Buffer cache hit ratio base are measurements for a very small timeslice. You just ran your query at times when the recent reads hit the buffer cache.

For proof, just look at the raw values over time - they go up and down.

SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name IN ('Buffer cache hit ratio','Buffer cache hit ratio base')

I would recommend a look at other metrics (perhaps sys.dm_io_virtual_file_stats?) to see what kind of IO you're driving.


How to move 200 materialized views without having to re-create in the new home

If you are moving the database (backup on old server and restore on new server) the MVs will come along.

Are you creating an empty database on the new server? If so you could use Data Pump to export the MVs from the old and import them into the new.

I would opt for using a full RMAN (or filesystem) backup and restore over Data Pump.


Oracle Database 12c on Oracle Linux 7 - autostart, enabling connection as sysdba

Based on my own research, google results and ansible's answer, I was able to come up with complete solution for Oracle Linux 7. Everytime you read Kitet - this is my Oracle Instance SID.

To start or stop database, dbstart and dbshut scripts are used respectively. They are located in $ORACLE_HOME/bin. Both of them need a parameter, which is $ORACLE_HOME. If there's no $ORACLE_HOME environment variable, first there's a need to set it (and some more) by running

. /usr/local/bin/oraenv

(note the preceding period, and it's not $ORACLE_HOME/bin/oraenv). Oraenv script will ask for a SID, which is needed to set correct $ORACLE_BASE. With this, using simply

dbstart $ORACLE_HOME

or

dbshut $ORACLE_HOME

will start and stop the listener and database.

To start listener using lsnrctl or start SQL*Plus using sqlplus / as sysdba, running . oraenv beforehand is also required, unless user wants to set manually some environment variables. If our linux box has only one instance, there's a possibility of automating running of . oraenv by editing user's bash profile. Enter

nano ~/.bash_profile

and type following lines (there's probably already umask 022 entry in this file, as per Oracle Database Installation Guide 12c Release 1 for Linux E41491-10):

export ORACLE_SID=Kitet
export ORAENV_ASK=NO
. /usr/local/bin/oraenv
export NLS_LANG=POLISH_POLAND.AL32UTF8
unicode_start

Line one and three are self explanatory. Line two will make oraenv not ask for database SID and use exported variable. Line four is optional and will set your language preference for client connections (e.g. SQL*Plus or lsnrctl will speak in your native language, if available). Line five is needed for console to display accented characters, and is also optional.

Automating startup and shutdown of Listener and Database

1) Once the instance is created, edit the /etc/oratab file setting the restart flag for each instance to 'Y'. My line is as follows:

Kitet:/home/oracle/app/oracle/product/12.1.0/dbhome_1:Y

2) Next, create a file called /etc/init.d/dbora as the root user, containing the following:

#!/bin/sh
# chkconfig: 345 99 10
# description: Oracle auto start-stop script.
#
# Set ORA_HOME to be equivalent to the $ORACLE_HOME
# from which you wish to execute dbstart and dbshut;
#
# Set ORA_OWNER to the user id of the owner of the
# Oracle database in ORA_HOME.

# path to oracle home (needed only to check if dbstart exists)
ORA_HOME=/home/oracle/app/oracle/product/12.1.0/dbhome_1
# this is the user who installed oracle
ORA_OWNER=oracle

if [ ! -f $ORA_HOME/bin/dbstart ]
then
    echo "Oracle startup: cannot start"
    exit
fi

case "$1" in
    'start')
        # Start the Oracle database and listener:
        # Remove "&" if you don't want startup as a background process.
        export ORACLE_SID=Kitet
        export ORAENV_ASK=NO
        . /usr/local/bin/oraenv
        # at this point we have $ORACLE_HOME env variable set
        su $ORA_OWNER -c "dbstart $ORACLE_HOME" &
        touch /var/lock/subsys/dbora
        ;;
    'stop')
        # Stop the Oracle database and listener:
        export ORACLE_SID=Kitet
        export ORAENV_ASK=NO
        . /usr/local/bin/oraenv
        su $ORA_OWNER -c "dbshut $ORACLE_HOME"
        rm -f /var/lock/subsys/dbora
        ;;
esac

3) Use the chmod command to set the privileges to 750:

chmod 750 /etc/init.d/dbora

4) Associate the dbora service with the appropriate run levels and set it to auto-start using the following command:

chkconfig --add dbora

That's all. dbstart and dbshut should write to their respective logs at $ORACLE_HOME/startup.log and $ORACLE_HOME/shutdown.log. There also is $ORACLE_HOME/listener.log. There we can verify if and how instance and listener started and stopped. We could also use ps -ax | grep ora or ps -ax | grep lsnr to see if listener / oracle are running.


TempDB Transaction log not releasing space

The log_reuse_wait_desc is only updated when SQL Server attempts to clear the log.  In other words, it's not necessarily a "live" view of whether the log can be cleared or not.  Paul Randal explains that in his blog post "Why is log_reuse_wait_desc saying LOG_BACKUP after doing a log backup?".

In the case of tempdb, which uses the SIMPLE recovery model, SQL Server attempts to clear the log whenever a CHECKPOINT is issued.  This is why doing a manual CHECKPOINT frees up the space, as you mentioned.

SQL Server is a lot less aggressive about doing automatic CHECKPOINTs on tempdb, both because it uses the SIMPLE recovery model, and because it is by definition "temporary" information.  So there's no need to constantly persist it to disk.  This is a performance optimization specific to tempdb.

Per another blog post by Paul Randal, the details are as follows:

automatic CHECKPOINT operations on tempdb are only done when the log usage reaches 70%
dirty pages are not written to disk on automatic CHECKPOINT (its primary purpose it to clear the log)

The "work table" rows in dm_tran_active_transactions are normal behavior, and shouldn't prevent the log from being cleared.  You can read more about that in this Q&A:

SQL Server sys.dm_tran_active_transactions long running transaction worktable


How to optimize and find minimum from the two sql query, by writing it as single query?

From MySQL 8.0, you can use WITH to first filter for user_id:

WITH Raw AS (
  SELECT pv_value, pv_meta
  FROM `{$wpdb->prefix}wpmlm_pv_meta`
  WHERE user_id={$user_id}
), Raw2 AS (
  SELECT
    SUM(
      CASE WHEN pv_meta LIKE 'left_%' THEN pv_value ELSE 0 END
    ) AS leftSum,
    SUM(
      CASE WHEN pv_meta LIKE 'right_%' THEN pv_value ELSE 0 END
    ) AS rightSum
  FROM Raw
)
SELECT
  CASE
    WHEN leftSum < rightSum THEN rightSum
    ELSE leftSum
  END
FROM Raw2

assuming that the external parameters are the same in the queries.

Your original query (queries) used two WHERE-conditions: one for filtering for the user_id, and one for a LIKE prefix search. The best working of the MySQL would be if it would first filter first for the user_id by an index, and then it would use a linear scan for the LIKE. However, there is no guarantee for it to do. It is possible, and even likely, that your query triggers a linear scan on the whole table.

This optimization not only reduces the required two searches to a single one, but it also enforces to commit the user_id scan first.

If you don't have an index of wpmlm_pv_meta(user_id) then create it.


Data synchronization across multiple databases

From your description, it sounds like you need something like Bucardo to try and merge data from multiple sources into one.

Though you'll probably have to write some custom conflict handlers to deal with data that conflicts across B, B1, and B2. There are examples out there like this for custom conflict resolution:

http://blog.endpoint.com/2014/07/postgresql-conflict-handling-with.html

Londiste also lets you merge data from multiple tables into one:

http://skytools.projects.pgfoundry.org/skytools-3.0/doc/howto/londiste3_merge_howto.html

If you can just replicate many child tables from different databases to a single database, and have your application look across all of the tables for the consistent view of your data, then you might be able to get away with something as simple as Mimeo on github from OmniTI Labs.

Without further detail on your requirements, I can only make broad suggestions.

Hope that helps.


How to Create relationship with more than one foreign key

I guess you want do something like this:

CREATE TABLE product (category INT NOT NULL,
                      id INT NOT NULL, price DECIMAL,
                      PRIMARY KEY(category, id)
                     ) ENGINE=INNODB;

CREATE TABLE customer (id INT NOT NULL,
                       PRIMARY KEY (id)
                      ) ENGINE=INNODB;

CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT,
                            product_category INT NOT NULL,
                            product_id INT NOT NULL,
                            customer_id INT NOT NULL,
                            PRIMARY KEY(no),
                            INDEX (product_category, product_id),
                            INDEX (customer_id),
                            FOREIGN KEY (product_category, product_id)
                                REFERENCES product(category, id)
                                ON UPDATE CASCADE ON DELETE RESTRICT,
                            FOREIGN KEY (customer_id)
                                REFERENCES customer(id)
                          ) ENGINE=INNODB;

See more here


I have read your question, but not your proposed solutions, so I can't be influeced.

So, my ideas is that you can do a table STUDENTS and another table, let's say STUDENT_TYPE.

In STUDENTS, you put a column, ID_STUDENT_TYPE. In this column, you can set, with the ID of STUDENT_TYPE, the type of the single student.

In this way, if you have to insert a new student type, you won't have any problems! ;)
It is an elastic solution!


You need an extra pair of parentheses around the indexed term if it is not a column or a function call.

In the words of the documentation:

  The expression usually must be written with surrounding parentheses, as shown in the syntax. However, the parentheses can be omitted if the expression has the form of a function call.

CREATE INDEX training_complete_history_adt_flds_index
ON document.training_complete_history (((adt_flds).created_at));

I think it is a bad idea to use a composite type if you want to use parts of it in a WHERE or join condition. It violates the first normal form.


Is there a major RDBMS that DOESN'T allow column name quoting/escaping?

As Leigh Riffel said, the answer to my question depends partially on what you mean by "major."

I'm going with: No, there are no major RDBMS engines out there that can't handle name escaping/quoting. A quoted/escaped name is apparently properly called a delimited identifier.

Martin Smith pointed out that delimited identifier is in the SQL-92 specification:

<delimited identifier> ::=
    <double quote> <delimited identifier body> <double quote>

Now, how well specifications are followed varies widely, but...

I've verified the following engines:

Microsoft SQL Server supports the standard "name" at least as far back as SQL Server 2000 (and also its own non-standard [name])
Oracle supports the standard
MySQL uses backticks (`name`) by default, but if you set SET GLOBAL SQL_MODE=ANSI_QUOTES; it supports the standard
Postgres supports the standard
SQLite (I said it depended on what you call "major") supports the standard and SQL Server's [name]

I figure the first four up there cover the vast majority of installations, and the fifth is important for embedded database work (as are a couple from the following list).

a_horse_with_no_name tells us that all of these support it too:

Sybase (he says Sybase does it like Microsoft)
DB2
Apache Derby
H2
HSQLDB

(This being a Stack Exchange site, if you know of others that also support this, feel free to edit the answer and add them directly if you have enough rep. If you don't, just comment and I'll fold them in.)


SQL query based on foreign key relationship

Your solution seems correct. Here's another way that may be more efficient as it doesn't do a grouping. It can't show the MIN(status) though:

SELECT
    "id", "name"
FROM
    "testapp_project" AS p
WHERE
    NOT EXISTS
        ( SELECT *
          FROM "testapp_action" AS a
          WHERE p."id" = a."project_id"
            AND a."status" <= 1
        ) ;


Get rows having different values for a column based on the duplicate values of combination of other 3 columns

Using standard SQL on most RDBMS, there are various ways.

Using a subquery:

SELECT d.dept, d.role1, d.role2, DEF
FROM data d
INNER JOIN (
    SELECT dept, role1, role2
    FROM data
    GROUP BY dept, role1, role2
    HAVING COUNT(distinct DEF) > 1
) dup
    ON dup.dept = d.dept AND dup.role1 = d.role1 AND dup.role2 = d.role2
;

The subquery returns sets of dept/role1/role2 with more than 1 distinct DEF.

Using a correlated subquery:

SELECT d.dept, d.role1, d.role2, DEF
FROM @data d
WHERE EXISTS (
    SELECT 1
    FROM @data
    WHERE dept = d.dept AND role1 = d.role1 AND role2 = d.role2 AND DEF <> d.DEF
);

The subquery return 0 to n rows. If at least one row exists, the row from the main table is returned.

Using CROSS APPLY:

SELECT d.dept, d.role1, d.role2, d.DEF
FROM @data d
CROSS APPLY (
    SELECT n=1
    FROM @data
    WHERE dept = d.dept AND role1 = d.role1 AND role2 = d.role2 AND DEF <> d.DEF
) ca
;

CROSS APPLY works with Oracle or SQL Server.

Output:

dept    role1   role2   DEF
a       abc     er      0
a       abc     er      1


SQLServer does not support function overloading - so you can't write two definitions for this procedure using different argument lists.

However, you could re-write the stored procedure to accept a string rather than a datetime as its first parameter.

I suspect the error occurs because of an implicit type casting when the string 'NULL' is received. You did, however, say that the parameter may be passed as "a string containing a valid date". The fact you get the error when the contents of the string is not a valid date implies to me the function is expecting a datetime (and not expecting a string) and performing an implicit type conversion (because in both those cases, it is receiving a string - in one case where the value represents a valid date, and in the other case where the value is 'NULL' - the first converts the type successfully to datetime and the second fails).

If you explicitly change your procedure to receive a string argument, then perform the conversion within the procedure itself, you could first check if the string value is 'NULL' - then set a local variable as null to be used within the procedure. If the string value is not 'NULL' then use a try catch block to cast(@input as datetime) - if it succeeds, then great, continue with your procedure. If not, throw an error.


Closed queries re-open when connecting to DB using MySQL Workbench

I thought this was some kind of auto-save feature from Amazon EC2 instance or some kind of bug, but nope my bad.
I found bug report and at the bottom of the page this is the answer:

  Andres Giordano:
  This is not a bug!!! You have to go to Edit --> Preferences --> General --> SQL Editor (panel) and uncheck "Save snapshot of open editor...."

That's the correct answer. Once this option is disabled, no more queries are stored and re open.
I hope this tip is useful for somebody. Greetings!


Determining that Indexes on a table are unused

Index maintenance (rebuild/reorganize) and DBCC CHECKDB activity most likely, possibly statistics updates. Any scheduled maintenance configured?

If there is no user access, bin them. Just be mindful of the time frame over which you decide they are no longer used. Are there any weekly or monthly reporting tasks for instance?

While you're looking, dig around for duplicate indexes as well.

Edit: regarding SSC link

From a quick scan through the thread, looks like the SSC folk had similar thoughts. They are however taking a more cautious stance on the possible "occasional" use of these indexes, taking the position that someone put them there for a reason, a perfectly reasonable argument. The counter argument is that all too often it's the exact opposite, someone put them there because they thought it was the right thing to do but through a lack of understanding or lack of testing, it wasn't.

I've brought a couple of systems back from the brink by doing nothing other than dropping unused and duplicated indexes. Over indexing can cause chaos.

It's your system, you need to understand and weigh the risks of leaving these indexes in place or dropping them. If you decide to go ahead with the drop, document what you do, why you're doing it, script the indexes and publish to all interested parties.


Slow update commands only on newly added data?

It is taking a long time because of out-of-date statistics. You've added a lot of rows to the table, so the statistics are not good now. Rebuilding the primary key seems to resolve the performance issue because you get updated statistics when you rebuild an index. But rebuilding an index is an expensive way to just get updated statistics.

Run UPDATE STATISTICS WITH FULLSCAN on the primary key, or consider a sampling. If the table has millions of rows in it, perhaps a 30 percent sampling would do the trick.


Unexpected behaviour when got error in the middle of mysqldump backup file import

The behaviour is correct and it is incorrectly observed as per update from the questioner.
This got verified by further debugging the dumpload adding a debugging select syntax after the erroneous SQL in the dump.


What is the difference between SQLEXPRESS and MSSQLSERVER?

You can install multiple copies of SQL Server onto the same machine. To keep them apart, all "copies" (called instances) except the first (default) one have to be named.

So by default, when you install your first instance of SQL Server, you won't give it any instance name - it'll be the default instance that you connect to use the server name of ., (local) or your machine name (or IP address).

Any further instance you want to install needs to get a separate, unique instance name.

Visual Studio will by default install SQL Server Express onto your machine (unless you explicitly tell it not to), and that will be called the SQLEXPRESS instance on your machine (hence the service name also is called SQLEXPRESS). You connect to that named instance using .\SQLEXPRESS or (local)\SQLEXRPESS.

So most likely, in your case, you installed a "full" version of SQL Server onto your machine (as the default instance - service is called MSSQLSERVER), and Visual Studio installed a second copy of SQL Server - an Express version - as SQLEXPRESS


In any decent RDBMS (e.g Postgres, SQL-Server, Oracle, DB2), the simple approach would work fine and swap the values of the two columns:

UPDATE game
SET homescore = awayscore,
    awayscore = homescore
WHERE (awayscore > homescore AND winner = hometeam)
   OR (awayscore < homescore AND winner = awayteam) ;

In MySQL, it will not work as expected! You'll have to use some kind of trick, like this one:

UPDATE game
SET homescore = (@tmp := homescore),
    homescore = awayscore,
    awayscore = @tmp
WHERE (awayscore > homescore AND winner = hometeam)
   OR (awayscore < homescore AND winner = awayteam) ;

You could also add one or two more columns in the table, use them for swapping and then drop them but it won't be very efficient. A better approach would be to use another (temporary) table:

CREATE TABLE temp_swap
( pk INT NOT NULL         -- whatever datatype the Primary Key of your table is
, homescore INT           -- INT or FLOAT or DECIMAL
, awayscore INT           -- whatever the two columns are
, PRIMARY KEY (pk)
) ;

Copy the values to be swapped there:

INSERT INTO temp_swap
  (pk, homescore, awayscore)
SELECT pk, homescore, awayscore
FROM game
WHERE (awayscore > homescore AND winner = hometeam)
   OR (awayscore < homescore AND winner = awayteam) ;

And then back, swapped, into the table:

UPDATE game AS g
  JOIN temp_swap AS t
    ON t.pk = g.pk
SET g.homescore = t.awayscore,
    g.awayscore = t.homescore ;

DROP TABLE temp_swap ;


A lot of the .NET source code is available, and much of it is Open Source, though under various licenses. Still other code is available, but only for reference purposes and cannot be used in your code.

Given that the stated goal is:

  I would like to increase that limit for a complex existing application that is bound to hit this limit. I don't wish to change the interface of the type. I am not confident that I could pull off changing the interface of the type without introducing subtle bugs into all the code that would have to be changed as a result.

if the intention is to update the current Hierarchy type, then whether or not the code is Open Source is irrelevant because even if it is, you would never be able to introduce any changes into the overall system: even if you could replace the Assembly in SQL Server, you wouldn't be able to sign it with the same Certificate / Private Key and hence it wouldn't be used by the system.

However, you could build your own SQLCLR User-Defined Type (UDT) to provide the desired functionality.

Still, as it relates to gaining a better understanding of the .NET Framework:

You can view the source code of most stuff here:  https://github.com/Microsoft/referencesource

and even more stuff here: https://github.com/dotnet/corefx

Both of the repositories noted above are Open Source and governed mostly under the MIT license, though you need to review the licensing info in each location, and sometimes in various folders, to be certain.

A lot of the remaining code can be found here: http://referencesource.microsoft.com/

The code found at ReferenceSource.Microsoft.com (the link directly above) that is not also found at the first two links (which are Open Source) is not Open Source and is instead governed by the MICROSOFT REFERENCE SOURCE LICENSE (MS-RSL), the relevant portion being (lightly edited to put the definition of "reference use" just following the usage of that phrase):

  the Licensor grants you a non-transferable, non-exclusive, worldwide, royalty-free copyright license to reproduce the software for reference use: use of the software within your company as a reference, in read only form, for the sole purposes of debugging your products, maintaining your products, or enhancing the interoperability of your products with the software, and specifically excludes the right to distribute the software outside of your company.

However, it appears that the Microsoft.SqlServer.* namespace is not available in any of those locations. Which means that the simple answer to the question of: "is HierarchyID Open Source?" is: No.

If the desire is to see the source code, then you would need to open the DLL -- Microsoft.SqlServer.Types.dll -- with a decompiler. BUT, there are potential legal and/or ethical issues to deal with. This is proprietary code so there are two main points to consider:

as stated in the SQL Server End-User License Agreement (EULA) (and as quoted in @TomV's answer), you can only "reverse engineer, decompile or disassemble" non-provided code "only to the extent that applicable law expressly permits". Meaning that for some folks, it is perfectly legal to do this, but for other folks, not so much. So you might want to consult a local intellectual property lawyer.
if it is legal for you to decompile, the intent of the legal language, plus the fact that some source code is provided (as linked at the top) but this particular code is not, certainly implies that the desire is for the general public to not see or have this code. So being legal to decompile shouldn't be taken as it also being legal to duplicate, but more so that it can be used as a reference (i.e. for debugging, etc).


To get desired results attendance.Date = '2015-03-30' should be part of LEFT JOIN, not WHERE:

SELECT ....
FROM
student
Left JOIN attendance ON student.StudentID = attendance.StudentID
AND attendance.Date = '2015-03-30'


Cannot view sql server agent in sql server 2005?

Minimum rights needed to see the agent node you need to have sysadmin rights on the instance or rights to the agent database roles in msdb.


Monitor disk usage with MongoDB Ops Manager

MongoDB Ops Manager 3.4.0 (released November, 2016) includes monitoring for hardware metrics (cpu, disk usage, and I/O utilization) via the Automation agent. For more information, see: System and Disk Alerts.

Prior versions of Ops Manager did not include hardware monitoring and required the use of an external tool (munin-node) for CPU & disk metrics. If you are still using an older version of Ops Manager and have installed Munin-node for monitoring CPU & I/O stats you could look into using the Munin df plugin to track free disk space. Munin supports sending alerts via email as well as through Nagios and external scripts.


Availability Groups Service Account has over 25000 sessions open

This is most likely because you have enabled the Log to table option in the agent job step.

Run

SELECT spid,
       program_name,
       db_name(dbid) AS databasename
FROM   sys.sysprocesses
WHERE  spid > 50

or if you have sp_whoisactive installed (http://whoisactive.com/)

EXEC sp_whoisactive
     @output_column_list = '[%dd%][session_id][sql_text][%database%][login_name][program_name]' /* if they are sleeping spids, add @show_sleeping_spids = 2*/

Get the uniqueidentifier of the job from the program_name, then run

SELECT name
FROM   msdb.dbo.sysjobs
WHERE  CONVERT(BINARY(16), job_id)=0x[id]

Replace 0x[id] with your id. Once you get the job name, go to the job step, then un-check the log to table option.


Oracle 12.2.0 installation NLS_CHARACTERSET QUESTION

The characterset is provided as part of the CREATE DATABASE statement, not as a parameter.

  Creating a Database: Example

  The following statement creates a database and fully specifies each
  argument:

CREATE DATABASE sample
   CONTROLFILE REUSE
   LOGFILE
      GROUP 1 ('diskx:log1.log', 'disky:log1.log') SIZE 50K,
      GROUP 2 ('diskx:log2.log', 'disky:log2.log') SIZE 50K
   MAXLOGFILES 5
   MAXLOGHISTORY 100
   MAXDATAFILES 10
   MAXINSTANCES 2
   ARCHIVELOG
   CHARACTER SET AL32UTF8
   NATIONAL CHARACTER SET AL16UTF16
   DATAFILE
      'disk1:df1.dbf' AUTOEXTEND ON,
      'disk2:df2.dbf' AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
   DEFAULT TEMPORARY TABLESPACE temp_ts
   UNDO TABLESPACE undo_ts
   SET TIME_ZONE = '+02:00';


Postgres: Database architecture and materialized views

Would it make sense to add the results of my materialized view
  everyday to a table ?

Yes, it makes sense to materialize.

The analysis of large datasets (see: OLAP, dimensional modeling) includes the concept of aggregations - which can be implemented as materialized views. You should design what aggregates will you keep. In my opinion you need at least two:

by (app_id) - full-time history of single app. all-time ranking, trends, etc etc.
by (app_id, day) - daily information for every single app. This can include average daily rankings and all other information that's relevant on this level. can also include trend analysis and al other daily info you can imagine.

You can always calculate higher-level information from lower-lever aggregate. For example if you had an aggregate on (app_id, day, collection_id) you could use it instead of (app_id, day).

You can materialize your aggregates with MATERIALIZED VIEW feature. But this is not an only way. If old data is static, it could be enough to insert new rows daily, with something similar to

INSERT INTO data_by_app_id_day (app_id, day, avg_ranking)
SELECT app_id, tscol::date, avg(ranking)
FROM main_table
WHERE tscol::date = current_date - 1
GROUP BY 1,2;

  Or should I partition that big table and do it another way ?

There is a lot of good sources on to partition or not to partition.

If you are going to store 2 years and more, daily partitions could be optimal.  But remember that very large number of partitions will make query planning longer. Threshold  depends on CPU speed / queries used.

PS. I assume that you ran out of normal ways to optimize:

indexes on all columns used in WHERE clause
partial indexes if needed
expression indexes if needed
optimal data types used
avoiding unnecessary data detail that explodes your dataset size


Configure Postgresql column capitalization with being able to query using different capitalization

As documentation claims unquoted identifiers are case insensitive. If you see that the identifiers are case-sensitive, this means that your application (connection library?) adds identifiers quoting. Configure it properly. - akina

  Is there some kind of setting within PostgreSQL that would allow to use capitalization for the column names while having case-insensitive queries?

You will have to modify at least one of your applications. There is no parameter in PostgreSQL that makes it "partly case sensitive". - laurenz-albe


The valid syntax is close to your second try, but you need to escape the column names with backticks not with single quotes:

ALTER TABLE `blog` CHANGE COLUMN `read-more` `read_more` VARCHAR(255) NOT NULL;


What you describe in your second paragraph sounds like the Entity Attribute Value pattern.

This is a known pattern for dealing with the type of data you are talking about. When you need to be able to be extensible, drive the types from the data and allow you to start tracking new types as you grow. It allows you stay flexible and agile and be a little more forgiving and generic in your database design and schema.

This pattern works, even in relational databases, but it does take more work to get good performance, and can cause you headaches down the line depending on how many rows, how many inserts you are doing and how frequently you query it. This SO Question talks about some of the pitfalls nicely in the firs answer listed with 20 votes. I have seen this model work well, especially in environments where the new types and entities really do come in and we want to let that be more application driven or user driven but we were fighting with performance more often in that model than a traditional relational database model but we needed the flexibility and we made it work. Definitely look at that SO question and do some more research on EAV and whatever DBMS you are using to find examples from others who have tried the same.


Connect to SQL Server without specifying port

If you have named instances and don't want to declare your ports then you need to ensure that SQL Server Browser is running

By default this runs through port UDP 1434


SQL Server database level roles for creating tables

If you want to create a role with specific rights, you could do this:

CREATE ROLE CreateObjects
GRANT CREATE TABLE TO CreateObjects
GRANT CREATE VIEW TO CreateObjects
GRANT CREATE FUNCTION TO CreateObjects
GRANT CREATE PROCEDURE TO CreateObjects
GRANT ALTER ANY SCHEMA TO CreateObjects

Create the test user:

CREATE LOGIN testlogin with password = 'StrongP@SSWORD123'

CREATE USER Testlogin FOR LOGIN Testlogin

Add the user to the role:

ALTER ROLE CreateObjects ADD MEMBER Testlogin

Test the user's permissions:

EXECUTE AS LOGIN = 'testlogin'

SELECT SUSER_NAME(),USER_NAME()

(No column name)    (No column name)
testlogin   Testlogin

CREATE TABLE dbo.test(id int)

DROP TABLE dbo.test
REVERT

Result

Commands completed successfully.

Or like Tom stated, add the user to the db_ddladmin role.

ALTER ROLE db_ddladmin ADD MEMBER testlogin


Securing Your MySQL Server within your team?

Some best practices:

Create a DBA user for yourself. This should be the only user with "WITH GRANT OPTION" in their permissions. This should also be the only user with select privileges on *.* because that includes mysql.user.
Every user should have their own username and password, no shared accounts. They should each have permissions to specific databases; again, not *.*.
And then delete that shared root user.
Don't let non-DBAs have root access on your db server.
Symlink your ~/.mysql_history file to /dev/null and it won't record anything.

There are others; this will get you to a better place.

You can also write a little script that outputs the grants (e.g., using pt-show-grants) and diffs to the previous output, and then emails you when there's a difference.

btw, it's hard to have enough space to store a general log on an active production system. It can get very big very quickly.


How to make TokuDB engine on Percona delete faster

If your table has 1 or more secondary indexes than each delete requires a read on the primary key index to get the fields necessary for the deletes in each secondary, which eliminates much of the TokuDB advantage over InnoDB. Also, if your delete pattern is "left-most" or you are performing a large number of deletes in a specific key range you will likely end up with quite a bit of garbage in your Fractal Tree indexes which will need to be cleaned up by optimizing the table/indexes.