Select query takes more time than it should I have a MySQL database table with nearly 23 million records. This table has no primary key, because nothing is unique. It has 2 columns, both are indexed. Below is its structure:

Below is some of its data:

Now, I ran a simple query:

SELECT `indexVal` FROM `key_word` WHERE `hashed_word`='001'

Unfortunately, this took more than 5 seconds to retrieve the data and show them to me. My future table will have 150 billion records, so this time is very very high.

I ran the Explain command to see what's going on. Result is below.

Then I ran the Profile using below command.

SET profiling=1;
SELECT `indexVal` FROM `key_word` WHERE `hashed_word` = '001';
SHOW profile;

Below is the result of the profiling:

Below is some more information about my table:

So, why this is taking so long? They are indexed too! In future, I have to run lot of LIKE commands, so this is taking too much time. What has gone wrong?

You asked "why this is taking too long?". You also said "Unfortunately, this took more than 5 seconds to retrieve the data and show them to me". Also, you reported the profiling output of your query.

As you can see yourself, the sum of times reported by the profiler for each steps count to 0.000154 seconds. So, from the point of view of the profiler, the query was completed in such a time (0.000154).

So why you're getting results in "...more than 5 seconds?".

You said you are filtering a 23 millions record table with a 3 char field. Unfortunately you don't tell us how many records your query is returning... but thanks to the provided EXPLAIN SELECT, it seems your query returned 336052 records.

It seems, also, that all of your activity run through some GUI (PHPMyAdmin?).

So, after all of the above, we can reformulate your original question as:

"why am I getting, within my GUI, 336.052 records shown in more than 5 seconds, if MySQL execution time for the related query is 0.000154 seconds?"

The answer, in my opinion, is quite simple: 5 seconds is the (really low, indeed) time to let 336.052 records travels along the path: MySQL engine => MySQL client libraries => PHP MySQL module => Apache => Network => your PC TCP/IP stack => Browser => DOM parser/builder/etc. => Rendered HTML page.

As for my previous experience, time required by the transmission of results is "normally" much higher than the time needed to retrieve such data. This is expecially true when libraries like PHP-MySQL or Perl-DBD-MySQL are involved: they really require lot of time to retrieve the records, after MySQL has properly identified (...and extracted) all of them.

How to solve this problem?

Again, quite easily: are you really sure that you need ALL of the 336.052 record, in a single, whole, dataset?

If your answer is really "YES! I need the all of them", than your application will handle PAGINATION and/or USER-Interaction by itself and... once it has gathered all of such data, it will probably spend lots of time interacting with the user without requiring any further MySQL interaction. In a such a case, waiting for 5 seconds (or even more) should not be a problem;
If your answer is "NO, I want to deal with a more 'human' dataset size", than you have to refine your query (at least) so that it will give back to you a more "human" dataset (tens or, hundreds, at most, records). In such a case, I bet that you'll get your result in a shorter time.

BTW: this is exactly the same issue you experienced in this other post, at ServerFault: 88 seconds to let 132M records travels along the.... not-mysql-strictly-related magic path :-)


Why is PostgreSQL's auto-explain not working in AWS RDS? These are my settings:

According to the documentation, as far as I can tell, this combination of settings along with a statement_timeout value of 3600000 should mean that hour-long queries time out, and well before then, they and anything else longer than ~214ms should be producing EXPLAIN output in the logs.  But they're not.

shared_preload_libraries already contained auto_explain by default when I checked.
The parameters above are applied to the server.
I've restarted the instance after applying the parameters.
My back end has reported an incidence of canceling statement due to statement timeout (which only started happening after I set statement_timeout) but there is nothing in the logs (at all, let alone in the vicinity of the time reported) showing anything other than xlog starting and stopping.

What else could be missing?

auto_explain only logs a statement upon successful completion. If it gets cancelled, it does not get logged by auto_explain.


Log Custom SQL Results During Maintenance Plan I would like the last step of my Maintenance Plan to execute a simple custom SQL script (SELECT * FROM <SomeTable> WHERE <Condition>), and save the results to a file (Text, CSV, etc).

Is this possible? The closest thing I've found is doing this in a .bat file that is executed by Windows, but that seems a little cumbersome.

You can use a mixture of xp_cmdshell and bcp:

exec xp_cmdshell
    'bcp "SELECT * FROM YourTable ...." queryout Log.txt -c -T'

The above extended stored procedure bcp call assumes a few things, such as a trusted connection.  But utilize the BOL Reference for BCP to fine tune your call.


Zabbix MariaDB Copy item data to another item My current DB:
MariaDB [zabbix]> SELECT VERSION();
+----------------------+
| VERSION()            |
+----------------------+
| 10.0.28-MariaDB-2+b1 |
+----------------------+

There are some duplicated items in my Zabbix.
I have the old item which has "history" and "trend" data, but it was disabled.
The new item also has data and is enabled.
I thought of copying old item history and trends to the new item but I'm not really sure how to do it safely.
I created some test items to check the update process, but It doesn't seem to go as I expected.

Old item - 30886 New Item - 32041

So, I wish to copy all data from the old item to the new item.
Meaning:

Old item keeps its data intact.
New item keep its current data and receives old item's data.
Both items use the same tables.

"history" table excerpt:
MariaDB [zabbix]> SELECT * FROM history WHERE itemid = 30886 LIMIT 5;
+--------+------------+--------+-----------+
| itemid | clock      | value  | ns        |
+--------+------------+--------+-----------+
|  30886 | 1609965887 | 0.3270 |  59358611 |
|  30886 | 1609966187 | 0.2747 |  14863699 |
|  30886 | 1609966486 | 0.3037 | 796425573 |
|  30886 | 1609966787 | 0.2855 |   9516260 |
|  30886 | 1609967086 | 0.2866 | 903642778 |
+--------+------------+--------+-----------+

I thought of doing something like this:
INSERT INTO history (`clock`, `value`, `ns`)
SELECT `clock`, `value`, `ns`
FROM history
WHERE itemid = 30886;

But that doesn't work. This way it won't insert the new itemid for every line and table "history" is specified twice, as target and source.
Maybe a more experienced fellow would point out some thoughts.
Any ideas?

After some digging, I managed to successfully copy history and trends from one item to another.
The sort of syntax I used is this:
History table:
INSERT INTO history (`itemid`, `clock`, `value`, `ns`)
SELECT '<new_itemid>', `clock`, `value`, `ns`
FROM history
WHERE itemid = <old_itemid>;

Trends table:
INSERT INTO trends (`itemid`, `clock`, `num`, `value_min`, `value_avg`, `value_max`)
SELECT '<new_itemid>', `clock`, `num`, `value_min`, `value_avg`, `value_max`
FROM trends
WHERE itemid = <old_itemid>;

The secret was with INSERT INTO and the SELECT clause that added the literal itemid.
The actual <new_itemid> was inserted into every new row.
The result:
MariaDB [zabbix]> INSERT INTO trends (`itemid`, `clock`, `num`, `value_min`, `value_avg`, `value_max`) SELECT '32041', `clock`, `num`, `value_min`, `value_avg`, `value_max` FROM trends WHERE itemid = 30886;
Query OK, 5227 rows affected (0.14 sec)
Records: 5227 Duplicates: 0 Warnings: 0

MariaDB [zabbix]> INSERT INTO history (`itemid`, `clock`, `value`, `ns`) SELECT '32041', `clock`, `value`, `ns` FROM history WHERE itemid = 30886;
Query OK, 2008 rows affected (0.10 sec)
Records: 2008 Duplicates: 0 Warnings: 0

I bet there's a better way, but this is all I could come up with for now.


Creating a Linked Server that Points to Itself I am attempting to create a linked server on SQL Server 2014 instance servername\instancename using the following call:

EXEC master.dbo.sp_addlinkedserver
    @server = N'servername\instancename',
    @srvproduct=N'SQL Server'

I am getting the error:

Msg 15028, Level 16, State 1, Procedure sp_addlinkedserver, Line 82
The server 'servername\instancename' already exists.

This works fine in SQL Server 2005, and according to MSDN,

  The linked server does not have to be another instance of SQL Server,

So I'm not sure what's changed in the recent version(s) that disallows this. Using the UI generates a similar message:

  You cannot create a local SQL Server as a linked server.

I understand that it's an odd thing to request, but it's to support some legacy code that worked in 2005 (and used to be on separate instances). The documentation states that it should work, but it doesn't. Is there a way to get this to work in 2014, or am I going to have to modify the underlying code?

Turns out that I was able to get it working with different parameters.

EXEC master.dbo.sp_addlinkedserver
    @server = N'LinkedServerName',
    @srvproduct=N'',
    @provider=N'SQLNCLI',
    @provstr=N'DRIVER={SQL Server};Server=(local)\InstanceName; Initial Catalog=DBNAME;uid=user;pwd=password;'


Very large btree index with few rows (openstreetmap gis data) I tried to import a subset of open street map data to a Postgres database using the standard tool (osm2pgsql). I then tried to rebuild the indices on the table using plain sql (dropping them, then recreating them) and they end up taking massive amount of space.

The table size is 9GB, with an int id, 70 text columns for attributes (for example column access_type has 250 different text values, and is null in 90% of cases), and a possibly large geometry column. If I try:

 CREATE INDEX planet_osm_polygon_accesstype_index
   ON planet_osm_polygon
   USING BTREE (access_type)

I end up with an index that's 1GB in size for 140k rows, which is massive considering how little data there is. This doesn't seem to happen on other systems, and I tried to ANALYZE, then VACUUM.

I don't know much about Postgres, but any hints about how to reduce the index size would be very appreciated.

(OS is ubuntu 12-04, PostgreSQL is version 9.1)

You seem to expect that rows with NULL values are excluded from a B-tree index automatically, but that's not the case. Those are indexed as well and can be searched for. However, since:

  access_type ... is null in 90% of cases

that's hardly useful in your case. Such common values hardly ever make sense in an index to begin with, be it NULL or any other value. Exclude NULL values from the index with a partial index.

CREATE INDEX planet_osm_polygon_accesstype_index ON planet_osm_polygon (access_type)
WHERE access_type IS NOT NULL;

Should be much smaller and thus faster.

Remember that you may have to include the same WHERE condition in queries to make Postgres realize it can apply the partial index.

Related answers with more details:

Unexpected Seq Scan when doing query against boolean with value NULL
Join table on some conditions


Does a single rollback affect all transactions? When I run the following script:

begin transaction
    select XACT_STATE()

    select @@TRANCOUNT

    begin transaction

    select XACT_STATE()

    select @@TRANCOUNT

    begin transaction

    select XACT_STATE()

    select @@TRANCOUNT

    rollback

    select XACT_STATE()

    select @@TRANCOUNT

I get the following result:

So one single rollback statement rolls back any number of transactions?

There is no point in writing the following then:

while @@TRANCOUNT > 0
      rollback

Yes commit and rollback behave differently in this respect.

Commit just decrements the @@trancount. Nothing is actually committed until that reaches zero.

Rollback will always rollback the transaction and set @@trancount to zero.

