Constraint exclusion to fetch latest N rows

Misconception 1: "Natural order"

<query without ORDER BY>
... would naturally order by id, therefore yielding the latest records.

There is no natural order in a SELECT statement. Without ORDER BY you get rows in arbitrary order. Generally that will be the cheapest order in which Postgres can satisfy your query, i.e. the order in which tuples are stored physically or in which they are retrieved after an index look-up. But there is no guarantee whatsoever. If your statement seemed to work, this was pure luck / coincidence and it can break at any time.
Use your first query instead. If logdate is, in fact, of type date, or if you need to be sure, you should add more ORDER BY items to break ties and get a stable sort order. If you don't care which, append your (new) primary key (see below):
SELECT id, logdate, content
FROM   measurement
ORDER  BY logdate DESC, measurement_id DESC
LIMIT 500;

If the latest row (biggest measurement-id) is guaranteed to have the latest logdate, you can just ORDER BY measurement_id DESC, but don't take this for granted. In a multi-user environment a row with a later logdate can be written sooner than another row with a sooner logdate.
This is one reason why your idea for the new primary key is not very useful:

10000000000 - extract(epoch from logdate), and use it as PRIMARY KEY

The other reason: it is bound to fail sooner or later if logdate is not guaranteed to be unique - which it most probably isn't.
Use a serial column measurement_id as primary key instead. Or bigserial if you expect more than 2147483647 rows over time.
Index
You claim you did it just as the documentation says, and the documentation says:

For each partition, create an index on the key column(s), as well as ...

And further down:

We probably need indexes on the key columns too:
CREATE INDEX measurement_y2006m02_logdate ON measurement_y2006m02 (logdate);
CREATE INDEX measurement_y2006m03_logdate ON measurement_y2006m03 (logdate);
...

The only tiny difference: the example in the manual uses the more sensible singular form for the table name: measurement instead of measurements.
If you go with my advice:
ORDER  BY logdate DESC, measurement_id DESC

make that:
CREATE INDEX measurement_y2006m02_logdate
ON measurement_y2006m02 (logdate DESC, measurement_id DESC);
...

More on why that would probably help:

Is a composite index also good for queries on the first field?
Working of indexes in PostgreSQL

Misconception 2: "scan all partitions"

Therefore, the query planner always scans all the partitions.

The query planner will plan to check all partitions in sequence. But as soon as the query is satisfied (500 rows are retrieved), it will stop executing. Test with EXPLAIN ANALYZE, you will see the annotation (never executed) behind remaining partitions.
If the planner shouldn't be smart enough to derive the best sequence in which to scan from your setup (can't test right now), you can give a hand with a UNION ALL query on the partitions:
(
SELECT measurement_id, logdate, content FROM measurement_y2006m03_logdate
ORDER  BY logdate DESC, measurement_id DESC
)            -- parens needed to include ORDER BY in individual legs of UNION query
UNION ALL
(
SELECT measurement_id, logdate, content FROM measurement_y2006m02_logdate
ORDER  BY logdate DESC, measurement_id DESC
)
...          -- latest partition first

LIMIT 500;

But that may not be necessary.


Filtered index statistics refresh threshold

You've got two questions in here:

  Is the above data sufficient information to base an index rebuild
  decision upon or would I need to have additional metrics involved?

One missing link is the size of the index.  If you're talking about an object with less than, say, 1000 pages, then index rebuilds aren't all that critical.

Another missing link would be the churn of the index.  Typically I see filtered indexes used when they're a very, very small subset of the entire table, and the subset changes fast.  Guessing by the name of your filtering field (OfferStatus = 0), it sounds like you're indexing just the rows where you haven't made an offer yet, and then you're going to immediately turn around and make an offer.  In situations like that, the data's changing so fast that index rebuilds usually don't make sense.

  Or, for filtered indexes do we even care about fragmentation and
  should just explicitly update statistics at X interval?

SQL Server updates stats on objects when ~20% of the data changes, but filtered indexes & stats are a special case.  They're also updated when 20% of the data changes - but it's 20% of the base table, not 20% of the filtered subset.  Because of that, you probably want to manually update stats on them periodically.  I love Ola Hallengren's maintenance scripts for this - the index maintenance stored proc has a parameter for updating statistics, and another parameter for choosing what level of sampling you want, and another parameter for choosing whether to update stats on all objects or only the ones with changed rows.  It's fantastic.


Problem deleting detached partition in DB2 LUW V9.7

When you detach a partition from a table, the detach operation returns quickly, but DB2 then starts a background process to clean up any global indexes.  You can't drop the table containing the detached partition until this process completes.

So, have you verified that the asynchronous cleanup that occurs after you issue the ALTER TABLE ... DETACH is actually finished?


How to create SOAP endpoint in SQL Server 2016?

You can no longer use SOAP/HTTP endpoints. This isn't a SQL Server 2016 problem, since this would not have worked in 2014 either. From the documentation on CREATE ENDPOINT:

  NOTE: Native XML Web Services (SOAP/HTTP endpoints) was removed in SQL Server 2012.

It has been deprecated for at least 8 years (this is the oldest version of the current document I could find):

Deprecated Database Engine Features in SQL Server 2008

If you look at that document, you will see that "The CREATE ENDPOINT or ALTER ENDPOINT statement with the FOR SOAP option" was deprecated at that time. Their suggestion for a replacement is to "Use Windows Communications Foundation (WCF) or ASP.NET instead."

Arguably, there could be a better error message, like the following (emphasis mine):

  Msg 7877, Level 16, State 1
  SOAP/HTTP endpoints are no longer supported in this version of SQL
  Server.

But your workarounds remain the same:

Continue using an older version of SQL Server.
Find another source for this information that doesn't require this type of information.
As the docs suggest, don't try to make SQL Server perform this work (it's not a web browser) - e.g. create an application or service that stores the data in SQL Server.


Need suggestions for best way to archive MySQL (InnoDB) tables

Performance

I would develop analytics that tell me how frequently older data is accessed and by what date intervals. If very few people look at anything older than 1 month, your options are numerous. If it's all over the board, that will be a different strategy.

Let's pretend that content older than 1 month is only accessed occasionally, say < 2% of the requests. Two options available to you include: partitioning and horizontal sharding, both by date. With partitioning, you simply partition by RANGE using the 'created' field in your table. With horizontal sharding you do the same thing, except using a cron job or event to create a new periodic version of your table, then migrate rows to the periodic table. With horizontal sharding, you could use UNION statements to span multiple tables.

Partitioning will have little or no effect on your data model. Sharding will require data model intelligence.


SQL Server Transactional Replication between SQL server 2019 Standard and SQL server 2016 Web Edition

After doing some research I go with Initialize a Subscription Manually


Oracle procedure with input parameter that performs multiple SUBSTR

You don't need a procedure, you can do this in a single SQL statement (though you could wrap that statement in a procedure if necessary):

insert into employee(employee_name)
with w as (select 'adam,alex,hales,jordan,jackob' names from dual)
select regexp_substr(names,'[^,]+', 1, level) from w
connect by regexp_substr(names, '[^,]+', 1, level) is not null;

select * from employee;

EMPLOYEE_ID | EMPLOYEE_NAME
----------: | :------------
          1 | adam
          2 | alex
          3 | hales
          4 | jordan
          5 | jackob

dbfiddle here


How to determine if a timestamp in one table is between two timestamps in a subquery of another table?

You can do this with one EXISTS subquery:

UPDATE "index" AS i
SET pln = TRUE
WHERE EXISTS
      ( SELECT *
        FROM planning AS p
        WHERE p.state = 'selected'
          AND p.macro NOT LIKE 'mae%'
          AND p.start_time <= i.datetime
          AND i.datetime < p.end_time
      ) ;

I changed the BETWEEN to >= start_time and < end_time, as it is more common (and more sensible) to have inclusive-exclusive datetime ranges. You can change it back to >= and <= or to (the equivalent) BETWEEN.


SQL Server Logon Auditing and Reporting the data from a DB Table

Auditing of logons is set with options available through the SSMS studio or via T-SQL. You can log success or failure or both.  The recommended destination is the Windows event log which can be queried with PowerShell or exported.

This fills up your event log fast as many service accounts are connecting many times a minute.

An audit policy can be created which can be more selective. From here

Connect the SQL server instance via SQL Server Management Studio.
Navigate to Security → Right-click “Audits” and select “New audit” →
Type in an name for the audit and select the location where the SQL
Server audit logs will be stored → Click “OK” →
Right-click the newly created audit and select “Enable audit”.
Right-click “Server Audit Specification” and select “New Server Audit Specification” →
Type in an appropriate name → Select the new audit from the audit drop-down
menu →
In the “Audit action type” column, select “Failed Login Group”
and “Successful Login Group” → Click “OK” →
Right-click on the newly
created server audit specification and select “Enable server audit
specification”.
To view the SQL Server audit login trail, navigate to
Security | Audits → Right-click the newly created audit and select
“View Audit Logs”

You can also make a system trigger but why bother when an audit policy will do the job?


In Job Step > Properties, only see two tabs: Package and Configuration — EncryptSensitiveWithPassword

In the job step properties after selecting SQL Server Integration Services Package as the type, switch Package source to SSIS Package Store to get the 9 tabs. SSIS Catalog is the default and only has 2 tabs.

SSIS Package Store:

SSIS Catalog (the default):


How to see what was executed in the database via binlogs for specific date?

You can give mysqlbinlog datetime stamps

Here is a sample datadir folder

[root@********* mysql]# ls -l /var/lib/mysql/mysql-bin.*
-rw-rw---- 1 mysql mysql 1073742028 Aug 13 03:29 /var/lib/mysql/mysql-bin.003111
-rw-rw---- 1 mysql mysql 1073741943 Aug 15 13:37 /var/lib/mysql/mysql-bin.003112
-rw-rw---- 1 mysql mysql  434864318 Aug 16 16:00 /var/lib/mysql/mysql-bin.003113
-rw-rw---- 1 mysql mysql         57 Aug 15 13:37 /var/lib/mysql/mysql-bin.index

Suppose you want to see everything executed from 10:45 AM to 2:25 PM on Aug 14th. All of Aug 14th is in /var/lib/mysql/mysql-bin.003112. Run the following:

mysqlbinlog --start-datetime="2012-08-14 10:45:00" --stop-datetime="2012-08-14 14:25:00" /var/lib/mysql/mysql-bin.003112 > SQL.txt

If your range was Aug 13th 5:00PM to Aug 14th 2:00AM, you must span two binary logs. So, you would run the following:

mysqlbinlog --start-datetime="2012-08-13 17:00:00" --stop-datetime="2012-08-14 02:00:00" /var/lib/mysql/mysql-bin.003111 /var/lib/mysql/mysql-bin.003112 > SQL.txt

Give it a Try !!!


Here's a solution for SQL Server. This does work if you have itemcode 3, 4, etc. and not just 1 and 2.

