Performance issue in Sql Server I have a master product table as like below :

CREATE TABLE dbo.[products](
    [id] INT NOT NULL IDENTITY(1, 1),
    [product_code] VARCHAR(100) NOT NULL UNIQUE,
    [price] FLOAT NOT NULL,
    [brand] VARCHAR(100) NOT NULL,
    [colour] VARCHAR(100) NOT NULL
);

So, if I create this table, a clustered index will be created on id column and a non-clustered index on product_code column.

And I am using this table in a website to show the products. And I will be using the sql query like below.

Query 1

SELECT * FROM dbo.[products]
WHERE [brand] IN ('brand1', 'brand2', 'brand3', 'brand4', 'brand5')
AND [colour] IN ('colour1', 'colour1', 'colour1')
ORDER BY [product_code];

And there is an another option to search for bulk of product_codes like below.

Query 2

SELECT * FROM dbo.[products]
WHERE [product_code] IN ('product_code1', 'product_code2', 'product_code3');

The conditions can be more in Query 1 and product codes can be more in Query 2.

Did I created the index properly?

Or is there any better way to improve the performance?

Short answer: No, you need more indexes.

If you're going to create a significant number of queries involving [brand] and [colour], you should index both columns:

CREATE INDEX products_colour_idx ON dbo.products (colour);
CREATE INDEX products_brand_idx ON dbo.products (brand);

If you mostly query using both columns, and there are not queries involving colour that do not also involve a brand, a multi-column index would be better, because by using just one index, the database can retrieve all the relevant rows. You would create it this way:

CREATE INDEX products_brand_colours_idx ON dbo.products (brand, colour);

The order of the columns [(brand, colour) vs. (colour, brand)] should be normally chosen in such a way that the most selective one comes first (basically: the one with more different values, first).

If you happen to have a significant number of queries with brand, colour and both brand and colour, you should have at least the two first indexes; if you need the fastest possible speed, have one index for (brand, colour) and another one for (colour). You don't need an index on purpose for (brand), the (brand, colour) one is good for looking for brands.

[product_code] will be indexed by the database automatically to enforce the UNIQUE constraint on it. See Create Unique Constraints and Unique Constraints and Unique Indexes. You don't need to create an index explicitly even if query_2 is frequent.


Want to clone a role, but am getting "can't login" message after doing so I’m using Postgres 9.5 on Ubuntu 14.04.  I would like to create a role (user) that has all the same privileges and access to a database that an existing role in my system has.  I thought I could pull that off by creating that user and then granting the other role to him…

postgres=# create role myuser;
CREATE ROLE
postgres=# GRANT rails TO myuser;
GRANT ROLE

but then when I login to that user, it turns out I don’t even have login privileges.

myuser@myproject:~$ psql mydb_production
psql: FATAL:  role "myuser" is not permitted to log in

What is the proper way to clone a role to another user (e.g. copy the privileges)?

The answer is simple, but may be very surprising:

  CREATE USER is now an alias for CREATE ROLE. The only difference is
  that when the command is spelled CREATE USER, LOGIN is assumed by
  default, whereas NOLOGIN is assumed when the command is spelled
  CREATE ROLE.

So use CREATE USER instead of CREATE ROLE.

There is one more equally tricky aspect of this. Even if the role you grant to the new role (in your example, rails) has the LOGIN option, myuser won't inherit it.  The reason is that GRANT only gives permissions, while LOGIN is an attribute of the user/role:

CREATE USER rails; -- means it has LOGIN
CREATE ROLE myuser;
GRANT rails TO myuser;

\du rails
           List of roles
 Role name | Attributes | Member of
-----------+------------+-----------
 rails     |            | {}

\du myuser
            List of roles
 Role name |  Attributes  | Member of
-----------+--------------+-----------
 myuser    | Cannot login | {rails}

Note: if you are using one of the trust and peer authentication methods in pg_hba.conf, it's possibly a good idea to rethink that.  If you are using LDAP (the last idea I have, given that you don't specify a password), then ignore this.


Which one is an ER diagram? I have started to study ER diagrams. When I browse through the ER diagram tutorials, I found something like figure 1:
Figure 1

I tried to create a sample ER Diagram in MySQL Workbench like:
Figure 2

I browsed Google images for ER Diagrams, and I saw both types of images. I don't know the similarities and differences between them.

One can say that a proper entity-relationship diagram is that which was created using the constructs introduced by Dr. P. P. Chen (e.g., rectangular and diamond-shaped boxes) in his famous 1976 paper entitled The Entity-Relationship Model—Toward a Unified View of Data.

Regarding your specific comparison, Figure 1 displays symbols (perhaps shapes) that look much closer to the Chen’s originals than the ones shown in Figure 2.

Pertinent considerations

But, if a certain drawing portrays (a) entity types, and (b) the corresponding entity types interrelationships, then it can be considered some kind of entity-relationship representation (and multiple methods are associated in a certain way with the work of Dr. Chen).

In this respect, IDEF1X is a data modeling technique —established as a standard in 1993 by the U.S. National Institute of Standards and Technology (NIST)— that takes Dr. E. F. Codd’s relational concepts (e.g., foreign keys and alternate keys) and combines them with entity-relationship ideas and first-order logic elements in order to assist in the composition of more rich and expressive diagrams (which can, e.g., depict supertype-subtype associations, entity type properties or attributes, etc.).

There are other efforts that produced, e.g., the enhanced entity-relationship approach, which also added new notions to the original view.

These paradigms (some more complete than the others) are platforms that were developed to capture business-domain-specific data meaning (i.e., data semantics). With their help, a practitioner can supply communication instruments that facilitate managing the data as it actually is, i.e., an important and independent asset, presenting the scenario of interest from a conceptual perspective (sometimes incorporating logical level aspects) to all the involved parties.

Asides

Concerning some aside comments, it must be said that (i) application program components are different from (ii) database structures, therefore they definitely hold different implications. In this manner, you can use “general” implements like UML sketches to shape object-oriented application program items, e.g., classes (basically, object templates), methods (i.e., object processes, behaviour), etc. In addition, application program components are part of the external level of a computerized information system, so any kind of sketch depicting them should not necessarily have a one-to-one correspondence with a diagram illustrating structures of the conceptual level.

That being so, and since software development is a profession that entails accuracy, identifying and employing the right tool for each clearly delimited task is paramount.


Index: Does 0% fragmentation imply a B-tree? I am currently dealing with Microsoft SQL Server and its index structures. The data structures for this are trees.

When I reorganize a table index (e.g. the primary key), the result afterwards is 0% fragmentation. Then, in my opinion, the tree is optimally set up. Does this also mean that the structure is now a B-tree, i.e. is a fully balanced tree?

The "B" in B-tree doesn't necessarily stand for "balanced". It depends on who you ask. See for instance this. As often is the case, terminology degrades (over time).

All SQL Server "row indexes" (as opposed to other types of indexes in SQL Server like hash, columnstore, full-text, XML (to some extent) and geospatial) are B-tree indexes. They are always "balanced" in the sense that you have the same depth (number of pages) from the root to all leaf pages.

This has no relationship with any of the two types of fragmentation that we usually talk about in the SQL Server world: external (jump back and forth when following the linked list) or internal (non-full pages).

One can argue that a clustered index in SQL Server should be considered a B+ tree, since the leaf "has the data", but again I doubt that you will find and authoritative reference for how the terms are used.


How to include empty string in string_agg? I've done a groklearning exercise up until the point of:

  Make sure to list all properties, even if there is some unknown
  address information; replace any missing parts with an empty string

Could someone guide me on how to make this work?

SELECT D.council_name, string_agg( P.house_number || ' ' || P.street  || ' ' ||    L.locality,  ', ' ORDER BY S.contract_date) AS commercial_properties
FROM Sales S, Properties P, Districts D, Localities L
WHERE S.property_id = P.property_id
AND P.locality_id = L.locality_id
AND P.district_code = D.district_code
AND P.primary_purpose = 'COMMERCIAL'
AND S.contract_date BETWEEN '2017-01-01' AND '2017-12-31'

GROUP BY D.council_name;

But it prints:

+-----------------+----------------------------------------------------------------------------------------+
|  council_name   |                                 commercial_properties                                  |
+-----------------+----------------------------------------------------------------------------------------+
| COOTAMUNDRA     | 218 PARKER ST COOTAMUNDRA                                                              |
| COWRA           | 6 VAUX ST COWRA                                                                        |
| GREAT LAKES     | 102 MANNING ST TUNCURRY                                                                |
| LAKE MACQUARIE  | 214 MACQUARIE RD WARNERS BAY, 19 NELSON RD CARDIFF, 21 NELSON RD CARDIFF               |
| NEWCASTLE       | 8 RURAL DR SANDGATE                                                                    |
| PARRAMATTA      | 5 AIRD ST PARRAMATTA, 5 AIRD ST PARRAMATTA, 5 AIRD ST PARRAMATTA, 5 ROSS ST PARRAMATTA |
| PORT STEPHENS   | 17 STOCKTON ST NELSON BAY                                                              |
| THE HILLS SHIRE | 24 LEXINGTON DR BELLA VISTA                                                            |
| WYONG           | 172 PACIFIC HWY TUGGERAH, 20 AMSTERDAM CCT WYONG                                       |
+-----------------+----------------------------------------------------------------------------------------+

Instead of:

+-----------------+----------------------------------------------------------------------------------------+
|  council_name   |                                 commercial_properties                                  |
+-----------------+----------------------------------------------------------------------------------------+
| COOTAMUNDRA     | 218 PARKER ST COOTAMUNDRA                                                              |
| COWRA           | 6 VAUX ST COWRA                                                                        |
| GREAT LAKES     | 102 MANNING ST TUNCURRY, MACWOOD RD SMITHS LAKE                                        |
| LAKE MACQUARIE  | 214 MACQUARIE RD WARNERS BAY, 19 NELSON RD CARDIFF, 21 NELSON RD CARDIFF               |
| NEWCASTLE       | 8 RURAL DR SANDGATE                                                                    |
| PARRAMATTA      | 5 AIRD ST PARRAMATTA, 5 AIRD ST PARRAMATTA, 5 AIRD ST PARRAMATTA, 5 ROSS ST PARRAMATTA |
| PORT STEPHENS   | 17 STOCKTON ST NELSON BAY                                                              |
| THE HILLS SHIRE | 24 LEXINGTON DR BELLA VISTA                                                            |
| WYONG           | 172 PACIFIC HWY TUGGERAH, 20 AMSTERDAM CCT WYONG                                       |
+-----------------+----------------------------------------------------------------------------------------+

It doesn't print "MACWOOD RD SMITHS LAKE" as it is missing the house_number.  How can I add the missing parts to the results?

You can use COALESCE to map null to an empty string for all attributes that may contain null:

string_agg( COALESCE(P.house_number, '')
         || ' '
         || COALESCE(P.street, '')
         ...
) AS commercial_properties


This would achieve it:

SELECT m1.id1, m2.*
FROM   map1 m1
CROSS  JOIN LATERAL (
   SELECT ST_HausdorffDistance(m1.g1, m2.g2) AS h_dist, m2.id2, m2.g2
   FROM   map2 m2
   WHERE  ST_HausdorffDistance(m1.g1, m2.g2) < 2
   ORDER  BY 1, 2
   LIMIT  2
   ) m2;

