Is there a FOR XML variant that can add attributes with arbritrary values? I'm trying to create a query similar to this

SELECT
'foo' AS Detail,
'bar' AS Detail,
'baz' AS Detail
FOR XML PATH ('Header')

To produce something like this-

<Header>
  <Detail type="A">foo</Detail>
  <Detail type="B">bar</Detail>
  <Detail type="C">baz</Detail>
</Header>

and it seems like there's no way to create attributes without assigning column values into them.

The query you have

SELECT
'foo' AS Detail,
'bar' AS Detail,
'baz' AS Detail
FOR XML PATH ('Header')

concatenates the values to a single Detail element.

<Header>
  <Detail>foobarbaz</Detail>
</Header>

To have multiple Detail elements you need to separate the columns with a null.

SELECT
'foo' AS Detail,
 null,
'bar' AS Detail,
 null,
'baz' AS Detail
FOR XML PATH ('Header')

<Header>
  <Detail>foo</Detail>
  <Detail>bar</Detail>
  <Detail>baz</Detail>
</Header>

And then you use the @ syntax suggested in the answer by Rob Farley to get the result you are looking for.

SELECT
'A' AS 'Detail/@type',
'foo' AS Detail,
 null,
'B' AS 'Detail/@type',
'bar' AS Detail,
 null,
'C' AS 'Detail/@type',
'baz' AS Detail
FOR XML PATH ('Header')

<Header>
  <Detail type="A">foo</Detail>
  <Detail type="B">bar</Detail>
  <Detail type="C">baz</Detail>
</Header>


How to determine the time out setting of mirroring? I know that the default TIMEOUT of database mirroring is 10 seconds if you do not change it. And I can use

ALTER DATABASE [DatabaseName] SET PARTNER TIMEOUT 30

to change it to avoid 'accident alerts'. But I want to know, How long time is good for a production database? The 30s, 40s, 60s, or even 120s? Which condition is the most consideration?
Thanks in advance.

The best practice is to be 10 seconds or over for timeout because it could trigger a false failure otherwise, the rest depends on your environment and your requirements for disaster recovery and high availability. If you set the number too high, it will take longer for the failover to occur when you do have an issue. The business needs should drive the results, the results should not dictate business needs.

Microsoft notes on the minimum.

SQL Sailor: Best practices and time outs for mirroring.

MSSQLTips: Greg Robidoux: DB Mirroring

SQL Central Discussion: Time outs for mirroring

To address what would be a good failover time, as I mentioned, it should be driven by your business requirements. Here are a few guides to developing those requirements:

Peak10: Carilyn Booker: DR and HA

Redgate: Joshua Feierman: DR and HA

Brent Ozar: DR and HA


Does Oracle optimize database while you use? I have a 10gb database, that the queries were running very fast on Oracle XE.

I exported this db with expdp and imported on a new server Oracle 12c (hardware better than original, much more RAM and CPU) with impdp and now the queries are being slow.

I think Oracle created some internal optimisation based on queries I frequently do, and now that the Database is imported, he lost all this optimisations. This is just my theory.

Anyone knows if Oracle optimize database while you use? If yes, how do I accelerate this process on a recently imported schema.

Edit: I monitored v$sql_monitor and there was just one problem I fixed with a new index. But queries still are slow.

I would guess that you have indexes that are unusable. Check and see if you have unusable indexes. If you do, then you need to rebuild them.

SELECT owner, status, COUNT(*) FROM dba_indexes GROUP BY owner, status ORDER BY 1,2;


Upgraded from SQL Server Express to standard but CPU usage is maximum 50% on dual core I have upgraded SQL Server Express 2008 r2 to SQL Standard Edition on amazon EC2 with a Intel Xeon E5-2670 v2 (Ivy Bridge) Processors with 2 vCPU.

I have verified the edition by running this:

SELECT  SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

and getting

10.50.2500.0    SP1 Standard Edition (64-bit)

Also in the Windows Logs -> Application I'm seeing this MSSQLSERVER log:

Detected 2 CPUs. This is an informational message; no user action is required.

I am running the below SQL Query:

SELECT SUM(CONVERT(BIGINT, o1.object_id) + CONVERT(BIGINT, o2.object_id) + CONVERT(BIGINT, o3.object_id) + CONVERT(BIGINT, o4.object_id))
FROM sys.objects o1
CROSS JOIN sys.objects o2
CROSS JOIN sys.objects o3
CROSS JOIN sys.objects o4

and seeing this on Windows Task Manager -> Performance:

and seeing this on windows Task Manager -> Applications:

I'm thinking that the SQL Server Express CPU limitation of 1 CPU is not removed.

Does anybody knows why? Does it have to be something with the query?
And how to fix it?

If you want to see pain points of a query capture the actual execution plan via SSMS when you run it. Viewing this within SQLSentry Plan Explorer (free!!) is a bit more easy on the eyes than what SSMS will show. There are some good blog post and articles scattered around the Internet on how to read an execution plan if you need it.

Capture Actual Execution Plan:


SQL Server Error "Database 'mssqlsystemresource' does not exist." On Linux, whenever I run use mssqlsystemresource to get access to Resource Database, I get an error. How do I resolve this.

1> use mssqlsystemresource
2> go
Msg 911, Level 16, State 1, Server x230, Line 1
Database 'mssqlsystemresource' does not exist. Make sure that the name is entered correctly.

When I try to log in with sqlcmd with DAC (admin:) targeting the database with -d

$ sqlcmd -I -S admin:localhost -U sa -d mssqlsystemresource -P Password0
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Cannot open database "mssqlsystemresource" requested by the login. The login failed..
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login failed for user 'sa'..

When I try to log in with mssql-cli's -A, I get

$ mssql-cli -A -d mssqlsystemresource
Username (press enter for sa):
Password:

Connection did not succeed.
Error message: Cannot open database "mssqlsystemresource" requested by the login. The login failed.
Login failed for user 'sa'.
Unable to connect. Please try again

The mssqlsystemresource database is only available in single-user mode. On Linux, that means

sudo systemctl stop mssql-server
sudo -u mssql /opt/mssql/bin/sqlservr -m


MariaDB Galera Cluster - Cert Deps Distance issue I have mariaDB Galera 10.2 Cluster running with 3 nodes; in google cloud VMs with 8 cpu ; 15Gigs Ram / node; My slave_thread setting = 24 ; currently there is no much write happening and local_receive_q_avg= 0.019481 but my Cert_deps_distance= 54 in two nodes and 0 in one node ? What does that mean ?

wsrep-cert-deps-distance

Most likely the 0 on one node was the only node being written to. So there are limited galera status changes there as it does not apply in the same was as the other nodes do.


SQL Server - Slow Database for no apparent reason I have a DB Server running SQL Server 2008 R2 SP2 on 24 physical Cores and 32GB of RAM. The database files are on a SSD Raid1 and the TempDB on a SAS 15k (150GB DB)

I assume that the server has more than enough resources and the queries are running slow.

On my test environment the queries run quick on a low resources server, and in production they are getting stuck, lasting for 100.. 200 seconds for apparent no reason.

My indexes are not fragmented, my CPU activity do not pass 25%, my SSD activity are 5% max, are there any ideas to help me solve this problem?

I don't know SQLServer specifics, but usually this sort of thing is due to table statistics being different in the two databases. Look at the query plans to see if they are different. Run the SQLServer version of "analyze table" or "analyze schema" commands.
If these things don't help, check how the databases are set up. Is it possible that the data is identical, but server configurations are different, and, for example, there is a much lower threshold on available memory for the production version.
As you said Indexes are not fragmented, Kindly check for the one with values above 30 % as FRAG value for pages greater than 1000.

You can proceed point 3 with Updating stats by running command: EXEC sp_updatestats; on database with slowness issue.


SQL Server Full-Text Index rebuild causing latency on other indexes I am rebuilding a full-text index on a table with a large number of rows.  The rebuild seems to be using all of the resources of the FT GATHERER threads, causing all of the other full-text indexes on the instance to report large latency.  Is there some way to throttle the resources that the rebuild is using so that the other indexes can keep up?

Using swasheck's comment as a potential answer, you can define a job to run the fulltext rebuild whenever the CPU becomes idle.

I also included the location of the crawl logs that SQL Server maintains everytime a catalog is populated.

Idle CPU

Defining when a CPU idle condition occurs is done in the properites of the SQL Server Agent.

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

Using perfmon to identify an inactive timeframe

Another option would be to use Perfmon's Data Collector.  You can use the SQL Server General Statistics Login counter to identify a timeframe when the server is less active. You can then define your Full Text Rebuild to run during that timeframe.

I didn't notice any particular way to limit the population or rebuild to one cpu with MAXDOP, like you can with rebuilding indexes.

ALTER FULLTEXT INDEX ON database_name.schema_name.table_name START FULL POPULATION;
http://msdn.microsoft.com/en-us/library/ms188359.aspx

ALTER FULLTEXT CATALOG catalog_name REBUILD;
http://msdn.microsoft.com/en-us/library/ms176095.aspx

Checking the FT Logs

There are logs that monitor the population of full text catalogs. These can be used to identify problem areas and also give you an idea of how long each catalog is taking to populate.

  %ProgramFiles%\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\LOG

  2012-07-13 04:00:04.62 spid105s    Informational: Full-text Full
  population initialized for table or indexed view
  '[database_name].[schema_name].[table_name]' (table or indexed view ID
  '66099276', database ID '13'). Population sub-tasks: 4.

  2012-07-13 04:04:14.59 spid131s    Informational: Full-text Full
  population completed for table or indexed view
  '[database_name].[schema_name].[table_name]' (table or indexed view ID
  '1621580815', database ID '13'). Number of documents processed: 101.
  Number of documents failed: 0. Number of documents that will be
  retried: 0.


Manage select results in sql We need to manipulate a set of records returned from a select query. This can be done in :

Either by saving the whole record to a table variable and then do the required process.
We can do like this

Select * from
   (select * from table where conditions) AS results
   where results.RowNum between ....

So what is the best method in this case.. using a table variable or the 2nd method.
Is there any performance issues in using the second method compared to first?

"it depends"

Using a table variable or temp table with requires overhead of creating populating this object

However, if the you require multiple processing steps then this is small compared to querying the same data over and over, especially as the query gets more complex.

Also, for multiple steps, using a table variable or temp table means working on the same set of data for all steps. Typically the underlying data usually changes as a result of other processes.

Finally, have you tried both techniques and benchmarked for your real situation? I use both approaches depending on all of the above factors. Sometimes it doesn't matter of course...


What is the name of the process for creating a new DB from an exported snapshot? I need to set up an automated process that:

creates a snapshot of an AWS RDS (MySQL) DB
uses that snapshot to create a new RDS instance that will be used as an "analytics playground"

