Couldn't find the long running SQL under v$session_longops

The best view (for me) to see the status of a long query is V$SQL_MONITOR.
This view include all query that has consumed at least 5 seconds of CPU or I/O time.
This view is very volatile, so you have to answer it faster after it's execution.
If you can't find it into this view, you can find it in a awr report


Query cleanup

First, lets rewrite your query to be readable, by using table aliases, qualifying field names, and using an ANSI join:

select t.userID, t.date, t.time, t.servID, t.timestamp,
       l.servID_HEX, l.SERV_LOCY, l.SERV_LOCX
from test t
     inner join locations l on (t.servID=l.servID_HEX)
where t.userID='<someusers>'
  and extract(dow from t.timestamp)=2;

What the query needs

To start breaking this problem down you'll want to do is look at the fields used and where they're used:

Tables: locations, test

Fields output: test.userID, test.date, test.time, test.servID, test.timestamp, locations.servID_HEX, locations.SERV_LOCY, locations.SERV_LOCX

Terms used in filter predicates: test.servID, locations.servID_HEX, test.userID, extract(dow from test.timestamp)=2

Indexes

Now, the most important thing is to make sure that you have indexes that target high-selectivity columns used in predicates.

"high selectivity" just means that there aren't tons of the same value - there's no point indexing a column if 50% of the time it's 'a' and 50% of the time it's 'b', because you just don't gain much benefit from the index.

Assuming that all these columns have lots of well distributed distinct values, so they're highly selective and don't have any values that're massively common, you'll want to create an ordinary b-tree index. That's enough for locations because there's just one value used in a predicate:

CREATE INDEX locations_servid_idx ON locations(servID_HEX);

For test it's more complex.

You could create indexes separately for each column, but it's more efficient to create a single composite index that has all the columns you're using in this query's predicates for a given table. So you want servId, userID and timestamp. However, you don't use timestamp directly - you use it to get the day of week, and an index on timestamp can't be used to look up extract(dow from timestamp).

This is where expression indexes come in.

Indexes don't just cover columns. They can also cover arbitrary expressions. In this case, we'll create an index that includes extract(dow from timestamp).

So the index for test will look like:

CREATE INDEX test_custom_idx ON test (
    servID,
    extract(dow from timestamp),
    userID
);

As you'll be doing different kinds of timestamp based queries I'd consider creating multiple indexes for different timestamp expressions.

Which order to put the columns in depends on how selective each column is. Even though I'm sure the day-of-week will be more selective I've put the server ID first because it's used in a join condition that lets us completely disregard rows of the other table.

Since you didn't provide sample tables or sample data I can't really test it and I'm not keen on dummying some up. Adjustments may be required.

Partial indexes

Partial indexes can also be a big win, where the index only contains data for a subset of the data, e.g.:

CREATE INDEX test_custom_idx ON test (
    servID, userID
) WHERE (extract(dow from timestamp) = 2);

contains only data for that day, so it's a lot faster to search for queries involving just that day, but cannot be used at all for queries that might involve different days, or for which the day is determined at runtime from the output of another part of the query.

Index-only scans and covering indexes

Another thing you can consider is that all those unused columns still have to be read from disk to fetch your rows. In PostgreSQL 9.2 and newer there's a way to avoid that using an index-only scan if all columns you are fetching are in an index.

So you could create a covering index, where you include columns that aren't really required for the actual search but are there so they can be output without reading from the main table. The first thing to do for that is to get rid of useless values in your SELECT list, so we'll delete the redundant t.date and t.time from your query. Then create two new indexes:

CREATE INDEX locations_covering_idx
ON locations(
    servID_HEX,
    SERV_LOCY,
    SERV_LOCX
);

CREATE INDEX test_covering_idx ON test (
    servID,
    extract(dow from timestamp),
    userID,
    timestamp
);

Reducing repetition

Since your dataset is static, having tons and tons of indexes isn't a big problem.

Each index has a cost for insert/update/delete. So for non-static data you want to minimize that by getting rid of indexes that don't provide enough improvement to be worth the cost.

One way to do that can be to split up composite indexes. In your case for example I might take the timestamp stuff out of the main index on location and put it in a bunch of new indexes instead. That way all the different timestamp expression indexes can be updated without rewriting a whole lot of unrelated data for userid and serverid as well. PostgreSQL can still often use the indexes together, combining them with a bitmap index scan.

Not a concern for your current dataset, but worth learning for later.

Data cleanup

This is a static data set. So if you don't need a column, drop it. Or better yet, create a new table with just the subset of data you need, pre-joined, using something like:

CREATE TABLE reporting AS
select t.userID, t.servID, t.timestamp, l.SERV_LOCY, l.SERV_LOCX
from test t
     inner join locations l on (t.servID=l.servID_HEX)
ORDER BY t.userID, t.servID, t.timestamp;

which creates a new table that has the data pre-joined and sorted for the most useful grouping of access.

You can then write much simpler queries that'll be much, much faster, against the new table, and your indexes will be a lot simpler too.


How do I compare 1000 items every minute over 1 million records in mongodb without pulling all the data?

If you add "_id" to your index {"3":1, "_id":1 } and then projection of {"_id":1, "3":1} to query, you get result directly from index in the memory (if you have enough memory to hold index in the memory).

Adition, you could use in-memory database engine.


Multiple Cascade Paths (Not really) - Sql Server

When SQL was first developed, the idea of cascading updates and deletes made sense. In practice, they are nothing but trouble. Deleting one row of one table or updating one field of one row of one table could lock up the entire database for hours because of the cascading locks. Or generate a race condition.

Plus, cascading actions are, by definition, side effects. You really, really want to minimize the amount of side effects. Nothing good comes from them.

So it is best to not allow the deletion of a row that is referred to by any foreign key(s). Always accept the default "On Cascade" option (No Action). Allow the DBMS to throw an exception and let the app respond correctly (delete/update the referencing rows then the referenced row -- after checking with the user if this is really what they want to do, of course).


This is my (untested) query:

SELECT p.id
FROM reservables AS p
   LEFT JOIN reservations AS c
      ON p.id = c.reservable_id
         AND NOT c.reserved_between
                 && tsrange('2020-02-01 14:30', '2020-02-01 15:30', '[]')
WHERE NOT EXISTS (SELECT 1 FROM reservations AS s
                  WHERE p.id = s.reservable_id
                  AND s.reserved_between
                      && tsrange('2020-02-01 14:30', '2020-02-01 15:30', '[]'))
GROUP BY p.id
ORDER BY count(c.id) NULLS FIRST
LIMIT 1;

The important part is the NOT EXISTS condition, the left outer join, GROUP BY and ORDER BY are just to collect the big plus. If the performance is not good enough, go for the simpler query.


How do I duplicate constraints from one existing table to another existing table with an identical structure?

CREATE TABLE table_m
(
    id int primary key,
    f1 int not null,
    f2 int not null,
    f3 int not null unique,
    unique (f1, f2)
);

CREATE TABLE table_a
(
    id int primary key,
    f1 int not null,
    f2 int not null,
    f3 int not null,
    constraint fk_f1_f2 foreign key (f1,f2) references table_m (f1,f2),
    constraint fk_f3    foreign key (f3)    references table_m (f3)
);

CREATE TABLE table_b
(
    id int primary key,
    f1 int not null,
    f2 int not null,
    f3 int not null
)

You can use information_schema view to get information about FOREIGN KEYS in this way:

SELECT   tc.constraint_name,
         tc.table_name,
         ccu.table_name AS foreign_table_name,
         string_agg(distinct kcu.column_name, ',') AS column_name,
         string_agg(distinct ccu.column_name, ',') AS foreign_column_name
FROM     information_schema.table_constraints AS tc
JOIN     information_schema.key_column_usage AS kcu
ON       tc.constraint_name = kcu.constraint_name
JOIN     information_schema.constraint_column_usage AS ccu
ON       ccu.constraint_name = tc.constraint_name
WHERE    constraint_type = 'FOREIGN KEY'
AND      tc.table_name='table_a'
GROUP BY tc.constraint_name, tc.table_name, ccu.table_name;

constraint_name | table_name | foreign_table_name | column_name | foreign_column_name
:-------------- | :--------- | :----------------- | :---------- | :------------------
fk_f1_f2        | table_a    | table_m            | f1,f2       | f1,f2
fk_f3           | table_a    | table_m            | f3          | f3

You can dynamically build SQL statements to add FOREIGN KEYS.

Copy&Paste and EXECUTE or use a function like this SO answer to execute.

SELECT   format('ALTER TABLE table_b ADD CONSTRAINT %s FOREIGN KEY (%s) REFERENCES table_m(%s);',
                 tc.constraint_name,
                 string_agg(distinct kcu.column_name, ','),
                 string_agg(distinct ccu.column_name, ','))
FROM     information_schema.table_constraints AS tc
JOIN     information_schema.key_column_usage AS kcu
ON       tc.constraint_name = kcu.constraint_name
JOIN     information_schema.constraint_column_usage AS ccu
ON       ccu.constraint_name = tc.constraint_name
WHERE    constraint_type = 'FOREIGN KEY'
AND      tc.table_name='table_a'
GROUP BY tc.constraint_name, tc.table_name, ccu.table_name;

| format                                                                                |
| :----------------------------------------------------------------------------------------- |
| ALTER TABLE table_b ADD CONSTRAINT fk_f1_f2 FOREIGN KEY (f1,f2) REFERENCES table_m(f1,f2); |
| ALTER TABLE table_b ADD CONSTRAINT fk_f3 FOREIGN KEY (f3) REFERENCES table_m(f3);          |

dbfiddle here


What queries is Oracle executing, how frequently, and time-taken?

Note: Using AWR data requires you to have the Diagnostics Pack license option purchased. It may be possible to run the queries below without having the necessary license. Be sure to check with your DBA/licensing manager before using these.

The Automatic Workload Repository (AWR) contains details of the activity on your database. These are stored in various dba_hist* views.

The kind of information you're looking for is primarily available in the dba_hist_sqlstat view. By joining this to dba_hist_snapshot you can get the information available in a given period:

select *
from   dba_hist_sqlstat t, dba_hist_snapshot s
where  t.snap_id = s.snap_id
and    t.dbid = s.dbid
and    t.instance_number = s.instance_number
and    s.begin_interval_time between trunc(sysdate)-1 and trunc(sysdate) -- yesterday's stats

This view holds various *_elapsed columns, which state the increase in the given snap (time period). e.g.:

ELAPSED_TIME_DELTA
CPU_TIME_DELTA
ROWS_PROCESSED_DELTA
BUFFER_GETS_DELTA
DISK_READS_DELTA
PARSE_CALLS_DELTA
EXECUTIONS_DELTA

Times are in microseconds. You can combine these to get average time/execution stats and so on. For example, this gives you the average time/execution for a given sql statement:

select sql_id, sum(t.elapsed_time_delta/1000000)/sum(t.executions_delta)
from   dba_hist_sqlstat t, dba_hist_snapshot s
where  t.snap_id = s.snap_id
and    t.dbid = s.dbid
and    t.instance_number = s.instance_number
and    s.begin_interval_time between trunc(sysdate)-1 and trunc(sysdate) -- yesterday's stats
group  by sql_id

The sql_id is a hash of the text of the SQL itself. You can find the actual statement from:

select * from dba_hist_sqltext where sql_id = :sqlid

Note that only "highest consuming" statements are retained in AWR. So if the query you're interested in isn't the slowest, most executed, most disk access, etc. it may not be in the AWR data. You can get around this by "coloring" sql_ids of interest. This ensures they stay in AWR for as long as your retention period is set. You can do this by running:

exec DBMS_WORKLOAD_REPOSITORY.ADD_COLORED_SQL(:sqlid);

If you just want an overview of your system in a time period (including "heaviest" SQL statements), the AWR report itself gives this. You can find an intro to this on oracle-base.


For what you are describing you would want to use a trigger on the table where you want the soft delete to cascade from.

Here are a few references on triggers:

MSSQLTips

Microsoft's documentation

Triggers have their place but they can quickly get out of hand when triggers fire other triggers or when the trigger execution takes a significant amount of time to complete.

Additionally performance degradation can be a factor with triggers as they happen synchronous when an insert/update/delete statement is made on the table which can extend the execution time of the statement.

Microsoft's Nested triggers

Info from Paul White on triggers

DML triggers for multiple data rows


Is there a progress indicator for OPTIMIZE TABLE progress?

There is nothing MySQL has that will indicate the progress of OPTIMIZE TABLE;.
You may have to go the OS using Windows Explorer and look for a growing tmp table. Hint: Temp table are MyISAM tables that do not have a .frm file with the name #sql-9999.MYD and #sql-9999.MYI. While these two fle exist, the query would be considered in progress. To know how far, one would have to know how many rows times the average row length to estimate the percentage done or left to be done.
MariaDB has some progress metering for the following:

ALTER TABLE
ADD INDEX
DROP INDEX
LOAD DATA INFILE
CHECK TABLE
REPAIR TABLE
ANALYZE TABLE
OPTIMIZE TABLE

I have discussed subject before in terms of MySQL operations

May 02, 2012 : How can I monitor the progress of an import of a large .sql file?
Jan 17, 2012 : Adding an index very slow...is there a mysql cmd to get an ETA or show progress?

If you are trying to reduce the size of ibdata1 for InnoDB, dropping tables simply leaves big gaps in ibdata1. You must perform a full Cleanup of InnoDB Infrastructure.

Apr 01, 2012 : Is innodb_file_per_table advisable?
Mar 25, 2012 : Why does InnoDB store all databases in one file?
Mar 29, 2011 : Maximum table size for MySQL database server running on Windows (NTFS) Table type InnoDB
Feb 04, 2011 : MySQL InnoDB - innodb_file_per_table cons?
Oct 29, 2010 : Howto: Clean a mysql InnoDB storage engine?

UPDATE 2012-12-21 19:30 EDT

Since this database uses innodb, will there be a growing temp table I can look for?

Let's say you want to run
OPTIMIZE TABLE mydb.mytable;

Look at two scenarios involving innodb_file_per_table and this OPTIMIZE TABLE
SCENARIO #1 : innodb_file_per_table disabled
Running OPTIMIZE TABLE will cause all the data and index pages for mydb.mytable to be appended contiguously to ibdata1. This will leave the space previously occupied to add further to the fragmentation madness.
In this scenario, the temp table would exist as InnoDB, and it would materialize in ibdata1. Thus, there would be nothing to visually monitor.
SCENARIO #2 : innodb_file_per_table enabled
For each InnoDB table mydb.mytable you would have

/var/lib/mysql/mydb/mytable.frm
/var/lib/mysql/mydb/mytable.ibd

The .ibd file contains the data and index pages for the table.
Running OPTIMIZE TABLE will cause all the data and index pages for mydb.mytable to be written to an external .ibd file that you can see in Windows Explorer.
CAVEAT
Enabling innodb_file_per_table and running OPTIMIZE TABLE will never reclaim the space formerly occupied within ibdata1. You will have to carry out the InnoDB Cleanup to recreate ibdata1 in such a way that data and indexes never, ever again reside in ibdata1.
CONCLUSION
As stated earlier, MariaDB can monitor OPTIMIZE TABLE;
If you want something you can monitor and you have innodb_file_per_table enabled, you can replace
OPTIMIZE TABLE LOGTABLEFOO1,LOGTABLEFOO2,LOGTABLEFOO3;

with the following mechanical equivalent:
CREATE TABLE LOGTABLEFOO1_NEW LIKE LOGTABLEFOO1;
INSERT INTO LOGTABLEFOO1_NEW SELECT * FROM LOGTABLEFOO1;
ALTER TABLE LOGTABLEFOO1 RENAME LOGTABLEFOO1_OLD;
ALTER TABLE LOGTABLEFOO1_NEW RENAME LOGTABLEFOO1;
DROP TABLE LOGTABLEFOO1_OLD;
ANALYZE TABLE LOGTABLEFOO1;

CREATE TABLE LOGTABLEFOO2_NEW LIKE LOGTABLEFOO2;
INSERT INTO LOGTABLEFOO2_NEW SELECT * FROM LOGTABLEFOO2;
ALTER TABLE LOGTABLEFOO2 RENAME LOGTABLEFOO2_OLD;
ALTER TABLE LOGTABLEFOO2_NEW RENAME LOGTABLEFOO2;
DROP TABLE LOGTABLEFOO2_OLD;
ANALYZE TABLE LOGTABLEFOO2;

CREATE TABLE LOGTABLEFOO3_NEW LIKE LOGTABLEFOO3;
INSERT INTO LOGTABLEFOO3_NEW SELECT * FROM LOGTABLEFOO3;
ALTER TABLE LOGTABLEFOO3 RENAME LOGTABLEFOO3_OLD;
ALTER TABLE LOGTABLEFOO3_NEW RENAME LOGTABLEFOO3;
DROP TABLE LOGTABLEFOO3_OLD;
ANALYZE TABLE LOGTABLEFOO3;

Please keep in mind the OPTIMIZE TABLE is nothing but ALTER TABLE ... ENGINE=InnoDB; followed by ANALYZE TABLE.
With these steps, you know in advance what the temp tables are

LOGTABLEFOO1_NEW.ibd
LOGTABLEFOO2_NEW.ibd
LOGTABLEFOO3_NEW.ibd

Simply Monitor Each File's Existence and Size Until it Disappears.


SQL Server: Performance Insert Into vs Select Into

What are good principles in figuring which is faster and why? I would
  think Microsoft would optimize to make insert into , just as fast, for
  careful programming.

The principles that I try to follow when analyzing something like this question are:

Avoid making unnecessary assumptions.
Read the official documentation.
Test the workload. The amount of testing depends on how fast I need the code to be.

I'm aware of two pieces of documentation that address your question. The first one is a blog post saying that SELECT INTO for temp tables has different behavior for eager writes as of SQL Server 2014. That is by design. So I don't think that it's correct to say that the difference is minimal for small tables. If anything, the optimization described in the blog post seems designed for smaller tables:

  The change in SQL Server 2014 is to relax the need to flush these
  pages, as quickly, to the TEMPDB data files.  When doing a select into
  … #tmp … or create index WITH SORT IN TEMPDB the SQL Server now
  recognizes this may be a short lived operation.   The pages associated
  with such an operation may be created, loaded, queried and released in
  a very small window of time.

  For example:  You could have a stored procedure that runs in 8ms.  In
  that stored procedure you select into … #tmp … then use the #tmp and
  drop it as the stored procedure completes.

  Prior to the SQL Server 2014 change the select into may have written
  all the pages accumulated to disk.  The SQL Server 2014, eager write
  behavior, no longer forces these pages to disk as quickly as previous
  versions.   This behavior allows the pages to be stored in RAM (buffer
  pool), queried and the table dropped (removed from buffer pool and
  returned to free list) without ever going to disk as long memory is
  available.   By avoiding the physical I/O when possible the
  performance of the TEMPDB, bulk operation is significantly increased
  and it reduces the impact on the I/O path resources as well.

The second piece of documentation explains that parallel insert into temp tables with INSERT INTO ... SELECT is available without a TABLOCK hint in SQL Server 2016 but requires a TABLOCK hint in SP1 and in future versions.

  The issue is first fixed in SQL Server 2016 Service Pack 1 . After you
  apply SQL Server 2016 SP1, Parallel INSERTs in INSERT..SELECT to local
  temporary tables is disabled by default which reduces contention on
  PFS page and improves the overall performance for concurrent workload.
  If parallel INSERTs to local temporary tables is desired, users should
  use TABLOCK hint while inserting into local temporary table.

Going back to your original statement, you can't logically deduce which of the two will be faster. What is faster depends on how Microsoft designed the software and the characteristics of your workload. Making guesses about the amount of time needed to create the column definitions just isn't helpful. Testing is helpful. If your testing suggests that SELECT INTO is faster than go with that. For what it's worth, I also work on data warehouse loading with a close eye on performance and I haven't seen the difference between the two approaches be anything that's worth worrying about.


It's hard to give you a specific guidance on your best setup, but in any case there is no such thing as a trigger writing to an OLAP cube.

I can give you some starting points though.

You need to look into different storage options for your cubes.

By default cubes use MOLAP (Multidimensional OLAP) in which you process a cube on a scheduled basis[1] and all data and aggregations are stored in the OLAP database. This has a latency as the relational data is only visible in the cube after processing.

This is by far the easiest option from a developer perspective, but won't resolve your requirement.

[1] With MOLAP, see below for the proactive caching options

There is also a ROLAP (Relational OLAP) storage mode, which stores the aggregations in indexed views in a relational database. There is no copy of the data in the cube but the data is available immediately in the cube.

This off course has an effect on the query performance to your SSAS cube. Generally the querys will return slower and processing time will be longer.

If performance is too slow, there is a third option called HOLAP (Hybrid OLAP), which stores the data in the relational database but the aggregations in the OLAP database. When the source data is updated the aggregations will be processed again. This offers zero latency, and relatively decent performance when the queries are resolved from the aggregations, but slow performance when the users drill down or the query has to access the relational store.

Proactive caching

You should also look into Proactive caching which determines how often the aggregations are updated. This should be a balance between the load that the processing puts on your relational database and the performance of the SSAS queries and the latency in your cubes.

If you use proactive caching the cube listens to changes in the underlying data source and processes your MOLAP partitions on the schedule you choose.

Have a look at this article Introduction to SQL Server Proactive Caching for an explanation of how that works


Get single records when duplicate records exist in 2 table

You can try to find highest sale for each email and then join it with leads:

WITH maxsales AS
(SELECT
sales.*,
ROW_NUMBER() (PARTITION BY sales.email ORDER BY Sales.Sales DESC) AS rn
FROM [dbo].[Sales]
)
SELECT maxsales.email as [sales_school]
  ,leads.email[leadds_insti]
  ,maxsales .*
  ,leads.*