Just to be clear there is only one transaction going on, irrespective of the value of @@trancount. Nested transactions in SQL Server are a myth. See Paul Randal's blog post:

A SQL Server DBA myth a day: (26/30) nested transactions are real.


Ranking items on a per user basis I have a MySQL DB with 20k-50k items and I want various users (let's say 1k-10k) to rank these items for themselves, so this is not going to be a global ranking but a per user one.
All items are identified by their 7 digit id.
Since I'm guessing that there will be a pretty sparse ranking (perhaps about 1k items per user), I'm not sure how to store these rankings.

As I see it, there are two obvious solutions:

Add one column per user to the items table where I note each rank. --> Problem: If one rank changes, there will have to be a lot of updates.
Store the entire ranking of one user as an ordered list in one textfield. --> Then I can do all the reordering and inserting in my code and could easily query a few top ranked items. But I always have to read a whole bunch of data.

What would be the more efficient way and am I missing another solution.

I am well aware of a few other questions regarding the same general topic but they have a global ranking of the items (also all items actually have a rank which is not the case in my problem) and usually handle 100x more data.

Suggest to record the rankings, as they occur, in a third table where the primary key links the user and the item. Table only records as many rankings as are made available. Each user's rankings are kept separate.

This records a ranking as an ordered triple: (user, item, rank).

    CREATE TABLE `user_item_rank` (
      `fk_user` ... NOT NULL,
      `fk_item` ... NOT NULL,
      `rank` ... NOT NULL,
      PRIMARY KEY (`fk_user`,`fk_item`),
      KEY `fk_user` (`user_table`),
      KEY `fk_item` (`item_table`),
      CONSTRAINT `...` FOREIGN KEY (`fk_user`) REFERENCES ...
      CONSTRAINT `...` FOREIGN KEY (`fk_item`) REFERENCES ...
      ...
      );


SQL Server 2016 vs 2012 insert performance I have two SQL Server instances on the same server:

Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) Standard Edition
(64-bit)
Microsoft SQL Server 2016 (SP1-CU5) (KB4040714) -
13.0.4451.0 (X64) Enterprise Edition (64-bit)

sp_configure results are the same on both instances (except of new 2016 options).
I have created new databases on both instances on the same disk folder. Autogrowth parameters are the same.
Autocreate and autoupdate statistics options are turned off.
Then I have made a test with 10000 inserts into a heap:

set nocount on
go

create table dbo.TestInsert ( i int not null, s varchar(50) not null )

declare @d1 datetime, @d2 datetime, @i int
set @d1 = getdate()
set @i = 1

while @i <= 10000
begin
  insert into dbo.TestInsert ( i, s ) select @i, replicate( 'a', 50 )
  set @i = @i + 1
end

set @d2 = getdate()
select datediff(ms, @d1, @d2)
drop table dbo.TestInsert

Result 1

The average test time

2012 - 530 ms
2016 - 600 ms

So, 2016 is about 11% slower.

Then I have made an SQL Profiler trace with results saved to the table to see the single insert duration in microseconds.

Result 2

The histogram of a single insert duration 2012 vs 2016:

Transaction logs' growth from sys.dm_io_virtual_file_stats is:

2012 - 5174784 bytes
2016 - 5171200 bytes

During these tests both instances are started. But one test is running only at the one instance each time. I have allocated 8Gb RAM per instance. The query plans are the same. It would be interesting to run each instance on its own box. But probably the one machine is better, because here we have no hardware and enviromnent hidden differences.

Questions

Why is 2016 slower?
May someone reproduce this test?

Obviously it is very difficult the coincidence of having exactly the same versions in the same server but... I hope my results will help you.
I have two different machines configured both Windows Server 2012 R2 Standard. Unfortunately they don't have the same hardware but similar:

Machine 1 (SQL Server 2016)

Processor: Intel(R) Xeon(r) CPU X5650 @2.67GHz
Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64)   Apr 29 2016    23:23:58   Copyright (c) Microsoft Corporation  Standard Edition     (64-bit) on Windows Server 2012 R2 Standard 6.3  (Build 9600: ) (Hypervisor)

Machine 2 (SQL Server 2012)

Processor: Intel(R) Xeon(R) CPU E5-2667 0 @ 2.9 GHz
Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
May 14 2014 18:34:29
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3  (Build 9600: ) (Hypervisor)

And I run 5 times in both machines the same script you have provided and obtained the following average:

2012: 9961
2016: 8971

What is almost the opposite results that you obtain. Anyway, as you have seen, my 2012 machine has better processor but the hard disk that normally is what shows the difference is the same. So, even if 2012 has better resources is a bit slower for my case.

(Sorry, please, check again, my first version had some important erratums)


Using MySQL Workbench 8 on RHEL7 gui - When I highlight text in the SQL editor it turns black I'm assuming this is a config issue in MySQL Workbench or in RHEL.
How can I change this to a readable color?

Turns out this is a bug resolved by upgrading to 8.0.16


Error 666 on clustered primary key I am receiving error 666:

  the maximum system-generated unique value for a duplicate group was
  exceeded.

I am experiencing this on an Azure SQL Managed Instance.

However, I shouldn't be getting this error. The index in question is a clustered primary key on a bigint identity column. So there are no duplicates, and should be no uniquifier.

The table starts empty with the current ident of zero. I am attempting to insert 2.9 billion records, letting SQL Server assign the identity key. After receiving error 666, the current ident is 2147483648.

This makes no sense to me, as uniquifier is supposedly only used on non-unique clustered indexes that contain duplicate values. My table does not meet those conditions.

Does anyone have any ideas?

The records are inserted using an INSERT INTO SELECT FROM ORDER BY query. All columns are specified other than the identity column itself. I am absolutely certain the data type of the identity column is BIGINT.

The source table is a heap table with no identity or clustered indexes, so the bigint column in question does not exist there. The destination table has a duplicate definition to the source table, other than having the bigint identity column / clustered primary key added.

I am inserting all records from the source to the destination table, specifying all columns from the source table in the column list / select list for the insert. The bigint column is not specified in the insert because it is an identity column on the destination side only.

I had a thought to pre-populate my source heap table with a unique id column first (like row number), enable identity_insert on the destination table and try batched inserts of 100m at a time. Could that work?

Every time this query fails it takes 15 hours to roll back.

There is an NCI on the heap table in the desired insert order, but it doesn't include all columns so there are bookmark lookups happening. If I do an uncommitted read against the destination table during the load, I can see records, with an increasing identity.

Last execution plan I saw was a table scan followed by a sort.

I was able to resolve this by doing a batched insert of 100m records at a time. The insert completed successfully without error, and I was able to insert 2.88bn records.

The other solution I tested was to insert the records into a table with an identity column but no clustered index, then add the clustered primary key afterward. That also worked.

Not sure why this was occurring inserting into a clustered table. I only tested this on Azure SQL MI, not our on-premise SQL Server 2017. So I don't know if there is any difference there.


Query returning unused stored procedures When I run SP_askbrent@asof = '2014-05-20 16:00' a period where the database was timing out, I'm getting dozens of CREATE PROCEDURE xxx entries.

When I run the following query, the results show the query was created in 2003, and last updated over a year ago.

select d.name, p.name, e.last_execution_time, p.create_date, p.modify_date
from sys.DM_exec_procedure_stats as e
  right join sys.databases as d on d.database_id = e.database_id
  right join sys.procedures as p on p.object_id = e.object_id
WHERE is_ms_shipped = 0
order by e.last_execution_time asc, p.modify_date desc, p.create_date desc

