Why did my database size almost double over night? Actually it almost doubled in a few hours.

I am hosting relatively small database in sql server 2008. By hosting I mean I have an account at hosting provider.
Last time I checked (call it hour zero) my DB was about 90MB big according to hosting data about disk space used. At the same time I did a backup and got backup file 93MB big.

Today I did some work with DB. Added few hundred record in a couple of tables. Nothing big, no blob fields nothing like that. Al this data shoud amount for less tha 0.1% of DB size.
Right now, 7 hours after hour zero I did another backup and I noticed that now my DB occupies 160MB. Meaning it almost doubled in size. But the new backup file is actually smaller than the one at hour zero. It is 81MB.

I assume that SQL Server reserves space in increments and it can reserve empty space for later use. But to double the size of DB in a few hours is a bit to much. In the past it has grown incrementaly pretty much correlated with the amount of actual data stored in DB.

I did run some transaction on DB, but all executed OK so I think none of them was left hanging.

I also regularly run elaborate query which tells me how many rows, hom much used space and how much total space every table needs. Nothing unusual there.
Another guess is that transaction log, which I admit do not know much about also takes disk space quota and I suspect this has grown out of proportions.
Just to chek I have restored the 81MB backup file, which holds the 160Mb big database. I did shrink database on that restored database and it more than halved to 77MB.
There are people who strongly advise against shrinking database, so I do not do that on production DB.

Can someone shed some light on what is going on or what should I keep my eye on?

EDIT:
Just checked.

Database files size: 131648 KB
LOG size:32448 KB

So, I guess my file size has grown. But why?

Did:

SET STATISTICS TIME ON
exec sp_MSforeachdb 'use [?]; EXEC sp_helpfile'

Got:

Growth for DB: 1024KB
Growth for log: 10%

BradC suggested I run a query, so I did and got:

db_name  type  logical_name  TotalMB  UsedMB  FreeMB  MaxSizeMB  GrowthRate
MyDb     ROWS  MyDB            128.6    73.4    55.2     1000.0     1MB
MyDB     LOG   MyDB_log         31.7     3.2    28.5       NULL     10%

Can't do much without more info; as a start run the following query on your database:

SELECT DB_NAME() as dbname, type_desc, name as logical_name,
    TotalMB = CONVERT(decimal(12,1),size/128.0),
    UsedMB = CONVERT(decimal(12,1),FILEPROPERTY(name,'SpaceUsed')/128.0),
    FreeMB = CONVERT(decimal(12,1),(size - FILEPROPERTY(name,'SpaceUsed'))/128.0),
    MaxSizeMB = CASE WHEN max_size = -1 THEN NULL
        ELSE CONVERT(DECIMAL(18, 1), max_size / 128.0) END,
    GrowthRate = CASE WHEN is_percent_growth = 1 THEN CONVERT(varchar(12),growth) + '%'
            WHEN growth = 0 THEN 'FIXED'
            ELSE CONVERT(varchar(12), growth/128) + 'MB' END,
    physical_name
 FROM sys.database_files WITH (NOLOCK)
 ORDER BY type, file_id;

This query looks into the detail of the database files, and tells you whether you are simply dealing with a lot of empty space, or actual data, and whether it is located in the data file or the log file.

You can also use the "growth rate" to determine if it simply grew in one big chunk, instead of smaller amounts.

Any further action depends on where the unexpected growth actually took place.


Are TEXT fields stored separately from the row in MySQL-ISAM? We have a "notes" table and some of the queries are getting very slow even though we're not searching on the note contents. We're wondering if moving the note content into a separate table would speed things up?

I wouldn't think separating the content would matter -- aren't TEXT fields essentially just pointers to somewhere else on disk? Or does table size matter?

Does InnoDB handle this better?

In MyISAM records are stored in blocks. There are like 20 different block types, some of can be really large. Those are used for BLOB/TEXT values. I never saw that MyISAM record stores a pointer to a BLOB (but I would not insist).

In InnoDB BLOB/TEXT values are also a part of the record and stored in-page as long as total record size not more than ~7k. Otherwise 768 first bytes are stored in-page and remaining part is in external pages. (In Barracuda format it just stores a 20 bytes pointer to the external page).


Combining two queries I am trying to write code for this query:

  Find the names of the actors that played at least in one movie since 2000 and where not directors of any movie at this period of time.

My thought was to split this query into two, then to merge the two parts. The first part will be code that finds the names of the actors that played in at least one movie since 2000. The second code will be a query that finds the actors who were not directors of any movie at this period of time.

I have this two queries:

Code for part one:

SELECT DISTINCT Actors.FirstName, Actors.LastName
FROM (Actors INNER JOIN ActorsMovies ON Actors.ActorID=   ActorsMovies.ActorID)
INNER JOIN Movies ON ActorsMovies.MovieID=Movies.MovieID
GROUP BY ActorsMovies.MovieID, Actors.FirstName, Actors.LastName, Movies.Year
HAVING COUNT(ActorsMovies.MovieID)>0 AND Movies.Year>=2000;

Code for part two:

WHERE SELECT DISTINCT Directors.FirstName, Directors.LastName
FROM( (Directors
INNER JOIN DirectorsMovies ON    Directors.DirectorID=DirectorsMovies.DirectorID)
INNER JOIN Movies ON  DirectorsMovies.MovieID=Movies.MovieID)
GROUP BY Movies.Year, Directors.FirstName, Directors.LastName
HAVING Movies.Year>=2000);

These two queries work.My difficulty is in merging them. I tried using NOT EXISTS here is my attempt:

 SELECT DISTINCT Actors.FirstName, Actors.LastName
 FROM (Actors INNER JOIN ActorsMovies ON Actors.ActorID= ActorsMovies.ActorID) INNER JOIN Movies ON ActorsMovies.MovieID=Movies.MovieID
 GROUP BY ActorsMovies.MovieID, Actors.FirstName, Actors.LastName, Movies.Year
 HAVING COUNT(ActorsMovies.MovieID)>0 AND Movies.Year>=2000
 WHERE NOT EXISTS(
 SELECT DISTINCT Directors.FirstName, Directors.LastName
 FROM( (Directors INNER JOIN DirectorsMovies ON   Directors.DirectorID=DirectorsMovies.DirectorID)
 INNER JOIN Movies ON  DirectorsMovies.MovieID=Movies.MovieID)
 GROUP BY Movies.Year, Directors.FirstName, Directors.LastName
 HAVING Movies.Year>=2000);

There are some problems with your attempt

WHERE is supposed to be before the GROUP BY (I assume this is true for ms-access)
The EXISTS predicate will be TRUE if there is a director, regardless if this has anything to do with the actor under investigation.

For the first part I would try something like:

SELECT Actors.FirstName, Actors.LastName
FROM (Actors
      INNER JOIN ActorsMovies
          ON Actors.ActorID = ActorsMovies.ActorID
     )
INNER JOIN Movies
    ON ActorsMovies.MovieID=Movies.MovieID
WHERE Movies.Year>=2000
GROUP BY Actors.FirstName, Actors.LastName

The parenthesis makes it a bit difficult to indent the query, but if I got it right ms-access requires them when more than two tables are joined.

Instead of GROUP BY we can use distinct as in:

SELECT DISTINCT Actors.FirstName, Actors.LastName
FROM (Actors
      INNER JOIN ActorsMovies
          ON Actors.ActorID = ActorsMovies.ActorID
     )
INNER JOIN Movies
    ON ActorsMovies.MovieID=Movies.MovieID
WHERE Movies.Year>=2000

Now, for every actor that satisfies these predicates we would like to make sure that they don't directed any movies after 2000

SELECT DISTINCT Actors.FirstName, Actors.LastName
FROM (Actors
      INNER JOIN ActorsMovies
          ON Actors.ActorID = ActorsMovies.ActorID
     )
INNER JOIN Movies
    ON ActorsMovies.MovieID=Movies.MovieID
WHERE Movies.Year>=2000

  AND NOT EXISTS (
      SELECT 1 -- anything will do, it's the existence of the row that's
               -- important
      FROM (Directors
            INNER JOIN DirectorsMovies
                ON   Directors.DirectorID=DirectorsMovies.DirectorID
           )
      INNER JOIN Movies
          ON  DirectorsMovies.MovieID=Movies.MovieID
      WHERE Movies.Year>=2000
        -- Correlate this sub-query with the first query
        AND Directors.FirstName = Actors.FirstName
        AND Directors.LastName = Actors.LastName
  )

I assumed here that both an Actor and a Director can be identified by their first- and last- name. This is probably not true in real life, but it is difficult to guess how they relate to each other without descriptions of the tables.

This probably wont work out of the box, but hopefully you get some ideas that will help you.


Mysql database security In a web system anyone, who gets access to the DB, can make changes in the account of any user (change posts, etc).
Is there any way to prevent this problem?
Hashing of the DB is not the way, as it's changed dynamically.
Increasing the security of the server is obvious advice, however there is no absolutely secure computer.

Limit root (and any other SUPER-privileged user) to

GRANT ... TO root@localhost ...

That prevents access from other servers.  You should hand out SUPER to very few people, and they should be aware of their responsibility.  The application should not have SUPER.

Limit application logins to the one database it uses:

GRANT ... ON dbname.* ...

That way, someone who hacks into the application code can't get past dbname.  This can be further refined via either of these:

GRANT SELECT ON dname.* ...    -- "read only"
GRANT ... ON dname.tblname ... -- "just one table"

The readonly may also need 'safe' things like

GRANT SELECT, CREATE TEMPORARY TABLE ON dname.* ...    -- "read only"

As you say, there is no absolute security.  My point here is there you can do a few things to slow hackers down.  (Same goes for honest people goofing.)

In rare cases, you may need the application to do something available only to root.  this can be done via a "Stored Procedure" that has SECURITY DEFINER (and root defines it).  That will expose only what the SP does, which might, for example, be one particular action on one particular table.


Three queries returning 1 row each So.. I've got:

3 DB  Queries returning 1 row each ....

SELECT value FROM ja_settings WHERE varname='clickatel_api_id'
SELECT value FROM ja_settings WHERE varname='clickatel_user'
SELECT value FROM ja_settings WHERE varname='clickatel_pass'

Could be re-written as:

SELECT value, 'clickatel_api_id' AS name FROM ja_settings WHERE varname='clickatel_api_id'
UNION ALL
SELECT value, 'clickatel_user' AS name FROM ja_settings WHERE varname='clickatel_user'
UNION ALL
SELECT value, 'clickatel_pass' AS name FROM ja_settings WHERE varname='clickatel_pass' ;