FROM maxsales
INNER JOIN leads ON leads.email= maxsales.email
WHERE maxsales.rn = 1


English is not the easiest language in the world to describe mathematical concepts and their relationship to the real world.  For a few years, I taught short courses in database design and programming in Spanish, even though Spanish is not my first language.  But there were a few concepts that came across more clearly in Spanish than they did in English.

The first thing you need to understand is that the relational data model was pretty much invented by one person, Ed Codd in 1970, and he based the data model on the mathematics of relations.  A great deal of the terminology is derived from terminology of mathematical relations, although there are subtle differences between mathematical relations and relations in the relational data model.

The second thing you need to understand is that "relation" and "relationship" are two different words with two different underlying concepts, even though there is a huge overlap between the two.  The word "relationship" tends to be used in ER modeling, and refers to an association between two or more entities in the real world.  These entities may be of different types or of the same type.

The third thing you need to understand is that you understand the subject matter through analysis but you come up with a relational model by design.  The difference between analysing the data and designing the database is often not clear even for many English speakers.

When you convert an E-R model into a relational model, both the Entities and the Relationships get expressed as relations.  so the difference between the Es and the Rs becomes almost moot as you make progress towards the eventual construction.

I apologize if this is too vague, but you'll have to clarify your question if you want more specific answers.


In case if I want to figure out user's department while selecting data from tasks, how do I build the query for it?

You'd first join user and then department.

SELECT *
       FROM task
            INNER JOIN user
                       ON user.id = task_user.id
            INNER JOIN department
                       ON department.id = user.department_id;

Such "chains" of joins are no problem for a DBMS in general, it's built to do that (but you should consider proper indexing to support it (but that's also true for queries not using joins)). The "shortcuts" you have in mind on the other hand bring a certain level of denormalization and with that a handful of problems.

Whether to include department_id in tasks depends on what you're trying to model.

If a task can be assigned to a whole department without a specific user from that department, you'll need it. Add a check constraint enforcing that one of both is null, to be able to distinguish between task assigned to only a user and those only assigned to a department.

If only users can have tasks assigned, you won't need it. You can query the department as shown above. Further more in that case it's dangerous to include it or at least it makes things more complicated. You have to check then, that the integrity isn't violated. Say if a user assigned to a task is in department A but the department assigned to the task is B. That obviously doesn't match. Unless users can be in several departments an a task should be assigned to a user in the role as a member of a specific department. That's one of the problems of denormalization I mentioned above.

(You see, there are many ifs here, you need to think through. Maybe even more than I just came up with!)

If you don't need the department_id because of the logic, I'd recommend not to use it in task. It most likely brings more problems and doesn't even solve another as joining usually is no problem.


Maintaining replicated data in a staging-to-warehouse environment

I presume you have substantial data volumes if you have separate staging and data warehouse servers.  From your posting It sounds like your staging server is doing a bit more than just staging the data.  You describe merging data into the fact table on the warehouse server, so I presume that the staging server actually has ETL processing on it as well.

I would not generally have recommended a transform-push approach like this; running ETL processing from the warehouse and pulling from a staging area is generally better.  The merge happens on the warehouse.  If you need to scale out you can replicate the warehouse, and the same operations will happen on the source and targets.

However, I guess you're stuck with the transform-push architecture, so you've got a couple of options:

Use SSIS or another ETL tool to copy the incremental load to a staging table on the warehouse and merge it to the fact tables from there.  This will be fairly efficient and can be done with set operations - i.e. a merge statement or insert and update.
Replicate the staging area into another database on your warehouse server and execute the merge from there after the ETL processing on the staging server has run.  This is probably more fiddly than the first option, and I can't really see any advantages to it.
If your staging and warehouse databases are located on the same database instance then you can create synonyms in the warehouse and merge through the synonyms.  The synonyms prevent a need to hard-code database refererences.


Whay does the SQL Server Service account has the ""Impersonate a client after authentication" Right?

The documented Windows Privileges and Rights for the database engine service account are:

  Log on as a service (SeServiceLogonRight)
  Replace a process-level token (SeAssignPrimaryTokenPrivilege)
  Bypass traverse checking (SeChangeNotifyPrivilege)
  Adjust memory quotas for a process (SeIncreaseQuotaPrivilege)
  Permission to start SQL Writer
  Permission to read the Event Log service
  Permission to read the Remote Procedure Call service

The SeImpersonatePrivilege is only documented as required for the SSIS service.

That being said, I will say that the SeImpersonatePrivilege privilege is required for the database engine. Windows Authenticated client connections impersonate the client when accessing OS resources like SSPI linked servers and network shares (think db attach, backups etc).


Update all rows when sort/ordering value is changed for single row?

I've never handled anything like this before but why not do a combination of the +1 and the decimal versions.  Do the decimal versioning while they are editing .. then at the end of an editing session (when they do a final save) go ahead and re-write all of the values as integers.  Presumably in any given editing period there won't be so many edits that you will run into major problems with precision.

Another option would be to use a list in a single column in say the project table.  Basically have your IDs listed there along these lines.

,43,23,14,55,27, etc.

Then you can use string manipulation to remove ,55, from where it is and stuff it between 23 and 14.  In SQL Server the T-SQL code might look like this:

DECLARE @OrderString varchar(500) = ',43,23,14,55,27,'
DECLARE @MoveString varchar(50) = '55'
DECLARE @MoveBefore varchar(50) = '14'

-- Remove the value to be moved
SET @OrderString = STUFF(@OrderString, PATINDEX('%,'+@MoveString+',%', @OrderString), LEN(@MoveString)+2,',')

-- Put it in the right place
SET @OrderString = STUFF(@OrderString, PATINDEX('%,'+@MoveBefore+',%',@OrderString),0,','+@MoveString)

-- Print it out
PRINT @OrderString


Is there a faster way of moving data between databases than CTEs?

This doesn't have anything to do with using a CTE specifically, as your question title implies. It is probably a combination of at least one or more of the following:

an expensive scan (or seek + a million lookups) to identify the rows to delete (I assume [Receive Date] is not the clustering key)
many underlying non-clustered indexes that also must be updated
blocking (both on the delete and on the target/archive table)
a batch size too large that causes too much transaction log activity (which can be especially bad if you have poor t-log configuration)

See this blog post but generally you may want to delete in smaller chunks and inject some artificial delays and/or checkpoints / log backups in between each individual delete.


You can still make it data driven - you just need more data!

First some data setup:

drop table if exists #Lookup;
drop table if exists #AdCampaign;

with LookupData as
(
    select * from
    (
        values
        ('instagram', 'InstagramCampaign', NULL, NULL),
        ('instagram', NULL, NULL, '%foobar%'),
        ('google', NULL, NULL, '%foobar%'),
        ('bing', NULL, '%abc123%', '%foobar%')
    ) as T(Provider, CampaignEqual, CampaignLike, KeywordLike)
)
select * into #Lookup from LookupData;

with CampaignData as
(
    select * from
    (
        values
        (1, 'Instagram', 'InstagramCampaign', NULL),
        (2, 'bing', 'Contains abc123 internally', 'bazfoobarbix')
    ) as T(ID, Provider, Campaign, Keyword)
)select * Into #AdCampaign from CampaignData;

There's no magic in there. It's all just a convenient (for me) way to populate some tables without worrying about datatypes, constraints, keys and whatnot all. The pertinent bit is the columns in #Lookup. I've inferred that Provider is mandatory. Campaign is split in two depending on how the values are to be compared. I'll get to that in a minute.

I use NULL in #Lookup to represent "not applicable" or "not a part of this comparison".

And here's the query that does the matching:

select
   a.*,
   '|',           -- just a visual separator for SSMS
   l.*
from #AdCampaign as a
inner join #Lookup as l
on a.Provider = l.Provider      -- I'm taking this as mandatory.
and (l.CampaignEqual is NULL or l.CampaignEqual = a.Campaign)
and (l.CampaignLike is NULL or a.Campaign like l.CampaignLike)
and (l.KeywordLike is NULL or a.Keyword like l.KeywordLike);

The idea is to have one column in #Lookup per value-operator pair. These then translate quite directly into the WHERE clause of the actual update. So Lookup.CampaignEqual compares to AdCampaign.Campaign using and "equals" operator. That's why Lookup contains both CampaignEqual and CampaignLike - so it's obvious what to compare to and how. This design can be extended indefinitely with columns such as ValueEqual, ValueLessThan, ValueStartsWith etc.

You could have fewer columns in #Lookup at the cost of more involved predicates. For example if we have just Lookup.Campaign then the predicate becomes

.. and (l.Campaign is NULL
     or a.Campaign = l.Campaign
     or a.Campaign like l.Campaign
     ... etc.
   )

The meaning's the same. I think the performance will be the same. It's a matter of style and what you're comfortable with maintaining.

It may be a little easier to get the lookup data correct with just a single column. Putting a wildcard in a ..Equal column or missing them from a ..Like would cause the matching to break.

OK, so more data and a somewhat lengthy where clause.


A restore always restores the files to the same sizes they were when they were backed up.

There is currently no way to "restore with shrink."

You'll need to restore the files to a drive with sufficient free space, and perhaps consider shrinking the files at that point, then use your preferred technique to move the files to the spot you want.


SQL Server Reporting Service 2014 installation Fail?

Turns out that there was problem with the downloaded media. Tried another source and it worked fine.

Thanks


Big Amount of  in Buffer Pool's Database Pages

Is this a problem ?

Maybe, maybe not. If your database(s) only make up 80% of your server's max server memory, it's about what I'd expect to see.

If you have more data than RAM, then the free space is likely due to something else asking for memory. Something else could be memory grants for queries, CHECKDB, index rebuilds, etc.

It could also represent a memory fight between Windows and SQL Server. This usually happens when you don't have max server memory set to give Windows its 10% memory tithe so it can function properly.

After that something else took the memory, it either hasn't been re-used by the buffer pool, or various something elses continue to use memory that could be used by the buffer pool.

  How can number of free_space_in_bytes be mitigated ?

If your database(s) represent ~80% of max server memory, nothing.

If they don't, you can:

Add memory
Try to get something elses to ask for less memory

Adding memory usually helps, if you add enough to both cache your most-queried objects and provide memory grants to processes that need them. Figuring that number out is an exercise left to you. There's no generic calculation, but a good place to start is having RAM at 50% of server data. In simple terms: if you have 10GB of data, have 5GB of RAM.

Getting something elses to ask for less memory comes down to figuring out what else is asking for memory, and tuning those things appropriately.

  Any other things to investigate / look at in our situation ?

Yep! Look at the Stolen Pages counter:

SELECT *
FROM sys.dm_os_performance_counters AS dopc
WHERE dopc.counter_name LIKE 'Stolen Server Memory%';

That can tell you how much memory SQL Server has taken from the buffer pool and given to something elses.

