Adaptive compression and HADR I'm looking into adaptive compression. I can't seem to find any information on how HADR is affected when a table is compressed and then reorg:ed. Example

t=mytable
db2 "alter table $t compress yes adaptive";
for i in $(db2 -x "select rtrim(indschema) || '.' || rtrim(indname) from syscat.indexes where rtrim(tabschema) || '.' || rtrim(tabname) = '${t}' order by 1"); do
    db2 "alter index $i compress yes"
done

db2 "reorg table $t resetdictionary";
db2 "reorg indexes all for table $t"
db2 "runstats on table $t with distribution and sampled detailed indexes all";

Question is whether I need to bring down HADR during this process and restart it once finished, or is everything captured in the log, so that HADR can continue?

Both online and offline reorganization are logged and therefore replicated by HADR. By stopping HADR you will simply allow the standby to fall behind the primary, which will cause a large spike in traffic (and potential log space issues on the standby) when you restart HADR. If you don't suffer from network congestion during normal operation, there's no reason to stop HADR during reorganization. If you do, consider dropping the the standby and rebuilding it anew after the REORGs on the primary are complete.


Group and count user notifications i have a notifications table like this:

i want to select all user 1 rows, but take only one row from rows with same parent id, and also have the count of those rows.

so i can show to user: 'jack and 3 more users have liked your post'.

many thanks

This should come close to meeting your need.

