Using totals on aggregates to improve performance I have two tables: details and totals of these details.

Details (Slow solution):

select
    OrderId             =   r.OrderId
  , TotalQty            =   SUM(r.Quantity)
  , TotalGrossConsid    =   SUM(r.Price * r.Quantity)
from dbo.Order r
group by r.OrderId

Totals (Fast solution):

select
    t.OrderId
  , t.TotalQty
  , t.TotalGrossConsid
  , t.IsValid
from dbo.OrderTotal t

Sometimes totals become invalid (some job has to recalculate changed totals but it delays).
As you understand the second query is faster and the number of valid totals is more then invalid ones.
So I am looking for a combined query that returns valid totals from the second table (totals) and returns dynamically recalculated totals by using the first slow query. So my goal will be reached: all totals are valid and time to response is faster then full recalculation.

Here is my attempt (Hybrid solution):

with fast_static(OrderId, TotalQty, TotalGrossConsid, IsValid)
as
(
    select
        t.OrderId
      , t.TotalQty
      , t.TotalGrossConsid
      , t.IsValid
    from dbo.OrderTotal t
)

, slow_dynamic(OrderId, TotalQty, TotalGrossConsid)
(
   select
        OrderId             =   r.OrderId
      , TotalQty            =   SUM(r.Quantity)
      , TotalGrossConsid    =   SUM(r.Price * r.Quantity)
    from dbo.Order r
)
select
    OrderId, TotalQty, TotalGrossConsid
from fast_static
where IsValid = 1
union all
select
  OrderId, TotalQty, TotalGrossConsid
from slow_dynamic s
    --inner join fast_static ff
    --on ff.OrderId = s.OrderId
where   --ff.Valid = 0 -- too slow!!!
    s.OrderId in (select OrderId from fast_static f where f.Valid = 0)

I have compared the Fast solution and Hybrid one, I got 32% to 68% (relative query costs). If you can see commented variant it equals to  1% to 99% (too bad). Is it possible to improve this query?

ADDED

@gbn:

Valid  =  case when i.OrderId is null then 1 else 0 end
...
dbo.OrderTotal t  left join dbo.InvalidOrders i

Yes, I have a job to recalculate totals and this process is not synchronized to query requests. InvalidOrders tables is a little one that store records to know that totals is invalid (to be recalculated)

SOLUTION

Indexed views are the best choice. Be aware on SQL Server edition (noexpand hint for non-enterprise editions) and be ready to recreate some database objects (SET ANSI_NULLS ON, SET QUOTED_IDENTIFIER ON) to start using indexed views on client side.

I can't understand why you can't aggregate on the fly and why it's so slow. Is the "Valid" idea a workaround to deal with the lag of OrderTotals or some business process

Both of these ideas discard the InvalidOrder table which is a workaround for poor indexing.

Suggestion 1:

Create a computed column

ALTER TABLE dbo.Order ADD PriceXQuantity AS Price * Quantity PERSISTED

Add an index

CREATE INDEX IX_Totals ON dbo.Order (OrderID) INCLUDE Quantity, PriceXQuantity)

See what happens

Suggestion 2:

Use an Indexed view

CREATE VIEW OrderTotals
WITH SCHEMABINDING
AS
select
    OrderId             =   r.OrderId
  , TotalQty            =   SUM(r.Quantity)
  , TotalGrossConsid    =   SUM(r.Price * r.Quantity)
  , COUNT_BIG(*) AS Dummy
from dbo.Order r
group by r.OrderId
GO
CREATE UNIQUE CLUSTERED INDEX IXCU_OrderTotals ON OrderTotals (OrderId9
GO

You can use the computed column here too


Is it possible to restore database only with .trn files? How do I restore database only with .trn (transaction log backup) file?

I did below two steps Microsoft SQL Server Management studio shows "Restoring".

1)

RESTORE DATABASE [database_name]
  FROM DISK = N'path\file_name.bak'
  WITH MOVE N'logical_filename' TO N'path\filename.mdf',
       MOVE N'logical_filename_log to N'path\filename.ldf' REPLACE,
       NORECOVERY;
GO

2)

RESTORE LOG [database_name]
  FROM DISK = N'path\filename.trn'
  WITH NORECOVERY;
GO

Run this:

RESTORE LOG [database_name] FROM DISK = N'path\filename.trn' WITH RECOVERY; GO

Notice I've changed it to WITH RECOVERY - this runs the recovery process and puts the database in a usable state.

Running WITH NORECOVERY leaves the database in a state where further log files can be restored.  This state prevents you using the database until you do a RESTORE LOG ... WITH RECOVERY


mysql "can't create test file" error on Centos When starting mysql on Centos servers with a custom data directory you might get an error similar to the following:

2014-10-15 10:23:56 17081 [Warning] Can't create test file /database/mysql/localhost.lower-test

If your chmod and chown settings are already correct you might need to look into selinux.
This page explains how to configure selinux for mysql:
https://blogs.oracle.com/jsmyth/entry/selinux_and_mysql


Postgres 9.6: tune for one time import I'm using pgloader to perform a one-time migration from MySQL to Postgres.

For that purpose, I want to temporarily configure Postgres specifically for that workload.

reduce WAL as much as possible: wal_level = minimal
archive_mode = off
Should I autovacuum = off ?

What else can I do to improve things without severely putting my data at risk?

Ee.g. I don't want to fsync = off. Although I can re-do this migration, this additional time would still be unwanted.

Additional setting after Feedback

synchronous_commit=off (thanks @a_horse_with_no_name )

I found that the following changes to postgres.conf yielded the best results so far:

archive_mode            = off       # old: on
autovacuum              = off       # old: on
commit_delay            = 100000    # old: 0
maintenance_work_mem    = 1GB       # old: 512MB
max_wal_size            = 10GB      # old: 1GB
synchronous_commit      = off       # old: on
wal_level               = minimal   # old: archive
work_mem                = 1GB       # old: 8MB

Also I had quite trouble getting the most out of a single pgloader invocation to migrate all the table. This would have been the most desired way, because pgloader that way also re-creates all the FK between the imported tables.

But the problems I was facing (pgloader hanging, not achieving the performance I was aiming for in terms of total time) I settled with this approach:

run a separate pgloader instance for every individual table, in parallel, inside tmux
using tmux wait-for the longest import migration
run a separate *.sql script for (almost each) table for post-processing (add FKs, add triggers), again in parallel inside a tmux
run vacuumdb --jobs=24 --analyze

To fit this purpose, the machine in question had been pimped:

14 cores (instead of 8)
64GB RAM (instead of 32GB; but they will eventually stay)

Also, for each individual pgloader invocation I had a seperate loader config with individual batch size configuration. The default batch size of 25000 wouldn't work for the bigger tables.

In the end, for my purpose and workload, this cut down the time

from 4h38m (1h11m importing, 1h41m adding FKs, 1h22 vacuuming)
to 2h8m6s (74m30s importing, 25m50s adding FKs, 27m44s vacuuming)

