Main Considerations When Moving From MS Access Programming to SQL Server I am a 100% self taught MS Access programmer (main part of my job is programming), I am now building larger databases; still using MS Access as the UI but SQL Server to store all data and do more of the 'work'.

In essence my question is; what subject matters do I need to know for SQL Server that I probably didn't learn or need when using Access?

Not looking for you to tell me how to do anything, more what you think are the most important things I should go an research - there's a lot of subjects and a hell of a lot of detail, don't want to find myself a long way down a less valuable path...

Brain dump:

Maintenance: what are the most important (check database, reduce
database, update statistics, rebuild etc)
Indexes - I don't know as much as I should, is there a good book/
blog etc that can teach me the basics upwards?
Anything else I have missed (there's probably lots, as I said I am
new to SQL Server...)

If it helps I work for a mid sized retailer and the databases I predominantly work on cover such things as

Reporting platform (summarises sales/ receipts/ inventory etc from
main system and provides fast reporting)
A reconciling tool between a third part and what our stores put
through registers (imports data from third party and cross references
the transaction logs)
Stores all data to do with our promotions; product, prices,
projections, actual results etc

As you learn more about SQL Server you will discover (to your delight) a number of things you can do in SQL Server at the database level that you previously had to do in Access at the application level. Some examples include:

Triggers: Procedures defined at the table-level to make stuff automatically happen whenever there is an INSERT/UPDATE/DELETE on the table.

Stored Procedures: Somewhat similar to Access macros and those little VBA procedures (Subs) you built in Access to do "useful stuff", except that in SQL Server they are part of the database itself. So, you can write a Stored Procedure (SP) once and use it (almost) anywhere, even across applications that share the same database.

Functions: These are somewhat analogous to the little VBA Functions you wrote in Access to incorporate into queries, except that SQL Server Functions, like SPs, are more tightly bound to the database. Also SQL Server Functions can be Scalar-valued (return a single scalar value) or Table-valued (return a rowset).

Fun SQL tricks: There are lots of SQL features available in SQL Server that are not supported in Access (Jet/ACE) SQL. "Common Table Expressions" (CTEs) and "RANK and PARTITION" are the ones that gave me "'aha' moments" as I was getting started.

I could go on, but these are the things I remember discovering early on that got me "thinking in SQL Server".

Don't get me wrong, I still think Access is an excellent tool when used appropriately, and should be given serious consideration as a way to

build (or just mock up) front-end applications against a SQL Server back-end, and
do data cleanup, create ad-hoc queries and reports, and perform similar tasks on your SQL Server data.

Finally, even though you will be working with SQL Server 2005 I highly recommend that you get a copy of SQL Server 2008 (or 2012) Express for learning, mainly because of the IDE enhancements in SQL Server Management Studio (namely, auto-completion and interactive debugging). Just be aware of any newer (2008+) features you may encounter and don't rely on them for your production code. (Or, use them as bullet points for your pitch to upgrade from SQL Server 2005....)


Can a SQL Server 2005 msdb database be restored as a User database? Can a SQL Server 2005 msdb database be restored as a User database on another instance (same Build)?

"Why?" you ask: I need a list of the mail items that existed prior to installation of two service packs .. the list was deleted (msdb.dbo.sysmail_delete_mailitems_sp) in order to address a post-install storm of mail messages.

Yes, you can, even on the same instance.

-- I assume you've already done something like this:
BACKUP DATABASE msdb TO DISK = '<backup_location>\msdb.bak' WITH INIT;

RESTORE DATABASE msdb_copy FROM DISK = '<backup_location>\msdb.bak'
  WITH REPLACE, RECOVERY,
  MOVE 'MSDBData' TO '<data folder>\msdb_copy.mdf',
  MOVE 'MSDBLog' TO '<data folder>\msdb_log.ldf';


Groupwise by groupwise select? I have two tables of data, such as:

CREATE TEMPORARY TABLE data_1 (user int, data int);
CREATE TEMPORARY TABLE data_2 (data int, file varchar(2));
INSERT INTO data_1 VALUES (1, 2), (1, 2), (2, 3), (2, 4), (3, 5);
INSERT INTO data_2 VALUES (1, 'a'), (3, 'a'), (4, 'a'), (5, 'b');

Table 1
user data
1    1
1    2
2    3
2    4
3    5
3    6

Table 2
data file
1    'a'
3    'a'
4    'a'
5    'b'
6    'c'

First I want to know, which users have more than one data point? Easy:

> SELECT user FROM data_1 GROUP BY user HAVING COUNT(data) > 1;
+------+
| user |
+------+
|    1 |
|    2 |
+------+

I also want to know which data-points are referenced to from more than one file.

> SELECT data FROM data_2 GROUP BY file HAVING COUNT(file) > 1;
+------+
| data |
+------+
|    1 |
+------+

This has the problem that GROUP BY file means we only get one data point per file, but that can be solved by a GROUP_CONCAT if necessary.

Now, but difficulty is that I want to combine both the conditions into a single query.

I want to know which users have more than one data point, for data points that together show up in the same file.

So the expected output from the sample data above would be 2.
User 2 would match because it has two datapoints, 3 and 4, and both of those have 'a' for 'file' value.

User 1 wouldn't match, because only one of data 1 and 2 is present in table 2. User 3 also would go away, because even though both data points 5 and 6 are in table two, they have different file values.

I could combine my two queries from above like this:

SELECT user FROM data_1 WHERE data IN (
  SELECT GROUP_CONCAT(data SEPARATOR ',') FROM data_2 GROUP BY file HAVING COUNT(file) > 1
) GROUP BY user HAVING COUNT(data) > 1;

But I think that fails on several ponts. First it appears like the group_concat turns the data ids into a string like '3, 4', so the IN () comparison is now not done on the correct numbers. but even if the IN did what I wanted, I expect this would apply the results from the inner query "globally" in the WHERE clause. I would need the condition applied groupwise to the outer query.

I think this will solve the problem. It shows users that have associated less distinct files than data.

Since the files associated for a user are less than the data, at least one file must be associated with more than one data (for that user):

SELECT
    ud.user
FROM
    data_1 AS ud
      JOIN data_2 AS df
      ON ud.data = df.data
GROUP BY
    ud.user
HAVING
    COUNT(DISTINCT df.file) < COUNT(*) ;

The query assumes you have UNIQUE constraints on data_1 (user, data) and on data_2 (data, file).


Using "OR" against two indexed columns doesn't use indexes In the query below, from and tid are indexes of the replies table.

SELECT * FROM `replies`
WHERE `from`="<userId>"
OR `tid` IN (SELECT `tid` FROM `posts` WHERE `from`="<userId>")

By using "OR", it seems that it does a full table scan (~3 million rows).
The EXPLAIN says that a possible key would be from, but then it doesn't use any.

However, in the query below, frid_lt and frid_gt are indexed. The two columns are in a complex index (frid_lt, frid_gt), but frid_gt has also its own index.

SELECT `mid` FROM `messages`
WHERE `frid_lt`="<userId>" OR `frid_gt`="<userId>"

And this query DOES use two indexes. The EXPLAIN says "index_merge" and "Using sort_union(frid_lt,frid_gt); Using where".

Why does the first query not use an index merge?
Is there any improvement I can make to make the engine use an index merge as well?

OR does not optimize well.  A common workaround is to use UNION:

( SELECT * FROM replies WHERE `from` = "..." )
UNION ALL   -- or UNION DISTINCT if you know there are no dups
( SELECT r.* FROM replies AS r
    JOIN posts AS p  ON p.tid = r.tid
    WHERE p.from = "..." )

Notice that I also avoided the usually-inefficient IN ( SELECT ... )

For further performance, have these indexes:

replies:  INDEX(`from`)
posts:    INDEX(`from`, tid)  -- in this order
replies:  INDEX(tid)