What am I missing? Why am I seeing create procedure calls nonstop while the procedures are seemingly very old and unmodified. As a reference, the DB has 700+ stored procedures, while being only 6,5GB in size, and the query above shows that 675 of them have not been used since the last reboot (unless I'm misinterpreting what last_executed = NULL means).

Whenever a proc is executed, you'll see the full CREATE PROCEDURE recorded in the DMVs, which SP_askbrent will be querying.

Executions of each individual statement is also recorded, in sys.dm_exec_query_stats, and you can retrieve the exact text of what has been executed, by CROSS APPLY sys.dm_exec_sql_text(sql_handle) and applying the start and end offsets from the sys.dm_exec_query_stats DMV.

This is the code I use to identify what queries on my server are placing quite a load on the database in terms of IO, Duration and CPU.

It only works on SQL Server 2008+ though (unless you exclude the plan_hash and query_hash), but gives a nice indication of where I should start to look in terms of refactoring.

Each individual query within a stored procedure has the performance recorded (providing it completes and does not timeout)

SELECT
     deqs.plan_handle AS PlanHandle
    ,deqs.query_hash AS QueryHash
    ,DB_NAME(dest.dbid) AS DatabaseName
    ,OBJECT_SCHEMA_NAME(dest.objectid, dest.dbid) AS SchemaName
    ,OBJECT_NAME(dest.objectid, dest.dbid) AS ObjectName
    ,last_execution_time AS LastExecuted
    ,deqs.creation_time AS PlanCreationTime
    ,SUM(deqs.execution_count) AS Executions
    ,SUM(total_elapsed_time) AS SumDuration
    ,CAST(100.0 * SUM(total_elapsed_time)
                / SUM(SUM(total_elapsed_time)) OVER() AS NUMERIC(5,2)) AS PctDuration
    ,SUM(total_worker_time) AS SumCPU
    ,CAST(100.0 * SUM(total_worker_time)
                / SUM(SUM(total_worker_time)) OVER() AS NUMERIC(5,2)) AS PctCPU
    ,SUM(total_logical_reads + total_logical_writes + total_physical_reads) AS [SumIO]
    ,CAST(100.0 * SUM(total_logical_reads + total_logical_writes + total_physical_reads)
                / SUM(SUM(total_logical_reads + total_logical_writes + total_physical_reads)) OVER() AS NUMERIC(5,2)) AS PctIO
    ,SUBSTRING(dest.text, (deqs.statement_start_offset/2)+1,
                            ((CASE deqs.statement_end_offset WHEN -1 THEN DATALENGTH(dest.text)
                                  ELSE deqs.statement_end_offset
                                END - deqs.statement_start_offset)/2)+1) AS statementtext
    ,deqs.query_plan_hash AS QueryPlanHash
  FROM sys.dm_exec_query_stats AS deqs
  CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

  WHERE deqs.last_execution_time > DATEADD(dd, -1, GETDATE())
    -- Exclude where the dbid is NULL
    AND dest.dbid IS NOT NULL
    -- Exclude the non-user databases
    AND DB_NAME(dest.dbid) NOT IN ('master', 'msdb')
  GROUP BY
        dest.text, deqs.statement_end_offset, deqs.statement_start_offset, deqs.plan_handle, deqs.last_execution_time, deqs.creation_time,
          deqs.query_hash, dbid, dest.objectid, deqs.query_hash, deqs.query_plan_hash


Moving postgresql data to different drive I am using AWS as my cloud environment. I installed PostgreSQL on the same drive as my root instance volume. I have attached and mounted the second drive to my instance. Now I want to move all my PostgreSQL data to the different drive. I am still in development mode so I can delete the old data if it makes transfer easier at this point of time. What is the best way to do this?

Is PostgreSQL tablespace something which I should look at?

The following should do it:

Shut down PostgreSQL
Make sure PostgreSQL does not run any longer
Check that PostgreSQL is really stopped
Copy the old data directory to the new drive This is usually defined through a commandline parameter  (-D) for your service or through the PGDATA environment variable.
Update your PostgreSQL configuration (service, environment var) to point to the new data directory.
If you copied the data, rename the old data directory to make sure PostgreSQL cannot find it any longer. That way you'll get an error message if you didn't get the configuration right.
Start the service
If everything is running fine, you can delete the old directory.


Database level access in Azure SQL managed instance In my Azure SQL managed instance I have two databases, Database_A and Database_B.
User A_ABC has access only to Database_A and he is part of role db_owner.

When the tries to alter a table we are getting the below error

Query:
alter table mta.mta_dmn
add active char(3)

Error:
Msg 916, Level 14, State 2, Procedure TR_XYZ_DDL_AUDIT, Line 8 [Batch Start Line 0]
The server principal "A_ABC" is not able to access the database "Database_B" under the current security context.

There is no cross database joins or any link between Database_A and Database_B, with Database owner permission my understanding was the user should be able to do any activity within the Database_A. Also there is no procedure 'TR_XYZ_DDL_AUDIT' which exists in either of the databases

I am not able to find why this error is coming/not able to understand this error or able to find a solution. Dropped the login completely and recreated just in case, but the issue persists

My guess is that you have a DDL trigger, which is the root cause for this. Check for triggers in Database_A (assuming this is the the database where the table to be altered lives).

Possibly this trigger executes a stored procedure in Database_B which should do some auditing of this operation.

The name TR_XYZ_DDL_AUDIT, gives us some clues.


Is PDO_OCI classed as ODBC? I did post this question at https://dba.stackexchange.com/questions/94087/is-pdo-oci-php-pdo-using-oracle-instant-client-classed-as-odbc anonymously, but then it wouldn't allow me to comment on it so I registered and am posting it again here. I'm not sure what else to do.

I have a question that's been concerning me for some time.

We use Oracle at our company and we're about to upgrade to a newer release. I understand that Oracle have some additional licensing if you wish to connect to the database via ODBC.

My question is, we have a web application that connects to the Oracle database using PDO_OCI (PHP PDO using the Oracle Instant Client) - is this classed as accessing the Oracle database via ODBC?

In answer to this comment on the original question:

  "I understand that Oracle have some additional licensing if you wish
  to connect to the database via ODBC" - where did you read that? The
  connection method is mostly irrelevant for licensing AFAIK

I'm not sure. I asked our software vendor to upgrade our Oracle database version and they are doing this but have raised a question "Do you access our database directly via ODBC? As this affects our licensing costs.".

OCI (Oracle Call Interface) is not ODBC. ODBC drivers may use OCI, but not the other way round.

I've never heard of Oracle charging for ODBC drivers/access. In fact, there's no mention of an ODBC component in the global price list (link here).

I would clarify with your vendor - maybe they're trying to extract some cash out of you by supplying/supporting some kind of additional product, or maybe it's just a miscommunication.


How can I disable the fade in and fade out effects in pgAdmin 4 when opening windows? Is there any way to disable the fade in and fade out effects in pgAdmin 4 when opening windows?

Example of fade in and fade out:

I could not disable the fade completely, but I got to remove the zooming effect (which also executes faster) improving the user experience. The zooming effect is added by AlertifyJS.

Edit the file alertify.pgadmin.defaults.js file located in C:\Program Files (x86)\pgAdmin 4\v1\web\pgadmin\static\js

Then change

alertify.defaults.transition = "zoom";

to

alertify.defaults.transition = "fade";

After making these changes, restart pgAdmin4 or click on File > Reset Layout.

Hopefully this step assists someone to find a better solution...

One can also set the above option to none, in this case it seems to default to the pulse transition.


Measuring IOPS for innodb_io_capacity Aurimas Mikalauskas's 17 Key MySQL Config File Settings gives the following advice regarding innodb_io_capacity and innodb_io_capacity_max:

measure random write throughput of your storage and set innodb_io_capacity_max to the maximum you could achieve, and innodb_io_capacity to 50-75% of it, especially if your system is write-intensive.

But how do I measure these IOPS? What block size do I use (I assume the page size, so 16K by default)? Do I fsync after every block?
A fio command like this one gives me around 300 IOPS maximum, which seems very slow for these drives NVMe SSDs these are supposed to be. Most advice I can find online has these variables in the thousands.
fio --name fio --readwrite=randwrite --ioengine=libaio --direct=1 --filename=fio --numjobs=1 --size=4G --gtod_reduce=1 --iodepth=64 --bs=16k --fsync=1

If you have a running instance of mysql, throw some data at it, then compute
( Innodb_pages_read + Innodb_pages_written +
       Innodb_dblwr_writes + Innodb_buffer_pool_pages_flushed ) / Uptime

Something like:
SHOW GLOBAL STATUS;
run some heavy I/O queries
SHOW GLOBAL STATUS;

Tediously subtract the 5 values and evaluate the expression.
This will be a crude number, and you can probably set the innodb_io_capacity* higher than what the expression gives you.


How do I write IPv4 slash notation in IPV6 I have the following IPv4 address and mash in my Postgres hba.conf file:

192.168.0.0/24

Which works great when connecting my other Mac perfectly as long as I connect to the PostgreSQL database server directly using it's IPv4 address. But how do I convert it to IPv6?

The /24 in IPv4 is for small network. You can translate it in /96 for IPv6.

Here's the quote from documentation :

  An IPv6 address range might look like ::1/128 for a single host (in
  this case the IPv6 loopback address) or fe80::7a31:c1ff:0000:0000/96
  for a small network.


SSIS Adding column to excel sheet template destination when importing from SQL Table Source I have an excel sheet template that has 3 columns. I also have a SQL server table that has the same 3 columns plus an additional 4th column. I need to import all 4 columns from the SQL table into the excel spreed sheet template. My problem is that when I try to MAP the columns in SSIS I can only map the 3 columns that the Table and excel sheet have in common. How can I have SSIS import all 4 columns and not just the 3 columns that the source and destination have in common? Is there a way I can have SSIS add the additional field column to my excel spread sheet destination?

When you do Export data from the database (using the wizard - Import\Export)

select -- write a query to specify data transfer.

If you are creating package from scratch using BIDS, then in the execute sql task editor, in the sql statement

then in the data flow task in the source query specify T-SQL query as below :


Question about non-clustered index storage in SQL Server I have a table people with two non-clustered indexes.

create table people(
 id_person int,
 first_name varchar(50),
 last_name varchar(50),
 city varchar(100),
 state char(2),
 zip_code int
)

CREATE INDEX id_first_name_last_name ON people(first_name, last_name, id_person)
CREATE INDEX id_last_name_first_name ON people(id_person, last_name, first_name)

Each index refers to the same columns but in a different order.

-- Insert some rows

insert into people values(1,'joe','smith','new york', 'NY', 10701)
insert into people values(2,'john','smith','new york', 'NY', 10701)
insert into people values(3,'joyce','smith','new york', 'NY', 10701)
insert into people values(4,'jocelyn','smith','new york', 'NY', 10701)

The size of each index is as follows

TableName   IndexName            IndexID    Indexsize(KB)
people          NULL                     0           16
people          id_first_name_last_name  2           16
people          id_last_name_first_name  3           16

Each index is using 16KB.

Now if I add a clustered index on id_person

create clustered index ix_id_people on people(id_person)

I end up with the same amount of space being used by each index.

TableName   IndexName            IndexID    Indexsize(KB)
people          NULL                     1           16
people          id_first_name_last_name  2           16
people          id_last_name_first_name  3           16

The only difference is now instead of a HEAP, I have a clustered index.

I was expecting that with a clustered index on id_person, the storage engine wouldn't need to include id_person anymore in each non-clustered index.

Questions:

Are the values for each column referenced by a non-clustered index stored only for the first non-clustered index and then referenced by all additional non-clustered indexes?

If a non-clustered index references the clustered index column, are the values from that column copied physically onto each non-clustered index or just simply referenced?

Update 14:55

Here is the query being used to calculate index size

SELECT
OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
i.index_id AS IndexID,
8 * SUM(a.used_pages) AS 'Indexsize(KB)'
FROM sys.indexes AS i
JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
GROUP BY i.OBJECT_ID,i.index_id,i.name
ORDER BY OBJECT_NAME(i.OBJECT_ID),i.index_id

Nonclustered indexes always include a row locator.

For a heap this will be an 8 byte RID (File:Page:Slot). On a table with a clustered index it will be the clustered index key column(s). And it will always be the copied values not a pointer to the values. This duplication of CI key values into all non clustered indexes is why it is often recommended that the CI key be narrow and not frequently updated.

In the table shown in the question the Clustered index key is a 4 byte integer and potentially may also include a 4 byte uniqueifier for any duplicate key values.

In your case as the NCIs are not declared as unique the CI key will be appended to the NCI key.

For unique non clustered indexes the CI key would be added as included column(s) in the leaf pages unless explicitly made part of the key.

See Kalen Delaney: More About Nonclustered Index Keys for some additional information about how you can see this for yourself.

With these 4 rows of data all three indexes only consume a single 8KB data page.

SELECT index_id,
       index_level,
       page_count,
       record_count
FROM   sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('people'), NULL, NULL, 'DETAILED')

Returns

+----------+-------------+------------+--------------+
| index_id | index_level | page_count | record_count |
+----------+-------------+------------+--------------+
|        1 |           0 |          1 |            4 |
|        2 |           0 |          1 |            4 |
|        3 |           0 |          1 |            4 |
+----------+-------------+------------+--------------+

The additional page shown in use by sys.allocation_units.total_pages is an IAM page. This is not used for storing data but just for tracking the pages and extents comprising the index.


Set Warnings (Alerts & Thresholds) in MSSQL Replication Monitor using TSQL How can I configure the settings on this screen in Replication Monitor using TSQL?

For reference, we're on SQL 2014.

Detailed information about programmatically monitoring replications can be found on the official Microsoft Docs site:

Reference: Programmatically Monitor Replication

There is detailed information on all the stored procedures required to query monitoring details:

To monitor Publishers, publications, and subscriptions from the Distributor

sp_replmonitorhelppublisher
sp_replmonitorhelppublication
sp_replmonitorhelpsubscription

To monitor transactional commands waiting to be applied at the Subscriber

sp_replmonitorsubscriptionpendingcmd

To monitor merge changes waiting to be uploaded or downloaded

At the Publisher: sp_showpendingchanges

  returns a result set showing information on changes that are waiting to be replicated to Subscribers

At the Subscriber: sp_showpendingchanges

  returns a result set showing information on changes that are waiting to be replicated to the Publisher

To monitor Merge Agent sessions

sp_replmonitorhelpmergesession (either at the subscriber or distributor)
sp_replmonitorhelpmergesessiondetail (either at the subscriber or distributor)

