What's the point of WHERE 1=2 for SELECT INTO table query If we want to create a new table from an existing one in SQL Server we can do the following

SELECT * into Table2
from Table1
WHERE 1=2

Whats the point of where clause? I tried it without the where clause and it ran fine. I've seen this where clause in many examples on internet but not the reason why it is required.

The reason you put the WHERE 1=2 clause in that SELECT INTO query is to create a field-copy of the existing table with no data.

If you did this:

select *
into Table2
from Table1

Table2 would be an exact duplicate of Table1, including the data rows.  But if you don't want the data contained in Table1, and you just want the table structure, you put a WHERE clause to filter out all of the data.

BOL SELECT INTO Reference quote:

  SELECT…INTO creates a new table in the default filegroup and inserts the resulting rows from the query into it.

If your WHERE clause has no resulting rows, none will be inserted into the new table:  Therefore, you end up with the duplicate schema of the original table with no data (which would be the desired result in this case).

The same effect can be achieved with TOP (0), for example:

select top (0) *
into Table2
from Table1;

Note:  The SELECT INTO will not duplicate the source table's indexes, constraints, triggers, or partition schema.


Why is this delete operation slower after a foreign key relationship has been added? Imagine the following table about cat owners.

drop table if exists  owners cascade;
create table owners(
    id bigint primary key generated always as identity ,
    name text not null
)
;

insert into owners(name)
select random()::text from generate_series(1,20000);
--insert 200,000 owners records

When I delete some of the owners records it is pretty fast:

delete  from owners
where id %10 = 0;

20000 rows affected in 85 ms

Now I add a table called 'cats' that refers to owners:

drop table if exists cats;
create table cats(
    id serial primary key ,
    name varchar(20000) not null,
    owner_id int not null references owners(id)
);

--insert 1bn cats records
insert into cats(name, owner_id)
select
       random()::text,
       owners.id
from generate_series(1,10), owners;

Lets delete some owners, but first we have to delete the cats that these owners 'own':

--delete the records in cats so we don't get a foreign key constraint violation
delete  from cats
where owner_id %10 = 1;

---now we do the same delete on owners as we did before
delete  from owners
where id %10 = 1;

2000 rows affected in 25 s 828 ms

Why is the second delete from owners ~5000 times slower than when we didn't have the cats table?

It's the check if the owner is still referenced by the cats table during the DELETE. The check is essentially done using a select * from cats where owner_id = ? for every owner that you delete.

You can speed up the check by creating an index on the foreign key column:

create index on cats (owner_id);


Performance Monitoring for Prepared SQL? My company is looking into changing to full use of prepared SQL across the board for some applications. For a few days, I've been trying to answer the question of "Do we have a way to monitor for performance" such as seeing a SQL transaction having high duration/CPU/IO actions and seeing what it was doing to produce that high load. Much of what I've come across has been concerns that this is unnecessary/obsolete in Microsoft SQL Server. I've raised concerns about this but in the mean time I'm still trying to come up with a way to track activity.

The best I've identified is that we can have our standard performance monitoring that we've always had, but we will also need a sort of "magic decoder ring" trace/XE session. This would have to track Prepare/Unprepare SQL actions to get the handle, and the associated statement completed messages to get what the associated query is for each SPID. Then if we encounter in our performance monitoring a transaction saying something like "sp_execute 32 etc etc..." we would look at the SPID requesting this action, take that handle # 32 (or whatever it is) and look at our magic decoder ring to figure out what the parameterized SQL statement is that is being run for analysis.

The answer we would be using currently needs to support SQL2008R2 and up (we have many hundreds of this version deployed at present as we are starting to move to next gen), which is why I mention SQL trace as we still utilize this over XE for many deployments due to the limitations of XE before SQL2012.

We will still be looking at whether or not prepared SQL is a good idea for us, as some new information coming up in research suggests it may not be. I'm simply asking if there is a clean way to directly monitor actions done via prepared SQL without such a cumbersome "magic decoder ring" method for keeping track of what the prepared SQL is.

You would most likely need to trap statement-level events since the "completed" RPC call for sp_execute isn't going to show the batch itself.

Also, you need more than just the SPID. Session IDs get re-used, hence it is not granular enough of a key. You will also need the Connection ID since that is more unique and is what the cached array of prepared statements is tied to.

Finally, what is the impetus for moving to prepared statements? The only benefit of them is saving some number of bytes in the network request if your app is frequently sending over the same parameterized queries. It is generally better to call Stored Procedures so that you are not sending over the queries each time anyway. I did recently here someone suggest that prepared statements could be faster than Stored Procedures, but it was a very specific use-case: simple DML statements executed at least 1 - 2 BILLION times per day (i.e. the same INSERT INTO dbo.Table (@1, @2, @3); executed 2 billion times with different values).

If you are executing non-parameterized ad hoc queries, then using prepared statements is worthless at best, and at worst it is a step backwards given how much harder they are to monitor.

Please also see my answer, also here on DBA.SE, to the following question:

What is the sense and benefit of using SqlCommand.Prepare()?


Need to figure out what FKs point to a specific table In SQLServer 2008 r2.  I need to figure for a specific table what FK's in other tables point to it.

I try:

SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS

But this does not give me what I need.

Any tips?

This looks like it may get you what you need:

http://www.sqlskills.com/BLOGS/PAUL/post/Programmatically-picking-apart-foreign-key-constraints.aspx


MySQL: Do you need to use backticks on column names when using the dot to refer to a table name (table_name.`column`) When using backticks with MySQL so column names don't collide with reserved words:

SELECT `count`, `type`
FROM    table1

Do you still need to use backticks to prevent it colliding with reserved words when you're doing this:

SELECT  table1.count, table1.type
FROM    table1

Should you still add the backticks like this:

SELECT  table1.`count`, table1.`type`
FROM    table1

Any help appreciated.

Your syntax (without backticks) will work on

MySQL - see here and here,

See SQL at the bottom of the post.

(tl;dr) The answer to the question "MySQL: Do you need to use backticks on column names when using the dot to refer to a table name" is "No, you do not need to use backticks!".

See also

PostgreSQL - here,
MS SQL Server - here,
Oracle - here.

However, I beg of you, if you value your own sanity, do NOT EVER use SQL keywords. COUNT and TYPE are such words.  It makes debugging a nightmare and is completely unnecessary anyway. Use identifiers such as my_count or thing_type instead. Just because you can drive your car over a cliff, doesn't mean that you should!

Most (all?) modern RDBMSs have (at least) 64 character(*) identifiers. That_is_more_than_enough_dont_you_think?

Furthermore (and very importantly) if you use the superfluous (and highly annoying) backtick, you'll be bunched when cutting and pasting your SQL to different servers - my advice is to avoid at all costs. It's no longer required and is tricky to type!

Get some SQL naming conventions off the web (example), pick one and stick to it! This is somewhat of a religious topic (an "ecumenical matter" in the immortal words of Fr. Jack!). Read around and decide for yourself!

My own preference is for table names to be singular (a table is a collection anyway), SQL in UPPER CASE and identifiers_in_lower_case_with_underscores - makes reading easy!

You (and those who come after you) will be eternally grateful!

(*) from here, Oracle was 30 bytes up and including 12.1, but it's now 128 bytes - almost a tweet! :-)

Now, consider the following DDL, DML, query and response:

(simple, legible and portable!)

=========== DDL and DML ==================

CREATE TABLE table1
(
  my_count INTEGER,
  my_type  VARCHAR (10),
  count    INTEGER,
  type     VARCHAR (10)
);

INSERT INTO table1 VALUES
(1, 'type1', 34, 'type1_bis'),
(2, 'type2', 23, 'type2_bis'),
(3, 'type3', 56, 'type3_bis'),
(4, 'type4', 78, 'type4_bis'),
(5, 'type5', 90, 'type5_bis');

And a simple query:

SELECT
  t.my_count,  -- see second MySQL sample!
  t.my_type,
  t.count,
  t.type
FROM
  table1 t;

Results:

my_count    my_type     count       type
1             type1        34  type1_bis
2             type2        23  type2_bis
3             type3        56  type3_bis
4             type4        78  type4_bis
5             type5        90  type5_bis


Union Query format in mysql I am training in MySQL.
I have database called ships. My goal is => For each class, define the number of ships of this class that were sunk in battles.
Result set: class, number of sunked ships.
Here are the tables from which I wanna get the result:

I tried to resolve my problem in this way:

SELECT classes.class, COUNT(outcomes.ship) as sunks
FROM classes LEFT JOIN outcomes ON classes.class=outcomes.ship
WHERE outcomes.result='sunk'
UNION
SELECT ships.class, COUNT(outcomes.ship) as sunks
                FROM ships
                LEFT JOIN outcomes ON ships.name=outcomes.ship
                WHERE outcomes.result='sunk'

The result set of my query is just: Bismark and Kogo classes but other classes were gone.

But it was not exactly what I wanted to get.
I want to get all classes - even with zero outcomes.result.

What am I going to do to make it works properly?
Here is the table script just in case: ships

First problem is that you "override the LEFT join by using the WHERE clause referencing that table, so no "NULL" row can show in the results.

Second problem is that you are using an aggregate (count()) but you forgot to add a proper GROUP BY.

Fixing these the query looks like this:

SELECT class, count(ship) as sunks
FROM (
  (
   SELECT classes.class, outcomes.ship
   FROM classes
   LEFT JOIN outcomes ON classes.class=outcomes.ship AND outcomes.result='sunk'
  )
  UNION
  (
   SELECT ships.class, outcomes.ship
   FROM ships
   LEFT JOIN outcomes ON ships.name=outcomes.ship AND outcomes.result='sunk'
  )
) AS tmp
GROUP BY class

There is a fiddle based on your script to see it in action http://sqlfiddle.com/#!9/4840f6/8


is there a recommended way to update many postgres schemes I have a database in postgres which has 15 schemes, the schemes are identical with the same tables, views and functions.

the system uses one scheme at a time, as if they were separate databases. So when i install an update i need to update the 15 schemes in the database.

