Until now, pgBouncer doesn't support rotate log. Hence, you have to do it by yourself.

You can refer to sites below:

How to rotate PgBouncer logs in Linux/Windows ?

How To Manage Log Files With Logrotate On Ubuntu 12.10


You should try printing your commands when they yield errors. If you issued this instead of executing it:

PRINT @cmd;

You would see that this:

WHERE ValueDate BETWEEN ' + CONVERT(VARCHAR,@b,121) + ' AND ' + CONVERT(VARCHAR,@E,121)

Yields this:

WHERE ValueDate BETWEEN 2012-07-01 AND 2014-01-25

Which, since those look to SQL Server like three integers with a couple of subtraction operators, becomes:

WHERE ValueDate BETWEEN 2004 AND 1988

Which isn't even a valid BETWEEN operation, even if it could translate those to dates.

What you should be doing is passing these as proper parameters, e.g.

SET @cmd = 'DELETE FROM dbo.DateTable -- always use SCHEMA prefix
 WHERE ValueDate BETWEEN @b AND @e;';

EXEC sp_executesql @cmd, N'@b DATE, @e DATE', @b, @e;

If any of these columns are DATETIME, not DATE, you really should not be using BETWEEN at all. Instead:

WHERE ValueDate >= @b AND ValueDate < DATEADD(DAY, 1, @e);

Please read:

https://sqlblog.org/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length
https://sqlblog.org/2009/10/11/bad-habits-to-kick-avoiding-the-schema-prefix
https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common
https://sqlblog.org/2009/09/03/ladies-and-gentlemen-start-your-semi-colons

As for the overall requirement of repeating this query against all date columns in the database, here is how I would do it (I've looked at the SO question you reference, and it just shows how to do this for one table):

DECLARE @b DATE = '2012-07-01', @e DATE = '2014-01-25',
        @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'
DELETE ' + QUOTENAME(s.name)
 + '.' + QUOTENAME(t.name) + '
  WHERE ' + QUOTENAME(c.name) + ' >= @b
  AND ' + QUOTENAME(c.name) + ' < DATEADD(DAY, 1, @e);'
 FROM sys.tables AS t
 INNER JOIN sys.schemas AS s
 ON t.[schema_id] = s.[schema_id]
 INNER JOIN sys.columns AS c
 ON t.[object_id] = c.[object_id]
 WHERE c.system_type_id IN (40,42,43,58,61);
       -- or just = 40 if you're only interested in DATE columns

PRINT @sql;
-- EXEC sp_executesql @sql, N'@b DATE, @e DATE', @b, @e;


Ok. I found the solution.

SELECT
  SUM(IF(day1 >0, 1, 0))
  #...
  FROM(
      SELECT
      payment_account_id, sum(day1) AS day1
      #...
        FROM (
           SELECT
             payment_account_id,
             IF((purchase_date < 1345120085 AND (expires_date > 1345120085 OR expires_date_calculated > 1345120085)), 1, 0) AS day1
            #...
           FROM transaction
           WHERE
             `product_id` IN
             (
               'a',
               'b'
             )
             AND payment_account_id IS NOT NULL
          ) AS tmp
      GROUP BY payment_account_id
     )
     AS tmp1;


Unable to connect to Listener after rebuild

It worked for me after setting Network Connection to TCP and trusting server certificate in the connection properties.


Compatibility level for published database and transactional replication

Do these features depend on the compatibility level of the published database or does this rely only on the version of the server?

The short answer is Yes they do depend but to a certain extent only.

Compatibility level can have an affect on the publishing and subscribing databases, with respect to object ownership and the existence (or not) of a particular schema.

Also, have a look at sp_vupgrade_replication

From BOL:

  SQL Server 2000 supports common schema changes to an existing publication database. You can add columns to, and drop columns from, a published table without dropping and recreating the publications and subscriptions referencing that table.
  Schema changes can be replicated during snapshot replication, transactional replication, and merge replication. Column additions and deletions are implemented at the table level and propagated to all Subscribers that receive data from that table.

  Important:  Schema changes to a published table must be made only through the replication publication properties dialog box in SQL Server Enterprise Manager or through replication stored procedures. Do not make schema changes to published tables using the SQL ALTER TABLE statements in a tool such as SQL Query Analyzer or by using SQL Server Enterprise Manager visual database tools. Changes made to the schema of a published table using these tools will not be propagated to Subscribers.

Refer to :

Compatibility Levels and their effects.
Using Multiple Versions of SQL Server in a Replication Topology


The Active Session Pool allows a limit to be place on the number of sessions that can be active on your instance. It is a part of the Resource Manager. The number possible is defined according to your current resource plan. The default plan allows an unlimited number of sessions. It is activated if resource management is performed in the instance. To find out if this is so then check the resource_manager_plan parameter:

show parameter resource_manager_plan

If there is a value then it is activated. You may see something like DEFAULT_PLAN as the VALUE. You can then query DBA_RSRC_PLAN_DIRECTIVES for the number of active sessions possible:

select GROUP_OR_SUBPLAN, ACTIVE_SESS_POOL_P1
  from DBA_RSRC_PLAN_DIRECTIVES
 where PLAN = 'DEFAULT_PLAN';

You'll get a few rows back relating to the number of groups/subplans that are part of this plan. NULL will indicate unlimited.
I prefer using Enterprise Manager to query and amend this. To find it in EM go to the Server tab then under the Resource Manager heading is Plans.

More information on the Resource Manager can be found here.


Off the top of my head, these are the improvements I would try:

Add a clustered (unique if possible) index to the temp table, #tblAssets (iAssetId). This will eliminate the Sort operator, and may potentially improve the type of join operator.
On the index IX_VehicleMonitoringLog_iAssetId_eEventCode_NonClustered, include the column iVehicleMonitoringId. This will eliminate the KeyLookup operator, which is potentially very expensive.

Not so much an optimization, but rather for correctness: Shouldn't this...

PARTITION BY ignOn.iAssetId, ignOn.dtUTCDateTime ORDER BY ignOff.dtUTCDateTime

... rather be...

PARTITION BY ignOn.iAssetId, ignOn.dtUTCDateTime ORDER BY p.dtUTCDateTime?

I'm thinking that for all the records of any given partition, the ignOff.dtUTCDateTime should be the same, and you probably want to loop through each "segment", i.e. the p.dtUTCDateTime, right?

You may already get the correct results by accident with the current query because the records may be correctly ordered from the Index Seek on p, but I would probably want to make sure by specifying the correct ordering. As a bonus, this might improve performance.


Index planning for many columns with different usage

If the only thing that inserts / updates data in this table is an out-of-hours process I'd be tempted to apply as many indexes as you can.  999 are allowed for a SQL Server 2012 table.  You can determine how many you need using the Database Tuning Advisor.

Obviously this will slow down the load process and consume a lot more disk space -  both from the indexes and the additional log data - but you can create a script to Disable the indexes prior to the load & Enable them afterwards to speed things up.

If disabling indexes isn't available to you you can create a script to drop & re-create them.


Get name column instead of ids from two foreign keys

You need to use 2 copies of names table:

SELECT groups_1.name AS gname, groups_2.name AS pname
FROM inheritance
JOIN groups AS groups_1 ON inheritance.gid = groups_1.id
JOIN groups AS groups_2 ON inheritance.pid = groups_2.id


How to Identify Multiple Insert Rows from Single Transaction

I would suggest using SEQUENCE
It is simple and the sequence value always increases in one way

-- you only need to create it once
create sequence seq as int start with 1
go

-- you can then do this
BEGIN TRANSACTION;
DECLARE @BatchID int = next value for seq
INSERT INTO dbo.Test2
       SELECT *, @BatchID AS [batch_id]
       FROM Test;
COMMIT TRANSACTION;


How to decipher entity relationships in a Physical Data Model given the corresponding Logical Data Model

I'm not sure that I understood your question...

That said, lemme try to help you.

I undestand the model represents the categorization of 'parties' as begin part of 'segments'. These categorizations are mutable and have to be traceable in time.

Let me humbly present what I think is a more complete LDM, where

PARTY is explicited
Segment Group Cd is exported as part of the identifying relationship (a solid line)
the verbs sentences are more verbose

From this LDM, the straightest derived 3NF (3rd Normal Form) PDM should be (PARTY entity not shown):

The PDM you posted:

is denormalized - MRKT_SGMNT_GROUP comprises both MRKT_SGMNT and MRKT_SGMNT_GROUP entities
is incomplete - it's missing Party_Sgmnt_End_Dttm
states redundant relationships - the two relationships are the same
or, more formalistically, states an incorrect relationship - the identifying relationship (the one represented as a solid line) yelds both key properties from MRKT_SEGMNT_GROUP as key properties to PARTY_SGMNT_HIST, which aren't - just Sgmnt_Id has been derived as part of the PK

With the denormalization presented, the PDM ought to be, with the missing property marked in bold red:

Did this serve as any help? Hope so...

Regards, Marcus Vinicius.


How to partition a table by timestamp, where data from every i'th day goes to partition i?

You can't use FROM_UNIXTIME() because hash partitions must be based on an integer expression.  But assuming your timestamp is stored as an integer, you can use DIV to return an integer.

Here's a demo of partitioning in the way you describe:

mysql> create table table_to_partition (
  my_timestamp int unsigned primary key
) partition by hash(my_timestamp DIV (60*60*24)) partitions 3;

mysql> insert into table_to_partition values (unix_timestamp(now()));
mysql> insert into table_to_partition values (unix_timestamp(now()-interval 1 day));
mysql> insert into table_to_partition values (unix_timestamp(now()-interval 2 day));
mysql> insert into table_to_partition values (unix_timestamp(now()-interval 3 day));
mysql> insert into table_to_partition values (unix_timestamp(now()-interval 4 day));
mysql> insert into table_to_partition values (unix_timestamp(now()-interval 5 day));

mysql> select table_name, partition_name, table_rows
from information_schema.partitions where table_name='table_to_partition';

+--------------------+----------------+------------+
| table_name         | partition_name | table_rows |
+--------------------+----------------+------------+
| table_to_partition | p0             |          2 |
| table_to_partition | p1             |          2 |
| table_to_partition | p2             |          2 |
+--------------------+----------------+------------+


Get The Lowest Date if The other Column is Null

Here is what I came up with

SELECT employee_id, from_date, to_date
FROM employees AS e1
WHERE to_date IS NOT NULL -- may not be useful as per comments.
  AND to_date - from_date <= (365 * 3)
  AND EXISTS (
    SELECT
    FROM employees AS e2
    WHERE e1.employee_id = e2.employee_id
    GROUP BY employee_id
    HAVING e1.from_date = min(e2.from_date)
  );


How to speed up a query with a lot of subqueries

I guess that using subqueries inside functions is the main performance problem.

It seems that the functions are fairly simple and you could drop them and easily rewrite the query with joins only:

SELECT
  loan.loan_number,
  loan.borrower_name,
  ...
  CASE WHEN loan.date_waiver_ordered IS NULL
         THEN 'Not Ordered'
       WHEN  MAX(doc.upload_date) >= loan.date_waiver_verified
         THEN 'Not Verified Since Last Upload'
       WHEN  MAX(doc.upload_date) < loan.date_waiver_verified
         THEN 'Verified'
       ELSE NULL
  END  AS get_waiver_status,
  ...

FROM loans_table AS loan

  LEFT JOIN documents_table AS doc             --- joining
    ON  doc.loan_number = loan.loan_number
    AND doc.document_type = 'waiver'

WHERE loan.loan_type IN ('A', 'B', 'C')
  AND loan.filing_date >= TO_DATE('30-OCT-12')
  AND loan.filing_date <  TO_DATE('13-NOV-12')

GROUP BY
    loan.PK,                                   --- the Primary Key and
    loan.loan_type,                            --- all the other needed columns
    loan.filing_date,
    loan.loan_number,
    loan.borrower_name,
    loan.date_waiver_verified ;


Best way to design a database with one person with different roles

Just to help other people with same doubts: the good answer is ypercube's commentary.


Postgres ON DELETE Rule Not Working With WHERE Clause

The manual in Rules on INSERT, UPDATE, and DELETE) describes the INSTEAD mechanism for the context of your rule as:

  Qualification given and INSTEAD

  the query tree from the rule action with the rule qualification an
  the original query tree's qualification; and the original query
  tree with the negated rule qualification added

