Compression not available on SQL Server Standard? Options?

Since 2016sp1 all the compression features have been available in all editions, though that may not help you at all as upgrading your production environments (I'm assuming they run 2014 or earlier?) isn't something to just do on a whim.

  I then went to compress the tables I had deleted data from

If you are deleting the data is compression really what you need? If it is, then to suggest how you might reduce the size of the remaining data we'd need to know a lot more about that data and its use.

  Shrinking is something I guess I've always shy'd away from,

Shrinking is usually not what you want to do so you are correct to be cautious - if the data could grow back to use the space again then you might as well leave it allocated to the DB as this will avoid the future (potentially performance harming if they happen at an inconvenient time) growth operations, and the shrink process can cause significant fragmentation especially if (as I've seen done) the data files are shrunk on a regular basis.

But if you have reduced the data size of the database by archiving/deleting a large amount of data and you don't expect the amount of data to grow back to close to its original size any time soon, a one-off shrink should not have significantly concerning side-effects. But shrink to how large the data is expected to be after a reasonable length of time, don't shrink down to the smallest the current data will fit in.


How can I re-index a column using SQL Server Compact Edition?

Since you can't generate the ID artificially with a windowing function, you will need to add a step and create it as an IDENTITY field in another table.

Something like this

            --temp table to store all the new data
    CREATE TABLE #Hierarchy
    (
          HId INT IDENTITY
        , ParentID INT
        , ListIndex INT
    )

    --distinct parentids in hierarchial model
    SELECT DISTINCT ParentID
    INTO #Parents
    FROM TemplateItem

    --looping variables
    DECLARE
          @loopCnt INT
        , @curParentId INT

    --set looping variables (not a fan of cursors)
    SELECT @loopCnt=COUNT(ParentID), @curParentId=-1
    FROM #Parents

    --loop through parentids and reset the ListIndex
    WHILE @loopCnt > 0
    BEGIN
        --get the next ParentID
        SELECT @curParentId=MIN(ParentID)
        FROM #Parents
        WHERE ParentID > @curParentId

        --temp table to store the refreshed ListIndexes by ID
        CREATE TABLE #NewIndex
        (
              NewIndex INT IDENTITY
            , Id INT
        )

        INSERT #NewIndex
            (Id)
        SELECT Id
        FROM TemplateItem
        WHERE ParentID = @curParentId
        ORDER BY ListIndex

        INSERT #Hierarchy
            (ParentID, ListIndex)
        SELECT  t.Id,
                t.ParentId,
                Sub.NewIndex - 1 AS ListIndex -->Reset to 0 here

        FROM    TemplateItem t
        JOIN    #NewIndex Sub ON Sub.Id = t.Id

        --CleanUp and continue/stop loop
        DROP TABLE #NewIndex
        SET @loopCnt=@loopCnt-1
    END


Multiple values in one table; need them to be in separate columns

What you need is

a "student" table
a "major" table
a linking table (also known variously as an "intersection" or "association" or "joining" table). The fancy name for this is "Associative Entity" (if you read nothing else, read this!).

1 row per "student" in the student table - this is critical for good design - otherwise you are going to have to keep track of multiple copies of your student data. This will typically contain their student id, name(s), address, tel: & - PRIMARY KEY student_id.

1 row per "major" - again, no multiple copies of data. Typically contains, id major name, department, prerequisites.... &c. PRIMARY KEY - major_id.

The joining table will probably contain only 1 record per student - the brigher/more hardworking ones might have more than 1 record per student and will just simply contain (student_id, major_id) - the PRIMARY KEY in this case will just simply also be (student_id, major_id). So, a student with 2 majors will have 2 records &c.

You will obviously then have a department table - possibly a joining table for department-major for multi-disciplinary majors? I imagine that most (all) majors will have a primary department?

NEVER have a .csv type arrangement where you have multiple "facts" stored in the same field of a database  - there are two reasons for this:

1) it makes SQL programming impossible and

2) it's a breach of Codd's rules (the second one) which states:

  Rule 2: The guaranteed access rule:

  Each and every data (atomic value)
  in a relational data base is guaranteed to be logically accessible by
  resorting to a combination of table name, primary key value and column
  name.

Even if you don't understand them yet, there is a reason why Codd's rules are regarded as fundamental amongst database practicioners.

Start with this - break down the problem, determine your entities (or "objects", or just plain "things"). Have a table for each one. That and joining tables for "things" which are connected in a 1->n or m->n relationship and you'll at least have the basics right. Come back to us if you have more specific requirements after having done that.


How should I proceed?

I would try to load the newer version of PostGIS fresh on a 9.6 before you load your dataset. If successful, I would edit the dataset to remove anything to do with PostGIS's internal functions. I would then load my dataset without those offending functions (make sure whatever you're loading does not have PostGIS internal stuff).

Also worth mentioning PostGIS 2.0 is EOL already and unsupported.


SQL Server Database grew too much too fast

The database is being running on SQL Server 2000 Standard for the last 6 years in Full Recovery mode.

This is fin as long as you are doing regular full and log backups to keep the log file size under control. There are plenty of articles and questions on here regarding log management.

  In the beginning of this summer the database was about 5GB. Since then, the only thing we did beyond normal usage is some extensive deletions.

Large deletions are fine but understand that the space that data used was not released back to the operating system, unless you did a shrink of the data file.

  This week, there was a problem with the PC and I was forced to do a backup and restore it on an SQL Server 2008R2 Express and set the database in 2008 mode.

I hope you tested your code! T-SQL syntax changed a good bit from SQL 2000 to SQL 2008 R2. I have supported many databases that broke due to some of these changes. Also note that Express has a limitation on how large a database can grow, 10GB with SQL 2008 R2

  The backup file was about 1GB. When I restored it, the MDF was 9GB of size! I checked the old MDF and it was of the same size. I checked the size of the tables and they cannot reach the 9GB reported!

When you do a backup of the database, the backup is only going to grab the data itself along with some configuration information of the database. One of those configuration items is the initial size of the data and log file. I would expect if you checked your database properties you might find the initial size of your data file (MDF) is set to 9GB in size.

  I did a shrink but the size did not change.

A database shrink is likely what you performed. Although ill-advised, if you need to regain disk space or just bring the data file (MDF) of your database under control you will need to perform a data file shrink. Again there are plenty of articles on MSDN and blog post that provide the way of doing this.

  Is there a chance that the Full Recovery, can affect the size of the MDF files?

Nope.

  I am thinking of settinng the recovery model to Simple, back-it-up and restore it. Is it going to make a difference?

With the size of your data file, nope.


Can I create a stored procedure in mysql using a txt file?

If you can execute SQL and you have sufficient privileges to create a stored procedure then you can create the stored procedure.

I'm assuming you can execute SQL with the .sh file above since you are currently using it to run jobs.  Since it looks like you are using this to perform maintenance on a scheduled basis I am going to further assume that you want to update the database/deploy changes using the same mechanism.  If this is true I would caution against this plan.  While it might seem nice to automate a deployment at midnight while you're sleeping if anything goes wrong, you won't enjoy the fallout when your boss/customer/angry person X wakes you up because the world is on fire....


Query is slow on AlwaysOn Secondary node

1) Please check the performance metrics (CPU%, Memory, buffers) on the secondary node. Is it a reporting server. AlwaysOn has an option to send all read-queries to the secondary node.

2) AlwaysOn utilizes snapshot isolation. Can you validate if the tempdb is being heavily used? Please validate the write/read latency on tempdb and other databases. Here is one way to check: Query from Database Journal

DECLARE @Sample TABLE (
  DBName varchar(128)
 ,NumberOfReads bigint
 ,NumberOfWrites bigint)

INSERT INTO @Sample
SELECT name AS 'DBName'
      ,SUM(num_of_reads) AS 'NumberOfRead'
      ,SUM(num_of_writes) AS 'NumberOfWrites'
FROM sys.dm_io_virtual_file_stats(NULL, NULL) I
  INNER JOIN sys.databases D
      ON I.database_id = d.database_id
GROUP BY name

WAITFOR DELAY '00:05:00.000';

SELECT FirstSample.DBName
      ,(SecondSample.NumberOfReads - FirstSample.NumberOfReads) AS 'Number of Reads'
      ,(SecondSample.NumberOfWrites - FirstSample.NumberOfWrites) AS 'Number of Writes'
FROM
(SELECT * FROM @Sample) FirstSample
INNER JOIN
(SELECT name AS 'DBName'
      ,SUM(num_of_reads) AS 'NumberOfReads'
      ,SUM(num_of_writes) AS 'NumberOfWrites'
FROM sys.dm_io_virtual_file_stats(NULL, NULL) I
  INNER JOIN sys.databases D
      ON I.database_id = d.database_id
GROUP BY name) AS SecondSample
ON FirstSample.DBName = SecondSample.DBName
ORDER BY 'Number of Reads' DESC;

3) Did you try just doing a simple backup/restore onto the secondary node, removing AlwaysOn, and validating if the query is still slow?

4) I know you don't have NOLOCK hints in query. Can you try rebuilding the indexes on primary replica, See what happens? Query execution takes significantly longer on SQL Server Always On secondary replicas.

"When Snapshot Isolation is enforced, NOLOCK hints are ignored. The discrepancy in execution duration between the primary and secondary replicas occurs because the NOLOCK hint is ignored on the read-only secondary replica where Snapshot Isolation is enforced but not on the primary replica where Snapshot Isolation is not enforced by default. This causes the scan of the clustered index to have key order enforced on the secondary replica. On the primary replica, the NOLOCK hint takes precedence and does affect behavior. When the clustered index is highly fragmented, the enforcement of key order for the scan on the read-only secondary replica causes SQL Server to issue single-page reads. But on the primary replica, SQL Server does an allocation unit scan to read multiple pages per IO request."

5)  Can you validate what the settings are? Queries Maxdop and Memory Allocation as suggested from Kin above, another good idea

-- Find MaxDop
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'max degree of parallelism'
GO

-- Change MaxDop settings replace to 8
EXEC dbo.sp_configure 'max degree of parallelism', 8;
GO
RECONFIGURE;
GO
 -- Find Memory allocation on instance
 SELECT [name] AS [Name]
 ,[configuration_id] AS [Number]
 ,[minimum] AS [Minimum]
 ,[maximum] AS [Maximum]
 ,[is_dynamic] AS [Dynamic]
 ,[is_advanced] AS [Advanced]
 ,[value] AS [ConfigValue]
 ,[value_in_use] AS [RunValue]
 ,[description] AS [Description]
 FROM [master].[sys].[configurations]
 WHERE NAME IN ('Min server memory (MB)', 'Max server memory (MB)')


It looks like your instance of Management Studio is either corrupt or is in need of repair.

This could be for many reasons, including installing it on an unsupported version of Windows, or an unsupported version of .NET. I have previously seen problems like this when someone has installed Sql Server 2012 on Windows Server 2016 without enabling .NET 3.5 Runtime.

What I recommend you do, is to remove the version of SSMS that comes pre-packed with Sql Server 2012 and install the standalone version instead.

To uninstall your existing version of SSMS, follow the steps below:

Open Programs and Features (type appwiz.cpl in Run/CMD)
Locate Sql Server 2012 and select Uninstall/Change.
Go through the wizard, choosing the Sql Server instance you want to modify.
Untick Management Tools - Basic and Management Tools - Complete.
Click next to skip through all of the remaining steps.
Click finish to uninstall SSMS.

Once you have completely unsintalled the existing version of SSMS, go to Download Sql Server Management Studio (SSMS), then download and install the latest version.

As always, check that your system meets the requirements for SSMS before attempting the install.


Page life expectancy error in sql server

cntr_value = 788645

Your PLE is 788645, that is perfect and even more: it reflects that your server happily doing nothing.

So, don't worry and don't look your monitoring tool: it reflects something known only to its author

P.S. 788645 seconds = 9,... days, that is right, yor image also shows 2017-09-03 that, I think, is server last restart time. Since that day no more than 2Gb of data was passing through your buffer pool and it's still there, so, one more time: no memory pressure, no working activity on this server.


SQL Server Upgrading Compatibility Level from 2005 to 2012

However what kind of issues can occur going from 90 to 110? What items can break?

Well this is basically very subjective, while I cannot tell you what could break from the code side I could definitely point you towards article which list down all breaking changes. Please refer to Breaking Database Engine Changes in SQL Server 2012.

With my past experience changing database compatibility level does not makes database totally dysfunctional. The compatibility level only defines how certain features would work in certain compatibility level. To help you again MS has listed down such changes in ALTER DATABASE (Transact-SQL) Compatibility Level. The article is thorough and includes details of all versions of SQL server

Using above 2 links you need to jot down the what could possibly fail in your scenario and fix it beforehand. After this is done the process is easy you run the command

ALTER DATABASE database_name
SET COMPATIBILITY_LEVEL = 110

Please make sure you run this during off production hours. Now you can ask users to test if something fails and it is really important for it to work run the command again and change compatibility level back to 90. So whole process also includes bit of hit and trial method.


Why is the result of a totally deterministic and all-encompassing "case" in select query in view nullable?


PL/pgSQL function + client-side lo_import

psql is the wrong tool for this kind of job because it basically can't get results into variables. It's good for user interface and static scripts, but for
interactive scripting where results from one operation are to be piped into another, it falls short. The best solution is to switch to a real scripting language such as Perl or Python that have good client-side APIs, including large-objects streaming.

When limited to bash, it's still possible to operate psql from the outside with coproc to get results out and back in, but it's much more complicated.


How to check multidimensional array of arrays contains array?

Technically there is no such thing as a two-dimensional array in Postgres. Quote from the manual:

  The current implementation does not enforce the declared number of dimensions either. [...] So, declaring the array size or number of dimensions in CREATE TABLE is simply documentation; it does not affect run-time behavior

So the value array[[442,2],[443,2]] is internally stored as array[442,2,443,2] and that's the reason why array[[442,2],[443,2]] @> array[2,443] is true, because it is internally represented as array[442,2,443,2] @> array[2,443] which obviously is true.

You can however do that if you declare an array of a row type.

create type idtype as (id integer, type integer);

Then you can do the following:

select array[(442,2), (443,2)]::idtype[] @> array[(442,2)]::idtype[];
--> true

but

select array[(442,2), (443,2)]::idtype[] @> array[(2,442)]::idtype[];
--> false


What's the reason for using double inner join in this SQL statement?

It might help to rewrite the query like this, so it is obvious that the 2 joins are different, i.e. the joins are to different subsets (of the same table):

FROM   maintable
       INNER JOIN secondarytable
               ON maintable.id1 = secondarytable.a_id1
       INNER JOIN table1
               ON secondarytable.id2 = table1.id3
              AND table1.type = '0'
       INNER JOIN table1 Table1Alias
               ON secondarytable.id2 = Table1Alias.id3
              AND Table1Alias.columna = 'conn'
       INNER JOIN
       ...
WHERE  LEFT(secondarytable.type123, 2) BETWEEN '01' AND '09'
       AND secondarytable.type456 = 'cate'


How to define the table structure for measured data in sqlite

Your second approach is not normalized: adding another sensor would require creating a new table, and querying multiple sensors would be horribly complex.

When all four sensors get a new value every second, and if you have multiple tables, then the database would have to update four tables. A single table is certainly more efficient.

For fast queries on time ranges, you need an index on the timestamp column.
This implies that every INSERT also needs to update the index.
(If you want fast queries on time ranges for a single sensor, you need another index that contains both the sensor_id and timestamp columns.)

Please note that you do not need AUTOINCREMENT when you never delete rows.
(Autoincrement In SQLite)


Surprising results from assigning multiple variables in FOR loop

Guess I am to blame for this confusion.
The code example is from my answer to your previous question, which had a subtle bug (now fixed).

What was going wrong?

jsonb_each_text() is a set-returning function, it returns a set of rows. When called with:

SELECT * FROM jsonb_each_text('{"a":1, "b":2}')

it returns:

 key | value
-----+-------
 a   | 1
 b   | 2

But when called like above with:

SELECT jsonb_each_text('{"a":1, "b":2}')

it returns:

 jsonb_each_text
-----------------
 (a,1)
 (b,2)

Each row is returned with a single value, a row value, the text representation of which is as displayed.

Now, the assignment in:

  FOR _key, _value IN SELECT jsonb_each_text($1) ...

assigns the first value per row to the first variable provided, which is _key in the example. The row value is cast to text in the assignment. Since there is no second value, _value is set to NULL. So, the later:

 RAISE NOTICE '%: %', _key, _value;

outputs the messages:

NOTICE:  (a,1): <NULL>
NOTICE:  (b,2): <NULL>

Which can be pretty misleading. (But note the dangling : <NULL>!)

Change to:

FOR _key, _value IN SELECT * FROM jsonb_each_text($1)

.. and the mists should clear.


The Benefits of Employing Table Locking
Every time you make a change to a table, MySQL must eventually commit that change to disk. Making a change to a table that has indexes requires even more work as MySQL needs to perform the additional step of updating the indexes. The effect of this additional step can be compounded when you make numerous changes to the data at one time, such as by performing an UPDATE to key (i.e., code) fields.  In this case, MySQL might have to perform hundreds or even thousands of write operations.

When we lock a table, MySQL won't update the index keys for the locked table until we unlock it. That can result in substantial time savings. Depending on the complexity of the indexing, judicious use of table locking can speed things up drastically.

Disadvantages of Table Locking

Table locking still allows multiple processes to read from a table at the same time, but if a process wants to write to a table, it must first get exclusive access, meaning it might have to wait for other sessions to finish with the table first. During write actions, all other sessions that want to access this particular table must wait until the write is done.

2.Table locking runs into trouble when the disk runs out of free space and needs to make room before the session can proceed. When that happens, all sessions that want to access the affected table(s) are made to wait until more disk space becomes available.

SELECT statements that take a long time to run prevent other sessions from updating the table in the meantime, making the other sessions appear slow or unresponsive. While a session waits for exclusive access to the table for updates, other sessions that issue SELECT statements will queue up behind it, reducing concurrency even for read-only sessions.


Another approach is you can layer your queries as sub-queries, which allow you to capture all results, like so.  Also you most likely wanted to use "sum" instead of "count".

select Rpt1, Rpt2, Balance = @totalsales-Rpt1-Rpt2
FROM   (Select Rpt1 = sum(case when empID = '1931' Then 1 else 0 end)
        ,      Rpt2 = sum(case when empID = '1831' Then 1 else 0 end)
        From totalsaleinfo
        Where saleid = @saleid) AS sums


How to implement a multi-user account facility in a database

You should have an accounts table and a users table, with either a one-to-many or a many-to-many relationship between the two.

As an example of the many-to-many relationship:

CREATE TABLE Accounts
(
    AccountID int NOT NULL
    , AccountName varchar(30) NOT NULL
);

CREATE TABLE Users
(
    UserID int NOT NULL
    , UserName varchar(30) NOT NULL
);

CREATE TABLE AccountsUsers
(
    AccountID int NOT NULL
        FOREIGN KEY
        REFERENCES Accounts(AccountID)
    , UserID int NOT NULL
        FOREIGN KEY
        REFERENCES Users(UserID)
    , UserLevel int NOT NULL
);

The UserLevel represents the type of account access; account manager, marketing executive, marketing assistant, etc.

The one-to-many relationship would look like:

CREATE TABLE Accounts
(
    AccountID int NOT NULL
        CONSTRAINT PK_Accounts
        PRIMARY KEY CLUSTERED
    , AccountName varchar(30) NOT NULL
);

CREATE TABLE UserLevels
(
    UserLevelID int NOT NULL
        CONSTRAINT PK_UserLevels
        PRIMARY KEY CLUSTERED
    , UserLevelDesc varchar(30) NOT NULL
);

CREATE TABLE Users
(
    UserID int NOT NULL
        CONSTRAINT PK_Users
        PRIMARY KEY CLUSTERED
    , UserName varchar(30) NOT NULL
    , AccountID int NOT NULL
        CONSTRAINT FK_Users_AccountID
        FOREIGN KEY
        REFERENCES Accounts(AccountID)
    , UserLevelID int NOT NULL
        CONSTRAINT FK_Users_UserLevelID
        FOREIGN KEY
        REFERENCES dbo.UserLevels(UserLevelID)
);

Insert some sample data:

INSERT INTO dbo.Accounts (AccountID, AccountName)
VALUES (1, 'Microsoft');

INSERT INTO dbo.UserLevels (UserLevelID, UserLevelDesc)
VALUES (1, 'User')
    , (2, 'Manager')
    , (3, 'Owner');

INSERT INTO dbo.Users (UserID, UserName, AccountID, UserLevelID)
VALUES (1, 'Bill Gates', 1, 3)
    , (2, 'Sataya Nadella', 1, 2);

Query combining the three tables:

SELECT a.AccountName
    , u.UserName
    , ul.UserLevelDesc
FROM dbo.Accounts a
    INNER JOIN dbo.Users u ON a.AccountID = u.AccountID
    INNER JOIN dbo.UserLevels ul ON u.UserLevelID = ul.UserLevelID

Results:

╔═════════════╦════════════════╦═══════════════╗
║ AccountName ║    UserName    ║ UserLevelDesc ║
╠═════════════╬════════════════╬═══════════════╣
║ Microsoft   ║ Bill Gates     ║ Owner         ║
║ Microsoft   ║ Sataya Nadella ║ Manager       ║
╚═════════════╩════════════════╩═══════════════╝

Cleanup:

IF OBJECT_ID(N'dbo.UserLevels', N'U') IS NOT NULL
DROP TABLE dbo.UserLevels;
IF OBJECT_ID(N'dbo.Users', N'U') IS NOT NULL
DROP TABLE dbo.Users;
IF OBJECT_ID(N'dbo.Accounts', N'U') IS NOT NULL
DROP TABLE dbo.Accounts;


Every graph graph dbms seems to be handling these questions with their own unique ways. From what I know about neo4j, It appears that they can handle this somewhat similarly to how Dgraph Labs can. By placing facets, aka edge properties, on the edges, a kind of point in time graph could be interpreted. (I know more about Dgraph, so that is why I lean that direction with links.)
Here are a few discussions around this topic in Dgraph community:

Storing and querying historical data
Facets in GraphQL
Versioned predicates
Adding property to the edges
Querying graph structure at a certain point in time


Merge multiple rows in table setting one column based on value in rows

According to your text if any of them are true is must be true, otherwise is false:

select
    key,
    case
      when sum(case when B is true then 1 else 0 end) > 0 then true
      else false
    end as result
from
   t
group by
    key;

db<>fiddle here


If the "SQL Server VSS Writer" service is running IO on the database files will be "frozen" while a snapshot is made of the databases and the database state (full database backup) will be included with the VM backup. This will not lock down the SQL server but will in some cases make it slower.

If the service is not running the database files will not be included in the backup and you have to use other means to backup them. This needs to be discussed with your DBA in many cases having full backups taken with the VM backups breaks the backup chain the DBA is maintaining and you should definitely make those backups more often than weekly and backup your transaction log as well. The VM backup (snapshot) can also affect performance of the SQL Server even though it's not making a snapshot of the database files.


Scheduling a task that happens very frequently in Sql Server

I've used SQL Server Agent to run jobs every 10 seconds. It's OK.

If you don't have it, you can use the Windows Scheduler to run osql or sqlcmd as required.
Or say Quartz.net in your app. Or anything that can call a database on a schedule (AutoSys etc)


SQL Server - Standard Deviation in a Windowed Query

I found a solution here. Essentially, you have to compute it using statistics:

SELECT DISTINCT
    TestName
    ,[BatchSize]
    ,[Iterations] = COUNT(*) OVER(PARTITION BY TestName, [BatchSize])
    ,[Min] = PERCENTILE_DISC(0) WITHIN GROUP(ORDER BY RuntimeMs) OVER(PARTITION BY TestName, [BatchSize])
    ,[5th] = PERCENTILE_DISC(0.05) WITHIN GROUP(ORDER BY RuntimeMs) OVER(PARTITION BY TestName, [BatchSize])
    ,[10th] = PERCENTILE_DISC(0.1) WITHIN GROUP(ORDER BY RuntimeMs) OVER(PARTITION BY TestName, [BatchSize])
    ,[25th] = PERCENTILE_DISC(0.25) WITHIN GROUP(ORDER BY RuntimeMs) OVER(PARTITION BY TestName, [BatchSize])
    ,[Avg] = PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY RuntimeMs) OVER(PARTITION BY TestName, [BatchSize])
    ,[75th] = PERCENTILE_DISC(0.75) WITHIN GROUP(ORDER BY RuntimeMs) OVER(PARTITION BY TestName, [BatchSize])
    ,[90th] = PERCENTILE_DISC(0.9) WITHIN GROUP(ORDER BY RuntimeMs) OVER(PARTITION BY TestName, [BatchSize])
    ,[95th] = PERCENTILE_DISC(0.95) WITHIN GROUP(ORDER BY RuntimeMs) OVER(PARTITION BY TestName, [BatchSize])
    ,[Max] = PERCENTILE_DISC(1) WITHIN GROUP(ORDER BY RuntimeMs) OVER(PARTITION BY TestName, [BatchSize])
    ,[StdDev] = SQRT((
        (COUNT(RuntimeMs) OVER (PARTITION BY TestName, [BatchSize])
        * SUM(RuntimeMs*RuntimeMs) OVER (PARTITION BY TestName, [BatchSize]))
        - POWER(SUM(RuntimeMs) OVER (PARTITION BY TestName, [BatchSize]), 2))
        / (COUNT(RuntimeMs) OVER (PARTITION BY TestName, [BatchSize])
        * COUNT(RuntimeMs) OVER (PARTITION BY TestName, [BatchSize]) - 1))
FROM #TimingResult

P.S. Feel free to use this statement when performance testing code. That's why I wrote it (in particular for this question).


Unable to alter authorization on an mirroring endpoint to a login with a slash in the name

Try surrounding it with the quoted identifier:

alter authorization
on endpoint::Mirroring
to [domain\sys-admin-group]

Notice the square brackets.


This is one (of many) situation where Stored Routines are less useful than one would like.

A possible workaround:  Build a VIEW without the IN clause, then use the IN clause when fetching from the VIEW.


Reorganizing an index does not update the statistics on an index.  Reorganize will ensure that the B+ tree is balanced, since SQL Server doesn't auto balance trees during operation.  Rebuilding an index will recreate the index, allowing the engine to update the statistics on an index.

You should consider periodically reorganizing and updating statistics on lightly fragmented queries granted you can spare the maintenance time.  You can also try to update statistics periodically using samples, if your data is homogenous enough.

You can read more about Update Statistics here

http://msdn.microsoft.com/en-us/library/ms187348.aspx

Kimberly Tripp blogs heavily on index maintenance on http://sqlskills.com

Check out her links for Statistics and Indexes.


If you would have the age as an attribute in the People dimension in addition to having it as measure, you could define your bands as calculated members of this attribute hierarchy (calculated members do not need to be calculated measures, they can be members of any hierarchy!):

WITH Member [People].[Age].[0-26] AS
            Aggregate([People].[Age].[0] : [People].[Age].[26])
     Member [People].[Age].[26-51] AS
            Aggregate([People].[Age].[26] : [People].[Age].[51])
     Member [People].[Age].[Over 50] AS
            Aggregate([People].[Age].[51] : null)
SELECT
  NON EMPTY {[Measures].[Count of People]} ON COLUMNS,
  NON EMPTY
    [People].[Gender].[Gender]
    *
    [People].[Some].[Other Attributes]
    *
    { [People].[Age].[0-26], [People].[Age].[26-51], [People].[Age].[Over 50] }
    ON ROWS
FROM [MyCube]
WHERE ( ... slicer here .... )

Please note that I kept the border ages 26 and 51 in two bands each, as the names of your bands seem to suggest to do this. You can do this in MDX, as each definition of a calculated member is independent of the others, and hence it is not forbidden to have intersections between calculated members. But you probably would opt to adapt the expressions and only include each age in one band.

The way you tried it does not work, as a set in MDX consists of tuples, while you deliver a string from your IIF expression.


Why intended integrity constraint cannot be enforced?

(Note: you have declared 2 constraints on this table, a PRIMARY KEY constraint and a CHECK constraint. The answer is about the second one.)
The CHECK constraint is trying to enforce that:

the number of rows in both tables should be less than 100

So, it should - if it worked - allow one table have 50 and the other 49 rows, or 1 and 98 or 5 and 10 or ... but none should have 100 or more. If one has 90, the other should have no more than 9 and any transaction trying to insert more rows, should be denied (raising a constraint error).
At least that's what it looks like it's trying to do. Now the problem or rather the two problems of the constraint:

A CHECK constraint is a row constraint, in almost all implementations. So it is checked for each row inserted or updated. This is an implementation issue and no current DBMS has implemented constraints that involve subqueries (except Firebird which claims to do so, but I haven't tested it).
There are a few DBMS that allow functions to be used in CHECK constraints (and the functions could contain subqueries), so the limitation can be "worked around" but that leads to all kinds of concurrency issues and the problem is not really solved. The advice, even in those DBMS, is not to have subqueries in CHECK constraints.
In my opinion, a constraint that involves many rows of the table (all of them in this case), should be an ASSERTION, not a CHECK constraint.

Second and more serious is that a CHECK constraint is a constraint on one table. It is not an ASSERTION (those are constraints that can span across multiple tables).
So even if there was an implementation that allowed CHECK constraints with subqueries, the result would be what one would expect. One way to understand why is that the constraint would only be checked when a row in Sailors would be inserted or updated.  This would lead to these problematic situations:
We insert 200 rows in the Boats table. The constraint would not be checked so that would be allowed. Then we try to insert a row in Sailors. the constraint is checked and the row is forbidden. But have we managed to enforce our intention? No, because the total of the two tables is 200 rows, which is more than 100.
We insert 5 rows in Sailors. No problem so far. Then 200 rows in the Boats table. The constraint would again, as before, not be checked so that would be allowed. Then we try to update a row in Sailors. The constraint is checked and we get an error. The UPDATE fails. But we still have 205 rows in total, so why should the update fail?

As a conclusion, there are two issues with this constraint. First, that current implementations do not allow subqueries in CHECK constraints and second that it should be an ASSERTION and not a CHECK constraint, as it involves more than 1 table.
And note that none DBMS has implemented ASSERTION so far (at least those SQL based, there are a few based on Tutorial D that have.)


A Schema's owner is always a User (or Database Role).  There may be a Schema with the same name as a User, because in very old versions of SQL Server a User and a Schema were the same thing.  So there's still a User called dbo and a Schema called dbo in every database.

A Schema's owner is a User who has full control of the schema, and who will be the default owner of every object added to the schema.  Owning an object suppresses permissions-checking on the object, and plays an important role in Ownership Chains.


Reason for multiplicity of character/collation settings in MySQL?

Version 5.7 made good strides toward settling on utf8mb4; the next version, 8.0, is there.

But, changing anything under the hood is all too likely to cause grief.  MySQL learned that lesson when "fixing" the handling of ß in utf8_general_ci collation many years ago.  It broke lots of users' tables.  So, they came out with the kludge utf8_general_mysql500_ci.

The grief over the 767-byte limit on a column in an index would break blindly changing utf8 to utf8mb4.

I predict that utf8 will live on, but only for compatibility for those upgrading.  Meanwhile, a really UTF-8-compliant utf8mb4 will be the default.  And there will be a succession of collations such as utf8mb4_unicode_ci, utf8mb4_unicode_520_ci, utf8mb4_0900_ai_ci, and more will keep up with the Unicode standard (4.0, 5.20, 9.0), which keeps changing.

The charset and collation for a database is only the default for subsequently created tables.  Ditto for table:column.

To change the charset/collation for an entire table:  ALTER TABLE .. CONVERT TO ...

I think I am sane.  This makes sense to me:

CREATE TABLE People (
    name        ... CHARACTER SET utf8mb4,
    postal_code ... CHARACTER SET ascii
);

It is even a performance issue if you let postal_code with utf8mb4 unnecessarily explode by a factor of 4 during certain operations.

As for why things are crashing in Aurora, I don't see enough info to help with this question.  And, since I have not heard of anything similar in MySQL or MariaDB, I suspect is may be an Aurora bug.  (Aurora is MySQL plus low level improvements; I have not heard of charset issues, but who knows?)


Install SQL Server Express r2 2008 using a configuration file

Unfortunately, you cannot install SQL Express without Administrator rights on the machine and it's not something you can set in the config file or on the command line. If being able to install without Administrator rights is a requirement, you may want to investigate using SQL Server Compact Edition.

See the following MSDN topics for more information:

Using SQL Server Compact 3.5 (Visual Studio)
How to: Deploy a SQL Server Compact 3.5 Database with an Application
SQL Server Compact 3.5 SP2 Books Online
Local Data Overview
Choosing Between SQL Server 2005 Compact Edition and SQL Server 2005 Express Edition -- A little dated now, but much of it still applies.


Replicating data to a head office with limitations

You might be able to do something with actual SQL replication, but if you've already got an existing routine, just modify it to filter specifically on a new bit field in every table: uploaded or whatever you want to call it.

So when the register writes the changes to the table, make sure it clears that bit. Or, if you can't alter that part of the routine, put an ON UPDATE trigger on the table that clears it when a row is updated.

Then make sure that the "upload to corporate" routine sets all those bits when it copies the data up.

Or, what might be even more useful, is to have a "last changed" field updated by your register uploads, and a "last uploaded to corporate" field. That way you can compare the two.


Un-attaching SPL 2008 BD not willing to Attach

I was trying to Shrink the files, but only had 10GB's of space left on the Array.

Get another LUN assigned to you. Extend the LUN. Buy a bigger disk. Add an external drive. Do something other than compromise your data because of disk space shortages. This is a problem that can be solved without ever touching your database in ways it doesn't like.

  I Stopped SQL and deleted the Test Log File

Please don't ever do this - it is a pretty good way to corrupt your data, because stopping the SQL Server service and deleting the log file can make it impossible for SQL Server to recover in-flight transactions that were running when you stopped the service.

  I tried Un-attaching the DB, but now I can't Attach it since the file is missing.

You can try CREATE DATABASE ... FOR ATTACH_REBUILD_LOG, which will re-create the log file (see the documentation), but this still may not help because it still requires a clean shutdown of SQL Server (we don't know that that happened), and who knows what has happened to your .mdf file in the meantime.

My suggestions:

Forget trying to re-attach the .mdf file and just restore your backup.
Read this post in full:

Why Does the Transaction Log Keep Growing or Run Out of Space?

If that is too much for you, and you care about your data at all, you need to consider hiring someone who can handle it. You don't necessarily need someone full-time; a remote DBA service will probably be sufficient.


How do I change table used in from clause of a select based on udf parameter?

You don't need a multi statement TVF here.

You can use an inline one

CREATE FUNCTION dbo.testing(@switch VARCHAR(1))
RETURNS TABLE
AS
    RETURN
      (SELECT number
       FROM   A
       WHERE  @switch = 'A'
       UNION ALL
       SELECT number
       FROM   B
       WHERE  @switch = 'B')

If the value passed for @switch is a variable or parameter (rather than a constant) you may find you get better plans if you use OPTION (RECOMPILE) with this. Particularly if the cardinality of A and B is very different (likely even without the hint only the relevant one will be accessed as the table access will be under a filter with a startup expression predicate).


The simplest is to nest it in another select with the appropriate condition:

select ID, JobID, NAME, ORDERNUM, STARTAFTER, FINISHBEFORE, START_OFFSET_MINS, STILL_REQUIRED
from (
    Select ID, JobID, NAME, ORDERNUM, STARTAFTER, FINISHBEFORE,
           START_OFFSET_MINS,
           TOTAL_MINUTES_REQ -
                 (SELECT coalesce(SUM(TABLE_PROCESSTIMES.END_TIME-TABLE_PROCESSTIMES.START_TIME) / 60, 0)
                  FROM TABLE_PROCESSTIMES
                  WHERE TABLE_PROCESSTIMES.PROCESSID = TABLE_PROCESSES.ID AND TABLE_PROCESSTIMES.START_DATE <= '24.04.2018'
                 ) AS STILL_REQUIRED
    FROM TABLE_PROCESSES
    ORDER BY JOBID, ORDERNUM
) a
where still_required <> 0


Find SQL text from Plan Diagram in SentryOne Plan Explorer

Unfortunately, there's not really a way to do this "automatically" that I'm aware of.

An execution plan operator doesn't necessarily map to something in the query text.  Some simple cases of this are functions and views.  Imagine a simple query like this:

SELECT * FROM dbo.vwOrderDetails;

The execution plan will probably have scans or seeks against dbo.Orders and dbo.OrderLineItems (whatever is in the view definition).  But there is nothing in the plan XML that indicates those scans and seeks "map back" to dbo.vwOrderDetails.

Another example would be execution plan operators that don't map easily to any specific SQL text.  For instance, a Sort operator might map to an ORDER BY clause.  But it might be a sort introduced to satisfy a MERGE JOIN.

In your specific case, your best bet is probably to look at the STUFF ... FOR XML PATH portions of your query and try to reason about which one is the node in question (maybe based on what operators are flowing into it, or based on the output list).

Another option would be to break the query up into smaller chunks that can be run separately to narrow down what is what.  You could comment out JOINs and / or items in the SELECT list until the XML Reader TVF node disappears, and then you'll know which part of the query text it's coming from.


Check out Basic Availability Groups. These are available in Standard Edition (and Developer) and offer exactly the functionality you're after.
You have two replicas, a Primary and Secondary. The client application connects to a shared listener name, and read and Write workload between the client application and database occurs on the primary replica through this listener. Data changes are synchronised to the secondary replica behind the scenes.
When the primary replica fails, if you have configured automatic failover then the availability group moves to the secondary replica, and the read/write workload begins working off the secondary replica. As this still occurs through the listener name, no application change is required.
After failover, the secondary replica becomes the new primary and once the old primary is online again, it becomes the new secondary. Data synchronisation resumes, but in the other direction to ensure that you can failover back to the original primary at some point if needed.
Check out this Docs article for an overview of Availability Groups. Note that Basic AGs function just like normal AGs but for Standard Edition only, so they have some limitations.


What are the risks (if any) of sharing a service account on more than one server?

Risk mitigation would indicate creating a separate account for each service on each machine.  The level of work required to create the accounts necessary is extremely minimal, but the unknown risks that accompany not doing so are quite high, according to Microsoft's own recommendations.
Microsoft Best Practices recommend using separate service accounts for all services.
See http://msdn.microsoft.com/en-us/library/ms144228.aspx#isolated_services for details.
The salient points being:

Isolate Services
Isolating services reduces the risk that one compromised service could be used to compromise others. To isolate services, consider the following guidelines:
Run separate SQL Server services under separate Windows accounts. Whenever possible, use separate, low-rights Windows or Local user accounts for each SQL Server service. For more information, see Configure Windows Service Accounts and Permissions.

There is also a KB talking about securing SQL Server that mentions how to configure service accounts properly:
http://support.microsoft.com/kb/2160720

When choosing service accounts, consider the principle of least privilege. The service account should have exactly the privileges that it needs to do its job and no more privileges. You also need to consider account isolation; the service accounts should not only be different from one another, they should not be used by any other service on the same server. Do not grant additional permissions to the SQL Server service account or the service groups. Permissions will be granted through group membership or granted directly to a service SID, where a service SID is supported. For more details please refer to Books Online Topic Setting Up Windows Service Accounts.

Technet has an article, titled Configure Windows Service Accounts and Permissions at http://technet.microsoft.com/en-us/library/ms143504.aspx that has this to say:

Security Note:  Always run SQL Server services by using the lowest possible user rights. Use a MSA or virtual account when possible. When MSA and virtual accounts are not possible, use a specific low-privilege user account or domain account instead of a shared account for SQL Server services. Use separate accounts for different SQL Server services. Do not grant additional permissions to the SQL Server service account or the service groups. Permissions will be granted through group membership or granted directly to a service SID, where a service SID is supported.

"MSA" in the above paragraph refers to "Managed Service Accounts" which is the default for installations on Windows 7 or Windows Server 2008 R2 and above.  Managed Service Accounts are defacto unique to each machine.
As an aside, one issue I think about when configuring multiple servers to run under the same service account is account lockouts.  If you use a single service account for all SQL Servers, and the service account gets locked out, all your servers might be affected.  If you have one account per service, at most one server can be affected by a lockout.


How to safely add column to replicated mysql database

"Safely" can have several meanings:

If you mean transactionally-safe, that is not possible, as you are using MyISAM. Operations on MyISAM are not guaranteed to be atomic, and so recoverable. Fully crash-safe tables require InnoDB (or Aria). The good news is that most MyISAM ALTER TABLE operations are implemented by recreating the table as a separate set of files, so in the event of a crash, nothing should be lost
If you mean safe, as in no operational disruption, a table like that may take a couple of minutes to be ALTERed, during which the original table could be read without problems for most of the process. Writes to that table, however, are blocked "Waiting for table metadata lock" and queued during the whole process. CPU and IOPS will increase -as you are doing a copy of the whole table-, but nothing alarming unless you are on full capacity already.
Regarding replication, ALTER TABLE will be transmitted through the replication process in a fully consistent manner. However, no writes to that table are sent to the slave (as they are not applied to the master) until the alter finishes. The table is not ALTERed and locked on the slave until then, when the alter will be applied. Replication will pause for that period (as replication is applied serially). Everything will be done consistently (as you shouldn't be doing out of band writes on the slave), but a lag equal to the time the alter takes to execute on the slave will occur.

If lag is a no-go for you, I recommend you to use an online alter table tool like pt-online-schema-change. The tools is not without problem (as rows are copied through SQL, it will generate a lot of traffic through the binary log), but a) it will allow writes to the table while the copy is done -although with reduced throughput, due to using MyISAM- b) it will minimize lag between the nodes, as the tables on the master and on the slave require almost no locking at all c) it will throttle automatically its own operations, stopping them temporarily if it detects that a slave more than 1 seconds (by default) behind the master due to the increased replication traffic.


Since your question tag says SQL Server 2016, you could take advantage of sys.dm_exec_describe_first_result_set which became available in SQL Server 2012.

  This dynamic management function takes a Transact-SQL statement as a
  parameter and describes the metadata of the first result set for the
  statement.

--Demo set up
DROP TABLE IF EXISTS [dbo].[Customers]
CREATE TABLE [dbo].[Customers](
    [CustomerID] [nchar](5) NOT NULL,
    [CompanyName] [nvarchar](40) NOT NULL,
    [ContactName] [nvarchar](30) NULL,
    [ContactTitle] [nvarchar](30) NULL,
    [Address] [nvarchar](60) NULL,
    [City] [nvarchar](15) NULL,
    [Region] [nvarchar](15) NULL,
    [PostalCode] [nvarchar](10) NULL,
    [Country] [nvarchar](15) NULL,
    [Phone] [nvarchar](24) NULL,
    [Fax] [nvarchar](24) NULL
) ON [PRIMARY]
GO

DROP VIEW IF EXISTS CustomerView
GO
CREATE VIEW CustomerView
AS
SELECT *
FROM dbo.Customers
GO

-------------------------------
--The solution
SET NOCOUNT ON;

DECLARE @sql NVARCHAR(MAX)
    ,@cols NVARCHAR(MAX) = N'';

SELECT @cols += N',' + NAME + ' ' + system_type_name
FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM dbo.CustomerView', NULL, 1);

SET @cols = STUFF(@cols, 1, 1, N'');
SET @sql = N'CREATE TABLE #tmp(' + @cols + ');'
SET @sql = replace(@sql,',',',' + char(10))
print @sql

Results:

CREATE TABLE #tmp(CustomerID nchar(5),
CompanyName nvarchar(40),
ContactName nvarchar(30),
ContactTitle nvarchar(30),
Address nvarchar(60),
City nvarchar(15),
Region nvarchar(15),
PostalCode nvarchar(10),
Country nvarchar(15),
Phone nvarchar(24),
Fax nvarchar(24));


Different execution plans depending on columns selected from CTE

The plan without row number is below.

This is assigned a cost of 44.866.