My problem comes when we update the database with changes in functions and triggers, because I need to modify in each function what scheme uses for each scheme; example:

CREATE OR REPLACE FUNCTION cliente11."restablecerComprobante"("numeroComprobante" text)
  RETURNS integer AS
$BODY$
DECLARE idComprobante INTEGER;
DECLARE tipoComprobante TEXT;
DECLARE idCliente INTEGER;
DECLARE fechaComprobante DATE;
DECLARE totalComprobante NUMERIC;
DECLARE estadoComprobante INTEGER;
DECLARE percepcionIIBB NUMERIC;
DECLARE percepcionIIBBxKg NUMERIC;
DECLARE percepcionIVA NUMERIC;
BEGIN

    -- cargo los datos del comprobante
    SELECT id, tipo, cli_id, fecha_comprobante, total_comprobante, percepcion_iibb, percepcion_iibb_kg, percepcion_iva INTO idComprobante, tipoComprobante, idCliente, fechaComprobante, totalComprobante, percepcionIIBB, percepcionIIBBxKg, percepcionIVA FROM cliente11.lista_comprobantes_a_liberar WHERE estado = 0 AND numero_comprobante ILIKE "numeroComprobante";

    -- otras consultas...

    -- genero las comisiones
    SELECT cliente11."generarComisionesVenta"(idComprobante, tipoComprobante) INTO estadoComprobante;

RETURN 1;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Is there a more practical way to update the 15 schemes without having to modify the script to run in each scheme?

Why don't you set the variable search_path to be each schema in turn?

See http://www.postgresql.org/docs/current/static/runtime-config-client.html


Mysql's internal mysql database in replication I searched around but the word game seems to delude any valid result and could not find anything relevant.

Mysql (the RDBMS) has an internal database named mysql, this database has information about the users and as far as I know, information and settings about the server running the RDBMS.

Background Info

In our set up we have a few MySQL 5.5 servers and one test MySQL 5.6 server.

Replication is set up so that the 5.6 is a slave of one of the 5.5

Incident

This week the replication to the 5.6 stopped working, when doing a show slave status this error was reported:

Error: Error Column count doesnt match value count at row 1 on query.
Default database: mysql.
Query: INSERT INTO tmp_user VALUES (localhost,root,,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,,,,,0,0,0,0,,)

When I checked, the mysql database did not have a tmp_user table, however the error is that the column count doesnt match, what I expected was for the error to be that the table did not exist (Error 1146).

Another conclusion that I gathered from this situation is that we are replicating the database mysql.

Questions

Is there any concern/benefit in replicating the mysql database?
Do you see this scenario as an injection attack? or a security concern?
(Perhaps I am missing a scenario where creating/populating/deleting a tmp_user table is
normal)

Thanks for your time

Answer to 2) question - Most probably you had some minor version upgrade for your master 5.5 MySQL server. There is a bug for 5.0 - Bug #50876 and according to my experience, the same bug exists in 5.5


Transaction Replication on Azure MI servers I'm struggling to verify whether an Azure Managed Instance of SQL Server can both subscribe to Transactional Replication, and Publish, at the same time - I've been googling like a demon and stackexchanging and all the rest of it and am getting wildly conflicting answers!!

Please please please - is this possible? If so, I'll work out how, but as the MI hasn't been put up yet I can't just try it and see...I can't submit the proposal to our change review board until I know how many servers I need, which I don't know until I know if I can do this...I'm stuck in a loop!!