Overlooking the part emphasized above would be the cause of the unexpected result.
I believe that in your test case, the mentioned DELETE will be transformed by the rule into commands to the same effect as:

UPDATE categories SET deleted_at = NOW()
    WHERE categories.id = categories.id AND deleted_at IS NULL;

DELETE from categories WHERE NOT old.deleted_at IS NULL;

The DELETE removes the row because the UPDATE before it has just set its deleted_at field to non-null.

Note also the categories.id = categories.id condition which is obviously not needed and comes from the misunderstanding that OLD does not reference a specific row like in a trigger, it's meant to be replaced by the table subject to the rule.

The main point to understand is that rules don't look themselves at the rows and so they don't generate a kind of IF-THEN-ELSE construct depending on the row contents. The WHERE clauses of the RULES (the qualifications) are not evaluated by the rule system, they are injected into the commands produced by the rule system and will be evaluated by the commands themselves.

See also What are PostgreSQL RULEs good for? and links within for some interesting insights on rules and why they're hard to use.


When were there changes in the memory settings of SQL Server

You could try reading the sql server error log searching for memory configuration changes.

EXEC xp_readerrorlog 0, 1, "memory"

Example on my test machine:

More info on xp_readerrorlog


How to fix an odd LSBackup job error in Log Shipping?

My suspicion would be that even after removing the Log Shipping configuration the Log Shipping system tables are not being cleaned up properly. I would suggest the following steps from blog.sqlauthority.com for your situation:

1. Update the arguments for the command below and execute on LSPrimary01:

EXEC MASTER.dbo.sp_delete_log_shipping_primary_secondary
@primary_database = N'Primary_Database_Name'
,@secondary_server = N'Secondary_Server_Name'
,@secondary_database = N'Secondary_Database_Name'
GO

2. Update the arguments for the command below and execute on LSSecondary01:

EXEC MASTER.dbo.sp_delete_log_shipping_primary_database @database = N'Primary_Database_Name'
GO

3. Run the query from your question again and examine the results.

If there are still improper entries, consider manually removing them.  An example of something you could do would be this:

SELECT * INTO Tempdb.dbo.TemporaryCopy_log_shipping_primary_databases
FROM msdb.dbo.log_shipping_primary_databases
--please note this only lasts until the database engine is restarted!

UPDATE dbo.log_shipping_primary_databases
SET monitor_server = 'LSPrimary01'
WHERE monitor_server = 'OddServer03'

Before running your own version of the commands above, which will try to clean out the system tables, it's worth considering whether you want to try skipping the GUI when trying to reset the Log Shipping configuration.  If you want to try that, I would recommend scripting out your current configuration (Right-click your database in SQL Server Management Studio --> Choose Properties --> Click Transaction Log Shipping in left sidebar -> Click Script Configuration button on the bottom right).

You can then examine the results for what commands it uses as well as whether you see it inserting the OddServer03 value anywhere.  At that point, you could clean everything up as described in the 3 steps above and then add Log Shipping back via the script you extracted and updated as needed.  That would give you 100% control over what is actually executing and it would be less likely for the OddServer03 to somehow end up in the configuration.

NOTE: If any of the above links break, use Google to search for them: - dba.stackexchange.com site for the title of 'Can I deactivate log shipping jobs without raising errors' - blog.sqlauthority.com site for the title of 'Removing Multiple Databases from Log Shipping'


Translate query using APPLY to run under compatibility level 80

The results from that query will not be scoped to particular database. They will show activity across the whole instance.

In general, it would be quite difficult to provide information at this granularity, because queries can access data from more than one database (Azure SQL Database aside).

In non-trivial queries, accounting for costs on a per-database basis (e.g. after joins and aggregations have been performed) would be pretty much impossible. Some DMVs do provide information about the context database at the time a query was executed, but again, the query might have accessed objects in an entirely different database(s).

The easiest answer, therefore, is to simply run the query in the context of a database that is set to compatibility level 90 or higher, such as tempdb or master. It will not affect the results (aside from not producing an error).

If this is not a suitable solution, please edit your question to clarify.


From Kimberly Tripp's post:

  Typically, PAD_INDEX is not needed unless you have extremely unbalanced activity and are unable to rebalance the tree (meaning you need to achieve high availability).

Read more: http://www.sqlskills.com/BLOGS/KIMBERLY/post/MSDN-Webcast-QA-Index-Defrag-Best-Practices-Fragmentation-Questions-Part-I.aspx

What she means by high availability is that you don't have the ability to run index maintenance jobs that would lock the table.  Personally, I wouldn't recommend ever setting this by default - leave it to DBAs who find that they're unable to manage index maintenance appropriately with any other methods, like using Ola Hallengren's maintenance scripts during off hours.  His scripts are genius - they'll do as much as possible online.


Then it looks like an optimizer's blind spot and you should use the second query.

When there is a condition joining two tables a and b: a.id = b.id and an additional condition a.id > @some_constant, seems like the optimizer uses the "index condition" for where to start the index scan on a (id) index but it doesn't use it for the second index b (id).

So, adding the (redundant) b.id > @some_constant allows it to produce a slightly more efficient plan, skipping a part of the b (id) index as well.