Returns 1 or 2 rows for every row in map1, extended with the top 2 corresponding row(s) in map2 (as defined by minimum Hausdorff distance) and the said Hausdorff distance between them. If there is no row with Hausdorff distance < 2 in map2, no row is returned.

Key element is the LATERAL subquery. There are variants of this query, depending on exact (missing) requirements. Related:

OFFSET and LIMIT on complex query
How to make DISTINCT ON faster in PostgreSQL?
How to speed up querying last values in a time series?
Optimise a LATERAL JOIN query on a big table

I wouldn't know of any way to use an index here. So this is going to be an expensive query.


Inserting random values into column How can we insert random values into a newly created column in a table in SQL Server?

I tried this:

alter table [dbo].[employee] add phoneno int

select * from [dbo].[employee]
update employee set phoneno=(ROUND((RAND(),0)) where phoneno NULL

Rather than two steps you could define the initial values using a DEFAULT constraint on the new column.


Is normalizing further to index on INT FK rather than VARCHAR worth it? I asked a question about whether or not I should normalize some static data and the general consensus was that it is not necessary since the data is unchanging, thus it is not redundant and will not likely result in update/deletion/insert anomalies.

However, is it worth normalizing further so that I can get an index on an INT type rather than VARCHAR? I'm not sure how most DBMS's do comparisons on strings but I would imagine that integers are faster than strings for comparisons.

At the same time if we are talking about a B+ tree index we are only talking about 3-4 comparisons for internal nodes and then however many records exist for the key which I am not even sure if comparisons occur at that level.

integer is typically a bit smaller and faster than character types in every respect. The difference mainly depends on the length of the strings.

You asserted that the text data is unchanging, else this would be a strong case for using a surrogate key instead (integer or whatever).

Circumstances that would still make me lean towards an added integer surrogate key:

Strings are much larger than the 4-byte integer. Little to nothing to gain with short strings. The benefit grows with the avg. string length.
The key is used in multiple indexes and/or FK columns, so benefits add up.
You work with a locale other than C (the typical case) and you need rows to sort in immutable (obvious) fashion. The sort order of character types depends on the current locale settings (COLLATION).
All other tables in your DB work with integer keys and you'd rather not break the pattern.

On the other hand, with Postgres 9.5 or later you can benefit from the "abbreviated keys" feature. But only if you work with locale C on a *nix OS. That would tilt the scales towards using original strings.

Related:

Indexes: integer vs string performance if the number of nodes is the same
Would index lookup be noticeably faster with char vs varchar when all values are 36 chars
Slow query ordering by a column in a joined table
What is the overhead for varchar(n)?


Understanding JOINs and why the syntax works the way it does Heyo,

I'm confused as to how this works:

UPDATE original
SET price=(SELECT price FROM new WHERE new.id=original.id)
WHERE id IN (SELECT id FROM new);

What it does is updating an original table with items with the prices of another table. The second table (my update table) has two columns, ID (which matches the corresponding ID of the first table) and a column for the new price.

As far as I understand, MySQL collects all columns that have a corresponding entry in the second table in the third row. And then it somehow magically updates the price column with a price column of the joint values created from the tables new and original. How does it determine which value goes where? How does it make sure nothing goes wrong if there is an ID used twice in the table with the new values?

Some clarification would be nice. Thanks in advance,
Yorrd

EDIT:

My question is, how does MySQL determine which row to match with which new value from the second table. Even if they are unique, how is being made sure that they're not mixed up accidentally?

The answer is in the parentheses:

SET price=(SELECT price FROM new WHERE new.id=original.id)

This is a scalar subquery.  It only ever returns one value (or no value, in which case it effectively returns NULL).  As @ypercube mentioned in comments, if there were ever more than one matching id in new then this expression would throw an error, since a scalar subquery can't and won't deal with more than one possible value.

This is also (by at least some definitions) a correlated subquery, containing a reference to a table (original) that is not mentioned in the FROM clause of the subquery.

The subquery is, essentially, executed once for each row in original in order to find the needed value in new, and that's how the rows don't get mixed up -- this expression is evaluated for each row in the original table.

At least, conceptually, that's how it happens.  The optimizer is free to decide --  within the limits of its design -- if there's a better way to perform your query than the way you've written it, as long as the chosen approach will still provide the exact same result (with the exception of the ordering of rows, which is by definition, undefined, unless you explicitly ORDER BY in a SELECT statement).  MySQL 5.6 brought some changes in subquery optimization that were largely improvements.

Although sometimes they are absolutely essential, subqueries can be a red flag that a query's logic could be improved, to make things easier on the optimizer and get the work done faster.  This is one of those cases.

A better, arguably clearer, and perhaps significantly better-performing way to write the example query would be this:

UPDATE original o
  JOIN new n ON n.id = o.id
   SET o.price = n.price;

There's no need to include WHERE n.id = o.id because the join will not only join the rows on that criterion, it will also exclude all rows that can't be joined.  The caveat here is that if id in the new table isn't unique, for a different reason than the error the original query would throw.  In this case, the result isn't deterministic, because you're not able to tell the server which of the more-than-one potentially matching row, so it will pick one, and you can't choose which one.  But if id is unique in the new table, there's no problem.

MySQL has historically been not the best at handling WHERE ... IN ( ... ) in some cases, and this rewrite also removes the need for that construct.


Msg 21, Level 21, State 1, Line 1 Warning: Fatal error 9001 We recently moved to a SQL Server that's on a VPS and every now and then we get a 9001 error when trying to do an update.

Rebooting the server fixes the problem.

I changed the maximum server memory (in MB) to 2000 because it's a 4GB VPS.

But I just don't know what's causing the error.

That error means that the log for your database (or for tempdb) is not avaialble for some reason.  That's a serious problem that is usually fixed by restarting the server.

If the problem recurs, it could be many things having to do with problems with your storage system.  One possible thing to check is to see if your database's log file (or tempdb's log file) is running out of space (not sure if that can cause this error or not).


How to map another server through SQL Server Management Studio I am trying to map another server by giving the  command

EXEC xp_cmdshell
    'NET USE H:\\568.256.8.358\backup_147 1234abc /USER:cranew /PERSISTENT:yes'

I got an error with this:

  network path not found

But I am able to map another server manually. Please help me in sorting this out.

This is more of a NET USE question than a SSMS/SQL Server question.

NET USE has the syntax:

net use [{DeviceName | *}]
   [\\\\ComputerName\ShareName[\volume]] [{Password | *}]]
   [/user:[DomainName\]UserName] [/user:[DottedDomainName\]UserName]
   [/user: [UserName@DottedDomainName] [/savecred] [/smartcard]
   [{/delete | /persistent:{yes | no}}]

So, I would expect your command to look like this:

EXEC xp_cmdshell
    'NET USE H: \\568.256.8.358\backup_147 1234abc /USER:cranew /PERSISTENT:yes'


Error on starting pgadmin3 When starting pgadmin3 I get the following error messages. This is the log file placed in my documents folder after starting:

2018-06-04 13:30:26 ERROR  :
ERROR:  function pg_last_xlog_receive_location() does not exist
LINE 1: ...E NULL END as inrecovery, CASE WHEN usesuper THEN
pg_last_xl...
^ HINT:  No function matches the given name and argument types. You might need
to add explicit type casts. 2018-06-04 13:30:34
ERROR  : Column not
found in pgSet: usecreatedb 2018-06-04 13:30:36
ERROR  : Column not
found in pgSet: usesuper 2018-06-04 13:30:37
ERROR  : Column not found
in pgSet: upsince 2018-06-04 13:30:37
ERROR  : Column not found in
pgSet: confloadedsince 2018-06-04 13:30:37
ERROR  : Column not found
in pgSet: inrecovery 2018-06-04 13:30:38
ERROR  : Column not found in
pgSet: replayloc 2018-06-04 13:30:39
ERROR  : Column not found in
pgSet: receiveloc 2018-06-04 13:30:39
ERROR  : Column not found in
pgSet: replay_timestamp 2018-06-04 13:30:40
ERROR  : Column not found
in pgSet: isreplaypaused 2018-06-04 13:30:41
ERROR  : Column not found
in pgSet: rolcatupdate 2018-06-04 13:30:41
ERROR  : Column not found
in pgSet: rolcatupdate 2018-06-04 13:30:42
ERROR  : Column not found
in pgSet: rolcatupdate 2018-06-04 13:30:43
ERROR  : Column not found
in pgSet: rolcatupdate 2018-06-04 13:30:44
ERROR  : Column not found
in pgSet: rolcatupdate 2018-06-04 13:30:45
ERROR  : Column not found
in pgSet: rolcatupdate

What do I have to do to avoid these errors in the future?
EDIT I am running on MacOS 10.13

Community wiki answer:

You are connecting to PostgreSQL 10, which is not supported.

I am not aware if there is a pgAdmin III version for server version 10 or higher. One choice could be pgAdmin4, or any other client - if you plan to do serious work, consider psql, the command line client.


Log Shipping with Clustered Instance- back up Current Set up- Clustered Instance Active/Passive
Node  -1
Node  -2
Current Back ups goes to Network Drive
Cluster Drive

E drive

Virtual Name ABC
Shared Folder - e\Share

New instance- for Log Shipping

My question is if i set up a log shipping between clustered instance will it break my current back ups that goes to the network

Not at all. You can continue to have the backups on the FCI go to your network share. The log shipping job can be configured to copy from the network share to a local drive on the new instance, or read right from the network share.


Failed to start mongo.service on ubuntu 16.04 after update from 4.0 to 4.2 I'm trying to update my MongoDB installation from 3.6 to the latest version [4.4] on Ubuntu16.04 using apt-get. Going from 4.0 to 4.2 I faced a problem during the startup process after updating the binaries and mongo.log shows the exception below:

2020-08-07T14:58:26.205+0300 I  STORAGE  [initandlisten] exception in initAndListen: Location40415: BSON field 'MinValidDocument.oplogDeleteFromPoint' is an unknown field., terminating

I'm using WiredTiger and I checked the feature compatibility version and other prerequisites according to the installation and the compatibility docs and everything looks fine. I also tried mongod repair but with no luck.
I don't have any clue on what could be the source of this problem and I'd be grateful if someone can help.

Interesting.. It should not be possible...  From the source:
    cpp_name: oldOplogDeleteFromPoint
￼                type: timestamp
￼                optional: true # This field only exists on 3.4 upgrade
￼                description: "The timestamp of the first oplog entry in a batch when we are writing
￼                              oplog entries to the oplog after which the oplog may be inconsistent.
￼                              This field only exists on 3.4 upgrade."

And this problem only exists on unclean upgrade from <=3.4 version. It is removed from source code at version 3.8 forward.
Yes, I know, this answer doesn't solve the problem, but... It looks like that your upgrade process was not "by the book", sorry.


SQL*Plus default output format I have noticed that using the same SQL*Plus client to connect to different Oracle database result in different outputs for the same SQL query.

Database 1:

C:\Oracle\instantclient_10_2>sqlplus user/passwd@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host1)(PORT=1521))(CONNECT_DATA=(SID=SID1)))'

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Aug 23 11:54:45 2013

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> select 'XXXX ',to_char(UniqueSequenceNumber,'xxxxxxxxxxxxxx') from mytable where rownum < 2;

'XXXX TO_CHAR(UNIQUES
----- ---------------
XXXX          300f483

Here the output is on one line, the headers are truncated.

Same thing on an other database:

C:\Oracle\instantclient_10_2>sqlplus user/passwd@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host2)(PORT=1521))(CONNECT_DATA=(SID=SID2)))'

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Aug 23 11:58:15 2013

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options

