Find which SESSION_CONTEXT key-value pairs was set I found that the following query shows how many session context variables was set:

SELECT cache_address
      ,name
      ,pages_kb
      ,pages_in_use_kb
      ,entries_count
      ,entries_in_use_count
FROM   sys.dm_os_memory_cache_counters
WHERE  TYPE = 'CACHESTORE_SESSION_CONTEXT';

But now I would like to know which key-value pairs of SESSION_CONTEXT() was set -- if any.

After doing extensive research on microsoft sites I didn't found anything related.

Thanks in advance.

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.


WAL archiving w/ streaming replication I'm running postgresql 9.3. I've currently got WAL archiving configured, but I'm doubting it's utility based on my configuration. On the master, I've got archive_command set to

  rsync -az --ignore-existing %p postgres@slave_replica:/wal_archive

and on the slave replica, I've got restore_command set to

  cp /wal_archive/%f %p || exit 0

If the slave server goes offline, it won't be able to receive the WAL files, and then when it comes back up, won't have what it needs.

Should I instead effectively reverse those two commands: archive the WALs locally on the master, and have slave replica rsync them from the master server? Or should I have the master archive the WALs both to a local directory and rsync them to the slave?

If the slave server is offline, the rsync in the archive_command should fail (exit with a non-zero exit value), which means the master will keep retrying it until it succeeds.  It will not remove a pg_xlog file until it is successfully archived.  So it will still be there when the slave comes back on online.

Also, the || exit 0 at the end of your restore command is just wrong.  PostgreSQL will ask for files which do not exist, and has to be told they don't exist (by returning a non-zero exit code)


Generic type functions in T-SQL Consider the following function

CREATE FUNCTION dbo.SafeDivide
(
    @a numeric,
    @b numeric
)
RETURNS numeric
AS
BEGIN
    RETURN CASE
               WHEN @b = 0
                   THEN 0
               ELSE @a / @b
           END;
END;

Here type numeric was used but this code would be useful for any numeric types with any precision.

Is there a way to define and use such function?

I tried sql_variant type but it complains

Operand data type sql_variant is invalid for divide operator

The error says what to do, that sql_variant type does not accept to operate the divide function.

I would suggest to use CONVERT/CAST to a specific data type, depending on your usage.
Below I will use float:

DROP FUNCTION IF EXISTS dbo.SafeDivide
GO
CREATE FUNCTION dbo.SafeDivide
(
    @a sql_variant,
    @b sql_variant
)
RETURNS sql_variant
AS
BEGIN
     RETURN CASE
               WHEN @b = 0
                   THEN 0.00
               ELSE Convert(float, @a) / Convert(float, @b)
           END;
END
GO
/*
select dbo.SafeDivide(4,4.4622434)
*/


Replica model in Cassandra vs Mongo? Say in Mongo we have one primary and two replica node(simple replica no sharding). Read and write request will be handled only primary node
and replica will just backup the data in case of failover.

My understanding/question about replication(not sharding) in Cassandra

Can we have only one node(say Node_1) serving read and write requests while other two simply working as replica nodes(these nodes just takes the replica request from Node_1 , no real time request from user ) like in Mongo considering replication factor as 3 ?

Per my understanding this should not be possible in cassandra, as in cassandra , replica nodes are nodes are equal peers , so it is mandatory that any of three nodes has to serve write and read request.

Is my understanding correct ?

Yes, your understanding is correct - this allows evenly distribute load between nodes & to handle more traffic.

Why would you need replicas if they just idle? If you're afraid of inconsistent data, then just set correct consistency levels to get strong consistency...


Linked SQL Server Issue I have 3 servers all running Windows Server 2016 Datacenter:

Server1 is a domain controller and has ssms installed for accessing SQL Server Instances on Server2 and Server3

Server2 & Server3 are running SQL Server Microsoft SQL Server 2016 (SP1-CU3)

Server2 has a linked server link to Server3

Server3 has a linked server link to Server2

If I log onto server2 and run a select query which uses the link to server3

Select top 5 * from [Server3].[DB3].[dbo].[TableName]

It runs fine and results are returned.

If I log onto server3 and run a select query which uses the link to server2

Select top 5 * from [Server2].[DB2].[dbo].[TableName]

It runs fine and results are returned.

But If I log onto Server1 and open ssms, connect to Server2 and run query

Select top 5 * from [Server3].[DB3].[dbo].[TableName]

I receive error 'Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.'

And the same happens if I log onto Server1, open ssms, connect to server3 and run query

Select top 5 * from [Server2].[DB2].[dbo].[TableName]

I am logging onto all 3 servers using the same domain credentials, but it feels to me like if im running the query from Server1, then its not passing my credentials through causing the query to be ran against the target sql instance as NT AUTHORITY\ANONYMOUS LOGON

This is the classic "Double Hop" problem.  Several pieces need to be in place for this to work. For server1 (SSMS) -> server2 (SQL) -> server3 (sql)

server2 must have a SPN
server3 must have a SPN
server2 must be allowed to delegate permissions to server3

A SPN (Server Principal Name) is set in Active Directory on an account and basically ties a service to that account.  e.g.  MSSQL Server on SERVER2 is running as domain\sql_server.  When you connect with Kerberos, You request a ticket from the DC to connect to "MSSQL Server on SERVER2".  The DC replies with a ticket is can only be un-encrypted by domain\sql_server.
SQL Server will attempt to self-register an SPN when it starts.  This is logged in the ERRORLOG

The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/MYLAPTOP:1433 ] for the SQL Server service.
The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/MYLATOP ] for the SQL Server service.
SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. This is an informational message. No user action is required.

Verify that you can connect from server1 to server2/3 using Kerberos authentication:

remote into server1
connect using SSMS to server2
Run this:

select auth_scheme from sys.dm_exec_connections where session_id =
@@spid

If it returns NTLM, then you are either missing the SPN on server2 or you have a duplicate SPN.  If it returns "Kerberos" then congratulations, you're partially there.

Repeat this test by trying to connect from server1 to server3.

Once you can successfully connect to both server2 and server3 using Kerberos, you need to ensure that the accounts that SQL is running as have permissions to delegate.  This is an active directory setting that is on the service account SQL is running as.


Physical memory usage approaching 100%(96%) Possible Duplicate:
  SQL Server 2008 R2 “Ghost Memory”?

Context:

Windows Server 2008 R2;
32GB RAM;
Application: SQL Server 2008 R2, several Connectors to Write Data into SQL Server(Write Frequency: 0.5HZ);
The Server has not been restart for more than half a year;
About 15G Data per month;
Support Several Applications hosted on other Server(Web Applicaion, Socket Application);

Edit:

Problem(From the Task Manager): In the Performance Tab, The Memory Display and the Physical Memory Usage History Chart both show that the memory usage is approaching 100%(96%); While in the Process Tab, in which the Memory(Private Working Set) Column is shown by default, and the Memory(Private Working Set) Usage of sqlservr.exe is realtively small(200-300M), the total Memory(Private Working Set) usage of all the Processes added up is normal as well(less than 10%).
Maybe the problem is that I just check the Memory(Private Working Set) Column, but I don't have access to the Server right now

is there some kind of memory-leak problems here?

No, the problem is more someone who does not know how SQL Server works.

SQL Server uses as much memory as feasible as cache for the database. Why not use it? So, what happens basically is that SQ LServer has a lot of data in memory in case it needs it, to avoid accessing the disc.

If you need more free memory, set a memory limit in SQL Server properties.

If oot - be happy, your memroy is used.


How to fix slow LIMIT queries with big offsets without heavily rewriting them? Imagine a very basic example of your average discussion board. For example:

CREATE TABLE threads (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    title VARCHAR(100),
    PRIMARY KEY (id)
)

CREATE TABLE replies (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    thread_id INT UNSIGNED NOT NULL,
    text TEXT NOT NULL,
    PRIMARY KEY (id)
    INDEX thread_id (thread_id),
)

There can be very lengthy discussions (people love bikeshedding!), maybe 100k or 200k replies per thread and users can read them paginated (the number of replies per page is variable, depending on user preferences, but if needed for the solution it can be limited to a fixed set). These tables might have ~40 million replies and ~2 million threads.

So you might end running this query to get the last replies of a thread:

SELECT * FROM replies
WHERE thread_id = 1234
ORDER BY id ASC
LIMIT 125400,10 /* whoops */

Which, as you know, is quite slow since MySQL has to walk 125,400 rows just to get there and return your 10 rows.

Hacky solutions I've thought:

Create a secondary index which assigns an incrementing number for each chunk of N posts (for example, a new field in the replies table which for the first 1000 posts contains 1, for the following 1000 it contains 2, etc).

I have to heavily modify the application, since there are tons of queries that read the replies table, it's not just a simple SELECT here and there and I really don't want to cripple and reengineer each query of the application.
It would force me to recalculate each time that I delete a reply or when I do other destructive operations (splitting, merging, etc).

For each link to the next page, attach the ID of the next post. That way the database can go directly to the row using the primary key of the replies table.

This is a web application, so this solution would have tricky SEO implications which I'd prefer not to deal with.

I might be dreaming here (and if so please do tell me!) but is there a solution that resides (almost) exclusively in the database and allows me to fix this problem without heavily modifying the application?

I've read about MySQL partitions, but I'm not sure they would help here.

You can add a new column to replies, call it position, and fill it with consecutive numbers of replies per thread (the position of the reply in the thread).

For example

id | thread_id | text | position
 1 |         1 | .... | 1
 2 |         2 | .... | 1
 3 |         1 | .... | 2
 4 |         1 | .... | 3
 5 |         2 | .... | 2
 6 |         3 | .... | 1

Further put an index on (thread_id, position, id) and it allows you to write queries like

SELECT * FROM replies
WHERE thread_id = 1234
AND position BETWEEN 125400 AND 125410
ORDER BY id ASC

which runs fast, since this does not need a full index scan.

You can either update this column in your application, or write a database trigger to do this automatically.

The initial effort is quite high I admit. We used this trick a few years ago on a high write frequented, quite large table, and like I said it cost some effort to get it running, but when the solution was in place, the performance gain was overwhelming.


What is the best way to analyze free space in a postgres database and the grow of usage? In Oracle you can in datadictinary analyze free space by using SQL like:

select
   b.tablespace_name, b.file_id, b.file_name, b.bytes/1024 total_KBytes,
   sum(a.bytes)/(1024) sum_free_Kbytes, max(a.bytes/(1024)) max_free_Kbytes
