SQLServer Inconsistency between changing values for columns vs variables Two similar situations outlined below. The first being table columns where we run a series of calculations across columns a, b and c. We do the same thing using declared variables and the result is different to the answer for columns.

It appears that SQL Server persists the value of the table columns for the duration of the command whereas the values of the variables will change during the command.

In a case where you mix variables and table columns the results are still different - but consistent to the descriptions above.

In other RDBMS's that we use there is no difference between these two. One poignant example of where these behave the same is Sybase.

So why is there a difference? Is there something that can alter this behaviour and make it consistent?

/* Column version */

create table #CalcTest (
  a int not null,
  b int not null,
  c int not null
)

insert into #CalcTest ( a, b, c )
values ( 4, 14, 24 )

update #CalcTest
set  b = a + c,
     a = 5,
     c = b - a

select a, b, c from #CalcTest

--output 5 28 10
--Note further - if you break up the update command into 3 commands c = 23

/* Variable version */

declare @a int, @b int, @c int

select @a = 4, @b = 14, @c = 24

select    @b = @a + @c,
          @a = 5,
          @c = @b - @a

select @a, @b, @c

--output 5 28 23
--Note c is 23 where it was previously 10!

It is just how SQL Server works. More specifically, how its UPDATE statement works.

One SELECT statement with variables in your example is equivalent to three separate SET statements.

SET @b = @a + @c;
SET @a = 5;
SET @c = @b - @a;

The calculations here are sequential. The order in which you write these lines is important as in traditional programming language.

UPDATE statement is not sequential in this sense. It reads current values of all columns of a row into memory (internal variables behind the scene) and uses these cached values in calculations. These cached values are not changed. The final result is written back onto disk in one go, not column by column.

I think, the most vivid example would be an example how you can swap values of two columns with this statement:

update #CalcTest
set  b = a,
     a = b

In traditional procedural languages you have to use third variable to store the value:

temp = a;
a = b;
b = temp;

UPDATE in SQL Server has this variable implicitly. I don't know how other DBMSs implement UPDATE, but at least Postgres and Oracle seem to work in the same way as SQL Server. While MySQL not.

EDIT:

Before I mark this as the correct answer I will clarify and say that it's not so much how updates work but that it is just how columns work and, no, we can't change or control it.

My interpretation from this and other research elsewhere is this:

Columns are updated atomically in SqlServer. They get their new values all at the same time at the conclusion of the statement. Hence no partial updates or updates of one and not the other. All or nothing.
Variables (even in the same statement as columns) are not updated atomically. They get their new value part way through the statement one after another and their changes can feed into each other as they go. This is just like most programming languages.
Also, SqlServer will not guarantee the order in which variables are updated in a Select (and I suspect the same is true of Updates). This is probably due to the optimiser and how it schedules the cost of complex instructions like sub-selects. Simple stuff like that presented here has never (as far as I can see) been processed in anything other that the order it's written in. Just don't rely on that order.
Microsoft recommends using the Set command to set variable values where that statement does not refer to tables or views, i.e. like those presented here. However, you can't chain up variables in a Set like you can in a Select. Also, Microsoft say this and then go right ahead and continue using Select to set variables in their examples. :-)


Binary Log Format in MySQL Reference manual for MySQL 5.6 states that " Some changes, however, still use the statement-based format. Examples include all DDL (data definition language) statements such as CREATE TABLE, ALTER TABLE, or DROP TABLE. "

Does this statement means that even if we have ROW format for binary logs all DDLs will be logged in binary log as statement based? How does this affect replication?

Kindly help me to understand this.

Even though binlog_format is a global variable, it doesn't exactly set the "format" of the binary log itself -- it sets the format in which DML events are logged for execution by the slave.

In STATEMENT mode, the actual DML queries that change data on the master are written to the log as they were received by the master, and subsequently executed on the slave.

In ROW mode, DML queries are not logged as SQL; instead, "images" of the actual changed rows are written to the log, which the slave applies directly to its data set.  When rows are deleted, the data contained in the deleted row is written to the log; when rows are inserted, the inserted rows are logged, and when rows are updated, the data from the old and new version of the row is logged.

Row-based logging has a number of advantages over statement-based logging, perhaps the most significant being the fact that it's absolutely deterministic, since the actual data from the actual rows is sent from machine to machine.  There are interactions with no user-defined variables, no non-deterministic functions (e.g. UUID()), no issues with rows being accessed in any particular order (e.g. DELETE ... LIMIT) ... but since row-based logging only deals with the data in the individual rows, it's only applicable to DML.

DDL is still logged an SQL query -- a statement-based log event -- because that's how DDL is done -- you alter a table with ALTER TABLE... the row based log events are "another way" of changing row data, but there would be no reason for MySQL to implement "another way" of handling ALTER TABLE (or similar events).  The row-based logging format is only interested in row images, not table objects.

DDL has none of the same problems with determinism that DML has, which is the primary problem that row-based replication solves -- there's more than one way to DELETE FROM t1 LIMIT 1 (which '1' row would get deleted is generally predictable but technically undefined) but there's already exactly and only one way to ALTER TABLE t1 DROP COLUMN c1.

How does this affect replication?  It doesn't have any negative impact on replication, or any particular impact at all, since that's how row-based replication has always worked since it was introduced in MySQL.

It's something to keep in mind if you are filtering replication because of the potential impact on how filtering rules are interpreted... but if you are thinking about filtering replication (not replicating all of your tables), my advice is that it's usually best to just take those thoughts... and let them go.  It's hard to think of any really good cases where filtering replication is worth the trouble when the default "replicate everything" is so much more straightforward and less subject to nuance, and if you have data that for some reason shouldn't be on the slave, there's always the BLACKHOLE storage engine.


A way to process a CTE once? I have a view that has serious performance issues, because I am linking it to a CTE that uses another view, with the same table references.
WITH
  splits(ID, [NEW TEU])
  AS
    (SELECT s.[DTL ID] AS ID, s.[NEW TEU]
     FROM   [POTS].[vwSPLITCARGO] AS s)

SELECT *
FROM   [MISNT].[CARGO_MANIFEST_DETAIL_VIEW] AS cmd
       LEFT JOIN splits AS s ON s.ID = cmd.ID

The vwSPLITCARGO view, references the same CARGO_MANIFEST_DETAIL_VIEW table to get it's calculations.
I know why it's slow. It's because the vwSPLITCARGO view is reading all the data from the same CARGO_MANIFEST_DETAIL_VIEW view for every record already in the CTE. In fact, the vwSPLITCARGO is always returning the same small set of records (approx 4 or 6 records). I don't need it to get those records all the time. Once would be great!
Is there a way to make the CTE behave like a temporary table, in that it retrieves the records once, and not be processed over and over?
Something to tell the CTE: CASE WHEN RunOnce = 0 THEN "do it" ELSE "don't do it" END

There is no query hint to spool or materialize a CTE.  So use a temp table, and optionally index the temp table.
SELECT s.[DTL ID] AS ID, s.[NEW TEU]
into #splits
FROM   [POTS].[vwSPLITCARGO] AS s;

