Saving and managing multiple database "states"?

SQL Server provides various ways to maintain states of the table + you can design your own:

Change data capture : CDC is use to capture insert, update or delete operation on a table. But remember, it does not support computed columns.
Change tracking: This also capture DML statements like CDC but does not hold historical data.
SCD Type 2: By creating your own historical data change table by using timestamp and mark the latest record as active.
Microsoft Sync Framework : You can use this if you want to have a replication of database. I used it when I had to do the sync between offline SQL Server to online. However, this may cost effort in implementation and also it will not provide you all the features you are looking for.

For future releases (i.e. SQL Server 2016 and above)

Temporal Tables: I did use that in my experiments on SQL Server 2016 CTP 2 and found it promising. In a way you can consider them as SCD type 2.

If I have to choose at your place than I would go for CDC.


You might want to decompose the support tickets fact table into transactions; User w on Date x moved ticket y to state z etc. This would facilitate metrics like "bounced tickets" etc. which service desk managers always seem to be keen on. This could be supplemented with the Accumulating Snapshot table you already have. Look here http://www.kimballgroup.com/2012/05/01/design-tip-145-time-stamping-accumulating-snapshot-fact-tables/ for a couple of options to implement this scenario.


Retrieving changed rows only from a table with 10^7 rows

If the version was 9.3 or newer, I would try this rewrite of the query:

SELECT
    w.stamp,
    w.column1,
    w.column2,
    w.column3
FROM
    "table_name" AS w
  JOIN LATERAL
    ( SELECT p.column1, p.column2, p.column3
      FROM "table_name" AS p
      WHERE p.stamp < w.stamp
        AND p.stamp >= '2015-12-01'::timestamp
      ORDER BY p.stamp DESC
      LIMIT 1
    ) AS p
    ON (w.column1, w.column2, w.column3)
       IS DISTINCT FROM
       (p.column1, p.column2, p.column3)
WHERE w.obj_id = 42
  AND w.stamp >= '2015-12-01'::timestamp
  AND w.stamp <= '2016-01-23'::timestamp
ORDER BY w.stamp DESC
LIMIT 100 ;

with the index you have and with an index on (obj_id, stamp DESC)

Unrelated to efficiency: are you sure that you want to use BETWEEN with timestamps? It's more common to work with inclusive-exclusive ranges. In other words, shouldn't this condition:

  AND w.stamp <= '2016-01-23'::timestamp

be?:

  AND w.stamp  < '2016-01-23'::timestamp

With BETWEEN (and <=), you are searching though a range that is 53 whole days long + 1 microsecond (or whatever the accuracy of timestamp is).


If you're asking why one of these plans is more expensive than the other one:

Your first plan performs a sort on a column that is not indexed (or not part of the index that is being used to retrieve the data). Therefore once the data is retrieved, because you've asked for an ORDER BY, the results have to be re-sorted (since the clustered index will return the data in a different order).

An index that will help this specific query with the sort be more efficient might be (and I'm not suggesting this would be ideal):

CREATE NONCLUSTERED INDEX cou_com ON dbo.Customers(Country, CompanyName)
  INCLUDE (CustomerID, City, Region);

But please don't go create that index without much more proper investigation. An index might help this one query but if your system is DML-bound (meaning more write activity than read activity) that could just make it worse. If this query is not slow and you are just concerned that it has a higher relative cost than some other query, this is not where you should start optimizing IMHO.

If you're asking generically how to analyze execution plans:

This is not really the purpose of the site. For some resources (books and e-books) on performance tuning in general, I would check out this question and its answers. In particular, Grant Fritchey's free e-book SQL Server Execution Plans.

You may also be interested in the free SentryOne Plan Explorer. It presents a lot more information - information that, in Management Studio, is hidden in Properties panels, incorrect in some cases, or simply not available at all. I blogged about how to present the benefits of this tool to other users, but it could be a tutorial of sorts as well.


Postgresql import from SQL Server - how to auto-generate sequences

In CREATE table you can use bigserial/serial type

CREATE TABLE xx (
 id  serial,
 data text
);

if you need to create sequence then
CREATE SEQUENCE serial START 1;

"Simple" way to import data from MSSQL is following
CREATE TABLE and replace IDENTITY with serial
Import all data. If you dont import id values, Postgresql replaces them with with new values. Example

INSERT into xx (data) VALUES ('test') ;

This creates new id for  row

 INSERT into xx (id, data) VALUES (1, 'test') ;

this one does not.
So import data with id and run

 select setval('xx_id_seq', max(id)) FROM xx

xx_id_seq is default naming convention where xx is table name id is column and seq id for sequence ( in my system pgadmin3 creates table with that logic)

If you want to automate whole process you need to write lot of code which reads INFORMATION_SCHEMA and does dynamic SQL , but i assume that it is not the question.

For more information see manual

sequences see : http://www.postgresql.org/docs/9.3/static/sql-createsequence.html

manipulating sequnces see: http://www.postgresql.org/docs/9.3/static/functions-sequence.html

datatypes see: http://www.postgresql.org/docs/9.3/static/datatype.html


Improve update performance in PostgreSQL data warehouse (RDS)

Have you considered the possibility that WAL (Write-Ahead Logging) is slowing down your update? Also see this more detail explanation.

This is a common problem for UPDATEs, INSERTs and ALTER TABLE operations on very large tables. According to my understanding, for any row affected by an update--even if that update is only on a single column--Postgres archives and replaces the entire row and updates all indexes on all indexes columns. While Postgres's implementation of WAL is extremely effective for maintaining data integrity in a transactional setting, it can seriously degrade performance for operations on large tables--particularly for data warehouses, where bulk updates involving many records are common.

I would need to know more about the table being updated to know if WAL is the culprit. Is a large proportion of records (say 30% or more) affected by the update? You stated that the value column is not indexed, but are there indexes on other columns? If the answer to both questions is yes, then I would strongly suspect WAL.

An effective solution to this problem is to create a new, unindexed table using the CREATE TABLE AS method described here. You will need to rebuild indexes, keys and constraints on the new table, but this is still much faster than updating-in-place. See also the related answer here.

The downside to the CREATE TABLE AS approach is that a simple UPDATE query becomes a monster multi-statement transaction. The latter code is not only cumbersome but also fragile: the table schema must be repeated for every update that uses this approach. Imagine a data warehouse pipeline with dozens of such updates. Any change to the table schema must be hard-coded into every single update operation.

As an alternative approach, I suggest you first try stripping all indexes not involved in the update (joins or where clause), then use a regular UPDATE statement and rebuild the indexes. Depending on the dimensions of you table, its indexes, the complexity of the update and the number of rows involved, an update-in-place may nearly as fast as the "CREATE TABLE AS" method, and your code will be simpler and more stable.


How to use MariaDB's new "Invisible" column type

The INVISIBLE is an attribute of the column it can be set CREATE TABLE and ALTER TABLE statements.

I've updated the reference page document to show this.

 CREATE TABLE t (x INT, y INT INVISIBLE, z INT INVISIBLE NOT NULL DEFAULT 4);

 DESC t;

Field | Type    | Null | Key | Default | Extra
:---- | :------ | :--- | :-- | :------ | :--------
x     | int(11) | YES  |     | null    |
y     | int(11) | YES  |     | null    | INVISIBLE
z     | int(11) | NO   |     | 4       | INVISIBLE

ALTER TABLE t MODIFY x INT INVISIBLE, MODIFY y INT, MODIFY z INT NOT NULL DEFAULT 4

DESC t

Field | Type    | Null | Key | Default | Extra
:---- | :------ | :--- | :-- | :------ | :--------
x     | int(11) | YES  |     | null    | INVISIBLE
y     | int(11) | YES  |     | null    |
z     | int(11) | NO   |     | 4       |

db<>fiddle here


Imposing column level security on a view for different user

There are several options:

You can use synonyms (and as many views as there are different cases), as Phil showed in his answer.
This is a feature of Virtual Private Database, included in the Enterprise Edition.
You can do it by hand with a little code

Here's an example of how you could do it yourself:

CREATE TABLE base_table (a NUMBER, b NUMBER, c NUMBER, d NUMBER, e NUMBER, f NUMBER);

CREATE TABLE column_rights (username VARCHAR2(30) PRIMARY KEY,
                            a NUMBER, b NUMBER, c NUMBER, d NUMBER, e NUMBER, f NUMBER);

INSERT INTO column_rights VALUES ('P', 1, 1, 1, 1, NULL, NULL);
INSERT INTO column_rights VALUES ('Q', 1, 1, 1, 1, 1, 1);
INSERT INTO column_rights VALUES ('R', 1, NULL, NULL, 1, 1, 1);

CREATE OR REPLACE VIEW my_view AS
SELECT b.a * c.a a,
       b.b * c.b b,
       b.c * c.c c,
       b.d * c.d d,
       b.e * c.e e,
       b.f * c.f f
  FROM base_table b
  JOIN column_rights c ON c.username = USER;

 GRANT SELECT ON my_view TO P; -- Q, R

The above assumes that all fields are NUMBER, but this would work with anything if you use CASE. This also assumes that the users are defined at the database level, but you could also use a custom identification scheme (replace USER by your function -- V('USER') in APEX).

In a more general setting, you could use a COLUMN_RIGHTS table with TABLE_NAME and COLUMN_NAME columns (this would unfortunately require N lookups where N is the number of columns).

You could also use a single INTEGER field or RAW field for the concatenated bit representation of the column rights so that all the information would still be in a single row and this could be used for multiple views.


MATCH AGAINST one character words returns empty rows with ft_min_word_len = 1

mysql> SHOW GLOBAL VARIABLES like 'ft_min_word_len';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| ft_min_word_len | 1     |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> SELECT event.id AS eid,
              event.name AS ename
       FROM event
       WHERE MATCH(event.name) AGAINST('+W*' IN BOOLEAN MODE);
+-----+--------+
| eid | ename  |
+-----+--------+
|   1 | W.W.W. |
|   7 | W      |
|  12 | Wa     |
|  11 | Wat    |
|  10 | Wats   |
|   9 | Watso  |
|   8 | Watson |
+-----+--------+
7 rows in set (0.01 sec)

Maybe you are using InnoDB:

mysql> ALTER TABLE event ENGINE=InnoDB;
Query OK, 12 rows affected (0.51 sec)
Records: 12  Duplicates: 0  Warnings: 0

mysql> SELECT event.id AS eid,
       event.name AS ename
       FROM event
       WHERE MATCH(event.name) AGAINST('+W*' IN BOOLEAN MODE);
+-----+--------+
| eid | ename  |
+-----+--------+
|   8 | Watson |
|   9 | Watso  |
|  10 | Wats   |
|  11 | Wat    |
+-----+--------+
4 rows in set (0.00 sec)

Where the option to change is innodb_ft_min_token_size (ft_min_word_len is for MyISAM only).


How to collapse into a single row from multiple rows


Single Server (Replica) Availability Group

First time caller, long time listener.

Ha! Love it! Welcome :)

  I've tested this situation on a few test servers and its working as expected.

You are correct, it should work without many if any issues. Since clustering will now be part of the scenario you don't get a free single node lunch... you're still beholden to any cluster or AG level issues that may arise (I'm not saying they will, just that it's now in the issues list).

  My question is has anyone tried this and had any issues?