from sys.dba_free_space a, sys.dba_data_files b
where a.file_id = b.file_id
group by b.tablespace_name, b.file_id, b.file_name, b.bytes
order by b.tablespace_name;

How can I do this in postgres? What are the best tools to view and monitor database space in postgres?

Postgres' tablespace concept is completely different to Oracle's. Postgres doesn't use container files the way Oracle does. Each table is stored in one or more individual files. A tablespace is essentially just a directory where files are stored. It is not container file like in Oracle.

If a table gets dropped or truncated the space is immediately released on the file system level.

For a single table you could measure the size of the file(s) on disk and the actual size of the data which can deviate substantially depending on the write pattern of the application. This is caused by Postgres' implementation of MVCC.

The Postgres Wiki has several queries to investigate that:
https://wiki.postgresql.org/wiki/Show_database_bloat


Using views as a source for staging table? I am in the process of trying to build a small datamart for my small business. I am planning on initially copying tables from the source DB to another DB for staging, where I can then do any data manipulation required to create fact and dimension tables.

The documentation for my primary business application recommends using the preset views when building any custom reports, as they will not change from release to release. Although I'm not really building custom reports, I'm wondering if the same logic holds here? Should/Could I use them for building a datamart?

I don't know a lot about SQL, but from what I've read, views aren't really tables, but more like preset queries. If I did decide to use them, and was running a stored procedure from the staging database which called for data referenced by that view, does this present a performance issue (as I know that views can require a lot of joins). I suspect that the answer here is to use the original tables instead of any views, but I would like confirmation. Btw, I'm using SQL Server. Thanks.

As has been noted in the comments, without more details, it's hard to give detailed help.  That said, I an provide some general advice.

If you're considering using vendor-supplied views to extract data for your datamart, you're first question shouldn't be about performance. Your first question should be, "Do the views have all the data I need?" You've probably already considered this, but the data you need is your driving force.

Second - if the source database resides in a different SQL Server instance, your data is being accessed remotely, a view may be a good option. If you access the data through, for instance, a linked server, hitting the tables directly may result in having to pull all the table data over to the active server, and then performing the joins. By hitting a view, you ensure that the joins will be processed on the source server. Now, there's nothing saying that the vendor-supplied views are the best possible views for your needs, but a view may be the best solution ultimately.


Graph Database with "non-graph" data --- Update ---
Thanks for the comments and the help so far. And I apologize for not specifying the question further. I have updated the question below.
--- Update ---

Currently I am asked to develop a database structure for a pretty big amount of data. I am debating between implementing a graph database instead of a "normal" relation database, and was wondering what the disadvantages are if the data does not necessarily contain any relationships? Could separate, unconnected, nodes be utilized the same way as a table / row in a table?

I am asking this because as of right now there are is no need for relationships but I am trying to future proof the database (with the anticipation of relationships) to expand the abilities of the data. If it is of any help, I am debating between OrientDB/Neo4j or mySQL/postgreSQL.

An example:
Let say we have a database full of stocks. Stock can be bought and sold at pretty much any time / day by anyone (as long as the market is open). Now this database could be a normal relation database: Table 1: IDs | Products | Prices | Sizes | Dates. But could potentially also be organized as a relationship database Node 1: Stock A | Node 2: Stock B.
If I just use the database for storing stock information, it seems to me a normal database will be better. But is this true? Will it negatively affect me working with a relationship database / will I be better off working with a normal database? Are there disadvantage to organize my data in nodes, instead of rows?

A picture to illustrate it all:

The database will mainly be used for products bought and sold similar like stocks, however they also have other information such as locations attached to them. I am trying to anticipate the implementation of features such as recommending products or even the prediction of next products to be bought by somebody.

Most of the queries from the database will be per product.
Pulled from the database: 100 to 1000 times a day.
Pushed to the database: 20.000 a day.

An added question which might reveal a disadvantage:
How easy will it be to go from relation-to-graph or from graph-to-relation. Any lock-in dangerous?

Thanks for all the help, comments have been great so far!
R

I think you are making a fairly common mistake here by selecting NoSQL technologies in order to be "future-proof" without understandng the tradeoffs.  If in doubt start with PostgreSQL and figure you can always set up a graph db or other NoSQL db on the side as you need it.  Also you can do graph traversal in PostgreSQL but keep in mind you are working with sets and so that's very, very different from a graph db with different benefits and drawbacks.

Your basic tradeoff is between flexibility of data entry and an ability to flexibly utilize data on output.  NoSQL db's (including graph db's) sacrifice the latter for the former and because very often you need an ability to run ad hoc queries on sets of data you probably want an RDBMS somewhere.  This means if you have a good data model, you should be able to add adjunct db servers to your environment for special purposes of your RDBMS won't handle it.

