Migrating Oracle to MySQL: MySql Workbench Not Available

You can use ssh to securely forward the MySQL port remotely. For example, if you execute:

ssh -L 3307:localhost:3306 <ssh-user>@<remote-server>

You will be able to connect to the local port 3307 as if MySQL was running there. All connection information will be tunneled though ssh, so it will be sent securely. That way you can connect using Workbench using localhost:3307

I cannot say if Workbench would be the best tool for the migration, though. Here you have some pointers and tools: http://database-scalability.blogspot.com.es/2014/04/porting-from-oracle-to-mysql.html


When is running a graph database like OrientDB in cluster mode required?

You ask two different questions here:

  [W]hen should I use a clustered version for a graph database?

This one is easy: as soon as your scalability or availability requirements cannot be met by One Giant Server.

  [G]raph databases work best in a single server configuration[...] What is the reason for this statement?

Looking at the full book quote you can see a hint at the reason (emphasis mine):

  Although a lot of NoSQL databases are designed around the idea of running on a cluster, it can make sense to use NoSQL with a single-server distribution model if the data model of the NoSQL store is more suited to the application. Graph databases are the obvious category here—these work best in a single-server configuration.

As the book to which you are referring explains, there are two non-mutually-exclusive approaches to distributed databases: sharding (partitioning) and replication.

Sharding works well when all entities in a shard are tightly coupled together and simultaneously loosely, or not at all, coupled with entities in other shards. This is rarely the case with a graph model, where each node can potentially be linked to all other nodes1.

Replication works well when updates to your data are small in scope (e.g. a small number of entity instances per unit of work). Again, in a graph model a change of one node can potentially affect the entire graph, or a large portion of it, which in an ACID-compliant database causes almost exponential growth of the transaction volume, since you must update at least the majority of replicas before the transaction can be considered durable.

Things become even more complex with peer-to-peer, that is bidirectional, replication, where you have to deal with conflict resolution on top of that.

As a result, distributing a graph database (where a true graph model is justified) does not provide much benefit from the scalability perspective. Partitioning a graph database doesn't improve availability either.

1 - If it is not, then you need multiple graph databases, or even a relational database instead.


Azure IaaS VM - Use D:\ Drive for tempdb - NTFS Formatting 64k

Does anyone have anything better or see any holes in this process?

As for holes, the blog post contains several, but your script appears to plug them.

As for something better, TempDB on the local SSD scratch drive is now available out-of-the-box for the SQL Server on Azure VM, along with separate storage pools for data and log.  Here's a screenshot of the storage configuration pane from the Azure Portal:

The VMs drives will be formatted as follows:

PS C:\> Get-Volume  | Format-List AllocationUnitSize, FileSystemLabel

AllocationUnitSize : 4096
FileSystemLabel    : Temporary Storage

AllocationUnitSize : 65536
FileSystemLabel    : SQLVMLOG

AllocationUnitSize : 65536
FileSystemLabel    : SQLVMDATA1

AllocationUnitSize : 0
FileSystemLabel    :

AllocationUnitSize : 0
FileSystemLabel    :

AllocationUnitSize : 4096
FileSystemLabel    : System Reserved

AllocationUnitSize : 4096
FileSystemLabel    : Windows

The recommendation here to use a 64KB allocation unit size for TempDb may only be intended for when TempDb is on remote storage, not on the local NVMe flash disk, and in any case shouldn't have a large performance impact.


Oracle Database authentication through Active Directory

This is called Enterprise User Security, which is available in Enterprise Edition databases.

You can do this with Oracle Virtual Directory or Oracle Unified Directory (preferred).

They both require the Directory Services Plus licence.

The required steps for this can be found at for example:

Enterprise User Security (EUS) with Active Directory (AD) Integration Using OUD Proxy (Doc ID 1571196.1)

A public walkthrough:

Configure EUS with OUD, AD and DB12c


Why mysql throws "invalid default value for field" only when two or more timestamp fields exist in the create statement?

This behaviour is described in explicit_defaults_for_timestamp system variable which is by default disabled for 5.6,5.7 (and effectively disabled on 5.1) and it is enabled in 8.0.

Quoting from above link:

  (5.7) If explicit_defaults_for_timestamp is disabled, the server enables
  the nonstandard behaviors and handles TIMESTAMP columns as follows:

  TIMESTAMP columns not explicitly declared with the NULL attribute are    automatically declared with the NOT NULL attribute. Assigning
  such a    column a value of NULL is permitted and sets the column to
  the    current timestamp.
  The first TIMESTAMP column in a table, if not explicitly declared    with the NULL attribute or an explicit DEFAULT or ON UPDATE
  attribute, is automatically declared with the DEFAULT
  CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP attributes.
  TIMESTAMP columns following the first one, if not explicitly declared    with the NULL attribute or an explicit DEFAULT attribute,
  are    automatically declared as DEFAULT '0000-00-00 00:00:00' (the
  “zero”    timestamp). For inserted rows that specify no explicit value
  for such    a column, the column is assigned '0000-00-00 00:00:00' and
  no warning    occurs.

  Depending on whether strict SQL mode or the NO_ZERO_DATE SQL mode is
  enabled, a default value of '0000-00-00 00:00:00' may be invalid.

Now the issue with 5.7 is that NO_ZERO_DATE mode is enabled. This does not allow the default value of 0000-00-00 00:00:00 which as described in the documentation would be added as default value (if not explicitly declared) in timestamp columns following the first one.

Mysql 8.0 still has the NO_ZERO_DATE mode enabled, but the explicit_defaults_for_timestamp envvar is by default enabled which according to the documentation it would add null as the default value see below (which will not cause any errors on table creation ):

  (8.0) If explicit_defaults_for_timestamp is enabled, the server disables the
  nonstandard behaviors and handles TIMESTAMP columns as follows:

  [..]

  TIMESTAMP columns not explicitly declared with the NOT NULL attribute are automatically declared with the NULL attribute and
  permit NULL values. Assigning such a column a value of NULL sets it to
  NULL, not the current timestamp.

  [..]

  The first TIMESTAMP column in a table is not handled differently from TIMESTAMP columns following the first one.

This behavior has also been discussed on MySQL's issue tracker but has marked as "Not a bug".

As @Akina mentioned in comments, do not rely on default values. Always write the full specification for the field.


Indexes !

Use InnoDB, not MyISAM.
Reformulate the query so it does not 'explode' (LEFT JOIN), then 'implode' (GROUP BY)...

Something like:

SELECT  a.article_id AS article_id, article_title, article_content,
        article_status, article_date_time,
      ( SELECT  SUM(article_hits)
            FROM  phpkb_article_visits
            WHERE  article_id = a.article_id
      ) AS article_hits
    FROM  phpkb_articles AS a JOIN phpkb_relations AS r  ON r.article_id = a.article_id
    WHERE  article_show='yes'
      AND  article_status IN ('featured','approved')
      AND  article_expiry_date >= now()
      AND  category_id=221
    GROUP BY  a.article_id
    ORDER BY  FIELD(article_status,'featured','approved'),
              article_title ASC;

Please qualify each field with the table that it belongs to.  (I am depending on all the unqualified article_* fields belonging to article_hits.)

4.

Indexes needed:

phpkb_article_visits:  (article_id, article_hits)
article_hits:  (category_id, article_show, article_expiry_date)
phpkb_relations:  (article_id)

Notice how I simplified the test for article_expiry_date.


It is only supported in  mysql 8 https://dev.mysql.com/doc/refman/8.0/en/roles.html


Can I find out what version of MySQL from the data files?

The cleanest method I can think of is to examine only one table : mysql.user

If you can login to mysql at all, run this query:

SELECT COUNT(1) column_count FROM information_schema.columns
WHERE table_schema='mysql' AND table_name='user';

Here are the answers you should get:

If you get 43, MySQL 5.6
If you get 42, MySQL 5.5
If you get 39, MySQL 5.1
If you get 37, MySQL 5.0

If you cannot login to mysql, copy the table

user.frm
user.MYD
user.MYI

to another machine running mysql.

On that other machine run CREATE DATABASE junk;

Go to the datadir in that other mysql server

Change directory to junk

Copy the three files for the user table into it. If the other machine is a Linux box, remember to run chown mysql:mysql user.*

Login to MySQL on that other machine and run

DESC junk.user;

It will tell the same numbers I mentioned before.

However, if you get 28 that's MySQL 4.1. If you get 17, that's MySQL 4.0. Have fun with that one.

Give it a Try !!!


Ensure you have enough disk space!
Shutdown mongos on all 3 nodes
From Master node; tar all local* and mymongobase* files
SCP files to 2 secondaries
Unpack files in mongo data location (/var/lib/mongodb)
Start mongod on all nodes

Works for me


I think your query can be written using window aggregates. I don't know if it will be more efficient though:

with seekResults as
(
  select distinct
    trx_id_fk,
    account_fk
    customer_cif,
    customer_tin,
    tin_type,
    id_number,
    search_number
  from transactionsOP
)
select
  trx_id_fk,
  case
    when account_fk is not null then
      count(*) over (partition by search_number, account_fk)
    when customer_cif is not null then
      count(*) over (partition by search_number, customer_cif)
    when customer_tin is not null then
      count(*) over (partition by search_number, customer_tin, tin_type)
    when id_number is not null then
      count(*) over (partition by search_number, id_number)
    else
      count(*) over (partition by search_number)
  end as transaction_period
from seekResults ;


InnoDB, Writing TRIGGER to INSERT into tables

Given your table structure:

create table user_address (
u_id integer auto_increment primary key,
street_name varchar(1000),
locality_name varchar(1000)
);

create table street_master
(
s_id integer auto_increment primary key,
s_name varchar(1000)
);

This will do the job:

DELIMITER $$
create trigger user_address_btrig
before insert on user_address
for each row
begin
DECLARE CNT INT;
SET CNT = ( select count(*) from street_master where s_name = new.street_name );

IF CNT=0
THEN
insert into street_master (s_name) values (NEW.street_name);
END IF;
end;
$$
DELIMITER ;

To test:

insert into user_address (street_name, locality_name) values ( 'test street', 'test locality' );

select * from street_master where s_name = 'test street';

I suggest you read the MySQL documentation on triggers and learn for yourself.


Should I extract / normalize the mailing address despite requirements limiting number / type of addresses?

Always bet on requirements changing, or being reinterpreted, at some point in the future.

Does the requirements documentation explicitly state that there are exactly two or just that there should be up to one primary and up to one secondary? If the latter then what could be meant is there could be many addresses of which up to one is the primary and up to one is the secondary and you should allow for more addresses.

Even if not, I would still err on the side of addresses being separate entities if you need to support more than one for any other given entity. The extra work now should be minimal, if you need to emulate the non-normal-form for the application logic you can by various means, and when the requirements change later your job will be a lot easier (and you can either impress the client/devs with your foresight or keep shtum and charge them as if the change request does require significant structural alterations in the DB).