Finally (and I'm happy to put this as a new question if needed...) when setting up the second publication from our other MI, there's no option to change the username for the publication - is this a usual expected limitation? For on Prem I'm used to being able to supply a different username for different publications...my colleague is concerned that having the same user means (and I quote): "that would be trying to use the same log reader agent on two different Publications (with different tables in them)? I'm concerned about conflicts and performance" - personally I don't see how having the same user will cause an issue though???

can [Managed Instance Databases] both subscribe to Transactional Replication, and Publish, at the same time

Yes. Both publishing and subscribing are supported and you can do both, even in the same database, even on the same tables ( ie: Bi-Directional Transactional Replication).

Transactional and Snapshot replication are supported on Managed Instance.  And Managed Instance databases can both publish and subscribe, and a Managed Instance can serve as either a local or remote distributor.

Here are the main supported scenarios:

Publisher with local Distributor on a Managed Instance

Publisher with remote distributor on a Managed Instance

Publisher and distributor on-premises with a subscriber on a single, pooled, and instance database

Transactional replication with single, pooled, and instance databases in Azure SQL Database


How to replicate from SQL Server 2000 to SQL Server 2008 R2? I have SQL Server 2000 as publisher and SQL Server 20008 R2 as subscriber.

I want to replicate data from SQL Server 2000 to SQL Server 2008 R2 on a different machine.

Primary key fields are not defined on some tables so transaction replication is not a choice.

I think merge replication is not a choice because the Subscriber SQL Server version is higher than the publisher.

What can I do?

Bad news: SQL Server 2000 isn't officially supported. You're putting yourself in a situation where the link between these two servers could cause problems, and if you call Microsoft for support, you'll be out of luck.

Don't be That Guy who breaks the production server because he wanted to connect these two servers. Tell your management that SQL Server 2000 is out of support. SQL Server 2008 R2 has lots of cool features to help - rather than working with replication in an unsupported scenario, put your time into getting that old SQL Server 2000 box into a supported scenario.


Is database partitioning for performance reasons meaningful in a virtualized environment? When running SQL server on physical hardware, placing database data and log files on separate disks or RAID arrays can benefit performance, resilience, and maintenance. Partitioning the database over separate physical disks and RAID arrays is also an option. Simply put, more spindles is better.

When SQL server is running in a virtual environment, the concept of spindles applies differently. You often have little to no direct control over what physical disks your virtual disks are mapped to. Does this mean that it doesn't make sense to partition a database, or split the data and log files over separate virtual disks, the same way we do with physical ones?

I understand that there are other reasons to partition databases that still apply in virtual environments. For instance, large read-only tables can be in a separate partition that only needs to be backed up once, but this is not the subject of this post.

Partition-elimination is an awefully good reason to still partition even in a virtual environment, if your queries are such that they can use take advantage of it.


Best practice to recover an CRUD statement to a linked server if connection is lost I am looking for the best practice for the following scenario.

We have a CRM in our company. When an employee updates the record of a company, there is trigger that fires a stored procedure which has a CRUD statement to the linked server hosting the SQL DB of our website.

Question:

What happens when the connection is lost in the middle of the CRUD and the SQL DB of the website did not get updated?
What would be the best way to have the SQL statement processed again when the connection is back?

I read about Service Broker or Transactional Replication. Is one of these more appropriate for that situation?

The configuration:

Local: SQL server 2008 R2

Website: SQL server 2008

Instead of having the trigger rely on the linked server being available, use a queue table. Have a background job that can use sp_testlinkedserver and process all of the items in the queue only when the server is available. It can use TRY/CATCH as well, and only mark those as processed when they are successfully updated, otherwise it will continue trying them every time the background job runs.

This allows you to continue updating the local table and not worry about the connection to the other server (and not making your local transactions wait on updating a remote server).


How do I persist recurring events? I've been tasked with designing a system to recur events. Events may recur daily, hourly, monthly or yearly. Users should be able to schedule events "every 3rd Wednesday of the month" or "The 1st of each month" or "hourly."

Say I'm starting with a 'events' table:

events
-------
id INT
name VARCHAR
notes TEXT
...

What fields/entities/architecture should be used to persist the recurrence rules?

fwiw, I'm using MySQL 5.5.x.

UPDATE (Aug 29, 2012):

Considering iCalendar standards
Reviewed this SO link
Still mulling a better path.

I think this falls under the category of things whose requirements are so varied and the solutions so complex that there aren't standardized solutions.

Ultimately you need these elements:

A system for recording the recurrence schedule.
A system for generating the recurring events.
A system for storing the events generated.

You probably need to spend a fair amount of time determining your requirements before you start implementing a solution, as getting it wrong up front will cost you tons of time later.

You could take a peek at the Mozilla Lightning project, as they have recurring tasks.


Do DB permissions flow from primary server to secondary server in SQL log shipping? Do DB permissions flow from primary server to secondary server in SQL log shipping?
If someone wants db read permissions on the secondary, do we grant on primary and the permission flows to the secondary or do we just grant the db permissions directly on the secondary?

The quick answer is yes, but only for the user permission on the DB, if you got login and user permission on the server-side, you need to copy these ones to the secondary server to avoid the possible issue in case you do a fail over to the secondary server


Timestamp primary & unique I need to create a database for data that looks like this:

I am planning to use this table structure:

CREATE TABLE IF NOT EXISTS `AUDCAD` (
  `date` BIGINT NOT NULL,
  `open` decimal(7,5) DEFAULT NULL,
  `high` decimal(7,5) DEFAULT NULL,
  `low` decimal(7,5) DEFAULT NULL,
  `close` decimal(7,5) DEFAULT NULL,
  `volume` INT DEFAULT NULL,
  PRIMARY KEY(date),
  UNIQUE (date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

date column is minute difference timestamps. It will be unique and if same timestamp insert/update came, can be ignored or override with no problem.

This is my first time planning table structure. Does that sound have problem? Do you have a suggestion over this?

Without knowing the query set that's going to hit this structure, I'll give a generalized answer:

I try to use the TIMESTAMP type for this, which works well with time/date functions, and is timezone agnostic (under the hood it is storing a unix timestamp).

By default, this will only be a 4 byte wide key (instead of an 8 byte bigint), so if you index other columns (which always tacks on the primary key at the end...albeit hidden), those structures can stay skinny and quick as well...and also not eat your RAM ;-).

TIMESTAMP by default has a resolution of 1 second, which is overkill for your purposes, but the easy use of date functions on it makes that worth it.

Main downside: TIMESTAMP is based on a 32-bit unix timestamp integer (it stores that way, but displays as a date/time), so it does eventually run out. At that time (decades from now, when RAM is cheap) you can adjust it, and the date/time display/handling nature of the type will allow you to make the change transparently to your software.

If you want to use it as an integer, you can use FROM_UNIXTIME(your_integer_here) to insert, and UNIX_TIMESTAMP(date) in queries that need that. I'm going to guess though that the date/time format is easier to work with for you though.

Also, you do not require a second UNIQUE key, as any PRIMARY key implies this already, and is included for free in the storage/RAM price of your data table (which is sorted by PK as a B-Tree...your table is an index, and can/will be used as such).


Is it possible in a view to reference a linked server implicitly rather than explicitly? I'm using views and stored procedures that reference a linked server:

SELECT Id, Name
FROM [LinkedServer].Database.dbo.FooView

I would like to hide the [LinkedServer].Database or at least the [LinkedServer] part of my views and stored procedures. This way I would be able to have "server-agnostic" code, that would be easily compared between production and dev servers.

This could be done at the server level (like the linked server) or at the database level (where the views and the stored procedures using it are).

CREATE SYNONYM dbo.FooView FOR [LinkedServer].Database.dbo.FooView

Then just use dbo.FooView in your SPs and they can be the same between production and dev.

Note that this is only for SQL 2005 and up.

A solution for SQL 2000 might be to use views that ONLY SELECT * FROM [LinkedServer].Database.dbo.FooView with no other code in them. You could have a meta-table with the list of these view names and corresponding named sources. Then, write an SP that loops through the table and (re)creates the views as needed. Then, if you want to change where the views point, you run your SP with the new linked server name as a parameter (or keep it in the meta-table and update that) and all your dependent objects will silently point to the new location now. As long as the column names, positions, and data types are the same, it will work just fine.


how to send mail from gmail smtp server by using the ssis I'm just getting into sql-server SSIS, so forgive me if this is a basic question. Use the SSISwant to send mail from  gmail smtp server.

Smtp host =smtp.gmail.com
port=587 ,
From_Mail_ID=xyz@gmail.com,
To_Mail_ID=abc@gmail.com,

Using the above information I want to send mail from SSIS. How do I use SSIS to do this?

If have any questions please ask.

There is a built in task for sending SMTP mail in SSIS.  If you want to use a script task to send mail via SMTP the post below lists several ways to do what you are looking to do.

http://social.msdn.microsoft.com/Forums/en/netfxnetcom/thread/a75533eb-131b-4ff3-a3b2-b6df87c25cc8


MySQL Workbench Model sync sql_mode Using MySQL Work Bench, When I look at the system variables of a server I am connected to, it shows the SQL_MODE as "no engine substitution"

When I open a Workbench Model, and use the "sync with any source" menu item. When it alters the tables, it runs the alter query using the follow sql_mode:

SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

Im not sure where it is getting that from, is it safe to override the sql_mode in the connection  to be just no engine substitute?

Why would it be trying to use a different mode when altering than the server shows it is using?

The server itself is running 5.6.47

MySQL takes his configuration from the my.cnf/ini file where you can activate or disable the modes or options.

There is also the possibility to put options to the program when it starts.

Please read up on the sql Modes or configuration that you want to change, but remember  the defaults are there for a cause, but the Mysql homepage has usual a  lot to tell about see, but read the warnings carefully. Everything could end in disaster.

I alos din't know where you see t taht the sql is empty, i am guessing that you mean https://dev.mysql.com/doc/workbench/en/wb-preferences-general-editors.html

Bujt that is as you can see something different


Multiple Database Design or NOT? I have a database design issue, one that has rattled my brain.

What is the project?

I am designing a platform where different people register a group, each group is allowed to register their own members, it is important to keep these groups away from each other, as each are to their own, each group would have access to 65 or more tables with possibilities of 1000 of rows of data in each table, in their own database, each database is a copy of an empty master database.

Where i am stuck.

I do not know if this is wise cause i could end up with a 1,000 - 30,000 databases. I need to either create a database with a unique name (scenario above) or create a database with a unique environment variable, where each group has it's own environment variable.

Where things get messy.

Assigning each group to a unique database allows me to keep things simple, allows me to access all the data within the group relatively easy (vs searching through 1000 of groups data in one database). a simple but not too important, but still relevant eg. Each group can create things like their own invoices, so if all groups have unique databases then the invoice numbers would follow logically (users perspective), but if all groups are within the same database invoice numbers would not follow logically (users perspective).

The Question Then

Should I, or would it be wise, to create multiple database to keep my system efficient, bear in mind that there is no need to cross over from one group to another.

I suggest:

A single database for all groups, but with
each table partitioned by group.

This should provide you with the convenience of a single database, combined with something like the performance you would expect from separate databases for each group.

Based on the comments on the related question on StackOverflow, you are using MySQL - you can find the official MySQL 5.5 Reference Manual section on partitioning here.

There is also a related StackOverflow question here, and a related blog entry here.


Convert character varying(255)[] to JSONB I currently have array of strings character varying(255)[] and want to convert it to a JSONB object. When I try to cast it directly, it results in an error, despite it being a valid JSON, is there a nice way to convert it?:

ERROR:  cannot cast type character varying[] to jsonb

You can not convert the array of strings to the single JSON object (directly at least). If you need to convert it to the array of JSONs use

select yourvalue::jsonb[];

If you need to convert it to the JSON array use

select jsonb_agg(unnest::jsonb) from unnest(yourvalue);

or

select to_jsonb(yourvalue::jsonb[]);

where yourvalue::jsonb[] ensures that all values in the array is really valid JSON and to_jsonb(...) converts it to the JSONB array.

Example:

with t(x) as (values(array['["a","b","c"]','{"d":"e"}','1']))
select to_jsonb(x::jsonb[]) from t;

Output:

[["a", "b", "c"], {"d": "e"}, 1]


A Remote GUI for PostgreSQL I've installed PostgreSQL 12 server onto an Ubuntu 20.04 server, but I want to access it using a GUI from my Kubuntu 20.04 laptop. I'd be interested to know any options for GUI administration.

I have an old Access database that I used mdbtools to export all tables to csv files. I'm wanting to import those table into PostgreSQL, but I'd really like something nicer than the command line.

One option I know about is pgadmin, however, it isn't clear to me where I should install that. Do I install it on the server or my laptop? I see that it is a web interface, I don't want to run a web server on my laptop just to have a GUI for Postgres located on a server. I'd like some direction from someone who uses a GUI to administer PostgreSQL remotely.

I've used Access, DB2, SQL Server, MySQL, MarinaDB, and Pervasive, but this is my first time messing with Postgres. Hopefully, I've saved the best for last. Please advise.

Use pgadmin (Open source)
https://www.pgadmin.org/

It has the same comofort as phpmyadmin, mysql  workbench ssms, and with some kings.


SQL Server Long Running Transaction - WAITFOR(RECEIVE conversation....DatabaseMail) I have recently implemented an Agent Job which checks SQL Server every 10mins for any long running queries and if detected it will send out a mail to recipients with the information. However since putting this in, I notice alot of the below query and wonder if this is something I should be concerned about:
WAITFOR(RECEIVE conversation_handle, service_contract_name, message_type_name, message_body FROM ExternalMailQueue INTO @msgs), TIMEOUT @rec_timeout

Understand its from Database Mail and the wait info is (1x: 62093ms)BROKER_RECEIVE_WAITFOR but should I need to worry or simply exclude it from the alerting.
Looking at it via sp_whoisactive can see that the open_transaction count is 1 and the status is suspended.
Any help is appreciated.

From my experience, you should ignore it. This query is the one that comes from the program that uses SQL Broker. It's normal. The program is simply waiting on SQL Broker to notify it this way. (Of course waiting on SQL Broker to notify will generate a long running wait.)
We are using the Broker and I have the same thing.


In Memory OLTP causing OOM Issue in SQL Server even though no data in In Memory OLTP Table We have only 1 memory optimized table in each database and there are total 10 databases as such. This table is completely empty. But still we can see in DBCC MEMORYSTATUS Output that In memory OLTP is consuming 7.68 GB out of 13 GB that has been assigned to SQL Server.
MEMORYCLERK_XTP (node 0)                         KB

VM Reserved                                       0
VM Committed                                      0
Locked Pages Allocated                            0
SM Reserved                                       0
SM Committed                                      0
Pages Allocated                             8055696 -- 7.68 GB

Sometimes CHECKPOINT is also not running because of OOM situation.
Other than restarting SQL Service, is there any other solution to resolve this issue?
SQL Server Version: Microsoft SQL Server 2016 (SP2-CU12) (KB4536648) - 13.0.5698.0 (X64)   Feb 15 2020 01:47:30   Copyright (c) Microsoft Corporation  Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Standard 6.3  (Build 9600: ) (Hypervisor)

It's a best practice to use a Resource Pool if running EE, that might help in this case. Also, garbage collection does not occur unless there is memory pressure/activity.


EventLog: Server TCP provider failed to listen on [ 'any'  1433]. Tcp port is already in use This isn't a duplicate; my scenario is different from others I've found:

MSSQLSERVER service does *not* start, port 1433 in use
https://blogs.msdn.microsoft.com/sql_pfe_blog/2016/10/05/tcp-port-is-already-in-use/
https://support.plesk.com/hc/en-us/articles/213409409

Differences:

Neither cls & netstat -oanb -p tcp nor TCPView reveal anything listening on port 1433
According to the EventLog message, this is occurring on IPV6
The problem doesn't manifest for two named instances running on the same machine—only the default instance

To my knowledge, I'm not using IPV6 in any way, shape or form. I certainly haven't configured it.

The service will start when I make any of these configurations:

Disable TCP/IP in SQL Configuration Manager
Enable TCP/IP but turn off Listen All in TCP/IP Properties
When Listen All is off, disable all IPs on the IP Addresses tab

If I do either of these, the service fails to start:

Turn on Listen All
Enable any IP address

This has got me pulling my hair out.

What's gone wrong here?

Because netstat doesn't indicate that anything is listening on port 1433, that leaves the possibility that a client is already using port 1433 when SQL tries to start. From the SQL Server Premier Field Engineer blog post about "TCP Port is already In Use":

  The second situation involves a client running on the server using the same dynamic port as SQL Server. Every TCP session requires two endpoints – a client endpoint and a server endpoint. Each endpoint is an IP Address/Port combination - also known as a TCP socket. Normally, the client socket code will ask the OS to provide a port from a range of dynamic ports. The server socket is the IP/TCP port of the service the client is connecting to (SQL Server for example).

The default dynamic port range might include port 1433. You can check it with netsh int ipv4 show dynamicportrange tcp.

  ...if you happen to have a client (think Windows OS system services) that starts BEFORE SQL Server does, it could use TCP/1433 for its client socket.
  ...
  So, how do you fix and prevent a situation like this? There's another feature within the OS that allows you to tell the OS the TCP ports in the Dynamic Port Range that should be EXCLUDED. All that is needed is to run the following with an Administrative Command Prompt:

netsh int ipv4 add excludedportrange tcp startport=1433 numberofports=1 store=persistent

  After that setting is made, the OS will not give any clients that port. Services can listen on it still, but you prevent clients from getting it when asking the OS for a dynamic port.

IPv6 version

The original poster indicated that even though the logged error mentioned IPv6, his problem turned out to be with IPv4. But if it had been an IPv6 port conflict, the same process can be followed for IPv6.

netsh int ipv6 show dynamicportrange tcp

netsh int ipv6 add excludedportrange tcp startport=1433 numberofports=1 store=persistent


Changing mysql root password on Windows after removing mysqld-nt.exe I am trying to  change password of MySQL root by following the steps here:

http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html

unfortunately the mysqld-nt.exe doesn't exist in Windows anymore according to thread here http://forums.mysql.com/read.php?11,206806,207257#msg-207257

So how would I go about this which exe is the replacement now. I should mention I have MySQL server 5.6

Thank You,

Saher

I got that work using otherwise a much simpler way:

sign in normally to mysql on command line or using the MySQL workbench;

mysql -u root -p

Use mysql database

use mysql;

update user password using simple update query.

update user set password=PASSWORD("NEWPASSWORD") where User='root' and Host = 'localhost';

then flush:

flush privileges;

Done!


How to retreive deleted records in a trigger on a table with composite primary key I am trying to optimize a trigger on a table in a database from a 3rd party. I am not allowed to change or add any columns to the schema.

The table has a composite primary key, one column is an integer, the other is nvarchar.

How can I retreive the deleted rows without using a temp table?

I am currently concatinating the keys in a string in the where clause like this:

SELECT FROM deleted WHERE (deleted.a + CAST(deleted.b as nvarchar)
       NOT IN (SELECT deleted.a + CAST(deleted.b as nvarchar)
       FROM inserted, deleted
       WHERE inserted.a = deleted.a
         and inserted.b = deleted.b))

I have also tried several variants of deleted.%%physloc%% kind of attempts, but with no luck. Is there any more elegant way to filter out the deleted records?

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


Best embed/reference/field strategy for many inserts (mongo) I'm building a gaming backend with Mongo, and have some issues on how to best design the schema to maximize performance and database size.

My models:

User

Match
 -ReferenceMany (User)
 -ReferenceMany (Score)

Score
 -ReferenceOne (Match)
 -ReferenceOne (User)

It takes 2 users to start a match. Say we get 10.000 users, and all play one match each day against another user, we get 5000 games a day.

Each match has three turns, which gives 6 scores, so for 5000 games we get 30,000 scores. These scores can be inserted simultaneously (in each game), so I have to make sure one user doesn't overwrite another users score. I believe I've solved that by having the scores in their own collection, and embedding them on the match, like so:

$score = new Score();

$score->setUser($user);
$score->setScore($playerScore);
$score->setGame($game);
$score->setMatch($match);

// Save score.
$dm->persist($score);

// Add score to match.
$match->addScores($score);

My issue with this approach though is that it takes 6 queries just to insert a score (there are some validating queries before the above code). With 30.000 scores a day, that's a whole lotta queries.

My initial thought was to just add the scores to the match as an array, but what will happen the if two users submit at the exakt same time, updating the same match?

I'd be really interested in hearing some opinions on this. Let me know if I need to clarify anything. I'm using Symfony2 with Doctrine ODM if that helps.

Why do you have Score model?
Why not keep only User and Match models and for each have a score attribute?
In this schema you have only the updates per score.
If its due to redundancy of attributes, please note this is common in a document based schema design.


MongoDB shard keeps growing, I don't know why? I've had a project dumped on me and I'm trying to get up to speed withMongo.   But I have an immediate firefight, in that one shard in my Mongo cluster just keeps growing and growing, adding more files daily, and I can't find the cause.   Our cluster is simple, 12 sharded replica sets.   "rs1" is the primary shard, and it's the one that keeps gobbling up more and more disk space while the other 11 shards haven't grown at all.    Our data is simple too, we just create 3 collections per day of our 3 document types.  And we keep those for 90 days.  So roughly at any time we have about 270 or so active collections, and nightly we drop the oldest collections as new collections are created for upcoming days.

The balancer seems to be running fine, I can see the chunk counts getting balanced in the collections normally.

Is there some way I can STOP mongo from sending any more data to my problem shard while I get a handle on what's going on?   I've already tried setting a maxSize, but that hasn't prevented it from still growing.   Right now rs1 is taking up about 885 2gb disk files.   (note that this is Mongo 2.6)

  shards:
      {  "_id" : "rs1",  "host" : "rs1/psc1c250:39080,psc2c250:39080",  "maxSize" : 1500000 }
    {  "_id" : "rs10",  "host" : "rs10/psc1c259:39080,psc2c259:39080" }
    {  "_id" : "rs11",  "host" : "rs11/psc1c260:39080,psc2c260:39080" }
    {  "_id" : "rs12",  "host" : "rs12/psc1c261:39080,psc2c261:39080" }
    {  "_id" : "rs2",  "host" : "rs2/psc1c251:39080,psc2c251:39080" }
    {  "_id" : "rs3",  "host" : "rs3/psc1c252:39080,psc2c252:39080" }
    {  "_id" : "rs4",  "host" : "rs4/psc1c253:39080,psc2c253:39080" }
    {  "_id" : "rs5",  "host" : "rs5/psc1c254:39080,psc2c254:39080" }
    {  "_id" : "rs6",  "host" : "rs6/psc1c255:39080,psc2c255:39080" }
    {  "_id" : "rs7",  "host" : "rs7/psc1c256:39080,psc2c256:39080" }
    {  "_id" : "rs8",  "host" : "rs8/psc1c257:39080,psc2c257:39080" }
    {  "_id" : "rs9",  "host" : "rs9/psc1c258:39080,psc2c258:39080" }
  databases:
    {  "_id" : "admin",  "partitioned" : false,  "primary" : "config" }
    {  "_id" : "prodLN",  "partitioned" : true,  "primary" : "rs1" }

rs1:PRIMARY> db.stats(1024*1024*1024)
{
    "db" : "prodLN",
    "collections" : 319,
    "objects" : 301960471,
    "avgObjSize" : 3532.3332748146363,
    "dataSize" : 993,
    "storageSize" : 1626,
    "numExtents" : 4436,
    "indexes" : 634,
    "indexSize" : 25,
    "fileSize" : 1765,
    "nsSizeMB" : 16,
    "dataFileVersion" : {
            "major" : 4,
            "minor" : 5
    },
    "extentFreeList" : {
            "num" : 1820,
            "totalSize" : 81
    },
    "ok" : 1
}

If you have one database with hundreds of collections (it's not clear that is the case because you have not included an sh.status() or similar but that seems to be what you are implying), then here is the likely scenario:

The prodLN database has rs1 as its primary shard
Therefore all new collections created within that database will be created on rs1
If you then shard the collections (data is sharded on a collection level, not a database level) the data will be migrated from rs1 to other shards, but then you must rely on MongoDB to clean up the data post-delete (this also assumes a good shard key to distribute the data well across the shards)
You have not mentioned what version you are running, but there are lots of answers out there about deleted space reuse already (some pointers: 1 2 3), let's just say it's not very efficient for certain usage patterns (including yours)
Someone has added a maxSize parameter to the database, so it will be considered "full" and never be a target for chunk migrations, but that does not override the primary shard behavior when creating new databases, they still go on the primary shard, take up space, then get moved off by the balancer

What can you do? Well, you have a few options, but they require changes and/or downtime for the use of the current database:

Start using more than one database, make sure that the new databases have different primary shards (use movePrimary if not), start writing data there, maybe rotate around several
You can use movePrimary on the current database, but you have to stop writing to any unsharded collections (read the behavior section in the link above).  Depending on how much data there is in unsharded collections this could take some time.
You can attempt to reclaim space on the problem shard to buy yourself some time.  If you want to minimize downtime then I would recommend a rolling resync but this is only possible if you have 3 voting members in each set - you may only have two based on your output, unless arbiters have been excluded.  You could add a third member temporarily to the problem shard to perform the resyncs, then remove it later.


Slave error after master upgrade I have two MySQL servers in replication (master -> slave).

I updated them with apt-get upgrade. It runs well on both except the I get some replication errors on slave now.

  Last_Errno: 1005 Last_Error: Error 'Can't create table
  performance_schema.cond_instances

  (errno: 131 "Command not
  supported by database")' on query.

  Default database: 'mysql'.

  Query:
  'CREATE TABLE performance_schema.cond_instances(NAME VARCHAR(128) not
  null,OBJECT_INSTANCE_BEGIN BIGINT not null)ENGINE=PERFORMANCE_SCHEMA'

Server versions are as below:

Master
Server type: MySQL
Server version: 5.5.62-0+deb8u1-log - (Debian)

Slave
Server type: MariaDB
Server version: 10.1.26-MariaDB-0+deb9u1 - Debian 9.1

First, you need to make sure MySQL slave user is active and has the needed permission then check if MySQL OS user on the slave machine has the needed access.


I have deleted Oracle listener from windows services I am using oracle 11 express edition and I have deleted the Oracle Listener service from windows services mistakenly and now I can't connect to the database.
Could someone help me to recreate the service again using command line.

Test case deleting  Listener and creating service manually

C:\>lsnrctl status

LSNRCTL for 64-bit Windows: Version 11.2.0.2.0 - Production on 04-AUG-2019 02:58:56

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.2.0 - Production
Start Date                04-AUG-2019 02:57:36
Uptime                    0 days 0 hr. 1 min. 23 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\listener.ora
Listener Log File         C:\oraclexe\app\oracle\diag\tnslsnr\XXX\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XXX)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XXX)(PORT=8080))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "XEXDB" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
Service "xe" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
The command completed successfully

