texts: Is it possible to concatenate query results to form a new query?

texts:
I have now:

SELECT TABLE_SCHEMA ,
   TABLE_NAME ,
   COLUMN_NAME
FROM  INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'NNN';

This gives me several hundreds of rows, because several tables contain field with that name. I want to search some value from these tables and columns by using the previous data in new query like:

SELECT <COLUMN_NAME> FROM <TABLE_SCHEMA>.<TABLE_NAME>

Could I include the results of the first query to the tags in second one in one query and how? I would like to avoid calling the every schema, table and column by hand to check only minor things in every table.

Source:

I took the first query from:
https://stackoverflow.com/questions/420741/getting-list-of-tables-and-fields-in-each-in-a-database


texts: Can I rollback/abort a transaction if no rows were inserted?

texts:
I have a transaction in which I want to do something like this:

START TRANSACTION;

INSERT INTO table1 (...)
SELECT ... FROM table 2;

... if select count(*) from table1 returns 0 abort/rollback ...

COMMIT;

Is this possible?


texts: How to modify a stored procedure using SQLCMD for MS SQL?

texts:
Suppose a stored procedure as follows have been created using the Microsoft SQL Management Studio.

use testbase
go
create procedure testtable_pricesmaller
    @pricelimit money
as
select * from testtable where price = @pricelimit;
go

Is it possible to modify a stored procedure using SQLCMD for MS SQL?


texts: Create a grouping based on chains of pairs

texts:
Say I have two tables:

CREATE TABLE datum
(
  datum_id SERIAL PRIMARY KEY,
  datum_text TEXT NOT NULL
);

CREATE TABLE datum_pair
(
  datum_id1 INTEGER NOT NULL REFERENCES datum (datum_id),
  datum_id2 INTEGER NOT NULL REFERENCES datum (datum_id)
);

datum_pair records related pairs of rows in datum. What I want to do is form identify groups that are all related, including relationships that pass through another row. For example, if the IDs 1 and 3 are related, the IDs 3 and 5 are related, and the IDs 5 and 7 are related, then 1, 3, 5, and 7 would all be in the same group. The number of rows participating in one of these chains is arbitrary.

I will need to perform various aggregations grouped by each chain. I'm not set on how the output should look, but one idea I have is to associate each ID of a chain with a single ID. For example, if 1, 3, 5, and 7 are all in the same chain, then this output would work:

 datum_id | min_id_in_chain
----------+-----------------
     1    |      1
     3    |      1
     5    |      1
     7    |      1

Solutions that offer a different output format but still allow for aggregation by chain are welcome.

There are two additional complications:

