Data directory of mysql moving from one drive to different drive?

You can try with below steps.

Stop the existing MySQL instance using services.msc, shutdown must be clean so that the instance flushes any pending changes to disk.
After successful instance shutdown, copy the data directory from existing location to the new location.
Existing location: C:\ProgramData\MySQL\MySQL Server 5.7
New Location: E:\MySQL
Change datadir parameter in current my.ini file. I have commented old entry and added new one:
#datadir=C:/ProgramData/MySQL/MySQL Server 5.7\Data
datadir=E:\MySQL\Data
Start MySQL instance using services.msc
After starting MySQL successfully, you can verify above changes by creating temporary database and verify data directory for the same database.


Check out this post from Erik Darling: CXCONSUMER Is Harmless? Not So Fast, Tiger

That shows a really extreme example of a problem query where CXCONUSMER is the highest wait.  So while the Microsoft recommendations indicate it's harmless, it definitely can be a sign of other problems.

Based on that, and your screenshot, it's quite possible that you're running into issues with skewed parallelism.  You can find some great info about skewed parallelism from Paul Randal: More on CXPACKET Waits: Skewed Parallelism

In theory, waits due to skewed parallelism should be surfaced as CXPACKET waits.  This doesn't appear to always be the case in practice at this time though.

Without the query text, and especially an actual execution plan, we can only guess at what might be causing the CXCONSUMER waits for your scenario.  But hopefully this helps gives you a direction to look in.

If you edit your question to include the execution plan and query text (and relevant table / index definitions), we can be more help in tracking down the root cause.


I have been tasked with Mysql Master-Master replication?

When using Master-Master Replication Setups (a.k.a Circular Replication), we tend to take things for granted. There are two aspects worth mentioning:

ASPECT #1 : PRIMARY KEY COLLISION

You mentioned primary key collision. That will definitely happen if you write to the same table on both Masters within the same window of time and that table has an auto_increment primary key. To circumvent this, MySQL added the following

auto_increment_increment
auto_increment_offset

The key to using these is the following:

set auto_increment_increment to be the same value on all nodes in Circular Replication topology.
set auto_increment_offset to be the any unique value >= 0 and less than auto_increment_increment

There are two instances where you do not have to use auto_increment_increment and auto_increment_offset in a two-node setup (DB1 and DB2)

INSTANCE #1: All writes (INSERTs, UPDATEs, DELETEs, DDL) occur in DB1 only

This allows DB2 to to be a hot standby
You can perform SELECTs from DB2 to reduce read I/O on DB1
You can perform mysqldump backups from DB2 without disturbing DB1
This allows you to add additional read-only slaves (See my post Adding slave to existing master-master config throws foreign key constraint failed error)

INSTANCE #2: In the event you have multiple databases in each node

If you write to the mydb1 on DB1, you read from mydb1 on DB1 and never from DB2
If you write to the mydb2 on DB2, you read from mydb2 on DB2 and never from DB1

I have discussed before : (May 07, 2012 : Setting Circular Replication in mysql)

ASPECT #2 : DATA DRIFT

This is a rather murky situation when binlog events do not executed on the Slave.

The factors involved here

Binary Logs
Relay Logs
Storage Engine
Network Transmission

It is relatively easy to setup Replication. It also relative easy to forget that binary logs, relay logs, and moving parts of InnoDB and MyISAM require conscientious flushing to disk.

Settings for Flushing Logs (See my post MySQL Master-Master Replication not syncing)

sync-binlog
sync-relay-log
sync-relay-log-info
sync-master-info

Flushing Storage Engines

Set innodb_max_dirty_pages_pct to 0 to increase flushing activity
Running FLUSH TABLES tblname; on MyISAM tables after bulk INSERTs, UPDATEs, and DELETEs
cronjob to run SET GLOBAL innodb_buffer_pool_dump_now = 1; (MySQL 5.6 only, see innodb_buffer_pool_dump_now). This dumps a numeric map of the Buffer Pool's data and index pages to Disk. You would also configure innodb_buffer_pool_dump_at_shutdown innodb_buffer_pool_load_at_startup
This is not an all-inclusive list. Your application must become more cognizant of the need to flush to disk

Networking

You must make sure the network traffic is not inundated, your switches are fine, any crossover cabling to bypass the network is not defective, there are no dropped packets,.
In terms of MySQL, the Replication I/O thread is dependent on the network. You must make sure mysqld can regularly heartbeat its master. Perhaps semisync replication could be installed and configured for this.


but what can I use to skip any database that's a part of AG. in other
  words what can I use to skip synchronized DB?

There is a difference between  getting any database that belongs to an AG or to only get the synchronized databases. For example, async nodes will keep having the status: "synchronizing" (or another message, but not synchronized).

To get all the databases participating in an AG:

SELECT name
FROM sys.databases
WHERE database_id
NOT IN (SELECT database_id
        FROM sys.dm_hadr_database_replica_states);

To get only the synchronizing databases:

SELECT name
FROM sys.databases
WHERE database_id
NOT IN (SELECT database_id
        FROM sys.dm_hadr_database_replica_states
        WHERE synchronization_state_desc = 'SYNCHRONIZED');

To also exclude the system db's:

SELECT name
FROM sys.databases
WHERE database_id
NOT IN (SELECT database_id
        FROM sys.dm_hadr_database_replica_states
        WHERE synchronization_state_desc = 'SYNCHRONIZED')
        AND database_id > 4;


What are the necessary course requirements to become a DBA?

I've discovered my education as a DBA and as an IT professional came in three tiers.

What I learned on my own. Basically from each job I was hired for, I learned how each shop operated, listened to business requirements and learned on my own how to complete the assignments. A lot of reading the manual (now Googling) and trial-and-error is involved but it's ultimately how we all learn.
What I learned from certifications. One time I picked a textbook for my favorite programming language and discovered that I was only using a fraction of the techniques described on the job. I wanted to learn as many aspects as I could. In the back of the book they had mentioned the certification program for my language (Microsoft Certified Professional in this case). It's a structured way to learn all the major features of the platform you're focusing on for your employment. There's a lot of studying and practice involved (My MCSD took four tests and my MCDBA and MCITP:DBA were comparable). You'll learn about features you might not end up using on a particular job site, but being aware of their existence helps shape you decisions on how to tackle a business requirement.
What I learned from the community. This by far, is the most valuable. You learn firsthand that you're not alone in your profession. You also learn what your peers have learned on the job and through their own research, and it's great to share what you've learned. You'll find your mentors here as well. There's a large of community of masters, MVPs and fellow DBAs who share their knowledge in-person, in print and online. Search for them in Google, attend a local user group, have your employee fund a trip to your favorite products' annual convention. I've seen at SQL PASS twice and the knowledge shared there is amazing.

One book that I've personally found best describes the calling of a DBA is DBA Survivor: Become a Rockstar DBA. It goes beyond the technical and talks about your relationship to your customers and your responsibilities to the tradecraft. It helped reaffirm my belief that the DBA profession is an essential one that is challenging, honorable and rewarding.


Updating rows in tables concurrently using Multithreading in Java

You can split your file  into multiple files.

Try POI-HSSF and POI-XSSF - Java API To Access Microsoft Excel Format Files (Apache POI Project)

Here's an example of how to read an Excel file :

    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file));
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFRow row;
    HSSFCell cell;

    int rows; // No of rows
    rows = sheet.getPhysicalNumberOfRows();

    int cols = 0; // No of columns
    int tmp = 0;

    // This trick ensures that we get the data properly even if it doesn't start from first few rows
    for(int i = 0; i < 10 || i < rows; i++) {
        row = sheet.getRow(i);
        if(row != null) {
            tmp = sheet.getRow(i).getPhysicalNumberOfCells();
            if(tmp > cols) cols = tmp;
        }
    }

    for(int r = 0; r < rows; r++) {
        row = sheet.getRow(r);
        if(row != null) {
            for(int c = 0; c < cols; c++) {
                cell = row.getCell((short)c);
                if(cell != null) {
                    // Your code here
                }
            }
        }
    }
} catch(Exception ioe) {
   }


New database not using the model configuration

I finally found the answer in Understanding SQL Express behavior: Idle time resource usage, AUTO_CLOSE and User Instances by Mike Wachal of the Microsoft SQL Express team:

  Supporting ClickOnce is something we wanted to do with SQL Express, so we modified the behavior of the CREATE DATABASE functionality in SQL Express in order to set AUTO_CLOSE to True for all databases.

So I guess I'll have to put a trigger in place to change auto close back to False when a new database is created.


Find the closest bar, when searching for a drink

set @x = 15;
set @y = 25;

select sqrt(pow(b.lat-@y,2) + pow(b.lng-@x,2)) as distance,  b.name, d.name
from `drinks` d
 join `drink__bars` db on d.id = db.drink_id
 join  `bars` b on b.id = db.bar_id
where d.`name` = 'Beer'
order by distance asc;

Full sqlfiddle, with some data. Use your preferred distance function to replace the plain one I used.