I'm looking for the proper name to call the process described in step 2 above. So I ask: what would the DBA community call the process of using a DB snapshot (export) to create a brand new database? Materializing? Restoring? Backing up? Something else?

If you are just looking for nomenclature to name the process, I would call it

DB Sandbox Provisioning
DB Self-Service Provisioning
DB Self-Service

If you need an actual process supplied by Amazon, you could look into

AWS Control Tower
AWS Service Catalog

If you are planning to script this yourself, there is always the AWS CLI.

create-db-snapshot
restore-db-instance-from-db-snapshot

Check out the Amazon Docs for further info.


Restore database from a save I use pgadmin 4 and PostgreSQL

I try to restore a database from a save.

I have put the directory of my file save C:\Users\H2O\Desktop\temp_DB\ as a binary path in file preferences.

When I try to restore my db from my tar file (database -> restore) I have got this error.

  'C:\Users\H2O\Desktop\temp_DB\pg_restore.exe' n'est pas reconnu en tant que commande interne (pg_restore is not recognize as an internal command)

Don't know where it comes from.

I use pg admin 4 and Postgresql

'"C:/Users/Public/Downloads/tempDB/ \pg_restore.exe"' n'est pas reconnu en tant que commande interne

The Binary Path in Preferences is for the Postgres binaries, not for the backup file. Revert the Binary Path to the default (probably C:\Program Files\PostgreSQL\$version\bin where you replace $version as appropriate for your installation).

Enter the path to the backup file in the Tools > Restore dialogue and not in Preferences.


How can I bring a database offline safely? Recently I took the database offline with the option Drop all active connections checked.
After that I couldn't bring it back online due to error Could not redo log record for transaction id on page database. I was able to recover the database with CHECKDB but I want to avoid such a scenario in future
As far as I understand an uncommitted transaction was interrupted which corrupted databases's integrity.
I wonder how can I disable new upcoming connections while letting the active transactions to finish?

Recently I took the database offline with the option Drop all active connections checked.

When you take a database OFFLINE, SQL Server is no longer protecting the database files from modification by other programs.  SQL Server tries to set file ACLs to prevent other users/programs from modifying the files, but having the database ONLINE provides an important additional protection.
Whenever possible perform actions in a way that doesn't take databases OFFLINE or stop the SQL Server service.  EG take the database to SINGLE USER mode instead of OFFLINE, and use Backup/Restore instead of moving database and databse files.
And if you do take the database OFFLINE, ensure that you have a backup first.


LOCK error after creating a clustered index I just created a clustered index on a 1MM row table (page and row locking turned on). I now try to run queries and I get this error:

  "The instance of the SQL Server Database Engine cannot obtain a LOCK
  resource at this time. Rerun your statement when there are fewer
  active users. Ask the database administrator to check the lock and
  memory configuration for this instance, or to check for long-running
  transactions."

I will need to adjust the page/ row locking settings later, but in the meantime I need to drop the index because I have users querying the table. I tried to drop it, but I received the same error. Any recommendations to drop the index for good? Thank you.

EDIT 1:
Based on Remus answer below, I ran the following script to enable dynamic allocation of memory for locks.

EXEC sp_CONFIGURE 'show advanced options', 1 RECONFIGURE
EXEC sp_configure 'locks', 0 RECONFIGURE

Then, I restarted the server.

Follow the How to troubleshoot SQL Server lock related problems and errors steps.

make sure locks option is 0, see Keep the Locks Configuration Option Default Value
make sure lock escalation disabling trace flags are not in place, see Lock Escalation
check your memory consumption, run DBCC MEMORYSTATUS, see How to use the DBCC MEMORYSTATUS command to monitor memory usage
make sure you do not have open transactions with many locks, see sys.dm_tran_locks and sys.dm_tran_database_transactions

There are millions of tables with clustered indexes out there that do not run into lock exhaustion issues. There is something more at play here.


Delete millions of rows from a SQL table I have to delete 16+ millions records from a 221+ million row table and it is going extremely slowly.

I appreciate if you share suggestions to make code below faster:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

DECLARE @BATCHSIZE INT,
        @ITERATION INT,
        @TOTALROWS INT,
        @MSG VARCHAR(500);
SET DEADLOCK_PRIORITY LOW;
SET @BATCHSIZE = 4500;
SET @ITERATION = 0;
SET @TOTALROWS = 0;

BEGIN TRY
    BEGIN TRANSACTION;

    WHILE @BATCHSIZE > 0
        BEGIN
            DELETE TOP (@BATCHSIZE) FROM MySourceTable
            OUTPUT DELETED.*
            INTO MyBackupTable
            WHERE NOT EXISTS (
                                 SELECT NULL AS Empty
                                 FROM   dbo.vendor AS v
                                 WHERE  VendorId = v.Id
                             );

            SET @BATCHSIZE = @@ROWCOUNT;
            SET @ITERATION = @ITERATION + 1;
            SET @TOTALROWS = @TOTALROWS + @BATCHSIZE;
            SET @MSG = CAST(GETDATE() AS VARCHAR) + ' Iteration: ' + CAST(@ITERATION AS VARCHAR) + ' Total deletes:' + CAST(@TOTALROWS AS VARCHAR) + ' Next Batch size:' + CAST(@BATCHSIZE AS VARCHAR);
            PRINT @MSG;
            COMMIT TRANSACTION;
            CHECKPOINT;
        END;
END TRY
BEGIN CATCH
    IF @@ERROR <> 0
       AND @@TRANCOUNT > 0
        BEGIN
            PRINT 'There is an error occured.  The database update failed.';
            ROLLBACK TRANSACTION;
        END;
END CATCH;
GO

Execution Plan (limited for 2 iterations)

VendorId is PK and non-clustered, where clustered index is not in use by this script. There are 5 other non-unique, non-clustered indexes.

Task is "removing vendors which do not exist in another table" and back them up into another table.  I have 3 tables, vendors, SpecialVendors, SpecialVendorBackups.  Trying to remove SpecialVendors which do not exist in Vendors table, and to have a backup of deleted records in case what I'm doing is wrong and I have to put them back in a week or two.

The execution plan shows that it is reading rows from a nonclustered index in some order then performing seeks for each outer row read to evaluate the NOT EXISTS

You are deleting 7.2% of the table. 16,000,000 rows in 3,556 batches of 4,500

Assuming that the rows that qualify are evently distributed throughout the index then this means it will delete approx 1 row every 13.8 rows.

So iteration 1 will read 62,156 rows and perform that many index seeks before it finds 4,500 to delete.

iteration 2 will read 57,656 (62,156 - 4,500) rows that definitely won't qualify ignoring any concurrent updates (as they have already been processed)
and then another 62,156 rows to get 4,500 to delete.

iteration 3 will read (2 * 57,656) + 62,156 rows and so on until finally iteration 3,556 will read (3,555 * 57,656) + 62,156 rows and perform that many seeks.

So the number of index seeks performed across all batches is SUM(1, 2, ..., 3554, 3555) * 57,656 + (3556 * 62156)

Which is ((3555 * 3556 / 2) * 57656) + (3556 * 62156) - or 364,652,494,976

I would suggest that you materialise the rows to delete into a temp table first

INSERT INTO #MyTempTable
SELECT MySourceTable.PK,
       1 + ( ROW_NUMBER() OVER (ORDER BY MySourceTable.PK) / 4500 ) AS BatchNumber
FROM   MySourceTable
WHERE  NOT EXISTS (SELECT *
                   FROM   dbo.vendor AS v
                   WHERE  VendorId = v.Id)