You can also look to see if SQL Server has had to force any query memory grants:

SELECT *
FROM sys.dm_exec_query_resource_semaphores AS deqrs
WHERE deqrs.forced_grant_count > 0;

Since you're on SQL Server 2017, you can use sp_BlitzCache:

EXEC sp_BlitzCache @SortOrder = 'memory grant';

Which will show you queries that ask for the largest memory grants. Of course, if your server is really low on memory, your plan cache data might stink, because turnover there will likely be really high. If that's the case, stop here and add memory before going any further.

You can try using Query Store, if that's turned on. Proc is available from the same link as above.

EXEC sp_BlitzQueryStore @DatabaseName = 'Your Important Database';

Then look for the "memory grant" pattern in the results. For the queries those bring back, look for Sorts and Hashes in the plans. Those usually represent index or query-tuning opportunities. If you need help tuning those, feel free to post them as new questions. Don't tack them on to this one.

And, of course, it could also be that you just never use that memory. I'd leave it alone, though, because you might grow into that 20% someday.


What MySQL storage engine and table schema should I use?

If you're updating frequently, you'll want innodb, which supports row-level locking.  Your stats can go in a different table, which will be more efficient since it's not as wide as your 'feed' table.

Your data types look pretty good (unsigned, small/tiny).

I'm not a fan of your column names.  Why not 'feed_id', etc.?  Will you have no other "noun" in the system that could be abbreviated as "f"?

If f_cat is a foreign key, it should be cat_id.

There's basically no difference between a VARCHAR(50) and a VARCHAR(255), so you might think about giving yourself more room if you might need it.

Since f_lang is NOT NULL and only two characters wide, it's a small waste to make it a VARCHAR.  I'd prefer to see it as a CHAR(2), which saves the lookup.  Or normalize it and make it a foreign key over to a language table.

You've only indexes f_id and u_id.  Is that really all you'll be coming in with?  If not, think of composite indexes that match your where clause.

Hope that helps.


How to automate the detection of the column type of a csv file for a table creation script?

there are command line tools that do field type inferencing.

One is xsv https://github.com/BurntSushi/xsv/