(And note that the PRIMARY KEY is an index, so don't add a redundant index.)

In your second example, the "index merge" that you experienced may or may not be faster than a UNION.

Oh, it's an UPDATE

To optimize UPDATE, do two separate UPDATEs (no UNION, no OR).  One straightforwardly checks from.  The other is a "multi-table UPDATE" (see the manual) similar to the second select above.


Query turned into a CPU gobbling monster I have a SP who's execution time went from 5 minutes > 20 minutes > 30 minutes > 53 minutes over four days

Waits were showing increasing CPU and suspended status

I isolated a single query that pegs the CPU

UPDATE thing.table
SET YYYYMM =
CASE
  WHEN
    DAY(SnapshotDate) = 1
    OR
  SnapshotDate = (SELECT MAX(SnapshotDate) FROM thing.table)
  THEN CAST(FORMAT(DATEADD(day,-1,snapshotdate),'yyyyMM') AS INT)
  ELSE NULL
END

I ran it again, adding WITH (RECOMPILE) at the end - no difference

I ran UPDATE STATISTICS thing.table - no difference

It would be interesting to run it and get the actual plan but I don't want to peg the CPU for an hour. I checked sys.dm_exec_cached_plans but it appears to only have the estimated plan not the actual plan

I rewrote using CONVERT instead of FORMAT (because I am suspicious of new things) - no difference

So I rewrote like this and took execution back down to a few seconds:

BEGIN TRAN;

UPDATE thing.table
SET YYYYMM = NULL;

UPDATE thing.table
SET YYYYMM = CAST(FORMAT(DATEADD(day,-1,snapshotdate),'yyyyMM') AS INT)
WHERE
(
DAY(SnapshotDate) = 1
OR
SnapshotDate = (SELECT MAX(SnapshotDate) FROM thing.table)
);

COMMIT TRAN;

The table has about 150,000 records in it. It's quite possible that it recently had a lot more records dumped in it, skewing statistics, but why would WITH(RECOMPILE) and UPDATE STATISTICS not fix that?. It takes a daily snapshot and possibly the number of records increased due to end of month.

So the questions are:

Is actual query plan stored anywhere?, to save me running it interactively?
Normally when a query suddenly takes forever it stale statistics but that doesn't seem to be the case here

This is my version of SQL Server

    Microsoft SQL Server 2014 - 12.0.4100.1 (X64)   Apr 20 2015 17:29:27   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.3  (Build 9600: ) (Hypervisor)

Here are the slow and fast query plans. No suprise they are different because they are doing different things:

Slow Plan:

Fast Plan:

I notice slowpoke uses an loop join and fasty uses a hash match.

I notice the small leg of the loop join has filter

[Expr1006]=DB.thing.table.[SnapshotDate]. Maybe that wasn't so small anymore?

The first query is so slow because it will do a full table scan on thing.table for every row in that table for which DAY(SnapshotDate) <> 1. So if you have 100k rows in the table in the worst case you'll do 100k scans which means reading through 10 billion rows. If the table is small enough it'll stay in memory so your parallel query will appear to burn through CPU.

You can tell by looking at the query plan carefully. The scan is on the inner side of a nested loop join. If that's not your cup of tea you can try live query statistics to see the query as it executes. That way you can get some of the information from the actual plan without needing the query to finish. There's no way to save off old actual plans without setting up extended events.

The second query is faster because the query optimizer is freer to rearrange the elements of the query due to the lack of the CASE expression. Instead of calculating the subquery for MAX(SnapshotDate) once per row the calculation is done once per query.

You'll definitely want to fix this query in some way or the execution time will continue to grow quadratically with the number of rows in the table. One workaround would be to add an index to the SnapshotDate column. The subquery will still execute once for each row but getting the maximum value will be a very cheap operation. A better way is to save off the value of the subquery to a local variable and to use that in your UPDATE query. Unless you have to worry about concurrency that shouldn't be an issue.

You can also stick to the fix that you found if you want. One suggestion that can help in some cases (depending on the table structure) would be to add a where clause to your first UPDATE:

UPDATE thing.table
SET YYYYMM = NULL
WHERE YYYYMM IS NOT NULL;


Is mongoreplay included in the toolset with mongodb 4.0 community version for windows? Where to find mongoreplay tool in mongodb 4.0 community version for windows? It is not in the same place as other tools - mongoexport, mongoimport, etc.

mongoreplay is currently not included in the Windows installation packages (as at MongoDB 4.0). This tool uses the libpcap packet capture library which is available on most UNIX/Linux platforms but is not standard on Windows.

There's a relevant feature request to watch/upvote in the MongoDB Jira issue tracker: TOOLS-1730: Release  mongoreplay for windows.

I also raised DOCS-12355 as this should be more clearly noted in the documentation.


INSERT/SELECT slow after TRUNCATE TABLE on table with 6 over mllion rows Seeing slowness in insert/select after truncating a table that has over 6 million rows.

I daily insert 5 to 6 millions records into a table and I was able to insert/select data without any issue for some 7 or 8 days but when the table size went above 10 GB / 30 million rows, there were a few timeout issues.

So I thought of truncating the table daily before data uploading since 1 day's data is enough for me. Now I am seeing extreme slowness after truncating. I am using sql server 2008.

do i need to update table stats? or is it some thing related to ghost rows?


Index on column with data type citext not used In PostgreSQL 9.4, with following schema:

CREATE TABLE people (
    id INTEGER PRIMARY KEY,
    name TEXT,
    junk CHAR(1000)
);

INSERT INTO people(id, name)
 SELECT generate_series(1,100000), md5(random()::text);

CREATE INDEX ON people (name text_pattern_ops);

if I search by name, the index is used:

test=# explain analyze select id, name from people where name like 'a%';
                                                           QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on people  (cost=248.59..1160.92 rows=6061 width=37) (actual time=2.412..8.340 rows=6271 loops=1)
   Filter: (name ~~ 'a%'::text)
   Heap Blocks: exact=834
   ->  Bitmap Index Scan on people_name_idx  (cost=0.00..247.08 rows=6266 width=0) (actual time=2.123..2.123 rows=6271 loops=1)
         Index Cond: ((name ~>=~ 'a'::text) AND (name ~<~ 'b'::text))
 Planning time: 0.600 ms
 Execution time: 8.991 ms

But if I replace TEXT with CITEXT:

CREATE EXTENSION CIText;

CREATE TABLE people (
    id INTEGER PRIMARY KEY,
    name CITEXT,
    junk CHAR(1000)
);

The index is not used anymore:

test=# explain analyze select id, name from people where name like 'a%';
                                                QUERY PLAN
-----------------------------------------------------------------------------------------------------------
 Seq Scan on people  (cost=0.00..2084.00 rows=500 width=36) (actual time=5.700..152.572 rows=6305 loops=1)
   Filter: (name ~~ 'a%'::citext)
   Rows Removed by Filter: 93695
 Planning time: 0.764 ms
 Execution time: 153.046 ms

According to CITEXT PostgreSQL documentation, the behavior should be as with TEXT:

  Otherwise, it behaves almost exactly like text.

How can I tell PostgreSQL to use the index?

Index usage with text_pattern_ops (as well as with the default operator class when using the C locale) depends on the binary representation of character data. citext stores original values with the case preserved, so there must be a problem with that ...

Like you commented, the actual reason is burried in collation support.

Either way, citext or text, you can make it work with an expression index:

CREATE INDEX people_name_idx ON people (lower(name) text_pattern_ops);

And a corresponding query:

SELECT id, name FROM people WHERE lower(name) LIKE 'abc%';

Note that lower(name) returns data type text, even when feeding it citext.

Alternatively, you could use a trigram index, which is more expensive to maintain, but also offers more capabilities:

How is LIKE implemented?
Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

Aside: your test case is suboptimal because your dummy values are all lower case to begin with and the pattern 'a%' is often not selective enough to use an index at all. And char(1000) doesn't make sense (even though irrelevant to the test).


Specific changes to glibc 2.28 that affect PostgreSQL I am preparing for upgrading a PostgreSQL server to Debian 10 (Buster) with the warning from Debian release notes about changes to gclibc affecting PostgreSQL in mind. The PostgreSQL wiki provides two specific strings that will be sorted differently under LC_COLLATE=en_US.UTF-8 with glibc versions prior to 2.28 (i.e. up to Debian 9) or from 2.28 (i.e. from Debian 10) respectively.

( echo "1-1"; echo "11" ) | LC_COLLATE=en_US.UTF-8 sort # 11 1-1 under Debian 9
( echo "1-1"; echo "11" ) | LC_COLLATE=en_US.UTF-8 sort # 1-1 11 under Debian 10

From this warning I have been expecting that similar problems occur with many string patterns. In order to better understand this I have created a table test with 1 million rows of random samples, i.e. each primary key is a string permutation of lower case letters, digits, hyphens and German umlauts. There are two additional rows that hold the two special strings 11 and 1-1.

When I dump this table with SELECT * FROM test ORDER BY key \g test.out from a database with encoding UTF8 and collation en_US.UTF-8 under Debian 9 and Debian 10 respectively what I observe is that the sort order only differs with respect to the two special strings. All the other (random) strings don't seem to be affected.

This makes me wonder what is the precise nature of changes to locale en_US (en_US.UTF-8) in glibc 2.28. Does it only concern the relative order between - and digits (and nothing else)? Where does the package source code reveal the exact difference (URL into GitHub, or similar)?

I'm not sure what the minor change were but even minor changes affect how the index stores data on disk, any difference would result in a collision and/or corruption when using a index that had the old sort method.  data is could be looked-up and/or stored in the wrong places..

This requires drop and rebuilding of the index
The source code to glibc can be found here

https://packages.debian.org/buster/glibc-source

Postgresql did add a new feature I think in 10 ICU
which allows the creation of custom Collation tables.
https://www.2ndquadrant.com/en/blog/icu-support-postgresql-10/

Which could be used to avoid these problems in the future


How to reuse dataset in that stored procedure First, I need to get a dataset then I want to use that dataset more then one time.

   SELECT @FullTimeEmpResult =
   ( select * from emp where type="FullTime"
   );

 SELECT * from @FullTimeEmpResult where salary >5000;

 SELECT * from @FullTimeEmpResult where YearOfExperience> 5;

how I can do this. Thanks in advance.

This is what i would do without knowing the table structure.

Otherwise I might create the temporary table with the correct columns first, and insert in it afterwards (and not use select * to insert but instead select the needed columns from the emp table).

I used a temporary table instead of a table variable as to (in most cases) improve query performance.

 SELECT *
 INTO  #FullTimeEmpResult
 FROM emp WHERE TYPE='FullTime';

 SELECT * FROM #FullTimeEmpResult WHERE salary >5000;

 SELECT * FROM #FullTimeEmpResult WHERE YearOfExperience> 5;

 DROP TABLE #FullTimeEmpResult;


MySQL stored procedure create database by month I'm a systems admin, and I'm not a dba, I'm wondering if I could use MySQL stored procedure to
Query main database and move records by month to another database located on the same system.

This way we can maintain database small main database, and have data moved to individual database by month.

Thank you

I would advice you to take a look at partitioning if you are running a recent version of MySQL and see if this could solve your problem.

Partitioning does not result in a smaller database but offers many of the same benefits with less administration once it is set up, and you will still have access to historic data.


Management Data Warehouse Database We have a MDW Database in our PROD Server of size 40GB.

I can see all the collector Jobs are not running properly and the purging MDW job.

mdw_purge_data_[ManagementDWH]

is not running saying the owner does not have access to server.

When I access the Tables in MDW Database I can see all the tables are updated few months ago.

Can anyone help me with below queries :

Do we really need MDW Database ?
What if I delete my existing MDW Database (Unless we need to troubleshoot any performance issues) ?

Please let me know if is required to keep the MDW Database.

Thanks

I don't think it's our place to make the decision of you needing MDW or not. Only you and your team with your knowledge of your environments can make that decision, but if you're not using it then I would try to find out who installed it, for what reason and if that reason is still valid.

If you do decide to remove it, be aware there are some 'problems' with the uninstall. See this Connect item for all the pain people have suffered over the years with the uninstall.

You've tagged 2008 R2 and 2014, so the version of SQL Server that MDW is installed on will decide how many issues you will encounter when trying to remove MDW. 2012 added 3 stored procedures to help the process but even they don't quite remove everything. This TechNet post covers the problem and this post covers the solution for 2008 and 2012.


Substract two database entries based on value of a certain field Good evening everyone,

I am looking for the most elegant way to substract two values of one column based on the value in another column. Until now I could only find hacky and unreadable solutions:

I have a table containing entries when orders on an exchange were opened and closed. It looks like this:

|| DATETIME || ORDERID || ACTION || TYPE || PRICE || ...
  2018-01-01   a34hsbf    OPEN      LONG    0.23
  2018-01-02   ghs7fa0    OPEN      SHORT   0.45
  2018-01-03   a34hsbf    CLOSE     LONG    0.47
  ...

Now I want to return a new table containing the differences between entries with the same ORDERID. There can always only be two entries with the same ORDERID. The new table should be indexed by the greater DATETIME of those two entries. Also the difference should take into account if the TYPE was LONG or SHORT. E.g. for LONG a rising price must give a positive difference, for SHORT it must give a negative difference and vice versa.

The new table should look like the following:

|| DATETIME || ORDERID || TYPE || DIFF ||
  2018-01-03   a34hsbf    LONG    0.24
  ...

Many thanks in advance for your help. :)

As RDFozz has pointed out in his comments, this solution only works if:

There is one OPEN and one CLOSE entries
Both entries has the same type, 'LONG' or 'SHORT'

select case when t1.datetime > t2.datetime
            then t1.datetime
            else t2.datetime end as datetime,
       t1.orderid,
       t1.type,
       case when t1.type = 'SHORT'
            then t1.price - t2.price
            else t2.price - t1.price end as diff
from   tbl t1
join   tbl t2
on     t1.orderid = t2.orderid
and    t1.type = t2.type
and    t2.action = 'CLOSE'
where  t1.action = 'OPEN'