I have, in fact, tried this. It does work, the 2-months part does give me a little bit of uneasiness... but assuming the configuration doesn't drift, you should be alright.

You can also pre-stage a listener by creating a DNS entry with the same name so connection strings don't change, use a network appliance or hardware load balancer, etc. The largest issue is enabling the Always On manager and the service restart required for it, in my opinion. All else (except the listener unless you do the DNS/HLB thing) should or can be transparent.

  Would anyone recommend against this solution.

I only have the reservations of getting used to the single server performance characteristics and then adding in a synchronous replica or such afterwards. Since the single node AG won't have sync_commit waits and such, you really won't be in for much of a shock... though this could change once the other node is added. Just something to think about.


Error While restoring DB2 db from AIX to AIX server?

Z ON DBNAME_NODE0000.out instructs the DB2 command line processor to redirect all output to a file named DBNAME_NODE0000.out in the current directory. Apparently, you don't have permissions to create files in that directory.


I have deleted a table, by exporting over the same table, and selecting the 'drop destination table before insertion'

As it turns out, we did have backups. I restored the backup on another server, and just copied the required table to the production database. Of course, I did it during such a time, where I was sure that no new records would have been saved. Just to be sure, I stopped the web application before doing this.

The idea came from the answer in the question that was linked by Darin Strait in the comments.


Phone number with different formats lookup

To clean up the numbers use a regex that removes everything that's not a numeric digit:
regexp_replace(phone_number, '[^0-9]', '', 'g')

You can create an index on that, but a regular B-Tree index won't be used LIKE conditions where the wildcard is on the left.
In Postgres you have two choices: you can create a regular B-Tree index on the reverse value and use that instead:
create index on patients ( reverse(regexp_replace(phone_number, '[^0-9]', '', 'g')) );

Then use that expression and compare it with the reversed input:
select *
from patients
where reverse(regexp_replace(phone_number, '[^0-9]', '', 'g')) like reverse('4169586390')||'%'

The other choice is to install the pg_trgm extension and create a trigram index on the expression. That way you can use the input "as is":
create index on patients
   using gist ( regexp_replace(phone_number, '[^0-9]', '', 'g') gist_trgm_ops);

That index is then usable for:
select *
from patients
where regexp_replace(phone_number, '[^0-9]', '', 'g') like '%4169586390'

However, GiST indexes are bigger and a bit slower to maintain than B-Tree indexes.

If you don't want to repeat that regex expression in every query you can create a view that includes that, e.g.:
create view patients_clean_phones
as
select ... other columns ..., regexp_replace(phone_number, '[^0-9]', '', 'g') as clean_phone
from patients;

Then use (assuming you are using the GiST index, so no reverse required):
select *
from patients_clean_phones
where clean_phone like '4169586390%';

Postgres is smart enough to still use the index in that case.

If you are on Postgres 12, an alternative to the view is to use a computed column with the regex expression, then create the index on the computed column.


is it possible to create a dynamic website without a database?(Only use a File to save Data)

It depends on how complex your web is.

I assume your web is contact mangager (create,update,delete) for 1 user only, you can storage records in a csv file on web server, it's not too difficult.

But if your contact manager serve many users, you have to implement concurrency, transaction for you text file. Its not easy.


This can be achieved with the expdp CONTENT flag:

expdp username/password DIRECTORY=directory_object_name \
                        DUMPFILE=dumpfile_name \
                        TABLES=table_names|TABLESPACES=tablespace_names|FULL=y \
                        CONTENT=metadata_only


Similar questions are asked regularly on this site. This usually boils down to end of line characters. Is your MySQL Unix/Linux or Windows? Use a hex editor to examine your scripts and see if you can find inconsistencies at line ends.

If this fails, you could take a dump using mysqldump and then use sed/awk/grep (or the editor of your choice - i.e. Notepad++) to put the file into a format suitable for SQL Server Express (dates tend to be the tricky ones here).

If you can access the MySQL database using phpMyAdmin, you should be able to use mysqldump.


To remove the linked server provider, uninstall the OleDb Provider.  For Oracle XCopy use the uninstall.bat in the installation folder, eg
PS C:\odacinstall> .\uninstall.bat oledb c:\oracle\odac64


mysql multiple unique values per primary key

You just need a composite (a.k.a. compound or multi-column) PRIMARY KEY:

CREATE TABLE callback
(
    f_unti_id varchar(10) NOT NULL,
    phone_number varchar(10) NOT NULL,

    -- Compound PK
    PRIMARY KEY(f_unti_id, phone_number)
);

(Actually, a UNIQUE constraint on multiple columns would suffice. Check How do I specify unique constraint for multiple columns in MySQL?).

Try it a http://sqlfiddle.com/#!9/9882a/1/0


Error granting SELECT on view that uses ALL_OBJECTS

Yes, use READ privilege (introduced in 12c) instead of SELECT.

SQL> create user MY_OWNER_SCHEMA identified by MY_OWNER_SCHEMA;

User created.

SQL> create user MY_LOGIN_USER identified by MY_LOGIN_USER;

User created.

SQL> grant create session, create view to MY_OWNER_SCHEMA;

Grant succeeded.

SQL> conn MY_OWNER_SCHEMA/MY_OWNER_SCHEMA
Connected.
SQL> CREATE OR REPLACE VIEW OBJECT_COMMENTS AS
SELECT OBJS.OWNER, OBJS.OBJECT_NAME, COMMENTS.COMMENTS
FROM ALL_OBJECTS OBJS
LEFT JOIN ALL_TAB_COMMENTS COMMENTS ON (
    COMMENTS.OWNER = OBJS.OWNER
    AND COMMENTS.TABLE_NAME = OBJS.OBJECT_NAME
)
WHERE
  2    3    4    5    6    7    8    9      OBJS.OWNER = 'MY_OWNER_SCHEMA'
    AND OBJS.OBJECT_TYPE IN ('TABLE', 'VIEW')
WITH READ ONLY
; 10   11   12

View created.

SQL> GRANT SELECT ON OBJECT_COMMENTS TO MY_LOGIN_USER;
GRANT SELECT ON OBJECT_COMMENTS TO MY_LOGIN_USER
                *
ERROR at line 1:
ORA-01720: grant option does not exist for 'SYS.ALL_OBJECTS'

Above fails, but:

SQL> GRANT READ ON OBJECT_COMMENTS TO MY_LOGIN_USER;

Grant succeeded.


SQL Merge Replication: Cannot resolve conflict

Primary key conflicts/violations will not be resolved automatically.  You will need to manually delete one of the offending rows to get the primary key conflicts from reoccurring on every synchronization.

If you expect primary key conflicts to occur regularly then you may want to consider doing one of the following:

Horizontally partition your data by adding a location specific identifier column and extend the primary key(s) to include this column.  Then have subscribers only insert rows belonging to their partition.
Set the Merge article property @compensate_for_errors to true which sends a compensating change to the source replica to undo the failed change when a primary key conflict occurs.


Can't upgrade a SQL Express to SQL Standard

Your problem is you are trying to install Service Pack 4 for SQL Server 2008 (the original release, not R2) on a SQL Server 2008 R2 instance.

Despite the similar names, SQL Server 2008 and SQL Server 2008 R2 are different releases, with different Service Packs.

Apply the correct Service Pack for SQL Server 2008 R2 (the latest is SQL Server 2008 R2 Service Pack 3), then you should be able to upgrade to 2014 Standard with no problem.


IMHO (and as Paparazzi has pointed out on comments) the easiest way to accomplish it and get better performance is using a single table by adding a CompanyID field.

CREATE TABLE Customers
(
    CompanyID   <some_type> REFERENCES Companies (CompanyID),
    CustomerID  <some_type> REFERENCES Customers (CustomerID),
    Name        text,
    PRIMARY KEY (CompanyID, CustomerID)
);

If you cannot modify your actual table schema, you could use two materialized views, one for each company, and add a clustered index on (company_id, id) or (id, company_id) depending on your queries.

I've set up a fiddle here

select company_id, id, name
from (
      select company_id, id, name from dbo.vcustA
      union all
      select company_id, id, name from dbo.vcustB
     ) x
where id = 2;

Execution plan for two materialized views using UNION:

But in fact, this is worst than create a new table with all records of both tables. Instead of use two materialized views, I'd suggest to create a new table and, using triggers, add/update/delete all records from customersA and customersB. Or set up a daily job that synchronize this information.


Efficient INSERT INTO a Table With Clustered Index

As the other answers already indicate SQL Server may or may not explicitly ensure that the rows are sorted in clustered index order prior to the insert.

This is dependant upon whether or not the clustered index operator in the plan has the DMLRequestSort property set (which in turn depends upon the estimated number of rows that are inserted).

If you find that SQL Server is underestimating this for whatever reason you might benefit from adding an explicit ORDER BY to the SELECT query to minimize page splits and ensuing fragmentation from the INSERT operation

Example:

use tempdb;

GO

CREATE TABLE T(N INT PRIMARY KEY,Filler char(2000))

CREATE TABLE T2(N INT PRIMARY KEY,Filler char(2000))

GO

DECLARE @T TABLE (U UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),N int)

INSERT INTO @T(N)
SELECT number
FROM master..spt_values
WHERE type = 'P' AND number BETWEEN 0 AND 499

/*Estimated row count wrong as inserting from table variable*/
INSERT INTO T(N)
SELECT T1.N*1000 + T2.N
FROM @T T1, @T T2

/*Same operation using explicit sort*/
INSERT INTO T2(N)
SELECT T1.N*1000 + T2.N
FROM @T T1, @T T2
ORDER BY T1.N*1000 + T2.N

SELECT avg_fragmentation_in_percent,
       fragment_count,
       page_count,
       avg_page_space_used_in_percent,
       record_count
FROM   sys.dm_db_index_physical_stats(2, OBJECT_ID('T'), NULL, NULL, 'DETAILED')
;

SELECT avg_fragmentation_in_percent,
       fragment_count,
       page_count,
       avg_page_space_used_in_percent,
       record_count
FROM   sys.dm_db_index_physical_stats(2, OBJECT_ID('T2'), NULL, NULL, 'DETAILED')
;

Shows that T is massively fragmented

avg_fragmentation_in_percent fragment_count       page_count           avg_page_space_used_in_percent record_count
---------------------------- -------------------- -------------------- ------------------------------ --------------------
99.3116118225536             92535                92535                67.1668272794663               250000
99.5                         200                  200                  74.2868173956017               92535
0                            1                    1                    32.0978502594514               200

But for T2 fragmentation is minimal

avg_fragmentation_in_percent fragment_count       page_count           avg_page_space_used_in_percent record_count
---------------------------- -------------------- -------------------- ------------------------------ --------------------
0.376                        262                  62500                99.456387447492                250000
2.1551724137931              232                  232                  43.2438349394613               62500
0                            1                    1                    37.2374598468001               232

Conversely sometimes you might want to force SQL Server to underestimate the row count when you know the data is already pre-sorted and wish to avoid an unnecessary sort. One notable example is when inserting a large number of rows into a table with a newsequentialid clustered index key. In versions of SQL Server prior to Denali SQL Server adds an unnecessary and potentially expensive sort operation. This can be avoided by

DECLARE @var INT =2147483647

