Is it more performant to use a JOIN or a when using LIMIT 1? I have a products table. I want to select all fields (of a given row) and the most profitable discount that the user can claim (depends on ordered quantity of the product).

When selecting only one value only, is it more performant to use a JOIN, like this:

SELECT p.*, qd.price AS qd_price

FROM products AS p

-- Find all applicable QDs
LEFT OUTER JOIN quantity_discounts as qd
    ON p.id=qd.product_id
    AND qd.min_amount <= ?
    AND (
        (qd.user_id IS NULL OR qd.user_id = 0 OR qd.user_id = ?)
        OR (qd.group_id IS NULL OR qd.group_id = 0 OR qd.group_id = ?)
    )

WHERE p.id=?

-- Get the lowest QD price
ORDER BY qd.price ASC
LIMIT 1

or a subquery, like this:

SELECT p.*,
(SELECT price FROM quantity_discounts
WHERE product_id=p.id
AND (
    (user_id=? or user_id=0 or user_id IS NULL)
    OR (group_id=? or group_id=0 or group_id IS NULL)
)
AND qd.min_amount <= ?
ORDER BY price LIMIT 1) AS qd_price
FROM products as p
WHERE p.id=?

The ORs are deadly for performance.  For starters, decide on 0 or NULL so you won't have to test for both.  (That won't actually help any since there would still be other ORs.)

I'm dubious about the formulation of the LEFT JOIN.  (BTW, OUTER is optional.)  Normally when using LEFT, the ON only says how the tables are related (p.id=qd.product_id); the WHERE provides the filtering.

I don't know which is better, but since you seem to be limiting to one id (via WHERE p.id=?), then the second formulation will look into quantity_discounts only once, and (hopefully) won't have too many rows to check.  That is, the OR may not be as bad as I started out with.

A slight help on quantity_discounts will be

INDEX(product_id, price)  -- in this order

Now I am worried.  One formulation has AND qd.min_amount <= ?, the other does not.


Autodetect ‘random_page_cost’ vs ‘seq_page_cost’ I read this article about PostgreSQL performance on SSD:

https://amplitude.engineering/how-a-single-postgresql-config-change-improved-slow-query-performance-by-50x-85593b8991b0

These two configurations seem to be important random_page_cost vs seq_page_cost

Since both parameters need to match the particular hardware I wonder if it is possible to automatically detect the matching values?

Update

I have these steps on my mind:

Script creates some dummy tables
Scripts inserts data to the tables
Script does some queries
Script shows matching values for random_page_cost and seq_page_cost
Human or an automated system takes these values and updates the config. This step is not part of the question.

Since both parameters need to match the particular hardware I wonder if it is possible to automatically detect the matching values?

It's certainly possible to set the parameters automatically, but no one has submitted a patch to do it.

You need to know the sequential and non-sequential read speeds of the drive. There are a ton of ways to get this, but you may just as well use Google because it likely doesn't matter that much. For instance, a quick google search for the sequential and non-sequential read performance of the Samsung SSD 840 Pro (256GB) shows this from AnandTech, with

Random Read 101.4/mbps
Sequential Read 510.7/mbps

That's roughly a ratio of 1:5, so

SET random_page_cost = 5;
SET seq_page_cost = 1;

Warning, random_page_cost takes into account the cache,

  Random access to mechanical disk storage is normally much more expensive than four times sequential access. However, a lower default is used (4.0) because the majority of random accesses to disk, such as indexed reads, are assumed to be in cache. The default value can be thought of as modeling random access as 40 times slower than sequential, while expecting 90% of random reads to be cached.

  If you believe a 90% cache rate is an incorrect assumption for your workload, you can increase random_page_cost to better reflect the true cost of random storage reads. Correspondingly, if your data is likely to be completely in cache, such as when the database is smaller than the total server memory, decreasing random_page_cost can be appropriate. Storage that has a low random read cost relative to sequential, e.g. solid-state drives, might also be better modeled with a lower value for random_page_cost.

I've shown my random_page_cost to be 5 times slower than sequential. It's still a wildcard how much of the random_page_cost is already cached. Alas, these values don't really matter much unless the Index scan and the Sequential were so close that you could reasonably pick the Sequential scan by accident. That's seldom the case. It's not uncommon for an Index to speed things up by thousands of times over.

For example, my cpu_index_tuple_cost is 0.005. AFAIK, that means scanning 1000 entries in the index is the same in the planner's eyes as going to the heap once to fetch a block.


Cloning a database to another collation I'm not sure if this is the best way to migrate data to another collation but I've done the following:

Scripted the source database and changed the collation in the script
to my required one
Ran the script and created the new database with the wanted
collation (along with all the tables,triggers,indexes etc.
I tried to 'Export Data' from the SQL Server management studio but I
got an error related to code page differences.
Since Export/Import Data didn't work, I resorted to using bcp to
mass export all the tables in the source database to txt files.
After that I began importing the rows using bcp but I saw some
errors related to truncation and invalid characters.

What's the best/proper way to migrate the data from a database with a different code page & collation after creating the clone from the scripted source?

You have several options:

In SSIS, in the SSIS ToolBox there is a component called Transfer
Database task.
use the method provided here
use the method provided here

I would probable go with 3), but that is a matter of taste.


PostgreSQL group roles This is not a "group by" question. I am struggling with permissions in postgres because I apparently don't understand how group roles work. Searching for this on SO has proven difficult because I get buried in questions about group by, which isn't the problem.

I have been using postgres to manage a series of research projects. These databases are not connected to any web front-ends. I import all of the data from CSV. For the past few months, I have been the only user. This has worked really well. I am the db owner and nobody else needed to care. Recently, I built a complex database containing data from several sources. Because of the number of tables involved, each data source has its own schema and I have created a series of convenience views to make it easier to work with these various data sets. These schemas should be read only to all users, except for myself.

This project requires several other people to have access to this database. Each user has a username / password and an individual schema, where they can work without cluttering up the public schema. I want to be able to control access to the schemas such that the schemas holding the original data are "select only" to the other analysts. However, I want the all other schemas in the database to more or less function like the public schema in terms of access. These schemas exist for logical structure, not to control access. I want everyone to be able to read/write/create/drop/etc. in these other schemas that don't hold the original data.

Unfortunately, postgres roles are rather tricky or I've been rather slow to understand. Setting this up has been tricky as new tables are created by different users. I keep having to re-run the grant access commands manually as we add new tables.

The alter default privileges seems to imply that I can do this using group roles, but when I have tried to do so, other users were unable to access new tables. This seems to imply that I can set up group roles that will allow me to manage access, but I haven't succeed thus far:

You can change default privileges only for objects that will be created by yourself or by roles that you are a member of.

I'm just looking for some help to understand how to best utilize group roles to control access to schemas/tables/views in Postgres. I don't want to have to manually grant access every time I add a new table, or worse, manually grant access every time I add a new user. I would prefer to be able to add a new user to a larger group, that controls the access.

Based on comments given, I have tried to use the following syntax. In this example, there are two users, achoens (me) and mrubin (a co-worker). I can log in as both users. As achoens, I run the following:

create schema foo;
alter default privileges in schema foo grant select on all tables to public;
select 1 into foo.test;

I log off as achoens and log in as mrubin. I then run

select * from foo.test;

Which returns a permissions error. In this instance, Postgres 9.1 is running on a windows server but I tried it on my home server running Ubuntu and got the same results. I like postgres but this makes it much harder to use in a collaborative environment without just making everyone into an administrator, which I would prefer to not do.

I also edited the above syntax to reference against a user group called analyst, which both achoens and mrubin are members of. No difference. User mrubin was unable to read the silly little one row table.

It sounds like what you probably want is to:

Create a role to own all the common tables and schema, or just use your own if you really will always be the only one with full control of the main tables.
Create another role you intend to give only read-only access to the shared tables and schemas. GRANT that role rights using GRANT SELECT ON ALL TABLES IN SCHEMA [x] for each shared schema. You may also want to ALTER DEFAULT PRIVILEGES to make sure this role has read rights on any new tables created in these schemas too.
Now GRANT each user membership of the read-only access role with INHERIT.
For the private schemas, create a schema the same as the user's username with CREATE SCHEMA [username] AUTHORIZATION [username] or the older style where you create the schema then ALTER SCHEMA ... OWNER TO.

See the postgresql manual for the detailed syntax of all of the above commands. Start with user management, part of the broader database administration topic that includes grant management etc. The PostgreSQL manual is detailed, comprehensive and readable: reading it is strongly recommended.


Most efficient way of updating table from another - Postgres I need to populate a foreign key column based on the contents of another table, and both methods I've tried are prohibitively slow.

I have the below tables (some columns omitted):

              Table "public.interface_events"
           Column           |  Type   |     Modifiers
----------------------------+---------+--------------------
 device_id                  | integer | not null
 interface_id               | integer |

Foreign-key constraints:
    "interface_events_device_id_fkey" FOREIGN KEY (device_id) REFERENCES device(id) ON UPDATE CASCADE ON DELETE RESTRICT
    "interface_events_interface_id_fkey" FOREIGN KEY (interface_id) REFERENCES interface(id) ON UPDATE CASCADE ON DELETE RESTRICT

                                       Table "public.device"
        Column        |         Type          |                      Modifiers
----------------------+-----------------------+-----------------------------------------------------
 id                   | integer               | not null default nextval('device_id_seq'::regclass)
 interface_id         | integer               | not null

Indexes:
    "device_pkey" PRIMARY KEY, btree (id)
    "device_interface_id_idx" btree (interface_id)

Foreign-key constraints:
    "device_interface_id_fkey" FOREIGN KEY (interface_id) REFERENCES interface(id) ON UPDATE CASCADE ON DELETE RESTRICT

Column interface_id in table interface_events needs updating from table device, so currently I have:

UPDATE interface_events SET interface_id = device.interface_id
FROM device
WHERE device.id = interface_events.device_id

I should point out that the relationship between device and interface_events is one to many, so there may be thousands of events per device. Is the UPDATE FROM method suitable in this situation? I'd earlier tried:

UPDATE interface_events SET interface_id = (SELECT device.interface_id FROM device WHERE device.id = interface_events.device_id);

But that took forever also.

There are 30 million rows in table interface_events, 32,000 in table device.

The update running currently (using the FROM syntax) has been active for 21 hours, although this is on a low spec test machine (2 x 3G cores, 3G RAM, spindle disk - it's disk I/O that's killing it, constant 99%).

It occures to me as I write I should have applied the foreign key constaint after the update, since I know it's integrity will be ok thanks to the column I'm copying from already being constrained, so I guess this question is more about the best query to use?

The fastest way will probably be to create a new table based on a join of the existing tables, create indexes and foreign keys constraints, and then drop the old "interface_events" and rename the new table into place.  Views and foreign keys into this table can complicate this.