datum_pair always contains relationships in both directions. So if there is a row for IDs 1 and 3, there is also a row for 3 and 1. (In my use case, this is actually the result of a self JOIN, but I don't think this is especially relevant to the question.) Each datum should only appear once in the output.
Not all rows in datum participate in a chain; many are solitary. But the final result still needs to include them. I would expect them to be identified as a chain containing only that row, so that aggregation would not combine these together.

I've created a sample SQL Fiddle containing 100 rows. To simplify testing the output, this sample relates all the odd numbered IDs and all the even numbered IDs except for 1 and 99. 1 and 99 are not related to any of the other rows.

Using PostgreSQL 9.3. (I would appreciate mentioning any simplifications provided by features of 9.4.)


texts:
I need a list of unreferenced objects, that is, objects that neither have any reference with other objects nor are used in any API (Oracle stored procedures).

This is required for reducing and resizing database size.

Please help me to find a list of these objects.


texts: Basic question about grouping and counts

texts:
I'm having trouble coming up with a query in MySQL.

Suppose we have a table that logs an action by a user.

User1 is a Doctor and is logged several times.
User2 is a Doctor and is logged once.
User3 is an Accountant and is logged twice.
User4 is a Clerk and hasn't been logged.
User5 is an Accountant and hasn't been logged.

I would like to know what "roles" (i.e., doctor, accountant, clerk) have entries in the log file.

Here is the schema:

CREATE TABLE `test1_users` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `role` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB;

CREATE TABLE `test1_log` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB;

And test data:

INSERT INTO `test1_users` VALUES
(1, 'Franklin', 'Doctor'),
(2, 'Sally', 'Doctor'),
(3, 'Linus', 'Accountant'),
(4, 'Patty', 'Clerk'),
(5, 'Marcie', 'Accountant');

INSERT INTO `test1_log` VALUES
(1, 1),
(2, 1),
(3, 2),
(4, 1),
(5, 3),
(6, 3);

If I want to see how many times a user has been logged, it's pretty straight forward.

SELECT u.name, COUNT(*) AS total
FROM test1_log l
LEFT JOIN test1_users u ON u.id = l.user_id
GROUP BY user_id;

The query produces the following:

+----------+-------+
| name     | total |
+----------+-------+
| Franklin |     3 |
| Sally    |     1 |
| Linus    |     2 |
+----------+-------+

But how would I structure the query if I want to see how many different "roles" have been logged?
Not how many times each role is logged, just a list of all the roles that are logged.

Specifically, how many different Doctors are logged? I don't care how many times each has been logged.

Such a query I'm looking for would produce the following output:

+------------+-------+
| role       | total |
+------------+-------+
| Doctor     |     2 |
| Accountant |     1 |
| Clerk      |     0 |
+------------+-------+

Both Doctors were logged, but only one Accountant and no Clerks.

Here's the closest I've come:

SELECT role, COUNT(l.id) AS total FROM test1_users u
LEFT JOIN test1_log l ON u.id = l.user_id
GROUP BY role;

But this only gives me the number of log entries, and I need the number of roles that made it into the log.

Thoughts?

Thanks.


texts: How can I Search for a string in all fields across all tables of a MySQL database?

texts:
I want to find a string and I don't know exactly which column and which table.

How can I find it?

Thanks.


texts: How to avoid SQLCODE=-968, SQLSTATE=57011 "The file system is full" error with merge statement

texts:
When doing the following merge (tempchoices is a temporary table created with DECLARE GLOBAL TEMPORARY TABLE tempchoices LIKE CHOICES NOT LOGGED IN importtemp ON COMMIT PRESERVE ROWS) all my diskspace gets used and I receive the "The file system is full" error.

MERGE into CHOICES ar
USING (SELECT * from session.tempchoices ) ac
ON ar.CHOICE_NAME = ac.CHOICE_NAME AND ar.CHOICE_LIST_NAME =
'account' AND ar.LOCALE = 'de_CH'
WHEN MATCHED and ar.DISPLAY_NAME != ac.DISPLAY_NAME THEN
UPDATE SET DISPLAY_NAME = ac.DISPLAY_NAME, DEPENDENT_VALUE =
ac.DEPENDENT_VALUE
WHEN NOT MATCHED THEN
INSERT (CHOICE_LIST_NAME, CHOICE_NAME, DISPLAY_NAME, VALUE, LOCALE,
SORT_ORDER, DEPENDENT_VALUE)
VALUES (ac.CHOICE_LIST_NAME, ac.CHOICE_NAME, ac.DISPLAY_NAME, ac.VALUE,
ac.LOCALE, ac.SORT_ORDER, ac.DEPENDENT_VALUE)

How can I avoid this error?

Resizing the disk is not an option.

What I have tried is setting the DB2_OPT_MAX_TEMP_SIZE option but with no observable result.

Both tables contain about 600'000 entries.

DB2 Version is 10.5 and I observe this both on Linux and Windows servers.


texts: How to match string in varchar field from one table with other table?

texts:
I have two tables,

table1 having a single column:

id     |   sentence(varchar 255)
1      |   I am a boy; a good boy.

and table2 having two columns:

word    |   meaning
I       |     مين
am      |     هون
a       |     أيك
boy     |     لرقة
good    |     اشا

Now I want to query both tables in such a way that I will get each word with its translation.

For the sample data the query result must be:

sentence                  |    word_meaning (virtual column)
I am a boy; a good boy.   |  {"I":"مين", "am":"هون", "a":"أيك", "boy":"لرقة", "good":"اشا"}

As you can see in word_meaning column, each word has its meaning with json.


texts: Is there a non-DDL way to preserve back-references when staging data into production?

texts:
Let's say I've got this hypothetical schema:

Source (OLTP) DB:

Table Orders
------------
OrderID int IDENTITY (PK),
CustomerID int NOT NULL,
OrderAmount decimal NOT NULL

Destination (DSS) DB:

Table Activity
--------------
ActivityID int IDENTITY (PK),
PersonID int NOT NULL,
Amount decimal NOT NULL

Table ActivityOrderImport
--------------------
ActivityID int NOT NULL,
SourceOrderID int NOT NULL

Table CustomerMapping
---------------------
CustomerID int NOT NULL,
PersonID int NOT NULL

Obviously the real deal is considerably more complicated with more transformations.  But assume for the moment that all this ETL does is merge specific transactions ("orders") from an external entity into a DSS that tracks generic "activities".  The links between the external customer and the DSS person are in the CustomerMapping table.

The idea of the "Import" table is to provide some sort of audit trail in case something goes wrong.  We don't have a lot of control over the source system and know it to be a bit on the flaky side.  So it's really important for us to be able to understand the origin of any given activity.

Right now, there's a script that does this with DDL, which looks something like this:

ALTER TABLE Activity
ADD OrderID int NULL

MERGE Activity
USING #StagingOrders
(...)

INSERT ActivityOrderImport (ActivityID, SourceOrderID)
SELECT a.ActivityID, s.OrderID
FROM #StagingOrders s
INNER JOIN Activity a
    ON a.OrderID = s.OrderID

ALTER TABLE Activity
DROP COLUMN OrderID

This works fine, in the sense of not crashing and burning, it just makes me nauseous every time I look at the DDL.

Permanently adding an OrderID column to the Activity table really isn't an option because the data may come from multiple sources, each currently needing their own log table.  Adding a separate column to the main table for all of these would break normalization and quickly turn the production database into a dog's breakfast.
Removing the IDENTITY constraint from the Activity table and using some natural derived key is a better option, but still impractical for the same reason - because every system has a different way of defining it, we'd end up having to use an nvarchar column for the primary key (ick).  Not to mention we'd lose the sequential-ness which is important for a lot of things.

So I've been wondering, is there a better way to do this, one that doesn't involve DDL but still takes into account the heterogeneous nature of the external keys (and thus the need for different tracking tables for each source)?

I'm not necessarily asking for just a script that'll work under the current design.  I realize that the design might have to be modified and I'm open to creating more tables or even separate staging databases; the only things I'm not willing to do at this point are (a) add those tracking columns to the base table and/or (b) remove the IDENTITY field.  It's important to maintain the separation of concerns here and keep the actual activity data separate from the import "log" data.

Any thoughts as to what I could do with this design to achieve all of the objectives here, or have I painted myself into a corner and made that impossible with the current restrictions?


texts:
I'm using SQL Server Express 2008 R2 and a third part application has its database on this database server.  The database seems way bigger than I would expect considering what it is actually doing.  When I do a rough add up of the space used by the tables and indexes I get to about half of the total size of the database file.

I am wondering what is using up the rest of the space.  I understand SQL Server Express is limited to 10GB which is why I'm concerned as the database is about 2/3 full. I also understand that there is free space within the database itself which is no problem.  I'm not short of disk space.

Here is how I have found the size of the tables and indexes.  I don't understand these queries very well as I googled them but they seem to report the numbers I'm interested in:

-- Size of database and how much space is used
SELECT
(SELECT CONVERT(DECIMAL(18,2), SUM(CAST(df.size as float))*8/1024.0)
   FROM sys.database_files AS df
   WHERE df.type in ( 0, 2, 4 ) ) AS [DbSize],
CONVERT(DECIMAL(18,2), SUM(a.total_pages)*8/1024.0) AS [SpaceUsed],
(SELECT CONVERT(DECIMAL(18,2), SUM(CAST(df.size as float))*8/1024.0)
   FROM sys.database_files AS df
   WHERE df.type in (1, 3)) AS [LogSize]
FROM sys.partitions p join sys.allocation_units a
  on p.partition_id = a.container_id;

Result:

DbSize   SpaceUsed  LogSize
-------  ---------  -------
7829.25    6130.09   611.13

OK so what is using that 6.1 GB?

The following query lists the sizes of all the tables and indexes (source):

;with cte as (
SELECT
t.name as TableName,
SUM (s.used_page_count) as used_pages_count,
SUM (CASE
            WHEN (i.index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
            ELSE lob_used_page_count + row_overflow_used_page_count
        END) as pages
FROM sys.dm_db_partition_stats  AS s
JOIN sys.tables AS t ON s.object_id = t.object_id
JOIN sys.indexes AS i ON i.[object_id] = t.[object_id] AND s.index_id = i.index_id
GROUP BY t.name
)
select
    cte.TableName,
    cast((cte.pages * 8.)/1024 as decimal(10,3)) as TableSizeInMB,
    cast(((CASE WHEN cte.used_pages_count > cte.pages
                THEN cte.used_pages_count - cte.pages
                ELSE 0
          END) * 8./1024) as decimal(10,3)) as IndexSizeInMB
from cte
order by 2 desc

If I add up all the tables and indexes with a size over 1mb, it comes to under 3GB in total.  (I tried sum in the above query but couldn't figure that out)

If I post the full output here it is very long and messy.

So it seems about half of the expected free space is being used up by something else.

Any ideas what I'm missing here or, do I need a better way to add up used space?


texts: MySQL: replicate some tables with replicate-ignore-db and binlog-ignore-db

texts:
Is there any way to replicate some tables from db1 and db2 with these settings:

replicate-ignore-db=db1
replicate-ignore-db=db2
binlog-ignore-db=db1
binlog-ignore-db=db2

There are other databases that need to be replicated so I can't use replicate-wild-do-table instead.


texts: What is difference between tempdb log and database log? In which circumstances these log files uses?

texts:
As we all know there are two types of log file (*.ldf) comes into the picture whenever any large query getting executed 1. database log (transaction log) file 2. tempdb log.

Please describe me how SQL server uses thes files while any query executed?

2nd thing i want to know, when it is safe to shrink log files (database log, tempdb log)? In which case we shouldn't log files?


texts:
I have built a two node SQL Server 2014 Always On cluster on the same subnet.

I need to create two availability groups/listeners in the same instance for two sets of databases. Do I need two separate IP addresses for each listener or one IP address can be shared for both listeners ?


texts: temp tables in tempdb are not cleaned up by the system

texts:
My question is similar to the one asked here: SQL Server tempdb rogue temporary tables

Since I don't have enough points to comment and the answer there doesn't address my concern, I can only ask a new question here.

Using SQL Search Tool in Visual Studio 2017 I can see the columns in the table and  confirm that the temp table named #BBC835DE is indeed from a table variable, which is related to a stored procedure. I re-run the procedure without any problem, but this table still hangs on. How do I drop tables like this and clean up the tempdb? Thanks.

PS. Based on the result from SQL Search Tool, it seems that not all the tables named like #BBC835DE are from table variables, some are from the cursor declared in stored procedures too.


texts: Is "SQL Server Auditing" what I'm looking for?

texts:
Man... this feels like a broad question, so apologies in advance. The word "auditing" seems to have some depth in the DBA world, and I may not be asking the right question when I google.

I'm trying to get my head around how to do some simple "auditing" in SQL Server. I have a subset of user tables for which I want to track changes (INSERTS, UPDATES, and DELETES). I want to know who made the change, when, and what the before and after results were (in the case of UPDATES).

I've done this in the past with triggers that would write to an "audit" table, and this provided a basic but intuitive way for me to quickly zero in on any concerning changes in the database.

For some licensing and support reasons, building triggers to capture this information won't work in my current case. I'm investigating other options (SQL Server features and commercial applications). I'm running SQL Server on AWS RDS, and it looks like SQL Server Auditing is supported. Does the output of SQL Server Audit lend itself to quick and easy consumption? Like, can I easily query for Table ABC, when/who/what was the last time a certain ROW was changed? Or alternatively is what I'm describing more in line with a commercial product (like Apex SQL)?


texts: What are the security implications of the privileges needed by the Bluemedora Postgres account?

texts:
Blue Medora is a service for forwarding monitoring metrics from Postgres to Google Cloud Monitoring.
As part of their setup instructions they offer a SQL script to configure a least privilege user to be used to login to the account. (pasted below for reference)
If the bluemedora account were compromised, what information could be leaked or what else could it get up to?
(obviously replacing tmppassword in the script is a given)
An obvious one is that it can pull all the stats that it reports on, including reporting queries that are being executed which could lead to leakage of PII if it's included in those queries.
Based on the info present an attacker could find out the names of users and superusers of the db, all the tables being used and with the queries probably a good deal of the table structures. Which could then be used to craft a targeted attack.
Are there other more direct ways to access the data stored in the tables with this account, or to escalate privileges?
Here's the script
CREATE SCHEMA bluemedora;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE OR REPLACE FUNCTION bluemedora.pg_stat_statements() RETURNS SETOF pg_stat_statements AS
$$
SELECT * FROM public.pg_stat_statements;
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

CREATE OR REPLACE FUNCTION public.explain_this (
      l_query text,
      out explain json
    )
RETURNS SETOF json AS
$$
BEGIN
  RETURN QUERY EXECUTE 'explain (format json) ' || l_query;
END;
$$
LANGUAGE 'plpgsql'
VOLATILE
RETURNS NULL ON NULL INPUT
SECURITY DEFINER
COST 100 ROWS 1000;

CREATE USER bluemedora WITH PASSWORD 'tmppassword';
GRANT SELECT ON pg_database TO bluemedora;
GRANT SELECT ON pg_stat_bgwriter TO bluemedora;
GRANT SELECT ON pg_stat_database TO bluemedora;
GRANT SELECT ON pg_stat_user_indexes TO bluemedora;
GRANT SELECT ON pg_stat_user_tables TO bluemedora;
GRANT SELECT ON pg_statio_all_sequences TO bluemedora;
GRANT SELECT ON pg_statio_user_indexes TO bluemedora;
GRANT SELECT ON pg_statio_user_tables TO bluemedora;
GRANT SELECT ON pg_tables TO bluemedora;
GRANT SELECT ON pg_tablespace TO bluemedora;
GRANT SELECT ON pg_user TO bluemedora;
GRANT SELECT ON pg_stat_replication TO bluemedora;
GRANT SELECT ON pg_stat_database_conflicts TO bluemedora;
GRANT SELECT ON pg_trigger TO bluemedora;
GRANT SELECT ON pg_stat_activity TO bluemedora;
GRANT SELECT ON pg_stat_statements TO bluemedora;
GRANT USAGE ON SCHEMA bluemedora TO bluemedora;
GRANT EXECUTE ON FUNCTION public.explain_this(l_query text, out explain text) TO bluemedora;


texts: count rows with same value and get column from other table

texts:
I'm trying to count product numbers for a period of time there are three tables.

invoice (table)
Column                   Column
-------------------------------------------
|invoicenumber (string) | datefinish(date)|
|A12345                 | 01/29/2015      |
|A12346                 | 01/30/2015      |
|A12347                 | 01/30/2015      |
------------------------------------------
invoiceDetails (table)
---------------------------------------------------------------
|invoicenumber (String) |productnumber (long)| Quantity(Short)|
|A12345                 |12345               |  2             |
|A12346                 |1244                |  1             |
|A12347                 |1244                |  3             |
---------------------------------------------------------------

products (table)
------------------------------------
|name(String) |productnumber(long)  |
|Book         | 12345               |
|Newspaper    | 1244                |
-------------------------------------

Here is the result I would like to get:

01/29/2015 2 Book 12345
01/30/2015 4 Newspaper 1244

I need the result set to include the:
datefinish, name, productnumber and the count of the productnumber by datefinish order by datefinish, productnumber.

I can get a count of all productnumbers from the invoiceDetails like this.

select i.productnumber , c.cnt from invoicedetails i
       INNER JOIN (SELECT productnumber , count(productnumber ) as cnt
        FROM invoicedetails GROUP BY productnumber ) C ON i.productnumber = C.productnumber

But if I add another table in the select i get and error and if I add a where in the JOIN i get and error. I tried to just get all but then I get a error: 'Invalid comparison or operation on strings with different collations.'

Select d.productnumber, p.name, t.datefinish from invoicedetails d, invoice t, product p where d.invoicenumber = t.invoicenumber and
d.productnumber=p.productnumber and t.datefinish >= '01/01/2015' and t.datefinish <= '01/31/2015'
order by t.datefinish, d.productnumber

Advantage Database by SAP

Thanks for any help,
Kim


texts: Searching 500 million records of binary data

texts:
I will inserting into a database the signatures of 500,000,000 pictures. The signatures will be generated using libpuzzle. Each signature is 338 bytes. (so 160 GB) plus a table for searching (read more below). I would prefer to keep the main database on one VPS server with a standard HDD (No SSD because of cost issues).

The most important aspect is search time, Insertion time does not matter.

In the past I have attempted this all within MySQL (with way less records) and had one database for everything, the main searching happened with a scheme like:

--
-- Table structure for table `signatures`
--

CREATE TABLE IF NOT EXISTS `signatures` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `compressed_signature` varchar(338) NOT NULL,
  `picture_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `picture_id` (`picture_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1107725 ;

-- --------------------------------------------------------

--
-- Table structure for table `stored_pictures`
--

CREATE TABLE IF NOT EXISTS `stored_pictures` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `url` varchar(255) NOT NULL,
  `pid` bigint(20) unsigned NOT NULL,
  `num` int(11) NOT NULL,
  `updated_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `picture_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_url` (`url`),
  KEY `idx_picture_id` (`picture_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2773867 ;

-- --------------------------------------------------------

--
-- Table structure for table `words`
--

CREATE TABLE IF NOT EXISTS `words` (
  `pos_and_word` char(5) NOT NULL,
  `signature_id` int(11) NOT NULL,
  KEY `idx_pos_and_word` (`pos_and_word`),
  KEY `signature_id` (`signature_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--

Where by nature of the libpizzle, you would search the words table for many signature_id and then get all the compressed_signature from the signatures table, do some math and spit back a score for each signature how similar it was for the search. Then for each similiarity that was above a threshold I would get the data I needed from stored_pictures by looking up the picture_id

It took about 5 minutes for 1 search to search 40,000,000 pictures - so I think there is room for improvement. Especially because I want this to be fast up to 500 million records.

Should I seperate all the non-essential data (only about 1% the size, which is everything that is related to the specific picture) in a seperate database? On a seperate server?

Because it's just doing a massive search for pos_and_word and spitting back all the signature_id's that could be a match, I assume that not having any type of relation with the data can help me pick a specific technology that will maximize my speed. Which technology is best for this?


texts:
I'm using SQL Server Management Studio 2012 and trying to use the 'Restore' functionality to copy an existing database into a new one.

This is something I've done a dozen times before, but I'm encountering an unusual problem: the wizard will not launch.  When I select 'Backup' from the Tasks menu, I get the expected 'wait' cursor and a delay before the tool opens, but no other issues.  When I select 'Restore', I don't get the wait cursor, and the wizard doesn't launch (even after 10+ minutes).

I have restarted both SSMS and my PC, this has not resolved the issue.

The only change I've made recently is installing several of Redgate's tools, but 30 minutes of google haven't brought to light any references to this being a known problem.

Can someone recommend a fix for this?

BTW, I have seen this question, but it sadly isn't applicable here - the wizard won't open at all, and this is in SSMS 2012, not 2014.


texts:
I have 3 databases on Azure's cloud, all with domains names such as xxxxxxxx.database.windows.net.

Would it be a bad idea to create domain name aliases for all 3, things such as:

database.domain.com

staging.database.domain.com

test.database.domain.com

The only issue I could come up with was that there could be a security risk involved, easily guessable domain names for the databases, but I'm not sure if that's even a risk with Azure SQL databases?


texts:
How do you run a query to list all tables, or all tables where tableName like '%abc%'.

Just poking around, I tried the following with no success:

select * from SYS.TAB$
select * from SYS.OBJ$ where Name like '%abc%'

I'm trying to re-learn in Oracle some basic things I know in MSSQL.


texts: How to show difference between two tables?

texts:
I have two databases, each containing a 'settings' table with the same structure like this.  (There are hundreds of rows, but this is an example.)

db1.settings                              db2.settings
+-------------------+-----------+        +-------------------+-----------+
| param             | value     |        | param             | value     |
+-------------------+-----------+        +-------------------+-----------+
| MinimalShotLength | 50        |        | MinimalShotLength | 30        |
| OneTemplate       | 0         |        | OneTemplate       | 0         |
| MP4Type           | Universal |        | MP4Type           | Universal |
| Mixdown002        | Default   |        | VideoDevice       | Aja       |
+-------------------+-----------+        +-------------------+-----------+

db1 may contain rows that are missing in db2 and vice-versa.  The rows are not in the same order.

I want to create a new table showing only the differences between the two like this:

+-------------------+-----------+------+
| param             | db1       | db2  |
+-------------------+-----------+------+
| MinimalShotLength | 50        | 30   |
| Mixdown002        | Default   | NULL |
| VideoDevice       | NULL      | Aja  |
+-------------------+-----------+------+

Is this possible in MySQL or do I need to do it in PHP?  The closest I could find was this answer, but that puts each param on a separate row so it's harder to read the output.


texts: Joining to Queries side by side calculating sales values by year

texts:
I want to join 2 tables from 2 different queries side by side.

Q1= Employees sales value for one year

Q2= Employees sales values for another year

Expected output:
Employee / TotalSalesValue_1996 / TotalSalesValue_ 1997

Below is my unsuccessful attempt, it seams to duplicate the Value of Q2.

select A1.Employee, A1.Title, A1.TotalSalesValue_1996, A2.TotalSalesValue_1997
from
(
  select concat(e.titleofcourtesy,' ', e.firstname, e.lastname) as Employee, e.Title, sum((od.unitprice * od.quantity) - (1 * od.discount)) as TotalSalesValue_1996
  from employees as e
  inner join orders as o on o.employeeid=e.employeeid
  inner join order_details as od on od.orderid=o.orderid
  where o.ShippedDate between '1996-01-01' and '1996-12-31'
  group by e.title, concat(e.titleofcourtesy,' ', e.firstname, e.lastname)
) as A1
,
(
  select concat(e.titleofcourtesy,' ', e.firstname, e.lastname) as Employee, e.Title, sum((od.unitprice * od.quantity) - (1 * od.discount)) as TotalSalesValue_1997
  from employees as e
  inner join orders as o on o.employeeid=e.employeeid
  inner join order_details as od on od.orderid=o.orderid
  where o.ShippedDate between '1997-01-01' and '1997-12-31'
  group by e.title, concat(e.titleofcourtesy,' ', e.firstname, e.lastname)
) as A2;

*Please note that I used one of the free data bases available (northwind.sql)


texts: How to store tax for a Product when tax rate for a Product can change and tax-exempted Products

texts:
Product

id
name
price

Tax

id
name
rate

Product_Tax

Product.id
Tax.id
effective date
expiry date

Is this a good design?

How to store Products that are tax-exempted?

Also, how to handle the case when the value of Tax changes for example VAT rate changes from 20% to 30% --> Should I store the value of rate in the Product_Tax table in that case


texts:
I searched and found many results using "Grep highest value of a row in MySQL" but I don't understand the results.

I have a row which primary key value is 4 and there are another 8 values.
Those are 45  78 89 54 98  14  19  70. I want to select the highest value, ie 98 from this row. How do I do this ?

My database table name is class6A


texts: I am facing a problem with oracles users tablespace. It automatically grows really fast. What kind of data is stored there? And what happens when it hits 100% of space used? Is there a solution to reduce the amount of data in this tablespace? At the moment we have 11 MB free of total 33 GB. Is it a good idea to delete it and how to do it? Thanks.


texts: SELECT multiple times based on changed dates

texts:
I'm trying to select different types of data depending on a payment method which is a simple intersection table with columns "id" (INT PK) , "customer_id" (INT FK), "payment_method_id" (INT FK) and "date_changed" (DateTime)

Now, the customer may request the admin to change the payment method at any time, however I need to keep track of the payment method even if it's no longer in use simply because if I change the payment method halfway through the month I'll get invoiced the first half of the month using payment method 1 and the second half using payment method 2.

Now, depending on payment method, I will need to run different queries (using PHP in the backend) however, I need to find out, what payment method to use for a certain period of time.

How could I get to know if there is a payment method more recent, and more outdated, oh and I obviously cannot select the payment methods by month since they may be left unchanged for years on end before seeing a change, and since this is about invoicing my data needs to be trackable for 5 years...

How could I go about selecting the different data based on the methods I get back and their "date_changed" ?

Thanks!


texts: Normalization, field types and relationship

texts:
The following table is part of my project. I'm really strict about field types and store engines. I've read a lot of questions from this site, but I think there are a lot to do in the first step. Is there anything wrong with below fields and/or their relationship?
Thanks in advance.

http://i.stack.imgur.com/AT1Ie.jpg


texts:
If we need to have 2 tables, one storing the author info(author_info), & another storing all the books info(book_info).

author_info has authorId as it's primary key, whereas the book_info table has bookId has it's primary key.

Now to answer a question like fetch all books written by an author in an OLTP system (latency is key), which of the following 2 will be better:

1) RDBMS: Having authorId as a foreign key in the book_info table, and joining the two tables based on the authorId.

2) Wide Column Store: Modifying the second table to be like a wide-column DB, wherein we author_id as the primary key, and simply query the second table based in author_id.


texts: SIMPLE or FULL recovery model for databases?

texts:
When should I use the full recovery model and when should I use the simple recovery model for databases?

I always used the full recovery model because it is the default, but today i encountered this error:

  Microsoft OLE DB Provider for SQL Server (0x80040E14) The transaction
  log for database 'DATABASE NAME' is full. To find out why space in the
  log cannot be reused, see the log_reuse_wait_desc column in
  sys.databases

The specific database is actually one of the smallest and most inactive databases on my server, so I have no idea how the log can be full on this database, and not the others.

To shrink the log and making the database accessable again, I changed the recovery model from FULL to SIMPLE and shrinked the logical file log, with the following command

alter database myDbName SET recovery simple
go
dbcc shrinkfile('LOG FILE LOGICAL NAME', 100)
go

It helped, but now I need to understand WHY it helped, HOW this situation started and HOW to prevent this in the future?

EDIT:

Every night at 1 o'clock, we are doing a scripted backup of every database on the server. This is being done by a 31 line script where the most important part is

set @Filename = 'D:\backup\' + convert(varchar, getDate(), 112) + ' - ' + @DBName + '.bak'
set @Description = 'Full backup of database ' + @Filename
BACKUP DATABASE @DBName TO DISK = @Filename WITH INIT , NOUNLOAD , NAME = @Description, NOSKIP , STATS = 10, NOFORMAT

Is the new recovery model and databaseshrink going to be a conflict with this script?

We are not doing any other kind of backup of the databases, and therefore not the transaction logs, should we?


texts:
I was compiling a package on an Oracle Database just fine and suddenly this error occurred:

ORA-00604: error occurred at recursive SQL level 1
ORA-01654: unable to extend index SYS.I_PLSCOPE_SIG_IDENTIFIER$ by 8 in tablespace SYSAUX
00604. 00000 -  "error occurred at recursive SQL level %s"
*Cause:    An error occurred while processing a recursive SQL statement
           (a statement applying to internal dictionary tables).
*Action:   If the situation described in the next error on the stack
           can be corrected, do so; otherwise contact Oracle Support.

I believe it's a Tablespace issue and raised the issue to our DBA Team. However, my colleague tried the same package on the same database, using the same user and was able to compile it just fine. We checked and our Database credential details are the same as well.

Any idea why it's not compiling in my SQL Developer but his is working fine?

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production    0
PL/SQL Release 12.1.0.2.0 - Production  0
"CORE   12.1.0.2.0  Production" 0
TNS for Linux: Version 12.1.0.2.0 - Production  0
NLSRTL Version 12.1.0.2.0 - Production  0


texts: Strategy for regularly updating datasets from excel files

texts:
I have ~10 excel files which are produced by a third party and updated each night. I've have a script that downloads them to my local machine. They contain ~ 10 fields (all short text / dates) and between ~10,000 and ~1m rows in each.

I'm planning to create a simple web application to enable people to search the data. I'll host it on AWS or similar. Search load will be light maybe ~1000 searches / day.

I have to assume that all the records are unique each night and need completely replace the online dataset.

It's relatively simple for me to convert the data from the excel files into a database such as Postgres and create a simple search on top of it.

My question is how do I deal with the time it takes to do the database update each night? Should I create two databases and have my application alternate between them every other night?

What is a typical strategy for dealing with a situation like this?

My current skill set is Ruby/Rails/Postgres building and simple web apps. But I've been intentionally vague about technology because I'm open minded about what to use. And I'm quite happy to learn something new to solve the problem.


texts: What are the implications of upgrading statistics SQL Server

texts:
I Have a SQL Server 2005 DB, with 500Gb of Data. The Statics do not upgrade from 2010 and this past saturday were updated. Yesterday the querys on DB were very slow and caused bad response times.

Nothing more change in the database since Saturday, so I think it may be the work in the statistics.

Tell me about it?

Thanks


texts: What does @RC mean in SSDT SQL Server Unit Testing?

texts: In Sql Server Unit Testing, SSDT, what does @RC stand for? Does it mean Row Count, or Row Column? Team wants to understand, we have been using it for long time, good tool which works, just curious.


texts: Query modify add column of Closing and Floor_Total

texts:
I want  of Column of  Total_Floor= (Opening + C_Weight)
Then need another column of Closing= Total_Floor-S_Weight -B_Weight
Below is my query
  DECLARE @FirstDOM DATETIME = ( SELECT CONVERT(DATE, DATEADD(DAY, - ( DAY(GETDATE()) + 26 ), GETDATE())));
DECLARE @LastDOM DATETIME = ( SELECT DATEADD(s, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)));

WITH cte1
AS ( SELECT   SUM(QTY) AS C_QTY ,
              SUM(IWeight) AS C_Weight ,
              EntryDate AS C_Date
     FROM     ConIssuance
     WHERE    EntryDate BETWEEN @FirstDOM AND @LastDOM
              AND DelID IS NULL
     GROUP BY EntryDate ) ,
     cte2
AS ( SELECT   SUM(s.prdqty) AS S_QTY ,
              SUM(i.weight) AS S_Weight ,
              s.EntryDate AS S_Date
     FROM     Probale s
              INNER JOIN ItemMasterFile i ON i.CodeItem = s.Codeitem
     WHERE    s.EntryDate BETWEEN @FirstDOM AND @LastDOM
              AND s.DelID IS NULL
     GROUP BY s.EntryDate ) ,
     cte3
AS ( SELECT   SUM(Bweight) AS B_weight ,
              SUM(Bpqty) AS B_qty ,
              EntryDate AS B_Date
     FROM     Bigbalprd
     WHERE    EntryDate BETWEEN @FirstDOM AND @LastDOM
              AND delid IS NULL
     GROUP BY EntryDate ) ,
     combinedResult
AS ( SELECT COALESCE(cte1.C_Date, S_Date) AS EntryDate ,
            cte1.C_QTY ,
            cte1.C_Weight ,
            cte2.S_QTY ,
            cte2.S_Weight
     FROM   cte1
            FULL JOIN cte2 ON cte1.C_Date = cte2.S_Date )
SELECT   CR.* ,
         C.*,
         LAG(CR.C_Weight-(C.B_weight + CR.S_Weight), 1,39557 ) OVER ( ORDER BY CR.EntryDate ,
                                                                           C.B_Date ) AS Opening ,
         (  (C.B_weight + CR.S_Weight)-CR.C_Weight ) AS Closing

FROM     combinedResult CR
         FULL JOIN cte3 C ON CR.EntryDate = C.B_Date
ORDER BY CR.EntryDate ,
         C.B_Date;


texts:
What is the best way to create an index on a bit string column? Let's say I had the column of type bit(4) and I wanted to search for all entries that had had a specific bit set. So if I had the entries:

bitfield | ...
--------------
1001
1010
0110
0010
0000

If I was trying to search for all entries that 0010 set, I can easily do that. But can I use indexes to optimize the searching?


texts:
Lets suppose we create a table called "SampleTable" and made "ID" column a primary key.  SQL Server by default creates a clustered index on the table. This physically sorts the data on the disk. Now suppose we create a view called "SampleView" with schema binding as select * from "SampleTable" and create a clustered index on the "SampleView". Now SQL Server will create a clustered index on the view and physically sort the data on the disk according to the clustered index.

If the data is already sorted on disk in SampleTable, how can it be sorted again in SampleView?


texts: Deleting aged rows from postgresql table which don't have related entries in another table

texts:
I maintain a website where users post interesting links, and other users leave replies under the said links. I want to delete aged link entries under which no one replied.

The postgresql database is like so: there's a table called links_link, and another one called links_publicreply. links_publicreply has a column called answer_to, which is a foreign key to links_link. I only want to delete aged rows in links_link which do NOT have a related entry in links_publicreply.

What would the queries be? Something like the following?

begin;
DELETE FROM links_link WHERE id NOT IN links_publicreply AND "submitted_on" < now() - interval('1 day');
commit;

I don't want to run into foreign key violations.


texts: MariaDB/MySQL IO limit in test environment

texts:
I have a test environment (all Linux) where we start our server application with MariaDB (which runs in docker in the case if it matters), run some tests, save the test results and drop the DB.

I want to limit the MariaDB disk usage (IOPS), because we really use the storage (with another systems) and I try to save some resources. I've realized, MariaDB shouldn't even write to the disk, as it is a test environment and I don't really need persistence. (If the node dies, the test anyway fails and we run it from scratch somewhere else.) As a side effect the test would be even faster (but it's not important).

What I've found is the Memory storage engine, what is not useful for me, because I don't want to change anything on SQL level as the same SQL scripts used on live systems (where a by mistaken left behind "ENGINE = MEMORY" would make a disaster).

I also could use a RAM disk as a filesystem and MariaDB wouldn't even realize the change. That's sound good, but in this case I have to maintain a right size of RAM disk and also waste of RAM as the data is (most likely) already in the RAM and we put it there again (on a RAM disk).

So I am looking for a solution which doesn't require changes on the DB level (SQL), but in the MariaDB config to reduce or completely eliminate the disk usage (IOPS) regardless of data durability.


texts: "Prelogin failure" when attempting to run stored procedure across linked server

texts:
I'm getting the below error when attempting to run a stored proc across a linked server that is going from Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)   to Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64).

The sql instance that I'm linking to is running the database engine successfully, however the sql server agent on the linked server's instance is unable to start with a 'object reference not set to an instance of an object' .net exception being thrown.

This error only started to occur after a scheduled server restart, so I'm thinking maybe there is a config option that was set somewhere that only took effect after the restart.

I've checked firewall and the resources available to the linked server (CPU, RAM and disk) and couldn't see anything causing an issue.

Is there anything else I should be checking? Any config options that may interfere with this that might have taken effect with the server restart?

EDIT: Ok so a server restart fixed the issue. I have no idea why though - can anyone suggest why this could have occurred and how it could've been fixed without a server restart?

OLE DB provider "SQLNCLI10" for linked server "Linkedserver" returned message "Client unable to establish connection due to prelogin failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
OLE DB provider "SQLNCLI10" for linked server "Linkedserver" returned message "Client unable to establish connection".
Msg 26, Level 16, State 1, Line 0
Client unable to establish connection because an error was encountered during handshakes before login. Common causes include client attempting to connect to an unsupported version of SQL Server, server too busy to accept new connections or a resource limitation (memory or maximum allowed connections) on the server.

The t-sql to create the linked server is below:

/****** Object:  LinkedServer [LinkedServer]    Script Date: 09/30/2009 12:34:15 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'LinkedServer', @srvproduct=N'LinkedServer\InstanceName', @provider=N'SQLNCLI', @datasrc=N'LinkedServer\InstanceName', @catalog=N'Database'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LinkedServer',@useself=N'False',@locallogin=NULL,@rmtuser=N'userid',@rmtpassword='########'

GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServer', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServer', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServer', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServer', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServer', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServer', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServer', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServer', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServer', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServer', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServer', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServer', @optname=N'use remote collation', @optvalue=N'true'


texts: How to insert in table from remote stored procedure without creating a distributed transaction?

texts: How can I insert in a local table from a remote stored procedure without creating a distributed transaction? I want to run a stored procedure on a remote server and use the output as the source for an insert into a local table.


texts: How are binlogs updated for MySQL temporary tables?

texts:
If I use CREATE TEMPORARY TABLE, can I write and update this MySQL table and know that none of it will be stored to the binlogs?
If I use data from the temp table to write to a non-temp table, is the
part-of-the-statement-that-reads-from-temp-table
simply substituted with plain values, or is there something else that happens in the binlog to indicate the data source?

I ask this because normally when reading from another table, the binlog shows the whole statement, meaning it normally does not substitute the plain values.


texts: SQL Server 2008 R2 Database - Why is the database not using free space before growing again?

texts:
I have a vendor application database with 700 tables.  The data hasn't ever been archived and dates back to 2006.   The business just wants all that data.

My question is as I am monitoring the size/growth of the database, I am noticing a trend where the database is growing but it has free space.

The database is 187GB, has 40GB free and the backup size is 146GB.
The database is growing by 1GB autogrow increment which is should not need to do if it has free space it can use, right?

I have looked at fill_factor as I wasn't sure what the vendor was doing, and at the database level it remains at the 0 default indicating the pages will be filled to 100%.  There are some objects, indexes mostly, that have a 80% or 85% fill factor.

I am stumped.

I am re-indexing and the disk fragmentation reports are coming back with little or no fragmentation.

Got any ideas?


texts:
This question is in regards to SQL 2005 and email alerts on long running queries.

Does this sound like the best approach (with the least impact to the server) to receiving alerts to long running queries is the following:

Create a server side trace that monitors across all databases or a specific database for events 10 (RPC:Completed) and 12 (SQL:Completed).
Set a trace filter on Duration for greater than n amount of time (for example, greater than 2 seconds).
Separate stored procedure that runs every n seconds (for example 5 or 10) to check for new entries and sends out an email alert. (To detect new entries I would need to keep track of the last entry detected but that is trivial.)

Thanks.


texts: Is it possible to write an SQL query that automatically reconciles/"transactionalizes" payments and charges

texts:
I'm still working on the project mentioned here (http://dba.stackexchange.com/questions/2428/how-do-i-properly-design-a-many-to-many-charges-payments-accounting-system).

The system is to give the user the option to either pay specific amounts on specific charges, or make generic "you figure it out" payments.  Given the table structure we went with (PAYMENTS, CHARGES, PAYMENTS_TO_CHARGES) I'm looking to cheat a bit.  Basically I'm looking for an amazingly snazzy "reconcile"  SQL query that will do the following:

STEP 1) Grab all Payments with remaining balance (basically credits)

STEP 2) Grab all Charges with remaining balance (partially paid, etc)

STEP 3) Insert portions of payments in the PAYMENTS_TO_CHARGES table until there is no more available credit or there are no more charges.

…I guess technically this isn't reconciliation so much as creating transactional data, but you get the idea.

Steps 1 and 2 are obviously very easy.  It's Step 3 that's the killer.  If there's no snazzy way to do this in SQL, I suppose I'll go with the old hand-coded step-by-step loop-through-each transaction and post payment_to_charge…just thought I'd ask.

Thanks in advance!

EDIT 1:
I've come up with this query to determine which charges have remaining balance, but it's giving me an error saying "Unknown column 'remaining_balance' in 'where clause'":

SELECT
       charges.*
     , (charges.amount - transactions.total_paid) as remaining_balance
FROM charges
   , (SELECT
             charge_id
           , sum(amount) as total_paid
      FROM payments_to_charges
      GROUP BY charge_id) as transactions
WHERE charges.member_id = 123
  AND charges.id = transactions.charge_id
  AND remaining_balance > 0
  AND charges.active_on < NOW()

I'm sure certain items are simply out of order, but I can't figure out what's generally wrong with this particular query.  Should I be using HAVING instead of WHERE?  Am I missing something else completely obvious?


texts: Find rows in a single table where values in a certain column are near-duplicates

texts:
I have a Postgres 11 table on RDS containing a column email; some of the values in this column [and only that column] are clearly de facto duplicates but differ in case, i.e., different capitalization, such as:

foo@****.com
Foo@****.com

To be clear, none of the present rows are true duplicates, nor share precisely the same values in that column. My objective is to identify these records [and, once found, eliminate/merge the de-facto duplicates].

My initial inclination was to use a self-join, e.g.:

SELECT c.email
FROM schema.table c
INNER JOIN schema.table d ON lower(c.email) = lower(d.email)
ORDER BY c.email;

However, this returns all the email records rather than only those that are de-facto-duplications.

Using a subquery such as the following produces a similar [i.e., too-inclusive] result:

SELECT c.email, alias.email
FROM schema.table c
  JOIN (SELECT email FROM schema.table) alias ON lower(c.email) = lower(alias.email);

Since I’m not looking for an aggregate, but rather a case-insensitive comparison, it seems to me that a window function is not the correct approach.

I think that this should be a straightforward query, but I’m having a difficult time seeing it clearly and am sure there is an error in the way I’m conceiving of the problem; it’s pretty frustrating.

In addition to searching here and on SO, I consulted Molinaro’s SQL Cookbook, but to no avail.

What is the correct way to structure the query so that it returns only those records whose email values are the same, disregarding case?

edit note : my initial question formulation expressed a misguided inclination to use ILIKE for case-insensitive matching, but the use of lower() as suggested in the below answers is far more sensible


texts: Understand why rank() over doesn't fit to don't select duplicates rows

texts:
I would like to understand why I have different results

I have a table called active_transfert where I log image transfert

user_id | image_id | created_at
--------|----------|-----------
1       |1         |2014-07-10
1       |2         |2015-01-21
2       |1         |2015-05-23
3       |1         |2016-07-22
4       |6         |2017-06-01
4       |6         |2014-08-22

I would like to return unique user_id per image_id.

SELECT user_id,
       image_id
FROM active_transfert
GROUP BY user_id,
         image_id; --50

SELECT user_id,
       image_id
FROM
  (SELECT user_id,
          image_id,
          rank() OVER (PARTITION BY user_id, image_id
                       ORDER BY created_at DESC) AS i_ranked
   FROM active_transfert) AS i
WHERE i.i_ranked = 1; -- 53

I run those queries against Redshift. Why my second query doesn't prevent duplicated records (same user_id and image_id)?

Expected result :

user_id | image_id |
--------|----------|
1       |1         |
1       |2         |
2       |1         |
3       |1         |
4       |6         |


texts: Where does SQL Server physically store the IDENTITY VALUE for a table?

texts:
I'm hoping someone can point me in the right direction on this one.  Here's my workings out so far.

SELECT * FROM sys.identity_columns is a system view which gives "last_value" but the definition for that view uses an internal function IdentityProperty(colName, 'LastValue') - so that's a dead end (not pulling it from a system table there).

Everywhere (I've looked) on the internet suggests using DBCC IDENT_... commands to uncover the value but that still leaves me in the dark as to where it's actually stored.

So, I arrived at searching the individual pages with DBCC PAGE(TestDB,1,1325,3) against my test harness db and using the RESEED command to reseed between values 10 and 12.

In doing this, I noticed the hex values on the IAM: Header, IAM: Single Page Allocations and IAM: Extent Alloc Status Slot 1 all changed.  (And realised they change periodically anyway along with the bUse1 value which changes incrementally by itself too).

So another dead-end and I'm all out of ideas.  Where else can I search?

I'm running SQL Server 2014. I have an insatiable thirst for internals knowledge and have yet to come across anything as elusive as this.  It's caught my attention because in theory, it (an absolute value) is stored somewhere and should (arguably) be locatable. In my quest to unearth locations of internally stored data / meta data, this particular value strikes me as particularly elusive.  I'm guessing/hoping that someone will come along and tell me, you can get it with DBCC PAGE but I was looking in the wrong place.


texts: How to group by when querying duplicate records?

texts:
I have a table with duplicates and I want to present it to the stakeholders so they can help me determine the source of duplication.  Currently I can print 1 row for each duplicate entry, but I want to show all attributes for each.  Here's the query I'm using:

select *, count(*), max(id) from foos group by name having count(*) > 1 order by name;

I'm not sure how to alter the query to get each record where another record shares a name.  Thanks for any guidance.


texts:
I have a table that has path1, path2, and sha1 value.  For any values of path2 and sha1, there can be multiple values of path1.  I just want one of those paths.  I don't really care which one.

I'm thinking I can do a group by for path2 and sha1.  Now I just need to select one of the values of path1.  I suppose I could select the minimum value of path1 but that would be doing extra work that isn't really needed.

Google tells me that Microsoft has "FIRST" but I don't see that in the postgres pages.  Plus... I'd like to stick with normal SQL if possible.


texts: JOIN tables and return count even if it is 0

texts:
I have this query:

select settings.customer_id, current::jsonb->'alarms' as alarms, from settings
join (select customer_id, current::jsonb->'alarms' as alarms from settings) as res on res.customer_id=settings.customer_id
where res.alarms is not null;

which returns

+------------------+
|customer_id|alarms|
|1          |{...} |
|2          |{...} |
|3          |{...} |
+------------------+

Then I have another entity registration, which fields customer_id and date, where date is of type date and I store the values in format YYYY-MM-DD.

What I want to achieve is to join the registration entity on customer_id and get a count on the registration records for a given single date, f.e. '2018-11-20'. But (here's the catchy part) I want to retrieve it in a way so if there are no registration records for the date, then return 0, otherwise return the actual number.

With the solutions I've tried so far, I would get only records that have the alarms AND have registrations as well, as if I have this:

select settings.customer_id, current::jsonb->'alarms' as alarms, coalesce(regs.count, 0) from settings
join (select customer_id, current::jsonb->'alarms' as alarms from settings) as res on res.customer_id=settings.customer_id
join (select customer_id, count(customer_id) from registration where date='2018-11-20' group by customer_id) as regs on settings.customer_id=reg.customer_id
where res.alarms is not null;

or

select settings.customer_id, current::jsonb->'alarms' as alarms, coalesce(count(regs.customer_id), 0) from settings
join (select customer_id, current::jsonb->'alarms' as alarms from settings) as res on res.customer_id=settings.customer_id
join registration as regs on settings.customer_id=regs.customer_id
where res.alarms is not null and regs.date='2018-10-10'
group by settings.customer_id, settings.current;

If I would put it in other words: get records which have alarms object and also get the number of their registrations for the given date, no matter if its 0 or > 0.


texts: I have a database design for an E-commerce website. Need to know how to display related product for each specific product

texts:
For example: A customer is viewing a product on the website, say a laptop.
There's a section on the left where i need to display products from the database that are associated with this particular product. This might be a charger or an external hard disk, etc. but only one which WILL go with this specific laptop (Meaning the charger HAS to be compatible with the laptop) I don't understand how to relate the products. Thanks in advance!


texts: Sql server 2016 Change only server and without all databases

texts:
How can i change server collation without changing whole databases, because i have
many databases which aren't belong to me, so i want change server collation and my database only.

SQL Server version:  2016
SQL Server Collation:  Arabic_CI_AI

in the server i have many databases, some of these databases i work on them and others are not belong to me and should not be touched.
I want to change the collation: Arabic_100_CI_AI

When i use this script:

sqlservr -m -T4022 -T3659 -s"MSSQLSERVER" -q"Arabic_CI_AI"

it change the server collation along with all databases in the server, but i don't want this i need to do change only the collation of the server only.

Is that possible?


texts: Connect Error (2054) - mysql_old_password issue still not solved

texts:
I use PHP 5.4.3 and I want to connect to a MySQL database (DB for brevity) remotely (the PHP script resides on a different server than the DB) with normal object-oriented programming (OOP) way:

$mysqli = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);

and I get the error:

  Warning: mysqli::mysqli(): The server requested authentication method unknown to the client [mysql_old_password]
  Connect Error (2054) The server requested authentication method unknown to the client

I found this and this but they don't seem to help. I used PHP 5.2.9 and everything worked OK, but downgrading the PHP version is not a solution for me.

I also have to mention that I don't have full access to DB's settings cause the client doesn't give me that kind of access. I told him to ask Hostgator for a newer password format support, but they answered that they cannot do anything which was not what I was expecting. I did a new effort from another remote server and I get now this:

  Connect Error (1045) Access denied for user 'user'@'host' (using password: YES)

by using PHP 5.3.9. It looks like the DB doesn't "let me in" because of the remote server's PHP script, but with Oracle's Workbench everything works fine.

Thanks for your time.


texts: How to dig deeper into ASYNC_NETWORK_IO waits

texts:
I've been handed a graph that comes from SolarWinds showing top wait events broken down by day.   The last 2 days there's a part of the daily color coded bar representing a total of 3 hours for ASYNC_NETWORK_IO.  Still trying to see if I can get access to Solar Winds panels to try and see if it offers a drill down into the graph or not.
I've been searching through google, websites and documentation for the last 2 hours and finding little of consequence to tell me how to drill down into this 3 hours.
Is it one large block, is it a few minutes here and there through out the day, does it match any particular window of high I/O activity (data warehouse refresh or something).
All I have is "here, there's a total of 3 hours of this wait yesterday and the day before, now tell me why" - and I must admit I really don't have any idea on how to drill down into this any further.
I've read all sorts of articles about "Well, it's usually a poorly designed application" or something along those lines.
Funny this is of the last 7 days, the first 5 are all clear.  No signs of this wait.  Suddenly there's a huge chunk of it.  I need to know how I can dig more.
As far as I'm aware there are no user complaints about system performance.
Is there a DMV or something to help out here ?
Anyone able to give me some pointers?
Thanks


texts:
Consider the following function

CREATE FUNCTION dbo.SafeDivide
(
    @a numeric,
    @b numeric
)
RETURNS numeric
AS
BEGIN
    RETURN CASE
               WHEN @b = 0
                   THEN 0
               ELSE @a / @b
           END;
END;

Here type numeric was used but this code would be useful for any numeric types with any precision.

Is there a way to define and use such function?

I tried sql_variant type but it complains

Operand data type sql_variant is invalid for divide operator


texts: Equivalents to `ora_rowscn` in other databases?

texts:
Do other databases other than Oracle have ora_rowscn (http://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns007.htm) equivalents, or do you have to implement them yourself in your fields, code and stored procedures?

ora_rowscn is a pseudo-column that is attached to all tables in Oracle created using some specific settings, which is used when doing an optimistic offline-lock.


texts: postgreSQL how can I make this work with an aggregation alias?

texts:
postgreSQL 9.5.6

How can I make this work using the percent_error alias?

SELECT time::date,
  sum((status='404 NOT FOUND')::int) as not_found,
  sum((status='200 OK' OR status='404 NOT FOUND')::int) as total,
  sum((status='404 NOT FOUND')::int)/sum((status='200 OK' OR status='404 NOT FOUND')::int)::float as percent_error
  from log
  group by time::date
  having sum((status='404 NOT FOUND')::int)/sum((status='200 OK' OR status='404 NOT FOUND')::int)::float > 0.01;

A query like this work:

select species,
  count(*) as num
  from animals
  group by species
  having num=1;

But not this - exactly the same as the first example above except here the last line is using percent_error alias:

SELECT time::date,
  sum((status='404 NOT FOUND')::int) as not_found,
  sum((status='200 OK' OR status='404 NOT FOUND')::int) as total,
  sum((status='404 NOT FOUND')::int)/sum((status='200 OK' OR status='404 NOT FOUND')::int)::float as percent_error
  from log
  group by time::date
  having percent_error > 0.01;


texts:
I want to create a de-dupe (remove duplicates) records (actually Guest Profiles) from a table (TableA).
The user can opt for 5 criteria (Surname, Name, Email, PostCode,Phone).
This means it can create combinations or use all or leave some out etc

I am trying to create a Dynamic SQL (with sp_executesql to avoid injection) but the problem is that I do not know how many criteria will the user use in order for me to build a GROUP BY clause.

Name    Initial     Email   PostCode    Phone
Guest1  George      g@u.com 33333       777777
Guest1  George      l@i.com 22222       888888
Guest1  George      g@u.com 11111       222222
Guest2  George      g@u.com 22222       000000


texts: MySQL - Duplicate Entry when trying to install a Plugin

texts:
I'm trying to install a Plugin on my MySQL database, but each time I try I get a Duplicate Entry error:

INSTALL PLUGIN audit_log SONAME 'audit_log.so';
Error Code: 1062. Duplicate entry 'audit_log' for key 'PRIMARY'

But If I run:

SHOW PLUGINS;
SELECT * FROM `information_schema`.`plugins`;

There is no sign of it.

If I run SELECT * FROM mysql.plugin; then there is an entry for it.

But If I run UNINSTALL PLUGIN audit_log; then it tells me it doesn't exist. Error Code: 1305. PLUGIN audit_log does not exist

I know the file is there, because there are two databases on this server running under mysqld_multi and it installed fine under the other one.

The server is 5.5.54, the server is Debian 8 (jessie).

Is there anything else I can do? Can I simply delete it from the mysql.plugin table without causing problems? Restarting the server isn't feasible as it is my master database.


texts: In PostgreSQL, is there a type-safe first() aggregate function?

texts:
Full question re-write

I'm looking for a First() aggregate function.

Here I found something that almost works:

CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$
        SELECT $1;
$$;

-- And then wrap an aggregate around it
CREATE AGGREGATE public.first (
        sfunc    = public.first_agg,
        basetype = anyelement,
        stype    = anyelement
);

The problem is that when a varchar(n) column passes through the first() function, it's converted into simple varchar (without a size). Trying to return the query in a function as RETURNS SETOF anyelement, I get the following error:

  ERROR: structure of query does not match function result type Estado
  de SQL:42804 Detalhe:Returned type character varying does not match
  expected type character varying(40) in column 2. Contexto:PL/pgSQL
  function vsr_table_at_time(anyelement,timestamp without time zone)
  line 31 at RETURN QUERY

In the same wiki page there is a link to a C Version of the function that would replace the above. I don't know how to install it, but I wonder if this version could solve my problem.

Meanwhile, is there a way I can change the above function so it returns the exact same type of the input column?


texts: mongodb read data from secondary hidden replicaset

texts:
We have a mongo cluster (3 member replica set) with two replicaset names (rs0 and rs1). We also enabled balancing and our data is splitted in both replicaset. In both replica set we have add a new replica member as hidden. Please find the hidden response below,

{
    "_id" : 3,
    "host" : "server1-replica-3:27018",
    "arbiterOnly" : false,
    "buildIndexes" : true,
    "hidden" : true,
    "priority" : 0,
    "tags" : {
            "dc" : "east",
            "use" : "reporting"
    },
    "slaveDelay" : NumberLong(0),
    "votes" : 1
}

For a reporting purpose we need to connect both hidden servers (since our data is splitted into both replica sets) via router, so that live data cannot affect with the activities we done for repoting. For this we have tried the below commands to connect the hidden servers, but no luck.

Commands we tried:

With multiple replicaset hidden members:

/usr/local/mongo/bin/mongo mongodb://server1-replica-3:27018,server2-replica-3:27018/databasename?replicaSet=rs0,rs1&w=0&readPreference=secondary&maxPoolSize=50

With read preference secondary

/usr/local/mongo/bin/mongo mongodb://localhost:27017/?readPreference=secondary

Enabled tagging for both hidden servers and tried the below commands:

/usr/local/mongo/bin/mongo mongodb://localhost:27017/?readPreference=secondary&readPreferenceTags=dc:east,use:reporting
/usr/local/mongo/bin/mongo mongodb://server1-replica-3:27018,server2-replica-3:27018/databasename?replicaSet=rs0,rs1readPreference=secondary&readPreferenceTags=dc:east,use:reporting

Can anyone please help us with the steps to connect hidden replicaset members from both replicaset?


texts: Are there limitations on size or record count in MySQL?

texts:
Are there any limitations to MySQL such as size or record limits?

The biggest tables in the database I am working on have over 150,000 rows.  Will MySQL be able to handle that?


texts:
I've got this fairly simple reporting procedure that searches through inventory transaction data (stuff being received, moving around the warehouse, etc). The whole thing is listed below - I've only anonymized the procedure name. It's pulling data from Dynamics GP (explaining the awful table/field names), and an internal data warehouse.

CREATE PROCEDURE InvTrans
    @strItem varchar(20) = NULL,
    @strSN varchar(20) = NULL,
    @strStartDate date,
    @strEndDate date
WITH RECOMPILE
AS

SET @strItem = ISNULL(@strItem, '') + '%'
SET @strSN = ISNULL(@strSN, '') + '%'

SELECT TOP 10000
    d.unitcost AS cost,
    d.unitprice AS price,
    d.qty AS quantity,
    RTRIM(d.itemnmbr) as item,
    RTRIM(iv.ITEMDESC) AS itemdesc,
    d.txdate,
    RTRIM(d.serltnum) AS serltnum,
    d.doctype,
    RTRIM(d.docnumbr) AS docnumbr,
    RTRIM(d.refnumbr) AS refnumbr,
    d.glpostdt,
    RTRIM(d.username) AS username,
    d.lnseqnbr,
    RTRIM(d.from_place) AS from_place,
    RTRIM(d.to_place) AS to_place
FROM dw..dw_invtrans d
    INNER JOIN IV00101 iv
        ON d.itemnmbr = iv.ITEMNMBR
WHERE d.itemnmbr LIKE @strItem
    AND d.serltnum LIKE @strSN
    AND d.txdate BETWEEN CAST(@strStartDate AS datetime) AND CAST(@strEndDate AS datetime)
ORDER BY d.itemnmbr, d.txdate

There's obviously parameter sniffing going on, that much I can tell. The dw_invtrans table has 4,711,806 rows. There are non-clustered indexes on (itemnmbr, txdate) and (serltnum, txdate).

If I execute the procedure like this, it takes 17-20 seconds:

EXEC InvTrans @strItem = '', @strSN = '23-A', @strStartDate = '1/1/1999', @strEndDate = '2/21/2012'

If I execute it like this, it take less than 1 second, and returns the same results:

EXEC InvTrans @strItem = NULL, @strSN = '23-A', @strStartDate = '1/1/1999', @strEndDate = '2/21/2012'

As far as I can tell, when SQL Server sees one of the two item or serial number parameters is null, it generates an execution plan that ignores that filter completely (since the query uses it for a LIKE, it's assuming it won't match any rows, but the procedure turns it into '%'). If either of those parameters is an empty string, it considers that filter (which will end up matching every row, since it turns into '%'), thus hash-matching the small number of rows with a matching serial number to a totally unfiltered dw_invtrans (4.7 million rows).

What would be the best trick to have the execution plan ignore either of the two filters if it's null or a blank string? Bear in mind that I'm going to bind this to Reporting Services - normally I'd just have it dynamically select which query to run inside the procedure, but that often screws up Reporting Services' output column enumeration since it runs the procedure with FMTONLY on.


texts: Faster alternative to scalar UDF with recursion to walk tree hierarchy?

texts:
I have a scalar function that traverses a simple parent-child hierarchy of customers to find the ancestor that's in charge of billing. Here's a simplified version of the schema.

CREATE TABLE Customer (CustomerID int, ParentCustomerID int, IsBillToCustomer bit)

The UDF walks up the chain until it hits the top parent, recording the IsBillToCustomer setting for each customer in the chain, then returns the CustomerID for the highest customer in the chain with IsBillToCustomer = 1.  The UDF is fast for each customer, but I need to run reports that return thousands of customers with data associated to their billing master and it takes tens of minutes for so many customers.  Is my only alternative to implement SQL Server's hierarchyID data type?  Will that even help?

Creating a SQL job that stores the data I need is not a viable solution because the data needs to be as up-to-date as possible and I can't be running a 40-minute CPU-intensive job over and over.

UPDATE:  I created a temporary table using HierarchyID and set up some indexes, then used IsDescendantOf in my query, and it still took over 13 minutes (and hammered my CPU's).


texts:
Hello I'm new to converting SQL to XML. I have been trying to get this to work for the past few days.

SELECT Item.foldername AS "foldername",
Item.status,
Item.vhrid,
Item.firstname,
Item.middleinitial,
Item.lastname,
dbo.getEnumDescript(Type, 'Type') AS title,
Item.email,
dbo.getEnumDescript(OfficeLocation, 'OfficeLocation') AS Office,
       (
    select  id.id 'practices/practice/ID',
           id.name'practices/practice/Name'
    from [dbo].[Groups] as aprac
    INNER JOIN [dbo].[PracticeGroups] as practices
    on aprac.PracticeGroupID = practices.ID
    where Item.vhrid = aprac.vhrid
    FOR XML path(''), type
)
FROM [dbo].[Att] as Item
FOR XML path, ROOT ('Item');

It returns this

<Item>
<row>
<foldername>foldername</foldername>
<status>1</status>
<vhrid>3</vhrid>
<firstname>firstname</firstname>
<lastname>lastname</lastname>
<title>title</title>
<email>test@test.com</email>
<Office>office</Office>
<practices>
  <practice>
    <ID>1</ID>
    <Name>test1</Name>
  </practice>
</practices>
<practices>
  <practice>
    <ID>2</ID>
    <Name>test2</Name>
  </practice>
</practices>
<practices>
  <practice>
    <ID>3</ID>
    <Name>test3</Name>
  </practice>
</practices>
<practices>
  <practice>
    <ID>4</ID>
    <Name>test4</Name>
  </practice>
</practices>
</row>

I'm trying to get it in this format.

<Item>
<row>
<foldername>foldername</foldername>
<status>1</status>
<vhrid>3</vhrid>
<firstname>firstname</firstname>
<lastname>lastname</lastname>
<title>title</title>
<email>test@test.com</email>
<Office>office</Office>
<practices>
  <practice>
    <ID>1</ID>
    <Name>test1</Name>
    <ID>2</ID>
    <Name>test2</Name>
    <ID>3</ID>
    <Name>test3</Name>
    <ID>4</ID>
    <Name>test4</Name>
  </practice>
</practices>
</row>

Any help would be thank full.


texts:
Is it possible to restore our database snapshot on ec2 directly on RDS as our database is very big ?

If not can we restore using csv files. ?


texts: Get records which doesn't have duplicate records

texts:
I have a table with polcies and class codes, I want to get all policies which doesn't have duplicate class codes. Do you have any idea? One policy may have one or more class codes. If any class code is present for more than one time then I want avoid that policy in the result set.

I need to avoid 23442242 from the result set.


texts: SQL Server 2014 (12.0.4439.1) to SP2 (12.5000) service start failure

texts:
In what seems to be eerily familiar to the oh-so-famous SSISDB SP1 fiasco, I've hit a snag while upgrading our DEV server to SQL 2014 SP2.

The base version was SP1+CU5 (12.0.4439.1).  We have SSISDB installed and it's in an AG.  I ran the upgrade package on the secondary and received an error that SQL Agent was unable to start.

I went and checked the services via config manager and saw that none of the services came back up.  Here's what the log file has given me:

2016-07-25 15:02:44.76 spid5s      Database 'master' is upgrading script 'SSIS_hotfix_install.sql' from level 201331031 to level 201331592.
2016-07-25 15:02:45.89 spid5s      Error: 945, Severity: 14, State: 2.
2016-07-25 15:02:45.89 spid5s      Database 'SSISDB' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.
2016-07-25 15:02:45.95 spid5s      Error: 912, Severity: 21, State: 2.
2016-07-25 15:02:45.95 spid5s      Script level upgrade for database 'master' failed because upgrade step 'SSIS_hotfix_install.sql' encountered error 945, state 2, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
2016-07-25 15:02:45.95 spid5s      Error: 3417, Severity: 21, State: 3.
2016-07-25 15:02:45.95 spid5s      Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.
2016-07-25 15:02:45.95 spid5s      SQL Server shutdown has been initiated
2016-07-25 15:02:45.95 spid5s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

Anyone else experience this?  How do I go about bringing this thing back up?  Do I actually need to restore the master DB?

If there's any additional information you need, just let me know.  This is a non-production secondary that this happened to so we're not dealing with downtime or breaking any SLAs.  If this is the same issue we ran into with SP1, I'd definitely like to get the word out.


texts: Queries hang when accessing Oracle via ODBC and Python

texts:
I have Python scripts running on a Windows 2003 server that access an Oracle 11g database in the US via VPN. I am in the UK. We have recently moved offices and upgraded our broadband speed. Some of these scripts hang when accessing Oracle views now, whereas they didn't before. I have isolated two queries, one of which hangs, and one which doesn't, and the only difference between the two is that the one that hangs pulls out one more column from the database. I don't know the underlying structure of the view, but from what I know of the database the column that "causes" the hang is from a different table to the ones that come out OK, and so the underlying query is doing a join when it hangs.

I'm guessing the problem is related to timeouts somewhere along the way. I've tried changing the ODBC settings for the Oracle driver, e.g. unticking the "query timeout" option, but nothing changes.

How can I prevent these queries hanging?

Another bit of info: when I run the same two queries on a Linux box based in the US, using the isql utility they both complete in the same time - 43 seconds.

The Python ODBC library I'm using is the one from Egenix.

Update: using Wireshark I've been following the packets that are exchanged. When I use the query that causes a hang I get a response from the server, and the first row of data within half a second, and then nothing ever again. When I use the query that doesn't hang it takes 40 seconds to get the first row, and then returns all the others.


texts: Fast DELETE with double subquery on big table for dupe remover

texts:
I have a table with 9million rows.
At some point in time i changed unique key from one column to two columns(for partitioning).

After this i created duplicates of my first column which is unwanted, so i try to delete all records that are duplicates

I asked about it (Find a longest sequence that doesn't have duplicates inside) and modified the code i received to make it MySql compliable.
Here's the code:

DELETE FROM t USING `mytable` AS t WHERE EXISTS
(SELECT *
    FROM (SELECT * FROM mytable) AS t2
    WHERE t2.Text = t.Text
    AND t2.Id < t.Id);

The Table looks like this:

+---+--------+----+----+
| Id| Text   |Col1|Col2|
+---+--------+----+----+
| 1 | adf    |1   |0   |
| 2 | qwer   |5   |1   |
+---+--------+----+----+

Problem is that the query takes over 2 hours now without sign of progress.

How i can optimize this query?

I'm running on 8 core/16GB Windows 10 machine.


texts:
I am building a compound index, and I'm not 100% sure which way to include a bit field that is part of the table. The index will be for something similar to a list of forum topics, where I want the single index to be by ForumId (int), IsSticky (bit), then DatePosted (desc) (datetime2(0))

But I want to make sure I handle the IsSticky bit field properly; Do I index it as if true is 1 or -1? If it's 1, I would index IsSticky descending; if -1, I would index it ascending.

Also, I'm asking for SQL 2008, but I'd be interested in knowing if it's different in 2005.


texts: Oracle 11gR2, duplicating and refreshing cloned database

texts: Assume I've cloned PROD database to TEST with RMAN "duplicate target database to CLONE from active database;". After several days I need to refresh CLONE. Will RMAN transfer all the datafiles from PROD to CLONE or only datafiles with changed data if I will issue "duplicate from active database again" ?


texts: Is it OK to duplicate parent field in child table for performance?

texts:
I have a very complicated query which involve more than 10 tables. Some parent tables are loaded only to search on one field. I wonder if I can copy this field in the child table (this data is never updated), so that I don't need to load the parent table in my query.

I've always learned that I should not store the same data in two different place, but maybe it's "allowed" for performance ?

For example, imagine a country table, a city table, and a street table. Having the country code in the street table would speed up some complicated queries.

Thanks


texts: Alter failed for schema 'dbo' when trying to change schema owner

texts:
I have some MS Access code that uses ODBCDirect to execute some sql statements in a SQL 2008 R2 DB.

The sql statements do not specify the schema name so subsequently are executed using the windows user as the schema name and not the result im looking for. The schema should be dbo.

All users belong to a Windows group which is a SQL login.

I read that if I change the owner of the schema dbo to the windows group that sql statemenets that do not specify a schema will default dbo.

So I tried to change owner of the schema to the windows group but get message

  Alter failed for schema 'dbo'

I do seem to change alter permission on schemas in the DB as well.

Does sql allow you to change the owner if so how???

Malcolm


texts:
Is there a way to restrict a user from viewing the table structure? I just want them to have a SELECT, INSERT, UPDATE, DELETE access only on that table.  I want to restrict a specific programmer on the following:
    1.  He is not allowed to see any table definition
    2.  He is not allowed to create Stored Procedure
    3.  Since there were already Stored Procedures (SPs) for saving, updating, selecting and deleting records, that programmer will just use this SPs in accessing/manipulating records.
    Overall, for security reason, we don't want that programmer to have any of design of the database.


texts:
When attempting to log on to a SQL Server that lives on a different domain, I get the following error:

  The target principal name is incorrect.  Cannot generate SSPI context.

I am using Windows Credential Manager to enable Windows Authentication across domains.


texts:
I am getting this error while restoring the backup.

  Too many backup devices specified for backup or restore; only 64 are
  allowed. RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL
  Server, Error: 3205)

I have .bak file, I am using SQL Server 2008 R2.

Thanks.


texts: Non-clustered Index Update not displayed in execution plan

texts:
Possible Duplicate:
  Nonclustered Index Insert

I'm trying to work out the cost of having a nonclustered index on a particular table, but I don't see any maintenance cost in the execution plan for an insert.

The table looks like this:

    CREATE TABLE [dbo].[ASPStateTempSessions](
    [SessionId] [nvarchar](88) NOT NULL,
    [Created] [datetime] NOT NULL,
    [Expires] [datetime] NOT NULL,
    [LockDate] [datetime] NOT NULL,
    [LockDateLocal] [datetime] NOT NULL,
    [LockCookie] [int] NOT NULL,
    [Timeout] [int] NOT NULL,
    [Locked] [bit] NOT NULL,
    [SessionItemShort] [varbinary](7000) NULL,
    [SessionItemLong] [image] NULL,
    [Flags] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
    [SessionId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[ASPStateTempSessions] ADD  DEFAULT (getutcdate()) FOR [Created]
GO

ALTER TABLE [dbo].[ASPStateTempSessions] ADD  DEFAULT ((0)) FOR [Flags]
GO

As well as a Clustered index on [SessionID] it has the following non-clustered index

/****** Object:  Index [Index_Expires]    Script Date: 12/14/2012 10:13:58 ******/
CREATE NONCLUSTERED INDEX [Index_Expires] ON [dbo].[ASPStateTempSessions]
(
    [Expires] ASC
)WITH (PAD_INDEX  = OFF, 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]

But when I run an execution plan for the following, which updates the column on which there is a non-clustered index:

UPDATE [ASPState].dbo.ASPStateTempSessions
            SET Expires = DATEADD(n, Timeout, GETUTCDATE())
            WHERE SessionId = '32gghltsuoesnvlzbehchp2m2014c0f1'
            RETURN 0

I don't see any mention of cost in the execution plan:

Is it usual to not see it? How can I calculate the cost of this index in write operations?


texts: How to match regular expressions in Oracle?

texts:
I'm using the following query:

select * from table
WHERE POSTAL_CODE LIKE '[a-Z][0-9][a-Z] [0-9][a-Z][0-9]';

However, this is not capturing anything. For example, a typical POSTAL_CODE looks like 'B3M 3K5'. Why would this not match?


texts: Model multiple 1-m relationship to a single entity

texts:
I have an entity called Box. This box has a from property which could be from a variety of different entities. It could be from a user, office, import, storage and many more.

So far I can think of two ways to model this:

Approach one:

| box_id | box_content | from_user | from_office | from_import | from_storage | ...  |
|--------|-------------|-----------|-------------|-------------|--------------|------|
| box_1  | apple       | user_a    | null        | null        | null         | null |
| box_2  | orange      | null      | null        | import_b    | null         | null |

Approach 2:

BOX
| box_id | box_content |
|--------|-------------|
| box_1  | apple       |
| box_2  | orange      |

USER
| user_id | name  |
|---------|-------|
| user_a  | Rick  |
| user_b  | David |

USER <-> BOX
| user_id | box_id |
|---------|--------|
| user_a  | box_1  |

IMPORT <-> BOX
| import_id | box_id |
|-----------|--------|
| import_a  | box_2  |

But I don't like either ways because in approach 1 there are so many null columns and in approach 2 I need to create a new relationship table every time there is a new from entity. Is there a better way to model this in a relational database?


texts: How to count number of consecutive events matching status of last event?

texts:
In MS-SQL Server 2008, I have a table of events in the following format:

Controller   |  ExecutionTime        |  Result
1            |  2012-09-24 09:00:00  | 0
1            |  2012-09-24 09:01:00  | 0
2            |  2012-09-24 09:02:00  | 1

I need to analyze this table to produce a result that:

Identifies each controller whose latest event was an error (result=1)
Identifies how many consecutive failures that controller has had (# events since the most recent successful event)

I've made some progress toward this task, I can identify the controllers whose most recent event was a failure - see this SQL Fiddle.  But I could use some help finding an approach to counting the # of consecutive failures that has led to this state.

Thanks!
John


texts: SQL Server Migration Plan - Alias Options for Multiple Apps Connecting

texts:
We currently have a number of databases on a single SQL Server 2008R2 instance (server name: prdServer01; Win VM). We want to migrate to a SQL Server 2016 instance (server name: prdServer02; Win VM).
We have many applications that connect to the 2008R2 DB server. So ideally we would like to avoid having to update each connection string in each application.
From what I can tell, our options are

Add an alias to the SQL 2016 server(prdServer02). That alias will be the same name as the SQL 2008R2 server(i.e. prdServer01)
Add a DNS entry to the Windows server (I'm not entirely sure how this works)

The problem I see, is that we would have to take the 2008R2 server offline before the alias will work. And it is also going to be difficult to test.
Is there a better solution for this? Are we best to give the new 2016 server a completely different alias and then update our apps one by one to use this new alias in the connection strings?


texts:
I'm finding acronomes such as 'SSIS', 'SSDT', etc etc a little confusing as to how they related to SQL Server. It seems to me that SQL Server implements the T-SQL language, and that a package such as 'SSIS' is a 3rd party 'wrapper' over the T-SQL language, and effectively a plug-in to SQL Server. Is this correct?

This blog post (here) mentions the benefits of SSIS vs T-SQL (which is why I assume that SSIS is a wrapper of T-SQL). Also, I can see that jobs created as SSIS packages can be found under SQL Server Agent in SSMS.


texts: Are GIST Index point geometry useful to speed up spatial query?

texts:
I know the question is a bit generic.
I have multiple tables with point geometries and a GIST Index eating many gigabits of space.
From what I understrand about Gist Index on polygons or polylines, it will store the bounding box and computes a first calculation on the bounding box instead of the full geometry resulting in faster processing.
But what about point geometry?
Will it to anything more that ordering the points spatially?
Thanks


texts: Total billions of rows and daily millions of row insert: What database system suits best

texts:
We developed a system with very high volume of data but very simplified data structure. We had only cellX, cellY, timeStamp and value columns.

Operations are:

Insert 455+ thousand rows per day
Query using range filters on cellX, cellY and timeStamp
Query is not required to return instantly, we can notify the user when requested data is ready.

Because the data was too large and we required index for query, we used this scheme:

Use SQL Server.
Clustered index on cellX, cellY and timeStamp.
Separate tables for every year, therefore total number of rows in a table remains within limit (~166 million).
Use a custom format for timeStamp. Skip year, keep only month, date and hour. We were able to keep it within 16 bit int.
Use partition on each years table.
Insert one day data at a time. Use partition switching to keep the database live while inserting data.

This has been working good so far. Although we notify the user after data is ready, the delay is never more than a few seconds as long as the query is reasonable.
But recently we got the opportunity to get more precised data and the data volume has been increased by 68 times!. Therefore, now we have:

insert 30+ million rows per day.
store 11 billion rows in a table for a year. This can be reduced by making quarterly (2.7 billion) or monthly (1 billion) tables.

This is possible that we might be able to receive more precised data in a year or two. Therefore it is possible that data volume will increase again by a significant factor.

The question is, will this scheme we are using sustain? Or we should migrate to another scheme, may be another database system leaving SQL Server?

Edit

The three dimension columns cellX, cellY and timeStamp are very regular in nature. You can define all of them by
f(x) = mx + c, for some integer x ranging (0, 1, 2, ..., X).


texts: How to prevent SQL files opening new instances of SSMS 17.9.1?

texts:
Synopsis

This is not a new problem and there are several solutions floating around on Internet.

Problem

When you double click on a .sql file, a new instance of SSMS is opened. I did not have this problem until new SQL Server Management Studio (SSMS 17.9.1) was installed.

What I tried

This page lists several solutions suggesting registry changes and file associations, and reinstalling SSMS. None worked.
This one has almost the same as the previous link. (SO is so much better than those forums.)
Registry changes are shown here with excellent screenshots. I added "%1" but that did not make any difference.
The suggestion mentioned on SO did not work either.
On DBA, it is mostly talking about raised permissions, which is not the case with my situation.

Workarounds

Yes, I know about these workarounds:

Ctrl + O
Drag .sql files to SSMS

I am not looking for workarounds.

My environment

Even with the new SSMS 17.9.1 (build: 14.0.17289.0), I am still pointing to SQL Server 2016 (13.0.5233.0). Previously, I was using SSMS June 2016 (build: 13.0.15000.23) with the same SQL Server (13.0.5233.0).

Because of permission, I am not running any application as administrator.

SQL Server versions are listed here.

SQL Server Management Studio versions are listed here.


texts: What do I need to restore an encrypted MSSQL database?

texts:
I am trying to get my head around MSSQL TDE encryption.

I have followed these steps to encrypt a database:

1) Create a master key on the master database:

USE master
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='thepbpass';
GO

2) Create certificate:

CREATE CERTIFICATE mycert
WITH SUBJECT='mydb DEK';
GO

3) Associate the certificate to the database:

USE mydb
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE mycert;
GO

4) Encrypt the database:

ALTER DATABASE mydb
SET ENCRYPTION ON;
GO

5) Export the certificate.

BACKUP CERTIFICATE mycert TO FILE = 'path_to_file'
GO

My question is:

If my server died and I needed to restore a backup, do I have everything required for the restore?

I have the following:

A certificate stored in a secure location.
Knowledge of the secure
master key password.

Do I need to worry about private keys or public keys?
I have seen discussion about private and public keys online and I am not sure how they are relevant to my configuration or how they are different from the master key which I created in step 1.


texts: How to know the locale that has been used to initialize a Postgres server?

texts: I need it in order to create a new database with initdb with the same locale, otherwise pg_upgrade will fail.


texts: How to handle dimension hierarchies that re-join?

texts: The obvious example is a time dimension where days roll up by day>month>year but also roll up days>weeks>year. BIDS complains about it, but I see no other way to model the situation.


texts: PostgreSQL: simple equality query returning no results for indexed columns

texts:
I have a very large table (around 140M rows) with five columns:

CREATE TABLE IF NOT EXISTS citations
(
   citing_id text,
   citing_issn text,
   cited_id text,
   cited_issn text,
   year int
);

There are hash indexes on citing_id and on cited_id, neither of which has any NULL values (confirmed), but queries selecting on equality in these columns are returning zero results no matter what. Even this query, which I would think would work in any populated table, returns zero rows:

SELECT *
FROM citations
WHERE citing_id IN (SELECT citing_id
                    FROM citations LIMIT 1)
LIMIT 100;

However, the same query on a non-indexed column works exactly as expected:

SELECT *
FROM citations
WHERE citing_issn IN (SELECT citing_issn
                      FROM citations LIMIT 1)
LIMIT 100;

The data was loaded from a CSV file using the COPY command, and I suspect there may be extra hidden characters on the end of the indexed values -- querying with WHERE citing_id LIKE '<id>%' is very slow (hash indexes don't help with partial strings) but returns the right results. Still, the first query above should work no matter what, unless I'm mistaken.

I'm running Postgres 9.5.3.0 on MacOS 10.13.1

edit:

The query plan for the first query (on the indexed column, returning no rows) is:

Limit  (cost=0.03..1.38 rows=100 width=58)
   ->  Nested Loop  (cost=0.03..265.83 rows=19751 width=58)
         ->  HashAggregate  (cost=0.03..0.04 rows=1 width=17)
               Group Key: citations_1.citing_id
               ->  Limit  (cost=0.00..0.02 rows=1 width=17)
                     ->  Seq Scan on citations citations_1  (cost=0.00..3164425.68 rows=148131768 width=17)
         ->  Index Scan using citations_citing_id on citations  (cost=0.00..265.14 rows=65 width=58)
               Index Cond: (citing_id = citations_1.citing_id)

and for the second query (on the non-indexed column, returning expected results):

Limit  (cost=0.04..139.54 rows=100 width=58)
   ->  Hash Semi Join  (cost=0.04..3581836.35 rows=2567617 width=58)
         Hash Cond: (citations.citing_issn = citations_1.citing_issn)
         ->  Seq Scan on citations  (cost=0.00..3164425.68 rows=148131768 width=58)
         ->  Hash  (cost=0.03..0.03 rows=1 width=10)
               ->  Limit  (cost=0.00..0.02 rows=1 width=10)
                     ->  Seq Scan on citations citations_1  (cost=0.00..3164425.68 rows=148131768 width=10)