Why do I get "Truncated incorrect date value" when using str_to_date()?

you have to pass your datetime column into date() function like this :
create table test (
  id int AUTO_INCREMENT,
  datetime_string datetime,
  date_date date,
  PRIMARY KEY (id)
);

insert into test(datetime_string) values('2014-12-14 22:49:13');
update test set date_date = date(datetime_string);

http://sqlfiddle.com/#!9/cc200b/1


Deleting Entries from Table not reducing my DB file

On case:

TRUNCATE removes the file and creates another one. BTW in MySQL TRUNCATE is not allowed on tables with FK dependencies.

DELETE invalidates the record and the space the record used to occupy may be claimed later on for reuse.

One other operation forces the rearrangement of the data: OPTIMIZE.

Off case:

If your application works in a manner which habitually deletes large quantities of data, you should consider writing a cron to call an OPTIMIZE over the affected tables (sometime during low traffic).

Otherwise you should not worry about this because as stated here "Deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions".

Take into consideration whether OPTIMIZE is transactional or not - DDL statements are not transactional in MySQL, not sure if OPTIMIZE is DDL.

If it's not you may need to do something else such as:

create a new table from the result of a full content select from the old table;
wait for the data to be inserted into the new table;
switch the table names with each other;
drop the old table;

The new table will have a clean slate.


SQL Server - Error 8623 Severity 16 State 1

You can use Extended Events to capture failing queries.
Here is a link to a detailed howto article.
https://www.brentozar.com/archive/2013/08/what-queries-are-failing-in-my-sql-server/

TSQL to create xevents and start:

CREATE EVENT SESSION what_queries_are_failing
ON SERVER
ADD EVENT sqlserver.error_reported
(
ACTION (sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.database_id, sqlserver.username)
WHERE ([severity]> 15)
)
ADD TARGET package0.asynchronous_file_target
(set filename = 'C:\XEventSessions\what_queries_are_failing.xel' ,
metadatafile = 'C:\XEventSessions\what_queries_are_failing.xem',
max_file_size = 5,
max_rollover_files = 5)
WITH (MAX_DISPATCH_LATENCY = 5SECONDS)
GO

-- Start the session
ALTER EVENT SESSION what_queries_are_failing
ON SERVER STATE = START
GO

-- Trigger Level 16 error
select * from Person.Person where ModifiedDate='20aaa';

--Query the logfile
;with events_cte as(
select
DATEADD(mi,DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP),
xevents.event_data.value('(event/@timestamp)[1]', 'datetime2')) AS [err_timestamp],
xevents.event_data.value('(event/data[@name="severity"]/value)[1]', 'bigint') AS [err_severity],
xevents.event_data.value('(event/data[@name="error_number"]/value)[1]', 'bigint') AS [err_number],
xevents.event_data.value('(event/data[@name="message"]/value)[1]', 'nvarchar(512)') AS [err_message],
xevents.event_data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS [sql_text],
xevents.event_data
from sys.fn_xe_file_target_read_file
('C:\XEventSessions\what_queries_are_failing*.xel',
'C:\XEventSessions\what_queries_are_failing*.xem',null, null)
cross apply (select CAST(event_data as XML) as event_data) as xevents
)
SELECT *
from events_cte
order by err_timestamp;


Dynamic SQL won't be able to see your table variable unless you also declare it and populate it within the same dynamic SQL scope. A #temp table will work fine, and I'm not sure why you "need" to use a table variable, but you can always do this:

SELECT * INTO #tbl FROM @tbl;

Anyway assuming you can change your process:

CREATE TABLE #tbl(Col1 INT, Col2 INT);

INSERT #tbl SELECT 1,4
  UNION ALL SELECT 2,3
  UNION ALL SELECT 5,8;

DECLARE @sql NVARCHAR(MAX);
DECLARE @filter NVARCHAR(255);

SET @sql = N'SELECT Col1, Col2 FROM #tbl';

SET @filter = N'Col1 = 1 OR Col2 = 3';

SET @sql = @sql + COALESCE(' WHERE ' + @filter, '');

EXEC sp_executesql @sql;

DROP TABLE #tbl;

Results:

Col1        Col2
----------- -----------
1           4
2           3

As an aside, you cannot do things like this:

select * from @tbl where @filter

You need to build such statements dynamically. SQL Server won't see @tbl or @filter as entities or where clauses.


The Circuitous Path to Learning (i.e. a semi-sequential log of what happened)

Sending a BULK INSERT query - referencing the z:\ path of my workstation - via the python script returns an error

The query is a variation of

BULK INSERT [dbo].[tStaging] FROM '/path/to/file.csv' WITH (
  FIELDTERMINATOR = ',', MAXERRORS = 1024, FIRSTROW = 1, KEEPNULLS,
  ERRORFILE = '/path/to/file.err', TABLOCK )

The error was

  Cannot bulk load because the file "Z:/pythonscript/data/file.csv"
  could not be opened. Operating system error code 3(The system cannot
  find the path specified.).

Switching to the S:\ based-path gives me

  Cannot bulk load because the file
  "S:/blah/shared/pythonscript/data/file.csv" could not be opened.
  Operating system error code 5(Access is denied.)

Hmm, okay.  I'll follow my own advice here and enable read and execute permission for the service accounts for the path specified.

Then if I specify an error file via the ERRORFILE = '{}' parameter, I may get:

  Cannot bulk load because the file "S:/blah/shared/pythonscript data/file.err" could not be opened. Operating system error code 80(The file exists.)

I cannot specify an "append" mode. As the MS documentation points out if I bother to read it, it just returns an error if the file exists.  That's frustrating. Okay...I say, I'll just delete the file (and the file.err.Error.txt that is also created) and re-run.

  Cannot bulk load because the file "S:/blah/shared/pythonscript/data/file.err" could not be opened. Operating system error code 5(Access is denied.)

So...write access too then? Silly me. Do this again for write access.

About now I hit an error that suggested that I didn't have access for BULK INSERT operations, so had to enable that feature

USE master
go
GRANT ADMINISTER BULK OPERATIONS TO pySqlUser

Solving that, I still get

  SQL ERROR in BULK INSERT. #4864 Severity: 16    State: 1        Message: SQL Server message 4864, severity 16, state 1, procedure , line 1:
  Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (eID)

eID is an autoincrement primary key that I'm trying to add to my data set in a staging table.  I tried destroying the table and moving the eID column to the last. That doesn't appear to help.

  SQL ERROR in BULK INSERT. #4866 Severity: 16    State: 1        Message: SQL Server message 4866, severity 16, state 1, procedure , line 1: The bulk load failed. The column is too long in the data file for row 1, column 12. Verify that the field terminator and row terminator are specified correctly.

I suspect that something is wrong with my row terminator. Expanding the field size of the 12th column, immediately before eID, complains about

  Bulk load data conversion error (type mismatch or invalid character for the specified codepage)

Playing around with the ROWTERMINATOR statement, I settle on, in python, ROWTERMINATOR = '{}'.format(chr(10))

Still problems.  Maybe if I add the KEEPIDENTITY keyword...

  Bulk load failed. User does not have ALTER TABLE permission on table 'dbo.tStaging'. ALTER TABLE permission is required on the target table of a bulk load if the target table contains triggers or check constraints, but the  'FIRE_TRIGGERS' or 'CHECK_CONSTRAINTS' bulk hints are not specified. ALTER TABLE permission is also required if the 'KEEPIDENTITY' bulk hint is specified.

That's weird, I say to myself, why would you need ALTER TABLE for KEEPIDENTITY? Then I look up KEEPIDENTITY again, and realize it's basically the opposite of what I want. I want it to "GENERATEIDENTITY" instead, but that's not an option. So, one option is to go with an XML file where I specify that the eID column is not coming from the imported data. Nah, I'll try a view instead.

Once the view was written, with all columns - minus eID - in same order as the underlying table, I could execute the following

qstring = "BULK INSERT {} FROM '{}' WITH ( FIELDTERMINATOR = '{}', ROWTERMINATOR = '{}', MAXERRORS = 1024, FIRSTROW = {}, KEEPNULLS, ERRORFILE = '{}', TABLOCK )".format(viewname, filename, fieldSep, chr(10), 1, ename)

which generates e.g.

BULK INSERT [dbo].[tStagingView] FROM 'S:/path/to/file.csv' WITH (
  FIELDTERMINATOR = ',', ROWTERMINATOR = '[newline here]',
  MAXERRORS = 1024, FIRSTROW = 1, KEEPNULLS,
  ERRORFILE = 'S:/path/to/file.err', TABLOCK )

I have to police the directory for error dump files and remove them before the SQL call, or I need to name my error files "{}.err".format(gmtime()) and trash them manually, but frequently. I had intended to use the system temp directory via ERRORFILE = '%TEMP%/file.err'. But that gives:

  Cannot bulk load because the file "%TEMP%/file.err" could not be opened. Operating system error code 3(The system cannot find the path specified.).

So despite SQL Server resolving linux-style path slash direction, it can't handle a simple environment variable expansion. Maybe I'll hard-code my server's temp directory path into my python code so I don't have to do any manual purges of my data directory. An alternative would be to create a cross-drive junction/symlink that points to the system temp, but that seems to be asking for trouble.


Look on fiddle

SELECT pp.*, so.date_created sale_date_created
FROM pp
JOIN so  ON so.date_created >= pp.date_created
        AND so.date_created BETWEEN COALESCE(pp.from_date, so.date_created) AND COALESCE(pp.thru_date, so.date_created);

COALESCE() is used to prevent discarding records containing NULL(s).


Redshift DROP and TRUNCATE hang with no locks

Please try this code to identify locks over past 7 days.

This is from the link here lock_wait.sql

