Database search with multi joins I have a MySQL database and I want to perform a little bigger search.
I have about 10k records in one of the tables and It's expected to grow, but slowly.
The biggest problem is that to perform the search I have to make a query with 4 JOINS which I think causes the search to be slow.
So here is some example struct:

[table records]
id INT unsigned PRIMARY KEY auto_increment
description text
label INT unsigned
type INT unsigned
price DECIMAL

[table records_labels]
id INT unsigned PRIMARY KEY auto_increment
label varchar

[table records_types]
id INT unsigned PRIMARY KEY auto_increment
type varchar

[table records_serial]
id INT unsigned PRIMARY KEY auto_increment
serial varchar
record INT unsigned

[table records_barcode]
id INT unsigned PRIMARY KEY auto_increment
barcode varchar
record INT unsigned

So here is how the things run:

I run a query which selects records.id, records.description, records.price, records_labels.label, records_types.type, records_serial.serial, records_barcode.barcode;
So the full query is like this:

SELECT
    records.id,
    records.description,
    records.price, records_labels.label,
    records_types.type,
    records_serial.serial,
    records_barcode.barcode
FROM
    records
    JOIN records_labels ON records_labels.id = records.label
    JOIN records_types ON records_types.id = records.type
    LEFT JOIN records_serial ON records_serial.record = record.id
    LEFT JOIN records_barcode ON records_barcode.record = record.id
WHERE records_serial.serial LIKE %SEARCH_TERM%
   OR records_barcode LIKE %SEARCH_TERM%

I think that the solution here is indexing I guess, but I'm not very familiar with it.
So shortly, how to speed up and optimize query of this kind?

To speed up query I would recommend indexes on the columns you join in:

records.id, records.type, records.label (combined index)
records_labels.id
records_types.id
records_serial.record

Depending on the data in records_serial.serial and records_barcode.* (which column do you want to query here?) it may be also useful to add indexes here.

That index suggestion is optimized for this single query. You should also consider the disadvantages for other queries.


Visual Studio extract database values to excel with header text! I am trying to extract the values from an mdf database to excel, but i can't display the header texts(title fields). I can only view the values. In order to make this work i need a query like this below, but it seems that select with cte
gives me an error.

When you paste code as an image, it's difficult the take that code and rewrite it to show you the correct way to do it.  You should always paste your code as text when asking a question.

You need to put the WITH CTE at the beginning of the sql =, such that

sql = ";with cte as (select *, row_number() over()... FROM [TABLE])
SELECT (Your Headers)
UNION ALL
SELECT (Specific columns) from cte
"


SQL Query For Displaying 'No Data Found' for values containing null and 0 along with other records The following was one of the practice questions in the online course i took on udemy for SQL.

I have a table 'emp' and consider the below names in the query map to their corresponding columns in table. In this table there is a column named 'comm' which is configured to accept number datatype. What i want is to retrieve data from the table in such a way that wherever the value in comm is empty(null) or 0, i want to display 'No data found' and wherever there is a value, it should show that number normally.

I executed the below query and it worked. But i'm looking for another concise and efficient way to write this, maybe using subquery or something else, i don't know.

select ename,sal,job,'No data found'as comm from emp
where comm is NULL or comm=0
union
select ename,sal,job,to_char(comm) as comm from emp where
comm is not null and comm!=0

In the query after union, i had to use to_char to avoid datatype error. Which makes me think that even when we are displaying data using select statements and not inserting/updating the record, the data type of the values in column should be same. Is it ? I was under the impression that since we are displaying data, we can display it however we want it. Please do comment on this also.

Appreciate your suggestions/thoughts here!

An example with CASE statement:

select
  ename, sal, job,
  case
    when comm is null or comm = 0 then 'No data found'
    else to_char(comm)
  end as comm
from
  emp
;

The UNION [ALL], INTERSECT, MINUS Operators

  The corresponding expressions in the select lists of the component queries of a compound query must match in number and must be in the same data type group (such as numeric or character).


SQLAnywhere: Switch to choose between WATCOM and T-SQL? Is there any switch available to set/force a script using either Watcom or T-SQL dialect on SQLAnywehre?

No, but if you use a specific WSQL syntax (such as the SET statement), your procedure will be compiled as WSQL.  Similarly, if you use TSQL specific syntax (such as GOTO), your procedure will be compiled as TSQL.  Mixing the two syntaxes generally results in an error.


Trigger to audit table changes I have created a trigger to audit changes to a table:

CREATE TRIGGER [dbo].[iudt_AutoAuditChanges]
   ON  dbo.CPTCategoryMaster
   AFTER INSERT,DELETE,UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    Declare @v_AuditID bigint

    IF OBJECT_ID('dbo.AutoAudit','U') IS NULL BEGIN
        CREATE TABLE [dbo].[AutoAudit]
        (   [AuditID] bigint identity,
            [AuditDate] DateTime,
            [AuditUserName] varchar(128),
            [TableName] varchar(128) NULL,
            [OldContent] XML NULL,
            [NewContent] XML NULL
        )

        ALTER TABLE dbo.AutoAudit ADD CONSTRAINT
        PK_AutoAudit PRIMARY KEY CLUSTERED
        (
            [AuditID]
        ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

        CREATE NONCLUSTERED INDEX [idx_AutoAudit_TableName_AuditDate] ON [dbo].[AutoAudit]
        (   [TableName] ASC,
            [AuditDate] ASC
        )WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    END

    Select * Into #AuditDeleted from deleted
    Select * Into #AuditInserted from inserted

    While (Select COUNT(*) from #AuditDeleted) > 0 OR (Select COUNT(*) from #AuditInserted) > 0
    Begin

        INSERT INTO [dbo].[AutoAudit]
            ( [AuditDate], [AuditUserName], [TableName], [OldContent], [NewContent])
        SELECT
            GETDATE(),
            SUSER_NAME(),
            [TableName]=object_name([parent_obj]),
            [OldContent]=CAST((SELECT TOP 1 * FROM #AuditDeleted D FOR XML RAW) AS xml),
            [NewContent]=CAST((SELECT TOP 1 * FROM #AuditInserted I FOR XML RAW) AS xml)
        FROM sysobjects
        WHERE
            [xtype] = 'tr'
            and [name] = OBJECT_NAME(@@PROCID)

        Set @v_AuditID = SCOPE_IDENTITY()

        Delete from AutoAudit
        Where AuditID = @v_AuditID
            AND Convert(varchar(max),oldContent) = Convert(varchar(max),NewContent)

        Delete top(1) from #AuditDeleted
        Delete top(1) from #AuditInserted

    End
END

This produces XML data like:

row CPTCategoryId="1" CPTCategoryName="NEW CHARGE" Remarks="" Status="1" CreatedBy="11" CreatedDate="2014-05-01T12:50:16.947" ModifiedBy="11" ModifiedDate="2014-05-01T14:20:47.793" LockVersion="0"

How can I separate the column data (CPTCategoryId, CPTCategoryName, Remarks... etc) for display in a grid view? Or is there a better way to implement this requirement?

You can catch values for every inserted column in a row and save them into the table where you save other audit data.

There are third party tools, such as ApexSQL Audit, that create such triggers, captures data (for UPDATEs both old and new) and shows reports.

When a row is inserted into a table with the following columns:

All column values are shown as separate columns

You can find more useful info here: Auditing triggers in SQL Server databases

Disclaimer: I work for ApexSQL as a Support engineer


PostgreSQL 9.6.17 COALESCE function error I'm trying a SQL query with the function COALESCE in PostgreSQL 9.6.17, but it produces an error:

invalid syntax for type double precision: ""

Example query:
SELECT COALESCE(date_part('year', s.date_pp),'')
FROM public.sendoc s.

This query is working on PostgreSQL 9.3.17.
How to get around the error at the server DB level without changing the source code of the program?

I don't think you can "fix" this without changing your query or going back to Postgres 9.3, where apparently type casting rules were more relaxed.
date_part() returns a floating point value, and in the absence of an implicit type cast Postgres tries to convert the other operand of coalesce() to a compatible value, and an empty string ('') cannot be so converted.


Filtering special characters in to_tsquery This question has been asked before here and here. I want to provide full-text support using postgreSQL via to_tsquery. However, since escaping characters leads to complications, I think it is okay to simply filtering special characters that could provoke errors.

What are the characters I need to remove to use to_tsquery safely. So far, I have identified the following:

Input that contains &, |, : might produce syntax errors
Input with * might not work as expected (to_tsquery('a*b') produces 'b')
quotes need to be escaped

What do you suggest in order to take user input in to_tsquery? What are other special characters I need to remove?

You may want to just leave it as users enter it, since you can safely use strings processed twice via quote_literal. In the to_tsquery documentation, the example for phrases within ts queries is:

SELECT to_tsquery('''supernovae stars'' & !crab');

Adding some junk to the phrase breaks it, unless you pass it twice via quote_literal, which makes any string safe to use:

SELECT to_tsquery(quote_literal(quote_literal('supernovae '':|stars')) || ' & !crab');


MySQL - Multiple index with join and where query I have the following table:

CREATE TABLE `table1` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `sha256` BINARY(32) NOT NULL,
    `state` TINYINT(4) NOT NULL DEFAULT '0',
    `is_ignored` BIT(1) NOT NULL DEFAULT b'0',
    PRIMARY KEY (`id`),
    UNIQUE INDEX `sha256` (`sha256`)
);

And I have the following query:

SELECT 1
FROM table2
JOIN table1 ON table1.sha256 = table2.sha256
WHERE table1.is_ignored = FALSE AND table1.state = 1

table2 has 20m rows indexed by sha256.

table1 has 5m rows.

I am considering how to index table1 (not many inserts).

Will creating an index of sha256_state_is_ignored index increase performance for this query comparing to 3 separate indexes on each the columns?(sha256 index is very dominant here)
Will it change if I make the combined index as state_sha256_is_ignored instead of sha256_state_is_ignored.(thats because sha256 already has an index from his unique index)

EDIT:

This is my final table:

CREATE TABLE IF NOT EXISTS maint.files_state_global (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `sha256` BINARY(32) NOT NULL,
    `source` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '0-default(cloud), 1-dynamic compsInOrg and vt',
    `state` TINYINT(4) NOT NULL DEFAULT '0' COMMENT '0-unknown, 1 - white, 2- black',
    `is_ignored` BIT(1) NOT NULL DEFAULT b'0',
    `date_in` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `last_seen` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `sha256` (`sha256`),
    INDEX `source` (`source`),
    INDEX `is_ignored` (`is_ignored`),
    INDEX `last_seen` (`last_seen`),
    INDEX `state_is_ignored_sha256` (`state`, `is_ignored`, `sha256`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

I must have a single index on source/last_seen/sha256.
sha256 is unique.
state and is_ignored must have index and will always come together(90% of the cases with sha256 as well).

So other then changing the sha256 to PK and removing id, are my indexes optimized?

WHERE table1.is_ignored = FALSE AND table1.state = 1

says to start a composite index with those two columns (in either order).  Then, looking at the rest of the SELECT, I see only sha256 being mentioned.  So, you may as make a "covering" index:

INDEX(is_ignored, state, sha256);

Meanwhile, get rid of id, assuming it is not really used elsewhere.  This will

shrink the disk footprint, thereby helping performance,
get rid of a unique index, thereby helping insert performance.

This leads to

PRIMARY KEY(sha256)

Meanwhile, table2 needs an index (possibly the PK) starting with sha256.

But... Digests (such as sha256) and UUIDs are terrible for performance due to their randomness.  Once the size of those two tables becomes more than your innodb_buffer_pool_size, performance will go downhill.  SSDs will help some.  More RAM will help a lot.  but you won't get much past 1K queries/sec.

More on building indexes.


slow index creation with gist and gist__int_ops I am seeing performance issues with frequently updated / inserts table, and one of my hypothesis is the gin index I have on int array.

The gin index was added since we have query of "X exists in array?" in high profile endpoint.

I created a one-time replica of the production DB and removed the index and replaced it with GIST and gist__int_ops to check it's performance (since it should work better on frequently updated /inserts table).

The gist index creation never finishes, I let it run for hours and no results on the other side, gin index creation takes few minutes.

I tried cleaning the data - I had arrays with 50 to 120 items, and I removed them and now all of my data is an array with one element and still, index creation is slow.

DB: PostgreSQL 11.5 on RDS  (db.m4.4xlarge)
Table size: 9373 MB

Any ideas what I can do here?

I have also had this problem, and have never found a solution, other than "don't use gist__int_ops".  The picksplit or penalty function seems to go pathologically wrong on large tables.  I don't know if this is a bug someone could fix with enough effort, or if it is an inherent problem.

  (since it should work better on frequently updated /inserts table)

I don't know how good this advice actually is (or ever was).  Note that it was removed from the docs in version 9.5.  But if you can never get the build to finish anyway, then surely it can't be good advice for your situation.


Indexing of a PostgreSQL bitstring (up to 20,000 bits each) I am building a table that contains chemical compounds (many millions of rows) and of those compounds certain predetermined features/fragments are flagged in a fixed length bitstring.
This bitstring will have between 2000 and 20000 bits, further research needs to be done to determine more precise figures there.
When searching for compounds that have certain specific features, or lack specific features, a search is done on a select subset of this bitstring. This can be a different subset every time.

Is there an index type that can make these searches efficient in PostgreSQL (9.6 or 10)?

Insertions are infrequent and done in a batch process, whereas searches are the mostly used operation and should preferably be quick and not have false positives or false negatives.

To me this vaguely sounds like work for a GIN index, but my understanding of this index type is not enough to say for sure if that is realy the case.

Actually there could be another solution, and that is to create a separate 'fragment_index' table, with fragment identifiers (as they have a fixed position in the bitstring they also have a numerical identifier) + compound-id pairs.
My worry there is that that table can become enormous (20M compounds with avg 50 hits on fragments = 1G rows) and multiple joins against it (one for each fragment) where that join can also return up to 80% of matches with the compounds table (in some cases this is likely) will not perform at all.

I would be gratefull to get any suggestions towards a direction to get this on the road.

update: i tried a GIN index using the trigram module on a varchar array with codified shortcodes, it gave mixed results, mainly depending on the amount of data left over after the filter operations.

For the sake of giving examples that have any meaning let's assume the table looks like this:

CREATE TABLE testcompounds (
 id serial primary key,
 cd_structure text,
 features_as_text varchar(128),
 features_as_bits bit varying(32)
);

CREATE INDEX flags_testcompounds on testcompounds using gin (features_as_text gin_trgm_ops);

CREATE TABLE fragments (
 id serial primary key,
 smarts text,
 keystring varchar(4),
 frequency int
);

insert into fragments (keystring,smarts) values('AAA', '*=O');
insert into fragments (keystring,smarts) values('AAB', '[#6]=O');
insert into fragments (keystring,smarts) values('AAC', '[#7]=O');
...
insert into fragments (keystring,smarts) values('AAN', '[#6]-F');
insert into fragments (keystring,smarts) values('AAO', '[#6]-Cl');
insert into fragments (keystring,smarts) values('AAP', '[#6]-Br');
...
etc.

and the features_as_text and features_as_bits fields have been fully populated.

An example of a query that can be ran on this would be:

select id, cd_structure from testcompounds
where
    (features_as_bits & (B'00000000000000000000000000000001' << (2-1)) = (B'00000000000000000000000000000001' << (2-1)))

AND (features_as_bits & (B'00000000000000000000000000000001' << (18-1)) = (B'00000000000000000000000000000000'))
AND (features_as_bits & (B'00000000000000000000000000000001' << (19-1)) = (B'00000000000000000000000000000000'))
AND (features_as_bits & (B'00000000000000000000000000000001' << (5-1)) = (B'00000000000000000000000000000000'))
AND (features_as_bits & (B'00000000000000000000000000000001' << (6-1)) = (B'00000000000000000000000000000000'))
AND (features_as_bits & (B'00000000000000000000000000000001' << (7-1)) = (B'00000000000000000000000000000000'))
AND (features_as_bits & (B'00000000000000000000000000000001' << (8-1)) = (B'00000000000000000000000000000000'))
AND (features_as_bits & (B'00000000000000000000000000000001' << (9-1)) = (B'00000000000000000000000000000000'))

In words: get everything that has feature 2, and does not have any of features 18,19,5,6,7,8,9

Famous problem in cheminformatics.  You will have a hard time coming up with an index that will beat a full-table scan for this purpose.  The most performant solutions keep the bitstrings outside of the database, where they don't have to deal with transactional overhead, and are optimized for efficient use of popcnt hardware support.

You can take some shortcuts, by for example keeping the bitstring data sorted by the number of on bits.  If the query says "find me things with each of these 45 different bits turned on" then you can quickly skip all the records with less than 45 bits turned on, as they can't possibly qualify.  The problem is that queries are usually sparser in bits than whole compounds are, so the number of records skipped by this manner is pretty small.  Another optimization is to use something like a genetic algorithm to select out certain bits as being more important than others (trained on the nature of the queries you run), and sorting on the population count of just those select bits rather than the entire bitstring.

Anyway, I don't think you will find what you want in an in-core PostgreSQL index, so be prepared to do some heavy hacking.  You might want to look to Indigo source code for some inspiration.  (Or maybe you can just use Indigo for your intended purpose directly)


Why GRANT UPDATE on SEQUENCE in PostgreSQL I have seen postgresql allowing users to Grant update on a sequence. But never seen any update query on the sequence.
Sequence values are usually changed using ALTER SEQUENCE command. I understand the need for GRANT USAGE and GRANT SELECT on sequences. But what exactly does a GRANT UPDATE on a sequence lets you do in PostgreSQL.

The documentation says:

nextval ( regclass ) → bigint
[...]
This function requires USAGE or UPDATE privilege on the sequence.

setval ( regclass, bigint [, boolean ] ) → bigint
[...]
This function requires UPDATE privilege on the sequence.


Oracle DB unix/linux autostart - oracle user no login shell I have to setup an autostart for a Oracle DB 12c on a RHEL 6.8 OS. The user oracle has no login shell e.g.

sudo su - oracle -s /bin/bash

I followed a guide for a RHEL setup. The init.d script Looks as follows:

#!/bin/bash
# chkconfig: 35 99 10
# description: Starts and stops Oracle processes

ORA_HOME=/opt/oracle/product/12cR1/db
ORA_OWNER=oracle

PATH=${PATH}:$ORACLE_HOME/bin
HOST=`hostname`
PLATFORM=`uname`
export ORACLE_HOME PATH
case $1 in
'start')
        su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl start"
        su - $ORA_OWNER -c "$ORA_HOME/bin/dbstart"
        ;;
'stop')
        su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl stop"
        su - $ORA_OWNER -c "$ORA_HOME/bin/dbshut"
        ;;
*)
        echo "usage: $0 {start|stop}"
        exit 1
esac
#
exit

After os reboot.  Oracle DB does not start. What could be wrong? Are there other logs beside $ORACLE_HOME/startup.log?

The init.d script has to be modified by adding "-s /bin/bash" to the start and stop case. This should allow the oracle user to be login without the password.

#!/bin/bash
# chkconfig: 35 99 10
# description: Starts and stops Oracle processes

ORA_HOME=/opt/oracle/product/12cR1/db
ORA_OWNER=oracle

PATH=${PATH}:$ORACLE_HOME/bin
HOST=`hostname`
PLATFORM=`uname`
export ORACLE_HOME PATH
case $1 in
'start')
        su - $ORA_OWNER -s /bin/bash -c "$ORA_HOME/bin/lsnrctl start"
        su - $ORA_OWNER -s /bin/bash -c "$ORA_HOME/bin/dbstart"
        ;;
'stop')
        su - $ORA_OWNER -s /bin/bash -c "$ORA_HOME/bin/lsnrctl stop"
        su - $ORA_OWNER -s /bin/bash -c "$ORA_HOME/bin/dbshut"
        ;;
*)
        echo "usage: $0 {start|stop}"
        exit 1
esac
#
exit


Update audit database name in triggers and views of main database? I have a a system with two databases, main database and audit database. A lot of the triggers and table views in the main database and audit database are referencing from one database to the other. No I needed to change both databases names but unfortunately they failed to work because they still have the old names in the code.

Is there a code to search and replace the old name used for referencing or in dependence?

Thank you,

You can use the text column from sys.all_sql_modules to create alter scripts with your new database names. Below is an example where I use AdventureWorks2014 to replace any text where there is a CREATE with an ALTER while changing database name HumanResources to HR.  Again this will only generate your scripts.

EDIT: Replaced syscomments with all_sql_modules.  Thanks Kenneth Fisher!

USE AdventureWorks2014
GO

SELECT
    o.name,
    o.type,
    o.type_desc,
    sc.definition,
    REPLACE(REPLACE(sc.definition, 'CREATE ', 'ALTER '),'[HumanResources]','[HR]')
FROM sys.all_sql_modules sc
    inner join sys.objects o
        on sc.object_id = o.object_id
WHERE o.type IN ('V','TF','P','TR') --Views, Table Function, Proc, Trigger. You can include/exclude additional types if you choose.

Hope this helps!


Why are users allowed to connect to SQL Server when creating sql account, that defaults login to 'master" for login When a new login is created in SQL Server it automatically defaults database to master.

We do not give permissions in master (systemdb) but in our user DB (CustomersDB) with db_datareader and db_datawriter.

We save the account.

Then a user is able to connect, why?  Does this have to do with public permissions in master?

All logins exist in the master database. Why it is default is probably related to this, but you can change the default database to your user database if you wanted to. The default being master simply means they have to explicitly set the user database in the connection string for the application.

The default database has nothing to do on why the logon can connect to the instance. This comes from being granted connection privileges to the instance. This is done by default when using SSMS to create the login.

Additional Info

Public role is granted connect permissions and any login created is automatically part of that role.

The only permissions granted for every endpoint is Connect. The only thing they get at the server level is view any database.

You actually cannot remove a login from the public role either, SQL Server does not let you because it is hard coded in there somewhere. You will most security standards for things like HIPPA or DoD (STIGs) want you to simply harden what the public role can do at the server level and within each database. See "INF: SQL Security - Restricting access to public..." for a bit more information on it all.

Creating a login straight from GUI or from just CREATE LOGIN and you will automatically see these permissions at the server level:


Building a branched versioning model for relational databases I am database designer and at my current project I'm implementing versioning capabilities required to concurrently edit rows of data in RDBMS. The project requirements says, that data editing sessions can go on for several hours or days until performing commit. Also, conflicts are arising during simultaneous modifying of the same data by different users should be handled with possibility of manual and semi-automatic resolution. In other words, desired editing workflow is similar to one used in document-oriented version control systems, such as SVN or Git. Therefore, traditional OLTP approaches and conflict resolution strategies (MVCC, optimistic/pessimistic locks) doesn't satisfy my constraints. I have done some observation of existing tools, that offer possibilities for branched version history and multiversion workflow:

ArcSDE - ESRI's ArcGIS supports versioning for geodatabases through ArcSDE data layer;
Oracle Workspace Manager - feature of Oracle Database, providing high degree of version isolation and data history management;
SQL:2011 temporal features, including valid time and transactional time support.

SQL:2011 doesn't solve my problem, as it offers support for "linear" history of edits, not branched I'm looking for. Solutions from ESRI and Oracle are good candidates, but I'm disappointed that both have vendor-specific interfaces for manipulating versions. It seems that at this moment nobody can offer industry standard solution for branched versioning of relational data (as SQL:2011 does for temporal tables and linear version history). As a newcoming database researcher, i want to understand:

is relational database community interested in developing standard models of branched data versioning and will any contribution or research in this area be valuable? (for example, standartization as it was done for temporal features in SQL2011 in the form of language improvements)
do developers and database designers lack for database-independent open-source middleware (similar to ArcSDE), that offers support for branched version management of relational data or it would be better to introduce such features in RDBMS itself?

I think I can try to dig deeper and propose some standard model or sublanguage to deal with Git-like versioning, but i don't know where to start.

IMO this problem lacks sufficient generality to build standards or reusable solutions.  It's really just a problem you solve with data modeling,

Something along the lines of:

CREATE TABLE DOC
(
  DOC_ID INT PRIMARY KEY,
  CURRENT_VERSION_ID INT,
  MODIFIED_AT DATETIME,
  MODIFIED_BY INT,
  CONSTRAINT FK_CURRENT_VERSION
    FOREIGN KEY (DOC_ID, CURRENT_VERSION)
    REFERENCES DOC_VERSION(DOC_ID,VERSION_ID)

)

CREATE TABLE DOC_VERSION
(
  DOC_ID INT REFERENCES DOC,
  VERSION_ID INT,
  VERSION_OWNER INT,
  SOURCE_VERSION_ID INT,
  DOC_CONTENT NVARCHAR(MAX),
  CONSTRAINT PK_DOC_VERSION PRIMARY KEY (DOC_ID,VERSION_ID)
)

If the content being versioned is not stored as a blob, but is in separate tables, the pattern still holds.  All the tables that store the versioned data need the VERSION_ID.

It would certainly be an interesting research project to explore all the different ways this can be done, and discuss options for conflict resolution, merging changes and sketch out what a general solution might look like.


Conditional Counting I'm analyzing a database of login records for a university and I'm trying to write a query that will give me an idea as to how many users are using the labs for short bursts, verse a more extended stay.

I have the query below query, which tells me how many times each user has used a lab for less than or equal to 10 minutes.  What I want is a result that tells me this and their total number of logins.

select username, count(*) as "<= 10 min"
    from logins
    where duration<="600"
    group by username
    order by username asc
    limit 10;

Example desired output:

+----------+----------+----------+
| username | <=10 Min |  total   |
+----------+----------+----------+
| user1    |        4 |        7 |
| user2    |       11 |       22 |
| user3    |        1 |        3 |
| user4    |        4 |        8 |
+----------+----------+----------+

If you want to return both the total number of logins and the total number of sessions that are less than 10 minutes, then you can get the result using your current query with some minor changes.

You can move the where clause filter inside of a CASE expression to get the count of the sessions less than 10 minutes. Then just add a second count() to get the total number of sessions for each username.

select username,
  count(case when duration<=600 then username end) as `<= 10 min`,
  count(username) TotalLogins
from logins
group by username
order by username asc
limit 10;

See SQL Fiddle with Demo.  You could also use the MySQL shorthand IF() syntax instead of the CASE expression:

select username,
  count(if(duration<=600, username, null)) as `<= 10 min`,
  count(username) TotalLogins
from logins
group by username
order by username asc
limit 10;

See SQL Fiddle with Demo. Both queries will give a result:

| USERNAME | <= 10 MIN | TOTALLOGINS |
|    user1 |         4 |           6 |
|    user2 |         3 |           4 |
|    user3 |         1 |           5 |
|    user4 |         4 |           9 |


Looking up which record(s) correspond to a specific resource description in dm_tran_locks I've spent the better part of two days trying to figure out what exactly is being locked in one of my tables and why I'm getting deadlocks that seem to make no sense at face value.

I've found numerous blogs explaining how to use the undocumented %%lockres%% function to get the hash for a specific row in a table. But each of these guides only give the example where the lock in question is on the primary key for the table. I have a strange situation where there is a lock on the primary key and a unique key as well.

For context: My primary key is a clustered index on a UUID string. The only other index in this table is a composite unique key on two columns (not including the pk). When I do an INSERT into this table, I can see in sys.dm_tran_locks that there are two X KEY locks on this table: one for the pk and one for the unique constraint.

My deadlock report seems to imply (unless I'm reading it wrong -- you can see my other question here) that the deadlock is caused by a second query also locking on the unique index.

I've been experimenting on a different database using the same schema to try and figure out how I can determine if the cause of the deadlock is avoidable or not. I did an INSERT in an open transaction and compared the resource description to the %%lockres%% of all records in the locked table, only to find out that the lock on the primary key maps to the row that I added, but the lock on the unique index does not match anything in the table at all.

Does anybody here know what %%lockres%% is for this unique index? It clearly isn't a specific record in my table.

For context, here is the queries I ran to see this information:

This query lists the locks on my current database. Output below.

SELECT dm_tran_locks.request_session_id,
       dm_tran_locks.resource_database_id,
       CASE
           WHEN resource_type = 'object'
               THEN OBJECT_NAME(dm_tran_locks.resource_associated_entity_id)
           ELSE OBJECT_NAME(partitions.OBJECT_ID)
           END                                     AS ObjectName,
       partitions.index_id,
       indexes.name                                AS index_name,
       dm_tran_locks.resource_type,
       dm_tran_locks.resource_description,
       dm_tran_locks.resource_associated_entity_id,
       dm_tran_locks.request_mode,
       dm_tran_locks.request_status,
       cleanlockrs

FROM sys.dm_tran_locks
         LEFT JOIN sys.partitions ON partitions.hobt_id = dm_tran_locks.resource_associated_entity_id
         JOIN sys.indexes ON indexes.OBJECT_ID = partitions.OBJECT_ID AND indexes.index_id = partitions.index_id
         CROSS APPLY(
    SELECT LEFT(SUBSTRING(resource_description, 2, LEN(resource_description)), LEN(resource_description) - 2)
) clean(cleanlockrs)
WHERE resource_associated_entity_id > 0
  AND resource_database_id = DB_ID()
  and resource_type = 'KEY'
ORDER BY request_session_id, resource_associated_entity_id

When I then execute the following query, I only get back a result for the resource description for the first index.

-- This returns the row I just added.
select * from entities where %%lockres%% like '%27f49aa9c0ac%'

-- This does not return anything.
select * from entities where %%lockres%% like '%7e24236fccb8%'

To find lockers on a particular index you have to use seek on that index explicitly:

select *
from entities WITH (index(unique_entities))
where %%lockres%% like '%7e24236fccb8%'


How to express an empty relation (or table) in PostgreSQL? Is there a way to express an empty relation in SQL without actually creating an empty table?

For testing purposes, I want to find a way to test something like:

SELECT count(*) FROM (...) my_empty_table;

where ... generates an empty table in-line. (I expect to get 0 as answer)

Is this possible?

I tried unsuccessfully:

SELECT count(*) FROM (VALUES) t;
SELECT count(*) FROM () t;

, which give syntax errors.

and

=> SELECT count(*) FROM (SELECT) t;
 count
-------
     1
(1 row)

(I'm primarily using SQL, but I'm also curious if there is standard-compliant way of writing this).

Your last attempt can be changed to:

select count(*) from ( select where false ) t

Which however is not standard SQL.

The only way I can think of in standard SQL is to use another subselect based on a VALUES clause that returns nothing:

select count(*)
from (
  select *
  from (values (null) ) x
  where false
) t

Or use an intersection that is empty:

select count(*)
from (
  select 1
  intersect
  select 2
) t

That's partially standard SQL, because a FROM clause is required by the standard.


wildcard in creating synonymys I want to create a synonyms for the user in my database. These are my user and my schema owner

User : Scoot
Schema Owner : kitty
I would like to create a synonmys "Scoot" for accessing all the table under my schema owner "kitty". User "Scoot" have assigned the correct access right to the table. This is my script but do it accept any wildcard since I have plenty of the table and don't want to make my script too long

 CREATE SYNONYM Scoot.* FOR Kitty.*;

It's not possible, however you can easily generate the needed commands.

Use SQL*Plus and spool this to a file:

select 'create synonym scoot.'||table_name||' for kitty.'||table_name||';'
from all_tables
where owner='KITTY';

... then run it in SQL*Plus.


Display the error message returned form Mysql I need to get the whole error message returned from MySQL and assign it to @error_string :

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
       BEGIN
        SET @error_string = 'SQLEXCEPTION_ERROR_MESSAGE'; <-- here
       END;

Is there any alternative, thanks.

From MySQL manual:

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
      GET DIAGNOSTICS CONDITION 1
        code = RETURNED_SQLSTATE, error_string = MESSAGE_TEXT;
    END;

I am just not sure about the use of local vs. user variables. User ones are visible for entire session and written in the @var form and if you need them, try to just adding @ to the var names in that statement.


Unable to log into SSRS through SSMS I am trying to log into SSRS with SSMS to make a custom role. I am using my domain admin account that is able to log into the database engine side of things fine but when I try to go to reporting services I get "The username or password is not valid or you do not have permissino to access this server"

The report server is configure to use SSL, and the report server is configure to use Basic Authentication.

Basic authentication is defined in the HTTP protocol and can only be used to authenticate HTTP requests to the report server.

If you use Basic authentication, use Secure Sockets Layer (SSL) to encrypt user account information before it is sent across the network. SSL provides an encrypted channel for sending a connection request from the client to the report server over an HTTP TCP/IP connection

If we configure the report server to use Forms (custom) Authentication over SSL, we will get the error "The username or password is not valid or you do not have permission to access this server. (Microsoft.SqlServer.Management.UI.RSClient)" while connecting to the report server from SQL Server Manager Studio(SSMS) using server alias.

To fix the issue, please use the full URL and authenticate with my domain\username and password.

For example,

  https://server.domain.com/reportserver


What happens with statistics when reverting to a database snapshot Lets say I have a database FOO for which I created a snapshot (named FOO_snap). Now I'll run thousands of queries on this DB. After that I will revert DB to FOO_snap. Will I lose all of the statistics which were created for these thousand of queries?

Yes you would:

  The original source database is overwritten by the reverted database, so any updates to the database since the snapshot's creation are lost.

and

  The metadata of a reverted database is the same as the metadata at the time of the snapshot.

Quotes from Revert a Database to a Database Snapshot in the MSDN Library.


Cannot Login after configure oracle-xe I already success install oracle-xe on my ubuntu 10-04, tried to configure the port and password sudo /etc/init.d/oracle-xe configure, and then restart my computer and go to http://127.0.0.1:8080/apex and input system as the username and my password admin but it said that Invalid Login Credentials.

The problem here I cannot login, try to re-install the oracle but same problem occur. Anyone can help me with this things? I'm very appreciated your help, thanks...

Update

I found that after I re-install again oracle-xe and set the password same with my system password it worked.

Thank you so much for everyone that has already read my problem. Maybe this problem sound funny, but it really happen to me. Please note that when you try to downvote someone make sure you give comment. I'm really disappointed with this thing happen to me, even I'm newbie I know the rules around here.

Question:
Why I got '-1' here? Please add a comment if I do something wrong, I ask because I don't know the solution and I tried to googling before and cannot find it. Because there is no one that help me here so I solve my own problem. Do you think that I'm here just to find point? No I'm here because I know you can help me to solve my problem. Please read this one before criticize someone


Transferring concepts - users/groups/permissions of db objects from mysql to postgresql This seemed like a better fit here than stackoverflow, so here goes:

I've been using mysql for a while since on most of the cheap webhosts it is the provided database. However, now I've started seriously developing web apps I've been using postgresql locally for my database, with a view to using it properly on the clients installations once I'm comfortable.

The only thing I've not yet grasped (of the basics, of course) is the concept of users/groups/permissions. In mysql, I create users and I can GRANT them permissions on the various tables I wish them to have access to.

How does this concept work in Postgres? Does it? On my local machine I've been allowing the user apache access to my tables so that my django apps can work, via:

echo "GRANT SELECT,INSERT,UPDATE,DELETE ON TABLE $table TO $2"
echo "GRANT SELECT,INSERT,UPDATE,DELETE ON TABLE $table TO $2" | psql $1
echo "GRANT SELECT,UPDATE ON SEQUENCE ${table}_id_seq TO $2"
echo "GRANT SELECT,UPDATE ON SEQUENCE ${table}_id_seq TO $2" | psql $1

(part of a bigger script)

Is this secure? I'm pretty certain per-app users sounds like a more sensible idea, but then I don't know.

So, I guess that makes my questions:

How do users/groups/permissions work in postgresql?
How do they compare to mysql?
What is considered best practise?

Granting permissions to users works exactly the same way in PostgreSQL.  There are some differences in the advanced features, but what you wrote above works fine.


Why not use SQL_Latin1_General_CI_AS for a global system? What would be some really good reasons to not use the SQL_Latin1_General_CI_AS collation on a system which handles English, German, Japanese, and Chinese data?

I'm having a really hard time finding good sources which compare collations and answer my above question as well as the following

How is Japanese_CI_AS different from SQL_Latin1_General_CI_AS when it comes to sorting non-Japanese characters?
Does UCA sort differently than Japanese_CI_AS and SQL_Latin1_General_CI_AS?
What is standard industry practice for systems used globally? (For example, what does Facebook, Twitter, Google, Flickr, Baidu or Microsoft, IBM, and SAP use?)
Does SQL_Latin1_General_CI_AS define a sort order for Japanese characters? If not, how/why does Japanese text work in SQL_Latin1_General_CI_AS?

Basically I'm trying to learn how to pick the appropriate collation :)

Thanks in advance!

Collations in SQL Server handle several aspects of string data:

Locale / LCID (referring to the Culture: en-US, fr-FR, etc)

This is used to determine culture-specific overrides to the default linguistic sorting and comparison rules used by Unicode / NVARCHAR data across all Collations as well as non-Unicode / VARCHAR data for Windows (i.e. non-SQL_) Collations.
Code Page

This is the character set used for non-Unicode / VARCHAR across all Collations. To be clear, Code Pages do not apply to Unicode / NVARCHAR data as Unicode is a single character set. And to be super clear, Unicode is a single character set regardless of how it is encoded: UTF-8, UTF-16, or UTF-32.
Sensitivity

Case and Accent sensitivity can be controlled across all Collations. Kana and Width sensitivity can only be controlled when using the Windows Collations and are assumed to be "insensitive" when using the SQL_ Collations.

Also, all of the Windows Collations should have a binary option (at least the deprecated _BIN, if not also _BIN2) whereas there are only two SQL_ Collations that have the _BIN / _BIN2 options: SQL_Latin1_General_CP850 and SQL_Latin1_General_CP437.
Ability to handle Supplementary Characters

A set of Collations with names ending in _SC were added in SQL Server 2012. These allow for proper sorting / comparison as well as handling by built-in functions for UTF-16 Surrogate Pairs (which is how UTF-16 encodes Code Points above U+FFFF). This option is not available to any of the SQL_ Collations.

Please note that regardless of Collation, all UTF-16 data can be stored and retrieved without any data-loss in NVARCHAR / NCHAR / XML columns and variables, even if the Collation does not allow for properly interpreting Supplementary Characters.

Also, there are some behavioral differences for non-Unicode / VARCHAR data only when using the SQL_ Collations:

ASCII value 0 / CHAR(0) does not equate to an empty string.
When sorting data using a case-sensitive Collation (e.g. SQL_Latin1_General_CP1_CS_AS), upper-case characters will sort ahead of lower-case characters.
String-sort is used, which sorts all punctuation ahead of all letters, instead of Word-sort, which ignores dashes and apostrophes within words (e.g. a-f sorts before aa using String-sort, but after it when using Word-sort).
No culture-specific character expansions are done (e.g. 'æ' = 'ae')

There is, however, behavioral consistency between NVARCHAR data using any collation and VARCHAR data using a Windows Collation.

Hence, ideally, the SQL_ Collations shouldn't be used given the above restrictions and differences, not to mention that they are also deprecated (and there are only 77 of them and 3810 Windows Collations as of SQL Server 2014). If anything, try to use the most recent version of a particular Collation (e.g. _100_), and if offered, use one ending in _SC.

Unfortunately, SQL_Latin1_General_CP1_CI_AS is the default when installing a new instance in the US (at least). But one should not willingly pick a SQL_ Collation for new development, especially when needing to deal with multiple cultures.

But to answer the 4 additional questions:

  How is Japanese_CI_AS different from SQL_Latin1_General_CI_AS when it comes to sorting non-Japanese characters?

This is with regards to NVARCHAR data only, right? The LCID determines which culture-specific overrides to apply to the default sort options. I would suspect that the US-English characters will sort the same between Japanese and Latin1 Collations, but I am not sure if that holds true for other languages that use those characters as well, or for characters not found in US-English, such as letters with accents. And an added complication is that you have both letters already with accents and then letters without the accents combined with just the accents (i.e. combining characters) and those things might not equate the same across the English / German / Japanese / Chinese locales.

  Does UCA sort differently than Japanese_CI_AS and SQL_Latin1_General_CI_AS?

I am not sure this question makes sense. There is a default sort order given to all characters. Then each locale can override (either replace or remove) any of those defaults or add new rules. So the UCA is the base weights for characters, but then each culture can deviate from those defaults. Hence there will be a large amount of overlap in the rules, but also a large amount of variation between them.

  What is standard industry practice for systems used globally? (For example, what does Facebook, Twitter, Google, Flickr, Baidu or Microsoft, IBM, and SAP use?)

Not sure what those companies do exactly, but I doubt it is pre-indexed with culture-specific linguistic rules. At least not across ALL data. Most sites do ask for your preferred language and might use that to handle some of the data. Either way, there is no way to have a single, truly culture-independent sorting.

  Does SQL_Latin1_General_CI_AS define a sort order for Japanese characters? If not, how/why does Japanese text work in SQL_Latin1_General_CI_AS?

Not sure what is meant by Japanese text "working", but Unicode is a single character set for all languages. So, the ability to store the characters of a particular language does not imply the rules by which those characters will be sorted.

As mentioned above, the UCA is a default sort order for all characters. The Latin1 Collations can do basic sorting across all languages (in terms of Unicode / NVARCHAR data), but it would only have the default rules. The Latin1 Collations wouldn't have any culture-specific rules, and there can be multiple sets of those rules. Also, as stated above, the SQL_ Collations do not have the ability to turn on Kana or Width sensitivity, which you might need.


MySQL created_by or updated_last-by I've been giving the task of taking SQL DDL from data modeler and to transcribe it into mySQL for an internship. The only thing I cannot figure out is how to take

Created_By VARCHAR2(30) DEFAULT USER,

and turn that into sql my resolution was just to do a trigger but now the trigger doesn't work

    /* Triggers for User Creation */
DELIMITER //
CREATE TRIGGER menu_creator_tr
BEFORE INSERT ON Menu
FOR EACH ROW BEGIN
    NEW.Created_By = USER();
END
DELIMITER;

it states that NEW.Created_By is missing a colon. I am very confused now. Just started learning sql and so i am lost. Some help for both problems is what I am looking for to learn, but a help on either or will help me finish this. I really need to know this though for I have more columns that are similar.

Okay I figured it out. No Need for Delimiters because it is technically one line or one statement and only needs one semicolon. Also make sure to notice that one is for before an Insert and one before an Update. I believe that SET was also necessary.

CREATE TRIGGER CREATE_BY_MENU_TR
BEFORE INSERT ON MENU
FOR EACH ROW
SET NEW.Create_By = CURRENT_USER();

CREATE TRIGGER CREATE_BY_MENU_TR
BEFORE UPDATE ON MENU
FOR EACH ROW
SET NEW.Last_Update_By = CURRENT_USER();

The Create_By and Last_Update_By are just columns of data type varchar.


Build JSON object from one-to-many relationship data in a single query? I have a PostgreSQL 9.5.3 DB with tables like this:

container
    id: uuid (pk)
    ... other data

thing
    id: uuid (pk)
    ... other data

container_thing
    container_id: uuid (fk)
    thing_id: uuid (fk)
    primary key (container_id, thing_id)

A container can point to any number of things (without duplicates), and a thing can be pointed to by any number of containers.

There may be a large number of containers and things (depends on how many customers we have). Each container would likely only have 1 to 10 things in it. We'll only query for around 20 containers at once, max. A container can be empty, and I need to get an empty array back.

I need to build json objects that represent containers, something like this:

{
    "id": "d7e1bc6b-b659-432d-b346-29f3a530bfa9",
    ... other data
    "thingIds": [
        "4e3ad81b-f2b5-4220-8e0e-e9d53c80a214",
        "f26f49e5-76b4-4363-9ffe-9654ba0b0f0d"
    ]
}

This is working fine, but I'm doing it by using two queries:

select * from "container" where "id" in (<list of container ids>)
select * from "container_thing" where "container_id" in (<list of container ids>)

I then procedurally build the "thingIds" array for each container.

Later I found a solution with correlated subqueries that works nicely for me at the moment.

select *, array(select thing_id from container_thing where container_id = c.id) as "thingIds"
from container c;

Is there a better way to do this, perhaps using a join somehow?
It seems like they always generate a single set of rows, which would mean duplicating the container data for every thing that's being pointed at.

If containers can be empty, the currently accepted solution does not work for you. It has to be an outer join to preserve rows without match - to get equivalent results to the correlated subqueries you are using in your fiddle:

select *, array(select thing_id from container_thing where container_id = container.id) as "thingIds"
from container

1.

SELECT to_json(sub) AS container_with_things
FROM  (
   SELECT c.*, json_agg(thing_id) AS "thingIds"
   FROM   container c
   LEFT   JOIN container_thing ct ON  ct.container_id = c.id
   WHERE  c.id IN (<list of container ids>)
   GROUP  BY c.id
   ) sub;

2.

With more than a few rows per container (you mentioned 20) it's typically faster to aggregate before you join:

SELECT to_json(sub) AS container_with_things
FROM  (
   SELECT c.*, ct."thingIds"
   FROM   container c
   LEFT   JOIN (
      SELECT container_id AS id, json_agg(thing_id) AS "thingIds"
      FROM   container_thing
      WHERE  container_id IN (<list of container ids>) -- repeat condition
      GROUP  BY 1
      ) ct USING (id)
   WHERE  c.id IN (<list of container ids>)
   ) sub;

3.

Or you can combine the ARRAY constructor you found with LEFT JOIN LATERAL:

SELECT to_json(sub) AS container_with_things
FROM  (
   SELECT c.*, ct."thingIds"
   FROM   container c
   LEFT   JOIN LATERAL (
      SELECT ARRAY (
         SELECT thing_id
         FROM   container_thing
         WHERE  container_id = c.id
         -- ORDER  BY thing_id  -- optional order for deterministic results
         ) AS "thingIds"
      ) ct ON true
   WHERE  c.id IN (<list of container ids>)
   ) sub;

What is the difference between LATERAL and a subquery in PostgreSQL?

Might be faster, yet.

SQL Fiddle. (Extending @a_horse's fiddle.)

Note that the result for empty containers is subtly different in the three queries above:

"thingIds":[null]
"thingIds":null
"thingIds":[]

4.

In Postgres 9.5 (since you are using it) you could also work with  jsonb and its functionality and one less subquery:

SELECT jsonb_set(to_jsonb(c), '{thingIds}', "thingIds") AS container_with_things
FROM   container c
LEFT   JOIN (
   SELECT container_id AS id, jsonb_agg(thing_id) AS "thingIds"
   FROM   container_thing
   WHERE  container_id IN (<list of container ids>) -- repeat condition
   GROUP  BY 1
   ) ct USING (id)
WHERE  c.id IN (<list of container ids>);

Alternatively:

SELECT to_jsonb(c) || jsonb_build_object('thingIds', "thingIds") AS container_with_things
FROM   ...


can I set a MAX Limit to a BIGINT cassandra's data type? can I set a MAX Limit to a BIGINT cassandra data type?
this is my first step in cassandra db.
for example: bigint(10) just like in mysql?

No. A Bigint is just a 64-bit signed long. No size or space limits can be specified.
https://cassandra.apache.org/doc/latest/cql/types.html


Filter sp_BlitzIndex output Is it possible to filter results of sp_BlitzIndex to show only unused and duplicate indexes? If so, how?

I haven't tried anything yet. Was just giving a thought of inserting sp output in a table and then filter it there. But I am sure there must be a better way.

I know this should go to Brent and team. But posting it here to get some more interesting answers.

We recently added more options to the sp_BlitzIndex output, but the kind of housecleaning mode you're asking about isn't one of them, unfortunately.

The closest it gets to that is to run it with @Mode = 4 (as of v3.0) or @Mode = 0 (prior to v3.0), which I suspect you're already doing. The benefit to using the v3.0 is you can use the @GetAllDatabases = 1 parameter to collect all the unused/dupes for the whole SQL Server instance in one pass.

I suggest storing the output to a table and querying it for only the unused/dupes. Probably not the answer you were hoping for, but sp_BlitzIndex doesn't have that functionality built in.


Count numbers in scientific notation (E-notation) I'm trying to check if a part number field has scientific notation values and came up with this simple solution:
SELECT count(*) as overall,
       count(*) filter (where partnumber ilike '%E+%') as matches
FROM   mytable
WHERE  partnumber ilike '%E+%';

Some of these strings are not scientific number. For example:
WS-X4748-UPOE+E=

How can I identify scientific numbers?
I tried this:
WHERE partnumber ilike '[0-9]%E+%[0-9]'

But it didn't seem to work.

This would do it:
SELECT count(*) AS overall
     , count(*) FILTER (WHERE partnumber ~* '^[-+]?\d*\.?\d+e[-+]?\d+$') AS matches
FROM mytable
-- WHERE .. -- no outer WHERE

~* is the case-insensitive regular expression match operator.
The pattern'^[-+]?\d*\.?\d+e[-+]?\d+$' only applies to numbers in E-notation (without blanks).
Details in the manual.


AlwaysOn High Availability MDF and LDF file locations We have 4 replicas with 03 replica in read only mode with manual failover.

We have some drive issues and we need to rearrange the MDF and LDF files.
On the primary we have 2 drives I and J. The I drive houses the MDF/ndf and LDF files. On the J drive are the tempdb files.

In all other 03 replicas we have only I drive with all MDF/ndf/ldf and Temmpdb files.

Currently the setup is working without any issues.

In our primary replica the drive space is running low with 10% left. Can we move the ldf file to the J drive which is 80% free?

Please note that in all 03 read replicas we do not have a J drive.

In our primary replica the drive space is running low with 10% left.
  Can we move the ldf file to the J drive which is 80% free?

  Please note that in all 03 read replicas we do not have a J drive.

In theory, you can use different disks. (Unless you are using direct seeding to seed the databases to the secondaries).

However, I would prefer to have the same disks and disk throughput for transparency and reducing the probability of I/O issues when a secondary becomes primary, among other considerations such as adding data files to your database.

a quick test:

On primary, create a database on two disks

CREATE DATABASE [test]
 ON  PRIMARY
( NAME = N'test', FILENAME = N'F:\DATA\test.mdf' , SIZE = 105472KB , FILEGROWTH = 102400KB )
 LOG ON
( NAME = N'test_log', FILENAME = N'E:\LOG\test_log.ldf' , SIZE = 11264KB , FILEGROWTH = 10240KB )
GO
ALTER DATABASE [test] SET RECOVERY FULL
GO

BACKUP DATABASE [test] TO DISK = '\\Share\test.bak'
WITH STATS=5;

Restore on secondary on one disk

RESTORE DATABASE [test]
FROM DISK = '\\Share\test.bak'
WITH MOVE 'test' to  'F:\DATA\test.mdf',
MOVE 'test_log' to 'F:\DATA\test_log.ldf',
NORECOVERY,STATS=5;

Add primary to ag (execute on primary)

ALTER AVAILABILITY GROUP AGName ADD DATABASE test;

Add secondary to ag (execute on secondary)

ALTER DATABASE test SET HADR AVAILABILITY GROUP = AGName;

Validate the databases on all replicas

Change the data file on the primary

ALTER DATABASE test
MODIFY FILE (name='test',FILENAME = N'H:\DATA\test.mdf')

ALTER AVAILABILITY GROUP AGName REMOVE DATABASE test;
ALTER DATABASE test SET OFFLINE WITH ROLLBACK IMMEDIATE

-- copy file

ALTER DATABASE test SET ONLINE

ALTER AVAILABILITY GROUP AG ADD DATABASE test;

Add the secondary to the AG again

ALTER DATABASE test SET HADR AVAILABILITY GROUP = AGName;

Validate the databases on all replicas

Changing the secondary to reside on completely different disks

On the primary

ALTER AVAILABILITY GROUP AGName REMOVE DATABASE test;
BACKUP DATABASE [test] TO DISK = '\\BEUAT0563\T$\DATA\test.bak'
WITH STATS=5;

On the secondary

RESTORE DATABASE [test]
FROM DISK = 'T:\DATA\test.bak'
WITH MOVE 'test' to  'T:\DATA\test.mdf',
MOVE 'test_log' to 'T:\DATA\test_log.ldf',
NORECOVERY,STATS=5,REPLACE;

On the primary

ALTER AVAILABILITY GROUP DA4EMS_BEUAT_AG ADD DATABASE test;

On the secondary

ALTER DATABASE test SET HADR AVAILABILITY GROUP = DA4EMS_BEUAT_AG;

Extra considerations

If you where to add extra files to the database on a disk that does not exist on the secondary, you will get an error:

USE [master]
GO
ALTER DATABASE [test] ADD FILE ( NAME = N'test2', FILENAME = N'F:\DATA\test2.ndf' , SIZE = 105472KB , FILEGROWTH = 102400KB ) TO FILEGROUP [PRIMARY]
GO

This attempts to create an extra database file on all replica's, expecting the disk to exist.


This counts ranges of at least 3 days with minutes_asleep = 0.
A continuous range of 5 days still counts as 1.
A continuous range of 6 days counts as 2. Etc.
Ignoring all other entries where minutes_asleep is different.

SELECT sum(ct) AS total_count
FROM  (
   SELECT (count(*)/3)::int AS ct  -- integer division truncates as desired + see below
   FROM (
      SELECT user_id
           , the_date - (row_number() OVER (PARTITION BY user_id
                                            ORDER BY the_date))::int AS grp
      FROM   fitness_data
      WHERE  minutes_asleep = 0
      ) sub1
   GROUP  BY user_id, grp
   HAVING count(*) >= 3
   ) sub2
UNION ALL  -- see below
SELECT 0
LIMIT 1;

UNION ALL ... is just to return 0 instead of "no row" where no qualifying ranges are found. You could also use COALESCE in another subselect, but I find this more elegant.

Why (count(*)/3)::int? count() returns bigint. sum(bigint) returns numeric. I am pretty positive we don't need to go that far. Casting back to int, so we get a bigint result. Should be faster, too.

Related answer with more explanation:

Find "n" consecutive free numbers from table


Why VolumeQueueLength goes high for SQL Server in AWS We have one SQL Server hosted on AWS EC2 machine. In the monitoring tab of one of the volume, we are seeing that VolumeQueueLength is periodically reaching to around 32 and then coming back.

So before troubleshooting that why it is reaching to 32, I want answer to few of my own questions:

On what factors VolumeQueueLength depends? Does it depends on volume's max IOPS capacity, IOPS happening at that moment, Volume throughput capacity, current throughput happening or anything else as well?
How do we correlate all these things and get some meaning info/conclusion out of it?  Like are we bottleneck in terms of IOPS or in terms of Throughput? For instance I have below info:

At 2:40 UTC
VolQueueLength = 31.6
VolumeReadOps = 912k
VolumeWriteOps = 8.78k
VolumeReadBytes = 41.2k
VolumeWriteBytes = 15.5k
Vol Type: gp2
Vol IOPS: 6144

Any help / inputs would be greatly appreciated. Need to have strong foundation in storage as a DBA.

A disk queue length of 32 for a period of time on modern storage systems is most likely insignificant.

To correctly interpret disk queue length, you need to know how many physical disks are involved.  If the volume is spanned across 32 physical disks, for example, then you have an average disk queue across the disks of 1.  Since it's unlikely you can determine how many physical disks are really being used for the volume, it's difficult, if not impossible, to know what disk queue length number is going to be alarming until you can correlate it with SQL Server wait statistics showing that it is waiting on I/O.  Also, a high disk queue length can simply mean that there's a high load, and as long as performance is acceptable, then you've got nothing to fix, but just note what the disk queue length is as a benchmark.

Generally you need to first look at SQL Server waits to determine if there is a bottleneck, and what the bottleneck is.  Check out articles such as Resolving SQL Server Disk IO bottlenecks to get started on this subject.  If you see I/O waits increasing, then you'll probably also see the disk queue length increasing.

So that answers question number 2.  To answer question number 1, it's simply a matter of how many disk requests are waiting to be serviced.  If you have faster disks, or more disks, you'll be able to throw a higher load at it before it a the disk queue starts to increase.  There are so many factors--for example, if you have only one slow CPU, the application won't be able to process much data, and you'll have almost no disk queue, but performance will be horrible.


How to setup Oracle at Amazon RDS I created a Relational Database Service (RDS) with Oracle database at Amazon Web Services (aws.amazon.com). However, when I attempt to connect to the database (with SQLDeveloper or Navicat), I am getting:

ORA-12545: Connect failed because target host or object does not exist

Other databases in RDS work (I tested MySQL and PostgreSQL). Based on the error message I know that I can connect to the database and it's not a firewall issue.

When I connect to the database with:

nc -zv oracle3.cocvztbjagzq.us-west-2.rds.amazonaws.com 1521

I am getting:

Connection to oracle3.cocvztbjagzq.us-west-2.rds.amazonaws.com port 1521 [tcp/ncube-lm] succeeded!

I tried to reboot the DB instance and wait for a few hours. But that neither help.

Do you have an idea how to fix the problem?

Note: RDS from Amazon is provided as a service - I do not have access to the operation system.

Are you able to run this command from a client that has the AWS tools configured ? (you could launch an EC2 instance with an Amazon AMI Linux image which is already configured to run this command if not)

rds-describe-db-instances --headers

This should return info to verify the port number and hostname as per here


Sleeping connections I have a dedicated MySQL server(8GB) with following configuration.

[mysqld]

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
lower_case_table_names = 1
default-storage-engine=InnoDB
innodb_buffer_pool_size=5G
innodb_log_file_size=512M
query_cache_size=512M
table_cache=64
innodb_flush_method=O_DIRECT

#Replication

log-bin=mysql-bin
server-id=1
sync_binlog=1
innodb_flush_log_at_trx_commit=1

binlog-do-db=test_db1
binlog-do-db=test_db2

# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

I have 2 applications which are connecting to different databases (test_db1 and test_db2) and perform all kinds of operations to the respective dbs. Both applications are written in C# and close the MySQL connections from application side.

 if (Connection != null)
            {
                if (Connection.State == ConnectionState.Open)
                {
                    Connection.Close();
                    Connection = null;
                }
            }

The 2 applications were running and doing their jobs perfectly. We set up replication recently and (I believe after that only) now connections are sleeping for long time and application have problem. I am getting an error Two many connections after a certain time and both applications stop working.

show status like '%onn%';

Variable_name             Value
Aborted_connects                 4
Connections                    1818
Max_used_connections         99
Ssl_client_connects              0
Ssl_connect_renegotiates         0
Ssl_finished_connects        0
Threads_connected                6

I have checked different posts asked about sleeping connection issues. Majority of the posts gave the reply like client side(application) is not disconnecting connection correctly or to reduce the value of interactive time_out and connection_time-out. Both solutions are not working for me.

In the c# code you posted the connection is only closed if it is 'Open', if the state was 'Connecting', 'Broken', 'Executing' or 'Fetching' it would be left open. It is standard practice in c# wrap your db connection in a using statement, this will make sure the connection is always properly disposed of, without the need to call connection.Close.

Here is an example where a stored proc is called from a connection with a using:

using (SqlConnection connection = new SqlConnection(connectionString))
{
    try
    {
        connection.Open();
        SqlCommand command = new SqlCommand("StoredProcName", connection);
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.Add(new SqlParameter("@SomeID", someID));
        command.ExecuteNonQuery();
    }
    catch (Exception)
    {
        /*Handle error*/
    }
}


SQL Server sp_send_dbmail Query Error EXEC msdb.dbo.sp_send_dbmail
@recipients= 'test@test.com',
@copy_recipients = 'test@test.com',
@query = 'SELECT * FROM INSERTED',
@attach_query_result_as_file = 1,
@subject = 'Auto-Generated Deleted Item in Order Item Detail',
@body = 'Item has been deleted from Order Item Detail',
@body_format = 'HTML',
@importance = 'High'

When the above code executes I get the following error:

  Msg 22050, Level 16, State 1, Line 0
  Error formatting query, probably invalid parameters
  Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 495
  Query execution failed: Msg 208, Level 16, State 1, Server BK00SQL0002, Line 1
  Invalid object name 'INSERTED'.

The below works: (How do I modify for INSERTED?)

    EXEC msdb.dbo.sp_send_dbmail
@recipients= 'test@test.com',
@copy_recipients = 'test@test.com',
@query = 'SELECT * FROM [PreMfg].[dbo].[Order Item Detail Audit]',
@subject = 'An Item Has Been Deleted From the Order Item Detail Table',
@attach_query_result_as_file = 1,
@body = 'An Item Has Been Deleted From the Order Item Detail Table',
@body_format = 'HTML',
@importance = 'High'

You can't reference the inserted pseudo-table from outside the trigger. And do you really want to send an e-mail for every insert/update? And make the transaction that invoked the trigger wait for all that to happen (even though database mail is asynchronous, it isn't free)?

I suggest you log the data to a queue table, and have a job that every minute (or five minutes, or some reasonable frequency) wakes up and sends mail for any new data it finds in the queue table.


Oracle - Automate Export/Unload of Data Oracle SQL Developer has an option to export the contents of a query result to various formats (CSV/fixed width/Excel/XML).  Is there any way to automate it?

If not, what free tools are available that will let me automate exports into the same formats that SQL Developer is capable of exporting to?

There are many ways to export data from Oracle and automate the functionality. Be sure to understand exactly what the data export is being used for, though. If it is for interop between systems, then export in a format your receiving system can understand. If it is for backup purposes, go for the exp/expdp (data pump) method because a database backup needs to store much more than simply data. (Better yet, just use RMAN. But I know many DBAs who also like to do full db exports on a regular basis as well.)

You can use a number of tools to accomplish this, TOAD being the one that springs to mind. It has a powerful data export tool that supports scheduling. There is a free version available, but I am uncertain if it has the scheduling functionality. Worth a try, though.

Alternatively, use the tools already at your disposal: SQL*PLUS, PL/SQL, and cron (or the Windows Scheduler if you run Windows). For a good example of how to write a PL/SQL routine that exports a table to a CSV file, see http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:88212348059 . If you use a procedure, you can schedule it from Oracle's own job scheduler.

You can do similar things purely with SQL*PLUS as well (without getting into writing a procedure), but it isn't quite as flexible, but for what you want, it might be just what you need. SQL*PLUS is also easily scriptable so that you can then call it whenever you want via cron/Windows Scheduler.

SQL*PLUS works really well on its own to create good fixed-width reports, but it is possible to do HTML and CSV as well. XML will probably require a procedure of some sort, but I'm not an export here, so SQL*PLUS may not be perfect here (it will output to HTML, though, so that might be good enough). If exporting to Excel, remember that the current versions use XML as their file format, which makes things easy (in one way) and painful in other ways (like needing to know beforehand how many rows you're going to have in the output file).

Regardless, with a little bit of work and the combination of two or three tools, you should be able to export your data in any format you wish on any schedule you desire.

Hope that helps.


MongoDB - Killed when database disk is full I'm using MongoDB 3.6.5 (already tested on 3.2 / 3.4) and i have enabled "directoryPerDB" in /etc/mongo.conf.

#mongod.conf

storage:
   dbPath: /var/lib/mongodb
   journal:
     enabled: true
   directoryPerDB: true

I created LVM disk's for my databases and symlinked in to /var/lib/mongodb

Here is problem:

When one of these lvm's is full, it will kill mongod service, so i can't use the rest of databases, where is free space on storage. Because of that, this will kill my primary node and subsequently the secondary node.

Is there some way to deal wiith it ?

Your help will be greatly appreciated!

As at MongoDB 4.0, the overall contents of the dbPath for a deployment are integral irrespective of the directoryPerDB setting. For example, a replica set member maintains a shared oplog (operation log) with a rolling record of write operations to all databases in a deployment. All members of a replica set must have a common data history: it is currently not safe for a replica set member to continue writing data (or ignore some writes) that will cause divergence from other members of the replica set.

If there is insufficient free space for updates to be committed to disk, the affected mongod process will shut down assuming administrative intervention is required to free up additional space.

  Is there some way to deal wiith it ?

Ideally you should have monitoring in place to proactively warn about low free disk space before this becomes a critical issue. Running out of disk space is generally not a great scenario for applications (or operating systems) to recover from automatically.

Although not specific to MongoDB, some admins create "bumper files" which can provide a buffer in case emergency space is needed. For example, creating a 5GB file on each volume which can be removed to provide free space as disk usage is approaching  full (assuming you have monitoring in place so this action can be taken before disk reaches 100% usage).

If you are less concerned about the availability of some databases, you could also manage them as separate MongoDB deployments so they do not impact write availability for more critical data. However, this may be be adding unnecessary complexity versus a single deployment with appropriate monitoring.


Disk Usage Report SSMS - query to obtain same info I would like to have a query that obtains the same info as in the Disk Usage report, section data/Log files autogrowth/autoshrink events

I would like to build a custom monitoring report but i could not find where this report comes. the only thing I could find is that this info comes from:

sys.fn_trace_gettable

but i don't know what field or joins I must do to have the same functionality as the report above.

EDIT: i need these fields, also the size of the growth (for analytical purposes)

thanks for your help.

If you just want to find auto growth and shrink events, you can try this out:

DECLARE @path NVARCHAR(260);

SELECT @path = REVERSE(SUBSTRING(REVERSE(path), CHARINDEX('\', REVERSE(path)), 260)) + N'log.trc'
FROM   sys.traces
WHERE  is_default = 1;

SELECT   te.name AS EventName,
         t.DatabaseName,
         t.DatabaseID,
         t.NTDomainName,
         t.ApplicationName,
         t.LoginName,
         t.SPID,
         t.Duration,
         t.StartTime,
         t.EndTime,
         (IntegerData * 8 / 1024.) as ChangeInSize
FROM     sys.fn_trace_gettable(@path, DEFAULT) AS t
JOIN     sys.trace_events AS te
ON t.EventClass = te.trace_event_id
WHERE    te.name = 'Log File Auto Shrink'
         OR te.name = 'Data File Auto Shrink'
         OR te.name = 'Log File Auto Grow'
         OR te.name = 'Data File Auto Grow'
ORDER BY t.StartTime DESC;

Adapted from Aaron Bertrand's query here.

More reading about querying the default trace here.


Is it viable to attribute permissions to SQL Server virtual accounts (NT Service\SQLSERVERAgent, etc)? Using MS SQL Server 2012 in Microsoft Windows Server 2008.

I am somewhat confused by the New Account Types Available with Windows 7 and Windows Server 2008 R2 virtual Windows accounts [NT SERVICE]\<SERVICENAME> like NT SERVICE\MSSQLSERVER, NT SERVICE\SQLSERVERAGENT, etc.

Is it viable of daring to give permissions to, for example, [NT Service\SQLSERVERAGENT]' to access a shared resource (or local file or directory)?
And, how to do this?
For example, to attribute permissions to a Windows file share to run psexec ?

While browsing  (or pressing button Find< or adding group and users to a file share) the available in domain and/or  local/remote computer, there is no such accounts available and entering it manually give an error:

  "An object (User or Built-in security principal) with the following
  cannot be found..."

Related (though different) question that provoked this one: How to copy bak files to remote share (without AD/domain accounts involvement)?

UPDATE, answering @Jon-Siegel's comment to my deleted answer:
there is no error in my screenshot, sometimes virtual accounts detection happens, sometimes fails. Really the last 2 days I cannot reproduce it.

Can it be explained that I do not have proper permissions?
When I try to open SQL Sever Configuration Manager, I am always getting:

---------------------------
SQL Server Configuration Manager
 ---------------------------
Cannot connect to WMI provider.
You do not have permission or the server is unreachable.
Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager.
Invalid class [0x80041010]
 ---------------------------
OK
---------------------------

Also, in eventvwr.msc I observe a lot of permission-related errors (under System):

The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID
{FDC3723D-1588-4BA3-92D4-42C430735D7D}
 and APPID
{83B33982-693D-4824-B42E-7196AE61BB05}
 to the user NT SERVICE\SQLSERVERAGENT SID (S-1-5-80-344959196-2060754871-2302487193-2804545603-1466107430) from address LocalHost (Using LRPC).
This security permission can be modified using the Component Services administrative tool

.

I am not sure whether I should open another question or stick with this one...
The follow-up questions are:
1)
what are permissions for reading virtual accounts?

I cannot list anything except local computer in "Select Users, Computers, Service Accounts, Cgoups or Built-in security principals" - the entries are visible but on opening them there are no a single sub-entries visible to me:

2)
How can I enter the NT SERVICE\SQLSERVERAGENT (by typing in it manually) on another computer?

Currently I can choose it only on local computer:

by choosing the name of local computer through pressing "Locations..." button, then choosing top entry in the tree list - see the scrrenshot above;
then typing "NT Service\SQLSERVERAGENT" in "Enter the object names to select" listbox

which is after pressing "Check names" button:

That is, there is no hint how to type it.
Pressing "Advanced..." button, then "Find Now") shows a list where virtual accounts are absent:

So, firstly have a quick look through this which explains virtual accounts:

TechNet: Service Accounts Step-by-Step Guide

To answer your questions:

1/ You can't list these virtual accounts in that dialog box, I'm not 100% sure of the reason, but you always need to type them in manually, in your screenshot you also have the active directory domain listed, you would never see them in here as they are local accounts.

2/ You can't add permissions on files/shares on another machine by finding these accounts, as they live only on the machine that you installed SQL server on (NVOSIB-1C in your case).  These accounts though can access resources on the network, but they do so in the context of the machine account of the server.

So, an example would be you have NVOSIB-1C with SQL Server installed and NVOSIB-Filer with a share on it that you want the SQL Server account to be able to access.

What you would do in this situation is to add the machine account of the SQL server to the permissions on the file share, you can do this just by assigning the account DOMAINNAME\NVOSIB-1C$ the permissions you want and then as the virtual account accesses the network in the context of the machine it lives on, it would have permission to do so.

Obviously this limits your ability somewhat to assign permissions, as anything running on NVOSIB-1C that authenticated in the context of the server would now have access to the share, if this is a concern, you can do one of 2 things:

Use standard domain service accounts for the SQL Server services, you then need to maintain passwords etc for these, but it makes assigning permissions somewhat easier.
Set up a managed service account in AD, this has the advantage that the local accounts have in that you don't need to worry about passwords, and also the advantage that you can see and manage them centrally in AD like a normal account, the main disadvantage is that you need to have the managed service account created before you install SQL server, so in your case you would have to reinstall to take advantage of this.


Mimic Standard Edition Functionality on Evaluation/Developer Edition In an attempt to do proper Edition Evaluations for future projects, I'm trying to find a good way to mimic Standard Edition functionality limitations on a SQL Server 2016 Evaluation or Developer Edition instance.  I'm thinking that for a given database, I could configure Resource Governor in tandem with some DDL triggers that block Enterprise-feature commands via EVENTDATA calls to emulate a lot of the resource usage and feature limitations that are specific to Standard Edition.  Certain instance-level limitations such as Buffer Pool size, etc. cannot be emulated directly, but I can limit many of these things at the query level which I'll consider good enough.  Is there anything that the combination of Resource Governor and DDL triggers wouldn't be able to cover?  Alternatively, does anyone have a solution already available to do this that I've not yet stumbled across?  Are there other approaches that would be easier?

This question relates to the core database engine.  I don't think there's even a feasible approach to trying this with other features such as SSIS, SSAS, etc., but if anyone knows of a similar approach for those features, I'm all ears.

Final note here for anyone suggesting I just install Standard Edition; I hesitate on this approach due to the licensing costs that would be associated with it.  I would either have to purchase licenses outright or have sufficient MSDN license coverage for any users touching it.  Sadly, this project is going to involve more people than have MSDN licenses and the cost to fully purchase Standard Edition 6+ months prior to the project going live isn't going to get approval from a budget committee, especially when approval of the project rests of the feasibility testing we need to conclude beforehand.

As of today, you can't. There are things inside the engine like advanced read-ahead that you simply can't turn off.

There was a Connect request for it, and Microsoft turned it down.


SQL Server deprecated Functions effect I'm using SQL Server in various versions. In case of this I'm using functions which got deprecated.

My question is, how a deprecated function affects the usage of SQL Server?

The functions are still working but could it be, that any settings blocking the execution?

Thanks for your help.

Deprecated functions / features work fine in your current SQL Server Version. It is just a hint for you that in future versions of the SQL Server these functions will be dissmissed or replaced by others.
It doesn't mean that these functions are removed in the very next version, but it is planned in long term to remove them.

A list of deprecated features is available here: Microsoft SQL Server 2016 Deprecated features


Restoring a SQL Server 2008 R2 backup on SQL Server 2005 I'm getting an error whenever I restore a testing database on SQL Server 2005 that was backed up from SQL Server 2008 R2. It seems that I cannot restore from a higher version of sql onto lower version, how can I solve this?

  The media family on device is incorrectly formed. SQL server cannot process this media family. RESTORE HEADERONLY is terminating abnormally.

I already tested to restore the same file in its own server (SQL Server 2008 R2) and it works, so it's not corrupted.

I cannot install the updated version of SQL into my older server, so how can I use my older SQL Server 2005 as a dev server but with the updated life data from a different instance?

You can't restore a 2008 backup on SQL Server 2005. Simply not possible.

You'll need to either:

(a) Restore it on 2008, then use Import/Export wizard, Generate Scripts Wizard, Copy Database Wizard, SSIS, 3rd party tools like SQL Compare, etc. to move the structure and data to the 2005 instance, or

(b) Upgrade. Developer Edition is $50 or so.


Select a range of values I have the following data in rows:

1
2
3
6
7
15
16

I want to write a SELECT statement to return contiguous ranges. Given the sample data above, the output would be:

1,2,3
6,7
15,16

Given the following sample data:

DECLARE @Data AS table
(
    data integer PRIMARY KEY
);

INSERT @Data
    (data)
VALUES
    (1),
    (2),
    (3),
    (6),
    (7),
    (15),
    (16);

One way to achieve the result you are after is:

WITH Grouped AS
(
    -- Identify groups
    SELECT
        D.data,
        grp = D.data - ROW_NUMBER()
            OVER (ORDER BY D.data)
    FROM @Data AS D
)
SELECT
    STUFF
    (
        (
            -- Concatenate items in the current group
            SELECT
                [text()] = ',' + CONVERT(varchar(11), G2.data)
            FROM Grouped AS G2
            WHERE
                G2.grp = Grouped.grp
            ORDER BY
                G2.data
            FOR XML PATH ('')
        )
        -- Remove initial comma
        , 1, 1, ''
    )
FROM Grouped
GROUP BY
    Grouped.grp;

Test at SQLfiddle


Is it possible to configure Oracle's CONNECT THROUGH based on roles? I don't have too much administrative experience with Oracle DBs, so forgive me if I make a fool out of myself.

In our current setup, each of our clients has a database user on our Oracle database. Most applications let them log in using this user and see only their data. However, when a support person needs to confirm an incident, he needs to impersonate the client user to get the exact same rights on the database. Currently this is being done by looking up the client's password - I don't need to explain why this is a bad idea.

Oracle has a nice "GRANT .. CONNECT THROUGH" syntax where you can give a user the ability to log in as someone else without knowing their password. The only problem is that, according to our DBAs, this permission has to be given explicitly on a per-user basis, so if we have 5 support users and 100 client users, this means we need to issue 500 commands to enable the support users to log in as any client user, which poses somewhat of a maintenance issue.

I find it hard to believe that Oracle does not support using roles for this, so you could say something akin to "let all SUPPORT_ROLE users log in as CLIENT_ROLE users". I've been looking around for a while but lacking the correct terminology I'm having a hard time figuring out whether this is really not possible.

TL;DR: Is it possible to configure Oracle's CONNECT THROUGH based on roles?

A.B.Cade and Phil gave the answer in the comments:

CONNECT THROUGH cannot be granted using a role.

As Phil said, creating a script to do this is trivial.  Be aware the database links do not work the same when used via proxy.

This situation does have risk, but given the requirements and limitations in Oracle, it is sometimes necessary.  You might consider allowing the script to accept two usernames.  The DBA can then run the script on demand for the particular client and support person combination that needs it.  By revoking the privilege when the support session is complete there will be less the potential for abuse.


Oracle SQL, how to access latest queries and their data I have SQL Developer, + Oracle client 11g.

I wrote a program (MFC Visual C++) that read and writes data to a certain table.
usually i put logs in the project so I could see what I insert/Update/Delete from the table.
Now I am about to delete multiple rows. and I would like to count the lines I deleted.

How can I know the latest queries that used in the DB, what they affected and get some data on activities that were held on the server ?

thanks !

To count the rows deleted just after the delete statement use SQL%ROWCOUNT.  Here is a demonstration.

--Setup.
drop table t1;
create table t1 as (select level x from dual connect by level <=5);

set serveroutput on size 1000000 format wrapped
DECLARE
   vCount Number(3);
BEGIN
   DELETE FROM T1 WHERE rownum<=2;
   vCount := SQL%ROWCOUNT;  --<<---<<---<<---<<---<<---<<---<<---<<---
   DBMS_Output.Put_Line('RowCount:' || vCount);
END;
/

For your broader question you can look into tracing, auditing, flashback database, flashback query, logminer, materialized views, streams, and change data capture (not necessarily in that order).  We would need more details about what data you need and why before directing you any more specifically.


Why would one use the Innodb Plugin instead of the builtin Innodb in Mysql 5.1? (GPL) Innodb works in Mysql 5.1 whether or not the innodb plugin, ha_innodb_plugin.so is loaded at startup or not. Configuration dependent of course.

What are the advantages and disadvantages of either using or not using the builtin or modular innodb features?

Thanks very much for any explanation :)

The InnoDB plugin came into existence in MySQL 5.1 since version 5.1.38.

Many new features in the plugin allow you to

make InnoDB access more CPUs

innodb_read_io_threads
innodb_write_io_threads

scale up InnoDB to match the IOPs of your disk (innodb_io_capacity)

You are better off upgrading to MySQL 5.6 which has surpassed MySQL 5.1/5.5 because in  5.6

you can dump the buffer pool to disk on shutdown and reload it on startup
you can segment the buffer pool into buffer pool instances to help prevent swapping if the buffer pool is more than 50% of installed RAM

I would suggest you upgrade to MySQL 5.6 instead of dealing with enabling the old 5.1 plugin


Backing up Oracle home directory while instances are running Can I take a backup of Oracle home directory (with cp -r) before upgrade while the instances using the home are running or is it required to shutdown instances first?

Trace files/alert logs are in different directory structure and all data files, redo log files, control files and spfiles are in ASM.

Yes you can do that. There's nothing dynamically changeable here in normal circumstances. All the files here are not touched except in case you apply some patch.

Files specific to the particular database resides in:

$ORACLE_HOME/dbs
$ORACLE_HOME/network/admin

I do that now and then to create test environment. Just for such more complex directory structure I prefer rsync. With it you can resume copying if you run into some disk space or network issues.


How to give a client backups of their data without compromising our database design? The client is asking for backups of their data, however we would not like to share our database design as it may lead to a security risk...

inb4: Security by Obscurity is not a valid security control method.

I have thought about dumping only pertinent tables with their data in csv or excel xml form. What is the correct and most acceptable way without giving our whole schema away?

Dumping the data into CSV would be the best way. This way, the data is "most usable" to the client - as opposed to an XML file or even an SQL dump. They could conceivably take that data and use it in another system, for example.

If the data is going to be backed up by the client with the intention of having you (or indeed another party) "restore" or otherwise import that data at a later date, your data dump will need to include Primary and Foreign Keys so you can match the data up again.

For example, you might export a "Category" CSV file which contains CategoryID and CategoryName, a "Customer" CSV file which contains CustomerID, Name, Email etc and a CustomerCategory CSV file which simply contains a list of CustomerID and CategoryID values. You would then be able to use the three CSV files to rebuild the client's Customer and Category tables if required.

You can export the data using outfile as in the following example:

select col1,col2 into outfile '/path/file1.(txt/sql/csv)' fields
terminated by ',' lines terminated by '\n' from tab ;


Postgres VACUUM taking forever I have an specific table that is taking forever o VACUUM.

There are no locks in the DB and even letting it overnight the VACUUM didn't finish.

According to the statistics (ANALYZE works fine) the table has 45GB in size and around 250K rows.

Is there some way to show the current status of the VACUUM and/or improve the way it executes?

You can see the progress of vacuum with

select * from pg_stat_progress_vacuum;

Here are some tips for vacuuming large tables

https://medium.com/coding-blocks/optimizing-storage-and-managing-cleanup-in-postgresql-c2fe56d4cf5

You do not have to wait too long for autovacuuming


Does TRUNCATE TABLE affects isolation level in Sql Server? As I am working on the project which has SQL SERVER  as a database....I'm maintaining isolation level in my project where I enabled snapshot isolation level for my project....

As I have to make some dirty read, I am querying it with setting SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED..I've implemented it in stored proc...Here is short version of my stored proc

ALTER PROCEDURE [dbo].[proc_ReadDirtyData]
@id INT,
@mode VARCHAR(5)

AS
BEGIN
    --Read Dirty Data
    IF (@mode = 'RDD')
    BEGIN
            SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
            SELECT * FROM tbl_transaction
            WHERE id = @id;--@id foreign key in tbl_transaction.
                           --This read has been done during the transaction from C# Apps
    END
END

In my C# apps, following structure is maintained

snapshot transaction start

stage1 : tblmaster --generates id
stage2 :tblchild --inserts the data with above id as foreign key.After successfull inserting,
          --dirty read goes here applying where equal to above id for other operation

if(operationSuccessful)
{
     transactionCommit()
}
else
{
     transactionRollback();
}

snapshot transaction end

I am getting problem in stage2 after TRUNCATE operation on operated table...

I've been checking whether it makes correct dirty reads,so I used to clear the data of that specific table where dirty read has been occured...

But, the problem comes when I use TRUNCATE for clearing the table data....If I use TRUNCATE, I can't make dirty reads where as if I use DELETE, it perfectly works for dirty read....

Here, I want to make clear that I'm not making dirty read during TRUNCATE/DELETE operation...After TRUNCATE/DELETE operation only, I go for dirty read of fresh data......

Now, I'm curious that whether TRUNCATE affects internal isolation of that table or not....

TRUNCATE table will take a SCH-M lock on the table.

Even at READ UNCOMMITTED level the SELECT query will need to take a SCH-S lock. This won't be possible until the TRUNCATE transaction has ended and the  conflicting SCH-M lock has been released.


how to update date field in a record to null? I have  records in a table which a field named as 'validity' contains date as '8/15/1947 12:00:00 AM'. I need to set this date as null. I am having 1000 records,in that validity field is '8/15/1947 12:00:00 AM'. I wrote a syntax

update ACCESSCODE set VALIDITY=null where VALIDITY='8/15/1947 12:00:00 AM';

which throws error as:

  'Incorrect date value: '8/15/1947 12:00:00 AM' for column 'VALIDITY' at row 1'.

Here is my table skeleton.

CREATE TABLE `ACCESSCODE`
(  `SNO` int(11) NOT NULL AUTO_INCREMENT,
   `ACCESS_CODE` varchar(255) DEFAULT NULL,
   `STATUS` int(11) DEFAULT NULL,
   `VALIDITY` date DEFAULT NULL,
    PRIMARY KEY (`SNO`)
) ENGINE=MyISAM
  AUTO_INCREMENT=10103
  DEFAULT CHARSET=latin1 ;

Please advice and correct me with the syntax.My table name is ACCESSCODE. Am working this TOAD. Thanks in advance.

Use one of the suggested formats in MySQL documentation, for datetime literals, like the 'YYYY-MM-DD HH:MM:SS' format:

WHERE validity = '1947-05-18 00:00:00' ;

or the simpler (in this case, that there is no time part):

WHERE validity = '1947-05-18' ;


SHOW INNODB STATUS averages calculated I am reading to all tutorials/books that when running SHOW ENGINE INNODB STATUS the Per second averages calculated from the last X sec, the X should be 20 to 30 seconds. I have never manage to exceed 10 sec, so my question is: Is the output valid? i can count on these statistics? Is there something i am missing here some parameter to collect status statistics more efficient?

The SHOW ENGINE INNODB STATUS \G command will show data from the last time the command was run till now - not sure what it will be the first time. I can't shut down the server here, but if you run the command rapidly twice in succession, you'll see the time since last run - set your

mysql> pager less;

mysql> show engine innodb status \G

=====================================
2014-07-04 15:44:42 7f0a2d9c9700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 6 seconds

Take a look here - averages of less than ~30 seconds are worthless
according to Peter Zaitsev. You could also take a look at Baron Schwartz's
innotop tool - available here.


Upgrade SQL Server 2016 R Service (in-database) I am just wondering whether this is doable. I have installed R service (in-database) together with SQL Server 2016 (patched at SP1+ CU1).

I notice R service version is at 3.2.2, which you can run the following script to check

declare @v varchar(100);
exec sp_execute_external_script @language=N'R'
, @script = N'v <- toString(getRversion())'
, @params = N'@v varchar(100) output'
, @v = @v out;

print @v;

-- returns 3.2.2

But I also installed Microsoft R client, and notice its R service engine is versioned at: 3.3.2.

So my question is "does SQL Server R service (in-database) now support R version 3.3.2 ?" if so, how can I upgrade it? if not, I guess I will wait until MS ships the update.

I read MSDN, and in it, it mentions using sqlBindR.exe to do the update, but this tool is available only with Windows R server, which I did not install and I even doubt that if I installed it, whether it would update the in-database R service.

I faced the very same problem (when trying to follow the steps described in Use sqlBindR.exe to Upgrade an Instance of R Services). I could not find any SQLBindR.exe.

A friend of mine pointed out to me, that I had to explicitly install "Microsoft R Server", not as part of the SQL Server install bits and pieces, but as a separate download.

All options about how you can download Microsoft R Server are described in a blog post at MSDN, Run Microsoft R Server for Windows by Heidi Steen.

Download and install was done in some minutes. SQLBindR took some more minutes.
Voilá: Now the following script is showing me, what I was looking for. :-)

declare @v varchar(100);
exec sp_execute_external_script @language=N'R'
, @script = N'v <- toString(getRversion())'
, @params = N'@v varchar(100) output'
, @v = @v out;

print @v;

-- returns 3.3.2