And change the DELETE to delete WHERE PK IN (SELECT PK FROM #MyTempTable WHERE BatchNumber = @BatchNumber) You may still need to include a NOT EXISTS in the DELETE query itself to cater for updates since the temp table was populated but this should be much more efficient as it will only need to perform 4,500 seeks per batch.


SQL Server 2008 Failover cluster We are currently running a working SQL Server 2005 Failover cluster, with 2 nodes. We are now in the process of replacing the servers in that cluster with 2 new servers running SQL Server 2008, my question, should I add the new servers to the current cluster and then retire the old servers or create a new cluster and then copy the databases to the new servers?

It would be cleaner and potentially more reliable to build your new SQL 2008 cluster first on new hardware. Do a test migration by restoring your databases from backups into your new cluster.


SELECT query in a SP in AlwaysOn Availability Group Read Only Routing There is a stored procedure which has select statement as well as insert and other statements. What will be the behaviour of the stored procedure in a Read Only Routing configured in AlwaysOn Availability Group?
Will the select statement go to the secondary server for reading and rest will be done on the primary server or since it is an inline statement in a stored procedure, that's why everything will take place on the primary itself.
I could not find any documentation regarding this, how will the select statement behave?

Read-only routing is per connection, not per batch or statement. Read-only intent is specified via the application connection string and the connection will be directed to a read-only replica (if possible) when the connection is established.


When to use TINYINT over INT? In general, I always use Ints.  I know that in theory this is not the best practice, though, since you should use the smallest data type that will be guaranteed to store the data.

For example, it's better to use tinyint when you know that the only data you will store is a 1, 0 or null (with a very small chance of expanding that to a 2 or 3 later).

However, the only reason I know for doing this is for storage purposes--using 1 byte on a row instead of 4 bytes.

What are the impacts of using tinyint (or smallint or even bigint) over just int, other than saving space on your hard drive?

Disk space is cheap... that's not the point!

Stop thinking in terms of storage space, think instead about buffer pool and storage bandwidth. At the extreme end, CPU cache and memory bus bandwidth. The linked article is part of the series highlighting issues with poor clustered key selection (INT vs GUID vs Sequential GUID) but it highlights the difference bytes can make.

The overriding message is design matters. The difference won't show up in an individual database on an appropriately spec'd server until you hit VLDB territory but if you can save a few bytes, why not do so.

I'm reminded of the environment described in an earlier question. 400+ databases, ranging in size from 50mb-50GB, per SQL instance. Scrubbing a few bytes per record, per table, per database across that environment could make a significant difference.


encryption of SSRS subscription emails and attachments Is it possible to set up an SSRS e-mail subscription, with an attachment, that is encrypted?

POST ANSWER-ACCEPT UPDATE: Does the SMTPUseSSL switch in the RSReportServer.config file help me at all?

ONE MORE UPDATE: No, the SMTPUseSSL switch does not get me there.  I have validated the answer below here:

The default e-mail delivery extension does not provide support for digitally signing or encrypting outgoing mail messages.

Not out of the box as far as I am aware, and if you need to distribute reports to external parties this is a bad idea as they will all have to install third party encryption software (incurring licensing and support costs).  It will also put your help desk people into the job of babysitting all the users as they forget their keys.

You're much better off providing a secure portal (this can be done with Sharepoint if you have this) and emailing out links to the reports.


Stored procedure suddenly very slow, updating statistics not helping Like the title says, I have a stored procedure that suddenly started running very slowly. Previously it took about 5 seconds to execute, now it is taking seven minutes or more. This seems to have started when I altered the procedure, but the change I made should not have impacted the performance, as all I did was change the alias on two columns being returned. I'm not sure if altering the procedure actually caused this, however, because a similar procedure that also used to be very fast is having the same issues.

I tried updating statistics on the relevant tables and it did not help. I had this same problem last week and was able to fix it by doing that, but now it's a no go. I also tried using with recompile to no avail and deleted the procedure then re-added it.

This is the line that is taking much longer than before:

select PK_ShipDataSubsystemConfigID
,timestamp
,cast((
        select top 1 Longitude
        from [RemoteServer].[dbo].[Ships].LocationAttributes la
        inner join [RemoteServer].[dbo].[Ships].Locations l on l.ShipLocationId = la.ShipLocationId
        where l.SampleDateTime <= timestamp
            and l.SampleDateTime <= @ToDT
            and ShipId = @ShipId
        order by SampleDateTime desc
        ) as varchar(20)) as Longitude
,'Vessel Position Lng'
from FuelCalc
inner join ShipDataSubsystemConfig s on s.PK_ShipDataSubsystemConfigID =       FuelCalc.FK_ShipDataSubsystemConfigId
where timestamp > @FromDT
and timestamp < @ToDT
and FuelCalc.FK_ShipId = @ShipId
order by timestamp desc

Running the inner select by itself now takes at least 2 seconds, so I can understand why the whole procedure is taking so long. I am wondering if it has something to do with it being a call to a linked server. I tried creating a view  joining the Locations and LocationAttributes tables to see if it made a difference, but it did not.

Any ideas? I have asked around with my coworkers and they are just saying I need to write a more efficient query (which may be true) but I really don't think that's causing my particular problem seeing as it was working fine until suddenly it wasn't. I looked at the execution plan but all it says is that the remote query is taking 100% of the time, but doesn't show the plan at more detail than that.

I would suggest that you create a temp table or use common table expression to get the list of all longitudes you require from your remote Server and use the new temp table or common table expression to inner join with your FuelCalc table.

Something like below.

WITH CTE as
(
select Longitude, MAX(SampleDateTime) as SampleDateTime, ShipLocationId, SHIPiD
        from [RemoteServer].[dbo].[Ships].LocationAttributes la
        inner join [RemoteServer].[dbo].[Ships].Locations l on l.ShipLocationId = la.ShipLocationId
        where l.SampleDateTime <= timestamp
            and l.SampleDateTime <= @ToDT
            and ShipId = @ShipId
        GROUP BY Longitude
)
select PK_ShipDataSubsystemConfigID
,timestamp
,cte.Longitude
,'Vessel Position Lng'
from FuelCalc
inner join ShipDataSubsystemConfig s on s.PK_ShipDataSubsystemConfigID =       FuelCalc.FK_ShipDataSubsystemConfigId
left outer join cte on cte.SHIPiD = FK_ShipId
where timestamp > @FromDT
and timestamp < @ToDT
and FuelCalc.FK_ShipId = @ShipId
order by timestamp desc


Where in the database file is an index rebuild started? Alternative to SHRINKFILE? When rebuilding an index in SQL server, where in the datafile does it put that data?

Does it start at the beginning of free space and fill in the gaps from there?

Context:  I have some large .ndf files I'm trying to shrink (not important why); but instead of using a SHRINKFILE command, I'm wondering if I can rebuild the indexes and then truncate the file from 80GB down to 25GB.

Edit:  There are many reasons to want to reduce the file space or reorganize it logically.  These may relate to fragmentation, how that fragmentation is translated to IO requests, moving large files across a network, swapping out storage space, how data is read sequentially, etc.  Excessive space usage could even prevent other files from being written to your disk that need the space or dbs that need room to grow.  You might have done some testing and now need to clean up excessive data and the space it required.  Imagine you just switched to a newer database server and could take a 400GB database and compress it down to 100GB with newly available db management techniques, compression, etc,  That's got value and I'm sure there are many other scenarios where this matters.  Just be careful to judge the reasons behind questions please.

I don't believe those details are documented anywhere, and even if you were to figure it out (by asking Paul Randal, or perhaps by mapping the exact pages before and after an index rebuild), you may not be able to rely on those going forward. You also might end up with some random single pages at the end of the file anyway, and still need to do a shrink.

The best idea, in my opinion, is to do an index rebuild into a different file group:

  Create a new filegroup
  Move all affected tables and indexes into the new filegroup using the CREATE INDEX … WITH (DROP_EXISTING = ON) ON syntax, to move the tables and remove fragmentation from them at the same time
  Drop the old filegroup that you were going to shrink anyway (or shrink it way down if its the primary filegroup)

If that's not an option, then you can instead do a shrink-then-reindex, or maybe a reindex-shrink-reindex:

Initial reindex: this releases free space within each page, and might be necessary if you've made major changes in the table structure like dropping large columns or changing column datatypes
ShrinkFile (never use ShrinkDatabase): Pick a reasonable target size, shrink each file with some remaining free space (I usually pick 15-20% free)
Final reindex: Resolves any fragmentation we created in step 2.

This multi-pass approach is a ton of IO, and might take a while to do. That's why the single-pass approach into a different file group is preferred, if you can pre-allocate the space.

Also, the final reindex may reclaim some of the space recovered in step 2. This is common where the majority of space is taken up by a single huge table or single huge index.


add a column to an existing table I'm wanting the best way to add a column to an existing table in my database.  I'm using SQL Server 2012. I don't want the data that is in the table to be affected, I just want to add a new column that has a default value of NULL.  The only ways I'm aware of for adding a new column is by using:

ALTER TABLE dbo.myTable ADD newColumn VARCHAR(20) NULL;

and the other way of adding a new column is by going into SQL Server Management Studio, bringing up the Design for the table I want to update, and adding the new column's information to the design.

There is no "BEST" way to add a column. There is only one way - using the T-SQL command. What do you think happens when you use SSMS table design? it's just a way to simplify tasks for those who don't know the syntax. When you click OK, SSMS generates the ALTER TABLE statement and executes it for you.


Microsoft Certification - 70-764 Administering a SQL Database Infrastructure Firstly, I apologise if this is not necessarily the best place to post such a question.

I am currently preparing for my 70-764 exam. I have purchased the following book:

70-764

I have a few questions regarding the book and its resources:

Previously, I have purchased other Microsoft Certification books and have normally got a PDF version, along with the printed book. Is this not the case anymore? Am I missing a disk or something?
The book has many URLS for supporting resources, however, these dont appear to be valid any longer. For example, early on within the book, there is a URL given for a list of useful links which accompany the book, I cannot find these online at all.
Practice tests also used to come with the book, however this does not appear to be the case any longer. Is this by design? Or am I just missing something here?

Many thanks in advance for any assistance.

Q1: Previously, I have purchased other Microsoft Certification books and have normally got a PDF version, along with the printed book. Is this not the case anymore? Am I missing a disk or something?

On the page you linked to, there were 3 options: book, ebook, and book + ebook. You would need to buy the book + ebook option if you wanted both.

Q2: The book has many URLS for supporting resources, however, these don't appear to be valid any longer. For example, early on within the book, there is a URL given for a list of useful links which accompany the book, I cannot find these online at all.

Yeah, typical problem with dead tree printed versions of the internet: the links are often out of date. You can try using the Wayback Machine at archive.org to see if they've been archived.

Q3: Practice tests also used to come with the book, however this does not appear to be the case any longer. Is this by design? Or am I just missing something here?

On the page you linked to, it says, "This title is also available for sale with a practice test." You would want to click that link and buy the other version with the practice test if that's important to you.


what is correct way of adding a user in admin with role userAdminAnyDatabase doing upgrade to 2.6 I am running a mongodb 2.4 with auth enabled  and want to upgrade to 2.6

Instructions in upgrade 2.6 says:

  Before beginning the upgrade process for a deployment that uses authentication and authorization:

  Ensure that at least one user exists in the admin database with the role userAdminAnyDatabase.
  then upgrade binaries
  then run athorization schema format if needed

So I have to add user with the role userAdminAnyDatabase in 2.4  before copies binaries of 2.6 ?

in current 2.4 shell added user with role userAdminAnyDatabase and then schema upgrade with userAdminAnyDatabase role after coping binaries of 2.6

db.addUser( { user: "a", pwd: "pass", roles: [ "userAdminAnyDatabase" ] } )

Is this the right way? Do i need to add read write access as well?

I think i might not get the error below if I go into admin database with role userAdminAnyDatabase

> show collections
2016-02-22T18:51:10.765-0500 error: {
  "$err" : "not authorized for query on oasis.system.namespaces",
  "code" : 13
} at src/mongo/shell/query.js:131

IMPORTANT
  The userAdminAnyDatabase user can grant itself and any other
  user full access to the entire MongoDB instance. The credentials to
  log in as this user should be carefully controlled.

  Users with the userAdmin and userAdminAnyDatabase privileges are not
  the same as the UNIX root superuser in that this role confers no
  additional access beyond user administration. These users cannot
  perform administrative operations or read or write data without first
  conferring themselves with additional permissions.

4 Steps for upgrading MongoDB v2.4 to v2.6

Ensure that at least one user exists in the admin database with the role userAdminAnyDatabase.
Upgrade client libraries,compatible with v2.6
Upgrade all MongoDB process to 2.6
Connect and authenticate to the mongod instance for a single deployment or a mongos for a sharded cluster as an admin database user with the role userAdminAnyDatabase.Use the authSchemaUpgrade command in the admin database to update the user data using the mongo shell.

See Also:

Upgrade MongoDB to 2.6
https://docs.mongodb.org/v2.6/release-notes/2.6-upgrade/

Create a User Administrator
https://docs.mongodb.org/v2.4/tutorial/add-user-administrator/


How to detect sudden bottleneck in mysql with 2000%CPU Our nginx-mysql server has been running smoothly for months (even years) . All of the sudden mysql consume around 700-2000% CPU , very little RAM. even after mysql restart, the usage goes up right off. Nginx CPU/RAM usage is minimal (since the traffic is just at 20% of peak). Turning off nginx solved it proving some queries were having problem.  The question is how to pinpoint the troublemaker one. any command or query to do so (from bash, nginx, log etc..)?

FYI: I did optimize and repair all the tables but did not work. The problem was solved when I truncate 2 "middle size" table of 2M rows. Those 2 tables used to behave normally with 20M+ rows, and they have good indexes...  I did purge/truncate the large tables from time to time.

Edit 1: mysql-slow.log did help a little but not right to the point

You should use SHOW FULL PROCESSLIST in MySQL console to display all running queries.


SSIS Dataflow Task issue, moving data from SQL Server 2008 to MySQL via ADO.Net Can you please help me in getting this problem solved..

I'm trying to build a SSIS package where I'm moving the data from SQL Server 2008 to MySQL.
However I'm getting a weird problem in dataflow task.

It is getting failed when the length of data in a column (varchar) exceeds the length 32chars.. I'm really confused with this message..

SSIS execution results doesn't show any proper message for the failure.

[ADO NET Destination [329]] Error: An exception has occurred during data insertion, the message returned from the provider is:

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component
"ADO NET Destination" (329) failed with error code 0xC020844B while processing input "ADO NET Destination Input" (332).
The identified component returned an error from the ProcessInput method. The
error is specific to the component,but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

Please help...

That's either a problem with MySql or with the driver you're using. All SSIS knows is that the driver says 'I can't do that'.

The place to start is by manually inserting as record of that kind into the destination db and looking for a more meaningful error.


csv import fails without throwing an error Ι am trying to import a csv file into an existing mysql 5.7 table.
Here is the command (the db is in docker with bind mount for persistence)
mysql -h 127.0.0.1 -uroot -prootpass --local_infile=1 jira_statistics -e "LOAD DATA LOCAL INFILE '/home/centos/bugs.csv' INTO TABLE bugs FIELDS TERMINATED BY ','"

However:
mysql> select * from bugs;
Empty set (0.05 sec)

The file cannot be loaded because there is a foreign key constraint.  I need to be able to catch errors like this (and any others that might occur) as an exit code since I will be scripting a large number of imports like this.
There is no error returned to the command line, and the docker logs of the MySQL container show no errors.
What is more, the exit code of the mysql command is 0!
Any ideas?

Encapsulate the LOAD in a Stored Procedure.  In there, capture the error and expose it to your script.

However, if LOAD DATA is not allowed in a Stored Proc, then you really need to write application code, not depend on on-line shell scripts.


Report of permissions in SQL server I want to write a report that gets all the permissions for every Login in our SQL server farm. For now I can get the logins but get stuck there trying to connect the logins to roles. Haven't even found a way to get the roles yet. Are there PowerShell functions available for this or do I need to write sql statements and parse the results with PowerShell? Alternatively, Am i making it too complicated and should get to the information from a different angle?

#Get all the logins for every server
#Filters out all "NT Service" and "NT Authority" accounts as well as MSPolicy Accounts
$SQLLogins = Get-SqlLogin -ServerInstance $DefaultServerList | Where {($_.name -notmatch "^NT Service\\.*") -and ($_.name -notmatch "^NT AUTHORITY\\.*") -and ($_.name -notmatch "##.*##")}

#For every one, determine the permissions
ForEach ($Login in $SQLLogins) {
    Write-Host $Login.name "    " $Login.Parent.Name
}

I am not 100% sure but dbatools.io might have one.


The connection is no longer usable because the server response for a previously executed statement was incorrectly formatted One of our application AX2013 is connecting to our DB's in an Availability group spanning three SQL2016 nodes.
End users are getting some error in the application that always point to the same error in the eventlog:
"[Microsoft][SQL Server Native Client 11.0]The connection is no longer usable because the server response for a previously executed statement was incorrectly formatted."
The ax-team is looking at the DBA-team to fix this issue but we already changed the timeouts and don't see any other tasks we could do to resolve this issue.
Does anyone have any ideas?
Kind Regards

Based on the details you've provided, it's likely this error is being caused by a network issue between the AX server and the SQL Server.  If you have a network team, it might be good to bring them in to investigate if there are packets being lost between the two servers.
Another possibility is that there is an issue with the driver AX is using to connect to SQL Server (SQL Server Native Client 11.0).  Some possible options there are to

reinstall the driver,
update to the most recent supported version of the driver,
try a different driver, like MSOLEDBSQL (if that's configurable in AX, I don't know anything about that)


Transaction Log usage by transaction Is there a way to find out how much of the transaction log a specific transaction uses?

I have a database with simple recovery model and a log file with max size 500 MB. During our tests we ran into a "log file is full" situation, which is pretty strange, because our largest table is just 200 MB in size, we use only implicit transactions, we have no concurrent users and we don't change large amounts of data, apart from the occasional bulk insert which adds another 80 MB or so.

We can probably find the culprit by refining our application tests, but is there a way to find the offending statement through some SQL Server monitoring means?

The log can be forced to grow if you have an old transaction that has not been committed or rolled back, regardless of the size (it could affect a single row). For the database in question, assuming you haven't resolved the problem yet, do this:

DBCC OPENTRAN();

This should show you the oldest active transaction (and there is a good chance this is the one that is preventing the log from wrapping). You can see the last thing they did (but not necessarily the thing that is causing the problem) by (documentation):

DBCC INPUTBUFFER(<spid>);

And if they have a query that is currently running:

SELECT *
  FROM sys.dm_exec_requests
  WHERE session_id = <spid>;

You can also see from sys.databases what the log holdup is (it could be something else entirely):

SELECT log_reuse_wait_desc
  FROM sys.databases
  WHERE name = N'<database name>';

There can also be reasons why an 80 MB transaction might require more than 200 MB of log space - see Remus' answer here:

Why does this 120 GB insert grow the log by over 780 GB?

And I recommend you read this page in its entirety (perhaps you are not using the right recovery model, and perhaps you should consider allowing the log more space to grow when it needs to, rather than shutting down all activity until you resolve the situation manually):

Why Does the Transaction Log Keep Growing or Run Out of Space?

Related:

Identify cause of unusual transaction log growth
Contents of transaction log file in more detail
SQL Server transaction log
Finding exact statement causing error 3930
SIMPLE or FULL recovery model for databases?
SQL Server log file continues to grow?
Changing a column's data type fills the transaction log
Log sizes unexpectedly large: log_reuse_wait_desc == "log_backup"


Why does SQL Server allow a float(53) value that falls outside the range specified by Microsoft? I have a particular table with a column data type of float(53), non null. According to the Microsoft Documentation, the smallest value above 0 that can be stored in this data type is is 2.23E-308. However, I am able to store a value much smaller, as small as 4.94065645841247E-324. See the following image of proof that SQL server can store and retrieve a value outside the range specified in the documentation:

When I run a query such as the following:

select 4.7E-309

SQL Server says: Warning: the floating point value '4.7E-309' is too small. It will be interpreted as 0.

The value inserted in the screenshot was inserted via the C++ ADO interface using record binding.

I have a few questions:

Why does SQL server handle values outside the range specified in the documentation?
Similarly, why does SQL Server issue a warning when an "out of range" value is in the query, when clearly the database can store values out of that range?

How are floating points stored on SQL database tables, and are there any SQL server settings that would strictly enforce the range on one SQL server as opposed to another?

I'm trying to troubleshoot an issue where an ADO record binding was (possibly) rejected due to the value being out of range, however all my test to reproduce the issue (on a different SQL server instance) show that SQL accepts values much beyond the limits specified in the documentation. Any information on how floating points are stored and handled in SQL (especially with relation to something like .NET or C++) would be much appreciated.

I have tested this with both SQL server 2005 and 2008 with the same results.

I suspect this is a validation error (ie. a bug). Wouldn't be the first, specially in 2005-2008 (there was a big data purity fixes push in 2012 I think). I suspect that a DBCC CHECKDB WITH DATA_PURITY will complain about the value.
SQL Server stores floats as IEEE 754, same way as .Net and C++ represents them. 64 bit can represent values way above and bellow the 2.23E-308, as you already found out about 4.94065645841247E-324 (ie. 0x0000000000000001 interpreted as a float). But such values fail another IEE754 requirement, namely reproducibility:

The standard requires operations to convert between basic formats and external character sequence formats. Conversions to and from a decimal character format are required for all formats. Conversion to an external character sequence must be such that conversion back using round to even will recover the original number. There is no requirement to preserve the payload of a NaN or signaling NaN, and conversion from the external character sequence may turn a signaling NaN into a quiet NaN.
The original binary value will be preserved by converting to decimal and back again using:

5 decimal digits for binary16
9 decimal digits for binary32
17 decimal digits for binary64
36 decimal digits for binary128

This requirement places the limit at documented numbers:

1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308

The fact that ADO binding allows basically any combination of bits to be passed as the value and the SQL Server accepts the binding buffer w/o validation results in the behavior you noticed. Technically, the data you inserted this way is a corruption. Again, I suspect that a) newer versions of the engine validate the buffer before write (in other words they catch the invalid value) and b) WITH DATA_PURITY check will report the problem (even on 2005/2008/R2 versions).