INSERT INTO Foo
SELECT TOP (@var) *
FROM Bar

SQL Server will then estimate that 100 rows will be inserted irrespective of the size of Bar which is below the threshold at which a sort is added to the plan. However as pointed out in the comments below this does mean that the insert will unfortunately not be able to take advantage of minimal logging.


You can use STUFF function in this way:

DECLARE @Columns nvarchar(max);

SET @Columns = STUFF((SELECT ', ' + QUOTENAME(Title)
                      FROM tempTable1
                      ORDER BY [Count]
               FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)'), 1, 1, '');

SELECT @Columns;

| (No column name)                     |
| :----------------------------------- |
|  [Mrs.], [Sra.], [Sr.], [Ms.], [Mr.] |

db<>fiddle here

Alternatively and depending on your SQL-Server version you can use STRING_AGG function:

DECLARE @Columns nvarchar(max);

SELECT
    STRING_AGG(QUOTENAME(Title), ',') WITHIN GROUP (ORDER BY [Count])
FROM
    tempTable1;

SELECT @Columns;


How to prevent duplicates while storing result from different application

You can add unique key constraint on the db table , the table itself will prevent the second application to insert duplicate data and return error code such as "unique key violation" ,  in the application you will need to handle this error/exception.


Why SELECT COUNT() query execution plan includes left joined table?

If ForeignId, ForeignTable, IsMain is not known* to be unique in ExternFile, then the QO will need to include that table to work out the count. Any time multiple rows match, the count will be affected.

Join Simplification in SQL Server
Designing for simplification (SQLBits recording)

* The optimizer does not currently recognize filtered unique indexes as unique

UPDATE (by OP):
The solution is to change line in query from LEFT JOIN (which can produce multiple rows):

LEFT JOIN ExternFile ON realty.Id = ExternFile.ForeignId AND ExternFile.IsMain = 1 AND ExternFile.ForeignTable = 5

to OUTER APPLY with TOP (which produce one row and does not affect COUNT)

OUTER APPLY (SELECT TOP (1) ServerPath FROM ExternFile WHERE ForeignId = realty.Id AND IsMain = 1 AND ForeignTable = 5) AS ExternFile

The query is now more effective. Adding a unique index could not be done, because values weren't unique, they were unique only for combination in the condition and this is not considered as unique as mentioned above.


Unable to copy a DB from SQL Server 2000 to SQL Server 2012 using copy wizard

The supported upgrade path does not go directly from 2000 to 2012.  You will have to have an intermediate step where you upgrade or restore those DBs to a 2005 or 2008 R2 instance.  Once you've done that, you can upgrade to 2012 or take a backup of the DBs in 2005, then restore them on to 2012.

We are migrating to 2014 as my company is on 2005 SP3 right now.


TL;DR: You cannot audit the roles usage.

You can however use the following views to determine which system privileges, object privileges, and other roles are granted to the roles which are in turn granted to the current user:

ROLE_ROLE_PRIVS describes the roles granted to other roles. Information is provided only about roles to which the user has access.
ROLE_SYS_PRIVS describes system privileges granted to roles. Information is provided only about roles to which the user has access.
ROLE_TAB_PRIVS describes table privileges granted to roles. Information is provided only about roles to which the user has access.

The only complicated thing here is privileges and roles inherited from other roles. If it's not the case for you, and the roles granted to your user have one level of inheritance, i. e. these roles are not children of any other roles, then you can just use the following queries to determine the privileges inherited by a particular user from the roles.

Determine system privileges:

select role, privilege
  from role_sys_privs
  where role in (select granted_role from dba_role_privs
                   where grantee = 'SPONGEBOB');

      ROLE            PRIVILEGE
----------    -----------------
 INDEX_MGR     CREATE ANY INDEX
 INDEX_MGR       DROP ANY INDEX

Determine object privileges:

select role, owner, table_name, column_name, privilege
  from role_tab_privs
  where role in (select granted_role from dba_role_privs
                   where grantee = 'SPONGEBOB');

      ROLE     OWNER      TABLE_NAME     PRIVILEGE
----------    ------    ------------    ----------
  HR_QUERY        HR     DEPARTMENTS        SELECT
  HR_QUERY        HR       EMPLOYEES        SELECT

In this example the user SPONGEBOB is assigned two roles INDEX_MGR and HR_QUERY from which he inherits system and object privileges. But what if these roles inherit privileges from other roles? For such case let's implement some more complex model:

APP_USER                  CREATE SESSION
 \
  APP_UNLIM_USER          UNLIMITED TABLESPACE
   \
    INDEX_MGR             CREATE ANY INDEX, DELETE ANY INDEX
            |
        SPONGEBOB
            |
     HR_QUERY             SELECT ON HR.EMPLOYEES, SELECT ON HR.DEPARTMENTS

As you might guess, APP_USER is a role granted to another role APP_UNLIM_USER, which in turn is granted to the role INDEX_MGR. The roles INDEX_MGR and HR_QUERY are granted directly to the user SPONGEBOB. In the diagram, all the privileges granted to the roles are put in front of the roles' names to which they are granted in the corresponding lines, e. g. APP_USER role is granted only CREATE SESSION privilege.

Let's query ROLE_ROLE_PRIVS view described earlier on behalf of the SPONGEBOB user:

select role, granted_role from role_role_privs;

           ROLE       GRANTED_ROLE
---------------    ---------------
      INDEX_MGR     APP_UNLIM_USER
 APP_UNLIM_USER           APP_USER

From the result, you can see that the role inheritance matches the one shown in the diagram. Now, we can use the role names returned by the query to determine what privileges these roles are granted.

First, let's present the roles' names in one column:

select role           from role_role_privs
union
select granted_role   from role_role_privs;

           ROLE
---------------
 APP_UNLIM_USER
       APP_USER
      INDEX_MGR

Then we'll determine which roles are assigned to the user SPONGEBOB directly. As you can see on the diagram above, these are HR_QUERY and INDEX_MGR:

select username, granted_role from user_role_privs;

  USERNAME     GRANTED_ROLE
----------    -------------
 SPONGEBOB         HR_QUERY
 SPONGEBOB        INDEX_MGR

Now, we'll combine the two previous queries together to have all roles' names in one result:

select role         from role_role_privs
union
select granted_role from role_role_privs
union
select granted_role from user_role_privs;

             ROLE
  ---------------
   APP_UNLIM_USER
         APP_USER
         HR_QUERY
        INDEX_MGR

Having obtained all the roles' names, we can now determine which privileges are granted to that roles. (In the examples below I replaced the previous query code with MYROLES for brevity.)

System privileges:

select role, privilege from role_sys_privs where role in (MYROLES);

           ROLE                PRIVILEGE
---------------    ---------------------
      INDEX_MGR           DROP ANY INDEX
       APP_USER           CREATE SESSION
      INDEX_MGR         CREATE ANY INDEX
 APP_UNLIM_USER     UNLIMITED TABLESPACE

Object privileges:

select role, owner, table_name, privilege
  from role_tab_privs where role in (MYROLES);

     ROLE     OWNER      TABLE_NAME     PRIVILEGE
---------    ------    ------------    ----------
 HR_QUERY        HR     DEPARTMENTS        SELECT
 HR_QUERY        HR       EMPLOYEES        SELECT

Since you now have all the privileges shown in a user-friendly format, you can automatically construct the auditing statements to determine which privileges are actually used. For example, the following query will return you the audit statements which you can use to check what system privileges are used:

select privilege, 'audit ' || privilege || ' by spongebob whenever successful;' stmt
  from role_sys_privs where role in (MYROLES);

                                                                STMT
--------------------------------------------------------------------
              audit DROP ANY INDEX by spongebob whenever successful;
              audit CREATE SESSION by spongebob whenever successful;
            audit CREATE ANY INDEX by spongebob whenever successful;
        audit UNLIMITED TABLESPACE by spongebob whenever successful;

The following query returns the object auditing statements:

select 'audit ' || privilege || ' on ' || owner || '.' || table_name
       || ' whenever successful;' stmt
  from role_tab_privs where role in (MYROLES);

                                                STMT
----------------------------------------------------
 audit SELECT on HR.DEPARTMENTS whenever successful;
   audit SELECT on HR.EMPLOYEES whenever successful;

In the same vein, you can construct NOAUDIT statements to stop auditing the privileges.


How can I order rows returned by the RETURNING clause of a DML statement?

You need to apply an ORDER BY to the result. To do that, put the DELETE statement into a common table expression. Then you can apply an ORDER BY when you select from it

with deleted as (
  DELETE FROM @tableName
  WHERE id = ANY (
        SELECT id
        FROM @tableName
        WHERE source = :p1 AND target = :p2 @readCondition
        ORDER BY createddate
        LIMIT @limit
        FOR UPDATE SKIP LOCKED)
  RETURNING *
)
select *
from deleted
order by createddate;


SQL Partioning on date, how does it work for future records?

In SQL Server you have to split the partition at the head of the table:

ALTER PARTITION SCHEME partition_scheme_name NEXT USED [ filegroup_name ];
ALTER PARTITION FUNCTION partition_function_name() SPLIT RANGE ( '20120401');

This will add a new partition for new incoming data. You should always an empty partition at the head of the table to make the split a instant metadata-only operation. If you populated the last partition with rows then splitting has to move data.


Row vs Columnar DB for queries with multiple OR statements

The examples in this answer are written from a SQL Server point of view. To repeat back the problem, you want fast queries when the WHERE clause is a series of OR statements. Queries will filter against 4 to 20 different columns and you don't know the columns ahead of time. The first query might look like this:
SELECT COUNT(*)
FROM #Q273599
WHERE ID1 = 1 OR ID2 = 2 OR ID4 = 4 OR ID5 = 5;

and the second query might look like this:
SELECT COUNT(*)
FROM #Q273599
WHERE ID1 = 1 OR ID2 = 2 OR ID8 = 8 OR ID9 = 9 OR ID10 = 10;

This is still a difficult problem for relational databases depending on the size of the table and the required query response times. The fastest method will likely be to define a single column index on every column and to use an RDBMS with a query optimizer that is able to find an index union plan. Creating an index on every column may be impractical from a storage, capacity limit, or DML overhead point of view.
Very generally, it is fair to say that columnar storage will be better for this type of query than row store. Microsoft lists a similar problem (end users searching by hundreds of different filters on a real estate website) as a good case study for the effectiveness of columnstore. I think it's as simple as if you're going to scan the whole table anyway, you might as well scan a smaller table due to the typically better compression that columnar storage provides compared to rowstore. Not needing all of the columns from the table will of course make columnar more attractive compared to row store.
If you're really concerned about performance I recommend mocking up some sample data and trying things out. For the table and query below, I ended up with a 4 second response time for a row store query, a 1 second response time for columnstore, and a 13 ms response time when all columns were indexed. This is just an example to illustrate the general point. Your data is an important part of the question.
CREATE TABLE #Q273599 (
    ID1 BIGINT NOT NULL,
    ID2 BIGINT NOT NULL,
    ID3 BIGINT NOT NULL,
    ID4 BIGINT NOT NULL,
    ID5 BIGINT NOT NULL,
    ID6 BIGINT NOT NULL,
    ID7 BIGINT NOT NULL,
    ID8 BIGINT NOT NULL,
    ID9 BIGINT NOT NULL,
    ID10 BIGINT NOT NULL,
    PADDING CHAR(500) NOT NULL
);