SQL> select 'XXXX ',to_char(UniqueSequenceNumber,'xxxxxxxxxxxxxx') from mytable where rownum < 2;

'XXXX'
--------------------------------
TO_CHAR(UNIQUESEQUENCENUMBER,'XXXXXXXXXXXXXX')
--------------------------------------------------------------------------------
XXXX
        3a1393b

Here the output is split on two lines.

I am not looking for SQL*Plus formatting options like linesize, column format, etc. that just impact the current session.
What I need to understand is what is making the output different even when I don't specify any options.

Is there some default settings on the server side?
Is it depending on the Oracle server version (10.2.0.1 in one case, 10.2.0.5 in the other)?

Note: column UniqueSequenceNumber is of type NUMBER

Note 2: Tried on a Server version 10.2.0.3, same result as on 10.2.0.1 (one liner)

In reply to miracle173 :

C:\Oracle\instantclient_10_2>sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Mon Aug 26 09:32:02 2013

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> connect user/passwd@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host1)(PORT=1521))(CONNECT_DATA=(SID=SID1)))
Connected.
SQL> select 'xxxx' from dual;

'XXX
----
xxxx

SQL> connect user/passwd@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host2)(PORT=1521))(CONNECT_DATA=(SID=SID2)))
Connected.
SQL> select 'xxxx' from dual;

'XXXX'
--------------------------------
xxxx

SQL> connect user/passwd@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host1)(PORT=1521))(CONNECT_DATA=(SID=SID1)))
Connected.
SQL> select 'xxxx' from dual;

'XXX
----
xxxx

SQL> connect user/passwd@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host2)(PORT=1521))(CONNECT_DATA=(SID=SID2)))
Connected.
SQL> select 'xxxx' from dual;

'XXXX'
--------------------------------
xxxx

Tom Kyte posted something like this once and the issue was the CURSOR_SHARING parameter.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3696883368520

If it is set to FORCE at the system/database level on host2, it could be mapping/forcing the 'XXXX' hard coded value to an arbitrary 32 character variable.


SELECT shop_id, COUNT(DISTINCT card_id) number_of_expired_cards
FROM sourcetable
WHERE card_expires < GETDATE()
GROUP BY shop_id


How to use transactions with SQL Server DDL? I have a login table into which all inserts are done by a single stored procedure.

CREATE TABLE dbo.LogTable(
    LogRefnr int IDENTITY(1, 1) NOT NULL,
    LogQuery varchar(255) NOT NULL,
    LogTime datetime NOT NULL,
    logQueryDuration int NULL,
    LogSessionID int NULL,
 CONSTRAINT PK_Log PRIMARY KEY CLUSTERED  (LogRefnr)
)
go

Create procedure DBO.LogInsert ( @Query varchar(255), @time datetime, @duration int, @SessinID int) as
begin
    Insert into LogTable ( LogRefnr, LogQuery, logQueryDuration, LogSessionID)
    Values  (@Query, @time, @duration, @SessinID);
end;
GO

Currently there are about 45500000 rows in that table and I want to direct logging to a different table.

My idea is to use the following script

begin Transaction

exec sp_rename LogTable, LogTableOld;

CREATE TABLE dbo.LogTable(
    LogRefnr int IDENTITY(46000000, 1) NOT NULL,            -- greater than select max(LogRefnr) from LogTableOld
    LogQuery varchar(255) NOT NULL,
    LogTime datetime NOT NULL,
    logQueryDuration int NULL,
    LogSessionID int NULL,
 CONSTRAINT PK_Log2 PRIMARY KEY CLUSTERED  (LogRefnr);
)
go

sp_recompile LogTable;
go

Commit;

Does this work and has minimal impact to other procedures calling LogInsert?

Yes. Transactions apply to DDL and span batches.

I'd do something like this. Note the use of SERIALIZABLE ISOLATION to ensure full isolation and XACT_ABORT which will force a rollback on any error.

SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
begin Transaction
GO
exec sp_rename LogTable, LogTableOld;
GO
CREATE TABLE dbo.LogTable(
    LogRefnr int IDENTITY(46000000, 1) NOT NULL,            -- greater than select max(LogRefnr) from LogTableOld
    LogQuery varchar(255) NOT NULL,
    LogTime datetime NOT NULL,
    logQueryDuration int NULL,
    LogSessionID int NULL,
 CONSTRAINT PK_Log2 PRIMARY KEY CLUSTERED  (LogRefnr);
)
go
EXEC sp_recompile LogTable;
go

Commit;


Under what conditions does SQL Server encrypt tempdb? When does tempdb get encrypted with Transparent Data Encryption?  What configuration, etc, causes tempdb to be encrypted this way?

When you encrypt the first database in the instance, tempdb will also be encrypted.  As per this reference on TDE:

  Transparent Data Encryption and the tempdb System Database

  The tempdb system database will be encrypted if any other database on
  the instance of SQL Server is encrypted by using TDE.

This will be the case when you set ENCRYPTION ON on a database.


UPDATE with Nested Query This is what i've tried

UPDATE (SELECT * FROM music GROUP BY songalbum)
SET albumID = (SELECT albumID FROM albumList GROUP BY album )
WHERE songalbum = albumList.album

this give the error #1248 - Every derived table must have its own alias

UPDATE music
SET albumID = (SELECT albumID FROM albumList )
WHERE albumID = NULL

UPDATE music
SET albumID = (SELECT albumID FROM albumList )
WHERE albumID = NULL
  AND songalbum = (select album FROM albumList)

both run but have no affected rows.

I am trying to populate the albumID in the music table with the albumIDs that are in the albumList table.

If it is MySQL, you can do this with a simple join:

UPDATE music AS m
  JOIN albumList AS a
    ON m.songalbum = a.album
SET m.albumID = a.albumID
WHERE m.albumID IS NULL ;

You could use a correlated subquery for this (similar to your 2nd attempt) but I wouldn't suggest it for MySQL. Less efficient and more complicated as it needs an additional condition in the WHERE clause to protect from updating music.albumID with null, for rows that have no related album:

UPDATE music AS m
SET albumID = (SELECT albumID FROM albumList AS a WHERE m.songalbum = a.album)
WHERE albumID IS NULL
  AND EXISTS (SELECT 1 FROM albumList AS a WHERE m.songalbum = a.album) ;


Group json data in db using a query I have a db whose values are as follows:img Can I call the json value of listing_id(23,27....so on) in properties column(with having row value as "listing") using a single query, I would Really Appreciate the help .

I'm using sqlite .json extension is not available.what I'm trying to do is::group all the rows having listing_id as a single value(23,27...).is there any other way?

If the content of the properties column is like in your screenshot, it's easy to remove everything from the content except for the pure number IDs:

SELECT REPLACE(REPLACE(properties, '{"listing_id":', ''), '}', '') AS listing,
       *
FROM   ahoy_events;

This will give you the pure numbers from your json column as (new) column listing.

| listing | id | visit_id | ... | properties        |
|---------------------------------------------------|
| 27      | 57 | 2        | ... | {"listing_id":27} |
| 23      | 58 | 2        | ... | {"listing_id":23} |

You can then group by this value.

Was this what you were asking for?


Mongodb group by queries Lets say one of my items in a collection looks like that:

{
  User:123
  Grade : 100
}

I want to sum up the grades for each student so i have this output:

123: 100, 445: 30 etc

Im normal sql, its the equivalent of a group by + sum() query. How can i do it with mongo?

You will have to use the mongodb aggregation framework.

Check this example: https://docs.mongodb.org/manual/tutorial/aggregation-zip-code-data-set/


After adding a slave to Master - Master configuration, slave syncs from only one master Futher to my previous question,

Here is what we have

      +<------+
      |       ^
      V       |
S1<---M1      M2
      |       ^
      V       |
      +-------+

what happens is Upon making any drop, create, update or insert on M1, the slave S1 Syncs the changes in M1. But when we are trying to do the same in M2, we are not getting any changes synced to the slave S1. Any suggestions on how to do that?

You need to ensure you have log_slave_updates enabled on M1, so that any changes that M1 makes via replication from M2 gets written to M1's master log (and thus replicated to S1).

Don't worry, the entries in M1's binary log will not get re-replicated to M2. M2 will ignore any updates which originated from its server-id.


PAGELATCH_EX on 2:1:128 We are having problems on SQL Server 2012 with contention on tempdb page 2:1:128, which seems to be related with system table sysobjvalues.

We have about 400 sessions waiting on this resourse 2:1:128 with wait type PAGELATCH_EX.

Nothing has changed in our application or on database, so we don't know the reason for this. We've tried to identify the offending query (if there is one) without success.

The problem starts to occur all of a sudden and a couple of hours later it goes away. There are no backups running except for the transaction log backup every 15 minutes taking 1 to 2 minutes.

tempdb is using 8 files, on a 2 CPU x 4 cores machine. Anyway, the latch seems to be unrelated to IO problems.

We have 256Gb of RAM.

CPU is normal on the machine for the load it has (80%), and IO is normal also. The number of transations processed is also normal.

Here is the result of SELECT @@version:

Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
    Dec 28 2012 20:23:12
    Copyright (c) Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.2  (Build 9200: )

Any ideas on what is happening or where should we look to find the problem?

Apparently we were able to solve the problem. There is one particular UDF that is being called thousands of times per second, and it declares 3 table variables. For most of the calls, the UDF returns before declaring the tables, but I did not realize that SQLServer creates them anyway. We separated the second part of the query to a new UDF that is called only when necessary. This way, most of the times, the table variables are not created (obtained from cache to be more precise). By doing this, we were able to significantly reduce the perf index Active Temp Tables, and SQL Server returned to normal behavior.

For those that did not know this: if an ad-hoc query, PROC or UDF declare a table variable (or a temporary table), SQL Server creates the tables, before starting execution. So, an early return does not prevent temp tables from being created. This completely fooled this accidental DBA. I was trying to find the culprit by changing queries one by one, putting a return in the beginning. Because of this behavior (which is understandable), I was failing to find the root cause of the contention on the DB. Live and learn every day!

For those who are now saying that it does not make sense to have a query called so many times, you are right! We are already changing the application to cache the results outside SQL Server.

Thank you Aaron and RTL for your help.

Renato Ferreira


Select data with a join I just started learning SQL now and trying to figure out this scenario:

We have 3 tables:

Clients (ClientID, Name, Surname, Age)
Products (ProductID, Name, Price)
Purchases (PurchaseID, Date, ClientID, ProductID)

What would be the best SQL query that will show the quantity of purchases (how many products they bought) made by clients?

It must only show the clients who made more than 1 purchase.

The result should contain the following fields:
Full name, Quantity of purchases.

You could do it like this:

SELECT  CONCAT(Name,' ', Surname) as FullName,
        COUNT(purchaseId) as "Quantity of purchases"
FROM Purchases as P
INNER JOIN Clients as C
on P.ClientID = C.ClientID
GROUP BY C.ClientID,Name, Surname
HAVING COUNT(PurchaseId) > 1;

DB-Fiddle

As a remark, if Fullname or Surname can be NULL then you would have to add a check for that, could be done with IFNULL()

SELECT  CONCAT(IFNULL(Name,''),' ', IFNULL(Surname,'')) as FullName,
        COUNT(purchaseId) as "Quantity of purchases"
FROM Purchases as P
INNER JOIN Clients as C
on P.ClientID = C.ClientID
GROUP BY C.ClientID,Name, Surname
HAVING COUNT(PurchaseId) > 1;