Answering your question

The stored procedures required to read or change the monitoring thresholds are the sp_replmontirohelppublicationthresholds and sp_replmonitorchangepublicationthreshold procedures.

To view the monitor threshold metrics for a publication

sp_replmonitorhelppublicationthresholds

sp_replmonitorhelppublicationthresholds
          [ @publisher = ] 'publisher'
        , [ @publisher_db = ] 'publisher_db'
        , [ @publication = ] 'publication'
    [ , [ @publication_type = ] publication_type ]
    [ , [ @thresholdmetricname = ] 'thresholdmetricname'

The parameters are:

@publisher        : the name of the publisher
@publisher_db     : the name of the published database
@pbulication      : the name of the publication
@publication_type : 0 = transactional publication
................    1 = snapshot publication
................    2 = merge publication

To modify the monitor threshold metrics for a publication

sp_replmonitorchangepublicationthreshold

sp_replmonitorchangepublicationthreshold
          [ @publisher = ] 'publisher'
        , [ @publisher_db = ] 'publisher_db'
        , [ @publication = ] 'publication'
    [ , [ @publication_type = ] publication_type ]
    [ , [ @metric_id = ] metric_id ]
    [ , [ @thresholdmetricname = ] 'thresholdmetricname'
    [ , [ @value = ] value ]
    [ , [ @shouldalert = ] shouldalert ]
    [ , [ @mode = ] mode ]

The parameters are:

@publisher        : the name of the publisher
@publisher_db     : the name of the published database
@pbulication      : the name of the publication
@publication_type : 0 = transactional publication
................    1 = snapshot publication
................    2 = merge publication
@metric_id        : 1 = expiration
................    2 = latency
................    4 = mergeexpiration
................    5 = mergeslowrunduration
................    6 = mergefastrunduration
................    7 = mergefastrunspeed
................    8 = mergeslowrunspeed
@thresholdmetricname: the name of the publication threshold
................      (either this parameter or @metric_id is required)
@value            : the new metric value
@shouldalert      : generate alert (1=yes;0=no)
@mode             : monitoring mode (1=on;2=off)


How Do I Troubleshoot Login Failed Errors In SQL Server? What are some tips for troubleshooting SQL Server login errors? Questions like:

How Do I see the full Error Message? My client just has a generic error message that says logon failed with no real information/state information.
What if there is no error in the error log, but I still can't connect?
How Do I read the logon failure (Error 18456) error messages?
How Do I troubleshoot the various conditions found in the error messages?
What is Kerberos, why do I care and how do I fix errors related to it?

How Do I See the Full Error Message?

The SQL Server error log on the instance you are trying to connect to will have any errors that SQL Server generated about the logon failure. If SQL rejected the connection/failed the logon for some reason it will generate an 18456 error typically. On the SQL side, this error will have an error state that gives clues to the cause of the error. On the client side, however, you may or may not see the useful details - it likely that you'll just see an error state of 1.

To see the full error message it is best to look at the SQL Server that you are trying to connect to and look in the SQL Server Error Log. You should see the error for the connection attempt if the connection is making it to SQL Server. From there the information is more useful than some other error messages if you know where to look.

What If There is no Error in the SQL Server Error Log?

This could be caused by several factors but it is quite likely that you are not dealing with a logon failure. Logon failures do a good job of presenting themselves in the error log. In this case it is quite possible that you are dealing with a failure to connect to SQL Server. You can troubleshoot this problem first and at least verify that you are connecting to SQL Server in the first place and that there are no issues here. Rather than get into all the potential steps for troubleshooting that here in this answer, this Technet Twiki article is an excellent resource to at least get you started.

How do I Read the Error Messages in the Log?

There are several resources for the meaning of the various error states and error messages. The below is pasted from this msdn link to get you started with some of the more common states of the 18456 error. These states may change in future versions or in the version you are on so it is a good idea to pay attention to both the message and the state and to dig deeper to understand the issue.

State ---- Meaning

2  ---- User ID is not valid.
5  ---- User ID is not valid.
6  ---- An attempt was made to use a Windows login name with SQL Server Authentication.
7  ---- Login is disabled, and the password is incorrect.
8  ---- The password is incorrect.
9  ---- Password is not valid.
11 ---- Login is valid, but server access failed.
12 ---- Login is valid login, but server access failed.
18 ---- Password must be changed.

There are many other states as well and in edits I'll add more or in other answers you'll see more. This link has a lot of other states, most of which are valid for most versions of SQL Server above 2000. The important take away here is to look in your error log on the server side and read the message - with logon failures the messages tend to be useful a lot more often than not.

And again State 1 is a generic message on the client.

Other Errors
There are other errors for SQL Server login or connection failures. In future edits or answers from other people more information can be added as well.

What Do I Do With That Data?

It depends..

The Obvious States

States 2 & 5 signify an invalid User ID. Check the user you are working with. Does your login exist? Are you specifying the database and does the login exist as a user - has it been mapped as a user in that database.

State 6 - Oops! You are trying to pass an Active Directory login name in a manner that uses SQL authentication.. Basically trying to pass Login Name of 'FunDomain\Mwalsh' but you are connecting with SQL authentication, not trusted. This is sort of like a 2 or a 5, just a bit more descriptive.

State 7 - Login is disabled.. See where it is disabled, why it is disabled and rectify the situation.

States 8 & 9 - Bad password. Is it mistyped? Has the password changed?

State 18 - I've never encountered this one in the wild. But it is telling us that our password is set to be changed. I suppose you could see this if you are connecting from an application that uses SQL authentication (like Great Plains...) and have setup a new account with a password that needs to be changed on first use - but your application doesn't understand that or have a functionality to change the password. If you just use SSMS, you just get a dialog to change the password and all is well.

Less Obvious States

11 & 12 sort of mean the same thing- These are ones you have to pay attention to the message closely with.

Is the word "Token" in the error message? Like "Login failed for user 'myDomain\UserName'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors."

This can be a case of your SQL Server Service running as local system instead of a domain account with access to domain info potentially.

It can also be a case of needing to delegate authority through kerberos - for instance when accessing a Linked Server through Windows Authentication and passing the credentials - that double hop requires Kerberos, not NTLM. So you'll need to work through Kerberos.

Even this message can also mean other things - I've seen this recently after a migration to a server with an already existing logon - it was a very old logon at my client's environment. Dropping and recreating the logon resolved the issue. Why? It could be a few things but this post from Simon Sabin a couple years ago is an excellent resource here.

UPDATE After answering this I wanted to look an error code about default databases and I found a great blog post from Aaron Bertrand who answers a lot of questions here :-) Go check out his resource for more error messages including many that aren't in SQL Server 2012 or 2008 but still in the versions in use today. Great stuff there :-)


SQL Server Socket/CPU Configuration In your opinion what is the best configuration for optimal SQL Server performance in regards to virtual guest Socket/CPU configuration? I've read numerous articles stating wide socket configuration (1 cpu per socket) provides for optimal performance. In other articles optimal performance for SQL Server is achieved with a 1 socket and 'x' number of cpus in that one socket. Matching the guest VM Socket/CPU to the physical host configuration.
SQL Server Standard is limited with 4 sockets, but what configuration have you found to perform best? We do plan to perform load testing in the near future. I look forward to your feedback. Thanks in advance!!!

For most workloads, once a processor, the number of vcpus, and the amount of vRAM has been chosen for the vm, the best choice will vcpus and vRAM spread evenly across the smallest number of vNUMA nodes which allow the vcpus and vRAM to align with physical resources on the same number of physical NUMA nodes underneath.
This blog post from Mark Achtemichuk explains this idea quite well.
Virtual Machine vCPU and vNUMA Rightsizing – Guidelines
https://blogs.vmware.com/performance/2017/03/virtual-machine-vcpu-and-vnuma-rightsizing-rules-of-thumb.html
So, for SQL Server Standard Edition which allows the lesser of 4 virtual sockets or 24 vcpus: all other things being equal, all vcpus in a single vNUMA node, with all vcpus and vRAM coming from a single underlying physical NUMA node is prefered.  If the amount of RAM(or the vcpus) for that vm won't fit in one physical NUMA node, split it across 2 NUMA nodes.  Only go to 4 NUMA nodes if you have to.  And... based on the analytics workloads I work with... try to stay on 4 NUMA nodes as long as possible before going to 8.
The blog post from from Mark Achtemichuk also links to some material from Frank Denneman - he delves into some of the specific VMware parameters that may be needed to balance a VM as desired.
While VMware has in the recent past recommended a default setting cores-per-scoket=1 for all Windows VMs including SQL Server up to 64 vcpus* to let ESXi automatically plan the vNUMA configuration, they are transitioning to recommending setting cores-per-socket to the expected number of vcpus in a vNUMA node.
I have seen one particular SQL Server workload and system where even though all vcpus and vRAM could fit on one vNUMA node on a single physical NUMA node, it was intentionally placed on 2 vNUMA nodes balanced across 2 physical NUMA nodes.  This was done because a partiticular memory object was only partitioned at the node level and could not be dynamically partitioned to the cpu level as many objects can be.  The result, while all vcpus were in a single vNUMA node, was significant spinlock contention that was affecting performance.  This spinlock contention was relieved by splitting the same number of vcpus into two vNUMA nodes (and balancing those vNUMA nodes across 2 physical NUMA nodes).  That is a very exceptional case, however, and quite frankly I expect them to transition back to a single vNUMA node on a single physical NUMA node as that workload matures.
*For Windows VMware VMs above 64 vcpus, cores-per-socket muct be set higher than one in order to conform to the Windows 64 socket limit.


Logical optimization of joins and selections I am studying query optimization. I have read that it might be preferred to apply relational selections before a logical join operation. Is there a scenario where joining first and then applying selections would be desirable?

I searched for an example of this, and found that people seemed to be saying that a query with conjunctions might benefit from this, but it is not clear to me why this should be. This is not a question about how a particular query optimizer in a particular commercial product performs, it is a question of logic.

An example would be appreciated.

EDIT:

Inner Joining allows joining first and then selection and it would be optimized approach too. Example:

SELECT * FROM employee,**(Inner Joining)** department
WHERE employee.DepartmentID=department.DepartmentID;

Yes, there are cases where applying the filter after the join is preferable. But they are rare.

Consider this example:

SELECT ...
FROM a
INNER JOIN b
  ON b.key = a.key
WHERE a.some_string LIKE '%foo%'

Now, assume that very few rows in a has a matching row in b. It is now faster to first join and remove the rows from a that does not match and then apply the expensive string filter.


Migrate from SQL Server 2016 Failover Cluster to Availability Groups We have a 16 node SQL Server 2016 failover cluster with around 20 SQL Server instances and total number of databases around 600.

Since we are upgrading our hardware to Hyper-Converged infrastructure which has limitations with failover clusters with shared disks then we have to migrate our cluster to availability groups.

My question is: what should we consider if we are going to migrate to SQL Server availability groups? can we have the same number of instances and multiple availability groups on the same servers as we have in the current failover cluster?

Appreciate if someone guide us with some technet articles which talks about similar cases.

Prereqs, Restrictions, Recommendations - Always On Availability Groups states:

  The actual number of databases and availability groups you can put on a computer (VM or physical) depends on the hardware and workload, but there is no enforced limit. Microsoft has extensively tested with 10 AGs and 100 DBs per physical machine.

An availability group is really just a container to group mirrored databases and associate a listener with them, and to provide the interface with Microsoft Cluster to determine what server should be primary.  The only significant associated work is the replication of the databases.

So the short answer is "yes," you can use the same number of instances and databases, but you have to consider whether the additional load of replication will overload the system.  So you'll want to pay close attention to the databases that have higher I/O and try to distribute them amongst the nodes so that one node isn't a primary and/or secondary for multiple heavy hitters.  Or, if that's unavoidable, make sure you've got lots of bandwidth, CPU, memory, and disk space.

AlwaysOn Availability Groups, Listener, Named Instances, Port Numbers, etc. is also something that you'll want to peruse.


Is there a way to identify the object behind the database synonym? I have a script which uses a synonym. I have to alter that code and the problem is I can't understand which object that synonym refers to. Is there a way to find out? I am using the original database instance where that synonym has been created.

PS I am using MS SQL Server.

Thanks for your time.

This seems to work for me:

SELECT
    [Schema] = Sch.name
  , Object   = Syn.name
  , Syn.base_object_name
FROM sys.synonyms          AS Syn
    INNER JOIN sys.schemas AS Sch
        ON Sch.schema_id = Syn.schema_id;


Why does Postgres run SORT on many rows, before GROUP BY? I'm using Postgres 9.4, with a recently analyzed database. These are my tables:

Materialized view "public.vw_chemical_summary_by_ccg"
     Column      |         Type         | Modifiers
-----------------+----------------------+-----------
 processing_date | date                 |
 pct_id          | character varying(3) |
 chemical_id     | character varying(9) |
 items           | bigint               |
 cost            | double precision     |
Indexes:
    "vw_idx_chem_by_ccg_chem_id" btree (chemical_id)
    "vw_idx_chem_by_ccg_chem_id_vc" btree (chemical_id varchar_pattern_ops)
    "vw_idx_chem_by_ccg_joint_id" btree (pct_id, chemical_id)

               Table "public.frontend_pct"
      Column       |          Type           | Modifiers
-------------------+-------------------------+-----------
 code              | character varying(3)    | not null
 name              | character varying(200)  |
 org_type          | character varying(9)    | not null
Indexes:
    "frontend_pct_pkey" PRIMARY KEY, btree (code)
    "frontend_pct_code_1df55e2c36c298b2_like" btree (code varchar_pattern_ops)

This is my query:

EXPAIN (ANALYSE, BUFFERS)
SELECT pr.pct_id AS row_id, pc.name AS row_name,
       pr.processing_date AS date, SUM(pr.cost) AS actual_cost,
       SUM(pr.items) AS items
FROM vw_chemical_summary_by_ccg pr
JOIN frontend_pct pc
  ON pr.pct_id=pc.code AND pc.org_type='CCG'
GROUP BY row_id, row_name, date
ORDER BY date, row_id;

The results of the analyse shows a very slow sort on 5 million rows, BEFORE running the GroupAggregate.

The result of the GroupAggregate is just 5,000 rows. So wouldn't it make more sense to aggregate first, then sort?

Explain here: http://explain.depesz.com/s/IS1

Any other suggestions for speeding up the query would also be very welcome.

I ran into the same situation, a large number of rows of raw data being sorted on disk in order to be fed into a GroupAggregate which drastically reduced the number of rows.

I tried removing the ORDER BY clause; that allowed the optimizer to choose HashAggregate, which was significantly faster. To give a picture of the speed increase, a particular subset of my data took about

13.9 seconds using the original Sort and GroupAggregate
9.4 seconds for a simple EXPLAIN ANALYZE SELECT without a GROUP BY clause
9.6 seconds using the unsorted HashAggregate

It also scaled similarly with more data. I'm only using about a million rows, so you may still need a higher work_mem for your larger dataset (I am using 10MB). Although http://www.depesz.com/2013/05/09/explaining-the-unexplainable-part-3/#hash-aggregate suggests that HashAggregate will go to disk if it doesn't fit in memory, I found that reducing work_mem just prompted the planner to revert to the Sort/GroupAggregate plan.

I added SELECT * FROM (<unsorted query>) AS nested ORDER BY 1,2,3 to the outside and this sorts the aggregated output to give the same results as the original query, while still using the fast HashAggregate. I think there is perhaps an optimization opportunity here that the planner is not taking.

I tested this on 8.4 and 9.3 and got the same results with both.

Update:

If one of the aggregates being calculated is a COUNT(DISTINCT <fields>), then apparently HashAggregate can not be used.


Find Duplicate Customers Okay... I have a table that has customers:

-- Individual Table
* ID (Internal Unique ID)
* IndividualID (External Unique Individual Identifier)
* Last Name
* First Name
* Birth Date
* SSN
* ...

The issue is that sometimes a person gets multiple Individual ID's. Say the person doesn't provide a SSN for one Encounter, the last name changes, typo in birthday, etc. So you end up with the same person in the individual table multiple times:

1, Frost, Jack, 1/1/2000, 000-00-0008
2, Frost, Jack, 1/1/2000, 000-00-0003
3, Doe,   Jane, 1/1/2000, 000-00-0005
4, Doe,   Janet, 1/1/2000, 000-00-0005
5, Frost, Janet, 1/1/2000, 000-00-0005

Those are just some examples. The basic idea is that I need to find individuals that are potential matches, so that the right person can merge the individuals into a single account.

The particular query I'm currently on is on SS2008-SP1, but I have other queries on SS2005 through SS2012. Is there any way I can improve this?

Initially I had a single select statement (instead of 2 temp tables, 5 inserts and a select statement), but the "This or This or This or..." took many minutes and this takes ~10 seconds. Population of Customers is ~144k (Select count(*) from Data)

Current I'm using a simple attempt to try and match four parts: Last Name, First Name, DOB, SSN. If 3 or 4 of them match on different individuals, the need to be inspected closer to determine if they really are the same person.

IF object_id('tempdb..#DATA') IS NOT NULL
    DROP TABLE #DATA;
GO

CREATE TABLE #DATA (
      EXTID VARCHAR(30) NOT NULL
    , LNAME VARCHAR(30) NULL
    , FNAME VARCHAR(30) NULL
    , SSN VARCHAR(11) NULL
    , DOB VARCHAR(8) NULL
    )