WITH locks AS (
SELECT svv.xid
,      l.pid
,      svv.txn_owner as username
,      TRIM(d.datname) as dbname
,      svv.relation
,      TRIM(nsp.nspname) as schemaname
,      TRIM(c.relname) as objectname
,      l.mode
,      l.granted
,      svv.lockable_object_type as obj_type
,      svv.txn_start
,      ROUND((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM svv.txn_start)),2) as block_sec
,      ROUND((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM svv.txn_start))/60,2) as block_min
,      ROUND((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM svv.txn_start))/60/60,2) as block_hr
,      CASE WHEN l.granted is false THEN ROUND((EXTRACT(EPOCH FROM current_timestamp) - EXTRACT(EPOCH FROM rct.starttime)),2) ELSE NULL END as waiting
FROM   pg_catalog.pg_locks l
INNER JOIN pg_catalog.svv_transactions svv ON l.pid = svv.pid
AND   l.relation = svv.relation
AND   svv.lockable_object_type is not null
LEFT JOIN pg_catalog.pg_class c on c.oid = svv.relation
LEFT JOIN pg_namespace nsp ON nsp.oid = c.relnamespace
LEFT JOIN pg_catalog.pg_database d on d.oid = l.database
LEFT OUTER JOIN stv_recents rct ON rct.pid = l.pid
WHERE  l.pid <> pg_backend_pid()
)
select distinct * from (
SELECT l.xid
,     l.pid
,      l.username
,      l.dbname
,      l.relation
,      l.schemaname
,      l.objectname
,      l.mode
,      DECODE(l.granted, true, 'True', false, 'False') granted
,      l.obj_type
,      l.txn_start
,      DECODE(l.granted, true, l.block_sec, NULL) as block_sec
,      DECODE(l.granted, true, l.block_min, NULL) as block_min
,      DECODE(l.granted, true, l.block_hr, NULL) as block_hr
,      waiting
,      b.max_sec_blocking
,      b.num_blocking
,      b.pidlist
FROM   locks l
LEFT OUTER JOIN
      (
       SELECT relation
       ,      mode
       ,      listagg(b.pid, ',') as pidlist
       ,      MIN(block_sec) as min_sec_blocking
       ,      MAX(waiting) as max_sec_blocking
       ,      COUNT(*) as num_blocking
       FROM   locks b
       WHERE  granted is false
       GROUP BY relation
       ,      mode
      ) b
  ON  l.relation = b.relation
AND  l.granted is true
AND (l.mode like '%Exclusive%'
OR (l.mode like '%Share%' AND b.mode like '%ExclusiveLock' and b.mode not like '%Share%'))
)
-- where objectname like 'pg_%'
ORDER BY granted DESC
,       max_sec_blocking desc nulls last
,      block_sec DESC,
waiting desc nulls last
;


Memory usage in linux in general and for Postgres in particular is a pretty complex subject, a good starting point is Bruce Momjians blog covering the subject and the usage of smem. It is well worth following the links in Chris Seibenmanns blog on the subject.


What is 'normal' will depend on your situation. An OLTP database and a warehouse will have very different usage profiles.

If you use a connection pool, then you probably don't expect to see a wide variation in the number of connections or sessions. Such a change would indicate a problem.

Generally you are best off looking for dramatic changes in your measures.

Grab SQL Developer 3 Early Adopter edition from the Oracle website. Check out some of the system reports, and look at the underlying SQLs.


What is the fastest way to insert data into MySQL memory table?

You need to batch the inserts. This is done by means of extended inserts.
In your question, you can take the row and make them a single insert as follows:
INSERT INTO `test` (`a`, `b`) VALUES
(4, 5),(5, 6),(6, 7),(6, 7),(1, 2),(2, 3),(3, 4),(4, 5),(5, 6),(6, 7);

Why do it this way ?
When you insert into a MEMORY table, there is still some mild disk I/O to check the table's existence by hitting the table .frm file. I wrote about this 5 years ago (I am using the MEMORY storage engine but MySQL still writes to my disk...Why?)
Your question's example does 10 inserts. That's 10 disk reads to the .frm file and 10 full table locks (the lock granularity for the MEMORY storage engine is a full table lock).
Changing it into a single extended insert, the .frm is read only once and the table locked once.
SUGGESTION
In your case, if test.sql has 10 million rows, there will 10 million tables locks and 10 million access of the .frm file.
You should setup test.sql to batch like 10,000 rows in a single insert. Don't worry, mysqldumps create extended insert like this all the time.
That way, you will have 1000 inserts instead of 10,000,000.
GIVE IT A TRY !!!


Unable to restore TDE enabled database when MAXTRANSFERSIZE and CHECKSUM is used

I was able to reproduce your problem.

Adding FORMAT to the BACKUP command solved it for me.

While I can't seem to find concrete documentation, it's my opinion that this is related to the fact that INIT retains the existing media header on the backup set while FORMAT creates a new media header.

I'm still researching this issue and if I find additional information, I will update this answer.


This isn't answering your question, but please be careful with extended events.  This one has no filters on it at all, so it's collecting a lot of data.  If this is run on a small test box then you won't know and won't care.  If you ran this on a highly transactional production system then there's a good chance your users would be able to tell you that something bad is happening.

If you want to know more about extended events read Jonathan Kehayias's work.

https://www.sqlskills.com/blogs/jonathan/an-xevent-a-day-31-days-of-extended-events/

If you have a PluralSight subscription he and his coworker Erin Stellato have some great videos teaching you a lot more on this subject.


What is the most efficient way to alter a column definition in a table with millions of rows

Would it be faster to:

Create a new table with the correct definition for Column1
INSERT INTO <NewTable> SELECT * FROM <OriginalTable>;
Rename OriginalTable to OriginalTable_old; Rename NewTable to OriginalTable
Validate and Drop OriginalTable_old

The advantage here is that you do not hold a lock on the Original table for the duration of the operation. The table should only be locked during the rename phase. (It assumes that SQL Server supports an object level rename.)


Weekly full backups and daily differential backups, saving the full backups from each week?

As you have stated, your backup strategy can be viewed as below :

  The reasoning for this type of plan is due to HDD size restrictions on the Azure environment I am working with.

Two imporatant things that you can do

Enable Instant file initialization (do follow other best practices when runing SQL Server on Azure VM)
Enable backup compression and take backups with WITH COMPRESSION

  Given that, is this a sound strategy, or where should I seek improvement?

From the above figure, there is a clear room for improvement. You should take a differential backup on Sat (just incase if a disaster happens). Also, its important to read Backups Gone Bad: The Danger of Differentials. Make sure if you have to take a full backup in the middle of your backup strategy, you use COPY_ONLY backup.

  Second, how do I go about retaining the previous week's full database backups but NOT the differentials?

Dont use maintenance plans as they have noticeable drawbacks. I highly recommend to use Ola's backup solution (and Index maintenance  solution as well).

Edit : As per your comment:

If a full backup occurs in the middle, your differentials would be useless, since the base has changed and you dont know who took the last full backup. The differential relies on the last full backup. If you know people will take adhoc full backups that will mess up your differentials then deny them backup rights and create a stored procedure that will take full backup with COPY_ONLY option and grant those people execute rights to that SP. Additionally, do concentrate on recovery model of the databases - Full recovery model has to have a log backup.

You could even query msdb.dbo.backupset where is_copy_only = 0 to find out user_name of users who are taking COPY_ONLY backups

Just adding this for completeness - Restricting users to COPY ONLY LOG backups


I'm the OP.  Thanks for the help from those above.  Here is what I ended up doing to solve this problem.  I think this is all of the steps but something could be missing.  (This is via Mac on Catalina.)  Happy to accept any corrections if I forgot a step.

Format the external drive AFPS or GUID via diskutility (using the "view all" command).  (This will delete everything on the drive, so you need a backup.)

After formatting, sudo chmod -R 0750 "/Volumes/your_external_hd_name/  from the command line - this will set the correct permissions.

initdb -D '/Volumes/your_external_hd_name/database/'     # creates a "database cluster"

pg_ctl -D /Volumes/your_external_hd_name/database/ start   # seems to run the cluster

NB: at the above step, if you get an error including the line "Is another postmaster already running on port 5432?", then run ps -ef | grep postmaster | awk '{print $2}' on the command line, followed by kill <pid1> ... <pidN> for any PIDs which show up.

psql postgres

CREATE DATABASE <database_name>;

\l                                                 # list databases

\c <your_database>                                 # connect to the database

\dt                                                # list tables

CREATE DATABASE <name>;                            # creates w/in the cluster

CREATE TABLE <table>;                              # include some columns

\dt                                                # to check that it was created,  should appear

If you need to undo/correct: DROP TABLE <name>;

\copy <table_name> FROM 'path/to/file';

\q # exit

 pg_ctl stop -D /Volumes/your_external_hd_name/  # seems to be important to prevent database from getting damaged, but I have never disconnected w/out it so I am not 100% sure.


Is selecting from the same server but different database slower than the same database?

To add a bit to TomTom's comment: The optimizer has full access to meta-data etc regardless of whether the data is in one database or several databases. And the optimizer or execution engine is on constrained to one database. As long at it is in the same instance! If you talk about cross-instance queries (using linked servers, for instance), then you will definitely hurt!


Comparing nullable column values using "IS NULL" versus "= NULL"

As documented in the manual it's not possible to use = to test for NULL values:

  Ordinary comparison operators yield null (signifying “unknown”), not true or false, when either input is null. For example, 7 = NULL yields null, as does 7 <> NULL.

is there "null-compatible operator" to use - Yes, right below the above quote it states:

  When this behavior is not suitable, use the IS [ NOT ] DISTINCT FROM predicates

So you can use

where c2 is not distinct from $1

One drawback of the IS DISTINCT or IS NOT DISTINCT operator is however, that they can't use an index.


Redundant argument in printf at /usr/bin/pt-table-checksum


How to grant a user the rights to access to all new (future) databases?

Well - there is a bigger question regarding why you are using NT Authority\Network, but I asked a similar question ages ago
Permissions on New Databases
So in theory, it is fairly simple.


New SQL Server 2008 instance, load old instance DB's

You can actually attach a database file.

CREATE DATABASE MyAdventureWorks
    ON (FILENAME = 'C:\PathToDataFile\DataFileName.mdf'),
    (FILENAME = 'C:\PathToDataFile\LogFileName.ldf')
    FOR ATTACH;

Modify the paths with the paths to your files. The trick is the FOR ATTACH clause.

You can attach a database by right-clicking on the Databases node then selecting the Attach item.

References:

How to: Attach a Database File to SQL Server Express

Attach a Database


As the error indicates you, your secondary database is not ready for log shipping or it is online. Before you start the actual log shipping process, you need to make secondary database ready for the log shipping. Either you can leave the secondary database under restoring mode and then let SQL Server take snapshot of your primary database, or you can take a full and transnational backup of primary database and restore it on your secondary DB using no recovery mode then continue your log shipping process.


OLEDB waits cover a variety of states, including (but not limited too) client-side profiler traces, some DBCC commands, materialisation of DMVs, possibly some full-text functions in 2005 (IIRC, most have been split out to FT specific waits in 2008) and also linked server queries as you mentioned.

I'm not aware of UDF functions inherently resulting in OLEDB waits, unless they are performing one of the actions I mentioned above.

Rather than looking at the raw percentage wait time attributed to OLEDB, have you checked the average wait time per wait? It may prove to be so low as to be insignificant.


Multi SQL Server Web licence on Single Machine

Apart from the licensing question, there's a question about the technical limitations of Web/Standard edition.