Is it possible to create this type of database for a massive forum? Kind of like how craigslist you select a state, then a city, and you're in that community.

How do I make my forum website's home page have 2 drop menus for a state and city, then it would bring the member to a specific Forum? is this really difficult?

Please direct me to what I need to learn to make this happen!

You can choose this option

Create on table to list all the state as below

State table with two columns

StateId - int(PK)
StateName - varchar

City table with two column as

CityId - int(PK)
StateId - int(FK)
StateName - varchar

Now you can have User table to save member where you add column CityId as 'FK' as below

UserId - Int/bigInt(PK)
CityId - int(FK)
..Other columns

Now in webpage you show list of state, and on selcting state populate city list from City table and on selecting city populate members.


SQL Server 2012 Availability group disk configuration options Background
I am quite far into the process of setting up a 2 server SQL Server 2012 Always on availability cluster to provide redundancy with a readable secondary replica of all our databases.
Since the hardware was ordered we've had a professional DBA audit our database and he's suggested separating our data, logs and tempdb. Unfortunately we do not have the capacity for the ideal configuration he's suggested. I therefore need feedback on my ideas for a suitable compromise...
Current setup
single server has 8 hard drive slots. We have a 2 disk RAID 1 for the OS, a 4 disk RAID 10 for data, logs and tempdb and 2 spares. I want to change this to increase performance.
Proposed setup
2 servers, each with 8 slots. Synchronous replication in the AG with auto-failover and the secondary to be readable for backups and reporting.
OS leave as is, a 2 disk RAID 1
tempdb has the most stalls by a long way so first off I'd like to put that on a separate drive, a single SSD for optimal random reads and writes.
data The read/write ratio of our data files is about 12:1 so read performance is key here. Ideally for this drive we would use RAID 10 or 0+1 with 4 disks for redundancy but we don't have enough slots. As we have the redundancy and auto-failover of the AG (the data is replicated on an identical drive on another server) I'm proposing a 2 disk RAID 1.
logs We have 3 slots left and need optimal write performance. Again, redundancy is not an issue as the 2nd host in the AG will have a replica of the logs. I am leaning towards a 2 disk RAID 0 here to avoid the write overhead of RAID 1.
We would then have a slot spare for backups and anything else non-critical.
Question

Should the log disk be RAID 1, RAID 0 or other?
Is the overall solution above the best option for me? If not, why

I would only use RAID 0 for the logs if you're okay with having reduced disaster recovery capability. You won't be able to make log tail backups in the event of a disk failure, and will only be able to restore to your most recent backup. However, this may be less of an issue if you're running Availability Groups, particularly with synchronous mirroring. You generally won't have any performance overhead for RAID 1 vs. a single non-RAID disk, but you will have better I/O performance with RAID 0 instead of RAID 1 for an equal number of disks.
Be aware that tempdb also has both data files and log files, and those could end up interfering with each other (performance wise) if placed on the same volume. You may want to consider foregoing the spare disk and using it for tempdb logs instead if performance still isn't acceptable. If your C: array is large enough, you might be able to get away with using it for on-disk backups, though you should probably partition it to prevent runaway backups from filling your OS disk.


Difference between joining one table twice times and operator OR I have a table Orders that contains creatorId and editorId. My query is trying to compare a current user userId = creatorId OR userId = editorId. So I must join table 2 times. It seems like that:

select...
from       Orders o
inner join Employee e1
on         o.creatorId = e1.userId
inner join Employee e2
on         o.ediorId = e2.userId

--It returns 174 rows.

But I'm really incredulous when using OR operator. The query will be:

select...
from       Orders o
inner join Employee e
on         e.userId = o.creatorId
           OR
           e.userId = o.editorId

--It returns 181 rows.

The result has a small difference here. It means 2 query upper is not same. So I will try by another way, using UNION. The query may like:

select...
from       Orders o
inner join Employee e
on         e.userId = o.creatorId
UNION
select...
from       Orders o
inner join Employee e
on         e.userId = o.editorId

--It returns 181 rows.

And finally, I will check the difference (missing rows) by using EXCEPT:

select...
from       Orders o
inner join Employee e
on         e.userId = o.creatorId
           OR
           e.userId = o.editorId
EXCEPT
select...
from       Orders o
inner join Employee e1
on         e1.userId = o.creatorId
inner join Employee e2
on         e2.userId = o.editorId

--It returns 7 missing rows.

Single query (temporarily called as first single query):

select...
from       Orders o
inner join Employee e
on         e.userId = o.creatorId

return 174 rows same to:

select...
from       Orders o
inner join Employee e
on         e.userId = o.editorId

My friend said that the second inner join is USELESS/MEANINGLESS for this case (the query that inner join one table 2 times is same as 1st single query). Though I have found that depending on several cases, sometimes they can join a table more than 2 times. But I'm really don't know to explain the difference between these upper queries. Please give me some advice, which query is right? Thanks so much.

I've set up the below sample: (There is a link at the end of the answer)

create table orders (orderId int primary key, creatorId int, editorId int);
create table users  (userId int primary key, name varchar(10));
insert into users values (1, 'A'),(2, 'B'),(3, 'C'),(4,'D');
insert into orders values
(1, 1, 2), (2, 1, 3), (3, null, 4), (4, 2, null);
GO

First let me have a look at which rows are returned using a single JOIN for both conditions.

select     *
from       orders
inner join users
on         creatorId = userId;
GO

orderId | creatorId | editorId | userId | name
------: | --------: | -------: | -----: | :---
      1 |         1 |        2 |      1 | A
      2 |         1 |        3 |      1 | A
      4 |         2 |     null |      2 | B

select     *
from       orders
inner join users
on         editorId = userId;
GO

orderId | creatorId | editorId | userId | name
------: | --------: | -------: | -----: | :---
      1 |         1 |        2 |      2 | B
      2 |         1 |        3 |      3 | C
      3 |      null |        4 |      4 | D

As you can see orders 1,2,4 match firts condition: creatorId = userId and orders 1,2,3 match the second one: editorId = userId

This should give you one idea about what will happen using OR and joining twice table users with editorId and creatorId.

Ok, first we will try using OR:

select     *
from       orders
inner join users
on         editorId = userId
or         creatorId = userId;
GO

orderId | creatorId | editorId | userId | name
------: | --------: | -------: | -----: | :---
      1 |         1 |        2 |      1 | A
      2 |         1 |        3 |      1 | A
      1 |         1 |        2 |      2 | B
      4 |         2 |     null |      2 | B
      2 |         1 |        3 |      3 | C
      3 |      null |        4 |      4 | D

That is, it returns orders 1,2,3 plus orders 1,2,4, all the records returned by first single query plus all records returned by the second one.

But what happens when you join twice users table:

select     *
from       orders
inner join users u1
on         editorId = u1.userId
inner join users u2
on         creatorId = u2.userId;
GO

orderId | creatorId | editorId | userId | name | userId | name
------: | --------: | -------: | -----: | :--- | -----: | :---
      1 |         1 |        2 |      2 | B    |      1 | A
      2 |         1 |        3 |      3 | C    |      1 | A

Hey, only orders 1,2 are there. If you remember the single queries, which returned rows are equals in both results? 1 and 2, and that's what you asked for: Give me the records where creatorId match a user AND editorId match a user too.

Humm, ok, what happens if I use AND instead of OR:

select     *
from       orders
inner join users
on         editorId = userId
AND        creatorId = userId;
GO

orderId | creatorId | editorId | userId | name
------: | --------: | -------: | -----: | :---

No rows are returned, ok what have we asked here?

Give me the rows where one editorId = creatorId.

select     *
from       orders
inner join users
on         creatorId = userId
UNION
select     *
from       orders
inner join users
on         editorId = userId;
GO

  Ok, now with UNION:

orderId | creatorId | editorId | userId | name
------: | --------: | -------: | -----: | :---
      1 |         1 |        2 |      1 | A
      1 |         1 |        2 |      2 | B
      2 |         1 |        3 |      1 | A
      2 |         1 |        3 |      3 | C
      3 |      null |        4 |      4 | D
      4 |         2 |     null |      2 | B

What was the question: Give all records that match first condition PLUS all records that match second condition, that is: orders 1,2,3 + orders 1,2,4

Using EXCEPT condition:

select     orderId, creatorId, editorId, userId, name
from       orders
inner join users
on         editorId = userId
or         creatorId = userId
EXCEPT
select     orderId, creatorId, editorId, u1.userId, u1.name
from       orders
inner join users u1
on         editorId = u1.userId
inner join users u2
on         creatorId = u2.userId;
GO

orderId | creatorId | editorId | userId | name
------: | --------: | -------: | -----: | :---
      1 |         1 |        2 |      1 | A
      2 |         1 |        3 |      1 | A
      4 |         2 |     null |      2 | B
      3 |      null |        4 |      4 | D

Question: Give all records returned by the query using OR: Orders: 1,2,1,4,2,3 except those records returned joining twice users table, Orders: 1, 2

Well now the final point on your question:

  which query is right?

I don't know. It depends on what is the desired result.

Please, tell us what are you trying to accomplish with your query.

Ok, your last comment clarify your question:

  My target is getting all data that userId=creatorId or userId=editorId without duplicating any orders

The easiest way is to use DISTINCT:

select     distinct orderId, editorId, creatorId
from       orders
inner join users
on         editorId = userId
or         creatorId = userId;
GO

orderId | editorId | creatorId
------: | -------: | --------:
      1 |        2 |         1
      2 |        3 |         1
      3 |        4 |      null
      4 |     null |         2

Or you can use a LEFT JOIN if you want to add information of users table.

select     orderId, creatorId, u2.Name as creatorName, editorId, u1.Name as editorName
from       orders
left  join users u1
on         editorId = u1.userId
left  join users u2
on         creatorId = u2.userId;
GO

orderId | creatorId | creatorName | editorId | editorName
------: | --------: | :---------- | -------: | :---------
      1 |         1 | A           |        2 | B
      2 |         1 | A           |        3 | C
      3 |      null | null        |        4 | D
      4 |         2 | B           |     null | null

dbfiddle here


How to select status variables into temp variables in mysql I have selected global variables into temporary local variables like below.

mysql> select @@long_query_time into @a;
Query OK, 1 row affected (0.00 sec)

mysql> select @a;

+------+
| @a   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

But when i am trying to do the same for status variables like below , i am getting error.

mysql> select @@qcache_hits;
ERROR 1193 (HY000): Unknown system variable 'qcache_hits'

I know we can select status variables into temp variables using information_schema tables like below.

SELECT @a := variable_value from information_schema.global_status where variable_name='com_select';

But I want to know if there is any way to select server status variables into temp variables without using information_schema tables.

Thanks in advance.

You can use information_schema's table for that purpose:

mysql> select VARIABLE_VALUE into @qcache_hits FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE  VARIABLE_NAME="qcache_hits";
Query OK, 1 row affected (0.00 sec)

mysql> select @qcache_hits;
+--------------+
| @qcache_hits |
+--------------+
| 0            |
+--------------+
1 row in set (0.00 sec)


Oracle Online Patching -- How To Exclude Tables in a Custom Schema Is it possible to exclude specific table objects from online patching? I believe the custom schema in which these tables exist is included in online patching. We are running 11g Enterprise Edition Release 11.2.0.4.0