INSERT INTO #Q273599 WITH (TABLOCK)
SELECT q.RN, RN, RN, RN, RN, RN, RN, RN, RN, RN, ''
FROM
(
    SELECT TOP (25000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
    CROSS JOIN master..spt_values t3
) q;

SELECT COUNT_BIG(*)
FROM #Q273599
WHERE ID1 = 1 OR ID2 = 2 OR ID4 = 4 OR ID5 = 5 OR ID6 = 6 OR ID7 = 7 OR ID9 = 9 OR ID10 = 9999999999999
OPTION (MAXDOP 1);


SQL Server 2008 R2 Express single CPU core usage and need of upgrade to SQL Server 2014 Express

Express editions are limited, see Features Supported by the Editions of SQL Server:

  Maximum Compute Capacity Used by a Single Instance: Limited to lesser of 1 Socket or 4 cores
  Maximum memory utilized: 1 GB
  Maximum relational Database size: 10GB

These limitations apply to 2008R2, 2012 and 2014 versions, and for 2005 and 2008 the max database size is even less: 4GB (CPU and memory restrictions are the same).

To troubleshoot your performance problems I recommend you read How to analyse SQL Server performance. If you discover that (likely) you are hitting the Express Edition limitations then you need to upgrade to a paid license edition (probably Standard). You will know you are hitting the limitations:

if you see CPU contention you'll see high SOS_SCHEDULER_YIELD wait times and high CPU perf counters on only 4 cores while otehr are idle.
symptoms of the 1GB memory utilization you'll see high PAGEIOLATCH_XX wait times and high disk utilization on OS counters (low memory triggers more IO).

Upgrading Express Edition from 2008R2 to 2012 should be a trivial matter, same for 2014. I don't think there is any feature in 2014 or 2012 that you could leverage in Express Edition, but reasons to choose the latest version always exists: T-SQL language enhancements, product support. See What's new in SQL Server 2014 and What's new in SQL Server 2012.


Calculation of two dependent variables in Access Query

I've got an answer from my friend below but it works very slow for my actual database.

Anyone has more efficient way doing it?

Table1

| ID n  c  d |
|------------|
| 1  1  1  2 |
| 2  2  0  2 |
| 3  3  0  2 |
| 4  4  1  2 |
| 5  5  2  2 |
| 6  6  3  2 |
| 7  7 -9  2 |

n=ID but n is number variable
sql:

SELECT Table1.n
    ,[b] + [c] AS a
    ,IIf([n] < 2, 2, IIf(DLookUp("[a]", "Query2", "[table1].[n]=" & [table1].[n] - 1) <> 0
         , DLookUp("[a]", "Query2", "[table1].[n]=" & [table1].[n] - 1), [d])) AS b
    ,Table1.c
    ,Table1.d
FROM Table1;


Does PostgreSQL and other databases have a feature to reset serial/identity values?

In Postgres you can use ALTER SEQUENCE RESTART ...  or setval() function to change that "internal counter" without truncating the table.

See also http://www.sqlines.com/postgresql/datatypes/serial for examples.

In SQL Server you can use DBCC CHECKIDENT to see and reset identity value of the table.


Create Temp Table From Stored Procedure Result

See following  question from stack overflow. According to this question you can use

CREATE PROCEDURE getDatabaseList
AS
BEGIN
    SELECT * FROM sys.databases
END

 SELECT * INTO #MyTempTable FROM
OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;','EXEC getDatabaseList')

-- create temp table as usual, select , update or drop table


Going from long/lat cords to Geometry or Geography type

Can you cast? No.

geometry and geography are PostGIS types, and your types are likely float, double, or something like that.

Can you create PostGIS types? Yes.

What you want to do is use ST_MakePoint(long,lat).

SELECT *,
   ST_MakePoint(pickup_longitude, pickup_latitude) as pickup_pt,
   ST_MakePoint(dropoff_longitude, dropoff_latitude) as dropoff_pt

If you don't have PostGIS.

Get it. Install it. Use it: CREATE EXTENSION postgis;
Or, if you can't get or have a good reason for not wanting it, then and only then should you even consider using the native type, point.

SELECT *,
   point(pickup_longitude, pickup_latitude) as pickup_pt,
   point(dropoff_longitude, dropoff_latitude) as dropoff_pt

Geometery vs Geography

Geography to geometry conversions are a function to PostGIS. But you can do it with

SELECT ST_MakePoint(long,lat)::geography;

Or, you can use another geometery srid with

SELECT ST_SetSRID( ST_MakePoint(long,lat), srid );


Active/Active could mean two things (or maybe others depending on the project manager's imagination).

At least two clustered instances of SQL Server on two nodes of a Windows cluster. This allows both pieces of hardware to be used simultaneously and not wasted, one server for each instance, with the worst case scenario being one server fails and the other takes all of the load for both instances in that case.
More likely, a manager thinks that both nodes of a cluster could be actively running the same instance of SQL Server, with reads and writes going to both. This is not how clustering works; it is not for scale out but for high availability / disaster recovery. But it is a common misconception, especially for managers. :-)

Most people that work with clustering regularly cringe when they hear active / active. It's inaccurate, ambiguous, and to them it's like nails on a chalkboard.


Does SA account need to be called "SA" or can I rename it safely?

It can be renamed. It's often considered a Best Practice for security, but there can be SQL Agent fallout if you don't bounce the Agent service or change job owners, and I can find reports of it borking Server 2008 upgrades, too.

That's enough to make me not want to bother renaming it.  I say assigning a complex password and disabling the login is enough, and it's generally a better option since it actually closes the vulnerability instead of merely obfuscating it. Obfuscation gains you very little security. In the future, if you truly need an SQL authorization sysadmin, create a new account for it. That's not particularly difficult. You have the same attack surface as renaming without having to remember that SID 0x01 isn't sa anymore (even though it still is sa).  And, of course, since the SID never really changes, it's not difficult to find which account is SID 0x01 (or a member of the sysadmin role, for that matter). Of course, if possible, don't even use mixed mode.  Best practice is to use Windows authentication only mode, AFAIK.


SQL Server Timeout Expired in ASP.NET C# WebForms application

Sounds like a poor performing query that is crossing over the default 30 second timeout for .net connections.  SQL Server will process a query for as long as it takes, but the application is deciding to stop the show after 30 seconds.

You should fire up SQL Profiler and capture long running transactions (ones that have duration > 1 second) and transactions that require more than a few thousand reads.

Once you have a few of these queries isolated, you can begin to examine them for poor performance (look at indexes, joins, etc.)


getting oracle error ora-00103 when I try to execute a package in oracle 10g

The DELETE FROM watersitesyn; code is still in the declarative part of your procedure.
You need to add a BEGIN to end the declarative part and begin the executable part as described in this section of the Oracle 11.2 Database 2 Day Developer's Guide.


Is Star/Snowflake database design good for applications? What is the alternative?

In Option 2 the Status_Code appears as non-key in two tables: Study, Contract. But which of those entities does it belong to? And what are the relative cardinalities of those entities?
Looking at Option 1, the Status_Code belongs to the Contract; there can be multiple Studys for a given Contract.
So if you were to adopt option 2, you give OLTP a headache: anytime there's a change in Status of a Contract, transaction processing needs to replicate that change to all Studys, with an obvious risk of getting out of synch. That's called an update anomaly.
Denormalising the model like Option 2 is legitimate in data warehouses, because we want fast reporting. It is unwise in a transaction processing-oriented data model, because of the processing cost and risks of maintaining data in synch.
So for an application focussing on transaction processing, Option 1 is indicated. And yes, you would create a Join view Study > Contract > Status.


Is there a difference between making a composite foreign key for all columns or a foreign key for each column?

Sure, there is a big difference. In first case you are referencing by combination of columns. It means ParentTable should contain a row for each combination of OrderId and CompanyId which you want to add to your ChildTable. In the second case it would be enough to have separate rows with corresponding OrderId and corresponding CompanyId.


If you see the standby summary in DGMGRL then it is quite straightforward to know whether your configuration is satisfying your RPO and RTO(derived from business needs) or not.

DGMGRL> show database orcl_stby;

Database - orcl_stby

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 0 seconds ago)
  Apply Lag:       0 seconds (computed 0 seconds ago)
  Apply Rate:      916.00 KByte/s
  Real Time Query: ON

The transport lag is measured as the delta from the primary database last update and the standby last received redo. This can effect your RPO. If you have to fail over your primary database at this time, redo data that did not arrive at the standby database server will be lost.

Similarly Apply Lag represent your RTO which indicates how far behind your standby database server is compared to the primary when it comes to applying redo data.


Restore failed for server (Microsoft.SqlServer.SmoExtended)

Try running RESTORE VERIFYONLY and see if you get more information about the failure.

You could also try running RESTORE with CONTINUE_AFTER_ERROR and then run DBCC CHECKDB:

RESTORE DATABASE database_name
FROM backup_device WITH CONTINUE_AFTER_ERROR

(i.e. running as TSQL rather than through SMO)

DBCC CHECKDB(N'databasename') WITH EXTENDED_LOGICAL_CHECKS;

  Specifying WITH CONTINUE_AFTER_ERROR in a RESTORE statement attempts
  to restore the database. However, there are many kinds of corruption
  that prevent recovering a database. We strongly recommend that you
  reserve using the CONTINUE_AFTER_ERROR option until you have exhausted
  all alternatives.

All backup strategies should include regular DBCC CHECKDB runs.

Also NOTE: You do not have a backup unless you periodically successfully test restores.

Responding to SQL Server Restore Errors Caused by Damaged Backups


You can create another table with expected records value and each time compare both tables records.
for compare data between two table you can use SQL Data Compare of Redgate package.


How to partition existing table in postgres?

Since #1 requires copying data from the master to the child while it is in an active production environment, I personally went with #2 (creating a new master). This prevents disruptions to the original table while it is actively in use and if there are any issues, I can easily delete the new master without issue and continue using the original table. Here are the steps to do it:

Create new master table.

CREATE TABLE new_master (
    id          serial,
    counter     integer,
    dt_created  DATE DEFAULT CURRENT_DATE NOT NULL
);

Create children that inherit from master.

CREATE TABLE child_2014 (
    CONSTRAINT pk_2014 PRIMARY KEY (id),
    CONSTRAINT ck_2014 CHECK ( dt_created < DATE '2015-01-01' )
) INHERITS (new_master);
CREATE INDEX idx_2014 ON child_2014 (dt_created);

CREATE TABLE child_2015 (
    CONSTRAINT pk_2015 PRIMARY KEY (id),
    CONSTRAINT ck_2015 CHECK ( dt_created >= DATE '2015-01-01' AND dt_created < DATE '2016-01-01' )
) INHERITS (new_master);
CREATE INDEX idx_2015 ON child_2015 (dt_created);

...

Copy all historical data to new master table

INSERT INTO child_2014 (id,counter,dt_created)
SELECT id,counter,dt_created
from old_master
where dt_created < '01/01/2015'::date;

Temporarily pause new inserts/updates to production database
Copy most recent data to new master table