C:\>sc query type=service | findstr Oracle*
SERVICE_NAME: OracleXETNSListener
DISPLAY_NAME: OracleXETNSListener
SERVICE_NAME: OracleServiceXE
DISPLAY_NAME: OracleServiceXE

I'll stop listener and delete the service

C:\>sc delete OracleXETNSListener
[SC] DeleteService SUCCESS

C:\>lsnrctl status

LSNRCTL for 64-bit Windows: Version 11.2.0.2.0 - Production on 04-AUG-2019 03:00:31

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   64-bit Windows Error: 2: No such file or directory
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=XXX)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   64-bit Windows Error: 61: Unknown error

Creating service manually

C:\>sc create OracleXETNSListener binpath=C:\oraclexe\app\oracle\product\11.2.0\server\BIN\tnslsnr.exe
[SC] CreateService SUCCESS

C:\>lsnrctl start

LSNRCTL for 64-bit Windows: Version 11.2.0.2.0 - Production on 04-AUG-2019 03:01:20

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Starting tnslsnr: please wait...

TNSLSNR for 64-bit Windows: Version 11.2.0.2.0 - Production
System parameter file is C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\listener.ora
Log messages written to C:\oraclexe\app\oracle\diag\tnslsnr\XXX\listener\alert\log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XXX)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.2.0 - Production
Start Date                04-AUG-2019 03:01:22
Uptime                    0 days 0 hr. 0 min. 1 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\listener.ora
Listener Log File         C:\oraclexe\app\oracle\diag\tnslsnr\XXX\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XXX)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Optional:Once the service is created go to services and configure service to auto start