@Jeff As your database is 12c, you can query dba_objectes and check the the oracle_maintained column, e.g. select OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS, ORACLE_MAINTAINED from dba_objects where OBJECT_NAME='MyObject';. If ORACLE_MAINTAINED is set to 'Y', then your object is likely to be affected by patching, if not it will be ignored.


How to do aggregate functions in GROUP_CONCAT I am generating a report to show outstanding Fee Like which Fee Type, How much amount is Fixed for that Fee Type, How Much Amount Paid for that Fee type and finally Balance Amount to be paid for that fee Type.

Here I am pulling data from 5 tables like Class Names from Classes table, Roll no & Student Name from admissions table, Fee Types from Feevars table, amount fixed for fee type from studentfees table, and finally amounts paid for fee types from fee collection table.

I am able to generate the partial results by mentioning the fee type names in select statment by summing and subtracting operations.

Here is the full database and my query producing the result. Please review  **@ my query in this demo, in the select statement I have mentioned the fee types manually.
But I want to generate the result without mentioning the Fee type as Column names.

For this I did one thing, I had taken all the fee types into a sql variable
like this

set @sqlList = null;SELECT GROUP_CONCAT(concat('tsf.', Replace(FeeName,' ',''))) INTO @sqlList FROM tbl_feevars;

this will result all the Fee types into single line as column names.
And Finally I have written code to produce output what I am expecting, but I am getting error like Error Code 1064: You have error in your sql syntax.

This is My final code

Expected Output Code

Please anyone tell me, what is error in my sql query. And suggest me if any?? other way to do this report.

My Tables:

CREATE TABLE `tbl_admission` (
  `AdmNo` varchar(50) NOT NULL,
  `Grp` varchar(45) NOT NULL,
  `Cls` varchar(45) NOT NULL,
  `rollno` int(11) NOT NULL,
  `StdNm` varchar(50)
);

CREATE TABLE `tbl_classes` (
  `C_Id` int(11),
  `C_Name` varchar(255) NOT NULL,
  `G_Id` int(11) NOT NULL,
  `status` tinyint(1)
);

CREATE TABLE `tbl_feevars` (
  `id` int(11) NOT NULL,
  `FeeName` varchar(70) NOT NULL
);

CREATE TABLE `tbl_stdfees` (
  `id` int(11),
  `Admno` varchar(50) default NULL,
  `admissionfee` float(10,2) default '0.00',
  `splfee` float(10,2) default '0.00',
  `tutionfee` float(10,2) default '0.00',
  `computerfee` float(10,2) default '0.00',
  `stationary` float(10,2) default '0.00',
  `transport` float(10,2) default '0.00',
  `hostelfee` float(10,2) default '0.00',
  `lab` float(10,2) default '0.00',
  `library` float(10,2) default '0.00',
  `miscell` float(10,2) default '0.00'
  );

CREATE TABLE `tbl_feecollection` (
  `auto_id` int(11) NOT NULL auto_increment,
  `GrupNm` varchar(40) NOT NULL,
  `class` varchar(30) NOT NULL,
  `rollno` int(2) NOT NULL,
  `sadmno` int(11) NOT NULL,
  `PaM` varchar(30) NOT NULL,
  `DDCN` int(25) NOT NULL,
  `bank` varchar(30) NOT NULL,
  `cheqd` date NOT NULL,
  `remarks` varchar(150) NOT NULL,
  `pdate` date NOT NULL,
  `admissionfee` float(10,2) default '0.00',
  `splfee` float(10,2) default '0.00',
  `tutionfee` float(10,2) default '0.00',
  `computerfee` float(10,2) default '0.00',
  `stationary` float(10,2) default '0.00',
  `transport` float(10,2) default '0.00',
  `hostelfee` float(10,2) default '0.00',
  `lab` float(10,2) default '0.00',
  `library` float(10,2) default '0.00',
  `miscell` float(10,2) default '0.00',
  PRIMARY KEY  (`auto_id`)
);

My current query is:

set @sqlList = null;
SET @sqlList1 = null;
set @query = null;
SELECT
  GROUP_CONCAT(concat('tsf.', Replace(FeeName,' ','')))
INTO @sqlList
FROM tbl_feevars;
SELECT
  GROUP_CONCAT(concat('tfc.', Replace(FeeName,' ','')))
INTO @sqlList1
FROM tbl_feevars;
SET @query
  = CONCAT('SELECT tsf.Admno,',@sqlList,',ta.StdNm,
                    sum(',@sqlList1,') as ''feepaid'',
                    (',@sqlList,' - sum(',@sqlList1,')) as ''outstanding fee''
                FROM
                    tbl_stdfees tsf,
                    tbl_Admission ta,
                    tbl_feecollection tfc
                where
                    tsf.admno = ta.admno
                    and tfc.sadmno = tsf.admno group by ta.stdnm);');

select @query;

PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Your query (the one that doesn't work) is:

   SELECT
      tsf.Admno,tsf.AdmissionFee,tsf.SplFee,
      tsf.TutionFee,tsf.ComputerFee,tsf.Stationary,
      tsf.Transport,tsf.Hostelfee,tsf.Lab,tsf.Library,
      tsf.Miscell,ta.StdNm,
      SUM(
          tfc.AdmissionFee,tfc.SplFee,tfc.TutionFee,
          tfc.ComputerFee,tfc.Stationary,tfc.Transport,
          tfc.Hostelfee,tfc.Lab,tfc.Library,tfc.Miscell ) AS 'feepaid',
      (SUM(
          tsf.Admno,tsf.AdmissionFee,tsf.SplFee,
          tsf.TutionFee,tsf.ComputerFee,tsf.Stationary,
          tsf.Transport,tsf.Hostelfee,tsf.Lab,tsf.Library,
          tsf.Miscell
          ) -
       SUM(
          tsf.Admno,tsf.AdmissionFee,tsf.SplFee,
          tsf.TutionFee,tsf.ComputerFee,tsf.Stationary,
          tsf.Transport,tsf.Hostelfee,tsf.Lab,tsf.Library,
          tsf.Miscell
          )
      ) AS 'outstanding fee'
      FROM tbl_stdfees tsf,
           tbl_admission ta,
           tbl_feecollection tfc
      WHERE
           tsf.admno = ta.admno
           AND tfc.sadmno = tsf.admno
      GROUP BY ta.stdnm;

You are using SUM, an aggregate function that sums up values in the same column, but you are just adding up the individual column values in one row - so MySQL gives you an error message. If you change the query as below, it will work.

SELECT DISTINCT
      tsf.Admno,tsf.AdmissionFee,tsf.SplFee,
      tsf.TutionFee,tsf.ComputerFee,tsf.Stationary,
      tsf.Transport,tsf.Hostelfee,tsf.Lab,tsf.Library,
      tsf.Miscell,ta.StdNm,
      tfc.AdmissionFee + tfc.SplFee + tfc.TutionFee +
      tfc.ComputerFee + tfc.Stationary + tfc.Transport +
      tfc.Hostelfee + tfc.Lab + tfc.Library + tfc.Miscell AS 'feepaid',
      ( (tsf.Admno + tsf.AdmissionFee + tsf.SplFee +
          tsf.TutionFee + tsf.ComputerFee + tsf.Stationary +
          tsf.Transport + tsf.Hostelfee + tsf.Lab + tsf.Library +
          tsf.Miscell)
          -
          (tfc.AdmissionFee + tfc.SplFee + tfc.TutionFee +
          tfc.ComputerFee + tfc.Stationary + tfc.Transport +
          tfc.Hostelfee + tfc.Lab + tfc.Library + tfc.Miscell)
        ) AS 'outstanding fee'
      FROM tbl_stdfees tsf,
           tbl_admission ta,
           tbl_feecollection tfc
      WHERE
           tsf.admno = ta.admno
           AND tfc.sadmno = tsf.admno;

Since you are not using an aggregate function, you don't need to use GROUP BY either.

Result:

  Admno | AdmissionFee | SplFee | TutionFee | ComputerFee | Stationary | Transport | Hostelfee | Lab  | Library | Miscell | StdNm | feepaid | outstanding fee
  1     |      6000.00 |   0.00 |      0.00 |     2500.00 |    1500.00 |      0.00 |      0.00 | 0.00 |    0.00 | 3000.00 | zzz   | 1400.00 |           11601
  2     |      5000.00 |   0.00 |      0.00 |     2500.00 |    1500.00 |      0.00 |      0.00 | 0.00 |    0.00 | 5000.00 | xxyy  | 1500.00 |           12502 |
  2 rows in set (0.01 sec)

That being said:
In my experience the database is much easier to maintain, if you'd restructure the tables and normalize them, have one row for each of the fees, and then use SUM and GROUP BY. You then could add fee types without changing the table structure, and your scripts don't need to know which fees to expect.

For example, change the tbl_stdfees to:

CREATE TABLE tbl_newfees (
   id INT AUTO_INCREMENT,
   Admno VARCHAR(50) NOT NULL,
   fee_id int,
   fee FLOAT(10,2) DEFAULT 0.00,
   PRIMARY KEY (id, fee_id, fee),
   CONSTRAINT FOREIGN KEY (fee_id) REFERENCES tbl_feevars.id);

This assumes that you only have one fee per fee type per id. If several fees might occur of the same type, you could add an additional identifier like a date. Use REPLACE to either insert a new few or overwrite an older fee.

Not taking in account the date and not making provisions for multiple fees of the same type bothers me, I would take provisions for it in production.

Also, I wouldn't allow a NULL value for Admno - especially since you are grouping by it (in this scenario).

Example:

mysql> select * from tbl_newfees;
+----+-------+--------+---------+
| id | Admno | fee_id | fee     |
+----+-------+--------+---------+
|  1 | 1     |      1 | 6000.00 |
|  2 | 1     |      4 | 2500.00 |
|  3 | 1     |      5 | 1500.00 |
|  4 | 1     |     10 | 3000.00 |
|  5 | 2     |     10 | 5000.00 |
|  6 | 2     |      5 | 1500.00 |
|  7 | 2     |      4 | 1500.00 |
|  8 | 2     |      1 | 5000.00 |
+----+-------+--------+---------+

This takes advantage of your tbl_feevars table - you'd have to define the FeeName only once.

mysql> select Admno,sum(fee) from tbl_newfees group by Admno;
+-------+----------+
| Admno | sum(fee) |
+-------+----------+
| 1     | 13000.00 |
| 2     | 14000.00 |
+-------+----------+

Another table for the paid fees - the meta information should go into a separate table and get joined in, however, if the bank, remarks, PaM, etc are specific for each individual fee, which they might be, the could be in the paid fees table as well. If they are general to the fee type, they could be in the tbl_feevars table. You know your system best.

mysql> select * from tbl_paidfees;
+----+-------+--------+---------+
| id | Admno | fee_id | fee     |
+----+-------+--------+---------+
|  1 | 1     |      1 | 6000.00 |
|  3 | 1     |      5 | 1500.00 |
|  4 | 1     |     10 | 3000.00 |
|  5 | 2     |     10 | 5000.00 |
|  6 | 2     |      5 | 1500.00 |
|  8 | 2     |      1 | 5000.00 |
+----+-------+--------+---------+

(this is a bit different from your example data, I just deleted the fee_id 4 entries complared to the other table)

I then select for the sums using a left outer join, which assumes that all outstanding fees have been entered into the table before an entry in the paid fees table has been made:

SELECT n.Admno,
       SUM(n.fee) AS "Total Fees",
       SUM(p.fee) AS "Total Paid",
       SUM(n.fee)-sum(p.fee) as "Outstanding Balance"
FROM tbl_newfees n
       LEFT OUTER JOIN tbl_paidfees p
       ON n.Admno=p.Admno AND p.fee_id=n.fee_id
GROUP BY n.Admno;

Results:

+-------+------------+------------+---------------------+
| Admno | Total Fees | Total Paid | Outstanding Balance |
+-------+------------+------------+---------------------+
| 1     |   13000.00 |   10500.00 |             2500.00 |
| 2     |   14000.00 |   11500.00 |             2500.00 |
+-------+------------+------------+---------------------+

To query for more detailed information for a specific individual, for example, you could do:

SELECT n.Admno, v.FeeName, n.fee AS "Total Fee", p.fee AS "Fee Paid"
FROM tbl_feevars v,
     tbl_newfees n LEFT OUTER JOIN tbl_paidfees p
                   ON n.fee_id=p.fee_id AND n.Admno = p.Admno
WHERE v.id = n.fee_id
ORDER BY n.Admno;

+-------+---------------+-----------+----------+
| Admno | FeeName       | Total Fee | Fee Paid |
+-------+---------------+-----------+----------+
| 1     | Admission Fee |   6000.00 |  6000.00 |
| 1     | Computer Fee  |   2500.00 |     NULL |
| 1     | Stationary    |   1500.00 |  1500.00 |
| 1     | Miscell       |   3000.00 |  3000.00 |
| 2     | Admission Fee |   5000.00 |  5000.00 |
| 2     | Computer Fee  |   2500.00 |     NULL |
| 2     | Stationary    |   1500.00 |  1500.00 |
| 2     | Miscell       |   5000.00 |  5000.00 |
+-------+---------------+-----------+----------+

Similarly, you can left join the tbl_feevars table to see which fees have not been entered for a student into any of the fees tables.

To display the results in one row, you could use the pivot table approach, similar to https://stackoverflow.com/questions/17680926/adding-columns-while-inserting-a-row-in-another-table/17681027#17681027, or just query regularly and assemble the result externally in a script.


Display non-null and null count using case statement In total there are 10 values in my table, 5 of which are null. I need to display the count using case statement in Oracle, like output below:

Sample Data:

+------+
| Eid  |
+------+
| 1    |
| 2    |
| 3    |
| 4    |
| 5    |
| NULL |
| NULL |
| NULL |
| NULL |
| NULL |
+------+

Output:

+--------+--------+
|  Eid   | Counts |
+--------+--------+
| Number |      5 |
| Null   |      5 |
+--------+--------+

This may not directly help you, since I only know SQL Server, but in any case this is how I would approach this:

Sample Data:

CREATE TABLE #Example
(
    Eid integer NULL
);

INSERT #Example
    (Eid)
VALUES
    (1),
    (2),
    (3),
    (4),
    (5),
    (NULL),
    (NULL),
    (NULL),
    (NULL),
    (NULL);

My idea revolves around the idea that COUNT(column) does not count NULLs, whereas COUNT(*) does:

SELECT
    [Number] = COUNT(E.Eid),
    [Null] = COUNT(*) - COUNT(E.Eid)
FROM #Example AS E;

Output 1:

+--------+------+
| Number | Null |
+--------+------+
|      5 |    5 |
+--------+------+

If the specific arrangement of rows and columns is important, I would UNPIVOT:

WITH Previous AS
(
    -- Same query as above
    SELECT
        [Number] = COUNT(E.Eid),
        [Null] = COUNT(*) - COUNT(E.Eid)
    FROM #Example AS E
)
SELECT
    U.Eid,
    U.Counts
FROM Previous
UNPIVOT
(
    Counts FOR Eid IN ([Number], [Null])
) AS U;

Output 2:

+--------+--------+
|  Eid   | Counts |
+--------+--------+
| Number |      5 |
| Null   |      5 |
+--------+--------+

Using CASE

SELECT
    C.Name,
    Counts = COUNT(*)
FROM
(
    SELECT
        Name =
            CASE
                WHEN E.Eid IS NULL
                THEN 'Null'
                ELSE 'Number'
            END
    FROM #Example AS E
) AS C
GROUP BY
    C.Name;

Output 3:

+--------+--------+
|  Name  | Counts |
+--------+--------+
| Null   |      5 |
| Number |      5 |
+--------+--------+

Edit:

This is the query form Babu ended up using:

select
    count(*) as cnt,
    case
        when smpth is null
        then 'NULL'
        else 'NUMBER'
    end numbertest
from employees
group by
    case
        when smpth is null
        then 'NULL'
        else 'NUMBER'
    end numbertest


Trying to understand MySQL Replication with SSL I'm trying to get my head around the use of SSL with MySQL replication.
I've spent all day reading blog posts / articles about this, and I'm still no closer to getting an answer to this question:

Essentially I'm trying to understand the relationship between the ca, client & server files.

In my fictional setup I have:

1 x Master DB - Main Site
5 x Slave DBs - Main Site
Each Slave DB connects to a Slave DB on a remote (all different) Customer Site.

Am I right in thinking therefore that I would:

generate the ca-cert.pem and ca-key.pem files on the Master DB
create a set of client-xxx and server-xxx files on each Slave DB (and a copy of the ca-xxx.pem files)
Onto each Customer DB I would copy the ca-cert.pem,client-cert.pem,client-key.pem from the matching Slave DB

If one of the customer DB's was compromised would I need to generate a new ca-cert.pem file on the master, and re-distribute it to all remaining slaves & customer DB's (requiring all my DB's to be restarted).
Or could I just delete the server-cert.pem file from that one slave server?

(obviously I could revoke the username/password - but I'm trying to understand the SSL side at present).

Create the server and client cert/key files on the master server and then copy only the client cert/key files to each slave server and configure SSL replication. So basically you have the same client cert and key on all slave servers. If a slave gets compromised change the replication user's credentials.


Taking a backup of a single table in the SQL Server I know how to take a backup of a database in SQL Server and restore it. But how can I take a backup of a single table, and then restore it? Is it possible?

I have a database with two different names (some different data) and I have to restore one table by taking it from one database to the other. How can I take a single table backup from one database and then restore it to the other one?

Right click the database; Tasks, Generate Scripts & enable Script Data from the options. Select the table and Finish.

This will generate a CREATE TABLE followed by INSERTs for the data.


CASE with 2 conditions Following case statement is not working.

case when (product_name = 'premium' AND SUM(answered_calls)>0 then (SUM(total_duration)/60.0)/SUM(answered_calls)) else 0 end AS Duration

Not sure if only that, but your are not closing the parenthesis correctly.

Same code with better indentation:

case
  when
    (product_name = 'premium' AND SUM(answered_calls)>0
  then
    (SUM(total_duration)/60.0)/SUM(answered_calls))
  else
    0
end AS Duration

See the problems:

case
  when
    (product_name = 'premium' AND SUM(answered_calls)>0
--  ^----------------------------- should close here --^
  then
    (SUM(total_duration)/60.0)/SUM(answered_calls))
--                      remove that closing one --^
  else
    0
end AS Duration


What collation to use for Ukraine? We have an accounting program with an SQL Server database and would like to start a Ukrainian version. I am not sure which collation to use: Ukrainian or Cyrillic_General?

With a non-technical Ukrainian, we compared the collation charts for Cyrillic_general and Ukrainian and found only minor differences towards the end of the table. That person thinks Cyrillic_general should do fine.

Also, SQL Server 2012 no longer lists the Ukrainian collation. Is this a reason for concern?

Any advice which to choose?

First, just to make sure that a collation is available or not, you can test in two ways:

Check the list of available collations (i.e. sys.fn_helpcollations()). Running the following query in SQL Server 2012 and 2014 should return 52 rows:

SELECT * FROM sys.fn_helpcollations() WHERE [name] LIKE N'ukrain%';

Try using the collation:

SELECT N'a' COLLATE Ukrainian_100_CI_AS;

If a collation is not available, it will let you know ;-)

Next, we should see if the collations work as expected (best not to fully trust documentation, right ;-) ?).

To test these two collations, run the following:

DECLARE @CollationTest TABLE (Col1 NVARCHAR(5));
INSERT INTO @CollationTest (Col1) VALUES (N'Ы'), (N'Ю'), (N'Ь'), (N'Ъ'), (N'Я'), (N'Э');

SELECT Col1 AS [Ukrainian_100_CS_AS]
FROM @CollationTest
ORDER BY Col1 COLLATE Ukrainian_100_CS_AS;

SELECT Col1 AS [Cyrillic_General_100_CS_AS]
FROM @CollationTest
ORDER BY Col1 COLLATE Cyrillic_General_100_CS_AS;

Results:

Ukrainian_100_CS_AS
----------------------------
Ъ
Ы
Э
Ю
Я
Ь

Cyrillic_General_100_CS_AS
----------------------------
Ъ
Ы
Ь
Э
Ю
Я

Those two orderings at least match the collations linked in the question (which makes sense given that those collation charts were done in 2009 and the _100 series of collations came out in SQL Server 2008).

The only difference seems to be in the initial sort order of one character: Ь (Capital Letter Soft Sign). Is the placement of one character a determining factor? You do need to mirror, as much as possible, the expectations of your customers. But, keep in mind that sort rules and initial ordering changes over the years.

Here are some examples of the Unicode spec for the Ukrainian locale. As you can see, both placements have been correct, though the "current" placement matches the Cyrillic collation, not the Ukrainian collation. The main release history of the locale-specific data starts here: CLDR Releases/Downloads

http://www.unicode.org/repos/cldr/tags/release-1-4/posix/uk_UA.UTF-8.src  (2006)
<CYRILLIC_CAPITAL_LETTER_SOFT_SIGN>                    <X5EF0>;<X0005>;<X008F>;IGNORE -- Ь
<CYRILLIC_CAPITAL_LETTER_YA>                           <X5EFC>;<X0005>;<X008F>;IGNORE -- Я

http://www.unicode.org/repos/cldr/tags/release-1-9/posix/uk_UA.UTF-8.src  (2010)
<CYRILLIC_CAPITAL_LETTER_YA>                           <X5D45>;<X05>;<X8F>;IGNORE -- Я
<CYRILLIC_CAPITAL_LETTER_SOFT_SIGN>                    <X5D46>;<X05>;<X86>;IGNORE -- Ь

http://www.unicode.org/repos/cldr/tags/release-21/posix/uk_UA.UTF-8.src   (2011)
<CYRILLIC_CAPITAL_LETTER_YA>
                           <X5D45>;<X05>;<X8F>;<CYRILLIC_CAPITAL_LETTER_YA>        -- Я
<CYRILLIC_CAPITAL_LETTER_SOFT_SIGN>
                           <X5D46>;<X05>;<X86>;<CYRILLIC_CAPITAL_LETTER_SOFT_SIGN> -- Ь

http://www.unicode.org/repos/cldr/tags/release-24/common/uca/allkeys_CLDR.txt  (2013)
042C  ; [.1B43.0020.0008.042C] # CYRILLIC CAPITAL LETTER SOFT SIGN -- Ь
042F  ; [.1B5E.0020.0008.042F] # CYRILLIC CAPITAL LETTER YA        -- Я

http://www.unicode.org/repos/cldr/tags/release-27/common/uca/allkeys_CLDR.txt  (2015)
042C  ; [.1E56.0020.0008] # CYRILLIC CAPITAL LETTER SOFT SIGN -- Ь
042F  ; [.1E6D.0020.0008] # CYRILLIC CAPITAL LETTER YA        -- Я

And to be fair, we should keep in mind that collations handle more than sorting. They handle casing, transformations, etc.

So what should one do? It is probably best, regardless of the rest of this, to choose the locale that is most common for your customers to be using as their main Windows OS locale. This will at least give consistent behavior to how everything else should be running for them. Run the following to see on your system:

SELECT os.os_language_version AS [WindowsLocale],
       COLLATIONPROPERTY(N'Ukrainian_100_CS_AS', 'lcid') AS [SQLServerLocale-Ukrainian],
    COLLATIONPROPERTY(N'Cyrillic_General_100_CS_AS', 'lcid') AS [SQLLocale-CyrillicGeneral]
FROM   sys.dm_os_windows_info os;

Ask as many of your customers / potential customers as possible to check their Windows setup so that you can plan accordingly.


We are running database maintenace with indexing the system, indexing fails due to locking? We have been trying to do re-indexing in our system, there are users still using the database during the indexing. While doing index for certain tables there is failure of indexing this seems to be due to deadlocks

the indexing script we use is:

--------  REBUILDING THE INDEXES----------------------

CREATE TABLE #TableIDs (Tablename nvarchar(200),IndexName nvarchar(1000))
        --Database to be indexed--
        INSERT INTO #TableIDs (Tablename, IndexName)
        SELECT a.object_id,name
        FROM sys.dm_db_index_physical_stats (DB_ID(N'RAL'), NULL, NULL, NULL, NULL) AS a
        JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
        Where avg_fragmentation_in_percent > 20 and name is not null  and OBJECT_SCHEMA_NAME(a.object_id) = 'dbo'
        And object_name(a.object_id) not in (SELECT
                                             OBJECT_NAME(p.object_id) BlockedObjectName
                                             FROM sys.dm_tran_locks AS tl
                                             INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
                                             INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
                                             INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
                                             INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
                                             INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
                                             CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
                                             CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
                                             Where db.name = 'RAL')

declare c cursor for select object_name(Tablename) as Tablename ,IndexName from #TableIDs
declare @TableName varchar(1000)
declare @IndexName varchar(1000)
open c
fetch next from c into @TableName, @IndexName
        while @@FETCH_STATUS = 0
        BEGIN

              if ((SELECT
                                         count(OBJECT_NAME(p.object_id)) BlockedObjectName
                                         FROM sys.dm_tran_locks AS tl
                                         INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
                                         INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
                                         INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
                                         INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
                                         INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
                                         CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
                                         CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
                                         Where db.name = 'RALMYT' and OBJECT_NAME(p.object_id) = @TableName) ) = 0
                BEGIN
                    EXEC ('ALTER INDEX ' + @IndexName + ' ON ' + @TableName + ' REBUILD WITH (FILLFACTOR=90,STATISTICS_NORECOMPUTE = OFF,ONLINE=OFF,MAXDOP=8)')
                    PRINT 'Reindexing ' + @IndexName + ' on  ' + @TableName + ' table'
                END
     fetch next from c into @TableName, @IndexName
End
CLOSE c
DEALLOCATE c

DROP TABLE #TableIDs

Please let me know..... how to avoid the errors or failures while indexing

If the requirement is to rebuild the indexes while letting users continue to work, you will find this extremely challenging, if not impossible, to meet if you can't do online index rebuilds, which are only available in Enterprise Edition.

There are really only two types of solutions:

Use online index rebuilds.
Do index REORGANIZE instead of REBUILD -- reorgs are always online operations, but may not do as good a job as a rebuild, and may run more slowly in comparison.

Regardless, instead of rolling your own scripts, I would highly recommend using an already-built-and-tested solution such as Ola Hallengren's maintenance scripts, which will intelligently maintain your indexes based on their current state of fragmentation. The indexes may not actually need to be rebuilt all the time.


Powershell Invoke-Sqlcmd Replacing £ with? When I create a SQL Script that inserts the value '£' or '€' into a table the value on the table is a question mark (?)
Though, when I insert '$' it inserts a dollar sign.

Anyone know what is going on and how to make sure a pound sign is inserted into the table?

I managed to test that the text file may have been the issue. When I opened up the text file and changed the encoding to Unicode, the £ and the € were inserted into the databsase.


Dropping large clustered primary key fills up tempdb Due to one of our tables growing outside of the range of an INT, something that wasn't predicted when the DB was produced, we're need to change the PK field to a BIGINT instead.

We are however running into problems regarding tempdb space usage at the very first step while handling that table: trying to drop the primary key constraint on it so we can alter it. None of our test environments have more space than 50 GB reserved for tempdb, while the table takes up around 120 GB of space right now. The table has but 5 columns and they contain only other INTs and a few short varchars.

Do any of you have any better, perhaps lighter suggestions where tempdb and logfile are concerned, to do the same changes? The table has only a few columns, all of which contain very little data. Sample below, as simple as it may be.

ALTER TABLE OURTABLE DROP CONSTRAINT OURTABLE_PK

ALTER TABLE OURTABLE ALTER COLUMN OURTABLE_ID BIGINT NOT NULL

ALTER TABLE OURTABLE ADD CONSTRAINT OURTABLE_PK PRIMARY KEY CLUSTERED (OURTABLE_ID)

Thanks!

I've been thinking about just creating a new table with the same definitions and a BIGINT as a bulk insert, then renaming it while dropping the old one. But I'm unsure whether that will prove to be any better a solution since the constraints would have to be put in afterwards anyway.

Assuming you have a maintenance window that allows for a short period of downtime I would suggest using BCP to dump the table to a file. If space is an issue, compress the target folder in advance of the export.

bcp.exe "Database.dbo.OurTable"  OUT "C:\Temp\bcp\OurTable.dat" -S ServerName -T -c -r "|¬|\n" -t "|¬|" /b 10000

DROP your old table, CREATE new with BIGINT key, then import the data.

BULK INSERT
    dbo.OurTable
FROM 'C:\Temp\bcp\OurTable.dat'
WITH
(
    FIELDTERMINATOR = '|¬|',
    ROWTERMINATOR = '|¬|\n',
    BATCHSIZE = 10000,
    ERRORFILE = 'C:\Temp\bcp\OurTable_error.txt'
)

The odd looking choice of delimiter |¬| is a typically safe combination that avoids collisions in text fields. So far anyway.

As you mention in the comments you can minimise growth of the log by switching to bulk-recovery. Given that you will have no other activity during this maintenance window you could switch to simple, run the process, switch back to full and take a full backup instead. Note that use of BATCHSIZE in the example will create a separate transaction for each batch.


What happens on the server when a MySQL client connection is lost? Imagine the following situation:
You have a MySQL server S and two clients, A and B.
Both clients connect to S (e.g. using the CLI).
A starts a transaction and then locks a table with WRITE permissions and inserts a row into the table.
Now B tries to select all rows from the table and - of course - has to wait until A has committed AND unlocked the table.
Imagine that A's connection is interrupted before it is able to commit or rollback. In my special use-case, the connection might be interrupted at any point in time.
I tested this scenario using three virtual machines which are connected to a common virtual bridge. Using brctl I can simply remove individual VMs in order to simulate connection interruptions.
The result
Client B is stuck, because it waits for the table to get unlocked.
Typing SHOW PROCESSLIST in the CLI of the server shows me  this:
+----+------+-----------------+----------+---------+------+---------------------------------+----------------------------+----------+
| Id | User | Host            | db       | Command | Time | State                           | Info                       | Progress |
+----+------+-----------------+----------+---------+------+---------------------------------+----------------------------+----------+
|  8 | root | B:38129         | foo      | Query   | 1676 | Waiting for table metadata lock | select blockname from root |    0.000 |
|  9 | root | A:54806         | foo      | Sleep   | 1679 |                                 | NULL                       |    0.000 |
| 12 | root | localhost       | NULL     | Query   |    0 | init                            | show processlist           |    0.000 |
+----+------+-----------------+----------+---------+------+---------------------------------+----------------------------+----------+

So the server thinks that the connection from A is still alive, while it already was lost minutes ago. Having a look at netstat also tells me that the connection is still considered to be ESTABLISHED and still alive.
I also observed the bridge: Not a single packet from S to A.
Client B is stuck forever. I have to restart the whole server to unlock the table.
What I expected
I want my server to detect (after some predefined time) that the connection to A is lost even if A did not send any more queries. Then I want my server to rollback any uncommitted transactions and finally to unlock the table.
How can I realise this?
Why aren't there any TCP keepalives from S to A?
Is it possible to define any kind of timeouts?

I played around with the variables wait_timeout, innodb_lock_wait_timeout, and interactive_timeout.

interactive_timeout is not interesting because my productive system will not use the CLI.
innodb_lock_wait_timeout seems not to be respected in any way.
wait_timeout indeed improves my situation: idling sessions are killed after the timeout. But I only want them to be killed in case of connection interruption. I want to keep the connection alive to improve performance (save tcp handshakes, SQL logins, db selection...)

In your case it shows that the session from client A is interrupted but not released on DB because of the MySQL instance has not terminated the connection from client A as it feels their is some calculation going on client side. The MySQL instance only stops/terminates the connections once it reaches the threshold time for session staying in IDLE state.
So, this IDLE state time limit is manage by wait_timeout parameter. So, you are suppose to set the value for wait_timeout parameter to your optimal usage of instance. Many of the cases 180 seconds seems to be ideal.


joining on 2 tables twice in MYSQL My problem is that I have following tables: packages, orders, order_items

order_items is halding id of package_id, order_id, and credits_number (which I need to retrieve)

order table can be with status = 1 (important for column credits_deducted_total) or 0 (important for credits_reserved_total)

credits_deducted_total and credits_reserved_total are giving me a problems because they should represent the number of deducted or reserved credits per project (which with following query is not the case).

Also 0 should be return as value for each project where I don't have orders with status = 0

Query which I was able to write:

select `packages`.`id` as `id`, `external_id`, `project_name`, `credits_available`, `credits_aq`,
`price`, `currency_code`, `packages`.`status`, `packages`.`updated_at`,
SUM(order_items.credits_number) as credits_deducted_total,
SUM(order_items_reserved.credits_number) as credits_reserved_total from `packages`
left join `order_items` on `order_items`.`package_id` = `packages`.`id`
left join `order_items` as `order_items_reserved` on `order_items_reserved`.`package_id` = `packages`.`id`
left join `orders` on `orders`.`id` = `order_items`.`order_id`
left join `orders` as `orders_reserved` on `orders_reserved`.`id` = `order_items_reserved`.`order_id`
where (`orders`.`status` = 1 or `orders_reserved`.`status` = 0)
group by `packages`.`id`;

select `packages`.`id` as `id`,
       `external_id`,
       `project_name`,
       `credits_available`,
       `credits_aq`,
       `price`,
       `currency_code`,
       `packages`.`status`,
       `packages`.`updated_at`,
       SUM(order_items.credits_number) as credits_deducted_total,
       SUM(order_items_reserved.credits_number) as credits_reserved_total
from `packages`
left join `order_items`
     on `order_items`.`package_id` = `packages`.`id`
left join `order_items` as `order_items_reserved`
     on `order_items_reserved`.`package_id` = `packages`.`id`
left join `orders`
     on `orders`.`id` = `order_items`.`order_id`
    AND `orders`.`status` = 1
left join `orders` as `orders_reserved`
     on `orders_reserved`.`id` = `order_items_reserved`.`order_id`
    AND `orders_reserved`.`status` = 0
group by `packages`.`id`;

Alternatively:

select `packages`.`id` as `id`,
       `external_id`,
       `project_name`,
       `credits_available`,
       `credits_aq`,
       `price`,
       `currency_code`,
       `packages`.`status`,
       `packages`.`updated_at`,
       SUM(CASE WHEN `orders`.`status` = 1
                THEN order_items.credits_number
                ELSE 0
           END) as credits_deducted_total,
       SUM(CASE WHEN `orders`.`status` = 0
                THEN order_items.credits_number
                ELSE 0
           END) as credits_reserved_total
from `packages`
left join `order_items`
     on `order_items`.`package_id` = `packages`.`id`
left join `orders`
     on `orders`.`id` = `order_items`.`order_id`
group by `packages`.`id`;


Why does an alias with a 'having' clause not exist in PostgreSQL? I am trying to calculate distance between two coordinates and fetching some information based on a few conditions. A similar query with the 'having' clause was working in MySQL. But why is it not working in PostgreSQL?
This is my query:
    SELECT *, ( 6371 * acos( cos( radians(latitude) ) * cos( radians( latitude1 ) ) *
    cos( radians( longitude1 ) - radians(longitude) ) + sin( radians(latitude) ) *
    sin( radians( latitude1 ) ) ) ) AS distance from table WHERE
    verified=true AND best_for LIKE '%xyz%' AND uuid NOT IN (SELECT uuid::uuid FROM table2 WHERE
    from_date BETWEEN '2020-12-17 06:30'::date AND '2020-12-18 12:30'::date AND
    to_date BETWEEN '2020-12-17 06:30'::date AND '2020-12-18 12:30'::date  AND )
    HAVING distance < 50 ORDER BY distance

Why does distance not exist? And how can I modify this query to make it work in PostgreSQL?

That's the rule the SQL standard sets (and MySQL ignores some of the rules the standard defines and allows invalid SQL).
You can't use a column alias on the same level where you defined it and having is only allowed in a query that uses aggregation. If you want to avoid repeating the expression, use a derived table.
It's also typically faster to use NOT EXISTS instead of NOT IN
SELECT *
FROM (
  SELECT *,
        ( 6371 * acos( cos( radians(latitude) ) * cos( radians( latitude1 ) ) *
          cos( radians( longitude1 ) - radians(longitude) ) + sin( radians(latitude) ) *
          sin( radians( latitude1 ) ) ) ) AS distance
  from table t1
  WHERE verified = true
    AND best_for LIKE '%xyz%'
    AND NOT EXISTS (SELECT *
                     FROM table2 t2
                     WHERE t2.from_date BETWEEN '2020-12-17 06:30'::date AND '2020-12-18 12:30'::date
                       AND t2.to_date BETWEEN '2020-12-17 06:30'::date AND '2020-12-18 12:30'::date
                       AND t2.uuid::uuid = t1.uuid)
) x
WHERE distance < 50
ORDER BY distance


Query a view in a procedure in which the view name is a variable My problem: I have a set of views from which I want to query dynamically.
I want to load the view names by SELECT * from sys.all_views into a cursor (let's call it view_cursor) and then do something like this in a procedure:

-- loop starts
FETCH NEXT FROM view_cursor INTO @view_name;
SELECT * FROM @view_name;
-- fetch next
-- loop ends

I have already tried this out with one view only which I pass as a parameter to a stored procedure:

CREATE PROCEDURE [dbo].[query_Special_View](@view_name VARCHAR(100)) AS
DECLARE viewname VARCHAR(100);
DECLARE @counter INT = 0;
BEGIN

    PRINT @view_name
    EXECUTE('SELECT COUNT(Value) INTO' + @counter + 'FROM' + @view_name + '');

    PRINT @counter;
END

Then, I wanted to execute the procedure with an existing view name:

EXEC    [dbo].[query_Special_View]
        @view_name = N'A_very_special_LOC_view' -- the view name as parameter
GO

The result is:

  Msg 207, Level 16, State 1, Line 1 Invalid column name 'Value'.

However the column Value, on which I try to invoke the COUNT() function in the procedure, does exist.

So, how can I write such dynamic queries in stored procedures?
I hope I can do this without any ugly dynamic SQL magic, otherwise I will write my dynmic query logic with Java + JDBC in which I can do such things without any hassle.

The problem relates to your use of @counter within your dynamic SQL. It's wrong in a couple of ways: You want to assign to that variable, but in the spot you're using it, it's being translated into building the string that is the SQL statement.  You should instead use sp_executesql and pass the count value back as a return parameter.

I'd also recommend that you use QUOTENAME to prevent against SQL injection in your statement. If someone creates a view with a carefully constructed name, they could make you have a very bad day.

Try using this procedure definition:

 ALTER PROCEDURE [dbo].[query_Special_View](@schema_name sysname, @view_name sysname) AS
 BEGIN
     DECLARE @params nvarchar(1000);
     DECLARE @sql nvarchar(1000);
     DECLARE @return bigint;

     SET @params =  N'@count bigint OUT';
     SET @sql = 'SELECT @count = COUNT(*)  FROM ' + QUOTENAME(@schema_name) + '.' + QUOTENAME(@view_name) + ';';

     PRINT @sql

     EXEC sp_executesql @sql, @params, @count = @return OUT

     SELECT SchemaName     = @schema_name,
            ObjectName     = @view_name,
            ObjectRowCount = @return

 END
 GO

Testing this out for a single call works just fine:

 EXEC dbo.query_Special_View @schema_name = 'sys', @view_name = 'databases';

Going back to your original issue...

You have a series of views that you want to get the row counts for. You don't even need this as a stored procedure. You can simply put those few lines of code directly into a cursor and execute it directly.

 DECLARE @SchemaName sysname,
         @ObjectName sysname,
         @params nvarchar(1000),
         @sql nvarchar(1000),
         @return bigint;

 --Table to hold the results
 CREATE TABLE #Results (
     SchemaName sysname,
     ObjectName sysname,
     ObjectRowCount bigint);

 --What objects do you want to get the row counts for?
 --I'm just querying sys.views, but edit this query for whatever you need
 DECLARE obj_cur CURSOR FOR
     SELECT SchemaName = schema_name(schema_id),
            ObjectName = name
     FROM sys.views v;

 --Use that cursor to loop through all objects
 OPEN obj_cur;
 FETCH NEXT FROM obj_cur INTO @SchemaName, @ObjectName;
 WHILE @@FETCH_STATUS = 0
 BEGIN
     --Dynamic SQL to get the row count
     SET @params =  N'@count bigint OUT';
     SET @sql = 'SELECT @count = COUNT(*)  FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ObjectName) + ';';
     EXEC sp_executesql @sql, @params, @count = @return OUT
     --Put that number into a table
     INSERT INTO #Results (SchemaName, ObjectName, ObjectRowCount)
     SELECT SchemaName = @SchemaName,
            ObjectName = @ObjectName,
            ObjectRowCount   = @return
     FETCH NEXT FROM obj_cur INTO @SchemaName, @ObjectName;
 END
 CLOSE obj_cur;
 DEALLOCATE obj_cur;

 SELECT *
 FROM #Results;

 DROP TABLE #Results;


CPU usage goes up at specific time of day probably because of Mysql My server is showing high cpu usage between 10 am and 2 pm (IST) since last few days. The traffic is prretty much constant throughout the day with no significant jump or fall at any of these time. Even the background tasks are not running at that time. Analyzing the access logs, there is nothing specific about these time. Yet the cpu usage remains near 140% for 3-4 hours continuously.

We are using ubuntu 14.04, mysql 5.7, apache2, django1.10

Using top, I figured out the most of the CPU (around 90% +) is being used by Mysql continuously and the RAM usage is also too high.

Can anyone suggest me what to look for and how to reach a solution or even find the culprit.

Including the content of my.cnf

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
explicit_defaults_for_timestamp

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address    = 0.0.0.0

log-error       = /var/log/mysql/error.log

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

Also, conf.d/ is empty. Seems that they have removed most of the default options from my.cnf

RAM: 2GB. Earlier in django settings there was CONN_MAX_AGE = 100 but i have removed it as i read that almost always persistent connection serve no good now and will try to see the effect tomorrow afternoon.

100% CPU very often means one particular query is running.  And it needs a better index.  Or a better formulation.

Turn on the slowlog, with long_query_time = 1.  If you can get to the file, then use mysqldumpslow -s t or pt-query-digest to find the worst coupld of queries.  Then we can discuss what 'composite' index to add (or whatever).

Swapping is terrible for MySQL.  Lower some settings to avoid it.  Let's see my.cnf and RAM size to help advise.


Postgresql: When using SSH tunnel, I receive the fe_sendauth: no password supplied error I am SSH Tunneling to my postgreql server. I have previously successfully configured 9 + win XP dev PCs to connect to this postgresql server, those PCs still connect successfully.

I am now setting up a Windows 7 64bit Dev PC, using all the same putty configurations for the connection, the same version of pgAdmin, however when I try to add the posgresql server to the servers list in pgAdmin it returns the dreaded Postgresql: 'fe_sendauth: no password supplied' error. ???

There is no password and all the previous XP PCs successfully connect using No Password.

I am using postgresql 9.3, pgAdmin 1.18, and I've tried ver 1.16 with same result.

Has anyone else experienced this issue and possibly found a solution?

Your Windows 7 PC supports IPv6 natively.

I'd say your pg_hba.conf is set to require md5 on connections from ::1 but not on 127.0.0.1. This works on your XP machines because they won't connect over IPv6. On Win 7, they'll happily use IPv6 (and PuTTY will happily tunnel IPv6), so they probably appear to have the origin address of ::1.

To confirm if this is the case, look at the PostgreSQL log file on the server.

(Personally I find it much less of a pain to just configure SSL on my PostgreSQL servers anyway, mind).


Oledb select and count statement I have a lottery database with columns Ball1 to Ball6 how do i find number of times a ball has been drawn from columns Ball1 to Ball6

For modern MySQL version:

WITH cte AS (          SELECT Ball1 ball FROM lottery
             UNION ALL SELECT Ball2      FROM lottery
             UNION ALL SELECT Ball3      FROM lottery
             UNION ALL SELECT Ball4      FROM lottery
             UNION ALL SELECT Ball5      FROM lottery
             UNION ALL SELECT Ball6      FROM lottery)
SELECT ball, COUNT(*) cnt
FROM cte
GROUP BY ball

For ancient MySQL version:

SELECT ball, COUNT(*) cnt
FROM (          SELECT Ball1 ball FROM lottery
      UNION ALL SELECT Ball2      FROM lottery
      UNION ALL SELECT Ball3      FROM lottery
      UNION ALL SELECT Ball4      FROM lottery
      UNION ALL SELECT Ball5      FROM lottery
      UNION ALL SELECT Ball6      FROM lottery) cte
GROUP BY ball


How to retrieve / read SQL Image Data which is stored in the database? From one application, we are storing all images in SQL database in the Image datatype. From another application, i need to retrieve the images and display it on User Interface.

I am able to store the images. I am not able to read the images.

How to retrieve / read SQL Image Data which is stored in the database ?

Download and Upload images from SQL Server via ASP.Net MVC
FILESTREAM MVC: Download and Upload images from SQL Server

For a winforms or a WPF applicaiton things are simpler, just assign the BLOB stream to the image StreamSource property.