Web edition is limited to "Lesser of 4 sockets or 16 cores", but for a Virtual Machine the sockets and cores are reported by the hypervisor, and may differ from the underlying hardware.  That appears to be the case here, as the hypervisor is presenting the VM with 8 VCPUs and claiming that they are on 8 separate sockets.

SQL Server on startup looks at the reported sockets and cores when and will limit the schedulers accordingly.  Here that's limiting the CPU use to half of what it should be.

To fix this, the hoster should reconfigure the hypervisor to accurately report the socket and core count of the hypervisor host.


ORA-12505: TNS:listener does not currently know of SID given in connect descriptor

As the ORA-19815 message indicates, your 3GB recovery destination is full; the database is unable to archive any more redo log files. It has reached a point where it needs to switch a full redo log file out, but there are none available for re-use because they can't be archived. I imagine this is why it wouldn't shut down cleanly, and possibly why you wanted to shut down in the first place as you wouldn't have been able to do much with the database in that state.

The short-term fix is to increase the size of the recovery area, assuming you have enough disk space, e.g.:

startup nomount
alter system set db_recovery_file_dest_size=4G scope=both;
alter database mount;
alter database open;

But you then need to work out how to manage your archives, and set the recovery area size to a sensible value for your backup and retention needs. This may be as simple as scheduling a job to remove obsolete backups, e.g. from the Enterprise Manager console's Availability tab, under Manage Current Backup; but you need to determine the best course for you (and not blindly follow advice from some random guy on the Internet who knows nothing about your requirements, and not that much about EM/RMAN either).


EXP-00085 warnings while doing full backup

I expect this is bug 5120780 fixed in 10.2.0.4 (a quick search on Metalink would have told you).


Best approach for moving tempdb .ndf files

The best approach is the one you can find on the Moving the tempdb database section of the documentation.

Your method is close to the one Microsoft recommends, except by the 2nd and  3rd steps you listed and the reason is:

  Because tempdb is re-created each time the instance of SQL Server is
  started, you do not have to physically move the data and log files.


Internal reason for killing process taking up long time in mysql

The reason the kill takes so long is most likely due to the rollbacks issued by the innodb transaction. From InnoDB performance tips:

  Beware of big rollbacks of mass inserts: InnoDB uses the insert buffer to save disk I/O in inserts, but no such mechanism is used in a corresponding rollback. A disk-bound rollback can take 30 times as long to perform as the corresponding insert. Killing the database process does not help because the rollback starts again on server startup.

Edit:
The innodb force recovery methods might be of  use to you (glad you did this on a test environment)

  You can kill the mysqld process and set innodb_force_recovery to 3 to bring the database up without the rollback, then DROP the table that is causing the runaway rollback.

And next time, insert a small subset of data each time. 10 million rows shouldn't take that long to insert, but there could be multiple reasons it does. Without knowing your environment I can't offer advice on that.


Create Linked server which fetches data from Sybase to SQL. Source Creating Linked server to SYBASE from SQL Server
  Use ODBC connections to create an SSIS package which will fetch the data from SYBASE database onto SQL server. Check this Extracting data
  from Sybase SQL Anywhere using SSIS through ODBC. Then Schedule
  the above created package via SQL agent job to update data on SQL
  server as per schedule which meets the requirement.


Checkpoints on secondary replica AlwaysOn AG

Is my assumption correct that in an AG checkpoints are being generated on the primary replica and redone on all secondary replicas?

Yes.

A CHECKPOINT is a logged operation, as it's part of SQL Server's recovery strategy.  When the database starts up, it needs to know what modified pages (in the log file) haven't been written to the data file - so it can redo those changes before user queries start hitting the database.

I'm going to snag a screenshot from my blog post that looks at certain types of logging activity:

As you can see, the CHECKPOINT "start" and "end" are written to the transaction log.

A simplified view of the way AGs work is that they redo log entries from the primary replica on all secondaries.

Thus, since CHECKPOINTs are logged, they will be replayed on secondary replicas.

As a side note, John Eisbrener usefully pointed out that, if there are other databases on the secondary replica, those CHECKPOINT operations are also going to be picked up by this Perfmon trace.


ALTER TABLE ... SWITCH would allow you to switch in each table when you want to change the "live" table.  The only requirement is to ensure both tables involved in the switch are in the same filegeoup, and have the same structure.


Plain answer is yes, if you have enabled trace flag 1118 on your instance a rebuild of those indexes will move them to full extends instead of mixed. You can get more information on this from Paul Randal's post: Are mixed pages removed by an index rebuild?

A synopsis of the comment raised by ray herring on July 10, 2014 and Paul's response:

Ray asked if enabling this flag, and performing rebuilds would move all the pages in mixed extends to dedicated extends. Paul stated yes it would. However one thing I would note is his second question, on if it was worth worrying about for existing tables and indexes. Paul stated, no, since mixed pages only really make a difference in performance is in tempdb.

So you are not likely going to see a great deal of performance increase by worrying about the current pages that are mixed. Just do your normal index maintenance and at some point they will get moved.


There can be benefit in creating indexes on temporary tables, but maybe not for a staging table. It's an "it depends" answer, unfortunately. You will need to test. If you posted the code for how you are interacting with the staging table, we could help determine if any indexes would help. An example of where an index might help is if you were joining the temp table to another table. If you were to index the joined column, there could be performance gains, especially if there are a lot of rows in the temp table.

You probably do not need to update statistics on the temporary tables. It's also an "it depends" answer, though I've never seen an update stats on temp tables in any of the thousands upon thousands of stored procedures I've looked at, nor have I needed to add it to resolve a performance issue.


I Want To insert data in transaction table and at the same time update customer table in codeigniter

Codeigniter does not support join for update in active records. You need to update your code as follows:

public function add($_iN, $_iC, $desc, $q, $qs, $_up, $_tp, $change_due,
$cn) {
$data = ['itemName' => $_iN, 'itemCode' => $_iC, 'description' => $desc,
'quantity' => $q, 'quantitys' => $qs, 'unitPrice' => $_up,
'totalPrice' => $_tp,'changeDue' => $change_due,'cust_id'=>$cn];
     $this->db->insert('transactions', $data);
     $updateSuccess=$this->db->query('customer JOIN transactions ON transactions.cust_id = customer.customer_id SET customer.now_debt = customer.now_debt + ? where customer_id = ? ' , array($change_due,$cn));
    if ($updateSuccess) {
        return $this->db->insert_id();
    }
    else {
        log_message('error',$this->db->error());
        return FALSE;
    }
}


What are my options for GUI based Administration & Management of PostgreSQL

you can check EnterpriseDB PEM. http://www.enterprisedb.com/products-services-training/products/postgres-enterprise-manager


In PostgreSQL 9.3 , UNION VIEW with WHERE CLAUSE not taken into account

If the view isn't pushing down the WHERE predicates, maybe a db function that dynamically creates the necessary query and only included the Foreign Tables (or shards) that are necessary?

If you need distributed Postgres, have you looked into Postgres-XC? http://postgresxc.wikia.com/wiki/Postgres-XC_Wiki


iso_datestamp to a MySQL TIMESTAMP so that it can be 5 bytes instead of 15.
Use InnoDB, not MyISAM.  (This is critical to some of the items below.)
Use CHARACTER SET ascii (or latin1) unless you need the multibyte utf8/utf8mb4.
PRIMARY KEY(symbol, datestamp)
An auto_increment is a waste of space and indexing.  The above is a 'natural PK' and it provides an efficient index.
PARTITION BY RANGE(TO_DAYS(datestamp)) -- this will significantly speed up deletion of 'old' rows.
With that PK and partitioning, you may not need any secondary indexes, thereby completely eliminating the 6GB you mentioned.  (If you have queries that are not adequately handled, let me know.)
Shoot for no more than about 50 partitions; do not bother with subpartitions.  (See link below)
Use DROP PARTITION for very efficient deleting by date.  (See link below)  If you are deleting by symbol, let me know.
DECIMAL(26,6) takes 12 bytes.  Consider shrinking it and/or moving to an 8-byte DOUBLE.  Caution: Double's ~16 significant digits and binary format may lead to rounding issues.
Consider using a 3-byte MEDIUMINT INT for a symbol_id and normalize the actual symbol name into another (small) table.
Metrics (eg, "alpha") should probably be 4-byte FLOATs.  It's 7 significant digits is overkill for such.
Set innodb_buffer_pool_size to about 70% of available memory (after accounting for apps, web server, etc.)
Insert new data in batches of 100-1000.  This runs literally 10 times as fast as one row at a time.

These changes should help with your various issues.
Currently, performance is falling off a cliff for multiple reasons.  The above tips will delay the cliff for about twice as long, and make the cliff less steep.  In some cases, the cliff is eliminated.
Also, my tips will cut back on the I/O needed -- by clustering, by locality of reference, by better caching, etc.
http://mysql.rjweb.org/doc.php/partitionmaint


What is blocking 'SELECT... WITH(NOLOCK)'?

