What is InnoDB Buffer Usage and how to clear it?

Assuming that your dataset is larger than your buffer pool, having it at 95% usage is not only normal, but a desired state. You want as much information as possible on memory- hardware and resources are there to be used- so that both next reads and writes can be done faster than having to access disk.

A different thing is if that memory usage is being used effectively or not: if your read hit ratio is high and if you can buffer writes effectively, which you can check by observing your number of disk IOPS (by querying InnoDB and OS statistics) and buffer pool content (available on the information_schema tables).

If you server is idle, do not worry about memory usage (again, assuming your dataset is larger than your buffer pool and that your buffer pool is smaller than your physical memory)- the buffer pool will fill naturally as you read and write data; and it will be  use more or less as a more complex version of an LRU policy (data used less will be evicted first). Unless you want to use that memory for something else, because it is not a dedicated server (so, lower your buffer pool usage), most people will envy you as having the buffer pool fully utilized is a desired state.

Please note that most operating systems will cache (files) in the same way- unless memory is used for other things, they will try to fit as much data as possible in memory.

Restarting the server will usually clear most of the buffer pool unless configured to be preloaded.


SQL Server Index Scan when using 'OR' operator

As you mentioned in your question, this kind of Google-style query isn't really what SQL Server is "good at."  Erik Darling talked about this exact query anti-pattern in his post The Only Thing Worse Than Optional Parameters….
All that aside.
It's possible to naturally get a seek with that type of query, but it's much more common to get the scan as you noticed.  Here's an example from the StackOverflow2010 sample database.
First I'll create these two helpful indexes:
CREATE NONCLUSTERED INDEX IX_DisplayName ON dbo.Users (DisplayName) INCLUDE ([Location]);
CREATE NONCLUSTERED INDEX IX_Location ON dbo.Users ([Location]) INCLUDE (DisplayName);
GO

Then I'll create a procedure similar to the one you have:
CREATE OR ALTER PROCEDURE dbo.sp_Test
    @SearchTerm nvarchar(100)
AS
BEGIN;
    SELECT TOP (50)
        DisplayName,
        [Location]
    FROM
        dbo.Users
    WHERE
        DisplayName LIKE @SearchTerm + '%' OR
        [Location] LIKE @SearchTerm + '%'
END;
GO

If I run that procedure with a fairly selective parameter, I'll end up with an index union plan.  If the parameter is less selective, a scan of one of the covering indexes is used instead.
DBCC FREEPROCCACHE;
GO
EXEC dbo.sp_Test @SearchTerm = N'Josh';
GO
DBCC FREEPROCCACHE;
GO
EXEC dbo.sp_Test @SearchTerm = N'S';
GO

Execution plans are here.
Note that this is true even if you write this as separate UNION queries directly.
One way to reliably get the index union plan, as mentioned in the linked post, is to add a FORCESEEK hint to the table you'd like to union on.
If I change the proc to this, I don't get scans on either plan:
CREATE OR ALTER PROCEDURE dbo.sp_Test
    @SearchTerm nvarchar(100)
AS
BEGIN;
    SELECT TOP (50)
        DisplayName,
        [Location]
    FROM
        dbo.Users WITH (FORCESEEK)
    WHERE
        DisplayName LIKE @SearchTerm + '%' OR
        [Location] LIKE @SearchTerm + '%'
END;
GO

The bigger issue with the query, as simplified in the question anyway, is that you are using TOP without an ORDER BY, which is likely to produce drastically different search results depending on which index is used.  Make sure your real query has an ORDER BY, or that this problem is accounted for in some way.


Single slave for multiple masters in AWS AuroraDB

I guess what you are looking for is here https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/AuroraMySQL.Replication.MySQL.html


SQL Server Management Studio (SSMS) is way too slow in its GUI

I also had the mystery slow down in v14 and then upgraded to 18.5  Post upgrade 18.5 worked fast...a few days (and reboots)  later it bogged down again. Network trace showed a ton of IP6 traffic which we have not configured but somehow became a default 'on' in Windows 10.

Disabled Ip6 on the ethernet adapter and all working fine now.


'CONCAT' is not a recognized built-in function name

CONCAT was introduced in SQL Server 2012; there is no way to make it work in SQL Server 2008 R2. From the documentation:

There's also no way to make it fail in 2012+, even with compatibility level. So have your people check SELECT @@VERSION; on both servers; you'll find that where CONCAT fails it is < 11. In order to make your code compatible with earlier versions, you'll need to use the standard string concatenation operator (+). I don't know how you would do this with a scalar function, unless you always used the exact same number of input strings and you change your code to use dbo.CONCAT() instead of CONCAT() (there will be scenarios where it matters, plus if your function does anything the native doesn't do, you want consistent behavior if/when you upgrade). So I wouldn't recommend that approach. You may also need to add NULL handling and other minor changes (impossible to tell you how to change your existing script exactly, if we can't see it).


Select tables on column entry in schema (with many tables)

In case you need assistance in merging all data, you could do it this way:

DO $DO$
DECLARE
  _tab text;
  _r record;
BEGIN
  FOR _r IN
    -- refine the following query according to your particular needs, if the following doesn't work well
    SELECT a.attrelid::regclass::text AS table_name
    FROM pg_attribute a
    JOIN pg_class c ON (c.oid = a.attrelid)
    WHERE a.attrelid IN (SELECT attrelid FROM pg_attribute WHERE attname ILIKE 'geom') AND -- any table with a "geom" column name
          c.relnamespace > 11 AND c.relkind = 'r'
    GROUP BY a.attrelid
    HAVING COUNT(*) = 3 -- only tables with three columns
    ORDER BY 1
   LOOP
     IF _tab IS NULL THEN
       _tab := _r.table_name;
       EXECUTE 'CREATE TEMP TABLE merged_multi_polygons (tablename text, LIKE '||_tab||');';
     END IF;
     EXECUTE 'INSERT INTO merged_multi_polygons SELECT '||quote_literal(_r.table_name)||', * FROM '||_r.table_name
             -- ||' WHERE size < 20' -- UNCOMMENT THIS LINE TO PRODUCE THE TABLE ONLY FOR SMALL-SIZED ENTRIES
             || ' LIMIT 1';
   END LOOP;
END;
$DO$;

SELECT * FROM merged_multi_polygons WHERE size < 20;


Efficient query to get last row group by multiple columns

The simplest way in Postgres is with DISTINCT ON:

SELECT DISTINCT ON (1,2,3,4)
       sp.game_id, sportsbook_id, spread_type, spread_duration, game_update_count
FROM   spreads sp
LEFT   JOIN schedule sch USING (game_id)
WHERE  date >= '2012-01-01'
AND    date <= '2012-01-02'
ORDER  BY 4,3,2,1, game_update_count DESC;

Details:

Select first row in each GROUP BY group?

The numbers are just syntax shorthand referring to the ordinal position of SELECT items.

If game_update_count can be NULL, you'll want game_update_count DESC NULLS LAST.

PostgreSQL sort by datetime asc, null first?


Postgres – how to alter the WHERE clause of a postgres index

No, you cannot alter an index. Because, even if PostgreSQL allowed this, it would need to rebuild the index anyway. Unless it implemented very smart checks on the WHERE clause, which would serve quite rare use cases.

Having many indexes is also not optimal. Every time you run a query, the planner has to consider all existing indexes and pick up the best one. If you have several indexes, the planner takes more time. Not ages - but still, this delay affects each and every query involving that table.

My suggestion is... be patient and build a big index. You can use CREATE INDEX CONCURRENTLY to avoid locks.


MSSQL Server XE column client_connection_id accessible through system view/table for auditing?

You may use this and filter using program_name, login, IP address...

select c.connection_id from sys.dm_exec_connections c
join sys.dm_exec_sessions s on c.session_id=s.session_id


How can I set the index fill factor back to server default?

Through testing, it seems as though to get back to the default fillfactor on an index you need to drop and recreate it.  When you do an ALTER INDEX ... REBUILD, any unspecified parameters will remain the same in the index's metadata unless otherwise explicitly changed in the ALTER INDEX command.

drop index IX_YourIndex
on YourTable
go

create index IX_YourIndex
on YourTable(YourKeyColumn)
go

To verify this:

select
    name,
    fill_factor
from testdb.sys.indexes
where name = 'IX_YourIndex'

fill_factor should be set to 0 to denote that it is using the instance's default value.  (Note: 0 is for the default value, even if the instance configured fill factor is set to 95)


Create employee rows for each date in date range

Here is an example of using recursion to build a common expression date table and then cross join to the employee table.

--demo setup
declare @EmpTable table (EmpName varchar(50))
insert into @EmpTable(EmpName) values('emp1'),('emp2')

--use recursion to build a common table expression date table
;WITH dates ([Date])
AS (
    SELECT convert(DATE, '2019-01-01') AS [Date] -- Put the start date here

    UNION ALL

    SELECT dateadd(day, 1, [Date])
    FROM dates
    WHERE [Date] < '2019-01-15' -- Put the end date here
    )
SELECT e.empname
    ,substring(DATENAME(month, ([date])), 1, 3) + ' ' + DATENAME(day, ([date])) AS [DateDescription]
FROM dates d
CROSS JOIN @EmpTable e
ORDER BY empname, [date]
OPTION (MAXRECURSION 32767) -- Don't forget to use the maxrecursion option!

| empname | DateDescription |
|---------|-----------------|
| emp1    | Jan 1           |
| emp1    | Jan 2           |
| emp1    | Jan 3           |
| emp1    | Jan 4           |
| emp1    | Jan 5           |
| emp1    | Jan 6           |
| emp1    | Jan 7           |
| emp1    | Jan 8           |
| emp1    | Jan 9           |
| emp1    | Jan 10          |
| emp1    | Jan 11          |
| emp1    | Jan 12          |
| emp1    | Jan 13          |
| emp1    | Jan 14          |
| emp1    | Jan 15          |
| emp2    | Jan 1           |
| emp2    | Jan 2           |
| emp2    | Jan 3           |
| emp2    | Jan 4           |
| emp2    | Jan 5           |
| emp2    | Jan 6           |
| emp2    | Jan 7           |
| emp2    | Jan 8           |
| emp2    | Jan 9           |
| emp2    | Jan 10          |
| emp2    | Jan 11          |
| emp2    | Jan 12          |
| emp2    | Jan 13          |
| emp2    | Jan 14          |
| emp2    | Jan 15          |


SAP BO Freehand SQL date formatting (Oracle db)

to_date function solved the issue and now the condition is recognized by BO's freehand SQL.

DT_CURRENT_DT = to_date('10/15/18', 'MM/DD/YY')


Postgres 12: Are there performance differences between PRIMARY KEY(A, B) and PRIMARY KEY (B, A) on a table PARTITION BY (A)?

No, that will make no difference; the order of the columns in the index doesn't matter if both columns are compared with = in the WHERE condition (of course only if there is AND between the conditions).

The index will be scanned using both columns at the same time, and all that matters is the number of index entries that match the condition, which will be the same in both cases:

  a  |  b           b  |  a
-----+-----       -----+-----
   1 |  -1          -1 |   1
   1 |   1  <---    -1 |   2
   1 |   1  <---     1 |   1
   1 |   2           1 |   1  <---
   2 |  -1           1 |   2  <---
   2 |   1           2 |   1
   2 |   3           2 |   3

index on (a,b)   index on (b,a)


140GB DataMemory is no problem.
So the easiest change is to upgrade memory.
Adding nodes increases both memory and CPU capacity.
But adding nodes also changes the scalability, so could
be both beneficial and negative dependent on the scalability of
your application.


Log File over 400gigs...need to bring this database back to life

It sounds like no one took log backups on that database for a very long time.  Restore the MDF and try:

EXEC sp_attach_single_file_db @dbname='YourDB',
@physname=N'C:\Path\To\YourDb.mdf'
GO

Then set up regular backups for those databases, both full and log backups.  This is an article on log backups that might help.  This is a more general article on SQL Server backups.

Good luck!


How to Bulk Insert sqlalchemy subclasses

I figured it out so SQLALCHEMY has a parameter in bulk insert mapping called return_defaults

WARNING: This is straight from the docs return_defaults – when True, rows that are missing values which generate defaults, namely integer primary key defaults and sequences, will be inserted one at a time, so that the primary key value is available. In particular this will allow joined-inheritance and other multi-table mappings to insert correctly without the need to provide primary key values ahead of time; however, Session.bulk_insert_mappings.return_defaults greatly reduces the performance gains of the method overall. If the rows to be inserted only refer to a single table, then there is no reason this flag should be set as the returned default information is not used.

so then all you have to do is this

db.session.bulk_insert_mappings(model_classes['Apple'], apple_list, return_defaults=True)

it's still alot faster than db.session.add


Sync two Oracle production server databases

You could use database link to transfer data between servers.

Assuming you already have the link set up, replicating data can be as simple as:

insert into target_table
select * from source_table@link_name;
commit;

Of course this will be more involved if the data volume is large or you have complex schema.


Why is a duplicate record inserted when it should not be?

This turned out to be a red herring.  When pgadmin gets disconnected midquery, it returns the last error again even if it was an entirely different command! :)


Replication taking too much time between requests

What could/should we do?

Discard the assumption of synchronous replication and redesign your application accordingly. Replication was never designed for this. Latency of hours can happen and you should design accordingly.

None of the existing SQL Server technologies, including Availability Groups readable secondaries in synchronous commit mode, make a guarantee of synchronous propagation of changes to replicas/secondaries. the only place from where you can read back your own changes and be guaranteed to see your own changes is the publisher/primary, where the changes were made in the first place. Any expectation to the contrary is folly.


How to group rows while filtering out values per each group?

In Postgres this can be solved using distinct on ()

select distinct on (id) *
from namechanges
order by id, change_date desc;

This is typically faster than using aggregation or Window functions.

Online example


Must declare the scalar variable "@VarName"

Take out the go after the variable declaration.  It is causing SQL server to end the batch which makes your variable declaration go out of scope.

alter proc Proc1
@ProductID int
 as
 select ProductName
 from Products
 where ProductID = @ProductID

Here is some details from Microsoft on what the go keyword/statement does.


Deadlock detected on CREATE TABLE in PostgreSQL

Let's assume that the target table of the INSERT ("TableD") has a column with a foreign key to "TableC". An INSERT to this table must acquire a shared lock on "TableC" and will keep that lock until the transaction ends.

Say two concurrent transactions, T1 and T2, start with such an INSERT. Each takes a shared lock on "TableC".

Later on, in order to do a CREATE TABLE involving a foreign key to "TableC", T1 must acquire an access exclusive lock on "TableC".

Since T2 has already a shared lock on "TableC", T1 will be put to wait until T2 commits or rollbacks.

At this point, if T2 tries to create a table also involving a foreign key to "TableC" (which again implies acquiring an access exclusive lock on "TableC"), it must wait for T1 to commit or rollback, because T1 holds a shared lock on "TableC".

So now T1 is waiting for T2 to finish and T2 is waiting for T1 to finish: there's the deadlock.

To avoid this situation, an explicit and exclusive lock may be taken at the beginning of the transaction, or at least at the beginning of the sequence of instructions that can't be run safely in parallel by concurrent transactions.


How to build a database that contain only the delta from yesterday

ApexSQL Log can provide the list of transactions made in a specified time period. It has a time range filter where you can specify the last 24 hours, or any other period you want to read. Just make sure you provide enough transaction log backups to cover the specified time range.

ApexSQL Log can also create a redo script for all these transactions, so you can execute it against the new database where delta data will be stored.

What's equally important, if you want this to be a reoccurring job, you can schedule it using ApexSQL Log CLI and SQL Server Jobs.

Here's an example: Automating daily transaction log reading

Disclaimer: I work for ApexSQL as a Support engineer


Is there a way to run mysqldumpslow against slow queries logged to a table?

mysqldumpslow only works on text files, so you need to forget about using it.

The table mysql.slow_log is a  CSV table by default.

mysql> show create table mysql.slow_log\G
*************************** 1. row ***************************
       Table: slow_log
Create Table: CREATE TABLE `slow_log` (
  `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `query_time` time NOT NULL,
  `lock_time` time NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) NOT NULL,
  `last_insert_id` int(11) NOT NULL,
  `insert_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `sql_text` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'
1 row in set (0.00 sec)

mysql>

You do not want to query against a CSV table as it will do full table scans every time.

What you can do is create a copy of the table into another database and index start_time

DROP DATABASE IF EXISTS my_slow_log;
CREATE DATABASE my_slow_log;
CREATE TABLE my_slow_log.slow_log LIKE mysql.slow_log;
ALTER TABLE my_slow_log.slow_log ADD INDEX (start_time);
ALTER TABLE my_slow_log.slow_log DISABLE KEYS;
INSERT INTO my_slow_log.slow_log
    SELECT * FROM mysql.slow_log
;
ALTER TABLE my_slow_log.slow_log ENABLE KEYS;

Then, you can query against my_slow_log.slow_log by datetime values

I have discussed this before

Nov 18, 2011 : Create a custom log table in MySQL
Mar 30, 2012 : MySQL slow log configuration

Give it a Try !!!

CAVEAT: You would have to reload the data every time you want the latest slow logs.

In that event, if you want just want slow logs from midnight 3 days ago, do this:

TRUNCATE TABLE my_slow_log.slow_log;
ALTER TABLE my_slow_log.slow_log DISABLE KEYS;
INSERT INTO my_slow_log.slow_log
    SELECT * FROM mysql.slow_log
    WHERE start_time >= DATE(NOW() - INTERVAL 3 DAY) + INTERVAL 0 SECOND;
;
ALTER TABLE my_slow_log.slow_log ENABLE KEYS;

or just in the last 3 hours

TRUNCATE TABLE my_slow_log.slow_log;
ALTER TABLE mysql.slow_log DISABLE KEYS;
INSERT INTO my_slow_log.slow_log
    SELECT * FROM mysql.slow_log
    WHERE start_time >= (NOW() - INTERVAL 3 HOUR);
;
ALTER TABLE my_slow_log.slow_log; ENABLE KEYS;

UPDATE 2014-12-11 14:50 EST

Your comment

  This makes querying the data a bit quicker, but I'm interested in how to provide a summary of slow queries that I can then pass along to our developers

You could present this to your developers by requesting slow logs entries as follows:

Today's 20 worst performing queries

SELECT query_time,sql_text
FROM my_slow_log.slow_log
WHERE start_time >= DATE(NOW()) + INTERVAL 0 SECOND
ORDER BY query_time DESC
LIMIT 20;

Count of Slow Queries By the Hour over the Last 2 Weeks

SELECT hr,COUNT(1) SlowQueries FROM
(
    SELECT (unix_timestamp(start_time) - MOD(unix_timestamp(start_time),3600)) hr
    FROM my_slow_log.slow_log
    WHERE start_time >= DATE(NOW() - INTERVAL 2 WEEK) + INTERVAL 0 SECOND
) A GROUP BY hr;

Count of Slow Queries By the Minute over the Last 6 Hours

SELECT mn,COUNT(1) SlowQueries FROM
(
    SELECT (unix_timestamp(start_time) - MOD(unix_timestamp(starttime),60)) mn
    FROM my_slow_log.slow_log
    WHERE start_time >= ( NOW() - INTERVAL 6 HOUR )
) A GROUP BY mn;

Queries like these can give you stats when slow queries run and what the actual queries were.

You can send these queries to a text file and send them to the developers

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SELECT query_time,sql_text FROM my_slow_log.slow_log"
SQL="${SQL} WHERE start_time >= DATE(NOW()) + INTERVAL 0 SECOND"
SQL="${SQL} ORDER BY query_time DESC LIMIT 20"
mysql ${MYSQL_CONN} -ANe"${SQL}" > Slow_Queries_Report.txt


Should SSIS packages and SQL database be on same server?

Oh god no, do not use the SQL Server Destination.

At this point, the negligible performance gains are not worth the random error it may throw while loading data. OLE DB Destination is rock solid, provides excellent performance and doesn't suffer from the infernal errors you can run into with the SQL Server Destination. Both components can store data into SQL Server and as a bonus, the OLE DB Destination allows you to run your SSIS package from any server.

SQL Server Destination works by some very clever voodoo, as I understand it. Basically, the SSIS engine lays the data as it would load into SQL Server and whoosh it gets sucked in with a minimal amount of checking on the database side since SSIS super promises, double pinky swears, that the data is good.

The issue that plagued many users in the 2005/2008 days was that something would happen between thinking about inserting the data and actually inserting it wherein the only reported error would be something exceptionally insightful like "The destination component reported an error." However, if you immediately restarted the package - no changes to the source or destination database, the data would load just fine.

As for the architect level questions, if you design your packages using the OLE DB Destination, you can be infinitely more agile than using the SQL Server Destination. What is the one thing SQL Server wants more than anything else for good performance? Memory. The more information it can keep in RAM, the better performance you will have. How long does SQL Server keep that memory? Until it reboots or until things get really bad and it is forced to give back lest the server topple over.

SQL Server Integration Services is a high performance ETL engine. It is able to transform the data very quickly by keeping it all in memory instead of writing it to temporary files or tables. The more rows of data it can load into memory, the more concurrent changes it can process and your throughput is increased. However, the general pattern for SSIS resource consumption is that it's bursty. Sure, it might look like hungry hungry hippos as it starts up consuming everything it sees but it runs - maybe for minutes, maybe hours but as soon as it's done, all the resources are freed and available for general processing.

Since SSIS is an in-memory ETL tool and SQL Server really likes memory, they're gonna fight. Maybe not today. The server has 256 GB of memory, SQL Server is only using 180 so there's a good 30% left for OS level processes and SSIS plays nicely in that space.

Time passes and Oh, the business is growing and we have new web thingers beating on the database all day long and there are now all these analysts are going against the server with Access and pulling all the data just to filter it for "today" once it gets to their desktop. Now SQL Server is asking for more and more memory. You can change the max server memory setting in SQL Server to give a little more to the database engine but at a cost for other OS tasks, like SSIS (it doesn't run in SQL Server's memory space).

At some point, you might run into a situation where you've maxed the server's physical memory and trimmed down everything running on that box that isn't SQL Server and the only thing left is SSIS. So now what?

In an OLE DB Destination world, you stand up another instance (or use an existing, less taxed box), deploy/move your packages from server 1 to 2. As long as permissions and any file system dependencies exist on the new box, you're done.

In a SQL Server Destination world, you have to edit every package and change the destination component, test, go through any change management review process and then deploy.

Something is going to be your limiting factor for performance with SSIS: Memory, Network, Storage, CPU. If you have packages pulling terabytes of data across the wire, only to write kilobytes of data, yeah that subset of packages might need to run as close to the source as possible.

Novice SSIS developers can write some really memory intensive packages. For example, they need to bring together customers from server1.database1 and their orders, also in server1.database1. If they just use the tools, they might have two OLE DB Source components in there that bring all the customers and all the orders into memory. To join the data, they would then need to use a merge component. Oh, but that has a requirement for sorted data. No worries, there's a handy dandy Sort component right there in the toolbox so we add one of those to each of our sources and then wire up the join. At this point, we just pulled all that data into memory, sorted it all in memory and then halved our available memory into the "before the asynchronous/blocking component" and "after the blocking component" Categorizations here

No problem, they rewrite and realize they can add an explicit ORDER BY in the source, and mark it as such, and eliminate two SORTs but they still have to pay the penalty for the join.

Or, someone who actually writes queries sees what they're doing and says "let's use a single query to get this data. Let the database engine use its indexes to make this efficient"


How to remove identity specification for multiple tables

There is no built-in command to remove the identity property from a column in SQL Server. You may have to create a new table, copy all the data across, and then rename the table. This can be time-consuming and awkward, especially if you have things like foreign key constraints, schema-bound functions, indexed views and so on.

That said, there is a way to avoid copying the data at least, but again this will not work in all scenarios, particularly if you have foreign keys. Example follows:

Example table and data

CREATE TABLE dbo.tbl_acc
    (
    a1     NUMERIC (18) IDENTITY NOT NULL,
    a2     NUMERIC (18) NULL,
    a3     VARCHAR (4000) NULL,
    a4     VARCHAR (3999) NULL,

    CONSTRAINT PK_tbl_acc PRIMARY KEY (a1)
    )
GO
INSERT dbo.tbl_acc
    (a2, a3, a4)
VALUES
    (1, 'a', 'b'),
    (2, 'c', 'd');

Meta data transfer and rename

-- Copy of the source table specification,
-- but without the IDENTITY property
-- Note the PRIMARY KEY constraint name
-- also has to be different
CREATE TABLE dbo.tbl_acc2
    (
    a1     NUMERIC (18) NOT NULL,
    a2     NUMERIC (18) NULL,
    a3     VARCHAR (4000) NULL,
    a4     VARCHAR (3999) NULL,

    CONSTRAINT PK_tbl_acc2 PRIMARY KEY (a1)
    )
GO
BEGIN TRANSACTION
    -- Transfer the data as a metadata operation
    -- TODO: Add error handling
    ALTER TABLE dbo.tbl_acc
    SWITCH TO dbo.tbl_acc2;

    -- Drop the source table (now empty)
    DROP TABLE dbo.tbl_acc;

    -- Rename the table
    EXECUTE sys.sp_rename
        @objname = N'dbo.tbl_acc2',
        @newname = N'tbl_acc',
        @objtype = 'OBJECT';

    -- Rename the primary key
    EXECUTE sys.sp_rename
        @objname = N'dbo.tbl_acc.PK_tbl_acc2',
        @newname = N'PK_tbl_acc',
        @objtype = 'INDEX';
COMMIT TRANSACTION;


Visual Studio 2010 Shell Error - is it Causing this SQL Server error?

You have several SQL Server errors in the posted message:

  String or binary data would be truncated.

  The INSERT statement conflicted with the FOREIGN KEY constraint "FK_UPC_Item". The conflict occurred in database "PosBe", table "dbo.Item", column 'Item_ID'.

  The INSERT statement conflicted with the FOREIGN KEY constraint "FK_VendorPartNo_Item". The conflict occurred in database "PosBe", table "dbo.Item", column 'Item_ID'.

Those are the result of the server's attempts to execute SQL statements issued by your application. They cannot possibly be caused by problems with Visual Studio or SQL Server Management Studio. SSMS is essentially just another application that, like yours, can connect to the database server. Your being unable to run it cannot possibly affect how SQL queries sent to the server by your application are executed.


Difference between db_owner and having all other database roles except the 2 containing the deny keyword

As per the Docs article, db_owner can perform functions that no other role or combination of roles provides, including but not limited to:

Adding users to fixed-database roles
Dropping the database
The CONTROL DATABASE permission

There is a diagram on the linked article that shows the fixed-role permissions for each role. Any permission not listed in that diagram is granted to the db_owner role only by default.


If a specific query hangs, it's usually due to blocking. Is it possible that someone else has an active transaction that is affecting this row (or the page or partition it's in)? When you run this query, what does it show in sys.dm_exec_requests for that spid? First run:

SELECT @@SPID;

Make note of the number, then try to run the query. In another window, run the following:

SELECT blocking_session_id, wait_type FROM sys.dm_exec_requests
  WHERE session_id = <SPID from above>;

If a session shows up in blocking_session_id, find out who they are and what they're doing.

SELECT * FROM sys.dm_exec_requests
  WHERE session_id = <blocking_session_id from above>;

DBCC INPUTBUFFER(<that spid>);

You can also check if the database has any old transactions that may be causing an issue:

DBCC OPENTRAN();


this book is useful

http://www.amazon.com/Data-Mining-Microsoft-Server-2008/dp/0470277742/ref=pd_sim_b_3

and here is some reviews and nice articles about it

http://blogs.msdn.com/b/jamiemac/archive/2008/10/02/data-mining-with-sql-server-2008.aspx

http://richardlees.blogspot.com/2008/12/book-review-data-mining-with-sql-server.html

slideshow
http://www.slideshare.net/PeterGfader/10-data-mining

--

i also suggest watching these video tutorials by Learn it first
as a start they can give you a basic but useful introduction

Data Mining with Analysis Services
http://www.learnitfirst.com/Course/165/Chapter/292/Data-Mining-SSAS.aspx

http://www.learnitfirst.com/Course/165/SSAS-2008.aspx

--

chapter 11 of BI Architecture and Design Guide (best practices)
is focused on Data mining

http://biarchanddesignguide.codeplex.com/releases/view/6572


Create two sequences, one for IBM and one for Airtel. Set the start points appropriately. Put all the logic in a stored procedure. Pick the right one for each INSERT.

This is not a great design. Surrogate keys like this are best if they are completely meaningless. By tying a range of values to a customer you may have problems in the future. What will you do when a range is exhausted? What if you acquire another customer? Will you tolerate gaps in the sequence? For a short-lived, single-purpose application you may be OK. For an enterprise application it is a risk.


You haven't given a lot of details, but on the face I'd say make it a new fact table.  It sounds like your cohorts will be entirely distinct, so it wouldn't be meaningful to total or average across both sets.  Your records won't have any overlap in dimensions (except Date, which is almost universal), so they're really distinct things, they just happen to have similar fields.


text unique to the grain should be stored in fact table or dimension table?

Yes it does make sense to keep the text in the fact table for two reasons :

It is the same grain as the fact, so having a dimension just for that would indeed make it grow as fast as the fact table
It is not linked to other attributes in the fact table, so it can be modeled as a Degenerate Dimension directly in the fact table (even though it usually applies more to an ID or a label).

Text won't be part of your select statement when you aggregate reviews so it shouldn't impact performance. It will be there only when showing the data at the most granular level.
If you find out you have some other low-cardinality descriptive attributes that you can't easily put in an existing dimension because they are not linked to other attributes, you can also build a junk dimension with all these unrelated attributes.


How to three way inner join to three way outer join in MySQL?

If you want to include in the results values from any of a, b or c for which there is no matching values in the other tables, you can first find all distinct values for the three tables and then LEFT JOIN to the derived tables you already have:

select
    d.common_key,
    a.val, b.val, c.val
from
    ( select common_key from table_a
      union distinct
      select common_key from table_b
      union distinct
      select common_key from table_c
    ) as d
  left join
      (select common_key, sum(val) val from table_a group by common_key) as a
    on d.common_key = a.common_key
  left join
      (select common_key, sum(val) val from table_b group by common_key) as b
    on d.common_key = b.common_key
  left join
      (select common_key, sum(val) val from table_c group by common_key) as c
    on d.common_key = c.common_key
 ;


Is it safe to kill ANALYZE TABLE query on a MyISAM table?

First thing to know is what ANALYZE TABLE does.
For a MyISAM table mydb.mytable, ANALYZE TABLE traverses all the indexes in the mytable.MYI file, reads all BTREE pages, computes statistics, and stores in them in the information_schema, specifically STATISTICS. You can see it when you run one of the following:

SHOW INDEXES FROM mydb.mytable;
SELECT * FROM information_schema.statistics WHERE table_schema='mydb' AND table_name='mytable';

ANALYZE TABLE is a read-only operation and does issue a table-wide read lock.
ANSWER TO YOUR QUESTION
Yes, you can kill ANALYZE TABLE and wait briefly for the read lock to be released. However, there is a significant penalty. Because the index statistics will not be completely computed, the MySQL Query Optimizer will take rather bad guesses at generating EXPLAIN plans of queries against that table.
SUGGESTIONS
SUGGESTION #1 : Delay ANALYZE TABLE
If you want to run OPTIMIZE TABLE mydb.mytable without doing the ANALYZE TABLE operation, do the following:
ALTER TABLE mydb.mytable RENAME mydb.mytable_opt;
ALTER TABLE mydb.mytable_opt ENGINE=MyISAM;
ALTER TABLE mydb.mytable_opt RENAME mydb.mytable;

This will rename the table, perform the defragmentation, and rename it back.
You could do it in one line as well
ALTER TABLE mydb.mytable ENGINE=MyISAM;

If you want to run ANALYZE TABLE later on, set up a cron job to run it in every midnight or perhaps once a week. You will have to do this inevitably if you expect queries against your table to perform well after an optimization.
SUGGESTION #2 : Change the MyISAM table to InnoDB
During the generation of an EXPLAIN plan, InnoDB will perform reads against the index pages of a table by traversing 2-3 levels of nonleaf nodes and literally approximate (or really guess) the cardinality of all column levels. You can tell this is happening to an InnoDB table when you run SHOW INDEXES FROM mydb.mytable; because the cardinality changes with each run.
In light of this, you can simply run
ALTER TABLE mydb.mytable ENGINE=InnoDB;

and that's it. Even if you ran ANALYZE TABLE on an InnoDB table, the statistics can get rather stale. By default, innodb_stats_on_metadata is already disabled in MySQL 5.6. If you have MySQL 5.5 and prior, you can disable the update of the statistics by doing this:
SET GLOBAL innodb_stats_on_metadata = 0;

You will still have to bite the bullet and run ANALYZE TABLE sometime downstream if the InnoDB table experiences high INSERTs, UPDATEs, and DELETEs. Otherwise, the MySQL Query Optimizer will take bad guesses at EXPLAIN plans.


MariaDB Galera Cluster (master-master) does sync Tables but no data

Galera doesn't support but the InnoDB ENGINE (and XtraDB, which is essentially the same). While there is experimental support for MyISAM, by activating wsrep_replicate_myisam, that is not guarantee to work, as it is not transactional.

The DDL that you executed worked because they do not use the ROW format, but STATEMENT-based replication.

While we could hack and maybe make it work, it would work poorly. I highly recommend you to change the tables to InnoDB: ALTER TABLE your_table ENGINE=InnoDB, FORCE; After that, any change will be synced automatically as desired.

References:

MariaDB Galera Cluster limitations: https://mariadb.com/kb/en/mariadb/mariadb-galera-cluster-known-limitations/
Aria is not supported: https://groups.google.com/forum/#!msg/codership-team/-VTXuYVnbN0/d-v4NVTQDNwJ


SQL Server Connection String: Can the password be encrypted in line?

This is more for SO than DBA, but if you are using the identity/password for the app pool in IIS it will encrypt the password for you when it is stored. However, be aware that even with this process passwords can still be decrypted with not much work.

The connection string itself does not allow you to enter an encrypted password, the mechanism that is saving the connection string handles encrypting sensitive data (or should).


The binaries can live on C:\ or another system drive (though said drive should be formatted using the default block size).  My personal preference is to place them on another system drive (again, formatted using the default block size) because of the following reasons:

The page file, by default, resides on the C:\ drive
Other processes keep all manner of junk on the C:\ drive, by default (e.g. Windows patches, C:\Temp, etc.)
SQL Server log files are written to where the binaries are installed, by default
I'm lazy and don't want to do a whole bunch of post-install reconfiguration, and this also makes it easier for future DBAs to find where the SQL-related log files are located in the event of an outage, etc.

The main point here is that between the page file, patching, etc. the C:\ drive can be utilized by processes outside of your control as a DBA.  The problem with keeping the SQL Server log files on the same drive as these other things is that over time this drive can (and generally does) get crowded which can potentially cause a system outage if it becomes full and you can't write to it.

Regardless where you install binaries, I also recommend you make sure the system databases (e.g. master, msdb, model) are located on your data drive.  If they're also located on the C:\ drive, they too can contribute to disk crowding with their growth over time.  Finally, just make sure the block size for the disks hosting database files (e.g. mdf, ldf, and backup files) are all formatted with 64Kb block size.  It's much more of a pain to make this happen after you lay down the binaries than before.

Final note; as pointed out by Aaron Bertrand in the comments, there are still some files that will always be created on the C:\ drive, such as various dlls and the Setup Bootstrap log files.  Leave those alone as they are necessary to keep your service running, but for me, moving most of my SQL Server Binaries off of the C:\ drive often proves beneficial in the long run.


When you running "alone", if you mean running it in "psql" but just without the EXPLAIN ANALYZE, then "psql" is reading the entire result set into memory, and going over that set deciding how long the longest entry is for each column so that it can format it to that length.  That can be slow, though I would not expect 30 seconds for 2317 lines!

Except I see you don't have a "corr" column in your "corr" table, so what you are aggregating is the entire row records, which you already said has a column that can be very wide.  So you are slinging a huge amount of data at psql, and it takes a long time to process it, plus maybe doing some swap.


MS Access: Update data from one table to another based on a choice

Not sure what you want to achieve so let's start with saying that if you already have a table with the doctor's details, you shouldn't add them to the patientdetails table. If you need to display the data somewhere you can use joins in your query.

Thoughts:

Never duplicate data (unless you have to, these are only special cases)
You can always query data if they are linked (let's say you add the referring doctors id to this table)

example query

select * from PatientDetails join ReferringDoctors on PatientDetails.ReferringDoctorsID=ReferringDoctors.ID

Please provide more info, your current table structure, why you want to copy the info etc.


MySQL doesn't "release memory" that is allocated statically, for example innodb_buffer_pool_size. These allocations will consume more memory until they reach the configured limit and then stay there. The only way to "release it" is to reduce the size of those variables. You can reduce innodb_buffer_pool_size at runtime without restarting mysqld on 5.7.


This isn't right,

WHERE lead_status_id = "1" || lead_status_id="2" || lead_status_id="4" || lead_status_id="5" || lead_status_id="6"

What you want is,

WHERE lead_status_id IN (1,2,4,5,6);

Seriously, learn to use whitespace and take some pride in your code. Pick a convention (capitalize all sql tokens, and lower case all of your identifiers) Even if you don't get sql who writes like that..

)a
)b

