Identity seed increased when using IDENTITY_INSERT Running this snippet on SQL Server 2008 produces different result than on SQL Server 2014. I need them to produce the same result.

My scenario is that I am writing unit tests (using tsqlt) and this code is part of a huge procedure which I am trying to test. In my test code I can do anything because that code will never end up in production. This "bug" does no damage in production, but is giving me problems when writing my tests. Meaning, I do not want to change the code, only the tests.

My local dev environment is SQL Server 2014 but some of our CI environments are still SQL Server 2008.

My first question is if there is any way to avoid this (bug) on 2008?
My second question is if there is any way to make 2014 behave the same as 2008  and thus making the tests pass on both 2008 and 2014. Any setting or trace flag or hack?

My current hack is to reseed the table to 0 (instead of 1) in case server is 2008. I do no not like this as I will need to change it or remove it when our environments are upgraded.

IF CHARINDEX('2008', @@VERSION) > 0 BEGIN
        -- HACK to fix identity bug in SQL 2008.
        DBCC CHECKIDENT('...peter', RESEED, 0);
END

Troublesome code:

IF OBJECT_ID('tempdb..#peter') IS NOT NULL DROP TABLE #peter;
CREATE TABLE #peter(ID INT IDENTITY(1, 1), VALUE CHAR(10));

SET IDENTITY_INSERT #peter ON;
INSERT INTO #peter( ID, VALUE )
VALUES  ( -1,'Thing' ); -- Explicit negative ID value inserted here
SET IDENTITY_INSERT #peter Off;

INSERT INTO #peter( VALUE )
VALUES  ( 'Stuff' );

SELECT * FROM #peter;

Result in SQL Server 2014:

SQL Server 2008:

Server versions:

Microsoft SQL Server 2008 (SP4)
Microsoft SQL Server 2014

This is a known behavior change that was reported on Connect and closed as "by design." You'll notice that the docs for 2008 & 2014 are slightly different, partially to reflect that the output of commands like DBCC CHECKIDENT() (2008 vs. 2014) better reflect reality.

The workaround you're using now is as good as any I can think of, since there is no magic trace flag to change the behavior of either version. It does seem odd to me that you prioritize your tests over the code. You should be making your tests so that they can interpret all code paths, not changing the code to match the one output the test expects. I have to wonder why the test even cares what surrogate ID value is generated by the database, because you shouldn't IMHO. These should be invisible to users and only used for referential integrity.


Object id error in sql server I want to know the object referenced as an associatedObjectId in a deadlock graph, so I ran the below query:

SELECT OBJECT_NAME(1234560112364076)

I get the following error:

  Arithmetic overflow error converting expression to data type int.

That looks more like a hobt_id than an object_id. Try:

SELECT [schema] = s.name, [table] = o.name, [index] = i.name
FROM sys.schemas AS s
INNER JOIN sys.objects AS o
ON s.[schema_id] = o.[schema_id]
INNER JOIN sys.partitions AS p
ON p.[object_id] = o.[object_id]
INNER JOIN sys.indexes AS i
ON i.index_id = p.index_id
AND i.[object_id] = p.[object_id]
WHERE p.hobt_id = 1234560112364076
GROUP BY s.name, o.name, i.name;

Also, I recommend opening the .xdl in SentryOne Plan Explorer, which should present you with friendly object names instead of long, indecipherable ids. I explain the deadlock analysis functionality here.


scheduler_count in Azure SQL DB I'm trying to find a scheduler_count for my Azure SQL DB, so that I can get sp_Blitz to run. On all other platforms, scheduler_count can be read from sys.dm_os_sys_info, but this dmv is not available currently.

SELECT scheduler_count, * FROM sys.dm_os_sys_info;

Do you know of a way to find it?

I also need a (estimate for) cpu_count.

Unfortunately this DMV is not accessible.

Adam Machanic has made a user feedback item, you can cast your vote to urge Microsoft to make it available.

It has been picked up by Microsoft, and has been under review since  april 6 2018.

You could get your DTU count or CPU count, depending on your model from a certain reporting interval from the sys.dm_db_resource_stats DMV.

SELECT  dtu_limit ,MAX(end_time) as end_time
FROM sys.dm_db_resource_stats
GROUP BY dtu_limit; -- DTU Model

SELECT  cpu_limit ,MAX(end_time) as end_time
FROM sys.dm_db_resource_stats
GROUP BY cpu_limit; -- VCpu Model

The maximum of 5 Dtu's on my basic tiered database is returned by the query

dtu_limit   end_time
5   2019-03-21 19:17:05.237

And as a result of being on the DTU model, VCpu count will return NULL.

cpu_limit   end_time
NULL    2019-03-21 19:17:05.237


why should use MongoDB In which types of projects, MongoDB is the best suited?
Can we use MongoDB in large applications?
Why Open Source CMS and framework doesn't have support for MongoDB?
How it is different than other open source RDBMS like MySQL and PostgreSQL?

Mongo is NOT an RDBMS. Don't treat it like one and you'll avoid a lot of mistakes. This is why applications that require an RDBMS don't support Mongo.

MongoDB is well suited to applications where:

The working set of documents (and their indexes) can be kept in RAM.
Document sizes do not change much between creation and steady state.
The schema needs to be flexible.

It can definitely be used in large applications, with the right engineering. It depends a lot on how the application plans to use Mongo, though. With sharding to spread write loads you can get to some very high throughputs.


Track MySQL Procedure Changes in MySQL We want to keep track of changes being done to MySQL procedures only, actually recently our procedures seem to have overwritten to some previous versions and we were unable to track it that who did this. We were able to track thread-id from mysqlbinlog but were not able to determine whose userid was using that thread-id.   Is there any way by which we can do following

• Track user-id from mysqlbinlog thread-id
•   Keep track of every create procedure/drop procedure

We are using MySQL 5.7.20 community edition on windows 10 & CentOS7 and we are using row level bin-logging

Any help is greatly appreciated.

You can use an audit plugin, such as MariaDB's audit plugin which also works for MySQL, see:
https://www.percona.com/blog/2016/02/15/mysql-mariadb-with-mariadb-auditing-plugin/

For MariaDB's plugin, you can configure it to log DCL queries only, which should cover CREATE/DROP PROCEDURE.


Optimizer logical trees: Order of the different tree types? Based on observing output from Traceflags 8605, 8606 and 8607 (and comments from i-one), I have this as the order in which the logical trees are generated.

Converted
Input
Simplified
Join-Collapsed
Before Project Normalization
After Project Normalization
Output

Outside of the ordering of Converted and Simplified, I have not however found independent verification that my assessment is correct.
Is this the correct order?


Delete millions of rows disabling Indexes I have a table with millions of rows. I'm deleting where year(data) in (2013,2014,2015). It's taking more than 4hrs. I'm trying now to delete only year 2013 but it's still very very slow (every year has like more than 600.000 rows and it has a varbinary column to store .PDF files).

I know that indexes can decrease performance on deletes. It will be a good Idea to Disable both indexes in that table, and then re-enable them?

Can it be faster if I create a view, and delete from the view?

Edit:

I made this query to delete it by top 1:

delete top (1) from MyTable where year(data) in ( 2013)--,2014,2015)
while @@rowcount > 0
    begin
    delete top (1) from MyTable where year(data) in ( 2013)--,2014,2015)
    end

Depending on how interconnected this table is with others in your database, and assuming that the table only has data for 2013-present, the fastest solution might be:

Script out your original table, and create a new empty table with the same structure.
INSERT the data for 2016 and 2017 into the new table.
DROP the original table.
Rename the new table to the original table's name (see the docs for ALTER TABLE).

Deletes are generally enough slower than inserts that it's probably faster to copy out 25-30% of the records in the table than to delete 70-75% of them. However, of course, you need to have sufficient disk space to hold the duplicates of the data to be kept to be able to use this solution (as noted by Toby in the comments).

If you do this, you'll want to be absolutely certain that the new table ends up exactly like the original, including any indexes, triggers, etc. You might want to truncate the original, rename it, and keep it around for a while instead of deleting it, just to be sure there's nothing you've missed. Also, outside of any clustered index, you may want to add clustered indexes and triggers after you've inserted the 2016 and 2017 data. If triggers are involved at all, make sure that whatever you do leaves the rest of your data in a valid state.

If other tables reference your table in foreign key relationships (as suggested by Joe Obbish in the comments), then this becomes somewhat more complicated. I would recommend scripting out all the foreign keys that point to this table, removing them, and then recreating them after the new table has been renamed. See this link to an article by Aaron Bertrand for help with this.


alter column varchar to nvarchar - will file group on full drive have issues? Our database server has 2 drives.
The first drive is full, so we had to add a new file on the 2nd drive so new data went to that drive.
This all worked great, and the mdf on full drive stopped growing and all new data goes to drive 2.
I now need to alter a column to go from varchar to nvarchar.
I know a lot of this data exists on the first drive in the MDF file that has 9mb remaining.
What will happen if I try to alter the column as the pages are on the full drive?
Is this a valid concern or will the file group stuff work it out and put the bigger column on the new drive?

So 1 filegroup with 2 files, one on each drive ?
In this case the writes will be distributed according to the space available on each file -
see the MS reference :- https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-files-and-filegroups?view=sql-server-ver15
So the SQL Server engine should sort it out - however you might not get the best performance for this you would probably want a new filegroup with a number of equally sized files - but life gets complicated quickly and for most databases it is not worth the extra planning / management for the small percentage performance improvements available.
Only precaution I would take is to set the maximum size of the file on the nearly full disk to be equal to its current size, as you wouldn't want it to attempt an autogrow and for that to fail.


How to order query results by tag weight in IN('tag1', 'tag2', 'tag3') Consider a simple query as

SELECT * FROM posts
JOIN tags USING(post_id)
JOIN tag_map USING(tag_id)
WHERE tags.tag IN('tag1', 'tag2', 'tag3')

tag_map is a many-to-many relationship between tables posts and tags.

In this query, mysql will find all posts tagged by any of these words, and order them by PRIMARY KEY.

How can we order the results by tag weight:

posts witch tagged with more words in the list (first posts with three tags from the list, then with two words, ...)
posts witch tagged with first words (first posts tagged with tag1 then posts tagged with tag2).

I did

select *
from posts p
join tag_map tm on tm.post_id = p.post_id
join tags t on tm.tag_id = t.tag_id
join
  (select post_id, count(tag_id) as tag_count
   from tag_map
   group by post_id) counts
  on counts.post_id = p.post_id
where t.tag in('tag1', 'tag2', 'tag3')
order by tag_count desc, tag asc

and it seems to work OK according to your specs:

Order by tag count - I joined with a sub-select that retrieves the tag count per post_id and ordered primarily by that count (descending)
Order by tag name - after ordering by tagcount/post, the query orders by tag name

Here's the SQLFiddle if you want to experiment more: http://sqlfiddle.com/#!2/8fcd7/9

Update: version of the SQLFiddle with index on (tag_map.post_id): http://sqlfiddle.com/#!2/02b5e/1