I would recommend against having Id on many-to-many relations. And prefer NOT NULL always, unless you have a very strong case to allow NULLs (you don't).


Compare two tables with same structure, but differing membership numbers

That should give you what you want:

SELECT UT.Name, T1.*, T2.membership_no as 'Unmatching in membership_no Second table'
FROM T1
INNER JOIN UT
ON T1.user_id = UT.user_id
INNER JOIN T2
ON T1.user_id = T2.user_id and T1.membership_no != T2.membership_no


TRCSESS

  TRCSESS is useful for consolidating the tracing of a particular session for performance or debugging purposes.

Yes, you can merge the scattered user session trace files using this tool and can get formatted out using TKPROF.

  TKPROF

  TKPROF can locate statements using the greatest resource. With baselines available, you can assess whether the resources used are reasonable given the work done.

Its usage and syntax are given in the following reference in details.

Reference:Tracing Utilities


Which server actually runs the SSIS package when SQL Agent and SSISDB are on 2 different servers

SSIS packages deployed to  to its Integration Service Catalog (SSIS DB) will run on the server where SSIS service and SSISDB in installed.


Is it possible to spin up a free development / test instance of an AWS Redshift server to run test queries on?

Redshift is a service so you cannot run it locally unless you want to buy AWS Outposts.

Nevertheless, you can try RS during Free Tier - trial period. After that price can be as low as $0.25 per Hour (+traffic+tax), billed hourly.


Design concerns for using 3x bit or one char(1) or one integer in table for holding status of item

In my experience, trying to encode multiple data points into a single column always ends up being more trouble than it's worth. Sure, it seems cool and clever to use BITWISE operators, but there are many things that go wrong and it won't always be efficient to test those bits without cumbersome and unintuitive workarounds. It's the same reason we stay away from storing comma-separated lists, JSON strings etc. in a single column - eventually you care about viewing or filtering on those distinct bits which you now have to extract, sometimes expensively.

With the information I have, my vote is for three separate BIT columns. They will still collapse to similar storage patterns as a single column with the three bits on/off, and can be made more efficient individually and across the board in several ways, including:

data compression
sparse columns
filtered indexes (e.g. WHERE allow_returns = 1)

Someone else advocated for three CHAR(1) columns. These do not benefit from storage collapse and also require a check constraint, making them less than ideal in my mind.

Now, my answer might change if you say, "well, what if I might add 15 other attributes in the future?" I certainly don't think it's wise to build the columns this way if they're not relatively static - changing the schema (and therefore all of the code and interfaces to it) for every new or changed attribute is going to be a royal pain. So in that case you might want to consider EAV - where the attributes are not part of the metadata but part of the data. There are a lot of objections to EAV, mostly around performance and the difficulty in enforcing constraints (in this case unlikely to be an issue if all of these attributes are either on or off), but it worked quite well for us at my previous job. You might model it like this:

CREATE TABLE dbo.Attributes
(
  AttributeID TINYINT PRIMARY KEY,
  Name VARCHAR(32) NOT NULL UNIQUE
);

CREATE TABLE dbo.ItemAttributes
(
  ItemID INT NOT NULL
    FOREIGN KEY REFERENCES dbo.Items(ItemID),
  AttributeID TINYINT NOT NULL
    FOREIGN KEY REFERENCES dbo.Attributes(AttributeID),
  Status BIT NOT NULL,
  PRIMARY KEY(ItemID, AttributeID)
);

And again, you can have filtered indexes here to make certain queries much more efficient, such as (imagine the AttributeID for "allow returns" is 10):

CREATE INDEX optAllowReturns ON dbo.ItemAttributes(ItemID)
  WHERE AttributeID = 10 AND Status = 1;

If you have certain attributes that are not on/off (for example, three states of manufacture or shipping), you can change the Status column to:

Value TINYINT NOT NULL

This can double as an on/off value for attributes that are represented that way, and as tri- or more-state value for attributes that require more than simple on/off. You can also reflect which type is which in the metadata of the dbo.Attributes table.


(Taken from comment)

Try putting a nonclustered index on both - use batch_id and batch_li as the keys, and make sure to include comments. The order in which you include them depends on the contents of the columns - I'd read up on that before you make a decision.


I didn't see official documentation answering your questions, but some of these things you can yourself (on a testing/dev server first!). Here's what I found:

Unless you are hoping to use a very large number of concurrent locks, you likely will not have any problems. On SQL Server 2017 CTP2.1, I was able to acquire 1MM concurrent locks in about 26 seconds. There were no collisions, and this appeared to use ~286MB of memory.

The SQL Server documentation specifies a maximum of 2,147,483,647 locks for 32 bit and "Limited only by memory" for 64 bit (which I assume you're using).

Test script

Below is a script that acquires 1MM locks and does basic monitoring of the elapsed time, memory consumed, collisions encountered.

BEGIN TRAN;

DECLARE @result INT,
    @i INT = 1,
    @locks_attempted INT = 1000000,
    @locks_acquired INT,
    @ts DATETIME2 = SYSDATETIME();

WHILE (@i <= @locks_attempted)
BEGIN
    DECLARE @Resource NVARCHAR(255) =
        'LongStringSoThatTheFirst32CharactersAreIdentical_' + CAST(@i AS VARCHAR)
    EXEC @result = sp_getapplock @Resource = @Resource, @LockMode = 'Exclusive';
    IF (@result <> 0) THROW 50000, 'Failed to acquire lock', 0
    SET @i = @i + 1;
END

DECLARE @elapsed_ms INT = DATEDIFF(MILLISECOND, @ts, SYSDATETIME());

SELECT @locks_acquired = COUNT(*)
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID
    AND resource_type = 'APPLICATION';

-- Acquired 1000000 of 1000000 locks in 26351 milliseconds
RAISERROR('Acquired %i of %i locks in %i milliseconds', 0, 1,
    @locks_acquired, @locks_attempted, @elapsed_ms);

-- ~286MB of memory for OBJECTSTORE_LOCK_MANAGER
SELECT *
FROM sys.dm_os_memory_clerks
WHERE type = 'OBJECTSTORE_LOCK_MANAGER';

ROLLBACK;

What about collisions?

I didn't observe collisions in any of my trials, but based on the documentation for sys.dm_tran_locks, it does appear that a 32 bit hash is used. With such a short hash, the odds of a collision should be fairly high (see chart below). However, even with 10MM concurrent locks, I did not achieve a collision. This might indicate that the hash is more complex under the hood, and that the resource_description column of sys.dm_tran_locks simply shows the first 32 bits of the hash (similar to the way it shows only the first 32 characters of the @Resource parameter). Or it might indicate that I was lucky.

If you are going to use this strategy, you should check to see if you get any collisions on your actual data. If you do, you could try to put unique elements (e.g., the schema_id, object_id, and your rowid) at the beginning of the @Resource. (It's not fully clear, but the documentation suggests that the first 32 characters of @Resource might be combined with the hash in order to uniquely identify the lock.)

(Image from Preshing on Programming)

Why do you want to do this?

It seems unusual to use sp_getapplock so heavily for something that seems similar the type of row-level locking that SQL Server provides on its own. I'm assuming you've carefully considered other approaches, but if you haven't yet done so, you might want to think about other solutions that don't involve sp_getapplock.


Group by every N records but remove incomplete group that has records less than N

WITH T AS
(
  SELECT
    RANK() OVER (ORDER BY dateReceived ) Rank,
    p.data
  FROM parameterData p where parameterID=@parameterID
    and dateReceived between @fromDate and @toDate
    and recipe = @recipe
    and isActive=1
)
SELECT
  ROW_NUMBER() OVER (ORDER BY (SELECT 100)) AS GroupID,
  (Rank - 1) / @groupSize as GroupSize,
  AVG(data) as average,
  MIN(data) as min,
  MAX(data) as max,
  MAX(data)-MIN(data) as range,
  count(*) as cnt
FROM T
GROUP BY ((Rank - 1) / @groupSize)
HAVING count(*) = 5;


Selecting/Matching cells that hold the same values as opposed to ID in MySQL/phpMyAdmin

SELECT  GROUP_CONCAT(CONCAT(Given_name, ' ', Surname)),
        Address
    FROM actor_uri_address
    JOIN actor_full_name_opc USING(ID)
    GROUP BY Address
    HAVING COUNT(*) > 1


Searching from Database having billion records, which database should i use

You could use a parallel system such as Azure SQL DW. This would basically split your data up into sixty parts, and should be able to optimise just as well as your regular SQL database, but spreading the work out across sixty databases. You would still need to make sure that your design was optimal, in regard to table structures and indexing, but you should be able to shrink the size of your problem down by a large factor. Your 20 minutes could become half a minute quite easily.

Of course, there are probably good ways to tune your current system.


Mirroring on-premise SQL Server to AWS EC2 SQL Server

I know this is very old but yes I have this working. Ended up using mirroring due to company policy between SQL 2016 AWS and on premise SQL 2016 running on Vmware.

Found this link very helpful.

https://tecadmin.net/setup-database-mirroring-sql-server/

I know not exactly what you were looking for but thought might help.


best performance for storing notifications in postgresql

There's nothing wrong with creating a dedicated table for notifications, along the route your example above is choosing to take. Whether you use triggers or handle managing it with calls from the application side is up to you and equally of merit as well.
Alternatively I was thinking similar to your last proposed solution, but by using a View as a way to centralize the different notification types and stay current in real-time. You would UNION ALL your different notification types and when you select from the View, you would likely order by the timestamp descending to show the latest notifications first.
The only other part you'd need to come up with is how to manage which notifications a user has already seen. For that you'd likely need a logging table of the notification's id and type, and had columns for the user's id and timestamp of when they viewed their notifications. Then in the View above you'd OUTER JOIN to this viewed notifications log, so you can have an additional column in it called isNew and you'd know which ones are new to the current user since the last time they looked at their notifications. At least this viewed notifications log table would be written to less frequently (only whenever a user looks at their notifications) than just a pure notifications table (every new instance of a notification), so it would be much transactionally lighter.


How can I know what hashing algorithm SQL Server used to decrypt the encrypted data when using the function DECRYPTBYPASSPHRASE?

But how does it know what was the algorithm used to encrypt data if there's no argument related to that on the function DECRYPTBYPASSPHRASE? Is it part of the encrypted data?

Yes, right on point.

I'm going to use the following for the output:

DECLARE @Data VARBINARY(MAX)
DECLARE @Text NVARCHAR(MAX) = N'I''ll get you, and your little dog too!'
DECLARE @Phrase NVARCHAR(100) = N'Fly My Pretties!'

SELECT @Data = ENCRYPTBYPASSPHRASE(@Phrase, @Text)

SELECT @Data AS [Encrypted_Data]

SELECT CAST(DECRYPTBYPASSPHRASE(@Phrase, @Data) AS NVARCHAR(MAX))

If I run this on my 2014 instance, I'll get the following for Encrypted_Data:
0x01000000E565142762F62...

If I run this on my 2017 instance, I'll get the following for Encrypted_Data:
0x020000004D261C666204F...

What should pop out is the preamble, where you can see the 2014 instance starts with 0x01 and the 2017 instance starts with 0x02. This is the versioning of the encryption type that is used. Note that there is more than just this but there is no need to get into that detail for the purposes of this answer, nor does it need to be public knowledge.

SQL Server 2017 understands 0x01 and 0x02 because it's new and knows the new things. SQL Server 2014 understands only 0x01 because it's older and does not know any of the new things as the new things were not backported.

  [...] SQL Server 2017 uses the SHA2 hashing algorithm to hash the passphrase. SQL Server 2016 and earlier versions of SQL Server use the SHA1 algorithm that's no longer considered secure.

This is not the same thing, but generally has to do with Symmetric keys being created with the same initialization vector across both versions. I blogged about this when 2017 came out and it was fixed a little bit later with the trace flag that must be used whereas in your question there is no trace flag needed for 2017 to read 2014 data as shown.


Most likely not queries but system operations, e.g. gather statistics.
Probably nothing to worry about.

V$SESSION_LONGOPS

  V$SESSION_LONGOPS displays the status of various operations that run
  for longer than 6 seconds (in absolute time). These operations
  currently include many backup and recovery functions, statistics
  gathering, and query execution, and more operations are added for
  every Oracle release.

Just to verify, check opname and target_desc.


This seems to have been released in the latest version as below:

https://github.com/olahallengren/sql-server-maintenance-solution/issues/47

You'll need to add the list of database names to the @Databases parameter - but do check through the documentation and Github site for clarification.

As hot2use states above, master and model will never be covered by this script though, as they are not user databases.

I would suggest you give the documentation a read here.


Impact of changing server machines names with SQL Server 2016 Standard in mirror state

Renaming Windows computer for SQL Server is supported an operation and described in documentation. For mirroring, it needs to be turned off and re-established after renaming.  Remember that renaming Windows requires a reboot, so be prepared for service outage.

  When you rename a computer that is configured to use database
  mirroring, you must turn off database mirroring before the renaming
  operation. Then, re-establish database mirroring with the new computer
  name. Metadata for database mirroring will not be updated
  automatically to reflect the new computer name. Use the following
  steps to update system metadata.

For what it's worth, could you use SQL client aliases instead of renaming the computers?


I'm not sure why you're using a variable, but you need to protect multiple statements with a transaction. What's happening is two users are calling the procedure at the same time, both are getting rowcount = 0, and then they're both trying to insert as a result.
set transaction isolation level serializable;
begin transaction;

update dbo.table1
  set value = @value
  where id = @id;

if (@@ROWCOUNT = 0)
begin
  insert dbo.table1(id, value1,value2...)
    values(@id, @value1, @value2...);
end

commit transaction;

I blogged about this since writing this answer:

Please stop using this UPSERT anti-pattern


Insert Value based on One Column based on Value on another Column

What you are trying to do is to create a computed column and I am pretty sure MySQL does not support them. There are a few ways around this issue:

Do the computation in the logic that inserts the data
Create a trigger on the table to do the calculation when data is inserted/updated
Create a view to represent the data how you want it.

My personal preference would be to use a view as it is easier to maintain as is a much neater solution. Your view would look something like this:

CREATE VIEW `vwHelp`
AS
SELECT `_ID`, `_date`,
CASE
   WHEN HOUR(`_date`) <10 THEN 1
   WHEN HOUR(`_date`) <16 THEN 2
   WHEN HOUR(`_date`) <24 THEN 3
   ELSE -1
END AS `_timestamp`
FROM `help`;


InsertId in one table and insert the same id with concat of array at the end in other table

You can do this in a single statement using a data modifying CTE
with new_t1 as (
  insert into table_1 (order_id, qty)
  values ('Exak123', 59)
  returning *
)
insert into table_2 (sub_id)
select concat(t1.order_id, '_', g.nr)
from new_t1 t1
  cross join generate_series(1, t1.qty) as g(nr);


Want to know details and meaning about the trace of SQL Server

You appear to have setup a trace with no understanding of why you've done so. There is little point in running a trace for 10 minutes, let alone months, without a reason.

  Scenarios for using SQL Server Profiler

  The first step in using SQL Server Profiler is to identify your
  reasons for monitoring an instance of SQL Server.

The most common scenarios (taken from the above link) which may be relevant to you are:

Find the worst-performing queries.
Monitor stored procedure performance.

A trace that captures RPC:Completed and SQL:BatchCompleted events (Stored Procedure and TSQL classes) with Duration, Reads and CPU data columns will provide you with a diagnostic log of how your queries are performing. You can import this trace file to a table and run queries against it to identify the slowest, highest IO or highest CPU consumers for example.

If you have direct access to the server and want to analyse performance, I'm inclined to suggest that you ignore profiler traces and start with the SQL 2005 Performance Dashboard Reports. When you've grasped the basic concepts of Troubleshooting Performance Problems, invest some time understanding Dynamic Management Views (DMVs) and how they can help with performance analysis.


SELECT color,
       AVG(CASE WHEN dimension = 'length' THEN value END) avg_length,
       AVG(CASE WHEN dimension = 'width' THEN value END) avg_width
FROM boxes
GROUP BY color;

fiddle


MySQL, would like to get both a field and a count inside GROUP_CONCAT

As a schema:

SELECT name,
       GROUP_CONCAT(CONCAT(status_id, ':', status_name, '(', status_cnt, ')')) AS all
FROM ( SELECT name,
              status_id,
              status_name,
              COUNT(status_name) AS status_cnt
       FROM source_tableset
       GROUP BY name, -- first level GROUP BY and counting
                status_id,
                status_name ) AS subquery
GROUP BY name -- second level GROUP BY and group concatenation


Does ALTER on PostgreSQL, Oracle table (respectively) create intermediate copy of the table?

From the Postgres manual:

Adding a column:

  When a column is added with ADD COLUMN, all existing rows in the table are initialized with the column's default value (NULL if no DEFAULT clause is specified). If there is no DEFAULT clause, this is merely a metadata change and does not require any immediate update of the table's data; the added NULL values are supplied on readout, instead.

(Emphasis mine)

Changing a column's type:

  Adding a column with a DEFAULT clause or changing the type of an existing column will require the entire table and its indexes to be rewritten. As an exception when changing the type of an existing column, if the USING clause does not change the column contents and the old type is either binary coercible to the new type or an unconstrained domain over the new type, a table rewrite is not needed; but any indexes on the affected columns must still be rebuilt.

Note the part about indexes being rebuilt for any column change.

Dropping a column:

  The DROP COLUMN form does not physically remove the column, but simply makes it invisible to SQL operations. Subsequent insert and update operations in the table will store a null value for the column. Thus, dropping a column is quick but it will not immediately reduce the on-disk size of your table, as the space occupied by the dropped column is not reclaimed.


Can slowms be set on each individual database in MongoDB?

setProfilingLevel actually affects two things. First parameter (level) tells when system writes to profile -collection (0-never, 1-only when slower than slowms or 2-always). Second parameter (slowms) affects actually always to syslog output of ALL databases. So, with db.setProfilingLevel(0,-1), nothing is ever written to profile-collection, but ALL queries (of all DBs) are written to mongod.log


Fetching user info from many databases at one go


multiple ids table for accounting schema

If you are concerned about multiple nulls in your CreditDebit, you may consider change the table to the following

create table CreditDebit (CD_ID int primary key, ref_id int, ref_entity varchar(20));

Here ref_id can refer to box_id/emp_id/customer_id depending on ref_entity column, which can have values like 'box', 'employee', 'customer'.
The disadvantage of this design is there is no way to create the foreign key relationship between CreditDebit` and the other three tables. However, you can implement the data integrity in the application layer (if you do not want to use trigger).


Need help in incrementing the date in oracle

Note the extra brackets.

SQL> variable d varchar2(100);
SQL> exec :d := '09-DEC-2014';

PL/SQL procedure successfully completed.

SQL> select '''' || (to_date(:d, 'DD-MON-YYYY') + 1) || '''' from dual;

''''||(TO_DATE(:D,'D
--------------------
'10-DEC-14'

Changing date format:

SQL> alter session set nls_date_format='DD-MON-YYYY';

Session altered.

SQL> select '''' || (to_date(:d, 'DD-MON-YYYY') + 1) || '''' from dual;

''''||(TO_DATE(:D,'DD-
----------------------
'10-DEC-2014'


Log shipping restore speed & index rebuild vs reorganize

If you want to improve the speed of your tlog restores, you will want to look into the bulk-logged recovery model.  I would never suggest running a database under this recovery model exclusively, though switching to it during your maintenance window will help significantly in your case.

The key here is to be very explicit on the approach you take when switching recovery models.  From the source, anytime you wish to utilize the bulk-logged recovery model, you should follow these steps:

Before you switch to the bulk-logged recovery model, take a tlog backup
Switch the recovery model of the db to bulk-logged
Perform your minimally logged operations (e.g. index maintenance, etc.)
Switch the database back to the full recovery model
Immediately take another tlog backup

Here's a handy little infographic that outlines the approach:

In an ideal situation, MS also encourages the following:

Users are currently not allowed in the database, or their activity is kept to a minimum.
All modifications made during bulk processing are recoverable without depending on taking a log backup; for example, by re-running the bulk processes.

These are not hard-line requirements, but good practice recommendations and the reason behind them is that all activity that completes during the period where the database is in bulk-logged mode (once a minimally logged operation occurs) will either have to be redone if you roll back to the tlog backup taken in step 1 or will be committed if you restore the tlog backup taken in step 5.  The yellow window identified in the infographic is an all-or-nothing sort of process, and you are unable to do any point-in-time recovery during that time-frame.  The moment you convert the database back to the full recovery model and take another tlog backup is the moment you can once again utilize point-in-time recovery.

Finally, there are some restrictions to using the bulk-logged recovery model, such as considerations for databases with read-only filegroups and online recovery scenarios, so do some testing to make sure you're not causing yourself more headache than necessary.

I've used the bulk-logged recovery model for years and it's quite helpful so long as you understand how to use it properly.


Index rebuild, data compression generated Large TLog

Regardless of your recovery model whenever an operation (index rebuild or reorganize) modifies a data page that page modification is put into the log.  In your case changing the fill factor can add more data pages, and move data to new pages.  SQL Server has to make room for the new data and to do that it has to keep track of the page changes.  The more pages that are changed, the more entries will be put into the transaction log.

In your case I would ask, what storage are you using, and what is your fragmentation like?  If you're using flash storage, I'd tell you that rebuilding indexes becomes much less important than it does if you're using magnetic storage.


What normal form does a surrogate key violate?

Arguably, it doesn't.

Adding a surrogate key is an implementation decision (to respect how the RDBMS works) taken at implementation time. During modelling and normalisation, you should end up with BCNF (slightly stricter and more correct 3NF) without surrogate keys

That is, introducing surrogate keys at the start of the design process is wrong. Even though we all do it...


XML load fails on postgresql 9.1, but not in 9.3, any workaround?

You hint us about the XML size but it may well not have anything to do with your problem.

If it worked fine for years, I would suspect something has changed in the XML format.

If the XML file appears to be valid (since it works in 9.3), you may be hitting a bug with previous versions of POSTGRES.

Try comparing 2 XMLs, before and after it started failing.

Apparently POSTGRES wasn't working well with XML files having DOCTYPE elements in it. This is only an example (although that could be it)

So while the XML file may be technically good, a change in its format may have place you in the way of a bug in versions prior to 9.3.

For your test, try an older XML file and a new one on the 9.1 version. If one works, you know it's a problem in the file format. If not, you at least know it's not the XML format.

You may also want to verify if there was any patching of the OS done. Postgres uses libxml2 to parse XMLs. if that has changed, this could be it and a downgrade could solve your problem.


Cassandra query response time degradation

Response time heavily dependent on the query that you're executing & how you're executing them. Linear scalability is achieved only if you're performing reads by primary or at least partition key. If you're doing something like SELECT * FROM table WHERE non_pk_column = 'something' ALLOW FILTERING or SELECT * FROM table, then Cassandra will need to get data from all your nodes, and as you increase the number of nodes, the latency is increasing.

Also, when executing query you need to make sure that you're using token aware load balancing policy that will send query request to the coordinator node that has one of the replicas.

But it's a really broad question, please provide more information about your queries, etc.


Assuming the field is of BINARY datatype:

UPDATE [dbo].[Table]
SET [field] = CONVERT(BINARY,'F')
WHERE [fieldID] = 1;

Here's my proof of concept:

CREATE TABLE #TempBinTable
 ([Identity] INT
,[BinColumn] BINARY);

INSERT INTO #TempBinTable
VALUES(1,CONVERT(BINARY,'T'));

SELECT [Identity], [BinColumn]
FROM #TempBinTable;
--RESULT
----Identity    | BinColumn
----1           | 0X54

UPDATE #TempBinTable
SET [BinColumn] = CONVERT(BINARY,'F')
WHERE [BinColumn] = CONVERT(BINARY,'T');

SELECT [Identity], [BinColumn]
FROM #TempBinTable;
--RESULT
----Identity    | BinColumn
----1           | 0X46

DROP TABLE #TempBinTable;


Can I use `base/pgsql_tmp` directory for non-admin applications?

Question 1:

It is a bad idea to create these files in the PostgreSQL data directory.  That is for database files only.  In addition, you cannot be certain that pgsql_tmp is there at all (it is only created when the first temporary file is written).

Use an absolute path and write the file somewhere else, ideally in /var.

Question 2

Of course there is a risk to fill up the file system - either through the files that you write, or through temporary files that the database writes.

Question 3

That is up to your code; the database does not guarantee that you don't write over the same file in two concurrent transactions.


Oracle procedure sometimes runs endlessy, other times finishes instantly

There is no commit in your code. You are likely blocking yourself if you run this twice from different concurrent sessions.
Add a commit at the end:
    /* Insert order data from ws2 */
    FOR ws2_purchase_rec IN ws2_order_crsr
    LOOP
        INSERT INTO ts_transaction VALUES('ws2_' || to_char(ws2_purchase_rec.code) || '_' || to_char(ws2_purchase_rec.entry_no),
            ws2_purchase_rec.date_, ws2_purchase_rec.qty, 'ws2_' || to_char(ws2_purchase_rec.user_), 'ws2_' || to_char(ws2_purchase_rec.product));
    END LOOP;

    COMMIT;

    EXCEPTION
        WHEN OTHERS THEN
        dbms_output.put_line('An error has occurred, all work the procedure has done has been rolled backed');
        ROLLBACK TO beforeProcedure;
END;
/


Oracle schema object name when fire trigger

Simply like this:

CREATE OR REPLACE TRIGGER orders_changes
after drop or alter or create on schema

BEGIN
  insert into table1 values (USER, ora_dict_obj_name);
END;

See Event Attribute Functions for predefined variables inside triggers.


Is there a Windows Performance Counter which contains the "Log Send Rate" for Availability Groups?

The DMV column log_send_rate will tell you Rate at which log records are being sent to the secondary databases, in kilobytes (KB)/second.
Depending on what SP + CU you are running, you may want to look into - KB 3012182 FIX: Log_Send_Rate column cannot reflect the rate accurately in SQL Server 2012 or in SQL Server 2014
Below PERFMON counter will tell you how many bytes were sent

SQL Server:Availability Replica --> Bytes Sent to Replica/sec
Number of bytes sent to the remote availability replica per second. On the primary replica this is the number of bytes sent to the secondary replica. On the secondary replica this is the number of bytes sent to the primary replica.


Write latecy increased when changed SQL Server to Enterprise on AWS

I know its because the buffer cache and plan cache cleared bcs of this upgrade

SQL Server is not my specialty, so I'll assume those things are true, but there's probably an even more significant issue involved, any time you create an RDS instance from a snapshot.

RDS is backed by EBS,¹ and EBS snapshots are backed by S3.  Instances spin up quickly because they don't wait for the entire snapshot to be loaded into S3 onto the volume.  All of the data is logically on the volume but not yet physically on the volume -- accessible, but not instantaneously accessible. It's lazily loaded in the background.  Reads of data that have not yet been loaded onto the volume have to first be read from S3 -- so you have a first-touch penalty when reading from the disk, any time the disk block being read is read for the first time.  It is not well-documented whether EBS is only reactive with the background loading, or would also go ahead and restore everything from S3 even for a volume not being accessed.

Either way, as you can imagine, this is suboptimal for a busy database.

The volume should eventually warm up and performance should rise to the level you expect, but the timing of this warmup is not documented.

Anecdotally, I have observed it to be in the 5 to 10 MiB/s range... or 18 to 36 GiB/hour... however, these results may have been subject to the observer effect, since I was deliberately trying to stimulate the recovery with sequential reads, not random reads.

A solution I have created for MySQL, which I recognize is not directly useful to you but may give you an idea, iterates all the tables and indexes, writing and running queries that attempt to scan each table and each index (writing queries that trigger the optimizer to use the index as a covering index), thus forcing them all to be read from disk, warming it up as quickly as possible.  The generated queries look similar to this:

SELECT DISTINCT LEFT(MD5(CONCAT(c1,c2,c3, ... cn)),1) FROM t1;

A query in this form will read all the rows from the table, but returns a result set of 16 (0-9, a-f) or 17 (+null) rows.  The resultset is unimportant, but passing the concatenated data to MD5() ensures no shortcuts for the optimizer -- a full table scan is forced.

See Initializing EBS Volumes for a description of the snapshot problem, but note that the techniques for warmup here won't help you since RDS does not provide access to the command line of the server.

See also this reddit discussion quoting AWS support which appears to confirm that RDS snapshots are indeed EBS snapshots, thus prone to this condition.

It'a also possible that you could hasten this warmup process by making a database backup by some native mechanism -- not an RDS shapshot.

¹ RDS is backed by EBS -- The probable exception to this is Aurora, which uses a proprietary storage system to create the replicated "Aurora cluster volume," which apparently doesn't use EBS.


The issue with the SQL appears to be a Merge Join...

The optimizer's estimate is that the Merge Join will account for 85.8% of the cost of running the query, but you should always treat these figures with a healthy degree of suspicion.

They are just estimates based on a fairly simple costing model that helps the optimizer make general choices between alternatives, but the numbers almost certainly do not correlate with the performance characteristics of the execution plan on your particular hardware. The cost estimates are a broad hint at things the optimizer felt were costly, but there's no substitute for analysis by a skilled and experienced database professional.

That said, the main reason for the optimizer's high cost estimate for the merge join in this case is that the join is assumed to be many-to-many. This is because the database schema does not provide any constraints or unique indexes to guarantee a one-to-many relationship. The assumed many-to-many join is also the reason for the outrageously high join output cardinality estimate. Given incomplete information, the optimizer produces an inaccurate estimate.

The question does not provide schema DDL for the tables involved, but we can infer some of the columns from the execution plan, and something about the relationships from the joins. The data types are unknown for the most part, and we cannot know about foreign keys or other constraints, of course.

An update to the question while I was writing this answer states that the query cannot be changed, but for the sake of analysis, I had already rewritten it to at least use modern JOIN syntax:

SELECT
    a.wf_state,
    b.oid,
    p.wf_user_id,
    p.error_no,
    item_followup = 1,
    p.element_type,
    selected = 0,
    p.version_no,
    p.p_description,
    p.s_description,
    p.proc_node_id,
    p.step_node_id,
    p.distr_type,
    f.project,
    d.reg_period,
    d.resource_id,
    d.last_update,
    a.used_hrs
FROM dbo.awfenquiry AS p
JOIN dbo.atstsehourdetmap AS b
    ON b.oid = p.oid
JOIN dbo.atstsehourdet AS a
    ON a.tsehourdet_id = b.tsehourdet_id
JOIN dbo.atstsegldetail AS c
    ON c.tsegldetail_id = a.tsegldetail_id
JOIN dbo.atstseheader AS d
    ON d.tseheader_id = c.tseheader_id
JOIN dbo.atschargecode AS e
    ON e.client = c.client
    AND e.chargecode_id = c.chargecode_id
JOIN dbo.atsproject AS f
    ON f.project = e.project
    AND f.client = e.client
WHERE
    p.element_type = N'TS'
    AND a.client = N'GB'
    AND
    (
        a.wf_state = N'U'
        OR p.distr_type = N'U'
    );

One of the main improvements you can make is to give each table a clustered index. Almost all tables benefit from having a clustered index, either for space management reasons, or simply because it provides an extra 'free' index. In many cases, the clustered index will also be the primary key, though this is not required. All tables should have a key. Many people would argue that a table is improperly formed without one. As a starting point, I have given each table a primary key based on my best guess. The primary key is clustered by default.

Putting all that together, a first pass at a schema is:

CREATE TABLE dbo.awfenquiry -- p
(
    oid integer PRIMARY KEY,
    element_type nvarchar(50) NULL,
    wf_user_id integer NULL,
    error_no integer NULL,
    version_no integer NULL,
    p_description nvarchar(50) NULL,
    s_description nvarchar(50) NULL,
    proc_node_id integer NULL,
    step_node_id integer NULL,
    distr_type nvarchar(10) NULL
);

CREATE TABLE dbo.atstsehourdetmap -- b
(
    oid integer NOT NULL,
    tsehourdet_id integer NOT NULL,

    PRIMARY KEY (oid, tsehourdet_id)
);

CREATE TABLE dbo.atstsehourdet -- a
(
    tsehourdet_id integer PRIMARY KEY,
    tsegldetail_id integer NULL,
    wf_state nvarchar(10) NULL,
    used_hrs integer NULL,
    client nvarchar(10) NULL
);

CREATE TABLE dbo.atstsegldetail -- c
(
    tsegldetail_id integer PRIMARY KEY,
    tseheader_id integer NULL,
    client nvarchar(10) NULL,
    chargecode_id integer NULL
);

CREATE TABLE dbo.atstseheader -- d
(
    tseheader_id integer PRIMARY KEY,
    reg_period integer NULL,
    resource_id integer NULL,
    last_update datetime2 NULL,
);

CREATE TABLE dbo.atschargecode -- e
(
    chargecode_id integer NOT NULL,
    client nvarchar(10) NOT NULL,
    project integer NULL,

    PRIMARY KEY (chargecode_id, client)
);

CREATE TABLE dbo.atsproject -- f
(
    project integer NOT NULL,
    client nvarchar(10) NOT NULL,

    PRIMARY KEY (project, client)
);

Statistics were not provided either, but we can at least see the overall table cardinalities in the execution plan. The following statements provide this information for the table definitions above, without any information about value distribution:

UPDATE STATISTICS dbo.awfenquiry WITH ROWCOUNT = 51826;
UPDATE STATISTICS dbo.atstsehourdetmap WITH ROWCOUNT = 2748620;
UPDATE STATISTICS dbo.atstsehourdet WITH ROWCOUNT = 2743040;
UPDATE STATISTICS dbo.atstsegldetail WITH ROWCOUNT = 1223270;
UPDATE STATISTICS dbo.atstseheader WITH ROWCOUNT = 328822;
UPDATE STATISTICS dbo.atschargecode WITH ROWCOUNT = 23983;
UPDATE STATISTICS dbo.atsproject WITH ROWCOUNT = 18169;

Based on the query, there are few obvious index candidates beyond the keys already added. Two that may be of use are:

CREATE NONCLUSTERED INDEX i
ON dbo.awfenquiry (element_type);

CREATE NONCLUSTERED INDEX i
ON dbo.atstsehourdet (client)
INCLUDE (wf_state, tsegldetail_id, used_hrs);

The resulting execution plan is not obviously a massive improvement over the original, aside from the (now one-to-many) merge join, but it is a step in the right direction perhaps:

All the tables are relatively small, so I would probably check next to see if query execution is being blocked by another process, fighting for resources with other queries, or struggling with an overwhelmed storage system. Fifteen seconds seems entirely unreasonable for the task.


Code creates different plan when ran ad-hoc vs. in a stored procedure

Usual suspects:

constants in adhoc, parameters in code
mismatch of data types in code
parameter sniffing

Point 1: the optimiser can choose the best plan for the constants.
Change the constants = change the plan. A parametrised plen is resuable

Point 2 will introduce implicit conversions because of datatype precedence
eg varchar column compared to nvarchar parameter

Point 3: use parameter masking or OPTIMISE FOR UNKNOWN
Edit: To test: run stored proc, run sp_updatestats, run again. This will invalidate cached plans which is better then clearing plan cache

Edit: after jcolebrand's comment

You can disable sniffing several ways. The main 3 are

RECOMPILE. This is silly IMO.
OPTIMIZE (sic) FOR UNKNOWN
Parameter masking

Parameter masking:

DECLARE @MaskedParam varchar(10)
SELECT @MaskedParam = @SignaureParam

SELECT...WHERE column = @MaskedParam

Masking and the OPTIMIZE hint have the same effect (maybe for different reasons). That is, the optimiser has to use statistics and data distribution (Note: still under testing by Mark Storey-Smith) evaluate the parameters on their own merits ? , rather than what they were last call. The optimiser can recompile or not. SQL Server 2005 added statement level recompilation so there was less impact

Now, why a plan with "sniffed" parameters is "sticky" compared to masked/"unknown" parameters, I'm not sure.

I've used parameter masking since SQL Server 2000 for all but the simplest code. I've noted that it is liable to happen with more complex code. And at my old job I has some report procs that I could change the plan parameter defaults. I reckon the "cargo cult" approach was easier than a support call.

Edit 2, 12 Oct 2011, after some chat

Parameter masking and OPTIMISE FOR UNKNOWN have the same effect as far as I can tell
The hint is cleaner than masking but was added with SQL Server 2008.
Parameter sniffing happens at compile time.
WITH RECOMPILE generates a new plan each execution. This means a poor choice of defaults will influence the plan. At my last job, I could demonstrate this easily with some report code: changing parameter defaults altered the plan regardless of supplied parameters.
This MS Connect article is interesting: Suboptimal index usage within stored procedure (mentioned in one of the SO answers below)
Bob Beauchemin mentions it too

Outstanding issues

Does sniffing still apply with WITH RECOMPILE? That is, if the optimiser knows to discard the plan does it aim for re-use?
Why are sniffed plans "sticky"?

Links from SO:

https://stackoverflow.com/q/6919166/27535
https://stackoverflow.com/q/5236631/27535
https://stackoverflow.com/q/1392307/27535
https://stackoverflow.com/q/2905440/27535
https://stackoverflow.com/q/467513/27535
https://stackoverflow.com/q/272726/27535
Remus Rusanu (MS SQL team) and I had a bunfight here on SO
Whitepaper on MSDN in conjunction with my SO analysis: https://stackoverflow.com/questions/414336/why-does-the-sqlserver-optimizer-get-so-confused-with-parameters/414368#414368


Can an index refer to only one or a subset of possible values?

You can create a partial index that includes all columns of the table:

CREATE UNIQUE INDEX not_processed
    ON transactions
        (tran_id, tran_type, tran_note, created_at)
    WHERE process_id = 0 ;


Line breaks handled inconsistently by SSMS

There is an option to change this behavior, go to Tools > Options > Query Results > SQL Server > Results to Grid, and check the box for "Retain CR/LF on copy or save":

You may have to exit and re-load SSMS for this change to take effect.


Duplicate rows with primary key constraint in PostgreSQL 10.6

I have some suggestions based on problems I had before, not with PostgreSQL 10, but with 9.1:

Try to reindex
Try vacuum full analyze verbose

If first two option don't work, try a dump and restore.

This happens because of a bug in indexes.


No Maintenance Plans Folder in SQL Studio 2008 R2

Express Edition (the free version of SQL Server) doesn't have maintenance plans because it has no SQL Server Agent.  Agent is the constantly-running-scheduler that kicks off jobs like maintenance plans.


Ranking System with Different Categories

Track the facts that drive your ranking system in normalized tables according to what makes sense for those facts without regard to how it drives your ranking system.

Then have a separate table or tables that converts your basic facts into a points system that allows you to do simple sorts for ranking.

The reason to keep these things separate is that your basic facts are driven by some kind of usage of your system, whatever that may be.  You want the code for tracking this usage to be clean and maintainable.  Even if you didn't have a ranking system, these facts would exist.  The logic to maintain these facts is independent of the logic to calculate rankings.  The ranking rules are an extra layer on top of the facts.  Also, your rules for ranking could (easily) change over time, so you don't want to tangle up your basic facts with less stable rules.


Convert or open Oracle 7.3.x database in Oracle 11

You should open your 7.3 database with Oracle server version 7.3. Than you need to export data using legacy exp command and import generated file into Oracle 11g using imp.
That would be safest path.

If I remember correctly you can not migrate database directly from 7.3 to 11g version.

EDIT:
As mentioned in comment, there are more ways to to get to 11g version. Here is upgrade guide http://docs.oracle.com/cd/E11882_01/server.112/e23633/toc.htm


For a boolean settings table, should I toggle a flag, or insert/delete?

active flag seems redundant to me - I don't see any value in storing it. Don't worry too much about delete/insert performance, I cannot think why user would change his options frequently. Even if some application controls user settings, I believe it's still relatively rare operation. Also, if on application side you use one or other ORM library, it's usually easier to map many-to-many relationship if link table has no extra columns.


How to retreive deleted records in a trigger on a table with composite primary key

If I'm reading that right, this should work:

select
  from deleted d
  where not exists (select 1 from inserted i where i.a=d.a and i.b=d.b)

Regarding srutzky's comment

  "And for an UPDATE, both inserted and deleted tables have the exact same rows in them (as you cannot update a row that did not exist)."

This is not true in the case of updatable primary keys.

create table dbo.srutzky (a int not null
  , b nvarchar(44) not null
  , c bit not null
  , constraint pk_srutzky primary key clustered (a,b)
)

insert into dbo.srutzky (a,b,c) values
 (1,'for an UPDATE',1)
,(2,'both inserted and deleted',1)
,(3,'have the exact same rows in them ',1)

update dbo.srutzky
  set a=4
    , b = 'might not have the exact same rows in them'
  output deleted.a as deleted_a
       , deleted.b as deleted_b
       , inserted.a as inserted_a
       , inserted.b as inserted_b
  where a = 3

Results

deleted_a   deleted_b                                    inserted_a  inserted_b
----------- -------------------------------------------- ----------- --------------------------------------------
3           have the exact same rows in them             4           might not have the exact same rows in them


insert with multiple row return through select

Almost. You need to remove the values clause and just put the select straight after the table and column names of the insert clause:

INSERT INTO notifications(text, uid, read_status)
SELECT "abc", uid, 0 FROM users WHERE status = 1;

Then the output of the select will go to the insert.


Trigger to update a materialized view once after relevant transaction?

An OK solution using triggers

This is what I'm doing now. It's not exactly what I wanted because the trigger fires once per statement instead of once per transaction. But it works for now.

(In the future, we're thinking of using an actual table for search instead of a materialized view, and updating individual relevant rows via triggers instead of refreshing an entire matview.)

Create a function to refresh the materialized view concurrently:

  CREATE OR REPLACE FUNCTION refresh_post_search()
  RETURNS TRIGGER LANGUAGE plpgsql
  AS $$
  BEGIN
  REFRESH MATERIALIZED VIEW CONCURRENTLY post_search;
  RETURN NULL;
  END $$;

And create a trigger for each of the underlying tables:

  CREATE TRIGGER refresh_post_search
  AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE
  ON posts
  FOR EACH STATEMENT
  EXECUTE PROCEDURE refresh_post_search();

  CREATE TRIGGER refresh_post_search
  AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE
  ON posts_tags
  FOR EACH STATEMENT
  EXECUTE PROCEDURE refresh_post_search();

  CREATE TRIGGER refresh_post_search
  AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE
  ON tags
  FOR EACH STATEMENT
  EXECUTE PROCEDURE refresh_post_search();


SQL Server 2005 Express Edition is limited to 1GB of RAM for the buffer pool. Non-express editions, by default, will not be limited unless configured to set a max. In either case, the usage will typically not decrease unless memory pressure forces it, or the service is restarted.

Multiple instances will definitely impact performance, especially if their memory allocations exceed physical memory, and the I/O contention can slow things down for concurrent queries.
Inactive databases will have no impact, other than the disk space they consume.


How do I check if a constraint exists on Firebird?

You can query RDB$RELATION_CONSTRAINTS table, if you know the name of the constraint.
Something like this:

set term ^;
execute block as
begin
    if (exists(
        select 0 from rdb$relation_constraints
        where rdb$constraint_name = 'INTEG_669'
    )) then
        execute statement 'alter table process_category drop constraint INTEG_669';
end
^
set term ;^

Used execute statement because alter table not allowed inside the block and if statement not allowed outside.


Can I make sure two columns dont have the same value

No, you can't. In most DBMS (Postgres, SQL-Server, Oracle, DB2 and many others), you can just add a CHECK constraint:

ALTER TABLE foo
  ADD CONSTRAINT aa_cannot_be_equal_to_bb_CHK
    CHECK (aa <> bb) ;

I don't see any way to have this in MySQL, using only referential constraints. Besides triggers, you could allow the two columns to have equal values and simply ignore the rows by accessing the table always through a view:

CREATE VIEW foo_correct AS
SELECT id, aa, bb
FROM foo
WHERE aa <> bb ;

Alternatively, you can restrict Insert and Update operations through (stored) procedures that take care of the constraint and not allow data to be inserted (or changed) that do not satisfy it.


SQL Server Replication with Historical Tables

Is it possible to replicate a SQL Server database and for the replica to have temporal/historical tables?

Yes.  If you use an AG Replica, the primary database and replicas will all have the same Temporal Tables.
If you use Transactional Replication, you can have a Temporal Table at the subscriber, where the table at the publisher is non-temporal.
If you use Change Tracking or Change Data Capture you can use the change data to ship and apply to a temporal table in a reporting database.
See

Usage of replication technologies is limited.
Always On: Fully supported
Change Data Capture and Change Data Tracking: Supported
only on the current table
Snapshot and transactional replication:
Only supported for a single publisher without temporal being enabled
and one subscriber with temporal enabled. In this case, the publisher
is used for an OLTP workload while subscriber serves for offloading
reporting (including 'AS OF' querying). Use of multiple subscribers is
not supported since this scenario may lead to inconsistent temporal
data as each of them would depend on the local system clock.
Merge
replication: Not supported for temporal tables

Temporal Table Considerations and Limitations


How to define alias in an ARRAY_AGG expression?

You are forming an ad-hoc row type (effectively an anonymous record) with this expression:

(media_files.position, media_files.token, media_files.title)

in your aggregate function call:

ARRAY_AGG((media_files.position, media_files.token, media_files.title)
          ORDER BY media_files.position) AS media_files

Arrays types can only be built upon well-known types. Your option is to announce  such a type to the system and cast the record to it before forming the array.
Create a well-known composite type:

CREATE TYPE my_type AS (
  position int    -- data type?
 ,token    text
 ,title    text
 )

I am guessing data types for lack of information here. Fill in your actual types.

Creating a table has the same effect: It announces a well known composite type to the system indirectly, as well. For this reason, you can (ab-)use a temporary table to register a composite type for the duration of the session:

CREATE TEMP TABLE my_type AS (
  position int    -- data type?
 ,token    text
 ,title    text
 )

Either way, you can then cast your record:

ARRAY_AGG((media_files.position, media_files.token, media_files.title)::my_type
          ORDER BY media_files.position) AS media_files

Then you can reference elements of the (now well-known) type by name:

SELECT media_files[1].position, media_files[1].token
FROM  (
     ...
   ,ARRAY_AGG((media_files.position, media_files.token, media_files.title)::my_type
          ORDER BY media_files.position) AS media_files
   ...
   FROM ....
   GROUP BY ...
  ) sub;

Now, Postgres can use these names for building a JSON value. Voilá.


SSIS error Property BackupDirectory is not available for Settings

This is a known issue with SSIS designer (although I cannot find the Connect article at this time).  Most likely the backup value key in the registry is not being updated correctly with the directory for this package.

To fix this (if you are on the default instance), launch Regedit and navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer.  Create a string value called "BackupDirectory" (no quotes) and in the value put the complete path of your backup directory.  Then restart and your issue should be resolved.

For a named instance, the Regedit path is:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\"name of your SQL instance"\MSSQLServer


ignore mysql schema on replication before moving from mysql 5.6 to mysql 5.7

Should I need to ignore mysql db before upgrading mysql 5.6 to 5.7 on this slave?

No, you should not.  MySQL should handle everything correctly when the replica is newer than the master by not more than one major release, e.g. 5.6 to 5.7 -- just like you have here.

The fact you appear to be using the ROW binlog format is good, but be sure you're using it on the master, too, for the best chance of continuous clean replication.

And do have a good cry while reading the official list of caveats and breaking changes.  Most of them are well-intentioned, at least.


PostgreSQL CHECK constraint is evaluated wrongly

Well, your query here:

return NOT Exists (
    Select 1
    FROM example_table AS a, example_table AS b
    WHERE a.id != b.id AND a.value = b.value);

is not going to see the not-yet-inserted row, which is why it returns true on this second INSERT in your example:

insert into example_table (id, value) values (1,0);

Now, you could almost fix this by hacking up the query to something more like:

CREATE OR REPLACE FUNCTION checkunique(in_value integer)
RETURNS boolean AS
$BODY$
begin
    RETURN NOT Exists (
    Select 1
    FROM example_table WHERE value = $1);
end
$BODY$
LANGUAGE plpgsql STABLE;

ALTER TABLE example_table add constraint unique_value check (checkunique(value));

However, this is really not what CHECK constraints are supposed to be used for, and it actually introduces a race condition if you have multiple transactions writing to example_table at the same time (can you see how?). Use the UNIQUE constraints that PostgreSQL provides. If your values are too large for the UNIQUE constraint's B-Tree index, create the UNIQUE constraint on an MD5() of the value.


EXEC master.dbo.xp_cmdshell 'The user name or password is incorrect'

You should check if you have permissions or not

exec xp_cmdshell 'net use \\SHDVNFDBV1\shared-backup\  /USER:domain\username Passw0rd1'

Do not copy to root directory as @AaronBertrand mentioned.

As a side note, why are you using SQL Server to do filesystem tasks? This should be done by PowerShell e.g. Copy-Item c:\scripts\test.txt c:\test


Table with clustered index implicitly sorting by unique nonclustered index

No, sorting is not implicit and should not be relied upon. In fact, in the first tooltip, you can see that it is explicitly stated that Ordered = False. This means SQL Server didn't do anything at all to implement any sorting. What you observe is just what it happened to do, not what it tried to do.

If you want to be able to predict a reliable sort order, type out the ORDER BY. Period. What you might observe when you don't add ORDER BY might be interesting, but it cannot be relied upon to behave consistently. In fact in this post, see #3, I show how a query's output can change simply by someone else adding an index.

T-SQL Tuesday #56 : Assumptions

  What is SQL Server's preference in choosing the UQ?

The UNIQUE NONCLUSTERED index contains the clustering key, so it is covering for the query. In this case, your table only has two columns, so the clustered and nonclustered indexes contain the same data (just sorted differently). They're both the same size, so the optimizer could choose either. That it chooses the nonclustered index is an implementation detail.

I call this a "coin flip."


How to connect to sql server with dynamic IP Address

Instead of connecting via IP address, connect to your machine's name. If you're connecting locally, you can use LOCALHOST as the name, or just a period ("."), which connects you to your local default instance.


How to delete all but the latest entry per customer in the fastest way?

Draft code:

CREATE PROCEDURE clear_table() AS
BEGIN
    CREATE TABLE temptable LIKE datatable;
    INSERT INTO temptable (customerID, creationDatetime)
        SELECT customerID, MAX(creationDatetime)
        FROM datatable
        GROUP BY customerId;
/* if there exists another fields in tables, then: */
/*  UPDATE temptable, datatable
        SET temptable.field1 = datatable.field1,
            ... ,
            temptable.fieldN = datatable.fieldN
        WHERE temptable.customerID = datatable.customerID
          AND temptable.creationDatetime = datatable.creationDatetime;
*/
    DROP TABLE datatable;
    ALTER TABLE temptable RENAME TO datatable;
END;

To increase performance - create index by (customerID, creationDatetime).


Pros and cons of accessing sql server using ssms locally over on the actual server

As a general practice we never even install SSMS on the server hosting SQL Server.

You got the first two already

1.Risk to accidentally rebooting the SQL Server.

2.Data security on the server.

However there are more:

First of all each session that goes to the server will take up resources (Memory/Cpu/Diskspace,DiskIO) needed by the SQL Server engine, this means a server might get under pressure by allowing people to directly connect on the Server. Which will slow down performance and might even cause timeouts to your applications.

When accessing the SQL Server Server, you can copy and duplicate data on the disks if there is not a good security structure, meaning you can easily fill up the diskspace where your sql server database files are located. Meaning your files won't be able to grow any more

If you have not set up the security rules well, you are able to install other software on the servers also taking away precious memory from the SQL Server server.


I've never tried this but I'm reliably informed it is 100% not possible. It is certainly not supported by Microsoft, for good reason, so why would you do it?

  The SQL Server edition you are installing must match across all the
  nodes in a SQL Server failover cluster. When you add a new node to an
  existing SQL Server failover cluster, make sure that you specify that
  the edition matches the edition of the existing failover cluster.

This comes from this MSDN link found almost instantly after a quick web search.

Bear in mind there are a few obvious and not-so-obvious functional reasons why you would not want to failover from Enterprise to Standard as some features are not supported in both.

The idea of a clustered environment should be that you maintain integrity of the database environment upon failover, so why compromise that in any way, whether talking about support or features?


Get all descendants from 3 hierarchical tables

Identifiers cannot be parameterized, so I can't think of a way to traverse multiple tables with an rCTE.

Assuming UNIQUE or PRIMARY KEY constraints on each table, so we don't have to worry about duplicates.

I wrapped the query in a SQL function to simplify passing the ID:

CREATE OR REPLACE FUNCTION f_all_descendants_of(_id text)
  RETURNS TABLE (descendants text) AS
$func$
   WITH p2 AS (
      SELECT project2_id
      FROM   project2
      WHERE  project1_id = $1
         OR  project2_id = $1
      )

   SELECT project3_id AS descendants
   FROM   project3
   WHERE  project2_id IN (TABLE p2)
      OR  project3_id = $1

   UNION ALL TABLE p2

   UNION ALL
   SELECT project1_id
   FROM   project1
   WHERE  project1_id = $1;
$func$  LANGUAGE sql;

The SELECT on project2 goes into CTE p2, so we can reuse the result for the SELECT on project3. The same is not needed for project1 and project3.

Call:

SELECT * FROM f_all_descendants_of('hnc4');

Produces your desired result exactly.

SQL Fiddle.

If the tables are big, be sure to have the right indexes. Related:

Is a composite index also good for queries on the first field?


scenario to update or insert big amount of data

I would do it like this:

Load all data into an empty staging table and use SQL to identify and correct problematic records before moving the data into your database.


Why not use a table instead of a materialized view?

One of the biggest benefit of using a materialized view is that Oracle takes care of keeping the data in sync.  If you have a separate aggregate table, you are responsible for keeping the data synchronized.  That generally requires a reasonable amount of code and a decent amount of testing and most organizations manage to make mistakes that leave holes that cause the aggregate table to get out of sync.  This is particularly true when you try to implement incremental refreshes of the aggregate table.

Another major benefit is that, depending on the settings, Oracle can use query rewrite to use materialized views when users issue queries against base tables.  So, for example, if you have a bunch of existing reports against a detail table that produce daily, monthly, and yearly aggregate results, you can create a materialized view on the base table that aggregates the data at a daily level and the optimizer can utilize that materialized view for all your existing queries.  This makes it much easier to optimize reporting workloads in a data warehouse without trying to go and rewrite dozens of reports to use your new aggregate table or to mess with DBMS_ADVANCED_REWRITE to force your own rewrites of the queries.


Upgrade SQL Server 2005 to SQL Server 2012

Could either tools (SSMS etc) or SSIS have been installed separately to the main install as 32bit?


Facing warning message while creating tar.gz file

As you have said, you are trying to create a hot image copy backup for cloning and recovery purposes, it's not the recommended way to take image copy backup. The file may contain fractured blocks-which have been changed during the copy command(tar in your case). The tar command has no intelligence to check whether the block has been changed or not during the archiving process resulting broken block known as split or fractured blocks included in the file.

Warning from tar command has spoken the same theme as I mentioned here. The file was being modified by Oracle processes, possibly doing read/write operations, as you said, you were trying to archive these files while the database is up and running.

Instead, if you use RMAN it has the ability to know whether the block is fine or changed by comparing the header and tail of a data block.

Documentation: RMAN Backup Concept/Image Copy Backup


The issue you are having is because your GEOMETRY points are not in the same SPACE REFERECEN ID.  For the first case you are in SRID = 0, for the second case you are in SRID = 4326. This makes the Intersection returns an empty list.

Check this documentation:

https://docs.microsoft.com/en-us/sql/t-sql/spatial-geometry/stintersects-geometry-data-type?view=sql-server-2017


In Mysql You can use SUM with an comparision, which returns 1 if the condition uis met or 0 if not.

SELECT
    `BSEG_ID`, `DST_ID`,
    SUM(`BILL_SQ` BETWEEN 0 AND 10) '0-10',
    SUM(`BILL_SQ` > 10 AND `BILL_SQ` <= 20) '10> <= 20',
    SUM(`BILL_SQ` > 20) '>20'
FROM CI_BSEG_CALC_LN
GROUP BY `BSEG_ID`, `DST_ID`

BSEG_ID | DST_ID   | 0-10 | 10> <= 20 | >20
------: | :------- | ---: | --------: | --:
  11111 | REV-WSC  |    1 |         0 |   2
  11111 | REV_WWSC |    1 |         0 |   2
  12334 | REV-WSC  |    1 |         0 |   2
  12334 | REV_WWSC |    1 |         0 |   2

SELECT
    `BSEG_ID`, `DST_ID`,
    SUM(`res_sum` BETWEEN 0 AND 10) '0-10',
    SUM(`res_sum` > 10 AND `res_sum` <= 20) '10> <= 20',
    SUM(`res_sum` > 20) '>20'
FROM
    (SELECT
         `BSEG_ID`, `DST_ID`,
         SUM(`AUDIT_CALC_AMT`) res_sum
     FROM CI_BSEG_CALC_LN
     GROUP BY `BSEG_ID`, `DST_ID`) t1
GROUP BY `BSEG_ID`, `DST_ID`

BSEG_ID | DST_ID   | 0-10 | 10> <= 20 | >20
------: | :------- | ---: | --------: | --:
  11111 | REV-WSC  |    0 |         0 |   1
  11111 | REV_WWSC |    0 |         0 |   1
  12334 | REV-WSC  |    0 |         0 |   1
  12334 | REV_WWSC |    0 |         0 |   1

db<>fiddle here
For ORACLE

SELECT
    BSEG_ID,DST_ID,
     SUM(CASE WHEN BILL_SQ >= 0 AND BILL_SQ <= 10 THEN 1 ELSE 0 END) "0-10",
     SUM(CASE WHEN BILL_SQ > 10 AND BILL_SQ <= 20 THEN 1 ELSE 0 END) "10> <= 20",
    SUM(CASE WHEN BILL_SQ > 20 THEN 1 ELSE 0 END) ">20"

FROM CI_BSEG_CALC_LN
GROUP BY BSEG_ID, DST_ID

BSEG_ID | DST_ID   | 0-10 | 10> <= 20 | >20
------: | :------- | ---: | --------: | --:
  12334 | REV-WSC  |    1 |         0 |   2
  12334 | REV_WWSC |    1 |         0 |   2
  11111 | REV_WWSC |    1 |         0 |   2
  11111 | REV-WSC  |    1 |         0 |   2

SELECT
    BSEG_ID,DST_ID,
     SUM(CASE WHEN res_sum >= 0 AND res_sum <= 10 THEN 1 ELSE 0 END) "0-10",
     SUM(CASE WHEN res_sum > 10 AND res_sum <= 20 THEN 1 ELSE 0 END) "10> <= 20",
    SUM(CASE WHEN res_sum > 20 THEN 1 ELSE 0 END) ">20"
FROM
    (SELECT
         BSEG_ID,DST_ID,
         SUM(AUDIT_CALC_AMT) res_sum
     FROM CI_BSEG_CALC_LN
     GROUP BY BSEG_ID, DST_ID) t1
GROUP BY BSEG_ID, DST_ID

BSEG_ID | DST_ID   | 0-10 | 10> <= 20 | >20
------: | :------- | ---: | --------: | --:
  12334 | REV-WSC  |    0 |         0 |   1
  12334 | REV_WWSC |    0 |         0 |   1
  11111 | REV_WWSC |    0 |         0 |   1
  11111 | REV-WSC  |    0 |         0 |   1

db<>fiddle here


SELECT INTO with regexp_replace() doesn't write changes into newly generated table

Your regexp_replace() statement is invalid. It would work like this:

SELECT regexp_replace(tbl, '('''')$', '' , 'g')
FROM (
 VALUES
  ('6''''')
 ,('6''''')
 ,('6''''')
 ,('20''''')
 ,('12''''')
 ,('18''''')
 ,('20''''')
 ,('8''''')
 ,('10''''')
 ,('''''')
 ,('''''')
) tbl(tbl)

Your SELECT INTO statement is invalid. It would look like this:

SELECT regexp_replace(tbl, '('''')$', '' , 'g')
INTO   temp table_4
FROM   temp_2
ORDER  BY id;

But I would use neither.

SELECT INTO is discouraged. Only supported for historical reasons. Use CREATE TABLE AS instead, which is the SQL standard way. Per documentation:

  CREATE TABLE AS is functionally similar to SELECT INTO. CREATE TABLE
  AS is the recommended syntax, since this form of SELECT INTO is not
  available in ECPG or PL/pgSQL, because they interpret the INTO clause
  differently. Furthermore, CREATE TABLE AS offers a superset of the
  functionality provided by SELECT INTO.

Bold emphasis mine.

For the presented examples, rtrim() is much simpler and faster:

CREATE TEMP TABLE table_4 AS
SELECT rtrim(tbl, '''')     -- trim all trailing '
FROM   temp_2
ORDER  BY id;


Find the nearest time from another table and add column

This answer might contain some typing erros, and is untested, because i cannot test from a picture...

SELECT
   AssemblyNo,
   SerialNo,
   AssemblyType,
   DefectType,
   BuildDate,
   BuildTime,
   x.Machine
FROM Table1 t1
CROSS APPLY (
    SELECT TOP 1 t2.Machine
    FROM Table2 t2
    WHERE t1.BuildDate=t2.WaveDate AND t2.Wavetime <=t1.BuildTime) x


postgres update parent if entity / foreign key updates

Here is my solution.

CREATE OR REPLACE FUNCTION update_recipe_timestamp_from_entry_proc()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE recipes SET modified_on = now() WHERE ident = NEW."recipe_id";
    PERFORM pg_notify('notify_recipe_update', CAST(NEW."recipe_id" AS text));
    RETURN NEW;
END;
$$ language 'plpgsql';

DROP TRIGGER IF EXISTS update_recipes_timestamp_e ON entries;
CREATE TRIGGER update_recipes_timestamp_e BEFORE UPDATE ON entries FOR EACH ROW EXECUTE PROCEDURE update_recipe_timestamp_from_entry_proc();


One way to do it is to use a table of numbers and CROSS APPLY.

Sample data

DECLARE @T TABLE (RecordID int, date_from datetime2(0), date_to datetime2(0));

INSERT INTO @T (RecordID, date_from, date_to) VALUES
(1, '2017-02-03 08:00:00' , '2017-02-04 17:00:00'),
(2, '2017-02-05 08:00:00' , '2017-02-05 17:00:00'),
(3, '2017-02-06 08:00:00' , '2017-02-10 17:00:00');

Query

In this example I generated a table of 10 numbers on the fly (CTE_Numbers). In production I have a permanent table with 100K numbers.

WITH
CTE_Numbers1(n)
AS
(
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)
,CTE_Numbers
AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY n) AS Number
    FROM CTE_Numbers1
)
SELECT
    T.RecordID
    ,CASE WHEN CA.Number0 = 0
    THEN date_from
    ELSE DATEADD(day, CA.Number0, CAST(T.date_from as date))
    END AS new_date_from
    ,CASE WHEN CA.Number0 = DATEDIFF(day, T.date_from, T.date_to)
    THEN date_to
    ELSE DATEADD(day, CA.Number0 + 1, CAST(T.date_from as date))
    END AS new_date_to
FROM
    @T AS T
    CROSS APPLY
    (
        SELECT CTE_Numbers.Number - 1 AS Number0
        FROM CTE_Numbers
        WHERE CTE_Numbers.Number <= DATEDIFF(day, T.date_from, T.date_to) + 1
    ) AS CA
ORDER BY
    RecordID
    ,new_date_from
;

Result

+----------+---------------------+---------------------+
| RecordID |    new_date_from    |     new_date_to     |
+----------+---------------------+---------------------+
|        1 | 2017-02-03 08:00:00 | 2017-02-04 00:00:00 |
|        1 | 2017-02-04 00:00:00 | 2017-02-04 17:00:00 |
|        2 | 2017-02-05 08:00:00 | 2017-02-05 17:00:00 |
|        3 | 2017-02-06 08:00:00 | 2017-02-07 00:00:00 |
|        3 | 2017-02-07 00:00:00 | 2017-02-08 00:00:00 |
|        3 | 2017-02-08 00:00:00 | 2017-02-09 00:00:00 |
|        3 | 2017-02-09 00:00:00 | 2017-02-10 00:00:00 |
|        3 | 2017-02-10 00:00:00 | 2017-02-10 17:00:00 |
+----------+---------------------+---------------------+


There is nothing you need to do, because this kind of anomaly cannot occur in PostgreSQL.
When the second transaction gets the lock on the row, it will re-read it and see the value modified by the first transaction.
See the documentation for details.


SQL Server - ensuring a complex relation on insert

You can use a Foreign Key for this if you add a unique constraint on Application(Id,SecurityPrinicpalType), something like this:
--drop table if exists ApplicationInstallSecurityPrinicpal
--drop table if exists ApplicationInstall
--drop table if exists Application

go

create table Application
(
  Id int identity not null,
  SecurityPrincipalType int,
  constraint pk_Apppliction
    primary key (Id),
  constraint ak_Application
    unique(Id,SecurityPrincipalType)
)

create table ApplicationInstall
(
  ApplicationId int not null,
  Id int identity not null,
  constraint pk_ApplicationInstall
    primary key (ApplicationId,Id),
  constraint fk_ApplicationInstall_Application
    foreign key (ApplicationID)
    references Application(Id)
)

create table ApplicationInstallSecurityPrinicpal
(
  ApplicationId int not null,
  ApplicationInstallId int not null,
  Id int identity not null,
  SecurityPrincipalType int,
  constraint pk_ApplicationInstallSecurityPrinicpal
    primary key (ApplicationId,ApplicationInstallId,Id),
  constraint fk_ApplicationInstallSecurityPrinicpal_ApplictionInstall
    foreign key (ApplicationId,ApplicationInstallId)
    references Application(ApplicationId,Id)
  constraint fk_ApplicationInstallSecurityPrinicpal_Appliction
    foreign key (ApplicationId,SecurityPrincipalType)
    references Application(Id,SecurityPrincipalType)
)


Performance problem after migration from SQL Server 2005 to 2012

The most likely explanation is that your queries are being executed using different execution plans on the new server, due to changes in statistics, index size and density, and configuration settings.

The configuration settings that have the biggest impact on query optimizer execution plan choices are:

max server memory
max degree of parallelism
cost threshold for parallelism

The question notes that memory has doubled on the new installation, so this is very likely to be the major factor. Intuitively, we might expect more memory to always improve performance, but this is not always the case because the optimizer may start to favour plans with more hashing, sorting, and scans instead of nested loops and index seeks.

For more information, see my previous answer:

difference in execution plans on UAT and PROD server

A setting of 20GB would not normally be large enough to justify setting startup trace flag 2335 (as referenced in that answer), but only testing would prove that one way or the other.

As a potential quick fix, you might consider setting max server memory back to the previous value, while you work on identifying query plans that regressed on the new server, and correcting the underlying causes using normal tuning methodology. I apologise if that sounds generic and hand-wavy, but the reality is that poor performance has very many possible causes, and there are established ways to identify and correct most common ones. The goal of my answer here is to get your system back somewhere close to where you were on 2005.

  After one hour of running with 10Gb of RAM: The last difference is the size of tempdb which is bigger than on the old server and uses now most of the memory, causing Page Life Expectancy to be hardly at 490.

The tempdb database may have grown previously to accommodate sorts and hashes that ended up spilling from memory; it may not need to be so large now. The tempdb database does not use memory any differently from any other database. PLE "hardly at 490" makes no sense as an English sentence.

The important point of setting max server memory back to 10GB was to encourage execution plans similar to the 2005 installation, thereby returning performance to acceptable levels. You don't say if it has helped or not. It is likely you need local professional support at this stage; I think we have reached the limit of what can reasonably be done in Q & A format.


pros/cons of different ways to store whether a record is one of two options?

If you are really sure that those types don't change (or at least not often) you can also use a variation of 3) using a column AddressType of type varchar with a check constraint that limits the values to 'work' and 'home'.

This is less flexible than a lookup table, but still better than a boolean that has some implicit meaning when set to false. And you wouldn't need the join (although I doubt a join against a table with two rows will be noticable)


Replications fails with a vague message: "The last step to run was step 3: Detect nonlogged agent shutdown"

I found the SQL job (under SQL Server Agent, Jobs folder at the left pane of SQL Server Management Studio) associated to Distribution Agent which is in charge of running Synchronization job. I edited the 2nd step of the job i.e. Run agent step (available in the Job Properties dialog that appears by double clicking the job or by right click on the job and selecting Properties).

In the Job Step Properties dialog, I added two parameters to get more log messages:
-OutputVerboseLevel 2 -Output F:\log_dir\snapshotagent.log

I re-run the Synchronization job and after seeing the error, the details were available in F:\log_dir\snapshotagent.log. I hope it is useful for you.


Extract query fingerprints and tables from large log files using pt-query-digest

I would try to use the --sample option:

pt-query-digest --sample 2 --print --no-report /path/to/largeLogFile.log

This would filter out all but the first 2 occurrences of a query fingerprint. --no-report removes the memory and cpu overhead of aggregating.


Postgresql 9.1 PITR file system base backup - do I need to stop database?

If you have archiving of WAL files already on, there should be no need to restart the primary db server.. If WAL archiving is NOT already turned on, then you might have to restart the server to have the configuration change be active. (Looking at the documentation, I believe issuing a SELECT PG_RELOAD_CONF(); is good enough for archiving .. but the postgresql.conf file may specifically say "changing this setting requires a restart")

The only other requirement is that you issue a pg_start_backup('label') and pg_stop_backup() (neither of which require a db service interruption).

The PITR backup will be valid STARTING from the point of your backup..  (As in, you will not be able to recover to a point BEFORE you issue pg_start_backup unless you have another backup from earlier). Start the backup .. perform a file-level backup of all db files .. stop the backup .. perform a file-level backup of the pg_xlog files ... you're done (with the backup process).

To restore, you need to restore all the files you just backed up (plus any additional WAL files that were generated since you ran the backup) and create a recovery.conf file before starting the server.. I won't go through all the details, as you are obviously already looking through the documentation on it.  :)


A large percentage of unused space in the table

If you have deleted a lot of data, this isn't recovered until you rebuild.

ALTER INDEX PK_PrimaryKeyName ON dbo.FREECLIENTDOC REBUILD;

Please see all the options in the documentation before just running the default.

Also, IMAGE is deprecated - if you are storing files in your database tables, you should look into VARBINARY(MAX) - on newer versions of SQL Server you can look at FILESTREAM or FILETABLE.


Is it possible to have a shared secondary Mongo DB instance between two replica sets..?

No you cannot have shared secondary as the sync is based on oplog replay plus a single monogd cannot be a member of 2 replica set.

The only way to have a single machine be a member of multiple replica sets is to run multiple mongod servers on that machine.


You should not need the extra FK in Times as that information can be derived from the existing relationships, but you will need to JOIN in the other tables to get at the extra property. Adding the extra key like that is sometimes a necessary optimisation but it does break "normal form" as you are duplicating data (meaning that either your business layer becomes responsible for maintaining the referential integrity of that duplicate, or you need to do the same in the database using triggers and other "powerful but be very careful with them" features of your chose database).

Your select * from times where stop_id = 1 and line_id = 1 should be something like:

SELECT times.id, times.stopid, ...
FROM   times
JOIN   stops ON times.stop_id=stops.id
WHERE  times.stop_id=1 AND stops.line_id=1

To simplify queries you can create views that abstract out the underlying structure a little, meaning you can keep the data in best form while dealing with it as it it did have the extra columns with duplicated information.

I'm not sure I'd model a timetable store that way though. I would assume that stops are separate entities with a many-to-many link between lines (unless you are counting a stop at which the no. 4, 5, and 45x lines stop at as three separate stops even though they are physically the same location). Also I'd probably want to group the lines together so that all the times for the "no 4" service are identifiable as a related collection but you can distinguish between the arrival times of each (so you can ask "what is the arrival time at X for the service that leaves Y at HH:MM" and so forth. Of course I could just be misreading your intended model! I'm thinking something like:

                                       TimedStop
                   TimedRoute          =============
Line               =============       ts_id (PK)         Stop
============       route_id (PK) ===>  route_id (FK)      =============
line_id (PK) ===>  line_id (FK)        stop_id (FK)  <=== stop_id (PK)
line_name                              arrival_time       stop_name
                                       depart_time        stop_location

Here TimedStop becomes a many-to-many relationship store for timed routes and stops. Of course there are probably several perfectly valid ways to model such a system depending on your other constraints, and the above may be rendered an incorrect model when you consider the fuller design spec (without more detail of what you are trying to model and what outputs are desired this is not possible to completely pin down, I made a fair few assumptions, which may or may not be correct, when throwing the above diagram together).

As another aside on a point of style: select * is generally best avoided in permanent code where possible. If you are instead specific about what you want out this because part of your API and other code using this output has more guarantees of what columns will be returned, even if the underlying structures are updated. It can also allow the query planner/runner to apply extra optimisations (potentially avoiding extra heap lookups or being able to replace a table scan with an index scan, and so forth).


How to get all column names of a table in in multiple rows

The folling query will provide one column, where each column name of the respective table is displayed in a separate row. The order seems random.

select column_name
from information_schema.columns
where table_name = 'questionnaire'
  and table_schema = 'fi';


Index Maintenance on SQL Server 2008 R2 Web edition

For small tables (typically reduced pages count), fragmentation cannot be avoided due to how sql server handles extents.

More info : http://www.practicalsqldba.com/2012/04/sql-server-measuring-index.html


There are some problems with your attempt

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

For the first part I would try something like:

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

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

Instead of GROUP BY we can use distinct as in:

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

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

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

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

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

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


Beginner questions about SQL and relational algebra

Your sql query is wrong:

- Assume there is a student s1 who has passed one exam
  after '2000-01-01' and none before.
  Your query results in {s1} - {} = {s1}. This will be a false positive.

- Assume there is a student s1 that passed three exams
  after '2000-01-01' and one exam before.
  Your query results in {s1} - {s1} = {}. This will be a false negative.

I'm having a bit of trouble reading your algebra expressions, but the first one looks ok. It should not matter that you do the selection (topic = "motorcycle") after the join instead of joining on the selection.

The second one can't be right. Assume there's a newspaper that published both an article on motorcycle and an article on something else. Your expression will pick the article on something else and therefore return that newspaper (incorrectly).


What's the maximum number of characters you can copy from a cell

The maximum number of characters you can render in most SSMS GUI elements is 43,679 (as this search reveals). If you want to copy more, you'll need to either:

extract it to a file
use one of the workarounds here
display it in the output grid in a different way, e.g. by first converting it to XML.


You can use opensource tool like sql-dbdiff or OpenDBDiff. Both are commandline, so can be used in automating scripts.

Also, if you want 3rd party licensed tool then Redgate's SQL Compare (if u want for data compare -- there is data compare as well)  is very useful and I have used it extensively for automation.

Out of curiosity, why do you need Indexes on Staging table as a staging table is meant for temporary loading data and then after cleaning it, the data gets loaded in the primary table ?

EDIT :

Based on your need, best will be to use SQL Server Partition Management Tool from Codeplex.

This utility provides a command line interface to:
1. Remove all the data from one partition by switching it out to a staging table. It creates the required staging table.2. Create a staging table for loading data into a partition. The staging table can be created with or without indexes -- if created without indexes this utility provides a separate command to create appropriate indexes on the staging table, before SWITCHing it into the partitioned table.
The commands can be invoked from other scripts for end-to-end sliding window scenarios. Using the utility allows you to avoid maintaining partition maintenance scripts that must remain synchronized with index or column changes in the permanent table, since necessary staging objects can be created on-demand.


What is the impact of building Postgres with --disable-thread-safety option?

Disabling thread safety will mean that libpq can't be safely used with multi-threaded applications unless the app is very careful to only interact with it using a single thread.

This isn't a concern for the great majority of apps.


SQL Server 2008 R2 Express Silent Install - FullText Service Not working

I solved by doing the following:

Using the original command of the post
Running a command to change the credential of the services (using sc.exe)
Rebooting so the services would start (calling sc start  was not working, even if called multiple times, don't know if it's related to changing the service credentia


Should I split these fields or leave as one?

Splitting given names from surnames is not normalisation.  What would be normalisation would be if you created a series of tables that contained additional details about some of the columns in your table.

For example, you could create a HOUSE table that contains information about the house other than the name, such as it's catch phrase or home town etc.  You might do something similar with TITLE and BOOK.

Don't worry about nulls in your case.  Some people are concerned about too many nulls because it can be a sign that your design is not correct or because they are worried about wasting disk space.  In your case neither of these concerns is germane.