Insert into a view derived from a table with a discriminator field I am in a situation in which I have a base table with some fields and a discriminator field which could have one out of a specific set of values.

From that base table, I have derived many views, one for each possible discriminator value, but such views do not include the discriminator column themselves.

The problem is, when I try to INSERT INTO one of the view, the insert succeedes, but the discriminator field in the base table is NULL.

Isn't there a way for me to insert in the view while having the discriminator value correctly set? Or do I need necessarily to insert in the base table instead?

Here is an example of what I mean:

create table base_table (
  a int unsigned auto_increment primary key,
  b char(10) default null,
  c char(10) default null,
  x tinyint unsigned default null
);

create or replace view base_view_1 as
select a, b, c
from base_table
where x = 1;

create or replace view base_view_2 as
select a, b, c
from base_table
where x = 2;

[ ... and so on ... ]

insert into base_view_1 (a, b, c) VALUES (null, 'xxx', 'xxx');

select * from base_view_1; -- no result

select * from base_table; -- one row like (1, 'xxx', 'xxx', null)

See that, in the example, the row from base_table as null as the value for x? Should mysql be able to populate that field automatically based on view definition?

What I would like to achive is that, when I insert on the view base_view_1, since it is defined for x = 1, mysql sets automatically the correct value in base_table, and the same when inserting in base_view_2 where x should be 2, and so on, for example:

insert into base_view_1 (b, c) values ('xxx', 'xxx'); // should insert into base_table the values ('xxx', 'xxx', 1)

insert into base_view_2 (b, c) values ('xxx', 'xxx'); // should insert into base_table the values ('xxx', 'xxx', 2)

// and so on for each distinct discriminator value

Now, is mysql capable of doing this automatically for me, or do I need to resort inserting directly on base_table?

You should do your INSERTs into the base table only
That way you can INSERT queries like these
insert into base_table (a, b, c, x) VALUES (null, 'xxx', 'xxx',1);
insert into base_table (b, c, x) VALUES ('xxx', 'xxx',2);

If you do INSERT queries like these
insert into base_table (a, b, c) VALUES (null, 'xxx', 'xxx');
insert into base_table (b, c) VALUES ('xxx', 'xxx');

then you should assign a default value of 1 for x in the table definition
create table base_table (
  a int unsigned auto_increment primary key,
  b char(10) default null,
  c char(10) default null,
  x tinyint unsigned default 1
);

Otherwise, you should only INSERT into base_table since there exists no mechanism for dynamically switching the default value of x within the definition of a view. The x column has no ability to be selected in the view (because you left it out of the SELECT clause). Consequently, it would definitely have no ability to be updated or assigned in the view.
Give it a Try !!!


Finding the names and the year of the movies that have at least 5 actors Given database of the website "eMovies" that manage data about cinema movies.

  Write in SQL query that finds the names and the year of the movies that have at least 5 actors.

My attempt:

The idea: to do a natural joint between "Movies" and "ActorsMovies" tables, and then to count the rows with the same MovieID, and then to check if count>5

I succeded to count the rows thank's to @Phil's advice, but still I need the name of the movies and the year,

Here is my start:

SELECT DISTINCT Year, MovieName
FROM Movies, ActrorsMovies
SELECT Count(ActorsMovies.MovieID)
FROM ActorsMovies
GROUP BY ActorsMovies.MovieID
HAVING (((Count(ActorsMovies.ActorID))>5));

This code does not work

EDIT: another attempt

 SELECT DISTINCT Movies.Year, Movies.MovieName
 FROM Movies, ActorsMovies
 GROUP BY ActorsMovies.MovieID
 HAVING Count(ActorsMovies.ActorID)>5;

This code works in T-SQL and should work in Ms Access:

SELECT COUNT(M.MovieID) as 'Count', M.MovieName, M.Year FROM Movies M
INNER JOIN ActorsMovies AM ON AM.MovieID = M.MovieID
INNER JOIN Actors A ON AM.ActorID = A.ActorID
GROUP BY M.MovieID, M.MovieName, M.Year
HAVING COUNT(M.MovieID) > 5

I also recommend not to use Year as a field name because it's a reserverd keyword in Ms Access and may cause problems:
https://msdn.microsoft.com/en-us/library/bb208875(v=office.12).aspx


Audit table insert restriction So I'm trying to create my own audit table for some user management analysis and stumbled across a little problem. I use this trigger

CREATE OR REPLACE
TRIGGER usr_t_audit_user_alnd
    AFTER LOGON ON DATABASE
    BEGIN
        INSERT INTO usr_t_audit_user (username, session_id, logon_day, logon_time)
        VALUES(UPPER(USER), SYS_CONTEXT('USERENV','SID'), SYSDATE, TO_CHAR(SYSDATE, 'HH24:MI:SS'));
    END;

to insert some standard informations into my table. It works fine, except the logoff time inside my other trigger isn't always that correct. But my actual problem is, that I don't want to insert all user logons. For example I only want to audit some chosen users. How can I achieve such a restriction? I can't put a WHERE-clause in my trigger. Anybody got some ideas?

You probably want to use something like,

CREATE OR REPLACE TRIGGER usr_t_audit_user_alnd AFTER LOGON ON DATABASE
BEGIN
  -- insert audit record only if user is one of specified, else ignore
  if ora_login_user in ('USER_A','USER_C','USER_D') then
    INSERT INTO usr_t_audit_user (username, session_id, logon_day, logon_time)
    VALUES(UPPER(USER), SYS_CONTEXT('USERENV','SID'), SYSDATE, TO_CHAR(SYSDATE, 'HH24:MI:SS'));
  end if;
END;


Do all nodes of a SQL Always-on Availability group need to be the same version of SQL? We currently have a number of SQL 2012 server pairs set up with SQL Availability groups where we are just syncing data on the secondary for a RO copy, no listener is currently set up for HA.

We now have an opportunity to add a listener so we can utilize the secondary for read-only and HA fail-over.  With HA fail-over, we started thinking of down the road scenarios like upgrading to SQL 2016.

Can we add a SQL 2016 server as a secondary node to our existing AG and when we're ready to migrate, fail-over the SQL 2012 primary to the SQL 2016 secondary and then make it the primary node going forward?

We don't like doing upgrades in place so migrating data to new servers with minimal downtime can be risky, especially if outage windows are small.  Just wondering if this is even an option to experiment with.  In theory, it could definitely minimize the data and server migration windows to a few minutes, if you can mix SQL versions in an AG.  I haven't come across anything that states you cannot do this but haven't had the time to try.

Yes, you can absolutely do this. I've done this with several 2012 to 2016 migrations and not encountered any issues along the way.

Just bear in mind, you will not be able to fail back to the 2012 instance once you fail to that 2016 replica, and until you have more 2016 replicas you will not have any HA in place.


how do you reference server logins from a visual Studio database project? I'm looking for a way to move all our SQL Server databases (on one server) into source control.

I tried creating a solution, and then adding VS database projects to the solution that correspond to the SQL Server databases.

As I was creating the projects (via importing existing databases) I chose to import security objects.

These security objects are users that need to reference logins of some kind. i.e. in a database project I have a folder Security with the SQL script: CREATE USER xxx. The VS debugger underlines this in red and warns me:

  SQL71501: User: xxx has an unresolved reference to Login xxx.

To fix this I tried creating a new project called SERVER, in which I would include server-related SQL files.

How do I reference this new project from the other database project?

Right click on references in your SSDT project. Click on Add Database reference.

Fill out the database reference information here. I bring in the server I am connecting to as a dacpac object, then reference it as a project in the current solution. Seems to work best for me.

EDIT: I just tested using a database project in the same solution as well and it resolved the references correctly.

Edit 2: Why this works - Projects within a VS solution do not "talk" to each other, by default. In the real world, servers and databases do "reference" each other. What we are doing here is explicitly making that connection, because the scope of a SSDT project can only be one database. Once you add the SSDT project and sync all the objects, including the security, you need to add that project as a reference in order for it "see" the database objects at the server level.


"Merging" grouped rows (column-wise, picking the first non-null value, ordered by another column) I'd like to apologize in advance for the poor title, but I had a hard time trying to describe my problem.

I have a table like this:

+---+--------------+--------------+-------+-------+-------+
|   | RECORD_LEVEL | RECORD_GROUP | COL_A | COL_B | COL_C |
+---+--------------+--------------+-------+-------+-------+
| 1 |            1 | group1       | NULL  | 0     | bar1  |
| 2 |            2 | group1       | foo2  | -1    | bar2  |
| 3 |            2 | group2       | foo3  | NULL  | NULL  |
| 4 |            5 | group2       | foo4  | 5     | bar4  |
| 5 |            1 | group3       | NULL  | 1     | NULL  |
| 6 |            2 | group3       | foo6  | 1     | NULL  |
+---+--------------+--------------+-------+-------+-------+

And I need to group it by RECORD_GROUP, using the first non-NULL value for each column. By 'first', I imply an ORDER BY RECORD_LEVEL ASC. The result I'd like is something like this:

+---+--------------+-------+-------+-------+
|   | RECORD_GROUP | COL_A | COL_B | COL_C |
+---+--------------+-------+-------+-------+
| 1 | group1       | foo2  |     0 | bar1  |
| 2 | group2       | foo3  |     5 | bar4  |
| 3 | group3       | foo6  |     1 | NULL  |
+---+--------------+-------+-------+-------+

I tried to reproduce this scenario in this fiddle: http://rextester.com/UMMP61375

Please, feel free to ask for more information or premises.

You can use the FIRST_VALUE analytic function for this:

select distinct
  record_group,
  first_value(col_a ignore nulls) over
    (partition by record_group order by record_level
      rows between unbounded preceding and unbounded following) as col_a,
  first_value(col_b ignore nulls) over
    (partition by record_group order by record_level
      rows between unbounded preceding and unbounded following) as col_b,
  first_value(col_c ignore nulls) over
    (partition by record_group order by record_level
      rows between unbounded preceding and unbounded following) as col_c
from
  tbl_record
order by
  record_group
;

RECORD_GROUP         COL_A                     COL_B COL_C
-------------------- -------------------- ---------- --------------------
group1               foo2                          0 bar1
group2               foo3                          5 bar4
group3               foo6                          1

We find the first value (FIRST_VALUE) for each row, ignore nulls (ignore nulls), for the current record_group "partition/group" (partition by record_group), for the whole "partition/group" (rows between unbounded preceding and unbounded following) based on the order of record_level (order by record_level), and get rid of the duplicate rows with DISTINCT.


Using SSIS to get record counts for tables that may not exist I have an SSIS package that executes simple queries against a list of servers.  I am having an issue with one particular simple query:  If a table exists, get the rowcount.  Otherwise report "not exists".

The SQL code works in Management Studio if I connect directly, but when I connect with SSIS using an ADO NET data flow task to execute the code I get a validation error.  It seems to me that SSIS is examining all objects in the code to see if they exist before executing.  It fails before it even tries to run.

If I comment out the "select count(*) from Table1" then everything works no problem.

The distributed servers are all using SQL Server 2008R2 Express Edition.

declare @Result varchar(1000),
        @Table1Count int

select  @Result = '',
        @Table1Count = 0

-- If tables exist, get the counts
IF EXISTS(select 1 from information_schema.Tables where Table_Name = 'Table1')
BEGIN
  select @Table1Count = count(*) from Table1
  select @Result = 'Table1: ' + convert(varchar(12),@Table1Count)
END
ELSE
  select @Result = 'Tables do not exist'

Any ideas?

You could try executing the offending statement using sp_executesql.