GO

INSERT INTO #DATA
SELECT
      EXTID = D1.EXTERNALID
    , LNAME = D1.LASTNAME
    , FNAME = D1.FIRSTNAME
    , SSN = CASE WHEN D1.SSN = '000-00-0000' THEN NULL ELSE D1.SSN END
    , DOB = convert(VARCHAR, D1.DOB, 112)
FROM Data D1
WHERE Type = 1 and STATUS = 1
GO

SELECT D1.*, [Splitter] = 'MATCH', D2.*
FROM #Demo D1, #Demo D2 WHERE D1.ID > D2.ID
    AND (   D1.LNAME = D2.LNAME
        AND D1.FNAME = D2.FNAME
        AND D1.SSN   = D2.SSN
        AND D1.DOB   = D2.DOB)
        UNION
SELECT D1.*, 'LName', D2.*
FROM #Demo D1, #Demo D2 WHERE D1.ID > D2.ID
    AND (   D1.LNAME <> D2.LNAME
        AND D1.FNAME = D2.FNAME
        AND D1.SSN   = D2.SSN
        AND D1.DOB   = D2.DOB)
        UNION
SELECT D1.*, 'FName', D2.*
FROM #Demo D1, #Demo D2 WHERE D1.ID > D2.ID
    AND (   D1.LNAME = D2.LNAME
        AND D1.FNAME <> D2.FNAME
        AND D1.SSN   = D2.SSN
        AND D1.DOB   = D2.DOB)
        UNION
SELECT D1.*, 'SSN  ', D2.*
FROM #Demo D1, #Demo D2 WHERE D1.ID > D2.ID
    AND (   D1.LNAME = D2.LNAME
        AND D1.FNAME = D2.FNAME
        AND D1.SSN   <> D2.SSN
        AND D1.DOB   = D2.DOB)
        UNION
SELECT D1.*, 'DOB  ', D2.*
FROM #Demo D1, #Demo D2 WHERE D1.ID > D2.ID
    AND (   D1.LNAME = D2.LNAME
        AND D1.FNAME = D2.FNAME
        AND D1.SSN   = D2.SSN
        AND D1.DOB   <> D2.DOB);

Edit to add Distinct Counts:

LName   FName   SSN DOB Count
36737   14539   115073  34284   144044

Edit: Cleaned up a bit to get rid of second temp table. Poking around the Estimated Execution plan, the above query - broken into 5 parts - uses hash map inner joins and takes about 10 seconds. My initial query, and other variations seem to use loop joins and is still chugging along at 10+ minutes.

To find rows where at least three out of four of those columns match you can use.

SELECT  D1.ID, D2.ID
FROM   DATA D1
       JOIN DATA D2
         ON D1.SSN = D2.SSN
            AND D1.ID > D2.ID
            AND 2 <= CASE
                       WHEN D1.FNAME = D2.FNAME THEN 1
                       ELSE 0
                     END +
                     CASE
                       WHEN D1.LNAME = D2.LNAME THEN 1
                       ELSE 0
                     END  +
                     CASE
                       WHEN D1.DOB = D2.DOB THEN 1
                       ELSE 0
                     END
UNION ALL
SELECT D1.ID, D2.ID
FROM   DATA D1
       JOIN DATA D2
         ON D1.DOB = D2.DOB
         AND  D1.FNAME = D2.FNAME
         AND  D1.LNAME = D2.LNAME
         AND D1.SSN <> D2.SSN
         AND D1.ID > D2.ID

The top branch gets all rows where the SSN are the same and at least 2 out of the three other columns are the same. The join on SSN is likely to be pretty selective in itself.

That just leaves one other possible three column combination left which is dealt with by the second branch.

Both branches of the UNION ALL have an equi join so it should perform better than a join with some complicated OR condition.


Reclaiming space from a large partitioned database We have a database where one of the business group decided to remove several millions of rows as part of their upgrade process. (we removed for them..).. This database is partitioned based on a date and removing all these rows would help us gain around 30% of the DB size back. But obviously, if we run the shrink command, there is a possibility of the database getting fragmented and running rebuild obvious increases the space of the database. Is there any better way of reclaiming the space other than moving to a different file group as suggested by Paul Randall?.
If we go via the traditional shrink and rebuild index route to reclaim the space on several of these partitions, we will be having to put the db in simple (bulk did not help much with reindex ) to avoid the transaction log from getting filled. This obviously will break the log shipping and we will have to setup the logshipping on this VLDB database back again which would be another tedious work. For a VLDB, what would be the best way to reclaim space for few of these tables without breaking the logshipping and using the existing commands?

Thanks for your help with this