Nonetheless, there are a couple specific reasons why if you are thinking at some point of adding various NoSQL solutions why I would recommend starting with PostgreSQL instead of MySQL and these have to do with performance of NoSQL-friendly database structures and the fact that PostgreSQL supports WITH RECURSIVE common table expressions.  This approach (WITH RECURSIVE CTE's) allows things like hierarchies and graphs to be traversed using recursive SQL statements which can repeatedly traverse paths.  These are also relatively efficient for a set-based approach, and so if you end up with a lot of part/subpart modelling problems you may be able to do that directly in PostgreSQL without too much work.  If this doesn't work however, you can also more easily represent the data in a way which is easier to import into your NoSQL db.  So it gets you further and it integrates better.  You may even be able to set up foreign "tables" against your NoSQL db and run queries against it from inside Pg!

More on the Tradeoff 1

Graph databases arose from a need to do binary traversal quickly over large sets of information.  The obvious example might be a social network site like LinkedIn which might want to tell you quickly how far removed you are from another user (in simple terms this means essentially they are designed to play "six degrees of Kevin Bacon").  Typically on a graph database you are essentially traversing nodes using what are often described as "three word sentences" which represent the graphs.  In this regard you are looking at essentially something like "John friended Jane" and traversing this way.  Typically the API is relatively navigational.

A relational database is really designed to work with sets of information.  Typically when giving up on relational databases, one is giving up on set operations too.  This can be a big deal.  Often it is faster both operationally and in terms of development time to be able to do set operations rather than navigate, aggregate, and report.  This is a huge difference and if your use case fits a relational workflow, I have trouble imagining that a graph db will help you.

More on the tradeoff 2:  Consistency Models

A second thing to investigate and keep in mind is the question of what consistency model your database uses.  Even with "ACID-Compliant" RDBMS's there is a range of consistency models based on different levels of transaction isolation that can be used to prevent problems.  If your data is important, the standard RDBMS consistency models are going to fare much better than most of the NoSQL models because they make more guarantees both to the DBA and to the application.  Graph databases are a relatively large field, and each vendor may have some differences in their consistency model.  This isn't necessarily something that will rule the use out, but it is something to be cautious about when looking at the solutions as a whole.

Note that schema flexibility is both a blessing and a curse in the NoSQL world and this also affects graph databases.  It is a blessing because you can get up and running in the initial stages faster, but it is a curse because the set operation strengths of an RDBMS depend on a fixed schema, and those that relax that to some extent (Informix, for example, supports jagged rows in return sets) require programmer knowledge of the places where those requirements have been relaxed.


Can you run two WSFC instances on two servers where each node is the primary for a different availability group? I'm drawing up a proposal for upcoming infrastructure changes. This will include a production server and reports/data warehouse server, each with Always On. To keep hardware and licencing costs down, is it possible to run in a configuration of Server-A running Prod-AG Primary and Rep-AG Secondary, and Server-B running Rep-AG Primary and Prod-AG Secondary?

I presume each server would need 2x of the following WSFC instances,sql instances, AG's, listeners, DNS names/ports.

I hope this makes sense, here's a diagram of what I think it will looks like.

In the case of a fail-over on either node, the workload/business need isn't that great that running off the same server for a couple of hours would be a major issue.

I've only found a couple of mentions of a similar setup that kind of worked but no definitive information from Microsoft or anyone who's successfully ran this setup.

SQL Edition will be 2017, most likely standard, I don't think we'll be approved for Enterprise. OS will be Windows Server 2016 Core.

Yes, you can have more than one availability group on a cluster.  Each availability group is completely independent of the others, and can be failed over to any node in the cluster separately.  From Configure SQL Server 2012 AlwaysOn Availability Groups for SharePoint 2013:

  Because SQL Server 2012 can host multiple availability groups on a single server, you can configure AlwaysOn to fail over to SQL Server instances on different servers. This reduces the need to have idle high performance standby servers to handle the full load of the primary server, which is one of the many benefits of using availability groups.

The is of course relevant regardless of whether SharePoint is involved.

You will not need additional instances--just one default instance of SQL Server on each node.  And I think you may be confused about WSFC instances.  You simply set up the cluster, install SQL Server on each node, and then create the availability groups.  When you create the AGs, the setup process will create all of the cluster roles and resources that are required.

Search YouTube and watch one being set up and I think that will make things clearer (https://www.youtube.com/watch?v=VKCqRgqLAuo).  The documentation gets murky because it continually refers to WSFC instances and availability groups, but if you're using straight AGs, you aren't going to be using WSFC instances.  It makes it appear far more complex than it is.


Modify XML: attributes into elements I have an XML column that contains data with similar structure:

<Root>
    <Elements>
        <Element Code="1" Value="aaa"></Element>
        <Element Code="2" Value="bbb"></Element>
        <Element Code="3" Value="ccc"></Element>
    </Elements>
</Root>

How can I modify the data using SQL Server to change each Value attribute into an element?

<Root>
    <Elements>
        <Element Code="1">
            <Value>aaa</Value>
        </Element>
        <Element Code="2">
            <Value>bbb</Value>
        </Element>
        <Element Code="3">
            <Value>ccc</Value>
        </Element>
    </Elements>
</Root>

Update:

My XML looks more like this:

<Root attr1="val1" attr2="val2">
    <Elements>
        <Element Code="1" Value="aaa" ExtraData="extra" />
        <Element Code="2" Value="bbb" ExtraData="extra" />
        <Element Code="3" Value="ccc" ExtraData="extra" />
        <Element Code="4" Value="" ExtraData="extra" />
        <Element Code="5" ExtraData="extra" />
    </Elements>
    <ExtraData>
       <!-- Some XML is here -->
    </ExtraData>
</Root>

I would like only to move Value attribute and preserve all other attributes and elements.

You can shred the XML and rebuild it again using XQuery.

declare @X xml = '
<Root attr1="val1" attr2="val2">
    <Elements>
        <Element Code="1" Value="aaa" ExtraData="extra" />
        <Element Code="2" Value="" ExtraData="extra" />
        <Element Code="3" ExtraData="extra" />
    </Elements>
    <ExtraData>
       <!-- Some XML is here -->
    </ExtraData>
</Root>';

select @X.query('
  (: Create element Root :)
  element Root
    {
      (: Add all attributes from Root to Root :)
      /Root/@*,
      (: create element Elements under Root :)
      element Elements
        {
          (: For each Element element in /Root/Elements :)
          for $e in /Root/Elements/Element
          return
            (: Add element Element :)
            element Element
              {
                (: Add all attributes except Value to Element :)
                $e/@*[local-name() != "Value"],

                (: Check if Attribute Value exist :)
                if (data($e/@Value) != "")
                then
                  (: Create a Value element under Element :)
                  element Value
                  {
                    (: Add attribute Value as data to the element Element :)
                    data($e/@Value)
                  }
                else () (: Empty element :)
              }
          },
      (: Add all childelements to Root except the Elements element :)
      /Root/*[local-name() != "Elements"]
    }');

Result:

<Root attr1="val1" attr2="val2">
  <Elements>
    <Element Code="1" ExtraData="extra">
      <Value>aaa</Value>
    </Element>
    <Element Code="2" ExtraData="extra" />
    <Element Code="3" ExtraData="extra" />
  </Elements>
  <ExtraData>
    <!-- Some XML is here -->
  </ExtraData>
</Root>

If Elements is not the first element under Root the query need to be modified to add all elements before Elements first and all elements after Elements after.


Selecting the last record that meets a condition I have a simplified table like this:

id |val |
---|----|
1  |6   |
2  |5   |
3  |4   |
4  |4   |
5  |8   |
6  |9   |
7  |7   |

I want to select the records in a way that where val < 5 the last record where val >= 5 is selected. So the result should look like this:

id |val |
---|----|
1  |6   |
2  |5   |
3  |5   |
4  |5   |
5  |8   |
6  |9   |
7  |7   |

I was wondering if this is possible without defining a function, so I tried:

SELECT
CASE
    WHEN val < 5 THEN LAG(id) OVER (ORDER BY id)
    WHEN val >= 5 THEN id
END id,
CASE
    WHEN val < 5 THEN LAG(val) OVER (ORDER BY id)
    WHEN val >= 5 THEN val
END val
FROM test;

but it only returns:

id |val |
---|----|
1  |6   |
2  |5   |
3  |5   |
4  |4   |
5  |8   |
6  |9   |
7  |7   |

Is is possible to write this query without defining a function? Or would it be better with a function?

EDIT: A combined solution

For the sake of completeness, although it slightly differs from the original problem description, I include that if the answers from @Andriy M and @Hogan are combined then NULL values will be preserved and only values below the threshold will be updated with the last valid value. In some cases it is desirable to keep the NULL values. Thus the query would be like this:

SELECT
    id,
    val,
CASE WHEN val < 5 THEN (SELECT sub.val
                        FROM test AS sub
                        WHERE sub.id <= main.id
                            AND sub.val >= 5
                        ORDER BY id DESC
                        LIMIT 1)
ELSE val END AS newval
FROM test AS main;

Which returns:

id |val |newval |
---|----|-------|
1  |6   |6      |
2  |5   |5      |
3  |4   |5      |
4  |NULL|NULL   |
5  |8   |8      |
6  |9   |9      |
7  |7   |7      |

Prior answer was wrong as pointed out in the comments -- this works:

SELECT id, val,
       CASE when val < 5 then (
            select max(val) from t as tsub where tsub.id < t.id)
       else val end as newval
FROM T

have to use a sub query to find the prior max.


Incorrect syntax near 'role' with generated script I've recently had to recreate a user several times.
To facilitate the creation of the user, I would right-click the user and choose script login as, create to, New Query Editor Window.

However, after doing this I receive a syntax error:

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

What is going on here?

You need to make sure your scripting options are set to target the right version. You can do this in Tools / Options / Object Explorer / Scripting / Script for server version. In your case, you should pick 2008 (it is the umbrella for both 2008 and 2008 R2), though 2005 will probably work as well.

The challenge is that if you move to the SQL Server 2016 version of SSMS, some of this backward compatibility is broken (and in fact it may also be broken in the version you're using now, I haven't tested lately, but if so, it's less likely that it will be fixed):

Connect #1870405 : [SSMS] - CTP 2.4 - Can't script certain downlevel options

The main reason you can't script this specific login's role for 2008 is that CREATE SERVER ROLE - in fact the ability to create a user-defined server role in the first place - was introduced in SQL Server 2012. So you will have to script the login without the role if you want to deploy to some version < 2012.

UPDATE: Microsoft just added a comment to the Connect item, indicating that it will not be fixed, and why:

  Hello Aaron,

  We've decided to not change the current behavior of the scripting.

  1. For the "Script As" scenario from OE this is intended as the syntax to create/alter/drop server roles didn't exist prior to 2012, which is why the exception is throw (there's nothing that can be generated that would be valid)

  2. For the Properties -> Script scenario that is also intended. The script button is intended to generate the script that would be ran if the "OK" button is pressed, and so it doesn't take the current SSMS scripting options into account.

  I will be updating the error message displayed for #1 though to more clearly indicate what the problem is.


MySQL - Creating sort index and update query takes forever I have the following MySQL query

UPDATE PCL AS p
JOIN HST AS h
ON h.SrNo =
   ( SELECT hi.SrNo
     FROM HST AS hi
     WHERE hi.SYM = p.INST
       AND hi.DATE >=  p.Date
       AND hi.HP >= p.T1
     ORDER BY hi.Date
     LIMIT 1
   )
SET p.T1A = h.Date
WHERE p.BS = 'B'
AND p.SrNo <18000;

However, this query takes over 3 hours to execute, SHOW FULL PROCESSLIST shows "Creating sort index" and almost 95% of the time it is creating sort index.

EXPLAIN SHOWS

Select type: DEPENDENT SUBQUERY
table: hi
type: index
possible keys: NULL
key: hst_idx_sym_date_hp
key_len: 32
ref: NULL
rows: 1002718
extra: Using where; Using index; Using filesort

I also tried the below update queries using subquery, but they are even slower

UPDATE PCL
SET `T1A` = (SELECT HST.`Date` from `HST` WHERE HST.`SYM`=PCL.INST AND
HST.`DATE` >=  PCL.Date AND HST.`HP` >= PCL.T1 order by HST.`Date` limit 1)
WHERE `BS` = 'B' AND SrNo <18000;

UPDATE PCL
SET `T1A` = (SELECT MIN(HST.`Date`) FROM `HST` WHERE HST.`Sym`=PCL.INST AND
HST.`DATE` >=  PCL.Date AND HST.`HP` >= PCL.`T1`)
WHERE `BS` = 'B' AND SrNo <18000;

Is there a way to optimize this query so that it executes faster?

Here is the SHOW CREATE TABLEs

CREATE TABLE `PCL` (
`SrNo` int(6) NOT NULL AUTO_INCREMENT,
`Date` date DEFAULT NULL,
`INS` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`T1` float(8,2) DEFAULT NULL,
`T1A` date DEFAULT NULL,
 ......
PRIMARY KEY (`SrNo`)
) ENGINE=InnoDB AUTO_INCREMENT=16893 DEFAULT CHARSET=latin1
and SHOW CREATE TABLE HST

CREATE TABLE `HST` (
`SrNo` int(11) NOT NULL AUTO_INCREMENT,
`SYM` varchar(20) DEFAULT NULL,
`Date` date DEFAULT NULL COMMENT 'YYYY-MM-DD',
`HP` float(8,2) DEFAULT NULL,
....
PRIMARY KEY (`SrNo`),
UNIQUE KEY `HST_idx_SYM_date_OP` (`SYM`,`Date`,`OP`),
UNIQUE KEY `HST_idx_SYM_date_CP` (`SYM`,`Date`,`CP`),
UNIQUE KEY `HST_idx_SYM_date_PC` (`SYM`,`Date`,`PC`),
UNIQUE KEY `HST_idx_SYM_date_LP` (`SYM`,`Date`,`LP`),
UNIQUE KEY `HST_idx_SYM_Date` (`SYM`,`Date`) USING BTREE,
UNIQUE KEY `HST_idx_SYM_date_HP` (`SYM`,`Date`,`HP`) USING BTREE,
KEY `HST_idx_date` (`Date`)
) ENGINE=InnoDB AUTO_INCREMENT=1103583 DEFAULT CHARSET=latin1 COMMENT='HST
Data - All'

You have joined HST on PCL but ON condition refers to the HST itself and there is no relation between HST and PCL. The table PCL is fully scanned and the only restriction used is the WHERE p.BS = 'B' AND p.SrNo <18000 that has no corresponding index.

UPDATE

  I want to fetch the date when the value of HST.HP first exceeds or equals PCL.T1.

UPDATE PCL AS pp
  JOIN ( SELECT h.Date AS date_the_value_first_exceeded
           FROM PCL AS p
           JOIN HST AS h  ON h.Date = p.Date      -- Joining tables by dates
          WHERE h.HP >= p.T1                      -- The condition you want
          ORDER BY p.Date ASC                     -- Order the result chronologically
          LIMIT 1                                 -- Single line required
       ) AS dd                                    -- Unrestricted join, each to each
   SET pp.T1A = dd.date_the_value_first_exceeded
 WHERE pp.BS = 'B'
   AND pp.SrNo < 18000
;

The version with MIN() function:

UPDATE PCL AS pp
  JOIN ( SELECT MIN(h.Date) AS date_the_value_first_exceeded
           FROM PCL AS p
           JOIN HST AS h  ON h.Date = p.Date      -- Joining tables by dates
          WHERE h.HP >= p.T1                      -- The condition you want
       ) AS dd                                    -- Unrestricted join, each to each
   SET pp.T1A = dd.date_the_value_first_exceeded
 WHERE pp.BS = 'B'
   AND pp.SrNo < 18000
;


designing a simple database Suppose I have a website, and I want to add "Search a part" button, and there are two options "Dirt" or "ATV-UTV", and if for example the user choose "Dirt" to he need to choose "Beta" or "Gas" or "Honda", and if he choose "ATV-UTV" he should choose "Suzuki" or "mazda"

                                    [Search a part]
                                    /              \
                                   /                \
                                [Dirt]             [ATV-UTV]
                                /  |  \             /      \
                               /   |   \         [Suzuki]   [mazda]
                            [Beta][Gas] [Honda]

Lest's suppose that the user chose Search a part->Dirt->honda then I want to display the user all the available parts for this "Dirt Honda"

      -------------------------------------------
      |Part No| Price| Stock status| Description|
      -------------------------------------------
      | 56    | 25$  | In stock    | Repair Kit |
      -------------------------------------------
      | 21    |76$   | In stock    | Bearing Kit|
      -------------------------------------------
      | 99    | 54$  | Not In Stock| Repair Kit |
      -------------------------------------------

What's the best way for designing the DB for my website? I mean, how many tables should I build? Should I do 5 tables? on for "Beta" one for "Gas", one for "honda" and also for "Suzuki" and "mazda"?

Just need help on how should I design it, thank you for help


Case with multiple conditions I need to change returned value, from select statement, based on several conditions. I tried something like that:

,CASE i.DocValue
  WHEN 'F2' AND c.CondCode IN ('ZPR0','ZT10','Z305') THEN c.CondVal
  ELSE 0
END as Value

why it is not working and what is recommended way to do this ?
There will be next several WHEN conditions.

Thank you

,CASE WHEN i.DocValue ='F2' AND c.CondCode IN ('ZPR0','ZT10','Z305') THEN c.CondVal
  ELSE 0
END as Value

There are two types of CASE statement, SIMPLE and SEARCHED.

You cannot evaluate multiple expressions in a Simple case expression, which is what you were attempting to do.

-- Simple CASE expression:
CASE input_expression
     WHEN when_expression THEN result_expression [ ...n ]
     [ ELSE else_result_expression ]
END
-- Searched CASE expression:
CASE
     WHEN Boolean_expression THEN result_expression [ ...n ]
     [ ELSE else_result_expression ]
END

Example of Simple CASE:

CASE x
  WHEN 'a' THEN 'b'
  WHEN 'c' THEN 'd'
  ELSE 'z'
END

Example of a Searched CASE:

CASE
  WHEN x = 1 AND y = 2 THEN 'a'
  WHEN x = 2 AND y = 1 THEN 'b'
  ELSE 'z'
END

Further Reading: http://msdn.microsoft.com/en-us/library/ms181765.aspx


Why does a query run slower in a Stored Procedure than in the Query window? I have a complex query which runs in 2 seconds in the query window, but about 5 minutes as a Stored Procedure. Why is it taking so much longer to run as a stored procedure?

Here's what my query looks like.

It takes a specific set of records (identified by @id and @createdDate), and a specific time frame (1 year starting from @startDate) and returns a summarized list of letters sent and estimated payments received as a result of those letters.

CREATE PROCEDURE MyStoredProcedure
    @id int,
    @createdDate varchar(20),
    @startDate varchar(20)

 AS
SET NOCOUNT ON

    -- Get the number of records * .7
    -- Only want to return records containing letters that were sent on 70% or more of the records
    DECLARE @limit int
    SET @limit = IsNull((SELECT Count(*) FROM RecordsTable WITH (NOLOCK) WHERE ForeignKeyId = @id AND Created = @createdDate), 0) * .07

    SELECT DateSent as [Date]
        , LetterCode as [Letter Code]
        , Count(*) as [Letters Sent]
        , SUM(CASE WHEN IsNull(P.DatePaid, '1/1/1753') BETWEEN DateSent AND DateAdd(day, 30, DateSent) THEN IsNull(P.TotalPaid, 0) ELSE 0 END) as [Amount Paid]
    INTO #tmpTable
    FROM (

        -- Letters Table. Filter for specific letters
        SELECT DateAdd(day, datediff(day, 0, LR.DateProcessed), 0) as [DateSent] -- Drop time from datetime
            , LR.LetterCode -- Letter Id
            , M.RecordId -- Record Id
        FROM LetterRequest as LR WITH (NOLOCK)
        INNER JOIN RecordsTable as M WITH (NOLOCK) ON LR.RecordId = M.RecordId
        WHERE ForeignKeyId = @id AND Received = @createdDate
            AND LR.Deleted = 0 AND IsNull(LR.ErrorDescription, '') = ''
            AND LR.DateProcessed BETWEEN @startDate AND DateAdd(year, 1, @startDate)
            AND LR.LetterCode IN ('a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o')
    ) as T
    LEFT OUTER JOIN (

        -- Payment Table. Payments that bounce are entered as a negative payment and are accounted for
        SELECT PH.RecordId, PH.DatePaid, PH.TotalPaid
        FROM PaymentHistory as PH WITH (NOLOCK)
            INNER JOIN RecordsTable as M WITH (NOLOCK) ON PH.RecordId = M.RecordId
            LEFT OUTER JOIN PaymentHistory as PR WITH (NOLOCK) ON PR.ReverseOfUId = PH.UID
        WHERE PH.SomeString LIKE 'P_'
            AND PR.UID is NULL
            AND PH.DatePaid BETWEEN @startDate AND DateAdd(day, 30, DateAdd(year, 1, @startDate))
            AND M.ForeignKeyId = @id AND M.Created = @createdDate
    ) as P ON T.RecordId = P.RecordId

    GROUP BY DateSent, LetterCode
    --HAVING Count(*) > @limit
    ORDER BY DateSent, LetterCode

    SELECT *
    FROM #tmpTable
    WHERE [Letters Sent] > @limit

    DROP TABLE #tmpTable

The end result looks like this:

Date        Letter Code     Letters Sent    Amount Paid
1/1/2012    a               1245            12345.67
1/1/2012    b               2301            1234.56
1/1/2012    c               1312            7894.45
1/1/2012    a               1455            2345.65
1/1/2012    c               3611            3213.21

I'm having problems figuring out where the slowdown is, because everything runs extremely fast in the query editor. It's only when I move the query to a stored procedure that it starts taking so long to run.

I'm sure it has something to do with the query execution plan getting generated, but I don't know enough about SQL to identify what could be causing the problem.

It should probably be noted that all the tables used in the query have millions of records.

Can someone explain to me why this is taking so much longer to run as a stored procedure than in the query editor, and help me identify what part of my query could be causing performance issues when run as a stored procedure?

As Martin pointed out in the comments, the problem is that the query is using a cached plan which is inappropriate for the parameters given.

The link he provided on Slow in the Application, Fast in SSMS? Understanding Performance Mysteries provided a lot of useful information which lead me to some solutions.

The solution I am currently using is to copy the parameters to local variables in the procedure, which I think makes SQL re-evaluate the execution plan for the query anytime it's run, so it picks the best execution plan for the parameters given instead of using an inappropriate cached plan for the query.

Other solutions which may work are using the OPTIMIZE FOR or RECOMPILE query hints.


Lock a table for a CTAS upsert in Azure SQL DataWarehouse I have a type 2 dimension in Azure SQL Data Warehouse.

Essentially I am creating an interim table with

CREATE TABLE myDimension_temp
AS
SELECT
...
FROM myStagingTable;

etc

After the CTAS is complete I do a

RENAME OBJECT myDimension TO myDimension_old;
RENAME OBJECT myDimension_tmp TO myDimension;
DROP TABLE myDimension_old;

Is there a way to lock these tables so that any actions against myDimension are blocked until the renaming of both tables is complete?

If so, is this worth implementing or is the time it takes to swap out tables in this manner so negligible that the chances of this occurring are too small to worry about or am I looking at/going about this in completely the wrong way?

There is generally no exclusive locking in Azure SQL Data Warehouse as the default isolation level is READ UNCOMMITTED as documented here.

Other approaches to this such as sp_get_applock are also unavailable.  Therefore you manage this based on low concurrency and workflow, ie don't allow users to connect to your warehouse during the update, or use some kind of flag that has to be checked during operations.


Storing external databases data into one I need to store different datasets coming from a provider where each dataset has it's own release path.
These datasets can be combined together to get the full picture of the data available from the provider.
I know from the doc of the publisher the combination of versions that are allowed.
My pain point is that I need to keep track of the version for each dataset.
Example of data:
"Main" dataset from publisher "ABC" has version "1.0".
"Ext" dataset from publisher "ABC" has version "release_3"
My schema is as follow:
Provider
ProviderId
Dataset
DatasetId
ProviderId
Version
VersionId
DatasetId
Main
MainId
VersionId
Ext
ExtId
MainId
VersionId
Based on that, is it a problem that FK "VersionId" in tables "Main" and "Ext" references a different record in table "Version" ?
I'm afraid that any user querying the DB will not expect to have diverging Version (as the FK name is identical in both tables).
Unfortunately that's the reality of the data provided by the publisher.
Is there a better design to accomplish the same result ?
NB: It is possible that in the future, datasets from different providers need to be combined.
Thanks

Technically your Version.DatasetId gets you back to the Dataset table and then you can determine the Provider by ProviderId. So while the Version table can be a little confusing by VersionId, as long as there's no overlapping VersionIds in Ext and Main then you should be ok. Otherwise you'll need to add the DatesetId to Ext and Main too.
From a design perspective for other developers and users, you can create a view that pre-joins the Ext and Main to Version and provide that view to them instead. You can even add a column called IsMain to the view to be extra explicit if you feel.


Set Order of top 10 Product manually in database I am creating a database for a mall. Client wants to set top 10 brands or stores manually. He wants to set order manually any time, for example:

store_Name     priority
Puma             1
Reebok           2
Dominos          3
Nike             4
NumeroUno        5

Product will be shows to user ordered by priority. Priority 1 will be shown at top.

My question is when I want to change the priority of store names like I want Nike to be shown at the top of search.

When I set the priority of Nike to 1 then automatically existing 1 position becomes 2 and 2 position becomes 3 and so on. How do I do this mechanism in SQL Server (stored procedure or Trigger)?

I am using SQL Server 2014 and my table has thousands of stores. I want 10 selectively store which is shown  at the top of mobile app.

Just for fun you can design a data model for which updating a row to rank N requires you to scan N rows and to update the value for just 1. Displaying the top N ranks also requires you to scan N rows. One weakness is that filtering down to just the Nth rank also requires you to scan N rows. Perhaps this could be useful if someone had many rows in their table and didn't want to potentially update millions of rows to perform a single rank change.

The key is to store a separate internal value for the rank that's different than the displayed rank but still in the correct order. Here I will store the internal priority as NUMERIC(10,6). For each update we can find the previous RAW_PRIORITY value and subtract 0.000001 from it. That will preserve the order with just a single row update. With that precision end users change the ranks of items up to one million times before problems start happening. The precision and scale can be further increased depending on your requirements.

Define the table:

DROP TABLE IF EXISTS dbo.X_STORES;

CREATE TABLE dbo.X_STORES (STORE_NAME VARCHAR(100), RAW_PRIORITY NUMERIC(10,6));

INSERT INTO dbo.X_STORES VALUES
('Puma',1),
('Reebok',2),
('Dominos',3),
('Nike',4),
('NumeroUno',5)

CREATE INDEX X_STORES__PRIORITY ON dbo.X_STORES (RAW_PRIORITY) INCLUDE (STORE_NAME);

Depending on exact requirements it may make sense just to cluster the table on RAW_PRIORITY. We also want a view to make it easy to display ranks in a human readable format as well as to make updates easier:

CREATE OR ALTER VIEW dbo.X_STORES_PRIORITY_ORDER
AS
SELECT STORE_NAME, ROW_NUMBER() OVER (ORDER BY RAW_PRIORITY) [PRIORITY], RAW_PRIORITY
FROM dbo.X_STORES;

With our starting date set Nike is 4th:

SELECT STORE_NAME, [PRIORITY]
FROM dbo.X_STORES_PRIORITY_ORDER
ORDER BY [PRIORITY];

Results:

╔════════════╦══════════╗
║ STORE_NAME ║ PRIORITY ║
╠════════════╬══════════╣
║ Puma       ║        1 ║
║ Reebok     ║        2 ║
║ Dominos    ║        3 ║
║ Nike       ║        4 ║
║ NumeroUno  ║        5 ║
╚════════════╩══════════╝

Let's change Nike to be ranked second:

UPDATE dbo.X_STORES
SET RAW_PRIORITY = (
    SELECT v.RAW_PRIORITY - 0.000001
    FROM dbo.X_STORES_PRIORITY_ORDER v
    ORDER BY v.RAW_PRIORITY OFFSET 1 ROW FETCH NEXT 1 ROW ONLY
)
WHERE STORE_NAME = 'Nike';

Results:

╔════════════╦══════════╗
║ STORE_NAME ║ PRIORITY ║
╠════════════╬══════════╣
║ Puma       ║        1 ║
║ Nike       ║        2 ║
║ Reebok     ║        3 ║
║ Dominos    ║        4 ║
║ NumeroUno  ║        5 ║
╚════════════╩══════════╝

Now let's change Dominos to be first:

UPDATE dbo.X_STORES
SET RAW_PRIORITY = (
    SELECT v.RAW_PRIORITY - 0.000001
    FROM dbo.X_STORES_PRIORITY_ORDER v
    ORDER BY v.RAW_PRIORITY OFFSET 0 ROW FETCH NEXT 1 ROW ONLY
)
WHERE STORE_NAME = 'Dominos';

Results:

╔════════════╦══════════╗
║ STORE_NAME ║ PRIORITY ║
╠════════════╬══════════╣
║ Dominos    ║        1 ║
║ Puma       ║        2 ║
║ Nike       ║        3 ║
║ Reebok     ║        4 ║
║ NumeroUno  ║        5 ║
╚════════════╩══════════╝


Mysql subquery not recognizing the subquery join table field I have a three tables. The main table is the journeydata and next table is the alarm tables. The primary key for journeydata is journeydataID and its available as key in the alarm tables. Next in the alarm table there is a fenceID which is a primary key of another table that is fence table. Below I am trying to run a subquery example.What I am trying to do is join all the alarm for a particular journeydataID if it exist into a single string. But I keep getting error #1054 - Unknown column 'alarmgroup.journeydataID' in 'on clause'. Next my task I want to show is a alarm has a fence name value. Example if there is a relevant value of a alarmName with fenceName show as alarmName(fenceName).

SELECT journeydata.dateTime,
alarmDetails
FROM
journeydata
LEFT JOIN
    ( SELECT GROUP_CONCAT(alarmType.alarmName) AS alarmDetails
      FROM alarm
      JOIN alarmType
      ON alarm.alarmTypeID = alarmType.alarmTypeID
      LEFT JOIN fence
      ON alarm.fenceID=fence.fenceID
      WHERE alarm.associateID=6
      GROUP BY alarm.journeydataID
    ) AS alarmgroup
    ON journeydata.journeydataID=alarmgroup.journeydataID

WHERe journeydata.associateID=6

Your derived table is missing the extra field it is looking for try this:

SELECT journeydata.dateTime,
alarmDetails
FROM
journeydata
LEFT JOIN
    ( SELECT
         GROUP_CONCAT( DISTINCT(
              IF(fence.fenceName IS NULL, alarmType.alarmName,
                   CONCAT(alarmType.alarmName,
                        '(',fence.fenceName,')'))
         )) AS alarmDetails,
         alarm.journeydataID as journeydataID   -- <<< ADDED
      FROM alarm
      JOIN alarmType
      ON alarm.alarmTypeID = alarmType.alarmTypeID
      LEFT JOIN fence
      ON alarm.fenceID=fence.fenceID
      WHERE alarm.associateID=6
      GROUP BY alarm.journeydataID
    ) AS alarmgroup
    ON journeydata.journeydataID=alarmgroup.journeydataID

WHERE journeydata.associateID=6

There might be more that needs fixing, but I would need a little time to look over the query and information you have provided.   Try this for now.

EDIT: Added the (untested) distinct grouping and formatting of alarmDetails as requested.


Separating working hours with non-working hours SQL Please can someone assist? I need to break up data between working hours and non-working hours (weekends also need to be taken into consideration).

I'm not really sure where to begin, other than:

select *
from Electricalconsumptionhalfhourview
where meterID = 17635382
  and [Date] < '2015-02-28 23:59:59.000'
  and [Date] > '2015-02-01 00:00:00.000'

Thanks for the help guys... I eventually settled on doing this...

select *
into #WorkingHours
from Electricalconsumptionhalfhourview
where meterID = 17635382
  and [Date] > '2015-02-01 00:00:00.000'
  and [Date] < '2015-02-28 23:59:59.000'

select *
into #NonWorkingHours
from Powerstardatahalfhourcacheview
where meterID = 1458194
  and [Date] > '2015-02-01 00:00:00.000'
  and [Date] < '2015-02-28 23:59:59.000'

SELECT *
FROM #WorkingHours
WHERE DATEPART(hh, [Date]) > 8 --Start of working hours
  AND DATEPART(hh, [Date]) < 17 --End of working hours
  AND DATEPART(dd, [Date]) NOT in (1,7,8,14,15,21,22,28)
  AND DATEPART(mm, [Date]) = 2

SELECT *
FROM #NonWorkingHours
WHERE DATEPART(hh, [Date]) < 8  --Start of working ho
   OR DATEPART(hh, [Date]) > 17  --End of working hours
  AND DATEPART(mm, [Date]) = 2
UNION
SELECT * FROM #NonWorkingHours
WHERE DATEPART(dd, [Date]) in (1,7,8,14,15,21,22,28)
  and DATEPART(mm, [Date]) = 2'

If I can understand what you are asking I wrote this code for you. This is when your workweek will be from monday till friday and your workhours will be from 8 to 17

select *
      from (
           select
           case when datename(dw,[date]) in ('Saturday','Sunday') then 'Weekend' else 'Week' end as WeekendOrWeek,
           datepart(HOUR,[date]) as HourDate,
           *
           from Electricalconsumptionhalfhourview
           where meterID = 17635382
           )WeekDays
where WeekDays.WeekendOrWeek = 'Week' and WeekDays.Hourdate > 8 and WeekDays.HourDate < 17

This is written for T-SQL. (SQL Server) If you want to get the dates which are not in the week just edit the where clause.


mysql > why replace command is not working on strings coming from json encoded? Say in my wordpress db I've got a post_content column containing strings like this:

href=\"http:\/\/dev.example.com\/example\/wp-content\/uploads\/2019\/07\/my.pdf\"

The above is a portion of string (placed inside a link placed inside a text) that comes from data saved by a plugin (acf) which uses json as data format.

Then I run

UPDATE wp_posts SET post_content = REPLACE(post_content,'http:\/\/dev.example.com\/example\/', 'https:\/\/www.example.com\/');

Well, that won't work. Zero rows affected and no errors. Why? What am I doing wrong?

That kind of code comes from acf plugin gutenberg block in wordpress, and that's because it's json encoded. But as far as I know, shoudl be trated just as any other string.. What's the matter?

To be more precise, here below I'm attaching an image from sequelpro and from cli to prove the backslashes are there (see comments).

As a primer it looks like the data is being stored with the escape character \ backslash to allow the WordPress server to automatically use the data in the engine itself.

You would have to double the back-slashes to find and replace the string.

You can verify this via dbfiddle.uk.

If you run your Search String against a MySQL 8.0 instance like this:

 select 'http:\/\/dev.example.com\/example\/'

... then you will receive this:

http://dev.example.com/example/

Reference: db<>fiddle for Question 255169 (db<>fiddle)

This isn't what is stored in the database and thus will no be found.

Solution

The solution is to double the (MySQL) escape character \ into:

select 'http:\\/\\/dev.example.com\\/example\\/'

...which returns this:

http:\/\/dev.example.com\/example\/

Reference: db<>fiddle for Question 255169 (db<>fiddle)


Changing the WHAT column of DBA_JOBS I am using a job to update a materialized view.

I found that the job would lock itself if the exec dbms_refresh.refresh(''"<schema>"."<materialized_view_name>"''); was not committed and I would like to change the "what" column of the job to include a commit.

I face two possible solutions :

solution 1: a more complicated way is to recreate the job entirely, which I would like to avoid

Run SYS.DBMS_JOB.REMOVE(14133); COMMIT;

Recreate the whole job to now include the commit

DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
  ( job       => X
   ,what      => 'exec dbms_refresh.refresh(''"<schema>"."<materialized_view_name>"'');COMMIT;'
   ,next_date => to_date('11-11-2016 00:00:00','dd/mm/yyyy hh24:mi:ss')
   ,interval  => 'trunc(sysdate)+1   '
   ,no_parse  => FALSE
  );
  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;
/

solution 2: update the DBA_JOBS view directly by running a normal update statement on the view

update dba_jobs set what=(
select what from dba_jobs
where job in ( '1','2','3','<etc>')
)||'COMMIT;'
where job in ( '1','2','3','<etc>');

Is there a better solution than these two, is solution two possible ?

I'd like to avoid solution 1 as let's imply I don't have the code for the view at hand it would take more time plus let's imply I have hundreds to modify.

Really? DBMS_JOB has a procedure called CHANGE.

declare
  jobno number;
begin
  dbms_job.submit(jobno, 'begin null; end;');
end;
/
commit;

select job, what from user_jobs;

       JOB WHAT
---------- ------------------------------
         1 begin null; end;

begin
  dbms_job.change(1, 'begin null; commit; end;', null, null);
end;
/
commit;

select job, what from user_jobs;

       JOB WHAT
---------- ------------------------------
         1 begin null; commit; end;


Disable auto stats in SQL Server I have couple questions about auto stats in SQL Server:

I know I can use the following statement to check if the database auto stats is disabled, but is there a statement to check if a specific index's auto stats is disabled?

SELECT DATABASEPROPERTYEX('Databasename','IsAutoUpdateStatistics')

If I disable auto stats for an index before my reorg jobs, will it prevent blocking issues in SQL Server?

In the following link there is a statement that you can disable auto stats on a database level https://msdn.microsoft.com/en-us/library/ms188775.aspx

is there a statement to check if a specific index's auto stats is disabled?

You can use sys.sp_autostats or sys.stats to get this information.

sys.sp_autostats can also be used to enable or disable automatic updates for a particular statistics object.

For example, using the AdventureWorks sample database:

EXECUTE sys.sp_autostats
    @tblname = N'[Production].[TransactionHistory]',
    @flagc = NULL,
    @indname = NULL;

The AUTOSTATS column shows if the statistics will be automatically refreshed:

SELECT
    S.name,
    S.stats_id,
    S.auto_created,
    S.user_created,
    S.no_recompute
FROM sys.stats AS S
WHERE
    S.[object_id] = OBJECT_ID(N'[Production].[TransactionHistory]', N'U');

The no_recompute column shows if automatic statistics updates will be skipped:

  If I disable auto stats for an index before my reorg jobs, will it prevent blocking issues?

Reorganizing an index does not update statistics. You probably need to clarify your question to explain the exact problem you are encountering.


Getting empty folder when using mongodump to back up my MongoDB Basically, I have a problem with using mongodump to back up my MongoDB.

This is the general syntax I use in SSH:

mongodump -d myDatabaseName -o ~/backups/my_backup

This is the resulting message:

Fri Apr 22 20:39:57.304 DATABASE: myDatabaseName   to     /root/backups/my_backup/myDatabaseName

This simply generates a blank folder with no files in it whatsoever. The actual database is fairly large, so not sure what's going on.

I would also like to add that my mongodump client and my MongoDB version are both the same (version 2.4.9).

Not sure how to go about fixing this. Any help is appreciated.

I was using the default mongodb-client deb package with Ubuntu. I removed those and installed the mongodb-org-tools package from mongodb.com https://docs.mongodb.com/master/tutorial/install-mongodb-on-ubuntu/?_ga=2.36209632.1945690590.1499275806-1594815486.1499275806

They have other install instructions for your specific OS if you are not on Ubuntu https://www.mongodb.com/download-center?jmp=nav#community


Why my code only return 1 row? Leetcode#626 I have used MySQL. My code is
SELECT
(CASE WHEN mod(s.id,2)=0 THEN s.id-1
WHEN mod(s.id,2)=1 AND s.id != MAX(s.id) THEN s.id+1
END) AS id,
s.student
FROM seat s
ORDER BY id

It only returns one row
{"headers": ["id", "student"], "values": [[2, "Abbot"]]}
But if I delete AND s.id != MAX(s.id)
then I will get all rows.
I don't understand it. Is my understanding of MAX function wrong?

You are using an agregate function which causes the result to collapse to single row.
Possible solution:
SELECT
(CASE WHEN mod(s.id,2)=0 THEN s.id-1
WHEN mod(s.id,2)=1 AND s.id != maxid.maxid THEN s.id+1
END) AS id,
s.student
FROM seat s
join (select max(s.id) as maxId from seat) as maxId on 1=1
ORDER BY id

You are using it in the SELECT, not CONDITIONAL (WHERE).
Now, the question you ask is a good one and i would have to refresh my knowledge of inner workings of SQL.
I think its because the moment it hits MAX, its collapses the result set, as it doesnt "see" a need to return all rows.
Simply said, the query processor doesnt care if its a condition in case statement or value to be returned. It's there and thats that.
Aggregation function without group by is a mysql thing, and not SQL standard, so the behaviour is specific to MySQL.


How to insert and or update a variable number of records and prevent concurrency issues I currently have the following stored procedure. However I am seeing some race conditions where I end up with incorrect data which I think is caused because the procedure isn't attomic and it can be called multiple times resulting in overlapping data.

CREATE PROCEDURE [dbo].[data_save]
    @user INT ,
    @players VARCHAR(MAX) ,
    @captains VARCHAR(MAX)
AS

-- Clear existing records if any
DELETE FROM dbo.data WHERE user = @user

DECLARE @captainsTable TABLE (captainId INT);
INSERT INTO @captainsTable (captainId)
SELECT DISTINCT TOP 3 item FROM [platform].[SimpleSplit](@captains, ',')

INSERT INTO dbo.data
( user ,
  player ,
  iscaptain
)
SELECT @user
     , item -- this is what is returned from the SimpleSplit
     , CASE WHEN [item] in (select captainId from @captainsTable) THEN 1
         ELSE 0
       END
  FROM [platform].[SimpleSplit](@captains, ',')

I want to be able to execute this concurrently for different users but not for the same user.

The number of records for one user might be 4 at point 1 in time and then in point 2 the save might be executed with 6 records for instance. So just updating the existing records is not an option.

It's not a problem if the last call to the save procedure "wins" and overwirtes the previous ones but I don't want to end up with a combination of data from both. I am using sql server 2008.

If your goal is to ensure that at the end of procedure dbo.data contains only records you inserted in the body of procedure, then you move DELETE to the very end (I assume you have primary key in data table, data_id:

--DELETE FROM dbo.data WHERE user = @user  -- do it at the end
DECLARE @captainsTable TABLE (captainId INT);
DECLARE @newData TABLE (data_id INT);
INSERT INTO @captainsTable (captainId)
SELECT DISTINCT TOP 3 item FROM [platform].[SimpleSplit](@captains, ',')

--store inserted rows into table
INSERT INTO dbo.data
( user ,
  player ,
  iscaptain
)
OUTPUT inserted.data_id INTO @newData
SELECT @user
     , item -- this is what is returned from the SimpleSplit
     , CASE WHEN [item] in (select captainId from @captainsTable) THEN 1
         ELSE 0
       END
  FROM [platform].[SimpleSplit](@captains, ',');
--delete all except recently inserted
DELETE b FROM dbo.data b WHERE user = @user AND NOT EXISTS
(SELECT NULL FROM @newData a WHERE a.data_id = b.data_id);

Also, it makes sense to begin transaction (or create a savepoint if the sp executed withing the context of existing transaction) in the beginning of procedure , and commit at the end.


MySQL: 2 separate queries run very fast, combined query with OR runs very slow EXPLAIN
SELECT inet_ntoa(src) as source, inet_ntoa(dst) as dest,
    firewall.name as fw, proto.proto, service, action.action,
    sum(count) as hits, max(date) as lastused, rule_uid.rule_uid
FROM log, action, firewall, proto, rule_uid
WHERE ( rule_uid.id = log.rule_uid )
    AND ( proto.id = log.proto )
    AND ( log.action = action.id AND action.action='accept' )
    AND ( log.orig = firewall.id)
    AND ( ( src >= inet_aton('0.0.0.0')
            AND src <= inet_aton('255.255.255.255') ) )
    AND ( ( xlatedst >= inet_aton('173.82.122.94')
            AND xlatedst <= inet_aton('173.82.122.94') ) )
GROUP BY src, dst
ORDER BY hits DESC, dst
LIMIT 100;

uses temporary/filesort, 92 lines, and runs in 30ms.

Similarly fast if I modify the query to search for the same IP in the dst column.

But if I combine both queries to search for that IP in the xlatedst OR the dst columns:

SELECT inet_ntoa(src) as source, inet_ntoa(dst) as dest,
    firewall.name as fw,  proto.proto, service, action.action,
    sum(count) as hits, max(date) as lastused, rule_uid.rule_uid
FROM log, action, firewall, proto, rule_uid
WHERE( rule_uid.id = log.rule_uid )
    AND ( proto.id = log.proto )
    AND ( log.action = action.id AND action.action='accept' )
    AND ( log.orig = firewall.id)
    AND ( ( src >= inet_aton('0.0.0.0')
            AND src <= inet_aton('255.255.255.255') ) )
    AND *( ( xlatedst >= inet_aton('173.82.122.94')
             AND xlatedst <= inet_aton('173.82.122.94') )
         OR ( dst >= inet_aton('173.82.122.94')
              AND dst <= inet_aton('173.82.122.94') )
         )*
GROUP BY src, dst
ORDER BY hits DESC, dst
LIMIT 100;

then it examines 2121544 rows and runs for 3 minutes.

Now I could just run both queries and concat the results. But I wonder if there is a simpler way?

There is a seperate index on both "dst" and "xlatedst". Why isnt it used?

In my experience though, doing anything with an OR condition can have horrible implications when running queries, have you considered running this as two separate queries and then combining the results?

E.g.

SELECT * FROM (

    SELECT inet_ntoa(src) as source, inet_ntoa(dst) as dest,
        firewall.name as fw, proto.proto, service, action.action,
        sum(count) as hits, max(date) as lastused, rule_uid.rule_uid
    FROM log, action, firewall, proto, rule_uid
    WHERE ( rule_uid.id = log.rule_uid )
            AND ( proto.id = log.proto )
        AND ( log.action = action.id AND action.action='accept' )
        AND ( log.orig = firewall.id)
        AND ( ( src >= inet_aton('0.0.0.0')
                AND src <= inet_aton('255.255.255.255') ) )
        AND ( ( xlatedst >= inet_aton('173.82.122.94')
                AND xlatedst <= inet_aton('173.82.122.94') ) )
    GROUP BY src, dst
    ORDER BY hits DESC, dst

    UNION ALL

    SELECT inet_ntoa(src) as source, inet_ntoa(dst) as dest,
        firewall.name as fw, proto.proto, service, action.action,
        sum(count) as hits, max(date) as lastused, rule_uid.rule_uid
    FROM log, action, firewall, proto, rule_uid
    WHERE ( rule_uid.id = log.rule_uid )
        AND ( proto.id = log.proto )
        AND ( log.action = action.id AND action.action='accept' )
        AND ( log.orig = firewall.id)
        AND ( ( src >= inet_aton('0.0.0.0')
                AND src <= inet_aton('255.255.255.255') ) )
        AND ( ( dst >= inet_aton('173.82.122.94')
              AND dst <= inet_aton('173.82.122.94') ) )
    GROUP BY src, dst
    ORDER BY hits DESC, dst

) ORDER BY hits DESC, dst
LIMIT 100;

That may do the job, or something similar :)


Notify on SQL trace event starting? I was wondering if there was a method of receiving some sort of notification when a SQL trace begins - be it e-mail or some other method? The circumstances of needing this we currently have a substantial amount of developers working within the same dev database and we sometimes find that a performance hit is due to one of them leaving the trace open instead of properly closing it. I understand the message needs to be spread to limit SQL trace usage to small timeframes, but alas, we are faced with this issue and must find a way to better administer it. Any insight would be much appreciated.

I don't believe there is an extended event for a trace starting.  One thing you could do is to write a SQL Agent job that queries sys.traces and if anything but the default trace exists, take whatever action you deem necessary.


Getting year DATEPART from Unix date In my database, the dates are retained as days elapsed since/before 01/01/1970, which I understand to be a Unix date format, eg. today is 23rd May 2018 and the value is 17674.

I'm trying to run a select statement against a column in a date table to show only the year.

If I run this:

datepart(year, c.cko_date)

I get dates that are in the 1940s (they should be the last couple of years).

When I run

select datepart(year, getdate())

it correctly tells me the date is 2018.

Any suggestions about what I'm doing wrong?

Assuming the column is an int, and not a varchar column, you could do this:

datepart(year, c.cko_date + 25567)

25567 is the number of days between 1900-01-01 and 1970-01-01; essentially this adjusts for the discrepancy between unix and SQL Server datetime.

The SQL Server datetime is designed around 1900-01-01 being the "epoch".  It may help to think of SQL Server dates as an decimal value representing the quanta of time until or since January 1st, 1900.  Times are the decimal portion such that midnight is 0.0, noon is 0.5, 6pm is 0.75.  The valid range of values that can be stored in a datetime column is 1753-01-01 00:00:00 to 9999-01-01 23:59:59.997.  Numerically, this corresponds to the range starting at -53690.0000000 and ending at 2958463.99999998.

Unix uses 1970-01-01 00:00:00 as its epoch.  By convention, the epoch starts in Universal Coordinated Time, or UTC.  For North America, the Central Daylight Time timezone is 5:00 hours behind UTC.  Therefore, to accurately convert a value from a Unix epoch time value into a datetime value in CDT requires adjusting for that 5-hour difference.  Therefore, a slightly more accurate example of converting from a Unix-time expressed in a number of days would be:

--declare an integer representing the number of days since 1970-01-01
DECLARE @days_since_unix_epoch int = 17674; --2018-05-23
--show the datetime value in UTC
SELECT UTC = CONVERT(datetime, @days_since_unix_epoch + 25567)
--show the datetime value in North American CDT
   , CDT = CONVERT(datetime, @days_since_unix_epoch + 25567) - CONVERT(datetime, '05:00:00');

The results:

╔═════════════════════════╦═════════════════════════╗
║           UTC           ║           CDT           ║
╠═════════════════════════╬═════════════════════════╣
║ 2018-05-23 00:00:00.000 ║ 2018-05-22 19:00:00.000 ║
╚═════════════════════════╩═════════════════════════╝

While we're technical, typically the Unix epoch is stored as the number of milliseconds since 1970-01-01 00:00:00, not the number of days.  The number of milliseconds can be quite difficult to deal with if extreme accuracy is a concern since there have been a number of adjustments to time since 1970 in order to deal with leap-seconds.  According to the wikipedia article I just linked to, there have been 26 one-second additions to time since 1972; to accurately deal with dates between 1970 and today, you need to appropriately add between 0 and 26 seconds.  I am uncertain if SQL Server's datetime structure accurately understands those adjustments.

Note also that SQL Server's datetime structure has a resolution of 3 milliseconds.  Consider the following:

SELECT [1ms] = CONVERT(datetime, '2018-05-22 00:00:00.001')
     , [2ms] = CONVERT(datetime, '2018-05-22 00:00:00.002')
     , [3ms] = CONVERT(datetime, '2018-05-22 00:00:00.003')

The output:

╔═════════════════════════╦═════════════════════════╦═════════════════════════╗
║           1ms           ║           2ms           ║           3ms           ║
╠═════════════════════════╬═════════════════════════╬═════════════════════════╣
║ 2018-05-22 00:00:00.000 ║ 2018-05-22 00:00:00.003 ║ 2018-05-22 00:00:00.003 ║
╚═════════════════════════╩═════════════════════════╩═════════════════════════╝

If you need better resolution, you should consider using the datetime2 data type since it supports microsecond-precision, and a potentially much larger range of dates and times:

  Date range: 0001-01-01 through 9999-12-31
  Time range: 00:00:00 through 23:59:59.9999999


copy package from schema B to schema C I am in the next situation: I am using oracle 11g. I am connected to an user, lets say schema1, where are a lot of permissions. I want to create a script which will copy the Package1 (and its body) from schema2 to schema3.

The script will be executed from schema1.

PS: I already look for a solution and I am not interested in export/import or in using other tools from toad, sql developer etc.

One way of doing this on a low level basis is by reading the USER_SOURCE for the corresponding package. In PL/SQL you could loop over the lines and add each line to an array (/table) of type DBMS_SQL.VARCHAR2A, lets call this variable 'sqlTable'.

The first line of your sqlTable contains the package name, with a little String manipulation you should be able to change the name from 'PACKAGE_NAME' to 'SCHEMA'.'PACKAGE_NAME'.

Then using DBMS_SQL package perform the following steps:

cur = DBMS_SQL.OPEN_CURSOR; -- cur is of type pls_integer
DBMS_SQL.PARSE(cur, sqlTable, sqlTable.first, sqlTable.last, false, DBMS_SQL.NATIVE);
DBMS_SQL.execute(cur)

Another way is to spool the output of the USER_SOURCE to a file, connect to the other schema and load / execute the file there. See this.


Permissions for executing subscription in SSRS I'd like to execute an SSRS subscription after some ETL work run by a SSIS package that takes a varying amount of time completes. I'm considering using this method, which basically consists of SSIS calling the same stored procedure that the SQL Server Agent jobs for subscriptions call to execute a subscription.

However, I'm not sure what the best way of managing permissions to execute the subscription would be. For example, I'm reluctant to add users to the RsExecRole because it's discouraged by Microsoft in some documents, and I'm reluctant to grant object-level permissions to execute the ReportServer.ado.AddEvent procedure because they'll likely get overlooked in any migrations. (Edit: I suppose we could work around this by having something execute the Agent job instead of calling the stored proc.)

I'm also hoping to avoid issues with the the code that executes the subscription breaking when one of the GUIDs changes.

How do SSRS professionals manage permissions for this type of request? Alternatively, is there a simpler method that bypasses these issues?

If you set up a report subscription with a One-time Schedule and gather its Schedule ID, you can execute it by using sp_start_job.  SSRS manages subscriptions with SQL Server Agent, which makes this possible without needing to use Reporting Server Events.  The executing user, I imagine a service account, will then only need to be a part of the SQLAgentOperatorRole.  See: sp_start_job#permissions.
You allude to this in an edit in you question, but I'd like to outline the process in case anyone else needs a specific solution.

Setting up the schedule is easy.  The default time is 2am on the day you set up the subscription schedule and has likely already passed, so you can leave it.

Then you can gather your Schedule ID by querying the Report Server database tables:

select C.Name ReportName,
    S.Name ScheduleName,
    SU.Description SubscriptionName,
    S.ScheduleID
from ssrs.Catalog C
join ssrs.ReportSchedule RS
    on C.itemID = RS.ReportID
join ssrs.Schedule S
    on RS.scheduleID = S.ScheduleID
join ssrs.Subscriptions SU
    on RS.SubscriptionID = SU.SubscriptionID
where C.name = 'Test Report'

(1 row(s) affected)

You can kick off the subscription by referencing the job name matching your Schedule ID:

/*Run Subscription*/
exec msdb.dbo.sp_start_job '8037A345-9F7C-45B0-99FB-B110996BD521'

(1 row(s) affected)
Job '8037A345-9F7C-45B0-99FB-B110996BD521' started successfully.

I have not yet had an issue with the a Schedule ID (GUID) changing and breaking the execution, but as a fail-safe you can create a data-driven subscription and explicitly state a unique description.

You can then reference the description to gather your Schedule ID dynamically.

declare @ScheduleID uniqueidentifier

select @ScheduleID = S.ScheduleID
from ssrs.Catalog C
join ssrs.ReportSchedule RS
    on C.itemID = RS.ReportID
join ssrs.Schedule S
    on RS.scheduleid = S.ScheduleID
join ssrs.Subscriptions SU
    on RS.SubscriptionID = SU.SubscriptionID
where C.name = 'Test Report'
    and SU.Description = 'My Test Report Subscription'

exec msdb.dbo.sp_start_job @ScheduleID

(1 row(s) affected)
Job '8037A345-9F7C-45B0-99FB-B110996BD521' started successfully.


Tombstone Table vs Deleted Flag in database syncronization & soft-delete scenarios I need to keep track of deleted items for client synchronization needs.

In general, is it better to add a tombstone table and a trigger that tracks when a row was deleted from the server database - basically adding a new row to the tombstone table with the data from the deleted item - or to keep the items in the original table and flag them as deleted, typically with a column of type bit, to indicate that a row is deleted and another column to track when the delete occurred?

In general it is better to know the specific requirements and not make design decisions based on what works best in most situations.  Either could be preferable.  Here are some specifics to gather:

How fast do deletes need to be?
How fast do un-deletes need to be?
How often will deleted data be queried and will it be queried with data that has not been deleted?
How fast do queries of deleted data need to be?
Do you need to preserve only deleted items or changes as well?
Do you need to keep the table/indexes on the primary table small?
What partitioning and/or change tracking technologies are available on the database platform?
How much disk space is available?
Will the deleting occur on the fly or in batch operations?


Store and search leveled categories with mysql json I have leveled category triplets column, that tag the specific blacksmith with items he can produce:

blacksmith_id (primary) | blacksmith_name (varchar) | item_tags [json] | blacksmith_lvl (int)

Single tag structure looks like this:

Level 1 (category) : Level 2 (sub-category) : Level 3 (sub-sub-category)

For example, blacksmith with id 1 can produce following items:

Iron:daggers:curved
Iron:daggers:poisoned
Steel:daggers:curved
Golden:swords:curved
... etc

Each blacksmith can be tagged with multiple item tags.

I chose to go with JSON since item data that blacksmith produces is just informative, and does not have any specific value other than finding the specific blacksmith by his skills, neither does it change. So i see no use in creating multiple joined tables with row for each item that blacksmith uses.

What would be the best (time / resources) way to store and later search on by specific category levels to get list of blacksmits using mysql json.

My ideas so far:

1. Using WHERE JSON_CONTAINS

["iron:daggers:curved","iron_daggers:poisoned","steel:daggers:curved"]

In this case the only option would be to search for complete item hierarchy Level1:Level2:Level3

2. USING WHERE JSON_EXTRACT

{"i0":{"lvl1":"iron","lvl2":"daggers","lvl3":curved},"i1":{"lvl1":"iron","lvl2":"daggers","lvl3":"poisoned"}}

For searching just on specific levels, but i dont know if it is possible to iterate through all items without specifying the key (i0, i1) to find for example just blacksmiths which lvl2's are daggers and how time consuming this is for mysql.

Please suggest your own structures you think would be most effective.

I chose to go with JSON since item data that blacksmith produces is just informative, and does not have any specific value other than finding the specific blacksmith by his skills

I find this paradoxical- searching by a multi-valued field IS one of the reasons to apply normalization, as it simplifies a lot the searches. You can do what you mention, but without extra information that justifies that decision, that is not the simplest approach from a pure relationship model. Because you say:

  Please suggest your own structures you think would be most effective.

I will first suggest how I would do it:

Normalized schema

CREATE TABLE blacksmiths (
  blacksmith_id int unsigned auto_increment PRIMARY KEY,
  blacksmith_name varchar(300) NOT NULL,
  blacksmith_lvl int unsigned NOT NULL
);

CREATE TABLE items (
  item_id int unsigned auto_increment PRIMARY KEY,
  item_name varchar(300) NOT NULL, # or level1, level2, level3?
)

CREATE TABLE blacksmith_items (
  item_id int unsigned,
  blacksmith_id int unsigned,
  level unsigned int,
  PRIMARY KEY(item_id, blacksmith_id),
  KEY(blacksmith_id),
  CONSTRAINT item_id_fk  (item_id) REFERENCES items (item_id),
  CONSTRAINT blacksmith_id_fk  (blacksmith_id) REFERENCES blacksmiths (blacksmith_id),
);

I don't get exactly how levels come into play- if it is a property of blacksmiths or items, but you can modify the join table to make it part of the primary key or not, and/or modify the indexes to search them faster. Or maybe the items columns have to be separated into metal, name and type, etc.

To get all items for blacksmith id1:

SELECT items.name FROM blacksmiths
JOIN blacksmith_items
USING (blacksmith_id)
JOIN items
USING (item_id)
WHERE blacksmiths.blacksmith_id = 1;

To get items with a specific level;

SELECT items.name FROM blacksmiths
JOIN blacksmith_items
USING (blacksmith_id)
JOIN items
USING (item_id)
WHERE blacksmiths.blacksmith_id = 1
AND blacksmith_items.level = 2;

Using joins is not really that complicated, and it will allow you more flexibility when you change the number of levels, or the number of items, and you can generate a json output from your application code.


SQL Server 2014 only using 10 percent of its available memory? Hoping someone can weigh in here. I have a 2014 developer edition box that doesn't seem to be performing well. I currently have 128 GBs of RAM allocated to the box and 120 GBs maximum allocated inside of SQL itself.
When I look at the server itself, Windows recognizes it and so does SQL, but it never seems to jump up above 10 percent. All of the other SQL boxes I've built have memory at least sitting at 90 percent or so.
It's running a QA environment that sees maybe 10 users on it at one given time, but I would expect our memory to be using a lot more than it is currently. Any ideas?

if you what that sql server reservs an amount of ram you have to configure minimum server memory.
In your case the min server memory is zero; then sql server takes only the ram it nees to answer the users' queries.
Change it in this way:
EXEC sp_configure N'min server memory (MB)', MBs_you_like
RECONFIGURE


How to change the default delimiter in PostgreSQL 9.1.9 using SET? From within a bash script I tried to use a replace and a detection on a field containing a pipe like:

IF position ('|' in var) IS NOT NULL THEN ...

...REPLACE(field, '|', '#')

The data itself was loaded into the DB using the COPY statement e.g.

COPY records FROM '$datasource' WITH DELIMITER AS ',' CSV HEADER NULL as '';

I am using psql (9.1.9) and the default field separator is pipe:

#> \f
#> Field separator is "|".

Ideally I would like to SET the default limiter in a CREATE OR REPLACE function at runtime like:

...
END; \$\$ LANGUAGE plpgsql SET config_param TO '#';

OR as a seperate statement just like
    SET config_param TO '#';

I searched the documentation and internet for the right name of the config parameter without luck. Any help appreciated.

This:

`IF position ('|' in var) IS NOT NULL`

is assuming that the result of position('|' in 'nopipes') is NULL. It is not, it's zero. position returns null only on null input.

regress=> select position('|' in 'abc|def|ghi');
 position
----------
        4
(1 row)

regress=> select position('|' in 'abcdefghi');
 position
----------
        0
(1 row)

regress=> select position('|' in null);
 position
----------

(1 row)

... so if you instead wrote:

IF position ('|' in var) <> 0

it should work fine.

The field separator \f in psql is irrelevant to this. It's only used to control how psql displays result sets. The server and functions on the server cannot see or be affected by this value. \f is just shorthand for \pset fieldsep, it's a psql-local control to choose the field separator for unaligned output:

regress=> \pset format unaligned
Output format is unaligned.
regress=> SELECT 1,2,3;
?column?|?column?|?column?
1|2|3
(1 row)
regress=> \pset fieldsep #
Field separator is "#".
regress=> SELECT 1,2,3;
?column?#?column?#?column?
1#2#3
(1 row)


Challenges with Key with Gaps? I had a mistake with a trigger which resulted in errors in PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit.
Each time during the error, the primary key of SERIAL increased.
After fixing the bug, the table measurements is

 measurement_id | measurement_size_in_bytes |             time
----------------+---------------------------+-------------------------------
              1 |                     77777 | 2015-07-14 18:29:56.858703+03
              2 |                       888 | 2015-07-14 18:29:56.882552+03
              3 |                       888 | 2015-07-14 18:30:15.505957+03
              4 |                       888 | 2015-07-14 18:41:01.878106+03
             39 |                     77777 | 2015-07-15 12:11:21.21391+03
             40 |                     77777 | 2015-07-15 12:11:59.551973+03
             41 |                     77777 | 2015-07-15 12:12:05.48982+03
             42 |                     77777 | 2015-07-15 12:13:02.402053+03
             43 |                     77777 | 2015-07-15 12:13:02.419412+03
             44 |                       888 | 2015-07-15 12:13:02.434728+03
         ...

where the amount of error statements has been 35 (= 39-4).
This jump in IDs may become a challenge later when I want to integrate this table with a partial index where order matters.

Table

CREATE TABLE measurements
    (
        measurement_id SERIAL PRIMARY KEY NOT NULL,
        measurement_size_in_bytes INTEGER NOT NULL,
        time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
        CONSTRAINT no_duplicate_measurements UNIQUE (time)
    );

What are the challenges of keys with gaps?

What you see is completely normal and expected.

  Note: Because smallserial, serial and bigserial are implemented using sequences, there may be "holes" or gaps in the sequence of
  values which appears in the column, even if no rows are ever deleted.
  A value allocated from the sequence is still "used up" even if a row
  containing that value is never successfully inserted into the table
  column. This may happen, for example, if the inserting transaction
  rolls back.

Unless you really need a gapless series of values, you don't have to worry about it.  This is how most (well over 99%, I guess) database tables work.

If you need it, be careful if you have concurrent inserts on the table - you will need some locking to exclude the possibility of choosing the same value by two or more sessions running in parallel.


What's the most efficient way to safely convert from Datetime2 back to Datetime I've got an etl project where the source has 60 datetime2 columns, and the target is entirely datetime.
A few days ago, we woke up to an empty data warehouse because one user at one hospital, for one patient, entered a surgery date of '1220-01-01'.
I temporarily got around the issue by couching each of the columns in a custom function:
    ALTER function [dbo].[scrub_datetime2](@date datetime2)
    returns date
    as
    begin

    declare @return date
    set @return = case when @date <= '1800-01-01' then null else @date end
    return @return

    end

And this works ok for getting rid of the red text, but It also doubled the time the job takes to run. I'm happy to do my part, keeping the hamsters in the server room warm through christmas, but I can't really justify losing 2.5 hours of precious overnight time to this issue.
Question How would most efficiently check that a datetime2 value will fit in datetime?

You should use the TRY_CONVERT function Docs
It will return null if value is incorrect
SELECT try_convert(datetime,'1700-01-01')


unexpected additional columns in most of my tables, command

SELECT column_name FROM information_schema.columns WHERE table_name='table'

worked as expected, but in one table i am getting additional tables that i was not expecting. What are those and how can i not see them ?

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.12    |
+-----------+

mysql> DESCRIBE users;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| userID      | int(11)      | NO   | PRI | NULL    | auto_increment |
| username    | varchar(20)  | YES  |     | NULL    |                |
| password    | varchar(100) | YES  |     | NULL    |                |
| email       | varchar(50)  | YES  |     | NULL    |                |
| avatar      | varchar(255) | YES  |     | NULL    |                |
| admin       | tinyint(1)   | YES  |     | NULL    |                |
| createdTime | datetime     | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

mysql> SELECT column_name FROM information_schema.columns WHERE table_name='users' ORDER BY ORDINAL_POSITION;
+---------------------+
| COLUMN_NAME         |
+---------------------+
| userID              |
| USER                |
| CURRENT_CONNECTIONS |
| username            |
| password            |
| TOTAL_CONNECTIONS   |
| email               |
| avatar              |
| admin               |
| createdTime         |
+---------------------+
10 rows in set (0.01 sec)

As you can see, I am getting 3 additional columns "USER, CURRENT_CONNECTIONS, TOTAL_CONNECTIONS" which i was not expecting. I need just column names as they are so that i can pass those columns to python for parsing

Is maybe name 'users' clashing with some internal Mysql column or other database?

Like @ypercubeᵀᴹ noticed and suggested:

SELECT column_name
FROM information_schema.columns
WHERE table_schema = 'mydatabase'
  AND table_name='users';

does the trick