CREATE TABLE [dbo].[Test]
(
    name char(1) NOT NULL,
    itemcode tinyint NOT NULL
);

INSERT INTO [dbo].[Test](name, itemcode)
    VALUES ('A', 1), ('B', 2), ('C', 2), ('D', 2), ('E', 1), ('F', 1);

SELECT
    t.name,
    t.itemcode
    FROM
    (
        SELECT
            name,
            itemcode,
            ROW_NUMBER() OVER(PARTITION BY itemcode ORDER BY name) AS RowNum
            FROM [dbo].[Test]
    ) t
    ORDER BY
        t.RowNum,
        t.itemcode;


I like the way you are thinking. Too many people don't think about these things until they've been burned by them.

You've asked about three things. Some are more "1 answer works" kind of questions. Some are more "Lots of opinions exist.

I'll take a first stab at least. One good rule of thumb, though - don't believe what Task Manager thinks. There are a few reasons that is not so reliable. Lots of memory from different places and various versions do things a slight bit different there.

There are actually a few places to see "total amount of memory currently used by SQL Server" - In perfmon/sysmon you can see two counters - "Total Server Memory" and "Target Server Memory". Simply put, the Total Server Memory is how much SQL Server is using. The Target Server Memory is how much SQL Server - at that moment in time- would like to have based on its current load. You can find this counter Perfmon in SQLServer:Memory Manager perfmon object. If you are on a Default instance it should just be SQLServer at the parent object. If a named instance, it would be something like MSSQL$InstanceName. But Memory Manager is where you can be.

For the second question - you can see a few memory counters under the Process object - and you'll see various instances for the various processes. There are a few counters here - I'd normally stick with Private Bytes to just get a generic "how much is that process actively/actually using"

For the last question - "Total amount of memory required by SQL Server for running processes efficiently" - that is a tougher nut to crack. While mileage certainly varies, I tend to look for a few adages here when I go down this road:

Memory is cheaper than licenses and just about everything else. You can get storage a little wrong. You can even be a bit under on procs. Memory under allocation is a sin you'll pay for for a long time - so I tend to be a bit aggressive in memory allocation where I can. (Generally).
If target memory is normally much higher than total memory in the point I made above - that can be a rather clear sign (do note that SQL memory takes some time to "stabilize" after a service restart - when you first bring SQL up - target and total play a game - you'll see them each grow. Wait for some stability.
How is Page Life Expectancy? This counter is in the SQLServer:Buffer Manager (really you should look at Buffer Nodes in case you have multiple NUMA nodes and see what all of your PLEs are for the various memory allocations in each NUMA node). Is it constantly up and down like it can't sit still? While workload and so many variables come into play - it's better to have a PLE that tends to go up or at least say stable. If it looks like a heart monitor (with a normal or fast heart rate), it could be a sign that you are having some memory pressure.

But I don't like tuning monitors only - you also have to ask "is this impacting our customers?" what's the experience like? Are folks complaining? Are queries going longer than baseline? Front end response times waiting too much on data calls? Do some SQL Server waits based monitoring - what are you watiing for mostly? Are they counters related to memory and potentially to IO?

(Of course each of those opens a whole new rabit hole.. What comes first "insufficient memory" or "bad queries?" - so take a holistic appraoch. Look at your queries. Look at overall tuning. But those counters will at least be a good start for you.


Speed and size with hidden index in postgresql

Computers are fast and 1,000,000 is no longer a very large number.

Also, our query plan cut off the last line, Execution time:.  In my hands, it takes around 40ms to scan 1,000,000 rows with a parallel seq scan.  This is instantaneous in human terms, but is still 500 times longer than the planning time which you are (perhaps) confusing it with.


Unable to create a new listener in oracle 11g

I found this.

It basically says:

  For Windows goto command prompt and type lsnrctl and hit enter

LSNRCTL> SET LOG_DIRECTORY X:/oracle/admin/
LSNRCTL> SET LOG_FILE XXXX.log

  For linux or unix connect as oracle user and then type lsnrctl and hit enter

LSNRCTL> SET LOG_DIRECTORY /XXX/oracle/admin
LSNRCTL> SET LOG_FILE XXXx.log

  after doing this now configure the listner if not configured from netmanger and start it.you should have permissions to directory which u are giving in set log_directory command.

(original grammar, spelling and punctuation preserved)

EDIT 14-Apr-2016
That link appears to be dead now, use this one for reference instead.


Count all products (with qty > 0) group by all brands. But get aslo count zero for brand which have 0 products

SELECT b.brand_id,
       COUNT(p.brand_id)
FROM brands b
LEFT JOIN products p ON b.brand_id = p.brand_id
                    AND p.qty > 0
GROUP BY b.brand_id


How to go about modeling the main object of relationship?

Since the tables use the MyISAM storage engine, I have two suggestions.

SUGGESTION #1 : Use Order of Insertion

The simplest way to do that would be arbitrarily insert the main object first for the given group.

Going forward, you would run queries like these:

Main object_id for every group

SELECT group_object_id,MIN(object_id) main_object_id
FROM object GROUP BY group_object_id;

Main object for every group by name

SELECT DISTINCT B.name GroupName,C.name ObjectName FROM
(SELECT group_object_id,MIN(object_id) main_object_id
FROM object GROUP BY group_object_id) A
INNER JOIN object B ON A.main_object_id = B.object_id
INNER JOIN group_object C ON B.group_object_id = C.group_object_id;

Epilogue

To ensure the first rule, 1 object belongs to 1 and only 1 group, add this index

ALTER TABLE object ADD UNIQUE INDEX (group_object_id,object_id);

Having a unique index on those two columns along with object_id definitely prevents insertion of an object_id twice in a group. This unique would speed up GROUP BY queries like the ones I mentioned. It would also speed up the collection of the objects in a group like the following:

SET @given_group_id = 53;
SELECT object_id,name FROM object
WHERE group_object_id = @given_group_id;

and

SET @given_group_id = 53;
SELECT A.name GroupName,GROUP_CONCAT(B.Name) Objects
FROM group_object A INNER JOIN object B
ON A.group_object_id = B.group_object_id
WHERE A.group_object_id = @given_group_id;

SUGGESTION #2 : Use two auto_increments within the same table

All queries in SUGGESTION #1 still apply, but the design is a little different.

Make the design the object table as follows:

object
----------------
object_id
group_object_id
PRIMARY KEY (group_object_id,object_id)
name

MyISAM would handle the incremental values of all object_id per group_object_id. At present, only the MyISAM storage engine has this capability. I have discussed this before:

Apr 21, 2012 : How can you have two auto-incremental columns in one table?
Apr 30, 2013 : Issues converting MyISAM table to InnoDB (auto column issue)

Give it a Try !!!

UPDATE 2013-08-28 02:05 EDT

Remember I said

  The simplest way to do that would be arbitrarily insert the main object first for the given group.

EXAMPLE

Let's say you ran the following to create the tables

use test
drop table if exists group_object;
drop table if exists object;
create table group_object
(
    group_object_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(20),
    PRIMARY KEY (group_object_id)
) ENGINE=MyISAM;
create table object
(
    object_id INT NOT NULL AUTO_INCREMENT,
    group_object_id INT NOT NULL,
    name VARCHAR(20),
    PRIMARY KEY (group_object_id,object_id)
) ENGINE=MyISAM;

and load the tables with

INSERT INTO group_object (name) VALUES ('GROUP1'),('GROUP2'),('GROUP3');
SELECT group_object_id INTO @GivenGroupObjectID
FROM group_object WHERE name = 'GROUP1';
INSERT INTO object (group_object_id,name) VALUES
(@GivenGroupObjectID,'OBJ_A'),
(@GivenGroupObjectID,'OBJ_B'),
(@GivenGroupObjectID,'OBJ_C');
SELECT group_object_id INTO @GivenGroupObjectID
FROM group_object WHERE name = 'GROUP2';
INSERT INTO object (group_object_id,name) VALUES
(@GivenGroupObjectID,'OBJ_X'),
(@GivenGroupObjectID,'OBJ_Y'),
(@GivenGroupObjectID,'OBJ_Z');
SELECT group_object_id INTO @GivenGroupObjectID
FROM group_object WHERE name = 'GROUP3';
INSERT INTO object (group_object_id,name) VALUES
(@GivenGroupObjectID,'OBJ_1'),
(@GivenGroupObjectID,'OBJ_2'),
(@GivenGroupObjectID,'OBJ_3');

You will have this data:

mysql> select * from group_object;
+-----------------+--------+
| group_object_id | name   |
+-----------------+--------+
|               1 | GROUP1 |
|               2 | GROUP2 |
|               3 | GROUP3 |
+-----------------+--------+
3 rows in set (0.00 sec)

mysql> select * from object;
+-----------+-----------------+-------+
| object_id | group_object_id | name  |
+-----------+-----------------+-------+
|         1 |               1 | OBJ_A |
|         2 |               1 | OBJ_B |
|         3 |               1 | OBJ_C |
|         1 |               2 | OBJ_X |
|         2 |               2 | OBJ_Y |
|         3 |               2 | OBJ_Z |
|         1 |               3 | OBJ_1 |
|         2 |               3 | OBJ_2 |
|         3 |               3 | OBJ_3 |
+-----------+-----------------+-------+
9 rows in set (0.00 sec)

mysql>

If you run my queries, note how you get the main object from each group

mysql> SELECT group_object_id,MIN(object_id) main_object_id
    -> FROM object GROUP BY group_object_id;
+-----------------+----------------+
| group_object_id | main_object_id |
+-----------------+----------------+
|               1 |              1 |
|               2 |              1 |
|               3 |              1 |
+-----------------+----------------+
3 rows in set (0.00 sec)

mysql> SELECT DISTINCT B.name GroupName,C.name ObjectName FROM
    -> (SELECT group_object_id,MIN(object_id) main_object_id
    -> FROM object GROUP BY group_object_id) A
    -> INNER JOIN object B ON A.main_object_id = B.object_id
    -> INNER JOIN group_object C ON B.group_object_id = C.group_object_id;
+-----------+------------+
| GroupName | ObjectName |
+-----------+------------+
| OBJ_A     | GROUP1     |
| OBJ_X     | GROUP2     |
| OBJ_1     | GROUP3     |
+-----------+------------+
3 rows in set (0.00 sec)

mysql>

Note how the main inserted object for each group has the minimum object_id for that group.

When you apply the rule I first mentioned, simply look for the minimum object_id for any given group_object_id from the object table.

YOUR LAST EDIT

Adding a flag

If you already have data in the object table and want to assign a random object as the main object, you could add a flag called main as INT NOT NULL DEFAULT 0 to the object table. Assuming responsibility, you would have to check to see, upon insertion, if a group already has a main object. If so, you would update the main flag of the previous main object to 0. Then, you could update another object's main to 1.

Rearranging to change main object

If you try adhering to my rule, first insertion is main, here is what you can do so that you can choose which object becomes first in the group:

EXAMPLE

Let's say from my example, that you want take OBJ_Z in GROUP2 and make it the new main object. You would do this:

SELECT group_object_id,object_id
INTO @GivenGroupID,@GivenObjectID
FROM object WHERE name = 'OBJ_Z';
DROP TABLE IF EXISTS objectnew;
CREATE TABLE objectnew LIKE object;
--
--
INSERT INTO objectnew (group_object_id,name)
SELECT @GivenGroupID,name FROM object
WHERE group_object_id = @GivenGroupID
AND object_id = @GivenObjectID;
INSERT INTO objectnew (group_object_id,name)
SELECT @GivenGroupID,name FROM object
WHERE group_object_id = @GivenGroupID
AND object_id <> @GivenObjectID;
--
--
DELETE FROM object WHERE group_object_id = @GivenGroupID;
INSERT INTO object SELECT * FROM objectnew ORDER BY object_id;
DROP TABLE IF EXISTS objectnew;
SELECT * FROM object ORDER BY group_object_id,object_id;

If you run this code, here is the new output

mysql> SELECT * FROM object ORDER BY group_object_id,object_id;
+-----------+-----------------+-------+
| object_id | group_object_id | name  |
+-----------+-----------------+-------+
|         1 |               1 | OBJ_A |
|         2 |               1 | OBJ_B |
|         3 |               1 | OBJ_C |
|         1 |               2 | OBJ_Z |
|         2 |               2 | OBJ_X |
|         3 |               2 | OBJ_Y |
|         1 |               3 | OBJ_1 |
|         2 |               3 | OBJ_2 |
|         3 |               3 | OBJ_3 |
+-----------+-----------------+-------+
9 rows in set (0.00 sec)

mysql>

Note that OBJ_Z is the main object because it is the first in the group. Simply query for the name of the object you want to be main in the first line and then run all the code. I have tried repeatedly, and it works.

Give it a Try !!!


This is probably a bad idea, and definitely not standard good practice.

In a typical fact/dimension schema for a data warehouse, based on Ralph Kimball's methodology, the fact tables should always store data at the most granular level possible. Your example fact table is storing data at two different levels of granularity. This is bad, if only because it's repeating the same fact twice. Fact tables are intended to get very deep (many rows) and duplication by design will not scale well. I'm going to answer your question, but also show how you can avoid that duplication.

Another principle of fact table design is that they should be as narrow as possible (few columns), and the columns there are should be as compact as possible (least storage space). This minimises the storage impact of tables with so many rows.

So storing strings in the fact table should be avoided: instead, store an integer foreign key to a dimension table, that holds the larger string values. Each string is thus only stored once (in the shallow, few rows, dimension table), with the smaller int being repeated many times in the deep fact table.

Once you decide that, it follows that actually, you only need one foreign key dimension ID column to represent both your City and State columns - because if you know the city, you inherently know the state too, each city is only in one state. These descriptors (and Country) should be columns in the Location (or Geography, or Territory, or whatever) dimension. The metrics you refer to also belong in columns in the dimension table. The dimension has one primary key per City.

Fact table

LocationId    Sales
1             100
2             100

Location dimension table

LocationId    City        State        Country        SalesPeopleInCity    StateExpenses
1             Miami       Florida      USA
2             Tampa       Florida      USA

Your example fact table now needs only the first two rows - duplication problem gone! If you want to find total sales for Florida, you join the fact table to the dimension table, restrict on the State column, and group up to find the sum of sales.

(Aside: with a good indexing strategy on a decent database platform, that probably won't take long at all, even if your fact table gets really big (millions/billions of rows). If lots of people need to find lots of different totals lots of times...? That's why pre-aggregated OLAP cubing technologies exist.)

It's much more acceptable for the dimension table to contain blanks (or nulls) in some columns if need be, and also to contain repeated values. The rationale for this is that the dimension table never has that many rows relative to the fact table: storage is not a problem. Because this is not a transactional system database, consistency is also not a problem.

So your metrics that apply only to State level can be repeated for every applicable City row in the dimension table. You don't need any blanks at all in this simple example. By repeating the values on every applicable row, you can always get the State-level value instantly no matter which City is being analysed.

Location dimension table

LocationId    City        State        Country        SalesPeopleInCity    StateExpenses
1             Miami       Florida      USA            6                    $2500
2             Tampa       Florida      USA            2                    $750

This is all assuming you wish to use a simple "star schema" data model. An alternative way to model this would be using a "snowflake schema", treating the location dimension as a hierarchy and sorting data about each level of the hierarchy (City, State, Country) in a different table. Metrics would then go in the appropriate level table, or perhaps in some generic attribute (key/value) table.

There are pros and cons to each approach and it depends mainly on how you will use the data.


SQL Server 2012 Replication Backup and Restore

Initialize SQL Server replication using a database backup

More steps at
https://www.mssqltips.com/sqlservertip/2386/initialize-sql-server-replication-using-a-database-backup/


Logical optimization of joins and selections

Yes, there are cases where applying the filter after the join is preferable. But they are rare.

Consider this example:

SELECT ...
FROM a
INNER JOIN b
  ON b.key = a.key
WHERE a.some_string LIKE '%foo%'

Now, assume that very few rows in a has a matching row in b. It is now faster to first join and remove the rows from a that does not match and then apply the expensive string filter.


This type of queries are sometimes called "greatest-n-pre-group" (there's even a tag for these questions at SO!) and there are various ways to write them.

Assuming that the (customer_id, last_updated_on) is unique - i.e. there are no two rows with same last_update_on timestamp for the same customer - then you could first GROUP BY customer_id and get the maximum timestamp in a derived table and then join to the original table. Like this:
-- query 1

SELECT s.*
FROM
    ( SELECT customer_id,
             MAX(last_update_on) AS last_update_on
      FROM some_table
      WHERE redeemption_date > CURDATE() - INTERVAL 6 MONTH
        AND wallet_status <> 'EXPIRED'
      GROUP BY customer_id
    ) AS m
  JOIN
    some_table AS s
      ON  s.customer_id = m.customer_id
      AND s.last_update_on = m.last_update_on
WHERE s.redeemption_date > CURDATE() - INTERVAL 6 MONTH
  AND s.wallet_status <> 'EXPIRED';

Another variation is to first find the customer ids (similarly to above) but then join to the table using a correlated subquery. This is often more efficient if one can find easily the distinct customer ids (so, perhaps not in your case). Here is how it can be done, so you can test both:
-- query 2

SELECT s.*
FROM
    ( SELECT customer_id
      FROM some_table
      GROUP BY customer_id
    ) AS d
  JOIN
    some_table AS s
      ON  s.wallet_id =                   -- the PRIMARY KEY
          ( SELECT si.wallet_id           -- of the table
            FROM some_table AS si
            WHERE si.customer_id = d.customer_id
              AND si.redeemption_date > CURDATE() - INTERVAL 6 MONTH
              AND si.wallet_status <> 'EXPIRED'
            ORDER BY si.last_update_on DESC
              LIMIT 1
          );


MySQL, foreign key, can't create table error 150

I've had a similar problem with this, and I finally resolved it by checking the field types of the two fields that were being referenced - they have to be exactly the same type - all the way down to the "Not NUll" and "Unsigned" settings...

In your original post, the "lang" table has the "id" field defined as "INT UNSIGNED NOT NULL", whereas the "trans" table has the "lang_id" field defined as "INT NULL"...


Data dictionary best practices in SQL Server 2008 r2

In our data warehouse, we built an application (data warehouse tracker) which maintains versions of data warehouse objects over time.  When employees in our data warehouse (project manager, data modeler, DBA, ETL developer, Quality Assurance, data warehouse specialist, report writer, etc...) work on a project to build a new data model, we attach new objects and existing objects to those projects.  Every change to a database object requires an explanation of what the object is being used for or what they are fixing in that object (new/change to data model, script being run to fix a data quality issue, ETL source code migration within informatica, defect being logged to a batch--or ETL staging or atomic run for data migration, report bug, etc...).  When there is a report request using that column (or table), we require a comment about why they are using that object.  If an defect is logged to that object, we attach a comment for the defect.  Someone reading about that object can see all changes made to that object, so they can get a good understanding of what it's used for.

We started using Meta Data Manager in Informatica, but nobody ever updated the descriptions within the data dictionary.  Because there may be different definitions of that object, we store the definition of all changes over time.  Our new approach works well, because anyone can go and correct someone else's mistake, and that keeps everyone honest.  Everyone using the object can comment on it's definition.  We still track source lineage within Informatica, but we just link to that lineage from our data warehouse tracker.


Does SQL Server 2008R2 Express actually enforce the memory and CPU limit?

SQL Server Express does enforce the limits, but the limits are not what you expect:

CPU: SQL Express enforces one socket, not one CPU. Multi-core and hyper-threaded machines will leverage multiple cores on one socket and SQL Express will use 2 or 4 cores on that socket. See Maximum Number of Processors Supported by the Editions of SQL Server:

    SQL Server supports the specified number of processor sockets multiplied by the number of logical CPUs in each socket. For example, the following is considered a single processor for purposes of this table:

    A single-core, hyper-threaded processor with 2 logical CPUs per socket.
    A dual-core processor with 2 logical CPUs.
    A quad-core processor with 4 logical CPUs.

Memory: Express restricts the max size of the buffer pool to only 1GB. The buffer pool accounts only for some of the overall process memory.

See Thread and Task Architecture and Memory Management Architecture


Restrict root user access to postgres user

You cannot do what you are asking.

Here's what you should do instead:

Run your database on its own computer in its own network segment. Don't run anything else on that computer (save backup / monitoring agents)
Use a separate management network for server management / backup / monitoring
Send your db + os security logs to another machine and use a software tool to monitor them and send alerts
Don't connect to database from web servers as a superuser


Really strange behavior using variables in MySql query

http://sqlfiddle.com/#!9/c77d8/189 - so after the query runs once in the current session, the last value is reused. That's expected behavior. MySQL user variables are session-specific.

Now why the 5.5 fiddle does not start with NULL - I suppose the fiddle runs EXPLAIN on the query before actual execution, and because of https://bugs.mysql.com/bug.php?id=44802 the derived table is materialized during that EXPLAIN (the subquery is executed) and so the variable already has a value in the actual run.

I tried to verify it in http://sqlfiddle.com/#!2/c77d8/16 - you can see that I am not running the query at all, only the EXPLAIN and it is enough for @qt to get the value of 500. That does not happen in the 5.6 version http://sqlfiddle.com/#!9/c77d8/190

So it is not exactly a bug, more a design/implementation limitation of optimizer before 5.6 which is documented (even when the case with user variables is not made clear, it is just a side effect of the documented behavior).


In the logical model it means that the attribute is optional, which translates into a nullable column in the physical implementation.


You cannot change a LOB type to a non LOB type and vice versa (I assume LUW here):

http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000888.html

[...]
SET DATA TYPE altered-data-type
Start of changeSpecifies the new data type of the column. The new data type must be castable from the existing data type of the column (SQLSTATE 42837). A LOB column cannot be altered to a different data type (SQLSTATE 42837). A non-LOB column cannot be altered to a LOB data type (SQLSTATE 42837).End of change.

One way is to unload the data with export:

db2 "export to t.ixf of ixf select ..., cast(varchar_clob as varchar(100)), ... from t"

then drop all foreign key pointing to this table, drop the table, create the new table, load the data back to the table and finally add the foreign keys that where dropped.

An alternative approach is to drop the foreign keys, rename the table, create the new table and use load from cursor. When done drop the renamed table


I have some rather distressing news: ORDER BY can still wreak some havoc with filesorts.

With all the hype about this being addressed and fixed, there is simply no way to get InnoDB to effectively use the index on an ORDER BY.

Start with the Ground Zero of InnoDB row data, the Clustered Index.

Rows are tagged with

a 6-byte transaction ID field
a 7-byte roll pointer field

Rows tend to be ordered by the whatever order the data was entered. The columns of a PRIMARY KEY are included in secondary indexes and are used to search for rows in the Clustered Index, Unfortunately, the two ID fields are not really used in dictating any ordering of rows within the Clustered Index. (For more info, please see MySQL Documentation on InnoDB Physical Row Structure)

Here is something even more disturbing: Did you know you could order rows in a table by the columns of the PRIMARY KEY or any arbitrary ordering you choose?

Here is the syntax:

ALTER TABLE tblname ORDER BY col_name [, col_name] ...

This could speed up some queries that a PRIMARY KEY ordered, but what's disturbing is that it only applies to MyISAM tables. Why not InnoDB ?

According to the MySQL Documentation on ALTER TABLE ... ORDER BY:

  ORDER BY enables you to create the new table with the rows in a
  specific order. Note that the table does not remain in this order
  after inserts and deletes. This option is useful primarily when you
  know that you are mostly to query the rows in a certain order most of
  the time. By using this option after major changes to the table, you
  might be able to get higher performance. In some cases, it might make
  sorting easier for MySQL if the table is in order by the column that
  you want to order it by later.

  ORDER BY syntax permits one or more column names to be specified for
  sorting, each of which optionally can be followed by ASC or DESC to
  indicate ascending or descending sort order, respectively. The default
  is ascending order. Only column names are permitted as sort criteria;
  arbitrary expressions are not permitted. This clause should be given
  last after any other clauses.

  ORDER BY does not make sense for InnoDB tables because InnoDB always
  orders table rows according to the clustered index.

This comes as no surprise to me since I mentioned this in one of my earlier posts : (Aug 29, 2011 : Preordering the table by a specified column)

Therefore, doing an ORDER BY on an InnoDB table never guarantees proper index selection due to its internal index organization. Thus, one should not be surprised by a filesort on an InnoDB table no matter what secondary indexes the table has.


There is most probably another transaction open that did a select on that table.

Look for sessions that are " in transaction".

Make sure they end their transaction (so that the sessions shows "" or if you cannot do that, kill them with pg_terminate_backend()


Perhaps it is easiest to see why this is happening by removing the aggregate functions:

SELECT c.name
     , dogs.quant
     , cats.quant
FROM customers AS c
LEFT JOIN purchases AS dogs
    ON c.id=dogs.owner_id
    AND dogs.type = 'dog'
LEFT JOIN purchases AS cats
    ON c.id=cats.owner_id
    AND cats.type = 'cat'

+------+-------+-------+
| name | quant | quant |
+------+-------+-------+
| Matt |     1 |     4 |
| Matt |     2 |     4 |
| John |  NULL |     3 |
| John |  NULL |     2 |
+------+-------+-------+

You get a "multiplier effect" since you are joining the purchase table twice. Here's an alternative solution:

SELECT c.name
    , sum(case when type = 'dog' then quant end) AS dogs
    , sum(case when type = 'cat' then quant end) AS cats
FROM customers AS c
JOIN purchases p
    ON c.id=p.owner_id
GROUP BY c.name;


SQL Server 2008 R2 Query Last Successful Database Backup For All DBs

Yes, as long as the history data is still in msdb database. The following block of code will get your the last 100 backups including full, differential and log backups.

SELECT TOP 100
    s.database_name,
    m.physical_device_name,
    CAST(DATEDIFF(second, s.backup_start_date,
    s.backup_finish_date) AS VARCHAR(100)) + ' ' + 'Seconds' TimeTaken,
    s.backup_start_date,
    CASE s.[type]
    WHEN 'D' THEN 'Full'
    WHEN 'I' THEN 'Differential'
    WHEN 'L' THEN 'Transaction Log'
    END AS BackupType,
    s.server_name,
    s.recovery_model
    FROM msdb.dbo.backupset s
    INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
    WHERE s.database_name = DB_NAME() -- Remove this line for all the database
    ORDER BY backup_start_date DESC, backup_finish_date
    GO


Migrate a remote oracle database (11g) into a docker oracle XE database (11g)

A child table may be exported at some point of time, let's say 12:00. Then as expdp goes on, the parent table may be exported minutes later, let's say at 12:05. Your dump file contains the content of those tables from different points of times.

Yes, if those tables were changing during that 5 minutes, their relation may become inconsistent and you will get key constraint errors. The child table may have references on parent entries that do not exist.

That is why you should use the flashback_scn or flashback_time or consistent=y parameters for expdp. With them, everything will be exported as they existed at 1 specific SCN or point of time. Not using these parameters is a very common mistake.

Then for the packages. No, not even a FULL=Y expdp exports everything. Most of SYS objects are not exported.

Let's say your packages use the SYS.DBMS_LOCK package. By default, users are not able to use that package, because they need to have the EXECUTE privilege granted on it. As the package was not exported, it will not be imported. Since DBMS_LOCK will not be imported, privileges granted on it will not be imported as well. So for this specific package, you need to grant the required privilege manually.

That was just 1 example. Then there is SYS.DBMS_CRYPTO and the others.

The "broken packages" part is just too broad to answer here. You are the one with access to the packages and the code, you are the one who can check the errors.


Does reordering table rows reduce the time for subsequent ordering with the ORDER BY clause?

No it won't make a difference. You're using the same records to query and order by regardless of the order of the rows. I'm sure there's other tuning metrics that can be assessed if your query(ies) is(are) running slow.


Exporting from SQL Server 2005 for import into ER Studio 9.0

I can't believe I didn't actually google the exact error message. There's a lesson to be learned there. Upon doing so, I learned that similar errors occur in other applications. The cause is non-ASCII codes. I was exporting from SQL in Unicode.

Here is the full solution:
In SQL:

Right-click on the desired db, choose Tasks > Generate Scripts
Select the desired database, next
Leave the default options (I've tried a few things, none worked), next
Select Tables only (I don't need anything except the tables, their fields, keys, and relationships)
Select all tables (or 4 tables, it doesn't matter)
Choose "Script to file", enter a file name
Under Save as: choose ANSI text (Unicode breaks the crap out of ER/Studio)
Finish

Then import into ER/Studio. My import has been running for 2 hours so far.

Next up, trying to figure out how to make an ordered list on this site. I'm so full of fail. :)


Query running slow on script and producing error 500 when run in phpmyadmin

You should refactor the query as follows

SELECT
    B.ShipAddress1, B.CustomLabel,
    B.Quantity, B.PhService, B.UniqueID,
    B.FullName, B.ShipPostCode FROM
FROM
(
    SELECT ShipAddress1 FROM internetorders
    WHERE Complete = '0' AND LabelCreated = '0'
    GROUP BY ShipAddress1 HAVING Count(*) > 1
) A LEFT JOIN internetorders B USING (ShipAddress1);

If ShipAddress1 is not indexed, then create an index for it

ALTER TABLE internetorders ADD INDEX (ShipAddress1);


How do I use SSMS to compare Database 1 Table 1 against Database 2 Table 1?

The name of your database can't be [D:\DATABASE1.MDF], that may be the path to the physical data file. So the complete table name (3 part name as in db.schema.table) is [DATABASE1].[dbo].[Table1].

If it's a one time task, use the Red Gate SQL Data Compare. It's a great tool and it offers a trial version.

If you have Visual Studio Professional you should also have Data Comparison Tool inside it. It's another great tool to compare data and schema.

If you don't have access to any other tools to compare the data in two tables, I would use tablediff.exe, which is included in the SQL Server installation. You'll find it in the SQL Server program path. Details here, on MSDN.

If you want to use SQL, than you can use the function binary_checksum (or its relative, checksum) to generate a hash and compare over two different versions of the same row, in case you can compare row by row. You can also use the functions for each column, not only for the complete row (off course, going row by row, based on a key). See an example here.


You are probably running the service on Linux. I've had similar problems with allocating large amounts of memory on Linux (as opposed to BSD) even if the amount of memory I was trying to allocate was reasonable in proportion to the system's total memory.

MySQL suggests having innodb_buffer_pool_size / innodb_buffer_pool_chunk_size < 1000. https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_buffer_pool_chunk_size

Right now you are at about 1455. Try setting innodb_buffer_pool_chunk_size = 274857984. Or even 549715968.

This will also change the allocation pattern on the OS level which might help the operation to succeed. Also adding some swap could help the allocation to succeed by providing even more addressable virtual memory - not that the swap would ever actually be used.


Performance impact of adding a foreign key to a 1M rows table

You are far better off maintaining a foreign key (FK) constraint if your data warrants it.

MySQL will automatically create an index - the FK must reference a UNIQUE KEY (obviously, can be the PRIMARY KEY (PK) - from here).

See here for a host of reasons to as to why applying data constraints in the database is a VERY good idea. The index on the T table will increase lookup speed at the price of a small hit for inserts.

Believe me, you will save yourself an immense amount of grief by making use of the database capabilities rather than trying to do this yourself.


InnoDB Foreign key updating vs. manual in-application updating

It is better to let InnoDB enforce foreign keys. Trying to implement referential integrity in an application can be brittle and would obligate you to write

rollback operations
check constraints on keys
manual cascading of DELETEs and perhaps UPDATEs

InnoDB is equipped for all that and more. Plus, operations done in application logic is difficult to rollback, especially if one uses stored procedures or user-defined triggers.


Changing Service Account password in SQL Server - Clarification

This may not be clear to you from the documentation states:

  If the instance of SQL Server is running under a domain account and
  the password for that account is changed, the password used by SQL
  Server must be updated to the new password.

This implies that you have to change the password in Active Directory first and then follow that procedure to update the SQL Server service account password using Configuration Manager.

As long as the service is running it will keep running after you change the password in Active Directory, but it will need the new one the first time it restarts.

Trying to change the password in Configuration Manager before it's changed in Active Directory will result in a failure to start the service as the account won't be able to authenticate.

Your credential will need to be changed after changing the password in Active Directory and isn't really related to when you change your service account password. You could do the credential first and the service account second or the other way around, as long as you change it in Active Directory first.

Changing the service account or the credential account will have no impact on one another. Updating one won't change the other and you have to change both yourself.


COMMIT on top of the pt-query-digest output

Forensics...

Median time for your COMMITs: 16ms.
Typical time for spinning disk write: 10ms.
Default setting of innodb_flush_log_at_trx_commit: 1, meaning flush to disk on every commit.

Conclusion:  The killer used the candlestick in the library. Or.. Most of what you are doing is very rapidly committing transactions.

Possible ways to speed up:

innodb_flush_log_at_trx_commit = 2 -- This flushes once per second instead of once per transaction.  That is less 'safe', but for some users, the tradeoff is worth it.
Batch you actions.  Instead of one INSERT per COMMIT, do lots of INSERTs.  The main cost in COMMIT is flushing one write to the rollback log, regardless of how much went on in the transaction.
Hardware -- SSD - drops the 10ms to 1ms (or something like that); RAID with battery backed write cache - makes writes take virtually 0 ms, even without loss of safety.
Since a few COMMITs are taking over 1 second, you could look for them and see if their transactions can be improved.


Filter on a window function without writing an outer SELECT statement

Yes, most analytic functions can be rewritten with semi-join. In your case it would probably not be very efficient:

SELECT <select_list>
  FROM <table_list>
 WHERE <where_conditions>
   AND ML.PREFERENCEORDER
       = (SELECT MAX(ML_IN.PREFERENCEORDER)
            FROM <table_list> alias_in
           WHERE <where_conditions>
             AND (ML.MASTERMERCHANTID, WMP.PRODUCTCODE)
                  = (ml_in.MASTERMERCHANTID,wmp_in.PRODUCTCODE)
          )
   AND ML.PREFERENCEORDER
       = (SELECT MAX(ML_IN.PREFERENCEORDER)
            FROM <table_list> alias_in
           WHERE <where_conditions>
             AND (WMP.MERCHANTID, WMP.PRODUCTCODE)
                  = (wmp_in.MERCHANTID, wmp_in.PRODUCTCODE)
          )


Multiple differences of opinion about transaction log autogrowth settings

Here's a guess:
When you seed (auto or through a backup) the database, the info from sys.database_files is copied into sys.master_files on the other end (which lives in a table in the master database).
My guess is that a later change for the file properties, which is stored in (what you see though) sys.database_files is not reflected in sys.master_files. Perhaps some changes triggers the state to be copied into sys.master_files and some change don't? And perhaps this has been improved in later versions?
Sorry to be so vague, I don't have the environment here to test this. I would do some changes to the ldf file and see if any of those changes causes sys.master_files at the other end to be updated.
Increase the file size. Shrink it (just 1 MB). Change max size. Etc. One at a time, of course.


Is it OK to restore master db backup, after changing WSFC clustered instance into non-clustered instance

To make sure I called up the Microsoft support and they said this might work, but not supported. They suggested not to do it. According to the support guy there are some difference in system databases when clustered or not, and the difference can cause an error, and there are known such cases too.
And they gave me this link.
So the answer seems to be no.


Temporarily splitting a SQL Server cluster

It sounds (from the extra comments) more like you want a way to keep two databases in sync after one is completely cut-off for a small or medium amount of time.

I'm not fully sure on how your DR scenario is going to work, and honestly at this point I'd suggest looking into the following:

Setup a cloud hosted server through something such as windows azure, Amazon AWS, etc. Then choose what fits best for you.

Move your infrastructure to the cloud - you won't have the issue of moving servers around, DR for hurricanes (I also live in FL so I get it), etc. Pick different regions (if applicable) to make sure you're covered. This is not as easy as it sounds but may pay-off for things such as this.

1.1. Have a dedicated DR site at co-lo facilities such as Rackspace, sungard, etc.
Availability Groups - This would require a lot more infrastructure than I believe you are willing to invest due to the nature of AD integration with AGs and Clustering. While Windows Server 2012R2 gives dynamic quorum and ad-detached failover clustering, I wouldn't advise using it in this situation.
Mirroring - Since AD, etc, would be an issue, it would be possible to use a local account on the hosted platform and setup the endpoints to use certificates. It also doesn't have to be part of your domain. You may have some latency issues that would need to be worked out or potentially not be acceptable. We don't know your SLAs.
Replication - There are so many ins and outs to replication, but a simple transactional setup could work for you, assuming your database meets the requirements. There are many gotchas just like the other technologies.
Log Shipping - A very simple DR method that may work for you close enough to when the server may possibly go down. This could be completed without being domain joined, but there would be a few extra hoops to jump through.

This is not, by far, an exhaustive list but more a guide to give you something to look at and think about. You'll need to meet with your business owners and decide what is and is not acceptable. Maybe they don't need seamless integration, maybe they just need best effort. We don't know, but this is a start.


You aren't going to be able to copy and paste like you want.  However you do have several options.  Simplest is to right click on the output and save as a CSV file.  That assumes that you don't have an commas in your text data though.

Your next option (and probably best) is to use the export wizard.  Right click on your database name, then Tasks, then Export Wizard

Your source will be the database you right clicked on.

Pick an excel destination.

Select the Write a query to specify the data to transfer.

Paste in your query

You can change the sheet name in your spreadsheet here.

Next is the list of mappings for you to review

And from here you can either run the package immediately or save the SSIS package for later use/fixes.  If your export doesn't work the first time and you are at all familiar with SSIS I would save the package so you can go into it and make corrections outside the wizard.

Then just hit finish and finish again.


Summarize from unlimited level depth parent-child in mysql

The following query:

SELECT  m1.id, m1.coa, IFNULL(sum(t.debit), 0) as debit,
        IFNULL(sum(t.credit), 0) as credit
FROM master m1
JOIN master m2
    ON m2.coa like CONCAT(m1.coa, '%')
LEFT JOIN transaction t
    ON m2.id = t.id
GROUP BY m1.id;

Returns the following for me:

+----+------+-------+--------+
| id | coa  | debit | credit |
+----+------+-------+--------+
|  1 | 1    |    30 |     30 |
|  2 | 11   |     0 |     25 |
|  3 | 111  |     0 |     25 |
|  4 | 12   |    30 |      5 |
|  5 | 121  |    30 |      0 |
|  6 | 122  |     0 |      5 |
+----+------+-------+--------+
6 rows in set (0.00 sec)

Please note that all transactions are or have 1 as parent, so I am not sure why you didn't add up your third transaction.

My query returns 1 row for each id, even if it has not transactions (with credit and debit 0). Change the LEFT JOIN to a JOIN and get rid of the IFNULLs if you only want ids with movements.

Also please be aware that this query will be very badly improved in performance with indexes, and that I would recommend rethinking an alternative structure if the query is frequent and any of the two tables is very tall.


Peak load upgrade and downgrade SQL Server 2017 Standard  SQL Server 2017 Enterprise

Answers to your questions below, this doesn´t necessarily mean that I think it is a good idea, it seems quite a high risk approach to me.

Yes restoring a standard edition db to enterprise edition just works as long as it is not on a later version than the destination server.
Restoring an enterprise edition to a standard edition works AS LONG AS you have not started to use enterprise features

you can query sys.dm_db_persisted_sku_features
see
https://www.brentozar.com/blitz/enterprise-edition-features/


SSL Configuration fails with a warning MySQL 8.0

It was a silly mistake.

I was referring to the certificates which didn't exist and I was using wrong directory name.

First, one must create the certificates, using mysql_ssl_rsa_setup/openssl and place them in the designated directory.

mysql_ssl_rsa_setup --datadir=/mysql_data/ssl_certs --uid=mysql

Use following configuration under the [mysqld] section

ssl-ca=/mysql_data/ssl_certs/ca.pem
ssl-key=/mysql_data/ssl_certs/server-key.pem
ssl-cert=/mysql_data/ssl_certs/server-cert.pem

Then initialise the database using

mysqld --defaults-file=/mysql_data/config/options.cnf --user=mysql --initialize

In my case even this did not work.

The mistake I was doing was using incorrect directory name;
I was using /mysql_data/ssl-certs instead of the correct one /mysql_data/ssl_certs.


If user owns a database fixed role are they the admin of the role?

SQL Server knows four types of roles:

fixed server roles
fixed database roles
server roles
database roles

The fixed roles are system supplied and you should not change them. In fact trying to change the owner on a fixed server role aborts the connection with a level 20 error.

For the non-fixed roles the following applies:

The owner of a securable has the CONTROL privilege on that securable. That is true for any securable, including databases, schemata, procedures and roles.

In the case of a role there is no additional privilege that is implied. In particular, the role owner is not automatically a member. The role owner also does not have any permissions on the other role members.

What the CONTROL privilege does imply is the ability to add and remove members from the role, change the owner on the role and drop the role.

Keep in mind however that the role owner can add themselves as a member to that role and therefore can easily acquire the permissions granted to the role itself.

This all is true for both server roles and database roles.

I just wrote a blog post about this with a little more detail: http://sqlity.net/en/2169/role-owner/


Use GROUP_CONCAT() to generate polygons in mysql

The error is likely cause because the "cycle" isn't closed, i.e. last point in the polygon does not match the first one.

This can be from two issues:

the group_concat_max_len setting is too low (default is 1024).

Solution: increase it, either in the server or session level.

the error-prone GROUP BY used to add the additional point.

The GROUP BY you use is prone to give wrong results, as it may not always select the row you want (WHERE logical_order = 1) to read the coordinates:

(
    SELECT zone,
           CONCAT(longitude, ' ', latitude) AS coordinate,
           logical_order
    FROM zones
    UNION
    -- This is to close the polygon by adding the first coordinate also
    -- as the final coordinate of the zone.
    SELECT zone,
           CONCAT(longitude, ' ', latitude) AS coordinate,
           COUNT(zone) + 1 AS logical_order
    FROM zones
    GROUP BY zone
) t0

I'd write it like this:

(
    SELECT zone,
           CONCAT(longitude, ' ', latitude) AS coordinate,
           logical_order
    FROM zones
    UNION ALL
    -- This is to close the polygon by adding the first coordinate also
    -- as the final coordinate of the zone.
    SELECT zone,
           CONCAT(longitude, ' ', latitude),
           1000000     -- unlikely to have a million points polygon
    FROM zones
    WHERE logical_order = 1
) t0


I've got an array of dates, and I want to find out if any of the elements in the array appear within a given daterange.

Depending on the query, you can use

row aggregation with bool_or mentioned by joanolo in his answer.
or, you can use ANY with the contains operator @>

SELECT daterange('2017-01-01', '2017-01-31', '[]')  @>
  ANY(ARRAY['2017-01-01'::date, '2017-02-03'::date]);

SELECT daterange('2017-01-01', '2017-01-31', '[]')  @>
  ANY(ARRAY['2016-01-02'::date, '2016-01-09'::date]);


There are multiple reason why you wouldn't want to do multimaster replication in a circular fashion, but most of them can be summarized in one: You will have 3 different single points of failure. Check this article (which is exactly what you asked for, but probably not what you want). Standard replication is very prone to drifts in data, as it is asynchronous, producing -if you are lucky- the replication to stop for all nodes or -if you are unlucky- replication to continue with different data between nodes.

While 5.6 GTID and other features minimized those consistency problems, you still have the 3 single points of failure, as replication is single-master (multi-source replication is available only in MariaDB and MySQL 5.7, negating the need for circular replication).

If you want a setup that is multimaster (write anywhere), I would highly recommend going for a different technology that manages conflicts between nodes. Galera (you may also find it under the names of Percona XtraDB Cluster or MariaDB Cluster) is probably the way to go. It works on WANs, "resolves" conflicts (rollbacks them and retries the transaction) is multithreaded, and can be used to substitute regular replication and/or for clustering. Very recommended if your goal is HA or read scaling. It is free and open source, very widespread (I've helped several banks and hosting companies using it), compatible with standard replication and uses standard InnoDB -not a different engine- for storage.

The biggest cons, of course, are that it is a different technology which may take some time to understand (although probably easier than other clustering technologies), and it has its small quirks. But, in my very own opinion it is worth the time learning about it to make things "work properly".

Can you setup circular replication? Sure, in the article I mentioned above you have the typical recommendations of log-slave-updates, auto_increment_increment and auto_increment_offset for each node. However, the few people that may be running this have to still either avoid multi-master writes or run it in a very controlled environment, where you cannot execute updates and deletes to the same tables in concurrency. Some people also over-engineer solutions involving GTID and semi-sync replication, etc., but generally not all companies have the dedication and knowledge to put patches over a protocol that is not natively prepared for this.


Your proposed design is completely viable.

Couple of things to consider:

the table most likely will be write (INSERT) heavy
will grow indefinitely unless archived
queries most likely fall under OLAP and not OLTP (aggregations over point queries)

Workload

Assuming InnoDB engine you want primary key to be a continuously incrementing id so INSERT won't be random causing too much page split and fragment the tablespace.

Benefits of AUTO_INCREMENT:

Always incrementing
Providing fast insert rate

Downsides of AUTO_INCREMENT:

One extra column
You probably will never query on PK

Timestamp would be a great candidate for PK if you can keep it UNIQUE since it's most likely will participate in most of your queries and most like as a range. (PK is at the end of every secondary key).

You could also experiment with the an id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY instead. Use whichever gives better performance.

Size

Try to keep the row size as small as possible to avoid sharding/partitioning/archiving as long as possible (eventually you will need to but MySQL is quite good with tens of millions of rows even).

To achieve this use only IDs in this table and have lookup tables where necessary. For example: instead of action VARCHAR(12) use action_id TINYINT UNSIGNED. TINYINT is 1 byte whereas action will be always at least 2 assuming ASCII, latin1 or similar small character set but longer the string becomes the more byte it will use obviously. UTF8 can use up to 4 bytes / character.

Only put index on columns where you really need to. Daily, monthly, etc aggregation tables will serve you better than heavy indexes on this table.

A possible schema

CREATE TABLE my_action_log (
    ts TIMESTAMP(3) PRIMARY KEY,
    action_id TINYINT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    post_id INT UNSIGNED NOT NULL,
    ...
    KEY ... # Whatever index you need
) ENGINE=InnoDB


Mongo Storage Engine from mmap to wiredTiger

That error implies there is already data in TestDataBase.testCollections. You might consider using the --drop option:

Before restoring the collections from the dumped backup, drops the collections from the target database. --drop does not drop collections that are not in the backup.
When the restore includes the admin database, mongorestore with --drop removes all user credentials and replaces them with the users defined in the dump file. Therefore, in systems with authorization enabled, mongorestore must be able to authenticate to an existing user and to a user defined in the dump file. If mongorestore can’t authenticate to a user defined in the dump file, the restoration process will fail, leaving an empty database.


The best way to query a partitioned table in Oracle

Oracle will take care of partition pruning when using
** making one query: select * from table where field in ('A','B')

Here is an example:

CREATE TABLE q1_sales_by_region
      (deptno number,
       deptname varchar2(20),
       quarterly_sales number(10, 2),
       state varchar2(2))
   PARTITION BY LIST (state)
      (PARTITION q1_northwest VALUES ('OR', 'WA'),
       PARTITION q1_southwest VALUES ('AZ', 'UT', 'NM'),
       PARTITION q1_northeast VALUES  ('NY', 'VM', 'NJ'),
       PARTITION q1_southeast VALUES ('FL', 'GA'),
       PARTITION q1_northcentral VALUES ('SD', 'WI'),
       PARTITION q1_southcentral VALUES ('OK', 'TX'));

explain plan for
select * from q1_sales_by_region
where state IN ('OR','FL')
SQL> SET LINESIZE 130
SQL> SET PAGESIZE 0
SQL> SELECT *
  2  FROM   TABLE(DBMS_XPLAN.DISPLAY);
Plan hash value: 2523828506

------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                    |     1 |    41 |     2   (0)| 00:00:01 |       |    |
|   1 |  PARTITION LIST INLIST|                    |     1 |    41 |     2   (0)| 00:00:01 |KEY(I) |KEY(I) |
|*  2 |   TABLE ACCESS FULL   | Q1_SALES_BY_REGION |     1 |    41 |     2   (0)| 00:00:01 |KEY(I) |KEY(I) |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("STATE"='NY' OR "STATE"='OR')

The KEY(INLIST) designation for the partition start and stop keys specifies that an IN list predicate.


jsonb_each_text(jsonb) returns jsonb, not text

jsonb_each is not jsonb_each_text

No, you're calling jsonb_each and you're referencing the docs for jsonb_each_text It shows

On \dfS json_each_text,

      Name       | Result data type |              Argument data types              |
-----------------+------------------+-----------------------------------------------+
 jsonb_each_text | SETOF record     | from_json jsonb, OUT key text, OUT value text
 jsonb_each      | SETOF record     | from_json jsonb, OUT key text, OUT value jsonb

Making that adjustment to call jsonb_each_text

SELECT *, pg_typeof(key) AS type_key, pg_typeof(value) AS type_value
FROM jsonb_each_text('{"2": "-200.08"}');

 key |  value  | type_key | type_value
-----+---------+----------+------------
 2   | -200.08 | text     | text


Best Practices for Using Temp Tables in Stored Procedures

There are times when the indexes on underlying tables just aren't enough to satisfy a particularly big query with lots of joins/applies/subqueries/CTEs. For those times, temp tables and table variables can be just what you need to improve performance.

By using a temp table to store intermediate results, you can create any indexes or statistics that you need to improve further processing. I run into this now and then when we need to build an occasional-use report on top of a bunch of OLTP data. Table variables can be used to similar ends, but with the restriction that they're not nearly as flexible with indexing (up until 2014, at least, which began to remove much of this restriction). But they are a bit lighter-weight, and more importantly they are scoped to the module rather than session, so you don't have to worry about naming conflicts like you would with temp tables.

There is overhead associated with creating and writing to temp tables, of course, to say nothing of the impact on code maintainability, so if the query can be written cleanly as a single query making use of set-based operations without sacrificing performance, then stick with that approach.


But it possible to setup to restore transaction log after finish of
  current running and this interval time no other user connect secondary
  database.

Yes it's possible, you can read more about it here: Using Secondary Servers for Query Processing


This is one of the best posts I've seen on this (regular enough here) topic. Seems to be the quickest way to test a backup.

What you've got to remember is that no process is 100% cast-iron guaranteed to restore your databases - there's the thorny issue of money.

Basically, every extra 9 (3, 4, 5 nines) that you put on to your procedures will (my own rule of thumb here) put the price of your backup and restore procedures up by a 0 - i.e. a factor of 10! Are you going to be checking every backup you make? You can follow industry best practice and make it crystal clear that that is what you are doing (to your bosses/shareholders/stakeholders) and that if they want more guarantees, they'll have to pay and pay lots!

You can talk about clusters, master-slave replication, master-master, geo-replication, RAID10 SSD's - the number of solutions for HA MySQL is huge, but the prices get huge pretty fast also!

Best practice is testing backups ~ 6 weeks - a role for a junior DBA - you should take a full backup once a week with incremental backups every night when traffic is low (for MySQL I strongly recommend Percona's Open Source XtraBackup).

Motto of the story, formulate a backup and restore plan with the other stakeholders (explaining the pros and cons - risk, cost &c.) and follow industry best practice and you won't go far wrong!


Replication Monitor Missing Subscriptions in Watch List

Update for resolution if someone is having similar situation.
I faced similar scenario where I could not get subscription agent list pulled up when launched replication monitor neither I could get the result from Sp_replmonitorhelpsubscription and the query kept on running without result. When checked currently executing query, all the  distribution agent jobs were being blocked by couple of pull subscription distribution agent also causing issue for other distribution agents jobs and couldnot pull replicated commands. I stopped and restarted those 2 head blockers distribution agent jobs that released the rest of distribution agent jobs. This resolved the issue with subscription agents not being showed up on the replication monitor.


Is storing redundant data part of the (CouchDB) self-contained data concept?

You understood correctly.  NoSQL databases like Couch trade normalization for fast reads and better availability.

Like you, I have some aversion to inconsistency/denormalization.  It's a trade off in which problems you want to be mindful of when you develop.


Access 2003 (SQL Server 2000) migration to SQL Azure

If your back end is currently SQL 2000, then you need to do a dance to get this to work, but it should be fairly straightforward to do something as follows:

Spin up a VM in Windows Azure, using a template for SQL2008R2 (that's the oldest available template, I believe).
Take backup of your SQL2000 instance, restore it to the Azure VM instance you just created.
Using Visual Studio, create a new database project pointing to the new SQL2008 R2 database.
Change the properties of the project to be SQL Database as the target.
Fix all the errors.
Export/Import to your SQL Database instance.
Remove your Azure VM (unless you want to keep it, of course, but why pay for something you don't need?)

I have no idea how long this will take you to accomplish, but that's the general outline that should work.


SQL Data type for "Reference" fields in table - SQL Server

Here is what I would do based on your requirements:

CREATE DATABASE [demo];
GO

USE [demo];

CREATE TABLE [dbo].[Player](
    [playerID] [int] IDENTITY(1,1) NOT NULL,
    [firstName] [varchar](50) NULL,
    [secondName] [varchar](50) NULL,
    [fk_team] [int] NULL,
 CONSTRAINT [PK_Player] PRIMARY KEY CLUSTERED ([playerID] ASC)
 );
GO

CREATE TABLE [dbo].[EventTypes](
    [EventTypeID] [int] IDENTITY(1,1) NOT NULL,
    [EventType] [varchar](50) NULL,
 CONSTRAINT [PK_EventTypes] PRIMARY KEY CLUSTERED ([EventTypeID] ASC)
 );

GO

CREATE TABLE [dbo].[Events](
    [eventsID] [int] IDENTITY(1,1) NOT NULL,
    [Minute] [varchar](50) NULL,
    [fk_EventType] [int] NULL,
    [fk_Player] [int] NULL,
 CONSTRAINT [PK_Events] PRIMARY KEY CLUSTERED ( [eventsID] ASC)
 );
GO
SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Events]  WITH CHECK ADD
CONSTRAINT [FK_Events_EventTypes] FOREIGN KEY([fk_EventType])
REFERENCES [dbo].[EventTypes] ([EventTypeID])
GO

ALTER TABLE [dbo].[Events] CHECK CONSTRAINT [FK_Events_EventTypes]
GO

ALTER TABLE [dbo].[Events]  WITH CHECK ADD
CONSTRAINT [FK_Events_Player] FOREIGN KEY([fk_Player])
REFERENCES [dbo].[Player] ([playerID])
GO

ALTER TABLE [dbo].[Events] CHECK CONSTRAINT [FK_Events_Player]
GO

INSERT INTO [dbo].[EventTypes] ([EventType])
      VALUES('Try'), ('Conversion'), ('Penalty');
GO
select * from EventTypes;

This should be what you need.   Have a great day!


Efficient way to store multi choice in a column with few options

Well, it is always difficult to give a generic answer to design questions.
Best would be to study the normal forms and see how that can be applied to exactly your situation! :)

Having said that, it sounds like you want to store reporting data (OLAP type table/database) so I base it on that.
In OLAP, you can see all different types of items which can be selected 0 or 1 time as Attributes to whatever you like to consider the 'Fact' and can be stored in the 'Fact' table. You can opt to not use separate tables for your fruit, sandwiches or drinks but only have 'check constraints' in your Fact table columns.

However, if your requirement is a little bit more complex, I'd opt for one Fact table, one for fruit (where you can store more lookup data per fruit), one for sandwich (with the same requirements as for fruit but possibly a different set of attributes/columns) and one for drinks. I.e. One extra table per 'selectable attribute'.
Now, if someone can choose More then 1 of anything, it gets more complex and you need to consider other options (link tables or such).


You can always check if a variable is dynamic or not with on http://dev.mysql.com/doc/refman/5.7/en/dynamic-system-variables.html

Or looking at the parameter directly:
http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_open_files_limit

If it is dynamic you can:

set global <variable_name> = <value>;

  Just don't forget to change in your my.cnf also otherwise next restart will override it.

If it is not then you have to restart MySQL to make it happen. Oracle is working on getting more and more variable dynamic and you can see the trend going through 5.5 -> 5.6 -> 5.7. Unfortunately though there's always going to be parameters which require restart.


Does SQL Server redistribute data after adding an additional data file?

The way that datafiles work, any new data will, mostly, get put in the new data file you added. Or in your case the two new data files.

However, SQL Server will not try to re balance your data. This would't make sense either as you will be causing a lot of IO / load on a database for, as far as SQL server knows, no reason.
You can however rebalance your data by recreating your indexes on a new filegroup while dropping the old indexes. The advantage being that you're allocating space in small chunks, getting unfragmented indexes and generally have more control.

You will however have to do this largely manually. And you'll probably want to create full backups before and after each operation.


Your where condition that checks for equality for utcdt converted to a date is SARGable, but uses a dynamic seek.

  The problem is that these hidden implicit conversions can result in inaccurate cardinality and distribution estimations at any stage of the plan.  So, even if you get a seek, the plan might be way off overall. - Dynamic Seeks and Hidden Implicit Conversions - Paul White

Try converting your where condition to an explicit range and see if there is any improvement:

select *
from Tick.X_H
where Symbol = 'donkey'
  and utcdt >= convert(datetime2(7),'20110102')
  and utcdt <  convert(datetime2(7),'20110103')

Based on this answer by Martin Smith the explicit range may reduce unnecessary reads.

References:

Dynamic Seeks and Hidden Implicit Conversions - Paul White
Cast to date is sargable but is it a good idea?
SARGable functions in SQL Server - Rob Farley


I believe you want the following

AUTHENTICATION = CLIENT
TRUST_ALLCLNTS = NO
TRUST_CLNTAUTH = SERVER

As you have changed above, the AUTHENTICATION=CLIENT, will force the client to authenticate. (Which can still use the OS as noted above.)

TRUST_CLNTAUTH = SERVER will cause the client to send the user id and password to the server for authentication when they are provided in the connect statement. This still does not force the client to require the user name and password as it can get them from the OS.

This is because DB2 will assume TRUST_ALLCLNTS as YES by default. This causes the server to assume all clients can be trusted and allow them to check against their respective OS's. So from what I know, to force the client to require a user name and password with the connect statement requires TRUST_ALLCLNTS to be set to NO. This will cause the server to not trust them and force them to send the user id and password to the server to authenticate against it.


Why does SQL Server 2012 Express use 9.5GB of RAM on my server?

SQL Express is limited to 1GB for the Buffer Pool, but there are many other memory pools in SQL Server. What I find surprising is the excessive use of non-buffer memory pools.
To find out memory usage per memory clerk, run this:

SELECT type, SUM(pages_kb)/1024 AS MemoryMB
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY 2 DESC

Hope this helps


So wouldn't a weak entity be in violation of 2nf? Because a non-prime attribute could be dependent solely on the foreign key?

No.  Consider a typical example:

SalesOrder(OrderId,OrderDate) PK (OrderID)
SalesOrderItem(OrderId,OrderItemNum,ProductId,Quantity,Price) PK (OrderId,OrderItemNum)

No OrderItem non-key attribute is functionally dependent on OrderItemNum.  OrderItemNum could be repeated for multiple orders.  In practice it's often not, but for the purposes of normalization it's just the trailing column of the primary key.


Oracle: Non key-preserved table should be

You can update a join in Oracle if the following conditions are met:

Only one base table is updated
All other tables are key-preserved: each of them must have at most one row for each row of the base table.

(additional restrictions on updating views apply)

In your example you update table PG_MACHINE only. Oracle has to make sure that for a single row of this table, only one row of the other can be found. This seems to be the case since you have a PK on PG_LABLOCATION.LABLOCID. Therefore you should be able to update the join. See for example this SQLFiddle with a similar setup.

In your case you should either:

make sure that the primary key is enabled, validated, not deferrable (interestingly, a deferrable constraint prevents Oracle from updating the join!)
use MERGE if PG_LABLOCATION.LABLOCID is unique for the relevant query. MERGE is less strict than update with joins and will only return an error if there is actually a duplicate in the result set (whereas UPDATE will fail if there is the possibility of a duplicate).
review your query, since you don't need values from the parent table in the SELECT clause, you could rewrite it as a semi-join (that guarantees that no duplicate will be generated):

UPDATE (SELECT mac.in_use, mac.update_time
          FROM pg_machine mac
         WHERE mac.lablocid IN (SELECT loc.lablocid
                                  FROM pg_lablocation loc
                                 WHERE loc.dnsname = 'value')
           AND to_date('02-JAN-2013') > mac.update_time)
   SET in_use = 1 - MOD(101, 2),
       update_time = to_date('02-JAN-2013');

This could be rewritten as:

UPDATE pg_machine mac
   SET in_use = 1 - MOD(101, 2),
       update_time = to_date('02-JAN-2013')
 WHERE mac.lablocid IN (SELECT loc.lablocid
                          FROM pg_lablocation loc
                         WHERE loc.dnsname = 'value')
   AND to_date('02-JAN-2013') > mac.update_time;

In this case I would go with the third option: in general you can't update the parent in a parent-child join.


From your paths, I can safely assume the following:

You are running MySQL in Windows
You used the MySQL MSI to install

What you need to do is establish the file my.ini

When installing MySQL for Windows using the MSI, the location of my.ini is expected to be C:/ProgramData/MySQL/MySQL Server 5.1.

Please run the following in a DOS Window:

cd C:/ProgramData/MySQL/MySQL Server 5.1
dir *.ini

You will see some sample my.ini files. However, if there is no file named my.ini in that folder, you must create one. Whether one exists or not, please run this:

cd C:/ProgramData/MySQL/MySQL Server 5.1
notepad my.ini

If you are asked to create it, please do so.

Next, create the following entry under the [mysqld] group header in my.ini:

[mysqld]
datadir=F:/naveen/data

Save my.ini

Next, stop mysql from the DOS command line like this:

C:\> net stop mysql

Next, make a copy of the entire data folder in the new location

C:\> xcopy "C:\ProgramData\MySQL\MySQL Server 5.1\data" F:\naveen\data /s

Last step, start up mysql

C:\> net start mysql

Try logging into mysql. Once you can login to mysql successfully, run this command:

show variables like 'datadir';

If F:\naveen\data shows up as the datadir, CONGRATULATIONS, YOU HAVE DONE IT RIGHT !!!

Once you are satisfied all your apps hitting MySQL works, you can delete everything in C:\ProgramData\MySQL\MySQL Server 5.1\data\*

Give it a Try !!!


Can a PRIMARY KEY constraint (with a NONCLUSTERED INDEX) check for only certain values?

You cannot have a Primary Key that only works for some of the rows. It would really cease being a Primary key at that point. Emphasis on Primary.

You can play with filtered indexes for a Unique index and that filter can be on values you specify.

But a primary key is there to enforce uniqueness so you can't make only some of the table part of the key.

You can disable the FKs while you make fixes, you can build a foreign key relationship on an existing table with NOCHECK - but that will mean that none of the relationships are checked. You have an untrusted foreign key and some of the benefits foreign keys offer like foreign key join elimination can't work that way because SQL isn't guaranteed that your row is there or not there.

Alternatively you could just not define that relationship and deal with the ramifications. But you are basically asking SQL server to break the referential integrity at the outset by making exceptions. If you could cart those exceptions off into a separate table and join to that table in a view or something like that you could get around this and keep the relationship on the "real" table moving forward.

Remember though: A filtered index just can give you some uniqueness on the other side of filter. This doesn't address FKs or even the PK. I think a better answer is fix the issue with the data. Even a -1 primary key value for "unknown" as is common in warehouses would be better than orphans.


SQL Server agent option in SQL Server Express

You can't, as found under the 'Management Tools' section here: http://msdn.microsoft.com/en-us/library/cc645993%28v=sql.100%29.aspx

If this is just for your development environment, you can pick up a copy of Developer Edition for about $50 US.  When you release, you will need a copy of Standard Edition or higher to be able to use the job agent.


If you have version 12.1 the following method also works, and is especially useful if you don't have access rights to create procedures on the production database.

It uses rank()over() to group the email addresses by id and sequence them,
and then sys_connect_by_path() to do the concatenation.

select id,  sys_connect_by_path(trim(email),' ') as email
from
 (
  select id, email,
         rank() over (partition by id order by email) as seq,
         rank() over (partition by id order by email desc) as rseq
    from emails
 )
 where rseq = 1 start with seq = 1
 connect by prior id=id and prior seq=seq-1
 order by id


The multi-part identifier > "a.surveyid" could not be bound

You need to use surveyid not a.surveyid. Because surveyusers as a declare in sub-query so you can not access a.surveyid in main query.


There's a SQL trace file being populated every day. How can I find the routine running the trace file populating it with data?

It looks like those files are coming from the Flight Recorder monitoring feature within SSAS.

To turn it off, connect to the Analysis Services server from SSMS, go to server properties, General tab, (you might need to select Show Advanced (All) Properties, and change the value under Log -> FlightRecorder -> Enabled to false.


Can I Improve the design of my Transaction Table?

Given balances are a sum of amounts, you are storing one of debits or credits as negative right?, the same table is ok. Having a few NULLable fields is ok too even if slightly redundant.


SQL Server 2012 Error 9512 when modifying user login

Sounds like you have a DDL trigger that is set to listen for ALTER_LOGIN events, and is trying to pass EVENTDATA() directly to some remote procedure.

Check for this using:

USE master;
GO
SELECT OBJECT_DEFINITION([object_id])
  FROM sys.server_triggers
  WHERE OBJECT_DEFINITION([object_id]) LIKE N'%ALTER[_]LOGIN%';

It could also be a database-level DDL trigger that listens for ALTER_USER:

USE the_relevant_database;
GO
SELECT OBJECT_DEFINITION([object_id])
  FROM sys.triggers
  WHERE parent_class = 0
  AND OBJECT_DEFINITION([object_id]) LIKE N'%ALTER[_]USER%';


There are three(3) aspects you need to consider
ASPECT #1
You may find this astonishing but did you know that some older versions of MySQL can outperform newer versions ? You must tune MySQL 5.5 properly. In fact, InnoDB has new options introduced into the InnoDB Plugin.
I have earlier articles in the StackExchange on this

Jul 16, 2012 : decreased performance of stored procedure when migrated from mysql server 5.0 to 5.5
Feb 22, 2012 : MySQL 5.1 vs MySQL 5.5 (5.1 twice as fast)
Nov 24, 2011 : Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)
Jun 19, 2011 : How do I properly perform a MySQL bake-off?

ASPECT #2
Looking at the my.ini, something caught my attention: you have innodb_thread_concurrency=10. That's a definite no-no, especially with MySQL 5.5. I say this because MySQL 5.x thrives on setting the innodb_thread_concurrency to 0. At Percona Live NYC 2011, Ronald Bradford explicit told me as I sat in the audience not to set this to any other value than 0. By setting it to zero, it lets InnoDB figure out the correct number of threads it needs create.
ASPECT #3
You need to activate multiple cores for InnoDB. MySQL 5.0/5.1 are not designed to use multiple cores. The InnoDB Plugin (from MySQL 5.1.38 has new options for doing so. Those options are fully present in MySQL 5.5. Such include

innodb_read_io_threads
innodb_write_io_threads
innodb_io_capacity


It sounds like you want to run the LIMIT, but still know how many rows would have been returned without the LIMIT.  Check out FOUND ROWS


There is also a way to create SQL Scripts Repository.
Add new template folder

Add script files


Using views as a source for staging table?

As has been noted in the comments, without more details, it's hard to give detailed help.  That said, I an provide some general advice.

If you're considering using vendor-supplied views to extract data for your datamart, you're first question shouldn't be about performance. Your first question should be, "Do the views have all the data I need?" You've probably already considered this, but the data you need is your driving force.

Second - if the source database resides in a different SQL Server instance, your data is being accessed remotely, a view may be a good option. If you access the data through, for instance, a linked server, hitting the tables directly may result in having to pull all the table data over to the active server, and then performing the joins. By hitting a view, you ensure that the joins will be processed on the source server. Now, there's nothing saying that the vendor-supplied views are the best possible views for your needs, but a view may be the best solution ultimately.


If the (subquery) has SELECT (whatever expression) AS col FROM ..., then you can do:

INSERT INTO mytable
  (col1, col2, col3, col4)
SELECT
  val1, s.col, val2, val3
FROM
  (subquery) AS s ;

or:

WITH s (col) AS
  (subquery)
INSERT INTO mytable
  (col1, col2, col3, col4)
SELECT
  val1, s.col, val2, val3
FROM
  s ;


Definition of indexing, datatype configuration, and DBMS for SHA3-256 hashes

SHA3-256 is 256bits. That's 32 bytes.

Don't store it as TEXT any form, or it'll be 256 bytes (8x the size).
I would store it as inline binary if your database supports it because I doubt anything it's going to have a data type for a 32 byte data type (which nothing uses except AVX2).

On indexing, any HASH index would work if your database provides it.

From PostgreSQL,

CREATE DOMAIN sha3_256 AS bytea;

CREATE TABLE datastore (
  id     int  PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  myhash sha3_256
);

CREATE INDEX ON datastore USING HASH (myhash);

INSERT INTO datastore (id,myhash) VALUES
  (1, decode('36f028580bb02cc8272a9a020f4200e346e276ae664e45ee80745574e2f5ab80', 'hex'));

As a special optimization you could store it as two UUIDs. Though I probably wouldn't bother.


MySQL Slave database size >> Master Database

From the details, it looks like the 'massive' difference is ~70MB in the dataplay schema. You can adjust your query to find out exactly which tables are different.

Two potential reasons for this difference is:

Schema difference on the master vs slave. Does the slave have utf8 instead of latin1 on a column or three? Does the slave have additional indexes? You can compare the schema by running a mysqldump of just the schema (--no-data option) and then diff'ing the results.
The slave has actual different data. Do you have session-only data that the application stores and won't matter? If no, you can use pt-table-checksum to find rows that are actually different. Then you need to dig into why they are different and fix that issue before reseeding the passive master or using pt-table-sync to resolve the differences.


Data Migration or Sharded Cluster to new Cluster

Migrating an existing sharded cluster to new hardware

If you want to migrate a sharded cluster to new hardware, there is a tutorial in the MongoDB manual: Migrate a Sharded Cluster to Different Hardware

Migrating data in an existing sharded cluster to a new cluster

There is no officially supported process to migrate documents between two different MongoDB clusters (at least as at MongoDB 2.6), but you do have a few options:

A typical approach is via backup & restore of the sharded cluster, but that involves some down time or a custom script to sync up changes that occurred while you were loading data into the new cluster.
The team at Mailbox has open sourced a set of Python scripts called Hydra which specifically aim to enable live migration of sharded  collections. For more background details see: Scaling MongoDB at Mailbox.

I haven't personally tried Hydra (and there is currently a caveat in the Github repo that the authors only tested with MongoDB 2.2.3 on Ubuntu 12.04), however it seems like a promising approach.


purge worker, purge coordinator, shutdown handler stuck

These threads are not a problem as they are normal background tasks with nothing to do at the moment.


from docs:

  The local shell command to execute to archive a completed WAL file
  segment. Any %p in the string is replaced by the path name of the file
  to archive, and any %f is replaced by only the file name. (The path
  name is relative to the working directory of the server, i.e., the
  cluster's data directory.) Use %% to embed an actual % character in
  the command. It is important for the command to return a zero exit
  status only if it succeeds

(emphasis mine)

Also take a look at setting WAL archiving


Combine the effects of separate delete and insert rows

If I'm understanding your needs correctly, this would probably work...

SELECT t1.Date, t1.Id, t1.For_Id, t1.New_Value, t2.Old_Value
FROM table t1 JOIN table t2
ON t1.Date = t2.Date
AND t1.Id = t2.Id
AND t1.For_Id = t2.For_Id
WHERE t1.New_Value IS NOT NULL
AND t2.Old_Value IS NOT NULL;


Why are queries causing spill to tempdb?

There's several different questions in here:

Q: Why weren't the queries spilling before?

They were, but SQL Server Management Studio didn't surface this as a clear error prior to SQL 2012. It's a great example of why when you're doing performance tuning, you have to go deeper than the graphical execution plan.

Q: Why do queries spill to disk?

Because SQL Server didn't grant them enough memory to complete their operations. Perhaps the execution plan underestimated the amount of memory required, or perhaps the box is under memory pressure, or they're just big queries. (Remember, SQL Server uses memory for three things - caching raw data pages, caching execution plans, and workspace for queries. That workspace memory ends up being fairly small.)

Q: How can I reduce spills?

By writing sargable T-SQL statements, having up-to-date statistics, putting enough memory in the server, building the right indexes, and interpreting the execution plans when things don't work out the way you expected. Check out Grant Fritchey's book SQL Server Query Performance Tuning for detailed explanations of all of those.


mongos for “MongoDB Shard,” is a routing service for MongoDB shard configurations that processes queries from the application layer, and determines the location of this data in the sharded cluster, in order to complete these operations. From the perspective of the application, a mongos instance behaves identically to any other MongoDB instance.

As you have mention here mongos.conf file

 net:
      port: xxxxx
      bindIp: 0.0.0.0

   sharding:
      configDB: <configReplSet>/<ip_of_config>:27019

As here i would like to say that here as you are bindIp in mongos.conf file with 0.0.0.0 but in sharding section , you are using  with port number 27019 of any replica set.

Note : As here I am not able to see any logpath destion in your mongos.conf file.

As per MongoDB BOL documentation here Starting in MongoDB 3.6, mongos bind to localhost (127.0.0.1) by default.

Did you try with this configuration of your mongos.conf file.

sharding:
  configDB: replicasetname/ipaddress:27019 -- external ip of replica set of any node which port is 27019, as you mention in your code. which must be participate the replication.
 net:
  bindIp: localhost,ipaddress -- external ip address, if you are using
  port: xxxxx --port number of mongos, it should be different from replica set node port number
systemLog:
  destination: file
  path: -- log destination of mongos file
  logAppend: true
processManagement:
  fork: true             -- if you are using the fork


How to add an automatically-populated custom sequence number?

Create a trigger that on INSERT will populate that filed.

You cannot have it as a calculated column, as latter cannot refer to any tables.


Populate nested associations using PostgreSQL

PostgreSQL 9.6+

PostgreSQL 9.6 adds jsonb_insert

You need to know that when you SELECT tablename FROM tablename you get a composite type that represents the row. You can feed this to the to_jsonb like such,

SELECT address.id AS address_id, to_jsonb(address)
FROM address;

SELECT user_id, to_jsonb(user)
FROM user;

SELECT city_id, to_jsonb(city)
FROM city;

Then realizing that the user is your outermost part of the JSON object, work inward by inserting the row type with jsonb_insert

SELECT jsonb_insert(
  to_jsonb(u),
  'address',
  a
)
FROM user AS u
JOIN address AS a
  ON u.id = a.user_id;

This should display the basic user with address, now you need the city too.

SELECT jsonb_insert(
  to_jsonb(u),
  'address',
  jsonb_insert(to_jsonb(a), 'city', to_jsonb(c))
)
FROM user AS u
JOIN address AS a
  ON u.id = a.user_id
JOIN city AS c
  USING (city_id);

And, that should work for you.