Create a sub query for sum data as a new column in SQL Server Suppose that I have a table named tblTemp which has this data:

|  ID | AMOUNT |
----------------
|   1 |     10 |
| 1-1 |     20 |
| 1-2 |     30 |
| 1-3 |     40 |
|   2 |     50 |
|   3 |     60 |
|   4 |     70 |
| 4-1 |     80 |
|   5 |     90 |
|   6 |    100 |

ID will be formatted as X (without a dash) if it's only one ID or (X-Y) if it's a new ID (Y) is child of (X).

I want to add a new column (Total Amount) to output as below:

|  ID | AMOUNT |   Total Amount |
---------------------------------
|   1 |     10 |        100     |
| 1-1 |     20 |        100     |
| 1-2 |     30 |        100     |
| 1-3 |     40 |        100     |
|   2 |     50 |         50     |
|   3 |     60 |         60     |
|   4 |     70 |        150     |
| 4-1 |     80 |        150     |
|   5 |     90 |         90     |
|   6 |    100 |        100     |

The "Total Amount" column is calculated as the sum of the amounts column for the same X ID.

In order to get the parent ID (X), I use the following SQL:

SELECT
  ID, SUBSTRING (ID, 1,
                 IIF (CHARINDEX('-', ID) = 0,
                        len(ID),
                        CHARINDEX('-', ID) - 1)
  ), Amount
FROM
  tblTemp

How can I write a query like this in SQL Server 2012?

You can use sqlfiddle here to test it.

What you're looking for is called a window function. It's similar to grouping, but it allows you to perform aggregate calculations on sets of records without actually grouping. Searching for "window function" or "over clause" should get you lots of helpful reading material.

select
  [ID]
  ,Amount
  ,[Total Amount] = sum(Amount) over(
    partition by left([ID],charindex('-',ID + '-') - 1)
    )
  from tblTemp


How to set local_listener to null upon creating database In Oracle, I am creating a database with a silent install using DBCA and a template. The template does not define local_listener.

<initParam name="local_listener" value=""/>

After the database is create, it has a value for local_listener = LISTENER_dbname. However, I don't want this parameter to have a value when it is created. How can I ensure that this parameter is created with a null value?

I tested specifying -initparams local_listener='' but the local_listener has been set anyways by specifying the empty string.

dbca -silent \
 -createDatabase \
 -templateName General_Purpose.dbc \
 -gdbName testdb \
 -sid testdb \
 -createAsContainerDatabase false \
 -SysPassword oracle \
 -SystemPassword oracle\
 -emConfiguration NONE \
 -datafileDestination /oradata \
 -storageType FS \
 -characterSet AL32UTF8 \
 -memoryPercentage 10 \
 -initparams local_listener=''

You might want to create a custom template referencing a .sql script with the command alter session set local_listener.

I'd only make that effort if in need to use it multiple times, though. If it is a one time thing, maybe you're better off setting local_listener manually.


Should I be regularly shrinking my DB or at least my log file? My question is, should I be running one or both of the shrink command regularly,

DBCC SHRINKDATABASE

OR

DBCC SHRINKFILE

=============================

background

Sql Server:  Database is 200 gigs, logs are 150 gigs.

running this command