PS: this is my first MySQL query, so if there are any criticisms regarding performance/style/anything else, I'd be glad to hear them and learn from them.


I get a query error 'unknown column' while the column exists I get error (1054): Unknown column 'wp_jpnx_survey_code.supplier_id' in 'field list'But that column does exist... Does anyone know why or how to fix this?

This is the code I used:

CREATE VIEW survey_codes_complete_overview AS SELECT *,
wp_jpnx_survey_code.supplier_id AS a_id,
wp_jpnx_survey_supplier_info.supplier_id AS b_id FROM
wp_jpnx_survey_code AS a LEFT JOIN wp_jpnx_survey_supplier_info AS b
ON a.supplier_id = b.supplier_id;

You are using aliases for those tables. So, specify the alias when selecting the column.

CREATE VIEW survey_codes_complete_overview AS
SELECT *,
a.supplier_id AS a_id,
b.supplier_id AS b_id
FROM wp_jpnx_survey_code AS a
LEFT JOIN wp_jpnx_survey_supplier_info AS b
ON a.supplier_id = b.supplier_id


How to understand what Columns Have Changed in Merge Replication I am using merge replication in SQL 2012. Some of my merge articles use column level tracking.

I am trying to understand how to use the 'lineage', and 'colv1' fields in the MSmerge_contents table to work out which columns have changed in a particular row in my database.

You can use sp_showlineage and sp_showcolv to inspect the lineage and columns that have been changed.  The sp_showcolv results contain a column, colidx, which is the index of the column(s) that were changed for a given version of the row.

If you need to take a more proactive approach, you can setup an auditing scheme.  I've provided an example of how to setup an auditing scheme on my blog:  http://www.sqlrepl.com/sql-server/auditing-changes-in-merge-replication/


Data modeling for Warehouse I am learning about database warehouses and data modeling. I came across this blog post. In the section, "DIFFERENT MODELS HAVE DIFFERENT PURPOSES" the author mentions that depending upon the need of the business, there can be two types of data modeling:

Relational
Dimensional

However, in both examples the author says that the modeling is done in 3NF form, I am confused, how is 3NF and star schema both used in dimensional model. Aren't they supposed to be fundamentally different?
Lastly, if I understand correct, the relational data modeling is done for OLTP systems and Dimensional is done for OLAP systems, right?

The post you mention does not say "modeling is done in 3NF form" in both relational and dimensional models. It only states that 1) star schemas are common in dimensional models and 2) 3NF schemas may also be used. It's an over-simplified statement but keep in mind the post is titled "Data Modeling Explained in 10 Minutes or Less" :).
In a nutshell: the biggest difference between dimensional, tabular and relational modeling is the measure of duplicate data being stored. Dimensional and tabular models contain (much) more duplicate data (de-normalized), but that allows them to be able to quickly 'slice and dice' it. Relational models do their best to store data only once in the model (normalized), allowing for fast transactional processing.
One does not exclude the other. It all comes down to which model provides the best performance and widest range of possibilities. And that, in turn, boils down to using the right tool for the right job.
Which leads me to your last question: relational models are commonly found in OLTP systems and dimensional (or tabular) models are commonly found in OLAP systems.


Should I Denormalize Tables To Avoid Joins? I'm in the process of developing a web application, and need some help with some basic db design.  Currently, I am unsure of the following three tables:
### User ###
 - Id
 - DetailId
 - etc... (About 6 more columns)

### Contact ###
 - Id
 - UserId
 - DetailId
 - etc... (About 6 more columns)

### Detail ###
 - Id
 - FirstName
 - LastName
 - etc... (About 10 more columns)

I am starting to wonder if it may be best to just eliminate the Details table and simply denormalize those columns back into the User and Contact tables.  Being that these tables will be read much more than they will be edited or updated, this denormalization should improve read performance by avoiding a table join. Is this a sound judgement or am I just falling for the root of all evil (Premature Optimization).  Either way, which would be considered a better practice?
Edit/Update
Thanks for the great feedback!  I realize I should have been a bit more descriptive, so here is some additional information on the use case for these tables.

The Details table contains mainly display information, like name, address, phone number, email, but this is NOT account information.  Instead it is more like print information, that will be used when the user prints quotes, invoices, etc...

Details table contains only columns that are duplicated in both a user and contact in terms of printing data.  So if I denormalize those columns back to the User and Contact table, any edits in column definition (or adding/removing columns) will have to be made twice.

The Users table may only have 100 records, but the Contacts and Details tables could potentially hold hundred of thousands, because each user can have hundreds of contacts.
But to be clear, a Detail "should" only belong to one Contact or User. However, a User can have many Contacts. So If there are 100 Users and each User has 100 Contacts, that's 10,000 Contacts, and 10,100 Details.

I would say that having FirstName and LastName in the User table is still normalized. Having the data in different tables seems excessive, and in practice you'll have a 1-1 relation between the two.

If you expect a lot of read traffic on the User table, add appropriate indexes to that table instead.


Proc runinng slow once in a while I am trying to troubleshoot the performance issue on one of the SP which executes throughout the day daily every 20 mins and on avg completes under a min.

For past couple of weeks it happens once in a week that it will stuck and keep running for hours. Stats maintenance is done daily for modified stats and automatic update stats on is ON

There is no change to SP neither too much of load being increased in that run time.

I recompiled that SP to see if the plan was bad due to parameter sniffing issue, but does not help.

Due to security constraints i am not able to share the plan or any table structure here.

I know without seeing plan and query its tough to comment, but need possible ideas on what else should i be checking here?

Is auto_update_stats_async on or off?

If this is off, it is possible that the query prepares, the stats are being discovered to be stale, and updated before the query executes.

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options?view=sql-server-2017#auto_update_statistics_async


Many-to-many database I am thinking about making a music database. Something where you can search by
artist, album or song. This seems to be a Many-to-many model, as an album can
have multiple songs, and the same song can be on different albums.
Currently I am just using some JSON files, but this isnt really robust, as it
can only represent hierarchical data.
This would just be a personal project, so I dont need anything too involved. I
have looked at SQLite, but also Document-oriented databases. I like the idea of
being able to persist the data as JSON or CSV files. What is a good option for
my use case?

Response to some questions:

Do you want a free solution or need to use an enterprise level system like
Microsoft SQL Server which costs money.

Free options only

How far do you expect to scale your database?...in other words, in the
foreseeable future roughly how big do you expect it to get (data-wise), how
many users do you expect will be using it both overall and at a given instance
of time, how often will data change inside of it?

Most likely it would be a tool that only has one user at a time. So either I
would use it myself, or someone might download my tool and use it for
themselves.

What does your current technology stack look like?

I would prefer something with of of these if possible: C#, D, Go, Nim or Rust.
Although I have seen many options in C and C++, so I might have to end up with
one of those.

I don't think any modern solution out there persists the underlying data of
the database in JSON or CSV files (though I could be mistaken).

I think Document-oriented database offer this, but I could be
misunderstaning it. Also I had seen Export to CSV for SQLite.