datetime   | orderid | type | diff
:--------- | :------ | :--- | ---:
2018-01-03 | a34hsbf | LONG | 0.24

dbfiddle here


Error while restoring from Enterprise Edition to Standard Edition I am restoring a database from a SQL Server Enterprise Edition onto a Standard Edition server.  I keep getting an error stating:

  Database 'MyDatabase' cannot be started in this edition of SQL Server because part or all of object 'containedmemebersnapshots' is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition.

As a result, my database is in suspect mode.

Is there any resolution so I can restore this database onto SQL Server Standard Edition?

Unfortunately SQL Server Standard Edition does not support vardecimal storage format or data compression.

You need to restore to a Developer Edition (or Enterprise Edition), remove the non-supported features by modifying the objects as necessary, then backup the modified database, prior to restoring it on Standard Edition.

Technet has a great page detailing what is and what is not supported by each edition.

MSSQLTips has an article that shows how to use the sys.dm_db_persisted_sku_features system DMV to identify features that won't be supported on Standard Edition.


Query or a New Column for Performance I have below tables:
ProductsTable (has product details),
UsersTable (has all application users),
RatingCategry (category for which user can rate a products like Quality, Cost etc.) and
UserRatingsforProductsTable (Users rating for a product for any RatingCategory) for a Mobile Application.

Products (ProductId, ProductName… other details like cost etc.)

UsersTable (UserId, UserName… other details like DateOfJoin etc.)

RatingCategory (RatingCategoryId, CategoryName)
**Data:**
1         Quality
2         Cost

UserRatingsforProductsTable (RatingId, UserId, ProductId, RatingCategoryId, Score)
**Data:**
1         5006  20  1   4.5

2         5006  20  2   5.0

3         5600  23  1   3.5

4         7800   20  1   2.5

5         8900   34  1   4.0

6         8900   34  2    4.5

What will be the best and faster query for a Mobile Application to show “Rating ScoreTotal” in the Products list (ProductId, ProductName, Cost, RatingScore)? Shall I do calculation in the Query or Add a new column in Products table that will be auto updated when each Rating added into UserRatingsforProductsTable?

The expected output should be:
ProductId, ProductName, Cost, OverallRatingForThisProd
---------  -----------  ----- ------------------------
*** OverAllRating = Average of Ratings from All Categories for the Specific Product from UserRatingsforProductsTable.

If the answer will be Query instead of adding a Column, can you please advise an optimized query?

Thank you.

I don't think adding a new column will be efficiently and you'll need to do more things like adding TRIGGERs to update the field and you'll get the newColumnvalue in every row for the table UserRatingsForProductsTable.

Information:

create Table Products(
  ProductId INT(4),
  ProductName VARCHAR(75),
  Cost DECIMAL(10,2),
PRIMARY KEY (`ProductId`));

CREATE TABLE UserRatingsForProductsTable(
  RatingId int(6) auto_increment,
  UserId int(5),
  ProductId int(4),
  RatingCategoryId int(2),
  Score decimal(5,2),
PRIMARY KEY (`RatingId`));

insert into Products select 20,'Product1',23.33;
insert into Products select 23,'Product2',223.33;
insert into Products select 34,'Product3',323.33;

insert into UserRatingsForProductsTable select 1,5006,20,1,4.5;
insert into UserRatingsForProductsTable select 2,5006,20,2,5.5;
insert into UserRatingsForProductsTable select 3,5600,23,1,3.5;
insert into UserRatingsForProductsTable select 4,7800,20,1,2.5;
insert into UserRatingsForProductsTable select 5,8900,34,1,4.0;
insert into UserRatingsForProductsTable select 6,8900,34,2,4.5;

Query:

SELECT
    p.ProductId,
    p.ProductName,
    p.Cost,
    ROUND(sum(URPT.Score)/(COUNT(DISTINCT URPT.RatingCategoryId)*COUNT(URPT.UserId)),1) as OverallRatingForThisProd
FROM Products as p
LEFT JOIN UserRatingsForProductsTable AS URPT ON (URPT.ProductId=p.ProductId)
GROUP BY p.ProductId
ORDER BY p.ProductId;

Try it on SQLFiddle.


SHOW connection_id() In mysql, I can inspect the current Connection ID by using:
SELECT connection_id();

And it returns the id:
# connection_id()
'6558851'

Or:
SELECT * from information_schema.processlist
WHERE id = connection_id();

# ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO
'6558851', 'abc', '99-51-01-something:55353', 'XA', 'Query', '0', 'executing', 'select * from information_schema.processlist plist where id=connection_id()'