You have a TOP without ORDER BY so SQL Server just needs to scan the clustered index and as soon as it finds the first 30,000 rows matching the predicate it can stop.

The table has 13,283,300 rows. A full clustered index scan is costed at 730.467 + 14.6118 = 745.0788 but this gets scaled down to 43.9392 because of the TOP.

Applying the same scaling of 5.9% to the number of rows in the table this would imply that SQL Server estimates that it will only have to scan 783,350 rows before it finds 30,000 matching the WHERE and can stop scanning.

NB: You say that only 474,296 rows match this predicate in the whole table but 508,747 are estimated to. That means that on average one in every 26.1 (13283300/508747) rows is assumed to match the filter. So it is estimated that 30,000 * 26.1 rows ( = 783K) will be read.

When you select * that means that the rownum column must be calculated. the plan for this is below. It is costed at 69.1185

You have an index on COLUMNE that can be seeked into. This satisfies the range predicate on COLUMNE >= 1472738400000 AND COLUMNE <= 1475244000000 and also supplies the required ordering for your row numbering.

However it does not cover the query and lookups are needed to return the missing columns. The plan estimates that there will be 30,000 such lookups. There may in fact be more as the predicate on COLUMNF = 1 may mean some rows are discarded after being looked up (though not in this case as you say COLUMNF always has a value of 1).

If the row numbering plan was to use a clustered index scan it would need to be a full scan followed by a sort of all rows matching the predicate. 69.1185 is considerably cheaper than the 745.0788 + sort cost so the plan with lookups is chosen.

You say that the plan with lookups is in fact 5 times faster than the clustered index scan. Likely a much greater proportion of the clustered index needed to be read to find 30,000 matching rows than was assumed in the costings. You are on SQL Server 2014 SP1 CU5. On SQL Server 2014 SP2 the actual execution plan now has a new attribute Actual Rows Read which would tell you how many rows it did actually read. On previous versions you can use OPTION (QUERYTRACEON 9130) to see the same information.


I think what you want can be achieved by adding in the column list a "depth" or "level" that increases by one for each recursion:

WITH RECURSIVE uplines(user_id, referred_by_user_id, depth) AS (
  SELECT t.user_id,  t.referred_by_user_id, 0
  FROM postgres.user AS t
  WHERE t.user_id = $userId
  UNION ALL
  SELECT t.user_id, t.referred_by_user_id, a.depth + 1
  FROM postgres.user AS t JOIN uplines AS a ON t.user_id = a.referred_by_user_id
  WHERE a.depth < $depth
)
SELECT user_id, depth
FROM uplines
ORDER BY depth ;


Finding five-minute interval prior to current time

Try this query

    select date_add(now(), interval -5 minute) five_mins_interval,
    date_add(now(), interval -10 minute) ten_mins_interval, now()
    currentTime


SQL Server Replication: Is there a way to know which articles are being used in a replicated database?