This could be posted as a suggestion for improvement (if it hasn't been already) to the Postgres hackers group.

After the edit, we know there is a FOREIGN KEY constraint from t2 that REFERENCES t1. So the "natural" (equivalent) way to write the query would be:

SELECT
    -- whatever
FROM
    t2
  LEFT JOIN
    t1 ON t1.id = t2.id
WHERE t2.id > -9223372036513411363 ;

Can you try this and tell us the execution plan it produces? There are some transformations that apply only to LEFT (outer) joins and not to inner joins.
Unfortunately this doesn't produce any different plan either.

The OP has posted a question at the Postgres performance list, we can see the whole thread here: PostgreSQL seems to create inefficient plans in simple conditional joins and the reply by David Rowley, which confirms that this is a feature that although it has been considered, hasn't yet been implemented in the optimizer:

  Yes, unfortunately you've done about the only thing that you can do,
  and that's just include both conditions in the query. Is there some
  special reason why you can't just write the t2.id > ... condition in
  the query too? or is the query generated dynamically by some software
  that you have no control over?

  I'd personally quite like to see improvements in this area, and even
  wrote a patch 1 which fixes this problem too. The problem I had when
  proposing the fix for this was that I was unable to report details
  about how many people are hit by this planner limitation. The patch I
  proposed caused a very small impact on planning time for many queries,
  and was thought by many not to apply in enough cases for it to be
  worth slowing down queries which cannot possibly benefit. Of course I
  agree with this, I've no interest in slowing down planning on queries,
  but at the same time understand the annoying poor optimisation in this
  area.

  Although please remember the patch I proposed was merely a first draft
  proposal. Not for production use.


How to properly and effective rename a vaue (column) of a foreign key in both tables?

I would consider using a code for the status. I.e
CREATE TABLE USER_STATUS
( STATUS_CODE CHAR(?) NOT NULL PRIMARY KEY
, STATUS_NAME TEXT NOT NULL UNIQUE
);

The use of char is discouraged by the postgres community, so an alternative would be to use text and an additional constraint:
CREATE TABLE USER_STATUS
( STATUS_CODE TEXT NOT NULL PRIMARY KEY
, STATUS_NAME TEXT NOT NULL UNIQUE
,     CHECK (LENGTH(STATUS_CODE) <= ?)
);

It should be more stable than the name, but more intuitive than an int.
But for your question, I would probably insert the new value:
INSERT INTO USER_STATUS (NAME) VALUES ('Valid');

then update all users:
UPDATE USERS SET STATUS = 'Valid' WHERE STATUS = *Normal';

and finally, remove the old status:
DELETE FROM USER_STATUS WHERE NAME = 'Normal';

For the more general case where there is a hierarchy of tables involved, you traverse them in DFS and insert new rows on the way down. When you reach a leaf you can use update and then delete rows as you climb the tree back up. You can use INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS to obtain the edges in the tree, and then use for example a topological sort to determine the ordering of the tables.
Another advice is to stick with the same name for the attribute throughout the model. I.e:
CREATE TABLE USER_STATUS
( STATUS_CODE ... NOT NULL PRIMARY KEY
, STATUS_NAME TEXT NOT NULL UNIQUE
);

CREATE TABLE USERS
( ...
, STATUS_CODE ... REFERENCES USER_STATUS (STATUS_CODE)
...
);


There are two things you must remember is at play
TRANSACTION ISOLATION
The default for tx_isolation is REPEATABLE-READ.
It is plausible and probable that the update sub set done = done + 2  where main_id =1 is being held up.
In fact, your INSERTs must generate auto_increment values. This requires doing some sort of gap index locking. If other INSERTs are occurring on main and sub,
LOCKS ON DATA
You could potentially catch such delays in the act by running
SHOW ENGINE INNODB STATUS\G

The update sub set done = done + 2  where main_id =1 might be visible at some point due to any row-level locks and/or index-level locks.
Locking of the PRIMARY KEY for auto_increment generation must occur for INSERTs, UPDATEs, and DELETEs. I remember a hefty deadlocking exercise I went through with RedBlueThing

Jun 14, 2011 : Reasons for occasionally slow queries?
Jun 08, 2011 : Will these two queries result in a deadlock if executed in sequence?
Jun 06, 2011 : Trouble deciphering a deadlock in an innodb status log

I actually discussed this in a later post : How to analyse innodb status on deadlock in insert Query?


Starting PostgreSQL server after a HDD crash results in FAILED STATE

The real answer will be in the PostgreSQL logs, in /var/lib/pgsql/data/pg_log.

However, before you take any action: It is vital that you take a file system level copy of your database before attempting repair  if any of your data is valuable to you. See http://wiki.postgresql.org/wiki/Corruption . You must copy the whole data directory. On Fedora that's /var/lib/pgsql/data by default, but verify that's correct for your install.

Based on the logs you've posted you certainly have some degree of database corruption. The storage that the database is on (the hard drive or file system) is most likely damaged. Take a copy NOW, and put it on a different hard drive or system.

Only once you have made a full file-system level copy of your data directory, try using pg_resetxlog to clear the damaged transaction logs and start your database. Even if it starts it is highly likely to be corrupt; you should pg_dump it then re-initdb it and restore the dump to the fresh instance.

If you still can't start it after a pg_resetxlog then post an updated log of the startup attempt after resetxlog. It's possible you'll need to start Pg in stand-alone mode with:

sudo -u postgres postgres --single -D /var/lib/pgsql/data -P -f i postgres

If that works, giving you a backend> prompt, try again after replacing the last "postgres" with the name of the DB you want to connect to. You should be able to SELECT, COPY data from tables, etc.

If that doesn't work, ie you can't start a standalone backend, then it's probably time to restore from backups - since you're sensible enough to have them. If anyone else reading this is in the same position, contact an experienced PostgreSQL consultant to see if they can recover data from your database. Be prepared to pay for their time and expertise.

Your file system is probably damaged

The severity of the damage to the PostgreSQL install suggests that your whole file system is probably damaged. You may wish to consider restoring the whole system from a backup or reinstalling it.

I would not trust this file system, fsck or no fsck.

SMART-test your drive

I also recommend that you run a SMART check on your hard drive with smartctl from smartmontools; assuming it's /dev/hda that'd be smartctl -d ata -a /dev/sda | less . Look for a failed health test, uncorrectable_sectors, a high read error rate, a reallocated_sector_count of more than 2 or 3, or a non-zero current_pending_sector. Run smartctl -d ata -t long /dev/sda to execute a non-destructive self test on your HDD; it won't interrupt normal functioning of the system. When the estimated time has elapsed run smartctl -d ata /dev/sda again and look at the self test log to see if it passed.

If anything looks less than perfect, replace the drive.

In future, consider automating this testing via smartd for early warning of drive failures.

(Content in this post was obsoleted by updates to the question. If you're troubleshooting a similar problem, look at this answer's edit history).


Check constraint to require one of two columns

Expanding on ypercube's question comments:

The first thing you need to do is make the securityID column nullable:

ALTER TABLE dbo.Employee
ALTER COLUMN securityID nvarchar(50) NULL;

Then the required check constraint is quite straightforward:

ALTER TABLE dbo.Employee
ADD CONSTRAINT [CK dbo.Employee securityID or alternateID must not be NULL]
CHECK (securityID IS NOT NULL OR alternateID IS NOT NULL);

Regarding the 'friendly' name I chose for the check constraint, see:

Naming CHECK and UNIQUE Constraints by Greg Low.


percona-online-schema-change is taking a lot of time

You are taking a hash of a phone number?  Why?  Indexing the phone number is arguably better than building a hash and indexing that.  For one thing, the phone number is always(?) shorter than the hash.

Kill the ALTER and rethink the task.

The likely reason for the problem is the randomness of the column being indexed -- Toward the end of the process, it will be slowing down to about 1 disk hit per row.  If you estimate 1ms per disk hit, I would estimate that it might take a week to finish.

What version are you running?  Can it add an index with ALTER=INPLACE?  That might let you (1) add the columns, then (2) in a separate ALTER do the ADD INDEX with ALTER=INPLACE, and not have to worry about it taking a week.

Still I question the need for the columns or the index.

If you need to discuss further, please provide SHOW CREATE TABLE.


Possible to drop table ignoring dependencies?

DROP TABLE example CASCADE;

will drop the table example. and drop all REFERENCES constraints that point into that table.

so after replacing the table you will also need to rebuild the references.

to get the dependencies you could use pg_dump --schema-only and then grep for REFERENCES example(

You will not be able to rebuild the references until the needed data is present in the table.


ALTER TABLE ADD CONSTRAINT failing when executed from function

I think you need:

for _tbl in(select DISTINCT child_table from not_deferrable_constraints) loop

If a table has more than one constraints, your code will try to recreate each one of them many times.

Or just use a single for loop, I don't think you need the nesting.

--Recreate not_deferrable_constraints
for add_constraint in (select constraint_name, fk_column, pk_column, parent_table, child_table
                       from not_deferrable_constraints) loop
    execute 'ALTER TABLE ' ||schema_name||'.'|| add_constraint.child_table || ' ADD CONSTRAINT ' || add_constraint.constraint_name || ' FOREIGN KEY ('|| add_constraint.fk_column ||') REFERENCES '|| add_constraint.parent_table ||' ('|| add_constraint.pk_column ||')';
end loop;


The first—and most important—thing you can do before repairing a corrupted table is to back it up. Never try running a repair operation on a table without backing it up; if you fail, your data might be in worse shape than originally and may even become un-salvageable. Backing up the original table file allows you the option of trying a different technique if your first attempt doesn't work.

Next, shut down the MySQL server. This is because myisamchk makes changes to the table file itself, so you don't want other users accessing or otherwise manipulating the file during the repair operation. Remember, don't just terminate the server with a call to "kill"; instead, use the mysqladmin SHUTDOWN command to ensure that MySQL closes all open tables gracefully and doesn't further compound your problem.

myisamcheck will be enough to get details regarding what is causing table to crash for more details you can refer this http://dev.mysql.com/doc/refman/5.0/en/myisam-check.html


How to export data from sql server 2008 to excel and email as attachement

You can use SSIS (SQL Server Integration Services). Simple example:

Control Flow task 1 - Data Flow:

OLEDB Source. Choose the source table/view/use a query...
Excel destination.

Control Flow task 2 - Send Mail Task. Attach the above generated Excel.

You can build the "Export from SQL to Excel" part also via the SQL Server Import and Export Wizard in BIDS. Check this video for a demo.
For the Send Mail Task, check this BOL article.

If you want to automate and schedule it, you can use a SQL Agent scheduled job with an SSIS job step. A video demo is available here.
If you automate it on a server, make sure Microsoft Access Database Engine 2010 Redistributable is installed (you do not need Excel there, just the "drivers").


Availability mode - manual failover mode, best practice for availability mode?

Can I related the crash to availability mode settings?

No.  By the looks of your log messages, you actually lost cluster quorum due to the removal of two nodes' votes, and then your witness (file share).  Is this a 3 node cluster with a file share witness perchance?  In that case, if you pulled these events from one of the node's event logs, then it may appear to each of the nodes that there is a lack of communication with all voters.  That would generate a similar, if not same, error footprint like you have above.  Nobody can talk to anybody, if that is the case.

During that time, the quorum will be lost as you are currently seeing.  There is a level of assuming here, as I'd need to see way more diagnostic information to pinpoint the cause of voter removal, but that is why the quorum was lost.

Regardless, this appears to be a problem that surfaced in a down cluster, in which case your availability mode would have nothing to do with the WSFC cluster failing.

As for "best practices" for the availability mode to go with, you need to determine requirements for data loss, performance impact, and a few other factors that are best described in this BOL reference on Availability Modes.


Design for scientific data. Data table with hundreds of columns or data table with a generic value column and hundreds of rows (EAV)?

What you are describing is the EAV (Entity Attribute Value) model which most database professionals would run a mile from. It's also sarcastically called the OTLT (One True Lookup Table) and is a classic novice mistake. Your hunch is correct!

Here (and here) is the opinion of Joe Celko (a veteran SQL programmer who is/was a member of the SQL standards committee.  The fact that he titles his article with the phrase "EAV of destruction" should give you a clue :-).  Celko also calls this the Massively Unified Code Key.

It's no coincidence that the acronym is MUCK! :-)

Storing data in this way destroys many of the advantages of relational databases such as DRI (Declarative Referential Integrity), CHECK constraints and DEFAULT values.

Please create a table with 100 fields and 10 rows - if that's what your data requires, then do it. Perhaps a couple of other fields with experiment ID, datetime and experimenter_id would also be useful? That way you can perform analyses over given periods, various other aggregates - basically slice and dice all of your data.

< Personal opinion > If you haven't chosen your database yet and are happy to use F/LOSS, then may I recommend PostgreSQL - it's SQL dialect is by far the richest of the Open Source databases (I have no connection to the project). Check here for how to enforce datatypes in SQLite, but PostgreSQL offers more than SQLite - it's multi-user for example and no need to jump through hoops to enforce datatypes.< / Personal opinion >

[EDIT1]

Just one further remark, and for completeness, there is only one significant system out there that makes use of the EAV model - and that's Magento (1, 2). Its principal niche is the fashion industry where the EAV model may be suitable for sparse tables (fashion items tend to be available in a myriad of colours, syles, sizes...). It is popular (1, 2), but then so is MySQL which is inferior in many ways to PostgreSQL, Firebird and (multi-user capability aside) SQLite.


Where to find the size of SQL Server data types?

How can I prove that the size of an XML is 5?

I believe this question is better stated as: "Where to find the size of variable-length SQL Server data types?"

The issue with variable-length types is that there is no specific size that can be documented anywhere as it all depends on the data currently being stored in a particular variable or column. And the only way to know the current size is to use the DATALENGTH built-in function. For example:

DECLARE @TestXML XML;
SELECT DATALENGTH(@TestXML);

SET @TestXML = N'';
SELECT DATALENGTH(@TestXML);

SET @TestXML = N'g';
SELECT DATALENGTH(@TestXML);
GO

Which returns:

NULL
5
9

Now, the XML datatype does more than just provide convenient access functions for .value, .nodes, .query, and .exist. It is "optimized" in that the physical storage is reduced by creating a dictionary of the tag and attribute names and replacing the text with an ID that can be looked up. So the internal representation of an XML document is not what you see when looking at the XML document.

As you can see below, the NVARCHAR string is 94 bytes due to being UTF-16 (Little Endian) and having 47 characters (as expected). That same value converted to VARCHAR takes up only 47 bytes for those 47 characters (as expected). However, once converted to XML, the size in bytes is 53. How is that? Well, the text of the "row" and "col" tags is recorded only once (in the internal dictionary) and then IDs are used in the internal hierarchy of the document.

DECLARE @TestNVARCHAR NVARCHAR(500);
SET @TestNVARCHAR = N'<row><col>1</col><col>2</col><col>3</col></row>';

SELECT LEN(@TestNVARCHAR) AS [LEN], DATALENGTH(@TestNVARCHAR) AS [DATALENGTH];
-- 47   94

DECLARE @TestVARCHAR VARCHAR(500);
SET @TestVARCHAR = CONVERT(VARCHAR(500), @TestNVARCHAR);

SELECT LEN(@TestVARCHAR) AS [LEN], DATALENGTH(@TestVARCHAR) AS [DATALENGTH];
-- 47   47

DECLARE @TestXMLfromNVARCHAR XML;
SET @TestXMLfromNVARCHAR = CONVERT(XML, @TestNVARCHAR);

SELECT @TestXMLfromNVARCHAR, DATALENGTH(@TestXMLfromNVARCHAR) AS [DATALENGTH];
-- <row><col>1</col><col>2</col><col>3</col></row>  53

This is also why white space and formatting is ignored and the format of the XML value when selected might not be exactly as it was when it was set / inserted:

DECLARE @EmptyTag XML;
SET @EmptyTag = N'<test></test>             <test></test>';
SELECT @EmptyTag;
-- <test /><test />

There are at least two cases, however, where DATALENGTH does not appear to be accurate: SPARSE data and Data Compression. The SPARSE option (introduced in SQL Server 2008) allows fixed-length types to take up 0 bytes when NULL, but then their standard size plus 4 bytes when NOT NULL. However, that doesn't show up correctly here:

DECLARE @TestSPARSE TABLE
(
  RowID INT NOT NULL,
  Col1 BIGINT SPARSE NULL,
  Col2 TINYINT SPARSE NULL
);
INSERT INTO @TestSPARSE ([RowID], [Col1], [Col2]) VALUES (1, 0, 0);
INSERT INTO @TestSPARSE ([RowID], [Col1], [Col2]) VALUES (2, NULL, NULL);

SELECT RowID,
       Col1,
       DATALENGTH([Col1]) AS [BytesCol1],
       Col2,
       DATALENGTH([Col2]) AS [BytesCol2]
FROM   @TestSPARSE;

Returns:

RowID    Col1    BytesCol1    Col2    BytesCol2
1        0       8            0       1
2        NULL    NULL         NULL    NULL

In cases like these you would need to use DBCC PAGE to view the actual row in the actual data page to see what is really happening.

Data Compression behaves the same as the SPARSE option in terms of the effect (or lack thereof) on the value reported by DATALENGTH. This, I suppose, should make sense given that DATALENGTH reports the number of bytes taken up by the datatype (which applies to both variables and columns), but the SPARSE option and Data Compression are storage optimizations (which only apply to columns).


Encrypt/Decrypt output & input for SSIS task

Encrypting files through SSIS is possible, but there is no real easy native solution available with SSIS. There are a wealth of third party productions out there than can assist with this. Time is money, so it all depends on how much you want to spend on trying to come up with a customized solution or just go buy something that can help you do it quicker.

There is a good write up on this blog post that walks thorugh doing it with some custom .NET code and a ScriptTask. He also list out some of the third party products that offer a solution as well.


Create simple database with secure data entry GUIs

Oh boy this is a loaded question and really broad.  You have so many things to consider.  First let's tackle security.  You have to understand your scope or surface area of attack and compliance requirements.

Do you accept credit card data?  You'll need to meet PCI.  Do you have health data?  You'll need HPAA.  Do you have sensitive information?  Then you will need to classify that.  A common formula for security budget and concern if you are not bound to a certain policy is take the percentage of likeliness of attack, how much it would cost, and factor that into how much to spend on security.  You might see hiring a security consultant to review might be worth it in that case.  There's a lot to consider on the scope for the front end as well, for example is this form public to the company, wide area network, the public internet, etc?

One then has to follow all the best practices to keep a secure front end.  Most likely your security holes will come from a un-secure back end but if you do things such as run the front end web apps as for example, the same user as the DB or a user that has elevated DB access you just created a potential for many penetrations into your data simply by not separating users.  Dev/QA/Prod environments being on the same network and sharing resources is another common attack vector.  Encrypt on all layers if possible.  Read up on front end security best practices such as:

Client Side Security Best Practices
MS Best Practices Link

I'd recommend starting with the basic security best practices from CISSP and/or PCI.  This is for security but not compliance.  Are you in fact required to meet compliance as well?  If so, what kind?  If any, you'd better ensure you do and have mitigating controls.

For the back end you'll want to follow best practices as well.  Depending on the DB engine you will have many ways of doing this.  A most common attack is SQL injection where the queries that are being passed are manipulated to be another query and bring back sensitive data.  Thus you will want to create users that have very tightly defined views, stored procedures (if your engine supports it), users, proxy users, and such so that they can access the minimal amount of information required.

Some DB Best Practices

MS Recommendations for DB Security

I'd say keep looking, reading up, patching, testing, run pen testing tools such as This one and come back with a more specific question.  Let us know what DB engine you're using, how you're doing the transport layer, front end, and so fourth.  Good luck!


If you're new to the DBA role then go to this site:

OLA

What you're trying to do has already been done.

Any DBA should have this site on their list of favourites.


How to import big sql file in Phpmyadmin

Maximum file size you can upload is specified in your php.ini [ the configuration file for php ]. You would like to look for upload_max_filesize = 2M and change it to something else, for example 20M. You can also try to compress your file size, phpadmin accepts many different archives, like zip, gz etc.


How to insert an IP-address into an inet column in PostgreSQL?

It seems pretty easy:

postgres=# create table inet_test (address inet);

CREATE TABLE

postgres=# insert into inet_test values ('192.168.2.1');

INSERT 0 1

postgres=# insert into inet_test values ('192.168.2.1/24');

INSERT 0 1

postgres=# select * from inet_test;

 address
----------------
 192.168.2.1
 192.168.2.1/24
(2 rows)


MySQL - Should I create a different database for users

If the data is related, it should be in the same database unless there is a very compelling reason not to.  By separating the data you have:

Made it impossible to maintain referential integrity
Increased the number of cross-database calls
Increased the amount of maintenance
Increased the likelihood you will build another database for analysis/backend reporting with complex reconciliation rules

If you are looking to improve fault tolerance or increase performance, there are generally better ways to manage that.


Restore large MySQL dump thorugh EasyPHP

You can use MySql Workbench for managing your remote database

here is the link for downloading MySql Workbench

http://dev.mysql.com/downloads/tools/workbench/


Update using NonClustered index results in large number of logical reads?

The difference is due to: Seek(nonclustered) vs. Scan(clustered).

Some (oversimplified) explanation:

When performing seek, SQL Server must lookup every row. To find that row it has to travel down the index tree and access a page on every level.
In your case, with 2000 rows, i believe there is one index level and a data level. This means that for every of 1000 rows, SQL has to access 2 pages (row1 index->data.. row2 index->data ...) which explains ~2k logical reads.

The scan on the other hand works in a way that it looks up the first row and then follows all rows on the page and all the next pages sequentially.
So it would look something like that : root page > index > find 1st data page > 2nd data page ...

This does not mean that Seek is worse and Scan is better (usually the opposite), because more often in the real world would you have large tables and retrieve/update only small portions of it (which seeks are good at). With operations on a large percentage of the table scans will often be more efficient.


Joins between linked servers are dangerous, so can we just load the data into a local table before doing the join?

I've used this technique in data synch jobs.  It helps if you don't need the entire table from the remote server (just a few columns or you can filter it down in your WHERE clause).  And, by bringing the data into a temp table on the instance where you're doing the most work, you can index the temp table to your needs and get statistics that are helpful to the work you're doing.

Of course, if you're trying to do this on a very frequent basis, it might not be the best solution for you.  Maybe SSIS?


using tnsnames.ora and sqlplus client without installing oracle 11g server on linux client

tnsnames.ora doesn't "come with" anything.  The key is not that file, as it is nothing but a names resolution file. And sqlplus is nothing but a CLI application. If you want to connect to an Oracle database (or ANY database) you must have, at a minimum, the client software for that particular database product. For oracle you need to download and install the Oracle client. The easiest would be the "instant client", which you can download from here: http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html.  If you want the full "Admin" or "Runtime" client, download from edelivery.com and select just the client software. Pay attention to the license terms.


Database Stuck in "Restoring" in SSMS After Running From an Outside Process

The problem is that you need to give it time so the database restores.

When you run your python code to execute the stored procedure, don't close the connection instantly, but pop a time.sleep(10) after it. This will give the database some time to prepare it to the online state (the time you need to sleep will vary based on db size).


Looking at your table structure means that while the actual row is small (many int values) there will be millions of rows.

Yes you can use a database of your choice, MySQL, Oracle, SQL Server, PostGres for the raw data from the cameras.

You can add partitioning by day which will create a partition for each day, and you may want to consider archiving data on an annual basis into a separate table for each year to make the table sizes manageable.

I would recommend that you setup another set of summary tables for reporting which can be based on the partitions, and this can be done on a daily basis (per partition) and automated.

The actual design of the summary tables depends on the reports you need to run, but it will be necessary so that your queries do not run off the base data table which will be huger.


You can check the condition of the check constraint from all_constraints or user_constraints view.

SQL> create table animal(
SR_ID number,
First_Name varchar2(20),
Last_Name varchar2(20),
PET varchar2(20) constraint ckpet check (PET in ('DOG', 'CAT', 'FISH'))
);

Table created.

SQL> select constraint_name,search_condition
from all_constraints
where table_name='ANIMAL'
and constraint_type='C';

CONSTRAINT_NAME     SEARCH_CONDITION
---------------     ---------------------------
CKPET                PET in ('DOG', 'CAT', 'FISH')


Safe values for InnoDB variables when using FULLTEXT searches

innodb_buffer_pool_size -- This should be set to about 70% of RAM.  But, if the other settings encroach too much into this, then lower it.  Swapping is bad.
innodb_buffer_pool_instances -- Simply set it to innodb_buffer_pool_size / 1G -- rounded down, but at least 1
innodb_ft_cache_size -- big enough for at least one row of text being indexed via FULLTEXT.  Bigger if practical, but don't squeeze out the buffer_pool
innodb_ft_total_cache_size -- If you are [re]building FULLTEXT in only one thread at a time, this is not relevant.
innodb_ft_result_cache_limit -- This is relevant only to SELECTs that involve a FULLTEXT index.  The max is 4G, but don't set it that large unless you have lots of RAM.  Start with, say, 1% of RAM; if you get an error, then raise it.

The settings are not dynamic, so change the config file and restart mysqld.
How much RAM do you have?  What is a typical and the max size of the column(s) in the fulltext index?


Primary Key At the End of Foreign Key index in MySQL is in order for sure?

FK is a constraint.  To facilitate checking that constraint, an INDEX is created.  Run SHOW CREATE TABLE for confirmation.  An InnoDB INDEX is expanded to include PK column(s).  Also, if there is an index with the leftmost column(s) matching the FK's column(s), the FK is happy.

When doing a SELECT, the Optimizer has many possible ways to do the query.  If you do not have an ORDER BY, then you must not assume the output is in any particular order.  The Optimizer may not have chosen the index you expect, or there could be a GROUP BY that is performed by first sorting, or the GROUP BY is done by hashing, hence randomly ordered.  Etc.

"Filesort" is a misnomer in two ways.  It may not involve a "file"; that is it might be done in RAM.  And it may need to be plural -- For example, GROUP BY a ORDER BY b is likely to involve two sorts.  EXPLAIN FORMAT=JSON ... is clearer on this than a plain EXPLAIN.

So, yes, the index for the FK is effectively as you describe (2 columns, ordered).  But so what?  Don't trust it; tack on an ORDER BY if you need the ordering.  As I mentioned above, the Optimizer is free to ignore that index.

A tip:  If you want to make use of both columns of the 'index' in question, then explicitly specify INDEX(base_id, record_id).  The FK should see that and say "That index is all I need; I won't make another one".  Humans reading your code will say "Hmmmm, the schema designer needs these two columns indexed for some reason".


When is it appropriate database design to use a separate schema?

I am going to mention both logical and physical design here.

I know IBM recommends placing staging tables into their own schema(s) and even into their own table spaces (database implementation specific to DB2 in this case) for reasons of management. Schemas allow you to manage better via separation of concerns and security. The tablespaces (physical design) allow you to better handle maintenance issues regarding REORGs, RUNSTATS, and backups.

While you may not be implementing IBM DB2, I think their philosophy is something worth thinking about and implementing.


SQL Server 2008 R2 returning wrong record

First, make sure your SQL Server is fully patched and up to date.  For 2008 R2, at this time I'd say the hard minimum is build 10.50.6220 (SP3 + MS15-058), and you really should be at 10.50.6537 (SP3 + MS15-058 + TLS 1.2 capability).

Second, make sure you have a unique index on the combination of NetworkUserID, NetworkID (with the highest cardinality one of those two first).

Third, patch your host OS and move that leaky service to another machine.
Or, if you absolutely cannot, then reboot the server often enough that it doesn't run out of memory before the next reboot.  Or buy more RAM for it.

My expectation is that EITHER the patching will fix it, OR putting the unique index on will fail because you've got duplicates in the table already.

The other thing that might be an issue is that you have an actual hardware fault, and may have corrupt data - this is actually very rare.

make sure you have SQL Alerts set up and running; Glen Berry has a script on sqlskills.com to do this.
run DBCC CHECKDB with DATA_PURITY first, on ALL databases on the instance.
make sure you have CHECKSUM checking on every database.
rebuild all your indexes (ALTER INDEX ALL on  REBUILD).

just in case CHECKSUM was added late, and some of the pages don't have it yet because they've never been touched.
and make sure every table has a clustered index, while you're at it.

check for existing records of disk errors (event log, hardware logs, etc.)
run chkdsk in Windows on all your drives.
if you're on RAID, try a consistency check and/or patrol read or other validation.
run underlying hardware diagnostics.


Are there any good database management applications for JDBC/Java databases?

DbVisualiser could fulfill your requirements as it supports quite a number of RDBMS, including JavaDB/Derby. You can see it in action below:

The only question mark is if the free version has some limitations which are a stopper for you. You can check the matrix here.


How to track only the tables used by app

There are two major things you can try
METHOD #1 : General Log as a Table
Convert the General Log into a Table. I already have 13 posts on how to create and rotate it.
You have the general log table open as a MyISAM table, you can query it like any other table.
In you case, you can do select against it by the event_time (if you query by the hour or by the day)
METHOD #2 : Compare Open Tables to INFORMATION_SCHEMA
STEP 01 :
If you run SHOW OPEN TABLES;, you will get a list of every table with open file handles.
It will look something like this:
+---------------------+----------------------------------------------------+--------+-------------+
| Database            | Table                                              | In_use | Name_locked |
+---------------------+----------------------------------------------------+--------+-------------+
.
.
.
+---------------------+----------------------------------------------------+--------+-------------+

Simply dump the output of this command to one text file (call it tables1.txt)
STEP 02 :
SELECT CONCAT(table_schema,'.',table_name) FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','performance_schema','mysql','sys');

Send the output of this query to a text file (call it tables2.txt)
STEP 03 : Filter out MySQL system tables
Remove 'information_schema','performance_schema','mysql','sys' from tables1.txt
STEP 04 : Compare the text files
Any table in table2.txt not in tables1.txt has not been used for reading or writing.
CAVEAT
You would have to leave MySQL running a long time to determine which table has open file handles. Please do not do the following:

Restart MySQL
FLUSH TABLES;

As long as you do not do these, the file handle will be open. If you do, you'll have to start over accessing the tables over time.
If you just need to find which tables have been written, you do not need to the INFORMATION_SCHEMA. I think INFORMATION_SCHEMA.TABLES.UPDATE_TIME is usable in MySQL 5.7. If you are using MySQL 5.6 or earlier, you can go to the OS.
EXAMPLE: Suppose your datadir is /var/lib/mysql and you want to see what tables have been written in the database reporting. You go to the OS and do this:
cd /var/lib/mysql/reporting
ls -lt ls -lt | grep "\.[iM][bY][DId]"

This will show you every table in the reportingdatabase in descending order by datetime. You can judge from there which tables have recent writes and which do not.


I could easily tune my sqlcmd scripts to do a "backup in 'full' -> switch to 'simple' -> backup in 'simple' -> revert to 'full'" and keep the file at bay, but I fear I could be running some risk I am not foreseeing right now.

Don't do this. It is a horrible idea because it gives anyone else looking at the system the wrong impression and is only a workaround to lie to the primary developer. That is not a good reason to make a technical decision around database recovery. There are unknown risks since we don't know anything about your company, application, etc. so only you can speak to those.
The correct path is to get a consensus among stakeholders, including any stubborn developers, and agree on a RTO/RPO that meets the business needs. Then adjust the recovery model accordingly.
Assuming you don't want to do that based on your post:
If you are 100% sure you don't care about recovery more granular than daily backups, keep it in full recovery mode and have a transaction log backup job that points to NUL so that growth is not an issue. It won't save the files anywhere, but will still allow you keep keep log growth under control.
Also see:

Switching to Simple Recovery - shrinking transaction logs
expressdb.io: SQL Server Express Backups - Some more background on RTO/RPO and some sample jobs using Ola Hallengren's backup solution with some specific guidance for SQL Server Express.

Full disclosure: expressdb.io is my site.


How to JOIN a table with a name that equals to a determined value in MySQL?

You can't directly without drawing in all the tables. You would need to first pick out the table name needed then use ad-hoc SQL or call a specific procedure per table to get the data out.

Drawing in all the tables as follows would work:

SELECT ac.carID, ac.type, ac.commonAttribute1, ac.commonAttribute2
     , ct.specialAttributeA
     , ca.specialAttributeB
     , cb.specialAttributeC
FROM   AbstactCar ac
LEFT OUTER JOIN
       CarTesla ct ON ct.carID = ac.CarID
LEFT OUTER JOIN
       CarAstonMamn ca ON ca.carID = ac.CarID
LEFT OUTER JOIN
       CarBMW cb ON cb.carID = ac.CarID

but obviously this is limited by needing to know all the types in advance and/or update the query every time a type is added/dropped/changed and possibly hitting some limit on the number of tables in any given SELECT. If a non-common attribute can appear in more than on of the sub-classes then change the definition for that output column to something like ca.specialAttributeD, cb.specialAttributeD AS specialAttributeD.

NOTE: I'm pretty sure the above is mysql compatible, but I'm generally an MS SQL Server fellow and haven't tested it so you may need to tweak the syntax slightly.


This part of your join is incorrect:

Trelaciones.Device_SN_O="FCQ1527Y0B3"

That should be your WHERE clause.

Your join should be:

FROM Trelaciones
INNER JOIN dispositivos
ON  Trelaciones.whatever_key_column = dispositivos.whatever_key_column

So

FROM Trelaciones
INNER JOIN dispositivos
ON  Trelaciones.whatever_key_column = dispositivos.whatever_key_column
WHERE Trelaciones.Device_SN_O="FCQ1527Y0B3"


Does Couchbase/CouchDB allow me to move / skip the CRUD middle man from the server?

I took a look at the REST API endpoint list on the Couchbase docs site.

I think it might be hypothetically possible. You could use the N1QL REST API to do inserts, selects, updates, and deletes.

I can't say for sure whether this is going to help scaling and load or not, but I do think you're going to have to address a number of other challenges: validation, authentication, authorization, business logic, and more.


Both SQLite and SQL Server understand enough standard SQL to do a recursive tree walk like this:

WITH AllAncestors AS (
  SELECT *
  FROM Material
  WHERE MaterialId = 12345

  UNION ALL

  SELECT parent.*
  FROM Material AS parent
  JOIN AllAncestors AS child ON child.ParentMaterialId = parent.MaterialId
)
SELECT *
FROM AllAncestors
WHERE ParentMaterialId IS NULL;

WITH AncestorsUntilPaid AS (
  SELECT *
  FROM Material
  WHERE MaterialId = 12345

  UNION ALL

  SELECT parent.*
  FROM Material AS parent
  JOIN AncestorsUntilPaid AS child ON child.ParentMaterialId = parent.MaterialId
  WHERE child.Paid = 0   -- stops before the parent of a paid child
)
SELECT *
FROM AncestorsUntilPaid
WHERE Paid = 1;


Don't store redundant information. Both total_bed_count and total_vacant_bed_count can be queried.

total_bed_count would be something like select count(*) from bed, while total_vacant_bed_count could be queried if you know which beds don't have patients assigned to them, maybe something like select count(*) from bed where patient is null.


SSRS breadcrum just breaks into multiple lines

AFter days of scratching my head and google finally fixed with a vey simple setting. This was resolved by adding the url to the Compatibility View Settings in the IE browser settings.


You need to review each of the parameters in the docs, located at https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_sched.htm#ARPLS72300.  Specfically, take a look at 'job_action' for your specified 'job_type' of "STORED_PROCEDURE".

"The action is the name of the stored procedure. "  (emphasis mine).  You are saying the action is a command -- "execute".

Also, does your procedure "delete_org_sre_package.process_org_sre_deletions" have any input or output parameters?


Can I calculate ROW_NUMBER() for only consecutive records?

I was able to address this using LAG():

SELECT
    Sequence, Subset,
    CASE WHEN Sequence = 1 OR Subset <> LAG(Subset, 1) OVER (ORDER BY Sequence)
        THEN 'New subset'
        ELSE 'Continuation'
        END
FROM
    @Data

This returns "New subset" for records #1, #4, and #6.  Apparently LAG() partitions slightly differently from ROW_NUMBER().

Obviously this doesn't provide row numbers, but it helped me reach the goal of identifying consecutive sequences of numbers, when the subset identifier can repeat.


How do I troubleshoot error 26: "Error Locating Server/Instance Specified"

When you try to connect to a local instance, SQL Server will always attempt to use Shared Memory. This connection will work fine:

localhost\SQL2012

This works in my scenario, and the connection is indeed using Shared Memory in this case.

SELECT session_id,net_transport,local_net_address,local_tcp_port
FROM sys.dm_exec_connections;

Results:

session_id  net_transport   local_net_address   local_tcp_port
----------  -------------   -----------------   --------------
53          Shared memory   <local machine>     NULL

If you want to try to override Shared Memory (not sure why, on a local machine) and force TCP/IP instead, you can't mix that with specifying the named instance explicitly, at least in all instances I tried (no pun intended). The named instance forces SQL Server to map the instance name to the port number, using SQL Server browser, but this apparently can't be done when you force TCP/IP in the first place (the whole point of using TCP/IP is so you go straight to the port and not bother with the browser). If your named instance happens to be running on its own IP address and forced to port 1433, you may be able to get this to work; you may also be able to get it to work if you are using a Client Network alias (but I could not).

I was able to connect if I looked up the current dynamic port assigned to that instance in Configuration Manager (in my case it was 49538), and then connect using tcp:localhost,49538:

So now:

SELECT session_id,net_transport,local_net_address,local_tcp_port
FROM sys.dm_exec_connections;

Results:

session_id  net_transport   local_net_address   local_tcp_port
----------  -------------   -----------------   --------------
53          Shared memory   <local machine>     NULL
54          TCP             10.211.55.10        49538

All variations I tried - across multiple machines and versions - where I tried to combine a named instance with overriding shared memory and use TCP/IP instead - yielded the same error that you received. This is both when I prefixed the server/instance name with tcp: and when I specified TCP/IP for the network protocol in the connection properties dialog.

Long story short: If you really need to force TCP/IP for a local connection, you're going to need to use a fixed port for that instance, and specify the port. Or use 1433 (which will only work if there's no default instance or if you have a dedicated IP address for that instance), then you won't need to specify the port, but you'll still need to specify the tcp: prefix or use that option in the connection dialog.

I do not suggest trying to disable Shared Memory. FWIW.


Since your spid 119 is in [sleeping] state, I'd recommend you to check:

DBCC Opentran() to check whether there is any open transaction on 119 (high possibility to my experience)
sp_lock 52 to check what lock in spid 52 is NOT granted
sp_lock 119 to check what lock it holds, and you can trace the lock back to the resource (such as table or index or key etc)

Also maybe you can turn on READ_COMMITTED_SNAPSHOT on your database to avoid the blocking between read and write operation.


Querying the Username in a Share Data source (SSRS)

After doing some digging, I doubt you'll be able to find an unencrypted list anywhere. It's storing it in the report server, so the SSRS engine is encrypting it on it's own; i.e it's generating a key and salt in the engine code and using it to encrypt\decrypt those columns. You won't be able to query it via T-SQL, but you might have better luck using PowerShell to parse through the HTML on the reporting front end.

I believe the tag in question is the value for input named "ui_txtStoredName". I attached a picture as an example.


A suitable solution is to create third table to link users with clients and so give access:

users             lnk_user_client_access
-----             ----------------------                clients
id_user --------- id_user                              --------
...               id_client   ------------------------ id_client
                                                       ...


how to restore database from other server - sql

In the absence of more information, I'll guess SQL Server.

SQL Server uses logins to connect at the Instance (server) level
A login maps to a user in a databases

You can see this in SSMS in the Security nodes on Object Explorer.

I suspect you have "orphaned" users. That is, the users in your restored database no longer have an associated login to map to. This need recreated (Windows logins) or remapped (SQL logins)

The MSDN article Troubleshooting Orphaned Users has checks, fixes and more details. I'd simply copy/paste or re-hash this so I recommend you look at this.


How one can install a given R package in SQL Server

How one can install a given R package in MSSQL?

See installation of R services step by step Here & Here

  How can I get R running properly on MSSQL?

So far, we have installed the components required for running R Services; now to enable the R Services feature itself you need to explicitly enable it with the following command before you can invoke R scripts in SQL Server.

--Execute this script to verify if R Services in enabled

EXEC sp_configure  'external scripts enabled';
Go

To Verify R services

--Execute this script to enable R Services on the SQL Server   instance

EXEC sp_configure  'external scripts enabled', 1;
RECONFIGURE WITH OVERRIDE
Go

--Execute this script to verify if R Services in enabled

EXEC sp_configure  'external scripts enabled';
Go

To Verify R services

Finally, you need to restart SQL Server service, which will automatically restart the related SQL Server Trusted Launchpad service (it runs advanced analytics extensions to enable integration with Microsoft R Open using standard T-SQL statements; disabling this service will make the Advanced Analytics features of SQL Server unavailable) as well for executing external R scripts.

At this time, if you execute the following command again, you will notice 1 for both config_value and run_value, which means you are not good to execute R script on this SQL Server instance:

--Execute this script to verify if R Services in enabled

  EXEC sp_configure  'external scripts enabled';
  Go

SQL Server 2016 includes a new system stored procedure (sp_execute_external_script) to enable you to execute external scripts inside the database engine. The stored procedure loads and executes a script written in a supported language from an external location.

For your further ref here


There are multiple issues here:

First, your console queries work fine in MongoDB v2.0.2 but don't work in MongoDB v2.2.1. This is probably a regression bug, but the JavaScript parser may have intentionally been changed between versions. The ... after entering a command means the console is waiting for a multi-line command to be completed; I'm not sure why in this specific situation.
Also, the backslash character \  in the $regex string must itself be escaped. (The JavaScript parser consumes the escape first, before the regex engine gets to see it.)

> db.test.find({word:{$regex:"\["}});    // no result
> db.test.find({word:{$regex:"\\["}});   // returns result when escaped properly
{ "_id" : 0, "word" : "[" }
{ "_id" : 1, "word" : "[]" }

MongoDB 2.0.2 console output:

> db.test.drop(); version()
version: 2.0.2
>
> db.test.insert({_id: 0, word: '['});
> db.test.insert({_id: 1, word: '[]'});
> db.test.insert({_id: 2, word: ']'});
> db.test.insert({_id: 9, word: 'no brackets'});
>
> db.test.find({word:/[\[]/});           // could not be run in console
{ "_id" : 0, "word" : "[" }
{ "_id" : 1, "word" : "[]" }
> db.test.find({word:/\[/ });            // could not be run in console
{ "_id" : 0, "word" : "[" }
{ "_id" : 1, "word" : "[]" }
> db.test.find({word:/\]/});             //returns result
{ "_id" : 1, "word" : "[]" }
{ "_id" : 2, "word" : "]" }
> db.test.find({word:/[\]]/});           //returns result
{ "_id" : 1, "word" : "[]" }
{ "_id" : 2, "word" : "]" }
> db.test.find({word:{$regex:"\["}});    //no result
> db.test.find({word:{$regex:"\\["}});   //returns result when escaped properly
{ "_id" : 0, "word" : "[" }
{ "_id" : 1, "word" : "[]" }
> db.test.find({word:{$regex:"\[[]"}});  //returns result
{ "_id" : 0, "word" : "[" }
{ "_id" : 1, "word" : "[]" }

MongoDB 2.2.1 console output:

> db.test.drop(); version()
version: 2.2.1
>
> db.test.insert({_id: 0, word: '['});
> db.test.insert({_id: 1, word: '[]'});
> db.test.insert({_id: 2, word: ']'});
> db.test.insert({_id: 9, word: 'no brackets'});
>
> db.test.find({word:/[\[]/});           // could not be run in console
... db.test.find({word:/\[/ });            // could not be run in console
... db.test.find({word:/\]/});             // returns result
... db.test.find({word:/[\]]/});           // returns result
{ "_id" : 1, "word" : "[]" }
{ "_id" : 2, "word" : "]" }
> db.test.find({word:{$regex:"\["}});    // no result
> db.test.find({word:{$regex:"\\["}});   // returns result when escaped properly
{ "_id" : 0, "word" : "[" }
{ "_id" : 1, "word" : "[]" }
> db.test.find({word:{$regex:"\[[]"}});  // returns result

MongoDB 2.2.1 console output (commented out lines that don't parse as expected):

> db.test.drop(); version()
version: 2.2.1
>
> db.test.insert({_id: 0, word: '['});
> db.test.insert({_id: 1, word: '[]'});
> db.test.insert({_id: 2, word: ']'});
> db.test.insert({_id: 9, word: 'no brackets'});
>
> // db.test.find({word:/[\[]/});        // could not be run in console
> // db.test.find({word:/\[/ });         // could not be run in console
> db.test.find({word:/\]/});             // returns result
{ "_id" : 1, "word" : "[]" }
{ "_id" : 2, "word" : "]" }
> db.test.find({word:/[\]]/});           // returns result
{ "_id" : 1, "word" : "[]" }
{ "_id" : 2, "word" : "]" }
> db.test.find({word:{$regex:"\["}});    // no result
> db.test.find({word:{$regex:"\\["}});   // returns result when escaped properly
{ "_id" : 0, "word" : "[" }
{ "_id" : 1, "word" : "[]" }
> db.test.find({word:{$regex:"\[[]"}});  // returns result
{ "_id" : 0, "word" : "[" }
{ "_id" : 1, "word" : "[]" }
>


