Is there a way to get the Actual Execution Plan for a cancelled query? Sometimes a query will be taking ages and you don't want to have to run the whole thing.  This is exactly the kind of time you may want to see the Actual Execution Plan to figure out why the query is inefficient.  However I've noticed that when you tell SSMS to include the Actual Execution Plan in the results, it only does so when the query finishes successfully and is not cancelled!  Why is this?  Presumably the plan has to be generated first, so it could be sent first by SQL Server and be displayed even for a cancelled query.  I know about the estimated execution plan but it's not the same.

In SQL Server 2016 Management studio you can see the execution plan while executing, using the include live execution plan. This works for SQL Server 2014+

For SQL Server 2014 I usually use this query to get the execution plan of execution query's

SELECT
    r.session_id
,   r.start_time
,   TotalElapsedTime_ms = r.total_elapsed_time
,   r.[status]
,   r.command
,   DatabaseName = DB_Name(r.database_id)
,   r.wait_type
,   r.last_wait_type
,   r.wait_resource
,   r.cpu_time
,   r.reads
,   r.writes
,   r.logical_reads
,   t.[text] AS [executing batch]
,   SUBSTRING(
                t.[text], r.statement_start_offset / 2,
                (   CASE WHEN r.statement_end_offset = -1 THEN DATALENGTH (t.[text])
                         ELSE r.statement_end_offset
                    END - r.statement_start_offset ) / 2
             ) AS [executing statement]
,   p.query_plan
FROM
    sys.dm_exec_requests r
CROSS APPLY
    sys.dm_exec_sql_text(r.sql_handle) AS t
CROSS APPLY
    sys.dm_exec_query_plan(r.plan_handle) AS p
ORDER BY
    r.total_elapsed_time DESC;

This gets the statement executing with their corresponding estimated query plan.


Unstructed data field: Query all values from array of objects by key I have a table that has a unique ID, and a second 'collumn' named 'data' that contains simple key/value items like:

"nickname": "value"
"fullName":  "value"
"office": "value"
"unity":  "value"

and a few, more elaborated structure items like:

"address":
    {
        "city": "value",
        "state": "value",
    },

and

"personalVehicle":
    [
        {
            "brand": "value",
            "model": "value",
            "plate": "value",
            "color": "value"
        },
        {
            "brand": "value",
            "model": "value",
            "plate": "value",
            "color": "value"
        }
    ]

Where, as you can see, personalVehicle is a key that stores an array of objects, in which every object has it's own simple key/value items.

I can query specific key values from address for all registries: SELECT data->'address'->'city' as city FROM person

+------------+
|    city    |
|------------|
| "city1"    |
| "city2"    |
| "city3"    |
+------------+

Here is the situation: I can query all info about the vehicles with SELECT data->'personalVehicle' as vehicles FROM person

+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| vehicles                                                                                |
|------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| [ { "brand": "Toyota", "model": "Corolla", "plate": "AAA-1111", "color": "Red" }, { "brand": "Ford", "model": "Focus", "plate": "ZZZ-9999", "color": "Blue" } ]  |
| <null>                                                                                |
| [ { "brand": "Hyundai", "model": "Tucson", "plate": "ABC-1212", "color": "Grey" } ]                                                                              |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------+

But I cannot retrieve an specific key for all objects, when the objects are inside of an array; in which case, I need to specify the index: SELECT data->personalVehicle->0->model as model from person

+-------------+
|   model     |
|-------------|
| "Toyota"    |
| <null>      |
| "Hyundai"   |
+-------------+

This guy up here, is the first index of the array, that is, the first car. I need to get the models for all N number of cars that the person might have.

How do I do that? Query that without specifying the index?

Grab the elements from the json array with json_array_elements and reassemble them with json_agg:

select id, json_agg(cars) from (
  select
    id,
    json_array_elements(data->'personalVehicle')->>'model' as cars
  from t
) subq
group by id

Output:

1   ["Toyota", "Honda"]
3   ["Chevrolet", "Ford"]
2   ["Renault", "Citroën"]

Here's a fiddle.


Different plans on Readonly Secondary Replica We have an AlwaysOn environment where we use the async readonly secondary replica for reporting purpose.

One of the reports intermittently runs for much longer than normal.  I can see when it runs slowly the plan is taking a different route and causing a clustered index scan on a big table.

However, when I run the same query in primary replica it runs fine and generates a plan that is what we expect.

Why does it generate a good plan on the primary replica but a bad plan on the secondary? what are the possible reasons?

The servers have exactly the same hardware and the configuration is the same.

It seems to be a bug in SQL Server, if you are using SQL Server 2012, 2014, or 2016 install the latest SP with CU that has a fix to This.

Details here:

FIX: Bad query plan created on secondary replicas after statistics updated via FULLSCAN option on primary replica in SQL Server 2012 or 2014


Deleting Entries from Table not reducing my DB file Iam usind INNODB Engine with innodb_table_per_file , so every table has it's own .ibd file instead of ibdata1 file.

When i Entered 512 Record in my users table its size get increased to 200kb from 96 kb but when I deleted all rows using Command

DELETE FROM USERS;

then my .ibd file didn't reduce not even by a single byte.

BUT When i delete rows using command

TRUNCATE TABLE USERS

Then my .ibd file got reduced to it's initial size which was on creation that is 96 kb.

Now i Want to know that is here any way to reduce .ibd files when i use DELETE FROM USERS WHERE ...  command

On case:

TRUNCATE removes the file and creates another one. BTW in MySQL TRUNCATE is not allowed on tables with FK dependencies.

DELETE invalidates the record and the space the record used to occupy may be claimed later on for reuse.

One other operation forces the rearrangement of the data: OPTIMIZE.

Off case:

If your application works in a manner which habitually deletes large quantities of data, you should consider writing a cron to call an OPTIMIZE over the affected tables (sometime during low traffic).

Otherwise you should not worry about this because as stated here "Deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions".

Take into consideration whether OPTIMIZE is transactional or not - DDL statements are not transactional in MySQL, not sure if OPTIMIZE is DDL.

If it's not you may need to do something else such as:

create a new table from the result of a full content select from the old table;
wait for the data to be inserted into the new table;
switch the table names with each other;
drop the old table;

The new table will have a clean slate.


MySQL Replication stuck at 'preparing' My MariaDB replication stuck at 'preparing'.
This happend after an update and restart of the server yesterday. Since then, replication is not working.

Setup: 2 MariaDB servers with master-master replication. Version: 10.4 (Upgraded few weeks ago from 10.1 - replication has worked after upgrade)

Since the output of both servers for SHOW SLAVE STATUS is similar, i am posting only from my first server:

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
                Slave_IO_State: NULL
                   Master_Host: data2
                   Master_User: replusr
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mariadb-bin.018865
           Read_Master_Log_Pos: 14137515
                Relay_Log_File: relay-bin.017274
                 Relay_Log_Pos: 4
         Relay_Master_Log_File: mariadb-bin.018865
              Slave_IO_Running: Preparing
             Slave_SQL_Running: Yes
               Replicate_Do_DB:
           Replicate_Ignore_DB:
            Replicate_Do_Table:
        Replicate_Ignore_Table:
       Replicate_Wild_Do_Table:
   Replicate_Wild_Ignore_Table:
                    Last_Errno: 0
                    Last_Error:
                  Skip_Counter: 0
           Exec_Master_Log_Pos: 14137515
               Relay_Log_Space: 768
               Until_Condition: None
                Until_Log_File:
                 Until_Log_Pos: 0
            Master_SSL_Allowed: No
            Master_SSL_CA_File:
            Master_SSL_CA_Path:
               Master_SSL_Cert:
             Master_SSL_Cipher:
                Master_SSL_Key:
         Seconds_Behind_Master: NULL
 Master_SSL_Verify_Server_Cert: No
                 Last_IO_Errno: 0
                 Last_IO_Error:
                Last_SQL_Errno: 0
                Last_SQL_Error:
   Replicate_Ignore_Server_Ids:
              Master_Server_Id: 2
                Master_SSL_Crl:
            Master_SSL_Crlpath:
                    Using_Gtid: No
                   Gtid_IO_Pos:
       Replicate_Do_Domain_Ids:
   Replicate_Ignore_Domain_Ids:
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
              Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0
1 row in set (0.000 sec)

ERROR: No query specified

Logs aren't showing anything and binaries are exploding since they cannot be rotated because the replication won't continue.

I've never seen something like that. Any ideas?

EDIT - How to downgrade to previous version:
This seems to be a bigger problem. See: https://downloads.mariadb.org/mariadb/10.4.6/