different settings for max_heap_table_size and tmp_table_size (MariaDB 10.1.x) I run pt-variable-advisor and got a note about different settings for max_heap_table_size and tmp_table_size.

With a web search I found only old articles (around 2007).

pt-variable-advisor h=localhost,u=root,p=Quule0juqu7aifohvo2Ahratit --socket /var/vcap/sys/run/mysql/mysqld.sock
(...)
# NOTE tmp_table_size: The effective minimum size of in-memory implicit temporary tables used internally during query execution is min(tmp_table_size, max_heap_table_size), so max_heap_table_size should be at least as large as tmp_table_size.
(...)

Our configuration

max_heap_table_size = 16777216
tmp_table_size = 33554432

We didn't modify the defaults from cf-mysql-release. I saw that MariaDB KB recommends other default values.

  cf_mysql.mysql.tmp_table_size:
    description: 'The maximum size (in bytes) of internal in-memory temporary tables'
    default: 33554432

  cf_mysql.mysql.max_heap_table_size:
    description: 'The maximum size (in rows) to which user-created MEMORY tables are permitted to grow'
    default: 16777216

I found also Optimize MySQL tmp_table_size and checked our values and the configuration:

MariaDB [(none)]> show global status like 'created_tmp_disk_tables';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Created_tmp_disk_tables | 12727901 |
+-------------------------+----------+
1 row in set (0.01 sec)

MariaDB [(none)]> show global status like 'created_tmp_tables';
+--------------------+-----------+
| Variable_name      | Value     |
+--------------------+-----------+
| Created_tmp_tables | 115714303 |
+--------------------+-----------+
1 row in set (0.01 sec)

MariaDB [(none)]> select (12727901*100)/(115714303 + 12727901) as "Created disk tmp tables ratio" from dual;
+-------------------------------+
| Created disk tmp tables ratio |
+-------------------------------+
|                        9.9094 |
+-------------------------------+
1 row in set (0.00 sec)

Is something wrong with our (default) configuration? We don't know our workload. We run about 500 small databases for small web apps with different usage pattern.

Short Answer: None of those is "wrong".

How are those settings used?

There are (if I am not mistaken) exactly 2 uses for those two settings.  They are, as alluded to in some of what you quoted:

When you do CREATE TABLE ... ENGINE=MEMORY, it is given a limit of the current value of max_heap_table_size.  You can, and "should", change that setting just before doing that CREATE.  Too large a value could waste precious RAM.
When complex SELECT needs to create a temporary table, such as in preparation for ORDER BY, it first tries to use a MEMORY table; if this fails (for any of several reasons), it resorts to using MyISAM.  The limit on the MEMORY table size is min(max_heap_table_size, tmp_table_size).

I do not believe tmp_table_size is every used by itself.

If you never explicitly create a MEMORY table, the having those two settings the same avoids confusion and discussions like this.

So, the admonition to make them equal is weaker than the wording you quote.  It is "sorta, kinda, oughta", and not "you MUST!".

A different perspective:  Let me approach your question from another point of view.

Intra-select tmp tables can happen often -- in every connection, and even multiple times in a single select.  So, keeping tmp_table_size "low" is important to avoid blowing out RAM.  I recommend no more than 1% of RAM, but this is rather arbitrary.

If, meanwhile, you are playing around with MEMORY tables, you need control over their sizes.

OK, I have hit a dead end.  Why do they have the "min(...)"?  Why not just leave them separate?

I don't know.

Future:  The above discussion applies to at least MySQL versions 4.0 to 5.7, plus all versions (so far) of MariaDB.  MySQL 8.0 uses a "temp table engine", which may lead to some of the above discussion being moot.


MySQL Referential Integrity Issue I'm having a lot of issues and don't really know where to look.  I'm pretty sure it's a referential integrity issue and has something to do with the addition of the Professor table and the Foreign Key Constraints between it and the Section table.

I've narrowed the error down to the Enrolled table.  It's the one that gives me "ERROR 1215 (HY000): Cannot add foreign key constraint" when I try an add it.  Remove the Professor, QualToTeach table and the Foreign Key constraints to professor from Section and it works beautifully.

Any help?

Here's my code:

CREATE TABLE Student (
    StudentID INT,
    Fname VARCHAR(30),
    Lname VARCHAR(30),
    DOB DATE,
    Major VARCHAR(10),
    PRIMARY KEY (StudentID)) ENGINE=InnoDB;

CREATE TABLE Professor (
    Emp_no INT,
    FName VARCHAR(10),
    LName VARCHAR(10),
    DeptCode VARCHAR(2),
    PRIMARY KEY (Emp_no)) ENGINE=InnoDB;

CREATE TABLE Phone (
    StudentID INT,
    PNumber VARCHAR(20),
    Type VARCHAR(5),
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID) ON DELETE CASCADE
    ) ENGINE=InnoDB;

CREATE TABLE Class (
    ClassID VARCHAR(5),
    NoCredits INT,
    Description VARCHAR(30),
    Requisite1 VARCHAR(6),
    Requisite2 VARCHAR(6),
    Requisite3 VARCHAR(6),
    PRIMARY KEY (ClassID)) ENGINE=InnoDB;

CREATE TABLE Section (
    ClassID VARCHAR(5),
    SecNo VARCHAR(10),
    Semester VARCHAR(4),
    ClassRoom VARCHAR(6),
    TimeOffered VARCHAR(20),
    Emp_no INT,
    PRIMARY KEY (ClassID, Emp_no, SecNo, Semester),
    FOREIGN KEY (ClassID) REFERENCES Class(ClassID),
    FOREIGN KEY (Emp_no) REFERENCES Professor(Emp_no)
    ) ENGINE=InnoDB;

CREATE TABLE QualToTeach (
    ClassID VARCHAR(5),
    Emp_no INT,
    PRIMARY KEY (ClassID, Emp_no),
    FOREIGN KEY (ClassID) REFERENCES Class(ClassID),
    FOREIGN KEY (Emp_no) REFERENCES Professor(Emp_no)) ENGINE=InnoDB;

CREATE TABLE Enrolled (
    StudentID INT,
    ClassID VARCHAR(5),
    SecNo VARCHAR(10),
    Semester VARCHAR(4),
    Grade CHAR(2),
    DropDate DATE,
    PRIMARY KEY (StudentID, ClassID, SecNo, Semester),
    FOREIGN KEY (ClassID, SecNo, Semester) REFERENCES Section(ClassID, SecNo, Semester),
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID))
    ENGINE=InnoDB;

I had some changes in that table and it got executed. mainly the primary key. pl go through the same,

    mysql> CREATE TABLE Student (
        ->     StudentID INT,
        ->     Fname VARCHAR(30),
        ->     Lname VARCHAR(30),
        ->     DOB DATE,
        ->     Major VARCHAR(10),
        ->     PRIMARY KEY (StudentID)) ENGINE=InnoDB;
    Query OK, 0 rows affected (0.10 sec)

    mysql> CREATE TABLE Professor (
        ->     Emp_no INT,
        ->     FName VARCHAR(10),
        ->     LName VARCHAR(10),
        ->     DeptCode VARCHAR(2),
        ->     PRIMARY KEY (Emp_no)) ENGINE=InnoDB;
    Query OK, 0 rows affected (0.14 sec)

    mysql> CREATE TABLE Phone (
        ->     StudentID INT,
        ->     PNumber VARCHAR(20),
        ->     Type VARCHAR(5),
        ->     FOREIGN KEY (StudentID) REFERENCES Student(StudentID) ON DELETE CASCADE
        ->     ) ENGINE=InnoDB;
    Query OK, 0 rows affected (0.14 sec)

mysql> CREATE TABLE Class (
    ->     ClassID VARCHAR(5),
    ->     NoCredits INT,
    ->     Description VARCHAR(30),
    ->     Requisite1 VARCHAR(6),
    ->     Requisite2 VARCHAR(6),
    ->     Requisite3 VARCHAR(6),
    ->     PRIMARY KEY (ClassID)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.10 sec)

            mysql> CREATE TABLE Section (
                ->     ClassID VARCHAR(5),
                ->     SecNo VARCHAR(10),
                ->     Semester VARCHAR(4),
                ->     ClassRoom VARCHAR(6),
                ->     TimeOffered VARCHAR(20),
                ->     Emp_no INT,
                -> PRIMARY KEY (SecNo, Semester),
                -> FOREIGN KEY (ClassID) REFERENCES Class(ClassID),
                ->     FOREIGN KEY (Emp_no) REFERENCES Professor(Emp_no)
                ->     ) ENGINE=InnoDB;
            Query OK, 0 rows affected (0.13 sec)

            mysql> CREATE TABLE QualToTeach (
                -> ID varchar(5),
                -> ClassID VARCHAR(5),
                ->     Emp_no INT,
                ->     PRIMARY KEY (ID),
                -> FOREIGN KEY (ClassID) REFERENCES Class(ClassID),
                ->     FOREIGN KEY (Emp_no) REFERENCES Professor(Emp_no)) ENGINE=InnoDB;
            Query OK, 0 rows affected (0.13 sec)

            mysql> CREATE TABLE Enrolled (
                ->     StudentID INT,
                ->     ClassID VARCHAR(5),
                ->     SecNo VARCHAR(10),
                ->     Semester VARCHAR(4),
                ->     Grade CHAR(2),
                ->     DropDate DATE,
                -> Enrolled_id INT,
                -> PRIMARY KEY (Enrolled_id),
                -> FOREIGN KEY (ClassID, SecNo, Semester) REFERENCES Section(ClassID, SecNo, Semester),
                ->     FOREIGN KEY (StudentID) REFERENCES Student(StudentID))
                ->     ENGINE=InnoDB;
            Query OK, 0 rows affected (0.19 sec)


Are my MongoDB servers right in setting? I have a small web site, so I used two servers.

A: master 4G Ram, 96G SSD
B: slave 2G RAM, 96G SSD

The B server is always for backup, no read, no write.

In my application, many users can (and will) be trying to update one document at the same time by inserting a few embedded documents (objects) into it during one single update.

My questions

Are all my operations in A server Strong Consistent?
Do I need to use "fsync = true" in my code in PHP?

At first you should move to a 3 node replica set to ensure High Availability. At your case i would suggest to add an arbiter to your set, and give higher priority to the server A to ensure that will be the Primary.

1) Probably your question is about atomicity rather than consistency (correct me if i am wrong) the most safe choice is command findAndModify but you need to be careful because on high concurrency you may get delays.

2) Calling fsync in your code after every operation is a bad idea because you may get stale periods. fsync just flush the pending operations to disk, it doesn't ensure atomicity of an operation.


Similar Jobs in two different Servers Can anyone just help me with a script to find similar SQL jobs in two different instances/servers?

By similar I mean jobs with the same name, which do the same task, with the same frequency and schedule.

If you've only got two instances just run something like the following against each and compare...

SELECT @@SERVERNAME AS ServerName
    ,j.job_id AS JobID
    ,j.NAME AS JobName
    ,j.enabled AS JobEnabled
    ,j.description AS JobDescription
    ,j.date_created AS JobCreated
    ,j.date_modified AS JobLastModified
    ,l.NAME AS JobOwner
    ,ss.schedule_id AS ScheduleID
    ,ss.enabled AS ScheduleEnabled
    ,sjs.next_run_date
    ,sjs.next_run_time
FROM msdb..sysjobs j
LEFT JOIN master.sys.syslogins l ON j.owner_sid = l.sid
LEFT JOIN msdb..sysjobschedules sjs ON sjs.job_id = j.job_id
LEFT JOIN msdb..sysschedules ss ON ss.schedule_id = sjs.schedule_id