This assumes you want the rel_user_id from the most recently added row for a given user_id and parent_id, and that the row with the highest id should be the one most recently entered. If you want the rel_user_id with the highest value instead, .you can basically just use the agg subquery (changing MAX(ID) to MAX(rel_user_id).

Also, I didn't worrying about the join to the posts table - I'm guessing that parent_id is the foreign key column, and you've got that; you should be able to get there from here.

Here's the query:

SELECT ntf.user_id
      ,ntf.parent_id
      ,ntf.rel_user_id
      ,agg.notification_count
  FROM (SELECT user_id, parent_id, COUNT(*) as notification_count, MAX(id) as max_id
          FROM notifications
         WHERE user_id = 1
         GROUP BY user_id, parent_id
       ) agg
         INNER JOIN notifications ntf ON (    agg.user_id = ntf.user_id
                                          AND agg.parent_id = ntf.parent_id
                                          AND agg.max_id = ntf.id
                                         )
 ORDER BY user_id, parent_id
;

The agg subquery gets us one row for each user_id and parent_id; we'll put the WHERE clause specifying which user_id you want here, to limit how many records we're grouping. This gets us the count of rows for the user_id and parent_id combo, and gets use the "most recent" id.

We then tie back to notifications with an INNER JOIN to get the rel_user_id from the indicated id row.

Here's a db-fiddle.com link showing the query in action, with the sample data you provided.


How to query an Extended Events target file on a SQL Azure Managed Instance by using T-SQL? I'm setting up an Extended Events trace in a Managed Instance (NOT Azure SQL DB) for the first time.
These are the steps I've carefully followed:

Created a Master key on my Database.
Created a Database Scope Credential by using the correct Shared Access Signature.
Created and started a trace. This works and everything gets traced as expected.

Now, I'm trying to query the XEL file by using T-SQL:
SELECT event_data FROM sys.fn_xe_file_target_read_file (N'https://whatever.blob.core.windows.net/xevents/MyTrace_132410674100570000.xel',NULL, NULL, NULL);

However, I get two different behaviors:

From SSMS: I just get zero rows and no error or messages displayed.

From Azure DataStudio: I get the following error message

Msg 300, Level 14, State 1, Line 1 VIEW SERVER STATE permission was
denied on object 'server', database 'master'. Msg 297, Level 16, State
1, Line 1 The user does not have permission to perform this action.

Some facts:

I'm able to download the XEL file by using Azure Storage Explorer.
I have already checked the full path to the XEL file and it's correct. I can download the files directly by using a browser.
The access level of the container is Public.
I've tried with different files (running and stopped traces, all of them with data).
The XEL files are full of data since I query them locally and I can open them by using SSMS.
I really don't think there's a missing key or scoped credential. Otherwise, the tracing would not work. Anyway, I dropped everything and started again from scratch and the behavior did not change.
All my tests were carried out by using the managed instance administrator account.

So...

Is reading XEL files really supported on Managed Instances?
Is there any cath on how to access the files by using the sys.fn_xe_file_target_read_file function?

I don't know if this is the right solution, but here's is the workaround that worked for me, in case someone is facing the same issue.

I created a small Azure SQL Database (S1).
I created a master key for this database.
I created a scoped credential pointing to the Azub Blob where the XEL files from the Managed Instance reside in (using the same Shared Signature)

I was able to query by using
SELECT event_data FROM sys.fn_xe_file_target_read_file (N'https://whatever.blob.core.windows.net/xevents/MyTrace_132410674100570000.xel',NULL, NULL, NULL);

It seems like Managed Instance does not support the use of this function (or I wasn't able to find the way to do it).
Hope this helps someone.


Recovering a dropped table in PostgreSQL I have :

CREATE TABLE ketoan_vn.Customers
(
  CustomerId character varying(10) NOT NULL,
  CustomerName character varying(150),
  CONSTRAINT Customers_PK_CustomerID PRIMARY KEY (CustomerId)
);

I inserted 2 rows into table ketoan_vn.Customers:

INSERT INTO ketoan_vn.customers(
            customerid, customername)
    VALUES ('C0001','Marry'),
    ('C0002','Tom');

I then dropped ketoan_vn.Customers :

drop table ketoan_vn.Customers

Now I want to recover the data of the dropped table.  Is this possible with PostgreSQL 9.1?

You can't, the file has been physically deleted from the disk. I'm assuming your real question is about a non-trivial table you care about, not a toy-table you can re-create at will.

If so: Restore from a backup.

If you don't have a backup, shut the computer down now and contact a professional data recovery team to see if they can recover the table from the empty space in the file system if it hasn't been over-written yet. Expect to pay several thousand dollars for the service.

Since the disk isn't phsysically damaged you can try some recovery yourself: Take a disk image of the drive by plugging it into another machine. Do not take a file-system-level copy, you need a raw image of the volume/partition the file system is on. You can then attempt to locate the table in the disk image, but be aware that it's likely fragmented and that the inode information that would identify its location is typically gone. Details would require knowledge of the operating system and file system the database is on.

More on this topic here.

Demo

Run your setup:

regress=# CREATE SCHEMA ketoan_vn;
CREATE SCHEMA
regress=# CREATE TABLE ketoan_vn.Customers
regress-# (
regress(#   CustomerId character varying(10) NOT NULL,
regress(#   CustomerName character varying(150),
regress(#   CONSTRAINT Customers_PK_CustomerID PRIMARY KEY (CustomerId)
regress(# );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "customers_pk_customerid" for table "customers"
CREATE TABLE
regress=# INSERT INTO ketoan_vn.customers(
regress(#             customerid, customername)
regress-#     VALUES ('C0001','Marry'),
regress-#     ('C0002','Tom');
INSERT 0 2

Get the location of the table file on disk:

regress=# SELECT
  COALESCE(
    pg_catalog.pg_tablespace_location(pg_tablespace.oid),
    current_setting('data_directory')||'/base/'
  )
  || (SELECT oid from pg_database where datname = current_database() )
  || '/'
  || relfilenode
  AS path_to_table_file
FROM pg_class
INNER JOIN pg_namespace ON (pg_class.relnamespace = pg_namespace.oid)
LEFT OUTER JOIN pg_tablespace on (reltablespace = pg_tablespace.oid)
WHERE nspname = 'ketoan_vn' AND relname = 'customers';

          path_to_table_file
------------------------------------------
 /var/lib/pgsql/9.2/data/base/43720/43733
(1 row)

Confirm the location on the shell:

$ sudo ls -l /var/lib/pgsql/9.2/data/base/43720/43733
-rw-------. 1 postgres postgres 8192 Apr 18 14:25 /var/lib/pgsql/9.2/data/base/43720/43733

Now back in psql, drop the table:

drop table ketoan_vn.Customers;

and confirm it's now either gone or zero length:

$ sudo ls -l /var/lib/pgsql/9.2/data/base/43720/43733
-rw-------. 1 postgres postgres 0 Apr 18 14:28 /var/lib/pgsql/9.2/data/base/43720/43733

(the actual file will be removed by the DB at some later time, but it's been truncated, or it'll get re-used. Either is very bad for your data.)


What is a good practice for storing unstructured tables as a field in mongodb Context:

I am developing an application that requires handling tables that do not necessarily have an uniform structure (eg. different/extra columns). Those unstructured files also need to be grouped (say 5 table together makes a group, each table has a different content). And each file in the group needs to be versioned preferably in a simple and efficient way (eg. no need to copy the entire file if only a row is changed).

Current solution I thought of:

Define a schema with some standard fields that application needs, such as name of a file group, ..., timestamps, path_to_repository, hash_of_repository_content (to invalidate), status (eg. being_updated, for concurrency) }
At path_to_repository, I'll have a git repository and keep files in csv format (create a submodule for each file so that each file in a file group can be versioned independently).
In order to access those files, I'll get the repository and head will be the current version of the files. Then I can do the versioning easily, can keep tables of unlimited size without any structure and wouldnt be limited by mongodb's max file size. If there are any further information to be versioned I can simply add them to the repository.
To handle concurrency, I'll update a flag in the mongodb, so it would take care of the concurrency for me. If two applications simultaneously try to write into repository, they'd set a flag/lock first. if flag is already updated by someone else, they need to wait.

Question:

I am not sure is this is a good practice (whether I'm re-inventing the wheel or not, would it have a good performance, can this scale, if there is any problems I dont see). Any advice/experience/criticism would be great.

Probably irrelevant extra information:

this is going to be used by nodejs/mongoose and also by a python script.

The decision about what to put in the document is pretty much determined by how the data is used by the application.

The data that is used together as users documents is a good candidate to be pre-joined or embedded.

One of the limitations of this approach is the size of the document. It should be a maximum of 16 MB.

Another approach is to split data between multiple collections.

One of the limitations of this approach is that there is no constraint in MongoDB, so there are no foreign key constraints as well.

The database does not guarantee consistency of the data. Is it up to you as a programmer to take care that your data has no orphans.

Data from multiple collections could be joined by applying the lookup operator. But, a collection is a separate file on disk, so seeking on multiple collections means seeking from multiple files, and that is, as you are probably guessing, slow.

Generally speaking, embedded data is the preferable approach.


problem running xp_cmdshell when Application Name set in connection string I have an app on 10 or so servers that runs a few xp_cmdshell statements against MSSQL 2008. It works fine on all the servers except for one. To make things worse, I can run all the commands in SQL Management Studio, but in the app, they don't work. I even made a temporary app to test and it works fine! But in the deployed app, I get a simple SQL error "Access Denied". I have narrowed it down to the connection string, if I include the application name

Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3};Application Name=TheGroovyApp

It throws access denied only when calling xp_cmdshell, normal SQL statements works fine. But if I remove the application name

Data Source={0};Initial Catalog={1};Persist Security Info=True;User ID={2};Password={3}

It works fine for both normal SQL statements and calls to xp_cmdshell. The strange thing is, its only happening on one of the ten servers. The only difference being that the server has SP1 and the others do not.

@@VERSION returns

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (VM)

I am thinking that there is some sort of authentication that can be granted to applications, but I cant seem to find anything. I can replicate it in SQL Managment Studio by adding

Application Name=TheGroovyApp

to the Additional Connection Parameters tab on the Connect to Database Engine dialog when you create a new query or change its connection.

The simple test statement I use is

EXEC master..xp_cmdshell 'DIR F:\SomeDirectory'

If anyone could shed some light on what is happening it would be much appreciated.

EDIT:

OK after a bit more investigation its even more confusing.

If I set the Application Name to the following which is the default for .Net connections, it works fine.

Application Name=".Net SqlClient Data Provider"

I can run xp_subdirs without any problems no matter which settings I use

EXEC master..xp_subdirs 'F:\SomeDirectory'

Now this is where it gets really weird. The first two fail, but the last one succeeds with the application name being set to my application name. But only if its xp_cmdshell that get called, xp_subdirs works with all three.

With Application Name in connection set

EXEC master..xp_cmdshell 'DIR F:\SomeDirectory' - Fails
master..xp_cmdshell 'DIR F:\SomeDirectory' - Fails
xp_cmdshell 'DIR F:\SomeDirectory' - Works
EXEC master..xp_subdirs 'F:\SomeDirectory' - Works
master..xp_subdirs 'F:\SomeDirectory' - Works
xp_subdirs 'F:\SomeDirectory' - Works

With Application Name not set in connection

EXEC master..xp_cmdshell 'DIR F:\SomeDirectory' - Works
master..xp_cmdshell 'DIR F:\SomeDirectory' - Works
xp_cmdshell 'DIR F:\SomeDirectory' - Works
EXEC master..xp_subdirs 'F:\SomeDirectory' - Works
master..xp_subdirs 'F:\SomeDirectory' - Works
xp_subdirs 'F:\SomeDirectory' - Works

The error returned in the query messages area in SQLMS when it fails

Msg 10011, Level 16, State 1, Line 1
Access denied.

This error message is only happening on the one server, I cannot replicate this on any others.

the database(s) associated with the application should be trustworthy in order to access the machine's command line.

Try

select name,is_trustworthy_on from sys.databases;

if your target database is not trustworthy then:

alter database db_name set trustworthy on;

but beware the compromise to security


Script execution order I did Generate Script on a database and got over 1700 scripts. I've sorted them into folders below:

Each folder contains .sql files of that type, for example 06 UserDefinedFunction contains many .UserDefinedFunction.sql files.
I'm going to make a batch script that goes to each folder and runs sqlcmd on every file in that folder.
Does the execution order matter here, and do I have the right order?

The execution order absolutely matters, and you shouldn't change it from what was generated. It is likely you have dependencies in one script on objects that need to be created first from another script. For example, if one of your Stored Procedures use a View then it will fail upon generation because it can't find the View it references. This is why generating scripts puts them in order by dependency instead of object type.
Only in the very rare case where all your scripts don't have dependencies on each other (or your re-ordering is coincendentally in order of dependency still), then you'd be ok.


Shard keys must all be ascending? I have a collection that I've been trying to shard, but I can't get it to shard with the index that I gave it.

The collection in question uses a hashed index, which I created using:

db.bar.ensureIndex({"_id": "hashed"}, {"background": 1})

But when I try to shard said collection, using:

sh.shardCollection("foo.bar", {"_id": "hashed"})

I get an error saying:

command failed: { "ok" : 0, "errmsg" : "shard keys must all be ascending" }

Does this mean that the data I have in collection bar needs to be ascending or that the version of Mongo that I'm using only supports range shard-keys?

Updating to the latest version of mongodb available for ubuntu fixed the issue.  This time, running db.bar.ensureIndex({"_id": "hashed"}, {"background": 1}), gave me:

Inserted 1 record(s) in 97564ms

and when I ran sh.shardCollection("foo.bar", {"_id": "hashed"}), I got:

{
  "collectionsharded": "foo.bar",
  "ok": 1
}

Here's the bash script that I used to update the servers:

#!/bin/bash

# for installing the latest and greatest version of MongoDB on Ubuntu Servers
if [[ "$UID" != 0 ]]; then # if not root, then exit non-zero
  printf "$(basename $0): err: %s\n" "must be root"
  exit 1
fi

apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv 7F0CEB10 # import the MonboDB public GPG key
echo 'deb http://downloads-distro.mongodb.org/repo/ubuntu-upstart dist 10gen' | tee /etc/apt/sources.list.d/mongodb.list # create `/etc/apt/sources.list.d/mongodb.list` containing the ubuntu repo for mongodb.
apt-get update # refresh the apt repos

for each in mongodb-clients mongodb-dev mongodb-server mongodb; do # remove the old version of mongodb
  apt-get remove "$each"
done

apt-get install mongodb-10gen # install mongodb from the newly added repo


Display subquery columns in result I'm new to SQL (also my first question) and have been trying to determine the best practises and most efficient way to code my problem. I'm using mySQLWorkbench on MacOS. I'm trying to get the result of an aggregate query across multiple tables. I need to display the first and last name, team name, earliest and latest instructor for each team. The following query gives me the first and last names of the correct instructors. The comments are what I tried to do, but the WHERE clause can only accept one operand.

SELECT i.firstname ,i.lastname -- , tt.name tm.dateJoined
FROM instructor i --, teachingTeam tt, teamMembership tm
WHERE id IN --and tt.name and tm.dateJoined IN
    (
        SELECT member --, team, dateJoined
        FROM teamMembership AS t
        WHERE datejoined = (
                SELECT min(dateJoined)
                FROM teammembership AS t2
                WHERE t2.team = t.team
                )
            OR dateJoined = (
                SELECT MAX(datejoined)
                FROM teammembership AS t2
                WHERE t2.team = t.team
                )
    );

I have the bulk of the query down I'm just having 2 issues;

Resolving the team ID to the team name (in an efficient way)
I'd like to output the dateJoined from the subquery rather than write an entire other query (seems redundant)

I believe that I can use WHERE EXISTS however I had trouble understanding it

I have a three main tables I need to work with.

One called teamMembership with four columns;

team member dateJoined and dateLeft

teamMembership.team is linked to teachingTeam.teamID,

teamMembership.member is linked to instructor.id

instructor with 5 columns;

id lastName firstName dateOfBirth and email

lastly, teachingTeam with three columns

teamID name and leader

teachingTeam.teamID is linked to teamMembership.team

teachingTeam.leader is linked to instructor.id

So, let's break this down into steps.

First, you need the earliest and latest dateJoined from teamMembership for each team. this can be done with a slight variation on what you already have:

SELECT tt.teamId, tt.name, MIN(tmbr.dateJoined) as dateJoined
  FROM teachingTeam tt
         INNER JOIN teamMembership tmbr ON (tt.teamId = tmbr.team)
 GROUP BY tt.name
UNION
SELECT tt.teamId, tt.name, MAX(tmbr.dateJoined) as dateJoined
  FROM teachingTeam tt
         INNER JOIN teamMembership tmbr ON (tt.teamId = tmbr.team)
 GROUP BY tt.name
;

GROUP BY says to give us the MIN (or MAX) dateJoined for all the rows for each specific team.

UNION says to take the results of the two SELECT queries, and treat them as a single result set. It also gets rid of duplicates, so if (for instance) all the members of one team have the same dateJoined, we'll just list them once. If you really want to have the earliest and the latest dates when they are the same, use UNION ALL instead.

Now, you want to list the instructors who joined each team on the dates indicated. The simplest way to do that is to treat our first result set as if it were a table, and then to join that data to our teamMembership and instructor tables. This is why we included teachingTeam.teamId in our result set above; to facilitate that join.

SELECT djt.name, mbr.dateJoined, i.firstname, i.lastname
  FROM (SELECT tt.teamId, tt.name, MIN(tmbr.dateJoined) as dateJoined
          FROM teachingTeam tt
                 INNER JOIN teamMembership tmbr ON (tt.teamId = tmbr.team)
         GROUP BY tt.name
        UNION
        SELECT tt.teamId, tt.name, MAX(tmbr.dateJoined) as dateJoined
          FROM teachingTeam tt
                 INNER JOIN teamMembership tmbr ON (tt.teamId = tmbr.team)
         GROUP BY tt.name
       ) djt  -- for Date Joined Team
         INNER JOIN teamMembership mbr ON (    djt.teamId = mbr.team
                                           AND djt.dateJoined = mbr.dateJoined)
           INNER JOIN instructor i ON (mbr.member = i.id)
 ORDER BY djt.name, mbr.dateJoined
;

Note - all code is untested.

So, we use the teamId and dateJoined from our original query to find the team members we want, and then use member to connect to the instructor table to pick up their first and last names.


InnoDB: SELECT COUNT(*) using primary key 25 times slower on Windows MySql 5.6, InnoDB. I have a table with 8 million records. It has an integer primary key, and a unique index u1 CHAR(40). It also has a few small fields and a VARCHAR(1000).

SELECT COUNT(*) FROM my_table FORCE INDEX(u1) takes 1.0 s

SELECT COUNT(*) FROM my_table FORCE INDEX(PRIMARY) takes 1.2 s

This is on Linux boxen. But on Windows boxen the times are 2 s and 45 s!

The cnf/ini files are roughly the same. Tweaking buffer sizes etc has been unsuccessful so far. There seems to be a big problem with primary key lookups on Windows. Perhaps a bug in InnoDB on Windows? Or what?

It turns out that the difference was the size of innodb_buffer_pool_size. On the Win box it was too low.

It appears that, if you need the primary key, all the records (8 million times 1000 chars) need to be read. InnoDB seems to use the primary key as a "clustered index", meaning that it's welded to each record. That's why reading just the values of the secondary key takes so much less memory, and thus less use of the memory buffer. If it's too small, the read will take a long time.

Increasing innodb_buffer_pool_size to 8G helped speed up operations.

I certainly don't understand the minute details, please correct me if this is nonsense.


Trigger with a Variable Select and Order by Is there a way to a set trigger that will activate once a new table is created and will get the new tables name?
I was able to come up with the query below but it only gets the first query by alphabetical order, not by date created

CREATE TRIGGER CreateTable
ON DATABASE
    FOR CREATE_TABLE
AS
     DECLARE
       @table varchar(max);
     SELECT  top 1  @table =   [name] FROM sys.tables
     insert into NewTable values(@table)
     ;
     end

You don't look at sys.tables for that... even if you got the last one created, on a busy system, how would you know that someone else didn't create one more recently? With TOP comes ORDER BY so the following would likely work most of the time, but it is a completely bad idea:

SELECT TOP (1) @table = name FROM sys.tables ORDER BY create_date DESC;

No, instead, you need to extract the table name from EVENTDATA():

CREATE TRIGGER CreateTable
ON DATABASE
FOR CREATE_TABLE
AS
BEGIN
    DECLARE
      @sql       nvarchar(MAX),
      @EventData xml = EVENTDATA();

    ;WITH x(s,o)
    AS
    (
      SELECT
        @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',  'nvarchar(128)'),
        @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'nvarchar(128)')
    )
    INSERT dbo.NewTable(ColumnName)
      SELECT QUOTENAME(s) + N'.' + QUOTENAME(o)
      FROM x;
END
GO


Fastest way to alter column type in a 2-billion+ records innodb table I have a MySQL table with 4 columns.  The 1st column record_id is of type int, primary key, and auto-incremented.  Now that I have added more than 2 billion records to this table I am finding that I have exceeded the range of my auto-incremented field. I need to modify record_id to a "bigint" to accommodate more records.  What would be the fastest way to do this?

Is there a way to do this in-situ without dumping the tables?

Take a look here at Percona's online schema change tool (anything from Percona is good - I'd advise you to look at all of the tools while you're at it). You could also try Shlomo Noach's online table change tool - test with both and see which one works for you (again, look at his entire toolkit).


mapping/translation table design for one to one or multiple to one condition for mapping I am trying to come up with a flexible design to allow mapping a code or a combination of codes from one system to a single code in another system for a given type of data.  For instance for group type 'customer level' codes 'abc' and '123' from system 'A' equal code '5' in system 'B'.  Another possible is that group type 'shipping code' code 'xyz' from system 'A' could be a direct mapping to code '99' in system 'B'.  I would like to have a flexible design that allows for the multiple conditions to one value and a one to one value mapping without having to create a new table for every condition if possible.  Is that possible?

I could just create a flat table that has a column for each system's codes and use a delimiter in the multiple code column, but that seems a bit messy.  There will not be hundreds of mappings but probably about 30 or so.  Thank you for any assistance in advance.

May be I'm missing something, but wouldn't simple

create table map (
  group_type varchar(20) not null,
  code_a varchar(20) not null,
  code_b varchar(20) not null
)

do what you want? (group_type, code_a) would be a unique constraint, and the choice of the appropriate index would be dictated by your DBMS abilities.


How to make a FULLTEXT search with ORDER BY fast? I'm trying to get a simple FULLTEXT match to be faster when using order by on another column on a table with over 100 million rows. The basis is one table with a fulltext on two columns and I want to search the database but order it by either the primary (least/most recent) or popularity. Is it possible to make a FULLTEXT with an order by on another indexed column fast? SQL Fiddle below with schema and explains of all queries:

See SQL Fiddle #1

What's very fast so far is denormalization of search columns in a separate table and a join but I would rather not have another table if not necessary. SQL Fiddle below (denormalized query at the end):

See SQL Fiddle #2

The first problem here is something you cannot control. What is it ???

The Query Optimizer's reaction to a FULLTEXT index. Why ?

(Now imagine the start of a STAR WARS movie with scrolling words...)

  A very long time ago, I discovered that FULLTEXT indexes will nullify the MySQL Query Optimizer's use of other indexes

Jun 10, 2011 : Mysql fulltext boolean ignore phrase
Oct 25, 2011 : FULLTEXT index ignored in BOOLEAN MODE with 'number of words' conditional
Mar 18, 2012 : Why is LIKE more than 4x faster than MATCH...AGAINST on a FULLTEXT index in MySQL?
AND MORE LIKE IT !!!

SUGGESTION

Refactor the Query so that the MATCH ...AGAINST collects keys only

EXAMPLE #1

SELECT a.id FROM a
WHERE
MATCH (`search1`,`search2`) AGAINST ('aaaa' IN BOOLEAN MODE)
ORDER BY a.id DESC
LIMIT 5;

should become something like

SELECT N.id FROM
(SELECT id FROM a WHERE
MATCH (`search1`,`search2`) AGAINST ('aaaa' IN BOOLEAN MODE)) M
INNER JOIN a N USING (id)
ORDER BY N.id DESC LIMIT 5;

EXAMPLE #2

SELECT a.id,a.popularity FROM a
WHERE
MATCH (`search1`,`search2`) AGAINST ('aaaa' IN BOOLEAN MODE)
ORDER BY a.popularity DESC
LIMIT 5;

should become something like

SELECT N.id,N.popularity FROM
(SELECT id FROM a WHERE
MATCH (`search1`,`search2`) AGAINST ('aaaa' IN BOOLEAN MODE)) M
INNER JOIN a N USING (id)
ORDER BY N.popularity DESC LIMIT 5;

CONCLUSION

The main idea: Collect the keys using MATCH ...AGAINST and join it back to the source table


Getting error USE statement is not supported to switch between databases with Azure Synapse formerly SQLDW Whenever I attempt to submit a query to Azure Synapse SQLDB (or try to do anything with the database from my application called DBForge) I get the following error message:
USE statement is not supported to switch between databases
Can someone let me know how to overcome this issue?

If you check out the documentation for the USE statement, you'll see that it is not supported for Azure Synapse Analytics (formerly Azure SQL DW).
To change database contexts, you'll have to open an explicit connection to the desired database.
If you're having issues with your IDE, try specifying the desired database in your connection string when you connect so there is no switching required.


Unstable backup size, where to look for causes I've got a new SQL Server 2014 (12.0.2000) instance with about 50 databases, it was recently upgraded from SQL Server 2005.

They were using a lot of backup space so the old maintenance plan was replaced to a different approach:

Check Db Integrity -> Back up Database (Full) (with Compression) -> Rebuild Index -> Maintenance Cleanup

That happens everyday while databases aren't being used and it preserves backups for 5 days.

At the beginning it worked OK and a lot of backup drive was saved but then some of the databases started backing up with erratic file sizes.

A few images that display the file size change:

This one went down a whole lot, that was expected. Thing is, it then when down another two times (?)

This other one when down a bit and then further down in size and finally got bigger than the first time it backed up with compression.

Finally this one went down in size, further down next day and then up the that place it was the first day.

In this case:

On restoring, the file the size increases quite a bit:

So they do restore the way they're supposed to but I'm here wondering, how come they vary in size, I can totally understand the change after the compression but after (?).

Why would the backup behave in this way? Any known reasons?

Additionally: Nothing really going on in the logs.

When using backup compression, the size of the backup will vary quite a lot assuming data is being changed in the database.

Performing index maintenance each night will move data around inside the database a lot, resulting in unpredictable changes to the amount of allocated pages inside the database.  Index defragmentation is not perfect and does not guarantee the index is completely defragmented - it is merely a way of reducing fragmentation.

The amount of change you're seeing is nothing to be concerned about, especially when you see your file sizes are measured in kilobytes.


What is the difference between the user `dbo` and the owner of the database stored in sys.databases We recently had a question come up where the user dbo in a database had an sid that didn't match the owner_sid in sys.databases.  I understand how the owner of the database is different from members of the role db_owner but I had always thought the user dbo was the actual owner of the database.  Is that not the case?  And if so are there any real differences between dbo and what's in sys.databases?

I had always thought the user dbo was the actual owner of the database.

That is (or at least should be) correct. The name "dbo" of that User never changes, yet the underlying SID does depending on who created the database, or who it was set to be via sp_changedbowner (up though, and including, SQL Server 2005) or ALTER AUTHORIZATION (starting with SQL Server 2008).

In all three of those cases, the record in sys.databases is also changed so that they are kept in sync. However, when restoring a Database, either from another system or from the same Instance but from a DB that was backed-up / detached prior to one of those 2 SQL commands being executed to change the owner, then upon RESTORE or attach, there will be a mismatch between the owner_sid column in sys.databases and the "dbo" sid in sys.database_principals in that DB.

As far as I am aware of, the record in sys.database_principals in each DB is the real owner, and the owner_sid column in sys.databases is a matter of record-keeping / convenience (similar to denormalization; without sys.databases the system would need to make separate queries across all DBs to get that info, each time requested!) and security. One thing it is used for is to identify a potentially harmful / invalid restored / attached DB is those records do not match. Attempting to access SQLCLR Assemblies marked as either EXTERNAL_ACCESS or UNSAFE won't load if one has chosen to go the less secure route of enabling TRUSTWORTHY as that relies upon the "dbo" SID as it needs to match a Login that has either the EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY permission. And when there is a mismatch in the SID between those two system catalog views, it can't be determined which one to use, and used as a red-flag for there being a potential security issue. In fact, I test for this condition in the installation script for SQL# to alert someone to make the appropriate change, just so that they don't have to waste time hunting it down in case SQL Server complains about it at some point.


Linq compiled query uses clusted index instead of filtered index Given the following table

CREATE TABLE Test(
    Id INT PRIMARY KEY IDENTITY(1, 1),
    Val INT
)

CREATE UNIQUE INDEX UX_Test_Val ON Test(Val) WHERE Val IS NOT NULL

DECLARE @i INT = 0
WHILE (@i < 30000)
BEGIN
INSERT INTO Test(Val) VALUES(@i)
SET @i = @i + 1
END

and I have a linq compiled query from a statement similar to

Test.Where(x => x.Val == 123).FirstOrDefault();

this gets compiled to something similar to

DECLARE @V INT
SELECT TOP 1 Id FROM Test WHERE Val = @V OR (Val IS NULL AND @V IS NULL)

Seems legit. But I am very disappointed when seeing the clustered index scan in the query plan.

The plan looks way better when I remove the second clause.

SELECT TOP 1 Id FROM Test WHERE Val = @V

This time the plan is a non clustered Index Seek with 1 row read

So my first guess is that the filtered index is the evil here and I created a second non-clustered not unique index on the same column

CREATE INDEX IX_Test_Val ON Test(Val)

This time both statements uses the newly created index and yields to the same index seek plan.

Here comes three questions:

Why does filtered index has such poor performance in this case?
How can I make linq to omit the IS NULL part?
Is there a way to enforce unique constrain that allows NULL with just one non filtered index?

Why does filtered index has such poor performance in this case?

The query couldn't use the filtered index, because of the IS NULL part.  By definition, the query needs to consider rows that have a Val of NULL, and the filtered index doesn't have those rows.

  How can I make linq to omit the IS NULL part?

This isn't really a database question, but...

LINQ is accounting for your parameter being nullable.  In the example you gave, it's not nullable (it's an integer literal).  In your real code, it's probably a nullable int (int?) or a string, or some other reference type.  Change the parameter to be non-nullable (or if it's a nullable value type, call .Value) to remove the IS NULL part of the query.

  Is there a way to enforce unique constrain that allows NULL with just one non filtered index?

If I understand your question, then no - I don't think so.  A UNIQUE constraint or index will allow 1 NULL value, but not multiple.  However, the filtered index might work for your purposes once the query issue has been fixed.


Selecting a proper data warehouse and/or big data approach We have about 2 TB of data distributed across literally thousand of tables in PostgreSQL 9.4.9, and we think that we should be moving the historic data to a more report-oriented and efficient solution. Which is the proper solution is what troubles me, as I'd like it to be AWS based (perhaps RedShift, EMR or other) as our platform resides there, but even within that ecosystem, there are several approaches to be used. I'm aware that asking about the choice of a specific engine and/or solution is offtopic, but I'd certainly like a suggestion of what kind of tools to choose from, without necessarily implying the use of single one. As a team, we have no knowledge yet of the data warehouse architecture and, just a bit of the big data technologies, but we certainly have to study on those subjects.

I think you should be thinking about what your end state is going to be.

I.e: "What is the requirement"?

The requirements should be driving the solution. Not the other way around.

What does the business want?
What reports do you need? What outputs?
Will your reporting solution benefit from aggregation? Would an OLAP cube be of benefit? Will you be transforming or denormalising the data?
Will your DW be fed from one system or many? If many: will it be application agnostic?
What display tools and technologies are expected? Any compatibility issues that might limit platform selection?
How long do your current reports take? And how fast do they need to be?. How fast is fast enough? What changed?
Can you produce the reports you need now with the data you have?
Are conventional reports still acceptable? Or does the business now need/want dynamic interactive tools? Reports? Dashboard? Exploration tools? Mobile?
How recent must the data be? Is yesterdays data processed overnight acceptable? Or do they need near real time?
Do they want analytical or operational data? What happened over the last 12 months or what happened this morning?

Once you have a clear requirement, it will allow you to shortlist viable solutions. Within your shortlist you can start to express preferences and pros and cons for the evaluation process.


An "unlimited" data model I need to store an unknown amount  of fields. Some are numbers (like IDs, zip codes and times), some are short strings (like names and states), and some are longer strings (such as descriptions). All fields are related to an ID.

I suppose I'd go with some kind of normalization, but what's the most optimal way? Have a table for each data type?

Thank you in advance!

The answer depends quite a bit on what you want to do.  One option is EAV as one of the notes pointed out.  Another option is what I call EAV-lite which is to have stored procedures for adding columns to an additional "extended attributes" table.  This gives you something of a fixed schema with extended attributes.  If you need to add something else you can do it.  Of course you need EAV-like catalogs to tell you which attributes to search on, which is why I call it EAV-lite.

What we did in LedgerSMB was to create two additional tables for this catalog info: custom_table_catalog and custom_field_catalog for tracking these extensions.  Then the extends_* tables hold these extended attributes horizontally.

If you were on Postgres you could use hstore for these extended attributes too.  In 9.1 you could even use JSON.  Of course care needs to be taken in breaking the standard normalized approach here.  I would recommend EAV-lite more likely.

Edit: adding sample code.  Note that this works on PostgreSQL but on MySQL you will need to rewrite the functions as stored procs and possibly some other things.

CREATE TABLE custom_table_catalog (
table_id SERIAL PRIMARY KEY,
extends TEXT,
table_name TEXT
);

CREATE TABLE custom_field_catalog (
field_id SERIAL PRIMARY KEY,
table_id INT REFERENCES custom_table_catalog,
field_name TEXT
);

CREATE OR REPLACE FUNCTION add_custom_field (table_name VARCHAR, new_field_name VARCHAR, field_datatype VARCHAR)
RETURNS BOOL AS
'
BEGIN
        perform TABLE_ID FROM custom_table_catalog
                WHERE extends = table_name;
        IF NOT FOUND THEN
                BEGIN
                        INSERT INTO custom_table_catalog (extends)
                                VALUES (table_name);
                        EXECUTE ''CREATE TABLE '' ||
                               quote_ident(''custom_'' ||table_name) ||
                                '' (row_id INT PRIMARY KEY)'';
                EXCEPTION WHEN duplicate_table THEN
                        -- do nothing
                END;
        END IF;
        INSERT INTO custom_field_catalog (field_name, table_id)
        values (new_field_name, (SELECT table_id
                                        FROM custom_table_catalog
                WHERE extends = table_name));
        EXECUTE ''ALTER TABLE ''|| quote_ident(''custom_''||table_name) ||
                '' ADD COLUMN '' || quote_ident(new_field_name) || '' '' ||
                  quote_ident(field_datatype);
        RETURN TRUE;
END;
' LANGUAGE PLPGSQL;
-- end function

CREATE OR REPLACE FUNCTION drop_custom_field (VARCHAR, VARCHAR)
RETURNS BOOL AS
'
DECLARE
table_name ALIAS FOR $1;
custom_field_name ALIAS FOR $2;
BEGIN
        DELETE FROM custom_field_catalog
        WHERE field_name = custom_field_name AND
                table_id = (SELECT table_id FROM custom_table_catalog
                        WHERE extends = table_name);
        EXECUTE ''ALTER TABLE '' || quote_ident(''custom_'' || table_name) ||
                '' DROP COLUMN '' || quote_ident(custom_field_name);
        RETURN TRUE;
END;
' LANGUAGE PLPGSQL;

I don't know if MySQL allows for alter table statements to be done dynamically, so worst case scenario you might have to maintain these functions in the app layer.

The application then selects the contents of custom_table_catalog and custom_field_catalog and caches these in memory.  When an item is saved or retrieved, these are then used to create SQL CRUD functions.  Currently they are used for orders, invoices, goods, and services.


COUNT (*) = 0 does not work? Alright, I'm trying to make a query for a library that will show which students have never borrowed a book. For this, I have done the following:

SELECT
    LEERLINGEN.LLNR,
    LEERLINGEN.VOORNAAM,
    LEERLINGEN.TUSSENVOEGSEL,
    LEERLINGEN.ACHTERNAAM,
    LEERLINGEN.KLAS,
    COUNT(*) AS AANTAL
FROM
    UITLENINGEN
    INNER JOIN LEERLINGEN ON UITLENINGEN.LLNR = LEERLINGEN.LLNR
GROUP BY
    LEERLINGEN.LLNR,
    LEERLINGEN.VOORNAAM,
    LEERLINGEN.TUSSENVOEGSEL,
    LEERLINGEN.ACHTERNAAM,
    LEERLINGEN.KLAS
HAVING
    COUNT(*) = 0;

This doesn't seem to work for some reason, as all it does is create an empty table when I click Execute.

What did I do wrong here?

You need to use an outer join, otherwise you won't get those students back that did not borrow a book.

Then you need to count() on the students table, not "the group" (which is done when you use (*))

SELECT
    LEERLINGEN.LLNR,
    LEERLINGEN.VOORNAAM,
    LEERLINGEN.TUSSENVOEGSEL,
    LEERLINGEN.ACHTERNAAM,
    LEERLINGEN.KLAS,
    COUNT(LEERLINGEN.LLNR) AS AANTAL
FROM
    LEERLINGEN
    LEFT OUTER JOIN UITLENINGEN ON UITLENINGEN.LLNR = LEERLINGEN.LLNR
GROUP BY
    LEERLINGEN.LLNR,
    LEERLINGEN.VOORNAAM,
    LEERLINGEN.TUSSENVOEGSEL,
    LEERLINGEN.ACHTERNAAM,
    LEERLINGEN.KLAS
HAVING
    COUNT(UITLENINGEN.LLNR) = 0;

I dont' know MS-Access, but I assume it supports outer joins using standard SQL.


Two mysql select queries into one query user

id   |  name
1    |  A
2    |  B

article

id   | title | descritption
1    | wx    | xyz
2    | yz    | abc

article_rating

article_id(article->id)  |  given_by(user->id)  |  given_rating
   1                              2                  4
   2                              1                  3

saved_article

article_id(article->id)  |  saved_by(user->id)
   1                              2

I'm changing some buttons and icons depending on whether a user has rated/ saved an article or not. I'm using two select queries for that:

rated or not

SELECT given_rating as rating
FROM article_rating
WHERE  given_by=? AND article_id=?

saved or not

SELECT saved_by as saved
FROM saved_article
WHERE  saved_by=? AND article_id=?

So my question is how can i combine both into one mysql query efficiently??

If the article_id is the same in both tables, and you are looking for the same article in both tables, then you could use an INNER JOIN to join the two tables, using the article_id column.

SELECT ar.given_rating as rating, sa.saved_by as saved
FROM article_rating  ar
INNER JOIN saved_article sa
ON ar.article_id = sa.article_id
WHERE ar.given_by=? AND sa.saved_by=? AND ar.article_id=?;

DB-Fiddle


PostgreSQL : dependencies on a schema I need to delete a schema in my PostgreSQL database. I would like to make sure that the schema is not used in another schema (for instance : this schema is used for a view in another one). Is there a way to list all the dependencies in the rest of the database ?

I have tried DROP SCHEMA mySchema ; but the given list of dependencies is partial...

Many thanks for help !

You can use pg_depend table to resolve that :

select objid::regclass
from pg_depend
where refobjid = (select oid
                  from pg_namespace
                  where nspname='mySchema');


MySQL Config for Large MyISAM tables I have to do some updates on myisam tables which have around 20 M records. Our server has 24GB of ram and 2 quad core xeon with 24 MB of cache. I want to know how I can change the my.ini file so I get the best performance. I have windows 7 64 bit.

You will need to check out the sum total of your indexes for MyISAM. Please run this query:

SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1))
recommended_key_buffer_size FROM
(SELECT LEAST(POWER(2,32),KBS1) KBS
FROM (SELECT SUM(index_length) KBS1
FROM information_schema.tables
WHERE engine='MyISAM' AND
table_schema NOT IN ('information_schema','mysql')) AA ) A,
(SELECT 2 PowerOf1024) B;

Whatever number comes out, use either that number or 8G, whichever is smaller.

MyISAM only caches index pages.

If you plan to migrate your data to InnoDB, use these settings:

[mysqld]
innodb_file_per_table
innodb_log_file_size=2047M
innodb_log_buffer_size=64M
innodb_buffer_pool_size=18G
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_io_capacity=10000

You wiil need to optimize the InnoDB infrastructure


tempdb in sql server Using SQL Server 2000, I am getting the following error.

  The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space."

How to solve it? I am unable to take backup of tempdb.

I have tried all the options like :

backup log tempdb with truncate_only
dump tran tempdb with no_log;
shrink the database.

You may want to consider just setting the Recovery Model for tempdb to Simple. That should get you around the problem you are having as it will reclaim all of the space used in the transaction log for tempdb. The MSDN documentation also suggests setting the Recovery Model for tempdb to Simple for performance reasons.

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

EDIT
According to the documentation, for SQL Server 2005 - 2008 R2, tempdb cannot be backed up and should always be set to the Simple recovery model. If for some reason you are not using the Simple recovery model for tempdb, I would suggest switching to it.

EDIT 2
Just to be certain that the recovery model for tempdb is set to Simple, execute the following stored procedure:

sp_helpdb

Check the results for tempdb, and make sure the status column for tempdb shows something like:

Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, ...

If it does, then your problem may be related to the operations you are performing. Are you trying to transactionally write to a temp table? Do you have open transactions that are not getting closed? Is there one operation in particular that is crashing, or does everything not work? You may want to check the answers to this similar question on serverfault for some more ideas. Long story short, if you're still getting this error after setting the Recovery Model to Simple I would start looking at the operations you're trying to perform as a possible culprit.

EDIT 3
From the output of DBCC SQLPERF (LOGSPACE) it appears that your tempdb log file is only 1.24 MB, which seems unnecessarily small to me. You can increase the size of the log file by using Enterprise Manager, or you should be able to use the following SQL (although you might need to look up the log file name by checking the files on tempdb):

ALTER DATABASE tempdb MODIFY FILE (NAME = templog, SIZE = 50MB)

You could try increasing the log file size to see if that helps.


SQL query to return result with different row I am working with a database that has a table called 'order' that looks like the following.

id    day    item
-------------------
11     1     apple
11     3     banana
11     4     berry
22     1     coke
22     3     pepsi
33     2     chips
33     4     salsa

I want mysql query output to look like the following:

id    1       2       3       4
------------------------------------
11    apple   null    banana   berry
22    coke    null    pepsi    null
33    null    chips   null     salsa

Is it possible to get such an output using mysql?

SELECT id,
       MAX(CASE WHEN `day` = 1 THEN item END) day_1,
       MAX(CASE WHEN `day` = 2 THEN item END) day_2,
       MAX(CASE WHEN `day` = 3 THEN item END) day_3,
       MAX(CASE WHEN `day` = 4 THEN item END) day_4
FROM source_table
GROUP BY id


Error when restoring SQL Server 2008 R2 backup on 2012 I am trying to restore a .bak file (backup taken on SQL Server 2008 R2, compatibility level 100). I am restoring the backup on SQL Server 2012 edition. I get the below error. Unfortunately the original DB from where the DB was backed up is no longer available. Any suggestions on how I can restore the DB to 2012 ?

  System.Data.SqlClient.SqlError: Read on *** failed: 13 (The data is invalid.) (Microsoft.SqlServer.SmoExtended)

Do a Restore Headeronly With File = '...'  If it still fails it's not a valid native SQL Server backup file. It's not an issue of versions.

Open it with a hex or text editor, what's the file header look like? Maybe it's compressed with a 3rd party solution like LiteSpeed and those require you to use a tool to convert to native format if you don't have it installed in this environment.


psql, I try to connect to a database and I get "psql: FATAL: Peer authentication failed for user ", why? I'm using psql (9.2.23) on my Dell Latitude 3540 laptop running Linux CentOS 7.
I want to do a simple thing: create a database username and log in as that username.

My operating system username is "davide", and the username I'd like to create and use in psql is "davide3". My laptop name is "laptop".

Here are the steps I followed, after having installed PostgreSQL:

  [davide@laptop ~]$ sudo su - postgres

  [sudo] password for davide:

  Last login: XXX

  -bash-4.2$ psql

  psql (9.2.23)

  Type "help" for help.

  postgres=# CREATE USER davide3 WITH PASSWORD 'davide3' CREATEDB
  CREATEUSER;

  CREATE ROLE

  postgres=# \q

  -bash-4.2$ exit

  logout

Then I try to login to the database by using the username "davide3":

  [davide@laptop ~]$ psql -d postgres -U davide3

  psql: FATAL:  Peer authentication failed for user "davide3"

Here's the trouble: I was expecting to get a password-insertion message, and to insert the 'davide3' password, but I did not get anything instead.

What did I do wrong?
How to solve this issue?

Thanks

You're not asked for a password because the pg_hba.conf configuration file in CentOS says that a connection through a Unix domain socket implies the peer authentication. peer succeeds only if your OS user is the same as your database user. It's a reasonable default.

You may edit pg_hba.conf to indicate that anyone can connect locally to davide3 to certain databases or all databases, or more generally whatever policy is best suitable in your case.

The file is documented at:
https://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html

Alternatively, keep the default policy and try to add -h localhost to psql invocation. This should trigger a different authentication rule (md5) in the default pg_hba.conf that will ask for the password.


Jetbrains 0xDBE : How can I load a SQL Server .bak file? I'm trying this new Jetbrains IDE and I can't figure out how to load a SQL Server 2008 R2 .bak file. Maybe I'm looking in the wrong menus. Is it possible?

An IDE is not going to be able to "load" a SQL Server backup. You need to restore the backup to a running instance of SQL Server, then the IDE will be able to connect to it and interact with it. You should be able to connect the IDE to a running instance of SQL Server, and then through a query window run a RESTORE command, documented here:

RESTORE (MSDN)

Depending on where the backup came from, you will likely need to use the WITH MOVE options, as it is unlikely your default data/log folders match those from the source.


How to "hack-drop" a database snapshot after SQL Server upgrade has botched it I'm dealing with an issue where database snapshots cannot be dropped after SQL Server has been upgraded to 2014. The snapshots are defunct but cannot be dropped.

Now although the Connect item has a disappointing comment in it I assume that this will be fixed eventually. However, I need to deal with this soon.

Is there a way to drop a database snapshot that cannot be deleted due to this bug? I fear that I have exhausted all documented means to do this. It might take undocumented modifications to system tables. Is there a way to force a database (snapshot) to be dropped?

I was able to reproduce this in an Azure VM, and resolve by simply setting the primary database offline and dropping the snapshot, eg

ALTER DATABASE AdventureWorks2012 SET OFFLINE
DROP DATABASE AdventureWorks2012_SN001


How to find the size, name and other details of Analysis server database? How to find the size, name and other details of an Analysis Services database ?

I have a Analysis Services server and I can see one database inside that.

I want to know the size and other basic details of this DB.

You could use powershell to connect to the database and list a lot of properties.

Use this script to find out what properties you can get from a database:

$loadInfo = [Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")
$server = New-Object Microsoft.AnalysisServices.Server
$server.connect("localhost")
$Server.Databases | Get-Member

Which outputs:

   TypeName: Microsoft.AnalysisServices.Database

Name                        MemberType Definition
----                        ---------- ----------
Disposed                    Event      System.EventHandler Disposed(System.Object, System.EventArgs)
Backup                      Method     void Backup(string file), void Backup(string file, bool allowOverwrite), void Backup(string file, bool allowOverwrite, bool bac...
CanProcess                  Method     bool CanProcess(Microsoft.AnalysisServices.ProcessType processType), bool IProcessable.CanProcess(Microsoft.AnalysisServices.Pr...
Clone                       Method     Microsoft.AnalysisServices.Database Clone(), System.Object ICloneable.Clone()
CopyTo                      Method     Microsoft.AnalysisServices.Database CopyTo(Microsoft.AnalysisServices.Database obj)
CreateBody                  Method     void IMajorObject.CreateBody()
CreateObjRef                Method     System.Runtime.Remoting.ObjRef CreateObjRef(type requestedType)
DependsOn                   Method     bool IMajorObject.DependsOn(Microsoft.AnalysisServices.IMajorObject obj)
Detach                      Method     void Detach(), void Detach(string password)
Dispose                     Method     void Dispose(), void IDisposable.Dispose()
Drop                        Method     void Drop(), void Drop(Microsoft.AnalysisServices.DropOptions options), void Drop(Microsoft.AnalysisServices.DropOptions option...
Equals                      Method     bool Equals(System.Object obj)
GetDependents               Method     hashtable GetDependents(hashtable dependents), hashtable IMajorObject.GetDependents(hashtable dependents)
GetHashCode                 Method     int GetHashCode()
GetLifetimeService          Method     System.Object GetLifetimeService()
GetReferences               Method     hashtable GetReferences(hashtable references, bool forMajorChildrenAlso)
GetType                     Method     type GetType()
GetUpdateOverwrites         Method     hashtable GetUpdateOverwrites(bool fullExpansion)
InitializeLifetimeService   Method     System.Object InitializeLifetimeService()
LinkDimension               Method     Microsoft.AnalysisServices.Dimension LinkDimension(Microsoft.AnalysisServices.Dimension source, string dataSourceId), Microsoft...
OnDeserialization           Method     void IDeserializationCallback.OnDeserialization(System.Object sender)
OnDeserializationBegin      Method     void IDeserializationStartCallback.OnDeserializationBegin(System.Object sender)
Process                     Method     void Process(), void Process(Microsoft.AnalysisServices.ProcessType processType), void Process(Microsoft.AnalysisServices.Proce...
Refresh                     Method     void Refresh(), void Refresh(bool full), void Refresh(bool full, Microsoft.AnalysisServices.RefreshType type), void IMajorObjec...
Submit                      Method     void Submit(bool submitPermanently), void Submit()
ToString                    Method     string ToString(), string IFormattable.ToString(string format, System.IFormatProvider formatProvider)
Update                      Method     void Update(), void Update(Microsoft.AnalysisServices.UpdateOptions options), void Update(Microsoft.AnalysisServices.UpdateOpti...
Validate                    Method     bool Validate(Microsoft.AnalysisServices.ValidationErrorCollection errors, bool includeDetailedErrors, Microsoft.AnalysisServic...
WriteRef                    Method     void IMajorObject.WriteRef(System.Xml.XmlWriter writer)
Accounts                    Property   Microsoft.AnalysisServices.AccountCollection Accounts {get;}
AggregationPrefix           Property   string AggregationPrefix {get;set;}
Annotations                 Property   Microsoft.AnalysisServices.AnnotationCollection Annotations {get;}
Assemblies                  Property   Microsoft.AnalysisServices.AssemblyCollection Assemblies {get;}
BaseType                    Property   type BaseType {get;}
Collation                   Property   string Collation {get;set;}
CompatibilityLevel          Property   int CompatibilityLevel {get;set;}
Container                   Property   System.ComponentModel.IContainer Container {get;}
CreatedTimestamp            Property   datetime CreatedTimestamp {get;set;}
Cubes                       Property   Microsoft.AnalysisServices.CubeCollection Cubes {get;}
DatabasePermissions         Property   Microsoft.AnalysisServices.DatabasePermissionCollection DatabasePermissions {get;}
DataSourceImpersonationInfo Property   Microsoft.AnalysisServices.ImpersonationInfo DataSourceImpersonationInfo {get;set;}
DataSources                 Property   Microsoft.AnalysisServices.DataSourceCollection DataSources {get;}
DataSourceViews             Property   Microsoft.AnalysisServices.DataSourceViewCollection DataSourceViews {get;}
DbStorageLocation           Property   string DbStorageLocation {get;set;}
Description                 Property   string Description {get;set;}
Dimensions                  Property   Microsoft.AnalysisServices.DimensionCollection Dimensions {get;}
DirectQueryMode             Property   Microsoft.AnalysisServices.DirectQueryMode DirectQueryMode {get;set;}
DismissedValidationResults  Property   Microsoft.AnalysisServices.DismissedValidationResultCollection DismissedValidationResults {get;}
DismissedValidationRules    Property   Microsoft.AnalysisServices.DismissedValidationRuleCollection DismissedValidationRules {get;}
EstimatedSize               Property   long EstimatedSize {get;set;}
FriendlyPath                Property   string FriendlyPath {get;}
Host                        Property   System.IServiceProvider Host {get;set;}
ID                          Property   string ID {get;set;}
ImagePath                   Property   string ImagePath {get;set;}
ImageUniqueID               Property   string ImageUniqueID {get;set;}
ImageUrl                    Property   string ImageUrl {get;set;}
ImageVersion                Property   string ImageVersion {get;set;}
IsLoaded                    Property   bool IsLoaded {get;}
Language                    Property   int Language {get;set;}
LastProcessed               Property   datetime LastProcessed {get;set;}
LastSchemaUpdate            Property   datetime LastSchemaUpdate {get;set;}
LastUpdate                  Property   datetime LastUpdate {get;set;}
MasterDataSourceID          Property   string MasterDataSourceID {get;set;}
MiningStructures            Property   Microsoft.AnalysisServices.MiningStructureCollection MiningStructures {get;}
ModelType                   Property   Microsoft.AnalysisServices.ModelType ModelType {get;set;}
Name                        Property   string Name {get;set;}
ObjectReference             Property   Microsoft.AnalysisServices.ObjectReference ObjectReference {get;}
OwningCollection            Property   Microsoft.AnalysisServices.ModelComponentCollection OwningCollection {get;set;}
Parent                      Property   Microsoft.AnalysisServices.Server Parent {get;}
ParentDatabase              Property   Microsoft.AnalysisServices.Database ParentDatabase {get;}
ParentServer                Property   Microsoft.AnalysisServices.Server ParentServer {get;}
Path                        Property   string Path {get;}
ProcessingPriority          Property   int ProcessingPriority {get;set;}
ReadWriteMode               Property   Microsoft.AnalysisServices.ReadWriteMode ReadWriteMode {get;set;}
ReturnPassword              Property   bool ReturnPassword {get;set;}
Roles                       Property   Microsoft.AnalysisServices.RoleCollection Roles {get;}
Site                        Property   System.ComponentModel.ISite Site {get;set;}
SiteID                      Property   string SiteID {get;set;}
State                       Property   Microsoft.AnalysisServices.AnalysisState State {get;set;}
StorageEngineUsed           Property   Microsoft.AnalysisServices.StorageEngineUsed StorageEngineUsed {get;set;}
Translations                Property   Microsoft.AnalysisServices.TranslationCollection Translations {get;}
Visible                     Property   bool Visible {get;set;}

You can combine or loop over these properties as much as you want, for example this script should give you the size of a known database:

$loadInfo = [Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")
$server = New-Object Microsoft.AnalysisServices.Server
$server.connect("localhost")
$database = $server.Databases.FindByName("AdventureWorksDW2012Multidimensional-EE")
Write-Output("database size Size : {0}MB" -f ($database.EstimatedSize/1024/1024).ToString("#,##0"))

outputs:

database size Size : 24MB

If you wanted the size of all databases on the server you can loop over them too:

$loadInfo = [Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")
$server = New-Object Microsoft.AnalysisServices.Server
$server.connect("localhost")
foreach ($database in $server.Databases)
{
Write-Output("{0} size Size : {1}MB" -f $Database.Name, ($database.EstimatedSize/1024/1024).ToString("#,##0"))
}

Outputs:

AdventureWorksDW2012Multidimensional-EE size Size : 24MB
MultidimensionalProject1 size Size : 0MB

Mix and match to your liking to get information on all databases, a single database, cubes inside a database, dimensions inside a cube etc.


MariaDB Slave cause "Copying to tmp table" DB: MariaDB 10.0.23

STOP SLAVE;
run query
SHOW PROFILE;

Status  Duration
starting    0.000028
Waiting for query cache lock    0.000006

init    0.000004
checking query cache for query  0.000011
checking privileges on cached   0.000005
checking permissions    0.000007
checking permissions    0.000005
checking permissions    0.000005
sending cached result to clien  0.000154
updating status 0.000005
cleaning up 0.000003

After starting slave, long "Copying to tmp table" shows up:

START SLAVE;
run query  EXPLAIN outputs same query plan like before starting the slave.
SHOW PROFILE;

Status  Duration
starting    0.000046
Waiting for query cache lock    0.000007
init    0.000006
checking query cache for query  0.000109
checking permissions    0.000007
checking permissions    0.000005
checking permissions    0.000006
Opening tables  0.000020
After opening tables    0.000009
System lock 0.000007
Table lock  0.000005
After opening tables    0.000007
Waiting for query cache lock    0.000005
After opening tables    0.000031
init    0.000050
optimizing  0.000032
statistics  0.000139
preparing   0.000050
executing   0.000007
Copying to tmp table    0.000025
Copying to tmp table    1.760070   <<<<<<<<<<<<<<<<<<<<
Sorting result  0.003707
Sending data    0.000013
end 0.000005
removing tmp table  0.000261
end 0.000006
query end   0.000006
closing tables  0.000010
freeing items   0.000013
updating status 0.000068
logging slow query  0.000008
cleaning up 0.000005

Not MariaDB Slave, the Query Cache.

It appears that the first Profile came from a machine where the identical query had already been stored in the Query Cache.  Hence, the query took only a fraction of a millisecond to simply deliver the old resultset to the client.

The second server apparently has not seen the identical query recently, so it had to work on the query (open tables, optimize, use a tmp table, etc).  This took a lot longer.

Addenda

The Query Cache works this way:

If a query is in the QC, and exactly the same query shows up to be run, the resultset is delivered very rapidly from the QC.  However,
When any modification to the tables(s) involved occurs, the all entries in the QC for that table(s) are purged.
Replication is a stream of modifications.

For this reason, then QC is rarely worth turning on for Production servers with a lot of write traffic.

So, I deduce that the some modification (INSERT, UPDATE, etc) occurred to that table, thereby invalidating the entry in the QC before that second copy of the SELECT was presented.

If the stream of changes from Replication had only touched other tables, then the second query would have been just as fast as the first.


SET CONTEXT_INFO for tracking specific SQL requests I've got a problem where I'm seeing some very strange lock waits, with the process at the top of the lock wait tree in the 'sleeping' 'AWAITING COMMAND' state.  I can see the machine name and application, and I'm pretty sure it's a client-side transaction that's blocked on the client-side after locking some records that's causing the problem, but I haven't been able to find through code examination what it could be doing.  I've already ruled out GC, because the same application is responding to other requests (we have a request to dump the status of all pending requests, each with a unique ID, and that returned just fine while the lock remained).  By the time we are notified of this issue, request are backed up to the tune of several thousand per SQL client machine, so sifting through all these requests is very painful without writing custom code just for that.

I'd like to add some info to the SQL connection information to track which request is the one at the top, not just which machine and application (and I think this may prove useful in diagnosing future issues as well).  I could append something to the application name in the connection string, but I think that would likely do bad things to the connection pooling.

I read this post: Can I Set The Value of App_Name() AFTER Login?
and this one: http://www.sqlservercentral.com/articles/T-SQL/2765/ which seem to indicate that I might be able to use SET CONTEXT_INFO to get data into sys.dm_exec_requests.context_info and then I would be able to use that data to identify the exact request the bad transaction is coming from.

We're not using SET CONTEXT_INFO for anything else in the application (and the SQL servers are dedicated to this application).

Is there some reason I would not want to do request identification this way, or some better way to track requests through to the SQL layer?

I've now got this in production and it's working beautifully.  We found the underlying issue before this went out, but it may be very useful for future issues.

To any future readers, SET CONTEXT_INFO can indeed be used for correlating SQL requests to the code and/or requests that run them, and I've seen no noticeable performance hit.

For anyone curious about the specifics of this locking issue, it turns out that in this case, we had several places in our code where we opened a transaction and then during the transaction, we called a library function that on the surface appeared to just retrieve a cached value, but in reality did some non-database work that should not have been done inside the transaction and then proceeded to open its own database connection.  Under certain high-load situations, this caused a deadlock because the transaction locked a highly-contented item, then while the improper processing was occurring, every other available connection became blocked waiting for the same highly-contented item.  Once the processing was complete, the errant code then attempted to open the separate database connection, which deadlocked because every available connection was in use and waiting for the transaction to complete.  Adding code to detect that situation and log the call stacks allowed us to find not only the case that had begun triggering multiple times per week, but five other cases as well.

Lesson: let the database do everything involved with every transaction (never use SqlConnection.BeginTransaction).  When you think you need to do something client side during the transaction, redesign the system so that you don't need to do that--you may think you're fine with the one thing you're doing, and you might be, but you never know how the code may subtly change in the future and break you just like this.


Max number of tables (objects) in SQL Server editions (compact, express) What are the maximum number of tables that you can have in:

SQL CE4
SQL 2008 R2 Express

Numbers here http://msdn.microsoft.com/en-us/library/ms143432.aspx I guess is for SQL Server 2008, but are they valid for Express and what about SQL CE4?

  The sum of the number of all objects in a database cannot exceed 2,147,483,647

The capacity specifications are valid for Express apart from the limitations listed in:

Features Supported by the Editions of SQL Server 2008 R2
Maximum Number of Processors Supported by the Editions of SQL Server (max 1 cpu)
Memory Supported by the Editions of SQL Server (max  1GB ram)

So yes, the maximum number of tables is limited by the 2,147,483,647 object maximum in Express.

Compact Edition differs substantially from Express, with very different capacity specifications. Haven't found an explicit reference to the maximum table or object count in CE but the 4GB size limit would prevent you getting anywhere near 2,147,483,647.


mysqldump not escaping single quotes properly I use mysqldump to backup my mysql database.
The problem is that the sql file generated by mysqldump doesn't escapes single quotes properly.

Here is an example of the mysqldump generated sql script :

INSERT INTO `someTable` VALUES (1,'This ain\'t escaped correctly');

That single quote escaping in "ain't" doesn't work and it makes the rest of the script being inside that string.
Is there a way around this?

That output of mysqldump is working as designed, and it is properly escaped, unless you try to restore the dump on a MySQL instance with SQL_MODE=NO_BACKSLASH_ESCAPES set.

There's an outstanding feature request to make mysqldump use a pair of single-quotes to escape literal single-quotes, as per ANSI SQL.  See http://bugs.mysql.com/bug.php?id=65941

In the meantime you might be able to convert from backslash-singlequote to pair-of-singlequotes with a command line this:

mysqldump test | sed -e "s/\\\'/''/g" > test-dump.sql

I tried that out briefly by creating a dummy table in my test database and inserting the string "O'Hare" into the table.  But that's hardly a comprehensive test -- I take no responsibility for this suggestion working in all cases.


Why does an SQL Server Agent job try to create a procedure I am using SQL Agent to schedule the exeuction of a procedure.

The first time that the procedure is executed, it runs without problems.
The second time the job (step) executes, it does not finish.

A variation of the following SQL statement tells me that the running statement is create procedure xyz... (where xyz is the name of the procedure that the job is supposed to run).

select
   prc.loginame,
   prc.program_name,
   sql.text                                           sql_text
from
   sys.sysprocesses     prc                 cross apply
   sys.dm_exec_sql_text(prc.sql_handle) sql

I don't do any DDL in my procedure (or otherwise) and am wondering what causes the job to try to create the procedure and why the job hangs?

Seeing the procedure create statement popping up is expected behaviour here, it has nothing to do with the job agent itself. And the job step is not recreating the procedure, it is only running it.

Example

If I create this procedure:

CREATE PROC dbo.Waitfordelay
as
WAITFOR DELAY '00:00:10';
SELECT 5;

And then run it in a query window:

EXEC dbo.Waitfordelay;

Then run your query:

loginame       program_name                                    sql_text
Domain\User    Microsoft SQL Server Management Studio - Query  CREATE PROC dbo.Waitfordelay  as  WAITFOR DELAY '00:00:10';  SELECT 5

The create proc also shows up.

If you want more information on the procedure call you could use something like dbcc inputbuffer

dbcc inputbuffer(spid)

For me this returns:

EventType   Parameters  EventInfo
Language Event  0   EXEC dbo.Waitfordelay

Or run sp_whoisactive with the parameter @get_outer_command set to 1.

exec [dbo].[sp_WhoIsActive]
        @get_outer_command = 1;
enter code here

Example result:

dd hh:mm:ss.mss session_id  sql_text                                 sql_command
00 00:00:00.733 54          <?query --WAITFOR DELAY '00:00:10'; --?> <?query --EXEC dbo.Waitfordelay --?>

running sp_whoisactive might also give you more information on what part your procedure is stuck on.

  I don't do any DDL in my procedure (or otherwise) and am wondering
  what causes the job to try to create the procedure and why the job
  hangs?

The create procedure part is cleared up, the actual problem of the job hanging will be guesswork without having a reproducable example or more information.

Is the job running a query? Can parameter sniffing be an issue here?

What are the wait types when running the job? What is the amount of cpu time / reads?

Running below query (yours adapted) when the jobstep is stuck might give more information:

SELECT
req.total_elapsed_time,
req.cpu_time,
req.reads,
req.last_wait_type
,req.wait_type,
req.wait_time,
sql.text  sql_text
FROM
sys.dm_exec_requests     req
cross apply sys.dm_exec_sql_text(req.sql_handle) sql;

We would need more details to know what the actual problem is.


Is there a bug with PL/Scope in combination with associative arrays? I believe I stumbled upon a bug with PL/Scope in combination with associative arrays, but perhaps I overlook something.

I have the following package

create or replace package tq84_pkg_c as

    procedure proc_1;

end tq84_pkg_c;
/

with its body

create or replace package body tq84_pkg_c as

    type num_t is table of number index by varchar2(10);

    procedure proc_2 is begin
        null;
    end proc_2;

    procedure proc_1 is
        v_num num_t;
    begin
        if v_num.exists(1) then
           proc_2;
        end if;
    end proc_1;

end tq84_pkg_c;
/

I compile the the package "with" PL/Scope:

ALTER SESSION SET PLSCOPE_SETTINGS='IDENTIFIERS:ALL';

ALTER PACKAGE TQ84_PKG_C COMPILE;

Then, I query all_identifiers:

select
  name          identifier,
  usage,
  type,
  usage_id,
  usage_context_id,
  line,
  col
from
  all_identifiers
where
  object_name in ('TQ84_PKG_C')
order by
  object_name,
  object_type,
  line,
  col;

With the following result:

IDENTIFIER                     USAGE       TYPE                 USAGE_ID USAGE_CONTEXT_ID       LINE        COL
------------------------------ ----------- ------------------ ---------- ---------------- ---------- ----------
TQ84_PKG_C                     DECLARATION PACKAGE                     1                0          1          9
PROC_1                         DECLARATION PROCEDURE                   2                1          3         15
TQ84_PKG_C                     DEFINITION  PACKAGE                     1                0          1         14
NUM_T                          DECLARATION ASSOCIATIVE ARRAY           2                1          3         10
NUMBER                         REFERENCE   NUMBER DATATYPE             3                2          3         28
VARCHAR2                       REFERENCE   CHARACTER DATATYPE          4                3          3         44
PROC_2                         DEFINITION  PROCEDURE                   6                5          5         15
PROC_2                         DECLARATION PROCEDURE                   5                1          5         15
PROC_1                         DEFINITION  PROCEDURE                   7                1          9         15
V_NUM                          DECLARATION VARIABLE                    8                7         10          9
NUM_T                          REFERENCE   ASSOCIATIVE ARRAY           9                8         10         15
V_NUM                          REFERENCE   VARIABLE                   11               10         12         12
PROC_2                         CALL        PROCEDURE                  12               10         13         12

Now, the problem is with the last two records: Their usage_context_id is 10 and this number is supposed (as I believe) to reference usage_id, yet, there is no record with usage_id=10.

So, is this a bug? Or is there a way to include the missing record into the result set?

I believe the problem is caused by the line if v_num.exists(1) then. If I take this if (and the corresponding end if) out, the problem disappears, and usage_conext_id for PROC_2 references an existing usage_id.

It is a bug, because the documentation states:

  USAGE_CONTEXT_ID is a reflexive foreign key to USAGE_ID

UPDATE: A bit of troubleshooting...

all_identifiers.usage_id comes from sys.plscope_action$.action#

Given:

SQL> /

IDENTIFIER   USAGE       TYPE                 USAGE_ID USAGE_CONTEXT_ID LINE COL
------------ ----------- ------------------ ---------- ---------------- ---- ---
TQ84_PKG_C   DECLARATION PACKAGE                     1                0    1   9
PROC_1       DECLARATION PROCEDURE                   2                1    2  15
TQ84_PKG_C   DEFINITION  PACKAGE                     1                0    1  14
NUM_T        DECLARATION ASSOCIATIVE ARRAY           2                1    3  10
NUMBER       REFERENCE   NUMBER DATATYPE             3                2    3  28
VARCHAR2     REFERENCE   CHARACTER DATATYPE          4                3    3  44
PROC_2       DECLARATION PROCEDURE                   5                1    5  15
PROC_2       DEFINITION  PROCEDURE                   6                5    5  15
PROC_1       DEFINITION  PROCEDURE                   7                1    9  15
V_NUM        DECLARATION VARIABLE                    8                7   10   9
NUM_T        REFERENCE   ASSOCIATIVE ARRAY           9                8   10  15
V_NUM        REFERENCE   VARIABLE                   11               10   12  12
PROC_2       CALL        PROCEDURE                  12               10   13  12

13 rows selected.

SQL>

... we're looking for the elusive sys.plscope_action$.action# = 10 for this object:

SQL> select a.obj#, a.action#, a.signature, a.action, a.line, a.col, a.context#, o.name, o.type#
from sys.plscope_action$ a, sys.obj$ o
where a.obj# = o.obj#
and name = 'TQ84_PKG_C'
and action# = 10;

      OBJ#    ACTION# SIGNATURE                            ACTION LINE COL   CONTEXT# NAME                                TYPE#
---------- ---------- -------------------------------- ---------- ---- --- ---------- ------------------------------ ----------
     83504         10 0C679E5FFBC26144A7F875495F68C2E1          3   12  18          7 TQ84_PKG_C                             11

SQL>

That's the missing row.

The ALL_IDENTIFIERS view joins sys.plscope_identifier$ with sys.plscope_action$ on the signature column - there's no matching row in sys.plscope_identifier$, which I suspect is causing the problem.


Update all rows when sort/ordering value is changed for single row? I am in the process of creating a GIS application with multiple layers and many pieces of content shapes on each layer. Currently each layer/shape is drawn in the order that it was returned from the database, but I want to give the user some control over the ordering. So the user should be able to drag and drop layers/shapes up and down in the layer tree, which should change the drawing order.

In order to persist these changes, each layer/shape must have som sort/ordering index in the database. However, if I use integers for the sorting key, whenever a layer/shape is sorted into a new position, this would require updating all of the following rows with +1.

Is there a good way to avoid this?

The reason I would like to avoid this issue is that there might potentially be a lot of content (thousands of shapes) in a layer, and I am concerned about the performance impact of updating that many rows. Furthermore, it's just not a very elegant solution.

I have considered using floating point values instead, which would make it possible to simply create a new value between two existing values, but at the risk of eventually running into precision problems.

Another approach I am considdering is storing the sort order as a kind of linked list, where each row points to the previous or following row, which would reduce the required number of updates, but would make construction of the tree in the GUI more complex. Even worse, if not all shapes/layers are displayed at all times, creating the layer/shape tree would become impossible.

Is there a commonly accepted good solution for this kind of problem?

I've never handled anything like this before but why not do a combination of the +1 and the decimal versions.  Do the decimal versioning while they are editing .. then at the end of an editing session (when they do a final save) go ahead and re-write all of the values as integers.  Presumably in any given editing period there won't be so many edits that you will run into major problems with precision.

Another option would be to use a list in a single column in say the project table.  Basically have your IDs listed there along these lines.

,43,23,14,55,27, etc.

Then you can use string manipulation to remove ,55, from where it is and stuff it between 23 and 14.  In SQL Server the T-SQL code might look like this:

DECLARE @OrderString varchar(500) = ',43,23,14,55,27,'
DECLARE @MoveString varchar(50) = '55'
DECLARE @MoveBefore varchar(50) = '14'

-- Remove the value to be moved
SET @OrderString = STUFF(@OrderString, PATINDEX('%,'+@MoveString+',%', @OrderString), LEN(@MoveString)+2,',')

-- Put it in the right place
SET @OrderString = STUFF(@OrderString, PATINDEX('%,'+@MoveBefore+',%',@OrderString),0,','+@MoveString)

-- Print it out
PRINT @OrderString


SQL Server 2008r2 won't start after windows updates, error log & wmi provider errors I have a SQL Server 2008 R2 instance on a Windows 2008 R2 box, and windows has not been updated for almost a year (I know), last night windows patches were applied (around a 100 that were pending), and this morning SQL Server will not start.

The error in event viewer is:

  initerrlog: Could not open error log file 'D:\Program Files\Microsoft
  SQL Server\MSSQL10_50.SQL1\MSSQL\Log\ERRORLOG'. Operating system error
  = 5(failed to retrieve text for this error. Reason: 15105).

As per many links (e.g.SQLServerCentral), I have found this does appear to be some kind of permissions issues. The service account SQL Server is configured to use is a network service account, I have checked this in AD and appears to be OK and the password is correct (nothing has changed since yesterday in that regard, when it was working) I have also tried:

Made the service account local admin on the box (temporarily)
Ensure that the service account was also added to the SQL Server groups on the server
Given explicit permission to the service account to the error log folder (full control); then to the whole program files folder (full control)

And I still get the same error.

Next, I tried to change the service account used (to either a network admin - (I know, just to test) or local service account) in Configuration Manager, when applying these changes I get a WMI Provider error:

  WMI Provider Error [call to WMI Provider error code: 0x800742a2]

I am a bit confused by this and haven't seen the like before.

Does anyone have any ideas about what is going on, or anything else to try that I've missed?

Edit 1 :

To add, I've checked the first hits when searching for the WMI error MS Support, SQLServerCentral, but they still seem to be suggesting permissions problems. I've double checked all the above relating to permissions issues and can't see how that is the problem (The service account is now local admin (!), in the SQL Server groups on the box, and has explicit full control to all program files). I am wondering whether this WMI error is a red herring, or totally separate problem.

Edit 2 :

It seems there was something up with the ERRORLOG file that was preventing startup, not a permissions issue - not sure what, but perhaps a corruption or something of the file itself, as deleting it (well, copying it elsewhere) seems to have forced SQL Server to generate a new one and it started up OK. I'm not sure what is wrong with the errorlog file (I will see if I can open it and investigate), and I don't know what the WMI Provider error was, now that the service is online I can't fully test it now, I will try again later in some planned downtime to see if the WMI Provider comes up (and update this as appropriate/add as answer).

Edit 3 (final update):

Seems all of the symptoms were misleading and the usual cause (permissions) to this kind of problem wasn't the cause in this case, it was a problem with the ERRORLOG file itself, and removing it (to be recreated) solved the issue and services came up. I've added an answer describing this. However, this seems to be the only case I can find of these symptoms having this cause, and many cases where it is permissions, which I've included links to above, the answer in this question goes into good detail on. Therefore, if you have this problem in the future, I suggest follow those steps (checking permissions issue first), before checking out the ERRORLOG file itself.

The official solution as per Microsoft article is:

  To work around this problem, add the domain user account to the SQLServer2005MSSQLUser$ComputerName$InstanceName domain group.

  To do this, follow these steps:
  1. Click Start, point to Administrative Tools, and then click Active Directory Users and Computers.
  2. In the Active Directory Users and Computers snap-in, click Users.
  3. Double-click SQLServer2005MSSQLUser$ComputerName$InstanceName.
  4. In the Properties dialog box, click the Members tab.
  5. On the Members tab, click Add.
  6. In the Select Users, Contacts, Computers, or Groups dialog box, type the user in the DomainName\UserName format under Enter the object names to select, and then click OK.
  7. In the Properties dialog box, click OK.

References

Error message when you change the service account of SQL Server 2008 components to a non-domain administrator account on a Windows Server 2008 domain controller: "The service did not start due to a logon failure" or "WMI Provider Error" (Microsoft Support)
WMI Provider Error - When Changing SQL Services Account (SQLServerCentral)

SQL Server Configuration Manager

When you run the SQL Server Configuration Manager and modify settings the configuration manager will add and remove permissions on files and directories based on the configuration changes made.

This is why Microsoft states, that configuration changes should always be made with the SQL Server Configuration Manager.

  Important
  Always use SQL Server tools such as SQL Server Configuration Manager to change the account used by the SQL Server or SQL Server Agent services, or to change the password for the account. In addition to changing the account name, SQL Server Configuration Manager performs additional configuration such as setting permissions in the Windows Registry so that the new account can read the SQL Server settings. Other tools such as the Windows Services Control Manager can change the account name but do not change associated settings. If the service cannot access the SQL Server portion of the registry the service may not start properly.

Reference: SQL Server Configuration Manager (Microsoft Docs)

Actual Steps To Perform

In your case you might have performed several of the steps, but not in the correct order. Try the following:

Open SQL Server Configuration Manager
Set the SQL Server (INSTANCE) service account to a user you don't want the service to run under
Restart the SQL Server (INSTANCE) service for your instance (should fail)
Close SQL Server Configuration Manager
Add the service account that you want the SQL Server service to run under to the specified group (as detailed at the beginning of the post; steps 1-7)
Open SQL Server Configuration Manager
Set the SQL Server (INSTANCE) service account to the user you just added to the SQLServer2005MSSQLUser$ComputerName$InstanceName group.
Restart SQL Server (INSTANCE) service for your instance

In the case that this doesn't work you may have broken ACLs (permissions) at some level in the SQL Server Program Files structure.

SQL Server Accounts And ACLs

Your service still isn't starting and it could still be an issue with permissions (ACLs)?

Then you might have to fix the file/folder permissions. The full list of SQL Server Account and the required permissions are listed in the following exhaustive documentations:

Setting Up Windows Service Accounts (Microsoft Technet / 2008R2)
Configure Windows Service Accounts and Permissions (Microsoft SQL Docs / 2012)

Everywhere where MSSQLServer is referenced in the documentation, replace with the account you wish to run your SQL Server (INSTANCE) with.

For example when checking the Reviewing Access Control Lists Created for SQL Server Service Accounts section of the document, ensure that your YourServiceAccount has access to:

Service Account for | Files and Folders        | Access
--------------------+--------------------------+---------------
MSSQLServer         | Instid\MSSQL\backup      | Full control
                    | Instid\MSSQL\binn        | Read, Execute
                    | Instid\MSSQL\data        | Full control
....

Instead of MSSQLServer use YourServiceAccount.

References:

Setting Up Windows Service Accounts (Microsoft Technet / 2008R2)
Configure Windows Service Accounts and Permissions (Microsoft SQL Docs / 2016+)


How can I restore a single record from a MySQL backup file? Occasionally we have an issue where a record's previous data might get wiped while editing.

We backup our database every night for issues like this, however sometimes I am not available to do a restore immediately.

I've been asked to create a tool our editors can use to restore a record from a previous backup file.

I imagine this would look a lot like the a Webmin panel, which leads me to believe I may be reinventing the wheel. However, our editors find Webmin difficult to work with, so this isn't a solution for us.

Is anyone familiar with something we can use for the purpose of restoring a single record?

If not, are there any best practices I should abide by?

Restore is overkill.

I'd use history/change tracking within the database/application itselF: then "rollback" becomes a standard day to day business or support actions

This would be with history tables, or Slowly Changing Dimension type 2 tables, to store "old" versions of rows. Perhaps with some schedules clean-up to purge older versions


Postgresql: sort by value position in array column, then by secondary order I'm not quite sure what the best way to phrase this is...
So in my DB there is pillars text array which is basically an enum where providers ordered what values meant the most to their business, from most important to providing that value for their clients, to least important.
I'm using PostGIS to query providers in a specific area, and want to return providers ordered first by the pillar that a client selected they were looking for, then by closest location.
so if the pillars all have values ['a', 'b', 'c', 'd'], in any order depending on what providers selected, and the client selected pillar c
the results of the query would preferably return any/all providers that have pillar c at array index 0 first, ordered by distance to geopoint, then by providers that have pillar c at array index 1 second ordered by distance to client geopoint, then idx 2, then idx 3
I'm really only looking for the top 3 results in all cases, and providers with pillar c at idx 1 would only be needed if there were less than 3 results for index 0
Is this possible to pull off in a single query? or should I just run it with a where clause and check the results length until I have 3 results?
The pillars column is indexed with a gin index btw

That sounds simple:
ORDER BY array_position(pillars, 'c'), st_distance(....)


Getting the employee history of Sales department from HR database in Oracle I am currently using the 'hr' database with Oracle 10g. You can view the schema diagram over here: http://docs.oracle.com/cd/B13789_01/server.101/b10771/diagrams001.htm#sthref63

What I would like is to retrieve the history of all employees who have worked in the 'Sales' department. I have selected only first name, last name, and job title for the working example but I'm getting the same job title where I should be getting two different ones.

This is the query I have so far:

SELECT a.first_name, a.last_name, b.job_title
FROM employees a, jobs b, job_history c, departments d
WHERE d.department_name = 'Sales'
AND c.employee_id = a.employee_id
AND a.job_id = b.job_id
AND c.department_id = d.department_id;

Any help would be appreciated.
JazakAllah khayr.

An introduction to SQL queries but better you look for an introductory SQL text

I assume that every employee is in the job_history table and every employee and therefore job is assigned to a department. Otherwise one will loose some employees.

Let's display the columns of the tables involved:

select employee_id,first_name,last_name,department_id
from employees;

select department_id, department_name
from departments;

select job_id,employeed_id,department_id
from job_history;

select job_id,job_title
from jobs;

First a list of all employees and their departement information

select *
from employee e, departments d
where e.department_id=d.department_id;

now select only the rows from the employees that are in the department 'Sales'

select *
from employee e, departments d
where e.department_id=d.department_id
and d.department_name='Sales';

for these employees now find the jobs have already head fron the job_history

select *
from employee e, departments d, jobhistory h
where e.department_id=d.department_id
and d.department_name='Sales'
and e.employee_id=h.employee_id;

Now for each job find the job title

select *
from employee e, departments d, jobhistory h, jobs j
where e.department_id=d.department_id
and d.department_name='Sales'
and e.employee_id=h.employee_id
and h.job_id=jobs.job_id;

Now we display only the columns we need

select e.first_name,e.last_name,j.job_title
from employee e, departments d, jobhistory h, jobs j
where e.department_id=d.department_id
and d.department_name='Sales'
and e.employee_id=h.employee_id
and h.job_id=jobs.job_id;

If we want to know in which department they worked in their job wee join another department table to get these information

select e.first_name,e.last_name,j.job_title,d2.department_name
from employee e, departments d, jobhistory h, jobs j,departments d2
where e.department_id=d.department_id
and d.department_name='Sales'
and e.employee_id=h.employee_id
and h.job_id=jobs.job_id
and h.depatment_id=d2.department_id;