create interface_events_temp as
    select interface_events.*, device.interface_id from interface_events
        join device on (device.id = interface_events.device_id)

But the interface_events.* notation relies on interface_id not already existing in interface_events.  Since it already exists, you would have to list out all columns in interface_events except for interface_id.


Can I write portable SQL statements? I am wondering if it is possible to write SQL statements that are 100% interoperable with most or all databases including:

MariaDB/MySQL/Percona
Postgres
Microsoft SQL
Oracle
SQLite

(Eg, can I just follow a specific SQL standard? Eg, is there something similar to POSIX compliance standards for SQL?)

If so, are there any linting tools available that I can use in a git post-receive hook to reject SQL usage that doesn't follow such a standard or non-compliant SQL code without having to try to commit the code on all DBs?

No, not for any significant and practical amount of code anyway.  You can try to hew to standards (e.g., use COALESCE rather than ISNULL) but there are too many differences, big and small.  Off the top of my head:

SQL Server supports double quotes and square brackets for identifiers; MySQL uses backticks.
SQL Server supports TOP, most other databases use LIMIT.
PostgreSQL only relatively recently implemented stored procedures, though in practice you could use functions instead.
MySQL is case-sensitive for table names (but not field names) when running on UNIX, but not when running on Windows.  SQL Server is case-insensitive either way (except when it is).
CTEs and windowed functions are not available on all systems, and are not always implemented the same.
SQL Server does not require a command delimiter (except when it does), but MySQL and Oracle do.  MySQL also requires using an alternate delimiters when defining stored procedures; MS SQL does not support any such thing.
Security is almost always different for different vendors.
Error handling is always different.
All of the above can change, and may have since I last worked with those systems.

Many people have written software with the goal of letting one write RDBMS-agnostic queries.  Most of these experiments failed, and a few broke out of the lab, lurching across the landscape spreading destruction in their wake.  But even the best won't have the performance of code written with the target system in mind.


Is DBCA in Oracle for a single database or the entire system? I ask this because I was going to create a simple phone list database for learning.  But when I used dbca, I was asked did I want to install the sample databases, HR, etc.  It also had the default locations for all archive_log files, passwords for sys, sysman...I thought dbca was just to create a single database.

What is dbca for?

DBCA will be reside on Oracle home. You can create as many number of databases in system using dbca. It will create default locations based on your unique SID (and dbunique name).


INDEX size as bytes - PostgreSQL 9.2 Is there any way to get this query to return the index size as BYTES?

SELECT
    relid::regclass AS table,
    indexrelid::regclass AS index,
    pg_size_pretty(pg_relation_size(indexrelid::regclass)) AS index_size,
    idx_tup_read,
    idx_tup_fetch,
    idx_scan
FROM
    pg_stat_user_indexes
    JOIN pg_index USING (indexrelid)
WHERE
    idx_scan = 0
    AND indisunique IS FALSE;

Currently it shows BYTES/KB/MB and GB...

Just remove pg_size_pretty from the query:

SELECT
    relid::regclass AS table,
    indexrelid::regclass AS index,
    pg_relation_size(indexrelid::regclass) AS index_size,


Does PostgreSQL always sequentially scan pages in the same order? Yet another question from my discovery of the great new EXPLAIN options in PostgreSQL. This one focuses on the BUFFERS option.
Here is EXPLAIN:
EXPLAIN (ANALYZE, BUFFERS) SELECT event_time FROM ui_events_v2  WHERE page ~ 'foo' LIMIT 1;
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..1539.68 rows=1 width=8) (actual time=0.858..0.858 rows=1 loops=1)
   Buffers: shared read=10
   I/O Timings: read=0.735
   ->  Seq Scan on ui_events_v2  (cost=0.00..3313394.58 rows=2152 width=8) (actual time=0.857..0.857 rows=1 loops=1)
         Filter: (page ~ 'foo'::text)
         Rows Removed by Filter: 112
         Buffers: shared read=10
         I/O Timings: read=0.735
 Planning Time: 6.455 ms
 Execution Time: 0.877 ms

It is quite fast - and this query is very slow on a cold start. This is a 30M row table, and seven rows have page containing substring foo. The first row matching this query would seem to show up 604k pages in, which would be ~20% of the ~3M pages mentioned in pg_class:
SELECT min(ctid) FROM ui_events_v2 WHERE page ~ 'foo';
     min
-------------
 (604435,10)

My assumption was that even if every page of this table were in the PostgreSQL or operating system cache, it still needs to walk some linear list of pages for a sequential scan. The section Buffers: shared read=10, and the 0.877 ms execution time suggests to me that it's somehow "picking up where it left off" and starting at pages that are far from the beginning. I thought it moved through page IDs and peeked into a cache trying for each page as it moved, but does it perhaps start instead from the cache itself? If that were the case, how does it discover the non-cached pages?
I know that rows aren't guaranteed to be found in any particular order, but I figured that within the same query strategy, the path followed would be relatively similar.

If multiple processes are trying to seq scan the same table, it will try to line them all up so that all read the same pages at about the same time, to maximize cache hits.  It does this by having the late comers start in the middle (where ever the existing ones happen to be at), and wrap around the end to finish the table at the page before where it started.  A side effect of this is that if one seq scan stops early, the next one will start up on the same page where the last one stopped.  So if the entire LIMIT can be satisfied by rows from one page, a scan like yours will just scan the same page over and over.
You can turn this optimization off with set synchronize_seqscans=off, then they all start from the first block in the table.


Which ports need to be opened in order to use Named Pipes I have a SQL Server with both TCP\IP and Named Pipes protocols enabled and configured.

From the server itself I can connect to the database using either TCP or Named Pipes, by specifying the connection as np:servername\instance

However from another server I can only connect using TCP\IP using servername\instancename. Adding the np: prefix just causes a connection timeout failure.

Do I need to open any specific ports in order to use Named Pipes? This is a named instance running on a dynamic port. The dynamic port is opened in the firewall.

The reason I'm looking into this is because I'm experiencing intermittent issues with an ODBC connection - timeouts, connection dropouts etc. From what I've read these issues have in the past been resolved by enabling Named Pipes - which I've done, but then by default my connections are using TCP, and by force, I can't get it to connect from this server.

We had the same issue with legacy applications built around 1998-2007 when we ported SQL Server to a VLAN--for security purposes. Depending on which server we connected to we'd get spurious results--one would work and the other would not...because of a tighter VLAN configuration.

Our solution was to "bag" named pipes and to force TCP--as shown below. The SQL server port is denoted by XXXXX. This forced our legacy apps to use TCP no matter what. So far, it's fixed all our issues.

tcp:servername\instance,XXXXX


Oracle 12cR1 - RMAN-11003, ORA-00354, ORA-00353 - Corrupt Redo Log Oracle 12cR1, disaster database.

ORA-00283: recovery session canceled due to errors
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 8 change 80357235718 time
11/01/2020 01:57:01
ORA-00334: archived log: '/FRA/.../o1_mf_2_61264_hsvvjoyb_.arc'

Database in mount mode and can't recover.
How can I resolve this?
Best regards,

That is not an online redo log. That is an archived redo log of thread 2 sequence 61264. It can be clearly seen from the error and the name of the file. The archivelog on disk was damaged. Restore it from backup and continue recovery using that.
If you do not have a backup of that archivelog, just roll forward the standby database with an incremental backup.
As a last resort, you can rebuild the standby database from the primary.


best way to store tags I'm a university student building a web application for a project with three collegues , I took the database portion.

Our site is basically a link sharing site and supports tags, what is an efficient way to store tags in Mysql database ? I thought of these ways :

1- table for tags : the relation between link and tag is many-to-many so I must break it with an intermediate table, I can't think of an SQL statements to fetch links with specific tags example : the user might search for links tagged with (php , mysql ) or (hardware-issues , gigabyte).

Also even if I did , it needs join , we're making the site global, that means the database will be very very huge some day , I don't think we can bare a join.

2-putting tags as an array in links table : I can put an array of strings, this means only one select statement(better than join I think) but manipulating the array will be hard, the link's poster might edit the tags on his/her link, I can figure out a way to deal with this but I'm not sure if this is the most efficient way , of course it's not normalized and the database will grow faster but I think on a server , speed is more important than size.

So is there a better way ?

Why do you think that you will  not be able to bare a join? In fact if I understand correctly, your other idea is to put a list of tags into a varchar column (am I correct?) and search links by searching text in that column and that will definitely be much slower since you will have to full-scan the links table for every search.

Joins are normal, every-day operations in relational databases and are not something that you should avoid.

So say you have 3 tables,

links(link_id, url)
tags(tag_id, tag)
tags_links(tag_id, link_id)

You want to find all links with tags in (php , mysql)?

select l.link_id, l.url
from links l
    inner join link_tags lt on (l.link_id = lt.link_id)
    inner join tags t on (lt.tag_id = t.tag_id)
where t.tag in ('php', 'mysql')