For all the others who have the same problem, follow these steps to downgrade (I'll give an example for 10.4.6, since this was my case):
Find the version you have used before and the related source: https://downloads.mariadb.org/mariadb/10.4.6/

After that, add it to your /etc/apt/sources.list:

deb [arch=amd64,ppc64el,arm64] http://mirrors.n-ix.net/mariadb/mariadb-10.4.6/repo/ubuntu/ xenial main

Downgrade:

apt install mariadb-server=1:10.4.6+maria~xenial mariadb-server-10.4=1:10.4.6+maria~xenial mariadb-client=1:10.4.6+maria~xenial mariadb-client-10.4=1:10.4.6+maria~xenial mariadb-server-core-10.4=1:10.4.6+maria~xenial mariadb-client-core-10.4=1:10.4.6+maria~xenial mysql-common=1:10.4.6+maria~xenial mariadb-client=1:10.4.6+maria~xenial libmariadb3=1:10.4.6+maria~xenial

Hold packages from upgrade, until fix is published:

apt-mark hold mariadb-server*

The steps are also explained here: https://jira.mariadb.org/browse/MDEV-20247?focusedCommentId=132114&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#comment-132114

I also had the same issue using Mariadb 10.4 master-slave replication.
I was using the setting innodb_thread_concurrency in the config file with a non-zero value. I updated it to:

innodb_thread_concurrency = 0

And now it's working again.


What is the best permissions to set for only creating and managing own databases? I want to create a login for a new user who could only create and manage their own databases. Other databases on the server should be read-only to that user. What would be a good set of roles/permissions to use to implement this?

Thank you for your help!

p.s. I am using SQL Server 2008 r2

If you grant the dbcreator server role to a login, that login can create databases.  Databases created by a login also (unless changed as part of the process) will be owned by that login, meaning that the login will be a member of the db_owner role for that database and have full rights to that database and all objects within it.

The primary issue here is that by granting the role, you will have no control over when and how the login creates databases, nor do you have any control on how many databases that login creates.  A login with dbcreator can create as many databases as there is space for.  My recommendation is that you create the database for the login and then grant that login db_owner rights in their database.  You can then grant the login db_datareader in the databases that it doesn't/shouldn't own.  This gives you better manageability along with accomplishing your stated goal.


How to split a string to and get info from another table in mysql I have a system to assign score for each sentence stored in a column:

TABLE 1: word_scores

word       score
this       3
is         4
a          5
test       1
another    0
sentence   8

TABLE 2: sentences (score column is calculated from TABLE 1)

sentence           score
this is a test     13
another sentence   8
this is            7

Now I need to
1. fetch each sentence into PHP,
2. then split the string to words,
3. then catch the score for each word,
4. then calculate the sum of word_scores

Is it possible to calculate the sentence score within mysql?

Bascially yes, there's a way to do that.

Here's a select statement that gets you each sentence with it's score

SELECT
PARSED_SENTENCES.sentence, SUM(COALESCE(WordScores.score, 0)) score
FROM (
     SELECT sentence,
     SUBSTRING(
          sentence
          FROM CASE
               WHEN INDEX_TABLE.POS = 1 THEN 1
               ELSE INDEX_TABLE.POS + 1
               END
          FOR CASE LOCATE(' ', sentence, INDEX_TABLE.POS + 1)
              WHEN 0 THEN CHARACTER_LENGTH(sentence) + 1
              ELSE LOCATE(' ', sentence, INDEX_TABLE.POS + 1)
              END
              - CASE
                WHEN INDEX_TABLE.POS = 1 THEN 1
                ELSE INDEX_TABLE.POS + 1
                END
     ) AS word
     FROM SentenceScores
     INNER JOIN (
          SELECT @rownum:=@rownum+1 POS
          FROM (
             SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
             UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
             UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
          ) a, (
             SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
             UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
             UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
          ) b, (
             SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
             UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
             UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
          ) c, (SELECT @rownum:=0) r
     ) INDEX_TABLE
     ON INDEX_TABLE.POS <= CHAR_LENGTH(SentenceScores.sentence)
     AND (
          INDEX_TABLE.POS = 1
          OR SUBSTRING(SentenceScores.sentence FROM INDEX_TABLE.POS FOR 1) = ' '
     )
) AS PARSED_SENTENCES
LEFT OUTER JOIN WordScores
ON PARSED_SENTENCES.word = WordScores.word
GROUP BY PARSED_SENTENCES.sentence;

You should be able to convert that to an update statement so that you could calculate the scores and apply them to the table at the same time.

Per the comment discussion below, if you wanted to replace the numbers-table subquery with a static numbers table you could replace this part of the query:

 INNER JOIN (
      SELECT @rownum:=@rownum+1 POS
      FROM (
         SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
         UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
         UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
      ) a, (
         SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
         UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
         UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
      ) b, (
         SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
         UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
         UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
      ) c, (SELECT @rownum:=0) r
 ) INDEX_TABLE

with just:

INNER JOIN INDEX_TABLE

where your numbers table and number column are assumed to have the same names as in the subquery, i.e. INDEX_TABLE and POS


What is the best way to remove this table partition? I have to change a partitioned table in Sql Server 2008 R2 to a normal table to make my database compatible with Sql Server 2016 Standard Edition.

Actually the table has 5 partitions with the following number of rows:

> boundary, rows
2009-01-01 00:00:00.000 419
2010-01-01 00:00:00.000 386031
2011-01-01 00:00:00.000 1307990
2012-01-01 00:00:00.000 673183
NULL                    9743057

The table contains a BLOB (image) column. The total size of the table is around 25 GB.

I have read through the question How to remove a table partition but although it has answered there is no accepted answer and the answers do not address my question entirely.

I realized the ALTER PARTITION FUNCTION MERGE RANGE command, but I do not really understand what will happen. Will the data be merget into one of the existing filegroups and afterwards I will still have a partitioned table?

Do I instead have to copy all the data into a new table with the same structure (may take quite a while...)?

I will have to perform this action during a downtime, so I need a procedure that is as efficient as possible.

To remove table partitioning entirely, rebuild all indexes with a filegroup specification instead of partition scheme.  Use CREATE INDEX with the DROP_EXISTING=ON option to do that efficiently.

See MSDN: CREATE INDEX page for syntax.


Does adding page compression to a table also compress existing indexes? I am creating a series of new tables that will have page level compression enabled.

Will this affect the indexes as well, or will I have to also specify compression when creating the indexes?

create table Message
(
  pk_Message   int identity  NOT NULL constraint PK_Message primary key,
  [Message]    varchar(900)  NOT NULL constraint UK_Message__Message unique,
  DateCreated  datetime      NOT NULL default getdate()
) with (data_compression = page)
go

AS you can see in my example, I plan on creating a unique constraint on a rather large data type, and want to take advantage of compression.

From: http://msdn.microsoft.com/en-us/library/cc280449%28v=sql.105%29.aspx

The compression setting of a table is not automatically applied to its nonclustered indexes. Each index must be set individually.


Filtering based on data in excel I was given an excel file with account numbers in them and told to write a query that returns the account numbers in the list that meet a certain criteria. While I was able to write the query in general without problems I don't know how to reference the excel document in the query. I worked around it by copy and pasting the account numbers into a

WHERE ACCOUNT_NUMBER IN (...)

The problem is that the amount of account numbers I had was over 6000 while the max numbers in a IN list is 1000. I ended up doing it piecewise but there must be a better way.

Any suggestions?

Use SQL Developer to (create the table and) load the data into a table.

From there, use a JOIN.


How to access Multiple DB2 Relational Databases In A Single Query I am looking for a solution to the problem described below.
There are two DB2 instances with two authorization IDs on the same server and can be accessed using the same user User1.
Example:
To access Inst1DB, I use the following commands:
[User1 ~]$ . /db2/authID1db2/home/authID1db2/sqllib/db2profile
[User1 ~]$ db2 connect to Inst1DB

To access Inst2DB, run the following commands:
[User1 ~]$ . /db2/auth2db2/home/auth2db2/sqllib/db2profile
[User1 ~]$ db2 connect to Inst2DB

Note that one needs to terminate the current DB2 session before connecting to another DB instance.
To terminate DB session, run the following command:
[User1 ~]$ db2 terminate

Issue:
I want to join two tables, one of which belongs to INST1DB and the other to INST2DB, while connected to INST1DB.
[User1 ~]$ . /db2/authID1db2/home/authID1db2/sqllib/db2profile
[User1 ~]$ db2 connect to Inst1DB
[User1 ~]$ db2 "Select * from Inst1DB.schema1.table1 T1 , Inst2DB.schema2.table2 T2 where T1.column1 = T2.column1.

Curerntly I am getting below error:

SQL0204N  "Inst2DB.schema2.table2" is an undefined name.  SQLSTATE=42704.

Query:
Can anyone suggest to me how to connect the two instances and perform a join on these tables?

One way you can achieve this is via federation. In short words, if you want to access db_b from db_a, you catalog db_b in instance of db_a. You then create nicknames in db_a for the tables in db_b that you want to access. The nicknames can with some restrictions be treated as tables, so you can join a table in db_a with a table in db_b.


Is there any significant performance difference when using string vs bigint as a primary key in Postgres (Aurora)? We can use same logic for both types (string/bigint) to generate consecutive ids (e.g. like https://instagram-engineering.com/sharding-ids-at-instagram-1cf5a71e5a5c). This way both are naturally sortable.
So, let's say we have a value 30722102503933550.
What is the real performance difference if we would leave it as bigint (30722102503933550::bigint) vs using as a string ("30722102503933550"::text) for a primary key.

The question of performance can only be assessed if you know the exact SQL statement. There is no way to answer it in general.
That said, you should go with bigint. A bigint takes 8 bytes of storage, while a text with the value in your question requires 18 bytes (1 byte compressed TOAST header). This becomes particularly relevant if you want an index on the column.
Besides, comparing numbers is faster than comparing strings.


Msg 1833, File 'ABC.ndf' cannot be reused until after the next BACKUP LOG operation I am having a Database.
The size of my database is near about 4GB(Only .mdf file).
In that database only few tables present which have the maximum data of database. One of them and the biggest table is Table1. This is a huge table means everyday near about 80000 data inserted and same no. of records deleted. There are 30 days * 80000 records present in that table. This table is used very frequently. Hence it takes time to query large data. So my development team decided to create Partition of same table. Table11 has four columns in primary key.
Primary Key Columns :

ChequeDate Date
RunNo Int
SorterNo Int
SequenceNo Int

We decided to partition the table on the basis of SequenceNo column.

I created a partition script and ran it which partitions an existing table. This works perfectly.
Next I run a "rollback" script to remove the partitioning. This also runs perfectly.

When I try to reapply partitioning on the same table it gives me the error mentioned in title. I found a manual solution that worked for me.

My problem is this process should run from a script only. No manual work should be necessary.

I assume that the problem is in the rollback script:

ALTER TABLE [dbo].[Table1] DROP CONSTRAINT [PK_Table1] with (move to [primary]);
DROP PARTITION SCHEME Table1PartSch
DROP PARTITION FUNCTION Table1PartFunc

ALTER DATABASE [MyDataBase] REMOVE FILE Table125LData;
ALTER DATABASE [MyDataBase] REMOVE FILE Table117LData;
ALTER DATABASE [MyDataBase] REMOVE FILE Table17LData;
ALTER DATABASE [MyDataBase] REMOVE FILE Table12LData;
ALTER DATABASE [MyDataBase] REMOVE FILE Table1RestData;
GO
ALTER DATABASE [MyDataBase] REMOVE FILEGROUP Table125LFG;
ALTER DATABASE [MyDataBase] REMOVE FILEGROUP Table117LFG;
ALTER DATABASE [MyDataBase] REMOVE FILEGROUP Table17LFG;
ALTER DATABASE [MyDataBase] REMOVE FILEGROUP Table12LFG;
ALTER DATABASE [MyDataBase] REMOVE FILEGROUP Table1RestFG;
GO

If someone have better solution other than Partitioning, to this situation is Welcome.

File '%ls' cannot be reused until after the next BACKUP LOG operation.

SQL Server insists that a backup is taken before a file is reused so it can guarantee database recovery in case of disaster.

The minimum backup needed is a log backup, but the documentation recommends a full database backup after any operation that adds or removes files from a database:

By adding a file, removing it, and attempting to add it again, you run into this requirement.

If you have a genuine need to add and remove files, you will need to add a BACKUP DATABASE or BACKUP LOG statement to your script.

This backup will form part of the recovery chain, so it is important you keep it safe in case you need to recover the database.

See BACKUP (Transact-SQL) for syntax details and further information.


MySQL database got corrupted It seems that whenever I restart my Windows 7 64bit box MySQL gets corrupted.

I just wanted to make this bug known as it's a serious issue. Are there any special settings I need to setup (task scheduler) to prevent this from happening?

Is there a way to inform Windows to shutdown MySQL?

MySQL can run as a service and I hope that windows is stopping the services gracefully or you are likely to have more problems outside of MySQL. If you want to be careful, stop the service by hand. This should allow MySQL to flush all buffers before stopping. I would guess from your comment that you're getting corruption that you are using the MyISAM engine; my advice would be to migrate your tables to the InnoDB engine to make best use of the durability of the engine. In the event of a crash or ungraceful shutdown, MySQL will better recover data.


This refactoring from cursor on a dblink'ed Oracle table OK? The data is being compared from the local version of the table to one in the contracts linked server.  I'm not certain what the goal of this code is in the first place, but I'm trying to get a handle on simplifying it a little.

CURSOR c3 IS
    SELECT bs.billing_service_id, bs.oser_id
    FROM   (SELECT oser_id
            FROM   other_services
            MINUS
            SELECT oser_id
            FROM   other_services@contracts) a,
           billing_service bs
    WHERE  bs.oser_id = a.oser_id
           AND bs.sent = 0
           AND bs.billing_service_id NOT IN (
               SELECT bd.billing_service_id
               FROM   billing_details bd);

-- snip

FOR c_rec3 IN c3 LOOP
    DELETE FROM billing_service
          WHERE billing_service_id = c_rec3.billing_service_id;
END LOOP;

Seems to me that this could be naively refactored (without fooling with any other issues it has like the implicit joins and other garbage) simply as:

    DELETE FROM billing_service
          WHERE billing_service_id IN
(
    SELECT bs.billing_service_id
    FROM   (SELECT oser_id
            FROM   other_services
            MINUS
            SELECT oser_id
            FROM   other_services@contracts) a,
           billing_service bs
    WHERE  bs.oser_id = a.oser_id
           AND bs.sent = 0
           AND bs.billing_service_id NOT IN (
               SELECT bd.billing_service_id
               FROM   billing_details bd)
);

I don't think it's a significant amount of data in these tables - about 26000 rows on each side, and I don't think the set of differences is that big of a subset of the two.

But I was wondering if there are issues with DB Links which make cursors preferable to regular set-oriented methods?  This operation is part of a stored procedure which is run from an Oracle job on a daily basis.

Joins across database links can lead to sub-optimal execution plans as Oracle doesn't have all the information available about both sites. Queries across db links can (and do) perform just fine when joined to local tables though.

If writing a new query with local and remote tables I'd start joining it all together (set-based) then only break it into separate queries if performance is unacceptable and other tuning hasn't worked.

Given your query is just a simple minus with no joins to the remote site, I would expect the refactored set-based approach to be quicker. Make sure you test it though!


SQL query to replace attribute value with another attribute value- postgre I have a table on PostgreSQL as below:

  id | user_id | attribute_key | attribute_value

I have data in the table as below:

1  | 1 | first_name | Jesus
2  | 1 | last_name  | Christ
3  | 1 | Email      | oldEmail1@test.com
4  | 1 | newEmail   | newEmail1@test.com
5  | 2 | first_name | Ram
6  | 2 | last_name  | Krish
7  | 2 | Email      | oldEmail2@test.com
8  | 2 | newEmail   | newEmail2@test.com
9  | 3 | first_name | Muhammad
10 | 3 | last_name  | Nab
11 | 3 | Email      | oldEmail3@test.com
12 | 3 | newEmail   | null

Now I need to check for each user_id, is there a "newEmail" exist and if exist, the corresponding "Email" value should be updated with "newEmail"value. and delete the "newEmail" attribute.

Expected output of above data table as below:

1  | 1 | first_name | Jesus
2  | 1 | last_name  | Christ
3  | 1 | Email      | newEmail1@test.com
5  | 2 | first_name | Ram
6  | 2 | last_name  | Krish
7  | 2 | Email      | newEmail2@test.com
9  | 3 | first_name | Muhammad
10 | 3 | last_name  | Nab
11 | 3 | Email      | oldEmail3@test.com

Can anyone help me with this?

Easy with a CTE:

with updata as (
select user_id,attribute_value
from eavtable
where attribute_key='newEmail'
)
UPDATE eavtable
SET attribute_value = updata.attribute_value
FROM updata
WHERE eavtable.user_id = updata.user_id
and eavtable.attribute_key = 'Email';

Then a delete:

delete from eavtable where attribute_key = 'newEmail';

DB Fiddle Link.

Might also be able to do the whole thing in a single merge.


Moving database objects to another database without wizard I have several databases on production and i have also test environment. I don't have permission to do backup and restore operations. Actually i am using wizard to generate scripts (schema + data) and run sql on another database. Is there a way i can do this via script (SQL, PowerShell, C#, etc..) ? Can i move all database objects from production to test on one click ideally, it is possible that script will universal and input parameter will be database name for example ? The problems with generated scripts are two => SQL can be too large to open in sql manager and also it is time consuming to run wizard everytime, because i need production data often.

The final vision based on that universal script or job is that i have an web dashboard, where i can click on database or to have set of command shell scripts and the database will replicate to the test environment where i can modify data.

Any Idea ?

You can use SMO to script a database schema from Powershell, C# or VB. A great article on the subject can be found here.

Regarding data, I think that the simplest option is to pipe each table's data to a file using BCP, then import it back to the destination database, again using BCP. I strongly suggest the native format (-n option), as it simplifies the process enormously.


Troubleshooting error message Invalid Object Name accessing MS SQL with python Using python, I can connect to a MS SQL database, but cannot run any queries at all. I always get the error - invalid object name.
I understand from other stack exchanges that I may be accessing the wrong database (although that is specified in my connection); there may be something wrong in my schema (whatever that is); or I simply don't have permission to run a query (I should have been given read only permissions).
My connection looks like:
import pyodbc

connSurvNet = pyodbc.connect(r'DRIVER={ODBC Driver 17 for SQL Server};' +
   ('SERVER={server},{port};' +
   # 'DATABASE={database};' +
   'UID={username};' +
   'PWD={password}').format(
   server= 'xxx',
   port= 1433,
   database= 'db',
   username= 'xxxx',
   password= 'xxxx')
   )

In another database, I can download a table into a pandas dataframe with the following code:
df = pd.read_sql_query('''SELECT * FROM MyTable''', conn)

But from the database in question, MyTable is never a valid object name.
We have a gui interface for querying the data, which allows me to see the SQL-code used for the queries. It includes code like:
FROM [Data].[Version]

I've a pdf of the tables, with all the table names and columns, but nothing I enter as MyTable is ever valid.
I've run a few ideas from Stack Exchanges, like DB_SELECT(), which shows I'm connected to a database 'master', but I do not know of other things I can do to isolate my issue.
I've tried [Data].[MyTable] as well as just Data.MyTable, and even [Data].[Version], but same error. Looking at the Object Explorer in Management Studio, I can see everything - the server is listed, then in a folder Tables, all the tables are listed, like Data.Version.
I can select the top rows, and see the data. What should I look for that will allow me to put the table into my dataframe? I guess it's obvious, but not to me.

Every table belongs to a schema
you yourself used an example - [Data].[Version]
Data is the Schema and Version is the Object name
So you need to find out which schema MyTable belongs to and supply it in your query.
[Data].[MyTable] for example.
(The Object browser in Management Studio is your friend)
Good to know. Every User has a Default Schema and the default default schema is dbo
Therefore if the Table belongs to the dbo schema and the user has the default default schema it is a shorthand to write MyTable.

The master database is a system database so looks like that might be where your problem is. You could try 3 part naming
dbname.schemaname.tablename
or try running a query "use dbname" which should change your context to the right db
But dig into why you are connecting to master instead of the database you specify in the connection string (might be a type, lack of permissions or many other things)


Window function IGNORE NULLS workaround for PostgreSQL With the following query I can use the LAG() function to repeat the last non null value of c column:

SELECT coalesce(open_time, extract(EPOCH from date_trunc('minute', datetime)) * 1000)               open_time,
   coalesce(o, LAG(c) over w)                                                                          o,
   coalesce(h, LAG(c) over w)                                                                          h,
   coalesce(l, LAG(c) over w)                                                                          l,
   coalesce(c, LAG(c) over w)                                                                          c,
   coalesce(v, 0)                                                                                v,
   coalesce(close_time, extract(EPOCH from date_trunc('minute', datetime)) * 1000 + ((60000 * 1) - 1)) close_time
from temporary_timeframe
window w as (order by datetime);

Getting the following result:

But I need to repeat the value of the c column while the current column value is null. I see that if PostgreSQL supports IGNORE NULLS attribute on window functions this would be solved. How to solve this without IGNORE NULLS?

LAG() doesn't repeat the last non null value.

Quoted from docs

  returns value evaluated at the row that is offset rows before the current row within the partition; if there is no such row, instead return default (which must be of the same type as value)

But you can set a partition depending on one column value and then use firts_value() function.

create table tbl (id int, a int, b int, c int);

✓

insert into tbl values
(1, 12, 4, 3),
(2, 10, 10, 5),
(3, 6, 12, 23),
(4, 6, null, 10),
(5, 7, null, 4),
(6, 1, 8, 10),
(7, 4, null, 3);

7 rows affected

select id, a, first_value(b) over (partition by grp) as b, c
from (
      select id, a, b, c,
             sum(case when b is not null then 1 end) over (order by id) as grp
      from   tbl
     ) t

id |  a |  b |  c
-: | -: | -: | -:
 1 | 12 |  4 |  3
 2 | 10 | 10 |  5
 3 |  6 | 12 | 23
 4 |  6 | 12 | 10
 5 |  7 | 12 |  4
 6 |  1 |  8 | 10
 7 |  4 |  8 |  3

db<>fiddle here


Create a MySQL database with charset UTF-8 I'm new to MySQL and I would like to know:

How can I create a database with charset utf-8 like I did in navicat?

create mydatabase;

...seems to be using some kind of default charset.

Update in 2019-10-29
As mentions by @Manuel Jordan in comments, utf8mb4_0900_ai_ci is the new default in MySQL 8.0, so the following is now again a better practice:

CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Answer before 2019-10-29
Note: The following is now considered a better practice (see bikeman868's answer):

CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Original answer:

Try this:

CREATE DATABASE mydatabase CHARACTER SET utf8 COLLATE utf8_general_ci;

For more information, see Database Character Set and Collation in the MySQL Reference Manual.


Is there a particular name for the type of structure of this database? I'm trying to set up a data structure for an internal database of skills within a company, but I'm not entirely sure what "model" this fits as I'm quite new to databases and data structuring in general.

I've made an example of what I'm trying to do in excel:

Basically I'd like to have entries (users, in this case) which can exist in several different branches and are recognized as the same user, but have variables distinct for each branch in which they exist. Specifically I require the option to then sort by entry and see in which branches they exist, along with that branch' specific values for that user.

I think what this boils down to is a network structure, but again, I'm really quite new to this. If anything I would love some keywords for me to start googling with, because as of right now I can't see the forest for the trees

I don't know if there is a name for the structure that you are displaying in Excel, but the reporting layer rarely maps to the best way to store the actual data in a relational manner.

This link discusses database normalization https://en.wikipedia.org/wiki/Database_normalization

Given your requirements above, I would use three tables, one for people, one for skills and another to map them together.  The decision about where to store a skill level (for example, Spanish 4) is up to you.  You could either make a seperate skill called "Spanish 4" or store the level information on the PersonSkill table in the example below.

Your reporting layer can handle displaying the data, even pivoting it based on skill name, or do whatever else you need to do with it.  Example below is for MSSQL specifically but other RDBMS would be similar.

/** CREATE TABLES */
CREATE TABLE dbo.Person
    (
    PersonID INT NOT NULL IDENTITY(1,1) PRIMARY KEY
    , PersonName VARCHAR(100) NOT NULL
    )

CREATE TABLE dbo.Skill
    (
    SkillID INT NOT NULL IDENTITY(1,1) PRIMARY KEY
    , SkillName VARCHAR(100) NOT NULL
    )

CREATE TABLE dbo.PersonSkill
    (
    PersonSkillID INT NOT NULL IDENTITY(1,1) PRIMARY KEY
    , PersonID INT NOT NULL REFERENCES dbo.Person (PersonID)
    , SkillID INT NOT NULL REFERENCES dbo.Skill (SkillID)
    )

/** GET SOME DATA **/
INSERT INTO dbo.Person
(PersonName)
VALUES ('John Smith') --1
    , ('Jane Doe') --2

INSERT INTO dbo.Skill
(SkillName)
VALUES ('Coding') --1
    , ('English') --2
    , ('Spanish') --3

INSERT INTO dbo.PersonSkill
(PersonID, SkillID)
VALUES (1,1)
    , (2,1)
    , (2,3)

/** QUERIES */
--What Skills does John have?
SELECT P.PersonName
    , S.SkillName
FROM dbo.Person AS P
    INNER JOIN dbo.PersonSkill AS PS ON PS.PersonID = P.PersonID
    INNER JOIN dbo.Skill AS S ON S.SkillID = PS.SkillID
WHERE P.PersonName = 'John Smith'

--Who can speak Spanish?
SELECT P.PersonName
    , S.SkillName
FROM dbo.Person AS P
    INNER JOIN dbo.PersonSkill AS PS ON PS.PersonID = P.PersonID
    INNER JOIN dbo.Skill AS S ON S.SkillID = PS.SkillID
WHERE S.SkillName = 'Spanish'


Concern on Active Directory Users on Azure SQL Server I was able to add Azure Active Directory Users to Azure Sql server. My question is not related in including or removing an Azure AD user, but its existence after the user ceases himself/herself.

My concern here is, will Azure SQL automatically remove the user from the DB and its grants or will the user be disabled?

Thank You,

I just tested this out in my Azure tenant.

Created a new Azure AD user
Added the test user to my Azure SQL DB.
Logged in as the test user and verified access.
Deleted the test user from Azure AD.
Confirmed: Azure SQL does not automatically remove the user from the DB.

Moreover this user retained access to the db for quite a while after the user record was deleted...  I did a db rename to disconnect the users from the db and then  tried to log in again and was denied.

You might be better off managing access to the database using Azure AD groups instead of adding individual users.


Dynamic pivoting in PostgreSQL I have a scenario where I have to pivot a list of rows to columns dynamically .For a particular order_number there might be several products. I want all those products to be in a separate column, along with its product_id (that too in columns), but we cannot predict how many products will there for a particular order_number.

Attaching a sample input table and desired output. I have tried the crosstab function but the result is not correct.

Table values:

Desired Ouput:

Contributions originally left as comments

I typically aggregate dynamic columns into a single JSON value these days rather than creating dynamic columns for each attribute. That might be a challenge though as well, with your outdated (and unsupported) Postgres version - a-horse-with-no-name

The desired output can't be obtained directly in SQL. You need to write a query that generates the query that generates your desired output. Giving up on your desired output and producing JSON instead may also be considered, as it would be simpler. - Daniel Vérité


Force Client to Validate SSL Certificate? When SQL Server uses the Force Encryption with value "yes" then if in the client-side we use encrypt=true option in the connection string, then the client application tries to verify the server certificate.

My question is this how we can force the client (by the server or any way) to verify the certificate regardless of the connection string value?

My question is this how we can force the client (by the server or any way) to verify the certificate regardless of the connection string value?

Connection string values override defaults set in the driver if the connection string has the item filled in. There is no overarching way of forcing this except to use TrustServerCertificate=false in the connection string to zero out any other options.


How do you detect whether you have permission to query sys.dm_os_performance_counters? According to the sys.dm_os_performance_counters documentation:

  On SQL Server, requires VIEW SERVER STATE permission.

  On SQL Database Premium Tiers, requires the VIEW DATABASE STATE permission in the database. On SQL Database Standard and Basic Tiers, requires the Server admin or an Azure Active Directory admin account.

How can you check, from T-SQL, if you have these permissions?

Eventually we worked it out!

If you're connected to SQL Azure Database, and the database tier is Premium, check for the VIEW DATABASE STATE permission
If you're connected to SQL Azure Database, and the database tier is not Premium, check for EXECUTE permission on sys.dm_os_performance_counters
Otherwise, check for the VIEW SERVER STATE permission

The bit of this that isn't an obvious translation of the documentation is that "EXECUTE permission on sys.dm_os_performance_counters" is equivalent to "being a Server admin or an Azure Active Directory admin" in the case of non-Premium Azure SQL Database. This was just what we found by trial and error, and is not documented anywhere that I'm aware of.

DECLARE @Result VARCHAR(MAX)
SET @Result = ''

IF (SERVERPROPERTY('EngineEdition') = 5)
    BEGIN
        IF (DATABASEPROPERTYEX(DB_NAME(), 'Edition') = 'Premium')
            BEGIN
                IF NOT EXISTS(SELECT 1 FROM sys.fn_my_permissions(NULL, 'DATABASE') WHERE permission_name = 'VIEW DATABASE STATE')
                    SET @Result = 'You do not have VIEW DATABASE STATE permissions for this database.'
            END
        ELSE
            BEGIN
                IF NOT EXISTS(SELECT 1 FROM sys.fn_my_permissions('sys.dm_os_performance_counters', 'OBJECT') WHERE permission_name = 'EXECUTE')
                    SET @Result = 'You do not have server admin or Azure active directory admin permissions.'
            END
    END
ELSE
    BEGIN
        IF NOT EXISTS(SELECT 1 FROM sys.fn_my_permissions(NULL, 'SERVER') WHERE permission_name = 'VIEW SERVER STATE')
            SET @Result = 'You do not have VIEW SERVER STATE permissions within this instance.'
    END
RETURN @Result


phpmyadmin is no longer letting me set auto_increment value I am following these instructions to reset the value of auto_increment to the last highest value using phpMyAdmin.
Example:
If I have this table and data:
DROP TABLE IF EXISTS `Tbl_Lis_Agencias`;
CREATE TABLE IF NOT EXISTS `Tbl_Lis_Agencias` (
  `IdAgency` int(3) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
  `AgencyCodU` int(3) UNSIGNED ZEROFILL NOT NULL DEFAULT '000',
  `AgencyName` varchar(40) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `Agency_Order` int UNSIGNED DEFAULT NULL,
  `AgencyStatus` int UNSIGNED NOT NULL DEFAULT '1',
  PRIMARY KEY (`IdAgency`),
  UNIQUE KEY `IdAgency` (`IdAgency`),
  UNIQUE KEY `Agency_Order` (`Agency_Order`),
  UNIQUE KEY `AgencyName` (`AgencyName`),
  KEY `xAgencyStatus` (`AgencyStatus`)
) ENGINE=InnoDB AUTO_INCREMENT=12345 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `Tbl_Lis_Agencias` VALUES
(001, 001, 'Panama', 1, 1),
(002, 020, 'Aguadulce', 2, 1),
(003, 080, 'David', 3, 1),
(004, 010, 'Vacamonte', 4, 1),
(005, 060, 'Prueba', 5, 1);

If I run this:

phpMyAdmin says that is was a succeful update:

but when I switch to the operation tab I see this again:

What I don't want to do and I am forced is to go to the terminal and run this:
ALTER TABLE Tbl_Lis_Agencias AUTO_INCREMENT = 5

Then I look at the operations tab again

Why isn't the operations tab updated in the first example?
The solution in this post is not useful, it is mandatory for me to do everything through phpMyAdmin, not through the terminal.
update
i have check that phpmyadmin show ghost value; related to this post: https://github.com/phpmyadmin/phpmyadmin/issues/16378

https://stackoverflow.com/questions/64214549/mysql-information-schema-bugged/64214609#64214609
MySQL 8.0 tries to cache the statistics about tables, but there seem to be some bugs in the implementation. Sometimes it shows table statistics as NULL, and sometimes it shows values, but fails to update them as you modify table data.
See https://bugs.mysql.com/bug.php?id=83957 for example, a bug that discusses the problems with this caching behavior.
You can disable the caching. It may cause queries against the INFORMATION_SCHEMA or SHOW TABLE STATUS to be a little bit slower, but I would guess it's no worse than in versions of MySQL before 8.0.
SET GLOBAL information_schema_stats_expiry = 0;

The integer value is the number of seconds MySQL keeps statistics cached. If you query the table stats, you may see old values from the cache, until they expire and MySQL refreshes them by reading from the storage engine.
The default value for the cache expiration is 86400, or 24 hours. That seems excessive.
See https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_information_schema_stats_expiry


3 additional varchar(32) keys and also updating them -- too bad for performance? I have a table in mind which basicaly would look like this:

table1
--------
id      bigserial autoincrement primary key,
token1  varchar(32) unique not null,
token2  varchar(32) unique not null,
token3  varchar(32) unique not null
[........some other data ......]

My client web app is build is such a way that I can't always use "id" to retrieve a record. Namely, I need to be able to retrieve a record by one of the "tokens" instead:

 select * from table1 where id = 1;
 select * from table1 where token1 = 'fdsafd';
 select * from table1 where token2 = 'ghgfdhg';
 select * from table1 where token3 = 'rewqrw';

Also, my client web app may need to update the "token(s)" also, different ones at different times, if a certain condition ocurrs. Not too often.

I estimate that the table would grow, at most, at 100.000 records per day, evenly distributed during a day; with around 20k updates per day.

Questions:

1) Will using these 3 tokens, of 32 characters long, as oppossed to using only "id",

and, also

2) Will updating them these

worsen the performance significantly?

3) Ways to improve it? All 3 tokens must remain. Should I create a separate table2 for tokens, to which I'd insert tokens only, to avoid having to update them in "table1"?