Only 'master' inside the system databases in Azure Managed instance During our POC we saw the system databases(model,msdb and tempdb) lying outside the system databases group.

Any specific reason behind it?

It was just because of the SSMS version. https://stackoverflow.com/questions/54695590/create-database-using-ssms-gui-fails-for-azure-sql-managed-instance

Used 17.9.1 and can see things lined up properly. Probably the previous version was treating as Azure database.


Creating crosstab() pivot table in PostgreSQL 9.0 I have a rather complicated issue for myself which I hope someone can help me with.

I want to use PostgreSQL to enable me to create pivot tables from a geometry-less table.

To keep it simple I will just show the table structure i want to use for the pivot and then use the methods given to create others, I hope :)

The fields I want to use are postcode_nospace_, and ageband.

ageband will be the columns across the top with postcode_nospace_ being the rows with a count of each postcode being the data.

There are 2132 records and of which some have more than one postcode, hence the count.

CREATE TABLE adult_social_care.activities_in_localities_asc
(
  ogc_fid integer NOT NULL,
  sort numeric(5,0),
  ageband character(12),
  postcode_nospace_ character(8),
  wkb_geometry geometry,
  CONSTRAINT activities_in_localities_asc_pkey PRIMARY KEY (ogc_fid)
);

UPDATE:

Here is the outcome I want to achieve as shown in an excel pivot table with the same data.

postcode 18_24  25_34   35_44   45_54   55_64   65_74   Total Count
----------------------------------------------------------------------------
BB115DE     1           2                   2      3       8
FY38LZ                              1       1              2

From looking around I have compiled the following SQL query. It groups by postcode and creates the field names required. However the fields are blank. I would ideally also want a total_count column at the end of the table.

SELECT * FROM crosstab(
    'SELECT postcode_nospace_, ageband, count(ageband) as total_count
     FROM adult_social_care.activities_in_localities_asc
     GROUP BY postcode_nospace_, ageband
     ORDER BY postcode_nospace_'

     ,$$VALUES ('18-24'::text), ('25-34'), ('35-44'), ('45-54'), ('55-64'), ('65-74'), ('75-84'), ('85-94'), ('95 AND OVER')$$)