Trouble installing Postgresql I'm trying to Install latest version of PostgreSQL and keep running into the below problem which states -

  Problem running post-install step. Installation may not be complete
  correctly Error running sc start "PostgreSQL 10 Server":Program ended
  with an error exit code

Other important information

Using windows 10
I'm not the administrator / user
Installing version 10 "PostgreSQL-10.3-1-win64-bigsql" file from PostgreSQL website
Running 64 bit code
Double clicked and ran the installer file
Symantec enterprise antivirus is installed

bituninstaller information is below:

  Windows Specific Configuration [23:04:29] Windows Actions (services)
  Starting service "PostgreSQL 10 Server" Executing sc start "PostgreSQL
  10 Server" Script exit code: 1060

Script output:

  [SC] StartService: OpenService FAILED 1060: The specified service does not exist as an installed service.

  Script stderr: Program ended with an error exit code

  Error running sc start "PostgreSQL 10 Server": Program ended with an error exit code

  Problem running post-install step. Installation may not complete correctly

  Error running sc start "PostgreSQL 10 Server": Program ended with an error exit code

  [23:16:32] Running pgAdmin post installation actions

  Installing pgAdmin3 ... Executing C:\PostgreSQL_re/pgc install pgadmin3

  Script exit code: 0

Running an installer without administrator privileges is seldom succesful.  Why don't you follow the steps outlined here:

https://stackoverflow.com/questions/26441873/starting-postgresql-and-pgadmin-in-windows-without-installation#26441939


How to grant Unmask permission to a user at table level and column level in sql server? How to grant unmask permission to a user for one particular table and to one particular column within a table?

For example, if in table1 if column1 and column2 is masked and in table2 if column3 and column4 is masked, for user usr1 with select Access if i want to provide unmask only to table1 and only column1 how to do so?

Unfortunately, there is no granularity for the UNMASK permission in SQL Server, users either see all masked data as masked or unmasked depending on whether they have UNMASK granted.

You could create a low-privilege user that has SELECT and UNMASK and create a stored procedure to retrieve only the columns you're after into a temporary table while impersonating this user. These results can then be joined back to your masked table after reverting the impersonation so that the columns that should be masked are and those that should be unmasked are retrieved from the temp table.


about giving same column name in every table of database I have a database with 5 tables.
In every table there is a column for checking status.
If i give the name to that column which is same in all the 5 tables;
What will happen?
Is it correct according to conventions?

You can skip table name prefix in the status column, You can have all the tables with same column name and same datatype, that will make your naming uniform through out the tables in the database

I have seen Database with 2000 of tables, and most of them have column status with  same datatype. TINYINT in our case.

Let say a query.

SELECT T1.columnName...., T2.Column,,...
FROM Table1 T1
JOIN Table2 T2 ON .....
WHERE T1.Status = 1 and T2.Status =1

If the status column is only for row data active and inactive, you can choose datatype as bit or TINYINT incase you are looking to add more option in future


MySQL databases on their own separate Logical Volume I'm looking for a way to keep MySQL database sizes within certain limits. That's why I was thinking about creating a logical volume for each database. So for example:

db1 => lv_db1 (5 GB)
db2 => lv_db2 (5 GB)
...

This way the databases will not be able to grow beyond their 5 GB.

I know the databases are stored in /var/lib/mysql/<database>, so my idea is to mount the logical volume to this folder. However, the problem now is that I need to create and mount the logical volume before creating the database, which causes the CREATE DATABASE to fail because it thinks the database already exists.

ERROR 1007 (HY000): Can't create database 'foo'; database exists

Does anyone have suggestions how I can achieve this?

My backup solution is to mount one logical volume to /var/lib/mysql, but that way one database could claim all the space.

This may sound twisted but here it goes....

DON'T USE CREATE DATABASE

If you login as root and you do the following:

cd /var/lib/mysql
mkdir db1
chown -R mysql:mysql db1

you just created the database. The INFORMATION_SCHEMA database will dynamically register db1 in the INFORMATION_SCHEMA.SCHEMATA table as a database.

Since you want to create a volume for it, you can do this

cd /var/lib/mysql
mkdir db1
mount /dev/sdb db1
chown -R mysql:mysql db1

GIVE IT A TRY !!!

Bad News: Backing up /var/lib/mysql will not scoop up mount volumes. You will have to resort to doing mysqldumps. That should not be an issue if you have 5GB mounts.


How to fine tune a huge problematic Heap Table? Problem:
A huge table is causing to a critical performance issue on a very critically important production database (MS SQL Server 2016 SP1 Standard Edition).
Facts about the problematic Table :

Heap Table without index.
450 GB of Size.
More than 190 million records.
More than 900 MB/s Physical I/O activity every time a simple SELECT
Query executes upon. Worst if the query includes WHERE clause.
Queries executed upon this table are causing 4-5 minutes delays on the application UI.

Suggested solution by the application/system manufacturer:

Convert the Heap Table to a Clustered Index Table.

Suggested performance tuning steps with respect to suggested solution:

Truncate db table (agreed with customer to delete records older than 1 year).

Don't Delete Records. Copy records created in last 1 year to a temp table. Truncate the original table, and then INSERT the records you have copied from the temp table to the emptied original table.

Make a de-fragmentation on the db table.
Migrate the db table from heap to clustered table.
Create a Clustered Index.
Create a maintenance job and schedule it to re-build the clustered index periodically.