(Actually, the latest numbers are slightly below 2h even, but I don't have more detailed stats for them)


How to turn JSON array into Postgres array? I have a column data of type json that holds JSON documents like this:

{
    "name": "foo",
    "tags": ["foo", "bar"]
}

I would like to turn the nested tags array into a concatenated string ('foo, bar'). That would be easily possible with the array_to_string() function in theory. However, this function does not accept json input. So I wonder how to turn this JSON array into a Postgres array (type text[])?

Postgres 9.4 or newer

Obviously inspired by this post, Postgres 9.4 added the missing function(s):
Thanks to Laurence Rowe for the patch and Andrew Dunstan for committing!

json_array_elements_text(json)
jsonb_array_elements_text(jsonb)

To unnest the JSON array. Then use array_agg() or an ARRAY constructor to build a Postgres array from it. Or string_agg() to build a text string.

Aggregate unnested elements per row in a LATERAL or correlated subquery. Then original order is preserved and we don't need ORDER BY, GROUP BY or even a unique key in the outer query. See:

How to apply ORDER BY and LIMIT in combination with an aggregate function?

Replace 'json' with 'jsonb' for jsonb in all following SQL code.

SELECT t.tbl_id, d.list
FROM   tbl t
CROSS  JOIN LATERAL (
   SELECT string_agg(d.elem::text, ', ') AS list
   FROM   json_array_elements_text(t.data->'tags') AS d(elem)
   ) d;

Short syntax:

SELECT t.tbl_id, d.list
FROM   tbl t, LATERAL (
   SELECT string_agg(value::text, ', ') AS list
   FROM   json_array_elements_text(t.data->'tags')  -- col name default: "value"
   ) d;

Related:

What is the difference between LATERAL and a subquery in PostgreSQL?

ARRAY constructor in correlated subquery:

SELECT tbl_id, ARRAY(SELECT json_array_elements_text(t.data->'tags')) AS txt_arr
FROM   tbl t;

Related:

How to apply ORDER BY and LIMIT in combination with an aggregate function?

Subtle difference: null elements are preserved in actual arrays. This is not possible in the above queries producing a text string, which cannot contain null values. The true representation is an array.

Function wrapper

For repeated use, to make this even simpler, encapsulate the logic in a function:

CREATE OR REPLACE FUNCTION json_arr2text_arr(_js json)
  RETURNS text[] LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
'SELECT ARRAY(SELECT json_array_elements_text(_js))';

Make it an SQL function, so it can be inlined in bigger queries.
Make it IMMUTABLE (because it is) to avoid repeated evaluation in bigger queries and allow it in index expressions.
Make it PARALLEL SAFE (in Postgres 9.6 or later!) to not stand in the way of parallelism. See:

When to mark functions as PARALLEL RESTRICTED vs PARALLEL SAFE?

Call:

SELECT tbl_id, json_arr2text_arr(data->'tags')
FROM   tbl;

db<>fiddle here

Postgres 9.3 or older

Use the function json_array_elements(). But we get double quoted strings from it.

Alternative query with aggregation in the outer query. CROSS JOIN removes rows with missing or empty arrays. May also be useful for processing elements. We need a unique key to aggregate:

SELECT t.tbl_id, string_agg(d.elem::text, ', ') AS list
FROM   tbl t
CROSS  JOIN LATERAL json_array_elements(t.data->'tags') AS d(elem)
GROUP  BY t.tbl_id;

ARRAY constructor, still with quoted strings:

SELECT tbl_id, ARRAY(SELECT json_array_elements(t.data->'tags')) AS quoted_txt_arr
FROM   tbl t;

Note that null is converted to the text value "null", unlike above. Incorrect, strictly speaking, and potentially ambiguous.

Poor man's unquoting with trim():

SELECT t.tbl_id, string_agg(trim(d.elem::text, '"'), ', ') AS list
FROM   tbl t, json_array_elements(t.data->'tags') d(elem)
GROUP  BY 1;

Retrieve a single row from tbl:

SELECT string_agg(trim(d.elem::text, '"'), ', ') AS list
FROM   tbl t, json_array_elements(t.data->'tags') d(elem)
WHERE  t.tbl_id = 1;

Strings form correlated subquery:

SELECT tbl_id, (SELECT string_agg(trim(value::text, '"'), ', ')
                FROM   json_array_elements(t.data->'tags')) AS list
FROM   tbl t;

ARRAY constructor:

SELECT tbl_id, ARRAY(SELECT trim(value::text, '"')
                     FROM   json_array_elements(t.data->'tags')) AS txt_arr
FROM   tbl t;

Original (outdated) SQL Fiddle.
db<>fiddle here.

Related:

Need to select a JSON array element dynamically from a postgresql table

Notes (outdated since pg 9.4)

We would need a json_array_elements_text(json), the twin of json_array_elements(json) to return proper text values from a JSON array. But that seems to be missing from the provided arsenal of JSON functions. Or some other function to extract a text value from a scalar JSON value. I seem to be missing that one, too.
So I improvised with trim(), but that will fail for non-trivial cases ...


Install MariaDB 10 on Ubuntu without prompt and no root password Pretty much the same as this question but I don't want to have a root password (this is just a dev machine).

Here's what I've got:

export DEBIAN_FRONTEND=noninteractive
sudo debconf-set-selections <<< 'mariadb-server-10.0 mysql-server/root_password password PASS'
sudo debconf-set-selections <<< 'mariadb-server-10.0 mysql-server/root_password_again password PASS'
sudo apt-get install -y mariadb-server

This will install MariaDB silently but it will set the root password to "PASS". If I delete that it does a weird partial install because it's still trying to prompt me.

In the link that Dimitar provided, the question itself hints at a solution - if you're putting this in a script, you could add the SET PASSWORD line with an empty password.

export DEBIAN_FRONTEND=noninteractive
sudo debconf-set-selections <<< 'mariadb-server-10.0 mysql-server/root_password password PASS'
sudo debconf-set-selections <<< 'mariadb-server-10.0 mysql-server/root_password_again password PASS'
sudo apt-get install -y mariadb-server
mysql -uroot -pPASS -e "SET PASSWORD = PASSWORD('');"

That said, I recommend that you keep using passwords, even for dev environments. As suggested on a related serverfault question, you could add lines to your my.cnf which contain the password, meaning that you can still simply fire up mysql by typing 'mysql':

[client]
user = root
password = s3kr1t


Why is the processor % different in Activity Monitor vs Resource Monitor? I'm tracking a significant difference between the processor % reported in the Activity Monitor for SQL Server vs the total processor % used in the Resource Monitor.  I'm seeing about a 50% difference between the two with the Activity Monitor always being higher.

System Specs:

SQL Server EE 2008 R2
Windows 2008 R2
Bare metal
96 cores w/no processor affinity set (4 Physical Sockets with 12 cores each w/HyperThreading enabled)
768 GB of memory
Active/Passive cluster
Direct attach storage of SSDs

What would cause such a difference?

Which one should I be looking at?

Update:

I see that resource monitor has it split between different categories however wouldn't the "CPU - Total" graph show all CPU usage on the machine?

The Task Manager Performance tab is showing the same difference between it and Activity Monitor.

Activity Monitor is on a 1 sec refresh.

Could there be an issue with Activity Monitor not seeing that each core is HyperThreaded?  Not sure how as SQL Server sees all 96 cores.

Update 6/28/2018

Even after migrating to AWS and updating the OS to Server 2012 R2 the SQL Server 2008 r2 Activity Monitor is still dramatically off from what Task Manager shows.  The below screen shot is from a load test that was just performed.  There is a visible 20% difference between the two metrics.  Even when Task Manager is reporting the overall CPU just over 55% the SQL Activity Monitor is pegged at 100%.  Metrics from the AWS Cloud watch show the same results as Task Manager so I have two data metrics that correlate with a third that isn't even in the ballpark.

Updated System Specs:
System Specs:

SQL Server EE 2008 R2
Windows 2012 Datacenter R2
i3.16xl - Dedicated Host
64 cores w/no processor affinity set (2 Sockets with 32 cores each)
488 GB of memory
Synchronous Mirroring
ENA Enabled w/EBS Optimization enabled

Resource monitor shows CPU from Processes and Services on two different graphs. (In your example, 32% + 37%, which means a total 69% CPU).

As for SQL Activity Monitor, it shows CPU utilization for the machine on which SQL is installed. (In your example, 64%, which is not that far from Resource monitor.)

I use a query to get the CPU utilization on my server. I run a job on my server and raise an alarm if I get an average over 90% for the last 10 minutes.

Here's my query:

DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info);

-- Top 10 : for the last 10 minutes
SELECT TOP(10)  100 - SystemIdle as TotalCPU,
           DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]
FROM (
  SELECT record.value('(./Record/@id)[1]', 'int') AS record_id,
        record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')
        AS [SystemIdle],
        record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]',
        'int')
        AS [SQLProcessUtilization], [timestamp]
  FROM (
        SELECT [timestamp], CONVERT(xml, record) AS [record]
        FROM sys.dm_os_ring_buffers
        WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
        AND record LIKE '%<SystemHealth>%') AS x
  ) AS y
ORDER BY record_id DESC;


How to Drop tables being a SYS user I am not being able to drop my tables using :

drop table <table_name>;

command. It says "the table is referencing primary keys of some other tables as foreign keys."
, and when I tried to drop foreign key constraints using :

alter table <table_name> drop constraint <constraint_name>;

statement. It says: "cannot drop constraint",  and when I tried to drop column itself on which foreign key constraint was applied, using:

alter table <table_name> drop <column_name>;

statement. It says: "cannot drop column owned by SYS table.". Somebody told me, SYS doesn't has the privileges to drop columns especially when foreign keys constraint are present, and he suggested me to make new User account apart from default SYS and customise all the privileges. Making new user is very lengthy process and I read many articles of making a new user already, but it doesn't help , coz, I've done the installation by choosing the option of "install database software only" and then later created and configured Database using DBCA(Database configuration Assistant ). I might have skipped certain steps or didn't look it properly while using DBCA and just kept the default settings.

Creating a new user requires you to use the CREATE USER privilege and the syntax given in the oracle documentation to create user is :

CREATE USER sidney
IDENTIFIED BY out_standing1
DEFAULT TABLESPACE example
QUOTA 10M ON example
TEMPORARY TABLESPACE temp
QUOTA 5M ON system
PROFILE app_user
PASSWORD EXPIRE;

This is the example I copied from the oracle documentation. Now the problem I am facing is with the PROFILE keyword in the above statement. I don't know what value to fill for PROFILE keyword and moreover the documentation suggests :

"Oracle recommends that you use the Database Resource Manager rather SQL profiles to establish database resource limits. The Database Resource Manager offers a more flexible means of managing and tracking resource use."

Now, when I started looking for Database Resource Manager, I came accross various steps such as:

To administer the Resource Manager in Enterprise Manager:

1) Access the Database Home page.

and it says: The Database Home page is the main database management page in Oracle Enterprise Manager Database Control (Database Control).

It asks to confirm certain steps to access the "DataBase Home Page" such as :

1.) Ensure that the dbconsole process is running on the database host computer.

I checked the dbconsole process by running the following statement on the command prompt :

C:\oracle_base\product\11.2.0\dbhome_1\BIN>./emctl status dbconsole

It showed a warning on the command prompt saying :

"OC4J configuration issue . C:\oracle_base\product\11.2.0\dbhome_1\oc4j\j2ee\OC4J_DBConsole_rubbalbhusri-PC_orcl not found."

Note : rubbalbhusri-PC is my system-name(the name I defined for my Operating System)

Now, my question is this :

Is "OC4J_DBConsole_rubbalbhusri-PC_orcl" directory created during installation itself ?

Why, I don't have one( "OC4J_DBConsole_rubbalbhusri-PC_orcl" directory ) ?

What should I do to have one( "OC4J_DBConsole_rubbalbhusri-PC_orcl" directory ) ?

Please don't tell me to install the whole "oracle database 11g" again.

Tell me some repair, which solves this problem, if any ?

You can use DROP TABLE <schema_owner>.<table_name> CASCADE CONSTRAINTS to drop the referential integrity too. Notice that there must be a reason to have a referential constraint in this table. Check it twice before drop. (I recommend to read documentation first http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9003.htm )

SYS is a special user, has some restrictions (like objects owned by SYS cannot be exported), but can alter any object in the database, including droping or alter some DB engine objects. That's why (and many other reasons) you should NEVER create user objects in SYS schema.
Objects created in SYS schema has a different treatment, and that's why you couldn't drop the column (or constraint, but I've never tried this).

I can say that the source of the problem was not the DROP TABLE syntax. The problem is that your objects where created in SYS schema ;). Oracle was designed to create and manipulate user objects within user's schemas.
It's like if you have a hammer and try to use it grabbing it by the head. It's not effective , It wont work as expected and you could brake it.

The good practice is to create USERS, and then create objects within user's schema.
You can create a new user just with:

CREATE USER <username> IDENTIFIED BY <password> ;

This will create a User (and a Schema), but it wont have any privileges. Logged in as SYS you can create and manipulate objects in this (and any) user schema, but this user will never be able to connect nor create objects. This are system privileges that can be easily granted with the roles CONNECT (just grant the CREATE SESSION privilege) and RESOURCE (grant a bunch of privilege to create several DB objects in his own schema):

GRANT CONNECT TO <username>;
GRANT RESOURCE TO <username>;

With this, you'll have a user with the DEFAULT profile (search in documentation for User Profiles) which can log in and can create user objects in his schema. A typical oracle db user.

These are all Oracle Administration basics. I've recommend to read the 2 Day DBA, DB Concepts, and DB Administrator's Guide documentation. All available at http://www.oracle.com/pls/db112/homepage (or, easy to remember, http://tahiti.oracle.com )

Regards.

-- edit- The OC4J_DBConsole_SID is related with EM configuration. I wouldn't mess around with this until you dominate Oracle's concepts. A GUI won't teach you the basis and it will progressively make you dependent of it.


Fault tolerance for Database sharding and Database partitioning I'm aware that database sharding is splitting up of datasets horizontally into various database instances, whereas database partitioning uses one single instance.
In Database Sharding, what if one of the database crashes? we would lose that part of the data completely. We won't be able to read or write on it. I'm assuming we are keeping a replica of all the databases we have shared? Is there any better approach? That would be too expensive, I believe if we have many database instances.
In Database partition, we could create a replica of the main database (that would be just one replica) since data partition splits dataset in the same database.
One last question would be, why would we go for a master-slave approach? Do the slaves have complete data or are the data partitioned among the slaves? I believe that the Master database has complete data, but I'm not sure about the slaves? If the slaves have different data partitioned, let's say, how would the fault tolerance. Would it just read from the Master database then?
I know these are a lot of questions. Could you please help me? I am interested in this and that's why I have so many questions, which I am not able to grasp.