The only way to come close to being sure would be to log their usage via things like the  Profiler, Extended Events, or depending on what version your SQL Server instance is, the Query Store. You'd have to pick a timeframe long enough to log that you think is sufficient since it's possible in some contexts / businesses, some of the tables are used with low frequency for quarterly / bi-yearly / yearly reports (but maybe that doesn't apply in your use case).
Depending on how important it is for that data to be online, you could always also perform a scream test and take the replicated destination database offline for a few hours or a day, or apply a similar strategy to a group of tables at a time, to see who starts complaining about what's broken and then concretely determining which tables were in use for those consumers.


Multi Subnet Availability Group IP Requirement for Windows Failover Cluster

Does the Windows Failover Cluster for a multi-subnet SQL Server
  Availability Group require a static IP entry for each subnet?

The CNO will require an IP address for every subnet it could reside in.

  I am running SQL Server 2012 on Windows Server 2012 Hyper V VMs in 2
  separate subnets in the same domain. I understand that I will need an
  IP from each subnet when I create the listener for my AAG. What I am
  unclear on is the configuration of IPs on the underlying Windows
  Failover Cluster.

For the underlying WSFC you'll need at a minimum:

Node1 - IP Address for each unique subnet for each network interface

Node2 - IP Address for each unique subnet for each network interface

CNO - IP Address for each unique subnet

EX: 2 nodes, 2 subnets, 1 interface per node, subnets 192.168.1.1/24 and 192.168.2.1/24

Node1: 192.168.1.10

Node2: 192.168.2.10

CNO: 192.168.1.20, 192.168.2.20

  Also, if the server hosting the secondary replica does require its own
  IP, does it also require its own unique cluster name (and can you
  explain why this is necessary)?

I'm not sure I understand this part of the question. All of the resources can only belong to a single cluster - there is no cluster inside of a cluster thing.

Edit - I looked at the link that you posted and I'm not sure why the author stated "•Cluster name for each node". My only guess is they meant each node needs a name and IP (for the node). Otherwise it's not a correct statement, the author should probably be contacted.


Why does my SQL Server Express (2014) connection break after restarting my computer?

The problem is very likely related to your machine; not SQL Server 2014 itself.

A few things I would start checking when the same issue happens:

SQL Server Error Log
Windows Event Log
SQL Server Service Account state and password (is the account disabled or locked?)
Try pinging the IP of SQL Server
Try connecting by forcing TCP, using tcp:192.168.0.1,1433 (replace 192.168.0.1 with the actual IP address)
Try running SysInternal's Process Monitor to see what is happening exactly while attempting the login process.
Is the Kerberos SPN set correctly?

I very much doubt the SQL Server team would release buggy products which even starts failing at login.  The logs will definitely give a clue about what is happening, perhaps the installation logs also show something relevant.


Count Total time difference between selected row

The second round starting with ID 4 has the time 08:11:55 in your sample data, so if indeed that's the time you're supposed to use as the starting point, the total should amount to 01:31:58. At any rate, here's a solution using the LAG and LEAD window functions. If you want to prevent a sort in the plan, make sure you create the following supporting index:

create unique index idx_time_status on dbo.t1(time, status);

If you're running on 2016+, you can enable batch processing by creating the following dummy index:

create nonclustered columnstore index idx_cs on dbo.t1(id) where id = -1 and id = -2;

Prior to SQL Server 2019, SQL Server would not consider using batch processing if there's no columnstore index present on at least one of the participating tables in the query. Creating this dummy index, even if it's really meaningless in its own right, enables the use of the more optimal batch processing for window functions. Check out the plans both with and without the index. I explain this in detail here.

Here's the solution code:

with c1 as
(
  select *,
    case
      when status = 'location'
        and lag(status) over(order by time) = 'location' then 'no'
      else 'yes'
    end as keeper
  from dbo.t1
  where status in ('location', 'out of tunnel')
),
c2 as
(
  select *, lead(time) over(order by time) as nxt
  from c1
  where keeper = 'yes'
)
select dateadd(second, sum(datediff(second, time, nxt)), cast('00:00:00' as time(0))) as total
from c2
where status = 'location';

I formatted the output as TIME assuming the total amount of time will be less than 24 hours. If it could be more, you'd just need to add a bit of formatting logic.


SSIS: What services to restart so Folder Permissions Propogate to username executing pacakge?

We were able to get the changes to propagate by restarting just the 1 service that was being used by our DWH (under the username we wanted to give access to). So I guess the answer to my questions is that you only have to restart the 1 specific service that the username invokes when running the package. Restarting the entire server was not necessary.

I would have liked to have written a more detailed answer but the process was done by someone else and I was just watching as they did it so I did not get a chance to see exactly what service they restarted but I recall seeing Data Warehouse in the service name.


If "large value types out of row" table option is enabled, the table size is increased

If you use

DBCC IND('TestDb', 'Size', -1)

You can obtain the file and page number of the LOB pages (where your "out of row" values are saved)

Now, with that information get a dump fo the page:

DBCC PAGE('TestDb', 3, 938947, 1) WITH TABLERESULTS;

From those results I can see that for strings with 64 bytes or less the record size will always be 84 bytes. For longer strings will be the actual bytes plus 14 bytes (I didn't analyse strings longer than a page)
And the storage engine will try to fit as many of those records in a LOB page.


How to grant or allow a particular SQL Server Login/User to always view a Column Level Encrypted data?

There is no out of the box way to automatically open a key for a particular user. You could create a logon trigger that opens the symmetric key. Something like this:

CREATE TRIGGER open_sec_key
ON ALL SERVER
FOR LOGON
AS
BEGIN
  IF ORIGINAL_LOGIN() = '<user>'
   OPEN SYMMETRIC KEY [key_name] DECRYPTION BY CERTIFICATE [cert_name];
END;

Logon Triggers.


How do I correct this value too long for type character varying(5)?

So first, what's the difference..

SELECT x, length(x)
FROM ( VALUES
  ('Cincinnati'),
  ('San Francisco')
) AS t(x);

Here is the output

       x       | length
---------------+--------
 Cincinnati    |     10
 San Francisco |     13

So..

San Francisco is three characters longer.
They're both over 5 characters.
That can't be the problem.

And further, if Cincinnati was in a varchar(5), it'd have to get truncated.

So the problem is your cityid. It is varchar(5). you probably want that to be an int anyway -- it'll be more compact and faster. So ALTER the table and fix it.

ALTER TABLE cities
  ALTER COLUMN cityid SET DATA TYPE int
  USING cityid::int;

As a side note... maybe someday PostgreSQL will speak column names in error messages. until then at least it's more verbose than SQL Server.


Calculate values in two different ways - A or B?

BEGIN;
INSERT INTO transactions ... (info about the transfer of funds)...;
UPDATE accounts SET balance = balance - $amt WHERE acct_id = $me;
UPDATE accounts SET balance = balance + $amt WHERE acct_id = $you;
COMMIT;

That guarantees that either all 3 actions happened, or none of them, even in the case of a power failure.

Yes, this violates the don't-store-redundant-data rule, but it is standard practice, and much more efficient.

Another accounting note:  Do not ever DELETE or UPDATE transactions, only INSERT more rows to fix problems -- this leaves an audit trail of what happened and when.


Database backup taking longer than usual to a networkshare

It was an issues on the hard drive. It never showed any errors in event viewer or the controller. When we rebooted the server, the errors showed up and we replaced the drives.


Simultaneous money withdrawls from a distributed/replicated database

Take a look at the views of Micheal Stonebraker here - pages 21 and 22. Stonebraker is both an academic and a serious commercial player in the database world (Postgres, Vertica, Informix, VoltDB inter alia). For your particular question, note his views on banking.

Stonebraker is an advocate of "NewSQL".

NewSQL is a two-pronged approach which addresses both fundamental facets of database processing: OLTP and OLAP.

For OLTP (banking, shopping) he proposes in memory, sharded, shared nothing architectures with none of the transactional/buffering overhead of "OldSQL" (that's Oracle, SQL Server, MySQL... &c. to you and me). His offering in this area is VoltDB. Oracle and Microsoft are playing catch-up (with In-Memory and Hekaton)
For OLAP (DW) applications he is a fan of column oriented stores - i.e. Vertica (a commerical success he sold to HP).

He says two main things:

that ACID is essential for a database system (see the .pdf), and
that there's nothing wrong with SQL - high level langauages are good and that NoSQL is basically really a great leap 30 years backwards.

He makes the excellent point that back in the 1970's, there was a debate in the CS/IT world about C versus assembler. Some thought that hand-crafted assembler (think Map-Reduce programmes) would be more efficient than a C compiler's output (think database query optimiser).

As you may have guessed, I'm a big Stonebraker fan.


how to use dual foreign key in oracle 10g

You could try this:

CREATE TABLE ref1
(
    id VARCHAR2(3) PRIMARY KEY
);

CREATE TABLE ref2
(
    id VARCHAR2(3) PRIMARY KEY
);

CREATE TABLE look
(
    p VARCHAR2(3),
    CONSTRAINT fk_p_ref1 FOREIGN KEY (p) REFERENCES ref1(id),
    CONSTRAINT fk_p_ref2 FOREIGN KEY (p) REFERENCES ref2(id)
);

Tested on a 10G R2 server with no complaint (yet).

Edit:

Simple test result:

SQL> insert into ref1 (id) values ('abc');

1 row created.

SQL> insert into ref2 (id) values ('def');

1 row created.

SQL> insert into look (p) values ('abc');
insert into look (p) values ('abc')
*
ERROR at line 1:
ORA-02291: integrity constraint (LINEQZ.FK_P_REF2) violated - parent key not
found

SQL> insert into ref2 (id) values ('abc');

1 row created.

SQL> insert into look (p) values ('abc');

1 row created.


How to do per-tenant backups for multi-tenant databases?

Postgres has no feature to ferret out semantically a subset of the rows in your tables. Nor does any other database I’ve heard of.

You will need to write your own code to retrieve just the one tenant’s rows from each of the relevant tables. This code might be in your app, or might be done on the server within Postgres.

Also, you’ll need to decide if any other tables not split by tenant should also be copied into each tenant’s backup.

I have heard about some database vendors considering features to support multi-tenancy explicitly. The database would understand that the data for each tenant should be considered separate and kept in logical and/or physical silos. But I don’t know of any such products  ready for production yet.


SQL Server: CONVERT From ISO8601 To DateTime Within Query

The issue is that you cannot CONVERT or CAST a VARCHAR ISO8601 datetime with an offset to a DATETIME.

From SQL Server 2008 onwards, the DATETIMEOFFSET datatype was introduced to handle datetimes with offsets.

As answered elsewhere, you would need to CAST your DateTime VARCHAR column to a DATETIMEOFFSET

SELECT *
FROM dbo.RebroadcastSmoothStreaming
WHERE
   (
       CAST(DateTime AS DATETIMEOFFSET) BETWEEN
         CAST('2014-01-01T00:00:00-06:00 AS DATETIMEOFFSET)
         AND { fn NOW() }
   )
ORDER BY Ip


How to audit table permissions as a non-DBA

You need to connect to schema_1 to run this query.
There is a helpful view user_tab_privs. If you are on 11g or higher, then
listagg function can show the list of other privileges. Otherwise remove row with listagg form query

select table_name, grantee ,
        max(sel) sel, max(ins) ins , max(upd) upd, max(del) del,
        case when max(other_privs) is not null then 'other' end other_privs,
        listagg(other_privs,',') within group (order by other_privs) as list_of_other_privs
 from
 (select table_name, grantee,
        case when privilege='SELECT' then 'y' end sel,
        case when privilege='INSERT' then 'y' end ins,
        case when privilege='UPDATE' then 'y' end upd,
        case when privilege='DELETE' then 'y' end del,
        case when privilege not in ('SELECT', 'INSERT','UPDATE','DELETE') then privilege end other_privs
   from user_tab_privs
 )
  group by table_name, grantee
  order by table_name, grantee


Your example suggests duplicate rows in the VALUES clause itself - which would result in:

  ERROR:  ON CONFLICT DO UPDATE command cannot affect row a second time

Can be solved by folding duplicates in the input. See:

How to include excluded rows in RETURNING from INSERT … ON CONFLICT

But a related problem affects your outlined query as a whole, and that's not as easy to overcome.

What to do if the same input row triggers multiple unique violations?
What to do if multiple input rows trigger the same unique violation?
What to do if multiple input rows trigger distinct unique violations of the same target row?
And combinations thereof.

Postgres developers probably didn't want to open this can of worms and restricted the UPSERT feature to a single constraint.

ON CONFLICT DO NOTHING - without conflict target - works for any applicable violation. The alternative action for this variant ("do nothing") is unambiguous.

You would have to be a lot more specific (also about concurrency and possible write load) to get a more specific answer.


What problems are there in not using a primary key column at all?

there are no queries where I will ever want to select a row by
  post_versions.id

If you have a column that you know you'll never have any use for whatsoever, be it a synthetic key or any other kind of column, then sure, remove it.

The problem with a table without a primary key, is that it can potentially  have duplicate rows, and, in the relational model, duplicate tuples do not exist.
So if your table happens to have duplicate tuples, it would violate a fundamental rule of the relational model.

But that's not necessarily a reason to create a synthetic key that you have no use for, for the sole purpose of having a primary key. Firstly, the way your table is structured with its updated column, it's very unlikely that it will ever have duplicates. Secondly, even if it happened, the consequences of having duplicates must be evaluated relatively to what is done with the data. When for instance a table is used to store log entries to be only displayed, there is no problem having duplicates. On the other hand, if an application had to update some specific row for some reason , then the absence of any primary key would be a problem and probably reveal a design error.


start mongod by service mongod start, attempt to create a lock file on a read-only directory /mgodb

If you look that /etc/mongod.conf, what are values of processManagement.pidFilePath and storage.dbPath? It sounds like that system tries to create files to some subdirectory, what don't exists or is not owned by user what is used to start mongod process.

You should never start your mongod with root user. And if you have done that, f.ex. when starting mongod to maintenance mode, always remember to chown -R user: <dir> data files back to the right owner.


How can I get rid of an unhelpful parallel branch when unpivoting a single row?

I am able to get the desired plan shape with a serial loop join when all of the following are true:

An APPLY or CROSS JOIN is used instead of UNPIVOT
The APPLY contains no outer references
The source of rows in the APPLY is a table value constructor as opposed to a table

For example, here is one way to do it:

SELECT A, B
FROM
(
    SELECT A
    , MAX(
        CASE
            WHEN A = 'VAL1' THEN VAL1
            WHEN A = 'VAL2' THEN VAL2
            WHEN A = 'VAL3' THEN VAL3
            WHEN A = 'VAL4' THEN VAL4
            WHEN A = 'VAL5' THEN VAL5
            WHEN A = 'VAL6' THEN VAL6
            WHEN A = 'VAL7' THEN VAL7
            WHEN A = 'VAL16' THEN VAL16
            ELSE NULL
        END
    ) B
    FROM (
         SELECT
           MAX(CASE WHEN ID = 1 THEN 1 ELSE 0 END) VAL1
         , MAX(CASE WHEN ID = 2 THEN 1 ELSE 0 END) VAL2
         , MAX(CASE WHEN ID = 3 THEN 1 ELSE 0 END) VAL3
         , MAX(CASE WHEN ID = 4 THEN 1 ELSE 0 END) VAL4
         , MAX(CASE WHEN ID = 5 THEN 1 ELSE 0 END) VAL5
         , MAX(CASE WHEN ID = 6 THEN 1 ELSE 0 END) VAL6
         , MAX(CASE WHEN ID = 7 THEN 1 ELSE 0 END) VAL7
         , MAX(CASE WHEN ID = 16 THEN 1 ELSE 0 END) VAL16
         FROM dbo.PARALLEL_ZONE_REPRO
    ) q
    CROSS APPLY (
        VALUES ('VAL1'), ('VAL2'), ('VAL3'), ('VAL4'),
        ('VAL5'), ('VAL6'), ('VAL7'), ('VAL16')
    ) ca (A)
    GROUP BY A
) q
WHERE q.B IS NOT NULL
OPTION (MAXDOP 4);

I get the desired plan plan shape as claimed with just one parallel branch:

I tried many other things that did not work. This answer is unsatisfactory in that I don't know why it works and it may not work in a future version of SQL Server, but it did solve my problem.


Can mongorestore replay oplog via mongos?

You cannot replay oplogs through a mongos process: oplog entries can only be applied via a mongod that is the primary of a replica set.

Please see the MongoDB documentation for tutorials on Supported Backup Methods including Backing up a Sharded Cluster with Database Dumps.


Truncating TEMP table before insert leads to big amount of logical reads on TEMP TABLE

The data in there is not really dropped, first of all. It has to do with how Temdb is implemented

Also, Microsoft has a great blog on TempDB and contention that addresses the following points:

Tempdb - Files, Trace flags, updates - Microsoft Blogs

  We are continuing to work on improving tempdb performance and metadata
  contention, but in the meantime, there are some best practices you can
  employ in your code that might help avoid the contention:

  Do not explicitly drop temp tables at the end of a stored procedure, they will get cleaned up when the session that created them
  ends.
  Do not alter temp tables after they have been created.
  Do not truncate temp tables
  Move index creation statements on temp tables to the new inline index creation syntax that was introduced in SQL Server 2014.


Do empty columns take up space in a table?

It takes minimal space when not used

one bit in the NULL bitmap
two bytes for length (which will be zero when NULL)

The overhead is minimal and optimisation will be premature.

Until you know you have an issue, just keep it in one table. You break KISS by introducing outer joins and add an overhead in querying the data.

See https://stackoverflow.com/questions/3793022/how-to-come-to-limits-of-8060-bytes-per-row-and-8000-per-varchar-nvarchar-valu/3793265#3793265 for more


How do I map a SQL Server login to a user that already exists in a read-only database?

Since the status of the database is Standby, I'm assuming this database is a Log Shipping secondary. If that's the case, the database cannot be written to at all on the secondary (making it writable would break Log Shipping).

Also, since you said that other databases (presumably not read-only) have correct mappings, I'm assuming you're using a SQL login. If that's the case, all you should have to do is recreate the login on the secondary server using CREATE LOGIN ... WITH SID to match the SID from the primary (look in sys.server_principals on the primary server to find it).

On the other hand, if you're using Active Directory for the login, you'll have to add the user principal to the primary database so that when it comes across to the secondary in a log backup, there's no need to do any remapping (note: depending on the situation, this may be a lot easier said than done!).


Has a standard join structure less performance?

SELECT posts.*
FROM posts_tags pt
     inner join tags ON tags.id = pt.tags_id
     inner join posts ON posts.id = pt.post_id
WHERE tags.name = 'some tag'
ORDER by posts.name DESC
LIMIT 10;


MySQL gives me:“Can't open and lock privilege tables: Table 'host' is read only”

custom mysql datadir? removed apparmor and still having mysql process failing to start issues? try this. may help some of you depending on your config. worked for me:

when using a custom mysql (5.5) data dir as described above-
ie., if the abs. path to datadir (my.cnf) is
/opt/some/other/mysql

even with apparmor removed/purged, you must make certain that permissions on ALL of the folders in the mysql datadir path are perm'd correctly.  this is a mysql convention- not related to apparmor.

look at /var/lib/mysql- the default datadir (my.cnf) on most installations: perms are 755 for /var and /var/lib.

in my custom datadir example above, when I set any/all of the dirs called 'opt' or 'some' or 'other' without an execute permission for allusers (ie., 750), mysql will not start.  once i add an execute perm to allusers (ie. 755, 771, etc.), BINGO. mysql starts.


Postgres - Insert performance with indexes

No, a basic btree index on a small column is typically cheap to maintain. Of course it gets slightly more expensive when the table has accumulated some bloat from dead rows, but the difference should be small. And you have to consider additional storage on disk for the index.

One thing seems worth mentioning: Updates on columns involved in an index in any way cannot use H.O.T. updates ("heap-only tuples"), so this might add some cost for updates changing the indexed column. But still, this would just prevent one internal optimization, no big deal. And only relevant if you involve the column in updates.

Is a composite index also good for queries on the first field?
Redundant data in update statements


PostgreSQL

I'm not sure why you would want to use regexes for this. PostgreSQL already has specific types.

CREATE TABLE whatev (
  IPAddress  inet,
  MACAddress macaddr,
  Hostname   text,
  ScriptName text,
  CHECK ( IPAddress IS NULL OR MACAddress IS NULL )
);

INSERT INTO whatev
VALUES ( '10.2.1/24', null, 'Evan Box', 'rmrf' );

Don't store ip's, netmasks, or macaddresses as text.

SELECT * FROM whatev
WHERE ipaddress >> '10.2.1.4';

I don't much understand the rest of what you want to do. I would also point you to ip4r which is awesomely great at this.

As far as storing conditions I'm not entirely sure what you mean, but you can certainly store conditions as inet there are a rich set of operators for it


SQL Server credentials - Maximum Password size

Passwords are case-sensitive. Passwords should always be at least 8
  characters long, and cannot exceed 128 characters. Passwords can
  include a-z, A-Z, 0-9, and most non-alphanumeric characters. Passwords
  cannot contain single quotes, or the login_name.

https://msdn.microsoft.com/en-us/library/ms189751.aspx


Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement

We had a situation where our sql server, a vm, went down with the ESX host it was on.

The vm rebooted, but TCP 1433 would not let us connect with the posted error.

We confirmed that all TCP settings were as intended in SQL Server Configuration, but ultimately the resolution was to change the IP of the server. Weird right?

My only thought is that something at the VMWare level was causing traffic to be dropped, but had no view into that platform as it was in the 'cloud.'

I don't know if is useful, but I hope someday it helps someone else.

Other notes are that SQL started fine, nothing in error log, with agent disabled we could access sql via pipes and udp 1434.


Super slow (day long) count(*) after upgrade to 5.6

It appears to be a change in the optimizer.  I haven't tracked down the exact setting that caused this yet but the explain on the 5.5 version was showing it using the Primary Key.  On 5.6 It was using the secondary char1(8) index.

Adding force index (primary) got it back to it's 3-5 minute count time.


percona mysql crashing at innodb initialization

solved by upgrading server from 5.5.13 to 5.5.20


Transaction - Timestamp Ordering. When is aborted transaction restarted

well, my teacher said that there is no clear answer since the methods varies. Some DBMSystems won't restart aborted transactions at all


MongoDB ReplicaSet reconfig when primary and majority don't exist

Yes, you can do this, and in fact with just 2 data centers you really have no other choice.   Essentially, whenever you do not have a primary you can still do a reconfig but you must pass in {force:true} to make it happen on a non-primary node.

This is listed in the docs for the rs.reconfig() command and in detail (with examples) as part of a tutorial which describes exactly what needs to be done in the scenario you described:

http://docs.mongodb.org/manual/tutorial/reconfigure-replica-set-with-unavailable-members/

If you are interested, I cover this and other availability issues in the (free) M202: Advanced Deployment and Operations course on MongoDB University


MongoDB stale replication, odd timestamp after data flush

No, it's not expected be there. Timestamp values should be decimal, not hexadecimal and msec value 3b1 is bigger than 333.

You could "move" primary status from current node to another node and try to resync this stale node again, this time it would read that other node and hopefully it's opLog is in good condition.


Will one nvarchar column affect query performance?

Without knowing more details, it's impossible to tell, but you might be suffering from implicit conversion.  There are a number of sites with information on the topic that you can use to help troubleshoot your particular query.

The short answer is that no, you should not need to convert all columns to nvarchar.  If you are trying to join an nvarchar column to a varchar column, however, you could see a major performance impediment.


Loyalty reward - record for every day of a week

Maybe.

SELECT count(distinct date(time_they_logged_in))
FROM `users`
WHERE `users`.`time_they_logged_in` between now()-interval 7 day and now();

If the count is 7 they logged in at least once per day in the past week.


Migrating Pure PHP Site with Associated Database To New Server

PHP is not running on your new server. Set it up correctly and be sure the database has been migrated and is working.

Try adding a simple page with:

<?php
phpinfo();
?>

To your webserver root folder and open it. If you do not get proper info but the script on the page the PHP scripts are not being processed.


encoding format of multilingual text while saving to database

One suggestion would be to use nvarchar(n) type column. You can then store UCS2 data in that column. The width of the column can be decided based on the data.


DSV - SSAS Project - New 2 Columns In Table Change More then I Planned

I have no idea why the columns changed appear twice and neither does google apparently.

With regards to the changes on the columns on the other hand your best option would be to compare the data types and table definitions on the .dsv.

Compare your new dsv to an old version of your dsv and have a look at all these datasource view properties

Certainly something has changed, maybe not necessarily in the datasource, but in what has been detected. It could be minor (correcter guessing of length for example) or a real problem, so I suggest you review the changes anytime this happens.

You could have a go at diffing the .dsv xml structure instead of reviewing each property. That sometimes looks messy but you could have some luck with BIDSHelper's SmartDiff's feature.


For loop in query - how can i use Union?

You can simplify your query above with this example, but it suffers from some limitations caused by our lack of knowledge of the schema of your table as well as what your final objective really is.

This should do in one step what your query does, but it has hard coded idEstatus values (which may be ok) and is limited to one month.

It's not clear form your example query if you really need the second half since it appears to be the same date (if you wanted year over year then the correct answer is different).

 ;WITH CTE_Details AS
    (
    SELECT YEAR(dat) AS [year]
        , MONTH(dat) AS [month]
        , idEstatus_Count_2 = CAST(COALESCE(SUM(CASE WHEN idEstatus = 2 THEN 1 ELSE 0 END), 0) AS DECIMAL(5,2))
        , idEstatus_Count_3 = CAST(COALESCE(SUM(CASE WHEN idEstatus = 3 THEN 1 ELSE 0 END), 0) AS DECIMAL(5,2))
    FROM t1
    WHERE idEstats IN (2,3)
        AND dat BETWEEN CAST('1/1/2017' AS DATETIME) AND CAST('1/31/2018 11:59:59 PM' AS DATETIME)
    GROUP BY YEAR(dat)
        , MONTH(dat)
    )
SELECT [year]
    , [month]
    , idEstatus_Count_2
    , idEstatus_Count_3
    , rf = (((idEstatus_Count_2 + idEstatus_Count_3) / 2.0) * 100) / ((idEstatus_Count_2 + idEstatus_Count_3) / 2.0)
FROM CTE_Details


What is the correct way to specify a record as the *current* record?

I've seen several approaches to this:

a boolean (if the DBMS of choice supports that) or character (e.g. 'Y','N') indicator column;
a more general "status" column, containing values like 'ACTIVE','HISTORY','EXPIRED' etc.
a pair of timestamp columns (e.g. START_DT, END_DT), indicating the range of dates when a particular record is (was) active. The current record would have the END_DT value in the future, like '9999-12-31', which makes querying the current record simple: ...where current_date between START_DT and END_DT
a history table where all non-current records would be moved as soon as they become non-current. If you're only interested in current records, you would query the current table, otherwise you'd query a view that UNION ALLs both current and history tables.

In the first three cases you would need to ensure consistency via some sort of a constraint, if your application is unable to do that.

Which approach you choose depends on what constitutes the current record in your particular case.


PostgreSQL Linux PAM Authentication Error

Since you didn't specify the -h option, PostgreSQL connects via local Unix socket. This is governed by the local entry in pg_hba.conf. Hence you are authenticated with "peer" authentication. But since you are not operating system user test, peer authentication fails.
To get PAM authentication, connect with -h 127.0.0.1.


Should we manually failover again if the node which was originally a primary role and went down recovered?

Should we manually trigger a failover to the dbserver1 to make it a Primary again?

Since you said that DBserver2 does not have the jobs and other server level objects you must failback the Basic Availability Group to DBServer1. Your worry about missing a job run is correct.
I would also like to say that this is a wrong configuration, you MUST have all jobs and logins created on DBServer2 ( which was originally acting as secondary) missing these would defeat the overall purpose of having HA/DR.

Should we always target to have a particular node to be Primary (in this case dbserver1), or each node should be exactly the same including all the objects outside of Availability Groups so that any node can take primary role at any time, and should be kept being primary node once a failover occurred?

You are correct both nodes should be exactly same. You need to re-create all the logins and jobs and any ssis packages. In short both the SQL Servers should be same in all the aspect


Oracle - PFILE or SPFILE when instance is down

It does not really matter, but spfile is favored because it provides better functionality.
When an instance is down, it does not use any of them.
You may check the alert log for the previous startups, the pfile/spfile location is displayed there after each startup.
If the database is registered in Grid Infrastructure as a database resource, the spfile location is typically (but not mandatorily) specified there (srvctl config database -d database_name).
By the way, most databases use SPFILE.


Is it normal to UPDATE STATISTICS this frequently?

There is multiple ways to fix the parameter sniffing issue.

If you update your statistics 10 times a day and especially if that is causing performance issues, then I would look at other options.

There is some good chance that the update of the stats is not what's fixing your parameter sniffing issue. The update of the stats causes the plan to be flushed from the cache (which is probably fixing your issue). Flushing only that plan from the cache would probably work as well without having to rebuild the stats ;)

If I were you, I would take it from the start and find which set of parameters causes SQL to take a bad execution plan. Once you have this, you can then start looking at the way to fix it.

You could use the QueryStore to fix this, forcing SQL to use always the "good" execution plan for example.

Sometimes, A good index can also fix parameter sniffing issue (that can cause SQL to build a different execution plan that would be good  for whatever the parameter is)


One case statement across multiple columns

Store the condition result into user variable and use it in the second CASE:

select
case when @cond := (cond) then
     'Column1 Cond T'
else'Column1 Cond F'
end,
case when @cond then
     'Column2 Cond T'
else'Column2 Cond F'
end,
from table


JSON in MySQL : get data from an object within a range of keys

Possible realization:
WITH RECURSIVE cte AS ( SELECT @from num
                        UNION ALL
                        SELECT num+1 FROM cte WHERE num < @till )
SELECT CONCAT('key', num) `key`,
       JSON_EXTRACT(test.val, CONCAT('$.key', num)) `value`
FROM test
CROSS JOIN cte
HAVING `value` IS NOT NULL;

fiddle


how to make secondary slave as readable in MongoDB?

For mongo shell sessions there is a concept of mongorc files which include JavaScript to execute when the mongo shell starts. You can use this feature to extend or customise the behaviour of the interactive shell.

If you want secondary reads to be allowed by default for all shell sessions you can either:

add the rs.slaveOk() command to the .mongorc.js in the current user's home directory:

$ echo "rs.slaveOk()" >> ~/.mongorc.js

add to the global /etc/mongorc.js which is executed before the user-specific '~/.mongorc.js` (Note: requires MongoDB 2.6+):

$ echo "rs.slaveOk()" | sudo tee -a /etc/mongorc.js


Problem is that the SUM(order) can be 0 in the CASE statement. Create a sub-query that delivers the price and order totals per loc_id and then use this to do the calculation if the sum of order is not 0:

SUM(CASE loc_id WHEN 1 THEN price ELSE 0 END) AS 'loc_id_1_price',
SUM(CASE loc_id WHEN 1 THEN order ELSE 0 END) AS 'loc_id_1_order'

Then the SQL look something like (check if this is possible in vertica):

SELECT year_created, month_created, 1 - total
       1 - CASE loc_id_1_order WHEN 0 THEN 0 ELSE loc_id_1_price/loc_id_1_order END as loc_id_1,
       1 - CASE loc_id_2_order WHEN 0 THEN 0 ELSE loc_id_2_price/loc_id_2_order END as loc_id_2,
       1 - CASE loc_id_3_order WHEN 0 THEN 0 ELSE loc_id_3_price/loc_id_3_order END as loc_id_3
FROM
  (SELECT
     MONTH(created_at) as month_created,
     YEAR(created_at) as year_created,
     SUM(price)/SUM(order_total) as Total,
     SUM(CASE loc_id WHEN 1 THEN price ELSE 0 END) AS 'loc_id_1_price',
     SUM(CASE loc_id WHEN 1 THEN order ELSE 0 END) AS 'loc_id_1_order',
     SUM(CASE loc_id WHEN 2 THEN price ELSE 0 END) AS 'loc_id_2_price',
     SUM(CASE loc_id WHEN 2 THEN order ELSE 0 END) AS 'loc_id_2_order',
     SUM(CASE loc_id WHEN 3 THEN price ELSE 0 END) AS 'loc_id_3_price',
     SUM(CASE loc_id WHEN 3 THEN order ELSE 0 END) AS 'loc_id_3_order'
   FROM data_price
   WHERE YEAR(created_at) = YEAR(NOW())
   GROUP BY year_created, month_created)
ORDER BY year_created asc, month_created desc


Poor performance on inner join using dates and between

Much more efficient to do this without having to go back and join to the periods table.

DECLARE @StartDate DATE, @EndDate DATE;

Select @StartDate = Min(StartDate), @EndDate = MAX(EndDate)
from dbo.PeriodCalendar_Weeks pcw
where (pcw.Year = @Year and pcw.Period < @Period)
  or  (pcw.Year = @Year and pcw.Period = @Period and pcw.Week <= @Week)
  or (pcw.Year = @Year -1 and pcw.Period >= @Period);

SELECT
  WeekEndDate = DATEADD(DAY, 6, DATEADD(WEEK, SalesWeek, @StartDate)),
  Store,
  DeliveryChargesTotal = dct
FROM
(
  SELECT DATEDIFF(DAY, @StartDate, SalesDate)/7, Store, SUM(DeliveryChargesTotal)
  FROM dbo.Daily_GC_Headers
  WHERE SalesDate BETWEEN @StartDate AND @EndDate AND isCanceled = 0
  GROUP BY DATEDIFF(DAY, @StartDate, SalesDate)/7, Store
) AS x (SalesWeek, Store, dct)
ORDER BY WeekEndDate, Store;

A filtered index may help, if many rows exist where isCanceled = 1 (these are just possible suggestions, depending on cardinality of Store, and may not be the most optimal):

CREATE INDEX x ON dbo.Daily_GC_Headers
  (SalesDate) INCLUDE (Store, DeliveryChargesTotal)
  WHERE isCanceled = 0;

If there are very few rows where isCanceled = 1, this may be better:

CREATE INDEX x ON dbo.Daily_GC_Headers
  (SalesDate, IsCanceled) INCLUDE (Store, DeliveryChargesTotal);

Both are worth trying on a test system, as well as moving Store into the key in either case, or moving IsCanceled to the INCLUDE list in the latter case. On my system, I found the best results with everything but the date in the INCLUDE list:

CREATE INDEX x ON dbo.Daily_GC_Headers
  (SalesDate) INCLUDE (Store, IsCanceled, DeliveryChargesTotal);

Again, you will need to test if any of these work out, or if the query above gives a different/better recommendation directly from SQL Server.


SQL Server - Snapshot is useful in production environments?

As an opinion based question and answer, I suspect that this will get closed, but here's my two cents.

I have used and seen Database Snapshots in my production environments, although rarely.

Scenario A) We used database snapshots to provide a static image of a database for reporting (ETL to a datawarehouse) purposes.  Daily a script would run at the specified time and create a snapshot for reports to use.  This database was transactionally consistent as of it's date of creation so useful for that.

Scenario B) A terrible maintenance job that ran and did some analysis of a very deep table and then would start deleting appropriate entries. We created a snapshot at the start of this process to prevent any blocking from taking place.  So the maintenance job was changed to get the list of "what needed work" from the snapshot, then drop the snapshot and then "do the work" on the live database.

The use case for database snapshots is niche for anything except CHECKDB.  SQL Server does some lock isolation tricks in the background that make it a good choice if you need a quick and dirty fix for certain classes of operations.


connect-mongo latest version return some error in my project

As per your error it shows you have Express 4.16.2 and mongodb 2.2.35.

As per NPM Mongo Blog here you can check out the compatibility . As well as for connection integration of Express 4.x.

The sample connection code should be as mentioned below for Express 4.x, 5.0 and Connect 3.x:

const session = require('express-session');
const MongoStore = require('connect-mongo')(session);

app.use(session({
    secret: 'foo',
    store: new MongoStore(options)
}));

For further information see here and here


SQL dumping all pages from buffer cache every few minutes

Although you seem to have solved the issue yourself, here is a summary of the relevant information surrounding the solution.

Server Memory Server Configuration Options

Microsoft write in their article Server Memory Server Configuration Options(Microsoft | SQL Docs) for the section Setting the memory options manually

(emphasis mine)

  Also, setting a min_server_memory value is essential in a virtualized environment to ensure memory pressure from the underlying host does not attempt to deallocate memory from the buffer pool on a guest SQL Server virtual machine (VM) beyond what is needed for acceptable performance.

The section regarding Lock Pages in Memory (same document) has an equalling compelling paragraph which reads as:

(emphasis mine)

  This Windows policy determines which accounts can use a process to keep data in physical memory, preventing the system from paging the data to virtual memory on disk. Locking pages in memory may keep the server responsive when paging memory to disk occurs. The Lock Pages in Memory option is set to ON in instances of SQL Server Standard edition and higher when the account with privileges to run sqlservr.exe has been granted the Windows Lock Pages in Memory (LPIM) user right.

The section LPIM goes on to explain that:

(emphasis mine)

  Setting this option does not affect SQL Server dynamic memory management, allowing it to expand or shrink at the request of other memory clerks. When using the Lock Pages in Memory user right it is recommended to set an upper limit for max server memory as detailed above.

... and in an important comment that:

(emphasis mine)

  Setting this option should only be used when necessary, namely if there are signs that sqlservr process is being paged out. In this case, error 17890 will be reported in the Errorlog, resembling the below example:

A significant part of sql server process memory has been paged out.
This may result in a performance degradation. Duration: #### seconds.
Working set (KB): ####, committed (KB): ####, memory utilization: ##%.

  Starting with SQL Server 2012 (11.x), trace flag 845 is not needed for Standard Edition to use Locked Pages.

Solution

Based on the above findings and your observations the solution to your problem would be to configure the following settings:

min_server_memory (5-10 GB?) based on your comment:

  About 8-9GB of data in the cache at the moment, seems to be stable.

...and Microsoft's recommendation of setting a min_server_memory.
max_server_memory (20-32 GB) based on your observation:

  It seems to suggest that 20GB is enough for the workload on this box. I'm happy leaving it with 32GB for now but I'd really like to get to the bottom of this so I can better configure the VMs/SQL.

...and Microsoft's recommendation of setting a max_server_memory.
Lock Pages in Memory : Enabled for SQL Server Service Account.
Based on the ERRORLOG entry of your SQL Server instance you mentioned and Microsoft's reference in the article.

  Setting this option should only be used when necessary, namely if there are signs that sqlservr process is being paged out.

Before You Continue...

(One of) the benefit(s) of having a virtualised environment is that resources can/should be shared, and possibly even over-commited. However, turning on Lock Pages In Memory (LPIM) could have a negative impact on your Hyper-V environment, if your hardware is hosting multiple instances. An over-commitment of RAM could exhaust other instances.

Before you consider switching all the levers, start off with settings 1. and 2. and if fine tuning these memory settings doesn't work, then consider turning on LPIM if you have enough hardware.


I need help using hierarchyid datatype and requiring a unique constraint on a varchar field

If you add another column for the ancestor, e.g.

CREATE TABLE [dbo].[tbl_server_group]
(
    [refid]     [int] IDENTITY(1,1) NOT NULL,
    [refhid]    [hierarchyid] NOT NULL,
    [reflvl]    AS refhid.GetLevel() PERSISTED,
    [refdate]   [datetime] NOT NULL DEFAULT GETDATE(),
    [refname]   [varchar](50) NOT NULL,
    [reflvl1id] AS refhid.GetAncestor(1) PERSISTED
);

You can ensure that there is only ever one 'www' per parent.  You can create a similar index for 'index.html'.

create unique index ux_tbl_server_group_1 on tbl_server_group(reflvl1id, refname)
where refname = 'www';

Obviously this is carrying it further than just the first level (www) and for children of www (index.html) but I don't see why the expanded restrictions won't work.
If you had to implement it exactly as specified, then you will have to resort to using a trigger.

Note: This makes use of SQL Server's filtered index, so you have to get the SET settings correct for any process that touches this table.


SQL Server - MERGE join condition with NULLable Columns

Mark Sinkinson nailed it in the comments:

  This is standard behaviour. NULL does not equal NULL

What you can do is adjust your predicate from this:

WHERE Destination.ColumnA = Source.ColumnA
    AND ...  -- other column comparisons

to the following:

WHERE ((Destination.ColumnA = Source.ColumnA)
       OR (Destination.ColumnA IS NULL AND Source.ColumnA IS NULL))
   AND ...  -- other column comparisons

This will provide a workaround that effectively equates a NULL in one column to a NULL in another.

So to work that into your Merge, it would be as follows:

MERGE INTO xx.dbo.acc_s AS Target
 USING yy.dbo.acc_s AS Source
 ON (Target.acc_id= Source.acc_id
     AND Target.s_id= Source.s_id
     AND (a_code= Source.a_code
          OR (a_code IS NULL AND Source.a_code IS NULL)
         )
    )
WHEN NOT matched BY Target THEN
INSERT (acc_id,s_id,a_code)
VALUES (Source.acc_id,Source.s_id,Source.a_code);


MongoDB can take advantage of a compound index when you query for the leading column(s), so in your case the index on userId and eventDate can be used for a query on userId. You don't need a separate index.