Any answer to your question will be subjective and somewhat high level (since there's not a lot of details to go off of).
To clarify the purposes of a Document-oriented database system, it's purpose is for storing non-relational or loosely-structured data. It's not necessarily for actually directly storing files (such as JSON and CSV files) themselves. Rather it'll store loosely-structured data (such as what JSON can be used for) in it's own system (with it's own format under the hood).
When to choose an RDBMS vs a Document-oriented Database System (or a NoSQL database system)?
Choose a RDBMS if your data exhibits most of the following:

Strongly structured, i.e. concretely defined as opposed to loosely defined e.g., the database will have Artists, Albums, and Songs. The structure of each object (entity) type will be mostly similar across each instance of that object type
Each instance of a particular type of data shares mostly the same attributes e.g., all songs will have a Title, Artist, Song Length, and may have lyrics or a genre
Relationships are easily definable among the data, e.g. A Song belongs to one-or-many albums, one-or-many albums belong to an Artist
Your use cases involve various querying that'll involve filtering by different attributes of the data, e.g. You want to know how many Albums are particular Song is on? Or how many new Songs were created in 2019 by Artists of specific Genres? etc

Choose a Document-oriented / NoSQL database system if your data exhibits most of the following:

There isn't a strong structure to the objects and their properties

Or the structure will be vary various or highly changing over time

If you don't need to answer more complicates questions by querying the data via it's structure and relationship with itself

E.g. if some of your Songs will only have Titles, others will only have Genres, and some don't have Artists at all. Sometimes there will be Artists and sometimes there won't be any Artists, and you want to store the historical information about some Artists and their families sometimes. And you want to store all Albums and all Songs in the same place as the Artist they belong to (so when you read back from the database, it's all or nothing).

It sounds like you have a basic relational data use case (by inferring from your example), and while most modern types of database systems can probably handle your use case, the best fit is likely a Relational Database Management System (RDBMS). Examples of an RDBMS are SQLite, PostgreSQL, Microsoft SQL Server, Oracle PL/SQL, MySQL.
Each of the aforementioned RDBMS have their own pros and cons, and features that are available in one system that are lacking in another. Microsoft SQL Server and PostgreSQL probably are the most evolved of the examples, but for what sounds like a very basic use case, any of them would be applicable.
Things you should consider are:

Do you want a free solution or need to use an enterprise level system like Microsoft SQL Server which costs money. (Again, speculating, but your use case is very basic and likely can use of the free solutions available.)
How far do you expect to scale your database?...in other words, in the foreseeable future roughly how big do you expect it to get (data-wise), how many users do you expect will be using it both overall and at a given instance of time, how often will data change inside of it? SQLite is a great basic and free database system which could be perfect for your use case if you don't expect it to scale into a database that is billions of records big and petabytes of data with millions of active users, otherwise a more built out database system like the alternative examples I gave might be the better fit.
What does your current technology stack look like? Certain technologies are better fits and integrate more easily with certain database systems which might drive you to choose PostgreSQL over MySQL, or Microsoft SQL Server over Oracle PL/SQL. (E.g. Microsoft SQL Server works very nicely with Microsoft's technology stack: .NET & C#, etc).
Where do you plan to host the database?...Locally, Mobile Storage, or in the Cloud?...Linux or Microsoft Server? (These will filter down your possible options.)

I don't think any modern solution out there persists the underlying data of the database in JSON or CSV files (though I could be mistaken). Some of the examples I provided above can persist the data in the structured JSON format itself in the database. They also can ingest, digest, store, and export data to / from CSV and JSON files. For example, Microsoft SQL Server has functions for storing, manipulating, and reading back JSON within the database itself. It also can export data into various formats such as CSV.
There are also solutions, like in AWS, where you can store the files as JSON or CSV and then their database technologies utilize another AWS process to load the database from those files, but that's also applicable to other database systems like Microsoft SQL Server.
To directly answer your question (which again this is subjective) is if you're just getting starting with databases, SQLite is a pretty lightweight and easy system to start with. It's free, and is pretty developer friendly for someone who doesn't have a lot of experience writing SQL queries. You can at least get an idea of how databases work while getting started and still in the early phases of your project and could always port it over to any other database system if you choose to later (especially if your use case remains as simple as you described in your question).

For more information on Pros, Cons, and varying features of some of the example Relation Database Management Systems I mentioned above, these articles make for good reads:

SQLite vs MySQL vs PostgreSQL: A Comparison Of Relational Database Management Systems

Difference between Oracle, SQL Server, MySQL and PostgreSQL

PostgreSQL vs. SQL Server (MSSQL) - Extremely Detailed Comparison

To summarize some of the key information above that's relevant:
SQLite: Small footprint, user-friendly, portable BUT limited concurrency & scale, and lack of user management
MySQL: Popularity and ease of use, additional features like replication BUT known limitations (lacks FULL JOINs and other modern features), development of new features has slowed down, larger data concurrency issues
PostgreSQL: SQL compliance, open-source and community-driven, extensible BUT potential memory performance issues
Microsoft SQL Server: Very performant, flexible, lot of built in features out of the box BUT generally costs money (there is a minimalize free version)


Execute Stored Procedure with Account that has only read access I created a stored procedure that has TRUNCATE and INSERT statements in it that I use for an SSRS report.

I typically only use an account that is a member of db_datareader for SSRS reports, however this stored procedure will not execute with an account that only has those rights.   The following error is reported when attempting to execute the stored procedure:

  Cannot find the object "table_name" because it does not exist or you do not  have permissions.

Do I need to use an account who has db_datawriter members to execute this SP or is there a way for me to have my db_datareader account execute it?

When I have a situation where someone (AD group or user) needs to TRUNCATE a table or a set of tables, I write the code in a stored procedure using the WITH EXECUTE AS 'dbo' clause, and grant execute rights to this procedure to the AD group/user who is designated to do the job.

For example:

CREATE PROCEDURE dbo.TruncateMyTable
WITH EXECUTE AS 'dbo'
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    -- Delete all existing rows from tables as we only need a daily snapshot
    TRUNCATE TABLE dbo.MyTable;
END
GO

GRANT EXECUTE ON [dbo].TruncateMyTable TO [User];

Stored procedures created with the WITH EXECUTE AS dbo clause allow the stored procedure to run under the security context of the server principal who owns the database where the stored procedure resides.  This inherently allows the stored procedure to do things like TRUNCATE TABLE that normally wouldn't be possible without db_ddladmin or sysadmin rights.  In the example above, the [User] database principal will be allowed to execute the dbo.TruncateMyTable procedure, and thereby truncate the dbo.MyTable table.


Could you monitor all changes made within a postgresql database by somehow subscribing to the replication log? I've been reading about postgresql replication and was wondering if it was somehow possible to listen to the replication binary log and push that to some tool like sumologic or splunk so that any changes made could be monitored without having to build update triggers on every table. Is this possible?

As I understand it, your options depend a bit on a key requirement: Do you need the changes recorded in exact transactional order or not?

If you need them in exact order, then a change table or replication decoding are the two workable options I've heard of. Postgres does not implement incrementally updated materialized views, so that's out.

If you do not need the changes in precise commit order, then you can use an alternative strategy. For tables of interest, include a column that takes an ever-incrementing number. This is first assigned on INSERT (easy) and then you have to manage updates on UPDATE (harder.) From there, you have a number line that you can select from. If you track the last number you processed, you can then grab everything > than that, and how you have your unprocessed rows. The primary advantage of such a solution is that you can use that same number line to track different rollups, etc.

Logical decoding is probably going to be simplest.


More Databases Per Sql Server 2005 Instance Our company currently hosts about 400 databases per SQL Server 2005 instance.  500-600 databases and we see performance start to deteriorate.
Database size ranges from 50mb to 40-60gb in the extreme, with probably an average of maybe a few GB.

What are some good metrics to collect and how would we get these to determine our bottleneck?

Is it a performance limitation of SQL Server to have too many databases?  Is it all of the cached procedure plans for hundreds of stored procedures for hundreds of databases?  Is it inefficient queries? Or a combination?

What is likely to help us increase the number of databases per server the most?  All of the schemas are identical and the databases separate each customer's data.  Maybe refactoring everything so we can combine multiple customers into one db and filter by customer, or optimizing our queries to the extreme? Or maybe its just a SQL Server limitation?

It's going to be a challenge to apply a uniform policy across such a varied estate. My first port of call would be to understand the biggest consumers of key resources (CPU, memory, IO) on each instance, with a view to isolating the problematic databases from the others. Glenn Berrys healthcheck DMVs would be a good starting point.

Who's using most of the memory (buffer pool) on each instance?

SELECT DB_NAME(database_id) AS [Database Name],
COUNT(*) * 8/1024.0 AS [Cached Size (MB)]
FROM sys.dm_os_buffer_descriptors
WHERE database_id > 4 -- system databases
AND database_id <> 32767 -- ResourceDB
GROUP BY DB_NAME(database_id)
ORDER BY [Cached Size (MB)] DESC OPTION (RECOMPILE);

How long are pages staying in the buffer pool?

SELECT cntr_value AS [Page Life Expectancy]
FROM sys.dm_os_performance_counters
WHERE [object_name] = N'SQLServer:Buffer Manager'
AND counter_name = N'Page life expectancy' OPTION (RECOMPILE);

Who's generating most IO?

SELECT DB_NAME(DB_ID()) AS [Database Name],[file_id], num_of_reads, num_of_writes,
io_stall_read_ms, io_stall_write_ms,
CAST(100. * io_stall_read_ms/(io_stall_read_ms + io_stall_write_ms) AS DECIMAL(10,1)) AS [IO Stall Reads Pct],
CAST(100. * io_stall_write_ms/(io_stall_write_ms + io_stall_read_ms) AS DECIMAL(10,1)) AS [IO Stall Writes Pct],
(num_of_reads + num_of_writes) AS [Writes + Reads], num_of_bytes_read, num_of_bytes_written,
CAST(100. * num_of_reads/(num_of_reads + num_of_writes) AS DECIMAL(10,1)) AS [# Reads Pct],
CAST(100. * num_of_writes/(num_of_reads + num_of_writes) AS DECIMAL(10,1)) AS [# Write Pct],
CAST(100. * num_of_bytes_read/(num_of_bytes_read + num_of_bytes_written) AS DECIMAL(10,1)) AS [Read Bytes Pct],
CAST(100. * num_of_bytes_written/(num_of_bytes_read + num_of_bytes_written) AS DECIMAL(10,1)) AS [Written Bytes Pct]
FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL) OPTION (RECOMPILE);

Which databases are consuming the most CPU and which particular procedures?

SELECT
    DB_NAME(qt.dbid) AS DatabaseName
  , SUM(qs.total_worker_time) AS [TotalWorkerTime]
FROM
    sys.dm_exec_query_stats AS qs
CROSS APPLY
    sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
WHERE
    DB_NAME(qt.dbid) IS NOT NULL
GROUP BY
    qt.dbid
ORDER BY
    SUM(qs.total_worker_time) DESC
OPTION
    (RECOMPILE) ;

SELECT TOP (1000)
    DB_NAME(qt.dbid) AS DatabaseName
  , qt.[text] AS [SP Name]
  , qs.total_worker_time AS [TotalWorkerTime]
FROM
    sys.dm_exec_query_stats AS qs
CROSS APPLY
    sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
WHERE
    DB_NAME(qt.dbid) IS NOT NULL
ORDER BY
    qs.total_worker_time DESC
OPTION
    (RECOMPILE) ;


Primary key and index column for Merge replication- Id vs GUID I currently am revising the tables structure for a few geographically distributed databases- all tables currently have a UUID that is set as primary key. Merge subscription is currently (and in the future) used to synchronize the databases and tables and it uses the UUID column for it.
I am having a few issues with performance.

Update: The UUID is supplied from the application end - not generated during data load.

Based on the research I have done (here is where I got much clarity on the issue Thank you! Thank you! )
https://stackoverflow.com/questions/11938044/what-are-the-best-practices-for-using-a-guid-as-a-primary-key-specifically-rega][1]

Here is the takeaway-

UUID as PK is ok- as long as it is is NOT Clustered
Do not create a clustered index on columns if you are not going to
search by the columns
Identity (bigint) + HostName much efficient as a clustered index

I am currently planning on designing to include an Identity , DatabaseServerName  and Date as a clustered Index and the UUID as a primary key only.

Here are my questions:-

since I DO WANT to enforce FK using the UUID columns - do I still
create a separate clustered index on identity?
All the joins are going to be based on the UUID- will I still benefit
from the clustered index?
Should I rethink joins on the UUID?If so- how should I go about it?

I disagree with some of that advice.  GUID as a clustered PK is ok as long as you use sequential GUID generation, eg NEWSEQUENTIALID().

Sequential GUID generation, even if only "locally" sequential, mitigates the bulk of the performance problems you will experience with random GUID generation.  The alternative designs end up just needing too many indexes.

You need GUIDs for distributed key generation, and that's never going to be as efficient as using int, but the uniqueidentifier column is a requirement for Merge Replication.  So your tables can simply be

create table SomeTable(Id uniqueidentifier rowguidcol default newsequentialid(), . . .)


Does the definition of 2NF prohibit _transitive_ dependencies on a partial key or only direct ones? We know a relation is in 2NF iff it is in 1NF and no non-prime attribute is dependent on a proper subset of the candidate key. But is "dependent" in this definition strict or loose in the following sense:

Given the set of dependencies

A -> B
B -> C

Is the relation

([D],[A],C)

(i.e., the candidate key is {D,A}) in 2NF?

The reason I ask is that there are no direct dependencies from the given set of dependencies with a proper subset of {D,A} on the left-hand side, but the transitive dependency A -> C does violate 2NF. So my question is:

In the definition of 2NF, are the "disallowed" dependencies direct ones only or do transitive dependencies "count" as violations? I think transitive dependencies do count (and that the relation above is not in 2NF), but in the sequence of 1NF, 2NF, 3NF, etc., as usually presented in the academic literature, transitive dependencies are typically only "removed" at the 3NF stage.

"Loose" dependency is the right idea

You seem to be thinking of "strict" vs "loose" or "direct" vs "direct or transitive" FDs where the explicitly given FDs are taken as the "strict"/"direct" ones. Such a notion of "transitivity" is only an artifact of how you chose to define the situation. The set of given FDs is only relevant in that their closure is relevant.

The definition of 2NF refers to all partial FDs and all full FDs. What particular ones given to describe a bunch that hold is irrelevant. When certain sets of FDs are present, certain other FDs are too. They are all just present together. By definition they are the ones in the closure of the given ones.

Re given FDs

It is a common problem that people will just say "these FDs hold". Note that that means "all the FDs in their closure hold", because if the given ones hold, the closure's hold--by definition of the closure. But if they don't say that the ones in the closure are the only ones that hold then you are (in general) limited in what you can determine about the relation. So usually they really mean, the only ones are the ones in the closure of the given ones. Ie that the given ones form a cover for the ones that hold.

Moreover if they say this in practice beware that they may not actually know that there aren't any other FDs than in the closure of the given ones, ie that they form a cover. Without knowing that there are no others it is possible to answer "what is the closure of these", but it may not be possible to answer "what are the CKs".

Transitive vs non-transitve FDs

The term "transitive" FD and "non-transitive" FD are defined and play a role in the definition of 3NF. (A non-transitive FD is one that's not transitive.) But they are not defined in terms of given FDs as "direct" either.

FD (functional dependency) A-> C is transitive when there is an X where A -> X and X -> B and NOT X -> A. (Likely that last conjunct is unexpected.)

Transitive FDs are those that hold as a consequence of all the FDs that hold, not just the ones given.

Similarly the definition of 3NF refers to all transitive FDs and all FDs. (2NF plus every non-prime attribute non-transitively dependent on every candidate key gives 3NF.) There is no notion of "direct" FD except "non-transitive" FD.


Determine structure of out cursor I have a function in Oracle that neither I nor my team wrote and is heavily obfuscated. However, it has a cursor out parameter. How can I find out the number and types that the cursor contains?

For an example of what I need, consider this cursor:

DECLARE
  TEMP1 VARCHAR2(500);
  TEMP2 NUMBER;
  TEMP3 RAW(16);
  TEMP SYS_REFCURSOR;
  PROCEDURE MAKE_TEMP(TEMP_RETURN OUT SYS_REFCURSOR) IS
  BEGIN
    OPEN TEMP_RETURN FOR SELECT DUMMY, 5, HEXTORAW('111') FROM DUAL;
  END;
BEGIN
  MAKE_TEMP(TEMP);
  LOOP
    FETCH TEMP INTO TEMP1, TEMP2, TEMP3;
    EXIT WHEN (TEMP%NOTFOUND);
    DBMS_OUTPUT.PUT_LINE('1: '||TEMP1);
    DBMS_OUTPUT.PUT_LINE('2: '||TO_CHAR(TEMP2));
    DBMS_OUTPUT.PUT_LINE('3: '||RAWTOHEX(TEMP3));
  END LOOP;
  CLOSE TEMP;
END;
/

If MAKE_TEMP were the function I'm trying to use, I would need to know that it has 3 values per row, and that the values are a VARCHAR2, a NUMBER, and a RAW in that order so I can define variables to hold the results.

I'm using Oracle 11.2.0.1.0, although I would appreciate if answers for newer versions exist as well.

Why I can't just look at the source

If you must know, the function is part or Oracle ASP.NET Membership Provider and is written by Oracle. I'm trying to debug a problem and would like to see what this function is returning. The function's source code starts like this:

create or replace
FUNCTION  "ORA_ASPNET_MEM_GETALLUSERS" wrapped
0
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
3
8
9200000
1
4
0
33

As you can see, it's (at best) going to be extremely difficult to extract any information about the cursor of that.

This example code shows how to parse a ref_cursor. There are additional columns available, see the definition of SYS.DBMS_SQL.desc_tab2 for a full list of available columns.

In this example, my ref_cursor is defined as select * from user_tables. You would of course substitute in your own ref_cursor.

DECLARE
   l_csrid       INTEGER;
   l_coldesc     SYS.DBMS_SQL.desc_tab2;
   l_colcnt      INTEGER;
   l_refcursor   SYS_REFCURSOR;

   FUNCTION tostring (p_label   IN VARCHAR2
                     , p_value   IN VARCHAR2)
      RETURN VARCHAR2
   AS
   BEGIN
      RETURN RPAD (p_label, 30)
             || ': '
             || p_value
             || UTL_TCP.crlf;
   END tostring;

   PROCEDURE myput_line (p_rec      IN DBMS_SQL.desc_rec2
                       , p_colnum   IN INTEGER)
   AS
   BEGIN
      DBMS_OUTPUT.put_line (   RPAD ('_', 30, '_')
                        || UTL_TCP.crlf
                        || tostring (p_label => 'Column'
                                   , p_value => p_colnum)
                        || tostring (p_label => 'Name'
                                   , p_value => p_rec.col_name)
                        || tostring (p_label => 'Type'
                                   , p_value => p_rec.col_type)
                        || tostring (p_label => 'Null OK'
                                   , p_value => CASE WHEN p_rec.col_null_ok
                                                     THEN 'Yes'
                                                     ELSE 'No'
                                                END));
   END myput_line;
 BEGIN
   OPEN l_refcursor FOR
      SELECT * FROM user_tables;

   l_csrid   := DBMS_SQL.to_cursor_number (rc => l_refcursor);
   DBMS_SQL.describe_columns2 (c       => l_csrid
                             , col_cnt => l_colcnt
                             , desc_t  => l_coldesc);

   FOR i IN 1 .. l_colcnt LOOP
      myput_line (l_coldesc (i), i);
   END LOOP;
END;


SQL Conditional Join - remove duplication from Stored Procedure This stored procedure accepts a search parameter.  I want to join on a table valued function if the search parameter is not null.  The way I have written it works, but it seems like lots of duplication of code.

Is there a better way of writing this query to avoid the check for the parameter?  Something like a conditional join?

--this is actually passed in to the stored procedure as a parameter which may be NULL (no search text just filters on page)
DECLARE @Search varchar(100)

IF @Search IS NULL

WITH Catalog_Search_Results(FIM_NO,Rnk)
AS
(
    SELECT I.FIM_NO, 0
    FROM IMF AS I
)
SELECT FIM_NO, RNK
    FROM Catalog_Search_Results
    ORDER BY rnk desc

ELSE

WITH Catalog_Search_Results(FIM_NO,Rnk)
AS
(
    SELECT I.FIM_NO,funcItemSearch.RNK
    FROM IMF AS I
    INNER JOIN dbo.Catalog_ItemSearch(@Search) funcItemSearch ON I.FIM_NO = funcItemSearch.FIM_NO
)
SELECT FIM_NO, Rnk
    FROM Catalog_Search_Results
    ORDER BY Rnk desc;

Your code looks ok. Conditional join would lead you to parameter sniffing issue and you used one of two most popular solutions to avoid it.
Also you may want to use the second approach - dynamic sql, something like this:

DECLARE @sql NVARCHAR(max);

SET @sql = N'
WITH Catalog_Search_Results(FIM_NO,Rnk)
AS
(
    SELECT I.FIM_NO, 0
    FROM IMF AS I
    '
IF @Search IS NOT NULL
   SET @sql = CONCAT(@sql, 'INNER JOIN dbo.Catalog_ItemSearch(@Search) funcItemSearch ON I.FIM_NO = funcItemSearch.FIM_NO');

SET @sql = CONCAT(@sql, ')
SELECT FIM_NO, Rnk
    FROM Catalog_Search_Results
    ORDER BY Rnk desc')

EXEC sp_executesql @sql, N'@Search varchar(100)', @Search


Data Schema For Stock Control / Multi Source Inventory I'm working on a project that involves stock control with multiple stock sources and sales channels. The overall hierarchy I've got so far looks like this;
Sales Channels <---- Allocated Stock Sources <---- Stock Locations (warehouses) <--- Stock Sub Locations <---- Shelf / Bin Locations

As far as rules go for how these entities relate to each other I've come up with this;

The system must have one or more sales channels, each sales channel
must have 1 or more stock sources, a stock source must have 1 or more
stock locations (warehouses / buildings / distribution centres ), a
stock location may have 1 or more Bin/Shelf locations.
A product may have 1 or more stock locations, may have one or more sub
locations in those stock locations and may have one or more Shelf / Bin
location.

First off, is this a solved problem where some reference schema exists I could utilise and save myself some headaches?
If there isn't a reference design for this situation, Am I best to build a 1 to Many relationship to assign stock to a sub location(s) and another 1 to Many relationship for Shelf Locations (if exists)
This is the ERD I've come up with so far;

You can check out the book Data Model Patterns by David Hay.  It has inventory and product modeling patterns.
It's hard to judge the logical correctness of model without more business details, but I think you should have someone with more experience in data modeling review the ERD so it meets business requirements.  You enumerated 1:many relationships, but you modeled many:many which more than meets what's stated.
From a technical perspective, you should not define the product table with PK column id char(36); define the column as product_id int or product_id raw(16) if you really think you have to.  RAW is an Oracle data type, use the appropriate data type for your DB platform.  This goes for all the tables.  Personally, I would not put id in all those "link" tables, it's not necessary.


Postgresql Write Only Primary Server Bottleneck for Streaming Replication I'm wondering about whether CPU/RAM plays an important role for the primary server for a Postgresql Streaming Replication setup where you have one primary server that receives all the write operations and hot standby servers which are used for read operations.

Intuitively the disk io is probably the most important factor but what about other factors like:

Number of CPUs
CPU power
RAM

Could you get away with having a fast SSD and skimping on the CPU/RAM? Or will something else bottleneck the write operations for the primary server?

This depends almost entirely on the workload - and has little or nothing to do with whether it's a standalone DB server or a streaming replication master.

Ideally you want your DB to fit within RAM. If it doesn't, add RAM until it does or you run out of money.

If you can't sensibly make your DB fit within RAM, make sure there's enough RAM to hold the heavily used indexes and any really hot tables if possible. Then start spending money on fast disk I/O instead.

If you do more than a tiny bit of writing, especially if it's not just inserts, ensure that your storage has fast disk flushes. You need this even if the database fits entirely in RAM. That means battery-backed write-back cache on a RAID controller, or good quality SSDs with proper, reliable power-loss protection.

For CPU, again, it depends on the workload. If you're I/O bound, lots of CPU is generally unnecessary. If you're not I/O bound (i.e. the DB mostly fits in RAM) then lots of CPU will benefit you more. Because PostgreSQL can't use more than one core per query (grr), workloads with few big queries want few fast cores, and workloads with lots of simple queries want lots of small cores.

Does this sound like a long and roundabout way of saying "it depends, benchmark against your workload"? Yep!

(BTW, read http://wiki.postgresql.org/wiki/Number_Of_Database_Connections and consider a connection pooler, no matter what your workload).


How to upgrade PostgreSQL 9.6 to 10 in Ubuntu 16.04? Is there any simple apt-get version?

Some people here said the long process was OK for them.
 (https://gist.github.com/delameko/bd3aa2a54a15c50c723f0eef8f583a44)

Is there any simple apt-get version?

Yes. As described in the webpage you linked to, you can install PostgreSQL 10 using the official posgres apt repository (instead of the default Ubuntu repository for 16.04):

sudo add-apt-repository "deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main"
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql

You can remove the old version (9.6) beforehand, e.g. by

sudo apt-get remove postgresql-* pgadmin3* pgagent

But make sure you backup your database first so that you can restore it after the upgrade as pointed out in the comments.


Supporting Multiple Oracle Clients I have an application where the data repository is moving to Oracle 11g.  This means we need the new Oracle 11g client on the application server to support the retrieval of their data.

Is it possible to have both the Oracle 10 and Oracle 11 clients running on the same server?

If not, can the Oracle 11 client support retrieval of data from an Oracle 10 database?

Yes, you can run multiple clients on one machine. It's mainly a case of making sure you set your ORACLE_HOME correctly before starting any work.

But you can talk to 10 DBs using the 11 client happily, so that would also work for you.


Temporary Tables With Nonclustered Indexes Including All Columns Background: Once per day several temporary tables that calculate a variety of different aggregated values are created. They all contain the same unique identifier (the field I would make a PRIMARY KEY on). Each table has around 100k rows with only 2–8 columns. Several JOINs and UNION ALLs are then done on the temporary tables. Every field in all of the temporary tables are used. Furthermore, most of the time every row is also used—worst case scenario, half of the rows is used somewhere and the other half is used somewhere else. Once the query is finished, the results are saved on the disk so that individuals can access the data for the rest of the day.
Question: Which of the following approaches should be the best fastest:
• Have no indexes on the temporary tables.
• Have a clustered index on all of the temporary tables (via the PRIMARY KEY declaration on the unique identifier).
• Have a nonclustered index on the unique identifier while including the remaining columns on all of the temporary tables.
• The last two bullet points together.
Thoughts: While running all four options simultaneously, each one had a query cost of 25% (relative to the batch); however, when the first bullet point is run, the execution plan (on the SELECT query) states that I should create a nonclustered index on the unique identifier while including the remaining columns for each of the temporary tables.
I'm a little perplexed by this. If I effectively use every field and row in all of the temporary tables, why would it suggest this? Wouldn't a heap or a clustered index be better?
To me, a heap would minimize the overhead in creating an index and sorting the data; and since I essentially need every row, there is no harm in doing a table scan.
A clustered index, on the other hand, should improve the temporary table that is created via a JOIN on two of the other temporary tables as well as improve the final SELECT query which relies on JOINs and UNION ALLs.
Related Note: On a large table with 400 columns and 70M rows—don't ask me why we have an absurd amount of columns—a query that only grabbed the PRIMARY KEY was ≈50 times faster when utilizing a non-relevant nonclustered index over the clustered index.
If anyone has any insight, I'd be grateful.

To summarize (and include some information from the comments), you have a process that runs once per day early in the morning that populates several 100k row temp tables with 2 - 8 columns each. This is a broad question but my initial reaction would be to create primary keys on all of the tables. If performance is good enough then I would be satisfied. If performance isn't good enough I would investigate further to find ways to improve the code. Removing the primary keys would technically be an option but in general I would expect to find performance gains by changing the queries that populate the temp tables.

A heap is a table without a clustered index. Note that a primary key does not need to be defined the same way as a clustered index. In fact, you can define a heap with a primary key. As far as I know this isn't a very common thing to do. There are a few advantages in using heaps for your workload:

Inserting the data will not lead to a sort. If you insert 100k rows into a table with a clustered index it is possible that the data will need to be sorted depending on the query that populates the table. All of the columns or just the clustering key could be included in the sort.
As of SQL Server 2014 the insert can run in parallel if you use the SELECT INTO syntax. Note that the query optimizer may choose not to use parallel inserts depending for a variety of reasons including the estimated size of the data, and MAXDOP settings.

In general, I would not worry about a sort of 100k rows, especially if this process runs early in the morning with nothing else. Parallel inserts are unlikely to be a big help as well for such a small amount of data.

There are a few disadvantages of using heaps for this workload:

No data integrity. There might be a bug in your process or corrupt data which could have been caught earlier with a primary key on the temp tables.
The data is not sorted, so any query plan operator which requires sorted data (such as a merge join operator or inserting into a clustered table) will require an explicit sort. Let's say you reference one table in three queries and all three times the query optimizer sorts the data. Why not sort the data on disk at the start to avoid those three sorts in the query plans?

Hard to say more on these points because I don't know anything about your process. It's possible that your queries against the temp tables could benefit from a MERGE JOIN but I doubt it will make a big difference at your volume.

Now let's talk about tables with a clustered index. As stated before, a clustered index does not need to match the primary key but I'll assume the most common default which is they are both the same. That is what you end up with when you define a PRIMARY KEY inline as part of the table definition.

There are a few advantages of tables with clustered indexes for your workload:

Data integrity!
Query plan operators benefit from a sort may already have the data sorted in the correct order. For example, this makes merge joins more attractive, especially if you are getting all of the data from the involved tables.
The query optimizer may be able to pick a more efficient plan if it knows that certain columns are unique. One example of this is SQL Server may know that the joins are not many to many which can lead to more accurate cardinality estimates and costing of join operators.

There are a few disadvantages of tables with clustered indexes for your workload:

Parallel inserts into the temp tables are not available in any version of SQL Server.
Populating the tables may require an explicit sort.

The advantages and disadvantages are mostly the exact opposite of a heap table, which should not be surprising.

The third and four options aren't really worth considering. You can create an index that includes all columns but it will duplicate the data. Just define your primary key the right way and you should have no use for an additional nonclustered index that includes all columns. Note that SQL Server Management Studio will not recommend that you should create a clustered index on a table.

One difference between the clustered index/primary key and the nonclustered index is that the clustered index/primary will not allow NULL values in the key columns and automatically enforces a uniqueness constraint. Of course, it is possible for you to create a nonclustered index with the same restrictions.

Ultimately, all that you can do is test your workload with the different options. So we cannot definitely say which approach will be fastest. With one set of data and queries the heap approach may be fastest. With a different set of data and queries the clustered table approach may be fastest. Do not discount the value of data integrity, but you may have other ways of enforcing data integrity.


Subquerying results in no row returned although records exist I have two tables, namely Employees and Payments. Below is the contents of the two:

The Employees table

 EmpID |EmpName
 ------ --------
   113    Harry

The Payments table

EmpID | Amount | Period | PaymentType | St
------------------------------------------
------------------------------------------
113 |  100   | 201207 |    1        |  1
------------------------------------------
113 |  70    | 201206 |    1        |  0
------------------------------------------
113 |  120   | 201207 |    1        |  0
------------------------------------------
113 |  200   | 201207 |    1        |  1
------------------------------------------
113 |  90    | 201207 |    2        |  1
------------------------------------------

Here's my query:

    SELECT EMP.EMPID ID, PAID_TOTAL,PAID_CURRENT,PAID_PAST,PAID_OT
  FROM EMPLOYEES EMP
  , (
      SELECT EMPID,SUM(AMOUNT)PAID_TOTAL
      FROM PAYMENTS
      WHERE PERIOD<='201207' AND ST=1
      GROUP BY EMPID
    )
    PY_TOTAL, (
      SELECT EMPID,SUM(AMOUNT)PAID_CURRENT
      FROM PAYMENTS
      WHERE PERIOD='201207' AND PAYMENTTYPE=1 AND ST=1
      GROUP BY EMPID
    )
    PY_CURRENT, (
      SELECT EMPID,SUM(AMOUNT)PAID_PAST
      FROM PAYMENTS
      WHERE PERIOD<'201207' AND PAYMENTTYPE=1 AND ST=1
      GROUP BY EMPID
    )
    PY_PAST, (
      SELECT EMPID,SUM(AMOUNT)PAID_OT
      FROM PAYMENTS
      WHERE PERIOD='201207' AND PAYMENTTYPE=2 AND ST=1
      GROUP BY EMPID
    )
    PY_OT
  WHERE
    (
      EMP.EMPID=PY_TOTAL.EMPID OR EMP.EMPID=PY_CURRENT.EMPID OR
      EMP.EMPID=PY_PAST.EMPID OR EMP.EMPID=PY_OT.EMPID
    );

What I would expect to get is:

  ID | PAID_TOTAL | PAID_CURRENT | PAID_PAST | PAID_OT
  113     460          300             70         90

But no row is returned from the above query.

One of the subqueries (derived tables) returns 0 rows and thus the whole query - which is a cross join of 5 tables - returns 0 rows as well.

You could rewrite with proper, explicit joins. If you make them outer (LEFT) joins, then you'll get results if one of the subqueries has 0 rows:

  SELECT EMP.EMPID ID,
         COALESCE(PAID_TOTAL, 0) AS PAID_TOTAL,
         COALESCE(PAID_CURRENT, 0) AS PAID_CURRENT,
         COALESCE(PAID_PAST, 0) AS PAID_PAST,
         COALESCE(PAID_OT, 0) AS PAID_OT
  FROM EMPLOYEES EMP
    LEFT JOIN (
      SELECT EMPID,SUM(AMOUNT)PAID_TOTAL
      FROM PAYMENTS
      WHERE PERIOD<='201207' AND ST=1
      GROUP BY EMPID
      ) PY_TOTAL ON EMP.EMPID=PY_TOTAL.EMPID
    ---
    --- more LEFT joins
    ---
    LEFT JOIN (
      SELECT EMPID,SUM(AMOUNT)PAID_OT
      FROM PAYMENTS
      WHERE PERIOD='201207' AND PAYMENTTYPE=2 AND ST=1
      GROUP BY EMPID
    ) PY_OT ON EMP.EMPID=PY_OT.EMPID ;

But it could be written even simpler, with only one join to Payments table:

  SELECT
      EMP.EMPID AS ID,
      COALESCE(SUM(CASE WHEN PERIOD<='201207' AND ST=1 THEN AMOUNT END), 0)
          AS PAID_TOTAL,
      COALESCE(SUM(CASE WHEN PERIOD='201207' AND PAYMENTTYPE=1 AND ST=1 THEN AMOUNT END), 0)
          AS PAID_CURRENT,
      COALESCE(SUM(CASE WHEN PERIOD<'201207' AND PAYMENTTYPE=1 AND ST=1 THEN AMOUNT END), 0)
          AS PAID_PAST,
      COALESCE(SUM(CASE WHEN PERIOD='201207' AND PAYMENTTYPE=2 AND ST=1 THEN AMOUNT END), 0)
          AS PAID_OT
  FROM EMPLOYEES  EMP
    LEFT JOIN PAYMENTS  P
      ON EMP.EMPID = P.EMPID
  GROUP BY EMP.EMPID ;

This may not be as efficient or need different indexes but it can easily be changed to include in the results only employees with at least one payment:

  FROM EMPLOYEES  EMP
    INNER JOIN PAYMENTS  P
      ON EMP.EMPID = P.EMPID

or employees with at least one payment in the examined period/restrictions:

  FROM EMPLOYEES  EMP
    INNER JOIN PAYMENTS  P
      ON  EMP.EMPID = P.EMPID
      AND (P.PERIOD<='201207' AND P.ST=1)

The (P.PERIOD<='201207' AND P.ST=1) condition is the OR of all the 4 conditions, simplified.


Max Connections Maria DB Every time I reboot Mariadb places me the max_connections in 151 and in the my.cnf I have it with 300. Why does this happen?

This depend if you set max_connections under the correct section inside my.cnf. what do you need to check(with examples)

go to MySQL Directory cd /opt/mysql
edit my.cnf using any text editor sudo vi my.cnf
search for [mysqld]
add the following lines directly under it max_connections=300
save and quit
restart the services.

Don't want a downtime?

connect to MySQL using root user mysql -u root -p
SET GLOBAL max_connections=300;
quit


SSAS Realtime Processing I created a cube in SSAS, how I can extract real time data from the data source to my cube?

I mean if any data modification (insert, update, delete) happens, SSAS should update the cube.

You need to look into proactive caching for your partitions and dimensions where you can set up notifications based on a tracking table.

From the documentation:

  Proactive caching provides automatic MOLAP cache creation and
  management for OLAP objects. The cubes immediately incorporate changes
  that are made to the data in the database, based upon notifications
  received from the database. The goal of proactive caching is to
  provide the performance of traditional MOLAP, while retaining the
  immediacy and ease of management offered by ROLAP.

The subject is a bit broad for an answer and there are quite a few options and drawbacks to consider.

In general you check the box "enable proactive caching" and pick some latency options:

and then you specify a tracking table on the "notification" tab:

What all the options such as enable ROLAP aggregations and the latency settings do is explained in more detail in this blog post or this one


Full backup at 1am, not started by my maintenace plan I set up a complete set of Maintenance Plan.

Full backup of all databases at 3AM.
Differential backup at 6h30, 12h30, 18h30 and 00h30.
Log backup at each hour, at 45 minutes (1h45, 2h45...)

In my SQL history list, (I use this script to find it: http://luvit.me/1GAfpPG ) I see a full backup a 1AM, with a GUID as the "physical_device_name" and a NULL value at as the "backupset.name".

WHO start the backup? How can I found if another guy use another tool for backup? Did this backup is started by SQL Server himself?

The problems is currently, we have some tools that do a database conversion for a new and not yet ready ERP, and the conversion tool change the Recovery mode: if the recovery mode is changed while this weird backup is running, the conversion tool crash...

Sorry if it's a noob question, but I didn't find anything ...

Thank you

Add columns:
    msdb.dbo.backupset.software_vendor_id,
    msdb.dbo.backupset.user_name

to your query.

If your software vendor id = 4608 then its sql native, anything else is a third party software. The user name should give you some clues as to what is occurring.

Also check SQL Agent for jobs executing at around that time.


Splitting a table with large columns for performance? I am working on a system that stores images in its database (SQL Server 2014) using the varbinary datatype. The table looks like this (extra columns removed for brevity):

| ImageDataId | DateUploaded |
|   1         | 1-1-2001     |
|   2         | 1-1-2001     |
|   3         | 1-1-2001     |

There is also a seperate table for the image itself.

| ImageId | ImageFile    |
|   1     | [byte array] |
|   2     | [byte array] |
|   3     | [byte array] |

The database was made with Entity Framework Code First, so the ImageId key in the second table is a foreign key reference to the Id in the first table.

I was told these tables were designed to improve performance. If I only need ImageData, but no image, querying the first table without any imagedata in it would be fast. I'm not an experienced DBA, so I may be missing something, but wouldn't a query be just as fast if I merge both tables:

| ImageDataId | DateUploaded | ImageFile   |
|   1         | 1-1-2001     | [byteArray] |
|   2         | 1-1-2001     | [byteArray] |
|   3         | 1-1-2001     | [byteArray] |

And then do a SELECT without the ImageFile column? Or is this query:

SELECT DateUploaded
FROM Images

Somehow faster with split tables?

Generally this can make sense, BUT: you have first to make sure the data is in the table.

If you use large table fields (varbinary(max)) then the data is actually not stored in the table but in separate pages anyway. If the original table as as thin as you indicate (id, date upload, not a lot more fields) Then this is a fallacy, with one exception: EF will gladly load all the data on object access - which makes acess slow thanks to EF inefficiency.

You can avoid that by not using EF for accessing the file field. That does make a TON of sense because you may never want to load the whole file into memory anyway. You want to be able to access the file in a loop reading max x bytes - so you can stream them off.

Welcome to the limitations of EF and the even worse limitations of code first.


Indexes Size 20 times bigger then the table itself and very slow queries The table I want to query has only about ~3000 rows with 3 columns:

int/varchar(100)/varchar(100)

yet it takes ~30 sec to query them all!

Then I saw that the table size is 1331MB but the indexes size is 25GB!

The table has only one index and that is the pkey.

VACCUM FULL is running every 24hours and the table sould be pretty static anyway

My question is why are the indexes so big (I guess thats why the querys are so slow) and what can I do to fix that?

Hope you have any ideas, because I have none and I haven't found anything on google.

EDIT: Postgres Version is 8.1.18

Pre-9.0 VACUUM FULL

You're on PostgreSQL 8.4 or older, where VACUUM FULL tends to bloat indexes. See this wiki page for details.

Don't run VACUUM FULL as a periodic maintenance task. It's unnecessary and inefficient. This remains true on current versions, it's just not as bad on 9.0 and above. If you feel the need to run VACUUM FULL regularly then you probably don't have autovacuum turned up far enough and are having table bloat issues. In fact, unless you've changed the FILLFACTOR on the table from its default 100 a VACUUM FULL is quite counter-productive; it'll compact away all the free space in the table, so following UPDATEs will have to extend the table.

Table extensions are currently one of the poorer performing operations in PostgreSQL, as they're controlled by a single global lock. So if you have tables that fluctuate in size, you really want to avoid constantly compacting and truncating them only to extend them again.

On some unusual workloads it can be worth running a periodic CLUSTER, which orders the table based on an index and effectively REINDEXes it. If you do many UPDATEs on the table should set a lower FILLFACTOR for efficiency.

If this table is being emptied and re-populated regularly, you should generally using TRUNCATE  followed by COPY to fill it back up. If it's big, drop the indexes before the COPY then re-create them afterwards to produce indexes that are more compact and faster and to speed up the data load.

For one-off mitigation, CLUSTER the table or REINDEX it.

8.1?!?!

After edit added version: Holy bleepazoids, batman. 8.1.18? Forget what I said about autovacuum, autovacuum in 8.1 was way too ineffective. Upgrade to a sane version ASAP. You're not even on the current point release of 8.1, 8.1.23, from December 2010. 8.1.18 was released in September 2009! You need to begin your upgrade planning ... well, about two years ago, preferably. Read the release notes for every .0 version between 8.1 and the current release, focusing on the upgrade notes and compatibility notes. Then plan and execute your upgrade. If you don't feel up managing that on your own there are people who'll help you with it (I work for one of them) but honestly, the release notes and docs are quite sufficient for most people to do an upgrade themselves without undue pain.

Moving from 8.1 to 8.3 or newer will be your biggest pain point, as PostgreSQL 8.3 dropped a whole bunch of implicit casts that lots of potentially buggy SQL relied on. You'll need to test your application carefully on the newer version. Other changes to be aware of are:

The removal of implicit FROM and in later versions removal of the backwards compatibility parameter for it;
UTF-8 validation improvements in newer versions that can cause older dumps to fail to load until the data is corrected;
The change to standard_conforming_strings by default;
The change of bytea_output to hex


Delete data from huge InnoDB table from MySQL Master and Slaves We have some huge tables in MySQL database, We have already archived/deleted data older than 2012 in 2013, Now we need to archive/delete data older than 2013 means we need to archive data for year 2012 without application downtime.

Tables  Size in GB      "TABLE_ROWS"    "TABLE_ROWS BEFORE 2012"
RTesAll 923.65          1982098430       611992998
RTest   32.1            205527090
RAdT    6.97            25324446
RAdv    4.37            28260973

So I need to delete 611992998 records from biggets table.

We have One MySQL Master and 4 MySQL Slaves, We need to delete data from all the servers, What I am thinking is i will delete data in chunks from master so that slaves also didn't lag too much.For that i have created a procedure here is the procedure, I have not yet tested it

DROP PROCEDURE IF EXISTS PurgeOlderData;
DELIMITER $$
CREATE PROCEDURE `PurgeOlderData`(In StartDate DATETIME ,In EndDate DATETIME,In NoOfRecordsToDelete BIGINT,In TableName CHAR(50))
BEGIN
    SET @delete_counter             = 0;
    SET @table_name             = TableName;
    SET @number_of_records_to_delete    = NoOfRecordsToDelete;
    SET @start_date             = StartDate;
    SET @end_date               = EndDate;

    WHILE @delete_counter < @number_of_records_to_delete DO
        SET @varSQL = CONCAT('DELETE FROM ', @table_name,' WHERE recordDate BETWEEN \'',@start_date ,'\' AND \'', @end_date ,'\' LIMIT 5000;');
        PREPARE stmt FROM @varSQL;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        /*SELECT @varSQL;*/
        SET @delete_counter = @delete_counter + 5000;
    END WHILE;
END $$
DELIMITER ;

I have used variable @number_of_records_to_delete in procedure because,I can pass no of records which i target to delete.

Sample call statement

CALL PurgeOlderData('2012-01-01 00:00:00','2012-01-05 00:00:00',100000,'RTestAll');

I can increase the value of @number_of_records_to_delete accordingly.

Why DELETE with LIMIT 5000 : I will first test how much time DELETE with LIMIT 5000 takes if it is Ok (Means executes in 1-3 seconds and Slaves are also Ok), I can increase DELETE LIMIT to 10000, If it is also Ok , I can increase some more.

Table Structure of huge table

CREATE TABLE `RTesAll` (
  `recordDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `networkId` bigint(11) NOT NULL DEFAULT '0',
  `publisherId` bigint(11) NOT NULL DEFAULT '0',
  `feedId` bigint(11) NOT NULL DEFAULT '0',
  `subPublisherId` bigint(11) NOT NULL DEFAULT '0',
  `subId` varchar(100) NOT NULL DEFAULT '',
  `searches` bigint(20) DEFAULT NULL,
  `matches` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`recordDate`,`networkId`,`publisherId`,`feedId`,`subPublisherId`,`subId`),
  KEY `K_networkId` (`networkId`),
  KEY `K_publisherId` (`publisherId`),
  KEY `K_feedId` (`feedId`),
  KEY `K_subPublisherId` (`subPublisherId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

So my questions are

Does the approach i am going to opt will work and is it optimized way to go. Because we don't want any issues in servers (Like slave lag) due to deletions.
What would be the best approach to do it without issues and downtime.
How can i use pt-archiver for doing this.

Here are some things you should consider:
CONSIDERATION #1
Using DELETE FROM tbl ... LIMIT 5000 is not replication-safe because the  5000 rows being deleted may not be the same order or the same set of 5000 rows on a slave. See MySQL Documentation (Replication and LIMIT). Should a DELETE on a Master get interrupted, it has to rollback. In that event, it could be remotely possible that the number of times the DELETE FROM tbl ... LIMIT 5000 is called may not be the same. Extra calls of the DELETE on a Slave is not critical. Too few calls could be bad. This should not happen.
SUGGESTION : Make sure the row count for RTesAll is identical on the Master and all Slaves.
CONSIDERATION #2
Since you are running a Stored Procedure on the Master, the DELETE command will not begin its execution on a Slave until the completion of the DELETE on the Master and the DELETE command is posted in the Master's Binary Logs. With one Master and 4 Slaves (5 DB Servers), there are a certain of calls to DELETE multiplied by 5.
SUGGESTION #1 : You should add set sql_log_bin = 0; to the Stored Procedure
DROP PROCEDURE IF EXISTS PurgeOlderData;
DELIMITER $$
CREATE PROCEDURE `PurgeOlderData`(In StartDate DATETIME ,In EndDate DATETIME,In NoOfRecordsToDelete BIGINT,In TableName CHAR(50))
BEGIN
    SET sql_log_bin             = 0;
    SET @delete_counter             = 0;
    SET @table_name             = TableName;
    SET @number_of_records_to_delete    = NoOfRecordsToDelete;
    SET @start_date             = StartDate;
    SET @end_date               = EndDate;

    WHILE @delete_counter < @number_of_records_to_delete DO
        SET @varSQL = CONCAT('DELETE FROM ', @table_name,' WHERE recordDate BETWEEN \'',@start_date ,'\' AND \'', @end_date ,'\' LIMIT 5000;');
        PREPARE stmt FROM @varSQL;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        /*SELECT @varSQL;*/
        SET @delete_counter = @delete_counter + 5000;
    END WHILE;
    SET sql_log_bin             = 1;
END $$
DELIMITER ;

to prevent all DELETEs from replicating. If you are deleting 611992998 rows, deleting every 5000 rows requires executing DELETE ... LIMIT 5000 122399 times (That's 611992998 / 5000 rounded up to next integer).
This will prevent having to write to the binlogs 122399 times.
SUGGESTION #2 : Run the Stored Procedure on the Master and Slaves at the same time.

This prevents replication lag
This gets rid of all the rows at the same time
This is best done during maintenance cycles or slow read times

CONSIDERATION #3
Make sure autocommit is enabled on Master and Slaves. Otherwise, all the deletes will pile up in the undo logs inside ibdata1 before either doing all the deletes at once as a single transaction or a huge rollback.
Give it a Try !!!


mysql error creating table I was creating a DB with mySQL, and this error appeared:

  Can't create table 'projectefinal.apuntats' (errno: 150)

I've got this:

    CREATE DATABASE IF NOT EXISTS projectefinal

CREATE TABLE IF NOT EXISTS usuaris(
    id INT NOT NULL AUTO_INCREMENT,
    nom TEXT NOT NULL,
    cognoms TEXT NOT NULL,
    dni VARCHAR(9) NOT NULL,
    aturat BOOLEAN NOT NULL,
    PRIMARY KEY(id),
    UNIQUE(dni)
);

CREATE TABLE IF NOT EXISTS cursos(
    id INT NOT NULL AUTO_INCREMENT,
    nom TEXT NOT NULL,
    maximspersones INT NOT NULL,
    PRIMARY KEY(id)
);

CREATE TABLE IF NOT EXISTS apuntats(
    id INT NOT NULL AUTO_INCREMENT,
    idcurs INT NOT NULL,
    idusuari INT NOT NULL,
    coneixements TEXT,
    motiu TEXT,
    FOREIGN KEY(idcurs)
        REFERENCES id(curs)
        ON DELETE CASCADE,
    FOREIGN KEY(idusuari)
        REFERENCES id(usuaris)
        ON DELETE CASCADE,
    PRIMARY KEY(id)
);

I've read before that may happends because a table is not created, but it is, I went one by one ("usuaris" first, "curs" for the second, and finally "apuntats") and it gave me that error. Any help on how to fix it?

Thanks again, as always!

your foreign key definitions are wrong, you switched table and column names.

this should work:

CREATE TABLE IF NOT EXISTS apuntats(
    id INT NOT NULL AUTO_INCREMENT,
    idcurs INT NOT NULL,
    idusuari INT NOT NULL,
    coneixements TEXT,
    motiu TEXT,
    FOREIGN KEY(idcurs)
        REFERENCES cursos(id)
        ON DELETE CASCADE,
    FOREIGN KEY(idusuari)
        REFERENCES usuaris(id)
        ON DELETE CASCADE,
    PRIMARY KEY(id)
);


Granting Privilege on SCHEMA I have two SCHEMA in a database(ABC,XYZ)

Now i want to create a user Test and grant select privilege only on SCHEMA (XYZ)

I know how to grant Privilege to a user, but confused how to grant privilege on specific SCHEMA?

That is not possible, the database does not support such grants.
You can grant the required privileges on each object individually in the schema.

For example:

begin
  for o in (select * from dba_tables where owner = 'XYZ')
  loop
    execute immediate 'grant read on "' || o.owner || '"."' || o.table_name || '" to ABC';
  end loop;
end;
/

It is better to create a role, grant that role to the other user, and maintain the grants to the role when you create new objects.

grant_system_privileges::=

grant_object_privileges::=

on_object_clause ::=


Read Committed Snapshot, stale data reads? I'm running SQL Azure and I have a following case.

I have two transactions W (writer) and R (reader). Both are running with RCSI (.net application with TransactionScopes using LTM). The R transaction starts at the same time as W and continuously executes SELECT statements to read the table that is written by the transaction W.

Normally, the transaction R sees all the the changes immediately after W commits its changes. However, occasionally the transaction R sees the state without the most recent commit from the transaction W.

The question is:

Does the RCSI guarantee that R always see the most recent state of the table?
According to:
https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-2017
Read committed should always get the latest revision of the row that was available at the time of executing the SELECT query. But is it guaranteed, when having two independent sessions?

Read committed should always get the latest revision of the row that was available at the time of executing the SELECT query.

Yes, you are correct. Since it's RCSI, it's at a statement level and not a transaction level which would be regular snapshot isolation.

  But is it guaranteed, when having two independent sessions?

Yes, it should be.

  Does the RCSI guarantee that R always see the most recent state of the table?

Yes, from when the statements starts it'll be the most recent and based off the version store if need be. This means if the read query started and the write was in the middle of the write, the older value would be seen by read statement.

What you're describing is normal and in line with small, quick, concurrent updates on one thread and reads on another. Most of the time RCSI alleviates the locking/blocking at the cost of potentially slightly stale reads. You could get to the bottom of the issue by checking on an individual statement level when each statement started, committed, etc., but if they are milliseconds or microseconds apart there really isn't anything you could do to change it outside of writing in thread synchronization for the application accessing the data - thus forcing the reads to not be stale.

Note that this assumes the writes are small, fast, and complete successfully. For example, a write where the application is hung and never commits would show the previous value when read via RCSI instead of staying blocked such as in read committed.


Constructing an SQL query with time intervals I've a simple but large table in an SQL database which has four fields:

id    (int PK)
time  (unix timestamp, not null)
value (double, not null)
ID_fk (integer foreign key)

Data is recorded every minute, but at a seemingly random second for subsets of sensors, each sensor has a foreign key value though.

My question is this:

I need to write a query that will tell me when, over all two minute intervals in the dataset, a series of sensors have a value greater than 0.9.

I was thinking for trying to create a temporary table and do it that way, simplifying the time by only storing it at minute resolution?

Any advice would be greatly received.

You could try using a CTE recursive function that generates a time series.

There is an answer on SO that shows how to do it:

WITH RECURSIVE dates(StartDate, EndDate) AS
(
    VALUES('2016-10-01 00:00:00', '2016-10-01 00:00:02')
    UNION ALL
    SELECT EndDate as StartDate, date(EndDate, '+2 seconds') as EndDate
    FROM   dates
    WHERE  EndDate < '2016-10-02 00:00:00'
)
SELECT     you_table.*
FROM       your_table
CROSS JOIN dates
WHERE      value > 0.9
AND        time >= StartDate
AND        time <  EndDate;


PHP SQLite Admin is running on PHP 5.4 I tried phpsqliteadmin

But it doesn't seem to work anymore on PHP 5.4.

There are several alternatives, but none of them seems to be "The One" that is maintained and widely used.

Is there a standard SQLite admin webinterface, (preferably installable via apt)

Try this one instead, it appears to be more active than the one you're using.
Another very active project for such tools with SQLite is here. Your tool hasn't been updated for over a year - that's usually a sign that the project is oprhanware.


Problem with data insertion in Oracle table from SQL Server table We have SQL server and Oracle server, and have configured a linked server from SQL Server to Oracle.

I'm trying to insert 10,000 rows from SQL Server into a table in Oracle.

When I insert 100,000 rows it takes 2 minutes, when I try to insert 1 million rows it can run for more than 10 hours and at the end of this I need to stop the insert because nothing is happening.

Could you please give me answer why it takes so long for 1 million rows? It should take approximately 1 hour if it runs the same speed as 10k rows.

What I'm trying to do:

INSERT INTO [linksrvname]..[schemaname].[oracletablename ] (
  column1,
  column2
)
SELECT top 10000
  column1,
  column2
FROM sqlservertable
;

...completed in few seconds. After that I have deleted all rows from the Oracle table. When I'm trying to insert 100,000 it is completed in few minutes. When I'm trying to insert 500,000 it is running for a few hours now; SQL Server hangs with OLEDB wait type. Unfortunately, I don't have access to the Oracle server.

What could the problem be?

SQL Server inserts across linked servers use row-by-row insert statements.  If you run this:

INSERT INTO [oracledb].database.schema.table
SELECT *
FROM dbo.LocalTable

SQL Server will use a cursor to fetch each individual row from dbo.LocalTable and run an INSERT INTO ... statement against the linked server for each row.

Essentially, the insert gets turned into this:

DECLARE cur CURSOR
FOR
SELECT *
FROM dbo.LocalTable;
OPEN cur;
FETCH NEXT FROM cur INTO @vars...
WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO [oracledb].database.schema.table (cols...)
    VALUES (@vals);
    FETCH NEXT FROM cur INTO @vars...
END
CLOSE cur;
DEALLOCATE cur;

You'll find that as the number of rows increases, the time required to run the insert will increase linearly.  This increases the chance that blocking on the target side may interfere with the inserts.  The OLEDB wait type indicates SQL Server is waiting for code in the OleDB provider to complete.  It occurs when SQL Server calls the SQL Server Native Client OLE DB Provider. This wait type is not used for synchronization. Instead, it indicates the duration of calls to the OLE DB provider.

The "best practice" for loading data from SQL Server to a link-server target is to run that either from the target side, or via a bulk-load app designed for that purpose.

You mentioned in comments that you do not have access to the Oracle server.  Without being able to see what's happening at the Oracle server side, it will be difficult to diagnose where the problem lies.


Transaction Logs: How much log data generated (hourly or daily)? I am trying to find out the amount of transaction-log data generated by SQL Server 2016 during every hour or every day. By "data generated" I mean how much data (in bytes/KBs/etc) was written to disk every hour (or every day).
Is there a way to find this out?
Our database is in FULL recovery mode and we do have regular transaction log backups. So, I am on the opinion that querying the backup metadata in msdb may help to achieve that. Does it work ? Will it give me correct and reliable results?
Second option will probably be to look at the amount of read/write IO that is happening to the transaction log files. Can this one work? If yes, how can I do that? Are there any SQL Server DMVs providing such information? What about Windows tools? (such as Windows performance monitors)?
If possible at all, I would prefer the second option above because it won't require having transaction log backups. Therefore, it can be used even with SIMPLE recovery model databases. So, my question is, is it possible?
Are there any other alternatives? Such as SQL Server tools or views that are readily available?
Please note, I need this data because we are trying to estimate the amount of network IO that will be required if we create a (near) real-time replication of our databases in the cloud. So, I thought we should somehow measure the amount of IO attributed to the transaction log files. Is my assumption correct that the required IO will be equal to the amount of Io to the transaction log files? Is this how SQL Server transaction replication work ? (i.e. by sending VLFs to the replicated site)?

Lots of questions in there.
First, are we talking about transactional replication? I know you said that, but we need to be certain that you aren't talking about Always On Availability Groups, log shipping or some other solution to get a "copy" of your database.
Answered: yes, transaction replication - I will refer to it as just "replication".
No, replication doesn't copy VLFs. I think it extremely difficult to get anything usable from the transaction log.
First of all, consider the replication architecture:
An Agent job, the Log Reader, reads the transaction log, generates DML statements from this and stored them in the distribution database. So first question is whether Log Reader is local or not.
Then the next Agent job, the Distributor reads these DML commands in the distribution database and applies them to each subscriber. And you probably realize, there is not a 1:1 mapping of the size of the log records (binary data) and the DML commands as results from the log records (text). And, of course, the question is where the distributor is running.
And the final question is where the subsriber is. So, you should draw a picture with the involved databases, where you have network between the databases and where the Agent jobs are running.
And there's more: Some operations aren't carried over with transactional replication. One example is index rebuild. It can generate massive amount of log records (if full recovery model), but such an operation isn't executed on the suscriber - so it is ignored by the log reader.
And since you say subset, if you mean subset of the database: How would you, from the log, determine which log records apply to tables/rows that are in this subset?
I'm mainly raising issues here, just to point out how easy you can fall into a too simplified "solution" for this which can be misleading. This MS article talks about a few options that can be usable, when you have implemented replication.
Disclaimer: I'm no replication expert, so feel free to correct me, y'all.


use SUM on foreign keys where it matches rows id? I have this table:

CREATE TABLE buildings (
    id SERIAL PRIMARY KEY UNIQUE,
    name character varying(20) NOT NULL UNIQUE,
    document jsonb
);

then this view:

CREATE VIEW v_rooms AS
SELECT buildings.id AS building_id,
    buildings.name AS building_name,
((jsonb_array_elements(jsonb_array_elements(buildings.document -> 'levels'::text) -> 'rooms'::text) -> 'meta'::text) ->> 'sqft'::text)::numeric AS sqft
   FROM buildings
  GROUP BY buildings.id;

finally this view:

CREATE VIEW v_buildings AS
SELECT buildings.id AS building_id,
    (buildings.document -> 'meta'::text) ->> 'name'::text AS name,
    sum(v_rooms.sqft) AS sqft
   FROM buildings,
    v_rooms
  GROUP BY buildings.id
  ORDER BY buildings.id;

All is well, except 'sqft' on v_buildings is returning the SUM of all the rows in v_rooms. I want to calculate SUM() where v_building.building_id  == v_rooms.building_id.

Join to the aggregate, don't aggregate the join:

SELECT b.id AS building_id,
      (b.document -> 'meta'::text) ->> 'name'::text AS name,
      r.sqft
FROM buildings b
  JOIN (
   SELECT building_id,
          sum(sqft) as sqft
   FROM v_rooms
   GROUP BY building_id
  ) r ON r.building_id = b.id;

Unrelated, but: although Postgres allows it, it's usually a bad idea to include an ORDER BY in a view definition.


Choosing the right blocksize for Oracle Database tablespaces with lobs We are currently switching from an older character set to Unicode using impdp/expdp on Oracle Database 19c. We had to changed several data types. We changed tables with LOBs from basicfile to securefile and found that the tablespace holding this lobs only uses an 8K blocksize. The table containes several TBs with pdfs around 2-3mb. Does it makes sense to increase the Blocksize of the tablespace holding the LOBs from 8k to 32k?

LOBs are managed in chunks (groups of blocks), not blocks, so there's not really anything to gain with this. Oracle assumes that an 8k block size is "universally good" for everything. By increasing the block size, you could potentially wind up wasting storage space that can't be efficiently allocated for smaller files because the chunk size is too large.
See here for a running discussion: https://asktom.oracle.com/pls/apex/asktom.search?tag=oracle-block-size
Final analysis:

That said, worrying about block size is like fiddling while Rome burns -
many (most?) apps have low hanging fruit which will give a better ROI on your time than tuning the block size.
8k is still best block size to be using.


how to convert this varchar to datetime format? I have a varchar column has data like (2015-12-02 20:40:37.8130000) which i have imported from csv file to SQL table. i need to convert this column to datatime as (2013-03-17 14:15:49.687).

 ALTER TABLE track_date alter column start_time datetime.

getting error as :

  Msg 241, Level 16, State 1, Line 1
  Conversion failed when converting date and/or time from character string.

while converting using convert function , getting same error:

SELECT convert(datetime,start_time,5) from track_date

  Msg 241, Level 16, State 1, Line 1
  Conversion failed when converting date and/or time from character string.

Can you please help, how to convert this varchar data to datetime format?

There is too much precision in the varchar to be converted into datetime.

One option(better in my opinion) would be to change the target column to datetime2(7). Then you can convert like this:

declare @dt varchar(50)
set @dt = '2015-12-02 20:40:37.8130000'

select cast(@dt as datetime2(7));

If changing the column is not an option the you can do the conversion like this:

declare @dt varchar(50)
set @dt = '2015-12-02 20:40:37.8130000'

select cast(cast(@dt as datetime2(7))as datetime)


How can I get a local variable that's a list in a stored procedure? I have some code in C# I'm trying to move to a stored procedure - Sql Azure does not like multiple calls. So everything needs to get moved to a proc.

Here's the C# code:

    List<string> addList = new List<string>(EmailDomains);
    List<string> deleteList = new List<string>();
    using (IDbCommand cmd = conn.CreateCommand())
    {
        cmd.Transaction = transaction;
        cmd.CommandText = "select * from CompanyDomains where CompanyDomains.CompanyId = @CompanyId";
        DbUtilities.AddParam(cmd, "@CompanyId", CompanyId);
        using (IDataReader reader = cmd.ExecuteReader())
        {
            int ordinal = reader.GetOrdinal("EmailDomain");
            while (reader.Read())
            {
                string domain = reader.GetString(ordinal).Trim();
                if (! addList.Remove(domain))
                {
                    deleteList.Add(domain);
                }
            }
        }
    }
    foreach (string domain in deleteList)
    {
        using (IDbCommand cmd = conn.CreateCommand())
        {
            cmd.Transaction = transaction;
            cmd.CommandText =
                "delete from CompanyDomains where CompanyDomains.CompanyId = @CompanyId and CompanyDomains.EmailDomain = @EmailDomain";
            DbUtilities.AddParam(cmd, "@CompanyId", CompanyId);
            DbUtilities.AddParam(cmd, "@EmailDomain", domain);
            cmd.ExecuteNonQuery();
        }
    }

    // deleted domains get added/updated to DeletedCompany
    foreach (string domain in deleteList)
    {
        DeletedCompany.InsertOrUpdate(conn, transaction, domain, CompanyName);
    }

    foreach (string domain in addList)
    {
        using (IDbCommand cmd = conn.CreateCommand())
        {
            cmd.Transaction = transaction;
            cmd.CommandText = "insert into CompanyDomains (CompanyId, EmailDomain) " +
                              "values (@CompanyId, @EmailDomain)";
            DbUtilities.AddParam(cmd, "@CompanyId", CompanyId);
            DbUtilities.AddParam(cmd, "@EmailDomain", domain.Trim());

            cmd.ExecuteNonQuery();
        }
    }

    // added domains get removed from DeletedCompany
    foreach (string domain in addList)
    {
                    using (IDbCommand cmd = conn.CreateCommand())
    {
        if (transaction != null)
            cmd.Transaction = transaction;
        cmd.CommandText = "delete from DeletedCompany where DeletedCompany.Domain = @Domain";
        DbUtilities.AddParam(cmd, "@Domain", domain);
        cmd.ExecuteNonQuery();
    }

    }

What I'm totally stopped on is I don't see any equivalent of a List is SQL. Is there a way to build all this up in a list?

Or is there a delete/insert/update I can do where the select uses the list I pass in? I am passing in a list in the form of a defined type(table) that is the new list of domains. From that list:

Any domain in the passed in list that is not in CompanyDomains is added to CompanyDomains.

Any domain in the CompanyDomains table that is not in the passed in list is 1) removed from CompanyDomains and 2) added/updated to the DeletedCompany table.

Any domain that is in the liast and in the DeletedCompany table is deleted from the DeletedCompany table.

The DeletedCompany.Domain column is unique in that table. So additions to it must be an upsert.

The CompanyDomains table also has a CompanyId column and everything there is where CompanyDomains.CompanyId = @CompanyId (passed in parameter to the proc).

This is for Sql Server 2008 & Sql Azure. It does not need to run on anything else.

thanks - dave

What I'm totally stopped on is I don't see any equivalent of a List is SQL. Is there a way to build all this up in a list?

So a list in SQL Server is basically a table. You can use table-valued parameters to pass in your complete list from C#. You basically just create a table type to define that list you are passing in, see the example in the MSDN link.

You would just need to take each requirement you list in your question or that the C# is performing, and write the query to pull the correct values from your tabled-values list and create your INSERT, DELETE, and UPDATE statements based on those values.

So for

  Any domain in the passed in list that is not in CompanyDomains is added to CompanyDomains.

Something like:

INSERT INTO CompanyDomains (<column list>
SELECT <column list>
FROM @TableParameter t
WHERE NOT EXISTS ( SELECT domain FROM CompanyDomains cd WHERE t.domain = cd.domain );


Running queries one after the other in psql/scripting environment? I am a little bit concerned about the possibility of queries running concurrently. I want to run a set of queries at worst as soon as the previous is ready through psql (PostgreSQL) and a shell script.

My initial attempt was something like this example:

queries=("SELECT ...", "SELECT ...")
for query in ${#queries[@]}
    "$query" | psql -U postgres -d database
done

I am pretty sure that with the way this is written that the bash script is waiting for each other command to be executed before moving to the next. But I am not sure on the interaction with psql - can there still exist the possibility that the database is executing queries concurrently? If so, how do I approach this so that my commands and queries are executed after each other?

As @a_horse_with_no_name said, if you want to restrict the order on db level, use sequential statements, either with psql -f queries.sql or :

psql <<EOF
select..;
select..;
EOF

In your example you call psql for each query. You don't send them to background so each psql waits previous to end before calling next. Concurrency should be sane here as well. Things that you have different here: lack of control over transaction, you open/close many connections instead of one and you put sql code into bash variable