Running this command on this (https://gist.githubusercontent.com/aborruso/3b1af402f0d2ed49465f218d19be81d9/raw/c0e95b320924e9e49902633d16e7ab253046ca16/input.csv)

xsv stats input.csv --everything | xsv table

you have

field       type     sum                 min       max         min_length  max_length  mean                stddev              median              mode  cardinality
id          Integer  5050                1         100         1           3           50.5                28.86607004772212   50.5                N/A   100
first_name  Unicode                      Annabal   Willabella  3           11                                                                      N/A   98
last_name   Unicode                      Albinson  Zaniolini   3           13                                                                      N/A   100
f           Float    2063.2419999999984  0.2656    51.1245     4           7           20.632419999999996  12.603955889545158  17.930799999999998  N/A   100

Using csvkit (https://csvkit.readthedocs.io/en/latest/index.html) and running

csvstat --csv input.csv

you will have

column_id column_name type   nulls unique min   max    sum       mean   median stdev  len freq
1         id          Number False 100    1     100    5,050     50.5   50.5   29.011 -   1, 2, 3, 4, 5
2         first_name  Text   False 98     -     -      -         -      -      -      11  Caren, Weylin, Heall, Flori, Lydia
3         last_name   Text   False 100    -     -      -         -      -      -      13  Saxby, Joderli, Kleinzweig, Coyle, Kleinplac
4         f           Number False 100    0.266 51.124 2,063.242 20.632 17.931 12.667 -   5.356, 12.596, 32.1245, 5.32, 0.2656

In csvkit you have also

csvsql -i postgresql input.csv

that gives you

CREATE TABLE input (
        id DECIMAL NOT NULL,
        first_name VARCHAR NOT NULL,
        last_name VARCHAR NOT NULL,
        f DECIMAL NOT NULL
);


How to create a function in MySQL that will return a random bool

It looks like you had a couple of errors. Try

CREATE FUNCTION IS_ROLEMEMBER (Rolename VARCHAR(255), Username VARCHAR(255))
RETURNS BINARY
READS SQL DATA
RETURN
(
    SELECT (FLOOR(RAND() * 10) % 2)
);

The main errors I saw were:

@Rolename and @Username - no need to use @

RETURNS BINARY AS - no need for AS

I included the READS SQL DATA line as this caused an error on my side otherwise. I believe this is related to replication, so depending on your set up you may not need it.


Why *not* ERROR: index row size xxxx exceeds maximum 2712 for index "foo"?

Short answer: compression.
The data type text allows (lossless!) compression and storage out of line by default:
SELECT typstorage FROM pg_type WHERE typname = 'text';  -- 'x'

The manual about pg_type.typstorage:

p: Value must always be stored plain.
e: Value can be stored in a “secondary” relation (if relation has one, see pg_class.reltoastrelid).
m: Value can be stored compressed inline.
x: Value can be stored compressed inline or stored in “secondary” storage.

Note that m columns can also be moved out to secondary storage, but only as a last resort (e and x columns are moved first).

Test with pg_column_size() instead of length(). Be sure to test actual table columns (with compression applied) not just input values. See:
CREATE TABLE tbl (id int, col text);
INSERT INTO tbl(id, col) VALUES
   (1, rpad(md5('non_random'::text),     100, md5('non_random'::text)))
 , (2, rpad(md5('non_random'::text),    1000, md5('non_random'::text)))
 , (3, rpad(md5('non_random'::text),   10000, md5('non_random'::text)))
 , (4, rpad(md5('non_random'::text),  100000, md5('non_random'::text)))
 , (5, rpad(md5('non_random'::text),  500000, md5('non_random'::text)))
 , (6, rpad(md5('non_random'::text), 1000000, md5('non_random'::text)));

SELECT id, left(col, 10) || ' ...' AS col
     , length(col) AS char_length
     , pg_column_size(col) AS compressed
     , pg_column_size(col || '') AS uncompressed
FROM   tbl ORDER BY id;

id | col            | char_length | compressed | uncompressed
---+----------------+-------------+------------+-------------
 1 | 67ad0f29fa ... |         100 |        101 |          104
 2 | 67ad0f29fa ... |        1000 |       1004 |         1004
 3 | 67ad0f29fa ... |       10000 |        160 |        10004
 4 | 67ad0f29fa ... |      100000 |       1191 |       100004
 5 | 67ad0f29fa ... |      500000 |       5765 |       500004
 6 | 67ad0f29fa ... |     1000000 |      11487 |      1000004

SELECT pg_column_size(rpad(md5('non_random'::text), 1000000, md5('non_random'::text)));

pg_column_size
--------------
       1000004

db<>fiddle here
Note how the value is forced to be unpacked from its storage format with the noop expression: pg_column_size(col || '').
The 5th row would be too big to fit the index tuple (even with compression) and trigger the error message in the title.
The 6th row would be to big to fit even the index page and trigger the related error message:

ERROR:  index row requires 11504 bytes, maximum size is 8191

The test values generated with rpad() have repeating patterns, which allow for massive compression. Even very long strings still easily fit the max. size after compression this way.
Related:

What is the overhead for varchar(n)?

Long answer
I ran more extensive tests, tampering with storage internals to verify my understanding. Only for testing purposes!

db<>fiddle here

dbfiddle does not allow write access to system catalogs. But the queries are there to try "at home".


How can I tell if an index is being used to sort in MySQL?

You should append EXPLAIN EXTENDED before query and see result yourself.

It should have an entry for

possible_keys

If this column is NULL, there are no relevant indexes. In this case, you may be able to improve the performance of your query by examining the WHERE clause to check whether it refers to some column or columns that would be suitable for indexing.

and

Keys

The key column indicates the key (index) that MySQL actually decided to use. If MySQL decides to use one of the possible_keys indexes to look up rows, that index is listed as the key value.

For more information you can refer this Explain output and Explain join_types


Should I increase max_connections in AWS RDS t1-micro for MySQL?

Each connection carries the load of per-connection buffers as set by these parameters

join_buffer_size
sort_buffer_size
read_buffer_size
read_rnd buffer_size

Changing the number of connections increases the amount of memory each connection can demand to this : (join_buffer_size+sort_buffer_size+read_buffer_size
+read_rnd buffer_size) X max_connections
I have written about these before

Jan 03, 2012 : Understanding mysqltuner Recomendations w/ Unused Catalogs
Apr 24, 2012 : How costly is opening and closing of a DB connection?
Feb 01, 2013 : Show processlist / Max concurrent connections seem to max out at 131

ANALYSIS
Amazon has to set the number of connections based on each model's right to demand a certain amount of memory and connections
MODEL      max_connections innodb_buffer_pool_size
---------  --------------- -----------------------
t1.micro   34                326107136 (  311M)
m1-small   125              1179648000 ( 1125M,  1.097G)
m1-large   623              5882511360 ( 5610M,  5.479G)
m1-xlarge  1263            11922309120 (11370M, 11.103G)
m2-xlarge  1441            13605273600 (12975M, 12.671G)
m2-2xlarge 2900            27367833600 (26100M, 25.488G)
m2-4xlarge 5816            54892953600 (52350M, 51.123G)

I wrote about this as well : When should I think about upgrading our RDS MySQL instance based on memory usage?
This allows Amazon to do the following:

Charge you for each memory model based on seamless MySQL usage
Fairly Apportion Resources for MySQL RDS per region
Shoot yourself in the foot for tampering with per-connection settings

RECOMMENDATION
Perhaps you should try useing Amazon EC2 where you have no restrictions on access to my.cnf


SSRS 2016 - Change connection string in all reports

You can use the replace value of XML DML command to modify the value stored in the connection string. I have put together an example below for you as a guide but please note that it is untested as I do not have access to a report server at the moment, it should at least point you in the correct direction:

WITH XMLNAMESPACES  -- XML namespace def must be the first in with clause.
(DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2006/03/reportdatasource'
        ,'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'
 AS rd)
,SDS AS
(SELECT SDS.name AS SharedDsName
       ,SDS.[Path]
       ,CONVERT(xml, CONVERT(varbinary(max), content)) AS DEF
 FROM dbo.[Catalog] AS SDS
 WHERE SDS.Type = 5)     -- 5 = Shared Datasource
SELECT [SharedDsName], [Path], [DEF]
  INTO #catalog
  FROM SDS;

UPDATE #catalog
   SET DEF.modify ('
    declare namespace rds="http://schemas.microsoft.com/sqlserver/reporting/2006/03/reportdatasource";
    declare namespace rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner";
    replace value of (/rds:DataSourceDefinition/rds:ConnectString/text())[1]
    with "new connection string"
');

UPDATE a
   SET a.[Content] = CONVERT(image, CONVERT(varbinary(max), b.[DEF]))
  FROM dbo.Catalog AS a
  JOIN #catalog AS b
    ON a.[Name] = b.[SharedDsName]
   AND a.[Path] = b.[Path];

DROP TABLE #catalog;

More information regarding the replace value of command can be found at Microsoft Docs.


WorkAround for PHP PDO(with libpq V 9.1.4) binding for use of CITEXT?

Locate the namespace inside which the citext type resides:

select nspname from pg_type t join pg_namespace n
  on n.oid=t.typnamespace where typname='citext';

Prepend that namespace (normally, 'public', but it might be different in your case and it might explain the problem) to the cast to citext:

$sql = "SELECT column1 from schema1.column1 where column1 = 'value'::public.citext";

If that solves the problem but in a way you find inelegant, you might reconsider how you're using schemas and search_path: make sure that all your custom types are accessible no matter what.


What exactly triggers the UPDATE of the column is_media_read_only on sys.database_files?

I'm an engineer from SQL Server product team. This read-only media behavior is not by design and we issued a hotfix for it.

To describe the behavior, the read-only media check is done when a file is opened (e.g., DB startup, DB state change like readonly->readwrite). SQL then maintains an read-only media flag for a file in memory and this can be persisted into metadata (on system catalog). The flag may be persisted into metadata when other file metadata happens to be modified or the DB state changes. The real problem here is that once it was hardened on the metadata (which is on the primary data file), we did not reset it properly even the media is no longer read-only. One condition that can reset the flag is attach, but it only does for secondary data files.

The desired behavior is that the read-only media flag is turned off when a file is opened and the media is no longer read-only. So, when a database restarts, its state changes, it is restored, or it is attached, the is_media_read_only should reflect it correctly for every file.

Fix

The fix is documented with KB Article 4538378.


Function To Perform Calculations Based Off Variable

Basically, we're just looking to spread the remainder from the division out over the values. This procedure does exactly that; it will return a table with the result vals from the split. I assume we do not want to return a variable number of columns (depending on the divisors we receive), and I decided that actually generating the text "First", "Second", etc. wasn't really a part of the question, so I'm returning each value in its own row.

CREATE PROCEDURE split_into_ints(@dividend int, @divisor int)
AS
BEGIN
    WITH result_rows AS
         (SELECT TOP (@divisor) ROW_NUMBER() OVER (ORDER BY o1.object_id)
                 as result_num
            FROM sys.objects o1 CROSS JOIN sys.objects o2
         )
        ,base_vals AS
         (SELECT @dividend / @divisor as base_result
                ,@dividend % @divisor as remainder
         )
    SELECT r.result_num
          ,CASE WHEN r.result_num <= remainder THEN 1 ELSE 0 END
           + v.base_result as result_val
      FROM base_vals v CROSS JOIN result_rows r
     ORDER BY result_num
END;
GO

The following:

EXECUTE split_into_ints 10,3;
EXECUTE split_into_ints 49,6;
EXECUTE split_into_ints 2000,11;

Yields these results:

result_num           result_val
-------------------- -----------
1                    4
2                    3
3                    3

result_num           result_val
-------------------- -----------
1                    9
2                    8
3                    8
4                    8
5                    8
6                    8

result_num           result_val
-------------------- -----------
1                    182
2                    182
3                    182
4                    182
5                    182
6                    182
7                    182
8                    182
9                    182
10                   181
11                   181

Alternately, you could make this an inline table-valued function:

CREATE FUNCTION fn_split_into_ints(@dividend int, @divisor int)
RETURNS TABLE
AS
RETURN
(
    WITH result_rows AS
         (SELECT TOP (@divisor) ROW_NUMBER() OVER (ORDER BY o1.object_id)
                 as result_num
            FROM sys.objects o1 CROSS JOIN sys.objects o2
         )
        ,base_vals AS
         (SELECT @dividend / @divisor as base_result
                ,@dividend % @divisor as remainder
         )
    SELECT r.result_num
          ,CASE WHEN r.result_num <= remainder THEN 1 ELSE 0 END
           + v.base_result as result_val
      FROM base_vals v CROSS JOIN result_rows r
);
GO

You run the same queries as above thusly:

SELECT * FROM fn_split_into_ints(10,3);
SELECT * FROM fn_split_into_ints(49,6);
SELECT * FROM fn_split_into_ints(2000,11);

Results are the same. To answer one comment: this would allow you to pull result_val into a variable:

SELECT @result_val = result_val
  FROM fn_split_into_ints(49,6)
 WHERE result_num = 6
;

EDIT: Changed function name, so function and procedure can co-exist (if you'd want them to, for some reason).


I think using separate wishlists for separate users will be your best option. It will be efficient and will not contain millions of rows.


Ways to stop access to a SQL express database

There is no way you can secure a SQL Express database against access from a computer administrator. In the typical SQL Express scenario this means that the user of your application (which is usually a also local administrator) can see everything in the database and it can also modify anything, no matter what grants or permissions you establish.

If you want to protect the IP in the database (which is the usual reasoning behind this ever repeating question) you need to prevent access to the physical database which you can only do by no releasing the database to start with. In other words, use a web service instead (hosted by you), or a cloud based database like SQL Azure.


strange behavior with Turkish language setting

You need to write your application and test it on a case sensitive server, there is no workaround. Asking all your customers to deploy on case insensitive server collations is not the answer. Fix your code, don't break your customers.


Percona pt-table-sync Can't determine Master

pt-table-sync --execute --verbose --databases Test -t dummy --no-foreign-key-checks h=192.168.14.5,u=checksum_user,p=checksum_password h=192.168.15.5

The above command successfully synchronized the data from Test.dummy on Master (192.168.14.5) to Test.dummy on Slave (192.168.15.5).


Getting multiplication of column values on a joined table based on condition

Here's how I solved it:

   SELECT PA.PAID_YEAR,PA.PAID_AMOUNT,EXP(SUM(LN(YR.RATE)))*PA.PAID_AMOUNT
     FROM YEARLY_RATES YR, PERSON_ACCOUNT PA
     WHERE YR.RATE_YEAR>=PA.PAID_YEAR
     GROUP BY PA.PAID_YEAR,PA.PAID_AMOUNT

EXP(SUM(LN(YR.RATE)))  is just a trick to get the multiplication of the column values as there's no ready function available in Oracle.


Clarification -- Disabling SQL Server 2014 Agent Job in AOAG Secondary replicas

just need to enable it on the current replica.

You will need to modify you sql agent jobs to include below step

CREATE FUNCTION dbo.fn_hadr_group_is_primary (@AGName sysname)
RETURNS bit
AS
BEGIN
DECLARE @PrimaryReplica sysname;
SELECT @PrimaryReplica = hags.primary_replica
FROM
sys.dm_hadr_availability_group_states hags
INNER JOIN sys.availability_groups ag ON ag.group_id = hags.group_id
WHERE
ag.name = @AGName;

IF UPPER(@PrimaryReplica) =  UPPER(@@SERVERNAME)
RETURN 1; -- primary

RETURN 0; -- not primary
  END;

GO

So you have to create the above function in all your replicas  and add as first step in sql agent jobs you wish to execute

If the replica is not primary,you can modify the job to Quit

This has been covered in depth here :
AlwaysOn Availability Groups and SQL Server Jobs, Part 7: Detecting Primary Replica Ownership


Login issues for the same SQL login on mirror server working fine on principal server

If logins are getting locked, that indicates the password is incorrect.  Either the server has the incorrect password recorded for the login, or the client is presenting the wrong password.

Since this is a mirroring environment, I'd bet the client is using the same password for both instances; meaning the password for the login on the mirror is not the same as the password for the login on the master.

Recreate the login on the mirror server with the known-good password, i.e. not the hashed version.

Ensure you get the sid correct (use copy-and-paste) from this query, ran against the principal server:

SELECT sp.name
    , sp.sid
FROM sys.server_principals sp
WHERE sp.name = 'login_name';

Use that sid in the CREATE LOGIN statement you run on the mirror:

CREATE LOGIN login_name
WITH PASSWORD = 'thepassword'
    , SID = 0x000000000000 --put the real sid here.
    , DEFAULT_DATABASE = tempdb;

The sid is only important because it links the login to the user inside the database.


Oracle Missing Comma error on check constraint

It's your regexp_like expression that is invalid. You're using your column as if it was a function name. The syntax is:

regexp_like(thing_to_check, pattern [, options])

You should write:

regexp_like(species_imagefile, '[A-Z0-9._%-]+.JPG', 'i')

'i' is for case-insensitive matching.


Is a update-only-once-row table worth sharding?

Is there a problem?  Or are you expecting to grow significantly?  10M/day = 120/second, which is high, but not necessarily the limit.

innodb_flush_log_at_trx_commit = 1 is the safest, but it is the slowest.  A value of 2 will give you a boost in performance.

Batching INSERTs is also a performance boost; however you may not be able to do so because of how your app works.  Also combining statements into a transaction would help -- if practical.

What you have described of your app sounds like all the activity is concentrated near the 'end' of the one table.  This implies that there is very little I/O (other than transaction logging, and that was what I was addressing above).

Back to your question...

Sharding across N servers will cut the I/O, locking, CPU, etc by a factor of nearly N.

The process that decides which shard to go to should be another machine, and the shards should be alone on each of the machines.

A problem with sharding to handle growth is what to do when you need more than N machines.


Preparing a query to paste into excel to get growth by month

Well I could crate a dynamic pivot. Maybe it can help you guys with your own tasks someday:

DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(name)
                    from TblDataBaseSize
                    group by name
                    order by name
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

set @query = 'SELECT date,' + @cols + ' from
             (
                select month(date) as date, name, convert(int,data_size_mb) as data_size_mb
                from TblDataBaseSize
            ) x
            pivot
            (
                avg(data_size_mb)
                for name in (' + @cols + ')
            ) p '

execute(@query);

The reason that i needed to create the dynamic is because I dont know how many databases I will have. This one above will create for a fixed numbers of databases ( or something different ).

select *
from
(
  select name, month(date) as mes, data_size_mb
  from TblDataBaseSize where name not in ('master','model','msdb','tempdb')
) src
pivot
(
  avg(data_size_mb)
  for name in (
[DB1], [DB2]    ,[DB3]  ,[DB4]  ,[DB5] ...
)

) piv;

I must say it didn't help me with anything because excel still cant understand what I need haha but it was a cool challenge.


If "you want to send it to the client with the same format", then it is reasonable to treat it as "opaque".

That is, store it as a string with no meaning or structure that you care about.  And it does not matter (within limits) what datatype you use.  VARCHAR(99), TEXT, BLOB -- any of these would suffice.


Use sub-query, consider the two outputs are temp1 and temp2:

SELECT
    sum( usercount ),
    `year`
FROM
    ( SELECT * FROM  temp1  UNION ALL SELECT * FROM  temp2  ) AS temp1
GROUP BY
    `year`

Edit   As @ypercubeᵀᴹ mentioned in the comment, UNION ALL will avoid removing the duplicated rows from temp1 and temp2.

Also if your tb and tb_old use the same sciper for the same user,
you may need to UNION before GROUP BY

SELECT
    `year`,
    count( sciper ) AS usercount
FROM
    ( SELECT sciper, YEAR ( `date` ) AS `year` FROM massfacturation_old
        UNION
        SELECT sciper, YEAR ( `date` ) AS `year` FROM massfacturation ) AS temp1
GROUP BY
    `year`

In the above query, for every year and sciper combination, the temp1 will only have one row.


What problems will I get creating a database per customer?

This solution is called a multi-tenant design where each tenant (customer) has their own database. Given that, there are some other considerations to the alternative approach which is a single database:

With a single database, everyone must be on the same version no matter what. It isn't possible to upgrade some customers and not others. This can be problematic if a customer wants a hotfix of an application that isn't ready for wide release.
With a single database, when you do an upgrade, every client is down. If something goes wrong, every client is screwed.
With a single database, it is much more difficult to throttle resources. I.e., if one client is hammering the database, it is harder to give them more resources separate from everyone else.
It is much more difficult to allow users to host their own versions of your application. If you are building a solution that will be used by large enterprises, this is often a non-starter. Their IT department wants complete control over access to the system.
It is probably cheaper to scale out databases rather than scale them up. I.e., having to invest in faster hardware to host one database to rule them all is probably more expensive than being able to scale customers out to smaller, less expensive database servers. I can't say this one definitively because it depends greatly on the server software. If you stick with MySQL, this is probably true because the licensing costs are negligible. However, if you move up to SQL Server for example, scaling out becomes much more expensive unless you use a VPS environment and the cost-benefit of scaling up vs. scaling out changes. I can say, however, that once your database gets very large, management requires ever-greater levels of expertise. Very large databases require playing around with multiple filegroups and pushing certain indexes to different spindles to get better performance. In short, they get can complicated very quickly.

Having separate databases does mean you have to build an update mechanism which matches the database version with the application/site version. However, separate databases do provide superior isolation of data and IMO have a lower cost of hosting. It isn't a solution for all scenarios. If your system were never going to be hosted outside of your hosting and needed to scale up in customers rapidly and having all users on the same version of the application and database schema was desirable, then certainly having a single database is a better approach.


SQLIOSIM fails to run when using UNC path

Well, technically I'd solved the problem, although there's still question about weird sqliosim behavior.

It appears that sqliosim do creates it's files in shared NAS folder (sqliosim.mdx, sqliosim.mdx) but for some reason I can't explain, it fails to resize files and aborts.

Workaround:
Create files before sqliosim run, with exactly the same size it expects,
I.e:
fstool file createnew \\SERVER\shared FOLDER\sqliosim.ldx [size in BYTES]

Repeat the same action for sqliosom.mdx
Now run sqliosim and make yousrlf a coffee. :)

Thank you.
Sam


Upgrading 12c to 18c or 19c should not be a problem. I don't think that 19c is that different from 12c. The dbua tool should be able to do the upgrade. You still need to test with a test database. But running the dbua tool should work. Going from pre-12c to 19c is a much bigger issue depending on what features are used.


Database Design for a Graph (Network Topology)

There is no great way for modeling a graph in a relational database. They are two different data models. One says data "is really" sets of tuples. The other says it "is really" nodes connected by edges. These are different. Although any real-world scenario can be represented in either model the affordances of each are different. If the use-case is to perform an operation on multiple items that conform to a predicate (e.g. update .. where..) the relational model will, generally speaking, be more convenient. For use-cases involving following connections between items ("people who bought .. also bought ..", "you and Andi have 17 friends in common") the graph model is likely to work better. A relational DBMS makes implementing the former easy and the latter more difficult, and vice versa for a graph DBMS.

There are a few well-trodden relational implementations for the specific sub-set of graphs that are trees.

When implementing a solution in a RDBMS we only have tables to work with. So one has to decide which tables are needed to implement a generalized graph. Are the items of interest in the problem domain simply "things" which can be modeled as a single Node table and all the values of interest in a NodeAttribute table? This is analogous to an EAV, but in a graphy kind of way. Generally this is not useful unless we are concerned purely with the connectivity of nodes.

Usually we want to group things into classes and apply different constraints to each class. We could add a Class attribute to the Node table. This still leaves all the hard work of filtering rows of specific Class, and navigating between classes, to user code.

Relational databases are designed to have one table per class. If we have multiple classes of nodes then each should have its own table. If, as in your question, there are sub-classes of nodes then the problem can be restated as "how to model sub-classes in a relational way?" This is now a problem of table inheritance, about which quite a lot has been written. Some DBMS implement this natively; SQL Server does not.

  how do I query this efficiently?

The same way you query any other relational database. Good indexes that support the queries. Understanding the implementation and referencing only those objects required to satisfy the query's needs. If you have 30 types of nodes and 5 types of edges it is unlikely that all will be required in every query.

The discussion above for node tables applies equally for edge tables.

There are hybrid DBMS which allow the representation of data in both graph and relational ways. Starting with 2017 SQL Server introduced graph extensions. Sadly the MATCH clause was little more than syntactic sugar on SQL-89 joins redux. No graph-like affordances were "supported in the first release", though the language was encouraging.

With SQL Server 2019 transitive closure is now supported. One can only hope polymorphism is not far behind.


Delete records if not present in subquery

Use the "not in", and don't refer to the outer query:

delete from prices
where (sku,id) Not in (
    select p2.sku, p2.id
    from prices p2
        where p2.valid_to >= CURRENT_DATE
        and p2."type" = 'regular'
)


This may sound naive, however, a partitioned table is essentially no different from any other table from the outside:

select * from non-partitioned-table;

will be no different than a:

select * from partitioned-table;

Hence, for your application, nothing changes.

The big difference is the speed at which a query can be run against a table when using an appropriate where clause.

For example: if you have 1000 lego blocks, equally distributed between 3 different colors, and you are looking for one that you know is green and has your name written on it, you are only going to "partition" your search through the legos by looking only at the GREEN lego blocks. You are only going to look through 1/3 of your whole set.

Similarly, if you partition a table according to a certain column, date is always a good example, and you are trying to find an entry in that table where the approximate time is known, you mysql will only attempt to search only in a subset (a partition if you will) of the whole table. So to answer your question: ensure that if you DO use partitioning, that the majority of your queries will include a where clause referring to the partitioning key.

If my lego example was to abstract, or anything else sounds wierd: i would advice reading up on: http://dev.mysql.com/doc/refman/5.5/en/partitioning-overview.html


Think of it this way -- REORGANIZE PARTITION reads the source partition(s) and creates the destination partition(s), putting each row wherever it belongs. If the source range and destination range are identical (but broken up differently), then nothing can get lost.

Some comments on the schema:

PRIMARY KEY (`idMsg`,`idVeh`,`tmMsg`),

idMsg is unique; tmMsg needed to be added because it is the "partition key".  But is there any reason to include idVeh?  (There may be, bot note each of your 4 secondary keys will include all 3 of these columns -- somewhat bulky.)

KEY `X3` (`tmMsg`),

It is usually useless to have the partition key at the start of an index.  (Again, you may have a good reason; I am suggesting that this might be a useless index.)

KEY `X4` (`idVeh`,`tmMsg`,`idTrigger`)

Unless you are using = when testing tmMsg, this index is unlikely to get to the idTrigger column.  (Again, I am asking you to justify it; there could be a SELECT that shines because of this index; but it seems unlikely.)

`amLat` double DEFAULT NULL,
`amLong` double DEFAULT NULL,

Are you tracking fleas on dogs?  16 significant digits is overkill.  See here.

About the only benefit in PARTITION by a time is to purge old data. (DROP PARTITION is a lot less invasive than DELETE.)  If you think you are getting some other performance benefit, let me know; I may argue with you.


It doesn't look like there is compression like you are talking about in the TDS

A question that might help you: Is data retrieved from SQL Server compressed for transmission?


Efficient count query for consecutive time windows

First time occurrence is not clear

select min(EventDateTime), max(EventDateTime), count(distinct(ProductID ))
     , datepart(YY, EventDateTime), datepart(MM, EventDateTime)
     , datepart(DD, EventDateTime), datepart(HH, EventDateTime)
from table
group by datepart(yy, EventDateTime), datepart(MM, EventDateTime)
       , datepart(DD, EventDateTime), datepart(HH, EventDateTime)


How to view contraints of a cloumn in postgresSql?

PostgreSQL exposes the constraints as well as other schema details through information_schema, so to find all the constraints for the table, query table_constraints, for example:

SELECT constraint_name FROM information_schema.table_constraints
    WHERE table_name='my_table' AND constraint_type='UNIQUE';

To find which columns are involved into the constraints, check constraint_column_usage.


How to search double quotes in fulltext search in sql

Since the content of 'Keywords' is interpreted by the RDBMS instead of being read literally, your code is vulnerable to SQL Injection . This is also why you get the syntax errors.

You must sanitize the content of 'Keywords'. There's an answer on Stack Overflow : here.


Relation between first name and last name

I think the example is a very theoretical one. I have never seen any real world examples of separate FirstNames and LastNames tables. Of course you can normalize your database this way, but it wouldn't make much sense in most of the cases.

Back to the relation. I think there are two relations here:

First name - Person 1:1
Last name - Person 1:1

I don't think there is a relation between the first and last name.

We can take a slightly different approach with one surname(last name) and multiple "first" names. Then there can be a many to one relationship between first names and person.

However, in most cases first name and last name are just attributes of one person. To solve the issue of mail generation, you can have 3 columns: first name, middle name and last name. Then you use only first and last names for mail generation and store the middle name for other purposes.


Trace binding variables from UPDATE statement

Set up environment:

SQL> create table t1 (c1 number, c2 number);

Table created.

SQL> insert into t1 values(1, 1);

1 row created.

SQL> commit;

Commit complete.

SQL> variable B1 number
SQL> variable B2 number

SQL> exec :B1 := 1;

PL/SQL procedure successfully completed.

SQL> exec :B2 := 2;

PL/SQL procedure successfully completed.

SQL> update t1 set c2 = :b2 where c1 = :b1;

1 row updated.

SQL> rollback;

Rollback complete.

Find my SQL and the captured bind values:

SQL> select prev_sql_id from v$session where sid = sys_context('userenv', 'sid');

PREV_SQL_ID
-------------
94abc43hj1btv

SQL> select sql_text from v$sql where sql_id = '94abc43hj1btv';

SQL_TEXT
--------------------------------------------------------------------------------
update t1 set c2 = :b2 where c1 = :b1

SQL> select name, position, datatype_string, value_string, was_captured
  2  from v$sql_bind_capture where sql_id = '94abc43hj1btv';

NAME         POSITION DATATYPE_STRING      VALUE_STRING         WAS
---------- ---------- -------------------- -------------------- ---
:B2                 1 NUMBER                                    NO
:B1                 2 NUMBER               1                    YES

Problem: the value of :B2 was not captured because it is not part of a WHERE/HAVING clause.

Enable SQL tracing in my session for this specific SQL statement and find the trace file:

SQL> alter session set events 'sql_trace[sql: 94abc43hj1btv]  level=12';

System altered.

SQL> select TRACEFILE from v$process where addr = (select paddr from v$session where sid = sys_context('userenv', 'sid'));

TRACEFILE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_2231.trc

Run the SQL again:

SQL> update t1 set c2 = :b2 where c1 = :b1;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from t1;

        C1         C2
---------- ----------
         1          2

And the content of the trace file:

PARSING IN CURSOR #140116042834336 len=37 dep=0 uid=85 oct=6 lid=85 tim=437775551 hv=3775967035 ad='63073648' sqlid='94abc43hj1btv'
update t1 set c2 = :b2 where c1 = :b1
END OF STMT
BINDS #140116042834336:

 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=00 csi=00 siz=48 off=0
  kxsbbbfp=7f6f4ef446c8  bln=22  avl=02  flg=05
  value=2
 Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=00 csi=00 siz=0 off=24
  kxsbbbfp=7f6f4ef446e0  bln=22  avl=02  flg=01
  value=1

:B2 is the first bind variable in the SQL text, so it is labelled as Bind 0, and you can see the actual value as value=2.

You can set the above systemwide (alter system instead of alter session), but each server process will have a seperate trace file and you will need to collect the required information from them.


Is there any doc listing standard  Oracle character sets correspondencies?

I believe this is covered by the documentation here


SQL 2008 R2 performance problems - mixed signals on disk performance

Not wanting to be one of those OPs who just disappears without providing closure here's an update..

Despite receiving multiple assurances from infra that the vhost was in no way overloaded and that contention for physical resources couldn't possibly be the problem they eventually moved multiple VMs off the host to another one and the symptoms disappeared once that was complete. So either the problems were caused by resource contention on the host bottlenecking the VM's performance or the symptoms that just coincidentally looked like resource contention just so happened to coincidentally disappear at the same time that load on the vhost was reduced.

So many thanks to everyone for the helpful comments/pointers!


Replicate multiple times a set of rows with unique column in a table

WITH gen AS (
    SELECT 1 AS num
    UNION ALL
    SELECT num+1 FROM gen WHERE num+1<=5
)
INSERT INTO test1 (name,value,type)
SELECT 'ins_' + CONVERT(varchar,num) + '_'  + CONVERT(varchar,id), value, type
FROM gen
CROSS JOIN test1

Some explanation provided by the OP, added so others can adapt the solution:

Actually used CTE as a generator of integers (called
'fnTally') which he later used in CROSS JOIN to produce cartesian
product of those two tables - the source one which is 'test1' and
generated by CTE called 'gen' (a combination of every row from two
tables). The only further optimization here would be to use cCTE
(cascade CTE introduced by Itzik Ben-Gan).


Allow remote access only from certain IP addresses

I use Windows Firewall and Powershell for this.  Unless of course you want to get something like ScaleArc to act as a reverse proxy.  Your options are really going to be rather limited to something that utilizes one of those technologies.  Firewall/IPSec or a reverse proxy.

This is the outline of the script I use to verify ports the rule exists in this environment and add it.  Specify -RemoteAddress to limit access to only certain IPs:

New-NetFirewallRule Docs

-RemoteAddress<String[]>
Specifies that network packets with matching IP addresses match this rule.
This parameter value is the second end point of an IPsec rule and specifies the computers that are subject to the requirements of this rule.
This parameter value is an IPv4 or IPv6 address, hostname, subnet, range, or the following keyword: Any.
The acceptable formats for this parameter are:
-- Single IPv4 Address: 1.2.3.4
-- Single IPv6 Address: fe80::1
-- IPv4 Subnet (by network bit count): 1.2.3.4/24
-- IPv6 Subnet (by network bit count): fe80::1/48
-- IPv4 Subnet (by network mask): 1.2.3.4/255.255.255.0
-- IPv4 Range: 1.2.3.4 through 1.2.3.7
-- IPv6 Range: fe80::1 through fe80::9
Note: Querying for rules with this parameter can only be performed using filter objects. See the Get-NetFirewallAddressFilter cmdlet for more information

.

Powershell Script:

##Internal SQL Endpoint for AGs to communicate together. Needed on all nodes.
$LocalPortNumber = '5122'
$PortDisplayName = 'SQL-AG-Endpoint'
$Protocol = 'TCP'
$PortCheck = Get-NetFirewallPortFilter | where {$_.LocalPort -eq $LocalPortNumber}
if ($PortCheck){  ##Check if exists))
write-host "Port $($LocalPortNumber) for $($PortDisplayName) already found. Not adding Firewall Entry" #-ForegroundColor DarkGreen
}
ELSE
{
write-host "Port $($LocalPortNumber) is not found. Adding now"
New-NetFirewallRule -DisplayName "$($PortDisplayName)" -Direction Inbound –LocalPort $($LocalPortNumber) -Protocol $($Protocol) -Action Allow
}


UPSERT statement never completes, infinite loop?

The immediate cause of the infinite loop is this. Per documentation:

  Note in particular that EXECUTE changes the output of GET DIAGNOSTICS, but does not change FOUND.

But there's more:

CREATE FUNCTION upsert(_ins text, _upd text)
  RETURNS int AS
$func$
DECLARE
   loop_ct int := 0;
   row_ct  int;
BEGIN
LOOP
   IF loop_ct > 1 THEN   -- terminate on 2nd loop
      RETURN 0;
   END  IF;

   EXECUTE _upd;         -- try update
   GET DIAGNOSTICS row_ct = ROW_COUNT;
   IF row_ct > 0 THEN    -- terminate if update successful
      RETURN row_ct;
   END IF;

   BEGIN                 -- try insert
      EXECUTE _ins;
      GET DIAGNOSTICS row_ct = ROW_COUNT;
      RETURN row_ct;
   EXCEPTION WHEN unique_violation THEN
      -- insert violated unique constraint, keep looping
   END;
   loop_ct := loop_ct + 1;
END LOOP;
END
$func$  LANGUAGE plpgsql;

Major points

GET DIAGNOSTICS instead of FOUND, as explained at the outset.
Don't start another block around the UPDATE. Expensive and no use.
I added a feature: on success, the query returns the number of rows affected - at no additional cost since we get the row count anyway. May or may not be useful.

The rest is unchanged, just reformatted.

I have to add a warning about SQL injection. Passing code as text is very susceptible. The code cannot be sanitized. A function like this must be handled with care. Certainly should not be created with SECURITY DEFINER. Related answer:

Issue with composite type in an UPSERT function


How to make MySQL join one-to-many with lowest value?

As suggested @ypercube it can be done by grouping by domains.id, domains.domain and selecting min(subscriptions.period). Complete query:

 select
        domains.domain,
        domains.id,
        MIN(subscriptions.period) as period

        from
            domains
        inner join urls ON urls.domain_id = domains.id
        inner join positions ON positions.url_id = urls.id
        inner join phrases ON phrases.id = positions.phrase_id
        inner join users ON users.id = phrases.user_id
        inner join subscriptions ON subscriptions.id = users.subscription_id

        GROUP BY domains.id

SQL Fiddle: http://www.sqlfiddle.com/#!2/438c1/2


where clause return empty row in view mysql

Assuming that the two date columns are indeed DATE columns, the problem is that you are not using proper format in the date literals. Use ANSI format:

SELECT *
FROM V_BALANCE
WHERE DATE BETWEEN '2016-07-24'
               AND '2016-08-03' ;

See the documentation for other options: Date and time literals.

If the two date columns are not DATE but some CHAR or VARCHAR, then the issue is there. Convert the columns to the proper date type, otherwise the comparisons will work incorrectly.


I found a solution for my problem. I can build a "group-index" and skip/take only the elements in the index. See the following script:

SELECT *
FROM
(
   SELECT x.*, ceil((rank() over (order by x."Timestamp") +
       count(1) over (partition by x."Timestamp"))/6) as pageindex
   FROM
   (
      SELECT substr("Timestamp", 1, 16) "Timestamp", "Action", count(*)  "Count"
      FROM "MyTable"
      WHERE substr("Timestamp", 1, 13) <= substr(CURRENT_DATE, 1, 13)
      GROUP BY substr("Timestamp", 1, 16), "Action"
      ORDER BY substr("Timestamp", 1, 16) DESC, "Action"
   ) x
)
WHERE pageindex > (:page - 1) * :pageSize and pageIndex <= :page * :pageSize


Step 1: Filter (WHERE) to get just the desired rows.  Producer2 is excellent for that.

Step 2: Now do the GROUP BY.  Well, the rows from Step 1 are not ordered by SectorID (because of the "range" on PeriodStart left things shuffled.  This could be done in one of two ways:  Via some hash in memory, or by sorting (probably in RAM) and doing a simple pass through the data.  It is hard to say which is faster.

You should move away from MyISAM.  This particular query will probably run faster with InnoDB because of the way indexes are handled between the two engines.

Your query is an example of one thing I talk about in my index cookbook -- "an index cannot handle both a range (PeriodStart...) and a GROUP BY.

I am suspicious of "Using index condition".  That is a synonym for "ICP" or "Index Condition Pushdown", wherein the high level handler lets the Engine do some of the index work (more efficiently).  However, I thought that MyISAM was not updated to do any ICP work.  If I am correct, then this is another reason for moving to InnoDB; it does benefit from ICP.

If this is a big table, we can talk about another way to speed up the query (perhaps 10x) -- Summary Tables .


Run several commands against data in text file (SQL Server 2008 R2)

Building on Rob's answer here's some sample code to do the work.

CREATE TABLE #Keys (YourKey int)

BULK INSERT #Keys FROM 'C:\YourFile.txt'

DELETE FROM YourTable
WHERE Id in (select YourKey from #Keys)

This will need to be cleaned up with your table and column names, but it'll do the delete as a single operation.


pg_hba.conf and postgresql.conf in old installation directory

Until postgres version 9.5 the upgrade procedure was to

create a new fresh database cluster using the new version
make a full dump of the old cluster
read the dump into the new cluster.

the selection of the cluster depends on the PGDATA environment variable.
the version of the cluster software version depends on the PATH environment variable.
Make sure they match your needs. The setting is done in the startup scripts and can be copied for the client.


unable to bring database online from recovery pending

The first issue was someone denied the SQL Server Services in the NDF and MDF files and once that denied was removed and granted appropriate access, the first issue was resolved.
The second solution was that corrupt NDF file had no data so I removed it and database was back online.


How likely is fragmentation on a table with 40000 products likely to affect performance

Based on the data that you have provided -

there are 67 total pages. Index fragmentation for such a small table would not affect the performance.  I would not worry about index fragmentation for the table that you have mentioned.

You should update your table statistics so that sql server can generate better query plan.

I would start my troubleshooting by

Running sp_Blitz: Overall Health Check

Read : Stop Worrying About SQL Server Fragmentation


I believe this is what you are looking for, using a window function:

select name,
    id,
    attr1,
    attr2,
    max(data_date) over (partition by id) as maxdata_date
from table1

Results:

name    id  attr1   attr2   maxdata_date
a       1   sals    ksdjf   2018-05-25
b       1   laskd   lskd    2018-05-25
c       2   klss    kjfe    2018-03-20
d       2   aow     lww     2018-03-20

http://sqlfiddle.com/#!18/109b5/1


Why dropping all foreign keys from a script is not working?

Here's a better script.

It doesn't use the awful INFORMATION_SCHEMA views. Among other things, these views do not expose foreign keys against unique constraints; only against explicit primary key constraints.
It doesn't place GO inside T-SQL, which won't work if you execute the command dynamically (since GO is a batch separator for interactive tools like SSMS, not a T-SQL keyword).
It doesn't have incorrectly copied apostrophes in place of single quotes (’ vs. ').
It deals with multi-column foreign key constraints (the code you have now assumes that all foreign keys are only defined against single columns).

The remainder is taken from my blog post, Four Practical Use Cases for Grouped Concatenation. It's script #4.

The drop is easy; just build a simple list of ALTER TABLE commands from sys.foreign_keys (I've also added commented-out filters that could be used to limit the scope to referenced tables matching a naming pattern or existing within a certain schema):

DECLARE @drop NVARCHAR(MAX) = N'';

SELECT @drop += N'
ALTER TABLE ' + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name)
    + ' DROP CONSTRAINT ' + QUOTENAME(fk.name) + ';'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS ct ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs ON ct.[schema_id] = cs.[schema_id];
-- WHERE fk.referenced_object_id IN
-- (SELECT [object_id] FROM sys.tables WHERE name LIKE N'%some pattern%')
-- WHERE fk.referenced_schema_id IN
-- (SELECT [schema_id] FROM sys.schemas WHERE name = N'some_schema')

PRINT @drop;

Don't execute that yet! Of course, you need to generate the CREATE commands while they still exist. Create is a little more complex. We need to generate the list of columns on both sides of the constraint, even though in most cases there is only one column (the multi-column situation is where grouped concatenation comes in real handy):

DECLARE @create NVARCHAR(MAX) = N'';

SELECT @create += N'
ALTER TABLE '
   + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name)
   + ' ADD CONSTRAINT ' + QUOTENAME(fk.name)
   + ' FOREIGN KEY (' + STUFF((SELECT ',' + QUOTENAME(c.name)
    FROM sys.columns AS c
    INNER JOIN sys.foreign_key_columns AS fkc
    ON fkc.parent_column_id = c.column_id
    AND fkc.parent_object_id = c.[object_id]
    WHERE fkc.constraint_object_id = fk.[object_id]
    ORDER BY fkc.constraint_column_id
    FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'')
  + ') REFERENCES ' + QUOTENAME(rs.name) + '.' + QUOTENAME(rt.name)
  + '(' + STUFF((SELECT ',' + QUOTENAME(c.name)
    FROM sys.columns AS c
    INNER JOIN sys.foreign_key_columns AS fkc
    ON fkc.referenced_column_id = c.column_id
    AND fkc.referenced_object_id = c.[object_id]
    WHERE fkc.constraint_object_id = fk.[object_id]
    ORDER BY fkc.constraint_column_id
    FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') + ');'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS rt -- referenced table
  ON fk.referenced_object_id = rt.[object_id]
INNER JOIN sys.schemas AS rs ON rt.[schema_id] = rs.[schema_id]
INNER JOIN sys.tables AS ct -- constraint table
  ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs ON ct.[schema_id] = cs.[schema_id];
--WHERE rt.name LIKE N'%some pattern%'
--WHERE rs.name = N'some_schema'

PRINT @create;

When you're happy with the output (keep in mind that PRINT is limited to 8K, so it may look like the command is truncated), add this to the end, and run it again:

EXEC sys.sp_executesql @drop;

-- drop and re-create the table here, whatever that needs to entail

EXEC sys.sp_executesql @create;


Combine two event tables for multiple timelines into a single result set

In addition to the solution of the simple case, add a PARTITION clause to the window functions in the inner query, to get group numbers per partition (per "timeline"). Combine group numbers with the respective timeline (id in your example) keep partitions separate in the second step:

SELECT id, ts
     , min(foo) OVER (PARTITION BY id, foo_grp) AS foo
     , min(bar) OVER (PARTITION BY id, bar_grp) AS bar
FROM (
   SELECT id, ts, f.foo, b.bar
        , count(f.foo) OVER (PARTITION BY id ORDER BY ts) AS foo_grp
        , count(b.bar) OVER (PARTITION BY id ORDER BY ts) AS bar_grp
   FROM   foo f
   FULL   JOIN bar b USING (id, ts)
   ) sub
ORDER  BY 1, 2;

Result as requested (except with id first).
SQL Fiddle

Your attempt to adapt the previous solution was very close. It didn't work because of PARTITION BY f.id / PARTITION BY b.id instead of PARTITION BY id. You really want the combined id to include missing rows in the result - that's where the last non-null value has to be filled in for the missing (NULL) value.

If performance is your paramount requirement consider a server-side function like demonstrated in the previous answer.


I don't think this is possible natively. The Cassandra role-based permissions as shown here show that granting a user MODIFY includes both UPDATE (which is effectively INSERT) and DELETE. You can't select which one you want.

Check out this blog entry I found, though and see if there's anything useful there. It looks like you could do read or write only permissions. Maybe if you can do write-only permissions you can limit the user from being able to delete a table or keyspace.

Hope this helps point you in the right direction.

-em


SQL Server 2008 R2 with huge amount of transactions

It is difficult to estimate server load based on "number of transactions" because cost of a transaction can vary greatly depending on the design of your DB and the nature of the query.  Enterprise and Datacenter editions are your versions to consider for situations with high volume and high scalability. Microsoft has comparison charts showing what the processor/memory limitations are for each, extra features, and virtualization restrictions. (Search: SQL Server version comparison)

What type of application is this supporting and for what business type?  Will it be performing strictly transactional OLTP tasks or are you looking to support reporting and analysis with (a separate instance of) the same product?


Get memory usage of mysql query during runtime

In MySQL 5.7, assuming you're using mostly INNODB tables, you can see how much memory is currently being used by running this query:

SHOW ENGINE INNODB STATUS

You can see the maximum amount of memory your DB will ever use by running this query:

SELECT ( @@key_buffer_size
+ @@query_cache_size
+ @@innodb_buffer_pool_size
+ @@innodb_log_buffer_size
+ @@max_connections * (
    @@read_buffer_size
    + @@read_rnd_buffer_size
    + @@sort_buffer_size
    + @@join_buffer_size
    + @@binlog_cache_size
    + @@thread_stack
    + @@tmp_table_size )
) / (1024 * 1024 * 1024) AS MAX_MEMORY_GB;


SQL Query Killed, remains in Kill/Rollback with estimated time increasing

How large is your database? It's not uncommon for rollback transactions to take considerably longer than the operations they are trying to do, especially if affected objects have dependencies.

Another important question is what environment are you running this on? I'm assuming you're not running it on live, which leads to a couple more questions, specifically:
-How long can you afford to wait for it?
-Can you kill the database and restore a backup?

If you can't wait for an long or potentially unknown amount of time, your best bet might be simply restoring a backup. If you don't want to do that, you might want to wait for a while longer. You can use the sys.dm_exec_requests DMV to get information on wait times and whether or not it's being blocked, and on what might be blocking it.

SELECT *
FROM sys.dm_exec_requests

After you find columns you might find useful, you can revise the select statement to get rid of the spam.

For more information, see this post by Paul Randal (SQL MVP and author) on the shrink command: http://www.sqlskills.com/blogs/paul/why-lob-data-makes-shrink-run-slooooowly-t-sql-tuesday-006/


Based on the execution plan, and the data distribution, MySQL would decide to scan the table or one of the indexes to get the required data.

If the data is to be read from an index, and the index is corrupted, it is possible that no data or wrong data is returned.

Optimizing the table will rebuild the table and its indexes. So, if an index was corrupted, it will be fixed after the optimization.

HTH


how to access my password in SSIS 2005 by VB.NET

Given your symptoms, I believe you have changed your PackageProtectionLevel from the default setting of EncryptSensitiveWithUserKey to DontSaveSensitive This is a good thing in my world view but it does require that you now manage sensitive data.

The hackish approach you are using is to create a password Variable and then use that in an Expression as part of the Connection String. That works but you are now storing a password in clear text which is what the default PackageProtetionLevel was designed to protect against.

Fixing it "right" in my mind would involve storing that password as an external, secured resource. We always used SQL Server configurations in our packages and extended the base table with a column indicating whether this configuration was sensitive or not. In production, we, the developers, only had access to rows that were set as insensitive. The service account which ran all the packages had no such restrictions.

Alternate approaches are to store these configuration values in an XML file, registry key, environment variable or a parent package.

You can also skip the configuration route if you know your packages will only ever be launched through method X (batch file, Agent Job, etc). In that case, simply provide the password as a run-time value to the invoking process and all will be well.


What exactly do database passwords protect?

The passwords you're talking about are passwords for MySQL users.  You don't really set a password for a database or table.  You can effectively implement different passwords for different DBs by creating multiple users with different passwords and only granting access to specific databases for each user.  When people say that you should set a password for the database, they probably mean that you shouldn't use users with no password, which is possible, but shouldn't be done due to the obvious security concerns.

Whether or not you "should" use multiple users depends on your situation.  If you have multiple databases that are used by independent applications, it is a good idea to create a user for each application to help isolate any risks from spreading between applications via the database.  If you have one database that is used by different applications, you can use different users to help track stuff or lock down permissions.  If you have on application, you're generally safe with using just one user.

The main reason to use different users is to lock down permissions and prevent any errors/attacks from causing too much damage.  If you're performing a task that only needs to read data, using a user that only has SELECT permissions can prevent accidental deleting or modifying of data; even though the risk of such an event is very low, it is still good to have something to prevent it.  Using different users is usually only helpful when all of the users would see significant use.  In your situation, you could use multiple users, but it would probably become more of a nuisance for you to keep track of when you would use which user and which user is currently connected.


postgres pg_xlog explodes during set tablespace

When you move a table to a new table space and your wal_level is above "minimal", then the entire contents of the table are copied to the WAL stream.  That is why pg_xlog grows quickly.  Shrinking slowly could simply be because your archive_command can't keep up, and WAL files can't be removed until they are archived if archiving is on.  What are your settings for wal_level, archive_mode, and archive_command?


Oracle by default does not cache query and function result, but both cache do exist.

http://www.oracle.com/technetwork/articles/sql/11g-caching-pooling-088320.html

The importance of these: there is more than caching blocks. Even if you process everything in memory, why process the same requests again and again, if you already know the answer?

What you are referring to when mentioning autotrace and physical reads, is the buffer cache.

  "I have also noticed that the buffer cache does not gets invalidated
  even when I try to restart the server."

This is a false conclusion. When you restart the database, the buffer cache will be empty again. Post your test that confirms the opposite.