Maybe it's just me but it sounds like one of those things where you need to just grab the data from each and look at it. It doesn't sound like you have specific enough definitions of the term 'similar' to write a sure-fire script.


Memory Allocation in Shared DB & IIS Environment In my use case, we have production servers that host both IIS & SQL on the same machine, not ideal but no flexibility to change that.

Basically a single machine is both a DB and an Application server, hosting multiple DBs and multiple instances of ASP.NET sites.

The last time I checked MS' best practice said ~75% RAM allocation to SQL, however this config doesn't leave much room for other processes to run.

Does any one know what Microsoft recommends for servers that host both SQL & IIS together ?

When there's nothing else on the machine besides SQL you need to leave at least 2-4 Gigs of RAM for the OS.  When running a bunch of different IIS sites you'll need at least 4 Gigs for the OS and IIS.  As some of your machines have only 4 Gigs that's a bit of a problem.  Can you add some RAM to these servers to get them up to 16 or 24 Gigs?

I'm assuming x64 here?


Including null values in unpivoted column Consider the following table:

id  title   genre1  genre2  genre3  genre4  genre5
1   Movie1  Thriller    Crime   NULL    NULL    NULL
2   Movie2  Fantasy NULL    NULL    NULL    NULL
3   Movie3  Political   Philosophical   Historical  NULL    NULL
4   Movie4  Science Fiction NULL    NULL    NULL    NULL

When I unpivot genre1, genre2, ... the NULL values are removed, so that, for example, the rows for Movie1 would be:

id title  genre     original_column
1  Movie1 Thriller  genre1
1  Movie1 Crime     genre2

Is there a way to include NULL values, for example:

id title  genre     original_column
1  Movie1 Thriller  genre1
1  Movie1 Crime     genre2
1  Movie1 NULL      genre3
1  Movie1 NULL      genre4
1  Movie1 NULL      genre5

I can SELECT ISNULL(genre1, '*'), ISNULL(genre2, '*') ..., but suppose I want the values to appear as NULL? Is there a better way than:

WITH
-- SELECT that unpivots table
AS unpivoted
SELECT unpivoted.id,
  unpivoted.title,
  ,CASE unpivoted.genre WHEN '*' THEN NULL ELSE unpivoted.genre END
  ,unpivoted.original_transaction
FROM unpivoted

Also, I notice that the query that returns the source data to be unpivoted MUST have an alias. For example:

SELECT ... FROM
  -- *must* give it an alias or it's a syntax error
  (SELECT ... FROM) AS source
  UNPIVOT(genre for original_column IN (genre1,genre2, genre3,genre4,genre5))

Does anyone know why? I have not found a way to refer to the alias anywhere in the query.

You can do the unpivot in a cross apply instead to get the result you want.

declare @T table
(
  id int,
  title varchar(50),
  genre1 varchar(50),
  genre2 varchar(50),
  genre3 varchar(50),
  genre4 varchar(50),
  genre5 varchar(50)
);

insert into @T values
(1, 'Movie1', 'Thriller', 'Crime',  NULL,  NULL, NULL),
(2, 'Movie2', 'Fantasy', NULL, NULL, NULL, NULL),
(3, 'Movie3', 'Political', 'Philosophical', 'Historical', NULL, NULL),
(4, 'Movie4', 'Science Fiction', NULL, NULL, NULL, NULL);

select T.id,
       T.title,
       G.genre,
       G.original_column
from @T as T
  cross apply (
              select T.genre1, 'genre1' union all
              select T.genre2, 'genre2' union all
              select T.genre3, 'genre3' union all
              select T.genre4, 'genre4' union all
              select T.genre5, 'genre5'
              ) as G(genre, original_column);

If you are on SQL Server 2008 or later you can use the Table Value Constructor instead of union all as suggested by Vladimir Baranov in a comment.

select T.id,
       T.title,
       G.genre,
       G.original_column
from @T as T
  cross apply (values (T.genre1, 'genre1'),
                      (T.genre2, 'genre2'),
                      (T.genre3, 'genre3'),
                      (T.genre4, 'genre4'),
                      (T.genre5, 'genre5')
              ) as G(genre, original_column);


using update query with subquery in Oracle sql I have an update query where I am trying to update a field but for more than one record, I am trying a join but it doesn't work.  Below I get multiple values that come back from the select statement but I think my syntax is incorrect below.

UPDATE sku@bn2 x
INNER JOIN (
   select g.prev_perm_ret, g.itm_cd
   from gm_prc_reg_prc_items g, sku@bn2 b
   where g.itm_cd=b.stylecode
   and b.storecode=00000
   and g.prev_perm_ret<>b.listprice
   and g.prev_perm_ret>g.ret_prc
   and b.sellprice=b.listprice
) y ON x.stylecode=g.itm_cd
SET x.listprice=y.prev_perm_ret

I would recommend to have a look into the merge into statement when youre deailing with oracle.

if you just want to update rows omit when not matched part of the statement

see http://www.oracle.com/webfolder/technetwork/de/community/apex/tipps/sql-merge/index.html and https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm


Script not allowed to create schema objects when generating tables despite db_owner role I am looking to make a "schema only" (no data) copy of a database on my local server. I used Tasks -> Generate scripts to script the tables from my existing database and then after creating the new database opened a query window and pasted the copied scripts in.

When I ran the scripts I received the following error ...

Msg 2760, Level 16, State 1, Line 1
The specified schema name "abc" either does not exist or you do not have permission to use it.
Msg 2760, Level 16, State 1, Line 1
The specified schema name "xyz" either does not exist or you do not have permission to use it.
Msg 2760, Level 16, State 1, Line 1

Now some of the tables I selected to be scripted do make use of different schemas (abc & xyz) yet I am unsure as to why they cannot be scripted. Looking at my login I am mapped to the db_owner role which I would think would give me the appropriate permissions to create the schema. I am using SQL Server 2012.

Do I understand correctly: you scripted tables only, created the database manually and then ran the script to create the tables?