AS ct("postcode" text, "18-24" numeric, "25-34" numeric,"35-44" numeric, "45-54" numeric, "55-64" numeric, "65-74" numeric, "75-84" numeric, "85-94" numeric, "95 AND OVER" numeric);

Your table definition says

...
ageband character(12),
...

which means the values in there look like '18-24       ' instead of '18-24'.  This way, the items from the VALUES list do not match the values in the table, therefore you get an empty table as result.

If you change the column type to a bit more senseful text (which trims the values, too, as Erwin points it out):

ALTER TABLE activities_in_localities_asc ALTER COLUMN ageband TYPE text;

you will get your desired results.


How to manage my own scripts Every administrators, developers have their own collection of scripts (some patterns). Time to time they are dismissed from one job and they are employed to other one. Is there any good, easy used, indexed tool to collect the common used sql scripts (structured folder/file system as a default)?

I started to play with RedGate's tool - Script Manager. You can have them together in a single place with some UI. It may worth a try :).


Short circuit count query I have a stored procedure that searches for rows given a long list of parameters. Some of the queries result in extremely slow count queries. Is it possible to stop counting after, say, one hundred rows so that I can simply display "100+" for the row count? It's not important that the users know exactly how many rows there are - especially when the result is in the thousands.

Is it possible to stop counting after, say, one hundred rows so that I can simply display "100+" for the row count?

Sure.  A query like this should do the trick:

with q as
(
  select top 100 *
  from …
  where …
)
select count(*)
from q


Order a nested table with path index according to list_order in SQLite I have a SQL table with this nested structure:
CREATE TABLE items (`id` INTEGER, `item_name` VARCHAR(6), `parent_id` INTEGER, `list_order` INTEGER);

My SQLite version does not support window function, currently my query at link SQLite query are returning as follow the result, but it's not ordered according to list_order, another detail is that the list_order column could come with all values equal to zero.
   id      name_item   parent_id  level  path_index list_order
---------------------------------------------------------------
   1        Pois           0          0     1           4
   2        Então          0          0     2           5
   3        Teste          0          0     3           3
   11       Teste 3        3          1     3.1         2
   12       Teste 2        3          1     3.2         0
   13       Teste 1        3          1     3.3         1
   4        Fundo          0          0     4           2
   7        Profundo       4          1     4.1         1
   8        Dhdhd          4          1     4.2         0
   9        Gagagaga       8          2     4.2.1       1
   10       Fsfsfsfs       8          2     4.2.2       0
   5        Profundo       0          0     5           1
   6        Gigante        0          0     6           6
   14       Teste          0          0     7           0

What I want the path_index column to follow list_order and level columns, like here:
   id      name_item   parent_id  level  path_index list_order
---------------------------------------------------------------
   14       Teste          0          0     1           0
   5        Profundo       0          0     2           1
   4        Fundo          0          0     3           2
   8        Dhdhd          4          1     3.1         0
   10       Fsfsfsfs       8          2     3.1.1       0
   9        Gagagaga       8          2     3.1.2       1
   7        Profundo       4          1     3.2         1
   3        Teste          0          0     4           3
   12       Teste 2        3          1     4.1         0
   13       Teste 1        3          1     4.2         1
   11       Teste 3        3          1     4.3         2
   1        Pois           0          0     5           4
   2        Então          0          0     6           5
   6        Gigante        0          0     7           6

How can I align the path_index sorted according to list_order and level columns?

Needed ordering may be performed by the next query:
WITH
cte AS ( SELECT *, CAST(list_order AS TEXT) as ord
         FROM items
         WHERE parent_id = 0
       UNION ALL
         SELECT items.*, cte.ord || '.' || items.list_order
         FROM items
         JOIN cte ON cte.id = items.parent_id )
SELECT *
FROM cte
ORDER BY ord

https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=a7957edeabb5a9a1c9dff2d2e92ffe0c
If list_order values above 9 are present then you must apply zero-padding expression to its value. For example
WITH
cte AS ( SELECT *, printf('%04d', list_order) as ord
         FROM items
         WHERE parent_id = 0
       UNION ALL
         SELECT items.*, cte.ord || printf('%04d', items.list_order)
         FROM items
         JOIN cte ON cte.id = items.parent_id )
SELECT *
FROM cte
ORDER BY ord

https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=52d6d43db054cb64754c37d3820c0b1e

do you have any trick to make the zero-padding dynamic? – alexandre9865

WITH
printf_format AS ( SELECT '%0' || LENGTH(MAX(list_order)) || 'd' AS format
                   FROM items ),
cte AS ( SELECT items.*, printf(printf_format.format, list_order) as ord
         FROM items
         CROSS JOIN printf_format
         WHERE parent_id = 0
       UNION ALL
         SELECT items.*, cte.ord || printf(printf_format.format, items.list_order)
         FROM items
         JOIN cte ON cte.id = items.parent_id
         CROSS JOIN printf_format )
SELECT *
FROM cte
ORDER BY ord

https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=728695f41db0913bcde76370f70c885a


Duplicate key error for composite primary key when running update query without having duplicate I have a table with the below schema

    CREATE TABLE `my_field_table` (
  `entity_type` varchar(128) NOT NULL DEFAULT '' ,
  `bundle` varchar(128) NOT NULL DEFAULT '',
  `deleted` tinyint(4) NOT NULL DEFAULT '0',
  `entity_id` int(10) unsigned NOT NULL,
  `language` varchar(32) NOT NULL DEFAULT '',
  PRIMARY KEY (`entity_type`,`entity_id`,`deleted`,`language`),
  KEY `entity_type` (`entity_type`),
  KEY `bundle` (`bundle`),
  KEY `deleted` (`deleted`),
  KEY `entity_id` (`entity_id`),
  KEY `language` (`language`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Data storage for field 49 (my_field)'

and the following two entries already present in the table.

+-------------------+----------+---------+-----------+----------+
|    entity_type    |  bundle  | deleted | entity_id | language |
+-------------------+----------+---------+-----------+----------+
| node              | article  |  0      | 9001      | 5555     |
| node              | article  |  0      | 9001      | abcc     |
+-------------------+----------+---------+-----------+----------+

When I run the following update query :

UPDATE `my_field_table` SET language='9002' WHERE entity_id = 9001;

I get the error message Error Code: 1062. Duplicate entry 'node-9001-0-9002' for key 'PRIMARY'

However I do not see such a combination of the field values already present which confuses me on the duplicate entry error. Usually I have at least one auto-increment key which I have as the primary key but this table does not have one and my lack of experience with DB isn't helping me either.

I tried searching for similar questions but the solutions suggested is to either check for existing entry or to create a new table with same structure and simulate this to check if my table is corrupted. The former does not fetch any results while I am able to reproduce the same error with the latter.

Some insight into the cause of this error would be greatly appreciated.

You have a primary key on the following fields, meaning that when combined they must be unique.

PRIMARY KEY (entity_type,entity_id,deleted,language)

Currently, they are unique because you have two entries in the table for language. After your update, language would have the same entry for both rows, and the rows would no longer be unique.


Time addition based on the previous row I appreciate if someone could give me some help.
I would like to deal with time addition based on the previous column using mysql. Here is what I have.

create temporary table test(id integer,
                            realtime datetime,
                            realminute float(15));

insert into test(id, realtime, realminute) values
("1","2018-12-12 03:03:03","96");

insert into test(id, realminute) values
("2","15"),
("3","10"),
("4","30"),
("5","30"),
("6","15");

Then I have this.

id|          realtime                      | realminute
1|           2018-12-12 03:03:03           |  96
2|                                         |  15
3|                                         |  10
4|                                         |  30
5|                                         |  30
6|                                         |  15

The following is what I would like to have. Note that 2018-12-12 03:03:03 plus 96 minutes would be 2018-12-12 04:39:03. This would be the realtime in the second column. And 2018-12-12 04:39:03 plus 15 minutes would be 2018-12-12 04:54:03 and this would be the real time in the third column and so on.

id|          realtime  | realminute
1| 2018-12-12 03:03:03 |  96
2| 2018-12-12 04:39:03 |  15
3| 2018-12-12 04:54:03 |  10
4| 2018-12-12 05:04:03 |  30
5| 2018-12-12 05:34:03 |  30
6| 2018-12-12 06:04:03 |  15

How would I calculate these realtime values?

I think I found the answer. The following code works.

select @realtime,
@realtime := date_add(@realtime, interval realminute minute) as j, t.*
from test as t, (select @realtime := "2018-12-12 03:03:03") a
order by id asc;


Modifying Schema on secondary replica I am looking for a solution for a secondary replica change.   I have a vendor database that uses memory optimized tables.   I am trying to find a solution that would allow the In Memory tables be changed to On Disk tables in a secondary replica for external integrations and reporting.

Can anyone think of a way to accomplish this?

SQL 2016 EE

Due to the "availability-groups" tag and the other terms you have used, I am assuming you are referring to a database in an availability group.

In short, the answer is no, you cannot do this.  The secondary database is read-only since it is a replica of the database on the primary.  If you removed the target replica from the AG, made the modification, and then tried to add it back to the AG, replication from the primary would no longer work.  The solution you are suggesting is 110% not possible with availability groups.

Since you can't modify the schema of the secondary replica, you'll have to start with a completely separate database for the reporting and keep it in sync with replication or an ETL process.  A lot more information would be required for anyone to be able to determine what would be the best solution.


Initial Full Backup required for Filegroup Backups? I'm assuming that an initial FULL BACKUP is required if I am taking FILEGROUP backups and would like this confirmed.  Many examples show one at the start, but don't make note that it is explicitly required.

I have a database that is terabytes in size with 12 filegroups, 4 of which are active and contain 50% of the overall data.

If I could backup 8 out of 12 read only sets (plus the PRIMARY) in the initial backup, and then separately keep the other 4, it would be much faster to restore, than restoring all 12, and then needing to restore newer versions of the 4 active filegroups.  Is this possible, or am I looking at a FULL BACKUP of the whole database, then separate FILEGROUP backups for the active?

I think what you're looking for is PARTIAL backups, which backup read/write filegroups (including PRIMARY) and any specified read-only filegroups. For your requirement, you could perform a PARTIAL backup of the 4 read/write filegroups plus PRIMARY and a File Backup of the 8 read-only filegroups.

Your file backups would not need to be performed again unless A) you change them to read/write, or B) your file backups become damaged. Beyond that, the file backups can be used for any restore going forward as needed.

Your PARTIAL backup of the PRIMARY and read-write filegroups can then be used as the source for PARTIAL restores or as a differential base for DIFFERENTIAL PARTIAL backups.