links.link_id and tags.tag_id must be primary keys. In tag_links you can put primary key on combination (tag_id, link_id) - the supporting index will make searches by tag_id fast. You can an should also index the link_id column in that table, so that you can find all tags for a given link fast. Also, put an unique index on tags.tag column so that you can find tag_id from tag fast (and tags should be unique anyway, so you're enforcing an important business rule with such a constraint and index).

Try that out, fill some imaginary test data into such a schema, say, 1M links, 10k tags and 10M tags_links, and test the performance. I think you'll be surprised with the results.


Top CPU consuming MySQL processes I am trying to find out the Query which is consuming high CPU, but can't do it so.

This command  top -H gives me the high CPU consuming thread PID, but i cannot match them with my processlist output.

Is there a way to do this?.

You can see which queries are slow by enabling the slow query log (SET GLOBAL slow_query_log=1; and setting a suitable value for the long query time variable to set a threshold for what you consider to be "long". (E.g. maybe SET GLOBAL long_query_time=3; - the unit is seconds). However, queries may be slow for other reasons than high CPU utilisation, e.g. due to high I/O utilisation. Still, this can be a starting point for at least identifying poor queries.

The slow log is by default written to a file in the data dir called '${HOSTNAME}-slow.log'. You can then use a tool like mysqldumpslow to analyse this file, e.g.: mysqldumpslow -s t /var/lib/mysql/localhost-slow.log. It will extract each slow query and order them from slowest to least slow. Or you can use pt-query-digest to do the same thing

You can then enable query profiling for your session (SET profiling = 1;), execute each of the candidate queries you have identified in the slow query log, and do:

SHOW PROFILES;

This gives you a list of the recent queries and their query IDs. Use this ID to find the CPU utilisation for each query with:

SHOW PROFILE CPU FOR QUERY <query id>;

This will show the various stages of the query execution, and the duration and CPU utilisation (user and system) for each of them.

Note that this isn't 100% accurate - the documentation page for SHOW PROFILE notes that:

  Profiling is only partially functional on some architectures. For
  values that depend on the getrusage() system call, NULL is returned on
  systems such as Windows that do not support the call. In addition,
  profiling is per process and not per thread. This means that activity
  on threads within the server other than your own may affect the timing
  information that you see.

Note also that the page says SHOW PROFILE is deprecated as of MySQL 5.6.7, and that the performance_schema is meant to replace it; see Query Profiling Using Performance Schema. However, there doesn't seem to be a way to extract CPU utilisation info from it, so you may just have to use SHOW PROFILE CPU ...


Looking for a utility to move data between databases (prod to dev) SQL Server 2012 I am looking for an easy way to copy table data from a production SQL Server 2012 machine to a development machine (same version, level). We currently just do an export from within SSMS, but we have a couple hundred tables so the process is a bit arduous.

Have you seen any third party utilities that will do this?

I haven't tried SSIS for a few years, but it has historically been problematic and more time consuming to setup than it was worth. I don't need to do anything except copy the data into the dev tables (not an append, but a drop all the rows and insert the data). All the schema already matches.

Yes -- I have searched for the answer already... that's why I'm posting :)

Let me know and thanks.

---------UPDATE
So how about this? Why not just link the servers; truncate the dev table and do a select into from prod to dev? I can script this out for the majority of the tables and not have to worry about a gui.

Downside?

Since you mentioned you want to copy table data only I guess SSIS would be perfect. I have been using it and it works just fine for me. It also depends on level of competency you hold with SSIS. You can go for backup restore but it would also restore tables whoes data you dont want to change. If you want complete refresh then of course no better option than backup restore.

You can go for import export wixard as well and its more easy than SSIS because it almost does all data conversion(if required) for you.
You should use OLEDB provider always when data transfer is to be done within SQL server.There are tools but personally I found SSIS more relaiable than tools.

Have a look at below link

http://www.mssqltips.com/sqlservertip/2684/importing-sql-server-data-using-ssis--which-option-is-fastest/

Best practice for SSIS packages

http://blogs.msdn.com/b/sqlcat/archive/2013/09/16/top-10-sql-server-integration-services-best-practices.aspx


Unable to see list of tables in Oracle SQL Developer I am using SQL Developer and now I can SELECT but viewing those tables in the list is not available. Is there some configuration I need to do in order to see the tables? User has select all tables privileges.

I am expanding the tree under my own user, not under the user who owns the tables.

For where you are looking, you can only see the tables that exist in the schema that belongs to the user you supplied in your connection details.

If you want to see 'the tables' - you need to expand the 'Other Users' node, and explore the users there, and go to their respective tables list.

If you can query these objects in a worksheet w/o adding a SCHEMA. prefix to the FROM clause, it's because there is a SYNONYM that allows you to do so.

You can configure SQLDev to show these SYNONYMS as tables in your tree.

I talk about how to do this here.


Very light queries are in slow-query-log ! Why? I try to optimize my websites, because MySQL falled in timeout.
I've simplied queries and added indexes.
Server works fine, but in log stil some queries.

Amazing ! : the only queries on the slow-query-log are very simple and light queries. I don't understand !...
Can you help me ?

My site is on Private MySql servor, OVH.
Logs extract :

# Time: 170109 18:17:04
# User@Host: doctsfgl[doctsfgl] @  [10.0.83.198]  Id: 837382
# Query_time: 1.185552  Lock_time: 0.000117 Rows_sent: 0  Rows_examined: 1
SET timestamp=1483982224;
delete from gl_visiteurs
                where IP like '68.180.230.33';
# Time: 170109 18:17:05
# User@Host: doctsfgl[doctsfgl] @  [10.0.83.198]  Id: 837382
# Query_time: 1.440233  Lock_time: 0.000151 Rows_sent: 0  Rows_examined: 0
SET timestamp=1483982225;
INSERT INTO gl_visiteurs (IP, user_id)
                VALUES ('68.180.230.33',0);
# Time: 170109 18:17:20
# User@Host: doctsfgl[doctsfgl] @  [10.0.83.92]  Id: 837412
# Query_time: 2.540715  Lock_time: 0.000124 Rows_sent: 0  Rows_examined: 1
SET timestamp=1483982240;
delete from gl_visiteurs
                where IP like '109.220.253.15';
# Time: 170109 18:40:36
# User@Host: doctsfgl[doctsfgl] @  [10.0.83.198]  Id: 839694
# Query_time: 1.566650  Lock_time: 0.000095 Rows_sent: 0  Rows_examined: 1
SET timestamp=1483983636;
delete from gl_visiteurs
                where IP like '66.249.64.218';

These queries, when executed in PhpMyAdmin, run in very short laps, about 0.01 s !!!
All columns concerned are indexed.
Table contain less than 100 records, and 3 columns !...

An idea ?
Many thanks
(sorry for my english ; I'm french)

The create table statement:

CREATE TABLE IF NOT EXISTS gl_visiteurs
( IP varchar(15) NOT NULL DEFAULT '',
  start timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  logue int(11) NOT NULL DEFAULT '0',
  user_id int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Indices:

ALTER TABLE gl_visiteurs
 ADD PRIMARY KEY (IP,user_id),
 ADD KEY user_id (user_id),
 ADD KEY start (start),
 ADD KEY IP (IP);

I noticed you are searching for an IP address

You could add an additional column with a numeric value for the IP

First, create the column to hold the numeric value of the IP and index it

ALTER TABLE gl_visiteurs ADD COLUMN IPVALUE INT UNSIGNED DEFAULT 0;

Next populate that new column and index it

UPDATE gl_visiteurs SET IPVALUE = INET_ATON(IP);
ALTER TABLE gl_visiteurs ADD INDEX (IPVALUE);

From there, you can do deletes via numeric lookups like this

delete from gl_visiteurs
where IPVALUE = INET_ATON('68.180.230.33');

and do inserts like this

INSERT INTO gl_visiteurs (IP, IPVALUE, user_id)
VALUES ('68.180.230.33',INET_ATON('68.180.230.33'),0);

UPDATE 2017-01-09 17:57 EST

Based on your comment, you can drop the IP column and keep the IPVALUE column

Then your inserts would be

INSERT INTO gl_visiteurs (IPVALUE, user_id)
VALUES (INET_ATON('68.180.230.33'),0);

but to see the IP your selects must be something like

SELECT INET_NTOA(IPVALUE) as IP,... FROM gl_visiteurs


Restoring a MySQL database My Ubuntu 16.04 server was recently hacked and my host allowed me to recover the file system via FTP, closing all other ports thus disabling me from logging in and stopping any services. I have the files on a local windows filesystem, which were copied while the server and services were still running.
I have a new Ubuntu 18.04 server, where I have installed MySQL but not WordPress (which is powered by MySQL); most of all, I want my WordPress blog back up on my new server, then I can write a post about the importance of CORRECTLY backing up a database. Can anyone tell me if this is possible and what I should do next?

It is not safe or reliable to ftp the files in your MySQL datadir while the MySQL server is running. MySQL keeps part of the data and metadata in memory, and writes them to the files on disk asynchronously. If you try to copy the files, this takes a non-zero amount of time, so you are likely to copy the files while they're being updated by MySQL Server. So you get the early part of the file before the update, and the latter part of the file after the update. The result is potentially an irreconcilable corrupt file.
A simple Wordpress blog is likely to have low traffic, so it's not like it's in a constant state of update. But the risk is still there.
You should work with your hosting provider to give you temporary ssh access. Or ask them to stop MySQL Server on your behalf.
And yes, make backups of your MySQL database regularly. I use jetpack.com to run automatic backups for me.


Multiple Result Tabs in Workbench I have several queries in the editor, each terminating with a semi-colon (I am teaching myself SQL and so in the editor is a mix of queries and notes like below):

SELECT *
FROM orders
WHERE order_date >= '2019-01-01';

-- AND operator

SELECT *
FROM customers
WHERE birth_date > '1990-01-01' AND points > 1000;

-- OR operator

SELECT *
FROM customers
WHERE birth_date > '1990-01-01'OR points > 1000 AND state = 'VA';

-- NOT operator

-- Exercise: FROM order_items table get the items
    -- for order #6
    -- where total price is greater than 30

SELECT *
FROM order_items
WHERE order_id = 6
AND quantity * unit_price >= 30;

When I first began using MYSQL, I would run a query and only the last SELECT statement would run and the results would show in the result tab. The ACTION OUTPUT would show the time-stamp which clearly indicated only the last SELECT statement in the editor was run.

Now, all queries ending with a semi-colon are running simultaneously and each appears on its own tab and in the ACTION OUTPUT section on the bottom (i.e., "orders43", "customers44", "customers45" and so on.)

The only way I can get the most recent result is by highlighting the specific SELECT statement and running the query. However, I want to know why the results aren't just "overriding" each other and the most recent SELECT statement being the results displayed in the same result tab...

At this rate, I'll have hundreds of result tabs for each query ending with a semi-colon, and this is clearly not what I want nor was it how the workbench was working for me up until recently.

I am not sure why this is occurring, or how to stop it.

Any help would be greatly appreciated.

Thanks!

You have actually 3 possibilities to run the queries:

Run everything that's in the editor. This is like running an external script. Workbench opens a result tab for each statement that returns a result set. Use the flash icon or cmd/ctrl+shift+enter to execute (cmd on macOS, otherwise ctrl).
Select a single statement or multiple statements and press the same shortcut as in point 1. This will only run the selected code. The editor is clever enough to find statement boundaries automatically to run complete statements.
Place your cursor in the statement you want to run and press cmd/ctrl+enter. This will only run this single statement (and hence only show a single result set).


Minimum permission to execute sp_changedbowner SP Is db_owner permission sufficient to execute sp_changedbowner?

I'm looking to change the database owner of a RDS SQL instance. I'm the owner of the DB, and still couldn't execute sp_changedbowner as master user.

I would like to know what the minimum permissions are to execute sp_changedbowner or ALTER AUTHORIZATION commands.

Community wiki answer:

From ALTER AUTHORIZATION (Transact-SQL):

  Requires TAKE OWNERSHIP permission on the entity. If the new owner is not the user that is executing this statement, also requires either, 1) IMPERSONATE permission on the new owner if it is a user or login; or 2) if the new owner is a role, membership in the role, or ALTER permission on the role; or 3) if the new owner is an application role, ALTER permission on the application role.

There may be additional considerations in the AWS RDS world.

Note that sp_changedbowner is deprecated in favour of ALTER AUTHORIZATION. The sp_changedbowner documentation says:

  Requires TAKE OWNERSHIP permission on the database. If the new owner has a corresponding user in the database, requires IMPERSONATE permission on the login, otherwise requires CONTROL SERVER permission on the server.


Best solution to fixing database design with GUID as primary key I am after some confirmation of this idea to fix a badly performing database or a better suggestion if any one has one. Always open to better suggestions.