My question to community:
How would you improve the performance of such a problematic table? What would you add to or remove from the suggested performance tuning steps?
Much appreciated and thanks a lot in advance.
Edited from here on:
OK, I didn't want to reply to all comments 1 by 1. So I'm editing the question and adding some more information.
1st : I am not an expert DBA. I would say an advanced beginner. That's why I am here to learn from the community as much as I can.
2nd : We just got this customer 2 weeks ago, and I have not got full access to the environment myself. This information (facts) about the problem, about the table is directly from the customer's sys admins.
3rd : There for, I have no idea why the table is Heap in the first hand, without an index, and they let it to grow this much without any partitioning and what so ever.
4th : Suggested Solution and Suggested Steps are not my own but directly from the manufacturer of the whole system (it's a well know Unified Communications System and the manufacturer is the best in this area. I guess you all know which one).
5th : I will hopefully have a full access to the environment next week and before that I just wanted to share what I got in hand and try to get as much opinion/suggestion as I could prior to going into this mess.
6th : Because unfortunately I am the one who is going to take this mess from here and try to make it better and smoother working mess.
Thanks to everyone who already spare some time and replied. I already got some insight. And I will definitely share more info as soon as I have more in hand.

Based on those facts, 190M rows, 450GB, un-indexed heap, long-running queries, you should create a Clustered Columnstore index and store the data in compressed columnar format.
However, that might not be the optimal solution, as you still have a table with no key and no indexes, which is strange.


Multiple database logs in backup device I am working on tidying up our log back up Jobs (Sql 2008r2 ), which have grown into a bit of a mess!

I am setting up a trn log back up job to a remote folder for each day ( in this example  called Monday).
I  then plan to set a backup device to each folder with a filename of [DAYNAME]logs.trn eg Mondaylogs.trn

My plan is that the maintenance job will back up 5 production databases ( database A B C D E) log files every 15 minutes to the back up device file Mondaylogs.trn

The one thing I just want to confirm is, if I want to restore to a point in time on a Monday for  just one of the database  (let us say database B) I will be to achieve this from the single file Mondaylogs.trn?

If I am incorrect do I have to set up devices MondaylogsA.trn MondaylogsB.trn and so on?

Whenever you want to restore a backup/log from a file which has more than 1 file inside, you can always select the file you want to restore for example

{RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN' WITH NORECOVERY, FILE = 1
GO}


How do I test if MySQL "require secure transport" is working? I activated the checkbox named "require secure transport" in the options file, found in the MySQL Workbench.

How do I know if MySQL "require secure transport" is working? Should I use command line at this point?

Just try to connect in a non-secure way and be sure it doesn't work.

If you are not sure wether you are using SSL or not to connect, then yes, the simplest way is using the command line client:

mysql --ssl-mode=DISABLED


Does mongodb support non readable files? I have uploaded PDF/PST file in mongodb by mongofiles, but when retrieving it in R(BSON) and converting it to "Character" type, I couldn't find the PDF/PST file. Is it possible to upload
PDF file or PST file? Which file formats does mongodb support (other than CSV,TSV,TXT,audio) ?

BSON has a Binary data type in which you can store anything you want. MongoDB sees it as a binary blob and does not care what type of data it is, so of course ot is possible to store PDF files. MongoDB documents are limited to 16 MB in size, so if you need to store large data, try GridFS.

http://blog.mongodb.org/post/183689081/storing-large-objects-and-files-in-mongodb


Can I get pg_rewind to show me lost data after a Postgres failover? Say I have Postgres setup with a warm standby using the build in replication features. Now, imagine a scenario where the data centre the master loses power and the slave is in another data centre and I promote it to a master. Later, the original master comes back up. At this point, I could use pg_rewind to rewind the original master into to a state where it can follow the new master. Is there any way I can see what changes pg_rewind has has undone so I could, for example, manually fix up the data by rerunning inserts against the new master if it was important?

If you had made a backup of the old master's pg_xlog content that was not applied on the new master after the point of divergence, as they call it, you can use pg_xlogdump (included in the PG distribution since 9.3) to examine the contents of transaction logs on the old master:

$ pg_xlogdump -p <pg_xlog_dir> <segment_file_name>

Unfortunately, WAL only contain the changes to the physical pages in the database cluster, so it is currently impossible to extract full SQL statements, but at least this should give you an idea of whether the number of changes made were significant and whether they hit any relevant relations.

You can use the output's xid field as an indication of how many transactions have been applied to the old master since the split, and the rel field's last number will tell you which relation a change has been applied to (look at pg_class.relfilenode to figure out what exact relation that would be):

SELECT  c.relname AS relname,
        n.nspname AS schema,
        c.relkind AS reltype,
        t.spcname AS tablespace
    FROM pg_class c
        LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
        LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
    WHERE c.relfilenode = '<number>';

In short, it is not going to be an easy task, but it is possible to get at least some information out of it.

Another, much easier way, would be to turn on statement logging, but that will probably cost you quite a bit unless you're storing logs on a separate device so that it doesn't affect server operation too much (log_min_duration_statement = 0).


Optimize two sql sentences adding indices I'm optimizing my SQL Server database using SQL Server Management Studio Activity Monitor.

The two more expensive sql sentences are:

set @codesToPrintCount =
    (select count(CodeId)
        from Code
        where CommissioningFlag = 255
        and AggregationLevelId = @codeLevel);

SET @code = (SELECT TOP 1 Serial
                FROM Code
                WHERE CommissioningFlag = 255
                    and AggregationLevelId = @codeLevel);

This is Code table sql script:

CREATE TABLE [dbo].[Code] (
    [CodeId]            INT            IDENTITY (1, 1) NOT NULL,
    [Serial]            NVARCHAR (20)  NOT NULL,
    [AggregationLevelId]  TINYINT      NOT NULL,
    [CommissioningFlag] TINYINT        NOT NULL,
    [ ... ]
    CONSTRAINT [PK_CODE] PRIMARY KEY CLUSTERED ([CodeId] ASC),
    CONSTRAINT [UC_CODE_SERIAL] UNIQUE NONCLUSTERED ([Serial] ASC),
    CONSTRAINT [FK_Code_AggregationLevelConfiguration]
           FOREIGN KEY ([AggregationLevelId])
            REFERENCES [dbo].[AggregationLevelConfiguration] ([AggregationLevelConfigurationId])
)

I don't know how I can speed up these sentences.

There are more rows in Code that table have the same value on AggregationLevelId column than columns with the value 255 on CommissioningFlag. In other words, on Code table there 1.050.000 rows with AggregationLevelId equals to @codeLevel and 32 rows or less with the value 255 in CommissioningFlag column.

Is it a good idea add two indices on this table? One for CommissioningFlag and another one for AggregationLevelId.

At this moment there are 1.100.000 rows on Code table and they get 23ms and 78ms respectably to execute.

By the way, these two sentences are in a stored procedure.

If you're not interested in rows that aren't 255, you could use a filtered index:

CREATE INDEX ix255 ON Code (AggregationLevelId)
INCLUDE (Serial, CodeId)
WHERE CommissioningFlag = 255;

This means it will only index the ones that have 255, in AggregationLevelId order, including the values of Serial and CodeId because you need to count/return those.

If you cannot (or do not want to) use a filtered index, try the following (as originally suggested in a comment):

CREATE INDEX pick_a_name_for_the_index
ON Code (CommissioningFlag, AggregationLevelId)
INCLUDE (Serial, CodeId);


Query is fast but becomes sluggish when created as a view Execution Plan as Query

SELECT VP.Branch
     , VP.ROUTE AS Route
     , VP.SAPCustomerID
     , S.CustomerID
     , S.ProductID
     , S.Date
     -- Group by Customer
     , CustomerQuantity = SUM(S.Quantity) OVER (PARTITION BY VP.Branch, VP.ROUTE, VP.SAPCustomerID, S.ProductID, S.Date)
     , CustomerFourWeekSalesAvg = SUM(S.FourWeekSalesAvg) OVER (PARTITION BY VP.Branch, VP.ROUTE, VP.SAPCustomerID, S.ProductID, S.Date)
     -- Group by Route
     , SUM(S.Quantity) OVER (PARTITION BY VP.Branch, VP.ROUTE, S.ProductID, S.Date) AS RouteQuantity
     , RouteFourWeekSalesAvg = SUM(S.FourWeekSalesAvg) OVER (PARTITION BY VP.Branch, VP.ROUTE, S.ProductID, S.Date)
     -- Group by Branch
     , BranchQuantity = SUM(S.Quantity) OVER (PARTITION BY VP.Branch, S.ProductID, S.Date)
     , BranchFourWeekSalesAvg = SUM(S.FourWeekSalesAvg) OVER (PARTITION BY VP.Branch, S.ProductID, S.Date)
FROM vw_SalesByWeek AS S
INNER JOIN SAP_VisitPlan AS VP WITH (NOLOCK)
    ON VP.CustomerID = S.CustomerID
    AND VP.DateFrom <= S.Date
    AND VP.DateTo >= S.Date

Execution Plan as View

-- Where vw_SalesByWeekSummary is the query above exactly.
SELECT [Branch]
      ,[Route]
      ,[SAPCustomerID]
      ,[CustomerID]
      ,[ProductID]
      ,[Date]
      ,[CustomerQuantity]
      ,[CustomerFourWeekSalesAvg]
      ,[RouteQuantity]
      ,[RouteFourWeekSalesAvg]
      ,[BranchQuantity]
      ,[BranchFourWeekSalesAvg]
FROM vw_SalesByWeekSummary
WHERE Route = '0600'

Problem

The query alone or as a stored procedure works just fine; however, the query as a view decides to do scans instead of seeks and uses different indexes.
How can I get the query to behave properly as a view? What is causing it to use different indexes and scan instead of seek?

SQLPlan files

Solo Query Plan
View Query Plan

Indexes

-- Solo Query Plan Indexes
CREATE NONCLUSTERED INDEX [IX_VisitPlan_ByRoute] ON [dbo].[SAP_VisitPlan] ([ROUTE] ASC) INCLUDE ([Branch])
CREATE UNIQUE NONCLUSTERED INDEX [UIX_CacheCS_ByWeek] ON [dbo].[Cache_ConvSalesByWeek] ([CustomerID] ASC, [ProductID] ASC, [WkStartDate] ASC) INCLUDE ([ID], [SoldQuantity], [Route], [FourWeekSalesAvg], [NumberOfPriorSalesWeeks])
CREATE NONCLUSTERED INDEX [IX_CacheSS_4wkAvg] ON [dbo].[Cache_ScanSalesByWeek] ([CustomerID] ASC, [ProductID] ASC, [Route] ASC) INCLUDE ([FourWeekSalesAvg], [WkStartDate], [SoldQuantity])

-- View Query Plan Indexes
CREATE UNIQUE NONCLUSTERED INDEX [UIX_VisitPlan_PK] ON [dbo].[SAP_VisitPlan] ([SAPCustomerID] ASC, [CustomerID] ASC, [DateTo] DESC, [DateFrom] DESC, [ROUTE] ASC, [DriverNumber] ASC) INCLUDE ([Branch])
CREATE NONCLUSTERED INDEX [IX_CacheCS] ON [dbo].[Cache_ConvSalesByWeek] ([CustomerID] ASC, [ProductID] ASC, [WkStartDate] ASC) INCLUDE ([SoldQuantity], [FourWeekSalesAvg], [Route])
CREATE NONCLUSTERED INDEX [IX_ScanSalesByWeek_Sunday] ON [dbo].[Cache_ScanSalesByWeek] ([WkStartDate] ASC) INCLUDE ([CustomerID], [ProductID], [SoldQuantity], [Route], [FourWeekSalesAvg])

The essential problem is that filtering on Route = N'0600' before computing the window functions (as in the query) is different from filtering on Route = N'0600' after computing the window functions (as in the view).

This would give different (= incorrect) results for the window functions in general, so the optimizer does not do that. See Of Windowing Functions And Where Clauses by Erik Darling for more information.

If all the window functions in the view were partitioned on Route, the optimizer would consider pushing the predicate down into the view because correct results would still be obtained. Sadly, that is not the case for your view. Adding OPTION (RECOMPILE) would not help in this case.

Consider rewriting (or supplementing) the view as an inline table-valued function, with an explicit parameter for Route. I have an example of the technique in an answer on Stack Overflow.

If you have other filter criteria beyond Route alone, and if you're going to have a stored procedure anyway, you could abstract away the type of filter criteria - make a TVF for route, and when route is passed to the stored procedure, call it. Make another TVF for date range, and when dates are passed, call it. Gets complicated when they pass both route and dates, if that's possible, but separation is likely going to be the safest way to get reliably predictable performance regardless of which parameter is passed. If you have optional filter criteria it may be that dynamic SQL and/or option (recompile) is your best bet.


Mysql REPLACE INTO query for Multiple rows insertion I'm trying to add multiple rows using 'replace into' , but its updating one row.

REPLACE INTO user_balance
(user_id,acc_type,currency,balance,enable_deposit,
enable_withdrawal,pending_balance,update_ip)
VALUES  (NEW.id,1,'USD',0,1,0,0,NEW.ip_address),
(NEW.id,1,'GBP',0,1,0,0,NEW.ip_address),
(NEW.id,1,'EUR',0,1,0,0,NEW.ip_address),
(NEW.id,1,'BTC',0,1,0,0,NEW.ip_address);

Is this right, the way am doing it?

PS: am employing a trigger to update the balance table whenever an user is created in the users table. It works well with if I replace one row, for multiple rows, it updates only the last values.

You should avoid using triggers in conjunction with REPLACE INTO on multiple rows. Why ?

REPLACE INTO is nothing more than a mechanical DELETE and INSERT. It can incite mysqld to address deadlocks (See my answer to How I prevent deadlock occurrence in my application?)

Here are two comments that shows the LIFO approach to processing triggers

  Posted by Atif Ghaffar on September 23 2007 9:12pm    [Delete] [Edit]
  PLEASE Note that the REPLACE does a DELETE operation.

  We did not realize this and had the triggers that should be triggered
  on DELETE triggered.

  After checking all the code, we just found a script that does a
  replace to refresh the values of some fields.

  We should have had used "insert into ... on duplicate update" syntax
  instead.

  Posted by J Mike on May 4 2009 11:06pm    [Delete] [Edit] If you are
  using REPLACE INTO... triggers are fired in this order (if delete of
  duplcate key is used):

  before insert
  before delete
  after delete
  after insert

Your problem ? An UPDATE trigger will never fire off if this has to be done multiple times in a single operation. At least, I don't see that happening.

SUGGESTIONS

You may want to use INSERT ... ON DUPLICATE KEY UPDATE (See my answer to Update one table from another table while sorting that table based on one column)
Stick to doing REPLACE on one row at a time


Implementing a PIVOT query How can I pivot the following SELECT statement:

         SELECT sc.CID,
                sc.CodeName     as OverviewText,
                scRAG.CodeName  as RAGStatusText
           FROM StatusCode      sc
LEFT OUTER JOIN ProjectOverview po
             ON sc.CID          = po.ProjectOverviewCID
            AND po.ProjectId    = 180
LEFT OUTER JOIN StatusCode      scRAG
             ON po.RAGStatusCID = scRAG.CID
          WHERE sc.SCID         = 18

Such statement yields the result set that follows:

CID OverviewText    RAGStatusText
--- ------------    -------------
153 Cost            Green
154 Requirements    Yellow
155 Schedule        NULL
156 Technical       NULL
157 Testing         NULL

But, I want it to return one single row with 10 values, as shown bellow:

 ----------------------------------------------------------------------------------------------
 | Cost | Green | Requirements | Yellow | Schedule | NULL | Technical | NULL | Testing | NULL |
 ----------------------------------------------------------------------------------------------

Can I pivot on CID?

I'm not sure if that is exactly what you want. But It gives your expected result. Note you can rename the columns as you please.

SET NOCOUNT ON

declare @t table(
    cid int,
    OverviewText varchar(15),
    RAGStatusText varchar(15)
 )

insert into @t values (153, 'Cost',           'Green');
insert into @t values (154, 'Requirements',   'Yellow');
insert into @t values (155, 'Schedule',       'NULL');
insert into @t values (156, 'Technical',      'NULL');
insert into @t values (157, 'Testing',        'NULL');

Select
    [153|1] as [xyz],
    [153|2],
    [154|1],
    [154|2],
    [155|1],
    [155|2],
    [156|1],
    [156|2],
    [157|1],
    [157|2]

FROM (
select CAST(cid as varchar) + '|1' type, OverviewText  text from @t
Union
select CAST(cid as varchar) + '|2' type, RAGStatusText text from @t
) as SourceTable
Pivot(
    min(text)
    for type in ([153|1],[153|2], [154|1],[154|2], [155|1],[155|2], [156|1],[156|2], [157|1],[157|2])
) as PivotTable;


COMPRESS TABLESPACE 11g I have quite big datapump dump file and tried to load it into compressed tablespace that is created with "DEFAULT COMPRESS FOR OLTP" because of lack of free space. Anyway, when I look at "compressed" dba_segments and compare them with source segments they have the same size.
How does this compress feature work? I expected lower size of compressed tables but it seems doesn't work this way. P.S. Datapump job is not finished due to no free space left.

The datapump export has the complete DDL for every object you exported.  So while the default for the tablespace is to compress, this can (and will) be overridden by any explicit setting for a table.
Check the COMPRESSION column on xxx_TABLES for the schema(s) you are importing to see if you are truly getting any compression.
You will need to use "transform=segment_attributes" variants on import to ensure that you strip off the storage elements of your objects to that they pick up the default you set at tablespace level.


DB2 to require password each time I am using db2inst1 to connect to a database in DB2 which I have installed on my machine. Therefore, db2inst1 user does not require username/password authentication (borrows them from the OS). I would like to change that, and force every time a connection is initiated a username/password to be requested.

More specifically, this is how the authentication configuration looks like:

  db2 get dbm cfg|grep -i auth

 GSS Plugin for Local Authorization    (LOCAL_GSSPLUGIN) =
 Server Connection Authentication          (SRVCON_AUTH) = NOT_SPECIFIED
 Database manager authentication        (AUTHENTICATION) = CLIENT
 Alternate authentication           (ALTERNATE_AUTH_ENC) = NOT_SPECIFIED
 Cataloging allowed without authority   (CATALOG_NOAUTH) = NO
 Trusted client authentication          (TRUST_CLNTAUTH) = SERVER
 Bypass federated authentication            (FED_NOAUTH) = NO

  db2 connect to dbName

   Database Connection Information

   Database server        = DB2/LINUXX8664 10.1.0
   SQL authorization ID   = DB2INST1
   Local database alias   = DBNAME

  db2 connect to dbName user db2inst1 using password

   SQL1639N  The database server was unable to perform authentication because
   security-related database manager files on the server do not have the required
   operating system permissions.  SQLSTATE=08001

I have played with some authentication combinations for "AUTHENTICATION" and "TRUST_CLNTAUTH" without much luck.

I believe you want the following

AUTHENTICATION = CLIENT
TRUST_ALLCLNTS = NO
TRUST_CLNTAUTH = SERVER

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

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

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


PL/SQL - how to identify sequences used by tables? Is there a way to get a resultset from executing a query against an Oracle database that would return a listing of all the tables in the database and which sequence it is using (if any)?

You can query with this one:
SELECT
    TABLE_OWNER, TABLE_NAME,
    REFERENCED_OWNER AS SEQUENCE_OWNER, REFERENCED_NAME AS SEQUENCE_NAME
FROM ALL_DEPENDENCIES d
    JOIN ALL_TRIGGERS t ON TRIGGER_NAME = d.NAME AND t.OWNER = d.OWNER
WHERE REFERENCED_TYPE = 'SEQUENCE';

Note, a sequence might be used by more than one trigger. And a sequence can be used also by function, procedure, package, insert, update, etc, which is not covered by this query.
Also this query does not cover IDENTITY columns and sequences, they are hidden.
It depends what you mean by "identify sequences used by tables"?


How do I update and insert specific set of rows to an existing table using Merge in SQL Server 2008 R2? I have a table in the database that I need to update based on the records being passed to it. I am given a table-valued-parameter that has the same schema as the existing table (for all intensive purposes). The requirements are:

If the TVP (source) contains a record with existing ID in the existing table (target) update that record, if it is not marked as deleted in the existing table and its modified date is more recent.

Conditions: source.Id = target.Id AND target.IsDeleted = 0
         AND source.Modified > target.Modified

If the TVP contains a record for which the ID does not exist in the existing table, insert the record to the table.

I have the following set up so far.

EDITED: This setup works to give the desired end result. I, however, don't believe this is the best way of getting the intended result. There are two select statements that calculate the source table for the Merge statement. I would like to avoid having to filter out the source table in this manner.

My question is this: is there a way to achieve the same result as the query below but without having to use two select statements as is currently used in the Merge statement?

IF OBJECT_ID('tempdb..#target') IS NOT NULL DROP TABLE #target
IF OBJECT_ID('tempdb..#source') IS NOT NULL DROP TABLE #source

CREATE TABLE #target  ( Id int, Modified int, IsDeleted bit, NoteText varchar(500) )
INSERT #target VALUES (1, 1, 0, '')
INSERT #target VALUES (2, 1, 1, '')
INSERT #target VALUES (3, 3, 0, '')

CREATE TABLE #source ( Id int, Modified int, NoteText varchar(500) )
INSERT #source VALUES (1, 2, 'Modified')
INSERT #source VALUES (2, 2, 'Modified')
INSERT #source VALUES (3, 2, 'Modified')
INSERT #source VALUES (4, 2, 'Modified')

-- ** NEED HELP WITH OPTIMIZATION HERE **
merge #target as t
using (select * from #source s where s.id not in (select id from #target where IsDeleted = 1 OR Modified > s.Modified)) as source
on t.id=source.id
when matched then update set Modified=source.Modified, NoteText = source.NoteText
when not matched then insert values (id, Modified, 0 ,NoteText) ;

SELECT * FROM #target;

-- Expected records in #target after operations
-- 1,2,0,'Modified'  ** UPDATED **
-- 2,1,1,''          ** NOT UPDATED BECAUSE ISDELETED = 1 **
-- 3,3,0,''          ** NOT UPDATED BECAUSE TARGET.MODIFIED > SOURCE.MODIFIED **
-- 4,2,0,'Modified'  ** INSERTED **

drop table #target;
drop table #source

A simpler way to write the MERGE is as follows:

MERGE #target /* WITH (SERIALIZABLE) */ AS T
USING #source AS S
    ON S.Id = T.Id
WHEN MATCHED
    AND T.IsDeleted = 0
    AND S.Modified > T.Modified
    THEN UPDATE SET
        Modified = S.Modified,
        NoteText = S.NoteText
WHEN NOT MATCHED THEN
    INSERT (Id, Modified, IsDeleted, NoteText)
    VALUES (S.Id, S.Modified, 0, S.NoteText);

The SERIALIZABLE hint would be necessary to avoid a potential race condition. This consideration obviously does not apply to a local temporary table target, but it would be remiss of me not to mention.

In broad terms, it is almost always wrong to perform any sort of filtering in the ON clause (or equivalently, in a CTE). The ON clause should only be used to specify how rows between the source and target are related.

See Inserting, Updating, and Deleting Data by Using Merge for more details.

The source and target tables should also have a unique index or key. Moreover, for best performance SQL Server should be able to guarantee that the MERGE statement will affect each target row a maximum of once. Without that, the execution plan will have to detect multiple DML operations on the same row in order to raise a runtime error if it happens. This often requires a Sort, and always requires a Segment, Sequence Project, and Assert. None of these will make your command execute any faster :)


SQL Server 2017 crashes when backing up because filepath is wrong I was trying to restore my database and SQL Server kept crashing. I would get a message in SSMS that said there was a network transport error (the connection dropped bc the crash). I checked the logs and found nothing more than SQL Server closed unexpectedly. I would then have to go and restart the service.

I narrowed the issue down to the script that the GUI was trying to run. The problem is when it goes to take a tail log backup, the path to the backup files is wrong. It should be D:\mapbenefits\...

BACKUP LOG [mapbenefits]
TO  DISK = N'D:mapbenefits_LogBackup_2019-02-21_13-58-24.bak'
WITH NOFORMAT, NOINIT,  NAME = N'mapbenefits_LogBackup_2019-02-21_13-58-24',
    NOSKIP, NOREWIND, NOUNLOAD,  NORECOVERY ,  STATS = 5

I have two questions.

How do I fix this path? I tried going into the server settings and the backup path is D: with no slash. If I add the slash the gui removes it. This is SSMS v17.9.1. I can pick D:\mapbenefits\ and that works but I want D:\DATABASE\...
Is this a bug? Should SQL server crash just because a path is incorrectly typed? Once I fixed the file path it has no trouble. I can reproduce anytime just by mucking up the filepath.

If I run a query to check version I get CU13, but if I go into settings I see version 14.0.1000.169.

Looks like this is a bug and is reproducible so I posted it to here: https://feedback.azure.com/forums/908035-sql-server/suggestions/36920542-incorrect-filepath-with-backup-log-command-causes

I was able to reproduce this.

On 2016, if I put an invalid path like that, I get this message:

  Cannot open backup device 'D:mapbenefits_LogBackup_2019-02-21_13-58-24.bak'. Operating system error 3(The system cannot find the path specified.)

On 2017 CU 13 (14.0.3048.4), it results in the service crashing.  You've already mentioned that in the latest hotfix (14.0.3049.1), the service doesn't crash, but the session is killed.

I've confirmed the same exact behavior applies to RESTORE DATABASE as well - passing a path like "D:Backups" (with a missing backslash) or "D::\Backups" (extra colon) crashes the SQL Server instance (thanks Michael K Campbell for bringing this up).

If I put a "valid" path that doesn't exist, I get the correct behavior ("cannot find the path specified") in 2017.

This is a bug - in both CU 13 and the hotfix build.  Passing incorrect parameters to the BACKUP or RESTORE commands shouldn't crash the service or kill your session.  You can report it on the feedback site.

Note: the service-crashing version of this bug can be reproduced in a public preview version of SQL Server 2019 (CTP2.2 - thanks to Denis Rubashkin for pointing this out)

Looking at this in a debugger, it seems that path validation code is simply broken. It ends up causing a stack overflow by recursively calling into sqlmin!CheckFileStreamReserved after the normal (and quite extensive) checks on the supplied path fail to make sense of it. The stack overflow brings down the service on build 3048. The same thing happens on 3049 except a sequence of access violations while attempting to handle the stack overflow instead breaks the connection rather than stopping the whole server.

A fix for this bug was released in SQL Server 2017 CU 15:

FIX: SQL Server 2017 crashes because of stack overflow when you try to back up database master to disk

The issue is also resolved in SQL Server 2019 CTP 3.0.


Why is data integrity not possible when using denormalization? This tutorial says the following:

  For proponents of denormalization, the thinking is as follows:
  normalization creates more tables as you proceed towards higher normal
  forms, but more tables mean there are more joins to be made when data
  is retrieved, which in turn slows down your queries. For that reason,
  to improve the performance of certain queries, you can override the
  advantages to data integrity and return the data structure to a
  lower normal form.

Why is data integrity not possible when using denormalization? I mean wouldn't the DBMS make sure that all redundant copies of some piece of data gets updated automatically when that piece of data is updated?

In Oracle, for example, denormalization is implemented using materialized views, and when the original tables are modified, then the data in the materialized view will be modified automatically (you can choose that the data not be modified automatically also). And even if you don't use materialized views (if your DBMS doesn't support it for example) and implemented the denormalization manually, you can still write triggers yourself to keep the data consistent (so using denormalization does not necessarily mean that data integrity is not possible).