Maintaining those indexes will have a cost.  It is not possible to say if that cost is "too bad" or if it is significant to you.  The cost will depend on your RAM and memory settings, on your data size, and on your storage system.  Do some benchmarking on your own system (or a clone of it) and see.

If 100,000 rows are arriving evenly throughout the day, any reasonably sized server should be able to cope with this load.  You might not want to use a AWS t2.nano.  But you didn't say how many rows are updated per day, but "not too often" doesn't seem hard to deal with.

Assuming all index pages other than the leaf pages are already cached, then an update of one random unique token to another random unique token would need to read one index leaf page (for the old value), read and dirty one table page, read and dirty one index leaf page (for the new value), and possibly read and dirty a 2nd table page if the new record can't fit on the same page as the old one, or if there is an index entry for the new value already and it needs to verify that entry is not "live" in violation of the unique constraint.  The WAL records covering all of this will need to be written (most likely fitting into one write request) and flushed.  This shouldn't take more than 100ms in the typical case if you have 5400 RPM hard disk, substantially less if many of those random reads are satisfied by the caches.  In the worst case could be a lot longer if someone else is syncing large amounts of data to disk at the same time and you get contention on that.


Can sp_Blitz, sp_BlitzCache, or sp_BlitzFirst identify what queries caused a major growth operation to occur on a particular log file? I'm currently logging the outputs of sp_Blitz, sp_BlitzCache, and sp_BlitzFirst regularly (via SQL jobs).