WITH
  splits(ID, [NEW TEU])
  AS
    (select * from #splits)

SELECT *
FROM   [MISNT].[CARGO_MANIFEST_DETAIL_VIEW] AS cmd
       LEFT JOIN splits AS s ON s.ID = cmd.ID


How to dump whole database from remote server on sequel pro I have a database connection to a remote host on SequelPro Mac OSX. How can i dump whole database. I need a dump like what mysqldump command does:

$ mysqldump --opt -u [uname] -p[pass] [dbname] > [backupfile.sql]

I found the table dump option, but i need full database dump.

You can dump database with SequelPro (Mac OSX) with File > Export (⇧⌘E) option.


How to convert a MySQL database to PostgreSQL? Is there any command line tool for converting the MySQL database to PostgreSQL? I'd also like to know if there is a way to convert the database using the normal mysqldump command.

have you tried:

mysqldump --compatible=postgresql dbname > export.sql

then do this to escape quotes

sed "s/\\\'/\'\'/g" export.sql > export1.sql

may need some more massaging so look at mysqldump doco more closely.


What does Threads_connected=-1 signify in MySQL? I was running the following: mysqladmin -u root -p ext -ri 10 | grep Threads_connected and was kind of surprised to find that Threads_connected could be -1:

| Threads_connected                 | 1            |
| Threads_connected                 | -1           |
| Threads_connected                 | 1            |
| Threads_connected                 | -1           |

What does the -1 signify?

This is on MySQL 5.0.77 on x86_64.

Thanks!

Perhaps you are seeing diff since last snapshot


Why is `max_allowed_packet` shows 2048 even though it's set as 16M in mysqld.cnf I've got the error related to max_allowed_packet:

  [S1000] Packet for query is too large (2,127 > 2,048). You can change
  this value on the server by setting the ‘max_allowed_packet’ variable.

So the limit is 2048. I've checked /etc/mysql/mysql.conf.d/mysqld.cnf and the value there is 16Mb:

max_allowed_packet = 16M

I've ran the query:

SHOW VARIABLES LIKE 'max_allowed_packet';

Which gave me 2,048. So the values are different. Is my assumption correct that the value in mysqld.cnf specifies limit for server while the query gave me the limit for client side? If so, where do I configure the value for the client side?

By running this query:

SET GLOBAL max_allowed_packet=16777216;

I only set the variable for the current session and as I understand it'll be reset after I restart the database. What's also interesting that:

SHOW VARIABLES LIKE 'max_allowed_packet';

still gives me 2,048m but:

SHOW GLOBAL VARIABLES LIKE 'max_allowed_packet';

gives me 16777216.

I need to set it in the client configuration file. But is there such a thing? I've done some reading but it's still vague.

Settings in my.cnf (etc) are seen only when mysqld starts up.

Settings changed by SET GLOBAL... are not seen until someone logs in.  But, such settings go away when mysqld is restarted.  (This item probably explains what you encountered.)

Settings changed for the "session" last only for that one connection, and only until disconnecting.

(MySQL 8.0 has some more situations, due to being about the "persist" changes across restarts.)

(Not all settings have a GLOBAL/SESSION split as implied above.)


Dynamically change a db in transact sql I am trying to run a script against a database that is dropped from a third party daily so the name of the database changes daily.  I can get the name of the new database using the sys.databases folder but I am trying to populate it into an SQL statement.  I am hoping to get this to run and move it into SSRS.  Thank you in advance.

You will want to use dynamic SQL to change the DB you are executing the query in:

DECLARE @dbname AS VARCHAR(100)
DECLARE @query AS VARCHAR(MAX)

SELECT @dbname = name
FROM sys.databases d
WHERE /*enter criteria here to get DB*/

SET @query = '
use ' + @dbname + '
/*enter your query here*/
'

PRINT @query
EXEC(@query)


SQL Server database backup on Remote site We have 4 different sites and each site has a SQL Server.

Our requirement is to have a SQL Server backup or database (Mdf files) of site 1, 2, 3 in site 4.

In case of disaster on any of the site  we need most recent database of the site.

each of the site is connected in network with very limited bandwidth.
As the database size is more it is not possible to take complete backup every time.

For the above scenario can any one suggest me a better solution?

You can use Deltacopy http://www.aboutmyip.com/AboutMyXApp/DeltaCopy.jsp it's a rsync solution for Windows, it can be used to sync files or folder between servers.


How to display all the dates in the date-range having group by dates? Suppose I have a table called “products”:

Productive product-name   product-quantity  created-date
111            ABC          10               1/10/2018
222            XYZ          20               1/10/2018
333            PQR          30               1/11/2018
444            MNC          40               1/15/2018
555            DEF          50               1/11/2018

If I run this query:

Select convert(date,createddate,101), count(product-quantity) from products
Where created-date > 1/9/2018 and created-date < 1/16/1018
group by convert(date,createddate,101)

Which will give me output such as

created-date.  product-quantity
1/10/2018            30
1/11/2018            80
1/15/2018            40

I want to achieve:

created-date.  product-quantity
1/10/2018            30
1/11/2018            80
1/12/2018            00
1/13/2018            00
1/14/2018            00
1/15/2018            40

How can I do it?

A basic implementation of a Calendar table.

CREATE TABLE Calendar(cDate datetime, cDay int, cDayOfWeek int, cDayName varchar(20));

DECLARE @date date = '20180101';
WHILE @date <= '20180131'
BEGIN
    INSERT INTO Calendar VALUES (@date, DAY(@date), DATEPART(weekday, @date), DATENAME(weekday, @date));
    SET @date = DATEADD(day, 1, @date);
END
GO

31 rows affected

CREATE TABLE products(Productive int, Name char(3), Qty int, Created date);
INSERT INTO products VALUES
(111, 'ABC', 10, '1/10/2018'),
(222, 'XYZ', 20, '1/10/2018'),
(333, 'PQR', 30, '1/11/2018'),
(444, 'MNC', 40, '1/15/2018'),
(555, 'DEF', 50, '1/11/2018');
GO

5 rows affected

Notice I've used a LEFT JOIN to get all records from calendar table and only those records that match on products table.

SELECT     c.cDate AS Created, COALESCE(Qty, 0) AS Qty
FROM       Calendar c
LEFT JOIN  (SELECT   CONVERT(date, Created, 101) AS [Created],
                     COUNT(Qty) AS [Qty]
            FROM     products
            GROUP BY CONVERT(date, Created, 101)) p
ON         p.Created = c.cDate
WHERE      c.cDate >= (SELECT MIN(Created) FROM products)
AND        c.cDate <= (SELECT MAX(Created) FROM products);
GO

Created             | Qty
:------------------ | --:
10/01/2018 00:00:00 |   2
11/01/2018 00:00:00 |   2
12/01/2018 00:00:00 |   0
13/01/2018 00:00:00 |   0
14/01/2018 00:00:00 |   0
15/01/2018 00:00:00 |   1

dbfiddle here


SQL - Update certain database records with a loop I have a certain IDs in a table that I would like to change a column value from false to true. How can I do a loop that goes through the specific IDs given and changes a column value from false to true?

I’m not very familiar with SQL and the only option that came to mind is through a loop.

This is a sample of my data.

Basically we have a stored procedure to change the “description” of the BetGenerationBetID given from FAILED to SUCCESSFUL example, the below will update the description of BetGenerationBetID 265809596 to SUCCESSFUL

With the above I can only update a record each time. So I want something that I can give all BetGenerationBetIDs that I want to update and changes the description of each ID at once.

If you have to do it inside of a procedure with a set amount of BetGenerationBetID's, then you could use user defined table types.

Step 1: Create Type and test table

CREATE TYPE dbo.BetGenerationBetIDS AS TABLE
    ( BetGenerationBetID int);

CREATE TABLE dbo.TableBetGeneration(BetGenerationBetID int, [description] varchar(20));
INSERT INTO dbo.TableBetGeneration(BetGenerationBetId,[description])
VALUES (1,'Failed'),(2,'Failed'),(3,'Failed');

Step 2: Create the Procedure that uses the table type

CREATE PROCEDURE dbo.uspOverrideBetAsSuccesful
@BetGenerationBetIDTable dbo.BetGenerationBetIDS READONLY
AS
    UPDATE TBG
    SET TBG.[description]   = 'SUCCEEDED'
    FROM  dbo.TableBetGeneration TBG
    INNER JOIN @BetGenerationBetIDTable AS BGID
    ON TBG.BetGenerationBetID  = BGID.BetGenerationBetID

GO

Step 3: Enter your values and run the procedure

DECLARE @BetGenerationTable as dbo.BetGenerationBetIDs
INSERT INTO @BetGenerationTable(BetGenerationBetID)
VALUES(1),(2)

EXEC dbo.uspOverrideBetAsSuccesful @BetGenerationTable

Validate

SELECT * FROM  dbo.TableBetGeneration

Result

BetGenerationBetID  description
1                   SUCCEEDED
2                   SUCCEEDED
3                   Failed


To what extent can we monitor jobs/suspended queries as DBO Short Background:

I work for company A, at company A we have a DBA that should not be. When we are tasked with items for troubleshooting which require quick turn around, it generally takes quite a bit longer than it should.

For this reason, we asked him to grant me DBO access to certain servers (Biztalk SQL servers). I'm the one who is dropping files and monitoring this stuff, sometimes overnight, and lately we have been experiencing system slow down.

So with these issues with Biztalk, I've gone into the SQL server and tried to dig around but it seems most of them require higher priveleges then I have. I've looked for suspended queries, killed them, but as DBO, I was wondering what's the extent I can monitor and find out about the jobs currently running/suspended.

I am familiar with exec sp_who2 and kill %% but other than that I've been hitting a wall...

My question:

Are there any useful features I can utilize as DBO that will assist in identifying the cause of these suspended queries? Other than who and who2?

Any homegrown queries you folks have wrote for identifying such causes of these errors?

Any help is much appreciated!

Though logging into the machine and analyzing the wait chain on the Resource Monitor could give some extra info (for instance if sqlservr.exe thread is blocked waiting for lsass.exe it could mean it is blocked at a authentication validation), what you'd really want is to be granted the VIEW SERVER STATE permission so you can view the DMVs that might help you, like this ones, for instance:

SELECT sqltext.TEXT,
 req.session_id,
 req.status,
 req.command,
 req.cpu_time,
 req.total_elapsed_time
 FROM sys.dm_exec_requests req
 CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
order by cpu_time desc


Things to keep in mind while editing Mysql "my.cnf" file Today I tried to boost mysql's(installed in my system Ubuntu 14.04) performance as I have many sites(PHP/Magento) installed in my system which relies on mysql as database server, but when I edited the lines like "query_cache_size", "query_cache_type" and other lines(only lines with numeric values which I increased), but instead of speeding it up, it decreased mysql's performance and also it timed-out when I tried to open multiple different pages in browser tabs.

So I need to know what to keep in mind when I edit the "my.cnf" the next time so it doesn't decrease performance but boost it.

What is the relation between those values like "query_cache_size", "max_connections" etc.? Are they inter-dependent ?

There are a lot of inter-dependencies.  Casual use of the Query cache can speed up some queries.  Busy systems tend to be slowed down by the QC.

Overcommitting RAM is deadly for performance.  Here is a discussion of that, and some of the settings.

32bit system with 8GB of RAM ! -- Is that a 32-bit OS?  Or a 32-bit MySQL on a 64-bit OS?  If the former, then 4GB is totally idle.  In either case, MySQL is constrained as if it has only 4GB.

What kinds of things slowed down when you changed what settings?  We can probably explain what happened if you give us some details.


MySQL - can't see end of I am trying to pick the MySQL charset combination which is correct.
Unfortunately, at least on my screen, the list of options is very narrow and doesn't show the full text of the options.
I want to pick option utf8mb4_unicode_ci, but I literally can't see to the end of the option to know whether I'm picking the right option.
I guess I could maybe work it out by looking at a list of all possible options and then sorting them alphabetically, but this seems a very complicated way of doing it.

it at the bottom of alphabetically sorted list.
But as you can't see the list very well, i posted an image.
So you see it is direct about utf8mb4_vi
I find your size of the window quite small.


Building a restaurant database I am designing an Italian restaurant database, but I face some problems considering the database design, which lead me to a halt. The part that I find difficult developing is the dishes table. A main dish could be a pizza, or some kind of pasta, or other. So a pizza could have many pieces and a pasta dish could be requested with a different kind of pasta as a customer wishes. On top of that you can add extra ingredients or remove some in every dish, which ingredients come to a cost and a quantity in the database which should be updated per order completion.

So should make an ISA relationship and separate my dishes to a pizza, pasta category, from there I could have the main pasta used in the dish and custom size for the pizza. In this scenario when placing an order the kind of pasta should be specified if different as well as the pieces in the pizza. Afterwards the ingredients and price would be multiplied by a constant factor proportional to the number of pieces, in that way avoiding storing all the possible combinations in the database.

Would this be a good design, what else could it be done to avoid data replication and have a readable order table? Any help would be much appreciated.

If the restaurant is that complicated I would use a simple solution. I would have an ingredients table and a entrée table to reference those ingredients. For example is you have a choice of pasta, sauce, cheese, and a meat on the side. I would have a table to store that information. I would then have a desserts and drink table that is separate. You can store your cost in the ingredients table and then do a calculation. The order table would then reference all of the previous information. This is just my thought process, but it may work for you.


Inner join using an array column Having trouble indexing and executing a query in O (log n) time.

The query includes an INNER JOIN, an ORDER BY, and an equality operation. If I understand the laws of databases correctly, a query can be indexed and executed in O (log n) time (or thereabouts) if a non-equality operator is not used on more than one column. In this case, I believe the INNER JOIN does count as an equality operator and the non-equality operator would be the ORDER BY portion of the query. This table has upwards of 10,000,000 rows and needs to handle several reads and writes per second.

Using PostgreSQL. This is what the table looks like. As you can see, the column 'Names' is a list property and it is the column that the INNER JOIN goes against:

Age Names                       Date
34  ['carla', 'john', 'sam']    3/13/2011
26  ['json', 'cindy', 'joel']   3/13/2011
72  ['beth', 'amber', 'susie']  3/13/2011
14  ['john', 'jim', 'debie']    3/13/2011

This is the query that we are trying to do:

SELECT * FROM the_table WHERE Age==26 AND Names=='john' ORDER BY Date

My background is from using App Engine's Big Table, so I've used equality operators here to indicate that 'john' should be one of the names in the Names column. This would be an acceptable query in GAE's big table, it would execute in O (log N) time as all Big Table queries are reqyured to do. I am assuming there is a way to do this in PostgreSQL as well since PostgreSQL accepts list data types as columns.

Is this possible to do in PostgreSQL?

If so, how should the index be set up (we can't figure out how to set up an index that takes into account the three properties)?

This is possible for PostgreSQL 8.4 or later. In order to create a multi-column GIN index, you need to install the additional module btree_gin.
Simple types like integer or date are usually better off with a (default) B-Tree index, so that's not installed with standard PostgreSQL. But for a case like this a multi-column index is fastest, so you need the additional index methods for the plain types.

In PostgreSQL 9.1 or later you can install it with CREATE EXTENSION:

CREATE EXTENSION btree_gin;

In older versions you would run as privileged system user (like postgres):

psql -d dbname -f SHAREDIR/contrib/btree_gin.sql

For a PostgreSQL 8.4 installation on Debian,this might be:

psql -d mydb -f /usr/share/postgresql/8.4/contrib/btree_gin.sql

Then, given this table:

CREATE TEMP TABLE tbl (age int, names text[], thedate date);

... you can create this multi-column GIN index:

CREATE INDEX tbl_gin_idx ON tbl USING GIN (names, age, thedate);

... which can be used by a query like:

SELECT * FROM tbl
WHERE  age = 26
AND    '{json}'::text[] <@ names
ORDER  BY thedate;

Note that a GIN index caries a non-trivial cost for write operations.
But that's hardly worth mentioning in comparison to what a SELECT on 10 million rows without index would do to you.


Error when trying to start Mirroring Thanks for looking at this, and thank you in advance for any answers!

I am attempting to start a mirroring session, but I am getting an Error 1418, which basically states that the TCP port doesn't exist. However, when I look at the SQL Logs, I am seeing a completely different error. It says that the login does not have CONNECT permissions on the endpoint.

Here is the problem, I do not know where this Login is coming from. It doesn't exist. It's never existed. It's not the login that I am using when connecting to either the Principal or the Mirror.

I can't create this login because there is a '$' in it, which again, makes no sense. Any help would be astronomically appreciated! Thank you.

So it seems that the issue was actually related to certificates. Once the certificate was backed up and shared to the mirrored server, everything worked as intended. I just wanted to swing back around and let those who were reaching out to help know where I landed.


how can an additional natural join with the same table twice *reduce* the row count This is real results from my database. I spend some time trying to reproduce this behavior with an SSCCE but failed. How is the last of the below results possible?

SELECT COUNT(*) FROM rr.resource    a
--- 15771

SELECT COUNT(*) FROM rr.resource    a
NATURAL JOIN rr.interface   b
--- 41419

SELECT COUNT(*)
FROM rr.resource    a
NATURAL JOIN rr.interface   b
NATURAL JOIN rr.interface   c
--- 0

The zero (0) row count in the last query is consistent and repeatable every time.
In some examples I created, adding a second natural join with the same table twice yields the same number of rows (as I was expecting). How is then the above result possible?

Basically, NULL is causing this, because NULL<>NULL. One of the columns in your self-joined table will be all NULLs.

Here's a little test case that shows why this can happen. Naughty NULL equality and the way NATURAL JOIN works, picking column names to join on for you.

Setup:

create table one ( a integer, b integer );

CREATE TABLE two ( A INTEGER , c integer);

insert into one values (1,1);

insert into two values (1,1);

insert into two values (1,null);

insert into two values(2,NULL);

Queries:

SELECT COUNT(*)
FROM ONE
NATURAL JOIN TWO tttt;

SELECT COUNT(*)
FROM ONE
NATURAL JOIN TWO tttt
NATURAL JOIN TWO ttttt;

The first query will give 2, the second query 1.

Your problem will just be a more advanced version of this.

Use JOIN .... USING with the columns of your own choice to workaround this.


Why does a query on 4 times more data take 300 times more processing? To score the price preference of our individual customers in, we run the below query.

Basically it is a weighted average of a product score. The score depends on the product group and the packaging type. The weight depends on the product type and the customer type.

SELECT SUBQRY.customerNo,
       SUM(WGT.weight * SUBQRY.avg_score) / SUM(WGT.weight) AS score
FROM
  (SELECT SALE.customerNo,
          SALE.ProductType,
          SALE.CustomerType,
          AVG(SCORE.score) AS avg_score
   FROM mySchema.Retail_sales AS SALE
   LEFT JOIN mySchema.product_scores AS SCORE
        ON (SALE.ProductGroup = SCORE.ProductGroup AND
          SALE.Packaging = SCORE.Packaging)
   GROUP BY SALE.customerNo,
            SALE.ProductType,
            SALE.CustomerType
   ) AS SUBQRY
LEFT JOIN mySchema.product_SOW AS WGT
        ON (SUBQRY.CustomerType = WGT.CustomerType AND
          SUBQRY.ProductType = WGT.ProductType)
GROUP BY SUBQRY.customerNo
ORDER BY SUBQRY.customerNo

We have two reatail chanes, one with 4 times as much sales and product Groups (16k vs 4k) as the other.
How can I explain the larger one takes 300 times more time to execute and how can I cure that?

PS: We currently have no indexes on tables within mySchema. Creating an index on mySchema.product_scores.ProductGroup does hardly help.

You need to provide more information for us to be sure: the table definitions including all the indexes and other keys defined upon them, if any of the referenced objects are views rather then tables then their definitions too and those of their dependant objects, and the query plan (I'm assuming sybase can be asked to present you with that as easily as other DBMSs).

A super-linear growth in effort compared to data size usually means that the database is having to scan or partial scan one object for every row output from another. Another reason for unexpected time for lager data is that the data was previously small enough to all be processed in memory but with the new size the database is having to either read more from disc (this compounds very badly with having to scan something multiple times) or spool intermediate results out to disc or both.

We could hazard some guesses, but if you edit the question to include more info (query plan and table/index/key/view definitions) we can make much better suggestions with less guess work (and tell you have to interpret that information yourself if the same problem comes up elsewhere in future).


Using GETDATE() in a variable returns 0 rows I have a query that declares and sets a datetime variable at the very beginning:

declare @_NextAppt as datetime
set @_NextAppt = dateadd(dd,3,getdate())

when I check it and execute a select @_NextAppt it returns three days from today which is what I want.

The problem occurs in my next select statement when I use

where table.apptdate = @_NextAppt

The query returns no rows.

Here's the sample query:

select distinct PatID, StaffID
from dbo.Appointments
where PatID is not null and ApptDate = @_NextAppt

I can get everything to work if I replace getdate() with an exact date like '11/09/2016'. But that doesn't work long term for my report.

What am I missing?  I'm relatively new to SQL and self taught so I'm sure there is some SQL quirk that I don't know about.

The problem is that GETDATE() returns a DATETIME. So the WHERE condition checks if any of the values in that column is equal to that exact date and time (+3 days).

You need to strip the time part, either by casting or by using a date variable:

declare @_NextAppt as datetime ;        -- use same type as the column

set @_NextAppt = dateadd(day, 3, cast(getdate() as date)) ;
      -- strips the time from getdate(),
      -- adds 3 days and
      -- does an implicit conversion to the variable's type (datetime)

We also don't know the type of the column. It would be good to use inclusive-exclusive conditions when comparing datetime values. The query below will work independent of whether your column is DATE or DATETIME or DATETIME2:

select distinct PatID, StaffID
from dbo.Appointments
where PatID is not null
  and ApptDate >= @_NextAppt
  and ApptDate  < dateadd(day, 1, @_NextAppt) ;

If the column is DATE, you could of course use an equality condition:

---
where PatID is not null
  and ApptDate = @_NextAppt ;

Necessary links to blog posts about good (and bad) practices with dates (note how I replaced the shorthand dd to the full day datepart when using the DATEADD() function above):

Bad Habits to Kick : Using shorthand with date/time operations
Bad habits to kick : mis-handling date / range queries
What do BETWEEN and the devil have in common?


Blocked Process Report - which process does lockmode apply to? I am analyzing a blocked process report and my blocking-process XML is as follows (some sections redacted for simplicity)

<blocking-process>
    <process status="running"
            spid="123"
            sbid="0"
            ecid="0"
            priority="0"
            trancount="2"
            lastbatchstarted="2019-11-22T03:00:00.347"
            lastbatchcompleted="2019-11-22T03:00:00.347"
            lastattention="1900-01-01T00:00:00.347"
            clientapp="MyAppName"
            hostname="MyServer"
            hostpid="6596"
            loginname="MyLogin2"
            isolationlevel="read committed (2)"
            xactid="184723528"
            currentdb="6"
            currentdbname="MyDB"
            Timeout="4294967295"
            clientoption1="536870944"
            clientoption2="128056">
        <executionStack>

        </executionStack>
        <inputbuf>

        </inputbuf>
    </process>
</blocking-process>

my blocked-process XML is as follows

<blocked-process>
    <process id="process89e4c7f848"taskpriority="0"
                logused="256"
                waitresource="KEY: 6:72057594046447616 (4bc05a090cdd)"
            waittime="13730"
            ownerId="184723742"
            transactionname="user_transaction"
            lasttranstarted="2019-11-22T03:00:09.190"
            XDES="0x89d0786e80"
            lockMode="X"
            schedulerid="33"
            kpid="8176"
            status="suspended"
            spid="112"
            sbid="0"
            ecid="0"
            priority="0"
            trancount="2"
            lastbatchstarted="2019-11-22T03:00:09.207"
            lastbatchcompleted="2019-11-22T03:00:09.207"
            lastattention="1900-01-01T00:00:00.207"
            clientapp=".Net SqlClient Data Provider"
            hostname="MyServer"
            hostpid="1092"
            loginname="MyLogin"
            isolationlevel="read committed (2)"
            xactid="184723742"
            currentdb="6"
            currentdbname="MyDB"
            lockTimeout="4294967295"
            clientoption1="671088672"
            clientoption2="128056">
        <executionStack>

        </executionStack>
        <inputbuf>

        </inputbuf>
          </process>
</blocked-process>

I can find the database and object affected in the blocking-process node:

waitresource="KEY: 6:72057594046447616 (4bc05a090cdd)"

However, what I don't know is what lock the blocking process held. I can see lockMode="X"in the blocked process node so my question ism does lockMode="X" relate to the lock that the blocking process was holding or the lock that the blocked process was trying to get?

I just tried this out in my local copy of the Stack Overflow sample database.  I ran the following query in one session:

BEGIN TRANSACTION;

UPDATE dbo.Users
SET Reputation = 0
WHERE Id < 1000;

Which was granted several X key locks in the users table (from EXEC sp_WhoIsActive @get_locks = 1):

<Lock resource_type="KEY" index_name="PK_Users_Id" request_mode="X" request_status="GRANT" request_count="752" />

I then ran this query in another session, which got blocked:

SELECT *
FROM dbo.Users
WHERE Id = 1;

According to the same sp_WhoIsActive query, this is now blocked and waiting on and S key lock from the Users table:

<Lock resource_type="KEY" index_name="PK_Users_Id" request_mode="S" request_status="WAIT" request_count="1" />

After 5 seconds, this produced a blocked process report event.  The "blocked process" node has a lockmode of "S" so this seems to prove that the lockmode is the lock being requested by the blocked process (not the lock being held by the blocking process).

Here's the full report XML for reference.  Unfortunately, it doesn't look like there's any way to tell the lock mode of the blocking process, other than consulting the lock compatibility matrix and inferring from that.

<blocked-process-report monitorLoop="587">
 <blocked-process>
  <process id="process25a0d94a108" taskpriority="0" logused="0" waitresource="KEY: 6:72057594039173120 (8194443284a0)" waittime="5279" ownerId="61463" transactionname="SELECT" lasttranstarted="2019-12-18T08:41:40.603" XDES="0x259bbe81ac0" lockMode="S" schedulerid="3" kpid="41876" status="suspended" spid="54" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2019-12-18T08:41:40.600" lastbatchcompleted="2019-12-18T08:41:40.600" lastattention="1900-01-01T00:00:00.600" clientapp="Microsoft SQL Server Management Studio - Query" hostname="redacted" hostpid="39076" loginname="redacted" isolationlevel="read committed (2)" xactid="61463" currentdb="6" currentdbname="StackOverflow2010" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
   <executionStack>
    <frame line="1" stmtstart="24" stmtend="104" sqlhandle="0x02000000f1f6b108d092143d5a978eea9570bb0e96dc06ea0000000000000000000000000000000000000000" />
    <frame line="1" stmtend="74" sqlhandle="0x02000000669ce70e2be50eb44c883b0c891f586643da33df0000000000000000000000000000000000000000" />
   </executionStack>
   <inputbuf>
SELECT *
FROM dbo.Users
WHERE Id = 1;   </inputbuf>
  </process>
 </blocked-process>
 <blocking-process>
  <process status="sleeping" spid="53" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2019-12-18T08:39:20.630" lastbatchcompleted="2019-12-18T08:39:20.630" lastattention="1900-01-01T00:00:00.630" clientapp="Microsoft SQL Server Management Studio - Query" hostname="redacted" hostpid="39076" loginname="redacted" isolationlevel="read committed (2)" xactid="41842" currentdb="6" currentdbname="StackOverflow2010" lockTimeout="4294967295" clientoption1="671098976" clientoption2="390200">
   <executionStack />
   <inputbuf>
SET STATISTICS XML OFF   </inputbuf>
  </process>
 </blocking-process>
</blocked-process-report>


Detach SQL Server database without deleting it? Is there a way to detach a database without deleting it?

Detaching a DB will remove it's entry from the internal list of DBs managed by Master but will leave the file there to re-attach.  This often could be perhaps not the best course of action to take but to do it follow the MS docs for detach a database.

You could execute the following but it could fail if users are connected to the database in your instance:

EXEC sp_detach_db 'Databasename', 'true';

You could always run a quick sp_who2 to see who is connected to your database or a more thorough:

select spid, status, loginame, hostname, blocked, db_name(dbid), cmd from master..sysprocesses where db_name(dbid) = 'databasename'

As BradC has answered, taking a database 'offline' is also a good option which has it's own benefit and drawback.  Detaching unlocks the file so you could have the file manipulated while it it detached.  Just note having a database 'offline' can cause your scripts or maintenance tasks that scan all your databases to report failures.  It will still 'see' the database, try to access it, but it will not be able to.  Typically most maintenance scripts I write have logic to handle this but many in the wild do not so just note that.


Getting the only updated field using a postgreSQL trigger? Is there any way to use a trigger which can get the only updated field from a record? I am just testing the audit trail log so it can save the field name alongside its other information when it is updated. I do not want to use IF statement because I may have more than 200 fields in my table.

If you are using the latest versions of PostgreSQL, you can use the JSON(B) functions and operators to your advantage. Although this is not (yet) a full solution, check to see if it mimics what you're trying to achieve:

-- The 'new' and 'old' entries will simulate the 'old' and 'new'
-- values for a row that you can use in a trigger function
WITH
new(id, changed_column, integer_changed_column, not_changed,
   array_changed_column, changed_null) AS
(
    VALUES (12, text 'Value', 1234, text 'unchanged',
        array [1, 2], cast(null as text))
),
old(id, changed_column, integer_changed_column, not_changed,
    array_changed_column, changed_null) AS
(
    VALUES (12, text 'New value', 1235, text 'unchanged',
        array [1, 3], text 'not-null')
)

-- And we get a setof records with the changes
SELECT
    *
FROM
    (
    SELECT
        column_name,
        (row_to_json(new)->column_name #>> '{}') AS new_value,
        (row_to_json(old)->column_name #>> '{}') AS old_value
    FROM
        new, old, (
        SELECT
            json_object_keys(row_to_json(new)) AS column_name
        FROM
            new
        ) AS cc
    ) AS s0
WHERE
    new_value IS DISTINCT FROM old_value
ORDER BY
    column_name ;

The result that you'll get shows you all the updated columns (=fields). I have assumed that more than one can be updated at once:

 column_name             | old_value | new_value
 ------------------------+-----------+-----------
 array_changed_column    | [1,2]     | [1,3]
 changed_column          | Value     | New value
 changed_null            |           | not-null
 integer_changed_column  | 1234      | 1235

NOTE: all values are converted to text, because it is the type that all others can be converted to.


Unable to turn off automatic memory management (AMM) i am running oracle 11.2.0.4 SE RAC on Red Hat linux 6, the database is using ASM for datafile management

i have been experimenting with turning on AMM and set the following parameters

alter system set pga_aggregate_target='0' scope=spfile sid='*';
alter system set sga_target='0' scope=spfile sid='*';
alter system set memory_max_target='8G' scope=spfile sid='*';
alter system set memory_target='8G' scope=spfile sid='*';

but now i wanted to turn off AMM on the instances and database, but when i make the required changes

alter system set pga_aggregate_target=2G scope=spfile sid='*';
alter system set sga_target=6G scope=spfile sid='*';
alter system set memory_max_target=0 scope=spfile sid='*';
alter system set memory_target=0 scope=spfile sid='*';

i receive this error when starting the instance

SQL> startup pfile='?/dbs/initdev12.ora';
ORA-00843: Parameter not taking MEMORY_MAX_TARGET into account
ORA-00849: SGA_TARGET 6442450944 cannot be set to more than MEMORY_MAX_TARGET 0.
ORA-01078: failure in processing system parameters

if i set the MEMORY_MAX_TARGET back to 8G i can start the database but my parameters now look like this

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 6G
sga_target                           big integer 6G
SQL> show parameter memory

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 8208M
memory_target                        big integer 0
shared_memory_address                integer     0
SQL> show parameter pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 2G

any ideas what i'm doing wrong?

cheers
dave

In case one does not want to use AMM MEMORY_MAX_TARGET setting has to be not set at all:

alter system reset memory_target;
alter system reset memory_max_target;


What does IDLE state denotes in a row of pg_stat_activity? Can someone resolve my below doubts about pg_stat_activity in terms of postgres 9.5?

Can some let me know what does the state IDLE denotes in the row of pg_stat_activity? Is it like it checks all the open connections to postgres and if the connection is not executing any query then IDLE denotes the last query executed by the connection?
In the output of select * from pg_stat_activity I am seeing rows with state IDLE and the query_start_date more than 2 days old? What does this signify ? Should I be worried about this?
Can someone let me know how pg_stat_activity is populated? If I am executing a query like Select * from pg_stat_activity does it produce the details from some snapshot maintained by postgres or it checks the current connections open in postgres to provide the details?

This has been answered in postgres mailing list

  Each row in pg_stat_activity represents an established connection to
  the server from a client. "idle" means the client is not currently
  executing a query nor in a transaction.

  If query_start_date is 2 days old, that just means the last query to
  be executed on that connection was two days ago.

  Not really a reason for worry, unless you have so many connections
  open that it's consuming more RAM than you can afford. It's generally
  desirable for a connection pool to have a few idle connections so
  queries don't suffer the latency of establishing a new connection.


SELECT using JOIN where result counts per join are less than n? I am trying to query tableA, joining or sub-querying on tableB, where the count of matching rows from tableB are less than 4.
Example:
tableA
+-------------------+
|columnA|columnB    |
+-------------------+
|item1  |focaccia   |
|item2  |spinach    |
|item3  |goat cheese|
|item4  |beets      |
+-------------------+

tableB
+-------------------+
|columnA|columnB    |
+-------------------+
|blah1  |focaccia   |
|blah2  |focaccia   |
|blah3  |focaccia   |
|blah4  |spinach    |
|blah5  |goat cheese|
|blah6  |goat cheese|
|blah7  |goat cheese|
|blah8  |goat cheese|
|blah9  |goat cheese|
|blah10 |goat cheese|
|blah11 |beets      |
|blah12 |beets      |
|blah13 |beets      |
|blah14 |beets      |
|blah15 |beets      |
|blah16 |beets      |
|blah17 |beets      |
+-------------------+

The desired result from my SELECT from tableA here would be:
+-------------------+
|columnA|columnB    |
+-------------------+
|item1  |focaccia   |
|item2  |spinach    |
+-------------------+

... since the count of other matching items is greater than 4 each.
Is this possible?

you can use a subquery to fin all columB that have a lower countg tha 4

CREATE TABLE TableA
    ("columnA" varchar(5), "columnB" varchar(11))
;

INSERT INTO TableA
    ("columnA", "columnB")
VALUES
    ('item1', 'focaccia'),
    ('item2', 'spinach'),
    ('item3', 'goat cheese'),
    ('item4', 'beets')
;

CREATE TABLE TableB
    ("columnA" varchar(6), "columnB" varchar(11))
;

INSERT INTO TableB
    ("columnA", "columnB")
VALUES
    ('blah1', 'focaccia'),
    ('blah2', 'focaccia'),
    ('blah3', 'focaccia'),
    ('blah4', 'spinach'),
    ('blah5', 'goat cheese'),
    ('blah6', 'goat cheese'),
    ('blah7', 'goat cheese'),
    ('blah8', 'goat cheese'),
    ('blah9', 'goat cheese'),
    ('blah10', 'goat cheese'),
    ('blah11', 'beets'),
    ('blah12', 'beets'),
    ('blah13', 'beets'),
    ('blah14', 'beets'),
    ('blah15', 'beets'),
    ('blah16', 'beets'),
    ('blah17', 'beets')

SELECT a.* FROM TableA AS a INNER JOIN (SELECT "columnB" FROM Tableb GROUP BY "columnB" HAVING COUNT(*) < 4) AS b
ON a."columnB" = b."columnB"

columnA | columnB
:------ | :-------
item1   | focaccia
item2   | spinach

db<>fiddle here


Comparison Clarification I would to hear the solution for the below requirement.

I have table A with key number and Fruits, table B with Fruits and rating with the below data. I have to retrieve result with least rating from B mapping keynumber A by based on fruits.

My result should be like

Keynumber   Fruits  Rating
123         Orange  4
456         Apricot 2

Table A

Key number  Country
123         Apple
123         Orange
456         Cherry
456         Apricot

Table B

Country Rating
Apple   5
Orange  4
Cherry  8
Apricot 2

I used the below query but it is displaying the two records of the same keynumber

select A."key number", A.fruits, B.fruits, B."rating from"
table A
  left join table B on A.fruits = B.fruits
order by a."key number"

Keynumber   Country Rating
123 Orange  4
123 Apple   5
456 Apricot 2
456 Cherry  8

declare @a table (Key_number int,  Country varchar(100));
insert into @a values
(123,         'Apple'),
(123,         'Orange'),
(456,         'Cherry'),
(456,         'Apricot');

declare @b table (Country varchar(100), Rating int);
insert into @b values
('Apple',   5),
('Orange',  4),
('Cherry',  8),
('Apricot', 2);

select top(1) with ties a.*, b.Rating
from @a a join @b b
        on a.Country = b.Country
order by row_number() over(partition by a.Key_number order by b.Rating);


Postgresql Backup all databases seperate files I need to create an automatic backup of all databases from PostgreSQL ( running on a Windows machine ).
I need to know if is possible to get backup of all databases but on separate files?
Using pg_dumpall creates a script that has all databases on it. But i need each database on a separate file

You can combine pg_dump with a batch script that iterates over all databases:
I use something like this to dump my development databases:
@echo off
set PGBINDIR=c:\Program Files\PostgreSQL\13\bin

rem avoid the password and user prompt from pg_dump
set PGHOST=localhost
set PGUSER=postgres
set PGPASSWORD=********
set PGPORT=5432

rem create a text file listing all databases
rem adjust here if you want to exclude a database
"%PGBINDIR%\psql" -X -c "select datname from pg_database where not datistemplate" -A -t -o dblist.txt -d template1

rem dump all user accounts and roles
"%PGBINDIR%\pg_dumpall" --globals-only --file=postgres_globals.sql

for /f %%i in (dblist.txt) do (
  echo Backing up: %%i
  "%PGBINDIR%\pg_dump" --create --format=c --file=%%i.pgdmp %%i
)


INNER JOIN for 3 tables I am doing a small course in SQL during my academic degree, I am very new with this material.

I saw Inner joinning 3 tables on StackOverflow, I tried to do INNER JOIN between my three tables, the INNER JOIN between the two tables are working well, but when I am adding the thired it's not working well, maybe I missed something?

this one is working:

 SELECT Actors.FirstName,Actors.LastName
 FROM Actors
 INNER JOIN ActorsMovies  ON Actors.ActorID= ActorsMovies.ActorID;

this one doesn't work, I get an error message that something is missing:

 SELECT Actors.FirstName,Actors.LastName
 FROM Actors
 INNER JOIN ActorsMovies  ON Actors.ActorID= ActorsMovies.ActorID
 INNER JOIN GenresMovies ON ActorsMovies.MovieID=GenresMovies.MovieID;

You could need brackets.
Try this:

SELECT Actors.FirstName,Actors.LastName
FROM ((Actors
INNER JOIN ActorsMovies  ON Actors.ActorID= ActorsMovies.ActorID)
INNER JOIN GenresMovies ON ActorsMovies.MovieID=GenresMovies.MovieID);


How to see entire longtext column in MySQL client Is there any way to view the entire text of a LONGTEXT column when we select it using the MySQL command line client - mysql?
This query:
select query from statement_analysis;

only shows 64 characters of the query.
This is on 5.7.25.
example from 5.7.33:
--------------
select max(length(query))
from statement_analysis
--------------

+--------------------+
| max(length(query)) |
+--------------------+
|                 65 |
+--------------------+
1 row in set (0.00 sec)

--------------
select query
from statement_analysis
where
length(query) > 60
--------------

+-------------------------------------------------------------------+
| query                                                             |
+-------------------------------------------------------------------+
| SELECT `DIGEST` , `CURRENT_SCH ... . `events_statements_history`  |
| SELECT COALESCE ( SCHEMA_NAME  ... ) - INTERVAL ? SQL_TSI_SECOND  |
| SELECT `sys` . `format_stateme ... cy` , `sys` . `format_time` (  |
| SELECT `performance_schema` .  ... IMER_WAIT` AS `avg_latency` ,  |
| SELECT COALESCE ( SCHEMA_NAME  ... statements_summary_by_digest`  |
+-------------------------------------------------------------------+
5 rows in set (0.00 sec)

If you run this command with 1000 or more it expands this to 1000:
set @sys.statement_truncate_len = 1000

But I would like to get the full query. It looks like the general log has the full length queries for what that is worth.
Bobby

The query column on x$statement_analysis shows more than 1000 characters. This was big enough for the problem I was working on. I do not know if it has some limit.
Here is the max size on the production database that I was working on:
--------------
select max(length(query))
from x$statement_analysis
--------------

+--------------------+
| max(length(query)) |
+--------------------+
|               1747 |
+--------------------+
1 row in set (0.08 sec)

So the answer is to use sys.x$statement_analysis instead of sys.statement_analysis and your query text will not be as limited.
Bobby


ora-01410 invalid RowID sporadically in a select statement I have a select that looks up some data by a join. The system runs Oracle 10.02g and the select looks like this

SELECT distinct t1.crit
  FROM table_name1 t1
 INNER JOIN table_name2 t2
    ON t1.crit = t2.crit
 WHERE NVL (t2.qty, 0) + NVL (t2.adds, 0) - NVL (t2.remove, 0) > 0
   AND t1.process_id = 'some_process_id'
   AND t1.item_no = 'some item NO'
   AND t1.to_gen = 1

where t1 has an index on (item_no, crit), and t2 has an index on (item_no, crit, X), and X is some additional column.

The objective of this statement is to find all candidates for which I need to generate data in additional tables. When the new data has been generated, the column t1.to_gen is set to 0. At the moment it is not clear, if the software which runs this statement, does intermediate commits, e.g. generates some new data for one item_no, sets t1.to_gen = 0 and commits, before using the next item_no from the cursor of the statement in question.

This statement is executed successfully several times a day in my environment, but in rare cases I get an error from oracle, whose source is not clear to me:

ora-01410: invalid RowID

As the select statement does not use the pseudo-column RowID direcly, Oracle will have to use it internally.

What is the source of this problem?

Update: Sad as it sounds, the system does intermediate commits, but that is not the source of the problem.

Are there other processes operating on the same tables? Indexes being rebuilt?

If so, then you could be hitting the situation that Jonathan Lewis describes here: http://jonathanlewis.wordpress.com/2007/09/16/index-rebuild/


What is the significance of phpMyAdmin's configuration storage and what should I know about it? After initializing phpMyAdmin on my server, I created a test database in the GUI platform. I realized that I had then received a notification in the phpMyAdmin browser that I needed to set up an initial configuration, in any database, and to "click here" to do it. So, I clicked the link and the database I was in was populated with a bunch of tables beginning with pma, which I believe represents the control user? I'm not really sure what this means or why every table begins with "pma_".

So, since the database I was in is now my "configuration storage" database, I renamed it and am just leaving it as is; no extra tables or data other than what the automatic configuration executed when I clicked the button that I mentioned previously.

The wiki for phpMyAdmin explains the configuration storage setup, but I don't believe I need to do that because the button that I had clicked in the alert space of the GUI did that for me.

So, what's the point of this database, what is pivotal information that I should know about it, and is it safe for me to just leave it alone and move on about my business? Thank you.

NOTE: I've used MySQL and phpMyAdmin before but it was under my university's environment and provided database; I never had to set up one of my own or do this confutation step.

There was a broken link in that wiki page (which I've just fixed), but it was supposed to take you to http://docs.phpmyadmin.net/en/latest/setup.html#linked-tables

Basically, they're additional features including bookmarked queries, comments, SQL-history, tracking mechanism, PDF-generation, transformations, relations, and more. By adding the tables (which you can do manually or phpMyAdmin often can do for you, which you have taken advantage of), you get access to these features. If you don't need the features, then you don't need the tables.

So you're correct that you don't need to manually run the SQL statements now.

The tables start with the pma__ prefix for the same reason WordPress tables start with wp_ and other applications use similar prefixes: in shared hosting situations, users often only get access to one database and several applications need to peacefully coexist. By adding a namespace prefix, you can have gallery_users, wp_users, and gramps_users (I'm making up these table names for the sake of example)


MongoDB How to query data in object with random names? I have a collection 'news' in database as follows.

{
"_id" : ObjectId("5a6d068b0088c92a34324d8e"),
"4210003170944" :
    {
    "date" : "20180126",
    "sectionId" : "100",
    "rank" : "1",
    "newsTitle" : "Foo",
    "oid" : "421",
    "aid" : "0003170944"
    },
"2140000800903" :
    {
    "date" : "20180126",
    "sectionId" : "100",
    "rank" : "2",
    "newsTitle" : "Bar",
    "oid" : "214",
    "aid" : "0000800903"
    }
}

If I want to search all news with "oid" : "421", what command should I use?

I've tried

db.locations.find({ "_id" : ObjectId("5a6d068b0088c92a34324d8e")}, { oid : '421' })

but it doesn't fetch me anything.

Please correct me if I'm wrong.

If I want to search all news with "oid" : "421", what command should I
  use?

As per MongoDB BOL $match (aggregation) Filters the documents to pass only the documents that match the specified condition(s) to the next pipeline stage.

The $match stage has the following prototype form:

{ $match: { <query> } }

So, in your case the query will be like that

   db.locations.aggregate({$match: {'4210003170944.oid': '421'}}).pretty()

Let's from starting onward i am going to show through mongo shell .
First i am going to create a collection name as locations. which is a collection name in your query. Then i will insert the data , which you have provided through the code and the do the desire query with $match (aggregation).

> db.createCollection("locations")
{ "ok" : 1 }
> db.locations.insert({
... "_id" : ObjectId("5a6d068b0088c92a34324d8e"),
... "4210003170944" :
...     {
...     "date" : "20180126",
...     "sectionId" : "100",
...     "rank" : "1",
...     "newsTitle" : "Foo",
...     "oid" : "421",
...     "aid" : "0003170944"
...     },
... "2140000800903" :
...     {
...     "date" : "20180126",
...     "sectionId" : "100",
...     "rank" : "2",
...     "newsTitle" : "Bar",
...     "oid" : "214",
...     "aid" : "0000800903"
...     }
... }
... )
WriteResult({ "nInserted" : 1 })
>
> db.locations.aggregate({$match: {'4210003170944.oid': '421'}}).pretty()
{
        "_id" : ObjectId("5a6d068b0088c92a34324d8e"),
        "4210003170944" : {
                "date" : "20180126",
                "sectionId" : "100",
                "rank" : "1",
                "newsTitle" : "Foo",
                "oid" : "421",
                "aid" : "0003170944"
        },
        "2140000800903" : {
                "date" : "20180126",
                "sectionId" : "100",
                "rank" : "2",
                "newsTitle" : "Bar",
                "oid" : "214",
                "aid" : "0000800903"
        }
}

Hope this will help out to you.


Which DB design should scale better? I'm sorry for the vague question description, but I'm honestly a noob in DB design, as I've only really worked with small databases. This might change in the near future though. I have a spider which periodically crawls a website that sells merchandise. The objetive of this spider is to:

1. Obtain essential info from each object being sold, such as name, product_code, url, description, etc.
2. Get access to the price history of said object (by periodically rerunning the crawler), with respective timestamp.

These two requirements lead to two possible (in my limited db knowledge) database designs:

First Option:
An INSERT only table called products, with no UNIQUE fields (except row number), containing all fields mentioned above. Every repeated run of the crawler will duplicate existing rows.

On each product found by the crawler, one simple (and guaranteed) insert on the product table.

When consulting price history, simply group by product_code.
Second Option:
Two INSERT only tables, one called products, containing all essential data (description, name, etc). This table also contains a UNIQUE field called product_code. The second table, called prices, contains only the fields product_code, timestamp and price. This table contains no UNIQUEfields, except for the row id itself.

On each product by the crawler, two inserts:

One simple and guaranteed insert on the prices table.
An insert attempt, which will only actually go through when a never before inserted product is found.

When consulting price history, simply group by product_code on prices table.

My opinion

I believe the second option will scale better in disk usage, as many fields will not be repeated needlessly (as in the first option).

The first option is probably faster though as it's simply one guaranteed INSERT. How much faster though? How bad is it, in the second option, to try and constantly fail to insert?

EDIT - Note when considering the above point, thats in the second option the products table will be much smaller than the prices table.

Please go ahead and suggest other designs if you think it may be better! I'm completely welcome to discussion.

Use both together.

A common design strategy in screen-scraping is to separate the task of fetching data from the task of processing data.

Have one thread focused on just fetching the data. Store that data in rather raw form on one "scrape" table. You can let these accumulate as they may be useful in debugging problems. Occasionally truncate the table when old rows are no longer of interest.

Use another thread or another app to process those data samples. As you process each sample, mark the "scrape" row as processed, parse the raw scraped data, and normalize the data to put the repeating product info (description, etc) in a parent "product" table. Put the changing data (price, date-time sample taken) in a child "price" table.

Use the product code as the key fields linking the two tables if you are certain that is reliable: the code is truly unique and always will be. In my experience such conditions are never true, screw-ups happen and such codes are recycled or mistakenly re-assigned or eventually change when companies are re-orged or merged. So I always use a surrogate key of type UUID, but you may decide to stick with natural keys. Reasonable people disagree on this.

If you are truly concerned about scaling, specify your expected volume of data. A serious enterprise database such as Postgres can easily handle millions of rows, even on low-end computer hardware such as a Mac mini. (Though, for critical data you would of course be using a better machine with error-correcting memory and RAID storage.)


Insert missing records from another table and update timestamp? I made an app for the sales team at my company, they visit the stores that our product is sold at and scan all the retail boxes that are physically there. Now I'm building the reporting side of things, and I'm a bit stuck. I'm running SQL Server 2008.

I've got a "Stores" table which lists all of the stores we sell to as well as each product that SHOULD be there, for example:

+---------+-------------+---------+
|  Store  |   Address   | Product |
+---------+-------------+---------+
| Walmart | 123 Fake St |  702640 |
| Walmart | 123 Fake St |  704651 |
| Walmart | 123 Fake St |  709999 |
| Target  | 456 Main St |  789562 |
| Target  | 456 Main St |  777898 |
| Target  | 456 Main St |  723456 |
+---------+-------------+---------+

I've also got a "Scanned" table which lists all of the products that our sales team has scanned with their phone while being at that physical location:

+-------------+---------+-------------+---------+
| Timestamp   |  Store  |   Address   | Product |
+-------------+---------+-------------+---------+
| 2016-10-06  | Walmart | 123 Fake St |  702640 |
| 2016-10-06  | Walmart | 123 Fake St |  704651 |
| 2016-10-06  | Target  | 456 Main St |  789562 |
+-------------+---------+-------------+---------+

The problem I'm running in to is showing what products were missing on any one visit, for example on 2016-10-06 at Target only 1 product was scanned, 789562, the other 2 from the "Stores" table (777898, 723456) are missing.

I've tried doing a right join:

SELECT * FROM Scanned RIGHT OUTER JOIN Stores on Store, Address, Product

The problem with this is for any missing records in the Scanned table that exist in the Stores table it's returning a NULL for the Timestamp column, so I get something like this:

+------------+---------+-------------+---------+---------+-------------+---------+
| Timestamp  |  Store  |   Address   | Product |  Store  |   Address   | Product |
+------------+---------+-------------+---------+---------+-------------+---------+
| 2016-10-06 | Walmart | 123 fake St | 702640  | Walmart | 123 Fake St |  702640 |
| 2016-10-06 | Walmart | 123 Fake St | 709999  | Walmart | 123 Fake St |  704651 |
| NULL       | NULL    | NULL        | NULL    | Walmart | 123 Fake St |  709999 |
| 2016-10-06 | Target  | 456 Main St | 789562  | Target  | 456 Main St |  789562 |
| NULL       | NULL    | NULL        | NULL    | Target  | 456 Main St |  777898 |
| NULL       | NULL    | NULL        | NULL    | Target  | 456 Main St |  723456 |
+------------+---------+-------------+---------+---------+-------------+---------+

I need the Timestamp column to be able to do reporting on this data.

I'm not sure if there is a better way to go about this, I'm open to anything, I've tried looking into updating the table so the NULL timestamp will match the timestamp of the other entries (we don't visit a store more then once a day) but I didn't get very far.

Well - I dreamed up the following - now it's up to yo to try and 'break' it with your real data :)

set nocount on
declare @Stores table (Store varchar(100),Address varchar(100), Product varchar(100));
insert into @Stores values ('Walmart','123 Fake St','702640');
insert into @Stores values ('Walmart','123 Fake St','704651');
insert into @Stores values ('Walmart','123 Fake St','709999');
insert into @Stores values ('Target','456 Main St','789562');
insert into @Stores values ('Target','456 Main St','777898');
insert into @Stores values ('Target','456 Main St','723456');

declare @Scanned table (Timestamp Date,Store varchar(100), Address varchar(100), Product varchar(100));
insert into @Scanned values ('2016-10-06','Walmart','123 Fake St','702640');
insert into @Scanned values ('2016-10-06','Walmart','123 Fake St','704651');
insert into @Scanned values ('2016-10-06','Target','456 Main St','789562');

WITH StoreVisits
AS (
    SELECT DISTINCT TIMESTAMP
        ,Store
    FROM @Scanned
    )
SELECT a.TIMESTAMP
    ,b.Store
    ,b.Address
    ,b.product
    ,CASE
        WHEN c.Product IS NULL
            THEN 'not-scanned'
        ELSE 'scanned'
        END
FROM Storevisits a
JOIN @Stores b ON b.store = a.store
LEFT JOIN @Scanned c ON c.Store = b.store
    AND c.Address = b.Address
    AND c.Product = b.Product
    AND C.TIMESTAMP = A.TIMESTAMP
WHERE c.product IS NULL

It produces the following result set


Is there any reason to back up these system-created certificates? I understand the need for backing up keys and user defined certificates, but is there any reason that we should back up these certificates that are system-created?
MS_AgentSigningCertificate
MS_PolicySigningCertificate
MS_SchemaSigningCertificate34284BF7CE6208DC9D835E9F45ADD94F0757439E
MS_SmoExtendedSigningCertificate
MS_SQLAuthenticatorCertificate
MS_SQLReplicationSigningCertificate
MS_SQLResourceSigningCertificate

Your first certificate needs to be backed up if you are moving the MSDB between instances.  https://support.microsoft.com/en-us/kb/2000274

In short, its not typically required to explicitly backup those certificates.  Particularly if you are backing up your system databases correctly.


Can you update a database with transaction logs only, without having to do a full restore? Initial note I am not a DBA, I am learning on the job.
Where I work we have an antiquated third-party system that sends us a .bak file once a week and log files every hour. To get the previous day's data in the database we have a process that runs a python script which builds the SQL to to do the full restore. The restore takes about 2 hours every night. We have a request from our end users to get the data as "live" as possible, which is theoretically possible as we receive the log file every hour. However I haven't found a way to restore log files only, as it appears a full restore has to be done before you can restore transaction log files.
I would like to run a process that loads in the latest log file every hour and then run a full restore once a week (when we receive the latest bak file). Is there anyway of doing this? Say by only restoring the latest log file or by running a partial restore so we don't have to restore all the files every time?

I would like to run a process that loads in the latest log file every hour and then run a full restore once a week (when we receive the latest bak file). Is there anyway of doing this?

Assuming you run the same version of SQL Server, Yes.  What you're looking for is called RESTORE WITH STANDBY:

RESTORE WITH STANDBY Leaves the database in a standby state, in which
the database is available for limited read-only access. This option is
equivalent to specifying WITH STANDBY in a RESTORE statement.
Choosing this option requires that you specify a standby file in the
Standby file text box. The standby file allows the recovery effects to
be undone.

Every time you apply another log backup users will need to be disconnected briefly.  This is how Log Shipping works, and so long as they send you all the transaction log backups, you don't need to restore the weekly full.  Just apply the next log backup in the chain.


How to list all past remote login information Instead of using "Remote Desktop" for connecting to SQL Server, I and my co-workers are using SQL Server Management Studio to apply changes to the remote server (each user has their own credentials).

One of us made some changes on SQL tables that put all the data at risk. I'm pretty sure one of us is guilty. My question is this. How can I prove this? Is there any sort of query to show all past remote connections from a client to a remote SQL Server instance? Maybe with IP addresses, computer names, credentials.

Does SQL Server Management Studio have some sort of log file or any query to show remote server logins?

P.S. No full backup on remote the SQL Server instance. Logs are pretty clean.

This is how we login to SQL Server:

You can login with (IP,Port Number) to remote server.By default the SQL Server don't log the logins.

If you have pretty clean logs then you shall not get login history data.
if you have configure SQL Login Audit before pretty clean log. Then some point of time there will be changes that to get the 'SQL Login Audit' details through TSQL like

EXEC xp_readerrorlog;

It will give only that 'SQL login audit details' which you had configure SSMS. like

1) Failed logins only
2)Successful logins only
3)Both Failed and successful logins

As per MSDN BOL https://msdn.microsoft.com/en-us/library/ms176013.aspx For remote server login details of current user you can find out from TSQL.

SELECT login_name ,COUNT(session_id) AS session_count
FROM sys.dm_exec_sessions
GROUP BY login_name;

for ref https://www.mssqltips.com/sqlservertip/1735/auditing-failed-logins-in-sql-server/ and https://msdn.microsoft.com/en-us/library/cc280728.aspx


Merge replication SQL Server 2008 publisher with SQL Server 2012 subscriber is this somehow possible to have SQL Server 2008 publisher with a SQL Server 2012 subscriber for merge replication?

No.  With Merge, a Subscriber can be any version no later than the Publisher version.  This is covered in Using Multiple Versions of SQL Server in a Replication Topology.


Load sybase .db into new sybase developer server We've got a few .db files laying around here which were created by Sybase (version 8.x, I guess) and I've the task to extract some data from those old databases.

Now I've installed the latest Sybase developer version (15.7) and want to add these databases to the sybase server.

Unfortunately I do not have any experience with sybase and I couldn't find any documentation in the official sybase manual for this task.

Is this even possible? Does anyone of you know about an article describing the integration of existing complete databases or the necessary commands for the task?

Your DB files are SQLAnywhere files, so first you will need to get SQLAnywhere.  Sybase has some documentation on upgrading databases from earlier versions here.


Why is my filtered index being ignored? --Setup, actual queries after the space
--create table fltrind (a integer,b integer)
truncate table fltrind
DROP INDEX fltrind.nf
DROP INDEX fltrind.f
DECLARE @i integer
SET @i=0
while @i<100
BEGIN
INSERT INTO fltrind(a) values(@i)
SET @i=@i+1
END
INSERT INTO fltrind(a,b) values(9000,0900)

insert into fltrind(a,b)
select top 100000 f1.a,f1.b from fltrind f1 , fltrind f2

create nonclustered index nf on fltrind(b) INCLUDE(a)
create nonclustered index f on fltrind(b) INCLUDE(a) where b is not null

UPDATE STATISTICS fltrind WITH FULLSCAN

select a from fltrind where b is not null

DROP INDEX fltrind.nf

UPDATE STATISTICS fltrind WITH FULLSCAN

select a from fltrind where b is not null

Looking at the query plan, the filtered index is not used as long as the non-filtered index is present.  Any idea why, and how can I get it to use the filtered index?

Dropping the non filtered index nf makes the optimizer use the filtered index f.

In fact, increasing the rowcount so that >10% of the rows qualify results in a table scan when the non filtered index is dropped.

Since SQL Server can skip NULL rows to start the range scan, the cost of either index is identical, so this is basically a coin toss for the optimizer. Look at the plans in SQL Sentry Plan Explorer* by default and when you hint the index (click to enlarge):

Since it's a toss-up, I don't know what benefit you'd get out of forcing SQL Server to choose one of two equally valid options.
* Disclaimer: I work for SQL Sentry.


Update an Indexed View "Online" Say I have an indexed view that looks like this:

ALTER VIEW dbo.MyIndexedView WITH SCHEMABINDING
AS
    SELECT ord.SomeColumn, COUNT_BIG(*) AS Count
    FROM dbo.Ordered ord
    WHERE ord.CreatedWhen >  CONVERT(DATETIME, '2014-11-01', 121)
    GROUP BY ord.SomeColumn
GO

CREATE UNIQUE CLUSTERED INDEX [CIX_MyIndexedView] ON dbo.MyIndexedView (SomeColumn)
GO

If I change the date time value in this view (say to '2014-11-01') then the clustered index needs to be regenerated.

Is there a way to have the existing view and index stay in effect until the new one is totally built and then have them swap?  Similar to Online=On

Here is how I would do it. First, create a couple of spare schemas:

CREATE SCHEMA HoldingTank AUTHORIZATION dbo;
CREATE SCHEMA Swapper AUTHORIZATION dbo;

Now, when it's time to refresh the view and change the filter predicate, create it in the HoldingTank schema:

CREATE VIEW HoldingTank.MyIndexedView
WITH SCHEMABINDING
AS
  SELECT ...
GO

CREATE UNIQUE CLUSTERED INDEX [CIX_MyIndexedView]
ON HoldingTank.MyIndexedView(SomeColumn);

Now, start a transaction, and swap the objects.

BEGIN TRANSACTION;
  ALTER SCHEMA Swapper TRANSFER dbo.MyIndexedView;
  ALTER SCHEMA dbo TRANSFER HoldingTank.MyIndexedView;
COMMIT TRANSACTION;

Then at a later time you can simply drop the old copy of the view from Swapper:

DROP VIEW Swapper.MyIndexedView;

I go into more detail about this solution in these blog posts:

T-SQL Tuesday #33 : Trick Shots : Schema Switch-A-Roo
Schema Switch-A-Roo : Part 2


Sql Server: how to validate schema against a textual representation (XML or the like) natively? I need a simple "complete dump" of the schema for our repo, in human-readable format: Sprocs, Views, the like. We need to "fix" some old schema issues that cropped in over the time, and "clean up" the schema. In addition, constant merging between quite different branches should be as easy as possible, therefor a complete representation of the schema to begin with, and not migration scripts, as they are mergeunfriendly (Schema conflicts would have to be found be tests, unfortunately our test coverage is a joke to begin with).

Just as a reminder: any binary blop or .bak file are not the goal, as well as any GUI tools. This should run on a CI server. We need full automation for the schema vialidation from the commandline, and a humanreadable filfeformat for the schemareprenstation in the repository.

I know that Sql Server itself allows to export the schema of a table (maybe a whole DB) as XML, but I can't find the statement.. any help?

Or any (preferably c#/.NET related, preferably opensource) tools that can do such a thing?

Sql Server Version would be 2008 and up.

Edit: I'm speaking of a script, not some GUI functionality.. I've seen that done by a colleague, and can't ask him as he's on vacation for quite some time.. searched for a while, but didn't find the script

Edit2
I need a schema compare and diff like Tools like Redgate provide, just for the commandline, and because of our dev focused process we need a humanreadable format.. XML or whatever. One approach is showed by http://schemacrawler.sourceforge.net -> http://schemacrawler.sourceforge.net/snapshot-examples/snapshot.txt

I've written a C# tool to migrate a Schema from Sql Server to e.g. LocalDb via SMO , so I know the challenges, but I was hoping that this time I could save the time, because things like sp_help and XML schema support is built right into Sql server.
So the question is more like "is there a script which could export all at once". This is for built time, not production time, so perf doesn't matter at all.

As Aaron mentioned, there is no click - click - finish and export to xml  option in sql server.

Rather you can use FOR XML AUTO, XMLDATA to do that using T-SQL.

use databaseName
go
SELECT SCHEMA_NAME(t.schema_id) AS schema_name,
        t.name AS table_name,
        c.name AS column_name,
        st.name as data_type,
        c.max_length
        ,c.Is_ANSI_Padded
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
inner join sys.types st on c.system_type_id = st.system_type_id
where t.is_ms_shipped = 0 and t.name <> 'sysdiagrams'
order by t.name
FOR XML AUTO, XMLDATA

Refer to : Specifying the XMLDATA Schema Option in a Query

  Generating an XML-Data schema is an overhead on the server, is likely to affect performance, and should be used only when data types are needed.

Alternatively, if you just want to script out the schema, then refer to this answer for more details.


Ignore empty rows while loading data into a table I created a table (tbldemo) with 22 columns using a CREATE TABLE statement in MySQL. When trying to insert the data from a .csv file using a LOAD DATA INFILE statement, I get this error:

  Error Code: 1261. Row 11513 doesn't contain data for all columns

How can I ignore the rows whose entire row is null or empty?

This is what I used to load the data from .csv:

LOAD DATA INFILE 'D:/Singapore/rau_sales_order.csv' INTO TABLE tbldemo
 FIELDS TERMINATED BY ','
ENCLOSED BY '"'  LINES TERMINATED BY '\r\n'
 IGNORE 1 LINES;

I don't want to open the .csv file and clean the data from the file by filtering it for null. Instead, I want to do the filtering directly in the query. Is there any way to achieve this?

load data local infile 'C:\\temp\\abc.csv'
into table table_name
fields terminated by ',' ignore 1 lines
(@col1,@col2,@col3,@col4)
set
table.column_name = @col1,
table.column_name = @col2,
table.column_name = @col3,
table.column_name = @col4;

(@col1,.....) extend the number of columns to your actual number of columns in csv file.
and relate these column numbers to table columns.
It will be done.


Removing Database from AlwaysOn Availability Group I have a 2-node SQL Server AlwaysOn Availability Group.  When I execute this T-SQL command on the Primary server:

USE master
go
ALTER AVAILABILITY GROUP MyAG REMOVE DATABASE TestDB

it removes the TestDB from the Availability Group.  Everything looks fine on the Primary server.  However, on the Secondary replica, the TestDB has the status of "Restoring ..." next to it.   The database is inaccessible.  The status remains "Restoring..." for a while.  Looks like the only thing I could do is to drop it, which I did.

My question is: after I remove the DB from MyAG, what do I need to do to make the TestDB available for use again on the secondary replica?

Simply run

RESTORE DATABASE TestDB WITH RECOVERY

on the secondary.


Database partitioning - Horizontal and Vertical sharding - Difference between Normalization and Row Splitting? I am trying to grasp the different concepts of Database Partitioning and this is what I understood of it:

Horizontal Partitioning/Sharding: Splitting a table into different tables that will contain a subset of the rows that were in the initial table (an example that I have seen a lot if splitting a Users table by Continent, like a sub table for North America, another one for Europe, etc...). Each partition being in a different physical location (understand 'machine'). As I understood it, Horizontal Partitioning and Sharding are the exact same thing(?).

Vertical Partitioning: From what I understood (http://technet.microsoft.com/en-us/library/ms178148%28v=sql.105%29.aspx ), there are 2 sorts of Vertical Partitioning:

Normalization (which consists of removing redundancies from a the database by splitting tables and linking them with a foreign key).
Row Splitting, here is what I don't understand, what is the difference between Normalization and Row Splitting? In what those 2 techniques differ from each other?

I have also read in this post (https://stackoverflow.com/questions/11707879/difference-between-scaling-horizontally-and-vertically-for-databases ) that the difference between Horizontal Partitioning and Vertical Partitioning is that in the first you scale by adding more machines, while in the second one you scale by adding more power (CPU, RAM) to your existing machine, is that a correct definition? I thought that the core difference between those 2 techniques resides in the way you split your tables.
This answer does make sense according to MongoDB's definition of Vertical Partitioning:
http://docs.mongodb.org/manual/core/sharding-introduction/

but it goes in contradiction with other answers or articles I have come across:

https://stackoverflow.com/questions/18302773/what-are-horizontal-and-vertical-partitions-in-database-and-what-is-the-differen
http://technet.microsoft.com/en-us/library/ms178148%28v=sql.105%29.aspx
http://building.wanelo.com/post/42361472646/the-case-for-vertical-sharding

I am sorry for the load of questions but I am a bit confused as a lot of different websites that I have came across say different things.

Any help clarifying would be greatly appreciated. Any link to a clear and simple demonstration with a few tables would also be very helpful.

Although Normalization and partitioning both produce a rearrangement of the columns between tables they have very different purposes.

Normalization is first considered during logical datamodel design. It is a set of rules which ensure that each entity type has a well-defined primary key and each non-key attribute depends solely and fully upon that primary key.

Partitioning comes in during physical database design, when we start to map logical attributes to physical columns and determine the operational characteristics required from the system. Sometimes it is an optimisation added after testing under load because performance was found to be inadequate. It can also play a role in implementing a data retention policy.

In partitioning we recognise that a table is made from rows and columns. When we partition we separate some of those rows (or columns) from the others and hold them in a physically different location.

Horizontal partitioning is when some rows are stored in one table, and some in another. There could be many sub-tables. A typical example is when currently-active transactional data is separated from old "archive" data. This keeps "hot" data compact, with associated performance improvements. We many be able to make the archive tables read-only, compressed and on cheaper disk, too.

As the next step each partition may be moved onto separate hardware. This is commonly know as "sharding." Advantages include being able to use many cheaper boxes rather than one very large, very expensive server, and being able to position a user's data geographically close to her. The cost is increased application complexity. Some DBMS incorporate this ability natively.

Vertical partitioning is when some columns are moved to a different table or tables. Similar to horizontal partitioning the motivation is to keep the "hot" table small so access is faster. Say you run an e-marketing company. 99% for the time you need a person's name and email address and nothing else. These will go in one table and all the other stuff which is useful but seldom-used - birthday, golf handicap, PA's phone number etc. - go in a different table. It can also help when the partitions have different update regimes or are owned by different sections of the business. The two tables can have the same primary key column, and corresponding rows could have the same key value. While it is possible to have multiple vertical partitions for a table, and to shard vertically, I've never come across it.

Vertical and horizontal partitioning can be mixed. One may choose to keep all closed orders in a single table and open ones in a separate table i.e. two horizontal partitions. For the open orders, order data may be in one vertical partition and fulfilment data in a separate partition.

The techniques I've talked about are ways to change the design to improve performance. Scaling is when you change the hardware. One can scale up by buying a bigger box with more RAM, CPU or faster disk, or scale out by moving some of the work onto a different box. Scale up is sometimes called scaling vertically whereas scale out can be called horizontal scaling. While horizontal scaling and sharding have an obvious relationship they are not synonymous. It would be possible to use replication technologies to copy an entire database to another location for use by the users there, thus achieving scale-out, without having to partition any tables.


MySQL - Is it good to have millions of tables in a single database? I have designed new MySQL database for my project, In my project I need to create tables dynamically with dynamic columns.
So I want to know is their any impact on database performance if I have unlimited (Millions or Billions) tables in a single database.
And also want to know what are the max tables MySQL allow to create in a single database (As I can check InnoDB permits 4 Million tables, but I don't know "what is InnoDB?")

ParentTable (Static)
+--------------------------------------------------+
| ID | UserId | ParentName | Desciption | Status |
+--------------------------------------------------+

ChildTable1 (Dynamic)
+---------------------+
| ID | UserId | Value |
+---------------------+

ChildTable2 (Dynamic)
+--------------------------------------+
| ID | UserId | Name| Address| Pincode |
+--------------------------------------+

Above is just a example how I have designed my database with dynamic tables, I'm storing Parents details in a static table and to store child data I'm creating tables dynamically with one or more dynamic columns.
Is there any other best way to achieve the same?

Update 1 :

I'm working on a project in which user can create their own web services from UI by simply selecting number of columns and defining name to each column.
For now I'm considering, I'll create one table for each web service with reference of UserId (As shown in above example).
So in this approach if I have 1 Million users and if every user will create 10 web services then it will create 10 Million tables in database.

How I can resolve this problem?

Absolutely not.

Each table takes at least one file in the directory that represents the database.  The OS overhead is non-trivial.  The OS effort to find a single file (table) in a million-file directory is not pleasant.  Etc.

If it generally poor design to be dynamically creating tables; it is terrible design to be creating millions.

Millions, even billions, of rows in a table -- this is OK.  Having an extra column that replaces your "table" designation -- this is the "right" alternative.

If you would like to discuss your application further (and more specifically than "parent" and "column"), we can discuss other design considerations.

Same arguments apply to having millions of databases.


postgres removing user from role membership How do I drop a role member? My role name tester is member of role postgres (which I do not want). How do I remove this membership?

 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+------------
 tester    |                                                | {postgres}
 postgres  | Superuser, Create role, Create DB, Replication | {}

With REVOKE e.g.:

REVOKE postgres FROM tester;


After trying to restart moved database: Unable to open the physical file "D:\Data\MYDBNAME.MDF" So the default location for databases on our server is

c:\program files\microsoft sql server\mssql11.mssqlserver\mssql\DATA

but due to space limitations we instead use

d:\data

I have now since corrected the default location to be d:\data

However I am unable to move a database that was created in the wrong location i.e. from c:\...\data to d:\data

Trying

ALTER DATABASE MYDATABASE set offline

ALTER DATABASE MYDATABASE
MODIFY FILE ( NAME = 'MYDATABASE', FILENAME = 'D:\Data\MYDATABASE.MDF')

ALTER DATABASE MYDATABASE
MODIFY FILE ( NAME = 'MYDATABASE_LOG', FILENAME =  'D:\Data\MYDATABASE_LOG.LDF')

ALTER DATABASE MYDATABASE SET ONLINE

fails with the following error message

  Msg 5120, Level 16, State 101, Line 1
  Unable to open the physical file "D:\Data\MYDATABASE.MDF". Operating system error 2: "2 (The system cannot find the file specified.)". File activation failure. The physical file name "D:\Data\MYDATABASE_log.lDF" may be incorrect.

  Msg 5181, Level 16, State 5, Line 1
  Could not restart database "MYDATABASE". Reverting to the previous status.

  Msg 5069, Level 16, State 1, Line 1
  ALTER DATABASE statement failed.

The server can definitely access the new location as all other databases in d:\data work ok

While the database is offline, you need to copy the files to the new location. This is not something that SQL Server does automatically when you alter the file location. My preferred method is to use the following steps:

Set the database offline
Copy (don't move) the database files
After copy completes successfully, ALTER DATABASE...MODIFY FILE to change metadata for file locations
Set the database online
Confirm the database restarts successfully

If there's a problem bringin the database online, the files are still in the original location, so you can rollback.

Delete the old files

When performing the copy, you'll need to ensure that the permissions on the files are the same in the new location as in the old location. Otherwise, you'll run into different problems.

If you are using ROBOCOPY to move the files, you can use the /COPYALL flag to ensure that security & file info is copied along with the file.

You can also manually verify the permissions, and verify that the folder permissions are inherited to all contents of the directory.


Retrieving 10 records from multiple tables I have two tables where I store post data. What I want to do is to get 10 records from those two tables. Table 1 - all posts, Table 2 posts that user read.
There are some scenarios;

User has never read a post. So all the records will be gotten from table 1.
This is easy: select * from table1;

User has read some posts but we don't know how many. In this case maximum 3 posts will be fetched from table 2, and the rest of the records will come from table 1.
We can do this by counting how many posts there are in table 2, if there are more than 3 records, get only 3 of them (if lower than 3, then get how many records there are), do the math to learn how many records will be fetched from table 1 and a second query for table 1 to get 7 (or more). But in this case we combine PHP with MySQL and do more work.

How can I get this done in one query?

With a slight waste of fetched rows, you can:

SELECT * FROM (
  SELECT * FROM table2 WHERE <your_own_condition> ORDER BY <your_own_condition> LIMIT 3
  UNION ALL
  SELECT * FROM table1 WHERE <your_own_condition> ORDER BY <your_own_condition> LIMIT 10
) select_both
LIMIT 10
;

At the worst case we will be reading 13 rows, then filtering them down to 10.


How to send an alert based on SQL Query results I currently have a maintenance plan in place that does a backup, re-indexing, etc.  If a task fails, it sends me an email alert.  I would like to add a "T-SQL Statement Task" that will execute a query and send me an email alert based on the results of the query.

For example:

Select Cast(case when count(inventory.items) > 1000 then 1 else 0 End as bit) as SendAlert
From Inventory

The maintenance plan's alert function seems to be triggered whether the Query executed successfully or not -- RATHER THAN from the Query Result.

Can I accomplish what I want from within a maintenance plan or is there a better way to do this?

I would like to add a "T-SQL Statement Task" that will execute a query and send me an email alert based on the results of the query.

You can do this using a TSQL Task and using sp_send_dbmail as below :

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'your mail profile',
    @recipients = 'dba@company.com',
    @body = 'results -- some body goes here',
    @subject = 'Sensible subject goes here ',
-- to send file attachments (if any)
    @file_attachments = 'L:\logs.TXT',
  -- send query results
    @query = 'Select Cast(case when count(inventory.items) > 1000 then 1 else 0 End as bit) as SendAlert
From Inventory';


Auto number for big data I'm working with application that can have millions of data. Just wondering if it is ok to use auto_number as primary key. I have read that bigint, int etc has limit. So for example site is on live for 10 years. Is it ok to use auto_number in it. Does big websites like facebook also use auto_number as their primary key?

Assuming you're using a 64 bit system, an INT(eger), 2^64  =   18,446,744,073,709,551,616, so if you have an insert every nanosecond, your system will run out of numbers after approximately 600 years - I wouldn't worry too much! :-)