Where is that function (if it's a function?) defined though? I've checked in sys and information_schema and was unable to find a function defined with that name. Where is this located then?

Update, on reviewing this a bit more, it seems like these are just built-in functions to the mysql database -- i.e., no different than using a GROUP_CONCAT() or any other function, for example:
SELECT connection_id(), now(), id, email FROM auth_user limit 10;

In this way it would be defined in the C++ code rather than as a user/system-defined function. Is that a correct understanding?

It is a thread _id which you see in SHOW Prpcesslist

ID

The connection identifier. This is the same value displayed in the Id column of the SHOW PROCESSLIST statement, displayed in the PROCESSLIST_ID column of the Performance Schema threads table, and returned by the CONNECTION_ID() function within the thread.

https://dev.mysql.com/doc/refman/8.0/en/performance-schema-processlist-table.html


OEM Grid Control Template I create an monitoring template for my database on OEM GC, apply it on somes database and now i want to know what database is using this template.
Can someone help me please?

Oracle implemented monitoring templates a bit differently than you might expect.  When you apply a template to a database it doesn't associate the template with the database, but instead applies the template settings to the database.  This is why modifying the template after it has been applied to the database has no effect until it is re-applied to the database.

The 11.1 Oracle Enterprise Manager documentation explains how to use the "Compare Monitor Template" feature to determine how closely the template matches a particular target.  In this way you can determine if the template has been applied to the database.

  Deciding how and when to apply a template is simplified by using the
  Compare Monitoring Template feature. This feature allows you to see at
  a glance how metric and policy settings defined in a template differ
  from those defined on the destination target. Compare Monitoring
  Template is especially useful when working with aggregate targets such
  as groups and systems. For example, after you apply a Monitoring
  Template to a group, you want to verify that the group members now
  have the same monitoring settings as the template. The Compare
  Monitoring Template feature makes checking simple. You can also
  schedule this as a report, allowing you to check periodically if the
  group members still follow the template settings.


Error in attaching mdf file from a crashed server to a new server One of our server hosting an important database crashed.

The sysadmin team could only access the files by erasing the password with a tool and then log in to access the .MDF files.

I copied the data to the new server and attempted to attach it, but I get the following error:

  TITLE: Microsoft SQL Server Management Studio

  Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

  ADDITIONAL INFORMATION:

  An exception occurred while executing a Transact-SQL statement or batch.     (Microsoft.SqlServer.ConnectionInfo)

  CREATE FILE encountered operating system error 32 (The process cannot access the file    because it is being used by another process.) while attempting to open or create the       physical file 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf'. (Microsoft SQL Server, Error: 5123)

In general if you have a crashed server and need to bring up your databases on a new server you want to restore from backup not copy and reattach the mdf files.  The crash could have caused corruption that will not exist in the backup.

You can restore your system databases as well although it isn't always recommended.  If you are going to you need to make sure your new system is as close to the old as possible.  Particularly the patching level for SQL Server.

If you do not have backups, and you absolutely have to have your system databases from the old server then you have two options.  You can either attach as a different dbname and copy information over (msdb and model only since it won't work for master).  Or you can tell your instance that the new location of the system database is actually the correct one.

So in your case you would copy the mdf & ldf msdb files to your new server.  Again make sure your patching is the same as the old server.  Then follow the instructions in this link to "move" your msdb files.  In other words you are going to tell it that the files from the old server are actually the correct ones.  Once done you will need to re-start your new instance.  Then make sure you keep the original msdb files from the install just in case.

Of course the other way to "move" your msdb files is to shut down your instance, move/rename the installed msdb files and copy in your old msdb files making sure they are in the same location & have the same name.  Then restart your instance.  If anything goes wrong you can simply move the installed msdb files back into place (while the instance is shut down) and try a different method.


Secondary filegroup for Full Text Index only - rebuild when lost Here's our config:

We have all our data in PRIMARY file group
We have our Full Text Index in another SECONDARY file group which resides on a very fast (but very unreliable) SSD drive. By "unreliable" I mean - it's a Amazon cloud server temporary drive (can be entirely lost in case of emergency restart/hardware failure).

Full-text index is not critical and we're ready to lose it completely as we can obviously rebuild it from scratch.

The question is: is there a way to revive the database in case the secondary file group is 100% lost, without going through the full backup/restore cycle? Like, simply delete the lost file group, then recreate it, then rebuild the index.

(We do have regular full database backups, log backups etc. etc. etc. so we can "restore to the point in time" but this implies long outage)

P.S. Also, any thoughts on how viable our approach is would be very much appreciated.

(answering my own question)

OK, so we ran a simulation - what will happen if we lose the secondary filegroup file?

Not good.

The database gets stuck in recovery mode and you can't do anything about it - you can't set the "emergency" mode, you can't change it to "single_user" mode - nothing. Basically, every command you run throws an error Unable to open the physical file "xxx.mdf". Operating system error 2: "2 (The system cannot find the file specified.)

The only solution is to restore the database. But "rebuilding" a secondary filegroup won't work, even if there's only a fulltext index in it.


SQL Server data files in Windows Azure: filegroups and partitioning still applicable? I just created a database using the new functionality "SQL Server 2014 data files on Windows Azure" and this works quite well.

However, I have a table with a billion records and in our local environment we have partitioned this table into 33 filegroups, my question is whether to store as Azure blob and also is it worth partitioning it into filegroups for higher performance?

My understanding is that Azure's storage is different from that in our environment, so, can we apply the same techniques to improve performance?

If you anticipate that your database file(s) will exceed more than 1TB in size you should configure the server to put the data files across multiple VHDs.
How can spread my databases across multiple VHDs?

Create one or more dynamic volumes consisting of multiple VHDs at the operating system level and put your database files on these volumes.
Split your tables and indexes into separate database filegroups and then place those filegroups in different files (MDF + NDF) which are spread across separately attached VHDs. Note that this will not offer any benefit for transaction logs because SQL Server does not stripe across transaction log files but uses them sequentially.

If you are migrated to Azure and want to compare the performance of your databases before and after the migration then take a SQL Server baseline(enter link description here)


Understanding Heap Read/Heap Hit i have one dockerized postgres database (The host has 1GB of ram and 2 CPU) with some tables, on a specific table, that it's used to read and write data continuously (every 5 seconds), i have strange issues, as you can see, the ratio between heap_read and heap_hit is quite low:

The table, has not so many data, only 300K rows, and the structure of the table it's the following:

Here it's the index stats:

I mean, the system seems to work fine...but i need to know if i have to do some specific tweaks in order to make it work better and have the  heap_read/heap_hit ratio close enough to 0.99?

Thanks

You are treating it as if block reads are a subset of block hits.  That isn't correct, the two are mutually exclusive.  To get a real hit ratio, you would have to compute as  sum(hits)/sum(hits+reads). But just looking at hit ratios is pretty useless anyway, in general but especially in PostgreSQL which doesn't use DirectIO but rather sits on top of the OS's file cache.  Hit ratios are part of mythology-based administration.

For all we know, this table has only had one or two seq scan done on it (for example, the ones which were done in order to create the index in the first place) and that event is now dominating your statistics.  You could reset the statistics (or better yet just stop looking at them) to get rid of one-time events.

The columns seq_scan, seq_tup_read, idx_scan, idx_tup_fetch are occasionally (but rarely) useful in figuring out the source of a problem when a problem is known to exist. They are entirely useless when there is no problem to diagnose in the first place.

You should instead spend your time doing useful things, like setting up a slow query log ("log_min_duration_statement" or better yet auto_explain, and pg_stat_statements), setting up test/dev/QA environments, or running disaster recovery drills so you know know that your backups are working and that you know how to use them.

My favorite settings of auto_explain for new projects are:

shared_preload_libraries = 'auto_explain'
track_io_timing=on
auto_explain.log_min_duration = '50ms'
auto_explain.log_analyze=on
auto_explain.log_timing=off
auto_explain.log_buffers=on

And then increase log_min_duration if you find it is catching too many queries.

Having log_analyze=on while also having log_timing=on imposes a pretty high overhead on all your queries; so setting log_timing=off is a compromise to get as much detail as possible but with minimal overhead.


Version store blowing up, but responsible sessions have no open transactions Tempdb is growing and it's all version store data.  When I run:

select hostname,elapsed_time_seconds,session_id, transaction_id, is_snapshot, blocked, lastwaittype, cpu, physical_io,  open_tran, cmd
from sys.dm_tran_active_snapshot_database_transactions a
join master..sysprocesses b
on a.session_id=b.spid
order by a.elapsed_time_seconds desc

This shows two sessions that have elapsed_time_seconds nearly 400,000 (~4.5 days).  However, the open_tran column for these sessions is zero every time I check.  The application is using implicit transactions--not sure if that is relevant.

Also, the hostname that is reported for one of these sessions has changed (no, neither the IP addresses nor the host names have been changed) from this morning.  So it seems that maybe the client that had that session id this morning disconnected, and another client has that session id now.

Even though it shows no active transactions for these sessions most of the time, I find transactions that match transaction_id in sys.dm_tran_active_transactions with the following properties:

transaction_begin_time: [~4.5 days ago]
name: DTCXact
transaction_type: 4
transaction_state: 2
transaction_status: 12
transaction_status2: 386
dtc_state: 1
dtc_status: 0
dtc_isolation_level: 4096

Is there a way to explain what I'm seeing?  If there are no open transactions, why would they still have something active in the version store?

SQL Server 2014 SP2 12.0.5214.6

This issue was caused by an orphaned distributed transaction.  Since the transaction is orphaned, the session ID is invalid by the time you see it.  To clear the condition you find the oldest transaction with an active snapshot, lookup of the unit of work (UOW), and then kill the UOW.

--find the transaction with the oldest snapshot version
select top 1 transaction_id, elapsed_time_seconds
from sys.dm_tran_active_snapshot_database_transactions a
order by a.elapsed_time_seconds desc

--using the transaction ID from the query above, find it in the active
--transactions DMV.  You should see the begin time as many minutes/hours ago
select transaction_id, transaction_begin_time, transaction_uow
from sys.dm_tran_active_transactions where transaction_id in(<transaction_id>)

--using the transaction_uow from the query above, kill the unit of work
kill '<transaction_uow>'

After it is killed, run the first query again to see if there are any more that need to be cleared.

In some cases you may need to find the UOW by first finding orphaned transactions, which are supposed to have a session ID of -2.

select * from sys.dm_tran_locks where request_session_id = -2

See Kill (Transact-SQL) for more.


Running a T-SQL job under another user I cant find my proxy user to "Run as" that user !? I am confused which kind of proxy user is appropriate to run a job with a simple T-SQL command.
(I have created a credential and proxy but cant find it under the "Run as" in my Agent job).Please advise!

There is no Run As proxy for T-SQL Job steps in SQL Server Agent.  You could upvote this with the chance that one day it will be filled:

https://connect.microsoft.com/SQLServer/feedback/details/632955/enable-sql-agent-proxies-to-run-t-sql-job-steps

The current way to get a T-SQL command to run with a proxy is to run it as an Operating System Command which does support a proxy user (instead of T-SQL which does not) and run the task either using SQLCMD.EXE or PowerShell to execute the code.


SQL Server cached an execution plan which is not optimal in some cases and uses it for all consequent queries Our application uses SQL Server 2014 and we got an issue related to the plan cache.

We have a parametrized query and its execution plan depends on parameter values. The server caches an execution plan which is not optimal in some cases and then uses it for all consequent queries.

Details:

We have a table consists of the following columns:

(
 [Revision] [bigint] IDENTITY(1,1) NOT NULL,
 [UserId] [uniqueidentifier] NOT NULL,
 ...A WHOLE LOT OF OTHER COLUMNS...
)

The meaning of those two columns is pretty clear, UserId is an Id of the user that the record belongs to, Revision is an auto-incrementing index of the record. Other columns are not important, but they exist and affect execution plans.

The table contains ~40.000.000 rows and ~200.000 distinct UserId values, so each user has 200 records in average. Rows are never updated, we use only INSERT and DELETE to modify data.

Our application executes the following query against this table:

SELECT * FROM SampleTable WHERE Revision > {someRevision} AND UserId = {someId}

The table has two indexes:

Clustered index: Revision asc
Non-Clustered index: UserId asc, Revision asc

When I execute this query manually, I see that the execution plan depends on the value of someRevision.

If it's relatively close to the current max value of Revision, the server uses Clustered Index Seek with Seek Predicate: Revision > someRevision
If it isn't close, the server uses Index Seek (NonClustered) + Key Lookup (Clustered) with Seek Predicate: UserId = someId AND Revision > someRevision.

Our application uses Linq-To-Sql and generates parametrized queries, they look like this:

exec sp_executesql N'SELECT * FROM [SampleTable] AS [t0]
WHERE ([t0].[Revision] > @p0) AND ([t0].[UserId] = @p1)',N'@p0 bigint,@p1
uniqueidentifier',@p0=1234,@p1='bc38dd12-238c-41a2-9dea-bb12ce105e6d'

I used dm_exec_cached_plans, dm_exec_sql_text, dm_exec_query_plan and understood that the server put a single plan for this query into the cache. So, if the query with the corresponding value of Revision came first, the plan using Clustered Index Seek would be stored in the plan cache and then would be used for all the consequent queries.

It leads to an excessive number of logical reads (x10000) and unacceptable execution time for queries which should be executed using the second plan (Index Seek (NonClustered) + Key Lookup (Clustered)).

Also I noticed that the threshold where the server switches between plans (the tipping point) depends on statistics, if it's stale, the plan can be sub-optimal even regardless the cache, because the server incorrectly estimates the number of rows with Revision greater than the given one.

In addition, we have a large set of similar tables with similar use cases and all of them have the same issue.

What can I do to solve this issue?

I could try to use OPTION (RECOMPILE), which is not easy with Linq-To-Sql, but it also doesn't look really optimal in performance terms.

Also I could use sp_create_plan_guide or hack Linq-To-Sql even more and try to WITH (INDEX(...)) clause to force using the second plan, but as I said there is a lot of tables with the same core structure, so this way looks like a lot of manual work.

Generally, my questions:

Can SQL Server understand that the plan stored in the cache is not optimal for given parameters and don't use it?

Are there some best practices of handling parametrized queries if their optimal execution plans depend on parameters?

This is called parameter sniffing, and it's covered extensively in Erland Sommarskog's epic post, Slow in the App, Fast in SSMS.

I can't even begin to do justice to it here, but sample solutions include:

OPTION (RECOMPILE) - which causes increased CPU use for the plan compilation, plus loses historical metrics of the query execution, but can build a unique plan for each set of parameters (although it can still be a suboptimal plan in cases of cardinality estimation issues)
Optimizing for a specific value - if you know your data well, you can use an OPTIMIZE FOR hint so that a plan is always built for a specific parameter value, regardless of what the user passed in. This is like creating technical debt - if your data skew changes, you may have to revisit your code in order to get a better plan.
Using index hints - which are generally worse than optimizing for a specific value because not only are you bossing the query optimizer around, but if that index disappears, your query simply fails. SQL Server doesn't try to use an alternate index for your query.
Plan guide - but if anything whatsoever changes about your query, even a single letter, then the plan guide will no longer match.
Combination of query and index tuning - get the developers to avoid selecting * (all the fields), and just get the fields they truly need. Then, build a covering index to match, and you'll get a single query plan that works well for all parameters.

Head on over and tackle Erland's excellent article - not only will it pay dividends today, but it will continue to pay off over your career as you solve this problem again and again. The solution that works well for your query today is likely to be very different than the solution you use for another query tomorrow.


How to search id from one table in another table's nvarchar column I have 2 tables

table1 ( id int, name nvarchar)
table2 ( pid int, parameters nvarchar)

parameters in table2 contains table1.id in nvarchar format, like
"some text 12345 some text", where 12345 is the id which is contained in table1.
Now I need to find all the table1.id s which are mentionioned in table2.parameters

So for my script is this, but this does not work

select id, name
from table1 r
where exists
(
select [Parameters] from table2
where [Parameters] like cast(r.id as nvarchar(15))
)

I can't use % with like but I need it. What is the way I can do it?

Yes, you can use % with LIKE. Since you are converting the INT "id" into a string, you can then concatenate the % character on both sides. But you also need to include a space separating each of them from the converted "id" so that you don't have an "id" of 23 matching a parameters value of something like "abc 1234 def". And yes, you can JOIN the tables since that is just a different way of expressing the same thing that you are already attempting.

SETUP

-- DROP TABLE #table1;
CREATE TABLE #table1 ( [id] INT NOT NULL PRIMARY KEY, [name] NVARCHAR(30));
-- DROP TABLE #table2;
CREATE TABLE #table2 ( [pid] INT NOT NULL PRIMARY KEY, [parameters] NVARCHAR(500));

SET NOCOUNT ON;
INSERT INTO #table1 ([id], [name]) VALUES (1, N'a');
INSERT INTO #table1 ([id], [name]) VALUES (2, N'b');
INSERT INTO #table1 ([id], [name]) VALUES (3, N'c');
INSERT INTO #table1 ([id], [name]) VALUES (12, N'ab');
INSERT INTO #table1 ([id], [name]) VALUES (23, N'bc');
INSERT INTO #table1 ([id], [name]) VALUES (123, N'abc');

INSERT INTO #table2 ([pid], [parameters]) VALUES (1, N'some text 12345 some text');
INSERT INTO #table2 ([pid], [parameters]) VALUES (2, N'DD 2 DD');
INSERT INTO #table2 ([pid], [parameters]) VALUES (3, N'EEE EE 123 EEEEE');
INSERT INTO #table2 ([pid], [parameters]) VALUES (4, N'NO23 12NO');
INSERT INTO #table2 ([pid], [parameters]) VALUES (5, N'HHHHHH YYY 23 ZZZZZ');
INSERT INTO #table2 ([pid], [parameters]) VALUES (6, N'QQQQQ r2q RRRRR');

CORRELATED SUBQUERY

SELECT r.[id], r.[name]
FROM #table1 r
WHERE EXISTS
(
  SELECT *
  FROM #table2 tmp
  WHERE tmp.[parameters] LIKE N'% ' + CONVERT(NVARCHAR(15), r.[id]) + N' %'
);
/*
id     name
2      b
23     bc
123    abc
*/

JOIN

SELECT t1.[id], t1.[name], t2.[pid]
FROM #table1 t1
INNER JOIN #table2 t2
        ON t2.[parameters] LIKE N'% ' + CONVERT(NVARCHAR(15), t1.[id]) + N' %';
/*
id     name    pid
2      b       2
23     bc      5
123    abc     3
*/


Join Fact table to SCD Type 2 ... how to write query? (SQL Server) I can't seem to find this simple fact anywhere.

I have a fact table like such in SQL server.

fact_picked
Emp_Name     Date     Apples_Picked
John        May 1 17    100

And a type 2 dimension table like this

dim_company
Emp_Name   Company     Effective_Since
John      Blue_Apples      June 1 2015
John      Apple_N_Stuff    Jan 1 2016
John      Da_Big_Apple     March 17 2017
John      Big_Tech         October 20 2017

How would I join the fact to the dimension table so I know which "company" picked the 100 apples?

In this case, logically, given the data, it's 'Da_Big_Apple' ... since John began working there in March 17 until Oct 2017, in which his apple picking task took place.

How do I do a join of these tables though? (assuming thousands of records).

I just get stuck. I know I should do something like

Select fp.emp_name, fp.date, fp.apples_picked
from fact_picked fp
left join dim_company dc
on fp.emp_name = dc.emp_name
and fp.date > dc.effective_since .... ???

I guess I'm not really sure. I guess I can use a view to convert the type 2 to a type 4 (with an end date in the table). Then it's simpler. Make sure the fact date is greater than the start date, yet before the end date. But is that the most elegant solution?

Setup some sample data:

create table fact_picked
(Emp_Name         varchar(30)
,[Date]           date
,Apples_Picked    int);

insert into fact_picked values
('John','10/17/15',175),
('John','05/01/17',100);

create table dim_company
(Emp_Name         varchar(30)
,Company          varchar(30)
,Effective_Since  date);

insert into dim_company values
('John','Blue_Apples',  '06/01/2015'),
('John','Apple_N_Stuff','01/01/2016'),
('John','Da_Big_Apple', '03/17/2017'),
('John','Big_Tech',     '10/20/2017');

Proposed solution:

select fp.Emp_Name,
       fp.[Date],
       fp.Apples_Picked,
       dc.Company,
       dc.Effective_Since

from   fact_picked fp

left
join   dim_company dc
on     dc.Emp_Name        = fp.Emp_Name
and    dc.Effective_Since = (select max(Effective_Since)
                             from   dim_company dc2
                             where  dc2.Emp_Name         = fp.Emp_Name
                             and    dc2.Effective_Since <= fp.[Date])

order by 1,2,5;

Emp_Name | Date                | Apples_Picked | Company      | Effective_Since
-------- | ------------------- | ------------- | ------------ | -------------------
John     | 17/10/2015 00:00:00 |           175 | Blue_Apples  | 01/06/2015 00:00:00
John     | 01/05/2017 00:00:00 |           100 | Da_Big_Apple | 17/03/2017 00:00:00

sub-query is used to find the dim_company record with the latest (ie, max()) Effective_Since date that is less than a given fact_picked.[Date]
use the sub-query result to determine which dim_company record to join with in the top-level query

To make sure the left join is working properly, assume we have only the following rows in the dim_company table:

truncate table dim_company;
insert into dim_company values
('John','Da_Big_Apple','03/17/2017'),
('John','Big_Tech',    '10/20/2017');

Run the proposed query again:

select fp.Emp_Name,
       fp.[Date],
       fp.Apples_Picked,
       dc.Company,
       dc.Effective_Since

from   fact_picked fp

left
join   dim_company dc
on     dc.Emp_Name        = fp.Emp_Name
and    dc.Effective_Since = (select max(Effective_Since)
                             from   dim_company dc2
                             where  dc2.Emp_Name         = fp.Emp_Name
                             and    dc2.Effective_Since <= fp.[Date])

order by 1,2,5;

Emp_Name | Date                | Apples_Picked | Company      | Effective_Since
-------- | ------------------- | ------------- | ------------ | -------------------
John     | 17/10/2015 00:00:00 |           175 | null         | null
John     | 01/05/2017 00:00:00 |           100 | Da_Big_Apple | 17/03/2017 00:00:00

And here's a dbfiddle for the above.


Need help in creating a query I have a table with below data.

columnA  columnB
ONE      value 1
ONE      value 2
ONE      value 3
TWO      value 1
TWO      value 2
THREE    value 1

Need help in creating a query which gives below

COLUMN X   COLUMN Y
ONE         value 1, value 2, value 3
TWO         value 1, value 2
THREE       value 1

What you are needing help is called Grouped Concatenation in SQL Server

This sql fiddle that I created will help you.

Basically you use stuff + FOR XML PATH.

If you are on sql server 2017 (vNEXT) then STRING_AGG is an inbuilt function that you can use

-- sql server vNEXT

SELECT [ColumnA]  =  [ColumnA],
       [ColumnBs] = STRING_AGG(ColumnB, ',')
 from dbo.Table1
 group by ColumnA


Seeking Suggestion for faster DB Insertion Policy I have written a server which keeps on receiving concurrent requests from 30K-40K different clients concurrently. Now I need to dump all those individual requests into a database. The database right now is SQL Server.

My server has a thread pool for handling 30k-40K clients and I literally can't call some blocking calls in those threads to directly insert into SQL Server. This will literally beat the design of my server.

What efficient design should I choose for inserting into the database?

Does anyone recommend a MQ (Message Queues) design? Like from server threadpool, threads directly write into MQ and the consumer will take care of dumping what ever exists in MQ into SQL database. But again if there is some fishy happens in SQL like latency in response, then the entire Queue will be kept on piling up on and on.

This is an undergrad final year project. I am sorry but I am not a DB expert. I will be using some community edition of SQL Server for Proof of Concept, and there will be one huge table close to 14-15 columns where activity of each client will be recorded.

Even for batch upload, I need to buffer data from each client to somewhere. Right now my concern is about deciding the strategy From Server to some layer (let's it call x) that will pool the data. I am not going to directly call INSERT From the Server receive thread, for obvious reasons. There will be a separate process/service that I call consumer that will take care of doing Bulk Insert in DB. I seek experts to get me the view about data polling techniques, like RabbitMQ etc. I have coded the Server in C/C++.

There is no FIFO requirement, but indeed whoever sends the data first and that data needs to get saved into DB soon. There are 30K-40K clients and at any second at least there are close to 60% of the clients sending data to Server.

There is no particular policy to faster insertion (loading), which can direct you to your target. Instead, there are some configuration and prerequisites which can help you to improve write (insert) performance.

The performance of any application, installed on OS lies on the hardware of the server. So, first, you should identify how much IOps disk you required, to collect that data you can use SQLIOSim.

If you'll use SAN, then you should choose HDD with low space and high IOps (You can collect details from Vendor about complete hardware). More numbers of disks result in more IOps and better write performance.
Because application writes data to disk, disk performance plays a great role when it comes to OLTP application. So, proper disk configuration is required.

Space on disk (HDD) are divided into 512 bytes sector and 4KB (default) cluster size contains 8 sectors. But the size of SQL Server data pages is 8KB. So, formatting cluster size into 8KB for .mdf and 64KB for .ldf will boost write performance.

You can visit this link for in-depth detail: Disk Partition Alignment Best Practices for SQL Server
Place Transaction Log file (.ldf) on RAID10 or RAID1, tempdb on RAID0 (because restarting SQL Server recreates it) and data file (.mdf) according to your security procedure (on RAID5, 1 or 10). All the data files should be within a separate partition.
Change Recovery Model of the database into BULK_LOGGED.
For OLTP application like yours, it's recommended to create a normal table (according to requirement) with minimum indexes. So, I would suggest creating a simple table with a clustered index on varchar datatype column.
Use BULK INSERT instead of BCP.
Follow this link for a detailed guide about data loading: The Data Loading Performance Guide

If it's normal INSERT through an application, then use ROWLOCK hint with the insert statement. Divide 40K clients into four parts and give them an Id. 10K = 1, 10K=2, 10K=3, 10K=4 (in a table). Now create 4 tables with the same schema (which will use to INSERT records). In Stored Procedure, on the basis of Id of User direct the transaction to respective table. This will reduce locking/blocking on the table.
Make sure you have a healthy network connection between application and database server. There is no DMZ and firewall in between. Routing request through multiple devices will increase total execution time.


How do I combine multiple lines for the same package? (SQL) I am doing an assignment and I am close to completing it. However the package balance appears on multiple lines and I would like to combine the balances for each package type.

IF OBJECT_ID('vwPackageRevenue') IS NOT NULL
    DROP VIEW vwPackageRevenue
GO

CREATE VIEW vwPackageRevenue
AS
SELECT (dbo.packageWH.PNAME) AS 'Package Name'
    ,(COSTPERSON * COUNT(GFirstName)) AS 'Total Revenue'
FROM dbo.packageWH
JOIN dbo.reservationWH ON dbo.packageWH.P_ID = dbo.reservationWH.P_ID
JOIN guestWH ON dbo.reservationWH.RNUM = dbo.guestWH.RNUM
GROUP BY dbo.packageWH.COSTPERSON
    ,dbo.packageWH.PNAME
    ,dbo.guestWH.GFIRSTNAME
GO

SELECT *
FROM vwPackageRevenue

Any ideas how to complete this?

I think you just need to add a SUM and I am not sure what the > signs are doing in there.

IF OBJECT_ID('vwPackageRevenue') IS NOT NULL
    DROP VIEW vwPackageRevenue
GO

CREATE VIEW vwPackageRevenue
AS
SELECT (dbo.packageWH.PNAME) AS 'Package Name'
    ,SUM((COSTPERSON * COUNT(GFirstName)))  AS 'Total Revenue'
FROM dbo.packageWH
JOIN dbo.reservationWH ON  dbo.packageWH.P_ID = dbo.reservationWH.P_ID
JOIN guestWH ON dbo.reservationWH.RNUM = dbo.guestWH.RNUM
GROUP BY
dbo.packageWH.PNAME

GO

SELECT *
FROM vwPackageRevenue

Does this work for you?


MySQL select data over time that matches pattern over several rows We currently log data from our plants running remotely for diagnostic purposes on MySQL.

I have been asked to come up with a typical running profile for several variables. For this, I would require to get a pattern where, say, our plant is running in mode 400 for atleast 2 hours (we currently have the datetime column as primary key).

Would it be possible to come up with a query that says "show rows where plant is running in step 400 for more than 2 hours"?

Thanks :)

EDIT: Sorry, but one more query to add to that, is it possible to search where a step change occurs, like "search for * where value changes from 0 to 100"?

EDIT2:
From a comment - an example would be:

datetime             plant_status incomer_power
2014-09-04 16:16:57     222     -67.06115
2014-09-04 16:17:27     222     -67.5517
2014-09-04 16:17:57     222     -68.0331
2014-09-04 16:18:27     222     -67.99048
2014-09-04 16:19:27     400     -158.7233
2014-09-04 16:19:57     400     -158.9488
2014-09-04 16:20:27     400     -159.0716

This is only a small snapshot of the data. But this data goes on for days. I would like a query, that would bring up the rows when plant_status changed to 400 (only 1 event in case of this example @ 2014-09-04 16:19:27).

I do something similar on my side, and I use a MySQL Stored Procedure / Cursor.

You loop through each line of the table and record the current datetime entry and plant_status into variables, and then move on to the next line.

At which point you compare the current datetime and plant_status with the last ones.

If it matches a set condition (in your case new_status != last_status) you add an entry to a temporary table.

And then at the end you simply select * from temporary_table_name to show you all the rows where the status changed.

You could easily convert this to check the datetime as well (changing the old_datetime variable each time the status changes), and comparing that to the current one to find out if there is a two hour difference).
(this probably should be a comment rather than an answer but I don't yet have enough points to comment - sorry)


SSIS - XML with row delimiter to a single column of a SQL table I'm trying to upload data from a CSV file into a SQL server table through SSIS package (Flat File source and OLEDB destination). This CSV has comma as column delimiter and {CR}{LF} as row delimiter. This csv file has one column which holds XML data in it. In this XML, they again used {CR}{LF} to make it as a multi-line XML and this XML is placed within a double quotes (""). Ideally, this whole XML needs to be placed in one column however {CR}{LF} makes it as a new row in my table.

Is there any method to have escape sequence and place the whole XML data into a single column?

I'm using Visual studio 2013 and connected to SQL server 2014 database.

Thanks.

In the flat-file connection manager, in the General pane, there is an option to set a text qualifier. If you set this to double quote (") for your file then SSIS should ignore any row delimiters that are contained between the double quotes.

There is also an AlwaysCheckForRowDelimiters property that could help. See this msdn page for more.


Access query to select multiple records with matching field I have a table with a list of people ([FirstName], [Surname], [Age]). Some of these people are in the same family. I'd like to return a list of people who have another family member in the table (same surname). However I'd like to exclude records that have no other records with the same surname. Whats the best way to do this in MS Access

Select [FirstName], [Surname], [Age]
From People
Where DCount("Surname", "People", "Surname='" & SurName & "'") > 1

OR

Select P.*
From (Select Surname From People Group by Surname Having Count(*) > 1) AS G
JOIN People AS P ON P.Surname = G.Surname


Automatic conversion of anonymous records returned from an UDF to well known table-type Proprietary code (that we cannot change) has a bunch of user defined functions of the type:

create or replace function f() returns record as $$ ... $$

which we call in the following way (for example):

SELECT status, log FROM f() as (status boolean, log text);

(status boolean, log text) is a rowtype of table T. Is it possible to automatically convert the record (or setof record return type) into the T rowtype without listing the attributes? What I am looking for is of the kind:

SELECT * FROM f() as T%rowtype

There is a way.

Given a table t and a function f() that returns an anonymous record that would match that table type:

CREATE TABLE t (id int, d date);

You cannot just cast the anonymous record, since a column definition list is required for

SELECT * FROM f()

Quoting the manual on the SELECT command:

  If the function has been defined as returning the record data type,
  then an alias or the key word AS must be present, followed by a column
  definition list in the form ...

Bold emphasis mine.

So, while all of these queries work:

SELECT '(1,2013-11-11)'::t;
SELECT ('(1,2013-11-11)'::t).*;
SELECT f();                      -- returning anonymous record
SELECT * FROM f() AS f(id int, d date);

Neither of these do:

SELECT * FROM f();
SELECT * FROM f()::t;

The latter raising an exception:

  ERROR:  cannot cast type record to t

You could wrap the SELECT with column definition list into a VIEW or function like @a_horse and @deszo suggested. That would work just fine:

CREATE OR REPLACE VIEW v1 AS
SELECT * FROM f() AS f(id int, d date);

But that wouldn't answer your question:

  convert the record (or setof record return type) into the T rowtype
  without listing the attributes?

Solution for single row

While a cast fails, an assignment in plpgsql works.

CREATE OR REPLACE function f1(OUT rec t) AS
$func$
BEGIN
rec := f();   -- assignment succeeds where cast failed (!)
END
$func$ LANGUAGE plpgsql;

Call:

SELECT * FROM f1();

While you can also write a set returning function with that pattern I did not find a way to SELECT from a set returning function without supplying a column definition list ...

->SQLfiddle


Just update one column, trigger updates another I have logged the slowest queries on my database, and one surprises me, showing many times in the list, and taking often many seconds to execute.

UPDATE books SET last_read='2018-12-31 11:23:45' WHERE book_id='15'

book_id is int identity PK (clustered), last_read is a datetime. The query is written with the 15 in single quotes, thus requiring a conversion, but I cannot imagine this being a big deal, because the conversion would only be done once per query. There are 6 indexes on the table, but the column last_read is not involved or included in any of them. The PK is on book_id and is nothing special.
The table is very central in database, contains 50 columns and 400,000 rows.

The estimated execution plan tells me:

UPDATE: 0%
Clustered Index Update (on the PK constraint): 100%

The table has a trigger FOR DELETE, which I consider irrelevant here, and a trigger AFTER UPDATE, which reads:

CREATE TRIGGER books_last_update
ON books
AFTER UPDATE
AS
    UPDATE books
    SET last_update = GETDATE()
    WHERE book_id IN (SELECT book_id FROM Inserted)
GO

This shows me that the initial query actually performs two queries, one after the other. However, both of the updated columns have no index defined.
I would like to make the initial query perform faster.

Do you see any optimization possible?

I am afraid that, since the trigger is on UPDATE, and it actually updates the column, it may trigger multiple times. Is it possible? If yes, how do I prevent it?

book_id is int

Then your code should say:

WHERE book_id = 15

And not:

WHERE book_id='15'

This shouldn't affect performance in this case, but it is a best practice to delimit (or not) correctly. Non-Unicode strings and dates with single quotes, Unicode strings prefixed with N, binary and numerics without any quotes.

  I am afraid that, since the trigger is on UPDATE, and it actually updates the column, it may trigger multiple times. Is it possible?

No, SQL Server is smart enough to not throw itself off a cliff. It recognizes that the update inside the trigger shouldn't recursively call the trigger in an infinite loop. You can prove this by running a server-side trace or an extended events session and you will see that the update inside the trigger only runs once.

You can add code to prevent an update of that column from triggering a second update (for example if someone had manually updated the last_update column, but that doesn't seem relevant here).

  Do you see any optimization possible?

Generally, if you find that triggers are slowing down your code (I haven't seen any proof of that in this case), look to get rid of the triggers. I'm not sure what application code is updating this table directly, but if you can use a stored procedure to process all updates to the table, then you can add the update to this column there, instead of having to rely on the trigger. But I suspect if you are waiting 5 seconds for this update to happen, the trigger is NOT the problem.


SSDT publish multiple referenced database projects to new instance I am going through the process of importing our existing SQL Server databases into SQL Server Data Tools in Visual Studio 2015 with the aim of putting them under version control.

All the database projects in my solution build correctly after splitting databases with circular references into composite projects. My problem is now trying to deploy them all to a new instance, the publishing is failing because the databases being referenced don't exist e.g.

  SQL72014: .Net SqlClient Data Provider: Msg 208, Level 16, State 1,
  Procedure Foo, Line 13 Invalid object name
  'DB1.dbo.Bar'.

If I try to deploy DB1 then it's same thing as that also references another database which hasn't been created yet

  SQL72014: .Net SqlClient Data Provider: Msg 208, Level 16, State 1,
  Procedure Bar, Line 64 Invalid object name
  'DB2.dbo.Foo'

How do I resolve this so I can publish all database projects in my solution to a new instance?

I've been able to get all databases in my solution to publish by doing the following:

Setting the correct connection string and publish options under Project Properties > Debug for each project in my solution.
Assigning multiple startup projects in Solution Properties
Debugging the solution will then build/deploy all databases in the correct order

Though this doesn't feel like it is the correct way to achieve this as it now means I need to modify all my projects if I want to publish to a different server.


Handling newsletter mail subscribers data A simple data structure for subscribers is email, name, phone, building.

Currently, Building is a VARCHAR field, however data contained in the field is actually mostly numeric.  How can I change the field type to INT without losing data?

Also, if a subscriber sends a request for newsletter, with name, phone, address in the request, and he need to click on a confirm link for the request, what will happen if before he clicks the link, the data structure is changed?  For example, if the phone field is deleted, when I insert the new subscribers data, there will be problem because the database structure no longer matches.  How can I fix this?

For your first problem, you may have to do it in stages. Here's one approach:

Create the new INT column - BUILDING_ID_INT
Create a new table that maps the building name text to Integer values
Write a program to populate the new BUILDING_ID_INT column based on the data in the current Building column and the values in the table created in #2.
Re-write all of your existing code to use BUILDING_ID_INT.
Delete the old Building column.

For the second problem, where the database structure changes between the time a user makes a request, and the time they confirm their request, it will vary on the kind of change. If an old column is being removed, then there shouldn't be any problem because your current insert statement should not be inserting data that's not insertable. For new columns that are added, default values can be used of it's non-nullable, and process will have to be introduced to handle these. For small volumes, it can be manually asking these users to send the additional new information. For large volumes, you'll want to write code.


sqlplus, command line parameters and ORACLE_HOME with spaces I have noticed strange interaction between ORACLE_HOME and parameter substitution.
Here is my test.sql

prompt &1
prompt &2
prompt &3
prompt &4
exit

The expected behavior:

C:\cygwin64\home\rejap\subst>sqlplus /nolog @test.sql one two three four five

SQL*Plus: Release 12.2.0.1.0 Production on Sun Jun 16 15:19:16 2019

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

one
two
three
four

C:\cygwin64\home\rejap\subst>

however, when ORACLE_HOME is set, things get interesting

C:\cygwin64\home\rejap\subst>set ORACLE_HOME=c:\Program Files (x86)\Oracle\product\12.2.0\client_1

C:\cygwin64\home\rejap\subst>sqlplus /nolog @test.sql one two three four five

SQL*Plus: Release 12.2.0.1.0 Production on Sun Jun 16 15:21:25 2019

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

Files
(x86)\Oracle\product\12.2.0\client_1\sqlplus\admin\glogin.sql
three
four

C:\cygwin64\home\rejap\subst>

Am I missing something fundamental here?

Are there any negative effects of not setting ORACLE_HOME at all?

Are there any negative effects of pointing ORACLE_HOME to symlink?

c:
cd \
mklink /D oracle c:\Program Files (x86)\Oracle
set ORACLE_HOME=c:\oracle\product\12.2.0\client_1

And of course I am interested in explanation of what is going on.

sqlplus and many other programs do not require ORACLE_HOME to be set.

In this documentation 9.2 Oracle states:

  Oracle Corporation recommends that you never set the ORACLE_HOME
  environment variable, because it is not required for Oracle products
  to function properly. If you set the ORACLE_HOME environment variable,
  then Oracle Universal Installer will unset it for you.

I did not find similar statement for never releases of Oracle, so it might be outdated. Newer installation guides for Windows do not mention ORACLE_HOME environment variable at all, so most likely it is really not needed.


Difference between count(*) and count(1) in mysql? In MySql we can count the total number of records by using count(1) or count(*).

Is there any technical difference between them?

They are the same. This has often been asked in Stackoverflow and here

count(*), count(0), count(1), count(-1)

all return the count.

In fact, in SQL Server, the expression isn't even evaluated.

Edit In fact, in SQL Server, a COUNT(ALL ...) CONSTANT expression doesn't appear to be evaluated at all, however, a COUNT(DISTINCT ...) is*.

e.g.

select count(ALL 1/0) from xyz; -- Succeeds

but

select count(DISTINCT 1/0) from xyz;  -- Divide by Zero

and at least one exception is NULL

select count(1) from xyz; -- Operand data type void type is invalid for count operator.

FWR in MySQL count(1/0) returns 0 irrespective of the number of rows.


How can we configure the Streaming Replication(master-slave) on single SAN storage in PostgreSQL? I want to configure the Streaming Replication in PostgreSQL.My data directories of Primary and Standby are in same SAN storage.Is it Possible to do replication.

I try to answer below, but:

DON'T DO THIS!

You are planning a disaster recovery setup, but when the disaster hits the shared storage (the SAN), you will stand there with no usable database.

You can do that, yes, just as @a_horse_with_no_name describes in his comment.  You have to have two separate data directories for it, the master pointing to one and the slave to the other.  Setting up the replication is the same as with completely separated machines.

Also, plan regularly taking and testing backups as part of your disaster recovery process.


Connect Error (2054) - mysql_old_password issue still not solved I use PHP 5.4.3 and I want to connect to a MySQL database (DB for brevity) remotely (the PHP script resides on a different server than the DB) with normal object-oriented programming (OOP) way:

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

and I get the error:

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

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

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

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

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

Thanks for your time.

PHP must be expecting to use the old MySQL authentication plugin algorithms. You do not have to restart mysql. Since old_passwords is a globally dynamic option, all you have to do is run the following:

SET GLOBAL old_passwords = 1;

In addition, please add this to /etc/my.cnf:

[mysqld]
old_passwords=1

to have future restarts retain this setting.

To further verify the need to do this, the next time you login to mysql, run this

SELECT LENGTH(password) password_length,COUNT(1) length_count
FROM mysql.user GROUP BY LENGTH(password);

If none of the passwords are of length 16, this may explain PHP's reluctance to login.

Sad to say, but the alternative would be to setup 16-character passwords, but you cannot reverse-engineer 41-character passwords. You would have to manually setup the 16-character passwords  using the original plain-text values.

For example, if root@localhost had 'helloworld' as the password, it would have convert it using the OLD_PASSWORD function. Here is a comparison:

mysql> select password('helloworld');
+-------------------------------------------+
| password('helloworld')                    |
+-------------------------------------------+
| *D35DB127DB631E6E27C6B75E8D376B04F64FAF83 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select old_password('helloworld');
+----------------------------+
| old_password('helloworld') |
+----------------------------+
| 6c755a9e66debe8a           |
+----------------------------+
1 row in set (0.00 sec)

mysql>

Once you activate old_passwords, to convert it you would have to do something like this:

UPDATE mysql.user
SET password = OLD_PASSWORD('helloworld')
WHERE password = PASSWORD('helloworld');
FLUSH PRIVILEGES;

UPDATE 2013-02-02 22:44 EDT

If changing the authentication style on the MySQL side has not worked for you at this point, I have some bad news for you. You stated earlier:

  I used php 5.2.9 and everything worked ok but lowering php is not a solution for me. I also have to mention that i dont have full access to DB's settings cause the client doesn't give me that full access.

You may need to downgrade PHP after all. If your hosting provider isn't in position or unwilling to adjust MySQL or give you a downgraded PHP, you will have to go with a hosting provider that will.

As an alternative you may want to look into Amazon EC2 when you can have full control of all software upgrades after running sudo -s. The links you mentioned before gave you what you needed to know. I gave you what you can do to accommodate the MySQL side. Do what you know has to be done to get this solved.


SQL case statement not executing 'else' I am on Oracle 12.1.0.2.0
I have a query that I wrote utilizing a CASE statement. I have 2 "WHEN" statements and one "ELSE" statement. The query is working as expected except for the "ELSE" statement. I get no syntax errors it just not hitting the "ELSE" case when it should, and instead returns nothing.

SELECT
CASE
WHEN COUNT(COLUMN_A) = 1
    THEN 'PASS'
WHEN COUNT(COLUMN_A) > 1
    THEN 'FAIL'
ELSE 'DOES NOT EXIST'
END AS TEST,
SCH.COLUMN_A
FROM SCHEMA.TABLE_A SCH
WHERE SCH.COLUMN_A = 'NA'
GROUP BY COLUMN_A

When I run this query (which in my understanding is the else case) then it returns 0 as it should:

SELECT COUNT(COLUMN_A) < 1
FROM SCHEMA.TABLE_A SCH
WHERE SCH.COLUMN_A = 'NA'

If you need to provide table structure, let me know but it is fairly straightforward.

What am I doing wrong? Why, when the query returns 0, is the else statement not returning "DOES NOT EXIST"? I have a feeling it is something simple but I am stuck and could use some help. I appreciate it.

You used this predicate: COLUMN_A = 'NA'

The result of COUNT(COLUMN_A) can be 0 only if you have 0 rows where COLUMN_A = 'NA' (as nulls are excluded because of the predicate). If you have 0 rows, the CASE statement is not evaluated at all, and the above query returns no result.

You need a query that returns result even if no rows match your predicate. E.g:

SELECT
  CASE
    WHEN C = 1
      THEN 'PASS'
    WHEN C > 1
      THEN 'FAIL'
    ELSE 'DOES NOT EXIST',
    'NA'
FROM
(
  SELECT COUNT(*) AS C
  FROM SCHEMA.TABLE_A SCH
  WHERE SCH.COLUMN_A = 'NA'
);


SQL Server Specific term full text search is not working I'm trying to perform an specific term full-text search in SQL Server. It looks like the way to do it is by wrapping the keywords in double quotes, which I'm doing in my app:

Query = "\"" + model.SearchString + "\""

However, when I perform the query against the database with a term such as "foo bar", it returns every occurrence of "foo" and "bar" inside the database. The expected result is that the database should return only the records that have "foo bar" specifically. This is the query:

SELECT o.OpinionDocumentId
FROM CONTAINSTABLE(OpinionDocuments, Html, '"foo bar"') AS KEY_TBL
LEFT JOIN dbo.OpinionDocuments AS o ON o.OpinionDocumentId = KEY_TBL.[KEY]
WHERE o.Date BETWEEN @from AND @to

What could be the reason why this query is not returning the results from an specific set of keywords?

Thanks

Your query actually needs to use two properties of the CONTAINSTABLE function-

<maximum distance> and <match_order>.

Using

CONTAINSTABLE(OpinionDocuments, HTML, 'NEAR((Foo, bar), 0, TRUE)') AS KEY_TBL

Specifies to find "Foo" near "Bar" with 0 search objects between (<maximum distance>  ", 0,") and in the order specified (<match_order> ",TRUE")


AES 256 data encryption on Mysql Is there a way to encrypt the whole data of a mysql DB using the AES 256 symmetric encryption method without encrypting the data manually using AES_ENCRYPT ?

This article explains about the same functionality in MsSql databases

XtraBackup provides such a functionality


database schema design for school system I am trying to create a database to store information about a school.

I need to store each department in the school. Each department then has a list of teachers. Each teacher has a list of classes and each class has a list of students.

The department name will be unique. However, teachers may span across departments. Another thing to note is that if teacher A teaches a class called 'Maths' and teacher B also teaches a class called 'Maths' then these should be different entities in the database (I think). For example, I may want to get a list of students that take the class 'Math' with 'teacher A' only.

So far I have this:

departments(department_id, department_name)
teachers(teacher_id, teacher_name);
departments_and_teachers(department_and_teachers_id, department_id (FK), teacher_id (FK))
student(student_id, student_name)
teachers_and_students(department_and_teachers_id, student_id);

which I think is good database design? However, it doesn't include any information about the 'classes' and I'm not sure what the best way of altering the schema would be. For each department and teacher combo I need to add a list of classes that are taught. Is it plausible to do something like this:

classes(class_id, class_name)
departments_teachers_and_classes(dtc_id, department_and_teachers_id (FK), class_id(FK));
department_teachers_classes_and_students(student_id, dtc_id)

EDIT - new suggestion based on answer received

departments(department_id, department_name)
teachers(teacher_id, teacher_name)
classes(class_id, class_name, teacher_id (PK), department_id (PK))
student(student_id, student_name);
classStudents(student_id (PK), class_id (PK))

To your specific question of (easily satisfied) plausibility, the answer is yes.  Since you seem to be looking for a broader critique. You seem to be mostly on the right track, but a few things are making your description overly complex.

It looks like your Classes table is more like what I would consider a course to be and your departments_teachers_and_classes is more of what I would expect a Classes table to be.  A course would be Math, but a class would be the Math course taught by a particular teacher during a particular school term.  A class is like an instance of a course.

To carry this change through, your departments_teachers_classes_and_students could simply be ClassStudents.

It seems as though you are using the department concept on several different levels.  You should decide where the department belongs and stop referencing it everywhere else.  You've said that a teacher can teach for multiple departments, so we can't put the department in the teachers table.  This leaves Classes and Courses.  Whichever you decide, it need only have a foreign key to the Departments table (unless you decide that one can be in multiple Departments).  This eliminates the Departments_and_teachers table.

Teachers_and_Students could be replaced with a teacher foreign key in the Classes table and students/classes foreign keys in a ClassStudents table.

In short, for the portion of the design you have described, you probably need the following tables:

Departments
Teachers
Students
Courses
Classes
ClassStudents
Terms


Sql Server audit could not write to the security log I am following the training kit for 70-462(Administering Microsoft SQL Server 2012 Databases), and stuck on Auditing SQL Server instances lesson. I have created new user in domain controller, assign it Generate Security audits and Log on as a service rights. Then I installed SQL Server instance on one of the machine in the domain with this account. I checked installed instance service and it is started under this account. I have also followed the steps here http://www.sqlskills.com/blogs/jonathan/resolving-error-33204-sql-server-audit-could-not-write-to-the-security-log/.

But when I am enabling the audit it logs the error:

  Sql Server audit could not write to the security log

Version: SQL Server 2012 - 11.0.2100.60(X64) Enterprise Edition (64-bit) on Windows NT 6.1 <X64>(Build 7601: Service Pack 1) (Hypervisor)

What else can I check?

It is a little bit silly, but after restarting the machine audit was enabled without any problem.


How do I combine multiple rows when using select that are essentially the same data? I have a database where there's a description text stored but the description text itself is broken up into separate rows based on the text itself.

For example:

|ID  |Row  |Text                  |
-----------------------------------
|1   |1    |The cat went to the st|
|1   |2    |ore and bought some ca|
|1   |3    |tnip.                 |
|2   |1    |The dog didn't like to|
|2   |2    |drink water.          |
|3   |1    |It's cold outside     |

How would I write a select query so that all of the rows in the Text column are combined into a single result based on the ID column and ordered according to the Row column?

It's quite simple using GROUP_CONCAT():

select group_concat(txt order by roooow SEPARATOR '')
from story
group by id
order by roooow;

By the way, row and text are bad names for columns, as they are reserved keywords.


Use GROUP_CONCAT() for this.

Your table:

create table foo
(
id int,
name varchar(10),
status int,
c_lan varchar(10),
Java_lan varchar(10),
Dot_lan varchar(10)
);

insert into foo values (  1,'A',111,'yes','Q','W'   );

insert into foo values (  1,'A',222,'yes','Q','W'   );

Testcase:

mysql> select id, GROUP_CONCAT(status separator ',') as status, c_lan, Java_lan, Dot_lan from foo group by id;
+------+---------+-------+----------+---------+
| id   | status  | c_lan | Java_lan | Dot_lan |
+------+---------+-------+----------+---------+
|    1 | 111,222 | yes   | Q        | W       |
+------+---------+-------+----------+---------+
1 row in set (0.00 sec)

mysql>

View:

create view concatview as (
select id, GROUP_CONCAT(status separator ',') as status, c_lan, Java_lan, Dot_lan
from foo
group by id
);

Test:

mysql> select * from concatview;
+------+---------+-------+----------+---------+
| id   | status  | c_lan | Java_lan | Dot_lan |
+------+---------+-------+----------+---------+
|    1 | 111,222 | yes   | Q        | W       |
+------+---------+-------+----------+---------+
1 row in set (0.00 sec)

mysql>

Obviously you may want to GROUP on more than just id (depending on what you consider a "duplicate", and may want to order the GROUP_CONCAT() in some way, but this points you in the right direction.

Edit to address your comment:

mysql> insert into foo values ( 2,'b',555,'yes','Q','W' );
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> select id, GROUP_CONCAT(status separator ',') as status, c_lan, Java_lan, Dot_lan
    -> from foo
    -> group by id
    -> ;
+------+---------+-------+----------+---------+
| id   | status  | c_lan | Java_lan | Dot_lan |
+------+---------+-------+----------+---------+
|    1 | 111,222 | yes   | Q        | W       |
|    2 | 555     | yes   | Q        | W       |
+------+---------+-------+----------+---------+
2 rows in set (0.01 sec)

mysql>


What is the safe+fast way to move all Postgresql data to another machine? I need to copy all data from one Postgresql 9.2.4 server to another (both on similar hardware, Ubuntu 12.04). Is it safe to shut down the source Postgres server, rsync the data directory to the new server and start up both servers? Do I need to copy the pg_xlog directory as well? Or is it ok to leave that out since the source server will be shut down cleanly (service postgresql stop)?

I have approx 275G of data so don't want to backup and restore as that will take ages.

No, the pg_xlog directory can't be omitted even if the server has been properly shut down before copying the data directory.

If you try, you should see that the target server will fail to start, complaining that it can't find a checkpoint record.


SSIS: Check file for row delimiter and dynamicly update row delimiter in Flat File Connection Manager at run time I am importing about 50 or so txt files into a SQL Server table. All the files have the same layout except that for some files the row delimiter used was {CR}{LF} and for others {LF} was used.

Ideally I will be asking the person who creates the txt files to stick with the same delimiter moving forward but I would like to know if there is a way that I can scan the file (maybe using a C# script) in order to capture the delimiter used in the file to then be able to update the Flat File Connection Manager's Row delimiter dynamically at run time.

Is this possible? If so, does anyone have an example of how to accomplish (or a better solution)?

I just found a Easy way here

You Just need to Set an Expression to Replace the '/r'(CR) in Data Flow Task

Just Awesome trick that can accept both CRLF and LF within a SSIS Package


In where clause, does MySQL continue checking conditions if first condition before AND falsy I have a question about and AND inside a where clause of a query. Within the where clause, I have a subquery but it is after a few other, row value conditions, like validated = 1 AND (subquery). I have a question about the AND...

How does MySQL treat it? Will it continue to the subquery if the first condition in the where clause, before the AND is false?

I did some experimenting.  Here's my guess:

WHERE (constant-expression-evaluating-to-FALSE)
  AND (SELECT ...)

It will evaluate the constant and stop.

WHERE (col ... )  -- some test that can't use an index (but always fails)
  AND (SELECT ...)

It will evaluate the SELECT.  Here the Optimizer is being smart -- it realizes that reevaluating the SELECT is likely to be costly, so better to do it once.  Actually, the EXPLAIN FORMAT=JSON says "optimized_away_subquery", which probably means that, in the process of deciding what to do with the subquery, it evaluated it.

Note that I am assuming that the subquery is not correlated.  That is, nothing from the original table is referenced in it.  If it were, then the Optimizer would be obligated to evaluate it for each row in the outer query.  And it would probably go back to the original case -- always FALSE would avoid evaluating the subquery.  However, multiple TRUEs would lead to multiple evaluations.

(I arrived at this by (1) somewhat understanding the Optimizer, and (2) testing.  Caveat:  My test was with 5.6.22; older versions, and MariaDB may work differently.  However, I believe that 5.7 and 8.0 will work the same.)


Is there a way to prevent Scalar UDFs in computed columns from inhibiting parallelism? Much has been written about the perils of Scalar UDFs in SQL Server. A casual search will return oodles of results.

There are some places where a Scalar UDF is the only option, though.

As an example: when dealing with XML: XQuery can't be used as a computed column definition. One option documented by Microsoft is to use a Scalar UDF to encapsulate your XQuery in a Scalar UDF, and then use it in a computed column.

This has various effects, and some workarounds.

Executes row by row when the table is queried
Forces all queries against the table to run serially

You can get around the row-by-row execution by schemabinding the function, and either persisting the computed column, or indexing it. Neither of those methods can prevent the forced serialization of queries hitting the table, even when the scalar UDF isn't referenced.

Is there a known way to do that?

Yes if you:

are running SQL Server 2014 or later; and
are able to run the query with trace flag 176 active; and
the computed column is PERSISTED

Specifically, at least the following versions are required:

Cumulative Update 2 for SQL Server 2016 SP1
Cumulative Update 4 for SQL Server 2016 RTM
Cumulative Update 6 for SQL Server 2014 SP2

BUT to avoid a bug (ref for 2014, and for 2016 and 2017) introduced in those fixes, instead apply:

Cumulative Update 1 for SQL Server 2017
Cumulative Update 5 for SQL Server 2016 SP1
Cumulative Update 8 for SQL Server 2016 RTM
Cumulative Update 8 for SQL Server 2014 SP2

The trace flag is effective as a start-up –T option, at both global and session scope using DBCC TRACEON, and per query with OPTION (QUERYTRACEON) or a plan guide.

Trace flag 176 prevents persisted computed column expansion.

The initial metadata load performed when compiling a query brings in all columns, not just those directly referenced. This makes all computed column definitions available for matching, which is generally a good thing.

As an unfortunate side-effect, if one of the loaded (computed) columns uses a scalar user-defined function, its presence disables parallelism for the whole query, even when the computed column is not actually used.

Trace flag 176 helps with this, if the column is persisted, by not loading the definition (since expansion is skipped). This way, a scalar user-defined function is never present in the compiling query tree, so parallelism is not disabled.

The main drawback of trace flag 176 (aside from being only lightly documented) is that it also prevents query expression matching to persisted computed columns: If the query contains an expression matching a persisted computed column, trace flag 176 will prevent the expression being replaced by a reference to the computed column.

For more details, see my SQLPerformance.com article, Properly Persisted Computed Columns.

Since the question mentions XML, as an alternative to promoting values using a computed column and scalar function, you could also look at using a Selective XML Index, as you wrote about in Selective XML Indexes: Not Bad At All.


What's the best solution for distribution of one database over two servers? I have a MS SQL Server 2014 database with large scale of data an lots of users;
and the RAID level is equal to 10. Now due to the rapid growth of data amount, I'm going to distribute this one database over two servers. What's the best solution for that?


oracle: No more data to read from socket We are using Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 and we have an error on a query that gives us the error

Error: No more data to read from socket
SQLState:  null
ErrorCode: 17410

The query is similar to the following

select * from (
    select ... <many, MANY fields>
    from table
    inner join
    left outer join
    left outer join
    inner join
    where string1='value1' and string2='value2'

) where rownum > 500 and rownum < 510

If I try to remove the final where statement the query runs fine.
Also if I try to "reduce" the rownum filter to a lower level of records the query runs fine:

where rownum<10

Searching on internet I have found that it could be caused by the parameter "cursor_sharing" to "similar" that seems buggy on some version of oracle. I tried to set it to "exact" but unfortunately I have the same error.

I was wondering if the retrieved data of a select statement may have a limit in bytes in oracle10g but I haven't found anything. I have found a limit on oracle8i but nothing to oracle10g.

We have the same problem executing the query on different client (dbvisualizer, squirrel) and on oracle console.

No more data from socket means that the shadow process crashed, with an
ORA-600 or ORA-7445, for example. Look for a trace file in the
user_dump_dest directory and/or an entry in the alert log. This is
likely a bug

See this : https://blogs.oracle.com/db/entry/ora-600_troubleshooting