I have a very large database (20+ million records growing by about 1/2 million per day) which are using GUID as PK.

An oversight on my part but the PK is clustered on SQL server and is causing performance issues.

The reason for a guid - this database is partially synchronised with 150 other databases so the PK needed to be unique. The synchronisation is not managed by SQL Server, rather there is a custom process built which keeps the data in sync for the requirements of the system - all based on that GUID.

Each of the 150 remote databases don't store the full data as stored in the central SQL Database. they only store a subset of the data they actually require, and the data the require is not unique to them (10 out of the 150 database may have some of the same records from other sites databases for example - they share). Also - the data is actually generated at the remote sites - not at the central point - hence the need for the GUIDs.

The central database is used not only for keeping everything in sync, but queries from 3000+ users will be executed against that very large fragmented database. Already this is a big problem in initial testing.

Fortunately we are not live yet - so I can make changes and take things offline if required which is at least something.

The performance of the remote databases is not a problem - the data subsets are pretty small and the database usually never gets above 1GB in size in total. The records are fed back to the main system quite regularly and removed from the smaller BD's when no longer required.

The performance of the central DB which is the keeper of all records is woeful - due to a clustered GUID as a primary key for that many records. The index fragmentation is off the charts.

So - my thoughts to fix the performance issue is to Create a new column - Unsigned BIGINT IDENTITY(1,1) and then change the Clustered PK of the table BIGINT column.

I would create a Unique Non Clustered index on the GUID field which was the primary key.

The smaller remote 150 databases don't need to know about the new PK on the Central SQL Server database - its purely going to be used for organising the data in the database and stop the bad performance and fragmentation.

Would this work and improve the performance of the central SQL database and prevent future index fragmentation hell (to a degree of course)? or have I missed something very important here which is going to jump up and bite me and cause even more grief?

You certainly need NOT to cluster on the GUID.  If you have something which would allow you to uniquely identify records other than that GUID, I'd suggest you look at building an unique index on that other field & making that index clustered.  If not, you are free to cluster on other fields, even using nonunique indexes.  The approach there would be to cluster however best facilitates splitting your data up and querying - so, if you've got a "region" field, or something, that might be a candidate for your clustering scheme.

The problem with changing to a BIGINT would be additions to the data from other databases & integrating their database into the central store.  If this is not a consideration - and will never be a consideration - then, yes, the BIGINT would solve the index rebalancing issue nicely.

Behind the scenes, if you don't specify a clustered index, SQL Server does much the same thing: it creates a row ID field & maps all other indexes into that.  So, by doing it yourself, you're solving it just as SQL would solve it.


SQL Server full text search query takes way too long to execute I've got query snippet as follows:

SELECT *
FROM CONTAINSTABLE(dbo.Analysis, *, 'FORMSOF(FREETEXT, "historic year-on-year exchange rates forecast year-on-year exchange rates")');

I'm running it on SQL Server 2014.

However it executes forever, so I just give up and cancel it.

If I remove words year-on-year it works just fine.
FTCatalog was rebuilt - didn't help.

This has been driving me crazy for a while now.
I dont't have a lot of experience with FTS searches, but this just doesn't make any sense.

Please advice how to make it work or where I should start looking at.

In place of your query, try this:

SELECT * FROM dbo.Analysis
where FREETEXT(dbo.Analysis, 'historic year-on-year exchange rates forecast
yearon-year exchange rates');

or this:

SELECT * FROM  dbo.Analysis
where CONTAINS(dbo.Analysis, "historic year-on-year exchange rates forecast
year-on-year exchange rates");

Also, the hyphens may be causing problems.  Try:

  year on year

or

  yearonyear

in place of the hyphens.

Lastly, did you build and enable a stop list?


Joining 4 tables and 2 counts I have four tables.

UserMaster
----------
UserKey - PK
UserName
UserID - UNIQUE

UserLogTransaction
------------------
UserID - FK:UserMaster.UserID
LoginTime
LogoutTime

CallCenter
----------
CallKey - PK
PickedUserKey - FK:UserMaster.UserKey
PickedTime
StartTime
EndTime

JobCardMaster
-------------
JCKey
RecordedUserKey - FK:UserMaster.UserKey
CreatedTime
CallKey - FK:CallCenter.CallKey

This is the scenario: Each user logs in and attends calls. Job cards are created for some of the calls not for all calls.

Problem: I want to get a report which has the following information

UserName | LoginTime | LogoutTime | NoOfCallsAnswered^ | NoOfJobCardsCreated^ | AverageCallDuration^

^ These columns are calculated per session per user. A session is a row from the UserLogTransaction table.

I tried with several attempts and they tend to take around 1 minute to run. So, I think there is some problem with my approachs.

SELECT UM.UserID, ULT.LogInTime, ULT.LogOutTime, COUNT(CC.PickedUserKey)
FROM dbo.UserMaster UM
JOIN dbo.UserLogTransactions ULT
 ON ULT.UserID = UM.UserID
JOIN dbo.CallCenter CC
 ON CC.PickedUserKey = UM.UserKey
JOIN dbo.JobCardMaster JC
 ON JC.RecordedUserKey = UM.UserKey
WHERE (CC.PickedTime > ULT.LogInTime
 AND CC.PickedTime < ULT.LogOutTime)
 OR (JC.RecordedUserKey > ULT.LogInTime
 AND JC.RecordedUserKey < ULT.LogOutTime)
GROUP BY CC.PickedUserKey, UM.UserID, ULT.LogInTime, ULT.LogOutTime

I am stuck on this query for several days. Appreciate any helps or hints.

Can you try using subquery? I can better design it if I have the table and sample data created on my DB server. But below is just a conceptual model which you can try on your server first.

SELECT
 UM.UserID, ULT.LogInTime, ULT.LogoutTime,
 (SELECT COUNT(CC.PickedUserKey)
    FROM dbo.CallCenter CC
    WHERE
     CC.PickedUserKey = (SELECT UserKey FROM dbo.UserMaster WHERE UserID = UM.UserID) AND
     CC.PickedTime > ULT.LogInTime AND
     CC.PickedTime < LOGOUTTIME) NoOfCallsAttended
FROM dbo.UserMaster UM
JOIN dbo.UserLogTransactions ULT
 ON ULT.UserID = UM.UserID

Subsequently you can create subqueries for other columns as well.
NoOfJobCardsCreated, AverageCallDuration


Dealing with measure groups of different granularity Let's say I have a simple data warehouse with the following fact tables:

FactPurchase: one row per property purchased, with purchase amount, value, etc.
FactMonthlyStatus: one row per property, per month; with rent paid, total maintenance expenses, etc.

I have some dimensions that apply to FactPurchase (such as property, property type, location) and some dimensions that apply to FactMonthlyStatus (such as occupancy status, condition, and also the month).

I'd like to be able to have a cube set up where I can see facts from both of these tables regardless of the dimensions I select. For example, if I am looking at total rent paid this month (by putting rent paid as a measure in the browser grid and month in the filter), I'd like to be able to see property type as a dimension as well. That is, so I can see rent paid by property type for month X.

In SQL, this would be easy. I join FactPurchase to FactMonthlyStatus on the property dimension key, and then I put month in the where clause, and I group by property type and sum up rent paid. However, I'm not sure how to properly model this in SSAS. It seems that I need to relate the property type dimension to both facts (as measure groups), but that dimension only directly maps to FactPurchase. There is no property type dimension on FactMonthlyStatus. I've tried many to many in dimension usage, and that appears to work at first, but if I had a dimension to my browser (e.g., condition) from the FactMonthlyStatus table, I end up getting, as expected, a many-to-many situation, with all of the used combinations of property type and condition, and not the combinations valid as of the month specified. Referenced also doesn't work because there's no intermediate dimension between my condition dimension and property type dimension. There's an intermediate fact, though.

If you want to see measures from two different fact tables side by side but different granularity, it's possible but not perfect. For example, the measure for purchase amount in FactPurchase has no monthly equivalent.

To do this, it helps to have as many related dimensions between both fact tables. So if you have a dimension such as property type that is related to FactPurchase, I would make that a dimension on FactMonthlyStatus as well. This can be done by adding these to the actual FactMonthlyStatus table and figuring them out in your ETL process or by creating a view that sits on top of FactMonthlyStatus that does a join to lookup these dimensions as they are loaded into your cube. However, you are still going to have dimensions such as month that are at a lower level of granularity than FactPurchase.

The main thing to figure out is how your measures in both fact tables relate to each other. Are you attempting to keep a running total for rent paid and see how it relates to what you paid for the property? If that's the case, it might make sense to create a cube based off a single view that combined the FactPurchase with an aggregate of FactMonthlyStatus to see how they compare.


Unexpected behavior of foreign keys I want to use Sqlite3 with Python and created queries as follows:
import sqlite3
from sqlite3 import Error

db = sqlite3.connect("temp.sqlite")
cursor = db.cursor()

cursor.execute("PRAGMA foreign_keys = ON") #Turns foreign_key support on

cursor.execute("""DROP TABLE IF EXISTS tx""")
cursor.execute("""CREATE TABLE tx (
                    tx_index BIGINT NOT NULL PRIMARY KEY,
                    hash CHAR(64) NOT NULL,
                    rbf INT NOT NULL,
                    tx_in_value BIGINT NOT NULL,
                    tx_out_value BIGINT NOT NULL
                    );""")

cursor.execute("""INSERT INTO tx VALUES ('1','2','3','11','11')""")
cursor.execute("""INSERT INTO tx VALUES ('2','2','3','11','11')""")

cursor.execute("""DROP TABLE IF EXISTS txin""")
cursor.execute("""CREATE TABLE txin (
                    addr BIGINT NOT NULL,
                    value BIGINT NOT NULL,
                    tx_index BIGINT NOT NULL,
                    FOREIGN KEY (tx_index) REFERENCES tx (tx_index)
                    );""")

list_tuple = []
list_tuple.append(('11','12','1'))
list_tuple.append(('12','12','2'))
# Uncommenting the line below will throw an error
#list_tuple.append(('12','12','3'))

cursor.executemany("""INSERT INTO txin VALUES (?,?,?)""", list_tuple)

cursor.execute("""SELECT * FROM tx""")
cursor.execute("""SELECT * FROM txin""")

db.commit()
db.close()

The above queries, when tested without Python here, works fine but in the Python, execution gives following error:
cursor.execute("INSERT INTO txin VALUES (?,?,?)", insert_trx_in)
sqlite3.IntegrityError: FOREIGN KEY constraint failed
It is simpler version of the problem. In real problem, I insert using executemany Any idea?

I believe the error was due to the mismatch in the foreign keys that cursor.executemany was trying to insert. If the foreign key to be inserted in TABLE2 is not present in TABLE1 (primary key holder), it throws an error.

It means that if TABLE 1 has 2 values in the primary key column then while inserting values in TABLE2, the values for the foreign key must exactly match to one of the primary key values in the TABLE1.


Table restructuring for efficiency? I am moving data from Access to SQL Server. One of the tables I'm working with has some columns like this:
ID | Date | Sensor1 | Sensor2 | Sensor3 | Sensor4 | Sensor5 | Sensor6 | Sensor7 | Sensor 8 | Sensor9

Certain Facts:
At any point atleast 3  of these columns would be filled.
More Sensors could be added in the future
This table in entirety has 120 columns (above is just a portion)
ID is TinyInt
Sensor data is Decimal(4,2)

When I looked at the table structure and the Nulls due to the facts above, I thought of changing it to a structure like this:

ID |Date | Sensor_NO | Data

My question is:
Considering I'm breaking down a huge table. This sort of table restructuring would make it much more difficult to join the individual tables because I will have to transpose the table first and then join the various tables based on ID.
Would my proposed structure going to improve efficiency to a point where it is worth the work of creating JOINS later? Would you consider such a transformation?

Only you can answer if the the change is worth the effort.

Yes restructuring would be marginally more efficient. The main advantage being that new sensors can be added in the future without altering your table structure. The downside is that it's harder to enforce business rules and logic.

The advantage of your current design is that if the sensor types are consistent per column you could have different data types, int, float, bit, varchar etc, whereas if you put all of your sensor values in a single column you'll need to use the lower common denominator (most likely a varchar).

Given that you're talking about sensors, I expect you will be recording over time, so you'd probably need a timestamp, and decide on what else need to be stored with the value. Quality codes? non-detects? what about the sensor type?


What options exist for copying data between servers on a regular basis? My apologies if this has already been answered.  I've searched SO and of course here on DBA, and am surprised to find no close matches.  Specifically, I'm looking for a solution which survives schema differences.

I finally got a development server with which to shadow a production data warehouse server.  I'll be testing schema and sproc changes here, eventually publishing such changes to the prod server.  To do so, I'll need reasonably current data, but it need not be updated in real time.  My plan is to copy end-state data from the main server to the dev server as the final step in overnight ETL.  What's the best way to achieve this?

Parameters

All automatic copying will be from prod to dev.  Any copying from dev to prod will be by hand, and generally will just be DDL.

Because the dev server's schema is expected to change independently of the production server, I don't want the copy process to fail when the target schema differs (it's OK if the specific tables in flux can't be kept in synch, of course).  Otherwise I would just DROP DATABASE and restore last night's backup.

Keeping the schema, SPs, views, and UDFs in synch is not necessary, or even desirable.  They should only change when I specifically change them.

Records can be changed retroactively in most tables, so incremental updates will probably not be practical.

The volume of data is 14 GB for the most critical tables, plus about 200 GB of less important data which can be updated weekly.

My goal is to finish the process within two hours.  The servers are co-located, and should have high throughput.  Copying (INSERT INTO..SELECT * FROM ProdServer..) a single table with 600 MB of data and 300 of indices took 7.5 minutes; not great.  Of some concern, another table with 11 GB of data and 8 GB of indices did not finish in 130 minutes, when I cancelled it.  I'll test this again without indices.

Taking the prod database offline is acceptable if it can be kept brief - no more than 30 minutes.  The dev database can be offline for hours if necessary.

If I can temporarily exclude a specific table, keeping it static for a few days while testing, that would be valuable.

Options

1: Replication

I could create a daily snapshot after the end of ETL, and publish it to the dev server.  I've not used replication before, but this seems to be the kind of scenario for which it is meant.  Time for me to learn a new facet of the technology?

2: Bulk Copy and BULK INSERT

I could write a script which iterates through every table in the prod database and spits out the content to a uniquely-named file.  On the dev side, I'd loop through these files and TRUNCATE/BULK INSERT into the target table, with a TRY..CATCH block in case the schema has changed.  I'm not sure if this would perform acceptably, but it would be fairly simple to implement.

3: INSERT from Linked Server

I can run a TRUNCATE/INSERT..SELECT for each table, probably pulling from the dev side.  This would be simple, and should be fast, especially if one drops and re-created the indices.  To cope with schema changes, one could identify the intersection of the field lists for each pair of tables and only attempt to copy those fields; this would help if many fields were NULLable.

Are there other options?  Is there an easier way that I'm overlooking?  Are there any gotchas that you've hit when working on similar projects?

This question describes the export side, but the desired output was CSV, so the options are limited (and BCP works great, as answered).

This question talks about quickly copying a whole database, including schema.  It specifically excludes replication.

This question discusses bi-directional synchronization, for which replication is suggested.

I'd consider backup/restore of a mix of full and differential backups from prod to dev.
Then synch between this restored copy to your actual dev databases

SQL Server 2008 R2+ supports backup compression in Standard Edition (In SQL Server 2008 it was Enterprise Edition only) which makes this easier.

Reasons:

You test your backup integrity and restore capability
You can reset your dev database easily if some code monkey bollixes it
You can compare "before" and "after" DDL, performance etc
You can snapshot the restored database as needed to roll this back if you make changes

I've used this before and will use it again for these reasons

I suggested this in my answer to The smallest backup possible ... with SQL Server


SQL Pull replication stops on unintialized subscription For a new project we want to test pull replication in mssql to replicate database (transactional replication) to multiple db server. I setup the pull replication by wizard in MSSMS and but the replication stucks on uninitialized subscription in the replication monitor.
Steps I have done:

Created a publication on publisher by wizard (Local Publications > New Publication...).
Created a subscription by wizard (Local Subscriptions > New Subscriptions...)

Publisher and distributor are on the same host. Subscriber uses SQLExpress. Authentication is done by sql authentication. SQL user used for authentication is dbo on Distributor DB.
What else is needed to initialize the subscription?

If  you want to Pull from a SQL Agent, you'll have to schedule and execute the replication job with a custom program or script.  Here's an old sample of doing it in PowerShell:
$asm = [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
$asm = [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Replication")
$asm = [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.RMO")

function Do-Replication
{

<# .SYNOPSIS Initiate Merge Replication Sync
   .DESCRIPTION This Function kicks of a Transactional Replication Synchronization
   .EXAMPLE Do-Replication -subscriber "DBROWNE0" "DBROWNE0" "inventory" "subscriberTest" "replTest" $false 1 4;
   .PARAMETER subscriber The SQL Instance Name name of the Publication, EG localhost
   .PARAMETER spublisher The SQL Instance Name name of the Publisher, eg MyPublisher
   .PARAMETER publication The name of the publication
   .PARAMETER subscriptionDatabase The name of the Subscriber Database
   .PARAMETER publicationDatabase The name of the publisher database
   .PARAMETER forceReInit $true to force a ReInitialization of the subscription, $false otherwise
   .PARAMETER verboseLevel Logging verbosity level
   .PARAMETER retries Number of times to retry the sync in case of a failure #>

  param([string][Parameter(Mandatory = $true,position = 0)] $subscriber,
    [string][Parameter(Mandatory = $true,position = 1)] $publisher,
    [string][Parameter(Mandatory = $true,position = 2)] $publication,
    [string][Parameter(Mandatory = $true,position = 3)] $subscriptionDatabase,
    [string][Parameter(Mandatory = $true,position = 4)] $publicationDatabase,
    [Boolean][Parameter(Mandatory = $true,position = 5)] $forceReInit,
    [int32][Parameter(Mandatory = $true,position = 6)] $verboseLevel,
    [int32][Parameter(Mandatory = $true,position = 7)] $retries
  )

  "Subscriber: $subscriber";
  "Publisher: $publisher";
  "Publication: $publication";
  "Publication Database: $publicationDatabase";
  "Subscription Database: $subscriptionDatabase";
  "ForceReInit: $forceReinit";
  "VerboseLevel: $verboseLevel";
  "Retries: $retries";

  for ($counter = 1; $counter -le $retries; $counter++)
  {
    "Subscriber $subscriber";
    $serverConnection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection $subscriber;
    try
    {
      $serverConnection.Connect();
      $transPullSubscription = New-Object Microsoft.SqlServer.Replication.TransPullSubscription;
      $transPullSubscription.ConnectionContext = $serverConnection;
      $transPullSubscription.DatabaseName = $subscriptionDatabase;
      $transPullSubscription.PublisherName = $publisher;
      $transPullSubscription.PublicationDBName = $publicationDatabase;
      $transPullSubscription.PublicationName = $publication;
      if ($true -ne $transPullSubscription.LoadProperties())
      {
        throw New-Object System.ApplicationException "A subscription to [$publication] does not exist on [$subscriber]"
      }
      if ($null -eq $transPullSubscription.PublisherSecurity)
      {
        throw New-Object System.ApplicationException "There is insufficent metadata to synchronize the subscription. Recreate the subscription with the agent job or supply the required agent properties at run time.";
      }
      $transPullSubscription.SynchronizationAgent.Output = "c:\temp\ReplicationLog.txt";
      $transPullSubscription.SynchronizationAgent.OutputVerboseLevel = $verboseLevel;

      if ($forceReInit -eq $true)
      {
        $transPullSubscription.Reinitialize();
      }

      $transPullSubscription.SynchronizationAgent.Synchronize();
      "Sync Complete";
      return;
    }
    catch [Exception]
    {
      if ($counter -lt $retries)
      {
        $_.Exception.Message + ": " + $_.Exception.InnerException;
        "Retry $counter";
        continue;
      }
      else
      {
        return $_.Exception.Message + ": " + $_.Exception.InnerException }
    }
  }
}


Access Query Design View: Show results in two columns from one table Lets say I have the follow Tables:

Outfits:
ID | Top Color ID | Bottom Color ID
1,2,1
2,3,4

Colors:
ID | Name
1, Red
2, Blue
3, Green
4, Orange

I'd like to have a query in design view to have the following result

Outfit ID | Top Color Name | Bottom Color Name
1, Blue, Red
2, Green, Orange

In design view, I can link "Color ID" to "Top Color ID" and "Bottom Color ID" but I don't know how to specify in the GUI to create "Top Color Name" and "Bottom Color Name"

Put this in the SQL View of the query design and you can then see the GUI required to build what you want.

select ID
, (select colorname from colors where id = outfits.topcolorid) as TopColorName
,(select colorname from colors where id = outfits.bottomcolorId) as BottomColorName
from outfits;


Any way of optimizing joins between 2 databases with different collations? I'm creating a query for a report where we must do a cross database join. Unfortunately the fields we have to join on have two separate collations - "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS". The join is very slow between these and is really impacting the speed of the report - is there any ways of optimizing this? Is changing the collation to match wise and/or difficult?

The join portion of the query is below

INNER JOIN EDE.dbo.DeliveryDetail D
              ON D.DeliveryNoteNo COLLATE database_default = Consignments.CustomerReference COLLATE database_default

If you can add columns to tables, you can try persisted computed columns, and index those. For example:

ALTER TABLE dbo.DeliveryDetail ADD DeliveryNoteNo_Collated COLLATE whatever PERSISTED;

CREATE INDEX IX_DeliveryDateNo_Collated ON dbo.DeliveryDetail(DeliveryNoteNo_Collated);

You're basically pre-baking converted versions of your fields once on insert (and kept updated for you automatically), and indexing those.

There are two really big drawbacks. First, these are new columns in your table: they'll take up more space, the indexes will take up more space, and if your apps are doing inserts without explicitly listing their column names, their inserts will fail.

Second, if the app's connection options aren't set correctly, your delete/update/insert operations can actually fail. NUMERIC_ROUNDABORT has to be set OFF, and ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, and QUOTED_IDENTIFIER must all be set to ON. You'll definitely want to test for this in development first, and for bonus points, check sys.dm_exec_sessions in production to check everybody's session settings.

For more info:

Books Online page on computed columns
MSSQLTips demo on using computed columns


Reset Running Total based on another column Am trying to calculate running total. But it should reset when the cummulative sum greater than another column value

create table #reset_runn_total
(
id int identity(1,1),
val int,
reset_val int,
grp int
)

insert into #reset_runn_total
values
(1,10,1),
(8,12,1),(6,14,1),(5,10,1),(6,13,1),(3,11,1),(9,8,1),(10,12,1)

SELECT Row_number()OVER(partition BY grp ORDER BY id)AS rn,*
INTO   #test
FROM   #reset_runn_total

Index details:

CREATE UNIQUE CLUSTERED INDEX ix_load_reset_runn_total
  ON #test(rn, grp)

sample data

+----+-----+-----------+-----+
| id | val | reset_val | Grp |
+----+-----+-----------+-----+
|  1 |   1 |        10 | 1   |
|  2 |   8 |        12 | 1   |
|  3 |   6 |        14 | 1   |
|  4 |   5 |        10 | 1   |
|  5 |   6 |        13 | 1   |
|  6 |   3 |        11 | 1   |
|  7 |   9 |         8 | 1   |
|  8 |  10 |        12 | 1   |
+----+-----+-----------+-----+

Expected result

+----+-----+-----------------+-------------+
| id | val |    reset_val    | Running_tot |
+----+-----+-----------------+-------------+
|  1 |   1 | 10              |       1     |
|  2 |   8 | 12              |       9     |  --1+8
|  3 |   6 | 14              |       15    |  --1+8+6 -- greater than reset val
|  4 |   5 | 10              |       5     |  --reset
|  5 |   6 | 13              |       11    |  --5+6
|  6 |   3 | 11              |       14    |  --5+6+3 -- greater than reset val
|  7 |   9 | 8               |       9     |  --reset -- greater than reset val
|  8 |  10 | 12              |      10     |  --reset
+----+-----+-----------------+-------------+

Query:

I got the result using Recursive CTE. Original question is here https://stackoverflow.com/questions/42085404/reset-running-total-based-on-another-column

;WITH cte
     AS (SELECT rn,id,
                val,
                reset_val,
                grp,
                val                   AS running_total,
                Iif (val > reset_val, 1, 0) AS flag
         FROM   #test
         WHERE  rn = 1
         UNION ALL
         SELECT r.*,
                Iif(c.flag = 1, r.val, c.running_total + r.val),
                Iif(Iif(c.flag = 1, r.val, c.running_total + r.val) > r.reset_val, 1, 0)
         FROM   cte c
                JOIN #test r
                  ON r.grp = c.grp
                     AND r.rn = c.rn + 1)
SELECT *
FROM   cte

Is there any better alternative in T-SQL without using CLR.?

I've looked at similar problems and have never been able to find a window function solution that does a single pass over the data. I don't think it's possible. Window functions need to be able to be applied to all of the values in a column. That makes reset calculations like this very difficult, because one reset changes the value for all of the following values.

One way to think about the problem is that you can get the end result you want if you calculate a basic running total as long as you can subtract the running total from the correct previous row. For example, in your sample data the value for id 4 is the running total of row 4 - the running total of row 3. The value for id 6 is the running total of row 6 - the running total of row 3 because a reset hasn't happened yet. The value for id 7 is the running total of row 7 - the running total of row 6 and so on.

I would approach this with T-SQL in a loop. I got a little carried away and think I have a full solution. For 3 million rows and 500 groups the code finished in 24 seconds on my desktop. I'm testing with SQL Server 2016 Developer edition with 6 vCPU. I'm taking advantage of parallel inserts and parallel execution in general so you may need to change the code if you're on an older version or have DOP limitations.

Below the code that I used to generate the data. The ranges on VAL and RESET_VAL should be similar to your sample data.

drop table if exists reset_runn_total;

create table reset_runn_total
(
id int identity(1,1),
val int,
reset_val int,
grp int
);

DECLARE
@group_num INT,
@row_num INT;
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;

    SET @group_num = 1;
    WHILE @group_num <= 50000
    BEGIN
        SET @row_num = 1;
        WHILE @row_num <= 60
        BEGIN
            INSERT INTO reset_runn_total WITH (TABLOCK)
            SELECT 1 + ABS(CHECKSUM(NewId())) % 10, 8 + ABS(CHECKSUM(NewId())) % 8, @group_num;

            SET @row_num = @row_num + 1;
        END;
        SET @group_num = @group_num + 1;
    END;
    COMMIT TRANSACTION;
END;

The algorithm is as follows:

1) Start by inserting all rows with a standard running total into a temp table.

2) In a loop:

2a) For each group, calculate the first row with a running total above the reset_value remaining in the table and store the id, the running total that was too large, and the previous running total that was too large in a temp table.

2b) Delete rows from the first temp table into a results temp table that have an ID less than or equal to the ID in the second temp table. Use the other columns to adjust the running total as needed.

3) After the delete no longer processes rows run an additional DELETE OUTPUT into the results table. This is for rows at the end of the group that never exceed the reset value.