A particular database's log file grew somewhat exponentially and I believe I can see the grows that occurred from the output from sp_BlitzFirst (via the SizeOnDiskMBgrowth column).

But I was wondering if any of those sp_Blitz... procedures would also be able to tell me the queries that were running that caused the large grows, or at least what queries were running at the time of those grows and then I can do a loose correlation?

Sure, start with:

SELECT TOP 100 *
  FROM dbo.BlitzCache
  WHERE CheckDate BETWEEN '2019-02-18' AND '2019-02-19'
  ORDER BY TotalWrites DESC;

The output of sp_BlitzCache includes the most resource-intensive queries in the plan cache. Starting with that query, you can narrow down what queries wrote the most data during your problem time span.

I like this approach a lot more than the Extended Events one since the XE session would only catch the query that happened to trigger the log growth - which may not be the one that did the most writes. (It might have just been the straw that broke the camel's back.)

Note that these are the queries that did the most writes altogether, though, and those numbers aren't reset each time sp_BlitzCache runs. You may need to do a little more digging to find out which ones ran the most often (or generated the most writes) during a particular time span by joining to the prior runs of that query in dbo.BlitzCache.


Database Backup compression I am trying to see all of my backups are compressed or not. I ran below query to check but not getting good result:

select top(10) database_name,
case when  backup_size = compressed_backup_size then 'Compressed'
else 'Not compressed'
end as Compression,
backup_finish_date
from msdb.dbo.backupset
where database_name ='MyDAtabase'
order by backup_start_date desc

I am using:

  Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)
  Jun 28 2012 08:36:30   Copyright (c) Microsoft Corporation
  Standard Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1)