Sharding is for write scaling.  And, yes, each shard has part of the data.  (It might have part of the biggest table.  Meanwhile, smaller tables might be manually kept in sync across the shards.  Or you might have other servers for other things.)
Replication (Master(s) + Replica(s)) has a complete copy of the data on each server.  One Master + one Replica provides some degree of HA, but the "failover" is likely to be manual.
Each shard could have a Replica for HA purposes.  Or you could use a cluster (InnoDB Cluster or Galera) for each shard.  That would give you a combination of read scaling, a little write scaling, and a lot of HA.
When using Master+Replica, all writes go to the Master.  "Critical reads" need to go to the Master, too.  Other reads can go to the Replica.  Since you can have an arbitrary number of replicas, reads can scale "infinitely".  (Booking.com had over 100 replicas a few years ago; I don't know how many today.)
A cluster has at least 3 nodes.  It can be one Master and the rest as replicas, or it can be all Masters.  With 3 (or more) they can vote on what to do if one machine goes dark.  Then those in the majority continue running until you can fix the broken node.
Of course, if you put the Master and all its replicas (or all cluster nodes) in a single datacenter, you run the risk of a failure of all of them.  (Yes, DCs do go down.)
PARTITIONing is rarely of any use.  (I list 4 use cases here:  http://mysql.rjweb.org/doc.php/partitionmaint )
(Please switch from the term "Slave" to "Replica".)


Alias of Subquery not working I have been trying to get an order by to work correctly with an aggregate function.

i.e. order by (bp + points)

I also try adding these as a total amount from the following query however. If I try to add the two columns together I get an error that the field bp can not be found.

If I try to sort by the addition of these fields it doesn't sort correctly and just orders by points

query

SELECT COUNT(game.game_id) AS correct_picks,
       sum(game.points) points,
       pick.user_id,

       (select sum(game.points)
        FROM xf_nflj_pickem_pick pick
        LEFT JOIN xf_nflj_pickem_game game
        ON game.game_id = pick.game_id
        WHERE game.bonus=1 AND pick.user_id = user.user_id
            and game.week_id <= 8 AND game.winner = pick.team_id
        Group by user.user_id) as bp,

       (bp + points) as total,

       user.user_id,
       user.username,
       user.avatar_date,
       user.avatar_width,
       user.avatar_height,
       user.gravatar,
       user.gender
FROM xf_nflj_pickem_game game
    LEFT JOIN xf_nflj_pickem_pick as pick
        ON (pick.game_id = game.game_id)
    LEFT JOIN xf_user as user
        ON (user.user_id = pick.user_id)
WHERE (game.winner = pick.team_id)
    AND game.week_id <= 8
    AND game.pool_id = 1
    AND pick.team_id <> 0
GROUP BY pick.user_id
ORDER BY (points + bp) DESC, user.username, pick.user_id ASC

Results if I just use the order by

correct_picks | points| user_id| bp|
16            | 16    | 845    | 3 |
14            | 14    | 1698   | 3 |
16            | 16    | 109    | 3 |
19            | 19    | 26787  | 3 |

so there are lots of random results here rather than being ordered by by the following

User_id, 26787, 845, 109, 1698

Try this:

SELECT correct_picks, points, pick_user_id, bp, (bp + points) AS total, user_user_id, username, avatar_date, avatar_width, avatar_height, gravatar, gender
FROM
(
SELECT COUNT(game.game_id) AS correct_picks,
       SUM(game.points) points,
       pick.user_id AS 'pick_user_id', -- Alias to avoid same name with user.user_id

       (SELECT SUM(game.points)
    FROM xf_nflj_pickem_pick pick
    LEFT JOIN xf_nflj_pickem_game game
    ON game.game_id = pick.game_id
    WHERE game.bonus=1 AND pick.user_id = user.user_id
        AND game.week_id <= 8 AND game.winner = pick.team_id
    GROUP BY user.user_id) AS bp,

       user.user_id AS 'user_user_id', -- Alias to avoid same name with pick.user_id
       user.username,
       user.avatar_date,
       user.avatar_width,
       user.avatar_height,
       user.gravatar,
       user.gender
FROM xf_nflj_pickem_game game
    LEFT JOIN xf_nflj_pickem_pick AS pick
    ON (pick.game_id = game.game_id)
    LEFT JOIN xf_user AS USER
    ON (user.user_id = pick.user_id)
WHERE (game.winner = pick.team_id)
    AND game.week_id <= 8
    AND game.pool_id = 1
    AND pick.team_id <> 0
GROUP BY pick.user_id
) tbl1 -- Alias for the subquery as table
ORDER BY (points + bp) DESC, username, pick_user_id ASC;

Made your query into a table(as a subquery) then used the ORDER BY (points + bp) DESC, username, pick_user_id ASC.

It seems like mysql does not allow using the aliases of the column to be used in the same line or hierarchy as the aliased column.


ORA-08102 while creating a view I am learning PL/SQL programming. I'm trying to create a view with the following statement:

create or replace view viewName as select ID from employees;

The view creation throws the following error:

ORA-00604 error occurred at recursive level 1
ORA-08102 Index not found, obj #39 , file 1 block 61248

How can I fix it?

The problem is with all objects, procedures,functions,views and i am using the HR Database of Oracle 11G for learning purposes.

File 1 belongs to the SYSTEM tablespace.
Objects with such low id as 39 are dictionary objects. For example in my database it is an index of OBJ$ (I_OBJ4). ORA-08102 also points us towards an index, which seems to be corrupt based on the error you get.

Find the index name by:

select object_name from dba_objects where object_id = 39;

Unfortunately you will not be able to rebuild this index (ORA-00701), but you can recreate it:

shutdown immediate
startup upgrade
alter session set nls_length_semantics=byte;
@?/rdbms/admin/utlmmig


MySQL views and table locking (5.1 MyISAM) I have a large (160 million rows, 50GB) table in MySQL 5.1 using MyISAM.

If I were to construct a view defined by a fairly complicated query, would a SELECT on the view lock the underlying table?

Keep in mind that SELECTs and UPDATEs are mutually exclusive for MyISAM. That's is to say...

UPDATEs, DELETEs, and INSERTs require a full table lock. SELECTs are blocked.
SELECTs prevent subsequent UPDATEs, DELETEs, and INSERTs from acquiring a full table lock.

Using either SELECT or UPDATE automatically mitigate MyISAM locking behavior, anyway. So, the answer to your actual question would be yes since we are dealing with MyISAM.
I would recommend you use LOCK TABLES on the view. Why ?
According to the MySQL Documentation

For view locking, LOCK TABLES adds all base tables used in the view to the set of tables to be locked and locks them automatically. If you lock a table explicitly with LOCK TABLES, any tables used in triggers are also locked implicitly, as described in Section 13.3.5.2, “LOCK TABLES and Triggers”.

Based on this, you should explicitly run LOCK TABLES on the view. The locks are performed on all the underlying tables then. That way, all the tables are locked as a group.
SUGGESTIONS

Make the all the underlying tables InnoDB if you want and if you can.
INSERTs to the underlying MyISAM tables can still happen if concurrent_insert is set to 2.


How To insert records in SqlExpress Database tables through sqlscript while installing setup? I'm creating a database and all of my tables using SQL Server Express through SQL scripts.  Now, I want to insert some records in tables like States (all states names), city (all city names).

How do I make a SQL script for this?  I want to add this code to my sqlscripts after creating the database.  Alternatively, is there a way to restore database tables by sql scripts?

If you have a script that generates your database and all of the objects, you can just append to the end whatever DML you are looking to accomplish.  For instance, if you generated a script for your entire database that contains all of the DDL, just open up that file and at the end of it put something like this:

insert into States
(
   Col1,
   Col2,
   Col3,
   -- etc...
   ColN
)
values
(
   "Col1Val",
   "Col2Val",
   "Col3Val",
   "ColNVal"
),
(
   "Col1Val2",
   "Col2Val2",
   "Col3Val2",
   "ColNVal2"
)

insert into City
(
   -- your column list
)
values
(
   -- your values
)

Likewise, you can just have a separate script file and use SQLCMD to run your DDL script and then have a separate script containing your INSERTs and run that script afterwards.  Here is a link on how to do this with SQLCMD.


In case of multiple updates of the same record in a single transaction, how many versions are being stored? According to MSDN article by Kimberly L. Tripp, Neal Graves's "SQL Server 2005 Row Versioning-Based Transaction Isolation

"... All the earlier versions of a particular record are chained in a linked list; and in the case of long-running row versioning–based transactions, the link will have to be traversed on each access to reach the transactionally consistent version of the row"

which is in the section "Understanding Row Versioning" common for following both "Row Versioning in Read Committed Using Row Versioning" and "Row Versioning in Snapshot Isolation" sections.

Further on the example of row-versioning due to modifications is given in context of multiple updates of the same record by multiple transactions (T1, T2, T3) in SNAPSHOT only.

In case only one transaction updates a record multiple times (by multiple statements), will multiple version stores be stored (linked) or only the one taken at the moment of the SNAPSHOT "image" taken?

Well, the answer to this question should immediately answer my other pending related questions:

Why do row-versioning isolations use a linked list of versions of record?
Does RCSI use less or same space than SNAPSHOT? Why?

In case of multiple updates of the same record in a single transaction, how many versions are stored?

Just one.

The first update to the row generates a row version and exclusively locks the row. Later updates to the same row within the same transaction do not generate new row versions.

A linked list of row versions can arise as follows:

Transaction T1 in a SNAPSHOT transaction reads a row and sees the value 'a'.
Transaction T2 (under any isolation level) updates the value from 'a' to 'b' and commits. This generates a row version for the value 'a'.
Transaction T3 (under any isolation level) updates the value from 'b' to 'c' and commits. This generates a row version for the value 'b'. This row version links to the previous row version for 'a'.
Transaction T1 reads the row value by traversing the link from the value stored on the current page ('c'), to the row version 'b', and then on back to row version 'a'.

Row versions are generated by data changes regardless of whether there is an existing reading transaction (at any isolation level). The versions may never be needed, but they are still generated - the same linked list of versions is created even without transaction T1.


ms sql query multiple table colums as identical tag I am using SQL Server Express 2017

My Table looks like this

+-------------+
| ID | Name   |
+-------------+
| 1  | Bert   |
| 2  | Agnes  |
| 3  | Diane  |
+-------------+

I'm looking for this kind of query result:

<MyRootElement>
  <MyTag ID='1' Name='Bert' />
  <MyTag ID='2' Name='Agnes' />
  <MyTag ID='3' Name='Diane' />
</MyRootElement

Can anyone help me, please?

Thanks in advance!

You need to use FOR XML clause at the end. You can specify each row's node with PATH and the root with ROOT. Also the having a column alias with @ at the start with move the value to an attribute.

;WITH YourTable AS
(
    SELECT
        V.ID,
        V.Name
    FROM
        (VALUES
         (1, 'Bert'),
         (2, 'Agnes'),
         (3, 'Diane')
         ) V(ID, Name)
)
SELECT
    [@ID] = T.ID,
    T.Name AS '@Name'
FROM
    YourTable AS T
FOR XML
    PATH('MyTag'),
    ROOT('MyRootElement')

Result:

<MyRootElement>
  <MyTag ID="1" Name="Bert" />
  <MyTag ID="2" Name="Agnes" />
  <MyTag ID="3" Name="Diane" />
</MyRootElement>

You can find all the XML options on the docs. This particular PATH mode is explained here.


Am I violating any NF rule on my database design? I am a newbie on creating database... I need to create it for my recruitment web application.

My application needs to schedule screenings, exams and interviews of applicants and save result in the database..

My database schema is as follows:

My problem is I included the applicant_id on other tables... e.g. exam, interview, exam type.

Am I violating any normalization rule?  If I do, what do you recommend to improve my design?

There are few things to consider other than normalization.  For instance, you have a column for AGE.  Are you going to update that every year? How will you know when to do that? The same goes for years of experience.

There are some columns that will probably have multiple values for each applicant: School, Course, etc.

You may also want to check your optionality on those relationships.  Right now an applicant must have a related exam, but an exam does not have to have an applicant associated with it. I'm guessing that's backwards to how things work in real life.  You have similar issues with all the other relationships.

It helps if you read out the relationships after you create them.


Sqlite: strange behavor on index , about group by operation CREATE TABLE [newtable](
  [id],
  [HLevel],
  [LeftBower],
  [RightBower],
  [NodeNumber],
  [NodeCount],
  [SortPath],
  [ID:1] INT,
  [parent] INT,
  [name] TEXT,
  [folder] NUM,
  [size] INT);

CREATE TABLE [tally]([N]);

This is the index I used:

explain query plan select pid ,newtable.size from (
select id as pid, leftbower as lft, rightbower as rgt from newtable where parent =1 and folder = 1
)
as base join newtable on newtable.id> base.lft and newtable.id<base.rgt

output:
id  parent  notused detail
5   0   0   SEARCH TABLE newtable USING INDEX vdsew (folder=?)
12  0   0   SEARCH TABLE newtable USING INDEX dd (id>? AND id<?)

but with group by:
explain query plan select pid ,newtable.size from (
select id as pid, leftbower as lft, rightbower as rgt from newtable where parent =1 and folder = 1
)
as base join newtable on newtable.id> base.lft and newtable.id<base.rgt group by pid;

output:
id  parent  notused detail
8   0   0   SCAN TABLE newtable
10  0   0   SEARCH TABLE newtable USING INDEX vdsew (folder=?)
23  0   0   USE TEMP B-TREE FOR GROUP BY

As rediculous consequence, If I want to get the group by result, create a temp table to save the result, then select from temp group by. This 2-step takes only about 200ms and the combination takes much much longer, depending on what index I am using. For instance, in this case(the indices I posted here).
This query:
create table temp as select pid ,newtable.size from (
select id as pid, leftbower as lft, rightbower as rgt from newtable where parent =1 and folder = 1
)

as base join newtable on newtable.id> base.lft and newtable.id<base.rgt ;
select pid, sum(size) from temp group by pid;

takes only 250ms to get the result, while
select pid ,sum(size) from (
select id as pid, leftbower as lft, rightbower as rgt from newtable where parent =1 and folder = 1
)

as base join newtable on newtable.id> base.lft and newtable.id<base.rgt
group by pid

takes very very long time, depends on which Index I used, and got the same out. In this case, it freezes forever.(It is because it use folder index. After I delete folder index, the query will use parent index and the time is about 14seconds. Anyway, this is another question. Let's focus why the behavoir of using indecies by  group by operation is so strange first.

It's probably because your index doesn't include the size field so it doesn't cover it so when aggregating over size with a GROUP BY the operation cost determined by the query optimizer thinks using a TEMP B-TREE is the better choice. See the Temporary Sorting B-Trees section of the "EXPLAIN QUERY PLAN" doc.
You can create an index on newtable that includes the size field as well and re-check the EXPLAIN. More information on a similar issue in this DBA.StackExchange answer.


Doing two counts on a single table in a single query There are some similar questions/answers on the forums but I think my problem is simpler.

I have two quesries, eg

SELECT count(*) FROM agent;
SELECT count(*) FROM agent WHERE active = 't';

I would like the output to be on a single row.  For bonus points it would be nice to scan through the table only once and update both counters, to get a result like this

active_agents | total_agents
--------------+--------------
    10        |     20

So I guess I have two questions:
What is the Neatest/clearest way to do this, and
What is the fastest way to do it for very large tables.

You can use CASE statement for this scenario:

SELECT count(*) AS total_agents,
       SUM(CASE WHEN active = 't' THEN 1 ELSE 0 END) AS active_agents
FROM Agent;

Sample execution with sample data:

CREATE TABLE Agent (Id INT, Active CHAR(10));

INSERT INTO Agent (Active)
VALUES ('a'), ('b'), ('t'), ('c'), ('t'), ('d');

SELECT count(*) AS total_agents,
       SUM(CASE WHEN active = 't' THEN 1 ELSE 0 END) AS active_agents
FROM Agent;


CAUTION:  Before implementing any solution that duplicates column values into new rows, consider a properly normalized table schema.  Normalized tables eliminate duplicated data and have appropriate primary and foreign keys linking multiple tables together (rather than trying to force all the data into one table).  Duplicating and saving values into multiple rows of a table creates a headache of having to keep the rows in sync, or later suffer from mismatched data in rows that should contain the same values.  It is always possible with normalized tables to produce multiple rows as desired WHEN NEEDED using appropriate JOIN queries

The best approach to producing the expanded rows is to use another (temporary) table.  For example, create a table named [TempTable] with the following values:

Col3
----
 1
 2
 3

Now do a CROSS JOIN between the two tables.  A cross join produces the Cartesian product of all rows in both tables.  This means it returns every possible combination of rows from each table.  (For Access [and I think for any other standard SQL relational database system] the default behavior is to always produce the Cartesian product for all rows which are not otherwise constrained by another JOIN.)  In Access SQL, CROSS JOINS are accomplished by listing tables without specifying any join type--a comma-separated list of tables:

SELECT DT.Col1, DT.Col2, TT.Col3
FROM DataTable As DT, TempTable As TT
WHERE DT.Col3 Is NULL
ORDER BY DT.Col1, DT.Col2, TT.Col3

That query will simply return the expanded set of rows, but it will not update the original table.

If you insist on saving the new rows to the original table, you can do that with a couple update queries based on the previous query.

Notice that I added the condition WHERE DT.Col3 Is NULL to avoid duplicating rows which have already been processed.

Save the previously listed query as a named query, for example [Expanded].
Add new rows for [Col3] >= 2 using the following INSERT command:

INSERT INTO DataTable ( Col1, Col2, Col3)
SELECT Expanded.Col1, Expanded.Col2, Expanded.Col3
FROM Expanded
WHERE Expanded.Col3 >= 2

Update original rows with 1 -> [Col3] using the following UPDATE command:

UPDATE DataTable SET DataTable.Col3 = 1
WHERE DataTable.Col3 Is Null;


Installing SQL Server 2008 R2 Express on Server 2012 - Could not load file or assembly I'm trying to install SQL Server 2008 R2 Express Edition SP2 on Windows 2012 server.

The error is:

Any clue?

Its complaining about the userSettings section of the Microsoft.SqlServer.Configuration.LandingPage.Properties.Settings file

Run below command :

rd /s /q %localappdata%\Microsoft_Corporation

Refer connect item and here for more details on resolving it.


Condition evaluation with NULL or '' I am having this strange effect from evaluating truthiness of a field

IF ( a IS NOT NULL && ((b IS NOT NULL) || (NOT b = '')) && c IS NOT NULL ) THEN
   set NEW.results = "Passed"
ELSE
   set NEW.results = "Failed"
END IF;

Basically, if a,b,c are not null (or empty) then pass otherwise fail.

When there is no data for b, the field is set to NULL and the condition returns "Failed"

BUT

When no data is an empty string ('') the condition returns "Passed"

What am I missing here?

UPDATE:
@akina pointed out what my logic was delivering, which was not desired.

I adjusted the condition logic to the proper condition:

( NOT ((v_sectionnumber IS NULL) || (v_sectionnumber = '')))

which gave the desired results.

As per W3Resource documentation MySQL OR operator
MySQL OR operator compares two expressions and returns TRUE if either of the expressions is TRUE.

Syntax:

OR, ||

When more than one logical operator is used in a statement, OR operators perform after AND operator. The order of evaluation can be changed by using parentheses.

The operator returns 1 when both operands are a non-NULL and one of them is nonzero and returns 0 when both operands are non-NULL and one of them is zero and returns NULL when one operand is NULL and other is zero and return 1 also when one is NULL and another operand is nonzero and NULL also when both operands and NULL.

Example

The following MySQL statement satisfies the condition - "both operands are a non-NULL and one of them is nonzero", so it returns 1.

SELECT 5 || 5;

MySQL> SELECT 5 || 5;
+--------+
| 5 || 5 |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

Example of MySQL OR operator with at least one (zero) 0

The following MySQL statement satisfies the condition - "both operands are non-NULL and one of them is zero", so it returns 1.

SELECT 5 || 0;

MySQL> SELECT 5 || 0;
+--------+
| 5 || 0 |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)

Example of MySQL OR operator with at least one NULL operand

The following MySQL statement satisfies the condition - "one operand is NULL and other is zero", so it returns NULL.

SELECT 0 || NULL;

MySQL> SELECT 0 || NULL;
+-----------+
| 0 || NULL |
+-----------+
|      NULL |
+-----------+
1 row in set (0.01 sec)

Example of MySQL OR operator with NULL and non-zero operand

The following MySQL statement satisfies the condition - "one operand is NULL and other is non-zero", so it returns 1.

SELECT 5 || NULL;

MySQL> SELECT 5 || NULL;
+-----------+
| 5 || NULL |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)


Using data from other database to populate a new database I have a SQL Server database DB1 and want to move data from DB1 to DB2. But only want some of the data to be moved.

I want all the rows from DB1 that have a catParent that is the same as I Id from DB2. And I only want the DB1.catDescription = DB2.Name and DB1.catLink = DB2.URL

How do I manage that? Do I need a sub select query inside my insert query and how is the syntax for this?

DB1

CREATE TABLE [dbo].[category](
    [catID] [bigint] IDENTITY(1,1) NOT NULL,
    [catDescription] [varchar](50) NOT NULL,
    [catParent] [bigint] NULL,
    [catSortOrder] [bigint] NOT NULL,
    [catLink] [varchar](255) NOT NULL,
    [catShow] [tinyint] NULL,
    [catTarget] [varchar](3) NOT NULL,
    [catAssign] [varchar](3) NOT NULL,
 CONSTRAINT [primary key] PRIMARY KEY CLUSTERED
(
    [catID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

DB2

CREATE TABLE [dbo].[Categories](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Guid] [uniqueidentifier] NOT NULL,
    [Name] [nvarchar](max) NOT NULL,
    [Description] [ntext] NULL,
    [AssignArticles] [bit] NULL,
    [URL] [nvarchar](max) NULL,
    [Target] [int] NOT NULL,
    [Publish] [bit] NOT NULL,
    [ParentCategoryId] [int] NOT NULL,
    [Sort] [int] NOT NULL,
    [RequireLogin] [bit] NOT NULL,
    [LevelVal] [int] NULL,
    [PageLayoutId] [int] NOT NULL,
    [M03PictureId] [int] NOT NULL,
    [Sitemap] [bit] NOT NULL,
    [Search] [bit] NOT NULL,
    [LinkText] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

This should do it.

update DB2.[dbo].[Categories]
set
    DB2.[dbo].[Categories].Name = DB1.[dbo].[category].catDescription,
    DB2.[dbo].[Categories].URL = DB1.[dbo].[category].catLink
from
    DB2.[dbo].[Categories] inner join
    DB1.[dbo].[category] on DB2.[dbo].[Categories].ID = DB1.[dbo].[category].catParent


SQL Job to be run earlier on last work day of month Right now, I have a SQL job (within SQL Server 2008 R2) that dumps a complex select statement into a table every weeknight (excluding weekends) and it works great.

Due to our company's Month End procedures, they require the data in this table for that day when running Month End. Right now I fire the job off manually, disable it so the same data isn't dump twice for the same day then enable the job again the following business day.

Is there a way to schedule this? Or script a job and have it depict whether or not it's the last work day of the month and depict what time to fire based on this parameter?

If this is not possible, then so be it I suppose... I haven't been able to find anything that might help point me to such a solution thus far. Any help is appreciated though!

EDIT: Here's a sample result set of a view that I'll using for the last work day of the month comparison (as you can see, I only have Work Days selected):

    CalendarDate:           WorkDay:
    2015-03-02 00:00:00.000        1
    2015-03-03 00:00:00.000        1
    2015-03-04 00:00:00.000        1
    2015-03-05 00:00:00.000        1
    2015-03-06 00:00:00.000        1
    2015-03-09 00:00:00.000        1
    2015-03-10 00:00:00.000        1
    2015-03-11 00:00:00.000        1
    2015-03-12 00:00:00.000        1
    2015-03-13 00:00:00.000        1
    2015-03-16 00:00:00.000        1
    2015-03-17 00:00:00.000        1
    2015-03-18 00:00:00.000        1

You can write this more tersely (e.g. without any variables at all), but I thought logically breaking it up might be more helpful.

DECLARE @Today DATE = SYSDATETIME();

DECLARE @FirstDayNextMonth DATE = DATEADD(MONTH, 1,
 DATEADD(DAY, 1-DAY(@Today), @Today));

DECLARE @LastWorkDayThisMonth DATE;

SELECT @LastWorkDayThisMonth = MAX(CalendarDate)
  FROM dbo.CalendarView -- guessing on name here
  WHERE WorkDay = 1
  AND CalendarDate >= @Today
  AND CalendarDate < @FirstDayNextMonth;

IF @LastWorkDayThisMonth = @Today
BEGIN
  -- do your last work day of the month stuff
END

(Also, I suggest changing your calendar view to expose DATE and not DATETIME.)


Multi-row insert vs multiple single row inserts In my app I do multi-row inserts when I can just because it reduces the number of round trips between the db and the app.

However, I was curious, is there any other advantages? For example, if multiple rows are inserted at once like this:

insert into tbl (c1, c2) values
(v1, v2)
(v3, v4)

versus:

insert into tbl (c1, c2) values (v1, v2)
insert into tbl (c1, c2) values (v3, v4)

and the table has an index, is the index calculated once in the first case, and twice in the second case? Or is it always once per insert? Assume that both queries are in the same transaction.

I'm using PostgreSQL.

I think @a_horse_with_no_name is right about that the index is updated once per statement, because if the statement has not completed its execution the data will not be visible since it is in transaction. And the definition of a statement includes having multiple values

And accoriding to the docs here index creation/update works more efficient with batches than single statements.

There is also a nice article from Peter Manis about the performance of various Insert methods which I can recommend.

Another thing to keep in mind is the FILLFACTOR of the index as it does have an impact on performance as Fabien Coelho describes here.


How to rename MySQL users without logging into MySQL? I wanted to rename the default Linux username on a Lubuntu 16.04 VM (from osgeo live) from user to newuser. I followed answers in this question to rename the Linux user. But after renaming, I cannot log into mysql, and mysql generates the following error:

mysql ERROR 1045 (28000): Access denied for user 'newuser'@'localhost' (using password: NO)

My guess is that there is a default MySQL user created by the VM creators which is the same as the Linux user (i.e. user). My question is:

How can I change the MySQL username to newuser) to be consistent (now that I cannot log in)?

-- Update --

I executed sudo service mysql restart --skip-grant-tables --skip-networking as suggested (after deleting /var/log/mysql/error.log) and the log has the following diagnosis:

2017-01-12T02:25:23.664857Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2017-01-12T02:25:23.664951Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
2017-01-12T02:25:23.804276Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-01-12T02:25:23.805216Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.16-0ubuntu0.16.04.1) starting as process 2574 ...
2017-01-12T02:25:23.808154Z 0 [Note] InnoDB: PUNCH HOLE support available
2017-01-12T02:25:23.808182Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-01-12T02:25:23.808186Z 0 [Note] InnoDB: Uses event mutexes
2017-01-12T02:25:23.808189Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2017-01-12T02:25:23.808192Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.8
2017-01-12T02:25:23.808194Z 0 [Note] InnoDB: Using Linux native AIO
2017-01-12T02:25:23.808365Z 0 [Note] InnoDB: Number of pools: 1
2017-01-12T02:25:23.808440Z 0 [Note] InnoDB: Using CPU crc32 instructions
2017-01-12T02:25:23.809445Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2017-01-12T02:25:23.815490Z 0 [Note] InnoDB: Completed initialization of buffer pool
2017-01-12T02:25:23.816764Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2017-01-12T02:25:23.828144Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2017-01-12T02:25:23.860141Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2017-01-12T02:25:23.860201Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2017-01-12T02:25:23.903728Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2017-01-12T02:25:23.904430Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2017-01-12T02:25:23.904442Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2017-01-12T02:25:23.904815Z 0 [Note] InnoDB: Waiting for purge to start
2017-01-12T02:25:23.955251Z 0 [Note] InnoDB: 5.7.16 started; log sequence number 2632377
2017-01-12T02:25:23.956218Z 0 [Note] Plugin 'FEDERATED' is disabled.
2017-01-12T02:25:23.963845Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2017-01-12T02:25:23.968294Z 0 [Note] InnoDB: Buffer pool(s) load completed at 170112  3:25:23
2017-01-12T02:25:23.972653Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2017-01-12T02:25:23.972715Z 0 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
2017-01-12T02:25:23.972741Z 0 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
2017-01-12T02:25:23.972799Z 0 [Note] Server socket created on IP: '127.0.0.1'.
2017-01-12T02:25:24.006451Z 0 [Note] Event Scheduler: Loaded 0 events
2017-01-12T02:25:24.007120Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.16-0ubuntu0.16.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
2017-01-12T02:25:24.686565Z 2 [Note] Access denied for user 'root'@'localhost' (using password: NO)

You will have to due it the sneaky way

STEP 01 : Restart mysqld without grants and with no one being able to login behind you

$ service mysql restart --skip-grant-tables --skip-networking

STEP 02 : Login to mysql passwordless

$ mysql

STEP 03 : Locate all users in mysql you want to change

EXAMPLE: Suppose the user in question is olduser and you want to changed to newuser

Run this

mysql> SELECT user,host FROM mysql.user WHERE user='olduser';

to user all users run this

mysql> SELECT user,host FROM mysql.user;

You can change the username with the following

mysql> UPDATE mysql.user SET user='newuser' WHERE user='olduser';

STEP 04 : Leave mysql when you have changed all users you needed to change

mysql> exit

STEP 05 : Restart mysqld normally

$ service mysql restart


SysAdmin server role and Active Directory Groups I've been struggling to find best practices information or any advice regarding Active Directory Groups that provide group members with the SysAdmin server role. I'm at a new job where I'm the only SQL Server DBA for 50 - 100 instances of SQL. There is one other (network) system administrator that will have the SysAdmin server role. There may be others in the future as well (may or may not be DBA's).

I'm picturing two options:

Option 1: create a group in Active Directory, add myself and any others to the group as needed, create SQL Server logins on each SQL Server instance for the AD group, and give the logins the SysAdmin server role.

This is convenient, and allows some flexibility. But it seems like a huge security risk. As a DBA, I don't have full control over who is in (or out) of the AD group. Currently, each SQL Server instance has a handful of logins with the SysAdmin role. I want to take SysAdmin away from all of them except for me, the network system admin, and sa. If I accomplish this task and implement Option 1, I end up no better than where I started from.

Option 2: use individual Windows Authentication (AD account) logins on each SQL Server and give each the SysAdmin server role.

This is not so convenient, but only two people/AD logins have SysAdmin privileges. As long as neither of us grant SysAdmin role membership to others, there should be a certain level of order.

Which option is preferred?
Are there other options I've overlooked?

Individual accounts are going to be much harder to administer.  They give you more granular control over access, but you can not keep out your Active Directory administrators.   Anyone who is granted local administrator rights on your server can gain sysadmin access to the instance.  And, as a very good DBA told me once, if you can't trust your domain admins you've got bigger problems on your hands.

By abstracting your access to AD groups, you gain two benefits:

Easier to manage access groups and rights based on roles, combined with stratified access control for other assets using those AD groups.
Provide more consistent auditing, as you will only need to show access by AD group and then who is contained within that group.


MySQL: Do killed query results get cached? If a query is executed from the MySQL command line, then KILLed it while it is running will the query and its partial results* be in the query cache and usable to subsequent runs of the identical query?

For simplicity's sake, let's say this is a plain old SELECT with no subqueries.

The list of exclusions on this page covers cases in which queries will not be cached such as the queried table changing between executions, and certain operations that are by nature not identical, but I do not see interrupted or killed queries listed anywhere. It must not be that a killed query is treated the same as one that ran to completion, but if it met all of the other criteria for caching it would already be cached* before it got killed, so I'm wondering what happens next.

MySQL 5.6

*I'm making several assumptions here.

Yes and no.

The Query cache will not see, much less keep, the partial result.  The page you referenced is talking only about the Query cache.  The QC is populated only after the complete result is built.  This gives a significant boost for a repeat of the identical query.  (Similar queries are not helped any by the QC.)

InnoDB's buffer_pool (and the equivalent in MyISAM) caches whatever blocks are read from disk.  Blocks that were read from disk before the killed was process will probably still be in RAM.  This will provide some performance boost for repeated executions of the same or similar queries, whether killed or not.


SMSS 2014 Network Server browser is empty I trying to setup a MSSQL 2014 environment and I installed SQL Server Management tools for a dedicated management computer to reach database server nodes easily without starting this resource-hungry stuff on servers itself.

However, on the management node, if i click on "Browse more..." in server address dropdown and i click on "Network Servers" tab, the listing is empty.
Checking it from a database server node, the tab filled correctly.

What things have to running correctly to be SMSS able to populate the listing of the servers (and instances) in the local network?

All computers are a part of the AD domain, the management node is one of the spare DC's (the machine has no other role than being DC, and only SQL Server management tools are installed), the two other node are a member server.

Windows Firewall set to allow any incoming conection from Domain zone, and adapters are configured to become a member of Domain zone, so i think firewall cannot be a problem.

Network server discovery via SQL Browser Service is done via UDP broadcast. Broadcasts are subject to local network segment rules (as in network mask, default gateway etc), so it may be that your server is on a different segment and the broadcast does not reach the desired servers. If that is not the case then the obvious culprit would be Firewall rules.


What replica in Availability Group should System Databases be backed up Just wondering what is the best practice for backing up system databases in Availability Group i.e should it be backed up in Primary, secondary or in all replicas please. Thanks in advance

Since they are independent of each other, backup all system databases, for each individual instance hosting a replica.


SQL optional where clauses without exponentially large number of only slightly different queries I'm working with postgres in python and I want to write a clear query that optionally allows WHERE clause subsetting, but doesn't require  me writing an exponential explosion of whether the subsetting where clauses exist or not.  Specifically, I have the following:

query = """
select *  -- in reality, something more complicated
from myTable t
WHERE
  t.first_id in :args1 AND
  t.second_id in :args2
"""

I would like to duplicate as little as possible, and for this query to return sensibly:

When args1 is null, and args2 is null
When args1 is null, and args2 has 1 argument
When args1 is null, and args2 has n arguments
When args1 has 1 argument, and args2 is null
When args1 has 1 argument, and args2 has 1 argument
When args1 has 1 argument, and args2 has n arguments
When args1 has n arguments, and args2 is null
When args1 has n arguments, and args2 has 1 argument
When args1 has n arguments, and args2 has n arguments

Where args1 can be:

args1 = []
args1 = ["a"]
args1 = ["a", "b"]

...for example, and similar for args2.

As you can see above,

   (1) is effectively no WHERE clause
   (2) is effectively remove the first argument and the AND
   (2) Would also likely want an equality operator = 'a', rather than an in ('a')

...etc.

For 2 clauses and 3 options, we have 3^2 = 9 different queries to write.  If we add another 1-2 where clauses, this quickly
becomes overwhelming and duplicates a lot of code for what is effectively the same problem. While I can write each of these queries out and switch case on them, I suspect this is a syntax SQL can do for me, and that has possibly been answered a thousand times on stackoverflow but I can't seem to find.

I'm using sqlalchemy in python if that changes anything.

Given that the phrase column IN () is not valid SQL syntax, I do not believe there is a way to do this with SQL. If there is any SQL syntax to do this, it is going to be exceptionally convoluted, likely lead to sub-optimal query plans, and bloat your queries by requiring you to specify the same argument list multiple times -- once to check the argument list in SQL, and once to actually use it.

In Postgres, the database should not treat a single-item IN clause differently than an equality clause. Rewriting a single-item IN clause as an equality clause gains you nothing.

Since you are using SQLAlchemy already, you can conditionally add these conditions to your query based on what is in the argument lists:

# your values need to be tuples for Postgres to properly
# convert them to lists for IN clauses; Python lists will convert
# to Postgres ARRAY types
args1 = ("value",)
args2 = ()

# the "1=1" is an always-true check so that you can
# specify a WHERE clause, even with no provided arguments
clauses = ["1=1"]
if len(args1) > 0:
    clauses.append("t.first_id IN :args1")
if len(args2) > 0:
    clauses.append("t.second_id IN :args2")
...

query = """
SELECT *  -- in reality, something more complicated
FROM myTable t
WHERE
""" + " AND ".join(clauses)

result = conn.execute(text(query), args1=args1, args2=args2, ...).fetchall()

SQLAlchemy does not care if you provide unused parameters. In other words, if args2 is empty, passing it to execute() will work here, even though the query will not have a placeholder for it.

If you are using the SQLAlchemy Core API for generating queries, you can chain your where() clauses in a similar way:

s = select([myTable])
if len(args1) > 0:
    s = s.where(myTable.c.first_id.in_(args1))
if len(args2) > 0:
    s = s.where(myTable.c.second_id.in_(args2))
...
result = conn.execute(s).fetchall()


Using inner join on a specific record? So we have 2 tables

users:

id | username | email
1  | testuser | test@test.com
2  | dirk     | test2@test2.com

data:

id | data         | username
1  | somedata     | dirk
3  | somemoredata | testuser

I want to select the username of the data table with the id 3 and then select his email. Which is the nicest way to do this with an inner join?

You can get the result by joining your two tables on the username column, then you can use a WHERE clause to filter for the row with id = 3:

select d.id, d.username, u.email
from data d
inner join users u
  on d.username = u.username
where d.id = 3;

See SQL Fiddle with Demo


Increase speed altering column on large table to NON NULL I recently added a NULL-able bit column to a table which has close to 500 million rows.  There's not a default on the column, however all inserts are specifying a value of 0 or 1, and I ran a one-time routine to assign 0 or 1 to all existing rows (updating the rows in small batches).  Every row should now have a 0 or 1 in that column.

I want to make the bit column non-nullable however when I tried doing so via ALTER TABLE t1 ALTER COLUMN c1 bit not null, it started running for 3 minutes and I stopped it because it was blocking all reads to the table and I suspected it was going to take a long time to complete.  It's possible it wouldn't take too long but I couldn't risk too much unavailability.  The rollback itself took 6 minutes.

Do you have any suggestions on how I can make the column non-nullable without it taking potentially hours to complete?  Additionally is there any way to estimate how long the ALTER TABLE ALTER COLUMN statement I started and then cancelled would take to complete?

I am using SQL Server 2017 Web Edition.

Instead of changing the column definition you could add a CHECK CONSTRAINT that doesn't allow NULLs for that column. The table will still need to be scanned but it won't need to modify every single data page, so it should be a much faster operation. Regrettably, a Sch-M lock will still be held during the operation. One trick is to try to get as much of the table into the buffer pool as possible before attempting to add the constraint. That may reduce the amount of time the Sch-M lock is held.

You can then remove the constraint and change the column definition during your next maintenance window.


A average Mark overall for all of one student subjects Lets say  i have a student table , a enlistment table that shows SubjectId and Results for each student  how would i get the overall average mark for a student that has more than 3 subjects
Create table  a_Student(
StudentId int(5) Not null,
StudentForename varchar (30) Not null,
StudentSurname varchar (45) Not null,
PRIMARY KEY(StudentId)
);
Create table a_Enlistment(
StudentId int(5) Not null,
SubjectId char(6) Not null,
results char (2),
primary key (SubjectId,StudentId),
foreign key (SubjectId) references a_Subject(SubjectId),
foreign key (StudentId) references a_Student(StudentId)
);

insert into a_Student (StudentId,StudentForename,StudentSurname)
values ("33447","Alan","Burns"),
("33672","Mark","Harrison",);

 Insert into a_Enlistment (StudentId,SubjectId,Results)
values
        ("33447","TMP243","78"),
         ("33447","AWD169","45"),
         ("33447","IIA887","48"),
        ("33672","TMP243","57"),
       ("33672","AWD169","67"),
      ("33672","IIA887","48"),

So i want lets say Alans overall mark for his 3 subjects  how would i do that?
Any help is welcomed thanks a lot

you can use a subquery to get the result for average and the number of subjects

Create table  a_Student(
StudentId int(5) Not null,
StudentForename varchar (30) Not null,
StudentSurname varchar (45) Not null,
PRIMARY KEY(StudentId)
);

Create table a_Enlistment(
StudentId int(5) Not null,
SubjectId char(6) Not null,
results char (2),
primary key (SubjectId,StudentId),
#foreign key (SubjectId) references a_Subject(SubjectId),
foreign key (StudentId) references a_Student(StudentId)
);

insert into a_Student (StudentId,StudentForename,StudentSurname)
values ("33447","Alan","Burns"),
("33672","Mark","Harrison");

 Insert into a_Enlistment (StudentId,SubjectId,Results)
values
        ("33447","TMP243","78"),
         ("33447","AWD169","45"),
         ("33447","IIA887","48"),
        ("33672","TMP243","57"),
       ("33672","AWD169","67"),
      ("33672","IIA887","48"),
      ("33672","test","52")

✓

SELECT s.*
,avgresults
FROM a_Student s INNER JOIN (SELECT
StudentId,
AVG(Results) avgresults,
COUNT(*) countr FROM a_Enlistment
GROUP BY StudentId) e ON s.StudentId = e.StudentId
WHERE countr > 3

StudentId | StudentForename | StudentSurname | avgresults
--------: | :-------------- | :------------- | ---------:
    33672 | Mark            | Harrison       |         56

db<>fiddle here


Want to match two date columns of two tables both have diff formats in mysql One column format is like '2005-03-22' and other is like '2005-03-22  22:09:01'.. i tried to_date function but its not working in mysql..which function should i use?

Found it... it will be date_format(date , '%Y- %m- %d')


Fixing a Mistake in SQL While troubleshooting an issue in my company I updated all the files in the table instead of the specific file that needed to be tested using.
create table _file_*date*_BAK as select * from _file;
update _file set category = 'null';
commit;

When trying to fix it I tried to pull from my backup file I got the error
update (select a.category as OLD, b.category as NEW

 from _file a inner join _file_*date*_BAK b on a.filecod = b.filecod) t

 set t.old = t.new;

Error report -
SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table
01779. 00000 -  "cannot modify a column which maps to a non key-preserved table"
*Cause:    An attempt was made to insert or update columns of a join view which
           map to a non-key-preserved table.
*Action:   Modify the underlying base tables directly.

not sure why that script didn't work.
Any help would be appreciated

The error is saying that there’s a chance that Oracle will end up updating the same row twice because there is no unique constraint on _file_*date*_BAK.filecod.
The simple solution is to create the unique index on your logical backup table:
Create unique index _file_*date*_bak_uix on _file_*date*_BAK (filecod);

And then running the update statement exactly as you wrote it already.
This answer is now version dependent though as of Oracle 19.10. If you are sufficiently patched, Oracle will allow you to execute this update statement and only fail if you have data which would cause a single row to be updated multiple times. This behaviour currently needs to be manually enabled with fix control, either at your session level or for the system (talk to your DBA):
Alter session set "_fix_control"='19138896:1';

I expect this will become default behaviour in a future major release.


0xDBE how to connect over local socket? I want to connect to mysql over socket.

This is what I'm trying to do:

and it's not working.

This is how it works in mysql workbench:

Your host is wrong, to use the socket it must be "localhost", not the socket address.

The url for JDBC to use a socket for connection is:

jdbc:mysql://localhost:3307/my database name?socket=/home/aspect_mkn8rd/tmp/mysql.sock

(leaving out here the definition of a user and password, as your software seems to require it apart, it would be &user=my user&password=my password)


B-tree node split strategy in SQL Server for monotonically increasing value Consider a B-tree index on a value that will always increase monotonically, e.g. a column of type IDENTITY. With a conventional B-tree implementation, whenever a node is full, it will be split 50%/50% and we end up with a B-tree in which (almost) all nodes will be only 50% full.

I know that Oracle discovers when a value is ever-increasing and in these cases Oracle performs a 90%/10% split instead. That way, (almost) all nodes will be 90% full and a far better page utilization is obtained for these, quite common, cases.

I have not been able to find documentation for a similar feature in SQL Server. However, I have performed two experiments in which I inserted N random integers, and N consecutive integers in an index, respectively. The former case used far more pages the latter.

Does SQL Server provide a similar functionality? If so: can you point me to some documentation on this feature?

UPDATE:
It seems, by the experiments provided below, that leaf nodes are kept un-splitted and internal nodes are split 50%/50%. That makes B-trees on increasing keys more compact than on random keys. However, the 90%/10%-approach by Oracle is even better, and I still look for some official documentation that can verify the behavior seen in experiments.

If it is adding a row at the end of the index it will just allocate a new page for the row rather than split the current end page. Experimental evidence for this is below (uses the %%physloc%% function which requires SQL Server 2008). See also the discussion here.

CREATE TABLE T
(
id int identity(1,1) PRIMARY KEY,
filler char(1000)
)
GO

INSERT INTO T
DEFAULT VALUES
GO 7

GO
SELECT sys.fn_PhysLocFormatter(%%physloc%%)
FROM T

GO

INSERT INTO T
DEFAULT VALUES

GO

SELECT sys.fn_PhysLocFormatter(%%physloc%%)
FROM T
GO

DROP TABLE T

Returns (Your results will vary)

(1:173:0) /*File:Page:Slot*/
(1:173:1)
(1:173:2)
(1:173:3)
(1:173:4)
(1:173:5)
(1:173:6)
(1:110:0) /*Final insert is on a new page*/

This does only appear to apply to leaf nodes though. This can be seen by running the below and adjusting the TOP value. For me 622/623 was the cut off point between requiring one and two first level pages (might vary if you have snapshot isolation enabled?). It does split the page in a balanced manner leading to wasted space at this level.

USE tempdb;

CREATE TABLE T2
(
id int identity(1,1) PRIMARY KEY CLUSTERED,
filler char(8000)
)

INSERT INTO T2(filler)
SELECT TOP 622 'A'
FROM master..spt_values v1,  master..spt_values v2

DECLARE @index_info  TABLE
(PageFID  VARCHAR(10),
  PagePID VARCHAR(10),
  IAMFID   tinyint,
  IAMPID  int,
  ObjectID  int,
  IndexID  tinyint,
  PartitionNumber tinyint,
  PartitionID bigint,
  iam_chain_type  varchar(30),
  PageType  tinyint,
  IndexLevel  tinyint,
  NextPageFID  tinyint,
  NextPagePID  int,
  PrevPageFID  tinyint,
  PrevPagePID int,
  Primary Key (PageFID, PagePID));

INSERT INTO @index_info
    EXEC ('DBCC IND ( tempdb, T2, -1)'  );

DECLARE @DynSQL nvarchar(max) = 'DBCC TRACEON (3604);'
SELECT @DynSQL = @DynSQL + '
DBCC PAGE(tempdb, ' + PageFID + ', ' + PagePID + ', 3); '
FROM @index_info
WHERE IndexLevel = 1

SET @DynSQL = @DynSQL + '
DBCC TRACEOFF(3604); '

EXEC(@DynSQL)

DROP TABLE T2


When is autoclose OK to be not OK By chance, I happened to see that the autoclose feature on SQL dbs (I'm using SQL 2008) shouldn't be on and I found that all of my SQL dbs were set to have autoclose on.

I've set autoclose to off on them all (I have about 400 dbs of varying sizes on my SQL Server instance - biggest db probably 350MB), but the memory allocation on my SQL Server process is now on 1,807,252k when its usually about 1,100k.

Is this normal?  Should I go back to autoclose on?

Should the system ones (eg master, model, etc) have their autoclose set to off as well?  This might have already been like this, but I'm not 100% sure.

In general, everyone that gives advice on this says to never enable auto-close because it just causes unnecessary delay when a user queries the database after it's been closed, along with a spike in resource utilization when a database has to come online.  And in general, that is golden advice for databases that are used on a daily basis.

However, when you get outside the theoretical perfect environment where every production database is really in production, it may make sense to enable auto-close in some instances if the following conditions are met:

There is a large number of databases that contain only archive data that is queried infrequently (less than once per day, for example).
The users are aware that there will be some delay the first time they query the database after it's been idle for a while, and they are OK with that.

Due to data retention policies requiring the data to stay around long after it's no longer used on a daily basis, and the struggle to get business units to actually decommission database they aren't using, it is certainly reasonable that auto-close might make sense in some cases.  If it can lower your hardware resource requirements and still meet the users' needs, it may be something to seriously, but cautiously, consider.

As for the memory allocation difference cited, there is no way to be sure what caused the difference without more information.


strange situation with very large partitioning table I have a very strange situation here...

On my SQL Box there is a large Table this table is partitioned, everything was fine until I forget to make new partition ranges. in the first picture the last partition has a boundary 201205110000.

All Data after 201205120000 is before the last boundary 201205110000, now i thought I can make the new boundary ranges, but the fi case is, If I make the next boundary 201205120000 sql server copy all data from 201205110000 to the boundary 201205120000, this makes for me sense, but I can’t do this because my transaction log becomes full. also the specific file, in this case file_20120512, is growing.

If thought I can create the last boundary 201206040000, then 201206030000, but if I do this SQL Servers  tran log runs full but the specific data file file_20120604 does not growing but the tran log …

What can I do?

So you have a huge partition at the end because you forgot to create new empty partitions. You could do this (will be an offline operation):

drop all unaligned indexes
create an empty staging table, with identical structure as your data table (including all aligned indexes) with the proper constraints to accept the last partition
switch the last partition into the staging table. now the data has moved into the staging table, the last partition of the real table is empty
split the partitioning function as needed, since the last partition is empty the operations will be instantaneous. Remember to leave an empty last partition
create a temporary partitioning scheme for the staging table aligned with the partitions in the tail of the real table
rebuild the staging table on the temporary partition scheme created above. Now the staging table is aligned with the empty partitions in the tail of the real table. If the rebuild has log space issues use an online rebuild and take frequent log backups. consider also enabling bulk_logged mode.
switch each partition one by one from the staging table into the real table.
add back any unaligned index you need (you shouldn't have unaligned indexes to start with if you ask me...)
drop the staging table and temp partitioning function/scheme
start maintaining the partitioning so you don't have to repeat this next time.


Query records that chain together based on date gaps I have a table in SQL Server 2012 with the below simplified schema and I need to define a query that grabs the most recent record and then returns the chain of records that have less than 30 days of a gap between them. Once there is a gap of more than 30 days the chain is broken and does not continue. Overlaps should continue the chain. Is there a way to solve this without resorting to something slow and recursive?

Schema

ID          (int)
Arrival     (datetime)
Departure   (datetime)

Sample data

ID Arrival       Departure
1 2009-04-19    2009-04-28
2 2009-04-27    2009-05-21
3 2009-06-11    2009-07-01
4 2009-07-19    2009-07-24
5 2004-04-19    2004-04-21
6 2009-02-28    2009-03-05

Desired Query Result

ID Arrival       Departure
4 2009-07-19    2009-07-24
3 2009-06-11    2009-07-01
2 2009-04-27    2009-05-21
1 2009-04-19    2009-04-28

The following solution (SQL Fiddle) uses the SQL Server 2012LAST_VALUE function:

SELECT
    MaxDiff.ID,
    MaxDiff.Arrival,
    MaxDiff.Departure
FROM
(
    -- Maximum difference encountered so far
    SELECT *,
        MaxDiff = LAST_VALUE(Diff.Diff)  OVER (
            ORDER BY Diff.Departure DESC
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    FROM
    (
        -- Difference between departure and prior arrival
        SELECT *,
            Diff =
                ISNULL(DATEDIFF(DAY, r.Departure,
                        LAST_VALUE(r.Arrival) OVER (
                            ORDER BY r.Departure DESC
                            ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)), 0)
        FROM dbo.Records AS r
    ) AS Diff
) AS MaxDiff
WHERE
    MaxDiff.MaxDiff <= 30
ORDER BY
    MaxDiff.Departure DESC;

Results:

╔════╦════════════╦════════════╗
║ ID ║  Arrival   ║ Departure  ║
╠════╬════════════╬════════════╣
║  4 ║ 2009-07-19 ║ 2009-07-24 ║
║  3 ║ 2009-06-11 ║ 2009-07-01 ║
║  2 ║ 2009-04-27 ║ 2009-05-21 ║
║  1 ║ 2009-04-19 ║ 2009-04-28 ║
╚════╩════════════╩════════════╝

Execution plan:


How to group by continuous days in mysql I have table where I am storing some sequential data that indicate occurrences of some event given day.

id     from_id        to_id   date
1      2              1       2014-08-01
2      2              1       2014-08-02

3      2              1       2014-08-04
4      2              1       2014-08-05
6      2              1       2014-08-06

Sometimes event hasn't got place. Then I would like to get min and max from every uninterrupted sequences of records by data. According to example data it should return

from_id        to_id   min            max
2              1       2014-08-01     2014-08-02
2              1       2014-08-04     2014-08-06

If I would do simple group by with min and max

Select from_id, to_id, min(date), max(date) from events group by from_id, to_id

it would return

from_id        to_id   min            max
2              1       2014-08-01     2014-08-06

How can I do that in MySQL?

SELECT from_id,to_id,
  CONCAT_WS(' - ',
    MIN(dt),
    CASE WHEN MAX(dt)>MIN(dt) THEN MAX(dt) END
  ) As intervals
FROM (
  SELECT from_id,to_id,
    CASE WHEN dt=@last_ci+INTERVAL 1 DAY THEN @n ELSE @n:=@n+1 END AS g,
    @last_ci := dt As dt
  FROM
    t, (SELECT @n:=0) r
  ORDER BY
    dt
) s
GROUP BY
  g

FIDDLE


How Automatic Failover Works (AlwaysOn) I'm going to use AlwaysOn technology on my servers, but it's some unclear points in the documentation:

Automatic failover looks like

Primary replica (A) unaccessable
Secondary replica (B) becomes first replica
When A become available it sets to secondary replica and get all changes from primary replica (B)
???

in documentation is written that:

  Later, when the server instance that is hosting the former primary replica restarts, it recognizes that another availability replica now owns the primary role. The former primary replica transitions to the secondary role, and its databases become secondary databases. The new secondary replica connects to the current primary replica and catches its database up to the current primary databases as quickly as possible. As soon as the new secondary replica has resynchronized its databases, failover is again possible, in the reverse direction.

Does it mean that former primary replica (A) will become primary replica automatically? Or it (A) will be secondary and failover in reverse direction must be done manually?

Thanks for your help

Does it mean that former primary replica (A) will become primary replica automatically?

No, all that means is when your replica comes back into the picture, and when the Availability Group database gets back in a SYNCHRONIZED state that it would be failover ready.  That operation will not happen automatically.  You indeed would have to either do this "failback" manually, or engineer a way to automate this (rather simple, let me know if you want to explore those options).

From a high-level view, your listing of steps is complete after step #3.


Prevent Deadlocks in Vendor Databases We have a Windows Update (WSUS) database that is used by Windows Update (obviously). A couple of times a day we will receive deadlocks on this database caused by the WSUS application running 2 update queries on the same record. The 2 queries come from the same process on one server.

We have tried changing the database to read committed snapshot, however that hasn't improved the situation. The WSUS process isn't reporting any errors, and the sys admin team is happy with how it's running.

Is there anything we should do to try and fix this, or should we just ignore the alerts for deadlocks on that database?

UPDATE: Deadlock has happened again. See below for the graph details:

For some reason stack exchange won't let me post the xml here.

As you are on SQL Server 2014, you can simply harvest historical deadlocks from the system_health Extended Events (XE) session and do some analysis.  It should be running all the time and will contain deadlocks since that last SQL Server service restart, although the ring_buffer could have cycled and ejected some older records.  Try code from here or here.  This
No need for Trace flag 1222 and no need to run Profiler / server side trace with the Deadlock Graph event which can only capture deadlocks from the time you start them / turn the trace flag on.  You can just look back in time.

As the WSUS database is relatively public domain (and not your secret business logic database) you could publish some of the deadlock XML here and we can help with the investigation.  Deadlocks are sometimes due to missing indexes, or if there are excessive exchange events then might be down to parallelism.  Let's have a look!


Open source tools to manage Data Quality Are there open source (or commercial) tools that can report data quality issues in a data warehouse using the Kimball star schema model?  It doesn't have to be the silver bullet.  I'm just looking for something to start with that can give basic details of data quality.

DataCleaner is pretty good (spoken like Larry David)

http://datacleaner.org/

Integrates w Pentaho Data Integration (Kettle) too


Transaction log growing on a mirrored principal I have a pair of SQL Server 2008 R2 servers hosting a synchronously mirrored database with no witness. This weekend, we had to do some network maintenance so I paused the mirroring. Upon resuming, the principal's status reverted to the usual Principal, Synchronized. The mirror, however, now reads Mirror, Synchronized / Restoring... I don't recall the Restoring... part usually being there.

The transaction log has begun to grow on the principal, as well, despite hourly log backups. This suggests to me that the synchronization process isn't freeing up transaction log space.

What's going on here and how do I fix it?

Mirror, Synchronized / Restoring... is the typical state for the mirror instance.

You can determine why the log file is growing by issuing the following command in a SQL Server Management Studio query window:

SELECT name, LOG_REUSE_WAIT_DESC
FROM sys.databases
WHERE name='your_database_here';

This will show several possible choices, such as:

NOTHING
CHECKPOINT
LOG_BACKUP
ACTIVE_BACKUP_OR_RESTORE
ACTIVE_TRANSACTION
DATABASE_MIRRORING
REPLICATION
DATABASE_SNAPSHOT_CREATION
LOG_SCAN
OTHER_TRANSIENT

You may need to perform at least one transaction log backup, among other actions depending on the LOG_REUSE_WAIT_DESCRIPTION shown in the query above.  For instance, there may be a long-running transaction that is forcing the log files to not be re-used.


What minimum permissions do I need to provide to a user so that it can check the status of SQL Server Agent Service? I have created a user and granted many permissions and roles to it including SQLAgentUserRole and SQLAgentReaderRole.

My application checks the status of the SQL Server Agent to see if it is running or not, and whenever I am trying to run my application using the user I have created it is not able to identify a Running SQL Server Agent.

When I do the same thing using a user in the sysadmin role it works fine.

We are using master..xp_servicecontrol to check status of SQL Agent Service and I already provided user with

GRANT EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER TO User;

Still it is not working. Any help?

What minimum permissions do I need to provide to a user so that it can check the status of SQL Server Agent Service?

NONE :-)

Starting in SQL Server 2005, Microsoft provided a mechanism to infer permissions via a "proxy". The proxy for the permissions is either a Login (for Server-level permissions) or a User (for Database-level permissions). In either case, the principal (server or database) is created from either an Asymmetric Key or Certificate, is granted the appropriate permissions, and then the permissions get inferred by signing one or more pieces of code using ADD SIGNATURE. By doing this, you are giving the code the permissions that you had granted to that server or database principal. So now you can create a stored procedure or function to do a very specific thing, and effectively grant only that code the appropriate permissions to run properly. Then you just handle permissions naturally, by granting EXECUTE / SELECT permission to only those users or roles who should be allowed to perform that action.

In terms of executing xp_servicecontrol, if you grant EXECUTE on it to a User, then that User can run it to get info on ANY service, which is not nearly as fine-tuned as allowing for only running it to get info on one particular service.

With the inferred permissions method in mind, we could create a Login and place it in the sysadmin role, but we don't even need to go that far. Starting in SP1 of SQL Server 2008 R2, a new DMV was introduced that shows status information for SQL Server related Windows / NT Services: sys.dm_server_services. The only permission this DMV needs is VIEW SERVER STATE. But again, you don't even need to grant this permission to the User in question. You can:

Create a Function (UDF or Multistatement TVF) or Stored Procedure that encapsulates the very specific usage of this DMV
Create a Certificate
Create a Login based on that Certificate
Grant VIEW SERVER STATE to that Login
Create that same Certificate in the database where the User in question exists
Sign the Function and/or Stored Procedure with that Certificate
Grant the User in question EXECUTE on that Function and/or Stored Procedure

In this setup, the User isn't given any permissions outside of running a Stored Procedure or Function. And while the VIEW SERVER STATE permission allows for A LOT of info to be seen, that permission is only used to run whatever code is in the Stored Procedure or Function. And as long as that code isn't Dynamic SQL, then that permission can't be applied outside of that narrowly-defined usage (or outside of any other code that has been signed by that same certificate).

Worried that you might slip up and accidentally grant ALTER PROCEDURE to that User and then they can put in any code that requires that permission? WRONG! Any change at all to an object that is signed will drop the signature from that object. So even if someone does manage to change that Function or Stored Procedure, it will no longer be signed by the Certificate and hence will no longer be able to infer permissions from the Certificate-based Login. And even if that User somehow has permission to add a signature to an object, that won't help without the password used to create the Certificate. And if the User has access to your passwords (or deployment scripts that might have them), then you have serious flaws in your process and standards, in which case it isn't worth the time and energy to do all of this, and just put that person in the sysadmin role ;-).

Try the following:

Initial setup:

USE [Test];
GO -------------------------------------------------------------
-- DROP PROCEDURE dbo.CheckSqlAgent
CREATE PROCEDURE dbo.CheckSqlAgent
AS
SET NOCOUNT ON;

SELECT dss.[status], dss.[status_desc]
FROM   sys.dm_server_services dss
WHERE  dss.[servicename] LIKE N'SQL Server Agent (%';

GO -------------------------------------------------------------
-- DROP FUNCTION dbo.IsSqlAgentRunning
CREATE FUNCTION dbo.IsSqlAgentRunning()
RETURNS BIT
AS
BEGIN
  DECLARE @IsRunning BIT = 0;

  IF (EXISTS(SELECT dss.*
             FROM   sys.dm_server_services dss
             WHERE  dss.[servicename] LIKE N'SQL Server Agent (%'
             AND    dss.[status] = 4 -- Running
            )
     )
  BEGIN
    SET @IsRunning = 1;
  END;

  RETURN @IsRunning;
END;

GO -------------------------------------------------------------

CREATE USER [MrNoLogin] WITHOUT LOGIN;
GRANT EXECUTE ON dbo.CheckSqlAgent TO [MrNoLogin];
GRANT EXECUTE ON dbo.IsSqlAgentRunning TO [MrNoLogin];

Test #1:

SELECT SESSION_USER AS [SessionUser];

EXEC dbo.CheckSqlAgent;
SELECT dbo.IsSqlAgentRunning() AS [IsSqlAgentRunning];

Returns:

SessionUser
-----------
dbo

status  status_desc
------  -----------
4       Running

IsSqlAgentRunning
-----------------
1

Test #2:

EXECUTE AS USER='MrNoLogin';
SELECT SESSION_USER AS [SessionUser];

EXEC dbo.CheckSqlAgent;
SELECT dbo.IsSqlAgentRunning() AS [IsSqlAgentRunning];

Returns:

SessionUser
-----------
MrNoLogin

Msg 297, Level 16, State 1, Procedure CheckSqlAgent, Line 7
The user does not have permission to perform this action.

Msg 297, Level 16, State 1, Line 6
The user does not have permission to perform this action.

The Fix:

REVERT;
SELECT SESSION_USER AS [SessionUser];
-------------------------------------------------------------
USE [master];

-- DROP CERTIFICATE [DoStuffCert];
CREATE CERTIFICATE [DoStuffCert]
    ENCRYPTION BY PASSWORD = N'W0rdUp,Yo!'
    WITH SUBJECT = N'Certificate for Managing Special Permissions';

CREATE LOGIN [MrDoStuff] FROM CERTIFICATE [DoStuffCert];
GRANT VIEW SERVER STATE TO [MrDoStuff];

BACKUP CERTIFICATE [DoStuffCert]
    TO FILE = 'C:\TEMP\ViewSqlAgentStatus.CER'
    WITH PRIVATE KEY
    (
        FILE = 'C:\TEMP\ViewSqlAgentStatus.PVK',
        DECRYPTION BY PASSWORD = 'W0rdUp,Yo!',
        ENCRYPTION BY PASSWORD = 'DontStartNoneWontBeNone'
    );
-------------------------------------------------------------
USE [Test];

CREATE CERTIFICATE [DoStuffCert]
    FROM FILE = 'C:\temp\ViewSqlAgentStatus.CER'
    WITH PRIVATE KEY (
        FILE = 'C:\temp\ViewSqlAgentStatus.PVK',
        DECRYPTION BY PASSWORD = 'DontStartNoneWontBeNone',
        ENCRYPTION BY PASSWORD = 'W0rdUp,Yo!'
    );

ADD SIGNATURE TO dbo.CheckSqlAgent
    BY CERTIFICATE [DoStuffCert] WITH PASSWORD = 'W0rdUp,Yo!';

ADD SIGNATURE TO dbo.IsSqlAgentRunning
    BY CERTIFICATE [DoStuffCert] WITH PASSWORD = 'W0rdUp,Yo!';

Test #3:

EXECUTE AS USER='MrNoLogin';
SELECT SESSION_USER AS [SessionUser];

EXEC dbo.CheckSqlAgent;
SELECT dbo.IsSqlAgentRunning() AS [IsSqlAgentRunning];

REVERT;

And now both work for MrNoLogin :-).