What permission needs to be added to see the Security folder in Azure SQL?

As a minimum you need to be a member of loginmanager role to see Security folder.

Ref:https://docs.microsoft.com/en-us/azure/sql-database/sql-database-manage-logins

  Login managers

  The other administrative role is the login manager role. Members of
  this role can create new logins in the master database. If you wish,
  you can complete the same steps (create a login and user, and add a
  user to the loginmanager role) to enable a user to create new logins
  in the master. Usually logins are not necessary as Microsoft
  recommends using contained database users, which authenticate at the
  database-level instead of using users based on logins. For more
  information, see Contained Database Users - Making Your Database
  Portable.

Creating a login:

    USE [master]
    GO

   CREATE LOGIN [testPerm2] WITH
   PASSWORD=N'strong PW'

Creating a user in master with no other privilege.

USE [master]
GO
CREATE USER testPerm2
    FOR LOGIN testPerm2
    WITH DEFAULT_SCHEMA = dbo
GO

Now connect using SSMS and this is what you see (Notice no security).

Adding testPerm2 user to loginmanager role.

EXEC sp_addrolemember N'loginmanager', N'testPerm2'
GO

Again connect with SSMS or refresh your previous connection and now testPerm2 user can see security tab.


I don't have access to a Netezza device, but according to the wiki,

  Netezza software was based on PostgreSQL 7.2,[9] but did not maintain
  compatibility.