NOTE: The PRIMARY filegroup contains system tables and database metadata, including metadata about the state of other database files/filegroups, so you should always include this in your regular backup processes, it can't be backed up once and retained for future reference like the read-only filegroups. If you've designed your database correctly, you would ideally have little to no user data in the PRIMARY filegroup anyway, so its includion in PARTIAL backups shouldn't make much difference to performance or space consumption.

EDIT:

As per Peter's comment, I wanted to include a quote from the documentation:

  Partial backups are useful whenever you want to exclude read-only
  filegroups. A partial backup resembles a full database backup, but a
  partial backup does not contain all the filegroups. Instead, for a
  read-write database, a partial backup contains the data in the primary
  filegroup, every read-write filegroup, and, optionally, one or more
  read-only files. A partial backup of a read-only database contains
  only the primary filegroup.

So with a PARTIAL backup, you're backing up all read/write filegroups, including PRIMARY, without exception. It is possible to include read-only files or filegroups as well, but all read/write are included in a PARTIAL backup automatically.


Encrypted column - limit key access I'm new to SQL encryption and have a SQL Server 2012 database. The database itself needs to be accessible by DBAs but the data within one of the columns a table should only be viewable in plain text by a few users.

To do this, I tried using a Symmetric Key to encrypt the column which works fine. However, I don't see the security in this as any of the DBAs who have access to the database also have access to the key (seems to be stored within the 'Security' folder in Management Studio).

How can I ensure that only certain users have access to the key itself so that it can be decrypted even if more users have access to the database itself?

If there's a parameter I can pass from the app, that works for me too (C#/WPF)

Thanks

All versions prior to 2016 (currently in CTP/beta) doesn't allow you to easily lock DBAs from the data. Column encryption that you employed stores the key in the DB and secures it using the key hierarchy within the database which roots itself in the service master key. That means you can't lock it away from DBAs. The only way out is to use extensible key management (ekm) to store your encryption keys outside of the database. That allows you to cut DBAs out of the loop but it implies your users are either technically savvy or you have done a great job integrating the user's application with the ekm provider. You can read up more about ekm and implementation options in the links below

https://msdn.microsoft.com/en-us/library/bb895340(v=sql.110).aspx
https://msdn.microsoft.com/en-us/library/dn198405(v=sql.110).aspx
https://www.thales-esecurity.com/knowledge-base/solution-briefs/thales-microsoft-sql-server-ekm
http://www.safenet-inc.com/data-protection/database-encryption/sql-encryption/

If you can upgrade to SQL Server 2016 (still in CTP/pre-release but by the time you test, upgrade, test, modify app, test it'll probably be released), the new Always Encrypted feature makes this a lot easier. Encryption takes place on the client (app) side and the master key is stored client side. Server will only ever receive cipher text so DBAs are essentially blocked even if they run server side or network level trace. However, that's only in 2016 and there's no indication it will be backported to earlier versions (rarely ever happens).


TSQL Execution Plan - Estimated Number of Rows = 1 - Poor Performing Query TSQL MSSQL 2008r2

I am trying to tune a poor performing query.

For 3 out of 4 of the largest tables (several millions of rows) being queried (index seeks), the plan has decided that the estimated number of rows = 1. I think this is where the issue is.  The number of logical reads is crazy high which I think is as a result.

I don't know what is making the query engine assume this is the case.  Statistics are updated and I'm not using any user defined functions. The query engine is not trying to "convert_implicit" any columns.  So what do I need to look at, change to make this query more efficient.

The plan is here, https://www.brentozar.com/pastetheplan/?id=S18GOLFig

The variables are missing from Paste the Plan. Here they are:

DECLARE  @1stDay_StartExtract   DATE
        ,@LastDay_EndExtract    DATETIME
        ,@WhenProcessLastTime   DATETIME;

SET @1stDay_StartExtract= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-3, 0);
SET @LastDay_EndExtract = DATEADD(SECOND,-1,DATEADD(MONTH, DATEDIFF(MONTH,0,GETDATE())-1,0));

DECLARE  @dtStartDate_LT DATETIME= DATEADD( DAY, -1,@1stDay_StartExtract )
        ,@dtEndDate_LT   DATETIME= DATEADD( DAY, 1, @LastDay_EndExtract );

Let me know if you need more information.

Thank you in advance.

I know of three main ways of addressing a query performance issue caused by a cardinality mis-estimate:

1. Giving the optimizer more information

The query optimizer generally works better if it has higher quality information to inform the model. Steps here can include updating statistics, creating new statistics, using the RECOMPILE hint to pass along the literal values or variables, or materializing key intermediate result sets to provide better cardinality estimates or indexing.

Rewriting your query to be more clear to the optimizer

This can include simplifying code to remove redundant filters or refactoring it to be more clear to the optimizer. The query looks complex and we don't have the view code so it's hard to say more. There are a few filters in the query that appear to be extremely complex. It wouldn't surprise me at all that the optimizer cannot do a good job with guessing how those filters will affect the results.

3. Taking advantage of SQL Server enhancements

Sometimes there are features that you can turn that will make SQL Server do a better job with your workload. If you aren't using trace flag 4199 you could test this query with it. Trace flag 4199 is a collection of query optimization fixes that Microsoft has done over the years. It is on my default in SQL Server 2016. Trace flag 2301 is a bit less straightforward. It makes some changes to the optimizer around join cardinality estimate and in a rough sense you can say that the optimizer works harder to find a better plan. It is riskier and not nearly as common as trace flag 4199. Might not be practical but worth mentioning that each new version of SQL Server makes changes to improve query performance. In SQL Server 2014 there is a new cardinality estimator model which works better for some workloads.

For your particular query, I also want to note that it's easy to misread the single row estimate that you're seeing. The estimated number of rows that you see on the inner side of the nested loop is the number of rows returned per iteration of the loop. Seeing one row estimated from a nested loop seek is common and often not a sign of a problem.

However, the cardinality estimate for the outer part of the query is a bit off (36269 actual rows versus 6976 rows). It's perfectly natural to see a high number of logical reads with a nested loop and to suspect that part of the query is slow and needs to be improved. I find it useful to try to think about what the query optimizer should do instead to get the data that it needs. Would a hash join be better? Merge join? A nested loop with a different index?

I don't have the full picture but the nested loop joins that you called out don't look that bad to me. I don't see any key lookups and one of the indexes is covering. One way to move forward is to materialize all of the results of the query up until that point. Gather statistics on the temp table. Then look at a query plan for the adjusted query and see how long it takes to run. If the query plan changes for the better then you have a useful clue on how to make it run faster. If it doesn't change then you can at least get a more precise measurement of what you think the slow part is. Good luck!


Invoke-PolicyEvaluation fails with xml files on network location I am just starting to explore using Policy Based Management in SQL 2016. I created a policy that runs in SSMS just fine. I exported the policy to a folder on the local machine (VMSk1) that is shared to Everyone/read. I go to a second machine (VMSk2) and execute the following powershell. The idea was to have a central location for the xml export files and run on any server....

$Path = "\\\vmsk1\PolicyExports\AllDatabasesInSync.xml"
Invoke-PolicyEvaluation -Policy $Path -TargetServerName $env:computername

...I get the following error...

  Invoke-PolicyEvaluation : File 'Microsoft.PowerShell.Core\FileSystem::\vmsk1\PolicyExports\AllDatabasesInSync.xml' does not have a valid XML format that can be deserialized to a policy.

If I copy the file to VMSk2 and run...

$Path = "c:\PolicyExports\AllDatabasesInSync.xml"
Invoke-PolicyEvaluation -Policy $Path -TargetServerName $env:computername

...the policy will execute.

I tried

$Path = "\\\vmsk2\PolicyExports\AllDatabasesInSync.xml"

and it failed.

So I am left with the conclusion that the cmdlet will not work with an xml file located anywhere but a local drive; which seems just plain wrong.

Am I missing something? Why can I not execute the policy using an xml export file from a network share?

Nope you are not missing anything, the command does not support use of UNC paths. At some point when the SQL Server module is published onto GitHub we might be able to help Microsoft fix that issue. However, until then you can work around it by using New-PSDrive to map it to a drive letter and that should work.


Centos 7 install MySQL5..5 Install MySQL5.5 in CentOS7 by following instructions on this page: A Quick Guide to Using the MySQL Yum Repository

After that start the service: service mysqld start

This returns the error:

Job for mysqld.service failed because a timeout was exceeded. See "systemctl status mysqld.service" and "journalctl -xe" for details.

Below is the mysqld log file:

[root@iZu1a81d1qsZ log]# vi mysqld.log
160513 00:11:16 mysqld_safe Starting mysqld daemon with databases from     /var/lib/mysql
160513  0:11:16 [Note] /usr/sbin/mysqld (mysqld 5.5.49) starting as process 15341 ...
160513  0:11:16 [ERROR] An old style --language value with language specific part detected: /usr/share/mysql/
160513  0:11:16 [ERROR] Use --lc-messages-dir without language specific part instead.
160513  0:11:16 [ERROR] Can't read from messagefile '/usr/share/mysql/errmsg.sys'
160513  0:11:16 [Note] Plugin 'FEDERATED' is disabled.
/usr/sbin/mysqld: Unknown error 1146
160513  0:11:16 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
160513  0:11:16 InnoDB: The InnoDB memory heap is disabled
160513  0:11:16 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160513  0:11:16 InnoDB: Compressed tables use zlib 1.2.3
160513  0:11:16 InnoDB: Using Linux native AIO
160513  0:11:16 InnoDB: Initializing buffer pool, size = 128.0M
160513  0:11:16 InnoDB: Completed initialization of buffer pool
160513  0:11:16 InnoDB: highest supported file format is Barracuda.
160513  0:11:16  InnoDB: Waiting for the background threads to start
160513  0:11:17 InnoDB: 5.5.49 started; log sequence number 1595675
160513  0:11:17 [ERROR] Aborting

160513  0:11:17  InnoDB: Starting shutdown...
160513  0:11:18  InnoDB: Shutdown completed; log sequence number     1595675
160513  0:11:18 [Note]
160513 00:11:18 mysqld_safe mysqld from pid file     /var/run/mysqld/mysqld.pid ended

My question is: what causes this? How can i fix it?

The main problem you are hitting is the one on line 8:

160513  0:11:16 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.

You can run the command mysql_upgrade to create the missing table.


PgAdmin III -- how to merge output pane and query pane Somehow, I clicked on something the wrong way and popped the output pane out into a separate window from the query pane. I've tried restarting my computer, restarting PgAdmin III, but nothing fixes it. It's really annoying, and very hard to work with if I have more than one query window open.

Hopefully the image shows what I'm talking about.

What can I do to pop the output pane back into the bottom of the query pane?

Go to File-> Reset layout
This will merge the outputpane to the bottom of the screen.