Where did you even get that from? .. SELECT (...) AS t, (...) AS x whitespace, and AS! That all said, I think you want something like this,

SELECT
  count(lead_status) FILTER (WHERE lead_status_id IN (1,2,4,5,6)) as 'Pending files',
  count(lead_status) FILTER (WHERE lead_status_id = 3) AS 'Completed files',
  any(employee_name = 'anna') AS has_anna
FROM vw_file_registrationlist;


Why does a simple loop result in ASYNC_NETWORK_IO waits?

The documentation for SET NOCOUNT says:

SET NOCOUNT ON prevents the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. For stored procedures that contain several statements that do not return much actual data, or for procedures that contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.

You are not running the statements in a stored procedure, so SQL Server sends DONE tokens (code 0xFD) to indicate the completion status of each SQL statement. These messages are deferred, and sent asynchronously when the network packet is full. When the client does not consume network packets quickly enough, eventually the buffers fill up, and the operation becomes blocking for SQL Server, generating the ASYNC_NETWORK_IO waits.
Note the DONE tokens are different from DONEINPROC (code 0xFF) as the documentation notes:

A DONE token is returned for each SQL statement in the SQL batch except variable declarations.

For execution of SQL statements within stored procedures, DONEPROC and DONEINPROC tokens are used in place of DONE tokens.