If the rebuild is not specified as ONLINE (which you can't do except on Enterprise Edition), the SELECT is blocked because of LCK_M_SCH_S, and if you execute sp_lock you will see it is an exclusive lock. Even NOLOCK can't penetrate that. You can simulate this by:

In one window, start a transaction which rebuilds offline (this is so that you can investigate beyond the rebuild instead of trying to force a really long rebuild). Make note of the spid:

USE AdventureWorks2012;
SELECT @@SPID;
BEGIN TRANSACTION;
ALTER TABLE Sales.SalesOrderHeader REBUILD WITH (ONLINE = OFF);

In a second window, execute your NOLOCK query (again, make note of the spid):

SELECT @@SPID;
SELECT * FROM Sales.SalesOrderHeader WITH (NOLOCK);

In a third window, check:

SELECT session_id, blocking_session_id, last_wait_type
  FROM sys.dm_exec_requests
  WHERE blocking_session_id IN (two spids above)
     OR session_id IN (two spids above);

EXEC sp_lock;

Scan the second resultset for all of the rows involved with the spid that is the main blocker.

Don't forget to rollback or commit...


Please note that the official documentation is the official source of information.
I took a brief look and this shouldn't be taken as an extreme look from every possible angle.

The wait type is benign as it's a permanent task that which is persisting QDS data in the background. The wait shows up and is incurred while the instance is up and running, it will have more incurred wait if there are items to persist and lower if there are less items to persist.

So, overall, unless there are other issues going on, this wait type can be ignored.


How to calculate differences between maximum value and current value for each row?

The MAX value you get in an subselect, the rest is simple math

CREATE TABLE Sales
    (`region` varchar(7), `sales` varchar(6))
;

INSERT INTO Sales
    (`region`, `sales`)
VALUES
    ('East', '1200'),
    ('Midwest', '(Null)'),
    ('North', '2500'),
    ('South', '4000'),
    ('West', '2400')
;

SELECT
`region`
, `sales`
,(SELECT MAX(`sales` ) FROM Sales) - `sales` AS diff
FROM Sales

region  | sales  | diff
:------ | :----- | ---:
East    | 1200   | 2800
Midwest | (Null) | 4000
North   | 2500   | 1500
South   | 4000   |    0
West    | 2400   | 1600

db<>fiddle here


How to replace to_tsquery by phraseto_tsquery in PostgreSQL?

You cannot do that with phraseto_tsquery.
The lower-level functions provide more features that you cannot get with phraseto_tsquery or websearch_to_tsquery.


Azure SQL Hyperscale, Very Large Tables, and lack of explicit partitioning

I spoke directly to MS on this.  Their answer is that there is no promises - the bad situation I described could well happen.  They don't give any SLAs on performance on Hyperscale.  There is no way to influence how it shards the data between page servers.


How does the optimizer in Oracle Database use I/O statistics?

You can see currently used statistics in SYS.AUX_STATS$.
DBMS_STATS.GATHER_SYSTEM_STATS collects system statistics. Running it with gathering_mode='NOWORKLOAD' will capture IO related stats of the server. You usually also run it under workload ("START/STOP", "INTERVAL").
(4,5)  Collecting system statistics during typical load should take care of potential problems you mentioned.
In addition , (4) will be affected by optimizer mode (ALL_ROWS vs FIRST_ROWS)

Also, with procedures in dbms_stats packages it's possible to collect stats multiple times, export (EXPORT_SYSTEM_STATS ), import (EXPORT_SYSTEM_STATS), or delete it (DELETE_SYSTEM_STATS).


Is this the right attitude from the DBA team?

I agree with ypercube - the best course of action would be getting a test environment with a recent backup of production data. You say you have SELECT permissions in production so it doesn't seem as if information is being kept confidential from the dev team. If this is possible, you would be able to quickly test your procedures and find possible improvements.

I disagree with your assumption that it is the DBA's job to optimize an inefficient query. Part of the role of the developer is to write queries and/or code with performance in mind.

However, I also disagree with the DBAs that all queries should run in under 3 minutes with no exceptions. That is an absurd statement if you are writing complex reports against millions of records.

Sounds like there needs to be some give and take from both sides. You may be able to make your queries more efficient, but the DBAs need to give you the tools to assist with that - mainly a test version of current production data. Also, if the queries are indeed performing poorly then the DBAs should provide you with trace logs and other information to help you pinpoint any possible hangups with your query.


To do what you want first step is generate a list of Dates  then join that reservation table to the generate list of dates.

Here is a thread on how to Generate list of Dates in MSSQL

From this result set select where List of dates table failed to joined to the reservation table.  The reservation columns will be filled with NULLs

To write a sample  query for you, have to know the structure the table and unique keys used to identify the records.


Copy real time data from Azure SQL into Azure Data Warehouse

You may want to consider using Striim as mentioned here by Microsoft Program Manager of Azure SQL Data Warehouse. Ellis Butterfield.


Postgres - Create table with foreign key constraint too slow

Your query got locked waiting for something - my bet is that it was waiting for some other transaction to finish.

When you're doing the create table, just issue (in another psql session):

select * from pg_locks where pid = XXX and not granted;

where xxx is pid of backend doing the create table.

This will show you what is the lock that the create table is waiting for.


As you said

  1) Import a sheet with only headers and sample data (to "seed" the
  data type) into a new table
  2) Use Design view to correct data types
  as necessary
  3) Import the sheet with all the headers and data

I would like to say that if you want to import excel sheet with header data then through TSQL is the best option to do that.

First you have to check in SSMS tools there is ODBC driver are there or not in Server Objects->Linked Servers->MICROSOFT.ACE.OLEDB.12.0 or other microsoft ACE OLEDB driver. it depends on your SQL Server version. In My case it is SQL Server 2012.

Suppose that if you are using SQL Server 2012 and there is MICROSOFT.ACE.OLEDB.12.0 then make sure 'Allow inprocess' is Enable.

How You shall do that

Right click over MICROSOFT.ACE.OLEDB.12.0 driver and in new window there will be
'Allow inprocess' option. check to that option like that

write down in your SSMS this TSQL statement like that, through this query you shall import your execl column header with your data.

Use DatabaseName;
        Go
        select * INTO [User]
        from openrowset('MICROSOFT.ACE.OLEDB.12.0',
        'Excel 12.0; Database=C:\TestFolder\User.xlsx; HDR=yes; IMEX=1',
        'select * from [User$]');
        Go

Here DatabaseName is the Name of Database.
User is the Table Name .
User.xlsx is the Excel sheet , which you will import.
Make sure that HDR='yes' which will contains your header column names.

I hope so that it will helpful to you . To import your Excel sheet data in SQL Server through SSMS tools & TSQL Script.


AWS Webapp tutorial: how to choose source in database security group?

A couple of things:

The tutorial assumes that you have already created an EC2 Security Group named WebServerSG, as shown in the previous step of the tutorial.
As of this writing, the auto-complete logic for the field in question keys off of the security group's Group ID rather than the Group Name. The tutorial incorrectly suggests the opposite. You can verify this by typing sg- (all Group IDs begin with sg-) and seeing the drop-down show all of your existing security groups.

Here's how it looks when I try it:

Security Groups are an abstraction that define inbound and outbound traffic rules that you can apply to instances.

The purpose of this exercise is to allow inbound traffic associated with MySQL (we can tell this because the inbound port in the listed rule is 3306 -- commonly associated with MySQL) from your web server(s) to your database server(s).

As demonstrated in the tutorial, you can reference other security groups in the rule definitions to build complex (or in this case, common -- web application servers regularly communicate with database servers) access patterns.


the result of REPLACE and CONVERT is a VARCHAR(8000) - how can I have a VARCHAR(50) as a result instead?

From the docs on REPLACE():

  If string_expression is not of type varchar(max) or nvarchar(max), REPLACE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, string_expression must be explicitly cast to a large-value data type.

This implies to me that it will always be varchar(8000) or nvarchar(4000) when the input isn't a max type. So try performing an additional CONVERT after the REPLACE:

SELECT
  [Rows Formated] = CONVERT(varchar(50),REPLACE(CONVERT(varchar(50),
    CAST(sp.[Rows] AS MONEY),1), '.00',''))
INTO #radhe
FROM @t AS sp;


Why can it take up to 30 seconds to create a simple CCI rowgroup?

In many respects, this is expected behaviour. Any set of compression routines will have widely ranging performance depending on input data distribution. We expect to trade data loading speed for storage size and runtime querying performance.

There is a definite limit to how detailed an answer you're going to get here, since VertiPaq is a proprietary implementation, and the details are a closely-guarded secret. Even so, we do know that VertiPaq contains routines for:

Value encoding (scaling and/or translating values to fit in a small number of bits)
Dictionary encoding (integer references to unique values)
Run Length Encoding (storing runs of repeated values as [value, count] pairs)
Bit-packing (storing the stream in as few bits as possible)

Typically, data will be value or dictionary encoded, then RLE or bit-packing will be applied (or a hybrid of RLE and bit-packing used on different subsections of the segment data). The process of deciding which techniques to apply can involve generating a histogram to help determine how maximum bit savings can be achieved.

Capturing the slow case with Windows Performance Recorder and analyzing the result with Windows Performance Analyzer, we can see that the vast majority of the execution time is consumed in looking at the clustering of the data, building histograms, and deciding how to partition it for best savings:

The most expensive processing occurs for values that appear at least 64 times in the segment. This is a heuristic to determine when pure RLE is likely to be beneficial. The faster cases result in impure storage e.g. a bit-packed representation, with a larger final storage size. In the hybrid cases, values with 64 or more repetitions are RLE encoded, and the remainder are bit-packed.

The longest duration occurs when the maximum number of distinct values with 64 repetitions appear in the largest possible segment i.e. 1,048,576 rows with 16,384 sets of values with 64 entries each. Inspection of the code reveals a hard-coded time limit for the expensive processing. This can be configured in other VertiPaq implementations e.g. SSAS, but not in SQL Server as far as I can tell.

Some insight into the final storage arrangement can be acquired using the undocumented DBCC CSINDEX command. This shows the RLE header and array entries, any bookmarks into the RLE data, and a brief summary of the bit-pack data (if any).

For more information, see:

The VertiPaq Engine in DAX by Alberto Ferrari and Marco Russo
Microsoft Patent WO2015038442: Processing datasets with a DBMS engine
Microsoft Patent WO2010039898: Efficient large-scale filtering and/or sorting for querying of column based data encoded structures


You could avoid the correlated subquery with a filtering join, like:

select  *
from    leads l
join    users u
on      l.client_id = u.id
join    (
        select  lender_id
        ,       client_id
        ,       max(price) as max_price
        from    leads
        where   disabled = 0
        group by
                lender_id
        ,       client_id
        ) tl
on      tl.lender_id = l.lender_id
        and tl.client_id = l.client_id
        and tl.max_price = l.price
WHERE   l.lender_id = 1
        and l.claimed_date is null
        and l.disabled = 0
        and u.last_activity_date > '2012-01-13 02:42:10'
group by
        l.client_id

If there is more than one lead with the highest price, this version will return all of them.


after some research and consult with other DBA friends concluded that it is a temporary table as any temporary table is named in this pattern only.


Creating a TYPE that is an Array within a Function (or a predefined Array TYPE) in PostgreSQL

You can just use varchar(30)[]. Example:

DO
$$
DECLARE
   var_string varchar(30)[] := '{a,b,c,d,e,f,g,h,i,j}';
BEGIN
   RAISE NOTICE '%', var_string;
END
$$;

(Assigning at declaration time is optional; defaults to NULL.)
(Lower case identifiers are advisable for Postgres - in contrast to Oracle.)

The manual on array declaration.

Of course, the number of elements is not limited to 10 for the basic type. Do you even need that? And do you need to enforce a maximum string length of 30? Typically a plain text array text[] should do the job.

You can specify the size of the array:

varchar(30)[10]

But that's not effective beyond documentation. The manual:

  However, the current implementation ignores any supplied array size
  limits, i.e., the behavior is the same as for arrays of unspecified length.

To positively enforce a maximum of 10 elements, you could create a DOMAIN over the basic type with a CHECK constraint.

CREATE DOMAIN varchar30_arr_max_10_elem AS varchar(30)[]
CHECK(cardinality(VALUE) <= 10);

Then use varchar30_arr_max_10_elem like any other type.

Related:

Issues with PL/pgSQL code

Aside: Why use the name var_string for an array? It's not a string.


The documentation tells you:

  A joined table is a table derived from two other (real or derived) tables according to the rules of the particular join type. Inner, outer, and cross-joins are available. The general syntax of a joined table is