INSERT INTO child_2015 (id,counter,dt_created)
SELECT id,counter,dt_created
from old_master
where dt_created >= '01/01/2015'::date AND dt_created < '01/01/2016'::date;

Rename tables so that new_master becomes the production database.

ALTER TABLE old_master RENAME TO old_master_backup;
ALTER TABLE new_master RENAME TO old_master;

Add function for INSERT statements to old_master so that data gets passed to correct partition.

CREATE OR REPLACE FUNCTION fn_insert() RETURNS TRIGGER AS $$
BEGIN
    IF ( NEW.dt_created >= DATE '2015-01-01' AND
         NEW.dt_created < DATE '2016-01-01' ) THEN
        INSERT INTO child_2015 VALUES (NEW.*);
    ELSIF ( NEW.dt_created < DATE '2015-01-01' ) THEN
        INSERT INTO child_2014 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'Date out of range';
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

Add trigger so that function is called on INSERTS

CREATE TRIGGER tr_insert BEFORE INSERT ON old_master
FOR EACH ROW EXECUTE PROCEDURE fn_insert();

Set constraint exclusion to ON

SET constraint_exclusion = on;

Re-enable UPDATES and INSERTS on production database
Set up trigger or cron so that new partitions get created and function gets updated to assign new data to correct partition. Reference this article for code examples
Delete old_master_backup


You can reset the identity value by

DBCC CHECKIDENT('tableName', RESEED, 0)

So next time you insert into TableName, the identity value inserted will be 1.

When you delete rows from the table, it will not reset the Identity value, but it will keep increasing it. Just like what happened in your case.

Now when you truncate the table, it will reset the Identity value to its original Seed value of the table.

Refer to : SQL SERVER – DELETE, TRUNCATE and RESEED Identity for a detailed example and some good explanation of Difference between Truncate and Delete


Is it possible to shrink mdf file in replication server

With SQL Server transactional replication, data is replicated at the table level, not the database itself. So you can make whatever changes you want to the database. The question is why you want to shrink the database in the first place. Check out ShouldIShrinkMyDatabase.com, and make sure you click through to the linked article. – AMtwo May 21 '20 at 11:20


Dropping Foreign Key In Production Environment

You should disable anything related to such keys before dropping them

Using the variables FOREIGN_KEY_CHECKS and UNIQUE_CHECKS in your session, run

SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;

Doing this in your session makes mysqld bypass referential checks in your session. You are still subject to the usual locks if anyone is accessing the tables whose constraint you are trying to discard. Indexes would be dropped quickly thereafter.

Your best approach would be to have a test server with the same amount of Production data. Run these two commands and perform the drop of the constraints in such a Staging server before doing this in Prod.

If you need to do this in Prod, schedule proper downtime.

Afterwords, do this

STEP 01 : Run the following

SET GLOBAL read_only = 1;
SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;

STEP 02 : Drop all foreign keys

STEP 03 : Run the following

SET GLOBAL read_only = 0;
SET FOREIGN_KEY_CHECKS = 1;
SET UNIQUE_CHECKS = 1;

STEP 04 : End Downtime


How to show difference between two tables?

MySQL knows nothing about FULL OUTER JOIN, so

SELECT t0.param, t1.value, t2.value
FROM (SELECT param from db1.settings
          UNION
      SELECT param from db2.settings) t0
LEFT JOIN db1.settings t1 ON t0.param = t1.param
LEFT JOIN db2.settings t2 ON t0.param = t2.param
-- remove duplicates if not need in
WHERE NOT (t1.value <=> t2.value)


Chose a name that is unambiguous. created could mean the following things:

"was created yes/no" (a boolean value)
the "object" that was created, maybe the name of it as a string
the datetime when it was created

Probably more. deliveredAt has similar problems. It could be the location where a parcel was delivered at. I really hate these ambiguous naming schemes.

If you just name it createDateTime all ambiguity is gone. The point here is not to add the type to the name. The point is to not leave the name open to interpretation what it means.


Auto incrementing a column based on another

romeo, before I craft a reply, I want to ask: Is there any chance you have the opportunity to edit the schema in this case?

If you can, you definitely should, because the problems you're running into are because of denormalization.

Normalization of the schema

From your example table above, it appears this should be split into at least two separate tables. From the information you've given you should have an invoice table and a client table, with (pseudocode) definitions as

CREATE TABLE invoice (invoice_id, client_id, invoice_date);

and

CREATE TABLE customer (client_id,service_id, phone_number);

Using these tables, you can establish a foreign key relationship from invoice (client_id) to customer (client_id), allowing a many-to-one relationship between invoice and client.

Subsequently, you can obtain all invoices pertaining to a particular client via

SELECT * FROM invoice JOIN client ON invoice.client_id = clinet.client_id
WHERE client = ...

Hope this helps!


Does spgist extension in PostgreSQL 9.2.3 support float or int array?

No, by default SP-GiST only supports these operators over point ranges box and text

Name            Indexed Data Type   Indexable Operators
kd_point_ops    point               << <@ <^ >> >^ ~=
quad_point_ops  point               << <@ <^ >> >^ ~=
range_ops       any range type      && &< &> -|- << <@ = >> @>
box_ops         box                 << &< && &> >> ~= @> <@ &<| <<| |>> |&>
text_ops        text                < <= = > >= ~<=~ ~<~ ~>=~ ~>~

That's not to say that it can not work over float or int[] but it may be awkward unless they have any special internal designation you can exploit for partitioning. That is to say, if your int[] and array are likely to produce balanced trees, or are equally distributed there is no point whatsoever (afaik).


Find max content length of column of particular table from entire SQL server

To do it for each database on a server you could do this:

To do it on the 3 servers, either run it on each, or create a central management server.

declare @cmd nvarchar(max)

declare @dbname nvarchar(500)
declare c cursor for select name from sys.databases where database_id > 4 and state_desc = 'ONLINE'
open c
fetch next from c into @dbname
while @@FETCH_STATUS = 0

begin
set @cmd ='
use '+@dbname+'
select max(len(column1 )) from table1
'
--print(@cmd)
exec(@cmd)
fetch next from c into @dbname
end
close c
deallocate c


COUNT(*) on InnoDB whenever phpMyAdmin loads

Edit config.inc.php, and change the setting for MaxExactCount.

$cfg['MaxExactCount']
    Type:   integer
    Default value:  500000

For InnoDB tables, determines for how large tables phpMyAdmin should get the
exact row count using SELECT COUNT. If the approximate row count as returned
by SHOW TABLE STATUS is smaller than this value, SELECT COUNT will be used,
otherwise the approximate count will be used.


I have to create tables where both have each as the foreign key . Which one should be created first

It doesn't matter--create both of them without the foreign key constraint, and then alter the tables to add the constraints.


Encrypt connection between SSRS and SQL Server

Unfortunately, you can't do this.
There are two options (RSCM and rsconfig.exe) to set the connection string that SSRS uses to connect to the Report Server database and neither of them gives you the option to add the "Use Encryption for Data=True" parameter on the connection string. And since SSRS uses the SQL native client to connect you can't configure an ODBC connection and use it instead. The inability to set connection string parameters is definitely a shortcoming. I'm afraid you'd have to force encryption at the instance level to meet your encryption requirement.


Spatial Loader DB2 giving error while importing shape file

I used db2level to check the client version and found that the client version was the mismatch.

Not working client version
--------------------------
DB21085I  Instance "DB2" uses "32" bits and DB2 code release "SQL09070" with
level identifier "08010107". Informational tokens are "DB2 v9.7.0.441", "s090521", "NT3297", and Fix Pack "0".

Working Client version
----------------------
DB21085I  Instance "DB2" uses "32" bits and DB2 code release "SQL09075" with
level identifier "08060107". Informational tokens are "DB2 v9.7.500.702", "s111017", "IP23286", and Fix Pack "5".


TABLOCK for both truncate and subsequent bulk insert

The table lock is not taken until a statement containing that hint is executed. Since TRUNCATE cannot be hinted we must add another statement that can be. A SELECT will suffice. We want it to be fast so a restrictive predicate is good. Something like
begin transaction;

select primary_key
from dbo.table (tablockx)
where 1=2;

.. etc.

It would be nice if we could use sp_getapplock for this but it cannot, at the time of writing, address schema objects.


Whenever you restore a backup file with the intention to restore more backup files, you need to include with norecovery. In your scenario, it would look something like:

restore database [yourDB] from disk = 'backup_file.bak' with norecovery, file=1
restore database [yourDB] from disk = 'backup_file.bak' with recovery, file=2

Note, once you run a restore with recovery, no further restores are possible. So, in your scenario where you want to restore from production every day, you'd need to restore the full backup every time. Not that that's a bad thing; I do it in my shop and it seems to work out alright.


Ordering by field, constructed by subquery is slow

Try to flatten the subquery into a join:

SELECT *
FROM (SELECT d.*,
             max(r.date) AS register_date
      FROM documents AS d
      LEFT JOIN registrations AS r
          ON r.document_id = d.id
      GROUP BY d.id) AS my_documents_view
ORDER BY register_date desc NULLS LAST
LIMIT 20;


No, DBO is essentially just a default schema and you can make other accounts with different privileges that have separate schemas. Even though, not quite sure how disabling the SA would help in elevating security


Are user defined types (UDTs) available in an Azure SQL database managed instance?

User-defined types are supported in Azure SQL Database except those based on a CLR assembly, which is not supported.


You can use this query, courtesy of Paul White:
SELECT SUSER_NAME(CONVERT(INTEGER, OBJECTPROPERTYEX(t.OBJECT_ID, 'OwnerId')))
FROM tempdb.sys.tables AS t
WHERE t.name LIKE N'#TempTableName%';


Oracle database backup and restore options

Data pump export & import are just fine for moving data around; I don't know of any limitation in what you described.


SQL Server Windows NT 64 bit Sudden High CPU Spike

There are a few ways to approach this.

Download sp_WhoIsActive and run it when you notice a CPU spike, or log it to a table and query the table looking at the CPU column (maybe ORDER BY CPU DESC).
Examine the plan cache using a free script -- I co-author one called sp_BlitzCache. By default, it will return the top 10 CPU consuming queries in your server's plan cache.
Buy a monitoring tool like SentryOne Performance Advisor and use it to examine your server during a spike.


Can I create a second filestream container on an existing SQL Server 2008 database without going offline?

Unfortunately no, not in SQL Server 2008.

According to FILESTREAM Overview - multiple containers can be added to a single FILESTREAM Filegroup but only as of SQL 2012.


joining tables without duplicate rows / NULL values

First, I should note that this is basically a formatting requirement: you don't want to repeat the values in the first two columns if they're the same as they were in the previous column. If you have more CPU power available at the application end than on the SQL Server, then you will be likely to get better performance if you can allow the application to handle the formatting requirements.

That said, sometimes the SQL Server has more than enough capacity to handle the formatting, or doing it at the application end doesn't make sense for some reason.

The following query should do what you want:

SET @PrevName := '';
SET @PrevStatus := '';
SET @RowNum := 0;