You will see a dramatic reduction in ASYNC_NETWORK_IO waits using:
CREATE PROCEDURE #P AS
SET NOCOUNT ON;

DECLARE
    @outer_loop integer = 0,
    @big_string_for_u varchar(8000);

WHILE @outer_loop < 5000000
BEGIN
    SET @big_string_for_u = 'ZZZZZZZZZZ';
    SET @outer_loop = @outer_loop + 1;
END;
GO
EXECUTE dbo.#P;

You could also use sys.sp_executesql to achieve the same result.
Example stack trace captured just as an ASYNC_NETWORK_IO wait begins:

An example TDS packet as seen in the inline function sqllang!srv_completioncode_ex<1> had the following 13 bytes:
fd 01 00 c1 00 01 00 00 00 00 00 00 00

Which decodes to:

TokenType = 0xfd DONE_TOKEN
Status = 0x0001 DONE_MORE
CurCmd = 0x00c1 (193)
DoneRowCount = 0x00000001 (1)

Ultimately, the number of ASYNC_NETWORK_IO waits depends on the client and driver, and what it does, if anything, with all the DONE messages. Testing with a loop 1/10th of the size given in the question (5,000,000 loop iterations) I found SSMS ran for about 4 seconds with 200-300 ms of waits. sqlcmd ran for 2-3 seconds with single digit ms waits; osql around the same run time with around 10 ms of waits.
The worst client by far for this test was Azure Data Studio. It ran for almost 6 hours:


my intention is to return 0 if ig.NumReceived or og.NumShipped return 0 as well

If so, convert

coalesce((p.StartingInventory-og.NumShipped+ig.NumReceived), p.StartingInventory) as OnHand

to, for example,

coalesce(  p.StartingInventory - og.NumShipped + ig.NumReceived,
           case when ig.NumReceived = 0 or og.NumShipped = 0
                then 0
                else p.StartingInventory
                end
        ) as OnHand


Is it possible to remote connect to SQL Server 2008 database with SQL Server Management Studio 2005?

If you are experiencing the problems that are documented as being fixed by installing that update, then there is unlikely to be another way to fix the problem while still keeping that version of the tool.

You don't say why you'd prefer not to install the update. Are you trying to keep yourself at that backwards level to stay specifically identical to a client installation or do you just not have admin rights on the machine so have to get someone else involved to apply the patch?

If you do have access to install software, another option might be to install SSMS 2016 which these days is distributed (for free) separately of the SQL Server engine and other parts and can be install side-by-side with older versions. SSMS 2016 can be obtained from https://msdn.microsoft.com/en-us/library/mt238290.aspx

  it will throw errors that indicated

When describing a problem or asking for help it is important not to skimp on details that you have which might give clues to people trying to help. As such you should provide the text of the error messages you have received, otherwise in this case we don't know if they are the sort of problem the update fixes or indicate another problem that needs a different fix. Help us to help you.


How to set column DEFAULT using value of a variable

You seem to assume variable substitution for an SQL utility command like ALTER TABLE, but that's not implemented.

Also not possible to pass values for utility commands to EXECUTE with the USING clause in plpgsql.
Detailed explanation for both in this related answer on SO:

Error when setting n_distinct using a plpgsql variable

Concatenate the statement, then execute it - like @Abelisto hinted. But you don't need a variable, you can concatenate the lookup value directly:

DO $$
BEGIN
EXECUTE format('ALTER TABLE beta ALTER COLUMN aref SET DEFAULT %L'
             , (SELECT a.id::text FROM alpha a));
END $$;

The SELECT must return a single value.
I cast to text explicitly, which is not strictly necessary. Casting the string literal to the target type uuid isn't required, either. Postgres will derive that from the column type automatically.

Related:

CREATE SEQUENCE using expressions with psql variables for parameters


SQL Server 2012: Give rights to read ddl but not modify?

You can give VIEW DEFINITION at the object, schema or database level.

GRANT VIEW DEFINITION ON dbo.objectname TO [user/role];

GRANT VIEW DEFINITION ON SCHEMA::schemaname TO [user/role];

GRANT VIEW DEFINITION ON DATABASE::databasename TO [user/role];

http://msdn.microsoft.com/en-us/library/ms173848.aspx


In what circumstances I am more likely to benefit from auto update stats async on?

I think you will be much better off testing your actual workload under sync and async, and see if you observe any differences. Coming up with a sweet spot to suit a particular feature is kind of backwards.

Generally, async is primarily beneficial when you don't care if the query that triggered the stats update still runs that last time with the old stats (or users can't wait long enough for the stats update to happen and the query to run). The threshold will not be easy for anyone else to tell you - it depends on how long the stats update takes (so depends on data size, I/O subsystem, sample size, etc.), how much the stats update helps the query (it may or may not generate a different plan, for example), and we don't know your end users' definition of "this query is taking too long."

Again, generally, sync is beneficial when you know that stats go south quickly, and that any query that runs with stale statistics will suffer. In this case, you should have a good idea that the duration of the stats update + the time of the improved query is on average less than the duration of the query using the stale stats.


The exact equivalent is:

SELECT COUNT(col1) as summation
FROM TABLE
GROUP BY col2

The SUM does nothing as there is only one aggregate based row per each partition of the OVER clause therefore it can be simplified to a simple GROUP BY instead.


How to compare two tables that have no primary key?

this is a start

select table1.*, table2.*
from table1
full outer join table2
 on table1.EmployeeID   = table2.EmployeeID