T1 join_type T2 [ join_condition ]

  Joins of all types can be chained together, or nested: either or both T1 and T2 can be joined tables. Parentheses can be used around JOIN clauses to control the join order. In the absence of parentheses, JOIN clauses nest left-to-right.

The only way to parse the expression

a JOIN b JOIN c ON cond2 ON cond1

that is in line with the above syntax is

a JOIN (b JOIN c ON cond2) ON cond1

so if your code doesn't throw a syntax error, that's how it must be interpreted.


Why does a rollback of a CREATE INDEX statement take longer than expected?

I believe what you are looking for is in the warning section of this MSDN reference under the Managing Heaps section.

  Creating or dropping clustered indexes requires rewriting the entire table. If the table has nonclustered indexes, all the nonclustered indexes must all be recreated whenever the clustered index is changed. Therefore, changing from a heap to a clustered index structure or back can take a lot of time and require disk space for reordering data in tempdb.

Your tempdb is going to play a critical role here and if you already have contention there with normal production requests maintenance tasks like indexing or de-indexing could take significantly more time to complete.  And as Kin stated your rollback is probably single threaded vs multi-threaded with the create and alter statements.


UPDATE YourTable
SET YourColumn = 'Latest Developer'
WHERE YourColumn COLLATE LATIN1_GENERAL_CS_AS = 'LATEST DEVELOPER'


This problem can be solved using accent insensitive collations.

Your database is probably using a AS (Accent Sensitive) collation so by default it will search for the exact match including accents.

You could instruct the WHERE clause to use another collation than the database default by specifying a collation with the comparison.

In this dbfiddle I created an example using the LATIN1 collations but you could use the same approach with the collation you are using by just changing AS into AI for the collation your column is currently using.

Use the Accent Insensitive collation that matches the collation the colummn is using. For example if the column is using SQL_Latin1_General_CP1_CI_AS, use SQL_Latin1_General_CP1_CI_AI and not Latin1_General_CI_AS or Latin1_General_100_CI_AS or any of the variations of those two since the behavior of the non-SQL_ collations will differ in more ways than just accent-insensitivity, and that might not be expected by users.

You can check the current collation in sys.columns.

CREATE TABLE testaccent (name nvarchar(50));
GO
INSERT INTO testaccent (name) VALUES ('Millière') , ('Milliere');
GO
-- returns Miliere
SELECT * FROM testaccent WHERE name = 'Milliere';

-- returns both
SELECT * FROM testaccent WHERE name='Milliere' COLLATE Latin1_General_CI_AI

--only returns Miliere
SELECT * FROM testaccent WHERE name='Milliere' COLLATE Latin1_General_CI_AS

Read through Using SQL Server Collations for more information.

Then again you'd probably want sorting to use this collation (as peufeu noted in the comments) to ensure that "é" sorts with "e". Otherwise, someone who paginates through results in alphabetical order would be surprised not to find the "é" where they expect them to be, but if you only want to touch this query you can add the COLLATE clause to the ORDER BY too.

As noted by Solomon Rutzky in the comments, if this only affects 1 or a few columns, another option is to create a non-persisted computed column that simply repeats the "name" column and provides the accent insensitive collation, and then index the computed column. This avoids the scan caused by changing the collation within the query. Then the query needs to filter on the new column.

Something like:

ALTER TABLE
dbo.[table_name] ADD [SearchName] datatype_of_name_column
AS ([Name] COLLATE LATIN1_GENERAL_100_CI_AI));

CREATE INDEX [IX_table_name_SearchName]
ON dbo.[table_name] ([SearchName] ASC);

Or you could also create a view instead of adding a computed column (as jyao prefers).


Unable to backup to a UNC path with a gMSA

If you check the documentation for Ola Hallengren's solution, you'll see under "Which permissions are needed for the SQL Server Maintenance Solution to work?" the following:

  DatabaseBackup: sysadmin
  DatabaseIntegrityCheck: EXECUTE on dbo.DatabaseIntegrityCheck, VIEW DEFINITION on dbo.CommandExecute, VIEW DEFINITION on dbo.CommandLog, VIEW SERVER STATE, db_owner on all target databases
  IndexOptimize: EXECUTE on dbo.IndexOptimize, VIEW DEFINITION on dbo.CommandExecute, VIEW DEFINITION on dbo.CommandLog, VIEW SERVER STATE, db_owner on all target databases

I believe the reason sysadmin is required is because the solution uses xp_fileexist to check the existence of a directory, and this extended stored procedure requires sysadmin rights.

Normally, the jobs are executed by a SQL Agent which has sysadmin rights by default granted to the per-service sid NT SERVICE\SQLServerAgent (or NT SERVICE\SQLAgent$instance_name for a named instance). If you use a proxy to run the jobs in SQL Agent, that proxy also needs sysadmin, as does the user account running the procedure from any other scheduler, as is your described scenario.


After renaming postgres user, getting "role 'postgres' does not exist" in logs on PostgreSQL startup

So after much more research and help from users here, it sounds like renaming your postgres DB user isn't a good idea. You'll need to backup, re-initdb, restore, and some other stuff.


MYSQL Datediff between two dates based on year


Can i Make Index for all columns(No write operations)?

This all depends on the size of the table. You're doing a full table scan without a where clause so an index isn't going to help. The use of the rand() function for sorting is going to produce a temp table and this will harm performance. If you have a primary key you could generate a random number in the app and perform a query where a single row is selected and can make use of the clustered index. You're likely to be serving this all from memory if you're using the InnoDB storage engine.


You don't want your test database to retain the same internal id number as the production database. Hence it would be best to use RMAN's command DUPLICATE, because it sets a different id (and also a new database name). This command is specifically designed to do what you require.


Indexes with High Lock waits - how to fix

Side note first - hmm, there's something a little suspicious in those results. They all say the same table, but at the same time, they say "NC indexes on table: 1". That means we think there's only 1 index on the table - but you're showing 4 here. These could be in different databases perhaps, or maybe there's a bug in the sp_BlitzIndex code.

That aside, you have a couple of different questions in here.

How do I know if I have a blocking problem? Look at wait stats, which tracks what your SQL Server has been waiting on. My personal favorite tool for that is sp_Blitz @CheckServerInfo = 1, @OutputType = 'markdown' because you can post the results here on Stack in your question. (Disclaimer: I work for the company that started those scripts.) One of the sections will show your most significant waits - focus on those first. If your primary wait types include LCK*, then you probably have a blocking issue.

How do I find queries that are taking locks? This one can be kinda tough without monitoring software. I'd start with sp_BlitzCache @SortOrder = 'duration'  That'll show you the longest-running queries, which can sometimes point out queries involved in blocking. You can't really filter by table, though, at least not quickly.

How do I design the right indexes to support those queries? That's kind of a big question. Generally, you want as many indexes as you need to support your workloads, but not so many that delete/update/insert (DUI) operations slow down due to the wide number of locks they have to get. If your table really does only have one index, then DUI queries are probably doing table scans in order to find the rows they want to update. It's time to look at how you're accessing those tables when you do updates.

For example, say you have the classic white pages of the phone book - organized by last name, first name. If you try to update all users whose first name is 'Brent', you're going to have to scan the entire phone book, and you can end up with some nasty locking situations. You'd want to create a separate index on first name so that you can quickly find the rows you need to update, and then get out of the table.


database inaccessible and stuck in startup state

What do I want now?
  Remove these databases in any way possible, so that they don't consume any resources. I don't mind trying unconventional methods.

To remove them just stop SQL Server and delete MDF and LOG files. I assume you have good backups.


Transactional Replication: can create 52 publication, but creating 53rd leads to problems

I believe you are running into the desktop heap issue described here (support.microsoft.com/kb/949296) and here (support.microsoft.com/kb/824422).


This might be a good start: http://www.sqlservercentral.com/Scripts/


MongoDB - sharding an collection with data already in it

When you shard an existing collection, MongoDB will split the values of the shard key into chunk ranges based on data size and start rebalancing across shards based on the chunk distribution. Rebalancing a large collection can be very resource intensive so you should consider the timing and impact on your production deployment.

In MongoDB 2.6 (which reached end of life in October, 2016) the balancer will only perform one chunk migration at a time. In MongoDB 3.4 or newer the balancer can perform parallel chunk migrations with the restriction that each shard can participate in at most one migration at a time. As at MongoDB 3.6, that means that a sharded cluster with n shards can perform at most n/2 (rounded down) simultaneous chunk migrations.

A faster approach to sharding a large existing collection would be to pre-split chunks in an empty collection based on the current data distribution for your shard key, and to then dump & restore your existing data into this new sharded collection. The pre-split approach minimizes rebalancing activities by creating appropriate chunk ranges so data is distributed on insert.

There have been significant performance improvements since MongoDB 2.6, so I would strongly recommend upgrading to a supported version of MongoDB (ideally MongoDB 3.4 or newer).


Shard key selection for both write scaling and query isolation?

The documentation provides an answer through kind of a back-door. As you mentioned, for shards:

For write optimization, choose a shard-key that has high cardinality.
For query isolation, choose a shard-key that allows the mongos instance to route the query to as few shards as possible.

It goes into a few more details, which are relevant to what you're looking for.

When attempting query isolation, always include the full shard-key in queries as it allows mongos to provide optimization. If you don't, it will query all shards.
If your preferred shard-key is not sufficiently cardinal, adding another field that does have sufficient cardinality to create a compound key will allow more efficient write-spreading.

The easiest gain here is to include the shard-key in all queries, even if you don't need the data in the query. This allows you to get the benefits of optimization without having to rewrite your query system to include cluster metadata.

You certainly can write a query-optimizer that uses cluster metadata. It is very possible to get the number of chunks in a specific collection and even get the chunk boundaries. You'll have to track the state of chunks in your collection since they will update from time to time, but it would allow you to write queries that only hit one shard at a time.

However, that's a lot of work and the gains you get may not be worth the extra engineering effort. Only you can tell if that's an optimization that's worth it to pursue.


ORA-01031 Insufficient Privileges Raised From Within Function

When you have permission to perform an action at the command line but not within a definer's rights stored procedure (and I hope you're using a stored procedure here, not a stored function), the problem is almost certainly that the privilege you need has been granted through a role, not directly to the user that owns the procedure.  Inside a definer's rights stored procedure, only privileges that have been granted directly to the owner of the procedure can be used-- privileges granted through a role (such as DBA) cannot be used.

You can verify that the problem is, in fact, that the privilege is granted through a role by disabling roles and then trying to create the user, i.e.

SQL> set role none;
SQL> create user ...

Assuming that fails, as I expect it will, you would need to grant the owner of the function the CREATE USER privilege directly

SQL> grant create user to <<owner of procedure>>

Then, you should be able to create users even with roles disabled and the stored procedure should work.


Increasing the size of the Oracle datafile