When to use MySQL query_cache? Up until recently, I have viewed the query cache as a very important tool to improve query performance. Today, I was listening to a podcast that discussed tuning the query cache to 0, and using a better memory caching solution (such as memcache.d).
But they also mentioned that there are a few cases in which query_cache is helpful. So a general recommendation would be to have it enabled to on-demand (using SELECT SQL_CACHE, with a query_cache_type = 2 config setting).
My question is, assuming you've got a caching solution like memcache.d in place, what type of circumstances would make the query_cache more optimal?
Edit: added link

I think there's a lot of wrong information about the query cache out there.

The best case for the query cache, is when you have to examine a very large number of rows, but only return a few to a client.  A typical situation where this is to be common, is a system where no proper optimization or indexing has been applied.

In a situation where many of the queries are primary key lookups, or otherwise very well optimized, the query cache can cause negative scalability.  Yes: it makes things worse!

The reason for this, is that the design adds some internal locking, which limits your MySQL server from scaling on multi-core machines.

The query cache is a cause for many "sudden stalls" in MySQL - not all of them obvious.  In Percona Server, we added a new state to the processlist (Waiting on Qcache mutex):
http://www.percona.com/docs/wiki/percona-server:features:status_wait_query_cache_mutex

(Disclaimer, I work for Percona.)


How to interpret explain sql? SELECT DISTINCT TB.ID, TB.Latitude, TB.Longitude, 111151.293413 * SQRT( pow( - 6.185 - TB.Latitude, 2 ) + pow( 106.773 - TB.Longitude, 2 ) * 0.988392289802 ) AS Distance
FROM `tablebusiness` AS TB
JOIN tableauxiliary AS TA ON TA.BusinessID = TB.ID
WHERE MBRContains( GeomFromText( 'MULTIPOINT(-6.23178308133 106.726216919,-6.13821691867 106.819783081)' ) , TA.Latlong )
AND MATCH (
TA.FullTextSearch
)
AGAINST (
'res*'
IN BOOLEAN
MODE
)
AND TA.Prominent >15
ORDER BY Distance

This query takes 12 seconds.

If I do explain

I saw this easy explanation

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  TA  fulltext    PRIMARY,Prominent,LatLong,LatLong_2,FullTextSearch  FullTextSearch  0       1   Using where; Using temporary; Using filesort
1   SIMPLE  TB  eq_ref  PRIMARY     PRIMARY     752     gensurv_isikota.TA.BusinessID   1

All has 1 row. I wonder what the hell does that mean?

Explaining the significant parts of that EXPLAIN:

Go to table TA and use FULLTEXT search.  I would expect this to be first since MATCH..AGAINST takes priority over other clauses in WHERE.  It estimated that "1" row would be found.  That is an estimate, regardless of the Engine.  (There are a few cases where it will be exact.  Mahesh's comment refers to SHOW TABLE STATUS.)
Reach into table TB via the PRIMARY KEY.  The PK consumes up to 752 bytes.  (Perhaps VARCHAR(250) CHARACTER SET utf8?).  It is using gensurv_isikota.TA.BusinessID for the value.  It expects to get 1 row.  (It can't find more than 1, since a PK is UNIQUE.)

Missing from the EXPLAIN is any useful hints about the rest of the WHERE (MBRContains and Prominent>15) other than to say "Using where".

Also missing is anything about the ORDER BY.

"Using temporary; Using filesort" is unnecessarily scary.  It means that it had to create a temporary table and sort it.  But it does not necessarily mean that it touched the disk.  It will first try to do the tmp table as a MEMORY table.  If that fails, it will use MyISAM, and probably hit the disk.  If it truly was only 1 row (or even 1000), then this step will be so fast as to be inconsequential.

But your real question is "why 12 seconds"?

First, find out how many rows MATCH 'res*'.  The speed of that query depends primarily on how many rows.

Next, provide SHOW TABLE STATUS and SHOW CREATE TABLE for each table.  Also, provide SHOW VARIABLES LIKE '%buffer%'.  I suspect you have default cache sizes -- see http://mysql.rjweb.org/doc.php/memory .

Another thing to note.  The query must gather all possible rows before it can start the ORDER BY.  Even if you added a LIMIT.

How to speed it up?

IF you are pretty sure that the MBRContains will filter down to fewer rows than the MATCH, then use a subquery to find ids from the MBR, then JOIN to the rest.  This might be faster.

Another thought...  Two subqueries of the form

    SELECT ...
    FROM ( SELECT id... MBRContains ...) s
    JOIN ( SELECT id ... MATCH ... ) f  ON f.id = s.id
    JOIN other tables, etc...

But that gets messy and iffy.


Generate incrementing IDs without sequences Suppose there's a multi-tenant application where users can create some kind of documents with basic structure like

CREATE TABLE users (id SERIAL PRIMARY KEY, email TEXT);
CREATE TABLE documents (
  id SERIAL PRIMARY KEY
, document_id INT NOT NULL
, user_id INT NOT NULL
, text TEXT);

For each user document_id starts with 1 and increases preferably with gaps being a rare occurrence. The obvious solution is to create a sequence for each user get the document_id from there. But according to this databases don't behave well when there are lots of relations there. Another solution is to store next_document_id in users table and update it as necessary, but that means the lock on this row will be highly contested slowing simultaneous transactions from the same user. Any other ideas?

Stick to one serial column per document and create a gap-less sequence per user_id dynamically in a VIEW - if you really need it.

CREATE TABLE users (
  user_id serial PRIMARY KEY
, email text
);
CREATE TABLE document (
  document_id serial PRIMARY KEY
, user_id int NOT NULL
, document text);

CREATE VIEW document_with_rn_per_user AS
SELECT *, row_number() OVER (PARTITION BY user_id
                             ORDER BY document_id) AS doc_per_usr_id
FROM   document;

Never use basic type names like text as identifier. It's allowed, but it makes queries and error messages confusing. Generally, use descriptive names.

Related:

Compacting a sequence in PostgreSQL


How do I recover deleted rows from one table while having Recovery SIMPLE Model? I am using Microsoft SQL Server 2014 How do I recover deleted rows from a table while having Recovery SIMPLE Model? Database is in Microsoft SQL Server 2014. I have managed to find them in fn_dblog table before data is overwritten with a new one. Can I do anything with it?

SELECT * FROM fn_dblog(NULL, NULL) WHERE Operation = 'LOP_DELETE_ROWS'

I also find out the specific time at which rows were deleted.

USE Databasename
GO
SELECT
[Current LSN],  Operation, [Transaction ID], [Begin Time], [Transaction Name], [Transaction SID]
FROM
fn_dblog(NULL, NULL)
WHERE
[Transaction ID] = ‘000:000001f3'
AND
[Operation] = 'LOP_BEGIN_XACT'

I've read SIMPLE Recovery doesn’t support transaction log backup. But is there really no way to restore that data?

I don't think, you get any recommendation on fn_dblog as it's undocumented. So got following options:

Recent Full Backup: Restore recent full backup as copy without overwriting existing database then you should be able to recover particular table or rows within table.
Third-party tools: you can use third-party tools that can directly read your transaction log to recover data from particular transaction, you need to take database offline to read .log file by tool. However, chances are less in this method since the recover model is SIMPLE which truncates the log automatically


Should I use a separate table to store image file names? I am learning about database design and I'm writing a Java GUI program with a back-end database.  The main table in my database stores a different product in each row.  I have some columns such as: product_id (PK), price, stock_quantity, etc.  I also have eight columns that store the names of the file names for that product's images: img_file_1, img_file_2 ... img_file_8.  The Java program uses those file names to find the images to display on the screen for that product.

Is this poor design?  Should I be storing these file names in their own table, and adding a foreign key to that table in my main table?

The program works fine as is, but I want to make sure I am learning good habits.

I think it's better to let the images have their own table. The way I've solved it is by having 3 tables.

One for the products, another one for the images
and the third for the connections.

The product table just stores information about the product and nothing about the images.
The image table just stores the image id and file extension (the image id is also the filename).
The "connections"-table stores image id and product id so that you know which image belongs to which product.

Though, if only one image can be connected to one product you only need two tables. The product table and image table. The product table is just like above but the image table now contains a third column, productid. Now you can easily see which image belongs to which product.


SOA and Oracle cascade data between two pdb I am Java developer and need help for design and manage database .
Please view this picture :

I have two service named :
1) Authentication Service (Register new user and login)
2) Customer service (Record User Information like address,phone,....)

my question is :
what happened in customer database if authentication service remove a user from database ?
How can manage dirty data from customer pdb ?

Note 1 : two services have not any access to other Oracle pdbs .
Note 2 : customer is child of authentication (Father) so authentication service can not connect to child web services (Only customer can connect to authentication service)

Update : This is only a simple example for describe what i want to do .

INTRO

If you use different database accounts for "Authentication Service" and "Customer Service", you can easily control what each REST Service can see and do.

This is what database are designed to do: Control of access to data.

By levering the database's ability to secure access to data, you can use a single USER_INFO table in a single database.

Conceptual Setup

Your USER_INFO table should be treated as (java parlance) private.

Since USER_INFO is private, you do not GRANT access on that table to the accounts used by the REST services.

You use VIEWS and PACKAGES as your (java parlance) public interface to that private data.

The account for "Authentication Service" will get access to one set of VIEWS and PACKAGES.
The account for "Customer Service" will get access to another set of VIEWS and PACKAGES.

A VIEW can easily limit what columns are displayed.  In some cases, you can control what Rows can be displayed.

A PACKAGES is just a collection of function/procedures.
eg registerUser and loginNamePassword would belong to PACKAGE AUTHENTICATION_SERVICE_API

You could, almost, think of a PACKAGE as a (java parlance) class.

Your design would look like this:

ANSWERS

You are now using a single table.  This isn't a concern
You are now using a single table.  Oracle doesn't do dirty data(*).

(*) I'm ignoring the DBMS package for 2-phase commits that allows you to do that.


In BEFORE UPDATE TRIGGER, how do I prevent changing nothing but timestamp? I'm working on a trigger, and part of its functionality is to prevent UPDATE if the only field being updated is the timestamp field.

My current solution is like this:

CREATE OR REPLACE FUNCTION verify() RETURNS TRIGGER AS $verify$
    BEGIN
        IF (TG_OP = 'UPDATE') THEN
            IF ((OLD.first, OLD.middle, OLD.last, OLD.address, OLD.phone, OLD.notes)
                IS DISTINCT FROM
                (NEW.first, NEW.middle, NEW.last, NEW.address, NEW.phone, NEW.notes)) THEN
                    RETURN NEW;
            ELSE
                    RAISE NOTICE 'Must change something besides timestamp.';
                    RETURN NULL;
            END IF;
        END IF;
        RETURN NEW;
    END;
$verify$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS verify ON test;
CREATE TRIGGER verify BEFORE INSERT OR UPDATE ON test
    FOR EACH ROW EXECUTE PROCEDURE verify();