SELECT Name
      ,currentstatus
      ,`status`
      ,username
  FROM (
        SELECT IF(@PrevName = ab.Name, '', ab.Name) as Name
              ,IF(@PrevName = ab.Name AND @PrevStatus = ab.currentstatus, '', ab.currentstatus) as currentstatus
              ,asb.`status`
              ,asb.username
              ,@PrevName := ab.Name as ActualName
              ,@PrevStatus := ab.currentstatus as Act_curstatus
              ,@RowNum := @RowNum + 1 as RowOrder
          FROM statustable ab
                 CROSS JOIN awsaccount asb
         WHERE (    ab.Name LIKE '%ahsan%'
                AND asb.username LIKE '%ahsan%'
               )
         ORDER BY ActualName, Act_curstatus
       ) sq
 ORDER BY RowOrder
;

I tested this via db-fiddle.com; you can play with the query and test data here.

Quick explanation:

We use the variables to hold the value of Name and currentstatus from the previous row. If Name is different, we display both values; if Name is the same but currentstatus has changed, we leave Name blank but show the new status.

Establishing an order is critically important if we have multiple names or current statuses; if we don't sort these, then rows with different names could be interleaved, meaning the same name would be repeated several times, as seen here:

...
Ahsan Naseem        | activated   | ACTIVE   | ahsan.naseem
                    |             | INACTIVE | ahsantest
Bruce Banner        | activated   | ACTIVE   | mr.green
Ahsan Naseem        | activated   | ACTIVE   | ahsantest2
                    |             | ACTIVE   | ahsantestuserprofileupdate
...

Further, we must preserve the order in which the rows are processed, or else the line with the name might not be the first one, as here:

...
                    |             | INACTIVE | ahsantest
                    |             | ACTIVE   | ahsantest2
Ahsan Naseem        | activated   | ACTIVE   | ahsan.naseem
                    |             | ACTIVE   | ahsantestuserprofileupdate
...

So, we use @RowNum to sequentially number the rows as we process them, and ultimately sort on that number. (I wasn't sure this was needed; however, without it, I got the results back inverted, so I obviously did need it!)

We put our query in a subquery, both so we can force the processed order onto the results, and so we can leave out the Actual_Name, Act_currentstatus, and RowOrder columns.

If you really wanted NULL instead of empty string for the repeated values, just replace the empty string in the IF functions with NULL. As I'm assuming this is for display purposes, empty string is probably a better alternative, but you may have some other reason for doing this.


Query slowness after migration from sql server 2000 to 2014

What version of SQL Server is the PI2 server in this situation?  When was the last time the underlying statistics were updated on these tables on the Linked Server?  While you updated statistics, it sounds like that was only done on the local database server that you migrated to.  Updating statistics on these underlying remote tables may help the optimizer identify a more optimal plan and allow for the entire query to execute remotely.

Another possibility is permissions (though you say that's no different between the old and new servers), four-part (aka distributed) queries require proper permissions to remote tables in order to get an optimal plan.  From the Guidelines to Using Distributed Queries page:

  To create the best query plans when you are using a table on a linked
  server, the query processor must have data distribution statistics
  from the linked server. Users that have limited permissions on any
  columns of the table might not have sufficient permissions to obtain
  all the useful statistics, and might receive a less efficient query
  plan and experience poor performance. If the linked server is an
  instance of SQL Server, to obtain all available statistics, the user
  must own the table or be a member of the sysadmin fixed server role,
  the db_owner fixed database role, or the db_ddladmin fixed database
  role on the linked server.

Really since all of these tables are on the linked server, I suggest you force the query to execute remotely in it's entirety instead of hoping it will do so with four-part/distributed query syntax.  You do this by reworking the query into an EXECUTE AT statement, such as follows:

EXECUTE('select
        MKPF.MANDT,
        MKPF.MBLNR,
        MKPF.MJAHR,
        MSEG.BWART,
        MSEG.MATNR,
        MSEG.LGORT,
        MKPF.BUDAT,
        MKPF.CPUDT,
        MKPF.CPUTM,
        MKPF.AEDAT,
        MSEG.SHKZG
    from PKT.pkt.MSEG AS MSEG
    join PKT.pkt.MKPF AS MKPF
        on MKPF.mandt=MSEG.mandt and MKPF.MJAHR = MSEG.MJAHR and MKPF.MBLNR = MSEG.MBLNR
    join PKT.pkt.MARA MARA
        on MARA.MANDT=MSEG.MANDT and MARA.MATNR = MSEG.MATNR
        and MARA.spart =''01''
    WHERE
        MSEG.Mandt=''500'' and MSEG.MATNR like ''00000000%'' and
        MKPF.CPUDT between ? and ?
        AND MSEG.BWART in (''101'',''561'')'
, @datefrom, @dateto) AT PI2

I'm suggesting the EXECUTE AT approach instead of using other LinkedServer syntax such as OPENQUERY or OPENROWSET as this will allow you to still generate the date parameters from data hosted on your local server (as your estimated query plan indicates) and pass them as parameters to the query you wish to run remotely in what will likely be a more optimal fashion.


Data sync across Local and Cloud Database

If the "Local DBs" were only read slaves then this would be feasible using replication filters. https://dev.mysql.com/doc/refman/7.0/en/change-replication-filter.html

However since you have some sort of master master requirements (the need to be able to write to both Local DBs and the Central Master) this will get messy quickly. Reason being MySQL is not meant for master master replication out of the box let alone master master with bi-directional replica filter demands. As such, there are two possible solutions I can think of.

1.) Split the the Central Master into multiple databases which are location specific, reflecting their given Local DB partner. Each partner pair should then become a Galera "cluster" consisting of both nodes plus an arbitrator.

2.) Have the entire topology you are describing be a Galera cluster and don't discriminate between location based data, i.e all servers have all data. This solution would obviously greatly reduce complexity but at the cost of increased resource requirements.

3.) If you prefer to use Postgres, you can experiment with some variant of the 2 above mentioned solutions using https://www.2ndquadrant.com/en/resources/pglogical/pglogical-docs/
But it seems at this time that multimaster and logical replication aren't coexisting.

To answer your other questions:

-I would try to achieve some sort of replication instead of a custom sync which would be difficult to implement especially if your data set is large. Also a custom sync solution will result in data inconsistency the majority of the time.

-In my opinion MySQL has more mature replication facilities available relative to your particular challenge.


How to Recover an InnoDB table whose files were moved around

The biggest thing most people forget about TRUNCATE TABLE is that TRUNCATE TABLE is DDL and not DML. In InnoDB, the metadata within ibdata1 contains a numbered list of InnoDB tables. Using TRUNCATE TABLE causes the internal metadata id of the InnoDB table to shift. This happens because TRUNCATE TABLE effectively does the following:

Example: To truncate an InnoDB Table called mydb.mytb

USE mydb
CREATE TABLE newtb LIKE mytb;
ALTER TABLE mytb RENAME oldtb;
ALTER TABLE newtb RENAME mytb;
DROP TABLE oldtb;

The new mytb would thus have a different internal metadata id.

When you copied the .ibd file to some other place, the .ibd contains within it the original internal metadata id. Simply putting the .ibd file back does not cause a reconciliation of the internal metadata id with that of the one in ibdata1.

What you should have done is this:

Copy the .ibd file of the InnoDB table. Then, run this

ALTER TABLE tablename DISCARD TABLESPACE;

To bring it back later on, copy the .ibd file back into datadir and then run

ALTER TABLE tablename IMPORT TABLESPACE;

This would have preserved the internal metadata id.

Make sure .frm is always present.

I once helped a client restore 30 InnoDB tables he hosed in the same manner. I had to use another DB server and play some games with adding and dropping InnoDB tables to hunt down the correct internal metadata id.

The client found this article : http://www.chriscalender.com/?tag=innodb-error-tablespace-id-in-file . We used it and it helped a great deal. I hope it helps you.


how to get the date of a file using xp_cmdshell?

This will depend on your regional settings, language, and potentially other settings.

...
WHERE TRY_CONVERT(date, LEFT(strData,10)) = CONVERT(date, GETDATE());

To avoid anything else from entering the #temp table in the first place, you can use forfiles, which allows you to specify a pattern (eliminating all the extra junk dir adds), specify a date (in this case any file with a modified date >= today), and also only return certain properties of the file (like the date), e.g.:

DECLARE @cmd varchar(4000) = 'forfiles /p C:\...\cmdshell1\ /m *.txt '
  + '/d +' + CONVERT(char(10), getdate(), 101) + ' '
  + '/c "cmd /c echo @fdate"';

INSERT #tmp(strData) EXEC master.sys.xp_cmdshell @cmd;

SELECT * FROM #tmp WHERE strData IS NOT NULL;

Results:

strData
----------
09/25/2018
09/25/2018
...

Your question says you only want the date value output, but that doesn't seem to make a lot of sense to me, since you already know the date, it will be the same on every single row, and all that really tells you is a count (so why not just run a count?). Here's how to pull out the file name with a similar technique (the for /f bit is to eliminate quotes around the @file output, as borrowed from this answer):

DECLARE @d char(10) = CONVERT(char(10), getdate(), 101),
  @cmd varchar(4000) = 'for /f %i in (''forfiles '
    + '/p C:\...\cmdshell1\ '
    + '/m *.txt '
    + '/d +' + @d + ' '
    + '/c "cmd /c echo @file"'') do @echo %~i';

INSERT #tmp(strData) EXEC master.sys.xp_cmdshell @cmd;

SELECT strData = @d + ' ' + strData
FROM #tmp WHERE strData IS NOT NULL;

Results:

strData
--------------------------------
09/25/2018 file1.txt
09/25/2018 file2.txt
...

But I have to agree with Kin, this is much better suited for PowerShell no matter what you're actually looking for and whether it makes sense. xp_cmdshell was turned off by default starting with SQL Server 2005, and that was done for a reason. Let SQL Server focus on the data, and PowerShell and other things deal with the OS layer.


Import csv data from localhost emacs SQLi error MySQL

It seems variable  "local_infile" is turned off before loading and if you specify "--local-infile" turns on this variable.

You can use following steps to load file

  login mysql using mysql -uroot -p
  set global local_infile to value 1
  Then try load data local infile

Hope it helps


Export subset of data for development environment

I've use the script in this post:
Updating multiple rows with random values from another table
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/f58c3bf8-e6b7-4cf5-9466-7027164afdc0/updating-multiple-rows-with-random-values-from-another-table?forum=sqlgetstarted

Generate the random data (another table) first:
http://www.generatedata.com/


Does MySQL guarantee atomic upgrade from read lock to write lock?

Indeed, the part of the manual you quoted does just what it says:

-- Connection #1
LOCK TABLE mytable READ; -- lock is acquired

-- Connection #2
LOCK TABLE mytable WRITE; -- execution is suspended, lock is NOT acquired

-- Connection #1
LOCK TABLE mytable WRITE; -- execution is suspended
-- READ lock was released, allowing Connection #2 to acquire the WRITE lock
-- Connection #2 resumes

Solution: do switch to InnoDB and use transactions. Performance of the InnoDB engine improved a lot in the recent versions.

-- Connection #1
BEGIN;
SELECT * FROM mytable LOCK IN SHARE MODE; -- lock is acquired

-- Connection #2
BEGIN;
SELECT * FROM mytable FOR UPDATE; -- execution is suspended, lock is NOT acquired

-- Connection #1
SELECT * FROM mytable FOR UPDATE;
-- deadlock is detected, Connection #2 is rolled back