Data integrity is possible in both normalized and denormalized databases.  What is different in the two cases is the number of different ways an application program can write bad data to the database.

Let's take a simple case of bad data.  In the database, it says that employee with id 123 has last name "Brown".  But in the real world, employee 123 has last name "Browne".  This is bad data no matter how you look at it.  But let's go a  little deeper.

In one database, a normalized one, the last name is stored in precisely one place,  in the Employees table.  It's either right or it's wrong.  And, if it's wrong, it's up to someone who knows the right answer to fix the data in the database. This is part of data management, although not a very exciting part to those of us who work in automation.

In another database, a denormalized one, the last name of employee 123 is stored in two tables,  the Employees table and the Contacts table.  The contacts table is about phone numbers and addresses.  In the normalized database,  there is no contacts table.  It is a view instead.  Now, there are more possibilities.  The last name could be right in both places, or it could be wrong in both places.  But there is another failure mode.  It could be right in one place, but wrong in the other place.  Now the database is not only wrong, but self contradictory.  This is simply not possible in the normalized database, because the last name is stored only once.

This is an example of what is called harmful redundancy.  Second Normal Form through Fifth Normal Form are about eliminating cases of harmful redundancy, by imposing rules that cause one fact to be stored in one place.  This doesn't guarantee data integrity, but at least it obviates data inconsistency.  You still have to manage data to get it all right,  but now the DBMS is helping you manage data correctly.