It works, but - if I add a field to the table, I would have to update the trigger, and that seems convoluted.

Is it possible to simplify the condition?

Make a copy of old, update the timestamp and compare it with new.

CREATE OR REPLACE FUNCTION verify() RETURNS TRIGGER AS $verify$
    DECLARE
        rec record;
    BEGIN
        IF (TG_OP = 'UPDATE') THEN
            rec=old;
            rec.timestamp = NEW.timestamp;
            IF rec IS DISTINCT FROM NEW THEN
                    RETURN NEW;
            ELSE
                    RAISE WARNING 'Must change something besides timestamp.';
                    RETURN NULL;
            END IF;
        END IF;
        RETURN NEW;
    END;
$verify$ LANGUAGE plpgsql;

(edit: thanks @ Granny Aching)

If you don't need OLD later in the trigger you can modify it without harm.

CREATE OR REPLACE FUNCTION verify() RETURNS TRIGGER AS $verify$
    BEGIN
        IF (TG_OP = 'UPDATE') THEN
            OLD.timestamp = NEW.timestamp;
            IF  OLD IS DISTINCT FROM NEW THEN
                    RETURN NEW;
            ELSE
                    RAISE WARNING 'Must change something besides timestamp.';
                    RETURN NULL;
            END IF;
        END IF;
        RETURN NEW;
    END;
$verify$ LANGUAGE plpgsql;


Query to get ORACLE_HOME path in Oracle 11G Can anyone help me out with an PL/SQL query to get the ORACLE_HOME path in Oracle 11G?

I require it to be fetched from the Oracle DB only & not via the Operating system's environment variables.

PS: I'm a DB newbie.

There is  sys_context function

that works for Oracle 12c

select SYS_CONTEXT ('USERENV','ORACLE_HOME') from dual;

This does not work for lower versions of Oracle.
For 11g and lower versions, I don't know a simple method.

PS: I read @Balasz' answer and now I know a simple answer for version 11g.


How to remove object from json array? My table:

CREATE TABLE items (
    id BIGINT PRIMARY KEY NOT NULL,
    name VARCHAR,
    images json
);

images format:

[
  {
    "id": "owner",
    "full": "<url>",
    "thumb": "<url>"
  },
  {
    "id": "note_0",
    "full": "<url>",
    "thumb": "<url>"
  },
  {
    "id": "note_1",
    "full": "<url>",
    "thumb": "<url>"
  },
  {
    "id": "note_2",
    "full": "<url>",
    "thumb": "<url>"
  }
]

I need something like this:

UPDATE items SET images = delete(images, 'note_1');

To remove all elements from the column images (holding a json array) where 'id' is 'note_1':

pg 9.3

UPDATE items i
SET    images = i2.images
FROM  (
  SELECT id, array_to_json(array_agg(elem)) AS images
  FROM   items i2
       , json_array_elements(i2.images) elem
  WHERE  elem->>'id' <> 'note_1'
  GROUP  BY 1
  ) i2
WHERE  i2.id = i.id
AND    json_array_length(i2.images) < json_array_length(i.images);

SQL Fiddle.

Explain

Unnest the JSON array with json_array_elements() in a subquery using an implicit JOIN LATERAL for the set-returning function. Details:

PostgreSQL joining using JSONB
How to turn json array into postgres array?

JOIN to the base table and add another WHERE condition using json_array_length() to exclude unaffected rows - so you don't update each and every row of the table, which would be expensive (and wide-spread) nonsense.

pg 9.4

This gets much easier with jsonb and additional jsonb operators.

UPDATE items i
SET    images = i2.images
FROM  (
  SELECT id, array_to_json(array_agg(elem)) AS images
  FROM   items cand
       , json_array_elements(cand.images) elem
  WHERE  cand.images @> '{[{"id":"note_1"}]}'::jsonb
  AND    elem->>'id' <> 'note_1'
  GROUP  BY 1
  ) i2
WHERE i2.id = i.id;

Eliminates unaffected rows at the start, which is much faster. Plus, there is extensive native index support for jsonb, too, now.

Here are some example, benchmarks and comparison of new features to old json and MongoDB,  plus outlook to jsquery by (some of) their main authors, Alexander Korotkov, Oleg Bartunov andTeodor Sigaevat PGCon 2014:

"CREATE INDEX ... USING VODKA"


How do I recover from Mariadb(10.4.18) binlog corruption I have a 3 node Mariadb Galera cluster running Mariadb 10.4.18 on ubuntu 18.4.
I applied updates to one of the nodes this morning and now Mariadb doesn't start on that node with the following error.
[ERROR] I/O error reading the header from the binary log, errno=175, io cache code=0
[ERROR] I/O error reading the header from the binary log
[ERROR] Can't init tc log
[ERROR] Aborting'
2 or the 3 nodes are running and are up to date.
There are 5 items in the mariadb-bin.index file and all point to files that are 0 length.
I have searched the internet to find out how to recover from this but the advices seems to be rather limited. I have found the following:
https://www.webmaster2020.com/forum/viewtopic.php?t=2716
http://raafat.tawasol.net/mysql-crashes-io-error/
These suggest deleting the corrupt fail and restarting. Is this safe and the best way to recover from this?

Once it became clear that the cluster had been working properly right until the reboot, I restored the node to the last backup and let Galera bring the node back up to date.
In the end it was came back very quickly.


PostgreSQL vs Oracle double More of rant, than a question, but maybe someone would explain -- why PostgreSQL migration wiki states that

  FLOAT (Oracle) is REAL (Postgres) and DOUBLE is DOUBLE PRECISION

when in PostgreSQL DOUBLE PRECISION supports 15 significant digits, whereas in Oracle DOUBLE provides 38 ?!

IMO, this is quite an omission for people planning to migrate their data to PostgreSQL.

The information seems to be inaccurate.

Oracle has only three numeric data types:

NUMBER: binary coded decimals. Slow, but exact.
BINARY_FLOAT: IEEE single precision floating point number. Fast and quite imprecise.
BINARY_DOUBLE: IEEE double precision floating point number. Fast and less imprecise.

You can use INTEGER, DOUBLE PRECISION, REAL and FLOAT in Oracle, like the SQL standard demands, but you will always get a NUMBER (FLOAT is NUMBER, only the precision is given in binary rather than in decimal digits).

PostgreSQL has more numeric data types:

numeric (or decimal, which is just another name): binary coded decimals. Exact, but very slow.

This corresponds to Oracle's NUMBER.
real (or float4): the platform's native single precision floating point numbers, usually IEEE

This corresponds to Oracle's BINARY_FLOAT.
double precision (or float8): the platform's native double precision floating point numbers, usually IEEE

This corresponds to Oracle's BINARY_DOUBLE.
integer (or int4): four-byte integers

Oracle has nothing like that.
bigint (or int8): eight-byte integers

Oracle has nothing like that.


Creative workaround for granting edit rights to individual jobs for a group? We have two developers that say they can not function without full rights to a few jobs on our database server. They both want rights to the same jobs.

Given that it seems that MS doesn't seem to be budging on granting rights to windows groups, I was wondering if anyone had a creative workaround? I can't let them have the system wide agent roles. http://connect.microsoft.com/SQLServer/feedback/details/508137/allow-windows-groups-to-own-sql-agent-jobs

I'm about to create a user and tell these two to log in to a shared account if they want to run/edit their jobs - but then there is no accountability.

Anyone have a creative solution I have missed?

Thanks :)

In the past to solve this I've created a web based app to allow users to change jobs.  The app connected via a sysadmin login, but only allowed the user to edit/start/stop the jobs that they had access to via the app.

Another option would be to go the single login route, but have auditing enabled for that login specifically and make sure that the auditing tracks the computer name as well as the login. That way if someone does something stupid you've got an audit trail and it'll tie back to their computer.

Group's can't be used to own jobs as the job runs under the context of the job owner.

There does need to be a better security model for jobs however.


calculating % difference for every row with the same ID Right now I have a table that looks like this but with around 1 million rows and over 100 company names.

Date  |  Company  | Price
10/08/16  Exxon      2.00
10/08/16  Shell      1.95
11/08/16  Exxon      2.01
11/08/16  Shell      1.97

What is the best way to go about calculating the percent difference per day per company so that I end up with a table or view that looks like this.

Date  | Company | % difference
10/08/16  Exxon     .56
10/08/16  Shell     .24
11/08/16  Exxon     1.005
11/08/16  Shell     1.01

would it be better to store each company as a column or keep it in row format, also how would I write the function so its not recalculating all million rows every day when I import new information.

I wrote out an answer that works in SQL Server before noticing you have this question tagged as MySQL.  For context, if you weren't in MySQL you'd use the LAG function like this:

SELECT
    s.P_date,
    s.Company,
    s.Price,
    s.Price/s.Last_price as Percent_diff
FROM
(   SELECT
        t.P_date,
        t.Company,
        t.Price,
        LAG(t.Price) OVER (PARTITION BY t.Company ORDER BY t.Company, t.P_Date)  Last_price
    FROM YourTable as t
) as s

The LAG line selects the Price value from the last record for the same company, allowing the outer query to do the percent calculation.

But MySQL doesn't support LAG.  Luckily there's an answer on Stack Overflow that shows how to simulate LAG in MySQL.  It even uses an example table almost identical to yours:

https://stackoverflow.com/questions/11303532/simulate-lag-function-in-mysql

I suggest you do this as a view, not a table.  That way the calculation will only run when you select from the view, leaving you free to update your main table without worrying about those million rows bogging you down.


Auditing Failed Logins in SQL Server Are there ways to understand which password entered when connecting to my server ?

The password is never recorded.  If it was it would be the first place a hacker would check for clues about correct passwords.

By default, TDS (Tabular Data Stream, the network protocol used by SQL Server), encrypts SQL Server secured passwords before sending them over the network.  If Active Directory logins are used with Kerberos authentication (the default), the client doesn't even send an encrypted version of the password over the wire, instead it sends proof of the Kerberos ticket to the server for authentication.  This means you cannot even sniff network traffic to determine what password has been tried.


dynamic postgres inside shell script not working PG_CMD="psql -d portal -U portal -c "
PG_CMD_TP="psql -d portal -U portal -t -P format=unaligned -c "
abc()
{
 $PG_CMD "
        DO \$$

           BEGIN
                   select * from customer;
          END;
        \$$";
}
*******MAIN***
abc

I want to print result of query on command line as well as I want to send it to excel file.

ERROR:  query has no destination for result data

Three misunderstandings:

You cannot return data from a DO command.
You cannot SELECT without target in plpgsql code. That's what the error message tells you.
You don't need either for a simple SELECT statement. Just run the statement itself:

abc()
{
 $PG_CMD 'select * from customer'
}