I recently had a similar project where we implemented data compression on a partitioned table and reduced our space usage, resulting in the need to shrink down the files.  We did the following to manage this while keeping the database online:

Off hours DBCC SHRINKFILE executions.  We would start the shrink file off production hours and cancel it before the next day's worth of production started.  This would go on for several weeks, but progress on the shrink was not lost (as pages are getting moved towards the "front" of the file and aren't rolled back).  This did cause fragmentation, but the fragmentation was acceptable to our business in order to keep the database online during file cleanup.
Rebuilding individual partitions for the indexes.  If your table is partitioned, my view is that you should be doing this anyway because it minimizes the impact of the rebuild.  While we were doing file shrinks, we only did weekly index maintenance (we usually do daily)  in order to work with/around the DBCC SHRINKFILE.  Once we had reduced the filesize, we went back to daily index rebuilds.  In order to rebuild the individually fragmented partitions, we leveraged Ola Hallengren's scripts, but the essential command is ALTER INDEX foo ON bar REBUILD PARTITION=n.  You would run this command for each partition (n) that you wanted to rebuild.

This was a multi-week process for us, but we reduced a 1.5 TB file down to ~500 GB and did not interrupt the database service to do it.


Fast alternative for "NOT IN" I have a table A that got a field called id that is the primary key of this table. I also have a table called B which also have a field called id as primary key.

Now I want to get all rows from Table A where the id value is not present as value in any table B id field-value.

My first query looked like this:

SELECT a.id FROM a WHERE a.id NOT IN (SELECT DISTINCT b.id FROM b)

Then I build a query that looked like this to improve the speed:

SELECT a.id FROM a LEFT JOIN b ON a.id = b.id WHERE b.id IS NULL

Now I got 600k rows in table A and 400k rows in table B and things are getting very slow. Is there any better query to run for this kind of operation or is there perhaps a better way to solve the problem at all? Any hints or pointers?

What you  must do is increase the join_buffer_size. What is  join_buffer_size ?

  The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans. Normally, the best way to get fast joins is to add indexes. Increase the value of join_buffer_size to get a faster full join when adding indexes is not possible. One join buffer is allocated for each full join between two tables. For a complex join between several tables for which indexes are not used, multiple join buffers might be necessary.

I highlighted that sentence because your query is as simple as it is going to get. You are doing a join on an integer which I assume is already indexed in both tables. If id in table b us not indexed, please index it ASAP.

I would suggest raising join_buffer_size to 4M.

You can change join_buffer_size in your session and rerun the query

SET join_buffer_size = 1024 * 1024 * 4;
SELECT a.id FROM a LEFT JOIN b ON a.id = b.id WHERE b.id IS NULL;

GIVE IT A TRY !!!


What database technologies do big search engines use? Does anybody knows how Google or Yahoo perform searches for keywords against very very huge amounts of data?  What sort of database or technologies do they employ for this?

It takes few milliseconds, but they have more than a billion pages indexed.

Pigeons.
The heart of Google's search technology is PigeonRank™, a system for ranking web pages developed by Google founders Larry Page and Sergey Brin at Stanford University:

Building upon the breakthrough work of B. F. Skinner, Page and Brin reasoned that low cost pigeon clusters (PCs) could be used to compute the relative value of web pages faster than human editors or machine-based algorithms. And while Google has dozens of engineers working to improve every aspect of our service on a daily basis, PigeonRank continues to provide the basis for all of our web search tools.
Why Google's patented PigeonRank™ works so well
PigeonRank's success relies primarily on the superior trainability of the domestic pigeon (Columba livia) and its unique capacity to recognize objects regardless of spatial orientation. The common gray pigeon can easily distinguish among items displaying only the minutest differences, an ability that enables it to select relevant web sites from among thousands of similar pages.
By collecting flocks of pigeons in dense clusters, Google is able to process search queries at speeds superior to traditional search engines, which typically rely on birds of prey, brooding hens or slow-moving waterfowl to do their relevance rankings.
When a search query is submitted to Google, it is routed to a data coop where monitors flash result pages at blazing speeds. When a relevant result is observed by one of the pigeons in the cluster, it strikes a rubber-coated steel bar with its beak, which assigns the page a PigeonRank value of one. For each peck, the PigeonRank increases. Those pages receiving the most pecks, are returned at the top of the user's results page with the other results displayed in pecking order.


Is it better to add a data file or better to increase the existing files (MAXSIZE) I'm quite familiar with SQL-Server as a developer but not so much in the DBA role.

Currently I'm working on a rather large DB (~7TB, living in 7 files, all equal in size and all within one single file group). The DB is running out of disc space.

The customer is going to increase the space available by 2 TB.

To state some things first:

The system is a fail over cluster
It is - why ever - no option to add a second file group.
I know that using a second file group might offer great benefits... (not wanted by the customer. It's an old system and nobody dares to touch it more than necessary...)

The options I'm thinking of:

I could use ALTER DATABASE with MODIFY FILE to increase the size of the existing files (~1TB each at the moment).
I could use ADD FILE to add one or two additional files similiar to the existing ones.

The questions

Is there anything special to keep in mind with fail over clusters'?
Is there any reason why to prefere one of the options?

remark: The goal is as little impact as possible.

Is there anything special to keep in mind with fail over clusters?

No

  Is there any reason why to prefere one of the options?

Yes, especially depending on your IO subsystem.  SQL Server uses two algorithms when deciding which file to use for allocating new extent. Round robin and proportional fill. My recommendation will be to use your first option.

I could use ALTER DATABASE with MODIFY FILE to increase the size of the existing files (~1TB each at the moment).

  Round robin means that the SE will try to allocate from each file in a
  filegroup in succession. For instance, for a database with two files
  in the primary filegroup (with file IDs 1 and 3, as 2 is always the
  log file), the SE will try to allocate from file 1 then file 3 then
  file 1 then file 3, and so on.

  The twist in this mechanism is that the SE also has to consider how
  much free space is in each of the files in the filegroup, and allocate
  more extents from the file(s) with more free space. In other words,
  the SE will allocate proportionally more frequently from files in a
  filegroup with more free space. This twist is called proportional
  fill.

  Proportional fill works by assigning a number to each file in the
  filegroup, called a ‘skip target’. You can think of this as an inverse
  weighting, where the higher the value is above 1, the more times that
  file will be skipped when going round the round robin loop. During the
  round robin, the skip target for a file is examined, and if it’s equal
  to 1, an allocation takes place. If the skip target is higher than 1,
  it’s decremented by 1 (to a minimum value of 1), no allocation takes
  place, and consideration moves to the next file in the filegroup.

Paul Randal explains this in details with example,
Investigating the proportional fill algorithm.


Trigger function taking column names as parameters to modify the row I am trying to write a trigger function in Postgres 9.4. Something like this (not working, yet):

  CREATE FUNCTION set_point_from_coords(source _regclass, target _regclass)
    RETURNS trigger AS $func$
  BEGIN
    NEW.target := ST_SetSRID(ST_Point(NEW.source[1], NEW.source[2]), 4326);
    RETURN NEW;
  END;
  $func$ LANGUAGE plpgsql

In this case, target is a column of type geometry and source is an array of decimals.

When a row is inserted with a coords array, I want to convert that to a point. The above would work if I just hard-coded column names, but I want to do it for different tables and different pairs of columns using the same function. And I don't have direct control over the INSERT itself.
Here's some of my experimentation: http://sqlfiddle.com/#!15/dddcd/1

Found this related blog post which I'm having a really hard time parsing.

I could live with this running after insert/update instead of before, if that makes it easier to code.

Problem
You had to pick the spot where all possible complications come together.

SQL (or PL/pgSQL) does not allow to parameterize identifiers. You need dynamic SQL with EXECUTE for that.

But the special plpgsql variable NEW in trigger functions is not visible inside dynamic code executed with EXECUTE.

And it's further complicated by passing column names as arguments to the trigger function in CREATE TRIGGER.

And it's not enough to make the target column dynamic, you want to fetch the source value from another dynamic column of the the row.

Unless you know your way around all the involved matters, I would rather try a simpler approach. Write a separate trigger function for each trigger and assign to the target column without dynamic SQL.
Solution
That said, it can be done with one line of code - and many lines of explanation. For the original example in the question, assuming this table definition:
CREATE TABLE tbl (
  tbl_id serial PRIMARY KEY,
  geom geometry,
  coords double precision[]
);

You need the additional module hstore installed (once per database) for this to work. Alternatively you could also use an undocumented feature of json_populate_record() / jsonb_populate_record() for the same purpose:

How to set value of composite variable field using dynamic SQL

CREATE OR REPLACE FUNCTION trg_demo()
  RETURNS trigger AS
$func$
BEGIN
   EXECUTE format('SELECT ($1 #= hstore(%L, ST_SetSRID(ST_Point($1.%2$I[1], $1.%2$I[2]), 4326)::text)).*'
                , TG_ARGV[0], TG_ARGV[1])  -- target (geom), source (coords)
   USING  NEW
   INTO   NEW;
   RETURN NEW;
END
$func$ LANGUAGE plpgsql;
Trigger:
CREATE TRIGGER demo
BEFORE INSERT OR UPDATE ON tbl
FOR EACH ROW EXECUTE PROCEDURE trg_demo('geom', 'coords');

If you don't understand what's happening here, consider my advice above.
Explanation
I formatted the dynamic computation of the geometry bold to help you get a grip on this. Compare with the simple case below.
db<>fiddle here - with point instead of geometry, as PostGIS is not installed.
Old sqlfiddle.
Here is an even simpler version, just assigning the bare text value (which would require the target column geom to be type text). The simplified part in bold again:
   EXECUTE format('SELECT ($1 #= hstore(%L, $1.%I::text)).*'
                , TG_ARGV[0], TG_ARGV[1])  -- target (geom), source (coords)
   USING  NEW
   INTO   NEW;
The core feature is the hstore operator #= to (per documentation):

replace fields in record with matching values from hstore

Both target and source are columns of the new row, which complicates matters. If the source value was a constant we could simply:
NEW := NEW #= hstore(TG_ARGV[0], 'POINT(123.0 456.0)');

Related:

Assign to NEW by key in a Postgres trigger

But we need dynamic SQL to resolve the column name and fetch the source value.

Use format() to concatenate the query string safely.

TG_ARGV[0] and TG_ARGV[1] access the first two elements to the (0-based!) text array of arguments passed by CREATE TRIGGER.

%I concatenates the argument passed to format() as identifier (safe against SQL injection).

$1 references the value passed to EXECUTE in the USING clause.

We need the cast to ::text after calculating the geometry, because hstore takes text.

We need to decompose the row NEW for the assignment back, because plpgsql assigns rows as target column-by-column.


SQL Server replication: can I modify contents of a snapshot? I have a merge replication which I was unfortunate enough to start modifying, in a way that required the subscriptions to get re-initialized. It makes very good sense and all, since I was touching some filters. But in my case, I changed something and then changed it back, so I know that the result of the new snapshot and new initialization will be identical to what I had.

However, the snapshot is re-created and the subscription is marked for re-initialization. And now the snapshot fails to get re-applied because there are data in subscriber's tables and the .bcp files in the snapshot have some data to load (same, actually, as we know). I can empty the .bcp file and then it seems to move on to the next table, so that's my strategy for now. I just wonder if anybody sees any pitfalls here. I suspect they are many and deep, just that my lack of experience doesn't let me see them.

I've experimented quite extensively with the mentioned situation and it looks like there are different scenarios in which snapshot is re-created: sometimes they involve bulk data being transferred, sometimes they don't. I suppose that the logic on the Publication side is clever enough to see when re-initialization of a subscription will require data being loaded again, and when - not.

Sometimes it brings disaster though, like when you try to re-create a filter, aren't able to and then would have to drop the article and create it again with a filter. The data is actually good on both sides, even if it's not matching the filter on the subscription - well, so what - it will after some updates. The problem is that then the snapshot will be re-generated and subscription marked for re-init. But the table is full of (good) data already and more likely than not you get an error trying to apply the snapshot.

So then, as in my question, I took all the involved .bcp files and emptied them (with a powershell script as they were many). Deleting them didn't help, because snapshot realizes something's been stolen from it. But empty bulk data files are harmless and then everything goes through, thankfully!

I suppose you could find a scenario when your application would suffer from some data being temporary present that doesn't match the replication filter, but in my case I think everything is working - so far so good.


Dumping and restoring on db server - postgres Is there a way to execute a backup and restore of PostgreSQL DB from App server without calling pg_dump from App server and executing pg_restore also on App server. In this way I'm executing operations from App server on DB server and making unnecessary network traffic.

I would like to execute dumping and restoring directly on DB server, but to initiate it from my application.

My current situation if that I make extra network traffic, wait more time and also I need to install PostgreSQL client on my app server.

Any hints?

There are a bunch of ways of doing this.  My recommendation would be to have a dedicated database for maintenance operations and use something like pg_message_queue to send messages to listening applications.  You could then have a listener which could initiate pg_dump or pg_restore based on what is passed to it.

This would end up being queries like:

SELECT * FROM pg_mq_send_message('backup_db', 'mydbname'::text);

The listener could then be notified in real time (or wake up and poll, both methods are supported), and go ahead and backup bydbname using psql.  You could do the same with restore, with a different queue.

You could try to just use LISTEN and NOTIFY without queue tables, but there is virtually no security on those commands themselves, so that could lead to issues.  Also note this is lighter weight than ssh and API is totally asynchronous.

Disclaimer I am the author of pg_message_queue.


Cannot upgrade to v12 due to sql_identifier data type I am attempted to upgrade Postgres 11.8 to 12.3 on an AWS RDS instance. The upgrade fails with the following error:

Your installation contains the "sql_identifier" data type in user tables
and/or indexes.  The on-disk format for this data type has changed, so this
cluster cannot currently be upgraded.  You can remove the problem tables or
change the data type to "name" and restart the upgrade.
A list of the problem columns is in the file:
tables_using_sql_identifier.txt

I do not have access to this file. I believe it is not available to AWS users. I can find the sql_identifier columns with the following:
SELECT
atttypid::regtype, *
FROM   pg_attribute
WHERE  attrelid = 'information_schema.columns'::regclass
ORDER  BY attnum

I can update all of the columns to be of type "name" but changing the information_schema tables seems aggressive and I cannot find any information about this error anywhere on the internet. Any thoughts are appreciated.

You need to find the columns with that type outside of information_schema.  Information_schema is not user tables, they are system tables.
SELECT *
FROM pg_attribute
  join pg_class on attrelid=oid
  join pg_namespace on relnamespace=pg_namespace.oid
WHERE atttypid::regtype::text like '%sql_identifier'
  and nspname!='information_schema'


Oracle Single table with json vs set of tables using joins I'm building a reporting solution. The data will be stored on Oracle database. I expect to get near to several billion data set since i have to keep data for 1 year period. When designing database schema i faced problem to go with single table or set of tables.

Single table in format of (ID, DATETTIME, JSON ) which has all the data dumped as json in JSON column. this will avoid any joins also cater for future event format changes.

Set of tables ( 4-6) data dived on them. query will be using several joins and several unions. Format will be hard to change.

What would be the better approach in terms of performance. For large data set how efficient json over joins ?

Part of that depends on whether or not you need to ever parse that JSON, say in a WHERE clause, or GROUP BY, etc...
If it stays a faceless blob, it's probably faster to leave it as option #1; but keep in mind this is rarely the case - you're likely to unexpectedly be asked to report/query on the JSON at some point!!!
I don't know about Oracle, but in Microsoft SQL Server, UNIONs tend to perform poorly when ALSO joining; they seem fine as a standalone query but once it becomes a subquery things seem to slow down, unless you do something like a materialized view.
So while I personally prefer option 2, the performance may bog down with a large data set that is over 1 million rows. Can you refrain from using a UNION, and instead place the records in the same table but distinguish them with a type column?

How many rows (and how wide is a single row) of your "large data set"?
Can you temporarily obtain a test environment and try both options under a load test?


Nested query efficiency in MySQL I have a table with several attributes like this:

id  id2  attribute
--------------------------------
 1  100  blue
 2  100  red
 3  100  green
 4  100  white
 5  102  blue
 6  102  green
 7  102  red
 8  103  red
 9  103  blue
10  103  white
11  104  red
12  104  black
13  104  green
14  104  orange
15  105  red
16  105  blue
17  105  green

I want to know:
- what are the top attributes for the entries that have 'blue'?
or
- what are the top attributes for the entries that have 'blue' and 'red'?

For the second query the results should be:

attribute   count1
--------------------
green       3
white       2

I can build the query dynamically. I have this working, using this approach:

SELECT
  mytable.attribute,
  count(mytable.id) as count1
FROM
    mytable,
    (SELECT
     id2
    FROM
     mytable
    WHERE
     attribute in ('blue', 'red')
    GROUP BY
     id2
    HAVING
     count(distinct attribute) = 2) as t
WHERE
 mytable.id2 = t.id2
and
 attribute NOT IN ('blue', 'red')
GROUP BY
 mytable.attribute
ORDER BY
 count1 desc

The problem is that if the inner query has a lot of entries the while process takes too long. Can anyone suggest a way to improve this?

Add indexes on (attribute, id2) and on (id2, attribute) and try this:

SELECT
    m.atttribute,
    COUNT(*) AS cnt
FROM
    mytable AS m
  JOIN mytable AS m1
    ON  m1.attribute = 'blue'
    AND m1.id2 = m.id2
  JOIN mytable AS m2
    ON  m2.attribute = 'red'
    AND m2.id2 = m.id2
GROUP BY
    m.attribute
HAVING
    m.attribute NOT IN ('blue', 'red')
ORDER BY
    cnt DESC ;

Test in SQL-Fiddle


What is the maximum number of users in MS SQL What is the maximum number of users on a single database that one can define?

I cannot seem to find this information on Maximum Capacity Specifications for SQL Server

There is no defined max number of logins and users in SQL Server. Although, based on how your application is configured you can hit the max number of user connections you can have to a given database (32,767) rather quickly.


Convert spatial points data to line data for use in Line Layer in SQL Server Reporting Services (SSRS) I have a query that returns the fields Order, Lat, Long, SpatialData, <OtherStuff>. SpatialData is a point.

I want to create a line layer that draws lines between my points based on the order field.

According to http://technet.microsoft.com/en-us/library/ee240828.aspx#LineLayer I need to do something like this:

SELECT N'Path' AS Name
    , CAST(
        'LINESTRING(
           -76.5001866085881 42.4310489934743,
           -76.4602850815536 43.4353224527794,
           -73.4728622833178 44.7028831413324)'
        AS geography) as Route

But how do I go from my data to something like that?

Below is a simplistic, single-query way to do it as a list of small 2 point line segments in one query, without using cursors.  There's a lot more that can be done if you build and store your lines in another table with a cursor and the .STUnion method and your point spatialdata field.

Assumptions and changes:  1.  includes city and state.  2. to be more realistic I stored lat and long as decimal(10,7), so, unfortunately this example converts lat and long back to varchar to build the line of Well Known Text (WKT).  3. I renamed order to ptorder since order is a reserved word.  4. Only one path or line exists in the source data table and cities not on the path have a NULL ptorder.  5. ptorder is a sequence without gaps, or else the query gets far more complicated.  6. I'm avoiding cursors.  7. I'm assuming your data looks like this:

5,    42.37, -71.03,  'Boston', 'MA'
3,    39.75, -104.87, 'Denver', 'CO'
2,    33.93, -118.40, 'Los Angeles', 'CA'
4,    25.82, -80.28,  'Miami Intl', 'FL'
NULL, 40.77, -73.98,  'New York', 'NY'
1,    33.43, -112.02, 'Phoenix', 'AZ'

Load some test data like this:

CREATE TABLE pointdatatable (
    ptorder             INT,
    lat                 DECIMAL(10,7),
    long                DECIMAL(10,7),
    pointspatialdata    GEOGRAPHY,
    city                VARCHAR(20),
    state               CHAR(2) )

INSERT INTO pointdatatable (ptorder, lat, long, pointspatialdata, city, state) VALUES
    (5, 42.37, -71.03,  CAST('POINT(-71.03 42.37)' AS GEOGRAPHY),  'Boston', 'MA'),
    (3, 39.75, -104.87, CAST('POINT(-104.87 39.75)' AS GEOGRAPHY), 'Denver', 'CO'),
    (2, 33.93, -118.40, CAST('POINT(-118.40 33.93)' AS GEOGRAPHY), 'Los Angeles', 'CA'),
    (4, 25.82, -80.28,  CAST('POINT(-80.28 25.82)' AS GEOGRAPHY),  'Miami Intl', 'FL'),
    (NULL, 40.77, -73.98,  CAST('POINT(-73.98 40.77)' AS GEOGRAPHY),  'New York', 'NY'),
    (1, 33.43, -112.02, CAST('POINT(-112.02 33.43)' AS GEOGRAPHY), 'Phoenix', 'AZ')

Then this query produces the segments using WKT:

SELECT
    a.ptorder,
    a.lat,
    a.long,
    a.city,
    b.ptorder,
    b.lat,
    b.long,
    b.city AS nextcity,
    CAST('LINESTRING(' + CAST(a.long AS VARCHAR) +' '+ CAST(a.lat AS VARCHAR) +', '+
        CAST(b.long AS VARCHAR) +' '+ CAST(b.lat AS VARCHAR) + ')' AS GEOGRAPHY) AS segmentspatialdata
FROM
    pointdatatable a
LEFT OUTER JOIN
    pointdatatable b
ON
    b.ptorder = a.ptorder + 1
WHERE
    b.ptorder IS NOT NULL
ORDER BY a.ptorder


What characters are word breakers in English for SQL Server 2005 and 2008 R2? I can find what DLL supports English word breakers by using sp_help_fulltext_system_components but I have not been able to find an actual list of the word breaking characters for English (like blank, ., %, etc.).

Anyone know of a source for this info?