Can Oracle TimesTen (or 12c's In-Memory) benefit from GPU?

Any application that uses GPUs for processing has to be custom-written to access the GPU's processing power.  Where GPUs shine are in floating point arithmetic, something that is low on the list of database operations, so no, no database software is likely to harness GPUs for processing.


This will get you the Status Description:

SELECT s.SR_Service_RecID, SR_Status.Description, c.Company_Name, s.Last_Update, s.Date_Req
FROM SR_Service s
    INNER JOIN Company c ON s.Company_RecID = c.Company_RecID
    INNER JOIN SR_Status ON s.ST_Status_RecID = SR_Status.SR_Status_RecID
WHERE SR_Type_RecID IN (SELECT t.SR_Type_RecID
                        FROM SR_Type t
                        INNER JOIN SR_Board b
                            ON t.SR_Board_RecID = b.SR_Board_RecID
                        WHERE t.description LIKE @Type
                           AND b.Board_Name LIKE @Board)
AND s.SR_Status_RecID IN (SELECT SR_Status_RecID
                          FROM SR_Status
                          WHERE Closed_Flag = 0)

To add the schedule, you'd need to add a key field from the schedule table to the SR_Service table.


SQL Server - Prevent Clustered Index Scan on a specific table

Is there a way to globally prevent SQL Server from generating Clustered Index Scan on this table?

Yes.  Replace the clustered index with a clustered columnstore index.  It will be highly compressed.  And scans can eliminate unneeded columns, and (critically here) unneeded row groups.

The query here is tricky to optimze:

select t.Id, t.ExternalId, t.Source, tn.Code
from XXX.LargeTable t
join Security tn on t.SecurityId = tn.Id
where t.TransactionTime >= @d1
and t.TransactionTime <= @d2
and tn.NationalityId = 22

The optimal plan depends almost entirely on how wide the range [@d1, @d2] is.  But SQL Server can't have multiple plans for the same query.  You're getting a table scan here as the alternative plan has a huge estimated cost, due to the double nested loop join.  As it turns out if few enough rows qualify in the transaction table, that plan will be cheaper than the table scan, but SQL has to come up with a plan that works reasonably well for any values of @d1 and @d2.

If the date range turns out to be verry narrow, as here, the clustered index scan is a terrible plan, as it requires reading the whole table.

But in the case of a Clustered Columstore SQL Server only has to scan the TransactionTime column to find the matching rows.  Not only that, each 1,000,000 row column segment has the min and max in the column segment header.  So if the range is narrow many of the row groups can be eliminated without actually scanning all the TransactionTime values.

The "bad" plan had mainly IO waits

  <Wait WaitType="PAGEIOLATCH_SH" WaitTimeMs="523619" WaitCount="83735" />
  <Wait WaitType="MEMORY_ALLOCATION_EXT" WaitTimeMs="13910" WaitCount="14455586" />

Scanning a CCI for the same query would require a tiny, tiny fraction of the IO required for the clustered index scan.


This answer isn't dynamic, but shows you how to achieve the goal in pure TSQL, making it dynamic should be straightforward.
Essentially, you just need to add another column to spread the data out so that the MAX(fieldname) isn't the max for the event.  I've chosen to use a ROW_NUMBER partitioned by the EventID so that each entry for each event is also numbered.
I did also look at https://stackoverflow.com/questions/1343145/tsql-pivot-without-aggregate-function
DROP TABLE IF EXISTS ##Events

CREATE TABLE ##Events
    (
    EventID INT NOT NULL
    , FieldName VARCHAR(20) NOT NULL
    )

INSERT INTO ##Events
    (EventID, FieldName)
VALUES (1, 'apple')
    , (1, 'paper')
    , (1, 'boy')
    , (2, 'banana')
    , (2, 'cat')
    , (3, 'girl')

;WITH CTE_RN AS
    (
    SELECT EventID
        , FieldName
        , RN = ROW_NUMBER() OVER (PARTITION BY EventID ORDER BY FieldName)
    FROM ##Events AS E
    )
SELECT [1]
    , [2]
    , [3]
FROM CTE_RN
    PIVOT (MAX(FieldName) FOR EventID IN ([1], [2], [3])) AS pvt


Design database where each field needs to have a checkbox

If the data has to be persisted between collection and verification, and each value can be accepted or rejected individually then there has to be a _verified column to match each _value column.
These verification columns are information about the thing to which they refer as much as the name and ID are so belong in the same logical entity type. Whether they go in the same physical table is a separate matter. If split this would be considered a vertical partitioning of the logical entity. There are good reasons to do that and good reasons not to.
If split the design and queries are more complicated. Likely response time will be fractionally better, though, since each table will be narrower. Both these can be swamped or mitigated by many other implementation details such as views or covering indexes.
Generally I'd keep a single table until proven otherwise.


Waiting for table level lock on wordpress site

I had this problem and added an index to wp_option.option_name


Concatenating two mysql queries to achieve desire results

To join the results of 2 queries into one result set, use

UNION

As in

SELECT Col1 FROM tableA
UNION
SELECT Col2 FROM tableB;

NOTE:  Only one semicolon, after the combined statement.


You can use GROUP_CONCAT to get your expected result:

SELECT US.name, GROUP_CONCAT(LO.loc_name) AS loc_name
FROM users AS US
JOIN users_location AS UL ON UL.user_id = US.id
JOIN locations AS LO ON LO.id = UL.loc_id
GROUP BY US.name;

Also you can use table alias users AS US, for better readability.

As per the comments, for any separator other than comma, you may use [SEPARATOR str_val] also ORDER BY will ensure the expected output in the ascending or descending order.

Sample code for hyphen as separator:

GROUP_CONCAT(LO.loc_name ORDER BY LO.loc_name SEPARATOR '-') AS loc_name


Troubleshooting Powershell errors from SQL Agent job

When you run a SQL Agent job step with the "PowerShell" type, you aren't actually running in (full) PowerShell. This job step type actually runs under the sqlps.exe minishell, which is based on PowerShell 1.0 and an extremely limited number of cmdlets.

Practically speaking, the PowerShell job step type is not very useful. Instead, I recommend using an "Operating System/CMDExec" job step, and calling PowerShell.exe from the command line.

Using the cmdexec job step type, your job step would look something like this:

PowerShell.exe "D:\Scripts\MyScript.ps1" -NonInteractive

By calling PowerShell.exe, you get the full/current PowerShell shell (instead of the mini shell), and you get to use the full/current SQL Server PowerShell module. This is particularly important if you are using SQL Server 2016+, where the SQLSERVER module has been enhanced with a much longer list of cmdlets.


How to remove unnecessary symbol in the column in SQL Server 2008

If it is just one symbol for each value returned, then you could do this

It seems like you have some trailing spaces, that is why RTRIM is used, or DATALENGTH() if they need to be preserved. Thanks to   @Denis Rubashkin for pointing that out.

Solution where the spaces are removed

SELECT DISTINCT RIGHT(RTRIM(NEW),(LEN(RTRIM(NEW)) - 1))
FROM [FixedAsset Control].[dbo].[NewViewReport]; -- RTRIM takes care of the trailing spaces

Solution without removing the spaces:

SELECT DISTINCT RIGHT(NEW,((DATALENGTH(NEW) / 2) - 1))
FROM [FixedAsset Control].[dbo].[NewViewReport]; -- Datalength also counts spaces

And if you want to update the column after you validated that the result is correct:

UPDATE[FixedAsset Control].[dbo].[NewViewReport]
SET NEW = RIGHT(RTRIM(NEW),(LEN(RTRIM(NEW)) - 1));

Or

UPDATE[FixedAsset Control].[dbo].[NewViewReport]
SET NEW = RIGHT(NEW,((DATALENGTH(NEW) / 2) - 1));

Testing

CREATE DATABASE [FixedAsset Control]
USE [FixedAsset Control]
GO
CREATE TABLE  [dbo].[NewViewReport] ([NEW] nvarchar(255));

INSERT INTO [dbo].[NewViewReport]([NEW])
VALUES('·OUTGOING QC OFFICE');

SELECT DISTINCT RIGHT(RTRIM(NEW),(LEN(RTRIM(NEW)) - 1))
FROM [FixedAsset Control].[dbo].[NewViewReport];

Result

(No column name)
OUTGOING QC OFFICE


PostgreSQL and MONEY data type for currency values

What about this?

A money value can be cast to numeric without loss of precision. Conversion to other types could potentially lose precision, and must also be done in two stages:

SELECT '52093.89'::money::numeric::float8;

Source:
http://www.postgresql.org/docs/9.1/static/datatype-money.html


you can use IF EXISTS clause:

create database if exists hive;


Guide to protect SQL Server against speculative execution side-channel vulnerabilities

Untrusted application logic means any application or service running any logic that you(r organization) does not trust.

For your purpose, if you want to be lenient (potentially less secure) you can substitute "unauthorized" for "untrusted" - if the server is running any unauthorized code, it falls under that category.

If you want to be conservative (potentially more secure), assume all your servers run untrusted application logic and move on with that as a founding security assumption, applying the more secure recommendations to them.


How to view the current settings of Autovacuum in Postgres?

For the general settings use:

select *
from pg_settings
where name like '%autovacuum%'

for table specific settings, check out the column reloptions in pg_class:

select relname, reloptions
from pg_class

You will probably want to join that to pg_namespace to limit this to a specific schema. Where joining needs to happen on the hidden col pg_namespace.oid (added 9.3+).

select relname, reloptions, pg_namespace.nspname
from pg_class
join pg_namespace on pg_namespace.oid = pg_class.relnamespace
where relname like 'data%' and pg_namespace.nspname = 'public';


How to insert values into a junction table?

If I guessed correct you are using PHP PDO, so what your are looking for is this function. http://us3.php.net/manual/en/pdo.lastinsertid.php. It will return the id off the last insert tuple into the database which you can save in a variable to be used in the insert for the junction table.

$stmt = $sqli->prepare("INSERT INTO book(book_original_name, book_turkish_name, book_publication_date, book_synopsis) VALUES (?,?,?,?)");
$stmt->bind_param("ssss", $_POST['book_original_name'], $_POST['book_tr_name'], $_POST['book_year'], $_POST['book_synopsis']);
$stmt->execute();
$book_id = $stmt->insert_id;
$stmt->close();

$stmt = $sqli->prepare("INSERT INTO metadata_images(metadata_image_value, type, size) VALUES (?,?,?)");
$stmt->bind_param("sss", $name, $_FILES['book_cover']['type'], $_FILES['book_cover']['size']);
$stmt->execute();
$cover_id = $stmt->insert_id;
$stmt->close();

$stmt = $sqli->prepare("INSERT INTO book_main_cover VALUES(?,?)");
$stmt->bind_param($book_id, $cover_id);
$stmt->execute();
$stmt->close();


Should I enable binlog binary logging on slave server?

Enabling binary-logs on your current slave server will eliminate two steps of the failover process but there would still be many other steps involved.

In a true failover and depending on the scenario you'd probably go through the following steps.

Enable binary logs on the new master via my.cnf or equivalent.
Restart MySQL on the new master.
Grant the old master's replication user access to the
new master.
Run the FLUSH TABLES WITH READ LOCK command on the new master.
BACKUP the databases being replicated on the new master.
Run the UNLOCK TABLES command on the new master.
RESTORE those databases to the new slave.
Run the stop slave command on the current slave.
Run the CHANGE MASTER command on the old master to point to the new
master.
Run START SLAVE on the new slave.

Update after Karoly's comments

As a follow response up to Karoly's answer and comments. If you really wanted to save time on the fail-over process, using a master-master replication schema would be the best idea in my opinion.

You would essentially prepare the slave to take over as master at anytime, leaving you with just redirecting your applications to the new master.

Assuming you've already setup a master-slave replication schema.
The Basic steps to then setup a master-master replication, you would.

Turn on bin-logs on the slave
Turn on log-slave-updates on both the master and the slave
Configure the master to update from the slave with CHANGE MASTER
Grant access to the replication user on both master and slave
start slave on both servers
Eventually make the slave server read-only via the read-only my.cnf
option


Why does TempDB spill happen even though statistics are correct?

As the tooltip shows, 7643 8KB pages are spilled = 59.71MB. The rest of the sort occurs within the allocated memory.
The sort spills for two reasons:

The sort is parallel. The accurate memory estimate is split evenly among the sort threads, and cannot be dynamically reallocated between threads at runtime. If the rows are not distributed evenly, one or more sort threads may spill. This is somewhat more likely to happen at low DOP e.g. 4 rather than 8.

SQL Server does not account for spill management overhead when estimating the memory requirement. This is detailed in FIX: Sort operator spills to tempdb in SQL Server 2012 or SQL Server 2014 when estimated number of rows and row size are correct.

On SQL Server 2019, the memory granted between executions may be automatically adjusted to avoid the spill via the row mode memory grant feedback mechanism.


I am sorry to say, but you have a corrupt backup file.  If you can, backup the database again, but use the "with verify" option to ensure it is fully functional.


AlwaysOn Availability Groups and Encrypted Channels

You can simply enable SSL on each replica as per normal, but the certificate you provision should have a CN that matches the Listener name that you're connecting to.

See here for more info.


Conversion failed when converting date and/or time from character string while inserting datetime sql server

Bad news: social security number isn't in the format of DDMMYYXXXX. (My own SSN starts with a number above 3, for example.)

Take out the DATEDIFF and CONVERT functions, and just look at the source data. You'll likely see the offending rows just by eyeballing it:

SELECT Users.Id, Users.FirstName + ' ' + Users.LastName AS Medarbajder,
Users.SSN AS CPRNR,
SupposedlyDate AS STUFF(STUFF(LEFT(Users.SSN, 6), 3, 0, '.'), 6, 0, '.'),
FROM dbo.Paychecks, dbo.Users
WHERE Users.CustomerId=214 AND Users.Id=Paychecks.UserId
order by 4;

I don't know where you got that formula from, but it doesn't work. (It certainly doesn't produce a valid date for my own SSN.) There are ways to guess approximate dates from social security numbers, but not produce valid birth dates.


If you want to be able to delete this data with the maximum efficiency, and you do not need to be able to roll the transaction back, then change the partitioning scheme to be based on days.

If you want to be able to optionally recover that data, then you could still partition it, but could remove it from the table using a partition exchange with a newly created table. You can then drop that table at a later date.

Re-partitioning aside, deleting 10 million rows is always going to require extensive undo logging, because a rollback operation has to recreate those rows. The redo would not be so extensive.

But it is never going to be a lightweight operation.

p.s. There's no such thing as blowing a transaction log, or putting a database down -- if you use informal, slang terminology to describe a problem then you're not describing it accurately, and you're inviting imprecise responses.


Optimize finding newest x rows from category

Community Wiki answer compiled from comments on the question

You should probably check the InnoDB settings. You may have given very little RAM for MySQL to use. How much RAM do you have, and what's the setting of innodb_buffer_pool_size?

It's usually set up around 70-80% of RAM in servers. Or more if your RAM is larger (say, more than 64GB).

Your index is good, yes. You might get better performance if you make that index  (data_id, timestamp) the PRIMARY KEY (and demote the (id) to a simple index). That would probably improve performance of the query (to a few milliseconds) at the expense however of slightly slower writes.

The UNIQUE (id) index is redundant. You can remove it (but that is irrelevant to the issue). I would probably remove the (data_id) index as well. The fetch index covers that as well. - ypercubeᵀᴹ

A tiny (16M) buffer pool cache leads to a lot of I/O. - Rick James


Squirel SQL uses wrong command for MySQL when renaming a column

OK - the syntax for changing a column name in MySQL is

ALTER TABLE table_name CHANGE old_col_name new_col_name COL_DATA_TYPE;

However, it appears that the syntax generated by SQuirreL SQL is

ALTER TABLE table_name ALTER COLUMN "old_col_name" RENAME TO "new_col_name";

I tried this with both the MySQL jdbc driver (from mysql.com) and the MariaDB driver (from mariadb.org) and it fails on both counts.

Indeed using the MySQL contextual menu (just above the Refactoring one you tried to use), it is also impossible to modify the column name.

So, indeed, it appears that you have discovered a bug in SQuirreL SQL. Congratulations!

You can, of course, use the SQL window to perform this operation (tested).

All I can say is that I'm surprised that SQuirreL SQL has such a flaw for a server as popular as MySQL.


The most secure way is to revoke the select privilege on the columns.
Then the user will never be able to see the contents no matter what. You can't really hide the body of a function, and you can't hide the table structure from a user (e.g. by querying pg_attribute).
But the information that there is a column named "password" doesn't reveal anything the user didn't know beforehand, so I don't consider this a problem.
A password stored in the database should always be encrypted anyway and with a one way encryption, so it can't be decrypted (e.g. SHA-256).
So even in the extremely unlikely case that the user finds a way to work around the privilege system in Postgres, he would only see the encrypted value and couldn't do anything with it.


Postgresql - Segmentation fault (core dumped)

I think the permissions are correct because otherwise you wouldn't have gotten here.

My guess is that a modification you have made has caused the problem.  This is particularly the case given that it is a segmentation fault.  What you really need to do is look at the call stack at the time the core was dumped and see if you can isolate where in the code this is happening, and then back track to see what modifications you have made may have caused this.  Given that you have modified the code, the first step is to check and see if you can reproduce as kgrittn says, but I am willing to bet that this will only show up with your modified code.

The next step is likely to be to run initdb inside a debugger and review the conditions at time of crash.  At this point standard C debugging practices are really your best bet.


Killed Backup in Rollback State for Days

Since the backup is waiting on a CMEMTHREAD, that is a critical section waiting for a memory allocation, it is likely that a restart of the service will either allow recovery to start on the database, or for the database to be marked "suspect", at which point you could drop and restore from backup.  Recovery on a 70MB database, assuming the log file isn't many many gigabytes, should be nearly instantaneous.

As always, before doing anything of this nature, you need to ensure you have verified-good backups of critical databases on this instance - by "verified", I mean you've restored them onto some other server (or your workstation), to ensure you can actually perform a restore.  There is nothing like getting free advice from the internet only to find you've lost everything.

As an aside, you should ensure you have the most recent service pack and cumulative update installed on your SQL Server; see SQLServerBuilds for details.


Copy Millions of Records from one table to another with the same schema, in the same database

How about keeping all the incremental data inside the inactive table until the end of the day, then load it into active and truncate inactive? Your view could reference both tables using a UNION ALL, that way you aren't switching between the two tables.

You could use partition swapping with the same method. You'd partition both tables, load inactive until 11 PM at night, then swap inactive's loaded partition into active. Your view would still need to reference both tables. This way, you have no downtime for loading besides the time it takes to initially import the file, and incremental files should be loading into an empty table every day.


The base table insert plan incorporates operations necessary to keep the indexed view synchronized with the base table as defined by the view definition. This part of the plan is automatically generated and cannot be disabled, or made to call the instead of trigger logic on the view instead. The contract of an indexed view is that it will always materialize the query stored in the view.

The main purpose of instead of triggers on views (indexed or otherwise) is to make them updatable when they wouldn't be otherwise. The question doesn't explain what you need to achieve, but it seems an indexed view is not the right mechanism. You might need a separate table instead of a view. Please ask a follow-up question if you want help solving the underlying problem, and ideally provide a self-contained reproduction script.


You can use EXEC for that purpose
DECLARE @LINKEDSERVER VARCHAR(20)= 'LS_CMT';
DECLARE @ODS_DBNAME VARCHAR(20)= '';
SET @ODS_DBNAME = DB_NAME();
DECLARE @OCS_DBNAME VARCHAR(20)= '';
SET @OCS_DBNAME = 'CMT'
                  + SUBSTRING(@ODS_DBNAME, CHARINDEX('_', @ODS_DBNAME), LEN(@ODS_DBNAME)) ;
 EXEC('SELECT * FROM ' + @ODS_DBNAME + '.[dbo].CLI_LOCATIONS');


Why are these queries slow in some environments and not others? Can we fix their performance?

You may want to disable the variable innodb_stats_on_metadata.

According to the MySQL Documentation on innodb_stats_on_metadata

  When this variable is enabled (which is the default, as before the
  variable was created), InnoDB updates statistics during metadata
  statements such as SHOW TABLE STATUS or SHOW INDEX, or when accessing
  the INFORMATION_SCHEMA tables TABLES or STATISTICS. (These updates are
  similar to what happens for ANALYZE TABLE.) When disabled, InnoDB does
  not update statistics during these operations. Disabling this variable
  can improve access speed for schemas that have a large number of
  tables or indexes. It can also improve the stability of execution
  plans for queries that involve InnoDB tables.


In what situation can snapshot restore be blocked after SET SINGLE_USER?

When you set the database to single user, it allows a single user into the database.  If there's someone else trying to get into the database, and you don't have a connection to the database someone else could get into the database.
A single script like:
use master;
alter database MyDb set single_user;
use MyDb

...is still a single connection.  Something else would need to log into the SQL Server and into that database.  It could be a user, a job, an admin.
Setting the database into restricted user will block anyone who isn't a member of the db_owner fixed server role from connecting to the database.  I'd recommend that, or shutdown the app/web servers while you do the database restore.


Application server uses single login to connect to a database

You cannot in this case. SQL has no way to know the windows login using the application account and workstation connecting via application server.

See this answer especially read second answer by Aaron Bertand which explains in details.

Who is the person using a SQL login?


SQL Server 2016 AlwaysOn DBCC CHECKDB on async replica

I can say that the error message you are getting is because of a bug in SQL Server 2016. Microsoft knows about it and has fixed it in CU3 for SQL Server 2016 RTM.
Note: The user error is reported but the CHECKDB should continue as is.
See FIX: Error 976 occurs when you run the DBCC CHECKDB command on an unreadable secondary replica of SQL Server 2016 Availability Group
Workaround
You can take snapshot of the databases and can run checkdb on it. In this case it would not fail.Database snapshot is enterprise feature I am sure you are not using basic availability group


Getting replication status for snapshot replication

Replication Monitor does this for me. You can see the current status, the status of past sessions and the details of which articles got delivered/copied.


How to recover from Backup Pending SQLSTATE=57019 in DB2?

Backup pending means you need to take an offline backup. Since you went from circular to archive logging DB2 needs a point in time from where it can apply the logs.