By default we setup compression, but once I ran that query I got result database is not compressed.

Your case statement is wrong. Right now if the values are the same then it shows compressed whereas you would want it to show not compressed.

select top(10) database_name,
case when  backup_size = compressed_backup_size then 'Not compressed'
else 'Compressed'
end as Compression,
backup_finish_date
from msdb.dbo.backupset
where database_name ='MyDAtabase'
order by backup_start_date desc


Backup and restore SQL server database with FILESTREAM filegroup I use SQL Server and have a huge database with two filegroups:

Primary: Which contains all the data except the large files (1MB+)
FILESTREAM (read/write): Which contains the large files

Now, the backup scenario is:

Each Friday get a full backup (2 A.M)
Each day on week except Friday get a differential backup (2 A.M)

Since the database is large, and it is in production on a remote server, whenever I want to bring the database to my local environment to create a test database (weekly), I have to bring both the primary and the filestream.

I would like to be able to change the way the backups and restores are done, in such a way that only had to bring the primary filegroup, ignoring the filestream. By this way, every week I would only bring the primary filegroup, and not all information that suppose the filestream.

I think there can be a lot of problems, and all filestream references can be lost when accessing the files. I would like to know if is it possible to modify the content of all filestream columns when performing a backup, or use a different filestream hosted in the test environment. Also, I've heard about Piecemeal Restore of only some Filegroups, but I have many doubts on how to carry it out.

Question 1: can I have this scenario?

Question 2: is it a good idea to have only one Full backup and bring differential backups/transaction logs to test environment?

Question 3: can I have better scenario to backup and restore?

I'm all ears to recommendations. If you have any example case, please show me with a T-SQL query.

I would like to know if is it possible to modify the content of all filestream columns when performing a backup

No.

I would create my test database with the same schema as the production database, and then write a custom script to sync the data in the primary filegroup (this is fairly easy as your production system is the source, so the sync is only in one direction).

Then with the filestream data, I would stub the files, which could be done any number of ways. Off the top of my head, I'd somehow generate zero-byte files on the test side.


SQL Server - One of the cores always at 100% I'm running SQL Server Web on a VM with 20 cores. (I know, Web edition can only make use of 16 cores but I'll get that resolved soon when I upgrade to Standard).

But what's funny to me is that for some reason, one of the cores is always at 100%. I've never seen this before as usually the load is spread out between all the cores.

Anyone experience this?

The long running queries/transactions are all types as this is handling an ecommerce site. The transactions themselves aren't the issue as I can paste them into new query window and they run fast so it sounds like it's just the sheer amount of them. Also, when traffic does down during off peak hours, I no longer have the issue.

There are no other applications running on the server that is using more than 1-2% of the CPU.

could be a few things, start by checking the wait types.
A couple I have seen
RSS Receive Side Scaling is not enabled so all n/w traffic is handled by a single CPU
or
Ghost cleanup running - this is a system SPID responsible for removing deleted records and runs on a single core
https://docs.microsoft.com/en-us/sql/relational-databases/ghost-record-cleanup-process-guide?view=sql-server-ver15


How do I ORDER BY timestamp in this recursive query? I have a working reqursive query for my comments table.

WITH RECURSIVE comments_tree(
  id,
  content,
  created_at,
  path,
  level
) AS (
  SELECT
    id,
    content,
    created_at,
    ARRAY[id],
    1 as level
  FROM comments
  WHERE
    parent_id IS NULL
UNION ALL
  SELECT
    comments.id,
    comments.content,
    comments.created_at,
    path || comments.id,
    comments_tree.level + 1 as level
  FROM comments_tree
  JOIN comments ON comments_tree.id = comments.parent_id
  WHERE NOT comments.id = ANY(path)
)
SELECT * FROM comments_tree ORDER BY path;

I have added 5 comments (2 of them have content 'parent x', others - 'child x').

Example of output (formatted to be more readable):

"-> parent 1: 16:49"
"--> child 1: 16:50"
"--> child 2: 16:51"
"--> child 3: 16:52"
"-> parent 2: 16:53"

I want to order comments by their creation time (created_at field) like this:

"-> parent 2: 16:53"
"-> parent 1: 16:49"
"--> child 3: 16:52"
"--> child 2: 16:51"
"--> child 1: 16:50"

How do I do that?

For simplicity I omit additional columns to focus on core of the problem.
Simple solution
Collect timestamps (created_at) in another array (path_created) like:
WITH RECURSIVE tree AS (
   SELECT id, created_at, ARRAY[id] AS path
        , ARRAY[created_at] AS path_created
   FROM   comments
   WHERE  parent_id IS NULL

   UNION ALL
   SELECT c.id, c.created_at, path || c.id
        , t.path_created || c.created_at
   FROM   tree     t
   JOIN   comments c ON t.id = c.parent_id
   WHERE  NOT c.id = ANY(t.path)
   )
SELECT *
FROM   tree
ORDER  BY path_created[1] DESC, path[1] ASC
        , path_created[2] DESC, path[2] ASC
        , path_created[3] DESC, path[3] ASC
        , ...

If two threads start at the same created_at, sort the smaller id first.
... which is good enough for a low maximum number of elements. Works if there are fewer elements in the array. It's neither very dynamic nor very elegant for an unknown number of levels, though.
Dynamic number of levels
You could concatenate or calculate a single value from created_at and id or use a document type like jsonb. All of these solutions have corner cases. If you put some smarts into it, it'll be unambiguous.
The general, clean solution is to compute the row number for each row per level with row_number(), ordered by any number of columns or expressions directly:
WITH RECURSIVE tree AS (
   SELECT id, created_at, ARRAY[id] AS path
        , ARRAY[row_number() OVER (ORDER BY created_at DESC, id ASC)] AS path_sort
   FROM   comments
   WHERE  parent_id IS NULL

   UNION ALL
   SELECT c.id, c.created_at, t.path || c.id
        , t.path_sort || row_number() OVER (ORDER BY c.created_at DESC, c.id ASC)
   FROM   tree t
   JOIN   comments c ON t.id = c.parent_id
   WHERE  NOT c.id = ANY(path)
   )
SELECT *
FROM   tree
ORDER  BY path_sort;

dbfiddle here
row_number() breaks ties among peers in any case. In the example the added id to the ORDER BY clause of the window definition. Assuming it is the PK and therefore breaks ties in deterministic fashion.


How do I do an outer join between all nested subqueries? (getting an error of 'OUTER' is not a valid input for this position) I have this query:

SELECT AVG(a.score) AS Average1, AVG(b.score) AS Average2, AVG(c.score) AS Average3
FROM
(SELECT subfactor.score, dateID, EmployeeID
    FROM employeescore
        LEFT JOIN subfactor
        ON employeescore.SubFactorID = subfactor.SubFactorID
            LEFT JOIN factor
            ON subfactor.FactorID = factor.FactorID
            WHERE DateID='Jan2015' AND employeescore.EmployeeID='1' AND subfactor.FactorID='A1')a
OUTER JOIN
(SELECT subfactor.score, DateID, EmployeeID
    FROM employeescore
        LEFT JOIN subfactor
        ON employeescore.SubFactorID = subfactor.SubFactorID
            LEFT JOIN factor
            ON subfactor.FactorID = factor.FactorID
                WHERE DateID='Feb2015' AND employeescore.EmployeeID='1' AND subfactor.FactorID='A1')b
ON a.EmployeeID = b.EmployeeID
OUTER JOIN
(SELECT subfactor.score, DateID, EmployeeID
    FROM employeescore
        LEFT JOIN subfactor
        ON employeescore.SubFactorID = subfactor.SubFactorID
            LEFT JOIN factor
            ON subfactor.FactorID = factor.FactorID
                WHERE DateID='Mar2015' AND employeescore.EmployeeID='1' AND subfactor.FactorID='A1')c
ON b.EmployeeID = c.EmployeeID;

Basically I want to select the averages of the three scores and I want to display the output regardless if one of the joined sub queries doesn't have any records. But I keep getting an error of ('OUTER' is not a valid input for this position).
How can I repair?

You need to specify whether you want an left, right or full outer join. A left outer join is the same as left join. Assuming this is what you want:

[...]
FROM (SELECT subfactor.score, dateID, EmployeeID
      FROM employeescore
      LEFT JOIN subfactor
          ON employeescore.SubFactorID = subfactor.SubFactorID
      LEFT JOIN factor
          ON subfactor.FactorID = factor.FactorID
      WHERE DateID='Jan2015'
        AND employeescore.EmployeeID='1'
        AND subfactor.FactorID='A1'
     ) a