I'll go through one implementation of the above algorithm in T-SQL step by step.

Start by creating a few temp tables. #initial_results holds the original data with the standard running total, #group_bookkeeping is updated each loop to figure out which rows can be moved, and #final_results contains the results with the running total adjusted for resets.

CREATE TABLE #initial_results (
id int,
val int,
reset_val int,
grp int,
initial_running_total int
);

CREATE TABLE #group_bookkeeping (
grp int,
max_id_to_move int,
running_total_to_subtract_this_loop int,
running_total_to_subtract_next_loop int,
grp_done bit,
PRIMARY KEY (grp)
);

CREATE TABLE #final_results (
id int,
val int,
reset_val int,
grp int,
running_total int
);

INSERT INTO #initial_results WITH (TABLOCK)
SELECT ID, VAL, RESET_VAL, GRP, SUM(VAL) OVER (PARTITION BY GRP ORDER BY ID) RUNNING_TOTAL
FROM reset_runn_total;

CREATE CLUSTERED INDEX i1 ON #initial_results (grp, id);

INSERT INTO #group_bookkeeping WITH (TABLOCK)
SELECT DISTINCT GRP, 0, 0, 0, 0
FROM reset_runn_total;

I create the clustered index on the temp table after so the insert and the index build can be done in parallel. Made a big difference on my machine but may not on yours. Creating an index on the source table didn't seem to help but that could help on your machine.

The code below runs in the loop and updates the bookkeeping table. For each group, we need to get the find the maximum ID which should be moved into the results table. We need the running total from that row so we can subtract it from the initial running total. The grp_done column is set to 1 when there isn't any more work to do for a grp.