SELECT name ,size/128.0 -
CAST(FILEPROPERTY(name, 'SpaceUsed') AS int) / 128.0
AS AvailableSpaceInMB FROM sys.database_files;`

produces this output..

MyDB:           159.812500 MB free

MyDB_Log:   149476.390625  MB free

So it seems there is some free space.

Our backup schedule is as follows:

Transaction Logs Once an hour
Full Backups twice a week
Differential Backups 5 times a week

I strongly recommend you read Paul Randal's article on why you should NOT shrink data files (log files yes, data files no).

I won't quote or try to summarise the article as I really wouldn't do it justice! Just something I think you should at the very least be aware of.


How can I do an incremental `mysqldump` without dropping table? I'd like to fetch the last 100 rows of a table via a mysqldump, but I already have the rest of the table. How can I only fetch the last 100 rows for a mysqldump and then replace (or overwrite) any duplicates I may have?

db1 Incremental Export

mysqldump -umy_user my_db1 my_table --single-transaction --replace --where="id > 900" | pigz > my_db1-my_table.sql.gz

db2 Incremental Import

pv my_db1-my_table.sql.gz | gunzip | mysql -umy_user my_db2

Unfortunately the mysqldump above creates a drop table my_table query. How can I skip this?

What I want to happen...

Say db2 has 950 records. And db1 has 1000. I want records 901-1000 from db1 on db2, as well as the 1-900 already on db2 (just leave them be). Replacing/Overwriting records 901-950 on db2 is preferred.

What Currently Happens...

After running the above, db2 only contains records 901-1000, and is now missing 1-900 (due to drop table).

You can use replace into flag (--replace) and where condition (--where)  while also omitting the create statement (-t):

mysqldump -t --replace --where="id between 901 and 1000" my_db1 table my_table


Running EXPLAIN in a SQL client improve performances on production server I have a MySQL 5.7.20, I use SQLYog Community as my MySQL client and I have a Tomcat Server running a Java application connecting with MySQL Connector 5.1.37.

My query is bad and is being rewritten, the question is not on how to improve that particular query. It loads the content of the table by chunck of 2000 lines, but does a JOIN on itself to also load multiple versions of a line, identified by a number, under the main version of the line.

SELECT t.*
FROM myTable AS t, myTable AS t2
WHERE t2.lineNumber = t.lineNumber
AND t2.version = 0
AND t.executionNumber='87710'
AND t2.executionNumber='87710'
AND t.lineNumber IN (24000,24001,24002,24003,[...],25996,25997,25998,25999)
AND t2.lineNumber IN (24000,24001,24002,24003,[...],25996,25997,25998,25999)
AND (t.version <> 0 OR ( 1  )) AND  1
ORDER BY t.lineNumber, t.version > 0, t.recordDate DESC;

In my case the total size of the table is a little over 160000 lines, so the load takes 81 executions of the query. This takes a random amount of time ranging from a few seconds to over an hour. What I notice is if I recreate the table from scratch and call the web server, each execution of the query takes about 43 secondes. If I run an EXPLAIN FOR CONNECTION using the MySQL client, I get a really bad execution plan.

1    SIMPLE    t    NULL    ref    executionNumber,lineNumber    executionNumber    4    const    80752    100.00    Using where; Using temporary; Using filesort
1    SIMPLE    t2    NULL    ALL    executionNumber,lineNumber    NULL    NULL    NULL    161504    5.00    Using where; Using join buffer (Block Nested Loop)

On the other hand, if I run a "regular" EXPLAIN and copy paste the query, I get a better execution plan.

1    SIMPLE    t2    NULL    range    executionNumber,lineNumber    lineNumber    4    NULL    2000    5.00    Using index condition; Using where; Using temporary; Using filesort
1    SIMPLE    t    NULL    ref    executionNumber,lineNumber    lineNumber    4    labo.t2.lineNumber    1    50.00    Using where

The weird part is, after running that second EXPLAIN, the performance on the web server improves and the Java call that was running for many minutes ends in seconds. I have reproduce this many times, waiting an arbitraty number of times between the start of the Java call and the run of the EXPLAIN. The query coming from the Java server always improve from the first query following my EXPLAIN.

So my question is, how can the execution of an EXPLAIN query changes the performances of another query ?

My best guess is that the MySQL query optimizer keeps offering a bad execution plan from its cache until I run the EXPLAIN, that forces it to compute a new execution plan, but I couldn't find any doc confirming that theory. And it wouldn't explain why the query optimizer offers a bad execution plan to a Java webapp executing a query and a better execution plan to a MySQL client running and EXPLAIN on that same query.

Use JOIN .. ON to establish how the tables are related.  (Actually a "self-join" in this case.)
Since you are building the WHERE clause, get rid of the spurious stuff about 1 at the end.
If version cannot be negative, use > 0 instead of <> 0; this might be easier to optimize.
Since you are JOINing on executionNumber and lineNumber, there is no need to repeat the filtering on them.

Something like this:

SELECT  t.*
    FROM  myTable AS t,
    JOIN  myTable AS t2
      ON   t2.lineNumber = t.lineNumber
      AND  t2.executionNumber = t.executionNumber
    WHERE  t2.version = 0
      AND  t.version > 0
      AND  t2.executionNumber='87710'
      AND  t2.lineNumber IN (24000,24001,24002,24003,[...],25996, 25997,25998,25999 )
    ORDER BY  t.lineNumber, t.version > 0, t.recordDate DESC;

Now it is easier to see what INDEXes are needed.  (Please provide SHOW CREATE TABLE; meanwhile, I will guess.)

I think myTable needs both of these "composite" indexes:

INDEX(version, executionNumber, lineNumber)
INDEX(executionNumber, lineNumber, version)

Then show us EXPLAIN SELECT ....  I think t2 will be referenced first, using the first index.  Then t will be reached into using the second.  In both cases, the "Rows" should be much less than what you saw.

There will be a "filesort" and probably "using temporary".  They are unavoidable due to the complexity of the ORDER BY.

I assume the table is ENGINE=InnoDB?  Then I don't see how 5.7.xx would end up with any inconsistencies in EXPLAIN.  Gone are the days of re-probing to get statistics at the 'wrong' time.

I'm pretty sure the Optimizer has no 'cache' for explain-plans.  However, the plan may change from one day to the next, due to lots of inserts, etc.


Using COUNT() implicitly for making a view over game match data? I've not used SQL in quite a while and I'm pretty rusty it seems. I've had to do a project recently and needed to use MySQL on Windows and when looking for solutions to this particular query, I get told that you can use COUNT(*) implicitly somehow, but I can't get it to work right.
What I need is something like:

PLAYER_ID
MATCHES_WON
MATCHES_LOST

0
4
1

1
2
5

2
2
2

I have made a simple SQL fiddle of my database here.
Could someone help me out with this? I'm willing to accept that I might have to make a more involved solution with some CURSOR and LOOP but thought I'd ask here. It's my first question on this community, so sorry if I didn't format this right or it wasn't an appropriate question.

This select first all individual player_IDs and uses them to get the numbers

CREATE TABLE `game_match` (
  `ID` INT(10) NOT NULL AUTO_INCREMENT COMMENT 'The unique ID of the match,',
  `ACCOUNT_ONE_ID` INT(10) NOT NULL COMMENT 'Foreign key for Player 1.',
  `ACCOUNT_TWO_ID` INT(10) NOT NULL COMMENT 'Foreign key for Player 2.',
  `WINNER_ID` INT(10) NOT NULL COMMENT 'Foreign key for which of the two players won.',
  INDEX(ACCOUNT_ONE_ID, ACCOUNT_TWO_ID, WINNER_ID) ,
  PRIMARY KEY (`ID`) USING BTREE
);

INSERT INTO game_match
  (ACCOUNT_ONE_ID, ACCOUNT_TWO_ID, WINNER_ID)
VALUES
  (0, 1, 0),
    (1, 2, 2),
    (1, 2, 1),
    (0, 1, 0),
    (1, 0, 0),
    (2, 1, 2),
    (0, 2, 0),
    (0, 1, 1);

SELECT
DISTINCT
PlayerID,
(SELECT COUNT(*) FROM game_match WHERE (ACCOUNT_ONE_ID = t1.PlayerID OR ACCOUNT_TWO_ID = t1.PlayerID) AND WINNER_ID =  t1.PlayerID) MATCHES_WON,
(SELECT COUNT(*) FROM game_match WHERE (ACCOUNT_ONE_ID = t1.PlayerID OR ACCOUNT_TWO_ID = t1.PlayerID) AND WINNER_ID <> t1.PlayerID) MATCHES_LOST
FROM (SELECT DISTINCT ACCOUNT_ONE_ID  AS PlayerID FROM game_match
UNION
SELECT DISTINCT ACCOUNT_TWO_ID FROM game_match) t1

PlayerID | MATCHES_WON | MATCHES_LOST
-------: | ----------: | -----------:
       0 |           4 |            1
       1 |           2 |            5
       2 |           2 |            2

db<>fiddle here


Help with understanding programming time/difficulty - SQL Data dump vs SSIS Packaged report I manage a team of data reporting analysts.  One of our main sources of data is run by another segment.  It is a SQL database, but they will not allow direct access to the data.

We have 3 csv files that are run automatically every evening that provide us with the bulk of the data we need to run reports. Recently new fields were added to the database and we need access to the information in those fields.

The original plan was that they would add the new fields to our existing data dump files or provide us with an additional data dump file with the new information.

Today, I was informed that they do not have the capacity to create or modify the data dump files, but that they can provide SSIS reporting, allowing us to open the front end of the database and export the data.

I need to know if creating an SSIS Reporting package that is exportable from the front end of the database is truly easier and less time consuming to develop than modifying or adding a package that will dump the files into a folder.

The issue I have is that we are attempting to automate all of our work.  Direct access would be the easiest way to automate reporting,  csv files cause some issues with automation, but can be automated.  Having to open the front end database application and log in and then export the data to excel is crippling my ability to automate our processes.

My experience is that one automation or another didn't really make a difference, but it has been a long time since I managed a data collection application.
I would love to hear from anyone who currently works with data collection systems and could provide me with the knowledge needed to either accept this solution or push back with facts and solid information on the differences in creating an automated report or an automated data dump.

The team creating this extract specifically said they have  the capacity to build an "SSIS" report package so the data can be exported from the front end of the database, but they do not have the capacity to build a new data dump with the new data or append the new data to the existing data dump. The new fields are all associated with the user ID.

They will not allow me access, but if I were creating a database with normalized tables this would be a straight dump of the user data joined to the supervisor table - providing historical supervisor effective and end dates. When you create a data dump or an extract executed from the front end of the database, would you use the same SSIS package - the difference being that one is scheduled and one is running on live data on demand?

I've been working with SQL Server for about 17 years. If I need to extract data on a repeating basis, I will almost always do it with an SSIS package.

At the risk of ticking off your SQL guys:

SSIS packages can be executed on a schedule (either using SQL Server jobs, or Windows scheduler if necessary). they are my first choice for automated data extraction.
SSIS can output data to CSV files, Excel files, or can even move it directly to other databases.
SSIS packages can be configurable, allowing changes to certain parameters without having to change the actual package.
For that matter, SSIS package files (*.dtsx) are actually XML files, allowing for certain changes to be made with a text editor (like, changing the name of a variable everywhere in the package); not that this is a feature that would be likely to help you, but....

Obviously, I can't tell you why adding a few additional fields to an existing export routine is outside their capacity right now. In most cases, I wouldn't expect adding a few additional fields to an existing extract process to be a major issue or to take much time. However, if (due to the nature of the extract process, or of the additional fields) a change to the format of the data were involved, or if the data required special handling (for example, a "notes" field with embedded tabs, carriage return, or newline characters), more time and effort might be required than you would expect.

It's possible that the problem is less the complexity of the change you need, than the amount of work already on their plate, and its priority level. If letting you get the data for yourself lets them stay on task, then it may be viewed as an acceptable compromise.

You mentioned that the extract you had been using came from the "front end" of the database. Generally, that's some sort of application (whether a compiled .exe, a script of some sort, or something web-based). Modifying an application does take time, and usually requires some degree of testing beyond just the immediate area of change (to make sure that none of the new code inadvertently changes anything outside that area, causing something to break).

If other development work on the application is under way, it can be difficult and time-consuming to bypass that to come out with a minor update to change a report. An SSIS package, being a standalone entity, is less likely to break anything else (as long as you aren't changing data), and thus can be developed and stood up without interrupting or interfering with other work.

NOTE: I'm interpreting the difference between a "data dump" and a "report" as being a matter of formatting. There are some things you can do to (exporting to a preformatted Excel template) that allow a certain amount of formatting, and some more complicated things (adding the appropriate HTML tags to your data to actually create an HTML file, instead of a CSV file or some such). However, it may often be easier (and faster, in the short- and even mid-term) to apply formatting after the fact.

Another NOTE: While this may have improved in later versions of SSIS, there are some issues when working with Excel files. These tend to be worst when importing data from them, but some of the issues (default limit of 255 characters per cell (unless one of the first eight rows has more than that), for example) do apply to exports. I've always tried to keep my life simpler by using .CSV files instead of actual .XLS files, for input or output.

For a simple one-off data extract, I would probably put a query together in SQL Server Management Studio, and copy the results to a spreadsheet of save them to a CSV file. For anything that will be repeating on a regular basis, I'll turn it into an SSIS package.  Either way, I'm hitting "live" data.

It's also possible to run an SSIS package from the command line (dtexec.exe), or with a simple, direct user interface (dtexecui.exe), rather than having to open it up in SQL Server Data Tools (or Business Intelligence Development Studio, depending on your SQL version). So, an SSIS package can be scheduled or "on demand". Whether the data is live or not depends on the database you're querying, not on SSIS itself.


PowerShell: Test-SqlAvailabilityReplica running script never completes I'm still fairly new to PowerShell so I'm unsure of all the troubleshooting steps I can take to solve this problem. I would like to use the below command to determine if the replica is primary or not.

I believe I have successfully loaded both the provider and smo. Intellisense appears to recognize the command.

If I run the following command

Test-SqlAvailabilityReplica

The script will run but will never finish. I'm running it on one of the replicas so I'm assuming it should just be grabbing the local host. If I add the -WhatIf flag it still runs without finishing.

I've also tried Test-SqlAvailabilityGroup and it never finishes either.

What can I do to get this command to work? How do I find out what is causing it to lock up?

EDIT:
It looks like my issue is with the path.... This path location works

SQLSERVER:\Sql\FooHostname\FooInstance\AvailabilityGroups\FooAg\AvailabilityReplicas\

but then I try using this path location and it fails.

SQLSERVER:\Sql\FooHostname\FooInstance\AvailabilityGroups\FooAg\AvailabilityReplicas\MainReplica

Edit2:

Are we sure it's

<ServerName>

at the end? I can set my location to

SQLSERVER:\Sql\FooHostname\FooInstance\AvailabiltyGroups\FooAg\AvailabilityReplicas\

just fine. When I do a DIR to see what is available I see my two replicas listed as

"FooNode1\FooInstance"

and

"FooNode2\FooInstance"

So for the "MainReplica" part of the path do I replace that with FooNode1 or FooNode1\FooInstance?

The commands around Availability Groups for SQLPS require you to pass in the provider path for the replica, or server itself (if you are building the AG).

As the example in the help documentation shows:

Test-SqlAvailabilityReplica -Path "SQLSERVER:\Sql\<ServerName>\<InstanceName>\AvailabilityGroups\<AG Name>\AvailabilityReplicas\*<SpecialCase>*"

If you are using a named instance change "DEFAULT" to the name of the instance.

So after some testing I found out the replica name is a special case if you are dealing with named instances. If you are dealing with a named instance you cannot just use:
    "SQLSERVER:\Sql\MyServer\MyInstance\AvailabilityGroups\MyAG\AvailabilityReplicas\MyServer\MyInstance"

You will get path not found errors...here is a kick on how to fix it.

I happened to issue this command against a named instance with an AG:

dir "SQLSERVER:\Sql\MyServer\MyInstance\AvailabilityGroups\MyAG\AvailabilityReplicas\" | select *

The PSChildName and other references to the replica names show that the backslash is being presented as the ASCII hex equivalent value of "5C". So to work with named instances you have to use this path format:

"SQLSERVER:\Sql\MyServer\MyInstance\AvailabilityGroups\MyAG\AvailabilityReplicas\MyServer%5CMyInstance"


MySQL is inserting "" as 0 in decimal fields. How to stop that? The fields are nullable, so they do allow nulls.

Why would MySQL convert "" to 0 instead of NULL?

Is there a way to force it to be NULL instead?

This seems be an on-going bug although it is not considered a bug, such as Bug #73041: Error 1366 when loading null into columns with decimal type using strict mode

According to Oracle's MySQL Documentation

  If you are not using strict mode, then whenever you insert an “incorrect” value into a column, such as a NULL into a NOT NULL column or a too-large numeric value into a numeric column, MySQL sets the column to the “best possible value” instead of producing an error:

  The following rules describe in more detail how this works:

  If you try to store an out of range value into a numeric column, MySQL Server instead stores zero, the smallest possible value, or the largest possible value, whichever is closest to the invalid value.

  For strings, MySQL stores either the empty string or as much of the string as can be stored in the column.

  If you try to store a string that does not start with a number into a numeric column, MySQL Server stores 0.

That's also in the the regular MySQL Documentation.

With rules like these, that's why a zero(0) pops up for an unspecified DECIMAL value.

If you look back at the bug report, someone got around it when using \N for null values while loading a CSV file with LOAD DATA INFILE.


SQL where clause effect on table? I have sql query as

select sourceIP, sum(sourceBytes)
from flows
group by sourceIP
order by sum(sourceBytes) desc

This brings result (dummy) as:-

sourceIp     SourceBytes
192.168.1.2  100
192.168.1.3  79
192.168.1.4  67
192.168.1.5  4
192.168.1.6  4

Now if I change the query to

select sourceIP, sum(sourceBytes)
from flows
where sourceBytes > 50
group by sourceIP
order by sum(sourceBytes) desc

The output be

 sourceIp        SourceBytes
 192.168.1.2     150
 192.168.1.3     40

I don't have access to DB right now, I cannot pull /show real table values, but the point here I want to make with the greater then statement the output is changed. I was of the view with second query I just want to process the results instead of all the values present flows table to just values which are greater range i.e 50. I want to know what level these two queries are not the same. Thanks.

You are doing a sum() which means you "lose" the original values -- they are aggregated.

By applying a where clause, you filter rows before the aggregation phase.

I suspect what you want is a having clause, something like:

select sourceIP, sum(sourceBytes)
from flows
group by sourceIP
having sum(sourceBytes) > 50
order by sum(sourceBytes) desc;

Having is like a where but applied to the aggregated results.

Note that you can also use a subquery and a where instead of having as follows:

select * from (
  select sourceIP, sum(sourceBytes) as sum_sourceBytes
  from flows
  group by sourceIP
) a
where sum_sourceBytes > 50
order by sum_sourceBytes desc;


ODBC Driver tracing not capturing any logparser events I have enabled Driver Tracing using these directions. I am running SQL Server 2014, which uses the 2012 native driver.

I run the start batch file then the following LogParser command:

  "c:\Program Files (x86)\Log Parser 2.2\LogParser.exe" "SELECT SUBSTR(Field1, 0, 2) AS CountryCode, SUBSTR(TO_STRING(Field2), 0, 5) AS Zip INTO ZipCodes  FROM 'c:\Users\jdearing\Downloads\us.txt'" -i:TSV -headerrow:OFF -o:SQL -server . -database LogParserTest -cleartable -transactionRowCount:0  -ignoreMinWarns:OFF

This inserts zero rows. instead producing this error for every row:

An error occurred while uploading data to SQL table
  SQL State:     22001
  Native Error:  0
  Error Message: [Microsoft][ODBC SQL Server Driver]String data, right
  truncation

Upon stopping the trace the etl is 1024k, but has no rows according to this logparser query:

  "c:\Program Files (x86)\Log Parser 2.2\LogParser.exe" "SELECT * FROM 'SnacTrace_000001.etl'" -i:ETW

I noticed a disparity in the GUIDS in the providers.txt batch file and the output of logman query providers | findstr SQL

echo {BA798F36-2325-EC5B-ECF8-76958A2AF9B5} 0xFFFFFFFF 128 SQLNCLI > providers.txt
echo {A9377239-477A-DD22-6E21-75912A95FD08} 0xFFFFFFFF 128 SQLNCLI10 >> providers.txt
echo {2DA81B52-908E-7DB6-EF81-76856BB47C4F} 0xFFFFFFFF 128 SQLNCLI11 >> providers.txt

Versus

SQLNCLI.1                                {BA798F36-2325-EC5B-ECF8-76958A2AF9B5}
SQLSERVER.09                             {AB6D5EEB-0132-74AB-C5F5-B23E1644DADA}
SQLSERVER.10                             {48D59D84-105B-00FA-6B49-03462F696737}
SQLSERVER.11                             {B2A28C42-A7C2-1563-97CC-3BE49FDA19F9}

So I updated the batch file to:

echo {BA798F36-2325-EC5B-ECF8-76958A2AF9B5} 0xFFFFFFFF 128 SQLNCLI > providers.txt
echo {48D59D84-105B-00FA-6B49-03462F696737} 0xFFFFFFFF 128 SQLNCLI10 >> providers.txt
echo {B2A28C42-A7C2-1563-97CC-3BE49FDA19F9} 0xFFFFFFFF 128 SQLNCLI11 >> providers.txt

Still no luck. What else can I try? Is logparser not using the native client?

Try specifying the -driver option explicitly, providing the name of the native client.


'sp_releaseschemalock' causing me a blocking issue? why 'sp_releaseschemalock' causing me a blocking issue?
what proccess that must execute sp_releaseschemalock or what function of sp_releaseschemalock?

I have searched various forums or blogs about the explanation of the sp_releaseschemalock but have not found a proper explanation. what sp_releaseschemalock do?

sp_releaseschemalock is an undocumented external proc which simply releases the schema lock:

As per our past experience on this, we witnessed the same but never had the issue with sp_releaseschemalock being a culprit.

Kindly check the complete details when checking the blocking. That is ,you should not completely ignore the one that is getting blocked rather than completely dependent on blkd_by_spid, as blkd_by shows sp_releaseschemalock as culprit.

Also would like you to refer this

  You should check your linked server configurations and make sure they
  are "collation compatible" checked. Otherwise the entire remote table
  is returned to the local calling server to execute the WHERE clause.

As mentioned, you need to go through the settings for the query getting executed via linked server because the primary server wont have the required statistical information when fetching the same from other server through its linked query.


Background
The current SQL Server model only uses single-column histograms and multi-column density information. Single column histograms are used to estimate selectivity for suitable predicates e.g. a = 1 or b > 50. A query with multiple predicates simply combines the individual selectivities (with assumptions) to produce an estimated overall selectivity.
For an example, see my article Cardinality Estimation: Combining Density Statistics
Multi-column density further informs the model by providing weak correlation information for multiple equality predicates and grouping cardinalities for aggregations.
Statistics associated with indexes are an opportunistic add-on to that model: The engine might as well collect (normally full scan) statistics while it is building an index. SQL Server automatically constructs a leading-column histogram and density information for the other keys.
Histograms for non-leading columns in an index may be constructed on demand automatically by the query processor, or in advance using sp_createstats with the @indexonly option (among others).
Multi-column histograms
The assumptions made when combining single-column statistics (as above) may or may not model the reality of the data well enough. In many cases, the options available (exponential backoff, independence, minimum selectivity) produce a 'good enough' estimate.
We also have filtered statistics (and indexes) as a natural solution to low-cardinality leading column indexes such as in the question example. Taking these to the logical extreme leads us closer to the multi-dimensional statistics that the question is not about.
When the available modelling options cannot provide a suitable estimate, a multi-column statistic histogram could indeed give a better selectivity estimate for suitable index predicates, in some cases. There are some difficulties around combining different data types in different columns, but nothing insurmountable.
We would also need a histogram for each level of the index keys (for best results); so for an index on (a, b, c) that would mean histograms on (a, b) and (a, b, c) in addition to the current single-column histogram on (a) alone.
The mechanism used to detect stale statistics would also need to be modified to maintain affected multi-column histograms. These histograms would likely end up being rebuilt more often than single-column statistics, simply because modifications to more columns affect them.
All this adds size, complexity, and maintenance overhead.
Multi-column statistics can be simulated (to a limited extent) using a statistic created on a carefully constructed computed column referencing multiple columns. The query would need to include a predicate on the computed column (or an exact textual match for the underlying formula) to take advantage of that statistic. There are probably only very limited situations where this approach is practical. Nevertheless, it has some of the same implementation issues as automatic multi-column histograms would.
Ultimately, the only people that could say for sure why SQL Server does not support multi-column statistics would be the designers themselves. If you feel you can make a strong case for a product improvement in this area with wide applicability, you could suggest it on Connect or via your normal support channel.
Footnote

In this case, it makes the whole histogram a lot less useful, because the first column only ever has two values

The histogram still provides useful information about the distribution of values in the leading column: When the statistics were built, there were 24,398 rows where IsArchived was false, and 216,602 rows where is was true.
In addition, the statistics object tells us there are (1 / 0.5) = 2 distinct values for IsArchived, (1 / 4.149378E-06) ~= 241000 distinct values for (IsArchived, Mystery) with an average row size of 37 bytes, and there same frequency for (IsArchived, Mystery, Id) with 4 extra bytes per row.
That's all good general-purpose information, which can be combined with statistical information about other columns to produce a selectivity estimate in queries with multiple predicates (as mentioned).


Reconcile users with logins using 'sp_change_users_login' Sql Server 2008 (and 2005):

I have a Sql Server instance, let's say, InstA, in a Domain (called AD). In that I have a domain Windows Authentication Login, called AD\Log1 that has an associated user, User1 in a database called DB1.

So, now I backup this database and restore on a new instance (Inst2) that is not part of any domain. It has an existing login (LocalMachine\Log2) that I need to have mapped to the existing User1 in the newly restored database DB1.

With sp_change_users_login, I get the following error:

EXEC sp_change_users_login 'Update_One', 'User1', 'LocalMachine\Log2'

Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 114
Terminating this procedure. The User name 'User1' is absent or invalid.

If I use ALTER USER User1 WITH LOGIN = 'LocalMachine\Log2', it renames the user User1 to LocalMachine\Log2 and maps it to LocalMachine\Log2 login. It works, but I do not want it to rename.

I would prefer to map this existing database user User1 in the database DB1 to an existing login in the new instance inst2 without changing the user name or creating a new login (AutoFix). Just update the SID and map it (without other changes).

Is it possible?

What I gathered so far is, for my situation, sp_change_users_login does not work because LocalMachine\Log1 is a Windows Account (not a Sql Login). but if both were Sql Logins, sp_change_users_login with work perfectly.

Thanks,

When you run sp_change_users_login, it automatically renames users as explained in Books Online:

  The name of the user will be automatically renamed to the login name
  if the following conditions are true. The user is a Windows user. The
  name is a Windows name (contains a backslash). No new name was
  specified. The current name differs from the login name.

To keep life easy, I'd keep the same usernames on both servers and then you should be fine with sp_change_users_login.


Does number of backup tables hinder performance Our main database (MySQL) has about 50 tables with about 2 million records each, many of the columns are indexed.
Before doing any major change, we either copy the entire DB under a new name like "DB_MAIN_Nov112018" and just leave it there as an online backup/copy or we copy individual tables and use the same principle.
Does this hurt the performance of the database server as it has to do the indexing of the original DB as well as the many tables and databases that are used as backups?

As long as the database and its tables and many indexes are just sitting there, there is essentially no harm:

The extra db and tables have no impact on your ordinary queries.
Some meta operation involving the information_schema will notice them.
The copy takes disk space.  (So would a dump.)

An advantage over a dump:  You can quickly perform a query on the old copy.


Enforce uniqueness across optional attributes for an entity I have an entity that has multiple attributes, and these attributes are themselves entities.

No two entities may have the same attributes, so there needs to be a uniqueness constraint across them. But at the same time, not all attributes are required.

The following DML illustrates this where attr_a and attr_b are not optional (do not allow NULL) whilst attr_c and attr_d are optional (do allow NULL):

CREATE TABLE attr_a (
  attr_a_id INTEGER(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT
) ENGINE InnoDB;
CREATE TABLE attr_b (
  attr_b_id INTEGER(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT
) ENGINE InnoDB;
CREATE TABLE attr_c (
  attr_c_id INTEGER(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT
) ENGINE InnoDB;
CREATE TABLE attr_d (
  attr_d_id INTEGER(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT
) ENGINE InnoDB;

CREATE TABLE entity (
  entity_id INT(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
  attr_a_id INT(10) UNSIGNED NOT NULL,
  attr_b_id INT(10) UNSIGNED NOT NULL,
  attr_c_id INT(10) UNSIGNED NULL,
  attr_d_id INT(10) UNSIGNED NULL,
  UNIQUE KEY uq_attr (attr_a_id, attr_b_id, attr_c_id, attr_d_id),
  KEY `fk_attr_a_id` (attr_a_id),
  KEY `fk_attr_b_id` (attr_b_id),
  KEY `fk_attr_c_id` (attr_c_id),
  KEY `fk_attr_d_id` (attr_d_id),
  CONSTRAINT `fk_attr_a_id` FOREIGN KEY (attr_a_id) REFERENCES `attr_a` (attr_a_id),
  CONSTRAINT `fk_attr_b_id` FOREIGN KEY (attr_b_id) REFERENCES `attr_b` (attr_b_id),
  CONSTRAINT `fk_attr_c_id` FOREIGN KEY (attr_c_id) REFERENCES `attr_c` (attr_c_id),
  CONSTRAINT `fk_attr_d_id` FOREIGN KEY (attr_d_id) REFERENCES `attr_d` (attr_d_id)
) ENGINE InnoDB;

So the intention is that these two rows from the entity table should not be allowed:

1,10,20,30,NULL
2,10,20,30,NULL

The following pairs of rows should be allowed, as though nulls were actually values (I understand the BDB engine would allow this, but I will be using InnoDB.)

1,10,20,30,NULL
2,10,20,NULL,NULL

1,10,20,30,40
2,10,20,NULL,NULL

1,10,20,30,NULL
2,10,20,NULL,40

The tables described above is obviously flawed, because of how NULLs are handled with a uniqueness constraint -- it would allow those two rows to exist.

In the case of an entity, attr_c and attr_d are optional rather than unknown. So I think allowing NULLs is not technically correct because the value is known to be undefined, rather than unknown.

I think the only practical solution is to reserve a item in each of the attribute tables for "undefined" (possibly with ID 0.)

I know this solution would work, but I want to avoid the mistake of adopting a known anti-pattern.

There isn't an easy way (except in SQL Server, see below) to enforce these constraints. And I say "these" and not "this" because they are indeed more than one.

You want to enforce uniqueness on (a,b,c,d) when all the attributes are not null. And uniqueness on (a,b,c) when d is null. And uniqueness on (a,b,d) when c is null. And uniqueness on (a,b) when both c and d are null.

You say that NULL value on c or d means that the value is known to be undefined, yet you allow both (10, 20, 30, 40) and (10, 20, NULL, NULL). Is the value defined or known to be undefined in this case?

Anyway, to actually enforce these rules, there are various options:

If you want to keep the data in a single table and you implement in a DBMS that have filtered/partial indexes (PostgreSQL, SQL Server), you can create 4 UNIQUE partial indexes:

UNIQUE (a, b, c, d) WHERE (c IS NOT NULL AND d IS NOT NULL)
UNIQUE (a, b, c)    WHERE (c IS NOT NULL AND d IS NULL)
UNIQUE (a, b, d)    WHERE (c IS NULL     AND d IS NOT NULL)
UNIQUE (a, b)       WHERE (c IS NULL     AND d IS NULL)

Some other DBMS (like Oracle and DB2) do not have partial indexes but they can be emulated, with different techniques (see the Use the Index Luke! website: DB2, "Emulating partial indexes is possible)

Specifically in SQL Server - and only in this DBMS, because it treats NULL values in regard to UNIQUE constraints differently than all the rest and not according to the standard - we don't really need all the 4 constraints and we can actually get your desired behaviour with what you already have, a simple UNIQUE constraint on (a,b,c,d).

It will do exactly what your rules want to enforce. This does not comply with the SQL standard and there is a small chance that the behaviour will change in future releases (unlikely I'd add, as DBMS try very hard for backwards compatibility) .

Another (similar to the partial unique index) option (kudos to @Erwin, see his answer) is to use a functional index. This could be used in PostgreSQL, Oracle and DB2 that have such indexes.

A variation is to use computed columns (for attributes c and d) and add a unique index based on the four columns (a, b, coalesced_c, coalesced_d). This feature is available in SQL Server but also in MariaDB 5.3+ (which is a MySQL variant) and in the latest MySQL version (5.7), too.

In older MySQL versions (which seems like the DBMS you use) and other ones (like SQLite) that do not have partial or functional indexes, if you want to keep only one table, I think the only option is to do what you considered:

Use a value (like 0 or -1 that can't appear in your data) as a substitute for "known to be undefined".

This would of course work in any DBMS.

And last, what you could also use in any DBMS (assuming it supports usual FOREIGN KEY and UNIQUE constraints) is to properly normalize the entity table into four ones, one for each case:

CREATE TABLE entity_ab (
  attr_a_id INT NOT NULL,
  attr_b_id INT NOT NULL,
  CONSTRAINT uq_attr_ab UNIQUE (attr_a_id, attr_b_id),
  CONSTRAINT fk_ab_attr_a_id FOREIGN KEY (attr_a_id) REFERENCES attr_a (attr_a_id),
  CONSTRAINT fk_ab_attr_b_id FOREIGN KEY (attr_b_id) REFERENCES attr_b (attr_b_id)
) ;

CREATE TABLE entity_abc (
  attr_a_id INT NOT NULL,
  attr_b_id INT NOT NULL,
  attr_c_id INT NOT NULL,
  CONSTRAINT uq_attr_abc UNIQUE (attr_a_id, attr_b_id, attr_c_id),
  CONSTRAINT fk_abc_attr_a_id FOREIGN KEY (attr_a_id) REFERENCES attr_a (attr_a_id),
  CONSTRAINT fk_abc_attr_b_id FOREIGN KEY (attr_b_id) REFERENCES attr_b (attr_b_id),
  CONSTRAINT fk_abc_attr_c_id FOREIGN KEY (attr_c_id) REFERENCES attr_c (attr_c_id)
) ;

CREATE TABLE entity_abd (
  attr_a_id INT NOT NULL,
  attr_b_id INT NOT NULL,
  attr_d_id INT NOT NULL,
  CONSTRAINT uq_attr_abd UNIQUE (attr_a_id, attr_b_id, attr_d_id),
  CONSTRAINT fk_abd_attr_a_id FOREIGN KEY (attr_a_id) REFERENCES attr_a (attr_a_id),
  CONSTRAINT fk_abd_attr_b_id FOREIGN KEY (attr_b_id) REFERENCES attr_b (attr_b_id),
  CONSTRAINT fk_abd_attr_d_id FOREIGN KEY (attr_d_id) REFERENCES attr_d (attr_d_id)
) ;

CREATE TABLE entity_abcd (
  attr_a_id INT NOT NULL,
  attr_b_id INT NOT NULL,
  attr_c_id INT NOT NULL,
  attr_d_id INT NOT NULL,
  CONSTRAINT uq_attr_abcd UNIQUE (attr_a_id, attr_b_id, attr_c_id, attr_d_id),
  CONSTRAINT fk_abcd_attr_a_id FOREIGN KEY (attr_a_id) REFERENCES attr_a (attr_a_id),
  CONSTRAINT fk_abcd_attr_b_id FOREIGN KEY (attr_b_id) REFERENCES attr_b (attr_b_id),
  CONSTRAINT fk_abcd_attr_c_id FOREIGN KEY (attr_c_id) REFERENCES attr_c (attr_c_id),
  CONSTRAINT fk_abcd_attr_d_id FOREIGN KEY (attr_d_id) REFERENCES attr_d (attr_d_id)
) ;

then the entity table would become a VIEW:

CREATE VIEW entity AS
        SELECT attr_a_id, attr_b_id, attr_c_id, attr_d_id
        FROM entity_abcd
    UNION ALL
        SELECT attr_a_id, attr_b_id, attr_c_id, NULL
        FROM entity_abc
    UNION ALL
        SELECT attr_a_id, attr_b_id, NULL, attr_d_id
        FROM entity_abd
    UNION ALL
        SELECT attr_a_id, attr_b_id, NULL, NULL
        FROM entity_ab ;


Get data from two tables with recursive relationships I have a MySQL 5.1 DB, being used behind a PHP application to track permissions and other items that need to be issued to staff that hold various positions within an org. To track what privileges a position has, I'm using this table:

mysql> desc position_privs;
+--------------+---------+------+-----+---------+-------+
| Field        | Type    | Null | Key | Default | Extra |
+--------------+---------+------+-----+---------+-------+
| position     | int(11) | NO   |     | NULL    |       |
| priv         | int(11) | NO   |     | NULL    |       |
| date_added   | int(11) | NO   |     | 0       |       |
| date_removed | int(11) | YES  |     | NULL    |       |
+--------------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)

position refers to an id column in a positions table, and priv refers to an id column in a privs table. Some positions also inherit privileges from other positions, which may in turn inherit from yet another position, etc. I have this table for that:

mysql> desc position_inherits;
+--------------------+---------+------+-----+---------+-------+
| Field              | Type    | Null | Key | Default | Extra |
+--------------------+---------+------+-----+---------+-------+
| position           | int(11) | NO   |     | NULL    |       |
| inherit_privs_from | int(11) | NO   |     | NULL    |       |
| date_added         | int(11) | NO   |     | 0       |       |
| date_removed       | int(11) | YES  |     | NULL    |       |
+--------------------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)

If the inheritances were always only one level deep, I could do this with a simple JOIN, but I'm stumped as to handling positions that inherit from a position, which in turn inherits from another position.

Is there a convenient way of doing this using MySQL, or should I just stick to SELECT * FROM $table and doing it in the application code instead?

BAD NEWS

MySQL does not support recursive data or table structures. Neither does it support recursive SQL.

GOOD NEWS

There is a hope. I have written some stored functions on how to retrieve hierarchies of data

Oct 24, 2011 : Find highest level of a hierarchical field: with vs without CTEs
Dec 10, 2012 : MySQL: Tree-Hierarchical query

CAVEAT

You would have to implement this against your tables.
You also need to make sure you are entering data as a Tree and not as a Graph.

https://en.wikipedia.org/wiki/Tree_(data_structure)
http://wiki.answers.com/Q/DIFFERENCE_between_GRAPH_and_TREE_IN_DATA_STRUCTURES
https://stackoverflow.com/a/7707983/491757

For any row in the position_inherits table that does not have a parent, simply set that row's inherit_privs_from to zero(0). That way, you have establish root positions.


What file formats can be restored using pg_restore on PostgreSQL? first question here, fairly new to DB's and Data itself.

My question is fairly simple, after playing around with Postgres, PGAdmin3, I realised I would like to access some public data, but I struggled to find any publicly available .tar files to practice with. Leading me to wonder what file formats you can restore from.

What file formats can I restore from using pg_restore? And is there a preference in the community towards certain formats?

Thanks for your time. Look forward to participating on the forum.

PS: If it wasn't clear, I am restoring from command line.

Exactly one file format is supported by pg_restore: the custom PostgreSQL dump formats from pg_dump -F c (custom), -F d (directory) and -F t (tar). Notably it cannot restore -F s (SQL) format dumps, which must be passed to psql -f instead.

If you want to load data, you probably want the \copy command from psql or the server-backend COPY command. It can load a variety of CSV-like data.

There are also external loader and ETL tools that can do more, e.g. pgloader, Talend Studio, Pentaho Kettle, CloverETL, etc.


Should I use MyISAM over InnoDB to save disk space? I have a table that consists of an int primary key and text column and serving primarily in a "read" role. There are currently only about 40,000 rows, but in time, I expect there to be an additional 500,000 rows per year. This is on a system with perhaps 10 active users per day, so very low-impact.
With MyISAM, the table is less than 4MB. With InnoDB, it is over 7MB.
If disk space is a concern, is MyISAM really so untrustworthy that I should use InnoDB over MyISAM?

You're worried about 3MB? er- don't.
Even at 10x that, roughly your expected increase, it would be 30M.
You aren't going to get saving of 30M of storage to counter any reliability failure.
Use InnoDB.


Would using SELECT FOR UPDATE and a subsequent INSERT guarantee uniqueness in text column? I have a table with a text column that I need to be unique. Because I can't put a unique constraint on the column in MySQL, I want to know if the following approach would guarantee uniqueness in the column:

Create a varchar column for holding the hash of the unique text column. Put a plain INDEX on it.
Start transaction
Use SELECT FOR UPDATE to see if hash exists
a) Use a subsequent INSERT if hash doesn't exist

b) If hash exists, SELECT by unique text to see if it exists. If unique text does not exist, do a subsequent INSERT, otherwise do 'UPDATE'
End transaction

If I use the above approach for all inserts in that table, can that guarantee uniqueness?

Yes, that should work.  However, consider either of these one-liners:

INSERT IGNORE ...

INSERT ... ON DUPLICATE KEY UPDATE ...

If you are using AUTO_INCREMENT, be cautious about 'burning' ids.

Links:

https://stackoverflow.com/questions/24616315/how-to-use-insert-on-duplicate/24617186
https://stackoverflow.com/documentation/mysql/866/insert


Azure SQL query never completes I am having an issue with a SQL query that runs and runs but never completes.  The database is an Azure SQL database.

My table has tens of millions of rows and looks like this:

CREATE TABLE [dbo].[MyData](
[CustomerId] [int] NOT NULL,
[TagName] [nvarchar](100) NOT NULL,
[TagValue] [real] NULL,
[TimeStamp] [datetime2](7) NOT NULL,
[status] [int] NULL,
CONSTRAINT [PK_MyData] PRIMARY KEY CLUSTERED
(
  [CustomerId] ASC,
  [TagName] ASC,
  [TimeStamp] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

My select statement looks like this:

DECLARE @StartDate as datetime
DECLARE @EndDate as datetime

Select @StartDate = LastReadDT from MyData_LastDT_Read
Select @EndDate = dateadd(minute,30,@StartDate)

SELECT   CAST([TagName] as varchar(100)) as [tag],
         CAST(isnull(TagValue, 0) as real) as tagvalue,
         CAST([TimeStamp] as datetime) as [DataTimeStamp],
         CAST(192 as int) as [status]
FROM     [dbo].[MyData]
where    CustomerID = 1
and      TimeStamp <= @EndDate
and      TimeStamp > @StartDate
order by TimeStamp asc

The strange thing is if I remove the @StartDate and @EndDate variables and instead just hard-code the actual TimeStamp values in the where clause, the query will return results in less than 1 second.  Why would hard-coding the TimeStamp values in the query make such a dramatic difference compared to the variables?  Any suggestions on how to improve the performance?  Unfortunately I need to use the variables as this query is executed repeatedly with different time ranges.

This has a scent of a parameter sniffing problem.  See what I did there?  Punny, right?

Anyway, Paul White, wrote up a great article on the various approaches available to resolve this issue, so I'm not going to go into detail when you should just read his article instead.  What you can try quickly though, because this is an ad-hoc statement, is to force a recompile using the OPTION (RECOMPILE) hint.  If this fixes your issue, you definitely are running into what I suspect.  However the best way to properly resolve this is to convert your statement into a Stored Procedure and then execute the Stored Procedure passing in the date parameters.  A parameterized Stored Procedure won't fall prey to the current issue and should be reusable as well.


restore postgres BBDD - message "already exists" I do a pg_dump from RDS, this dump need to restore en local develop context
psql -U postgres -d devDDB -f /tmp/prodDDBB.sql

and too many errors jump, like:
psql:/tmp/prodDDBB.sql:123255: ERROR:  duplicate key value violates unique constraint "operation_keys_pkey"
DETAIL:  Key (id)=(1088) already exists.

psql:/tmp/prodDDBB.sql:226882: ERROR:  relation "brands_vehicles_account_index" already exists

this is normal on a restore operation? the database already exist, i guess this s normal.

use
-c
--clean

Output commands to clean (drop) database objects prior to outputting the commands for creating them. (Unless --if-exists is also specified, restore might generate some harmless error messages, if any objects were not present in the destination database.)

This option is only meaningful for the plain-text format. For the archive formats, you can specify the option when you call pg_restore.

see manual
so that all elemenst are dropped before you restore the database.
in your case use pg_restore with the same parameter, that has the same effect


how to enable temporal database capability in mysql.? How to enable the temporal database compatibility in mysql database?

Is there any library or a plug-in to enable the temporal upward compatibility? I want to keep the past records whenever a current record changes.

Add a from_date field, and a nullable to_date field
expand your primary key to include from_date
keep everything in UTC
consider using PostgreSQL as there is a temporal extension for preventing overlapping time periods.

Here is the book from "THE" temporal db guy (Snodgrass):

http://www.cs.arizona.edu/~rts/tdbbook.pdf

Also try Chapter 4 of Celko's Smarties book


PDB state in Oracle 12c RAC Installed two node Oracle RAC. On node 1 opened pdb1 and shows open mode as read and write but on node 2 it's still in mounted state?.

On Node1

   rlwrap sqlplus / as sysdba

        SQL*Plus: Release 19.0.0.0.0 - Production on Tue May 14 04:29:47 2019
        Version 19.3.0.0.0

        Copyright (c) 1982, 2019, Oracle.  All rights reserved.

        Connected to:
        Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
        Version 19.3.0.0.0

        SQL> show pdbs

            CON_ID CON_NAME                       OPEN MODE  RESTRICTED
        ---------- ------------------------------ ---------- ----------
                 2 PDB$SEED                       READ ONLY  NO
                 3 PDB1                           READ WRITE NO

        SQL> select CON_ID,DBID,NAME,OPEN_MODE,RESTRICTED from v$pdbs;

        CON_ID        DBID NAME       OPEN_MODE  RES
        ------ ----------- ---------- ---------- ---
             2  1968208496 PDB$SEED   READ ONLY  NO
             3  1212646563 PDB1       READ WRITE NO

SQL> select instance_number,instance_name,instance_mode from v$instance;

INSTANCE_NUMBER INSTANCE_NAME    INSTANCE_MO
--------------- ---------------- -----------
              1 cdbrac1          REGULAR

On Node2

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED

SQL> select CON_ID,DBID,NAME,OPEN_MODE,RESTRICTED from v$pdbs;

CON_ID            DBID NAME            OPEN_MODE  RES
------ --------------- --------------- ---------- ---
     2      1968208496 PDB$SEED        READ ONLY  NO
     3      1212646563 PDB1            MOUNTED

SQL>  select instance_number,instance_name,instance_mode from v$instance;

INSTANCE_NUMBER INSTANCE_NAME    INSTANCE_MO
--------------- ---------------- -----------
              2 cdbrac2          REGULAR

ALTER PLUGGABLE DATABASE

pdb_open::=

instances_clause::=

To open the PDB on all RAC instances:

ALTER PLUGGABLE DATABASE PDB1 OPEN INSTANCES=ALL;


how to Upload xml data to db Table by check primary key in oracle i want to upload xml file data to db table by checking the primary key and wants update the each row data.Here trid getting as a primary key.example

OFF
    1133
    2388977

is their any solution for upload xml to db table by check primary key in oracle ?

You can try using third party tool such as Toad for Oracle. There is a function to import the .xml file into database and of course it will check the primary key before loading the data.


Duration unit in SQL Server trace file I have generated a trace file in SQL Server 2012, not really sure about the unit of duration, it is measured by millisecond?

As CPU is in milliseconds.From the beginning with SQL Server 2005, the server reports the duration of an event in microseconds (one millionth, or 10-6 of a second) and the amount of CPU time used by the event in milliseconds (one thousandth, or 10-3 of a second). Even in SQL Server 2000, the server reported both duration and CPU time in milliseconds. In SQL Server 2005 and later, the SQL Server Profiler graphical user interface displays the Duration column in milliseconds by default, but when a trace is saved to either a file or a database table, the Duration column value is written in microseconds.

For your further ref Interpreting CPU Utilization for Performance Analysis and Here


PostgreSQL function thinks table reference exists when it does not So I run this query:

SELECT f_copy_tbl('parts','parts1');

...while working on code based off of this answer and I get this error message:

  ERROR:  relation "parts1" already exists

pgAdmin III doesn't show the table (and I obviously reloaded the left column pressing F5 after clicking on the correct items because it's of cascading reload effect). Just to cover my bases I run the following query:

DROP TABLE IF EXISTS parts1 RESTRICT;

...to which psql returns:

  NOTICE:  table "parts1" does not exist, skipping

So what is parts1 a reference to if not a table?

How many queries? All the queries:

CREATE OR REPLACE FUNCTION f_copy_tbl(_tbl regclass, _newtbl text)
 RETURNS void AS
$func$
BEGIN

-- Copy table
EXECUTE format('CREATE TABLE %I (LIKE %s INCLUDING ALL);', _newtbl, _tbl);

-- Fix serial columns, if any
EXECUTE (
SELECT concat_ws(E'\n'
       , string_agg('CREATE SEQUENCE ' || seq, E';\n') || ';'
       , string_agg(format('ALTER SEQUENCE %s OWNED BY %I.%I;'
                          , seq, _newtbl, a.attname), E'\n')
       , 'ALTER TABLE ' || quote_ident(_newtbl)
       , string_agg(format($$ALTER %I SET DEFAULT nextval('%s'::regclass)$$
                                 , a.attname, seq), E',\n')
       )
FROM   pg_attribute  a
JOIN   pg_attrdef    ad ON ad.adrelid = a.attrelid
                       AND ad.adnum   = a.attnum
     , quote_ident(_newtbl || '_' || a.attname || '_seq') AS seq  -- new seq name
WHERE  a.attrelid = _tbl
AND    a.attnum > 0
AND    NOT a.attisdropped
AND    a.atttypid = ANY ('{int,int8,int2}'::regtype[])
AND    ad.adsrc = 'nextval('''
         || (pg_get_serial_sequence (a.attrelid::regclass::text, a.attname))::regclass
         || '''::regclass)'
);

--Now populate `parts1` table with `parts` table's content...
SELECT f_copy_tbl('parts','parts1');
INSERT INTO parts1 SELECT * FROM parts;

--Now get primary key so we can fix the sequence...
--DECLARE PKEY VARCHAR;
--SELECT pg_attribute.attname INTO PKEY FROM pg_index, pg_class, pg_attribute WHERE pg_class.oid = 'parts1'::regclass AND indrelid = pg_class.oid AND pg_attribute.attrelid = pg_class.oid AND pg_attribute.attnum = any(pg_index.indkey) AND indisprimary;

--Now
--SELECT setval('parts1_' || INTO PKEY || '_seq', (SELECT MAX(pkey) + 1 FROM parts));

/**********
--Now get highest number of primary key *AND* increment it...
--DECLARE PKEY_NUMBER INTEGER;
--SELECT (PKEY + 1) INTO PKEY_NUMBER FROM parts1 ORDER BY PKEY DESC LIMIT 1;

--Fix primary key's autoincrement...
--ALTER SEQUENCE parts1_id_seq RESTART WITH PKEY_NUMBER;
*********/

END
$func$ LANGUAGE plpgsql VOLATILE;

Your issue is that you have defined a recursive function ... the function is calling itself.  The logic that exists in it currently would result in a stack dump with OUT OF MEMORY error (or something along those lines, at least) if it didn't give you the error of "table already exists" to begin with..

CREATE OR REPLACE FUNCTION f_copy_tbl(_tbl regclass, _newtbl text)
 RETURNS void AS
$func$
BEGIN

--<snip stuff>

--Now populate `parts1` table with `parts` table's content...
SELECT f_copy_tbl('parts','parts1');  --- THIS IS THE OFFENDING LINE HERE .. REMOVE IT
INSERT INTO parts1 SELECT * FROM parts;

--<snip stuff>

END
$func$ LANGUAGE plpgsql VOLATILE;

Your order of SQL should be as such:

CREATE OR REPLACE FUNCTION my_func(param1 VARCHAR, param2 VARCHAR) RETURNS VOID AS $$
DECLARE
  var1 VARCHAR;
BEGIN
  -- DO STUFFS HERE FOR FUNCTION
END;
$$ LANGUAGE plpgsql;
-- THE ABOVE DEFINES THE FUNCTION

-- THE BELOW EXECUTES THE FUNCTION
SELECT my_func('val1', 'val2'); -- THIS ONE LINE WILL EXECUTE ALL SQL DEFINED INSIDE THE FUNCTION

The function definition is what is between the dollar quoting..


Dump all Views of multiple databases in SQL Server I need to export the views from multiple databases of an older server. (Essentially, to generate a list of views using a particular piece of SQL Server)

The fastest way I've found thus far is to manually use the Generate scripts function to export the views to a file.

I feel like there is probably a better way I don't know about.

Thanks in advance,

You will certainly need the right permissions to view the views definition. This should work for you. Please note that I'm using the undocumented sp_MSforeachdb stored procedure. This means that it could change without notice so is not advisable to include as part of your production/live tsql code as it could fail. Check Aaron comment about it.

EXEC sp_MSforeachdb '
    IF DB_ID(''?'') > 4
    BEGIN
        USE [?]
        SELECT DB_NAME()
        SELECT  c.text
            FROM    sysobjects o
                    JOIN syscomments c
                        ON c.id = o.id
            WHERE   o.type = ''V'';
    END'


Large MySQL import stuck at "Waiting for table flush" Two weeks ago (!) I started a large import (dump sql file is 540GB). Now the import is stuck at this state:
"show processlist" shows: Command = "Field List", State="Waiting for table flush".
Looking at the record count for a table I selected randomly, I see zero rows, whereas in the source DB there are millions of rows, so the import is clearly not done. The .ibd file for that table is tiny, whereas on the source it's several GB. For other tables this is not the case, i.e. the .ibd files are very large on the target DB.
The server shows heavy swap activity, with the load being around 5.
Following suggestions I got on this site I started the import with these commands:

set autocommit=0;
set unique_checks=0;
set foreign_key_checks=0;

...and ended the import with these commands:

commit;
set unique_checks=1;
set foreign_key_checks=1;

What is going on now? Must I wait?

I have always found large mysqldumps to be problematic. The best tactic I know is to reduce the size of the import. aka split the database dump into many dumps that let you run a per table restore this will let you make tangible progress and reduce the size and time each import takes.
https://github.com/kedarvj/mysqldumpsplitter


How to configure account to be able to create/delete folders and files? I have a newly created domain account (domainname\user1).
This will be used by SQL server agent job proxy credential to run a SSIS package that creates/deletes files and folders on the same computer as the agent job. So effectively the package will run as though it is the user domainname\user1.
What is the permission that I need to assign to domainname\user1 on the computer? For example: I don't want to assign it to the Local administrator group. Should I add it to the Users group? Or any other group?

You might find a better answer asking this question on ServerFault but in general I believe you'd just have to add that domain user to the Folder Security tab of the root folder where files will be created and deleted. You'll likely want to give that user Read, Write, and Modify permissions in the Security tab. Please see this article for more information.


MySQL import silently failing I'm trying to import a large (30GB) SQL file (generated using mysqldump) into an RDS instance MySQL 5.7 database, from an EC2 instance on the same VPC.
It's importing most without a problem, but missing around 5 tables from the end of the dump.
Looking around for related logs in the AWS console I can't really find much of use, all I'm seeing is this:
2021-02-17T12:16:20.168722Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 10360ms. The settings might not be optimal. (flushed=599 and evicted=179, during the time.)
2021-02-17T12:16:36.461195Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5988ms. The settings might not be optimal. (flushed=490 and evicted=124, during the time.)
2021-02-17T12:16:46.848239Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 6766ms. The settings might not be optimal. (flushed=447 and evicted=0, during the time.)
2021-02-17T12:17:16.371538Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 10380ms. The settings might not be optimal. (flushed=370 and evicted=240, during the time.)
2021-02-17T12:19:22.153786Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 27942ms. The settings might not be optimal. (flushed=299 and evicted=637, during the time.)
2021-02-17T12:21:19.368279Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 16509ms. The settings might not be optimal. (flushed=361 and evicted=136, during the time.)
2021-02-17T12:23:01.420623Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5097ms. The settings might not be optimal. (flushed=349 and evicted=125, during the time.)
2021-02-17T12:23:16.836488Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 8285ms. The settings might not be optimal. (flushed=307 and evicted=121, during the time.)
2021-02-17T12:23:52.965471Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 19104ms. The settings might not be optimal. (flushed=299 and evicted=607, during the time.)
----------------------- END OF LOG ----------------------

I'm not really sure where to start looking without any logs. Only thing I can think of is maybe lack of memory, but looking at the stats it still had "some" memory left on the RDS instance while it was importing.
When I check the file the CREATE TABLE statements are definitely there for the missing tables.
Can anyone give any tips on how I can figure out what's going wrong?

Turned out this was just lack of memory on the chosen RDS instance. Increased it to the "medium" instance instead of "micro" and it imported fine at around 10x the speed, with all tables.


In sql-server according to syntax, who has to go first: DECLARE or SET NOCOUNT ON? In sql-server according to syntax, who has to go first: DECLARE or SET NOCOUNT ON?

The declare statement will not generate a rowcount message so there is no observable difference between the two.


MYSQL Replication _ Master port is 3306 and slave port is 3307 I have set master - slave replication.

Master mysql port = 3306
master server id = 1

Slave mysql port = 3307
slave server id =2

log-bin is enabled on master, and also replication user is
created on master with replication rights.

change master to
master_host = 'masterIP',
master_port=3306,
master_user = 'username',
master_password= 'password',
master_log_file = 'mysql-bin.000037',
master_log_pos = 89265280;

But when I run

Start Slave;

It shows error as below.

Error Code: 1794. Slave is not configured or failed to initialize properly. You must at least set --server-id to enable either a master or a slave.

Can anyone help ?

Master mysql port = 3306
master server id = 1
mysql vesrion 5.5.50

Slave mysql port = 3307
slave server id =2
mysql version 5.5.50

I have tested the above scenario with Master port 3306 and slave 3307. And it's working fine. I have followed the steps you have given in the question.

As you have mentioned in the comment.I think it compatibility issue.You can check with the similar version.

 mysql master version = 5.7
 mysql slave version = 5.6

  Statement-based replication depends on compatibility at the SQL level between the master and slave. In others, successful SBR requires that any SQL features used be supported by both the master and the slave servers. For example, if you use a feature on the master server that is available only in MySQL 5.7 (or later), you cannot replicate to a slave that uses MySQL 5.6 (or earlier).

http://dev.mysql.com/doc/refman/5.7/en/replication-features.html
http://dev.mysql.com/doc/refman/5.7/en/replication-compatibility.html


Unable to restore database backup because it is accessed by other connection --first command

use master

Go
-- second command
ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Go
-- Third command
RESTORE DATABASE [MyDB] FROM  DISK = N'D:\Restore_Backup\Restore_05042015\AcctDB.bak'
WITH  FILE = 1,
MOVE N' AcctDB '
TO N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\WFStageAcct.mdf',
MOVE N' AcctDB _log'
TO N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\WFStageAcct_log.ldf',
NOUNLOAD,
REPLACE,
STATS = 10

GO

--forth command

ALTER DATABASE MyDB SET MULTI_USER

Go

I run the above commands, one by one to restore database in different server. But sometime I cannot restore the database after changed to single user mode, because it is accessed by other connection. If I run the whole script except the last part together would that block other connection so I will be the only user?

Running this all at once does not guarantee that no one will open a connection between you putting it in single-user mode and you starting the restore. Instead, you can put the database in offline mode rather of single-user mode to avoid this problem.

E.g.

ALTER DATABASE MyDB SET OFFLINE WITH ROLLBACK IMMEDIATE;


Mariadb json store with unicode \u sequences I am using mariadb and I store json data with unicode encoded strings eg \u0395, in a medium text column name remoteData with collation utf8_general_ci.
My problem is where query this column like that
where remoteData like '%ΕΛΕΥΘΕΡΙΟΣ%'
I get an empty set because the value is stored as \uxxxx sequence like this
"patientFirstName":"\u0395\u039b\u0395\u03a5\u0398\u0395\u03a1\u0399\u039f\u03a3"

I have been suggested to use JSON_EXTRACT function like this
WHERE JSON_EXTRACT(`remoteData`, "$.patientFirstName")

but as far as I know this will ignore any index on the column. The table in two weeks has gone to almast 100000 rows and it is growing fast, so I want to be indexed.
What would be the best approach to handle such data?
Should I change from sql starage to no sql like mongo etc?
Thanks in advance.

\u0395 is just 6 characters to MariaDB.  Searching for the 5-character "word" u0395 would succeed.
You need to change the JSON strings to have characters, not unicode codepoint encoding.
In PHP, I would consider write a 1-time script to bring in each line, decode it, and write it back out.
Meanwhile, the writing should be changed to
json_encode($stuff, JSON_UNESCAPED_UNICODE)

(or the equivalent in your app's language).
IntlChar::chr may be part of the solution.
Or:  $utf8string = html_entity_decode(preg_replace("/U\+([0-9A-F]{4})/", "&#x\\1;", $string), ENT_NOQUOTES, 'UTF-8');
Or:  https://stackoverflow.com/questions/6058394/unicode-character-in-php-string
Or:  https://stackoverflow.com/questions/2934563/how-to-decode-unicode-escape-sequences-like-u00ed-to-proper-utf-8-encoded-cha


Best way to store, query and update 300M rows of data I'm struggling to find a solution (preferably DBaaS) that I can rely on for storing and querying some 300M rows of data (roughly 100GB).

The data in question is pretty much numeric. There is also one "description" column that I would want to perform full-text search on. There are couple of "category" columns used for filtering as well. I also want to filter/order search results in many ways (10+ different indexes).

There is no need for doing complex joins since the data is pretty much denormalized. The data is updated heavily: some 50M of records are being replaced every day.

I've first tried with DynamoDB, but it can support only up to 5 indexes, and is not capable of doing full-text search at reasonable speed. I've also considered google's BigQuery, but it is designed for "append-only" data. I'm now considering Redshift, but I'm not sure how it will be able to handle such large number of daily updates.

Any advice would be appreciated!

I ended up storing data in DynamoDB and doing daily sync with Redshift. I've tried Redshift with 600M sample data on 4 node cluster and it runs extremely fast. It is exactly what i need.


Password encryption type I would like to confirm if MySQL uses SHA1 for 'PASSSWORD' hashing and why test below doesn't work:

mysql> GRANT USAGE ON *.* TO 'test'@'localhost' IDENTIFIED BY 'password';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> SELECT user,host,password FROM mysql.user WHERE user='test';
+------+------------+-------------------------------------------+
| user | host       | password                                  |
+------+------------+-------------------------------------------+
| test | localhost  | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
+------+------------+-------------------------------------------+
1 row in set (0.01 sec)

mysql> \q
Bye
[root@test ~]# echo -n "password" | sha1sum | awk '{print toupper($1)}'
5BAA61E4C9B93F3F0682250B6CF8331B7EE68FD8

The MySQL documentation Encryption and Compression Functions page has various functions that can be used for hashing passwords. It also clearly states that:

  Note

  The PASSWORD() function is used by the authentication system in MySQL Server; you should not use it in your own applications. For that purpose, consider MD5() or SHA2() instead. Also see RFC 2195, section 2 (Challenge-Response Authentication Mechanism (CRAM)), for more information about handling passwords and authentication securely in your applications.

As for the other question, you can test at SQL-Fiddle that PASSWORD() (at least in versions 5.1, 5.5 and 5.6 that we can test at SQL-Fiddle) is implemented as applying SHA1() twice on the provided string:

SELECT
  SHA1(UNHEX(SHA1('password'))),
  PASSWORD('password');

Thanks should go to Rene Cannao who has digged into the source code and provided the answer in the blog post: PalominoDB.com : Hashing Algorithm in MySQL PASSWORD()

There is also this, older answer on StackOverflow which is relevant: Simulating MySql's password() encryption using .NET or MS SQL

If you want to confim with Linux CLI, just tested at Ubuntu:

ypercube@Zeus:~$    echo -n "password" | sha1sum |
      perl -ne 's/([0-9a-f]{2})/print chr hex $1/gie' | sha1sum
2470c0c06dee42fd1618bb99005adca2ec9d1e19  -


Oracle: Truncate large table takes forever Problem

I'm truncating a very large table ( 210+ million rows ).
It's been hours since it started and I have no idea when it will be done.
There're several tables like that, that I want to truncate.
The database is Ora9i.

Question

How can I do to make truncates faster ?
Also, Is there a way to see the progress of the truncate operation ?

I assume you are using dictionary managed tablespaces. You have to wait until all extents are deallocated. The following statement should report the number of extents that are to deallocate:

select extents
  from dba_segments
  where owner='TABLEOWNER'
  and segment_name='TABLENAME'

Index segments and materialized view logs have to be deallocated too, if they exist.

If you drop and recreate the table you will have to wait, too. Dropping the table will take as long as truncation of the table because the number of extents to release is almost the same.

Maybe the following will help:
instead of drop and recreate you

1)  rename the table TABLENAME to OLDTABLE
  2)  create a new table with the original name TABLENAME. Now you have an empty table TABLENAME
  3)  you drop the table OLDTABLE

The advantage of this approach is you can continue your work with table TABLENAME after step 2. But the space occupied by the former table TABLENAME will be release during the execution of step 3.
In Linux you can start the job that does step 3 in the background with nohup so that you must not leave your terminal window open. Use
nohup sqlplus user/password@tnsname @dropscript.sql &.

Even if you want to restart the database during execution of step 3 this is not a problem because Oracle can handle such situations.
As far as I remember it does this in the following way:
After the restart of the instance (or if the sessions  that does the drop of the table dies for some other reasons) the segment of the table is converted to a temporary segments. Its extents are deallocated by a background process (I think it is smon). The table is removed from the dictionary after the death of session so the tablename OLDTABLE is not visible in the dictionary anymore.


Documenting Databases with Extended Properties - value is being overwritten I have a query that views some information about an entire instance of databases. Using Extended Properties, I have also added information on database level to what customer the database belongs to, among other things.

To view all the information collectively, my query looks like this:

SELECT d.database_id                     AS [ID],
       d.NAME                            AS [Database Name],
       Round(Sum(mf.size) * 8 / 1024, 0) AS [Size (MB)],
       d.state_desc                      AS [State],
       d.is_read_only                    AS [Read Only],
       d.create_date                     AS [Created Date],
       Max(CASE
             WHEN SEP.NAME = 'Shared' THEN value
           END)                          AS [Shared],
       Max(CASE
             WHEN SEP.NAME = 'Product' THEN value
           END)                          AS [Product],
       Max(CASE
             WHEN SEP.NAME = 'Customer' THEN value
           END)                          AS [Customer]
FROM   sys.master_files mf
       INNER JOIN sys.databases d
               ON d.database_id = mf.database_id,
       sys.extended_properties SEP
WHERE  d.database_id > 4 -- Skip system databases
GROUP  BY d.database_id,
          d.NAME,
          d.state_desc,
          d.is_read_only,
          d.create_date
ORDER  BY d.NAME

And the results become this:

As you can see, all Extended Properties "Shared", "Product", and "Customer" will be overwritten by the first database's Extended Properties. In reality, if the query worked as intended, it would be something like this instead:

Notice the emphasis on the last three columns. I have tried to group the query on SEP.name and SEP.value, but the result have not been to any help. How does one fix this?

Your query is missing the joining clause against sys.extended_properties. It's currently doing the cartesian product against the previous join result (notice the comma at the end of the first join and no condition on the WHERE linking the tables).

FROM
    sys.master_files mf
    INNER JOIN sys.databases d ON d.database_id = mf.database_id,
    sys.extended_properties SEP
WHERE
    d.database_id > 4 -- Skip system databases
GROUP BY
    -- ...

I'd recommend always explicitly writing the JOIN operator (and avoid writing the relationships in the WHERE) since it makes the query more readable. If you want to apply cartesian just write CROSS JOIN.

Extended properties on database object are tricky because their record on sys.extended_properties have no id (they are listed as 0). The documentation says that database objects are listed with class = 0, so we are gonna query those:

The way you can retrieve these is through the system function sys.fn_listextendedproperty, by supplying all parameters as DEFAULT:

SELECT F.*
FROM fn_listextendedproperty(default, default, default, default, default, default, default) AS F

However, this will only retrieve the extended properties of the database in which you are executing the function. So to see ADatabase's extended properties you will need to write ADatabase.sys.fn_listextendedproperty(...). This means that you will require a dynamic, database by database solution to correctly retrieve all these (in which case you can use the sys.extended_properties while preceding the corresponding USE <DatbaseName> before).

The following solution uses a temporary table and a cursor to retrieve all databases extended properties and finally join with your query by database_id. I've tested it and ran successfully (but always review it!).

IF OBJECT_ID('tempdb..#DatabaseExtendedProperties') IS NOT NULL
    DROP TABLE #DatabaseExtendedProperties

CREATE TABLE #DatabaseExtendedProperties (
    DatabaseID INT,
    DatabaseName SYSNAME,
    PropertyName SYSNAME,
    PropertyValue VARCHAR(MAX))

DECLARE
    @DatabaseName NVARCHAR(100),
    @DatabaseID INT

DECLARE DatbaseCursor CURSOR FOR
    SELECT
        S.name,
        S.database_id
    FROM
        sys.databases AS S
    WHERE
        S.database_id > 4

OPEN DatbaseCursor
FETCH NEXT FROM DatbaseCursor INTO @DatabaseName, @DatabaseID

WHILE @@FETCH_STATUS = 0
BEGIN

    DECLARE @DynamicSQL NVARCHAR(MAX) = '
        USE ' + QUOTENAME(@DatabaseName) + '

        INSERT INTO #DatabaseExtendedProperties (
            DatabaseID,
            DatabaseName,
            PropertyName,
            PropertyValue)
        SELECT
            DatabaseID = ' + CONVERT(NVARCHAR(10), @DatabaseID) + ',
            DatabaseName = ''' + QUOTENAME(@DatabaseName) + ''',
            PropertyName = T.name,
            PropertyValue = CONVERT(VARCHAR(MAX), T.value)
        FROM
            sys.extended_properties AS T
        WHERE
            T.class = 0'

    EXEC (@DynamicSQL)

    FETCH NEXT FROM DatbaseCursor INTO @DatabaseName, @DatabaseID

END

CLOSE DatbaseCursor
DEALLOCATE DatbaseCursor

-- Your query with the temporary table's join
SELECT d.database_id                     AS [ID],
       d.NAME                            AS [Database Name],
       Round(Sum(mf.size) * 8 / 1024, 0) AS [Size (MB)],
       d.state_desc                      AS [State],
       d.is_read_only                    AS [Read Only],
       d.create_date                     AS [Created Date],
       Max(CASE
             WHEN SEP.PropertyName = 'Shared' THEN SEP.PropertyValue
           END)                          AS [Shared],
       Max(CASE
             WHEN SEP.PropertyName = 'Product' THEN SEP.PropertyValue
           END)                          AS [Product],
       Max(CASE
             WHEN SEP.PropertyName = 'Customer' THEN SEP.PropertyValue
           END)                          AS [Customer]
FROM   sys.master_files mf
       INNER JOIN sys.databases d ON d.database_id = mf.database_id
       INNER JOIN #DatabaseExtendedProperties AS SEP ON SEP.DatabaseID = d.database_id
WHERE  d.database_id > 4 -- Skip system databases
GROUP  BY d.database_id,
          d.NAME,
          d.state_desc,
          d.is_read_only,
          d.create_date
ORDER  BY d.NAME


Execution Plans Differ for the Same SQL Statement As a background to this question the details can be found at this link.

In a nutshell: I have 2 SQL statements that are almost identical except for the RIGHT table. The LEFT JOIN is the same and the returned columns vary because the tables are different. The databases were moved to a SQL Server 2012 instance from 2008. On the 2012 instance, 1 query executes in under 3 seconds, and the other takes almost 2 hours to execute. The execution plans differ and are posted at the link.

However, I can take both queries and execute them on the SQL Server 2008 server and they both complete in under 3 seconds. Both execution plans on the 2008 server are identical to the 'Good' execution plan on the 2012 server.

I have a new statement that will work but the application that it's used in is a C# windows application using inline SQL. I would like to not break that pattern for this 1 statement. Can anyone shed any light on this problem?

The query optimizer has a number of choices when constructing an execution plan for this query. Among the many strategies available, it can choose between hash join and nested loops join. Which one it decides to use depends sensitively on the statistical information available, and other factors like the amount of memory configured for SQL Server to use.

It just so happens that the optimizer chooses a nested loops strategy in one case, and a hash join in the others. If you were to force the use of a hash join (e.g. using the query hint OPTION (HASH JOIN) where a nested loops join is currently being used, you would find that the estimated cost of the nested loops plan appears to be the cheaper option to the optimizer.

This is not a bug. It is a fairly routine example of plan choice being sensitive to the statistical information available (among other things). The fact that the nested loops join performs so poorly in reality is a consequence of the query and database design not being very optimizer-friendly. Given very low-quality information to work with, the optimizer's plan selection is barely better than a guess.

Anyway, assuming you need to avoid the poor-performing plan shape without changing the source code (a reason to prefer stored procedures over in-lining SQL, by the way) you have two main options:

The first is to use a plan guide to force the 'good' plan shape for the target query. This is reasonably advanced work if you haven't worked with plan guides before. Extra steps will be involved if the literal values specified in the example could ever be different.

A second option is to present the optimizer with a more useful index to use. In this case, this involves adding a computed column (a fast metadata-only operation) and then indexing the new column:

-- Metadata-only operation
ALTER TABLE dbo.InHouse_CSV_Backup
ADD MERSNUMBER_CC AS
    REPLACE(LTRIM(RTRIM([MERSNUMBER])),'-', '');

-- Index on computed column
CREATE NONCLUSTERED INDEX
    IX_dbo_InHouse_CSV_Backup__MERSNUMBER_CC
ON dbo.InHouse_CSV_Backup (MERSNUMBER_CC)
INCLUDE (MERSNUMBER);

The query is very likely to use the index, resulting in better plan stability and most likely good performance too. It is not a perfect solution by any means, but it is a relatively simple and unobtrusive one, given the information available.

Potential areas for future improvement:

Consider stored procedures instead of embedding SQL in the application
Consider NOT EXISTS instead of outer join with null rejection
Avoid the unnecessary RTRIM when comparing strings with trailing spaces
Be careful with implicit type conversion. For example, MERSMin is varchar(300) but MERSNUMBER is nvarchar(31) (Unicode). The results of expressions have similar derived types, e.g. the REPLACE is deemed to produce an nvarchar(4000).


SQL Server 2005 : How to use pivot instead of a bunch of case statements? Please show me how I can modify this SQL select to use pivot instead of all the case statements.

SELECT t.ProjectId,
   COALESCE(MAX(ExecutiveChampion), 'n/a'),
   COALESCE(MAX(BusinessOwner), 'n/a'),
   COALESCE(MAX(BusinessAnalyst), 'n/a'),
   COALESCE(MAX(GeneralContractor), 'n/a'),
   COALESCE(MAX(PrimaryPM),  'n/a'),
   COALESCE(MAX(DevelopmentManager), 'n/a'),
   COALESCE(MAX(DevelopmentLead), 'n/a'),
   COALESCE(MAX(TDM), 'n/a'),
   COALESCE(MAX(PTM), 'n/a')
FROM (
    SELECT pl.ProjectId,
           CASE WHEN StakeholderCID = 95  THEN FullName ELSE NULL END as 'ExecutiveChampion',
           CASE WHEN StakeholderCID = 96  THEN FullName ELSE NULL END as 'BusinessOwner',
           CASE WHEN StakeholderCID = 97  THEN FullName ELSE NULL END as 'BusinessAnalyst',
           CASE WHEN StakeholderCID = 100 THEN FullName ELSE NULL END as 'GeneralContractor',
           CASE WHEN StakeholderCID = 101 THEN FullName ELSE NULL END as 'PrimaryPM',
           CASE WHEN StakeholderCID = 102 THEN FullName ELSE NULL END as 'DevelopmentManager',
           CASE WHEN StakeholderCID = 103 THEN FullName ELSE NULL END as 'DevelopmentLead',
           CASE WHEN StakeholderCID = 104 THEN FullName ELSE NULL END as 'TDM',
           CASE WHEN StakeholderCID = 105 THEN FullName ELSE NULL END as 'PTM'
      FROM @pList pl
     INNER JOIN StatusCode sc
        ON 1 = 1
       AND SCID IN (8, 9)
      LEFT OUTER JOIN ProjectStakeholder ps
        ON pl.ProjectId = ps.ProjectId
       AND sc.CID = ps.StakeholderCID
   ) as t
GROUP BY t.ProjectId

Tweaking is left as an exercise to the requester.

Using PIVOT and UNPIVOT

SELECT
    ProjectId,
    [95] AS [ExecutiveChampion],
    [96] AS [...],
    [97] AS [...],
    [100] AS [...],
    [101] AS [...],
    [102] AS [...],
    [103] AS [...],
    [104] AS [...],
    [105] AS [...]
FROM (
    SELECT
        pl.ProjectId,
        StakeholderCID,
        FullName
    FROM @pList pl
    INNER JOIN StatusCode sc
        ON 1 = 1
            AND SCID IN (8, 9)
    LEFT OUTER JOIN ProjectStakeholder ps
        ON pl.ProjectId = ps.ProjectId
            AND sc.CID = ps.StakeholderCID
) AS SourceTable
PIVOT (
    MAX(FullName)
    FOR StakeholderCID IN ([95], [96], [97], [100], [101], [102], [103], [104], [105])
) AS PivotTable;


getIndexes is not a function on mongo shell I'm trying to get the index of a collection.

> db.version()
3.2.6

> db.getCollectionNames()
[ "last_aggregated_stat_ids", "mapps", "stats", "system.indexes" ]

> db.mapps.getIndexes()
[
    {
        "v" : 1,
        "key" : {
            "_id" : 1
        },
        "name" : "_id_",
        "ns" : "app_development.mapps"
    }
]

> db.stats.getIndexes()
2016-07-20T16:51:04.601+0200 E QUERY    [thread1] TypeError: db.stats.getIndexes is not a function :
@(shell):1:1

Any ideas of what can happen?

db.stats() is a function call so db.stats is a function reference which has no function called getIndexes().

To get them db.getCollection("stats").getIndexes()

https://docs.mongodb.com/manual/reference/method/db.stats/


How to avoid full table scan when using OR operator on an Index Essentially, I want to get intersection between 2 tables based on matching IDs with the option of wildcard '*' to return all IDs:

  Select * from A a inner join B b on b.id = a.id or b.id = '*'

Table A is fairly large (10 M rows), and I have setup the id to be index already.

Because of OR, index id is not used, and it does a full scan (Takes 20 secs).

If I don't allow the wildcard:

  Select * from A a inner join B b on b.id = a.id

It uses id index and takes only 300 ms

Edit:

By separating into 2 select with Union helped. Detail here: https://stackoverflow.com/questions/5901791/is-having-an-or-in-an-inner-join-condition-a-bad-idea

A common trick for optimizing OR is to turn it into UNION:

( SELECT * FROM A JOIN B USING(id) WHERE condition_1 )
UNION DISTINCT
( SELECT * FROM A JOIN B USING(id) WHERE condition_2 )

(I'm with Jack on being confused on what you really wanted, so I avoided spelling out the details of the conditions.)

Be sure to include index(es) that let the queries start with the conditions.


MySQL:- Join two SELECT queries including join and aliases also I have two queries and both are working fine individually, but I want to join both the queries and display the output in a single grid.

Query1

SELECT t1.*, t2.*
FROM item_master t1
INNER JOIN
(
SELECT item_master_id,
   SUM(received_quantity) AS received_quantity,
   SUM(ordered_quantity)  AS ordered_quantity,
   SUM(unit_cost)  AS unit_cost
FROM material_line_item
GROUP BY item_master_id) t2
ON t1.id = t2.item_master_id

In this query1 I am using two tables to fetch records

Query2

SELECT item_master_id
,SUM(open_quantity) open_quantity
FROM closed_stock
GROUP BY item_master_id

And in query2 from a single table

In all the above 3 tables 3 columns are there received_quantity ordered_quantity and open_quantity so I have to show all this 3 quantity columns in a single query by joining this above two queries.

So far what I tried

SELECT * FROM (SELECT t1.*, t2.*
FROM item_master t1
INNER JOIN
(
SELECT item_master_id,
   SUM(received_quantity) AS received_quantity,
   SUM(ordered_quantity)  AS ordered_quantity,
   SUM(unit_cost)  AS unit_cost
FROM material_line_item
GROUP BY item_master_id) t2
ON t1.id = t2.item_master_id)

join

(SELECT item_master_id
,SUM(available_quantity) open_quantity
FROM closed_stock
GROUP BY item_master_id)

It would be better to simplify the query without nested levels of subqueries. You already have as simple table (t1) joined to a derived table (t2). Join them further to the other query2 as a derived table (lets call it t3 in lack of a better name):

SELECT
    t1.*,

    t2.received_quantity,
    t2.ordered_quantity,
    t2.unit_cost,

    t3.open_quantity
FROM
        item_master AS t1
    INNER JOIN
        (
        SELECT item_master_id,
           SUM(received_quantity) AS received_quantity,
           SUM(ordered_quantity)  AS ordered_quantity,
           SUM(unit_cost)         AS unit_cost
        FROM material_line_item
        GROUP BY item_master_id
        ) AS t2
    ON t1.id = t2.item_master_id

    INNER JOIN
        (
        SELECT item_master_id,
               SUM(open_quantity) AS open_quantity
        FROM closed_stock
        GROUP BY item_master_id
        ) AS t3
    ON t1.id = t3.item_master_id ;

If there are items in the item_master table that don't appear in one or both the other two tables, you might want to change these INNER joins to LEFT joins.


Moving a large table to another filegroup I have a 30 million row table, which is causing performance issues. I thought of moving it to a new filegroup for better performance.

My questions are:

This table has many foreign key references. If I just move the clustered index to another filegroup, will it still work right? Or do I also need to move the references to the new filegroup?

How about leaving the nonclustered indexes on the old filegroup?

Please suggest on my approach and my question.

Yes your Foreign Key constraints (References) will work once you move the clustered index to a different filegroup.
You can leave the non-clustered indexes in the old file group (in my personal opinion it has some advantage to have clustered and non clustered index  in different file group especially if the underlying disks are different and write heavy workload)
Remember when you create a non-clustered index by default it goes to the same file group where your clustered index is. If you want non-clustered indexes in a separate file group you need to explicitly mention that. Here is a good example of this.


SQL Server 2008 - How would I create a dynamic query for finding relationships and references to the primary key? Since I'm new and I've never shared, and yet there are a few useful scripts that I need on a daily basis, I thought I should.

As a new DBA, some data fix DML operations and simple business logical analyses are much faster when I have a template with which I can check the constraints in a very large database with hundreds of tables. Pinal Dave's script (source posted in the answer below) was a great help as I started, but since then I expanded on it a bit to include the possibility to search for referenced ID's in related tables as well.

I hope this proves useful to some of you guys!

The query I've used for the scenario above, is below. Let me know what you think, and if you've links to other such generic and useful tips.

/*
Modified from Pinal Dave's original @ SQLAuthority:
    http://blog.sqlauthority.com/2009/02/26/sql-server-2008-find-relationship-of-foreign-key-and-primary-key-using-t-sql-find-tables-with-foreign-key-constraint-in-database/

Supply the name of the table, and optionally the ID (default = NULL) whose
references you wish to see from other tables.
*/

DECLARE @SEARCHED_NAME VARCHAR(50), @SEARCHED_ID INT
SET @SEARCHED_NAME = 'TABLENAME' -- The name of the table whose constraint connections you wish to see.
SET @SEARCHED_ID = NULL -- The parent table ID you want to find in any of the tables referencing the parent table.

SELECT f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id
WHERE OBJECT_NAME(f.parent_object_id) LIKE @SEARCHED_NAME
OR OBJECT_NAME (f.referenced_object_id) LIKE @SEARCHED_NAME
ORDER BY f.name

IF @SEARCHED_ID IS NOT NULL
BEGIN

    CREATE TABLE #SEARCHOBJREF (RowNum INT IDENTITY(1,1), TableName VARCHAR(256)
        , ColumnName VARCHAR(256), MatchCount INT)

    INSERT INTO #SEARCHOBJREF (TableName, ColumnName)
    SELECT DISTINCT OBJECT_NAME(f.parent_object_id) AS TableName,
    COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName
    FROM sys.foreign_keys AS f
    INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
    INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id
    WHERE OBJECT_NAME (f.referenced_object_id) LIKE @SEARCHED_NAME
    ORDER BY OBJECT_NAME(f.parent_object_id)

    DECLARE @ROW_NOW INT, @ROW_MAX INT, @SQL VARCHAR(MAX), @TNAME VARCHAR(256), @COLNAME VARCHAR(256)
    SELECT @ROW_NOW = MIN(RowNum), @ROW_MAX = MAX(RowNum) FROM #SEARCHOBJREF

    WHILE @ROW_NOW <= @ROW_MAX
    BEGIN
        SELECT @TNAME = TableName, @COLNAME = ColumnName FROM #SEARCHOBJREF WHERE RowNum = @ROW_NOW

        SET @SQL = 'UPDATE #SEARCHOBJREF '
            + ' SET MatchCount = (SELECT COUNT(*) FROM ' + @TNAME + ' WHERE '
            + @COLNAME + ' = ' + CAST(@SEARCHED_ID AS VARCHAR(64)) + ') '
            + 'WHERE RowNum = ' + CAST(@ROW_NOW AS VARCHAR(64))
        EXEC(@SQL)

        SET @ROW_NOW = @ROW_NOW + 1
    END

    SELECT TableName, ColumnName, MatchCount as 'Matches_for_ID' FROM #SEARCHOBJREF WHERE MatchCount > 0
    DROP TABLE #SEARCHOBJREF

END