and table1.AssignmentID = table2.AssignmentID
where table1.EmployeeID is null or table2.EmployeeID is null
order by isnull(table1.EmployeeID, table2.EmployeeID)

But that order by may break the outer so if you get no rows remove it

Note this data does not appear to be properly normalized.  This would allow for the same EmployeeID with different name. It is odd that dependent is dependent on assignmentID. If you are going to clean it up then consider improving the data design.

Pick one to fix (add missing data) in the case table1

Then go after data the does not match

select t1.*, t2.*
from table1 t1
join table2 t2
  on t1.EmployeeID   = t2.EmployeeID
 and t1.AssignmentID = t2.AssignmentID
 and (    t1.DependentLastName <  t2.DependentLastName
       or t1.DependentLastName is null
       or t2.DependentLastName is null )
left table2 t2b
  on t1.EmployeeID        = t2b.EmployeeID
 and t1.AssignmentID      = t2b.AssignmentID
 and t1.DependentLastName = t2b.DependentLastName
where t2b.EmployeeID is null


net start sql service with startup option

For the article you linked it references the specific start up options used by the Database Engine ("sqlservr.exe"). So the "-" dash is used as stated in the article. This article points to the method used in a DOS prompt to start SQL Server in single user mode. I believe using /m"SQLCMD" would work the same way, but I normally use the commands noted below.

So from a DOS prompt, the command to start SQL Server default instance in single user mode and only allow SQLCMD to connect would be:
sqlservr.exe -m"SQLCMD"

If you have a named instance it would be:
sqlservr.exe -m"SQLCMD" -s MyServer\MyInstance


How to optimize performance of backup process? Servers go down during backup

let us discuss few approaches for backup

If you are using replSet then could be very easy to take backup from   secondary hidden if possible.It will simply makes things easy for you.
3 member replica set 1 primary ,1 secondary ,1 secondary but hidden(can be used for backup)
you can use snapshot using LVM ,as it is system based approach so need manual intervention.for details Click here!
You can go for MMS(MongoDB Management Service) It will use your oplog to create backup.If not using replset then also you can create OPlog click here for details.

Also have a look at strategies comparison  click here


How to perform almost the same query N times without rewriting it N times?

You can use the IN statement

SELECT COUNT(*), field_name
FROM MY_table
WHERE field_name IN (1,2,3,4,5)
GROUP BY field_name


SSIS comparison load between new, updated and deleted records and populate a staging database

If I've understood correctly your OLE DB Destination is to the 'target' table and your OLE DB Destination 1 is to the 'staging' table? That would explain why your new rows aren't appearing in the 'staging' table as you're only sending them to the 'target' table.

If you want all rows (updated or inserted) to appear in the 'staging' table, I would suggest you remove the Lookup and Conditional Split and simply insert all rows to the 'staging' table. Then use a Execute SQL task to fire a MERGE statement to update or insert as appropriate to the 'target' table. In my experience this is much more performant that using the lookup to decide whether to insert or update


Optimize schema and query for overlapping date ranges query

You have only primary keys for tables and indexes related to the FK restrictions but no indexes related to the queries you run.

SELECT qtm.date_i
     , s.name
     , pp.*
  FROM query_time_window   AS qtm
  JOIN seller              AS s   -- some ON condition is strictly recommended
  LEFT JOIN product_price  AS pp  ON pp.seller_id = s.id -- most conditions are moved to WHERE
 WHERE ( pp.product_id = 1 OR pp.product_id IS NULL )
   AND ( pp.created_at <= qtm.date_f   -- I've changed '!a>b' to 'a<=b'
         AND ( pp.cancelled_at IS NULL
               OR pp.cancelled_at >= qtm.date_i) -- I've changed '!a<b' to 'a>=b'
       )
 ORDER BY qtm.date_i, s.id;

Table query_time_window should have an index (date_i, date_f) or (date_f, date_i)
Table product_price should have an additional index (product_id, seller_id, created_at, cancelled_at) - an exact order of the columns depend on the real data. You can try different combinations and choose the fastest one.


SQL Server Cardinality Estimation Warning

Sometimes the warning is nothing more than that, just a warning, and not something actually affecting performance.
The two things that are most likely affecting your performance are the Table Variable, and the fact you're looping instead of using a more relational solution. So I'd first run a SELECT * INTO #images FROM @images to put it inside of a Temp Table before your WHILE loop, and then use that Temp Table inside the loop instead, to potentially improve performance.
To answer your question though, I believe the fact that your imageid is an INT but you're using it in a string function like CONCAT() is where the Implicit Conversion is coming from that is inducing that Cardinality Estimate warning. If you stored a copy of it in your @images table variable already casted as a string data type that was the same type as the extension field and used it in the CONCAT() function instead then the warning should go away.
Also, Table Variables typically result in poor Cardinality Estimates themselves because of their lack of statistics, which may be why the "Estimated Number of Rows Per Execution" is showing 1. (Note there's been improvements in SQL Server 2019.)


Does a materialize view refresh in its creation?

The BUILD DEFERRED clause in the CREATE MATERIALIZED VIEW DDL statement causes the actual population to be deferred until the first refresh.

While Oracle parses the actual SQL used to populate the view, it does not execute it & will therefore not pick up "runtime" problems.

This is easily demonstrated.

Parsing error, due to NONSENSE not being a datatype:

SQL> CREATE MATERIALIZED VIEW PARSINGERROR
BUILD DEFERRED
REFRESH COMPLETE ON DEMAND
AS
SELECT
    CAST ('null' AS NONSENSE) as parsing_error
     from dual;

    CAST ('null' AS NONSENSE) as parsing_error
                    *
ERROR at line 6:
ORA-00902: invalid datatype

SQL>

Runtime error, that won't get picked up as it doesn't analyse the actual values involved, and is unaware that the included string isn't a number:

SQL> CREATE MATERIALIZED VIEW RUNTIMEERROR
BUILD DEFERRED
REFRESH COMPLETE ON DEMAND
AS
SELECT
    CAST ('notanumber' AS number) as runtime_error
     from dual;

Materialized view created.

SQL>

SQL> begin
  2  DBMS_MVIEW.refresh('RUNTIMEERROR');
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-01722: invalid number
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2566
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2779
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2748
ORA-06512: at line 2

SQL>


I maintain an Access 2003 application that connects to a pair of SQL Server 2005 servers setup with mirroring.  I use the following connection string which allows the application to connect to the mirror after an application restart:

Dim ws As Workspace
Dim db As Database
Dim a As Long

Set ws = DBEngine.CreateWorkspace("MyODBCWorkspace", "admin", "", dbUseJet)
Set db = ws.OpenDatabase("C:\[pathtodatabase]", True, False, "MS Access;")
For a = 0 To db.TableDefs.Count - 1
    Me.ProgressBar1.Value = a
    DoEvents
    If db.TableDefs(a).Connect  "" Then
        db.TableDefs(a).Attributes = TableDefAttributeEnum.dbAttachSavePWD
        db.TableDefs(a).Connect = "ODBC;DRIVER=SQL Native Client;SERVER=[PrimaryServerName];Trusted_Connection=Yes;APP=[MyAppName];WSID=[MyWorkstationName];DATABASE=[SQLServerDatabase];Failover_Partner=[MirrorSQLServerName];"
        db.TableDefs(a).RefreshLink
    End If
Next a
db.Close
ws.Close
Set db = Nothing
Set ws = Nothing

I use this code in a VB launcher app I created that the user double-clicks to launch the Access MDB/MDE.

Alternatively, this is the content of a DSN file we sometimes use to accomplish the same thing:

[ODBC]
DRIVER=SQL Native Client
APP=Microsoft Data Access Components
Trusted_Connection=Yes
SERVER=[ServerFullyQualifiedDomainName]
FAILOVER_PARTNER=[MirrorFullyQualifiedDomainName]
DATABASE=[DatabaseName]


What would cause a transactional replication subscription to restart repeatedly with no errors?

Just in case anyone else comes across this problem, I believe I have found the solution.

I ran through all of the steps again from beginning to end in preparing the database for replication, and I realised that one of the triggers we have on the database was not marked with the 'Not for replication' property. It previously had been, but a code change had been made and deployed a version of the trigger without this property. After changing this back I set up a new snapshot and applied it successfully with no problems.

Due to the size of the database I was trying to replicate, I haven't tested flipping the setting back to off and trying it again to confirm this was what fixed it, although I did try it with a smaller database and didn't see the same behaviour. My thought on this is that the trigger was causing some sort of error with the data but not properly throwing an error message.

To check whether this property is set or not just run the following script and look at the is_not_for_replication property - 1 means it is set, 0 means it is not.

SELECT name,
is_not_for_replication
FROM sys.triggers
WHERE is_ms_shipped = 0;

If you are seeing the issue described above and then find that one or more triggers do not have this property set, update the definition of the stored procedure to include the not for replication property and try again. Setting the property is explained here: https://stackoverflow.com/questions/44882512/how-to-create-trigger-with-not-for-replication-in-merge-replication-of-mssql


Read only access to Slaves from Server A

First a clarification. A machine is a client, not a server, not running the mysqld program. B and C are servers, they are running mysqld and they are part of a master-slave topology, they are slaves of another server (just assume is D)

And after that, mysql permissions are the answer. Just define a user in servers B and C and grant only the select permission

On both B and on C

GRANT SELECT ON *.* TO 'name_of_user'@'IP_of_A' IDENTIFIED BY 'pass'


How to cope with SQL Server 2012 vs SQL (Azure) Database sql files

Rather than maintaining two separate code bases, the easier way to do this is by aiming for the lowest common denominator. Try to only use features in the database that are supported in Azure SQL DB. That way you'll have a single code base that works for both, and you'll have much easier upgrades, too.

For box-product customers that truly need SQL Server features that aren't available in Azure SQL DB, build a separate set of scripts that are deployed after the database is created.

If there are specific features you need in SQL 2012 that aren't available in Azure SQL DB, try posting separate questions about each of those (or list 'em in the question itself) so we can get a better feel for why you really need two separate code bases.


GET_LOCK([name], 0) behaviour and multiple GET_LOCK() order

OBSERVATIONS
In your question, you said

I read from the documentation that the behaviour of distributing a lock requested by many connections after RELEASE_LOCK is called from the connection holding the lock is undefined.

You are overlooking something far more basic
MySQL Documentation clear says the following on GET_LOCK()

A negative timeout value means infinite timeout.
If multiple clients are waiting for a lock, the order in which they will acquire it is undefined. Applications should not assume that clients will acquire the lock in the same order that they issued the lock requests.

The Documentation says nothing about a zero(0) timeout value. Thus, that behavior, in itself, is simplified. If it acquires the lock, it immediately returns 1.  If it does not acquire the lock, it immediately returns zero(0).
You said p2 has a 10-second timeout (because you said you are using GET_LOCK("lock",10)).
If you are expecting p1() to pause execution, using a non-positive will not accomplish this.
SUGGESTION
You may want to execute a loop in your Stored Procedure to attempt to acquire the lock with some arbitrary number of seconds to pause between lock attempts:
SET @seconds_to_pause = 5;
SET @lock_acquired = GET_LOCK('lock',0);
WHILE @lock_acquired = 0 DO
    SET @x = SLEEP(@seconds_to_pause);
    SET @lock_acquired = GET_LOCK('lock',0);