That does not increase the size of a data file, it adds one more datafile to the tablespace.
To enlarge an existing data file (for example to 20G):
alter database datafile '/oradata/ovbs/user_dataXX.dbf' resize 20g;

To add a new datafile to that tablespace (10G)
alter tablespace user_data_tbs add datafile '/oradata/ovbs/user_dataYY.dbf' size 10g;

Another option is to make the original datafile auto extensible:
alter database datafile '/oradata/ovbs/user_dataXX.dbf' autoextend on;

That way, the database will automatically extend the datafile as necessary, until the file system it resides on becomes full
The size of datafiles is totally transparent to applications. See https://docs.oracle.com/en/database/oracle/oracle-database/21/admin/managing-tablespaces.html#GUID-C207AC96-0F99-4F05-B3DD-E4A4FA17F98C for details


Yep, you don't need HAProxy with the newest versions pgpool-II. I'm not sure in which version it is introduced, but "watchdog" feature that is now embedded in pgpool does everything needed for automatic fialover and real HA.

Take a look at this post - it'll help for sure.


Using all the CPU cores by NDBD and MySQL

Solution is rather simple: Run mysqld multiple times on the same DB Server !!!.

Assign each mysqld to run on a different port.

The OS will spread the multple mysqld processes across the cores.

UPDATE

You may have to jerry rig some port forwarding using mysql-proxy

Assuming 10.240.35.180 is the SQL node

startup mysql on port 3307 in the SQL node
startup mysql on port 3308 in the SQL node
startup mysql on port 3309 in the SQL node
startup mysql on port 3310 in the SQL node

In the mgnt node, set up 4 instances of mysql-proxy

mysql-proxy 10.1.1.6 to 10.240.35.180 port 3307
mysql-proxy 10.1.1.7 to 10.240.35.180 port 3308
mysql-proxy 10.1.1.8 to 10.240.35.180 port 3309
mysql-proxy 10.1.1.9 to 10.240.35.180 port 3310

Now place IPs 10.1.1.6 - 10.1.1.9 in config.ini and startup mysql cluster. This sounds like a very major stretch, but it's your only shot at getting mysqld running across multiple cores.

BTW : back on August 10, 2011, someone asked a similar question in ServerFault and gave a detailed explanantion from the MySQL Cluster Team.

Give it a Try !!!


Database Mirroring protocol TCP ports used. One default, one dynamic?

When an application establishes a TCP connection, it specifies a port for the inbound (receive) port, and uses a (somewhat) randomly selected port for outbound (source, or send).  The inbound port for AG replication is 5022 by default, so all replication traffic is sent TO port 5022.  Accordingly, you see the network reads occurring on port 5022, as SQL Server is listening on port 5022 and reading packets that are sent to that port.

The outbound port, which is selected randomly (more or less), is the port it is sent FROM.  Accordingly, you see the writes associated with the random port (63420 in your example), as SQL Server is writing data to this port to be sent on the connection.

See How are source ports determined... for more


For a small database with just a few rows, you might say that the design is whatever you can maintain and sustain. However, a few things to think about include:

Remember that the first implementation is not the future implementation.  Planning ahead just a little can save headaches in the future.
If the database should scale up in the future you might find yourself refactoring tables and code.
Since in Table3 the columns are named GFA(m2), Residential units, et cetera for whatever you are measuring you, you are committing to changing that table repeatedly as new measures are added.

By the way, I note that your values(float) of 200.3 becomes 200 in the final table. (Maybe just a typo.)  But be sure to keep the data types of the columns defined to fit the data you are storing.

And, yes, if I was delivering this to someone I would normalize it further.


How to detect whether MySQL server is running as Amazon RDS

Try

SELECT @@version;

I believe that Amazon has their own build of  MySQL and  would be apparent there.

EDIT 2012-05-30

@@version does not return a unique string, but it appears that all RDS instances have the same basedir/datadir/tmpdir, etc, so you could look at @@basedir and see if it matches /rdsdbbin/mysql/:

mysql> select @@basedir;
+------------------+
| @@basedir        |
+------------------+
| /rdsdbbin/mysql/ |
+------------------+
1 row in set (0.00 sec)

This is not 100% definitive, but chances are pretty high that if the basedir is set to something like rdsdbbin, then it's an RDS instance.


"parameter name used more than once" error when declaring function - why this is happening?

You've defined an input variable and a return variable with the same name, so it can't be disambiguated by PL/PGSQL, as the error message is telling you.

You can change it to returns table (c integer, d integer) as well, or you can create a type for this, like CREATE TYPE mytype (a integer, b integer) and then use it in your function as returns setof mytype, which should give you the behavior you're looking for, without colliding variables.


Aley,

GRANT ALL ON my_db.* TO my_user@127.0.0.1;

is a superset of:

GRANT SELECT, INSERT, UPDATE, DELETE ON my_db.* TO my_user@127.0.0.1;

So the last one is superfluous. You must understand that SHOW GRANTS do not show every grant query that you executed, and that in some specific cases, it may not show all grants for a user. That is something that can be very confusing in MySQL.

Also, another tip is that you do not need FLUSH PRIVILEGES; if you only use GRANT/CREATE/DROP keywords, only if you modify directly the mysql.* tables.


Taking just the core part that identifies the id of the row to return, the following query encapsulates the logic needed:
SELECT TOP (1)
    MQ.id
FROM dbo.messagequeue AS MQ
WHERE
    -- Current row
    MQ.processed = 0
    AND MQ.failed = 0
    AND
    (
        EXISTS
        (
            -- Previous row in strict sequence
            SELECT *
            FROM dbo.messagequeue AS MQ2
            WHERE
                MQ2.testrunident = MQ.testrunident
                AND MQ2.processed = 1
                AND MQ2.failed = 0
                AND MQ2.filesequence = MQ.filesequence - 1
        )
        OR MQ.filesequence = 0
    )
ORDER BY
    MQ.testrunident ASC,
    MQ.filesequence ASC;

Executing this query efficiently needs a small change to an existing index, whose definition is currently:
CREATE NONCLUSTERED INDEX [messagequeue_sequenc_failed_idx]
    ON [dbo].[messagequeue]([processed] ASC, [failed] ASC)
    INCLUDE([id], [testrunident], [filesequence]);

The change involves moving testrunident and filesequence from the INCLUDE list to the index keys. The new index still supports all the queries the old one did, and as a side-effect of the change, the redefined index can now be marked as UNIQUE. The following script will perform this change (you can perform this operation ONLINE if you are running Enterprise Edition):
CREATE UNIQUE NONCLUSTERED INDEX [messagequeue_sequenc_failed_idx]
ON [dbo].[messagequeue]
(
    [processed] ASC,
    [failed] ASC,
    testrunident ASC,
    filesequence ASC
)
INCLUDE
(
    [id]
)
WITH
(
    DROP_EXISTING = ON
    --, ONLINE = ON
);

With this index in place, the execution plan for the revised query is:

To return data from the identified row, the final query is a simple extension:
SELECT
    MQ3.id,
    MQ3.testrunident,
    MQ3.filesequence,
    MQ3.processed,
    MQ3.filedata,
    MQ3.retries,
    MQ3.failed,
    MQ3.msgobject,
    MQ3.xmlsource
FROM dbo.messagequeue AS MQ3
WHERE
    MQ3.id =
    (
        SELECT TOP (1)
            MQ.id
        FROM dbo.messagequeue AS MQ
        WHERE
            MQ.processed = 0
            AND MQ.failed = 0
            AND
            (
                EXISTS
                (
                    SELECT *
                    FROM dbo.messagequeue AS MQ2
                    WHERE
                        MQ2.testrunident = MQ.testrunident
                        AND MQ2.filesequence = MQ.filesequence - 1
                        AND MQ2.processed = 1
                        AND MQ2.failed = 0
                )
                OR MQ.filesequence = 0
            )
        ORDER BY
            MQ.testrunident ASC,
            MQ.filesequence ASC
    );

Second option
There is another option because you are using SQL Server 2012, which introduced the LAG and LEAD window functions:
SELECT TOP (1)
    ML.id
FROM
(
    SELECT
        M.id,
        M.testrunident,
        M.filesequence,
        M.processed,
        M.failed,
        PreviousProcessed = LAG(M.processed) OVER (
            ORDER BY M.testrunident, M.filesequence),
        PreviousFailed = LAG(M.failed) OVER (
            ORDER BY M.testrunident, M.filesequence),
        PreviousFileSequence = LAG(M.filesequence) OVER (
            ORDER BY M.testrunident, M.filesequence)
    FROM dbo.messagequeue AS M
) AS ML
WHERE
    -- Current row
    ML.processed = 0
    AND ML.failed = 0
    -- Previous row in strict order
    AND ML.PreviousProcessed = 1
    AND ML.PreviousFailed = 0
    AND ML.PreviousFileSequence = ML.filesequence - 1
ORDER BY
    ML.testrunident,
    ML.filesequence;

This query also needs an adjustment to an existing index, this time by adding processed and failed as included columns:
CREATE UNIQUE NONCLUSTERED INDEX [IXd_testrun_sequence]
ON [dbo].[messagequeue]
(
    [testrunident] ASC,
    [filesequence] ASC
)
INCLUDE
(
    [processed],
    [failed]
)
WITH
(
    DROP_EXISTING = ON
    --, ONLINE = ON
);

With that index in place, the execution plan is:

I should also mention that your general approach would be unsafe if more than one process were ever working on the same queue at once. For more information about this and general queue table design see Remus Rusanu's excellent article Using Tables As Queues.
Further analysis
The original execution plan showed a big difference between the number of rows SQL Server expected to process, versus the number actually encountered during execution. Opening the plan with SQL Sentry Plan Explorer shows these differences clearly:

SQL Server chose an execution strategy that would have worked well if the number of rows really had been as small as it had estimated:

Unfortunately, the chosen strategy did not scale well when the estimates turned out to be far too low. Parts of the execution plan were actually executed 458,260 times - not a recipe for instant results. Had the SQL Server optimizer known the true numbers, it likely would have chosen a different strategy.

It is tempting to think that a discrepancy between estimated and actual row counts must be due to inaccurate statistical information, but chances are you have reasonably up-to-date single-column automatic statistics on these tables. Improvements might be made by providing additional statistical information, but the root cause of the inaccurate estimates is something quite different in this case.
By default, when SQL Server chooses an execution plan for a query it assumes all potential result rows will be returned to the client. However, when SQL Server sees a TOP operator, it rightly takes the number of rows specified into account when estimating cardinality. This behaviour is known as setting a row goal.
Essentially, a row goal means the optimizer scales back estimates under the Top operator to reflect the fact that fewer rows will be needed than the full potential size of the result set. This scaling implicitly assumes that values of interest are evenly distributed within the set.
For example, say you have a table with 1000 rows, of which 10 satisfy some query predicate. Using statistics, SQL Server knows that 1% of the rows meet the criteria. If you write a query to return the first row that matches, SQL Server assumes that it will need to read 1% of the table (= 10 rows) in order to find that first match. In the worst case, the ten rows that meet the criteria might appear last (in whatever order they are searched) so 990 rows that do not match will be read before SQL Server encounters the one you want.
The improved query (using the improved index) still suffers from this problem to a certain extent, but the effects are much less marked:

The fundamental question we are asking of the optimizer's row goal logic here is: how many rows do we need to check before we find the first one (according to the order by clause specification) that is in sequence, unprocessed, and not marked as having failed. This is a tough question to answer based on the limited statistics SQL Server keeps.
In fact there is very little we can do to communicate all nuances of this complex problem to the optimizer effectively. The best we can do is to provide it with an efficient index that we expect to locate the qualifying row as early as possible.
In this case, that means providing an index that returns unprocessed, un-failed entries in order by clause order. We expect to have to check relatively few of these before finding the first one that is in sequence (i.e. the preceding row exists and is marked as processed and not marked as failed).
Both solutions shown above eliminate the Key Lookup operations seen in the original query because the new index now includes (covers) all needed columns. Also, the new index is likely to find the target row more quickly. The original execution plan scanned index IXd_testrun_sequence in (testrunident, filesequence) order, meaning it would encounter old test runs first, most of which will be marked processed. We are looking for unprocessed rows in the outer query, so this strategy was inefficient (we end up performing the sequence check on 458,260 rows).
Finally, checking for a particular sequence value is much more efficient than finding the maximum of a potentially large set. This is the difference I have highlighted in the previous code as looking for the previous row in strict sequence. There is a semantic difference between these two queries and the solution using MAX shown in the question. My understanding is that you are interested in the first matching row; that row need not be the highest filesequence for the testrunident.


Conversion error on paramater but not variable

I'm wondering if the parameter/variable situation is really just masking an unrelated implicit conversion within your query.  This is a stretch, and someone who's better with the internals of SQL Server may be able to clarify if I'm completely wrong here, but I suspect you are getting different execution plans when switching between the parameter and variable approach.  The differences in the plans may be drastic enough where an implicit varchar → int conversion error is only occurring with the parameterized approach based on join order as opposed to the variable approach as the variable isn't being sniffed properly and an entirely different join order is being used as a result.

To test my theory, throw an OPTION (RECOMPILE) statement at the end of your query when you convert your stored procedure to use a variable and see what happens.  If the error occurs again, you are probably running into a different implicit conversion within your query that only occurs with a particular execution plan, meaning the error you're seeing isn't related to the input, but rather data already in the system and how you're joining tables within your statement.


Using PostgreSQL both with (not only) SQL Workbench - how to apply alternate delimiters

I have asked in the SQL Workbench forum and Thomas Kellerer (author of SQL Workbench) helped me. This is a summary of his answer.

First, my sequence was wrong because -- is a comment, but the delimiter must be a valid (not commented out) character or a valid sequence of characters. This makes finding something suitable for a delimiter in SQL Workbench and still not breaking sql code for psql and pgAdmin impossible.

However, there is a workaround: to execute the script through following command:

WbInclude -file=/some/dir/my_function.sql -delimiter=XXXXXX;

That XXXXXX can be anything that is not in the code. This way you can only execute scripts, not edit them, but that's sufficient for me. Currently, there's no better way to operate the code both in and out SQL Workbench.


On SQL Server Backup: Msg 3271, Level 16, State 1, Server Line 1 A nonrecoverable I/O error occurred on file

So after many google searches and realizing that there was very little space left on the drive that I was trying to back up to, I found the issue.

Resolution: There is not enough space on the drive to fit the backup.

That seems like something that would be pretty easy to display Microsoft!!

Hope this helps someone else


How do I attach a database in SQL Server?

What Is Detach or Attach and How do They Work?

We'll start with detach. When you detach a database in SQL Server, you are taking the database offline and removing it from the SQL Server instance from which you are detaching it. The databases data and log files remain in tact and are left in a consistent state so you can then attach the database at a later point or to another SQL Server instance. Attach connects the data and log files from a database that has been properly detached (or that were copied from a cleanly shut down instance of SQL Server) to an instance of SQL Server and brings the database online.

How Do I Detach a Database?

You can do this in T-SQL or from the SQL Server Management Studio GUI.

In the GUI, you right click on the database you wish to detach, select All Tasks and click on Detach. From there you'll get the detach dialog.  You can choose to drop connections first to forcibly disconnect any active connections and rollback work they were in the middle of executing. You can also choose to update statistics before the detach.

In T-SQL:

-- You don't want to be in the database you are trying to detach
USE Master
GO

-- Optional step to drop all active connections and roll back their work
ALTER DATABASE DatabaseName
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

-- Perform the detach
EXEC sp_detach_db 'DatabaseName'
GO

For the system stored procedure sp_detach_db there are two paramaters that you can pass in optionally:

@skipchecks - acceptable input is 'True' or 'False' if 'True', SQL Server will update statistics before detach. If 'False', it won't. If you don't specify anything here the statistics will be updated in SQL Server 2005 or later.

@keepfulltextindexfile - The default here is 'True' - if this is set to true, the full text index metadata will not be dropped during the detach.

To see a lot more about detach and some more details on the risks I highlight below, the Books Online article for sp_detach_db is a good place to start.

How Do I Attach a Database?

You can also do this in T-SQL or from the SQL Server Management Studio GUI.

(NOTE: If you have the data and log files from a database that was not properly detached, your attach may not work. When detach occurs, the database is brought offline and the log and data files are put into a consistent state. This also happens when a service is cleanly shut down.)

In the GUI, you right click on the top level Databases folder for your instance and select Attach. In the next dialog you would then select the primary data file (.MDF) of the database you wish to attach and ensure you have the other files selected and their appropriate locations specified, and click OK, attaching your database.

In T-SQL the best way to do this in SQL Server 2005 and forward is through the CREATE DATABASE command. This is the method that is supported beyond SQL Server 2012. If you want to see how to use sp_attach_db, you can see that in the books online articles for [sp_attach_db][3] or [sp_attach_single_file_db][4]

When you have your log file and data files available and they are consistent this is the T-SQL approach:

-- Using Create Database and the FOR ATTACH clause to attach

CREATE DATABASE DatabaseName
    ON (FILENAME = 'FilePath\FileName.mdf'), -- Main Data File .mdf
    (FILENAME = 'FilePath\LogFileName.ldf'), -- Log file .ldf
     (FILENAME = 'FilePath\SecondaryDataFile.ndf)  -- Optional - any secondary data files
    FOR ATTACH
GO

You can see more about the Create Database statement in books online as well.

How Do I Detach/Attach in SQL Server Express?

It's actually the same. If you are using SQL Server Management Studio Express you can use the detach/attach dialog in the GUI described above or the T-SQL steps through SSMS Express described above as well. No difference with Express there.

If you don't have SSMS Express, you can download it (Here is the SQL Server 2012 Express version).

Of you can enter into a SQLCMD session and use the same T-SQL constructs described above.

When Should I Consider Doing a Detach or Attach?

First a word on what detach and attach is not meant to be used for: Backup and Recovery Detach and Attach is not a way to backup your database for routine recovery purposes. There are no transaction log backups this way, it puts your database into a state where the database files can be deleted accidentally and is not a good way at all for this purpose.

That said, detach and attach are good for a few use cases (not exhaustive, feel free to edit to add or create a new answer with more):

Sometimes for migrations (although I prefer backup/restore for those as discussed in my answer here)
When you want to remove a database that is no longer actively used but have the ability to attach later as needed.
In certain troubleshooting situations, this may be called upon
Don't have the space to backup or to restore both a data and log files to another environment (you shouldn't ever be here but I've used it to move dev databases around environments at times.. Didn't want or need the log so did an attach/rebuild of the log file)

Risks and Warnings

Again, books online is a good resource here, but I'll call out some specific considerations to have in mind with detaching or attaching a database -

Detach

You are taking your database offline. It won't be accessible anymore. This should be obvious, but worth calling out. This is why it isn't a great backup option.
When your database is online, SQL Server locks the files. I wouldn't recommend trying this to prove me wrong, because there could be some other situation at play, but you typically can't delete a database file (data, secondary data or log file) while SQL Server is online. This is a good thing. When you detach, you have no such protection - this can be a bad thing.
If you are dealing with database corruption and you find some article someplace that has a first step of Detach - it's wrong - if you detach a corrupt database, that may be it. You may not be attaching that database again.
Cutting and pasting your production database files throughout your network is a way to potentially introduce file level corruption.. Another reason I prefer backup/restore when doing migrations.
It might cause a maintenance plan to fail.  The situation is that you have, as I did, set up a maintenance plan to carry out regular backups of all databases without checking best practice. This works fine so you stop thinking about it. Someone else then decides to take a database they're not using offline. The maintenance plan will fail from that point forwards until you modify the maintenance plan by checking the "ignore databases whose state is not online" option in the "Database(s)" dialog. Note that it won't just fail for the offline database - the maintenance plan will fail with an error at the point when it tries to backup the offline database so some online databases might not be backed up.  (different author for this point so treat with suspicion)

Attach
 - Just like you shouldn't run scripts from the internet or accept packages from strangers at the airport, you shouldn't attach a database you got from someone else without some steps to verify it. This database could have code inside of it in triggers, stored procedures, etc. that could compromise your environment. You should review a database you want to attach in a safe, and firewalled environment, not your production system.

What About Different Versions or Editions of SQL Server?

These are no different than the rules around restoring databases between versions. You can generally restore up to the next version for 3 versions (SQL Server 2008 to SQL Server 2012, for example will work. SQL Server 2000 to SQL Server 2012 will not). You cannot go backwards at all via backup/restore or detach/attach - you'd have to script out objects and script out the inserts and do it manually or with a tool that does this. For editions, you can generally move between the main SKUs of SQL Server - for instance you can move a database from Standard to Enterprise with no extra work. If you are using Enterprise features (Say, compression or partitioning), you'll need to disable those features before you make the move, though. You can get an idea of the features you'd need to consider disable by looking here.


Help me understanding this Exercise Question

I'm not really sure this is truly a DBA question, as it sounds like your problem is understanding the question itself.

The question asks which books generate less than 55% profit. Profit would be the difference between retail and cost. So, the question is, what should the profit be less than 55% of? The basic options are 55% of retail, or 55% of cost. I would assume that retail is intended.

So, you'd need to calculate which books show retail - cost as less than 55% of retail. Then, make a call as to what "recently" means (given orders from just two months, I'd go for all of those orders), and sum the total quantities ordered for each of those books.