LEFT JOIN (SELECT subfactor.score, DateID, EmployeeID
           FROM employeescore
           LEFT JOIN subfactor
               ON employeescore.SubFactorID = subfactor.SubFactorID
           LEFT JOIN factor
               ON subfactor.FactorID = factor.FactorID
           WHERE DateID='Feb2015'
             AND employeescore.EmployeeID='1'
             AND subfactor.FactorID='A1'
      )b
          ON a.EmployeeID = b.EmployeeID
[...]


UnShrunk SQL Server database restore which is larger than available disk space I cannot restore a Microsoft SQL Server database due to lack of hard drive space. The original database was 1.6TB and approximately 1.4TB was deleted from it and a backup was taken without shrinking the database.

The backup file is only 168GB and was sent to me on a hard drive through a courier. When I try to restore the 168GB backup file it tries to create the original 1.6TB .mdf data file and errors because all my available servers have less than 1TB of available disk space.

First I tried to restore the data files to a NTFS Compressed folder, but I got an error stating that the target file may not have the compression bit set.

Then, per this link: Shrink SQL database during restore

I have tried putting the restored .mdf file into a symbolic link folder which points to a compressed folder, however it errors:

  System.Data.SqlClient.SqlError: There is insufficient free space on
  disk volume 'C:\' to create the database. The database requires
  1541063704576 additional free bytes, while only 748261179392 bytes are
  available. (Microsoft.SqlServer.SmoExtended)

Then, since this is running within a HYPER-V VM, I've tried adding a new virtual disk into a compressed folder, but the VM would not start complaining about one of the drives residing in a compressed folder.

Then, I tried adding a new HYPER-V virtual disk into a symbolic link folder which points to a compressed folder.  This works fine, except the file is not compressed while it's restoring and was getting close to running out of space before I killed it (I'm guessing Explorer may compress the file AFTER it is closed by the writing process "SQL")

The restored database should be only 168GB of actual data within it.

Does anyone have any solution?

I'm not sure if this is an answer, but it's too big to comment

I had the same issue many times. In my case it always was about the log file. when you get backup, the log file may not contain any active log/data and it will not take any space in the backup file, but when restoring, SQL server tries to allocate the same space. If there is any way you can ignore log file when restoring, you might be able to restore.

check this out, it may help


why do I have three copies of data/log files per database on same server instance OS: Windows 7 Professional

Database: SQL Server 2008 R2 Standard Edition

Just detected by SpaceMonger that, each of my databases on test instance has three copies of data files (.mdf) and log files (.ldf).

they are located at three folders:

..\DataFiles\Test
..\LogFiles\Test
..\Microsoft SQL Server\Mssql10_50.test\Mssql\Data

and they have exactly same file size and timestamp.

I confirmed that I have no mirroring setup for any of my databases, neither do I have any special setup to enforce the database to have files splitted.

The only suspicious setting I can find is:

the first two locations are the server level database default location (right click server in the MSSMS -> Properties -> Database Settings -> Database default locations): the first is for Data while the second is for Log,
and the last is each database's file location appearing in the MSSMS (right click a database -> Properties -> Files -> Database Files)

This annoying problem has made my server available disk space very low and forced me to do housekeeping very often.

any idea why does this happen?

any methodology i can follow to find out what has been wrong?

-- EDIT ---

Thank you all for the comments and reply.

We have identified the problem - not related to the sql database setting, but the folders themselves: the three folders have a setup to mirror each other. we have got our network team to investigate why it's so.

It is possible that your databases were set up this way. To find out you can run this query:

SELECT DATABASE_NAME(database_id) DbName,* FROM sys.master_files ORDER BY 1;

It lists all files that belong to each of your databases.

If the databases in question list only one file each, then something outside of SQL Server is creating these copies.

Otherwise each database is just split into multiple files and actually taking up that space. In that case you can really only either delete databases or increase the disk size.

It can have performance benefits to split a database into multiple data files. However, splitting the log into multiple files is usually unnecessary as SQL Server is writing always only to one of them at a time.


Corrupt date fields causing query failure in Oracle Having a real problem with date fields in Oracle. Executing the following works:
ALTER SESSION set NLS_DATE_FORMAT ='DD-MON-YYYY';
select * from mytable where id = 1;

However, changing the NLS_DATE_FORMAT causes the query to fail.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
select * from mytable where id = 1;

I end up with a date specific error.

ORA-01858: a non-numeric character was found where a numeric was expected
Cause: The input data to be converted using a date format model was incorrect. The input data did not contain a number where a number was required by the format model.
Action: Fix the input data or the date format model to make sure the elements match in number and type. Then retry the operation.

The table has four date fields, and I've been able to narrow down the fault to certain instances where one specific field is not null, confidental_until.
If I dump this date field, I don't get any evidence of it being corrupt
select dump(confidential_until) from mytable where id = 1;

Typ=12 Len=7: 120,107,12,31,1,1,1

What could be going wrong? Only 187 records of 420,000+ rows have this problem, with 80,000+ having a non-null confidential_until field.

The date looks fine:

SQL> CREATE TABLE testdate (d date);
Table created
SQL> INSERT INTO testdate
  2     VALUES (to_date(to_char((120-100)*100+107-100)||'-12-31','yyyy-mm-dd'));
1 row inserted
SQL> SELECT DUMP(d), d FROM testdate;
DUMP(D)                                                                          D
-------------------------------------------------------------------------------- -----------
Typ=12 Len=7: 120,107,12,31,1,1,1                                                31/12/2007

Are you able to use the specific conversion function TO_CHAR on those lines? For instance SELECT to_char(confidential_until, 'yyyy-mm-dd') FROM mytable WHERE id = :x


Database table as a queue, order by another table's column I have two tables. One table serves as the queue from which multiple users simultaneously dequeue items one at a time, by flagging the item's row. The other table is joined and used for ordering. It is required that no two users will get the same item from the queue and the queue will not block users, so if user a is busy getting the top item, user b should be able to get the next available item.

The problem is, and i can reproduce, simultaneous dequeue will result in exactly one user to succeed; the others are left empty handed. This happens even when there are multiple candidate items in the queue, so the queue is not empty.

If i remove the order by clause, everything works fine, users can simultaneously dequeue, without blocking each other.

How can i solve the problem, i need the ordering and i need the queue to be non-blocking.

Following are the two tables:

CREATE TABLE DBO.QUEUE
(
  QUEUEID    INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
  SOMEACTION VARCHAR(100)
);
GO

DECLARE  @counter INT
SELECT   @counter = 1
WHILE (@counter <= 10)
BEGIN
    INSERT INTO DBO.QUEUE (SOMEACTION)
    SELECT 'some action ' + CAST(@counter AS VARCHAR);
    SELECT @counter = @counter + 1
END

CREATE TABLE DBO.QUEUE_ORDERING
(
    QUEUEID    INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    ORDERING_COLUMN INT
);
GO

DECLARE  @counter INT
SELECT   @counter = 1
WHILE (@counter <= 10)
BEGIN
    INSERT INTO DBO.QUEUE_ORDERING (ORDERING_COLUMN)
    SELECT @counter;
    SELECT @counter = @counter + 1
END

And the testing script is as follows , running them from multiple sessions will reproduce my problem:

BEGIN TRAN TRAN1
DECLARE @QUEUEID INT;
;WITH QUEUE_CTE AS
(
    SELECT   TOP(1) Q.QUEUEID, Q.SOMEACTION
    FROM     DBO.QUEUE Q WITH (ROWLOCK,READPAST, UPDLOCK)
    JOIN     DBO.QUEUE_ORDERING QR WITH (NOLOCK)
    ON       QR.QUEUEID = Q.QUEUEID
    ORDER BY QR.ORDERING_COLUMN DESC
)

SELECT @QUEUEID = QUEUEID FROM QUEUE_CTE
PRINT 'processing item # ' + CAST(@QUEUEID AS VARCHAR)

WAITFOR DELAY '00:00:05'
COMMIT

Edit:

I've traced down the issue to indices. If i order by the column that is part of the clustered index, then only the relevant row is locked. Otherwise, all rows are locked. I still don't have any idea how to resolve the issue. I need the ordering to be on a column that can not be part of the clustered index. The first image shows the problematic one, you can see that all the rows are locked. In the second one, below, only one row gets locked; order by is on the clustered index column.

It seems that preventing the locking of candidate rows is impossible with an ordering on a column not part of the clustered index.
I came up with a workaround, that seems to solve the problem on the minimal example i provided. I'll do some tests to make sure if it solves the actual production problem.
The idea is to split dequeue operation into two steps. First step respects the ordering and selects a small candidate set; second step ignores any orderings and works on this small set. Thus preventing locking of all the rows.

Select more than one rows to be dequeued, without removing the original order by clause. In my case, i changed the CTE to select top(50) rows. And also changed (ROWLOCK, READPAST, UPDLOCK) to read (NOLOCK) as i don't intend to dequeue all these rows, these are all just candidates for the one row i am willing to dqueue. These rows are stored in a table variable, to be joined in the second step, which follows below.

Create a new (second) CTE, this time joining with the table variable (which contains at most 50 rows), where clauses will be repeated here to prevent an unwanted row appearing in the result set. The ordering this time is removed from the new CTE to prevent the locking.

Although not critical for my specific needs, there are a couple of issues with this approach:

If more than 50 users simultaneously try to dequeue, only the lucky 50 of them will succeed, while others will not be able to get an item.
Granularity of the ordering is lost, each item in the candidate list of 50 are treated equally.
Again on the ordering, it might be the case that a user dequeues an item which was not in the top 50, but this won't be a totally off target miss; it will be close to the top 50. This is due to the two step approach: things might change and new items might arrive that have higher priority, from the time first CTE runs until the second CTE which performs the actual update. Anyways, it is highly likely that the item dequeued is within the first 50 items.

Test script for the new approach is as follows:
BEGIN TRAN TRAN1
DECLARE @QUEUEID INT;

DECLARE @CANDIDATE_TABLE TABLE (
    QUEUEID INT NOT NULL
);

;WITH CANDIDATE_CTE AS
(
    SELECT   TOP(50) Q.QUEUEID
    FROM     DBO.QUEUE Q WITH (NOLOCK)
    JOIN     DBO.QUEUE_ORDERING QR WITH (NOLOCK)
    ON       QR.QUEUEID = Q.QUEUEID
    ORDER BY QR.ORDERING_COLUMN DESC
)
INSERT INTO @CANDIDATE_TABLE (QUEUEID)
SELECT QUEUEID FROM CANDIDATE_CTE;

;WITH QUEUE_CTE AS
(
    SELECT   TOP(1) Q.QUEUEID
    FROM     DBO.QUEUE Q WITH (ROWLOCK, READPAST, UPDLOCK)
    JOIN     @CANDIDATE_TABLE CT
    ON       CT.QUEUEID = Q.QUEUEID
)
SELECT @QUEUEID = QUEUEID FROM QUEUE_CTE
PRINT 'processing item # ' + CAST(@QUEUEID AS VARCHAR)