END WHILE;

Give it a Try !!
YOUR ORIGINAL QUESTION

is it possible for the lock to be passed down successive calls to p1 and p2 has to wait?

If you do not have a loop to acquired the lock as I suggested,the answer would have to be no.


Error on updating a field with binary values

Simply use UNHEX("1E395565").
There could be a lot of other issues going on.  So please provide SHOW CREATE TABLE for the old and new tables.  Also provide a sample of what is in the old table, but use
SELECT col, HEX(col) FROM ... WHERE ...

so we can see the value in an unambiguous way.  (That is, not mangled or "fixed" by the client.)
Where does 1E395565 come from?  It is not anything meaningful (not utf8, etc) that I recognize.


What are some consequences of putting quotes in a WHERE clause for an integer column?

Both do the same and you won't be able to measure any difference in performance.

Details

This is a string literal or string constant: '5'

The manual:

  A string constant in SQL is an arbitrary sequence of characters
  bounded by single quotes ('), for example 'This is a string'. To
  include a single-quote character within a string constant, write two
  adjacent single quotes, e.g., 'Dianne''s horse'. Note that this is not
  the same as a double-quote character (").

If there is no context from which a type can be derived, a string literal is initially assumed to be type text. (Not the case in your example.)

The manual once more:

  The explicit type cast can be omitted if there is no ambiguity as to
  the type the constant must be (for example, when it is assigned
  directly to a table column), in which case it is automatically coerced.

This is a numeric literal or numeric constant: 5

The manual:

  A numeric constant that contains neither a decimal point nor an
  exponent is initially presumed to be type integer if its value fits in
  type integer (32 bits); otherwise it is presumed to be type bigint if
  its value fits in type bigint (64 bits); otherwise it is taken to be
  type numeric. Constants that contain decimal points and/or exponents
  are always initially presumed to be type numeric.

So a numeric literal starts out with a specific type. It may then be cast to a different type as the context requires - if such a cast is defined. That's a subtle, but important difference - which makes no effective difference in your case, since 5 is initially integer, which is exactly the type it needs to be.

But it matters in other cases. Try this:

CREATE TEMP TABLE tbl1 (t int);
SELECT * FROM tbl1 where t = '0';         -- works!
SELECT * FROM tbl1 where t = int '0';     -- works!
SELECT * FROM tbl1 where t = int2 '0';    -- works!
SELECT * FROM tbl1 where t = 0;           -- works!

CREATE TEMP TABLE tbl2 (t text);
SELECT * FROM tbl2 where t = '0';         -- works
SELECT * FROM tbl2 where t = text '0';    -- works
SELECT * FROM tbl2 where t = varchar '0'; -- works
SELECT * FROM tbl2 where t = 0;           -- fails !!!

ERROR:  operator does not exist: text = integer

Because the numeric literal starts out as integer and there is no assignment cast defined for integer --> text. (Any type can be cast to text with an explicit cast (0::text) but that cast is not assumed here.)


How do I find PostgreSQL's data directory?

If you can connect to the database with superuser access, then

SHOW data_directory;

is the shortest way.

If the server is not running and you forgot where the data directory was, then you really have to guess yourself.  An operating system specific convention or the shell history might provide hints.


Correlated subqueries. Count Visits after Last Purchase Date

Problem solved.
Unfortunately ClickHouse does not support aliases for subqueries and hence referencing. So I had to find another workaround using SELECT from subquery (with LastVisitDate) LEFT JOINed with another subquery (with LastPurchase Date) containing and then using WHERE clause "LastVisitDate < LastPurchaseDate".


Oracle - Scan listener and multiple databases

Listener is a process running in the Oracle Server machine. The responsibility of the listener is to listen for the incoming database connection request and spawning server process to handle the request(in Dedicated Server mode) or handing over to dispatcher process(in Shared-Server mode). In RAC architecture we have SCAN listener to redirect connection requests to the nodes. So just to redirect the connection three SCAN listeners are enough. However we can have more SCAN listeners by adding more SCAN VIPs.

  No of scan IPs has been determined after careful study by Oracle base development and has been found sufficient as of now. You can still configure more if you want to. But that, I believe, would be the wastage of the IPs. more..

  If the SCAN name resolves to one IP address, root script (root.sh or rootupgrade.sh) will create the number of SCAN VIP resources(ora.scan1.vip) and corresponding SCAN listener resource(ora.LISTENER_SCAN1.lsnr) depend on how many IP address the SCAN name resolves to, i.e.if the SCAN name resolves to two IP addresses, it will create two SCAN VIP resources and two corresponding SCAN listener resource.more...[MOS Account required)

Here is a very good discussion for the same kind of question.

Why 11gR2 scan have limited maximum to 3 ips


How to resolve ORA-01031: insufficient privileges error on Windows installation?

The problem is that your Oracle Client installation is in the system PATH before your Oracle Server installation (in fact, you even mention that it worked before you installed the client).
Proceed as follows:

Remove the Oracle Client as it's only confusing things. Use the uninstall option but afterwards check that the directory and the Oracle Client registry key is gone and remove by hand if necessary.
(It's possible to get this working by having the Oracle client home in the path after the server home, but it's much simpler to just have the single install. An Oracle server installation already contains a complete Oracle client anyway).

Make sure that the path to the server's home is in your PATH. And no other Oracle home is in the path.

Make sure that your account is in the ORA_DBA group. Double check this, especially if you are using domains.

If all else fails, you could remove SQLNET.AUTHENTICATION_SERVICES=(NTS) from your sqlnet.ora as this will force you to use password authentication.


Group by multiple columns, agregate others and select all in SQL Server

This is a greatest-n-per-group problem and there are many ways to solve it (CROSS APPLY, window functions, subquery with GROUP BY, etc). Here's a method using window functions and a CTE:

WITH ct AS
  ( SELECT *,
           rn = RANK() OVER (PARTITION BY PARAMETER_NAME, GW_LOCATION_ID
                             ORDER BY Report_Result DESC)
    FROM SLVs_Flagged
  )
SELECT PARAMETER_NAME, GW_LOCATION_ID,
       Max_Report_Result = Report_Result,
       DETECT_FLAG
       -- more columns
FROM ct
WHERE rn = 1
ORDER BY PARAMETER_NAME, GW_LOCATION_ID ;

The query will return all tied results (if there are ties). If you want a single result for every (PARAMETER_NAME, GW_LOCATION_ID) combination, you can resolve ties by using ROW_NUMBER() instead of RANK() and modifying the ORDER BY inside the OVER (..) clause. Eg. (prefer DETECT_FLAG with N over Y):

           rn = ROW_NUMBER() OVER (PARTITION BY PARAMETER_NAME, GW_LOCATION_ID
                                   ORDER BY Report_Result DESC, DETECT_FLAG)


update matching column value in anywhere in database sqlserver

One possible approach is to generate and execute a dynamic statement using information from system catalog views. It's important to note, that such an update may fail, if assetid column is a primary key or has constraints in some of the tables.

DECLARE @stm nvarchar(max)

SELECT @stm = (
    SELECT CONCAT(
        ' UPDATE ', QUOTENAME(sch.[name]), '.', QUOTENAME(tab.[name]),
        ' SET ', QUOTENAME(col.[name]), ' = 1',
        ' WHERE ', QUOTENAME(col.[name]), ' = 1020;'
    )
    FROM sys.columns col
    JOIN sys.tables tab ON col.object_id = tab.object_id
    JOIN sys.schemas sch ON tab.schema_id = sch.schema_id
    WHERE col.[name] = 'assetid'
    FOR XML PATH(''), TYPE
).value('.', 'nvarchar(max)')

PRINT @stm
EXEC sp_executesql @stm

SQL Server (and most other relational database systems) store information about the tables, views, procedures etc. that have been created in a set of internal tables. These are collectively known as the catalog. Specifically there are ones for schemas, tables and columns. We are allowed to read the catalog from code we write.

When a client passes SQL to a server the query is just a text string. So if we can generate a string of the correct format we can pass it to the server and it will be executed. The SELECT statement above generates such a string using the information available in the catalog.

Rather than write a loop that processes one table each pass the code constructs a single string containing all required statements. Each statement is terminated by the semicolon at the end of the literal ' = 1020;'. The statements are concatenated using a trick of how FOR XML constructs its output. In newer versions of SQL Server STRING_AGG could have been used instead.

The print statement is just to aid debugging.

Finally the exec submits the generated string to SQL Server for execution.


I have a correct subquery that returns results but when I use it as an "IN" clause I get error 1064

You can't SELECT like this, where you dynamically SELECT the table name. That's why it's not working. The subselect works, but.. try to replace subselect by a constant (i.e. known) name and it still won't work.
For something like this you either need an if then else flow or dynamic SQL or do it on application side.


SQL Server 2017 in Azure Cloud config DB Mail

SQL Server only supports sending email via SMTP using Basic Authentication without TLS/encryption. Basic Authentication means that the client application passes the username and password with every request. Basic Authentication makes it easier for attackers armed with today’s tools and methods to capture users’ credentials.

Many email providers are ending support for unencrypted/non-secure SMTP connections because of the risk involved. I don't know if mail.gmx.com supports non-secure connections, but it would not be surprising if they are forcing secure connections, even on port 25 (traditionally for non-secure connections).

Additionally, I notice you mention that you have "created a new GMX email address and enabled it for pop3 external programs."  I'm not familiar with GMX, but you will need to have SMTP enabled, not POP to send mail. Perhaps GMX has one configuration switch that controls POP3 & SMTP jointly, but it's worth double-checking to make sure you really have SMTP enabled.

At most of my prior employers, we white list our DB Servers to send mail without authentication via corporate mail relay servers. This vastly simplifies the mail setup, by eliminating the authentication hassle. This is possible because the network & security is more tightly controlled on an internal network, minimizing the possibility of an unauthorized user running a spambot via unauthenticated SMTP.


Best Azure Service for Large no of SQLServer Databases

My Question is that in the Long Run and from management and scalability point of view which option is better

For "management and scalability" Azure SQL Database is clearly better.

Azure SQL Database was designed for this scenario.  You can spread out your databases across multiple pools, as needed, or move specific databases to non-pooled DTUs.  And Azure SQL Database has built-in patching, HA/DR, monitoring, and backup.

Azure Virtual Machines give you way more flexibility to design SQL Servers that exactly fit your needs, but that comes with a cost of complexity and management.

In both cases you should plan to spread your databases out over a few Elastic Pools / VMs, so you can scale out as well as up.


MongoDB skipping 20 documents when storing in a variable

The shell does not actually return just 20 records, what you get returned when you run a find like the one you list in the shell is a cursor, the shell then by default iterates over and displays the first 20 documents returned by that cursor (for convenience).  For example:

> db.foo.find({}, {_id : 1})
{ "_id" : ObjectId("573c4afbaa6cd35206c16a8d") }
{ "_id" : ObjectId("573c4afbaa6cd35206c16a8e") }
{ "_id" : ObjectId("573c4afbaa6cd35206c16a8f") }
{ "_id" : ObjectId("573c4afbaa6cd35206c16a90") }
{ "_id" : ObjectId("573c4afbaa6cd35206c16a91") }
{ "_id" : ObjectId("573c4afbaa6cd35206c16a92") }
{ "_id" : ObjectId("573c4afbaa6cd35206c16a93") }
{ "_id" : ObjectId("573c4afbaa6cd35206c16a94") }
{ "_id" : ObjectId("573c4afbaa6cd35206c16a95") }
{ "_id" : ObjectId("573c4afbaa6cd35206c16a96") }
{ "_id" : ObjectId("573c4afbaa6cd35206c16a97") }
{ "_id" : ObjectId("573c4afbaa6cd35206c16a98") }
{ "_id" : ObjectId("573c4afbaa6cd35206c16a99") }
{ "_id" : ObjectId("573c4afbaa6cd35206c16a9a") }
{ "_id" : ObjectId("573c4afbaa6cd35206c16a9b") }
{ "_id" : ObjectId("573c4afbaa6cd35206c16a9c") }
{ "_id" : ObjectId("573c4afbaa6cd35206c16a9d") }
{ "_id" : ObjectId("573c4afbaa6cd35206c16a9e") }
{ "_id" : ObjectId("573c4afbaa6cd35206c16a9f") }
{ "_id" : ObjectId("573c4afbaa6cd35206c16aa0") }
Type "it" for more

Note the last line, if you type it you will get 20 more results, and you can repeatedly do so until you reach the end of the results and exhaust the cursor.

When you assign the result of a query to a variable you are storing the cursor in that variable, not the results, and no iteration is done unless you call it.

The reason for the differences in your count is not because of any skipping, it is probably simply because you have 2413 documents with a student field and 580 without a student field for a total of 2993.  You haven't shared your actual doc structure, but to illustrate with a basic example:

// insert 2413 docs with a student field, 580 without
for(var i = 0; i < 2413; i++){db.foo.insert({student : i})};
for(var i = 0; i < 580; i++){db.foo.insert({})};
// now do a count of all records
db.foo.count()
2993
// count only those with student field
db.foo.count({student : {$exists : true}})
2413


store log of update,insert operation of postgresql user

Have a look at the Postgresql wiki page where there are comprehensive examples of audit mechanisms for both Postgresql 9.0 and 9.1+

PG 9.0: https://wiki.postgresql.org/wiki/Audit_trigger
PG 9.1+:
https://wiki.postgresql.org/wiki/Audit_trigger_91plus, with more code from Craig Ringer at https://github.com/2ndQuadrant/audit-trigger


I'm assuming that you mean you want the id of the item which is the most expensive based on the data from all three tables.  For each table, you need the id and the price of the item(s) with the maximum price in that table.  For one table, that is:

SELECT id, price FROM Tn WHERE price = (SELECT MAX(price) FROM Tn)

So, there's a sub-query:

SELECT id, price FROM T1 WHERE price = (SELECT MAX(price) FROM T1)
UNION
SELECT id, price FROM T2 WHERE price = (SELECT MAX(price) FROM T2)
UNION
SELECT id, price FROM T3 WHERE price = (SELECT MAX(price) FROM T3)

And, you need to find one of the id values with the maximum price from that sub-query:

SELECT id
  FROM (SELECT id, price FROM T1 WHERE price = (SELECT MAX(price) FROM T1)
        UNION
        SELECT id, price FROM T2 WHERE price = (SELECT MAX(price) FROM T2)
        UNION
        SELECT id, price FROM T3 WHERE price = (SELECT MAX(price) FROM T3)
       ) AS M
 ORDER BY price DESC
 LIMIT 1

Note that if there are two equally expensive items listed in any one of the tables, the UNION query will return more than 3 rows.  If there are two or more equally expensive items, the query with the LIMIT chooses one, and you can't predict which.  If you need to see all the id values of equally expensive items that cost the most, then you need to play a similar game using nested queries.  This blows my mind, but should work:

SELECT id, price
  FROM (SELECT id, price FROM T1 WHERE price = (SELECT MAX(price) FROM T1)
        UNION
        SELECT id, price FROM T2 WHERE price = (SELECT MAX(price) FROM T2)
        UNION
        SELECT id, price FROM T3 WHERE price = (SELECT MAX(price) FROM T3)
       ) AS M
 WHERE price =
       (SELECT MAX(Price)
          FROM (SELECT id, price FROM T1 WHERE price = (SELECT MAX(price) FROM T1)
                UNION
                SELECT id, price FROM T2 WHERE price = (SELECT MAX(price) FROM T2)
                UNION
                SELECT id, price FROM T3 WHERE price = (SELECT MAX(price) FROM T3)
               ) AS M2
       );

This would be easier using a WITH clause:

WITH MaxPrices AS
     (SELECT id, price FROM T1 WHERE price = (SELECT MAX(price) FROM T1)
      UNION
      SELECT id, price FROM T2 WHERE price = (SELECT MAX(price) FROM T2)
      UNION
      SELECT id, price FROM T3 WHERE price = (SELECT MAX(price) FROM T3)
     )
SELECT id, price
  FROM M
 WHERE price = (SELECT MAX(Price) FROM M);

Not every DBMS supports the WITH clause like this, and I believe MySQL is one of the DBMS in the category that does not include such support.


Do MySQL optimizations hold for MariaDB also?

Yes, all the basic optimizations in MySQL 5.5 apply to MariaDB 5.5.

However, starting with MariaDB 10, a real fork, not all improvements in 5.6 are in MariaDB codebase, and alternatively, MariaDB has some exclusive features (Hash JOINS). For now, those are not too different, that may change in the future. For a book, where most of the optimizations are regarding basic indexing, it may not be very relevant.

And you can always check the latest improvements online -blogs, wikis. This is a presentation I created for the newest MySQL 5.6 query optimizations techniques.

Have a look at this image for a visual representation (white zones are MariaDB-only). 5.6- and 5.7-only are not represented:


Disable top (n) Sort Optimization for a specific view

I can't think of a fully transparent way to achieve what you want with views without disabling row goals in general, which probably won't suit your purposes.

  As the purpose of the view is to provide some business analytics to data analysts, while they are developing reports they use to check a sample of the view by doing a select (top N) query.

Perhaps you could convince them to use a TOP (x) PERCENT instead? This has to count the number of rows in the whole set to work out the percentage. Materializing the full set comes with a tempdb cost, but this could be manageable if the data is a reasonable size. In any case it will give a plan without a row goal.

SELECT TOP (1) PERCENT TV.* FROM TheView AS TV;

They may find this more natural than choosing an arbitrary sort order (and one which is guaranteed to need a physical sort.

All that said, it's possible that a detailed investigation would reveal tuning options to make the query with TOP as quick as it should be. That's consulting work though, so beyond what can be addressed here on the basis of estimated plans alone.


Thank you Akina for the tip, got it working using the following query

SELECT
      COUNT(measurement) as 'Measurement Errors'
     FROM (
        SELECT *
        FROM measurelog
        ORDER BY Id DESC
        LIMIT 100
        ) sub
    WHERE (measurement < (baseline - tolerance))

which now returned 7 results


MySQL event scheduler waiting on empty queue since server restarted 12 days ago

The event_Scheduler is, as it's name suggests, a way to Schedule Events (in this case queries) to run in MySQL at a given time. It is simply waiting for an event to trigger it and tell it to do something. As you can see you have no events set up, so it will be waiting for a long time.
It is enabled by default, but can be disabled by running:
SET @@global.event_scheduler = 0;
You also want to add:
event_scheduler = OFF
to your my.cnf file to prevent it starting after a reboot.


As a rule, a CTE will NEVER improve performance.

A CTE is essentially a disposable view.  There are no additional statistics stored, no indexes, etc.  It functions as a shorthand for a subquery.

In my opinion they can be EASILY overused (I see a lot of overuse in code in my job).  Some good answers are here, but if you need to refer to something more than once, or it's more than a few hundred thousand rows, put it into a #temp table instead and index it.


No, Truncate can't be used with a WHERE clause. Truncate simply deallocates all the pages belonging to a table (or a partition) and its indexes.

From BOL:

-- Syntax for SQL Server and Azure SQL Database

TRUNCATE TABLE
    [ { database_name .[ schema_name ] . | schema_name . } ]
    table_name
    [ WITH ( PARTITIONS ( { <partition_number_expression> | <range> }
    [ , ...n ] ) ) ]
[ ; ]

If you're looking for a more efficient way to delete data, I'd start here.


You can't index a rows-returning expression, so it would probably be best to normalize your data with one nickname per row, rather all stuffed into an array.  Then you could index the "nickname" table and join it to the "persons" table.

If you don't want to do that, you could transform the name field into a string, and index that.  This could lead to unwanted results if the query could span over the delimiter and still meet the cutoff (not very likely, unless the query also contains punctuation), so the queries are not identical, but might be good enough.

create or replace function arr_val_agg(jsonb) returns text as $$
  select string_agg(bar.value,', ') from jsonb_array_elements($1)
    join lateral jsonb_each_text(value) bar on true;
$$ language SQL immutable;

create index on persons using gin (arr_val_agg(name) gin_trgm_ops );

select * from persons where arr_val_agg(name) %> 'jhon';

As the lengths of arr_val_agg get longer, the more false positives you are likely to get from the trigram index which need to be filtered out by a recheck, so performance might suffer.


Oracle - Query to join fact table to bridge table and show bridge group rows in one row?

Based on your example output you can use LISTAGG. The basic query that you can add whatever other criteria to is;

SELECT DISTINCT (p.product_group_id ||' '|| LISTAGG(c.product_id||'  '||c.product_name, ' ')
                                   WITHIN GROUP(ORDER BY p.product_group_id, c.product_id)
                                   OVER (PARTITION BY p.product_group_id))
  FROM product c
       INNER JOIN
       product_bride p ON c.product_id = p.product_id

If you need more attributes from the product then you will need to concatenate them in the LISTAGG. I haven't included a join to the Fact table but that can easily be included if you need something from it.


If you want to see the records that have more than one value in Attribute_Value then you can't simply use filters on a WHERE because you want to validate rows in a group (a WHERE will filter by row, not by set).

Try using a GROUP BY with HAVING to check existance of multiple values for a particular set, then use this result to display the proper records.

SELECT
    D.Attribute_Name,
    D.ADAPTERID,
    D.Attribute_Value
FROM
    (
        SELECT
            Attribute_Name,
            ADAPTERID
        FROM
            database1
        WHERE
            Attribute_Value IN ('ACTIVE','DESIGNED AUTOMATICALLY')
        GROUP BY
            Attribute_Name,
            ADAPTERID
        HAVING
            COUNT(DISTINCT(Attribute_Value)) > 1
    ) Mult
    INNER JOIN database1 D ON
        Mult.Attribute_Name = D.Attribute_Name AND
        Mult.ADAPTERID = D.ADAPTERID

This will still display multiple records with the same Attribute_Value if repeated (by Attribute_Name and ADAPTERID), then you can just add DISTINCT right after SELECT to see uniques. You might also want to filter by Attribute_Value on the outmost SELECT in case you are just interested in this values.

SELECT DISTINCT
    D.Attribute_Name,
    D.ADAPTERID,
    D.Attribute_Value
-- ...

Also the INNER JOIN will filter rows that have NULL value on either Attribute_Name or ADAPTERID (on most databases). If you still want to display them you will have to add NULL check functions (the syntax depends on the DBMS, like ISNULL() for SQL Server or NVL() for Oracle).


When working with XML in SQL Server you use the xml Data Type Methods and when shredding XML documents you typicly use the nodes() and value() methods. The XML you have here also include a number of namespaces so you have to specify the ones you need using WITH XMLNAMESPACES (Transact-SQL).

The XML is quite complex so without knowing how you want the data extracted I can only give you a couple of sample queries that you then can modify to whatever it is you need.

You have four w:p nodes and here is a query that fetches the attributes from those nodes. Using @ specifies that it is the value of an attribute you want-

with xmlnamespaces('http://schemas.openxmlformats.org/wordprocessingml/2006/main' as w)
select P.X.value('@w:rsidR', 'char(8)') as rsidR,
       P.X.value('@w:rsidRDefault', 'char(8)') as rsidRDefault,
       P.X.value('@w:rsidP', 'char(8)') as rsidP
from @doc.nodes('/w:document/w:body/w:p') as P(X);

SQL Fiddle

If you in addition to that want the text in the w:t node you need to do a cross apply to a second nodes() clause that will shred the XML within the w:p node.

with xmlnamespaces('http://schemas.openxmlformats.org/wordprocessingml/2006/main' as w)
select P.X.value('@w:rsidR', 'char(8)') as rsidR,
       P.X.value('@w:rsidRDefault', 'char(8)') as rsidRDefault,
       P.X.value('@w:rsidP', 'char(8)') as rsidP,
       T.X.value('text()[1]', 'nvarchar(max)') as Text
from @doc.nodes('/w:document/w:body/w:p') as P(X)
  cross apply P.X.nodes('w:r/w:t') as T(X);

SQL Fiddle

You said in your question that you want to get the values from all the tags. I don't know how useful this is to you but you can build a Name-Value list with all the attributes and elements in the XML.

This will give you all elements.

select T.X.value('local-name(.)', 'nvarchar(max)') Name,
       T.X.value('.', 'nvarchar(max)') Value
from @doc.nodes('//*') as T(X)

Change '//*' to '//@*' and you will get all attributes.

select T.X.value('local-name(.)', 'nvarchar(max)') Name,
       T.X.value('.', 'nvarchar(max)') Value
from @doc.nodes('//@*') as T(X)

And you can combine them in one query as well.

select T.X.value('local-name(.)', 'nvarchar(max)') Name,
       T.X.value('.', 'nvarchar(max)') Value
from @doc.nodes('//@*, //*') as T(X)

SQL Fiddle


If you don't want point-in-time restore for your databases, switch them to SIMPLE recovery, and simplify your maintenance to:

Check Database Integrity
Update Statistics
Backup Database (Full)


Not able to access a database on shard mongodb cluster

Try db.repairDatabase() which fixes inconsistency across database.
Check mongod and mongos logs for results.


Sounds like PhpPgAdmin is probably doing a session-level SET bytea_output = 'escape'.

If you open a new psql session and SHOW bytea_output; it'll be hex there.

PhpPgAdmin may offer a way to control this behaviour, or it might be hard-coded to work around compatibility issues (if it doesn't understand the hex format for bytea).


Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

You're getting the same object_id multiple times because you get a row per index, not a row per object. The point of this DMV is to show you which indexes are getting used more or less than others. So maybe you want:

select  I.object_id
            ,S.name
            ,si.name
            ,I.last_user_scan
            ,i.user_scans
            ,i.user_lookups
            ,i.last_user_update
            ,i.last_system_scan
            ,i.user_updates
            ,s.modify_date
from sys.dm_db_index_usage_stats I
Inner Join sys.objects S
ON I.object_id = S.object_id
INNER JOIN sys.indexes AS si
ON I.[object_id] = si.[object_id]
AND I.index_id = si.index_id
WHERE type = 'U';

If you really just want the last access and the sum of all activity against a table, without caring about which index(es):

SELECT s.name
            ,MAX(I.last_user_scan)
            ,SUM(i.user_scans)
            ,SUM(i.user_lookups)
            ,MAX(i.last_user_update)
            ,MAX(i.last_system_scan)
            ,SUM(i.user_updates)
            ,MAX(s.modify_date)
from sys.dm_db_index_usage_stats I
Inner Join sys.objects S
ON I.object_id = S.object_id
WHERE type = 'U'
group by S.name;

These aren't ideal because if you have dbo.Accounts and test.Accounts, they will get grouped together.


SQL log file 2TB is not enough, now what?

multiple log files are bad

It's not that multiple log files are bad... It's that multiple log files are totally unnecessary and provide no benefit ... unless you require a log file that is larger than 2TB.

With data files, SQL Server can benefit from multiple files because it will perform parallel I/O to/from both files at the same time. However, with log files, SQL Server will only write to one or the other. The circular nature of the log file means that if you create two files, SQL Server will write to one, get to the end, then write to the other, get to the end, then go back to the first, etc.

There is no performance gain from having multiple log files.

However, because of the capacity limitation that any log file can be at most 2TB in size, if you need more than 2TB of log file, you would need to create multiple log files. This is the only reason to create multiple log files.

Why do you need a 2TB log file?

  I just can't imagine a scenario where having 2TB of log files even occurs

Because your transaction log will grow during full backups, if you have a very large database that takes a log time to back up, and is very busy during backups, you may generate more than 2TB of transaction log during the backup.

Similarly, if you have a very large, log-running transaction (such as an index rebuild on a very large table), you may generate 2TB of transaction log before it can be re-used.

Of course, your transaction log can grow for a number of reasons. These aren't necessarily normal to make your log grow very large, but they do contribute to growth. If your transaction log backups are not completing successfully, or your AG is not synchronizing, or replication is not reading transaction from your log....... your log file will grow. If those problems continue, your log file will eventually reach 2TB.

That said, the need for 2+TB of log file is very rare.


mongodb read data from secondary hidden replicaset

Hidden replica set members are invisible to client applications via replica set or mongos connections so all three of your examples are working as designed.
As per the documentation on Hidden Replica Set Members:

Clients will not distribute reads with the appropriate read preference to hidden members. As a result, these members receive no traffic other than basic replication. Use hidden members for dedicated tasks such as reporting and backups. Delayed members should be hidden.
In a sharded cluster, mongos do not interact with hidden members.

You can only connect to a hidden member directly (i.e. by specifying a single hostname and port). Read preferences and tags are ignored for direct connections.
For example, any of the following should work assuming server1-replica-3 is a resolvable hostname and you aren't blocked by firewall configuration:
 mongo server1-replica-3:27018
 mongo --host server1-replica-3 --port 27018
 mongo mongodb://server1-replica-3:27018

If you have a sharded cluster with multiple hidden members, you can only read from one hidden member at a time via a direct connection. Note that bypassing mongos with a direct connection to a hidden secondary may return stale or orphaned data for a sharded collection.
If you want to dedicate nodes for specific read use cases in a sharded cluster, instead of making these hidden you should use secondary read preferences with replica tag sets. Be aware that reading from secondaries may return stale, duplicate, or orphaned data for a sharded collection as at MongoDB 3.4. For more background, see SERVER-5931: secondary reads in sharded clusters need stronger consistency. An improvement for SERVER-5931 was recently committed to the MongoDB 3.5 development branch, so secondary reads in sharded clusters should be safer in the MongoDB 3.6 production release (expected later this year).


You can do this by putting the GROUP BY in a subquery. Here is a solution:

CREATE TABLE #1 (ID int, TotalAmount decimal(20,2));
CREATE TABLE #2 (ID int, Individualamount decimal(20,2));

INSERT #1 VALUES (100, 1000), (101, 500);
INSERT #2 VALUES (100, 500), (100, 300), (100, 100), (101, 200), (101, 300);

SELECT a.ID
, a.TotalAmount
, c.[Sum(IndividualAmount)]
, d.[Diffence(TA-IA)]
, e.[%of diff]
FROM #1 a
LEFT JOIN
(
    SELECT b.ID, [Sum(IndividualAmount)] = SUM(b.IndividualAmount)
    FROM #2 b
    GROUP BY b.ID
) c
    ON c.ID = a.ID
CROSS APPLY
(
    SELECT [Diffence(TA-IA)] = a.TotalAmount - c.[Sum(IndividualAmount)]
) d
CROSS APPLY
(
    SELECT [%of diff] = 100.0 * [Diffence(TA-IA)] / NULLIF(a.TotalAmount, 0)
) e;

Results:

ID   TotalAmount  Sum(IndividualAmount)  Diffence(TA-IA)    %of diff
100      1000.00                 900.00           100.00   10.000000
101       500.00                 500.00             0.00    0.000000


Oracle PK index extremely low performance

As there are more than 10% of the rows with access("MYHIST_ID">4615200), a full table scan is the fastest way to select them.

If you would try a much higher number (eg. "MYHIST_ID">46152000), Oracle's optimizer may prefer using that index.


Is there a way to partition a mysql table, by range columns, where the 2 columns in question are of datetime and bigint?

The solution can be:

create table temp
(id int not null primary key,
 doj datetime not null primary key,
 doj_month tinyint) -- add field for partitioning
partition by range columns(id,doj_month)
(partition p0 values less than (1, 13),
 partition p1 values less than (2, 2),
 partition p2 values less than (2, 3),
 partition p3 values less than(maxvalue, maxvalue));

-- create triggers for additional field calculations
create trigger tr_i_temp
for insert on temp
for each row
set NEW.doj_month = month(NEW.doj);

create trigger tr_u_temp
for update on temp
for each row
set NEW.doj_month = month(NEW.doj);

But you must to remember that there exists a lot of cases when triggers are not fired (cascade operations, LOAD DATA, etc.). If you use such operations you MUST update this additional field "by hands" (for example, update query which reassigns doj field).


grant permissions in oracle from one database to another

If you actually have different databases (and it sounds like you do), you can't grant permissions for users in one database to access data in another database.  You'd need to create a database link in the EmployeeDB database that connects to the ContractorDB database.  That database link can either use a fixed username and password in the ContractorDB database (i.e. create an EmployeeUserRemote user in ContractorDB with a password that doesn't change that can be hard-coded into the database link definition) or that database link can be a CURRENT_USER database link in which case the username and password would need to match in the two databases for every user that wants to use the database link.  Queries that use the database link log in to the remote database (ContractorDB) as the specified user and have the privileges of that user in the remote database.


Oracle expdp et impdp : order of schemas?

You have no direct control over the order on objects creation. Anyway, you can try to perform a full export, and then, run an impdp only of the schemas you need.
For example:

expdp system/**** full=y directory=<dir> dumpfile=<dumpfile>

and then import all the schemas you want (in the order you need):

impdp system/**** directory=<dir> dumpfile=<dumpfile> schemas="schema1, schema2, .. , schemaN"

I have not done a test, but you can give it a try.

Hope it helps.