1 db query that returns 3 rows ...  (there's even better ways than a union... I know).
This would eliminate 2 db server roundtrips on ALL pages.

How could I write this Query?
Thank you!

I guess you want something like this:

SELECT value, varname
FROM ja_settings
WHERE varname IN ('clickatel_api_id', 'clickatel_user','clickatel_pass');


Cannot see two tables with the same name I have two non-public schemas in one database. A table named "foo" exists in both schemas and I can execute all necessary DDL & DML commands against both. However, when I execute \d, I cannot see both of them. What gives?

Repro

create schema a;
create schema b;

create table a.foo (i int);
create table b.foo (i int);

set search_path = "$user", public, a, b;

In psql

postgres=# \d
            List of relations
 Schema |   Name    | Type  |   Owner
--------+-----------+-------+------------
 a      | foo       | table | pvandivier
(1 row)

I \set ECHO_HIDDEN on to extract the underlying query run by \d and moved the relevant lines from the WHERE clause into the SELECT to examine the differences between the two table objects.

SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
  pg_catalog.pg_table_is_visible(c.oid),
  n.nspname,
  c.relkind
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname IN ('a','b')
ORDER BY 1,2;

Running this query reveals...

 Schema | Name | Type  |   Owner    | pg_table_is_visible | nspname | relkind
--------+------+-------+------------+---------------------+---------+---------
 a      | foo  | table | pvandivier | t                   | a       | r
 b      | foo  | table | pvandivier | f                   | b       | r

The relevant difference appears to be the output of pg_table_is_visible(). But why should it return "False" when asking if b.foo is visible?

  Why is pg_table_is_visible() returning False for a table I have access to?

The concept is_visible here appears to mean "the first one you see in the search_path"; and not "can you see it". You can demonstrate this for yourself by changing the ordering of schemas in the search_path.

postgres=# set search_path = "$user", public, b, a;
SET
postgres=# \d
            List of relations
 Schema |   Name    | Type  |   Owner
--------+-----------+-------+------------
 b      | foo       | table | pvandivier
(1 row)

postgres=#

Note that b.foo is returned when b is listed first in the search_path. This is supported as well by the output of the previous query re-run in the same search_path.

 Schema | Name | Type  |   Owner    | pg_table_is_visible | nspname | relkind
--------+------+-------+------------+---------------------+---------+---------
 a      | foo  | table | pvandivier | f                   | a       | r
 b      | foo  | table | pvandivier | t                   | b       | r
(2 rows)

We can now clearly see that pg_table_is_visible() is True for b and False for a. Therefore "is_visible" is an indicator of current accessibility and not an indicator of whether a relation exists.

Note that attempting to determine visibility of a non-existent object has a different set of behaviours.

postgres=# -- attempting to get the oid of a non-existent object
postgres=# select 'c.foo'::regclass;
ERROR:  schema "c" does not exist
LINE 1: select 'c.foo'::regclass;
               ^
postgres=# select 'bar'::regclass::oid;
ERROR:  relation "bar" does not exist
LINE 1: select 'bar'::regclass::oid
               ^
postgres=# -- passing an oid for a non-existent object returns NULL
postgres=# select pg_table_is_visible((random() * 1e7)::int::oid);
 pg_table_is_visible
---------------------

(1 row)

postgres=#

You can also read this somewhat in source at src/backend/catalog/namespace.c. I'm not fluent in C, but the following citations appear to support this reasoning.

pg_table_is_visible() references RelationIsVisible

Datum
pg_table_is_visible(PG_FUNCTION_ARGS)
{
    Oid         oid = PG_GETARG_OID(0);

    if (!SearchSysCacheExists1(RELOID, ObjectIdGetDatum(oid)))
        PG_RETURN_NULL();

    PG_RETURN_BOOL(RelationIsVisible(oid));
}

RelationIsVisible reveals the following code comments just above the return logic

/*
 * If it is in the path, it might still not be visible; it could be
 * hidden by another relation of the same name earlier in the path. So
 * we must do a slow check for conflicting relations.
 */


What is the corresponding MySQL TimeStamp in MS SQL Server? I need a timestamp to be created automatically when I insert information in a table. mySQL has TimeStamp but  MS SQL Server TimeStamp doesn’t correspond mySQL. Is there any other way around?

From MSDN: "The Transact-SQL timestamp data type is not the same as the timestamp data type defined in the SQL-92 standard. The SQL-92 timestamp data type is equivalent to the Transact-SQL datetime data type."

To be able to have a date automatically inserted in your table, you should have a column of type datetime with a default value of getdate() - the system function that shows the current date of the system.


What is the signifigance of a fillfactor of 20? I have a database for our ticket system in SQL Server express.  I was poking around and noticed that a lot of the indexes have a fillfactor set to 20.  What is the signifigance of it being set to 20?  Why would you set it so low?

@ 20 % you're consuming 5 times the disk actually needed for the index. I'm sure there's some business case where that might make sense, but I've never seen it. If you aren't rebuilding these regularly (rebuild returns to original fill factor level, in this case 20%), I'd be interested to see how full the indexes are after a month of operation. General ref: http://msdn.microsoft.com/en-us/library/ms177459.aspx


Mysql Database Design - identifying required patterns I'm trying to figure out which pattern (if any) might describe the problem I'm facing when designing the database for a club membership management system. I require help in identifying the pattern (if one exists) or some pointers of what considerations I need to make in order to make some headway.
Members are a main entity, with some information such as contact details on them.
Members can have central functions at the club, ie be the treasurer or commodore etc.
In order to model the club's top level hierarchy, I would have the ID from Members, an enumeration table "BoardMemberTypes" and a "BoardMembersType" table linking the two.
However, the Club also has sub-groups, each with their own hierarchy structure.
Every Member is part of at least one of the Subgroups, with the subgroup Role "member", and the membership type such as "active" or "passive".
Each of these structures have a few identical and a few specific roles. As an example:

Subgroup A has chair, vice chair, treasurer from the identical set, and "Subgroup A Technology Chair" from a list only applicable to Subgroup A
Subgroup B has a chair, a treasurer, and a "Specific Subgroup B 1" and "Specific Subgroup B 2" role
Subgoup C might have only a chair and a "Specific Subgroup C" role.

The Subgroups are the complicated bit:
Enumeration Table "Subgroup" (ID, Name)
Enumeration Table "MembershipType" (ID, Name)
Enumeration Table "MembershipFunction" (ID, Name)

First hurdle: When presenting the admin interface, I'd need to constrain MembershipFunction depending on the subgroup.
I guess this could be achieved through a link table:
 SubgroupMembershipFunctions (Id, SubgroupId, MembershipFunctionId)
 e.g. (Name output after applying joins obviously)

1    Subgroup A    Member
2    Subgroup A    Chairman
3    Subgroup A    Subgroup A Technology Chair
4    Subgroup B    Member
5    Subgroup B    Chairman
6    Subgroup B    Specific Subgroup B 1

In theory, I could now create a table "MembersSubgroupMembershipFunctions" with MembersID, SubgroupMembershipFunctionsId
At this point, I'm still lacking the abilty to store the MembershipType (active, passive) which only really applies to the "member" entry.
The end result I need would be something like this maybe
User          Subgroup    Type        Status
Justin Case   A           member      active
Justin Case   A           chairman    null(?)
Justin Case   B           member      passive
Justin Case   B           B 1         null(?)
Joe Bloggs    A           member      active
Jane Doe      B           member      active
Jane Doe      C           member      passive
Jane Doe      C           vicechair   null(?)

Any help, pointer or idea towards improving this design is greatly appreciated.

I don't know if there is a design pattern beyond "normalize properly", but I don't think you're too far off from a workable solution.
Given your description I don't see a need for a Subgroup entity - we can just have Groups and denote one of those as "The Board".
For specific GroupFunctions, we'll consider those to be a special case for GroupMembers, i.e. once someone is assigned to a group they can be assigned Chair/Vice Chair/Special Assistant to the Jr Vice Chair/etc.  Each GroupFunction can be assigned to at most one GroupMember.
As for active/passive/etc., it's entirely possible to have one level for the Member and different levels for each Group, but you might need some transaction logic if a "passive" member cannot be an "active" member of a group.  Alternately, you could derive the MembershipType for each Member based on the groups they are members of.
I'll leave the datatypes and full DDL to you (including audit tables), but this should work:
CREATE TABLE MembershipType
(
  MembershipTypeCd
 ,Name
 ,CONSTRAINT PK_MembershipType PRIMARY KEY (MembershipTypeCd)
 ,CONSTRAINT AK_MembershipType UNIQUE (Name)

);

CREATE TABLE Member
(
  MemberId
 ,MembershipTypeCd
 ,CONSTRAINT FK_Member_Has_MembershipType FOREIGN KEY (MembershipTypeCd) REFERENCES MembershipType (MembershipTypeCd)
 ,CONSTRAINT PK_Member PRIMARY KEY (MemberId)
);

CREATE TABLE MemberFunction
(
  FunctionShortName
 ,Name
 ,CONSTRAINT PK_MemberFunction PRIMARY KEY (FunctionShortName)
 ,CONSTRAINT AK_MemberFunction UNIQUE (Name)
);

CREATE TABLE Group /* A reserved keyword in most DBMS - may make sense to rename */
(
  GroupId
 ,Name
 ,CONSTRAINT PK_Group PRIMARY KEY (GroupId)
 ,CONSTRAINT AK_Group UNIQUE (Name)
);

CREATE TABLE GroupMember
(
  GroupId
 ,MemberId
 ,GroupMembershipTypeCd
 ,CONSTRAINT FK_GroupMember_Member_Of_Group FOREIGN KEY (GroupId) REFERENCES Group (GroupId)
 ,CONSTRAINT FK_GroupMember_Is_Member FOREIGN KEY (MemberId) REFERENCES Member (MemberId)
 ,CONSTRAINT FK_GroupMember_Has_MembershipType FOREIGN KEY (GroupMembershipTypeCd) REFERENCES MembershipType (MembershipTypeCd)
 ,CONSTRAINT PK_GroupMember PRIMARY KEY (GroupId, MemberId)
 ,CONSTRAINT AK_GroupMember UNIQUE (MemberId, GroupId)
)
;

CREATE TABLE GroupFunction
(
  GroupId
 ,FunctionShortName
 ,CONSTRAINT FK_GroupFunction_Available_For_Group FOREIGN KEY (GroupId) REFERENCES Group (GroupId)
 ,CONSTRAINT FK_GroupFunction_Is_MemberFunction FOREIGN KEY (FunctionShortName) REFERENCES MemberFunction (FunctionShortName)
 ,CONSTRAINT PK_GroupFunction PRIMARY KEY (GroupId, FunctionShortName)
);

CREATE TABLE GroupFunctionAssignment
(
  GroupId
 ,FunctionShortName
 ,MemberId
 ,CONSTRAINT FK_GroupFunctionAssignment_Assigned_To_GroupMember FOREIGN KEY (GroupId, MemberId) REFERENCES GroupMember (GroupId, MemberId)
 ,CONSTRAINT FK_GroupFunctionAssignment_Assigment_Of_GroupFunction FOREIGN KEY (GroupId, FunctionShortName) REFERENCES GroupFunction (GroupId, FunctionShortName)
 ,CONSTRAINT PK_GroupFunctionAssignment PRIMARY KEY (GroupId, FunctionShortName)
);

Pictures are usually easier to understand (if you are unfamiliar with IDEF1X, read this):


Foreign key with multiple table options I'm not sure if the title is very good, feel free to suggest a better one.

People can "comment" on different objects of my site,similarly to StackExchange (for example they can comment on a Question, an Answer or a Comment).
The way I've decided to design it is something like this:

CREATE  TABLE IF NOT EXISTS `comment` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `user_id` INT UNSIGNED NOT NULL COMMENT 'Who voted' ,
  `object_type` VARCHAR(45) NOT NULL COMMENT 'The table or class name of the voted object. Such as \"answer\", \"question\", ...' ,
  `object_id` INT UNSIGNED NOT NULL COMMENT 'The primary key of the object being voted on' ,
  `value` TINYINT(1) NOT NULL COMMENT '+1, -1 or 0 for a comment' ,
  `content` TEXT NULL COMMENT 'A free text comment about the object' ,
  `votes` INT UNSIGNED NULL DEFAULT 0 COMMENT 'How many votes did this comment receive' ,
  `create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
  PRIMARY KEY (`id`) ,
  INDEX `fk_vote_user` (`user_id` ASC) ,
  CONSTRAINT `fk_vote_user`
    FOREIGN KEY (`user_id` )
    REFERENCES `user` (`id` )
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = 'Keeps track of \"upvotes\" or \"downvotes\" of content.'

Probably too much info for here but basically I'm taking the approach of a generic "object_type" VARCHAR ("QUESTION" for example) and an "object_id".

Is this a good approach? Can I make foreign keys? What would you suggest to improve performance?

I am using MySQL and PHP (and Yii).

Instead of object_id and object_type columns, why not use a primary key for each object type to be commented on, but maintain the object_type (for partitioning later) so that you have question_id, comment_id, and answer_id. For each row of data, two of the columns will be null, but it is easy on your ORM (single table inheritance with a type qualifier).

Your queries will also join to the correct FK, I am not sure of the space usage for this, but I expect it to be very performant since the joins do not need additional filters on the where clause for different types as well as complex "SQL join acrobatics"


How to sort aggregate function call If I have an expensive function call that takes a JSONB and returns an INT, e.g.

CREATE FUNCTION my_expensive_function(data JSONB) RETURNS INTEGER AS

How do I go about ordering the aggregate result from calling it on a set of JSON data, e.g:

SELECT ARRAY_AGG(my_expensive_function(data) ORDER BY ???)
    FROM JSONB_ARRAY_ELEMENTS(json_set) data;

I get an error if I try to assign a name to the result:

SELECT ARRAY_AGG(my_expensive_function(data) id ORDER BY id)
    FROM JSONB_ARRAY_ELEMENTS(json_set) data;

ERROR:  42601: syntax error at or near "id"
LINE 1: SELECT ARRAY_AGG(my_expensive_function(data) id ORDER BY id)...
                                                 ^
LOCATION:  scanner_yyerror, scan.l:1134

I found I can do it by calling the function twice, but I hate that:

SELECT ARRAY_AGG(my_expensive_function(data) ORDER BY my_expensive_function(data))
    FROM JSONB_ARRAY_ELEMENTS(json_set) data;

I also found that this seems to work, but it is a bit cumbersome also:

WITH data_set AS (
    SELECT my_expensive_function(data) AS id
    FROM JSONB_ARRAY_ELEMENTS(json_set) data
) SELECT ARRAY_AGG(id ORDER BY id) FROM data_set;

UPDATE: I should have specified - Is there any magic I can do in a single query or do I have to use WITH/subquery?  Why can't I add a name to the result of the function?

Use a subquery:

SELECT array_agg(x ORDER BY x)
FROM (SELECT my_expensive_function(data) AS x
      FROM jsonb_array_elements(json_set) AS data
     ) AS q;


SQL Server 2019 Always On using Distributed Network Name We're running a couple SQL servers in Azure that are set up with an Always On availability group and Windows Failover Clustering. The servers are Windows 2019 and we're running SQL Server 2019. When we set up the cluster, it was set up to use a Distributed Network Name instead of a static cluster IP address. Thanks to this we shouldn't need an internal load balancer according to these notes: https://github.com/MicrosoftDocs/azure-docs/issues/34648.

I'm struggling to understand exactly how this works though. Based on what I read, it seems like our connection strings will point to the DNS name of the cluster (let's call it AgCluster). If I look in DNS, there is an A record for AgCluster pointing to sql1 and another pointing to sql2. When I use AgCluster in my connection string it seems to always connect me to the primary server, even if I have ApplicationIntent=ReadOnly set. When I query @@SERVERNAME I always get the same server.

So with the Distributed Network Name setup, what should I use in my connection strings to make sure read/write queries go to the primary and read only go to a secondary? Any guides on setting this up in general would be helpful. Thanks!

Something you were trying to do in May 2020 is doable now. In SQL Server 2019 CU8 (released 01/10/2020) they have added support for Distributed Network Name.
Have a look at this KB4578579 - Improvement: Availability Group listener without the load balancer in SQL Server 2019
Also for instructions on how to configure dnn take a look at this atricle Configure a DNN for failover cluster instance


Multiple match() against() in a MySQL query Same question asked on here, with no answers.

I'm using MySQL 5.7.17 on Windows 10.
I got this table:

CREATE TABLE `tbl1`(
  `id` int(10) unsigned NOT NULL,
  `col1` varchar(128) NOT NULL DEFAULT '',
  `col2` varchar(128) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  FULLTEXT KEY `col1_idx` (`col1`) WITH PARSER `ngram`,
  FULLTEXT KEY `col2_idx` (`col2`) WITH PARSER `ngram`,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And I would like to make a query that col1 MUST have str1 and col2 MUST NOT have str2, I run multiple MATCH() AGAINST() in a query,like this

SELECT * FROM tbl1
 WHERE MATCH(col1) AGAINST('+str1' IN BOOLEAN MODE)
   AND MATCH(col2) AGAINST('-str2' IN BOOLEAN MODE);

But this query returns nothing.

When I use EXPLAIN, like:

EXPLAIN SELECT * FROM tbl1 WHERE
 MATCH(col1) AGAINST('+str1' IN BOOLEAN MODE)
   AND MATCH(col2) AGAINST('-str2' IN BOOLEAN MODE);

it returns:

+----+-------------+-----------+------------+----------+-------------------------+---------------+---------+-------+------+----------+-----------------------------------+
| id | select_type | table     | partitions | type     | possible_keys           | key           | key_len | ref   | rows | filtered | Extra                             |
+----+-------------+-----------+------------+----------+-------------------------+---------------+---------+-------+------+----------+-----------------------------------+
|  1 | SIMPLE      | tbl1      | NULL       | fulltext |    col1_idx, col2_idx   | col1_idx      | 0       | const |    1 |    11.11 | Using where; Ft_hints: no_ranking |
+----+-------------+-----------+------------+----------+-------------------------+---------------+---------+-------+------+----------+-----------------------------------+

How do i make the query works? Any advice would be appreciated.

First, I think we need to assume that str1 and str2 occur in less than half the rows.

I think you will need to CREATE TEMPORARY TABLEs, one with list of id from one MATCH, one from the other.  Create an index on id.
 Then JOIN the two tables.

It's messy, but FT wants to go first, yet you have two FT clauses in a single query.  Hence my attempt to turn it into two queries.

Since you are using 5.7, and it has the smarts to create an index on a derived table:

SELECT t3.*
    FROM ( SELECT id FROM tbl1 WHERE MATCH ... +str1 ... ) AS t1
    JOIN ( SELECT id FROM tbl1 WHERE MATCH ... -str2 ... ) AS t2  USING(id)
    JOIN tbl1 AS t3  ON  USING(id);

Give it a try. -- I am guessing.


shard key specification I have a document like so:

{
    "_id" : ObjectId("52f29f91ddbd75540aba6dae"),
    "teams" : [
        {
            "name" : "Washington Bullets",
            "score" : 100,
            "home" : false,
            "won" : 1,
            "players" : [
                {
                    "player" : "Jeff Ruland",
                    "pts" : 19
                },
                {
                    "player" : "Cliff Robinson",
                    "pts" : 22
                },
                {
                    "player" : "Gus Williams",
                    "pts" : 21
                },
                {
                    "player" : "Jeff Malone",
                    "pts" : 18
                },
                {
                    "player" : "Charles Jones",
                    "pts" : 4
                },
                {
                    "player" : "Dan Roundfield",
                    "pts" : 2
                },
                {
                    "player" : "Perry Moss",
                    "pts" : 7
                },
                {
                    "player" : "Dudley Bradley",
                    "pts" : 0
                },
                {
                    "player" : "Darren Daye",
                    "pts" : 5
                },
                {
                    "player" : "Tom McMillen",
                    "pts" : 2
                },
                {
                    "player" : "Manute Bol",
                    "pts" : 0
                }
            ]
        },
        {
            "name" : "Atlanta Hawks",
            "score" : 91,
            "home" : true,
            "won" : 0,
            "players" : [
                {
                    "player" : "Dominique Wilkins",
                    "pts" : 32
                },
                {
                    "player" : "Tree Rollins",
                    "pts" : 9
                },
                {
                    "player" : "Cliff Levingston",
                    "pts" : 8
                },
                {
                    "player" : "Spud Webb",
                    "pts" : 12
                },
                {
                    "player" : "Kevin Willis",
                    "pts" : 13
                },
                {
                    "player" : "Randy Wittman",
                    "pts" : 6
                },
                {
                    "player" : "Scott Hastings",
                    "pts" : 4
                },
                {
                    "player" : "Lorenzo Charles",
                    "pts" : 4
                },
                {
                    "player" : "Sedric Toney",
                    "pts" : 0
                },
                {
                    "player" : "Ray Williams",
                    "pts" : 0
                },
                {
                    "player" : "Jon Koncak",
                    "pts" : 3
                }
            ]
        }
    ]
}

I want to shard by teams.name. I tried doing:

sh.shardCollection("finalNBA.list", {teams:{name:1}})

But I get the error that the shard key can only be 1 or hashed. Does anyone know how to to enable sharding using an embedded field?

Try using, The key here is dot notation.

sh.shardCollection("finalNBA.list", {"teams.name":1}})

Make sure you have already created an index for test.name field.


SQL Relationship between Subtree Cost and Performance Time What is the general relationship between "SQL Final Subtree cost" and "Query time performance"?

Example:
When tuning a query and it goes from
Subtree cost 0.2 to 0.1,
does that mean query time will be twice as fast? I do not see this occurring in my queries.

We have a Server and cannot truly measure query performance even with "Set statistics time on" and "DBCC DROPCLEANBUFFERS". There are different processes going on in the server, transactions, programs, background items.

Thanks,

The subtree cost represents the estimated cost of a plan. It can be useful when investigating why the query optimizer chose one plan over another. For example, you might see a plan with a hash join and think that a loop join would have been a more efficient choice. Adding a query hint to force a loop join and comparing subtree costs can be helpful in determining why SQL Server chose a hash join.

The estimated cost of the plan will often not match the "performance time" of a query for many reasons, including hardware differences, blocking by other processes, overall server workload, model limitations, and assumptions based on imperfect information. In addition, a subtree cost of 0.1 versus 0.2 is really not a meaningful difference at all. If you have a query that has a low relative cost to the rest of your workload but that query runs for a long time that's a hint that the query optimizer is making an incorrect assumption or deduction. The root cause of those types of issues often comes down to cardinality estimates. On the other hand, sometimes a relatively expensive query will run for a long time. Looking at the parts of the plan estimated to take a long time can provide useful clues as to why the query is running for a long time. However, some query tuners will tell you to not look at estimated costs at all.

Below are a few example queries just to show that it's possible to have an extreme difference between the estimated cost and the run time. I'm testing on SQL Server 2017, but it's possible to come up with similar demos in all versions. First I put 100k sequential integers into a heap:

CREATE TABLE dbo.OptimizerUnits (ID BIGINT NOT NULL);

INSERT INTO dbo.OptimizerUnits WITH (TABLOCK)
SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);

Consider the following query:

SELECT ID
FROM dbo.OptimizerUnits
WHERE
(ID % 10) % 101  = 10

A human can look at that query and deduce that it won't return any rows, but the optimizer currently doesn't have that kind of logic built-in. It instead guesses that about 990 rows will be returned. That gives the following query a total estimated cost of 79590.1 units:

WITH OptimizerUnitsCTE (ID) AS
(
    SELECT ID
    FROM dbo.OptimizerUnits
    WHERE
    (ID % 10) % 101  = 10
)
SELECT TOP (100) t1.ID, t2.ID, t3.ID
FROM OptimizerUnitsCTE t1
CROSS JOIN OptimizerUnitsCTE t2
CROSS JOIN OptimizerUnitsCTE t3
ORDER BY t1.ID + t2.ID + t3.ID DESC;

However, the query runs in under 50 ms on my machine.

Now let's go in the other direction. Consider the following query:

SELECT ID
FROM dbo.OptimizerUnits
WHERE
(ID % 10) % 101  = 1
AND (ID % 10) % 102  = 1
AND (ID % 10) % 103  = 1
AND (ID % 10) % 104  = 1

Once again, a human could deduce that the above query will return exactly 10000 rows. The query optimizer does not know that and it guesses that the query will return just 16.7439 rows. This results in an estimated cost of 1.45306 optimizer units for the following query:

WITH OptimizerUnitsCTE (ID) AS
(
    SELECT ID
    FROM dbo.OptimizerUnits
    WHERE
    (ID % 10) % 101  = 1
    AND (ID % 10) % 102  = 1
    AND (ID % 10) % 103  = 1
    AND (ID % 10) % 104  = 1
)
SELECT TOP (100) t1.ID, t2.ID, t3.ID
FROM OptimizerUnitsCTE t1
CROSS JOIN OptimizerUnitsCTE t2
CROSS JOIN OptimizerUnitsCTE t3
ORDER BY t1.ID + t2.ID + t3.ID DESC;

I ran the query for a while on my machine and estimate that it would take around 4.5 days to complete.

In summary, poor cardinality estimates made a query with a cost of 79590.1 units take under a second and a query with a cost of 1.45306 units take about 4.5 days.


Does amazon rds PostgreSQL require vacuum? It isn't clear if auto vacuum is running in rds. Thanks!

Run this query to show if/when your tables were last vacuumed.

SELECT * FROM pg_stat_all_tables;

A table won't be vacuumed until the insert/update/delete threshold is reached, which is 20%.

You can tweak the setting in RDS by creating a new DB Parameter Group and change the setting you want.


SQL Server 2012 Replication, how to ignore action with specific trigger? SQL Server 2012. Database is in merge replication, no conflicts presented. Current example is simply for understanding, but real schema is very huge to describe. Schema and trigger at SQLFiddle for clear view.

Database has two tables Store and Debug. Store is in replication and has actual data. Debug is outside of replication and presents only on publisher (no need to debug subscribers).

Table Store has trigger which activates at every delete query and dumps current time to Debug.

While debugging some queries in trigger I realized that any query that matches the insert/update/delete in trigger are transferring to subscribers. As described above subscribers don't have the table Debug. While SQL Server Agent synchronize it catches a lot of conflicts about insert to this table:

  Invalid object name 'Subscriber_dbname.dbo.Debug'.

I don't use NOT FOR REPLICATION parameter for trigger because subscriber can delete row too and I need to catch this moment only on publisher. What can you suggest to avoid conflicts except having this table in every subscriber and creating dummy trigger for delete after insert having debug table always clean in any subscriber? Maybe there's any way to bypass insert/delete/update made from specific triggers?

I think I solve it successfully in the easiest way:

CREATE TRIGGER [dbo].[LotController]
   ON  [dbo].[Store]
AFTER DELETE
AS
BEGIN
   IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Debug]') AND type in (N'U'))
   BEGIN
       SET NOCOUNT ON;
       INSERT INTO [Debug](data,value)
       VALUES ('Time',GetDate())
   END --Table not exists
END --Trigger


Is there an equivalent to OPENROWSET in Oracle? Is there an equivalent to OPENROWSET in Oracle?

From OPENROWSET (Transact-SQL):
This method is an alternative to accessing tables in a linked server and is a one-time, ad hoc method of connecting and accessing remote data by using OLE DB.

Answer provided by John Eisbrener in a comment

The effective equivalent is a Database Link.  Sadly there's not an identical equivalent to what OPENROWSET does in one Oracle command or statement.  You have to declare the db link first before accessing it.  If you don't want to edit a tnsnames.ora file, you can create the dblink using an in-line statement, which allows you to programmatically emulate OPENROWSET functionality.


ALTER TABLE SET LOGGED locked by autovacuum metrics is an unlogged table that has around 3M rows, I have just executed the ALTER TABLE metrics SET LOGGED to keep this data logged. But it seems to be locked by some autovacuum running process.

  pid  | usename  | blocked_by |              blocked_query
-------+----------+------------+------------------------------------------
 13462 | postgres | {32090}    | ALTER TABLE sensors.metrics SET LOGGED;

    duration     |                                 query                                  |  pid
-----------------+------------------------------------------------------------------------+-------
 02:03:08.131365 | autovacuum: VACUUM pg_toast.pg_toast_315873283 (to prevent wraparound) | 32090

My autovacuum settings for a 32 core and 98gigs RAM:

 autovacuum_max_workers
------------------------
 12
 autovacuum_vacuum_cost_limit
------------------------------
 -1
 autovacuum_vacuum_cost_delay
------------------------------
 20ms
 vacuum_cost_page_hit
----------------------
 1
 vacuum_cost_page_miss
-----------------------
 10
 vacuum_cost_page_dirty
------------------------
 20

Can i do something to avoid locking, or fix this locking without terminating a the alter table process that is loggin the data?

You can terminate the autovacuum (32090).  You should not make a habit of doing that, but in this situation it should be OK.

You might want to first run:

select age(relfrozenxid) from pg_class where relname='pg_toast_315873283';

And make sure the reported value is well below 2 billion.

You also might want to check that pg_toast_315873283 is the toast table for sensors.metrics.  I don't see why it would block in the first place were that not the case, but it doesn't hurt to verify those assumptions.

On the other hand, you could just be patient and let the autovacuum finish.  The vacuum needs to happen, and it will generate less WAL traffic if you get the vacuum done while it is still unlogged.

On the third hand, you might want to start a manual VACUUM FREEZE sensors.metrics in one session, and then go cancel both the ALTER TABLE and the autovac, and then do the ALTER TABLE once the vacuum has finished.  This will get done faster than the autovac (the default setting for manual vacuums do not use IO throttling), and again does work that needs to be done eventually and is probably better done while table is still set to UNLOGGED.


RMAN restore to lower disk space I have 30.7GB backup file generated from RMAN. The original ./datafile is 950.28GB. According to previous DBA, there is a lot of free space in table space. Now, I have 300GB free disk space in hard disk. How can i restore the backup file using limited disk space ? Thkb4

One of the first things Oracle does at restore time is pre-create the data files to the defined size. So you do need a way to store the full size of the files. A compressed filesystem could be used for that and with a little luck it works. It just does not solve the problem.

What you do want is to re organize the source database. The quickest way is to prepare a new database with correct sized tablespaces and datafiles. Next use datapump to transport the schemas to the new database. If needed, this can even be done online by using smart tools like dbvisit replicate (or goldengate if money is of no importance). Trash the old database when the two are in sync.


How to handle csv data in postgres? I'm trying to create a simple bi tool similar to Power BI (see here).

You can see in Power BI that it allows the user to upload the CSV files (say with 10 million rows too). Once uploaded we can select a particular column in the csv files in the UI to create visualizations.

I was wondering how the database schema will look like for this particular operation? If the user could able to save 3,4,5 and n columns of csv, how the UI is so quick and response enough to get back the data? Are there any techniques for handling CSV data in Postgres sql?

Kindly help me out.

I'm a newbie to postgres and database in general.

1/ I was wondering how the database schema will look like for this
  particular operation?

To manage upload file, I use a simple table to save info of those files such as file_name, file_path, upload_date and more.
The point here, I don't save those files in PG. I will upload them to other storage (Google Storage) and save path to file_path.
For example:

file_name   file_path                           upload_date
csv_01      www.storage.com/bucket/csv_01       2017-22-22 11:10:00
csv_02      www.storage.com/bucket/csv_02       2017-22-22 11:15:00

  2/ If the user could able to save 3,4,5 and n columns of csv, how the UI
  is so quick and response enough to get back the data?

In terms of app, there are some libraries to handle UI (depends on your language).
This app will retrieve CSV info from PG, base on that it will load data from storage and show to user.

  3/ Are there any techniques for handling CSV data in Postgres sql?

You can use COPY command to process CSV data. But note that

  COPY naming a file or command is only allowed to database superusers,
  since it allows reading or writing any file that the server has
  privileges to access

Please read this article How to import CSV file data into a PostgreSQL table to know more .


How do I track why my SQL Azure occupied space pulsates? This is inspired by this StackOverflow question. I have this code:

SELECT CONVERT(INT, SUM(reserved_page_count) * 8 / 1024) FROM sys.dm_db_partition_stats

for detecting database space consumption and it returns exactly the same result as SQL Azure management portal pie chart. The problem is the number fluctuates greatly without apparent reasons - sometimes it's around 35% and sometimes it's around 54%.

This is kinda worrying - I don't get what happens and the major concern is that we suddenly consume all the space and our service comes to a halt.

I seriously tried to look into sys.dm_db_partition_stats and sys.partitions but they look like a pile of magic numbers to me.

What data should I use to find the root cause of the fluctuations?

I'd have you look at tables and indexes with the following code:

SELECT sys.objects.name, SUM(reserved_page_count) * 8.0 / 1024 as [SizeinMB]
FROM sys.dm_db_partition_stats, sys.objects
WHERE sys.dm_db_partition_stats.object_id = sys.objects.object_id
GROUP BY sys.objects.name
ORDER BY [SizeinMB] DESC

You will want to check the size of the objects at regular intervals to see if you can determine which ones are in flux.


Postgres minor version upgrade What is the recommended method to perform a minor version upgrade of postgres on linux (Centos)? I am looking at upgrading from 9.5.4 to 9.5.5.

Just do a dist-upgrade or the like on Linux, or install the newer versions on Windows/OSX. Minor versions ensure total compatibility with the data directory (heap, and index).

Stands to reason though, you'll have to stop the database during the upgrade.

From the official docs on versioning

  Minor releases are numbered by increasing the last part of the version number. Beginning with version 10, this is the second part of the version number, e.g. 10.0 to 10.1; for older versions this is the third part of the version number, e.g. 9.5.3 to 9.5.4. The PostgreSQL team only adds bug fixes to minor releases. All users should upgrade to the most recent minor release as soon as possible. While upgrades always have some risk, PostgreSQL minor releases fix only frequently-encountered, security, and data corruption bugs to reduce the risk of upgrading. The community considers not upgrading to be riskier than upgrading.

So essentially, PostgreSQL is so conservative that minor-version number upgrades are only issued when there is more risk to not-upgrading than upgrading. I've been working with PostgreSQL for about 15 years. I don't do any additional backups nor take any special precautions when doing minor upgrades. Not saying you can't -- even the docs say they come with "some risk."


Enforcing minimum cardinality in One To many design? If we have the following One to Many database design.

Team

+------+-----------+
| t_id | team_name |
+------+-----------+
| 100  |  germany  |
+------+-----------+

FootballPlayers

+--------+------+-------------+--------+
|  p_id  | t_id | player_name | number |
+--------+------+-------------+--------+
|    1   |  100 |   T Muller  |   11   |
|    2   |  100 |   T Peters  |   8    |
|    1   |  100 |    Messi    |   2    |
|    1   |  100 |   J Cole    |   10   |
+--------+------+-------------+--------+

If a Team must have a minimum of 11 FootballPlayers, how can we enforce this within the database design?

Could I use Triggers? Perhaps One To Many is wrong? ...

Declare a constraint on Players, something like

check (10 < (select count(*) from p_id where t_id = @t_id))

and ensure that your application inserts at least 11 players the first time.

It's not clear this is a good idea, though.  It's one thing to say every player belongs to a team.  It's something else to say all teams have 11 players.  In the first case, you're enforcing referential integrity: what would teamless player be?  (Well, you might have worked with him.  I have.)  In the second case, you're imposing a business rule: does a team cease to exist if player #11 quits?  Is it the case that when player #11 quits, the whole team must be deleted?

There might be such a rule, but it seems more likely (from this distance) that the team continues to exist but becomes ineligible to play.  In that case you might want a view

create view EligibleTeams as
select * from Teams as t where 10 <
    (select count (t_id) from Players where t_id = t.t_id)

Also, is it clear you need t_id and p_id?  Not to me.  I think your keys are the names of the players and the teams.  Definitely you don't want two Ravens or whatever, and the odds that you'll have two players named Roger Staubach on one team are longer than the Cowboys winning the World Series.


SQL SERVER 2016, Restore database get 15517 I got "15517" error When I tried to restore database when the process reached 100%

I googled several hours,the recommended solution is to change the database owner , I did so
ALTER AUTHORIZATION ON DATABASE::[joy] TO [sa]

I checked the database owner has been changed successfully.

Then I restored again, but I still got the same previous "15517" error.
Is there anyone can help me?

Can you check what login was mapped to the dbo user in the original SQL Server instance (where the database came from)?
My guess is that the dbo user points to a login that doesn't exists, and that is wnat is setting of this situation.
If that dbo (again, check on the original server) points to a Windows login, then just create that login. If it points to a SQL Server login, then create it using the same SID ad on the originating server. You can for instance use sp_help_revlogin on the originating server to script out the CREATE LOGIN command.
Where did you change the owner by the way? On the originating server? If so, then the login for the dbo user should definitely exist!
Perhaps it is because you who do the restore isn't a sysadmin?


Query tuning - performance Could you please help me tune this query. It takes over half an hour to run! I want to identify repeat bookings (can only be identified using an annoying varchar field - email)

UPDATE #Repeats --a temp table
SET    Repeats = (SELECT Count(col1)
                  FROM   Booking b1 -- a massive table -- 8 million rows
                  WHERE  email IN --a varchar(100) field
                         (SELECT Email
                          FROM   Booking b2
                          WHERE  DateDiff(day, b2.BookedDate, b1.BookedDate) > 1)
                         AND BookingStatusId != 7
                         AND #Repeats.BookedMonth = MOnth(BookedDate)
                         AND #Repeats.BookedYear = Year(BookedDate)
                         AND #Repeats.Code COLLATE Latin1_General_CI_AS = CONVERT(VARCHAR, b1.Code)
                         AND b1.BookedById = 5)

The table has a columnstore index that the plan is picking up.. Even then it is slow. If I drop the columnstore index, then the plan uses the nonclustered index on email. Thus there are two indexes on booking table. 1. columnstore index 2. nonclustered index on email.

I'm thinking along the lines of joins (instead of 'where email in') or using Window functions. Since I am not very well versed in these areas, I am a bit lost.

You should look at the Execution Plan and see where most of the cost is for the query. Without the results of the Execution Plan it is difficult to offer advice.

I suspect the query optimizer is using the best process possible for the query as it is structured. You might try breaking the query into smaller parts, like query the email field to get the results into a temp table first, then run your main query using the temp table of emails.

The might not make the query run faster, but this might expose information in the Execution Plan that will help you troubleshoot.

Another option is to use include to add columns to your non-clustered index to return all the values in the index scan to maybe speed up the process.


Does the Int type primary key insert order affect query performance in MySQL? Table t has an Int type primary key id without "Auto Increment"

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);

Use the following three ways to insert data:

Insert sequence id: 1-10000
Descending insert id: 10000-1
Random insert id: 1-10000

Does the order in which rows are inserted as suggested above affect performance in select, order by id and query operations?

I already answered a question like this

Oct 26, 2012 : How badly does innodb fragment in the face of somewhat out-of-order insertions?
Jun 28, 2012 : Benefits of BTREE in MySQL

Of the three methods to insert data you mentioned, I would easily choose method #3 : Random insert id: 1-10000. Why ?

Ascending or Descending Ordered Insertion into an index structure will generate root and node splits 45% of the time as I mentioned in my two earlier posts.

In contrast, the ordering does not matter when it comes to reloading of a table from a mysqldump or upon the execution of a LOAD DATA INFILE command. That's because of the use of sorting mechanisms after the completion of the data load of the table and the index creation can be performed.

For example, look at how a MyISAM table is reloaded.

Data Pages for .MYD are populated
Index Pages for .MYI are populated with unique keys in parallel with Data Page Reload
Secondary Indexes are built by the internal execution of Repair by sorting

In that instance, order does not matter.

Switching gears to SELECT ORDER BY, performance could be nominally affected if needed keys from indexes are so split apart that multiple index pages must be accessed for ranges of data, this again leads me to choose method #3. The lower the frequency of index page splits, the fewer pages need to be read by an ORDER BY request.


Why the Accounting DB Schemas do not allow to maintain a clean Products Table? As far as I know, the database schema of the vast majority of Accounting Systems follows this architecture:

Considering all the FKs, I know that the product table is going to grow in time and, if I don't want to corrupt anything, it will not let me to delete any discontinued product. Same goes for most of the other tables. I understand the reason behind using all those FKs, but following this scenario, I am mortgaging myself using those FKs today for a great pain in the future. If we use this DB in a high volume transaction company for some years, I'm almost certain that we will encounter performance and maintenance issues.

I'm asking from an amateur point of view, does anybody know if there exists another DB schema for Accounting Systems that allow to clean those tables?

One practice I've seen in financial systems is to snapshot all relevant data at the time of the transaction and store it statically. This would entail relevant product, partner, transactional, audit etc. values being serialized (JSON, XML or proprietary binary format) into a single column. "Relevant" includes de-referencing foreign keys and eliminating system-internal surrogate keys.

Once serialized the future life of the transaction need not rely on the original normalized values being available. They can be amended or removed to satisfy the on-going needs of the OLTP system they are there to support.

Yes, this does duplicate the data and increase the size of the database. These systems did not leave the completed transactions in situ for long, however. Batch jobs promptly removed those rows from the OLTP database into fulfillment, archive and warehouse databases.

This had several benefits. The OLTP schema was separated from that of the other databases. Audit trail was guaranteed as the serialized values were never re-written. Patches to the reference tables could never possibly affect in-process activity. And, as in your case, no-longer needed values need not be retained simply to support history.


What is the best method to add error handling in SQL 2005 stored procs? What’s a good way to make stored procs robust enough that they can scale very well and also contain error handling?

Additionally, whats the best way to handle multiple error scenarios in a stored proc and have an intelligent feedback system that will return meaningful error information to the calling apps?

Alex Kuznetsov has a great chapter in his book Defensive Database Programming (Chapter 8) that covers T-SQL TRY...CATCH, T-SQL transactions & SET XACT_ABORT settings, and using client-side error handling. It will help you a lot in deciding which of the options makes the most sense for what you need to accomplish.

It is available for free at this site. I am in no way affiliated with the company, but I do own the hard copy version of that book.

There are a lot of little details on this subject that are explained very well by Alex.

Per Nick's request... (but not all of this is in the chapter)

In terms of scaling, you need to be brutally honest about which activities need to be in the db code and which should be in the app. Ever notice how fast-executing code tends to come back to designing for a single concern per method?

The easiest way to communicate would be custom error codes (> 50,000). It's also pretty fast. It does mean you'd have to keep the db code and app code in sync. With a custom error code, you can also return useful information in the error message string. Because you have an error code strictly for that situation, you can write a parser in the app code tailored to the error's data format.

Also, which error conditions need retry logic in the database? If you want to retry after X seconds, then you're better off handling that in the app code so the transaction doesn't block as much. If you are only re-submitting a DML operation right away, repeating it in the SP could be more efficient. Keep in mind, though, that you'll have to possibly duplicate code or add a layer of SPs to accomplish a retry.

Really, that's currently the biggest pain with TRY...CATCH logic in SQL Server at the moment. It can be done, but it's a bit of an oaf. Look for some improvements coming to this in SQL Server 2012, especially re-throwing system exceptions (preserving the original error number). Also, there's FORMATMESSAGE, which adds some flexibility in constructing error messages, especially for logging purposes.


Updating Database Email Profile for all notifications I am creating an email profile for database email in a script that needs to be run on 200+ instances. The script will create a database email profile to send out alerts from agent jobs. Is there a way to go through all the jobs that exist on an instance and change a parameter so it will use the new email profile I created instead of the email profile its currently using? (added bonus if afterwards i can clean up all the unused email profiles)
DECLARE @sb INT;
DECLARE @sc INT;
SET @sb =
(
    SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb'
);
SET @sc =
(
    SELECT CONVERT(INT, value_in_use)
    FROM sys.configurations
    WHERE name = 'Database Mail XPs'
);
IF (@sb = 1 AND @sc = 1)
BEGIN
    PRINT 'Creating email profile';
    DECLARE @account_id INT;
    EXEC msdb.dbo.sysmail_add_account_sp @account_name = 'DatabaseServices - SQLMail Account',
                                         @display_name = N'XXX - Database Services',
                                         @replyto_address = N'noreply@blah.com',
                                         @description = N'SQL Server generated email from databases services team',
                                         @mailserver_name = 'yyy.xxx.pvt',
                                         @email_address = 'ITDatabaseServices@texasmutual.com',
                                         @port = 25,
                                         @account_id = @account_id OUTPUT;
END

I think what you're looking for is msdb.dbo.sysjobs and the notify_email_operator_id field specifically. If you can get the ID of the profile after creation, then you likely just need to update the table sysjobs references in a single UPDATE statement, on each instance you run your script on.


Understanding trigger behavior I'm having trouble with a trigger which I can't get my head around.

I have a table called TABLE_A with an AFTER INSERT trigger that executes a procedure. The procedure basically does the following three steps….

Process the data in the TABLE_A row to create a new row in TABLE_B.
Updates the TABLE_A row to indicate it’s been processed.
Updates a summary TABLE_C to increment a record count.

It implements this with three sequential SQL statements. Although I've simplified the first statement, there are no intermediate steps here, just three bits of SQL one after the other:-

--Insert new record that includes the record ID of the source.
--NOTE - recIdB is an autogenerated ID from a database sequence.
INSERT INTO TABLE_B(idA, lots_of_other_fields….
VALUES (new.recordIdA, lots_of_other_values…..
RETURNING recIdB INTO recentRecIdB;

--Update original record in TABLE_A to confirm it’s been processed.
UPDATE TABLE_A SET processed = true WHERE recIdA = new.recIdA;

--Update the summary table with the new record identifier.
UPDATE TABLE_C SET newRecords = (newRecords +1), recentId = recentRecIdB;

The trigger normally runs happily for hours on end processing millions of newly inserted records. Occasionally it will go squiffy and simply not execute the 2nd two update statements.

I know that the insert into TABLE_B has worked, because I can see the record with the unique ID from TABLE_A.
I know that UPDATE TABLE_A hasn't executed (not successfully anyway) because the 'processed' flag isn't set.
I know that the trigger can't have failed between the INSERT and the 1st UPDATE because there is no code between the two statements i.e. nothing to fail.
I can't see how UPDATE TABLE_A can possibly fail because it's so damn simple! there are no calculations to go wrong, a single row is being updated based on a unique identifier provided by the database itself - not a value I've calculated.

My only explanation for UPDATE TABLE_A not being executed is that the code failed before it got there. However I don't see how this can be the case because if the preceding INSERT INTO TABLE_B had failed then there would be no new record in TABLE_B.

My final guess is that first part of INSERT INTO TABLE_B is working (hence the new record I can see afterward) but the final part (RETURNING recIdB INTO recentRecIdB;) has failed. Is this possible? If the trigger bombs-out at this point it would explain what I'm seeing - a new record in TABLE_B but no updated record in TABLE_A

The following may also be relevant.

The code has been working (unchanged) for years without problem. The
code still works 90% of the time but now experiences periods of a few
hours at a time when it never gets past the 1st statement
TABLE_A and TABLE_B both use a record ID generated from the database
as follows:-

.

recida integer NOT NULL DEFAULT nextval('table_a_recida_seq'::regclass)
recidb integer NOT NULL DEFAULT nextval('table_b_recidb_seq'::regclass)

There are no other processes running on the database. The process adding records to TABLE_A is doing nothing else, this trigger is the only running code.
The two updates UPDATE TABLE_B and UPDATE TABLE_C either both work or
both fail together - hence my assumption that the error occurs
between INSERT TABLE_B and the 1st update.

The final thing that may be relevant is that the record identifiers used by TABLE_A and TABLE_B (recIdA and recIdB shown above) are both INTEGERS and are both over 2 billion. However, they are both still several million records away from the maximum INTEGER value of 2,147,483,647. (this is a current issue and I'll be updating to BIGINT asap)

Any suggestions? I'm already looking into how to raise messages to help de-bug triggers but any ideas would be appreciated, could the INSERT statement be inserting a record correctly and then failing at RETURNING X INTO Y? Could the problem be down to the sequences approaching maximum size?

UPDATE - The source records are being inserted in blocks of INSERTS involving a couple of hundred records at a time. (several hundred INSERTS per transaction)

I cannot figure out what exactly is the problem, but I suggest that you add something like this after the update of table_a:

IF NOT FOUND THEN
   RAISE EXCEPTION 'row % in table_a not found!', NEW.recida;
END IF;

Then at least you can keep the inconsistency from happening.

If the trigger fails, all the effects of the statement and the trigger will be rolled back, because they are always in a single transaction.


Adding subscription for replication in sql server when i am trying to add subscription on subscriber part in SQL server. error be like :
This database is not enabled for publication.

Can you please suggest me how to solve this issue..
Note:IS Adding Transaction subscription script is different ? & Adding Transaction pull subscription script is different ?
Can you please guide me.
Thanks in advance

This is the article that helped me with replication.  So the database that you are sending from is your publisher and you'll need to go to replication/local publications and create a new publisher with the databases you want to send.

You may or may not use a middleman/distributor but the subscriber is where you want the database(s) to end up and you will need to set that up as well.

https://www.sqlshack.com/sql-server-replication-overview-of-components-and-topography/


MyISAM table keeps crashing. What are my options? My MYSQL table is very large (250GB, around 200M rows). Recently I've been putting it through a lot -- parallel processes that do a large number of reads.

It has started locking and crashing with alarming regularity. About once per week. The recovery is obviously time consuming.

Does this seem consistent with overloading, or should I suspect another cause (e.g., hardware failure)?

Next, what are the best steps for resolving it? I think switching to InnoDB will probably take a very long time. I can dial down the processes accessing it, but I'm unclear how low to go to stop being "abusive". Any advice?

In most cases, crashing a MyISAM table is nothing more than throwing mud on the MyISAM file header. It keeps a running count of the number of times the file has been opened. It increments upon opening and decrements upon closing.
You may want to look at the value for open-files-limit. This is an option that you cannot dynamically set with mysqld running. It can be configured at startup. What is interesting is its explanation in the MySQL Documentation:

Changes the number of file descriptors available to mysqld. You should
try increasing the value of this option if mysqld gives you the error
Too many open files. mysqld uses the option value to reserve
descriptors with setrlimit(). If the requested number of file
descriptors cannot be allocated, mysqld writes a warning to the error
log.
mysqld may attempt to allocate more than the requested number of
descriptors (if they are available), using the values of
max_connections and table_open_cache to estimate whether more
descriptors will be needed.

By default, mysqld sets this values based on the OS and how many maximum open file handles mysqld believes the OS will give to it.
You can take a risk and raise that number in /etc/my.cnf and restart mysql. As mentioned in the MySQL Documentation, a warning will be posted.
Talk to your sysadmin to see if your OS can have its file handle limit raised.
Once you can get it raised, restart mysql and see if the open_files_limit was raised as well.


How can I determine who dropped a DB in SQL Server 2005 (and when)? Is there a way to find out who dropped a database in SQL Server 2005 and the time / date that it was dropped?

If it was recent enough, it may still be in the default trace. The basic assumption will be that the DROP event will be the last event recorded for this database. So if someone dropped the database 'splunge' you should be able to catch it this way (again depending on how long ago it happened):

DECLARE @FileName VARCHAR(255)

SELECT @FileName = SUBSTRING(path, 0,
    LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc'  --'
  FROM sys.traces WHERE is_default = 1;

SELECT TOP 1 LoginName, HostName, ApplicationName, StartTime, TextData
  FROM sys.fn_trace_gettable( @FileName, DEFAULT ) AS gt
  WHERE EventClass = 47 AND DatabaseName = 'splunge'
  ORDER BY StartTime DESC;


Perform Sum In Update Statement I am trying to run this update statement, but it keeps giving me the error of

  (3 row(s) affected)
  Msg 157, Level 15, State 1, Line 9
  An aggregate may not appear in the set list of an UPDATE statement.

What do I need to do diff in order to be able to have the sum added to the second field?

Create Table #ABCD
(
  time1 decimal(16,4)
  ,time2 decimal(16,4)
)
Insert Into #ABCD(time1) Values
('12.01'), ('13.04'), ('22.22')

Update #ABCD
SET time2 = SUM(time1/60/60)

One way is to calculate the aggregate inside a CTE (I blogged about this last month):

;WITH CTE AS
(
  SELECT time1, time2, t2 = SUM(time1) OVER()
  FROM #abcd
)
UPDATE CTE SET time2 = t2/60/60;

But I don't understand the value of putting the same SUM(), aggregated over the whole table, into that column for every single row. Seems easier to just store this single value in a variable, or calculate it at runtime (just use the query inside the CTE).


Ctrl-F1 shortcut stopped working in SSMS 2014 I've had the Ctrl-F1 query shortcut bound to sp_whoisactive for years (this technique is described in How to assign CTRL+F1 to sp_whoisactive in ssms 2012?).  A few days ago, it stopped working. If I hit Ctrl-F1, SQL Studio now launches Books Online in Internet Explorer.  Has anyone else seen this problem?  Is there a fix?

I confirmed that the shortcut it still set.  Changing the shortcut does not fix the problem, Ctrl-F1 still launches BoL.  Cycling SQL Studio has had no effect.

FWIW, this happened after IT restarted the server while I was logged in, so SSMS did not shut down clean.

I can of course use another keyboard shortcut (though sadly it seems Ctrl-1 is hardcoded to sp_help), it just bothers my OCD to use a different shortcut for the same command on different machines.

Here's a long shot: I think your Management Studio has for some reason changed the keyboard mappings to the 2012/2014 default setup, which is more similar to the regular Visual Studio apps. This changed as of SQL Server 2012 so, among other things, Ctrl+F1 now opens Books On-Line.

To verify if this is the case for you, you probably won't be able to execute queries using Ctrl+E or view estimated query plans using Ctrl+L.

I found a way to reset the keyboard shortcuts to the "classic" setup and wrote a blog post about it a while ago. The short version is:

Open the "Tools" menu i SSMS,
Select "Import and Export settings...",
Select the "Reset all settings" option, click "Next",
On the last page of the wizard, you have the option to back up your current settings. I would recommend you to do this.

Hope that helps.


Firebird Index Creation Fails I'm creating an index in a table with the following command:

CREATE INDEX IDX_CVE_OBS ON FACTF01 (CVE_OBS);

But it returns me the following error:

this operation is not defined for system tables. unsuccessful metadata update index IDX_CVE_OBS already exists

I have checked and the index name doesn't exists on that table. does the index name have to be unique across all the database? I'm not sure if the index name already exists on another table.

Index names need to be unique in the entire database; indexes are not part of table so they need to be uniquely identified. The Interbase 6.0 Language Reference says:

CREATE [UNIQUE] [ASC[ENDING] | DESC[ENDING]] INDEX index ON table (col [, col …]);

  ...
  index Unique name for the index

The Firebird Book, 2nd Edition by Helen Borrie says:

  CREATE INDEX <index-name>
  —names the index. The identifier must be distinct from all other object identifiers in the database, apart from constraint and column identifiers.


SQL Server 2008 R2 differential backups much larger than expected We have several SQL Server 2008 R2 databases for which we perform a full backup every Sunday then differential backups Monday to Saturday. We also do transaction log backups every 10 minutes.

The first differential backup on Monday is usually quite small, but Tuesday to Saturday are much larger but similar in size to each other.

I used some scripts I found which predict the differential backup size, e.g. https://dougzuck.com/sql-differential-backup-size-prediction and http://www.sqlskills.com/blogs/paul/new-script-how-much-of-the-database-has-changed-since-the-last-full-backup/, and they predict a very much smaller backup size.

Examples are:

database1, full backup size 5Gb, diff size 3.5Gb, predicted diff size 84Mb
database2, full backup size 40Gb, diff size 1Gb, predicted diff size 17Mb

As you can see, the actual sizes are hugely different and can be half the size of the full backup after only a couple of days.

I know users aren't creating or modifying the actual data to any great extent. As far as I can tell, there are no index rebuilds or other management tasks happening between the full and differential backups.

It's like something is happening on Monday which causes the Tuesday onward differentials to be huge. Backup compression is not used.

I looked at the transaction log backups and the average of the total daily size is around 40Mb. I know it's not as simple as this, but 7 days at 40Mb = 280Mb. Doesn't explain the 3.5Gb differential backup! There are no huge transaction log files. There are no bulk load operations taking place.

A small transaction log doesn't mean the diff won't be small though.  A diff backup looks at the physical page level since SQL Backups backup database pages.  When even 1 bit of data is changed in a page any point after a full backup, a flag is made active so the DB engine knows to back it up if a diff is taken.

Transaction logs largely record the minimal data needed to get the data within the pages back to how they were prior, thus it could be much smaller.  If you only changed 1 bit of data you are going to only reflect that change in your log.  When the diff backup runs, the entire 8k page is backed up.

Thus a Diff backup will ALWAYS backup the entire page if even 1 bit is changed on there.  A transaction log will record what's needed to roll that page back to the prior transactionally consistent state thus in this case it could be tiny, but the diff backup will still backup the entire page.

If you want more info check out the Backup Internals video on the Technet MCM site:
https://technet.microsoft.com/en-us/dn912438#mcm-readiness

Do you have any logging?  If not check out your default trace.  It'll at least tell you some minimal information.  If you don't have any logging, this would be a great time to start.


Avoiding a possible table reference error I'm working on a project that's due in 2 weeks and have hit a possibly serious roadblock. The app that I'm working on is some tuition reimbursement management system, whose use cases are dependent on the roles of the employee (user) trying to access it. It's a solo project, in which I was given a two-page document of some high-level requirements, involving the employee, the direct supervisor, the department head, and the benefits coordinator.

After a week of trying to plan it, and work on it, I came up with this: the employees table has a foreign key to itself called manager_id (an employee is a base user, then, iff there are no records such that manager_id equals that employee's employee_id), and a department_roles_id that points to the department_roles table that has a department and a department_role_name. Here's a screenshot of my ERD for this

This approach, however, seems to have a problem. What happens when someone inserts an employee record into employees, with the same department_role_id as another record, but they have two different manager_ids (for example, if one of them was a manager of that department but the other one wasn't)? How to avert this?

NOTE: if it helps any, I'm on Oracle 11g.

Based on the comments, it sounds like every department_role will belong to one and only one manager (but a single manager could manage multiple department roles).

If this is the case, then the database design should enforce it; department_role should include the column manager_id, a foreign key to the employee table.

And, if that's true, and every employee with a manager has a department_role as well, then including manager_id in employee would be denormalizing the database; you'd have to keep the employee.manager_id in sync with the department_role.manager_id. Since you could identify the manager based on the department_role_id, manager_id shouldn't be in employee.

You brought up the concern that this would be an illegal reference in Oracle. I located a StackOverflow Q & A that addressed this.

Their solution is:

Create the table without the constraint first
Optionally, "prime the pump" by manually inserting managers and database roles before adding the constraints.
Create the constraints next (before inserting any data), using ALTER TABLE ... ADD CONSTRAINT ....
If at some point you find that you can't add a manager because a department role doesn't exist, and you can't add the role because the manager doesn't exist, you can use the following to get around it:

ALTER SESSION SET CONSTRAINTS = DEFERRED;
insert into emloyee (<column_list>) values (<value_list>);
insert into department_role (<column_list>) values (<value_list>);
commit;

That said, given your set-up, I don't think you'll need to (unless someone is explicitly given a department_role that they manage, meaning they manage themselves, which seems unlikely).

NOTE: Logically, the top person in the organization will not have a manager, and may or may not have a department role, so your constraint must allow for that (either an employee with no department_role_id, or a department_role with no manager_id). For example, either the company CEO has no department_role listed, or the department_role of "CEO" would have no manager listed.


MySQL pivot table via a view Suppose the following schema

CREATE TABLE `log_apache` (
  `date` datetime NOT NULL,
  `count` int(10) unsigned NOT NULL,
  `input` bigint(20) unsigned NOT NULL,
  `output` bigint(20) unsigned NOT NULL,
  `server` char(30) NOT NULL,
  `vhostid` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`date`,`server`,`vhostid`),
  UNIQUE KEY `date` (`date`,`vhostid`,`input`,`output`)
);

INSERT INTO log_apache VALUES ('2014-04-01', 12, 19031, 1358358, 'server 1', 11);
INSERT INTO log_apache VALUES ('2014-04-01', 1, 41051, 151977, 'server 1', 15);
INSERT INTO log_apache VALUES ('2014-04-01', 22, 4051, 11115, 'server 1', 5);
INSERT INTO log_apache VALUES ('2014-04-01', 21, 5893, 81906, 'server 2', 5);
INSERT INTO log_apache VALUES ('2014-04-01', 13, 4051, 11115, 'server 5', 11);
INSERT INTO log_apache VALUES ('2014-04-02', 9, 8195, 331551, 'server 3', 115);
INSERT INTO log_apache VALUES ('2014-04-02', 12, 19031, 1358358, 'server 2', 12);
INSERT INTO log_apache VALUES ('2014-04-02', 12, 19031, 1358358, 'server 3', 2);
INSERT INTO log_apache VALUES ('2014-04-03', 12, 19031, 1358358, 'server 4', 11);
INSERT INTO log_apache VALUES ('2014-04-01', 12, 19031, 1358358, 'server 4', 51);
INSERT INTO log_apache VALUES ('2014-04-02', 12, 19031, 1358358, 'server 1', 11);

I need to form a view of the table that looks more like this

-----------------------------------------------------------------------------------
|  Server  | 2014-04-01                                | 2014-04-02               |
----------------------------------------------------------------------------------|
| Server 1 | sum(`count`*input) | sum(`count`*output)  | input calc | output calc |
| Server 2 | sum(`count`*input) | sum(`count`*output)  | input calc | output calc |
| Server 3 | sum(`count`*input) | sum(`count`*output)  | input calc | output calc |
| Server 4 | sum(`count`*input) | sum(`count`*output)  | input calc | output calc |
| Server 5 | sum(`count`*input) | sum(`count`*output)  | input calc | output calc |
-----------------------------------------------------------------------------------

But not just with one day but for every day.

Ideally I'd have a where condition for the view so I could isolate vhostid and date range. I believe this requires a pivot table but I have very little experience with them and am not sure how to begin constructing such a query. If you could help with this and provide and explanation as to how and why the query should be that way that would be fantastic.

PS I came up with this query but clearly the result set it wrong.

SELECT t0.server, t1.`date_in`, t1.`date_out`, t2.`date_in`, t2.`date_out`
  FROM (select distinct(server) as server from log_apache order by server) as t0
  LEFT JOIN (SELECT server as Server,
                    sum(`count`*input) as date_in,
                    sum(`count`*output) as date_out
               FROM log_apache
              WHERE vhostid = 11
                AND `date` = '2014-04-01'
               GROUP BY server) as t1
    ON t0.server = t1.server
  LEFT JOIN (SELECT server as server,
                    sum(`count`*input) as date_in,
                    sum(`count`*output) as date_out
               FROM log_apache
              WHERE vhostid = 11
                AND `date` = '2014-04-02'
              GROUP BY server) as t2
    ON t0.server = t2.server

May be you need much more simple table?

----------------------------------------------------
|  Server  | Date       |                          |
---------------------------------------------------|
| Server 1 | 2014-04-01 | input calc | output calc |
| Server 2 | 2014-04-01 | input calc | output calc |
| Server 3 | 2014-04-01 | input calc | output calc |
| Server 4 | 2014-04-01 | input calc | output calc |
| Server 5 | 2014-04-01 | input calc | output calc |
| Server 1 | 2014-04-02 | input calc | output calc |
| Server 2 | 2014-04-02 | input calc | output calc |
| Server 3 | 2014-04-02 | input calc | output calc |
| Server 4 | 2014-04-02 | input calc | output calc |
| Server 5 | 2014-04-02 | input calc | output calc |
| Server 1 | 2014-04-03 | input calc | output calc |
| Server 2 | 2014-04-03 | input calc | output calc |
| Server 3 | 2014-04-03 | input calc | output calc |
| Server 4 | 2014-04-03 | input calc | output calc |
| Server 5 | 2014-04-03 | input calc | output calc |
----------------------------------------------------

That table can be produced by:

SELECT z.server_name,
       w.date,
       SUM(w.input_calc)  AS input_calc,
       SUM(w.output_calc) AS output_calc
   FROM log_apache AS w
   JOIN servers    AS z ON z.id = w.server.id           -- server_id / server_name table
   WHERE w.date BETWEEN '2014-04-01' AND '2014-04-05'   -- for example only
     AND w.server_id IN (1,2,3,4,5)                     -- for example only
   GROUP BY w.server.id, w.date
   ORDER BY w.server.id, w.date;

Some suggestions about efficiency:

Replace server char(30) NOT NULL with server_id INT NOT NULL. Grouping by INT way more faster than by CHAR() as far as no collation conversions needed.
Create separate index (date, server_id) for GROUP BY.
Precalculate count*input on insertion to spread the load.


Synchronise Oracle Tables to SQL Server Our ERP is using an Oracle database while our BI system is based on SQL Server.
We need to get the changed data in Oracle and copy it in our staging area.

At the moment we get the changed rows by comparing Oracle ROWNUM columns and copy the data with "Microsoft Connectors for Oracle by Attunity" in SSIS.
Our problem is that we have to modify the packages by hand to add dynamic queries and it's very inconvenient.

We use this connector because it's easy to create tables on SQL Server with the correct data type based on the oracle source and the performance is great.

We don't like to use linked servers, at least with the default drivers as we had a lot of trouble (a lot of casting to get data and very bad performance compared to the Attunity product)

I've seen that Attunity offers a product called "CDC for ssis".
Does anybody have any experience with this tool?
What's the best way to get changed data from Oracle to SQL Server?
What's the best way to create tables with correct data types?

You can synchronize Oracle and SQL Server in many ways. For example:

You can use sql server transactional replication with Oracle as a publisher:
https://msdn.microsoft.com/en-us/library/ms152481.aspx

You can use Oracle GOldengate (very expensive):
http://www.oracle.com/technetwork/articles/datawarehouse/oracle-sqlserver-goldengate-460262.html

regards
Giova


Why do primary keys have names of their own? From a mathematical view, granted that a table has at most one primary key, it seems to be a shortsighted design decision to refer to primary keys by some arbitrary name instead of a simple table property.

As consequence to change a primary key from nonclustered to clustered or vice versa, you have first to search for its name, than drop it and finally readd it.

Is there some advantage in using arbitrary names that I do not see or are there DBMS not using arbitrary names for primary keys?

Edit 2011-02-22  (02/22/2011 for those who don't want to sort there date):

Let my show the function, with which you can derive the names of a primary key from its tables name (using early sql-sever aka sybase system tables):

create function dbo.get_pk (@tablename sysname)
returns sysname
as
begin
    return (select k.name
    from sysobjects o
        join sysobjects k   on k.parent_obj = o.id
    where o.name = @tablename
    and o.type = 'U'
    and k.type = 'k')
end
go

As gbn states no people really like the generated name, when you do not supply an explicit name:

create table example_table (
    id int primary key
)

select dbo.get_pk('example_table')

I just got

PK__example___3213E83F527E2E1D

But why must names in sysobjects be unique. It would be perfectly OK to use exactly the same name for the table and its primary key.

Doing it that way, we didn't need to set up naming conventions, which could be accidentally violated.

Now answers to Marian:

I only used the task of changing a
clustered into a nonclustered
primary key as an example where I
need to know the actual name of the
pk to be able to drop it.
Things don't need to have proper
names, it is sufficient if they can
easily uniquely denoted. That is the
basis of abstraction. Object
orientated programming goes this
way. You need not use different
names for similar properties of
different classes.
It is arbitrary, because it is a
property of a table. The name of the
table is all you need to know if you
want to use it.

Primary keys (and other unique constraints) are implemented as indexes, and are dealt with in exactly the same way - it doesn't make sense from the programmer's point of view to have separate code paths for PKs and indexes (it would double up the potential for bugs).

Other than being referred to by foreign keys, a PK is just a unique constraint which is in turn implemented as an index, so changing the properties of a PK is just the same as changing the properties of any other index. Also having an explicit name means they can be referred to in query hints like any other index.


Implementing a function written for Postgres 9.4 in Postgres 8.4 I have a simple function (tested on Postgres 9.4) that I need to implement on Postgres 8.4. The error I get is that function format does not exist. I've been trying to find resources but 8.4 is 6 years old at this stage and it's hard to find relevant information apart from the docs!

CREATE OR REPLACE FUNCTION create_table()
  RETURNS void AS
$BODY$
DECLARE
    rows RECORD;

BEGIN
    FOR rows IN SELECT column as tablename from table
        LOOP
            EXECUTE format('
               CREATE TABLE IF NOT EXISTS %I (
                date date,
                number integer
               )', rows.tablename);

        END LOOP ;
    RETURN;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

Here's the error message:

ERROR:  function format(unknown, text) does not exist
LINE 1: SELECT  format('
                ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Any suggestions would be greatly appreciated!

EDIT: I've amended the loop in my function to this:

        LOOP
            EXECUTE 'CREATE TABLE IF NOT EXISTS ' || rows || ' (
                date date,
                number integer
               )', rows.tablename;

        END LOOP ;

However now I'm getting this error on quote_ident():

ERROR:  function quote_ident(record) does not exist

The pre-format solution used to be the good old concatenation, made safe by using quote_ident().

In your case, it would look like

...
    EXECUTE $$
        CREATE TABLE IF NOT EXISTS $$ || quote_ident(rows.tablename) || $$ (
             date date,
             number integer
        ) $$;
...

Note that 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.

Another shortcoming of version 8.4 is that the CREATE TABLE IF NOT EXISTS syntax does not exist there - it was introduced with 9.1.  You can still do a test for it:

IF EXISTS (SELECT 1 FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid WHERE relname = 'rows.tablename' AND nspname = 'your_schema')
THEN
    -- do the EXECUTE
END IF;

If you have only one schema around, you can omit the join to pg_namespace.  pg_class and pg_namespace are part of the system catalogs - you may want to check out what else can be found there.


Oracle instant client What is the difference between instant client and the original Oracle client? The Oracle client is almost 800MB and the instant client is not so much. I need to install oracle client, ODBC drivers and JDBC drivers. What is the best way to go and which one should I choose? The original 800 megs client or the instant client?

The Instant Client is easier to install and more lightweight. It doesn't even need to be installed, you can just unzip it and use it. You don't need an Oracle Home either.

On the other hand, it does not support deprecated features like Oracle Names. If you don't need any feature the instant client omits, I suggest you use it. It looks like you are covered by the list of what is supported:

  Instant Client can be used to run your OCI, OCCI, Pro*C, JDBC, and ODBC applications without installing a full Oracle Client. In addition, Instant Client supports SQL*Plus. As of Instant Client 10.2, it is also possible to develop applications for OCI and OCCI using the Instant Client SDK download.


ORA-16706: Data Guard broker worker process not available Database version is 12.1.0.1.
We have one primary database and two physical standby database and dataguard also configured.

Dataguard sync is fine working between primary and physical standby.(No lag)

While checking the status of standby database by using DGMGRL getting below error

  DGM-17016: failed to retrieve status for database "xxx_x"
  (xxxx_x - standbydbname)
  ORA-16706: Data Guard broker worker process not available

Thanks for comments for my doubts.

As now I have found a root cause and fix.
Please find a fix:

The Broker is killing the RSM0 process and restarting it because of an internal timeout that is too low. This can happen even in network choke condition also.
Please provide what the OperationTimeout is set to.
DGMGRL> connect sys/
DGMGRL> show configuration OperationTimeout;

SOLUTION
Increase the OperationTimeout or apply the patch.
If this shows low 30-120, then set it higher, up to 600 (10 mins) :

DGMGRL> EDIT CONFIGURATION SET PROPERTY OperationTimeout=600;

Configuration - Dataguard_ABC

Protection Mode: MaxPerformance
  Databases:
  ABC_P - Primary database
    ABC_F - Physical standby database
    ABC_D - Physical standby database

Then works fine.


What would make a MySQL database create tables in a different collation other than its default? I am adding tables to a Wordpress database on my localhost and it is changing my collation from utf8_general_ci to latin1_swedish_ci.  I'm kinda stumped because I'm not familiar with working directly with databases since I am new to them.  Could you explain why this happens?

My default character set is utf8 and my collation is utf8_general_ci.

Also, when I use the same code to create a table in other databases it works correctly.

HOLY CRAP I just now tried using wordpresses $wpdb class and it is doing the SAME THING!  What the crap!

FOUND ANSWER AT WORDPRESS ANSWERS ---- https://wordpress.stackexchange.com/questions/5927/default-table-collation-on-plugin-activation?rq=1

Finally actually adding this to the question.
You must specify the COLLATION while creating the table. It will inherit the collation from the database which is latin1_swedish_ci. Wordpress sets the collation only for the connection, not for the database. E.g. for WHERE or ORDER BY clauses... See Answer and comment similar to this in link above.

hrake gets the credit not I


Disable SQL Server Log Shipping to nonexistent destination I am administering a SQL Server 2008 instance in a dev environment. At some point before I became an administrator, someone enabled log shipping from one of the databases on this dev instance to another dev instance. Now, the log shipping destination server no longer exists, and the source instance is creating hourly error log entries indicating the transaction log backups are severely out of sync. When I attempt to remove the destination server or disable log shipping for this database, I receive an error, since I cannot login to the nonexistent destination server.

Short of rebuilding the destination server, which is the last resort option, what can I do to disable log shipping without having to login to the destination server?

You just need to run two stored procedures to remove the setup of log shipping from the primary database How to: Remove Log Shipping (Transact-SQL).  As the secondary doesn't exist you shouldn't need to perform step 2 & 4.


SQLAnywhere: Visualize in real time CPU and RAM consumption of sql connections I want to keep track of which connection use most of resources.

Within Sybase Central (SQL Anywhere 9, 12), for each server and database, we have pane to check in real time All Connected Users and Connections.

How to check for each connect it's cpu and ram consumption (in real time)?

You can get the approximate CPU consumption with one of the connection properties:

  ApproximateCPUTime

  Returns an estimate of the amount of CPU time accumulated by a given
  connection, in seconds. The value returned may differ from the actual
  value by as much as 50%, although typical variations are in the 5-10%
  range. On multi-processor computers, each CPU (or hyperthread or core)
  accumulates time, so the sum of accumulated times for all connections
  may be greater than the elapsed time. This property is supported on
  Windows and Linux.

See http://dcx.sybase.com/index.html#1201/en/dbadmin/connection-properties.html for usage in v12.0.1.

I'm not aware of any statistics on RAM consumption at connection level - I can see how it would be hard to get accurately as a lot of the resource might be shared with other connections.

Hope this helps.


Expired 2016 RC No Backups I have an expired version of SQL Server 2016 RC2 with SSRS installed as well.
The developer decided he didn’t get all the RDL from the SSRS before it expired and now needs to recover those.
I tried to run the installation to upgrade it, but it fails because the services are not started.
I tried to run the Edition Upgrade and it only upgrades a few components of which is not the engine.

How can I recover this?  When the developer installed and configured SSRS they did not save the encryption key, so I don’t believe I can do a new install and restore the mdf.  Is there anything else I can do.

The correct procedure when you cannot start the SQL Server services on a computer (for whatever reason), is to simply copy all the database files to a working server and attach them there.