WAITFOR DELAY '00:00:05'
COMMIT

The outputs of two simultaneous sessions are:

(10 row(s) affected)
processing item # 10

(10 row(s) affected)
processing item # 9


What's the low-down on MariaDB JSON support? Does MariaDB ever plan to have full JSON support or is it just going to be an alias forever?

It's a bummer to set constraints definition in the table just to make it work properly. Application-level migration libraries (like Phinx) doesn't support it so you have to do extra work which defeats the purpose of migration libraries.

No plans that I know about.

Regarding constraints, I assume you mean for JSON validation: See Changes and Improvements in MariaDB 10.4:

  The JSON_VALID function is automatically used as a CHECK constraint
  for the JSON data type alias in order to ensure that a valid json
  document is inserted (MDEV-13916)

... so you don't have to set the constraint explicitly.


PostgreSQL - Create view with autoincremental column I have a PostgreSQL table, and I need to create a view with a new column. This column needs to be an auto-incremental column starting at 1 and going to N.

Is this possible to do without effecting the original schema of the legacy data structure?

You can use row_number, and the easiest way is to just add a

row_number() OVER (PARTITION BY true)

field into the view. You need the PARTITION BY and using the "true" expression is the most performant way (no need for sorting like in Fabrizio Mazzoni's answer).


Cannot SUM datetime field in SQL Server migrated from Access I am new here and am new to SQL Server (Express). I used the SSMA tool and it converted Access date/time fields to datatime2. The data contains duration data so there is no need for a date. I want to SUM the column but I need to change to a Time or numeric data type due to 8117# errors when I SUM. SQL Server will not allow a conversion using the designer / ALTER commands as there are always implicit/explicit conversion errors. Thank you for any possible solutions you may have to offer.

Presumably your new column has values which are a certain datetime relative to 1-Jan-1900, or some other date.

...in which case, you could rename your column, and create a calculated column with your original column name, which is the number of second (or minutes or whatever granularity you want), so that you can SUM that instead.

Example:

SELECT t = DATEADD(SECOND, SUM(DATEDIFF(SECOND, '19000101', myTimeColumn)), '19000101')
FROM dbo.someTable;


Pass data from a table row into a create login statement using dynamicSQL I have a lab exercise that involves creating a script that uses dynamic SQL and a cursor to loop through each row of a table and then create a loginID and more.

We've done NO work concerning cursors/dynamicSQL because our instructor said we'll never need it. I don't think I'd have any problems with an action query within a cursor, but I'm stuck on how to pass data from a table row into a CREATE LOGIN statement, within a cursor, using a dynamicSQL string.

Should the cursor be nested inside the DynamicSQL code? Vice versa? I haven't attempted to run it as it's not finished, but I think I'm on the wrong track anyway. Can anyone help? Thanks.

Here are the lab instructions and my (incomplete) code so far.

LAB Instructions:

Write a script that uses dynamic SQL and a cursor to loop
through each row of the Administrators table and

(1) create a login ID for each row that consists of the administrator’s first
and last name with no space between;

(2) set a temporary password of “temp” for each login;

(3) set the default database for the login to the MyGuitarShop database;

(4) create a user for the login with the same name as the login; and

(4) assign the user to the OrderEntry role you created in Exercise 1

My Code:

-- Create Dynamic SQL
DECLARE @DynamicSQL varchar (max)

-- Cursor variables
-- Should "SET @DynamicSQL =" go before the cursor variables?
DECLARE @FirstName varchar(255), @LastName varchar(255),
        @updateCount int;  -- needed?

-- Create cursor, select table rows through which to loop
DECLARE Admin_Cursor CURSOR FOR
    SELECT FirstName, LastName  -- Data needed from rows?
    FROM Administrators         -- Table with needed rows
OPEN Admin_Cursor;              -- Open the cursor

-- Get values from first row and store in cursor variables (FirstName/LastName)
FETCH NEXT FROM Admin_Cursor INTO @FirstName, @LastName;

-- set loop condition/begin loop
WHILE (@@FETCH_STATUS = 0)
    BEGIN
        SET @DynamicSQL = (CREATE LOGIN 'FirstName' + 'LastName' -- this is where Im lost
    -- some code
    -- some code

     -- begin next iteration of loop
    FETCH NEXT FROM Admin_Cursor INTO @FirstName, @LastName;
    END;

CLOSE Admin_Cursor;         -- Close the cursor
DEALLOCATE Admin_Cursor;    -- Erase from database?
EXEC (@DynamicSQL);         -- Execute the dynamicSQL string

Thanks to anyone willing and able to help.

I understand that the exercise is to use a cursor and dynamic SQL, but just to give you another view (and something to take beyond this one class I suppose), you can do a lot of administrative tasks like this without cursors. I feel that when you default to cursors for tasks where you do need to process things iteratively in a loop, you tend to then rely on them for tasks where you don't need a loop at all. So almost always better to think about what you need to do to a set of rows, rather than to each row. In this case, we need to execute the same set of commands against a set of users. Borrowing from the other answer:

DECLARE  @Administrators TABLE(firstname varchar(255),
                               lastname varchar(255));

INSERT INTO @Administrators VALUES('John','Wick'),
                                  ('Frodo','Baggins'),
                                  ('Gandalf','The Grey');

DECLARE @sql nvarchar(max) = N'';

;WITH u AS (SELECT u = QUOTENAME(firstname + lastname) FROM @Administrators)
SELECT @sql += CHAR(13) + CHAR(10)
  + N'CREATE LOGIN ' + u + N' WITH PASSWORD = ''temp'', CHECK_POLICY = OFF;
       ALTER LOGIN ' + u + N' WITH DEFAULT_DATABASE = MyGuitarShop;
       CREATE USER ' + u + N' FROM LOGIN ' + u + N';
       ALTER ROLE  OrderEntry ADD MEMBER ' + u + N';'
FROM u;

PRINT @sql;
--EXEC MyGuitarShop.sys.sp_executesql @sql;

Result:

CREATE LOGIN [JohnWick] WITH PASSWORD = 'temp', CHECK_POLICY = OFF;
  ALTER LOGIN [JohnWick] WITH DEFAULT_DATABASE = MyGuitarShop;
  CREATE USER [JohnWick] FROM LOGIN [JohnWick];
  ALTER ROLE OrderEntry ADD MEMBER [JohnWick];
CREATE LOGIN [FrodoBaggins] WITH PASSWORD = 'temp', CHECK_POLICY = OFF;
  ALTER LOGIN [FrodoBaggins] WITH DEFAULT_DATABASE = MyGuitarShop;
  CREATE USER [FrodoBaggins] FROM LOGIN [FrodoBaggins];
  ALTER ROLE OrderEntry ADD MEMBER [FrodoBaggins];
CREATE LOGIN [GandalfThe Grey] WITH PASSWORD = 'temp', CHECK_POLICY = OFF;
  ALTER LOGIN [GandalfThe Grey] WITH DEFAULT_DATABASE = MyGuitarShop;
  CREATE USER [GandalfThe Grey] FROM LOGIN [GandalfThe Grey];
  ALTER ROLE OrderEntry ADD MEMBER [GandalfThe Grey];


RESTORE VERIFYONLY locks file in Azure Blob Storage I'm using SQL Server 2012 SP2 to backup my transaction logs directly into Azure Blob Storage using the BACKUP TO URL statement.

I am then attemtpting to verify my transaction log as follows:

RESTORE VERIFYONLY FROM  URL = 'https://mystore.blob.core.windows.net/logfile.trn'
WITH CREDENTIAL = 'azurecreds'

The RESTORE VERIFYONLY operation puts a lease on the file in Azure which I can see using Azure Management Studio's blob browser (The last 2 I created without running RESTORE VERIFYONLY).

I can manually break the lease using Azure Management Studio but am I doing something wrong with RESTORE VERIFYONLY that is leaving an active lease on the file?

This seems very similar to:

FIX: Cannot remove backup file after you restore the database from Windows Azure in SQL Server 2014 or SQL Server 2012

From that Microsoft Support article:

  Symptoms

  Assume that you use the RESTORE HEADERONLY or RESTORE FILELISTONLY command to restore a database from Windows Azure Blob storage service in Microsoft SQL Server 2014 or SQL Server 2012. After the command is completed, you cannot remove the backup file.

  Cause

  The issue occurs because there is an infinite lease on the backup file. Some race conditions between internal threads cause the lease to be not released in some cases.

This is marked as first fixed in Cumulative Update 4 for SQL Server 2012 SP2. While the fix does not mention RESTORE VERIFYONLY explicitly, you should definitely check this out before reporting it as a bug.

The latest Service Pack for SQL Server 2012 at the time of writing is SP3, which will also include the fix mentioned above.

Finally, just to document something GregGalloway originally left in a comment on the question, you can remove active leases using a script in the following MSDN article:

Deleting Backup Blob Files with Active Leases


How to make a Root to get access to the MariaDB server, trough a client app like MysqlWorkbench? I am running a brand new Ubuntu 16.04 virtual machine to perform some testes with MariaDB AND Nginx AND PHP7.

When I was installing MariaDB (trough apt-get install mariadb-server) I was not asked to set a password for root user, like it is when we do install MySql-server.

Then I installed MySql Workbench as a client to MariaDB server.

When I try to connect to the db server as a root with no password, I have an error like the picture below:

As you can see in the picture below, the root user has no password, and I have created another user with no password using this syntax: CREATE USER 'ig2'@'localhost' IDENTIFIED BY ''.

So now, when I try to to connect the just created user ig2 with no password I have a warning and a successful connection.

So, I sit a password to the root user with this syntax: SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123'); and now the root has a encrypted password.

If I try to connect again with the root and his password

And I still have the same connection errror for the root user:

If I set the same password for the user ig2, using the same syntax used to sit root's password, I can have a successful connection.

And it is weird when I try to connect trough the terminal:

The root user can connect with or without password.

In resume, I don't know what is happening, and I don't know how to make the root to get access to the server, trough a client application like Workbench.

Does someone can explain me?

The answer to this question should help you out:

$ sudo mysql -u root

[mysql] use mysql;
[mysql] update user set plugin='' where User='root';
[mysql] flush privileges;

In short, the new version of MariaDB checks to see what the system user is and only allows the actual system user root to log in to the DB user root. Disabling all plugins disables that functionality.


High MySQL CPU usage, queries seem fine, not running ntp I've had about 48 hours of extremely high CPU usage on my main MySQL database for no apparent reason.

I realize this question is quite similar to the one posed here: High CPU usage from MySQL with no queries at all running

However, this MySQL server does not have ntp installed at all, and as the leap-second bug is from a few months ago, I don't think that's the explanation.

SHOW PROCESSLIST does not show any long-running processes/queries, and there have been no recent changes to DB schema, etc.

The server is also extremely locked down so that only other application servers within the VPN may access it, and has plenty of RAM (17 GB) which is not maxed out either.

Is there anything else that I should look into, or any other common causes of high CPU?

The server is an EC2 m2.xlarge instance: 17.1 GB RAM, 6.5 EC2 "Compute Units" with this configuration:

user        = mysql
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
skip-external-locking

key_buffer      = 256M
sort_buffer_size    = 16M
read_buffer_size    = 16M
max_allowed_packet  = 32M
thread_stack        = 192K
thread_cache_size       = 8

max_connections        = 200
thread_concurrency     = 12

query_cache_limit   = 4M
query_cache_size        = 128M

log_error                = /var/log/mysql/error.log

log_slow_queries    = /var/log/mysql/mysql-slow.log
long_query_time = 2
log-queries-not-using-indexes

server-id       = 1
log_bin         = /var/lib/mysql/mysql-bin.index
expire_logs_days    = 10
max_binlog_size         = 1000M
sync_binlog     = 1

innodb_data_file_path   = ibdata1:2000M;ibdata2:10M:autoextend
innodb_buffer_pool_size = 11G
innodb_additional_mem_pool_size = 128M
innodb_log_file_size    = 1000M
innodb_log_buffer_size  = 32M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

You can have a high load from lots of short queries as well.  Try setting your long_query_time to 0, and force all clients to reconnect.  Let that run for a few minutes and then set it back to your default.  You can peruse the results by hand or use a tool like pt-query-digest.

In either case, you should be able to figure out if there is in fact a bunch going on, just very quickly.


SQL Server Full-Text Index crawl: Is it done or not? I am on SQL Server 2012. I have one table with one full-text index. That index was dropped and recreated around 8am due to a change in the datatype of the indexed column from TEXT to VARCHAR(MAX). These are the relevant entries in the log:

  2017-01-06 08:02:52.65 spid25s     Informational: Full-text Full
  population initialized for table or indexed view
  '[Helpdesk].[dbo].[TicketPosts]' (table or indexed view ID
  '581577110', database ID '8'). Population sub-tasks: 4.

  2017-01-06 10:56:47.98 spid9s      Informational: Full-text Full
  population completed for table or indexed view
  '[Helpdesk].[dbo].[TicketPosts]' (table or indexed view ID
  '581577110', database ID '8'). Number of documents processed: 7016145.
  Number of documents failed: 0. Number of documents that will be
  retried: 0.

  2017-01-06 11:24:42.11 spid20s     Informational: Full-text Auto
  population initialized for table or indexed view
  '[Helpdesk].[dbo].[TicketPosts]' (table or indexed view ID
  '581577110', database ID '8'). Population sub-tasks: 1.

  2017-01-06 11:24:43.12 spid9s      Informational: Full-text Auto
  population completed for table or indexed view
  '[Helpdesk].[dbo].[TicketPosts]' (table or indexed view ID
  '581577110', database ID '8'). Number of documents processed: 0.
  Number of documents failed: 0. Number of documents that will be
  retried: 0.

Between 11am and 11:24am, the TableFulltextItemCount OBJECTPROPERTY and the TableFulltextDocsProcessed OBJECTPROPERTY were equal (and were the same as the documents processed shown in the log for the first crawl).

However, during that time the TableFulltextPopulateStatus OBJECTPROPERTY returned 1 (Full Population In Progress), sys.dm_fts_index_population.completion_type_description was 'NONE', and sys.fulltext_indexes.crawl_end_date was null.

Why did the DMVs think that the crawl was still in progress long after the log says it was done and the processed count equals the item count? What was it doing during that time?

No change was made to the data between the first and second crawl, so why did the second crawl occur?

Since you have Change Tracking = AUTO configured you are telling the SQL Server to check on the changes that being made in the FULL TEXT INDEX. That means that the process is still running at a low level.

I would not be at all surprised to see some activity as the Full Text Indexing service is checking for changes.

Populate Full-Text Indexes for SQL Server 2012

A similar question was asked here back in 2013:

Full Text Search Populate Status - Processing notifications

Quoting from Frank J Garcia's answer:

  There is a small overhead associated with change tracking because SQL Server maintains a table in which it tracks changes to the base table since the last population. When change tracking is used, SQL Server maintains a record of the rows in the base table or indexed view that have been modified by updates, deletes, or inserts.


How to change the default isolation level of a transaction in a stored procedure in PostgreSQL PL/pgSQL? I am interested in controlling the isolation level of a transaction inside a PostgreSQL procedure but haven't found any information on how to do it. The documentation on Transaction Management within Chapter 42. PL/pgSQL — SQL Procedural Language only states that:

A new transaction starts out with default transaction characteristics such as transaction isolation level.

Can we change the default isolation level? Can you provide example code?

That should be simple:
ALTER PROCEDURE myproc
   SET default_transaction_isolation = 'repeatable read';

The downside to that is that because of the limitations of transaction management inside procedures, you can no longer COMMIT or ROLLBACK inside such a procedure.
If you need that, you could start the procedure body like this:
COMMIT;  -- otherwise you get a silly error
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Transaction management inside procedures is pretty limited at the moment.


Elevation data in a database (tens of billions of information) I have a need of a service which gives me the elevation for a coordinate (latitude, longitude). There is a dataset called SRTM3 where you have this information in a list of files. Each file contains a representation of the elevation value in a 1201*1201 square and each elevation value is stored as a big endian integer. There are around 20 000 files in this dataset.

For example, for the latitude 50.4161 and longitude 14.12345, you would find the data inside one of these 1201*1201 values of the file N50E014.hgt.

My question is this : if I were to import all these data in a table, I would have around 20 000 * 1201 * 1201 = 28 848 020 000 records.

Is it the right way to go in storing this in a database? If yes, what system would you recommend? The only request I would have to make is something like:

SELECT elevation
FROM elevation e
WHERE e.min_latitude < latitude
AND e.max_latitude > latitude
AND e.min_longitude < longitude
AND e.max_longitude > longitude`

Or should I just load the data from the raw file directly on request? But it means loading a file on each request (I could keep some of the most used in memory but it does not seem efficient).

EDIT: I was thinking about something more around the line of a task queue than opening the file on each request but let's forget this and keep our focus on database system. @Paparrazi said that a conventional database can handle this much data and advised with a cluster index. I thought of that previously but in this case, once the data are loaded, the only operation will be a read (never or almost never a write). It seems kind of useless to use these conventional databases with lot of feature like transactions, ... when you don't need it. Is there another alternative ? In bigdata oriented database (cassandra, mongo, ...) ?

EDIT2: I am looking into @MickyT answer with postgis. It seems to fit my needs. I will try to import the data into the index clustered table or the postgis raster one, benchmark the 2 solutions and post my result here.

PostgreSQL with the PostGIS extension would appear to have everything you need to manage this data.

PostGIS has a fairly rich set of functions that deal with rasters like the files that you have described.  Further documentation on enabling and using rasters is here.

Once you have the rasters loaded a query like the following should perform reasonably well

SELECT p.Identifier, ST_Value(r.rast, p.geog) AS elevation
FROM query_point p
    INNER JOIN elevation_raster r ON ST_Intersects(r.rast, p.geog);


What is the difference between these extracting date method? Assuming I have a table called MyTable, with a TimeStamp field "Created". I want only extract the Date part of this column, and in my research I found these alternatives:
SELECT created AS "Original",
       date(created) AS "DateFunction",
       created::date AS "DoubleColonCast",
       cast(created as date) AS "CastFunction",
       date_trunc('day', created) AS "DateTrunc"
FROM MyTable

In matter of SARGability, what is the preferred method?
What is the "SQL ANSI" (ie more portable) way?
What is the most common/used in Postgres?

As others have pointed out date(created), created::date and cast(created as date) are functionally equivalent.
If you want to be portable, use cast(). I think the :: operator is more common in "Postgres land".
However, date_trunc('day', created) is not equivalent to the other expressions, because it returns a timestamp value, not a date.
Neither of those expressions will make use of an index on created - you would need to create an expression based index with the expression used in your queries. The cast() expression will be rewritten to :: by the query optimizer, while date(...) will not. So if you create an expression based index, using cast() or :: won't make a difference between the indexed expression and the one used in the query.
But if you use date(..) or date_trunc() you will have to use the same expression in your queries to make use of the indx.


Query Notification setup I am relatively new at playing around with this and have been trying to follow a tutorial on setting up in the section named "Prerequisites to Create SQL Notification Requests" within this site http://www.codeproject.com/Articles/144344/Query-Notification-using-SqlDependency-and-SqlCach However I am not sure what I should be putting in :://the service and there were no instructions or information on the site regarding this.  I was wondering what I should do or what other information I need to complete this section.

The service specification is the name of the service used for query notifications.  The tutorial is focused on SqlDependency, which creates dynamically-named services (GUID appended).  No need to explicitly grant SEND permissions on the service in that case because the creator of the service will have CONTROL permissions on the service anyway.

If you were to use the lower-level SqlNotificationRequest object, then you would need to grant SEND on the service, REFERENCES on the contracts, and RECEIVE on the queues.  That allows a less privileged account to subscribe to query notifications.


TempInsertStateItemShort procedure on ASPState is taking more than 1 second to execute I'm working on a .NET website who runs in load balance within more than one server and because of that I need to have my session on SQL Server.

In the last 2 weeks I've notice that the procedure TempInsertStateItemShort is taking more than 1 second to run.

Basically this procedure inserts into tempdb.dbo.ASPStateTempSessions

 CREATE PROCEDURE dbo.TempInsertStateItemShort
            @id         tSessionId,
            @itemShort  tSessionItemShort,
            @timeout    int
        AS
            DECLARE @now AS datetime
            DECLARE @nowLocal AS datetime

            SET @now = GETUTCDATE()
            SET @nowLocal = GETDATE()

            INSERT [tempdb].dbo.ASPStateTempSessions
                (SessionId,
                 SessionItemShort,
                 Timeout,
                 Expires,
                 Locked,
                 LockDate,
                 LockDateLocal,
                 LockCookie)
            VALUES
                (@id,
                 @itemShort,
                 @timeout,
                 DATEADD(n, @timeout, @now),
                 0,
                 @now,
                 @nowLocal,
                 1)
            RETURN 0

tempdb.dbo.ASPStateTempSessions table has 800k rows and the PK index is about 98% fragmented.

Does the fragmentation be the reason for insert take more than 1 second?
Does anyone had experience that?
How can I improve performance on this table?

Thanks in advance!!!!

As stated by Max Vernon:

  800,000 rows seems to indicate the site has 800,000 sessions active (or at least active in the last 20 minutes - which is the default session timeout). Is that in line with the actual traffic you have? If not, I'd say the website does not have correct rights to the ASPState database.

The fragmentation would be "normal" for a table where the PK is a randomly generated GUID, as in this case.

I've note that ASPStateTempSessions was not being clean up expired sessions.

There is a job to clean up expired sessions ASPState_Job_DeleteExpiredSessions that was disable. I just enabled it and things are back to normal.

one more thing, just a good link I just found that helps to optimize ASPState.
link