Performance is a whole other story.  It may be the case that one of the databases outperforms the other for certain kinds of traffic.  It's even possible that one of the databases is more performant for updates but the other is more performant for queries.  But great performance and wrong answers may be catastrophic in some cases, and acceptable in other cases.

In the example I've given, the detection of wrong data is fairly trivial, even if it's somewhat tedious.  In the real world, there are examples where wrong data detection is well nigh impossible unless you get all the help you can from the DBMS.  Normalization can help in this regard.

Well, why don't you just makes sure that all the application code always does the right thing?  In the first place, the amount of application code grows dramatically, as the scope of the mission increases.  In the second place,  data errors can creep in by the use of interactive SQL,  maybe by the DBA.  Writing defensive application code is a good thing,  but for one reason or another, it might not get done.

Sorry this is so long,  but I needed to give you an idea about what's really going on here.  Data management is a never ending battle.  Finding and cleaning up wrong data is just part of that battle.  It's a dirty job, but somebody has to do it.  A good DBMS, coupled with normalization can help.


MySQL full text search ranking I found in this article the formula that MySQL use to ranking in FTS

w = (log(dtf)+1)/sumdtf * U/(1+0.0115*U) * log((N-nf)/nf)

Where

dtf     is the number of times the term appears in the document sumdtf
is the sum of (log(dtf)+1)'s for all terms in the same document U
is the number of Unique terms in the document N       is the total
number of documents nf      is the number of documents that contain
the term

But I wonder that what is 0.0115?

From your source:

  The normalization factor is the middle part of the formula. The idea
  of normalization is: if a document is shorter than average length then
  weight goes up, if it's average length then weight stays the same, if
  it's longer than average length then weight goes down. We're using a
  pivoted unique normalization factor. For the theory and justification,
  see the paper "Pivoted Document Length Normalization" by Amit Singhal
  and Chris Buckley and Mandar Mitra ACM SIGIR'96, 21-29, 1996:
  http://ir.iit.edu/~dagr/cs529/files/handouts/singhal96pivoted.pdf. The
  word "unique" here means that our measure of document length is based
  on the unique terms in the document. We chose 0.0115 as the pivot
  value, it's PIVOT_VAL in the MySQL source code header file
  myisam/ftdefs.h

So, it's a "best practice" pivot value.


Order table Database design I'm in need of some help with the database design of order table of a shopping cart for online food ordering system. I have a design below. Tables about which I'm Concerned  are order_details and order. Basically what is going to happen is user selects foods from restaurant menus and will place order, User details are stored in the user table (which is not shown in the image).

This is my first attempt, I was never good at Database. So please take it easy if you fill that the question is way too low for this site.

And my questions are:

Are the tables good enough?
Do I need to add any more fields?
And one more question I need to store multiple addresses of users(user can have multiple shipping address) how can I handle this?

http://i.stack.imgur.com/HCYBi.jpg

Note The item_price table in the image is not i use. Please ignore that table while answering. Thanks

And again ;) I recommend that too often these days.
The Data Model Resource Book, Volume 1.
Order management, address management - solved, problems explained, well documented. Complex but you can simplify - just reading it will really help you understand the problems at hand.