This isn't an official list, but using a loop to work through a list of characters, and using sys.dm_fts_parser like so:

declare @i integer
declare @cnt integer
set @i=0
while @i<255
begin
  set @cnt=0
  select @cnt=COUNT(1) FROM sys.dm_fts_parser ('"word1'+REPLACE(CHAR(@i),'"','""')+'word2"', 1033, 0, 0)
  if @cnt>1
    begin
      print 'this char - '+CASE WHEN @i > 31 THEN char(@i) ELSE '' END+' - char('+convert(varchar(3),@i)+') is a word breaker'
    end
  set @i=@i+1
end

I can generate a list of characters that sys.dm_fts_parser reckons break the words. (sys.dm_fts_parser returns a row for every 'word' found in the import, so if it returns more than 1 row we had a word breaker)

This could be expanded to extended/non-english character sets by using nchar() rather than char() (and a bigger value for @i), and changing parameter 2 (lcid) in the call to sys.dm_fts_parser


What are my locking options for a Merge statement? I have a stored procedure that performs a MERGE statement.

It seems like it locks the whole table by default when performing the merge.

I'm calling this stored procedure inside of a transaction where I'm also doing some other stuff and I wish it would only lock the rows affected.

I tried the hint MERGE INTO myTable WITH (READPAST) and it seemed to lock less. But there was a warning in the ms doc that said it could insert duplicate keys, bypassing even the primary key.

Here is my table schema:

CREATE TABLE StudentDetails
(
StudentID INTEGER PRIMARY KEY,
StudentName VARCHAR(15)
)
GO
INSERT INTO StudentDetails
VALUES(1,'WANG')
INSERT INTO StudentDetails
VALUES(2,'JOHNSON')
GO

CREATE TABLE StudentTotalMarks
(
Id INT IDENTITY PRIMARY KEY,
StudentID INTEGER REFERENCES StudentDetails,
StudentMarks INTEGER
)
GO
INSERT INTO StudentTotalMarks
VALUES(1,230)
INSERT INTO StudentTotalMarks
VALUES(2,255)
GO

Here is my stored procedure:

CREATE PROCEDURE MergeTest
    @StudentId int,
    @Mark int
AS

WITH Params
AS
(
    SELECT @StudentId as StudentId,
        @Mark as Mark
)
    MERGE StudentTotalMarks AS stm
    USING Params p
    ON stm.StudentID = p.StudentId
    WHEN MATCHED AND stm.StudentMarks > 250 THEN DELETE
    WHEN MATCHED THEN UPDATE SET stm.StudentMarks = p.Mark
    WHEN NOT MATCHED THEN
        INSERT(StudentID,StudentMarks)
        VALUES(p.StudentId, p.Mark);
GO

Here is how I'm observing the locking:

begin tran
EXEC MergeTest 1, 1

And then in another session:

EXEC MergeTest 2, 2

The second session waits for the first to complete before proceeding.

You need to give the query processor a more efficient access path to locate StudentTotalMarks records.  As written, the query requires a full scan of the table with a residual predicate [StudentID] = [@StudentId] applied to each row:

The engine takes U (update) locks when reading as a basic defence against a common cause of conversion deadlocks.  This behaviour means the second execution blocks when trying to obtain a U lock on the row already locked with an X (exclusive) lock by the first execution.

The following index provides a better access path, avoiding taking unnecessary U locks:

CREATE UNIQUE INDEX uq1
ON dbo.StudentTotalMarks (StudentID)
INCLUDE (StudentMarks);

The query plan now includes a seek operation on StudentID = [@StudentId], so U locks are only requested on target rows:

The index is not required to be UNIQUE to solve the issue at hand (though the INCLUDE is required to make it a covering index for this query).

Making StudentID the PRIMARY KEY of the StudentTotalMarks table would also solve the access path problem (and the apparently redundant Id column could be removed).  You should always enforce alternate keys with a UNIQUE or PRIMARY KEY constraint (and avoid adding meaningless surrogate keys without good reason).


Migrating SSRS 2008 to 2016 - ReportSync cannot see Data Sources I am trying to migrate our SSRS 2008R2 server to a fresh install of SSRS 2016.

I am using ReportSync and have managed to get connect to both servers OK. I created a test folder with a test report on the 2008 server and have migrated that fine to the 2016 server.

The problem is arising when I am trying to migrate the Data Sources folder.

As you can see from the screen shot, there is not plus symbol next to this folder, suggesting it is empty, yet when I browse the folder using the Reports Manager in Internet Explorer, I can see the Data Sources there. Furthermore, we have a Data Source in the Datasets folder (don't ask!) and when I expand that folder out in ReportSync, I cannot see this Data Source.

The credentials I have used in the ReportSync tool are the same as I am browsing the webpage with and this is a domain account. The account is in the BUILTIN\Administrators group which has "Content Manager" role on the Data Sources folder

Thanks to McNets for the heads up on the map DataSources option. To solve the problem I had to:

Load up both servers as per the image in the question
Select Tools > Map DataSources
I was getting a runtime error at this point this was stopped by creating a datasource on the target server and clicking the "Load" buttons again in ReportSync
The resulting window shows all the source server datasources with a dropdown list next to each allowing selection of the target server data sources
Recreated all source server data sources on target server (including all the details - connection strings, authentication etc...
Using the map datasources option, assign each source server data source to the relevant target server data source
Click set
Click sync at the bottom of the main window

The test report I created now opens


Why mysql-noinstall5.6.x.zip includes ibdata file? English is not my main language, please feel free to correct me or ask for clarification or a different explanation.
I've been messing around with MySQL for Windows and trying to understand how it works. Right now I'm working with MySQL 5.5 and 5.6 using the no-install.zip version and following the steps explained in the manual to get them both to work.
Any versión(¿subversion?) of MySQL 5.5.x (i.e., 5.5.12, 5.5.62) includes the data folder and only three folders inside it (mysql, performance_schema and test). I decompressed the zip, generated a my.ini file and ran the server. It created the ibdata and log-files and everything worked fine.
As for MySQL 5.6.x, it also includes the data folder with the same three folders as MySQL 5.5 but it already has the ibdata and log files (I downloaded different subversions just to make sure, and the files are present in all of them). So I followed the same process as for MySQL 5.5: I decompressed the zip, generated a my.ini file and ran the server, everything worked as expected.
Just for the knowledge's sake I deleted everything to make a clean start. Decompressed again the MySQL 5.6 zip file but this time I deleted the default ibdata and log-files and ran the server and it worked... aparently. It created the ibdata and log-files. But it lost some information from the mysql database. For example, I can no longer check the innodb_index_stats table using a select nor a describe query.
Now, my questions:

Why does the MySQL 5.6 zip file includes default ibdata and log-files?
What does those default files contain?
I'm fully aware that deleting the ibdata file erases the information and it's the reason the server is missing some data. But, why when they are created from scratch they miss this information? Isn't the server supposed to create them with full default information just as MySQL 5.5 does?

I already tried to answer those question but I couldn't find anything useful. I slightly read the first MySQL 5.6 release notes and randomly others between the first and last but didn't find any explanation on why it does include default files.
I also searched in Google about ibdata file but the answers are more in the sort of how to recover/backup/dump information from damaged ibdata or how to shrink it.
So, why? :)

To give you an more extensive answer, inno_db stores these data
data dictionary aka metadata of InnoDB tables
change buffer
doublewrite buffer
undo logs

so there is a lot of information and it can get really big.
so mysql 5.6 stores there data, that it needs when it is restarted, and 5.5 doesn't.


User Defined Function Return value Hello all I have a function:

ALTER FUNCTION [dbo].[getContentURL]
(
    @VID    int,
    @accountID int
)
RETURNS VARCHAR(500)
AS
BEGIN

DECLARE @cTable varchar(50) = '[CONTENT_CONNECT].[dbo].CONTENT_' + CONVERT(varchar(5),@accountID)
    DECLARE @vTable varchar(50) = '[CONTENT_CONNECT].[dbo].VERSION_' + CONVERT(varchar(5),@accountID)
DECLARE @sql VARCHAR(max) =  ''
DECLARE @RETURN VARCHAR(500)
DECLARE @ParmDefinition VARCHAR(500)
 SET @sql = '
    DECLARE @CompanyID int
    DECLARE @RETURN VARCHAR(500)
    DECLARE @ProjectNumber int
    DECLARE @ContentID int
    DECLARE @VersionNumber int
    DECLARE @FileName varchar(400)
  select @VersionNumber= V.Number,@ContentID= V.ContentID,@ProjectNumber=P.Number,@FileName=C.Name,@CompanyID=P.CompanyID
    from ' + @vTable + ' V
    inner join' + @cTable + ' C on V.ContentID=C.ID
    inner join Project P on C.ProjectID=P.ID where V.VID='+convert(varchar,@VID)+'

 select ''/''+Convert(varchar,@CompanyID)+''/''+Convert(varchar,@ProjectNumber)+''/''+Convert(varchar,@ContentID)+''/''+Convert(varchar,@VersionNumber)+''/''+@FileName'

 SET @ParmDefinition = N'@RETURN VARCHAR(500) OUTPUT'
    exec sp_executesql @sql, @ParmDefinition,  @RETURN OUTPUT
  set @RETURN=(select @RETURN)
 RETURN @RETURN
END

but when I execute this function

select  dbo.getContentURL(177,1)

this gives error:

The name 'select --------------------' is not a valid identifier.

how to return this executed value? can any one  help me please?

I can't say that I understand what it is that You are trying to do...
The thing is, just like You have already been told, that You can't use Dynamic SQL in a Function.

Using a Stored Procedure will solve the problem You encountered:
(I did not try to find another logic to retrieving the same data, which is possible, I just converted Your Function to a Stored Procedure)

CREATE PROC [dbo].[usp_getContentURL]
(@VID INT, @accountID INT)
AS
DECLARE @cTable varchar(50) = '[CONTENT_CONNECT].[dbo].CONTENT_' + CONVERT(varchar(5),@accountID)
DECLARE @vTable varchar(50) = '[CONTENT_CONNECT].[dbo].VERSION_' + CONVERT(varchar(5),@accountID)
DECLARE @sql VARCHAR(max) =  ''
SET @sql = '
DECLARE @CompanyID int
DECLARE @ProjectNumber int
DECLARE @ContentID int
DECLARE @VersionNumber int
DECLARE @FileName varchar(400)
select @VersionNumber=V.Number, @ContentID=V.ContentID, @ProjectNumber=P.Number, @FileName=C.Name, @CompanyID=P.CompanyID
from ' + @vTable + ' V
inner join' + @cTable + ' C on V.ContentID=C.ID
inner join Project P on C.ProjectID=P.ID
where V.VID='+convert(varchar,@VID)+'
select ''/''+Convert(varchar,@CompanyID)
+''/''+Convert(varchar,@ProjectNumber)
+''/''+Convert(varchar,@ContentID)
+''/''+Convert(varchar,@VersionNumber)
+''/''+@FileName'

EXEC (@sql)
GO

If this does not meet Your needs please post what exactly You are trying to do.
Good Luck,
Roi