WITH UPD_CTE AS (
        SELECT
        #grp_bookkeeping.GRP
        , MIN(CASE WHEN initial_running_total - #group_bookkeeping.running_total_to_subtract_next_loop > RESET_VAL THEN ID ELSE NULL END) max_id_to_update
        , MIN(#group_bookkeeping.running_total_to_subtract_next_loop) running_total_to_subtract_this_loop
        , MIN(CASE WHEN initial_running_total - #group_bookkeeping.running_total_to_subtract_next_loop > RESET_VAL THEN initial_running_total ELSE NULL END) additional_value_next_loop
        , CASE WHEN MIN(CASE WHEN initial_running_total - #group_bookkeeping.running_total_to_subtract_next_loop > RESET_VAL THEN ID ELSE NULL END) IS NULL THEN 1 ELSE 0 END grp_done
        FROM #group_bookkeeping
        INNER JOIN #initial_results IR ON #group_bookkeeping.grp = ir.grp
        WHERE #group_bookkeeping.grp_done = 0
        GROUP BY #group_bookkeeping.GRP
    )
    UPDATE #group_bookkeeping
    SET #group_bookkeeping.max_id_to_move = uv.max_id_to_update
    , #group_bookkeeping.running_total_to_subtract_this_loop = uv.running_total_to_subtract_this_loop
    , #group_bookkeeping.running_total_to_subtract_next_loop = uv.additional_value_next_loop
    , #group_bookkeeping.grp_done = uv.grp_done
    FROM UPD_CTE uv
    WHERE uv.GRP = #group_bookkeeping.grp
OPTION (LOOP JOIN);

Really not a fan of the LOOP JOIN hint in general, but this is a simple query and it was the quickest way to get what I wanted. To really optimize for response time I wanted parallel nested loop joins instead of DOP 1 merge joins.

The code below runs in the loop and moves data from the initial table into the final results table. Notice the adjustment to the initial running total.

DELETE ir
OUTPUT DELETED.id,
    DELETED.VAL,
    DELETED.RESET_VAL,
    DELETED.GRP ,
    DELETED.initial_running_total - tb.running_total_to_subtract_this_loop
INTO #final_results
FROM #initial_results ir
INNER JOIN #group_bookkeeping tb ON ir.GRP = tb.GRP AND ir.ID <= tb.max_id_to_move
WHERE tb.grp_done = 0;

For your convenience below is the full code:

DECLARE @RC INT;
BEGIN
SET NOCOUNT ON;

CREATE TABLE #initial_results (
id int,
val int,
reset_val int,
grp int,
initial_running_total int
);

CREATE TABLE #group_bookkeeping (
grp int,
max_id_to_move int,
running_total_to_subtract_this_loop int,
running_total_to_subtract_next_loop int,
grp_done bit,
PRIMARY KEY (grp)
);

CREATE TABLE #final_results (
id int,
val int,
reset_val int,
grp int,
running_total int
);

INSERT INTO #initial_results WITH (TABLOCK)
SELECT ID, VAL, RESET_VAL, GRP, SUM(VAL) OVER (PARTITION BY GRP ORDER BY ID) RUNNING_TOTAL
FROM reset_runn_total;

CREATE CLUSTERED INDEX i1 ON #initial_results (grp, id);

INSERT INTO #group_bookkeeping WITH (TABLOCK)
SELECT DISTINCT GRP, 0, 0, 0, 0
FROM reset_runn_total;

SET @RC = 1;
WHILE @RC > 0
BEGIN
    WITH UPD_CTE AS (
        SELECT
        #group_bookkeeping.GRP
        , MIN(CASE WHEN initial_running_total - #group_bookkeeping.running_total_to_subtract_next_loop > RESET_VAL THEN ID ELSE NULL END) max_id_to_move
        , MIN(#group_bookkeeping.running_total_to_subtract_next_loop) running_total_to_subtract_this_loop
        , MIN(CASE WHEN initial_running_total - #group_bookkeeping.running_total_to_subtract_next_loop > RESET_VAL THEN initial_running_total ELSE NULL END) additional_value_next_loop
        , CASE WHEN MIN(CASE WHEN initial_running_total - #group_bookkeeping.running_total_to_subtract_next_loop > RESET_VAL THEN ID ELSE NULL END) IS NULL THEN 1 ELSE 0 END grp_done
        FROM #group_bookkeeping
        CROSS APPLY (SELECT ID, RESET_VAL, initial_running_total FROM #initial_results ir WHERE #group_bookkeeping.grp = ir.grp ) ir
        WHERE #group_bookkeeping.grp_done = 0
        GROUP BY #group_bookkeeping.GRP
    )
    UPDATE #group_bookkeeping
    SET #group_bookkeeping.max_id_to_move = uv.max_id_to_move
    , #group_bookkeeping.running_total_to_subtract_this_loop = uv.running_total_to_subtract_this_loop
    , #group_bookkeeping.running_total_to_subtract_next_loop = uv.additional_value_next_loop
    , #group_bookkeeping.grp_done = uv.grp_done
    FROM UPD_CTE uv
    WHERE uv.GRP = #group_bookkeeping.grp
    OPTION (LOOP JOIN);

    DELETE ir
    OUTPUT DELETED.id,
        DELETED.VAL,
        DELETED.RESET_VAL,
        DELETED.GRP ,
        DELETED.initial_running_total - tb.running_total_to_subtract_this_loop
    INTO #final_results
    FROM #initial_results ir
    INNER JOIN #group_bookkeeping tb ON ir.GRP = tb.GRP AND ir.ID <= tb.max_id_to_move
    WHERE tb.grp_done = 0;

    SET @RC = @@ROWCOUNT;
END;

DELETE ir
OUTPUT DELETED.id,
    DELETED.VAL,
    DELETED.RESET_VAL,
    DELETED.GRP ,
    DELETED.initial_running_total - tb.running_total_to_subtract_this_loop
    INTO #final_results
FROM #initial_results ir
INNER JOIN #group_bookkeeping tb ON ir.GRP = tb.GRP;

CREATE CLUSTERED INDEX f1 ON #final_results (grp, id);

/* -- do something with the data
SELECT *
FROM #final_results
ORDER BY grp, id;
*/

DROP TABLE #final_results;
DROP TABLE #initial_results;
DROP TABLE #group_bookkeeping;

END;


Where does my.cnf get stored in Linux? I am new to linux, and i just installed MySQL-server-5.1.59-1.glibc23.i386.rpm in my linux machine.

I wanted to know where my.cnf file gets stored..?
Will my.cnf gets created with the installation or not..?

I am using Fedora core 13

Edited: Still i am not able to find this file.
When i asked my admin, he is telling we need to copy my-large.cnf and place in /etc folder.. is it so...?

Most likely /etc/my.cnf or /etc/mysql/my.cnf.  Also check /home/username/my.cnf and /home/username/.mysql/my.cnf

If no luck there you can run

find / -type f -name my.cnf

That will scan you're entire disk where non priviledged access is allowed (which is fine; it should find it, i'm assuming your distro came with it and you're able to connect or you wouldn't be asking this much; but it will tie your disk up)


Using Ola Hallengren's full backup but database option is for simple? Each database on the server is set for simple recovery. I'm using Ola's script with  'full' in the script.

I tried looking on Google, but it seems backup and recovery are almost synonymous. Is there a difference? What am I actually backing up Full or simple?

A full backup can be taken from a database in any recovery model (full, simple, or bulk-logged). It is an unfortunate conflict in terms that is somewhat overloaded. You could consider this type of backup to be a single, point-in-time, snapshot of a database that can be restored on its own in entirety.

The recovery model just dictates how logging works and whether log backups are possible.

In simple recovery model, you can take full backups and differential backups, but not log backups. This means you cannot restore to a point-in-time other than the time the any given full backup was taken (or that full + latest subsequent diff). It also means your log space is re-used because, once a transaction is committed, it no longer needs to be in the log in order to be backed up.
In full recovery model, you can take full and differential backups, but you can also take log backups, which allow you to restore to any point in time covered by any log backup taken since the previous full or previous full + previous diff. In this case log space isn't re-used until they are backed up, and most people find this out the hard way after the logs have filled a disk.
In bulk logged recovery model, the situation is essentially the same as full, except that portions of the log are "blocked out" - meaning you can't restore to a point in time that exists in any window where bulk operations that were minimally logged took place.

To summarize:

The choice between full and simple recovery model is impacted by your need to support point-in-time recovery (and is a choice that has nothing to do with whether you use Ola's solution to manage backups).
The choice between full and log backups in Ola's solution is clearer - you must use full if you want any backups at all, and you can additionally specify diff if you want, and log if you have databases in full recovery model.

There is a ton about recovery model and backup/recovery strategies online, I just wanted to clarify the difference between the two uses of the term "full" here.


Is there a way to run parameterized update statements using only DB2 commandline and bash? [DB2 LUW 10.5] I need to update a few hundred thousand rows in a DB2 table. Individual update statements will take longer to execute compared to precompiled approach. I cannot use perl or any such scripting tools with ODBC/JDBC/CLI and PreparedStatement.executeUpdate etc. due to certain limitations.

Is there a way to accomplish this using the db2 commandline tool and procedural SQL? My tool set is restricted to bash, SQL and DB2 Command line interface.

If you are on 10.1 or later you can use INGEST. It is almost as fast as load, more feature rich and has less impact on a running system. First, you need to enable it by creating control tables (if you haven't already):

db2 'call sysproc.sysinstallobjects ( 'INGEST' ,'C' , null, null )';

Now you can ingest the data like:

db2 "ingest from file tab1.del
 format delimited (
     $colb ...,
     $colc ...
 ) update tableA set colA = null
   where (colb, colc) = ($colb, $colc)"

Here is an developerworks article:

https://www.ibm.com/developerworks/data/library/techarticle/dm-1304ingestcmd/index.html

and here is the documentation:

https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0057198.html


Visualization of MySQL/PostgreSQL table structure as a graph/UML Is there any program, source code or API that will take a MySQL or PostgreSQL schema and
spit out a UML or some other graph visualizing of it?

A useful little protip - Visio is quite widely available within corporate I.T. suites and Visio Pro or higher can be used to reverse engineer and document a database.

Visio professional has a database reverse engineering tool that will read a database schema and create E-R models from it.  Both PostgreSQL and MySQL support ODBC INFORMATION_SCHEMA tables so the ODBC driver for the Visio DB modeller will work with them.  Start by creating a database diagram, and then look for 'Reverse Engineer' under the 'Database' menu.

As a bonus, if the physical DB has missing foreign key constraints you can add them back into the model for documentation purposes.  For a more complex schema you can also create several diagrams showing different subsystems.  I find myself using this to document systems at client sites on a semi-regular basis and it's often the only tool available.


Can I limit the time a table lock is being held server side? I have a SQL Azure server with some stored procedures with TABLOCKX hints that I call from C# code on a Windows Azure web role VM.

I'm kinda paranoid about those hints - what if there's some network protocol failure and the transaction doesn't end or something like that and a lock is held forever and all invokations of that stored procedure block on the statement with the TABLOCKX hint and all my stuff comes to a halt.

Is it somehow possible to tell SQL Server that the lock should not be held for more than say one minute so that it is unconditionally released after that time?

You can explicitly specify using :

SET LOCK_TIMEOUT timeout_period

  timeout_period

  Is the number of milliseconds that will pass before Microsoft SQL Server returns a locking error. A value of -1 (default) indicates no time-out period (that is, wait forever).
  When a wait for a lock exceeds the time-out value, an error is returned. A value of 0 means to not wait at all and return a message as soon as a lock is encountered.

  At the beginning of a connection, this setting has a value of -1. After it is changed, the new setting stays in effect for the remainder of the connection.

  The setting of SET LOCK_TIMEOUT is set at execute or run time and not at parse time.

  The READPAST locking hint provides an alternative to this SET option.

  CREATE DATABASE, ALTER DATABASE, and DROP DATABASE statements do not honor the SET LOCK_TIMEOUT setting.

Refer to : Support for SET statements in Azure

UPDATE:

  Is it somehow possible to tell SQL Server that the lock should not be held for more than say one minute so that it is unconditionally released after that time?

No. As I mentioned in the comments section.

THe database wont be able to maintain its ACID properties.

Either the query fails and returns an error or the lock is held by SQL Server  until the transaction is completed and then released.


MS SQL Server 2012 R2 SP4 MS SQL 2012 R2 SP3 is out of support.  We were having issues with DB Cluster which is 2012 R2 SP3 and Microsoft said it’s not supported version. We need to go for SP4 to have support.
I suppose it’s not just MS SQL version, Service Pack is also associated.
Can you correct me here?

...I suppose it’s not just MS SQL version, Service Pack is also
associated.

Yes. You can read the lifecycle policy on service packs here.

When a new service pack is released, Microsoft provides either 12 or
24 months of support for the previous service pack, varying according
to the product family (for example, Windows, Office, Servers, or
Developer tools).

As a side note, if the product is out of support then the service pack is also out of support, regardless of this policy.

When support for a product ends, support of all the service packs for
that product also ends. The product's lifecycle supersedes the service
pack policy.

For your case, the SQL Server 2012 SP3 lifecycle policy can be found here.
The SQL Server 2012 SP3 support end date was on 10/09/2018

Note that starting from SQL Server 2017, the servicing model was changed to only CU's and GDR's when needed.


Restore InnoDB from .frm files My database crashed and my PC and I now only have the .frm and .ibd files left.

Is there any way to restore my database?

Two potential workarounds:

Simulate the internal InnoDB table counter. That is, create work tables (with innodb_file_per_table enabled) until you have the internal pointer of table id equal to (1 – id_of_ibd_table_you_need_to_restore).
Manually hex edit the .ibd file, changing the table id.

See Recovering an InnoDB table from only an .ibd file for the full explanation and the detailed steps involved.


Joining two inline functions, slows considerably when filtering on second function I have two inline functions that run quickly enough alone.

When I left join them like this:

select *
from
Function1(-1) F1
left join
Function2(-1) F2 on F1.key = F2.key

...I get my result set in approx 14 seconds. Perfectly fine for what it's doing.

But if I add a where condition:

Where
F2.Boolean = 0

...then it takes over 8 minutes to complete.

There are some results that do not have a F2 record so those are null.

I did a test with isnull(f2.boolean,0) = 0 and that runs in 10 seconds, also if a place a or F2.boolean is null it runs quickly.

I am using SQL Server 2008 R2, and tested on SP2 and SP3. Does anyone have ideas or suggestions for what to look at?

The functions are simple select statements joining multiple tables. Nothing special in them. Each individual function returns results of the entire tables in under 10 seconds.

When I add "where one of the date fields in F2 is between two dates", that runs fast. I also just tested on a varchar(2) field in the where clause, and this causes it to be extremely slow as well (but not the datetime field). ok, Why would a filter on a bit field or varchar from the second function cause such a speed decrease, vs a datetime between? Another observed behavior: If I add a filter on F1, to specify only ones that actually have a F2 record first then the original filter on F2 it runs quickly again.

Workaround suggestion:

If you can change the functions, try making them multi-statement table value function instead of inline functions. When doing so, you define an output table in the RETURNS part. In the DDL of the output table, declare a good clustered primary key.

Example:

CREATE FUNCTION dbo.fn_test1(@i int)
RETURNS @out TABLE (
    a    char(5) NOT NULL,
    b    date NOT NULL,
    c    int NOT NULL,
    PRIMARY KEY CLUSTERED (a, b)
)
AS

BEGIN;
    INSERT INTO @out (a, b, c)
    SELECT x, y, z
    FROM dbo.someTable
    WHERE someCriteria>@i;

    RETURN;
END;

Even though swapping an inline function for a multi-statement function comes with its own set of advantages and drawbacks, an important difference in your case may be that the output is stored in a temp table with a clustered index before the join happens. If you've properly aligned the clustered indexes on the two functions, joining their output tables should be a speedy affair.

Here's an idea of what kind of query plan you could expect (excluding the inner workings of the functions, obviously).


Understanding Variations on Multiple Joins I have two versions of a multiple join which produce identical results. The five tables are:
customers ← sales ← saleitems → paintings → artists

The arrows (hopefully) show the relationship between the tables.
Each table has a primary key called id, and the inner tables have a foreign key to another table called [othertable]id.
The first version is a classic join with the table listed in the above order
SELECT
    c.id, c.givenname, c.familyname,
    a.givenname, a.familyname
FROM
    customers c
        JOIN sales s ON c.id=s.customerid
            JOIN saleitems si ON s.id=si.saleid
                JOIN paintings p ON si.paintingid=p.id
                    JOIN artists a ON p.artistid=a.id

;
The second version jas all the JOIN clauses first, and the the ON clauses, in the reverse order.
SELECT
    c.id, c.givenname, c.familyname,
    a.givenname, a.familyname
FROM
    customers c
        JOIN sales s
            JOIN saleitems si
                JOIN paintings p
                    JOIN artists a
                    ON p.artistid=a.id
                ON si.paintingid=p.id
            ON s.id=si.saleid
        ON c.id=s.customerid

;
OK, so it works, but can any one explain how the second version works? Why must the order of the ON clauses be the reverse of the JOIN clauses? Is it possible to randomly order the JOIN or ON clauses?
I have tested this in Microsoft SQL as well as with PostgreSQL.

You can't randomize them, the order is important. Maybe you can see it clearer if we add a few parenthesis between the joins.

SELECT
    c.id
FROM
    customers c
        JOIN (sales s JOIN saleitems si ON s.id = si.saleid)
    ON c.id = s.customerid

What this query is doing is joining first sales with saleitems and then their result is being joined with customers. You can't reference a customers column inside the parenthesis join because it's not accessible:

SELECT
    c.id
FROM
    customers c
        JOIN (sales s JOIN saleitems si ON s.id = si.saleid
            AND c.id = c.customerid) -- What is c.customerid here??

  Msg 4104, Level 16, State 1, Line 6 The multi-part identifier
  "s.customerid" could not be bound.

If you ask me, I'd stick with the first way of writing the query, it's way clearer for most programmers that way.


An event has run for certain amount of time. How to find the time for how long this event has run in mysql? I am using MySQL. I ran one event it took long time.
How to find the time for how long this event has run?

Maybe you can create an evenlog-table. Then inside your event :

BEGIN
  SET @starttime = now();
  ....
  insert into event_log (name, starttime, endtime) values ('myevent', @starttime, now());
END

Like this you'll have a log of all your events over a longer period to compare.


Delete all records in tables with reference to another reference Sorry if the title to this question makes no sense.  I really wasn't sure how else to word what I needed to do.

So I have the following tables in my database:

customers
    customer_id (PK)
    customer_name
    website

customer_address
    customer_id (FK) (PK-A)
    street (PK-B)
    city
    region
    code

customer_contact
    contact_id (PK)
    contact_name
    customer_id (FK)

customer_contact_phone
    contact_id (FK) (PK-A)
    phone_number (PK-B)
    extension

I am trying to set up a procedure which would allow me to delete all the data pertaining to a specific customer.  Now I know how to delete any or all of the addresses pertaining to the customer whose data i am trying to delete.  However, I am not know how to properly delete all of the contact data.

My issue is that there can be multiple contacts for a customer, and each contact can have multiple phone numbers.  I am not sure how to go about setting up these records for deletion because of how many foreign keys are set up.

You just have to traverse the relationship backwards, deleting from the last child first, then the next child, and so on.

Let's say you want to delete all the data for a specific customer, you could have this procedure:

CREATE PROCEDURE dbo.ObliterateACustomer
  @delete_me int
AS
BEGIN
  SET NOCOUNT ON;

  DELETE dbo.customer_contact_phone
    WHERE contact_id IN
    (
       SELECT contact_id
         FROM dbo.customer_contact
         WHERE customer_id = @delete_me
    );

  DELETE dbo.customer_contact WHERE customer_id = @delete_me;

  DELETE dbo.customer_address WHERE customer_id = @delete_me;

  DELETE dbo.customer WHERE customer_id = @delete_me;
END
GO

For the delete from a relation, there are several ways to do it. IN is arguably the most straightforward, but you can also delete from a join:

DELETE p
FROM dbo.customer_contact_phone AS p
INNER JOIN dbo.customer_contact AS c
  ON c.contact_id = p.contact_id
  WHERE c.customer_id = @delete_me;

Or using EXISTS:

DELETE p
FROM dbo.customer_contact_phone AS p
WHERE EXISTS
(
  SELECT 1 FROM dbo.customer_contact AS c
  WHERE c.contact_id = p.contact_id
    AND c.customer_id = @delete_me
);

You could even use MERGE, but I'm not going to show you how, because I think it's often a terrible idea. :-)


Does Log-Shipping need Transaction Log Backup from Primary Server? In this video tutorial it is explained that we should use the transaction log backup of the primary server database and restore it into the secondary server database for log shipping.

I was doing a sample practice to check at my machine and got to know that we don't need to take a backup of the transaction log from the primary database server. Just take a full backup and restore it to the secondary server.

By doing this it will automatically copy the transaction log file to the secondary server.

Can you please confirm if this is the right approach or if it is mandatory to include the transaction log backup from the primary server?

If you don't include the transaction log, then it's not really log shipping, is it?

Your full backup is restored to that point in time, and that's where it will stay until you either (a) apply a log or (b) apply a differential or another full backup. Yes, the restore does also bring over the transaction log, but only the state of the transaction log at that same point in time. I guess technically you could argue that you shipped the log once, but the point of log shipping is to keep the secondary server up to date more often than the full backups (which are typically very large in comparison to periodic log backups).

So if your intention is to do this backup / restore once a day, and you don't need to keep the copy up to date throughout the day, just do a simple backup and restore, and stop calling it log shipping.

If you want the copy to stay relatively up to date throughout the day, then yes, it will be mandatory to take periodic log backups on the primary server and restore them on the secondary server. Note that you have to weigh what's more important: keeping a copy of the data up to date, or letting users query stale data uninterrupted. If you restore a log to the secondary server every 15 minutes, you have to kick all the read-only users out every 15 minutes, because you need exclusive access to the database in order to restore the log.

You can read more information about log shipping here:

http://msdn.microsoft.com/en-us/library/ms187103(v=sql.90).aspx


SQL Server 2008 R2 Restore Database From File - "failed: 38(failed to retrieve text for this error. Reason: 15105)" I'm running a database restore on a backup file that was created up on a clients server, the backup file has been moved to our server (same SQL version 2008 R2) and I'm getting the following error after the T-SQL has been running for nearly 7 minutes:

TSQL:

RESTORE DATABASE [Charms_OrangeGrove]
FROM    DISK = N'S:\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Charms_OrangeGrove_290114.bak'
    WITH  MOVE N'CharmsData_dat'
          TO N'S:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Charms_OrangeGrove.mdf',
          MOVE N'CharmsData_log'
          TO N'S:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Charms_OrangeGrove_Log.ldf'

Error:

Msg 3203, Level 16, State 1, Line 1
Read on "S:\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Charms_OrangeGrove_290114.bak"
    failed: 38(failed to retrieve text for this error. Reason: 15105)
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

It looks like you are having a disk read problem or perhaps a corrupted backup.

I would try

a. Run RESTORE VERIFYONLY FROM DISK =
        N'S:\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Charms_OrangeGrove_290114.bak'
b. If it comes up clean try copying it to a different location
        (preferably a different drive on different physical hardware)

Here is a link where someone had a similar problem.  It has some additional steps he tried and mentions that he had a hardware problem.

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/2a127a18-dfd0-4668-afc4-f5eb497f4977/sql-server-2008-restore-fails-msg-3203?forum=sqldisasterrecovery


Oracle: Mutually modifying two tables? Suppose I have two tables I want to keep in sync:

create table a(x number, s varchar2(10));
create table b(x number, s varchar2(10));

It's easy to keep B synchronized with A, using triggers such as:

create or replace trigger a_t1
after insert on a
for each row
begin
  insert into b(x,s) values(:new.x,:new.s);
end;

Of course, if I add the inverse trigger:

create or replace trigger b_t1
after insert on b
for each row
begin
  insert into a(x,s) values(:new.x,:new.s);
end;

I will get a mutating table error if I insert into either table (and which of course is the only thing that keeps the triggers from executing in an infinite cycle).

My question:  how can I arrange for these two tables to sync themselves, such that modifications to A are reflected in B, and vice versa, without modifying the applications that are modifying A and B?

This is for a very short-term solution, so no hack is too cheap.

(disclaimer)
I realize the horribleness of what I'm asking!  This is a short-term integration bridge and will go away as soon as integration is complete.
(end disclaimer)

Just keep one table. Then replace the other table with a view on the table. Now you should be able to insert/update/delete a row in either the view or the table. Normally this should work. If the insert/update/delete in the view cause a problem then you can always create triggers on the view that replace insert, delete, and update (create trigger t_x instead of update on view). Check the oracle documentation for more information about the triggers