You have to either script the whole database and create the new database using that script (change the DB name if you'll execute it on the same instance) or at least include schemas in the list of database objects to script ("Choose objects" step of the wizard).


Filtered index error when using two column condition I created a filtered index (in SQL Server 2012)  and compared it with non-filtered index – I could see promising improvement as shown below. Now I need to change the filter condition to use two columns in the table. The required condition is  WHERE InboundQuantity - OutboundQuantity <> 0 or  InboundQuantity <> OutboundQuantity

But when I apply this filter, I am getting error message

  “Incorrect WHERE clause for filtered index 'IX_WO_PlantCD_FilterQtyNotEqual'”.

I know there are limitations with filtered index. However is there a way to achieve this improvement with two column condition, using filtered index or the like?

Query

--Normal Index
CREATE NONCLUSTERED INDEX IX_WO_NormalPlantCD
ON dbo.MyTable (PlantCD) INCLUDE (InboundQuantity,OutboundQuantity)

--Filtered Index
CREATE NONCLUSTERED INDEX IX_WO_PlantCD_FilterInboundQtyNotEqual
ON dbo.MyTable (PlantCD) INCLUDE (InboundQuantity,OutboundQuantity)
WHERE InboundQuantity <> 0

--Query 1
SELECT SUM([InboundQuantity] - [OutboundQuantity])
FROM [MyTable]
WHERE [PlantCD] = 'XX'

--Query2 (suing same where condition as filtered index)
SELECT SUM([InboundQuantity] - [OutboundQuantity])
FROM [MyTable]
WHERE [PlantCD] = 'XX'
AND InboundQuantity<>0

Plan

Where a filtered index on a computed column is too limited, you have the option of creating an indexed view.

The indexed view is maintained automatically by the database, so you do not need to worry about getting trigger logic correct for all possible DML operations. You also do not have to worry about complicated correctness problems under high concurrency.

The overhead of maintaining the view indexes is also typically less than for triggers.

For example, given a table:

CREATE TABLE dbo.MyTable
(
    PlantCD varchar(10) NOT NULL,
    InboundQuantity integer NOT NULL,
    OutboundQuantity integer NOT NULL
);
GO
-- Pretend the table has rows
UPDATE STATISTICS dbo.MyTable
WITH ROWCOUNT = 1000000, PAGECOUNT = 50000;

A suitable indexed view might be:

CREATE VIEW dbo.MyTableQtyDiff
WITH SCHEMABINDING
AS
SELECT
    MT.PlantCD,
    QtyDiff = SUM(MT.InboundQuantity - MT.OutboundQuantity),
    NumRows = COUNT_BIG(*)
FROM dbo.MyTable AS MT
WHERE
    MT.InboundQuantity <> MT.OutboundQuantity
GROUP BY
    MT.PlantCD;
GO
CREATE UNIQUE CLUSTERED INDEX
    CUQ_dbo_MyTableQtyDiff__PlantCD
ON dbo.MyTableQtyDiff
    (PlantCD);
GO
-- Pretend the view has some rows
UPDATE STATISTICS dbo.MyTableQtyDiff
WITH ROWCOUNT = 10000, PAGECOUNT = 50;

If you are running Enterprise Edition, automatic indexed view matching means a query written without referencing the view directly can still use it:

-- Enterprise Edition required
SELECT
    SUM(MT.InboundQuantity - MT.OutboundQuantity)
FROM dbo.MyTable AS MT
WHERE
    MT.InboundQuantity <> MT.OutboundQuantity;

The execution plan shows the indexed view being accessed:

For other editions, you would need to reference the view directly, and use the NOEXPAND hint:

-- Any edition
SELECT
    SUM(MTQD.QtyDiff)
FROM dbo.MyTableQtyDiff AS MTQD WITH (NOEXPAND);

Execution plan:

The same connection setting restrictions apply to indexed views as for indexed computed columns. You may choose to use the NOEXPAND version even in Enterprise Edition, since use of the view is guaranteed (not decided by the optimizer), and cardinality estimates will usually be better as well.

The effect of adding an indexed view depends on many factors, so you need to test it. Any safe solution has the potential to increase contention, but where an indexed computed column doesn't fit, an indexed view is often the next best option. If done well, the effect can be minimal. The overhead of an indexed view over a single table can be no more than that of adding a new nonclustered index to the base table.


Why does my DB2 silent/unattended Install instead invokes DB2 Install wizard and GUI. Can someone help on ths issue? I want to install DB2 10.5.6 DPF on my VMWare windows machine. I manually created the response files (both the server.rsp and addpart.rsp files).

When it came time to perform the server install, I issued the following on the DB2 command line: ./db2setup -l db2server_installlog.txt  -t db2server_installtrace.txt  -r db2server.rsp

What happened is that instead of the above command installing DB2 silently and unattended, the DB2 Install wizard and the attendant GUI is invoked and I am invited to make the very same choices that produced the the response files that I am trying to use in the first place!!

Can anyone help explain the strange behavior that I have on my hand? I have installed DB2 unattended with response files before now with no issues. The difference with this install is that I am trying to see if I can install DB2 with Data Partitioning Feature on a trial basis for educational purposes only. Could not having a valid license or officially endorsed trial DB2 DPF version be the cause of the weird install behavior?

Any and all assistance will be highly appreciated.

Thanks

Okonita

You will need to use the wizard to install DB2 at least on the primary partition/system of the partitioned database system. See here for the instructions. Thereafter you can use a response file to set up the other partitions. The steps for using a response file mention as a requirement that the primary computer has been set up using the wizard.


Patching SQL Server 2012 If I apply Cumulative Update 3 in SQL Server 2012 SP1, do I still have to do Cumulative update 1 and 2 before I can apply CU 3? And also, how would you know what CU's or hot fixes that has already been applied in SQL Server?

No. Each CU contains all the fixes in the CUs prior to it.

For the second part of the question, the only RELIABLE way supported across most versions (2005+) is to get a 4 part number and compare it to any of the comprehensive build lists available on the internet:

Select Serverproperty('ProductVersion')

You can also check @@Version but it's not reliable, as some older CUs don't list their CU number as they were meant to. You can also try Serverproperty('ProductUpdateLevel') but it's only supported on a few newer versions and service packs of SQL Server.

So I stick with build numbers. Most DBAs have one script or another to pull data from web sources into a table to do translations.

If in doubt start the CU installer and it will check whether it's needed or not. It won't let you continue upgrading if you've already upgraded everything on the server.


Transaction log space used in percent - how to clear or empty I am trying to learn more about the transaction log, which I think is the bottleneck of many Stored Procedures (used for reporting) which do data modifications.
So I tried below:
select * from sys.dm_db_log_space_usage;
update dbo.A
set col1 = 'hello'
where id < 500000;
select * from sys.dm_db_log_space_usage;

So now the log file used is 1.8%. How can I clear it? I thought when the transaction is committed it resets to "zero" (and by "it" I mean percent of transaction log used). I have tried `checkpoint' but that did not help.

The guide David Browne linked should explain everything you need, so I highly concur that you should read it. But the five second version is the Transaction Log will grow until a backup is taken of it to ensure the log itself is hardened (aside from the transaction being committed to disk). When the Transaction Log grows, it'll use the reserved free space it has available inside the log file until it runs out of space in the file itself, then the file itself will undergo a growth operation on the disk.
Once a growth operation occurs, the size of the log file on disk will remain that size unless an explicit shrink operation occurs, but internally on SQL Server, the space consumed by the log file is flushed and re-used when a log backup occurs. So while from the OS's perspective, the log file may be one size, the actual available reserved space inside that log file is managed by SQL Server and consumed until it runs out and needs to grow the log file again or a backup occurs to flush the used space so it can be re-used.
One last thing I meant to add, is that your stored procedure performance is almost certainly not related to how full your Transaction Log is. If you post a new question with the execution plan of your stored procedure, we likely can point you in the right direction of the actual root cause.


Mongoose is not showing the ISODate in the result I am using mongodb version 4.2.2.
I have following data:
{
    "_id" : ObjectId("5f0c58089c058304f7a64fa9"),
    "date" : ISODate("2021-07-11T00:00:00.000Z"),
    "dayOfWeek" : 0,
    "staffMember" : ObjectId("5f0c58076b559419d62efe47"),
    "startTime" : 885
},

/* 2 createdAt:2020-07-13T12:48:08.000Z*/
{
    "_id" : ObjectId("5f0c58089c058304f7a64fa8"),
    "date" : ISODate("2021-07-11T00:00:00.000Z"),
    "dayOfWeek" : 0,
    "staffMember" : ObjectId("5f0c58076b559419d62efe47"),
    "startTime" : 870
},

/* 3 createdAt:2020-07-13T12:48:08.000Z*/
{
    "_id" : ObjectId("5f0c58089c058304f7a64fa7"),
    "date" : ISODate("2021-07-11T00:00:00.000Z"),
    "dayOfWeek" : 0,
    "staffMember" : ObjectId("5f0c58076b559419d62efe47"),
    "startTime" : 855
},

/* 4 createdAt:2020-07-13T12:48:08.000Z*/
{
    "_id" : ObjectId("5f0c58089c058304f7a64fa6"),
    "date" : ISODate("2021-07-11T00:00:00.000Z"),
    "dayOfWeek" : 0,
    "staffMember" : ObjectId("5f0c58076b559419d62efe47"),
    "startTime" : 840
}

I am using mongoose in my project. When I run this query in node js:
const { memberId, date } = req.params;
let isoDate = date.toISOString();
let timeSlots = await TimeSlots.find(
{
  staffMember: memberId,
  date: isoDate
});

The result printed on the console:
db.spaTimeSlots.find({"staffMember" : ObjectId("5f0c58076b559419d62efe47"), date: new Date("Wed, 11 Jul 2020 00:00:00 GMT")})
   .projection({})
   .sort({_id:-1})
   .limit(100)

However, I want this result:
db.spaTimeSlots.find({"staffMember" : ObjectId("5f0c58076b559419d62efe47"), date: ISODate("2021-07-11T00:00:00.000Z")})
   .projection({})
   .sort({_id:-1})
   .limit(100)

I want the result returned with the date in ISODate format, not as text (i.e. not Wed, 11 Jul).

You need to add like this:
let date = new Date().toISOString();
let isoDate = new Date(date);

Hope it works.


Is there much difference between having a key (intA, intB), or having key (longAB) where longAB is the concatenation of bytes of intA and intB? This is a bit of an odd question.  Let's go with MySQL for example.  I don't know a ton about db internals.

Compare two scenarios, in one, we have a table with a composite key of the from (intA, intB).  Queries filter by range intA, and the intB portion of the key is to enforce uniqueness.  So we might do WHERE intA > 0x123 AND intA < 0xabc

The alternate scenario is where we have key (longAB), where longAB is the byte concatenation of intA and intB, in other words intA << 32 | intB.  Now our queries look like WHERE longAB > 0x12300000000 AND longAB < 0xabc00000000

So we'd get back the equivalent rows.

My question is, would these have substantially different impact on space usage, IO intensity, and why?

Thanks

Worry about performance only after you've worried about the business.  If intA and intB are meaningful to the business, then NEVER EVER hide those attributes in columns that are "obfuscated" in the way you suggest.  Doing that WILL ALWAYS come back to haunt you.

You'll pay the price when someone wants to do a WHERE INTB = somevalue, and that moment WILL come.

With your design hack, the only way you could answer that question is by filtering the value out of your obfuscated column using some variant of MOD(yourcol,32BIT_MAX).  That means table scan by definition, unless you have been smart enough to also add an index on MOD(yourcol,32BIT_MAX).  But adding that index in addition to an index on yourcol is likely to make the overall solution SLOWER, precisely the opposite of what you were hoping to achieve.


Search and compare list of ever-growing IPs I'm a newbie when it comes to mysql things and queries in general, just wanted to point that out.

I'll keep this as short as I can.

I have a table where I track the IPs of users that enter the front-page of a site. Any other page is not logged.

I have a unique ID for that table set as the IP of the client.

Every time a new user hits the front-page I have to look-up his IP in the table to make sure his IP is not there, if it's not it gets inserted, if it is, nothing happens.

So far this is working great, but then again, there aren't many IPs in there.

Since I'm using Laravel's Eloquent, I can't give you the precise query, in PHP it looks like this:

Tracker::where('ip', $client_ip)->first();

Although I'm not sure, I suspect that the raw query looks something like this:

SELECT ip FROM table_name WHERE column_client_ip = '127.0.0.1' LIMIT 1

The IP itself is saved as a varchar. I know about INET_ATON but I'm not exactly sure if I can use it with ipv6.

My questions are as follows:

I know that searching and comparing varchar type fields isn't the fastest thing in the world, do I have any other options to save IP? Keep in mind that they can also tunnel their IP which, in case I'm not wrong, it adds to the total length of the string I get as the IP - I could be way off here, my sysadmin skills are basically non-existent.
Currently the search is really fast, mostly because there aren't a lot of records and I also keep an index on the ip column. Realistically speaking, at what point should I start being concerned that the search/compare will slow down significantly?

If you guys have any other suggestions or want to warn be about possible issues I may have in the future with the current setup, I'm all ears.

I'm doing this at work. I use INET_ATON() to IPv4 and INET6_ATON() to IPv6. This search is faster than VARCHAR fields search. But, one important point:

This function was added in MySQL 5.6.3.
INET_ATON()   Return the numeric value of an IP address.
INET_NTOA()   Return the IP address from a numeric value.
INET6_ATON()  Return the numeric value of an IPv6 address.
INET6_NTOA()  Return the IPv6 address from a numeric value.

Documentation: MySQL Misc


I've changed the 'impossible dates' and I've set a rextester sample.

First group by RoomId and BookingId and calculate percentage.

(
 SELECT      b.BOO_RoomId, bd.BIL_BookingId,
             COUNT(*) as NumNights,
             COUNT(*) * 100 / (SELECT COUNT(*) FROM ___BillableDatas) AS Percent
 FROM        ___BillableDatas bd
 LEFT JOIN   ___Bookings B
 ON          b.BOO_Id = bd.BIL_BookingId
 GROUP BY    b.BOO_RoomId, bd.BIL_BookingId
) co

Then LEFT JOIN with rooms table and show all values:

SELECT      r.ROO_Name,
            COALESCE(NumNights, 0) NumNights,
            COALESCE(Percent, 0) Percent
FROM        ___Rooms r
LEFT JOIN   (
            SELECT      b.BOO_RoomId, bd.BIL_BookingId,
                        COUNT(*) as NumNights,
                        COUNT(*) * 100 / (SELECT COUNT(*)
                                          FROM ___BillableDatas
                                          WHERE BIL_Date >= '2017-02-01' AND BIL_Date < '2017-02-23'
                                          ) AS Percent
            FROM        ___BillableDatas bd
            LEFT JOIN   ___Bookings B
            ON          b.BOO_Id = bd.BIL_BookingId
            WHERE       BIL_Date >= '2017-02-01' AND BIL_Date < '2017-02-23'
            GROUP BY    b.BOO_RoomId, bd.BIL_BookingId
            ) co
ON          R.ROO_Id = co.BOO_RoomId
;

+----------+---------------+-----------+---------+
| ROO_Name | BIL_BookingId | NumNights | Percent |
+----------+---------------+-----------+---------+
| Room 1   |       1       |     5     | 71,4286 |
| Room 2   |       2       |     2     | 28,5714 |
| Room 3   |       0       |     0     |  0,0000 |
+----------+---------------+-----------+---------+


Set TERMOUT OFF is not working when I am using sqlplus in command prompt? I am using the sql statement listed below in my spool sql script, and I am executing this script from sqlplus. But in terminal query output is still coming and I am using sqlplus in command prompt. But when I tried, directly launching sqlplus.exe and using this script I am not getting query output in terminal.

Can someone please help on how to stop terminal output when I launch sqlplus from command prompt?

SET ECHO OFF
SET TERMOUT OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SET LINESIZE 83
SET TRIMSPOOL ON
SET WRAP ON
SET RECSEP OFF

SPOOL '&1'

SELECT
    DATA_OUT_DATA
FROM
    STAGE.DATA_TABLE
ORDER BY
    DATA_UNIQ_ID,DATA_INV_ID, DATA_SEQ_NO;

SPOOL OFF

SET PAGESIZE 24
SET LINESIZE 80
SET FEEDBACK ON
SET TERMOUT ON

12.41.61 SET TERM[OUT] {ON | OFF}

  Controls the display of output generated by commands in a script that
  is executed with @, @@ or START. OFF suppresses the display so that
  you can spool output to a file without displaying the output on
  screen. ON displays the output on screen. TERMOUT OFF does not affect
  output from commands you enter interactively or redirect to SQL*Plus
  from the operating system.

Put the above commands in a script, then call that script.

script.sql:

SET ECHO OFF
SET TERMOUT OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SET LINESIZE 83
SET TRIMSPOOL ON
SET WRAP ON
SET RECSEP OFF

SPOOL '&1'

SELECT
    DATA_OUT_DATA
FROM
    STAGE.DATA_TABLE
ORDER BY
    DATA_UNIQ_ID,DATA_INV_ID, DATA_SEQ_NO;

SPOOL OFF

SET PAGESIZE 24
SET LINESIZE 80
SET FEEDBACK ON
SET TERMOUT ON

Then:

SQL> @script.sql


Initials separated by periods (e.g. J.E.F. Smith) In a MySQL database I have a column that holds the initials of a person. However not all input is nice and clean. To give an example the initials for the name John Edward Fredrick Smith should be J.E.F.

Unfortunately in our database you would also find JEF or J.EF or JE.F. or J E F.

I thought I'd use the replace function to first remove all spaces and periods and then add periods after each letter. That last part (the adding a period after each letter) is the hard part. I just can't figure out how to do that in MySQL.

Any help would be much appreciated.

Vincent

You could probably write a function which looped over characters of the string to update any length string, but I'm guessing here you have a fairly limited range (i.e. you're unlikely to have records with more than four initials). If that's true you can do something like the following:

-- Setup example table
create table people (initials varchar(1024));

insert into people (initials)
values
  ('JEF'),
  ('J.EF'),
  ('JE.F.'),
  ('J E F'),
  ('J.E F  F');

-- Remove all spaces
update people
set initials = replace(initials, ' ', '');

-- Remove all periods
update people
set initials = replace(initials, '.', '');

-- Add periods after each letter
update people
set initials =
  case
    when length(initials) = 1 then initials
    when length(initials) = 2 then concat(substring(initials, 1, 1), '.', substring(initials, 2, 1))
    when length(initials) = 3 then concat(substring(initials, 1, 1), '.', substring(initials, 2, 1), '.', substring(initials, 3, 1))
    when length(initials) = 4 then concat(substring(initials, 1, 1), '.', substring(initials, 2, 1), '.', substring(initials, 3, 1), '.', substring(initials, 4, 1))
    else initials
  end;

SQL Fiddle example: http://sqlfiddle.com/#!9/7fc7e/1

Edit: Note that I've used three separate update statements here to make it easier to understand, you could collapse this down to one or two statements if running this over a large table, to avoid multiple updates.


How to keep the structure of the Tablix when there is no data to show We have a SSRS report which shows the result of a dataset in a tablix. If the query in the dataset returns no data, the header of the Tablix gets displayed but not the tablix cells below the header. I know there is a property to NoRowMessage, but in fact it is not what we want. Because when I specify a message, SSRS shows the message instead of the empty tablix.I must show the tablix structure as it is, but with empty cells.

I even set the rule for each cell value of the tablix using IsNothing function, to show blank (""), if the value is null, but it didn't help.

Do you have any idea?
Thanks for your help in advance.

You can add a row outside of the outermost group right under the tablix header row.

Set the row visibility to Show or hide based on an expression.  The expression would like like:

=iif(CountRows("DataSet1") > 0, true, false)

When there is no data, the table will show the headers and empty row.

When there is data, the empty row will be hidden.


SQL Filegroup degrading performance On our dev boxes, our database resides entirely in the PRIMARY filegroup, and everything works fine.

On one of our production servers, recently upgraded from 2005 to 2008, we noticed it was performing slower than it should.  On this machine, there are two filegroups - PRIMARY and INDEXES.    Both filegroups contain 1 file per logical volume, one logical volume per CPU. The filegroups are sharing logical volumes (both of them are spread out across all disks).

We isolated a few queries that were performing fast on the dev boxes and slow (up to 40x slower) on the production machine.  Turned out these queries were using the non-clustered indexes that resided in the INDEXES filegroup.   Tweaking some of the queries to only use clustered indexes that were in the PRIMARY filegroup dropped their times back to normal.

As a final confirmation, we redeployed the same database on the same machine to have everything in PRIMARY, and things went back to normal!

Here's the statistics output of one of the queries, run on the machine in question  (table names changed to protect the innocent):

FAST (everything in PRIMARY filegroup):

(3 row(s) affected)
Table '0'. Scan count 2, logical reads 14, ...
Table '1'. Scan count 0, logical reads 0, ...
Table '1'. Scan count 0, logical reads 0, ...
Table '2'. Scan count 2, logical reads 7, ...
Table '3'. Scan count 2, logical reads 1012, ...
Table '4'. Scan count 1, logical reads 3, ...

 SQL Server Execution Times:
   CPU time = 437 ms,  elapsed time = 445 ms.

SLOW (indexes split into their own filegroup):

(3 row(s) affected)
Table '0'. Scan count 209, logical reads 428, ...
Table '1'. Scan count 0, logical reads 0,...
Table '2'. Scan count 1021, logical reads 9043,....
Table '3'. Scan count 209, logical reads 105754, ....
Table '4'. Scan count 0, logical reads 0, ....
Table '5'. Scan count 1, logical reads 695, ...
**Table '#46DA8CA9'. Scan count 205, logical reads 205, ...**
Table '6'. Scan count 6, logical reads 436, ...
Table '7'. Scan count 1, logical reads 12,....

 SQL Server Execution Times:
   CPU time = 17581 ms,  elapsed time = 17595 ms.

It's pretty clear that having a second file group is making SQL Server batty with choosing an execution plan.  Looking at the corresponding execution plans for the query in each configuration verifies this, they are quite different.

What's going on?  Any insights would be much appreciated.

Random thoughts

The statistics etc weren't disable on rebuild into new filegroup?
Maintenance tasks do not look at the filegroup?
Heavily fragmented disk?
Try MAXDOP 1
Exactly same fillfactor etc?

The IO looks way off so I wonder if stats or fragmentation is causing an issue


GRANT USAGE on all schemas in a database? I want to GRANT USAGE to a user/role for a given database. The database has many schemas.

I know there's an ON ALL TABLES IN SCHEMA, but I want "all schemas". I tried GRANT USAGE .. ON DATABASE, but that's obviously wrong (it doesn't actually exist).

This is for Postgres 9.3 or 9.4, it happens to be a server that is on AWS RDS.

You have at least two options.

The first one makes use of a small query and a text editor.  We have to collect the schemata of our interest:

SELECT nspname
  FROM pg_namespace;

You can add a WHERE clause if you want to limit the scope.  Copy the output and amend it, so you get a number of GRANT USAGE ON SCHEMA ... TO your_role; commands.  Then just feed it to psql, for example:

psql -f multigrant.sql

A usual variant of this could be a shell script that loops over the collected names and calls psql, passing the constructed GRANT statement to the -c option.

The other solution does basically the same in one pl/pgsql block, building a dynamic query.  The core is the same - we have to collect the schemata.  Then we loop over all of them, granting the permissions schema by schema:

DO $do$
DECLARE
    sch text;
BEGIN
    FOR sch IN SELECT nspname FROM pg_namespace
    LOOP
        EXECUTE format($$ GRANT USAGE ON SCHEMA %I TO your_role $$, sch);
    END LOOP;
END;
$do$;

Notes:

unlike for tables, sequences, functions and types, one cannot set default privileges for schemata (as of 9.4).  You will have to grant this privilege for any newly added schema manually.
here I am using dollar quoting when building the dynamic query.  This allows me to use 'normal' syntax, as opposed to multiplicating single quotes, for example (not present in this example).  This way most editors will highlight the statements nicely.
I also use format() with the %I format specifier to have the object name properly quoted if necessary.  This approach is far more readable than building the query with concatenation of string constants and some quote_ident() calls.
pg_namespace can be found in the pg_catalog schema.  Check out the other objects in there  - they store every aspect of your schemas, tables and so on.


Does mysqlhotcopy  block SELECT statements from different clients? I would like to create a backup of different MyISAM tables on a server using mysqlhotcopy. These tables are about 6GB in size. I would like to know, does mysqlhotcopy block users from SELECTing (reading) data from the tables while taking the backup?

Thanks in advance, Tim

The documentation is not as clear as it should be, but I would say no, mysqlhotcopy does not block SELECT queries.
Here is why
REASON #1 : MySQL Docs on 'mysqlhotcopy'

mysqlhotcopy is a Perl script that was originally written and
contributed by Tim Bunce. It uses FLUSH TABLES, LOCK TABLES, and cp or
scp to make a database backup. It is a fast way to make a backup of
the database or single tables, but it can be run only on the same
machine where the database directories are located. mysqlhotcopy works
only for backing up MyISAM and ARCHIVE tables. It runs on Unix and
NetWare.
To use mysqlhotcopy, you must have read access to the files for the
tables that you are backing up, the SELECT privilege for those tables,
the RELOAD privilege (to be able to execute FLUSH TABLES), and the
LOCK TABLES privilege (to be able to lock the tables).

REASON #2 : MySQL Docs on 'Lock Tables' Restrictions

• If you are using tables for a nontransactional storage engine, you
must use LOCK TABLES if you want to ensure that no other session
modifies the tables between a SELECT and an UPDATE. The example shown
here requires LOCK TABLES to execute safely:  LOCK TABLES trans READ,
customer WRITE; SELECT SUM(value) FROM trans WHERE
customer_id=some_id; UPDATE customer   SET
total_value=sum_from_previous_statement   WHERE customer_id=some_id;
UNLOCK TABLES;   Without LOCK TABLES, it is possible that another
session might insert a new row in the trans table between execution of
the SELECT and UPDATE statements.

In summary, since mysqlhotcopy launches LOCK TABLES, SELECT queries are evidently still allowed.


Accent Sensitive Sort Why do these two SELECT statements result in a different sort order?

USE tempdb;
CREATE TABLE dbo.OddSort
(
    id INT IDENTITY(1,1) PRIMARY KEY
    , col1 NVARCHAR(2)
    , col2 NVARCHAR(2)
);
GO
INSERT dbo.OddSort (col1, col2)
VALUES (N'e', N'eA')
    , (N'é', N'éB')
    , (N'ë', N'ëC')
    , (N'è', N'èD')
    , (N'ê', N'êE')
    , (N'ē', N'ēF');
GO

SELECT *
FROM dbo.OddSort
ORDER BY col1 COLLATE Latin1_General_100_CS_AS;

╔════╦══════╦══════╗
║ id ║ col1 ║ col2 ║
╠════╬══════╬══════╣
║  1 ║ e    ║ eA   ║
║  2 ║ é    ║ éB   ║
║  4 ║ è    ║ èD   ║  -- should be id 3?
║  5 ║ ê    ║ êE   ║
║  3 ║ ë    ║ ëC   ║
║  6 ║ ē    ║ ēF   ║
╚════╩══════╩══════╝

SELECT *
FROM dbo.OddSort
ORDER BY col2 COLLATE Latin1_General_100_CS_AS;

╔════╦══════╦══════╗
║ id ║ col1 ║ col2 ║
╠════╬══════╬══════╣
║  1 ║ e    ║ eA   ║
║  2 ║ é    ║ éB   ║
║  3 ║ ë    ║ ëC   ║
║  4 ║ è    ║ èD   ║
║  5 ║ ê    ║ êE   ║
║  6 ║ ē    ║ ēF   ║
╚════╩══════╩══════╝

The behavior you are seeing here is due, in a general sense, to the fact that the Unicode Collation Algorithm (UCA) allows for complex, multi-level sorting. More specifically:

Sorting is not Comparison:

Determining whether two strings are the same or different is fairly straight forward (given a particular locale/language and set of sensitivities). But determining the order of 2 or more strings can be highly complex.
Sorting is done in a series of steps, with each step applied to the entire string, not character by character:

Standard: sort base characters (regardless of accent and case differences)
IF Accent-sensitive, apply accent / diacritic weights
IF Case-sensitive, apply casing weights

When you sort by col1 (single character), it first determines that all characters have the same weight since they are all "e". Next, it applies the accent / diacritic weights. There are no casing differences so the third step wouldn't change anything. So the only differences are in step 2, which is why there is a preferred order for those rows based on col1.

When you sort by col2 (two characters), it first determines that each row has a different weight since both characters are used to determine the sort weight (e.g. "ea", "eb", etc). Next, it applies the accent / diacritic weights. There are no casing differences so the third step wouldn't change anything. So there are differences in steps 1 and 2 this time. But since the differences in step 1 have already been applied to each string before the weights of step 2 are considered, the weights from step 2 don't have any effect on the ordering; they would only apply if weights from step 1 for two or more rows were the same.

The following adaptation of the sample code from the question hopefully illustrates the sorting behavior described above. I added some additional rows and an additional column to help show the impact of the Collation being case-sensitive (since the original sample data is all lower-case):

SETUP

USE [tempdb];

-- DROP TABLE dbo.OddSort;
CREATE TABLE dbo.OddSort
(
    id INT IDENTITY(1,1) PRIMARY KEY,
    col1 NVARCHAR(5) COLLATE Latin1_General_100_CS_AS,
    col2 NVARCHAR(5) COLLATE Latin1_General_100_CS_AS,
    col3 NVARCHAR(5) COLLATE Latin1_General_100_CS_AS
);
GO

INSERT dbo.OddSort (col1, col2, col3)
VALUES (N'e', N'eA', N'e A')
     , (N'ê', N'êE', N'ê E')
     , (N'é', N'éH', N'é H')
     , (N'ë', N'ëC', N'ë C')
     , (N'E', N'EG', N'E G')
     , (N'Ë', N'ëh', N'ë h')
     , (N'è', N'èD', N'è D')
     , (N'é', N'éB', N'é B')
     , (N'ë', N'ëH', N'ë H')
     , (N'ē', N'ēF', N'ē F');

TEST 1

SELECT [id], [col1], UNICODE([col1]) AS [CodePoint]
FROM dbo.OddSort
ORDER BY col1;

Returns:

id    col1    CodePoint
1     e       101
5     E       69
8     é       233
3     é       233
7     è       232
2     ê       234
4     ë       235
9     ë       235
6     Ë       203
10    ē       275

What we can see in the results above:

The Code Point is not determining the sort order
The non-accented characters are sorted before the accented characters (within the same letter: f would still come after all of these). Clearly, accent weights are applied before case weights.
Lower-case sorts before upper-case within the same accented (or non-accented) character (i.e. the e then E, and the ë then Ë). This tailoring is used by most of the Windows Collations, while most of the SQL Server Collations sort upper-case first.

TEST 2

SELECT [id], [col2]
FROM dbo.OddSort
ORDER BY col2;

Returns:

id    col2
1     eA
8     éB
4     ëC
7     èD
2     êE
10    ēF
5     EG
3     éH
6     ëh
9     ëH

What we can see in the results above:

First-level sorting truly is the base characters. If it were accents / diacritics then the ëC (id = 4), ēF (id = 10), and EG (id = 5) rows would not be where they are. If it were casing, then the EG (id = 5) row would not be where it is.
Second-level sorting truly is the accents / diacritics. This explains why the last three rows are éH -> ëh -> ëH instead of ëh -> éH -> ëH (i.e. IDs 3 -> 6 -> 9 instead of 6 -> 3 -> 9).
Third-level sorting truly is the casing. This is why the last 2 rows are ëh -> ëH, since lower-case sorts first.

TEST 3

SELECT [id], [col3]
FROM dbo.OddSort
ORDER BY col3;

Returns:

id    col3
1     e A
8     é B
4     ë C
7     è D
2     ê E
10    ē F
5     E G
3     é H
6     ë h
9     ë H

What we can see in the results above:

The sort order is exactly the same as in Test 2. The only difference in the test values here is that there is a space between each character, removing the possibility of contextual rules. Hence, we know that the reason for the difference in sort order for col2 in the question is again due to "Multi-Level Comparison", and not "Contextual Sensitivity".

Additional notes:

With regarding to getting the exact rules, that is not as easy as it should be. The problem with getting concrete explanations of these rules is that the Unicode sorting rules, while definitely documented, are a recommendation. It is up to vendors, such as Microsoft, to implement those recommendations. Microsoft did not implement the recommendations exactly as stated in the Unicode documentation so there is a disconnect there (similar to how neither the HTML or CSS specifications are implemented as completely, nor even in the same way, across vendors). Then, there are different versions of the Windows Collations (you are using version 100 which came out with SQL Server 2008) and that is tied to a Unicode version that is much older than the current version of Unicode or of the ICU Collation demo is using. For example, the What's New in SQL Server 2008 Collations section of the "Collation and Unicode Support" documentation for SQL Server 2008 makes 2 very interesting points about what is "new" to the _100_ series Collations:

  Unicode 5.0 case table.

Unicode 5.0 was published in July of 2006 (well, the character database was released then, and the full spec followed in late 2006). The current version is 10.0 which was published in June of 2017. And given the release pattern of the past 4 years, it is likely that version 11.0 will be out sometime mid-2018.

  Weighting has been added to previously non-weighted characters that would have compared equally.

Those weights were more than likely defined in the Unicode Standard, just not in this implementation of it.

Still, the UCA documentation linked above is a good place to start.
Sort Keys used by Windows / .NET / SQL Server are not exactly the same as shown in the Unicode Standard (See @Patrick's answer) or implemented in the ICU. To see what Windows / .NET / SQL Server use, you can try the CompareInfo.GetSortKey Method. I created a SQLCLR UDF to pass in these values and get the sort key. Please note that I am using SQL Server 2017 on Windows 10 with .NET Framework 4.5 - 4.6.1 installed, so .NET should be using Unicode 6.0.0. Also, Level4 is not being used for these strings.

CHAR    L1     L2     L3     L4
e      0E21
E      0E21           12
ë      0E21    13
Ë      0E21    13     12

Looking at these sort keys for Test 1, and realizing that the levels are sorted like multiple columns within an ORDER BY clause (L3 is sorted within same values of L2, which is sorted within same values of L1), should illustrate that the reason for the behavior noted in the question is in fact the multi-level sorting capability of Unicode. Likewise:

CHAR       L1         L2       L3       L4
EG      0E210E25              1212
éH      0E210E2C      0E      0212
ëh      0E210E2C      13
ëH      0E210E2C      13      0212

Looking at some of the sort keys for Test 2, we can see that base characters are sorted first (L1), then accents are sorted (L2), and then casing is sorted (L3).
Since the datatype is NVARCHAR, we are only concerned with Unicode Code Points and sorting algorithms, hence the use of the UNICODE() function in TEST 1. While Code Pages are specified by most Collations, they only pertain to VARCHAR data. Meaning, while Code Page 1252 is specified by the Latin1_General* series of Collations, that can be ignored here.
The weights described in the Default Unicode Collation Element Table (DUCET) (Version 5.0.0 which should map to the _100_ series Collations) are fine for US English, but not other locales / languages. Other languages need to start with the DUCET and then apply locale-specific override rules as defined by the Common Locale Data Repository (CLDR) project. From what I can tell, versions 1.4 / 1.4.1 were released in 2006. To get those overrides, download the CLDR 1.4 "core" file via http://unicode.org/Public/cldr/1.4.0/core.zip, then, in that zip file, go to the collation folder and find the XML file corresponding to the locale being used. Those files contain just the overrides, and are not complete sets of collation rules.


Best option for a database used by a translation app I'm about to start working on an app which will include words and their translations in various languages.

What is my best option for a database?

Queries will be made instantly. So if someone selects "Russian" as a second language and starts typing "Please pass the salt", each of those words will have to be looked up in Russian. And maybe just listing each word won't be the exact translation of the phrase which will lead to another query which will maybe look up the phrase based on the words in the sentence.

So I can use MySql, or any other non sql database Firebase, Mongo, CouchDb ... etc. But I don't know if not using a relational database is my best option.

Can I do something like this which seems ok but the database will get huge when considering all languages:

{
  "english": {
    "word1": {
      "russian": "word1inrussian",
      "spanish": "word1inspanish",
      "korean": "word1inkorean"
    },
    "a phrase": {
      "russian": "phrase in russian",
      "spanish": "phrase in spanish",
      "korean": "phrase in korean"
    }
  }
}

You don't typically handle translation inside of the database. This is for no specific reason. It certainly could be done, and has been done but that's not the way things are done now.

There are two huge sources of internationalization data,

Unicode Common Locale Data Repository (CLDR)
International Components for Unicode (ICU)

Unicode CLDR is absolutely gigantic. Normally you use something like Globalize.js which wraps these and pulls down data for the features that it supports.

In order to generate most simple strings, a globalization library needs this much crap... (English only shown for simplicity)

> var msg =
  '{GENDER, select, male{He} female{She} other{They} }' +
  ' found ' +
  '{RES, plural, =0{no results} one{1 result} other{# results} }' +
  ' in the ' +
  '{CAT, selectordinal, one{#st} two{#nd} few{#rd} other{#th} }' +
  ' category.';

You'll get these results:

> var mfunc = new MessageFormat('en').compile(msg);

> mfunc({ GENDER: 'male', RES: 1, CAT: 2 })
'He found 1 result in the 2nd category.'

> mfunc({ GENDER: 'female', RES: 1, CAT: 2 })
'She found 1 result in the 2nd category.'

> mfunc({ GENDER: 'male', RES: 2, CAT: 1 })
'He found 2 results in the 1st category.'

> mfunc({ RES: 2, CAT: 2 })
'They found 2 results in the 2nd category.'


ERD Design for Employee Leave I found the following ERD design:

Which trying to capture as much information about employee leave (i.e sick, travel, etc).
What am trying to understand is what does the columns:

Table : Employee_Annual_Leave

year_number // Why is this set as the PK? What does it mean by
year_number der_leave_cumulative_days_taken // where could it be
possible be derived from?
der_leave_days_remaining // and how does
these two column work in real scenario?

PS: Yes i have read the "Note" box written above it. But i still dont get it.

Could anyone explain what it means?

To answer your questions:

Year number is part of a (three column) composite primary key.  There will be one record per employee + year + type of leave (vacation/sick/...)  Year number probably means the calendar year stored as an integer.
der_leave_cumulative_days_taken means the total of leave actually taken, which would be derived by summing the days between Employee_Leave_Taken.leave_from_date and Employee_Leave_Taken.leave_to_date where those dates fall within the year given by Employee_Annual_Leave.year_number for the same employee_id and leave_type_code.
der_leave_days_remaining is next to pointless because it would be Employee_Annual_Leave.leave_days_allowed less der_leave_cumulative_days_taken.  A real scenario would be that an employee is allowed three weeks vacation (15 days) and has taken two weeks so far (der_leave_cumulative_days_taken=10 days) and so they have 5 days remaining.  The point of keeping these precalculated in this way is that you can refer easily to these values in a query or report.  Notice the date_updated field.  This implies that there is a regular (perhaps scheduled) batch job that calculates these derived values periodically, or perhaps they are recalculated when changes are made to either leave allowed or leave taken.


Automatically have Nonclustered indexes in a Different Filegroup Is there a way to automatically move clustered indexes A on one filegroup, and all nonclustered indexes to a different filegroup B upon DDL creation? We have developers constantly creating tables/indexes, and would like a way to automate this in dev environment. We have an old SAN Hard drive, and in some cases separating indexes on different filegroups may optimize performance, according to our DBA.

https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-files-and-filegroups?view=sql-server-2017

Clustered index is just a well arranged table. When we create clustered index on heap, it arrange data of the tables based on key column. So moving clustered index means you are moving table to different file group.

There’s no inbuilt feature in SQL Server to automatically move indexes to specific file group when we create index, instead we can write index maintenance script to move indexes to specific file group based on index type or we'll have to specify file group while we are creating indexes.

Also, this link will help, https://stackoverflow.com/questions/55187160/automatically-create-indexes-in-different-filegroup-edit-publish-profile-script


Query execute slowly when changed compatibility Level of my database I use SQL Server 2014 and have a database with compatibility level 110 (SQL Server 2012).
also I have a stored procedure on it that execute about 10 second. When I change compatibility level of my database to 120 (SQL Server 2014), execution time of my stored procedure became about 30 minute.
I use following steps but execution time of my stored procedure not changed.

Step 1: Use DBCC CHECKDB command.
Step 2: Execute DBCC UPDATEUSAGE on my database.
step 3: Rebuild all indexes on my database.
step 4: Drop all index and all foreign key on my database and create all again.

After all of above step my stored procedure execution time not changed.
But when compatibility of my database returned to 110, my sp run about 10 second.
I check above step multiple and get a single result.
How must I do. and how can I change compatibility level of my database to 120 (SQL Server 2014).
Thanks in advance.

I have recently seen this exact same problem with an upgrade from SQL Server 2008 R2 to SQL Server 2014 where a very small number of Stored Procedures (SPs) (approx 5 out of 3,000 ) performed worse in SQL 2014 Compatibility Level 120 than in SQL 2014 Compatibility Level 110 or in SQL  2008.

In SQL 2014 the Cardinality Estimator (CE) has changed which may impact how a SP performs in SQL 2014 Compatibility Level 120.

There are a few possible solutions

Leave the SQL 2014 Database running in Compatibility Level 120 and add a dbcc Trace Flag to force SQL Server 2014 to use the prior CE <-- I would not recommend this
Leave the SQL 2014 Database running in Compatibility Level 120 and Tempoarily add

OPTION (QUERYTRACEON 9481)

to the offending SP to force just that SP to use the prior CE. The SP should now perform in Compatibility Level 120 as it did with Compatibility Level 110. <-- I prefer this approach as your Database/System gains all of the other advantages of SQL 2014 while the "fix" only impacts the offending SP.

Irrespective of the approach I would then profile the execution plan/read/writes etc of the offending SP on a test system in SQL Server 2014 Compatibility Level 120 and rewrite the SP to cater for the new CE.

Once tested and verified I would then apply to production.

Microsoft have a good article on the Cardinality Estimator changes in SQL 2014


Tiny query used to be sub-second speed, now takes 60+ seconds Ok, I've got a monitoring process that runs the following query to make sure the databases are backing up regularly.  This process used to take 1-2 seconds for connection, query execution, and everything.  Now the process is timeing out and if I run the query in SMSS it takes over a minute.

SELECT D.name AS [database_name], D.recovery_model, BS1.last_backup, BS1.last_duration
FROM sys.databases D
LEFT JOIN (
    SELECT BS.[database_name],
    DATEDIFF(HH,MAX(BS.backup_finish_date), GETDATE()) AS last_backup,
    DATEDIFF(MI,MAX(BS.backup_start_date),MAX(BS.backup_finish_date)) AS last_duration
    FROM msdb.dbo.backupset BS
    WHERE BS.type = 'L'
    GROUP BY BS.[database_name]
) BS1 ON D.name = BS1.[database_name]
ORDER BY D.[name]

Now the sub-select by itself still takes less than a second and returns about 20 rows (the msdb.dbo.backupset table has ~500K rows).  The sys.databases table has about only about 30 rows.  So I'm lost as to why this query now takes a minute to run.

The problem started after the C: drive was full for a few minutes so I thought maybe an index for corrupted. So I've rebuilt all the indexes (including PK) on the msdb.dbo.backupset table.  I can't find the sys.databases table so I'm guessing it's a "virtual table" or something?  I looked at the estimated execution plan but that stuff is mostly over my head.

Any suggestions?

the sub-select by itself still takes less than a second and returns
  about 20 rows ... Any suggestions?

In that case you may as well materialize it into a #temp table and join onto that instead.

SELECT BS.[database_name],
       DATEDIFF(HH, MAX(BS.backup_finish_date), GETDATE())                 AS last_backup,
       DATEDIFF(MI, MAX(BS.backup_start_date), MAX(BS.backup_finish_date)) AS last_duration
INTO   #BS1
FROM   msdb.dbo.backupset BS
WHERE  BS.type = 'L'
GROUP  BY BS.[database_name];

SELECT D.name AS [database_name],
       D.recovery_model,
       BS1.last_backup,
       BS1.last_duration
FROM   sys.databases D
       LEFT JOIN #BS1 BS1
         ON D.name = BS1.[database_name]
ORDER  BY D.[name];

DROP TABLE #BS1 ;


Modifying a non managed table partition I ran across a partition that was initially setup (sql 2012) beginning of 2014 to break by month but wasn't updated for 2015. The main issue is that the last partition was setup as Jan 2015 so Feb data is loading into it and I'd like to add future months to the partition and break out the Feb data out of the Jan 2015 partition.   My first thought was to load the data to another table and truncate the existing table then drop and recreate the partition as necessary but the table has over a billion records so storage and time are a consideration.  What other options would have I have separate the Feb data from the Jan 2015 partition as well as add future months with the least impact to the system?

Here's the existing partitioning function being used.

CREATE PARTITION FUNCTION [YearMonthPartFunction](date)
    AS RANGE RIGHT
    FOR VALUES (N'2014-01-01T00:00:00.000',
                N'2014-02-01T00:00:00.000',
                N'2014-03-01T00:00:00.000',
                N'2014-04-01T00:00:00.000',
                N'2014-05-01T00:00:00.000',
                N'2014-06-01T00:00:00.000',
                N'2014-07-01T00:00:00.000',
                N'2014-08-01T00:00:00.000',
                N'2014-09-01T00:00:00.000',
                N'2014-10-01T00:00:00.000',
                N'2014-11-01T00:00:00.000',
                N'2014-12-01T00:00:00.000',
                N'2015-01-01T00:00:00.000')

The best way to do this is:

Let us call your original table Original.

Create two new tables Jan and Feb) matching the schema of Original. You might want to use the partition management tool for this: https://sqlpartitionmgmt.codeplex.com/. Place these new tables directly on the filegroup you want to end up in. Do NOT place them on a partition scheme.
Add a check constraint to Jan limiting the data to only the data in January 2015
Do the same for Feb, but limiting data to February
Script and drop index on Jan and Feb
Use INSERT Jan WITH (TABLOCK)... SELECT Original WHERE Month = 'January' to populate Jan. This is bulk logged, and hence, should
run at around 40-80MB/sec with minimal impact on the transaction log
As above, for February.
Add indexes back on Jan and Feb
Switch out the old January/February partition from Original to an empty table. Original now has no 2015 records (validate this)
SPLIT the January partition function of Original on the February/January boundary (and any other months you want to prepare for)
ALTER TABLE Jan SWITCH TO Original PARTITION x where x is the partition number of January
ALTER TABLE Feb SWITCH TO Original PARTITION y, where y is the partition number of February
Profit

This is the fastest way to achieve the result with minimal impact on the disk system and transaction log.

It is rather complicated, but learning Partition Fu tricks like these are key to getting the best out of SQL Server partitioning.


how to Grant Permission on a table to a user on the other database in oracle Hi I have a table in my schema to be accessed from a user from Another Oracle databse..

I am trying with

GRANT all on a.table_name to test@o11db;

here a is my Schema and Test is another schema in o11db. But I am not able to get  it right.

Oracle article about Create database link:
To access a remote schema object, you must be granted access to the remote object in the remote database.

That means you need to grant the privileges locally (to the database on which they are) to the user as whom a user connect via the database link.

So, for example, if the database link was created like so

create database link DB_LINK
connect to TESTUSER
identified by PASSTHEWORD
using ...

then you have to

grant all on a.tablename to TESTUSER;


ORA-12899 when creating varchar virtual column based on numeric column I have a table with numeric column:

create table test (a number(2,0));

Next I want to create a virtual column based on column a:

alter table test add (b varchar2(3) as ('A:'||to_char(a)));

This results in error:

ORA-12899: value too large for column "B" (actual: 12, maximum: 42)

Why do I get this error? This works if I replace to_char with substr, but I'm curious how does Oracle determine the "actual" and "maximum" values?

I suspect the maximum value is related to maximum precision of numeric types (Oracle ignoring the data type precision), but I still don't quite understand this error. Character set is UTF-8.

The error message is somewhat strange as presented, but when you get this message creating a virtual column, the value for maximum is the minimum length that you need to specify to be able to create the column.

The reason for this is that Oracle doesn't know the maximum length of the expression to_char(a) and assumes that it will be 40 characters: 38 places of precision for the number, plus 1 for each of the decimal point and positive/negative indicator.

Add 2 characters for "A:" and the minimum length that Oracle judges as "safe" is 42.


mongo2.6: db.serverStatus().connections nothing happened? I wanted to create a user,and got authorize error.
So I modify the /etc/mongod.conf :

#auth=True

And then restart the mongo,and create the user on terminal :

use admin
db.createUser(
  {
    user: "superAdmin",
    pwd: "    ",
    roles: [ { role: "userAdminAnyDatabase", db: "admin" } ]
  }
)

It was success,then I modify /etc/mongod.conf  back to  :

auth=True

And I use remote login with robomongo
And I want to check db.serverStatus().connections
But nothing happened?
How can I fix this?

The userAdminAnyDatabase role does not grant access to run the serverStatus command. Your db.serverStatus() command is returning an authorization error and you are then referencing a non-existent connections property which results in the "no results to show" message.

You can confirm this by running db.serverStatus(), which should return something similar to the following if the current user is not authorized:

{
     "ok" : 0,
     "errmsg" : "not authorized on admin to execute command { serverStatus: 1.0 }",
     "code" : 13
}

To allow the serverStatus command you need to grant either a built-in role that includes this privilege (clusterMonitor or hostManager in MongoDB 2.6) or create a user-defined role if you want a more narrow scope.

For example, to create and grant a custom role to run serverStatus:

use admin
db.createRole({
    role: "serverStatus",
    privileges: [
        { resource: { cluster: true }, actions: [ "serverStatus" ] },
    ],
    roles: []
})

db.grantRolesToUser("superAdmin",["serverStatus"])


mysqldbcompare giving Compare table checksum FAIL error Running mysqldbcompare on two test databases on same server. There is no data discrepancy in any table but showing following message which I'm not able to understand. Any help is appreciated.

TABLE         a                                     pass    pass    -
            - Compare table checksum                                FAIL
            - Find row differences                                  pass

TABLE         b                                      pass    pass    -
            - Compare table checksum                                FAIL
            - Find row differences                                  pass

It means that the simple approach (table checksum) failed, but that the rows are identical, which is a pass.  Since the row checksum takes a lot longer than the table checksum, you want to have table checksums pass as much as possible to avoid subsequent row checksums.


SQL Server Agent Job is Delayed? I have an SQL Server Agent job set up to run every minute on the minute.

Usually it runs exactly at :00 on the clock but occasionally it waits several seconds.

See below, one of the jobs was triggered to run at 6:32AM, but only actually started running 9 seconds later.

I suppose there are many reasons this could happen, but my question is: Is there any way that I can pinpoint what is causing this (as opposed to just guessing, making a change, and hoping that fixes it)?

I read this question and answer, but this is not the issue in my case. I do have other SQL Server Agent jobs, but no other jobs were running at 6:32, and also there are no other jobs that touch any of the tables involved in this particular job. When I tried running the query in the accepted answer of that question, it always resulted in 0 rows of data.

I would take a look at not just the SQL jobs that are running, but see if Windows (or antivirus) happens to be running something at that same time. I know McAfee has a tendency to run almost at random, and it can really drain my machine's resources.


Is reply to client about transaction commit, before binary log or after? When does MySQL server reply to client that a SQL statement has completed or a transaction has been committed?

Before it writes to binary log or after?

In general, Mysql writes to the binary log at the end of the transaction. So if a client opens an explicit transaction the statements will be written into the binary log only after a commit. But if the statements involve nontransactional tables (MyIsam) it is possible that they are written to the binary log before the end of the transaction. If you have autocommit enabled,  Mysql replies to client after a statement is written to the binary log, because every statement is a transaction.

Using mysqlbinlog command utility you can do the following experiment. Open a shell with a command like this:

mysqlbinlog --read-from-remote-server --stop-never -h<host> -P<port> -u<user> -p<passwordd> binary-log.000003

Into another shell you can launch mysql client.

mysql> create table tr (i int) engine=innodb;
mysql> insert into tr values(1),(2),(3);

You could see the output of mysqlbinlog scrolling immediately with a text like this:

# at 1016
[..]
SET TIMESTAMP=1458127130/*!*/;
create table tr (i int) engine=innodb
[...]
# at 1281
[..]
SET TIMESTAMP=1458127136/*!*/;
BEGIN
/*!*/;
# at 1384
[..]
SET TIMESTAMP=1458127136/*!*/;
insert into tr values(1),(2),(3)
/*!*/;
# at 1514
[...]
COMMIT/*!*/;

If you try:

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tr values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

the output of mysqlbinlog scrolls down only after the commit.

If you try create table notr (i int) engine=myisam; and try to insert into notr , mysql writes immediately to binlog independently if the insert is inner a transaction. And the behavior can be different if you mix notransaction and transactional tables.
Hope this help.


Select with if statement return 'column does not exist' Database, application.
PostgreSQL 11.4 (Ubuntu 11.4-1.pgdg19.04+1),
Dbeaver 6.1.1.

What I need?
Return right values.

What I do.
Here is part of code.

select
if(available_count < 5, "neni skladem", "na sklade") as availability
from
...

Return this.

SQL Error [42703]: ERROR: column "neni skladem" does not exist

Where is the catch?

Thanks.

You have two errors in your statement:

There is no IF in SQL, you need to use a CASE expression
String constants are defined using single quotes in SQL ('), double quotes are for identifiers

select case
          when available_count < 5 then 'neni skladem'
          else 'na sklade'
       end as availability
from ...


Determine the currently executing statement in a long running PL/pgSQL stored procedure I have a long running PL/pgSQL stored procedure in PostgreSQL. How can I determine the currently executing statement in it?

One crude but effective method, available in any version, is to raise notices in your plpgsql function:

CREATE OR REPLACE FUNCTION foo() RETURNS void AS
$func$
BEGIN
PERFORM ...
RAISE NOTICE 'step1';

INSERT ...
RAISE NOTICE 'step2';

DELETE ...
END
$func$ LANGUAGE plpgsql

Another possibility would be to use auto-explain with

SET auto_explain.log_nested_statements = ON;

The second method gives you a lot more than you asked for. Both are crutches and work-arounds and more or less intrusive.
The upcoming feature in 9.4 by Pavel that @Craig identified in his comment seems like a major improvement:

GET DIAGNOSTICS text_var = PG_CONTEXT;

It's already documented in the /devel manual.


How to categorize rows by SUM of a column in mysql? I have a table as

id  num_items  folder
1   4
2   33
3   74
4   41
5   24
6   34
7   46
8   55
9   11

I want to assign folder number to rows but limiting the number of items in each folder (e.g. 100). Thus, increasing the folder number (next folder) if SUM(num_items) > 100.

The UPDATEd folder should look like

id  num_items  folder
1   4          1
2   33         1
3   74         2
4   41         3
5   24         3
6   34         3
7   46         4
8   55         4
9   11         5

Since, SUM make the calculation for the entire column, apparently, I need to reset the value/process of SUM after reaching the maximum item in folder.

One option might be using user-variables:

SET @folder = 1;
SET @items = 0;
SELECT id, num_items,
  (SELECT IF(((@items:=@items+num_items)>100), @folder:=@folder+1,   @folder)) as folder,
  IF(@items>100,@items:=0,@items) as checkItems
FROM foo;

Here's an sql fiddle of it, though your example id=4 does not match. In the first block, it's 44, in the expected output block it is 41, so the folders are off.

 Adding a caveat here. According to the documentation on user variables:

  As a general rule, you should never assign a value to a user variable and read the value within the same statement. You might get the results you expect, but this is not guaranteed.

Just keep that in mind.

Edit by RolandoMySQLDBA (2012-12-17 14:30 EDT)

So as to be sure of what the user variables look like, perhaps have each of the user variables printed along the way : ( See Example )

SET @folder = 1;
SET @items = 0;
SELECT id, num_items,
  @folder as CurrentFolder_Before,
  @items as CurrentItems_Before,
  (SELECT IF(((@items:=@items+num_items)>100), @folder:=@folder+1,   @folder)) as folder,
  IF(@items>100,@items:=0,@items) as checkItems,
  @folder as CurrentFolder_After,
  @items as CurrentItems_After
FROM foo;

DTest gets a +1 for his application of user variables.


How can I enforce that my composite key columns do not equal one another in MySQL? I have a pivot table in which there is a composite key made up of two columns that reference the same table. The columns are requester_id and addressee_id and both of them refer to the id field on a users table. I'm trying to make sure that something like this cannot happen:
requester_id   addressee_id   created_at
1              1              2020-08-09 18:40:23

I've also added an index expression to ensure that there can only be one unique pairing of IDs as well:
alter table `user_relationships`
add unique index `unique_relationships_index`
((least(requester_id,addressee_id)), (greatest(requester_id,addressee_id)))

To avoid having 1|2 and 2|1.

MySQL-8.0.16 enforces check constraints
alter table user_relationships add check (requester_id <> addressee_id)

Afterwards:
insert into user_relationships values (3,3,NOW())

Check constraint 'user_relationships_chk_1' is violated.

ref: fiddle


Global Temporary Table used in Stored Procedure executed concurrently across sessions I use dynamic SQL in a stored procedure and must use a global temp table so the temp table and its data is available outside the instance when sp_executesql happens.

I need the stored procedure to be able to be run multiple times simultaneously. Could I do something like this?

...

BEGIN TRY
WHILE OBJECT_ID('tempdb..##BOM', 'U') IS NOT NULL
BEGIN
WAITFOR DELAY '00:00:01'
END;

...
Here is the general idea behind the stored procedure. (This is not the code I use just a gist of what I'm doing)

CREATE TABLE ##BOM(...);
DECLARE @TBL AS TABLE(...);

INSERT INTO ##BOM
SELECT * FROM EXPLODE_BOM_F(@ORG, @JOB); --all data inserted into temp table for job

SET @SqlQuery = 'SELECT * FROM ##BOM LEFT JOIN STUFF WHERE 1=1 '; --build sql query
IF @Param IS NOT NULL
   SET @SqlQuery = @SqlQuery + ' AND ##BOM.ParamField = @Param' --add to sql query if specific params are passed to SP

INSERT INTO @TBL (Fields, field...) --insert records into table
EXECUTE sp_Executesql @SqlQuery; --from final sql query

SELECT * FROM @TBL;

Based on your 2nd code snippet in the question, if the table already exists by the time you get to the sp_executesql, then you didn't need a global temporary table (##table) to begin with: a local temporary table (#table) would work just fine. Local temporary tables are available to sub-processes, and any changes made to them (data and/or schema) are available to all levels, up to the top level where it was created.

If that code snippet is actually inaccurate, and we interpret the following:

  I use dynamic SQL in a stored procedure and must use a global temp table so the temp table and its data is available outside the instance when sp_executesql happens.

to mean that the temporary table is created within Dynamic SQL at some point AND needs to be available to other Dynamic SQL later on, then you still don't need a global temporary table: just create the local temporary table in the main process scope, prior to any Dynamic SQL, and just use the Dynamic SQL to insert into it.

The following is the code posted in the question, modified slightly to be working code, AND working with a local (not global) temporary table:

IF (OBJECT_ID(N'tempdb..#BOM') IS NOT NULL)
BEGIN
  DROP TABLE #BOM;
END;

CREATE TABLE #BOM(ObjectID INT, ObjectName sysname, ParamField VARCHAR(3));
DECLARE @TBL AS TABLE (ObjectName sysname NOT NULL, ColumnName sysname NULL);
DECLARE @SqlQuery NVARCHAR(MAX),
        @Param NVARCHAR(100);

--SET @Param = 'v';

INSERT INTO #BOM (ObjectID, ObjectName, ParamField)
  SELECT [object_id], [name], [type]
  FROM   [msdb].[sys].[objects] so

SET @SqlQuery = 'SELECT tmp.ObjectName, sc.[name]
                 FROM #BOM tmp
                 LEFT JOIN msdb.sys.columns sc
                        ON sc.[object_id] = tmp.ObjectID
                 WHERE 1=1 '; --build sql query

IF (@Param IS NOT NULL)
BEGIN
  SET @SqlQuery = @SqlQuery + ' AND tmp.ParamField = @InnerParam'; --add to sql query
END;

INSERT INTO @TBL (ObjectName, ColumnName) --insert records into table
  EXECUTE sp_Executesql @SqlQuery,
                        N'@InnerParam NVARCHAR(100)',
                        @InnerParam = @Param; --from final sql query

SELECT * FROM @TBL;

In either case, you don't need to create a real table that enforces process separation via @@SPID or a GUID. That is just extra junk that needs to be cleaned up whereas the local temporary table is inherently "thread safe" and is automatically cleaned up for you.


Replicate MySQL INSERT to SQL Server table I'm running into a challenge here trying to "sync" a MySQL table with a SQL Server table. My scenario involves my linux-based website submitting form data submissions to a MySQL database on that web server.

What I'm shooting for is to copy any inserts on the MySQL table to the SQL Server table, and I'm not finding a whole lot of support for this. I'm using SQL Server Management Studio and I have my SQL Server database and I've added the MySQL as a linked server.

I had the thought of creating a trigger on that linked server, but it doesn't look like that will work coming from the SQL Server side.

Does anyone have any experience with this? I'm looking for the "best practice" scenario for this type of situation. The syncing between these tables needs to be as close to real time as possible.

Thanks!

This has been asked before and there are a couple of useful posts.  But the work is still being left in your hands.   You might check these posts:

richardtallent has a post: https://stackoverflow.com/questions/1455018/replication-from-mysql-to-ms-sql
AndrewSQL offered an outlined approach that got more votes at: Replicating a remote MySQL database to MS SQL Server 2008

The bottom line is that there are hints, techniques, and some tools that you might be able to marshal into an approach that suits you.  But the work is left in your hands.

In the same thread as AndrewSQL, there is a post from Gaius recommending GoldenGate for MySQL and MS SQL.  But I have no experience with it.