so, I'll use the PostgreSQL option for the fiddle which you can find here. According to the documentation, Netezza has Window/Analytic functions.

What I did to answer your question was:

Create a share table:

CREATE TABLE share
(
  t_date  DATE NOT NULL, -- BAD IDEA to use SQL keywords for fieldnames!
  account INTEGER NOT NULL,
  price   INTEGER NOT NULL
);

Populate it:

INSERT INTO share
VALUES
('2020-02-01',       100,                    200),
('2020-02-01',       101,                    200),
('2020-02-01',       102,                    200),
('2020-02-02',       100,                    210),
('2020-02-02',       101,                    200),
('2020-02-02',       102,                    200);

Then I ran the following query (using Window - or Analytic - functions):

SELECT
  ROW_NUMBER() OVER() as rn,
  t_date AS d, account AS a,
  LAG(price) OVER () AS la, price, LEAD(price) OVER() AS le
FROM share;

Result:

rn  d   a   la  price   le
1   2020-02-01  100     200 200
2   2020-02-01  101 200 200 200
3   2020-02-01  102 200 200 210
4   2020-02-02  100 200 210 200
5   2020-02-02  101 210 200 200
6   2020-02-02  102 200 200

The rn field is a proxy PRIMARY KEY - I presume your table has one? OK, so far so good. But, you can't put Window functions in a where clause, so this will be our basis for the following query:

SELECT
  rn, d AS tx_date, a AS acct, la AS previous, price, le AS future,
  CASE
    WHEN le > la THEN 'Increase'
    ELSE 'Decrease'
  END AS action
FROM
(
  SELECT
    ROW_NUMBER() OVER() as rn,
    t_date AS d, account AS a,
    LAG(price) OVER () AS la, price, LEAD(price) OVER() AS le
  FROM share
) AS tab
WHERE le != la;

Result:

rn  tx_date     acct    previous    price   future  action
3   2020-02-01  102          200      200      210  Increase
5   2020-02-02  101          210      200      200  Decrease

So, this is, I think, pretty much what you wanted. Following tranaction 3, there was an increase and prior to transaction 5 there was a decrease. If you want the focus to be on transaction 4, then just play around with the SQL - it shouldn't be too difficult. Window/Analytic functions are very powerful and well worth learning! BTW, next time you ask a question, could you please provide your table structures as DDL (CREATE TABLE foo...) and your data as DML (INSERT INTO foo VALUES (...) - or use a fiddle. p.s. welcome to the forum!


Rebuild index error: "The job was stopped prior to completion by User"

The user stopped the job, probably because it was running long.

That is not an error message, it is telling you what happened.

Contact the user and ask them why the stopped the job.


Actually, you can do this with a single collection (news items) and a simple query. No need for references (continent names aren't going to change any time soon) or strange data models here:

{
  _id: new ObjectId(),
  continent: "Asia"
  title: "whatever",
  date: yesterday
},
{
  _id: new ObjectId(),
  continent: "Africa",
  title: "other title",
  date: today
}

The according query would be

db.news.find({}).sort({ "continent": 1, "date": -1 })

In order to speed this query up, you should create an according index

db.news.createIndex({ "continent": 1, "date": -1 })

With that approach, you can easily do things like "For each continent, count the number of news for last week":

var date = new Date()
var last_week = date.setDate( date.getDate() - 7 );
var result = db.news.aggregate([
  { $match:{ "date":{ $gte: last_week } } },
  { $group:{ "_id": "$continent", "newsitems":{ $sum: 1 } } }
])


Confused about sys.dm_io_virtual_file_stats num_of_reads/writes column

The latency of of the I/O system (which the average latency per IOPS is an indicator of) tell you if you are adding more load on the I/O system than it can handle. Because of this, the average latency, when compared with the media you are running on provides a good indicator as to whether I/O is the problem or not.

As a rule of thumb, here are the latencies you should expect if you are not overloaded on the I/O system

Spinning media: Around 5-10ms
SSD: <1ms

If you are seeing latencies higher than that, you know that the I/O system is being asked to do more work than it can handle


Error: Could not read Block X of relation base/Y/Z

Before doing anything else, read and act on: http://wiki.postgresql.org/wiki/Corruption .

Most likely you have disk or file system problems.

If you suspect any kind of DB corruption for whatever reason you should stop the DB and copy the entire database at the file system level before attempting any recovery.

Once you've done that, then you can look into possible repairs. You'll probably have some significant data loss, so your goal should be to get it working to the point where you can pg_dump the damaged databases, re-initdb, and reload.

If you have a recent backup, now would be a good time to think about using it.


Move live SQL Server database to a new server

since you are using SQL Server 2008 Web edition, your best bet is to use LOG SHIPPING.

Your database size is 100GB, so initializing the secondary from a backup should be considered. Dont use GUI to set up initial logshipping.

SO your steps should be :

Backup 2008 Web edition database and restore it on 2012 enterprise WITH NORECOVERY.
Launch Logshipping wizard and make sure to select secondary is already initialized. Configure logshipping to be every 1 min(this is the minimum you can get).

Move all the logins, Jobs, SSIS packages.

During the switch to new server :

stop all the webservers connecting to the database
stop all the backup, copy and restore jobs of logshipping.
take a final Tail log backup and restore it on secondary WITH RECOVERY.
Sync the logins.
Change the web.config of the application to point it to the new server. This way you dont have to change the IP address of the new server to match the old one.

I have given the post restore steps in more detailed manner here.


Query hangs forever at first but works after one day

Not sure if the query against that one table really "hangs", it might just be taking much longer time to execute. If you say things look fine next day, the lack of statistics on the newly created tables might be the reason for poor performance. Next time, you follow your process of "copying" tables, you may want to explicitly collect statistics on newly created tables and their indexes before you run the queries.


SQL Server transaction log backups: test whether tail log follows last known log backup

I turned to my copy of SQL Server 2008 Internals and the DMV sys.database_recovery_status was pointed out to find the first LSN of the next log backup. Which going by BOL the column last_log_backup_lsn provides you with:

  Log sequence number of the most recent log backup. This is the end LSN of the previous log backup and the starting LSN of the next log backup.
  NULL= No log backup exists. The database is offline or the database will not start.

Just to mention as well that Kalen also brings up the point that you will get a NULL value if the database is in SIMPLE recovery mode (autotruncate mode) or if no log backup exist.

  But how can I obtain the FirstLSN of the tail log?

Without actually backing up the tail log of a database (don't have an test instance to try this on) you could logically conclude that the value returned in the column mentioned would be the first LSN of the next log backup, in your case the tail.

So executing the following will return the value I believe you are looking for:

SELECT
   last_log_backup_lsn
FROM
   sys.database_recovery_status
WHERE
   databse_id = DB_ID('MyDb')

This DMV is available starting in SQL 2005.

EDIT
Unless you read the BOL link, please note that this DMV will only return values to databases that are online, or opened as BOL references it. If a failure occurs that requires you to take a tail log backup of a database you will not be able to verify this value through the above code unless the database is accessible; which in a failure it probably would not be.


"Cannot resolve table name" error when applying MySQL dump to RDS

Just found out that this was a bug that has been fixed in the next release, MySQL 5.7.11.

Extract from the changelog:

  InnoDB: Creating a table with a full-text index and a foreign key constraint failed when foreign_key_checks was disabled. (Bug #22094601, Bug #78955)

It took me time to figure out that the full-text index was the source of the problem, I had to trim down my dump file line by line until the dump could be successfully imported.

Unfortunately RDS does not support MySQL 5.7.11 yet, so I had to work around this issue. Here is what I did:

created the dump file with mysqldump as usual
opened the dump file and commented out all FULLTEXT KEY lines
noted down these lines in notepad
imported the dump to the replica as usual
connected to the replica, and issued ALTER TABLE statements to add each full-text index I had in my notepad back to its table

This worked perfectly well and my tables are fully in sync now.


Convert a row to a nested JSON document containing all relations

Your guess is pretty close, you'll need dynamic SQL.

But this should be considerably faster and more elegant than looping through all columns in the NEW record etc.:

CREATE OR REPLACE FUNCTION trg_jsonb_row_with_fk()
  RETURNS trigger AS
$func$
DECLARE
   _sql text;
   _jsonb_row jsonb;
BEGIN
   SELECT 'SELECT to_jsonb($1) || '
       || string_agg(
            format('(SELECT jsonb_build_object(%1$L, t.*)
                     FROM %2$s t WHERE %3$I = $1.%1$I)'
                 , a.attname                 -- %1$L, %1$I
                 , c.confrelid::regclass     -- %2$s
                 , f.attname)                -- %3$I
            , ' || ')
   FROM   pg_constraint c
   JOIN   pg_attribute  a ON a.attrelid = c.conrelid
   JOIN   pg_attribute  f ON f.attrelid = c.confrelid
   WHERE  c.conrelid = TG_RELID
   AND    c.contype  = 'f'               -- to select only FK constraints
   AND    a.attnum   = c.conkey[1]       -- assuming only single-col FKs!
   AND    f.attnum   = c.confkey[1]
   INTO   _sql;

   IF FOUND THEN                         -- FKs found
      EXECUTE _sql USING NEW INTO _jsonb_row;
   ELSE                                  -- no FKs found, plain conversion
      _jsonb_row := to_jsonb(NEW);
   END IF;

   RAISE NOTICE '%', _jsonb_row;         -- do something with it ...
   RETURN NEW;                           -- proceed with org. row
END
$func$  LANGUAGE plpgsql;

Example trigger using above function:

CREATE TRIGGER upd_bef_jsonb_row_with_fk
  BEFORE UPDATE ON tbl
  FOR EACH ROW EXECUTE PROCEDURE trg_jsonb_row_with_fk();

This builds subqueries for all FKs from the Postgres catalog tables and executes the SQL command dynamically. For simplicity, I include all user columns from corresponding rows in lookup tables (t.*).

Nitpick: jsonb_build_object(%1$L, t.*), not jsonb_build_object(%1$L, t).
Seems like adding noise, but it avoids a corner case problem: This is supposed to work for any input table, and one might contain a column named t. Then the name t in above expression would resolve to the column instead of the table alias (the whole row). Using t.* removes this ambiguity as it can only resolve to the whole row. (Parentheses would be required to refer to a composite type column, like (t).*). Read the manual here and here.

Since it uses the column name of original FK columns as key name for extended objects, plain concatenation with || does what you need: it replaces the existing simple value with the jsonb object.

Further reading:

Retrieving all PK and FK
How to access NEW or OLD field given only the field's name?


Improving the Original Query

The query as written will use the nonclustered index that you created, but it won't seek into it - the whole index gets scanned.

I reproduced the query and data locally - the DATEDIFF version has a plan that looks like this:

Table 'Events'. Scan count 1, logical reads 28323

 SQL Server Execution Times:
   CPU time = 1891 ms,  elapsed time = 1943 ms.

This plan will scan the Events table once for each row in your #NewEvents temp table.  It filters rows at the Nested Loops join operator after they've already been read.  This is not ideal.

To get a seek, you could write your query like this:

SELECT *
FROM #NewEvents
WHERE EXISTS (SELECT *
    FROM [dbo].[Events] e
    WHERE
    e.EventDate >= DATEADD(dd, -7, #NewEvents.EventDate)
    AND e.EventDate <= #NewEvents.EventDate
    AND e.EventTitle = #NewEvents.EventTitle
    AND e.EventContent = #NewEvents.EventContent
);

That plans looks like this:

Table 'Events'. Scan count 1, logical reads 7

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 149 ms.

For each row in the temp table, there is a seek into your nonclustered index to just get the events that are within 7 days of the new event.  A filter is applied to qualifying rows to check if the title and content are equal.

The nice thing about this seek is that will, at most, read all the events within a 7 day span.  Which should be a fairly small number.

Thoughts about the Indexed View

  I created an index on the view like the one above INCLUDING the computed columns, but for whatever reason I can't get the query to use that index on the view

I don't think the view is really necessary, if the change mentioned above provides reasonable performance.  But you could try adding a WITH (NOEXPAND) hint after the view name in the query to see if you can force the view to be used.


Best table name instead of "user", if the DB doesn't like that

Put some context in the name. If the table is for a CMS, maybe name the table CMS_USER. If it's a reporting tool, REPORT_USER. You could include the application name in it, as ${applicationName}_USER. Failing those, would just APPLICATION_USER be OK? I did once see a table named UZER, along the tradition of naming a variable representing a class as clazz, but I'm not too fond of that one. You could do something like U5ER, since a "5" sort of looks like an "S". Or you could just go totally phonetic and name the table something like YOOZER.


What does a best-practice SQL Server maintenance plan look like?

You have to plan it with the business needs in mind.  Sometimes it's a maintenance plan (like when a shop doesn't have full time DBAs or wants a minimum of complexity.)  Other times it's Ola Hallengren's maintenance scripts.  Whatever you do, it has to take into account the business needs for that data.  I discussed it in my post Ozar's Hierarchy of Database Needs.


How to identify what SSIS package(s) use a given Environment Variable?

Since Environment Variables can be used at either the Project or Package level, this query will indicate said level the variable is used and quickly return the results I want.  Hopefully this will come in handy for someone else in the future.

SELECT    objp.[referenced_variable_name] AS [EnvironmentVariable]
        , fldr.name AS FolderName
        , proj.name AS ProjectName
        , COALESCE('Package: ' + pkg.name, 'Project') AS Scope
        , objp.parameter_name COLLATE Latin1_General_CS_AS AS ParameterName
FROM SSISDB.catalog.object_parameters objp
    INNER JOIN SSISDB.catalog.projects proj
        ON objp.project_id = proj.project_id
    INNER JOIN SSISDB.catalog.folders AS fldr
        ON proj.folder_id = fldr.folder_id
    LEFT JOIN SSISDB.catalog.packages pkg
        ON objp.object_name = pkg.name
        AND objp.project_id = pkg.project_id
-- Only search Projects/Packages that reference Environment variables
WHERE objp.value_type = 'R'
    AND objp.referenced_variable_name LIKE '%SMTP%'


Create log file only for my executed queries

The log_statement in postgresql.conf applies to all users and all databases, but if it's not what you want, you may provide per-database and per-user values, with:

ALTER USER someuser SET log_statement='all';

and/or

ALTER DATABASE dbname SET log_statement='all';

and leave log_statement to 'none' in the global configuration file postgresql.conf.

Also when you're superuser, you can enable logging anytime just for your session with:

SET log_statement='all';

As to what is logged, it can be configured with log_line_prefix but it doesn't go as far as logging tables and columns names. To log which columns are affected by UPDATEs you'd need to do that in triggers in every table, as in tablelog


Mongo WriteConflictException in log file

this does not seem to affect the application, because I am not seeing any special errors and everything seems to work fine.

Write conflict exceptions (WCEs) are an indication from the WiredTiger storage engine that multiple internal writers are trying to update the same data. WiredTiger uses optimistic concurrency control (more specifically Multi-Version Concurrency Control aka MVCC) rather than pessimistic (or lock-based) document updates.

WCEs are an expected (and mostly internal) aspect of this approach: when the storage engine detects conflicts between two concurrent write operations, one will incur a write conflict and be transparently retried. The WCEs are logged for diagnostic purposes but are not exceptions that your application needs to handle.

  Should I be concerned about this?

A small number of write conflict exceptions can be expected and should not have any impact aside from some possible (and generally negligible) latency for internal retries. Your redacted log lines show low retries and do not appear to be concerning in terms of WCEs.

If you are seeing updates with tens or hundreds of write conflict exceptions (or significantly slow execution), it would be worth reviewing the data model and updates in question for potential improvements in efficiency. For example, if a high number of write conflicts was being logged when incrementing a counter in a popular document, you might consider coalescing  N updates of  {$inc: 1} into  a single update of {$inc: N}.

The MongoDB Concurrency FAQ is also worth reviewing for more information.


Is there a way to change the compaction throughput via startup options in Cassandra?

There isn't a command-line option/system property to change the compaction throughput.

You could use nodetool setcompactionthroughput after Cassandra has started.


Relational database primary key requirements

For each relation (ie. table) the primary key value must be unique and immutable (ie. it doesn't change).

You're likely to have a relation for location, one for rooms, one for racks. The primary key values for each of them must all be unique, so two racks in different rooms would not have the same PK value. However you could have the same PK value for a rack and a location, because they are different relations. There would be no significance to the values being the same or different across relations.


Ideal values for wait time in SQL server

What you should think about is not the just absolute values of wait_time_ms or waiting_tasks_count you should also look at the average wait times. When you look at an average wait, you should ask yourself: "Is this a reasonable amount of time to wait on this resource?"

For example (if I am doing the math right) your PAGEIOLATCH wait is in the range of 4-5ms. This is "good" - because that is the response time of a hard drive to fetch an I/O request. However, if you are running on SSD (where this value is expected to linger in the 1ms range), it is "bad".

As another example, your WRITELOG wait is over 10ms. This is "bad" because if you do sequential I/O right, this value (even on spinning rust) should really be in the 1ms range.

However, all of this has to be seen in the context of what you are trying to achieve. If you are looking to "generally make things faster" (not an uncommon request) you need to look at the biggest waits (by wait_time_ms) first as they are most likely to hurt you. If your tuning is more targeted, look at specific wait types, even if they are lower on the list.

For example, you have a few LCK_M_S waits that average 70ms. They don't look important because there are so few of them. But maybe they affect some important users. If this is an OLTP system where queries should get in and out fast - 70ms is a "bad" number (because locks in short running transactions should be held only a few milli- or even microseconds).

Another example: If you are trying to make DML queries faster, you will want to target the WRITELOG and PAGEIOLATCH_EX waits. If you are optimising for reads, you would focus more on either reducing the PAGEIOLATCH_SH wait (for example with SSD or RAM) or making the database do less read I/O (for example by optimising indexing).


Asked to Not Use Transactions and to Use A Workaround to Simulate One

You can not not use transactions in SQL Server (and probably any other proper RDBMS). In the absence of explicit transaction boundaries (begin transaction ... commit) each SQL statement starts a new transaction, which is implicitly committed (or rolled back) after the statement completes (or fails).

Transaction simulation suggested by the person who presents him- or herself as your "DBA" fails to ensure three out of four requisite properties of transaction processing, because it addresses only "soft" errors and is not capable of dealing with "hard" errors, such as network disconnects, power outages, disk failures, and so on.

Atomicity: fail. If a "hard" error occurs somewhere in the middle of your pseudo-transaction, the change will be non-atomic.
Consistency: fail. It follows from the above that your data will be in an inconsistent state following a "hard" error.
Isolation: fail. It is possible that a concurrent pseudo-transaction changes some of the data modified by your pseudo-transaction before yours completes.
Durability: success. Changes you make will be durable, the database server will ensure that; this is the only thing your colleague's approach cannot screw up.

Locks are a widely used and empirically successful method to ensure ACIDity of transactions in all sorts or RDBMSes (this site being an example). I find it very unlikely that a random DBA can come up with a better solution to the concurrency problem than hundreds, possibly thousands of computer scientists and engineers who have been building some interesting database systems over the last, what, 50? 60 years? (I realise this is somewhat fallacious as an "appeal to authority" argument, but I'll stick to it regardless.)

In conclusion, ignore your "DBA"'s advice if you can, fight it if you have the spirit, and come back here with specific concurrency problems if they arise.


Need a better approach in how to design this relation

So I can't see exactly what your primary keys are, but it sounds like your issue isn't with subtypes themselves, but more likely the choice of primary key and path dependence.
So if fluxo has a primary key id_fluxo, and the relation to fluxo_acao is one to many AND duplicates aren't too tightly controlled, you can set up your primary key as:

id_fluxo_acao - bad, just a row pointer
(id_fluxo, id_fluxo_acao) - okay, still a row pointer but no notion of what is unique
(id_fluxo, ordem)  (flow, order?) - better, but per your comment it could update frequently and would require updates to the related subtype.
(id_fluxo, timestamp) - best, provides uniqueness and supplies valuable metadata

Then the primary key of each subtype will contain id_fluxo and you can join directly from fluxo to any of the subtypes.
Your comment:

But lets say that i want to select all the entries in the three tables
from fluxo how can i do this with joins. After addind this other pk in
fluxo_acao

This you can do easily enough with a simple query that can be hardened into a view later:
SELECT
  fluxo.id_fluxo
 ,fluxo.descricao
 ,fluxo.id_status
 ,fluxo.data_criacao
 ,fluxo.id_empresa
 ,fluxo.tipo
 ,fluxo_acao.tipo AS acao_tipo
 ,fluxo_acao.timestamp
 ,fluxo_acao.ordem
 /* columns from each subtype */
FROM
  fluxo fluxo
INNER JOIN
  fluxo_acao fluxo_acao
    ON fluxo_acao.id_fluxo = fluxo.id_fluxo
LEFT JOIN
  fluxo_acao_mensagem fluxo_acao_mensagem
    ON fluxo_acao_mensagem.id_fluxo = fluxo_acao.id_fluxo
        AND fluxo_acao_mensagem.timestamp = fluxo_acao.timestamp /* Not sure what the Portuguese would be for "created at" or "CreateTimestamp" */
LEFT JOIN
  fluxo_acao_pergunta  fluxo_acao_pergunta
    ON fluxo_acao_pergunta.id_fluxo = fluxo_acao.id_fluxo
        AND fluxo_acao_pergunta .timestamp = fluxo_acao.timestamp
LEFT JOIN
  fluxo_acao_escolher_fila  fluxo_acao_escolher_fila
    ON fluxo_acao_escolher_fila.id_fluxo = fluxo_acao.id_fluxo
        AND fluxo_acao_escolher_fila.timestamp = fluxo_acao.timestamp

If there is a need for only one of the subtypes (and don't need the column ordem) you can just query the individual table directly.


SQL Server Agent unable to view network drives

Thank you for all the help. UNC file paths was the solution.

Another thing to check is that you refer to the output location with a UNC path instead of a mapped letter drive. Letter drive mappings aren't reliable from something running as a service.  – Doug Deden 2 days ago

Here is some more information regarding mapped drives and services: https://stackoverflow.com/questions/182750/map-a-network-drive-to-be-used-by-a-service – G.Smith 2 days ago


The order of the values in the in clause is not significant.

the rows may be visited in any order, this order can change at any time.

however with such a small set of targets and in the presence of an ordered index (not a hash index) the records will probably be visited in index order.