Strictly only RAM matters? I have to work on a project which would have a extra large table with billions of records but only two fields one is id and another is text.

And id would be set to BIGINT and PK then if 8+2 (extra 2 bytes for overhead) bytes per row is taken to index then in my calculation 64GB RAM should be enough to hold at least 5 billion records index.

But its only one factor I am considering.

Do you think there are other factors also which I should consider?

And RAM size can also be increased if needed.
And that table would be most busy table with few writes and lot of reads.

You need to consider the storage engine you will be using for your project because MySQL caches differently for each storage engine.

MyISAM caches index pages only. InnoDB caches data and index pages.

Given that you have a table with large ids, I hope you are using MyISAM.

Here is a query to help you determine the best key_buffer_size:

SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1))
recommended_key_buffer_size FROM
(SELECT LEAST(POWER(2,32),KBS1) KBS
FROM (SELECT SUM(index_length) KBS1
FROM information_schema.tables
WHERE engine='MyISAM' AND
table_schema NOT IN ('information_schema','mysql')) AA ) A,
(SELECT 2 PowerOf1024) B;

This will give the Recommended Setting for MyISAM Key Cache (key_buffer_size) given your current data set (the query will cap the recommendation at 4G (4096M). For 32-bit OS, 4GB is the limit. For 64-bit, 8GB.

UPDATE 2011-09-16 14:49 EDT

If you are using InnoDB, sizing the key_buffer_size will not help. InnoDB and MyISAM cache differently.

Here is the same kind of query to size your InnoDB Buffer Pool:

SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
WHERE engine='InnoDB') A,
(SELECT 2 PowerOf1024) B;


Merge Replication: Where are rowguids of conflict losers stored? I'm  trying to obtain the row guid of all the merge replication conflict losers listed in my conflict viewer.  The bug that was allowing the conflicts to occur has been identified and resolved but now I need to go and remove the conflict losers from the subscribers so that they quit trying to upload the rows in conflict with each new sync.

I can go through each conflict individually to obtain the rowguid but there are a couple thousand of them.  So, surely there's got to be a query I can write to obtain this data in one swoop.

Anyone have experience querying the merge replication system tables for conflict info or know which system table I should be looking in.  I'm not finding much help out on the interwebs.

Straight from Advanced Merge Replication Conflict Detection and Resolution:

The Conflict Viewer displays information from three system tables:

Replication creates a conflict table for each table in a merge
article, with a name in the form
MSmerge_conflict__.
Conflict tables have the same structure as the tables on which they
are based. A row in one of these tables consists of the losing
version of a conflict row (the winning version of the row is in the
actual user table).
The MSmerge_conflicts_info table provides information about each
conflict, including the conflict type.
The sysmergearticles table identifies which user tables have conflict
tables and provides information about the conflict tables.

This means that if you find a record in the MSmerge_conflict_[PublicationName]_[ArticleName] table, it is the "loser".  The record that you find in the actual table is the "winner".

Hope this helps,

Matt


Drop privilege to tables only, Not to database I created a user with drop privilege to a database.

How can I create a user who has the privilege to drop tables of a specified database, but not database.

Do GRANT DROP ON TABLE thedatabase.* TO user1.
The MySQL Reference Manual is a great place to look for this kinds of things.


How do you use psql client to connect to a postgresql ipv6 host? # postgresql.conf
listen_addresses='::'

and

# pg_hba.conf
hostssl  webdb  webserver   ::0/0    cert

The postgresql server is running on docker with pingable ipv6 address of "GlobalIPv6Address": "fe80::242:ac12:2" - so no firewalls obstructing.

I am using the following command to connect

psql --command="select * from test;" -d webdb -h fe80::242:ac12:2 -p 5432 -U postgres
psql: could not connect to server: Invalid argument
    Is the server running on host "fe80::242:ac12:2" and accepting
    TCP/IP connections on port 5432?

Why is the host not recognized? Is it not possible to use ipv6 with psql? Also, I did not find an ssl parameter option in psql.

As Daniel Vérité mentioned in the comment, on Unix, "%interface" should be given as a suffix to the ipv6 address. You can get the interfaces by running ifconfig command. So, the right psql command was

psql --command="select * from test;" -d webdb -h fe80::242:ac12:2%br-67d2c47f5b8e -p 5432 -U webserver

I had other problems. But now, the host was reachable. Next, I noticed that the suffix "clientcert = 1" had to be added to pg_hba.conf (don't know why)

# pg_hba.conf
hostssl  webdb  webserver   ::0/0    cert clientcert=1

listen_addresses value in postgresql.conf was fine (restricting requests & responses to only ipv6 addresses) - so is the value '::0/0' in pg_hba.conf

I then got ssl validation errors from the server. I had to set path variables on my client side for psql.

export PGSSLCERT=/my-folder/my-certificate.pem
export PGSSLKEY=/my-folder/my-private-key.pem

If your private key is password protected, psql prompts you for the password. Once the connection went through, (=> ssl works fine) , I noticed that the role "webserver" didn't have login permission. I had to alter-role to allow login and had to grant select and update access to the test table.

Now it works. Thanks to Daniel for the ipv6 suffix solution.


Correct data types for my database I have to make Movies database with the following tables:

•   Directors (Id, DirectorName, Notes)

•   Genres (Id, GenreName, Notes)

•   Categories (Id, CategoryName, Notes)

•   Movies (Id, Title, DirectorId, CopyrightYear, Length, GenreId, CategoryId, Rating, Notes)

Let me give you my query for Directors database:

CREATE TABLE Directors (
Id INT PRIMARY KEY IDENTITY,
DirectorName NVARCHAR(60) NOT NULL,
Notes NVARCHAR(MAX)
)

Genres and Categories tables are almost the same. I have only changed the size of NVARCHAR.

I am having doubts about Movies table. I don't have experience with databases and I am not sure if I chose the right data types for each column. I would be very grateful if someone with more experience can help me and see what I've done.

CREATE TABLE Movies (
Id INT PRIMARY KEY IDENTITY,
Title NVARCHAR(60) NOT NULL,
DirectorId INT FOREIGN KEY REFERENCES Directors(Id),
CopyrightYear SMALLINT NOT NULL,
[Length] FLOAT(1) NOT NULL,
GenreId INT FOREIGN KEY REFERENCES Genres(Id),
CategoryId INT FOREIGN KEY REFERENCES Categories(Id),
Rating FLOAT(1) NOT NULL,
Notes NVARCHAR(MAX)
)

The biggest issues that jump out to me are the lengths of the NVARCHAR columns (60 characters will certainly not be long enough for all movie titles, and probably not enough for all director names), and the use of the FLOAT data type. In my opinion and experience, FLOAT is almost never the right data type to use.

For Length, the correct type would depend on what is being stored. Most of the time, movie length is stored as a number of minutes (rounded to the nearest whole number). If that is how you are storing it, I would suggest SMALLINT. However, if you are storing length as hours or something where you need non-integer values, I would suggest the NUMERIC (or DECIMAL; they are synonyms and mean exactly the same thing) data type (choose precision and scale to meet your needs; see https://docs.microsoft.com/en-us/sql/t-sql/data-types/decimal-and-numeric-transact-sql?view=sql-server-2017 for details).

For Rating, I'm guessing you're not talking about something like MPAA rating (which is a string value), but some kind of critics rating or something like that. Again, I would suggest using NUMERIC instead of FLOAT.

Since your question was only about data types, that is all that I have addressed. However, there are certainly other design concerns I would have with this design (like, how are you handling movies with multiple directors, genres, and categories?). But that seems to be out of scope for this specific question.


Stored procedure to change string to datetime in MySQL This is a follow up to this question. Turns out the driver that I am using (can't change it) is not compatible with timestamp values. Lets say it is set in stone that I have to load the data into my MySQL 8.0 database as a string type. There is no native function within the software I am using to read in the PLC and write into the db that can change a string to a timestamp.
What I'm trying to figure out now, is how to set up a trigger that converts the string to a timestamp on insert. This idea was suggested to me by the help hotline of the software, but if you have any better ideas, I'm open to that too. Thus far, I have 2 columns: one where I "receive" the string which is of type VARCHAR(30), and then another one where I write the timestamp value with the trigger.
This setup feels super stupid to me because I end up with 2 columns with the same data just in different formats, and one of them is completely useless.
Thus far, my table looks like:
CREATE TABLE `test` (
  `id` mediumint NOT NULL AUTO_INCREMENT,
  `Date_VARCHAR` varchar(30) DEFAULT NULL,
  `Date_TIMESTAMP` timestamp(6) NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=52383 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

And the trigger is:
DELIMITER $$
CREATE DEFINER = CURRENT_USER TRIGGER `db_test`.`test_BEFORE_INSERT`
    BEFORE INSERT ON `db_test`.`test` FOR EACH ROW
    BEGIN
    SET NEW.Date_TIMESTAMP = NEW.Date_VARCHAR ;
    END$$
DELIMITER ;

Is there a better way of doing this to avoid having the same data twice? I would like to make a trigger that just converts the data and writes it all in one column instead of the setup I have come up with.
Here's a quick look at what I have going on . I have done an insert of some random values, but it's actually all being done with the datalogger plugin of KEPServerEx.

Based on the fiddle you may apply
ALTER TABLE test
DROP COLUMN Date_TIMESTAMP,
ADD COLUMN Date_TIMESTAMP TIMESTAMP AS (STR_TO_DATE(Date_VARCHAR, '%Y-%m-%dT%h-%i-%s'));

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=104e72ae4730b2c8c30f84f61bf392d7
The column Date_TIMESTAMP may be defined as STORED (occupies disk space, fast access) or VIRTUAL (calculated each time its value is retrieved, does not occupy disk space).

Alternatively you may use BEFORE INSERT and BEFORE UPDATE triggers which will convert inserted data into correct datetime literals (but the column will stay VARCHAR nevertheless). In BEFORE UPDATE you may check current data format to avoid double convertion which will cause an error.
CREATE TRIGGER trigger_name
BEFORE INSERT
ON test
FOR EACH ROW
SET NEW.Date_VARCHAR = STR_TO_DATE(NEW.Date_VARCHAR, '%Y-%m-%dT%h-%i-%s');

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=08eb56a0b8846297c81c07e2703c0ab9


mysqld dead but subsys locked I'm executing the following command in a shell script and I'm executing this on an RHEL-6 server and MySQL version 5.1.47-

mysqld_safe --defaults-extra-file=/custom/path/of/my.cnf

The path of my.cnf can be anything. (If I am not wrong, as a developer we cannot determine where mysql could be installed.) After the above command in the script, there are a few mysql commands to be executed. When the script executes the above command, the output console displays that the mysql service has successfully started. But when the control comes to the part where the mysql commands are to be executed, the following error message comes up-

mysqld dead but subsys locked

Any idea where I am going wrong?

This is what my my.cnf contains-

[mysqld]
innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=1
innodb_file_io_threads=4
max_binlog_size=20M
max_allowed_packet=16M
max_connections=500
log_bin=logFile.log
binlog-do-db=test
server-id=1
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
binlog-format=ROW
log_warnings=1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

For me, this means that the daemon has crashed and was restarted, but the lockfile that mysqld uses to prevent multiple daemons from accessing the same database is still there...

I fix it with

service mysqld stop
service mysqld start

Not sure if you can just to a restart instead of a stop then start, and I haven't figured out the cause yet for the crash either.


PostgreSQL: PreparedStatement to execute set schema command I need to execute in a Java application a SET schema ? statement, where the placeholder is replaced by a value provided at runtime by the caller.

I assumed I could use PreparedStatement to overcome potential SQL injection, but if I try to execute a prepared statement I get a PSQLException with message

ERROR: syntax error at or near "$1"  Position: 18

If I do execute the SQL by just concatenating the schema name by doing:

public void executeSetSchema(Connection con, String schema){
  try{
     Statement stmt = con.createStatement();
     stmt.execute("SET SCHEMA '" + schema + "'");

  } catch(Exception){}
}

Then everything works as expected.

Clearly, this happens with all the latest releases of PostgreSQL

Why I cannot use a PreparedStatement and what should I do instead to prevent SQL injection here?

You can't pass identifiers as parameters. It's similar to select * from ?

However, you can use a function to set the search path:

public void executeSetSchema(Connection con, String schema) {
  try (
    PreparedStatement pstmt = con.prepareStatement("select set_config('search_path', ?, false)");
  ) {
     pstmt.setString(1, schema);
     stmt.execute();
  } catch(Exception) {
  }
}

I moved the prepareStatement() call into a try-with-resources so that the statement is closed properly.


How to extract the grants on default roles of PostgreSQL 10: pg_read_all_settings, pg_read_all_stats, pg_monitor and pg_signal_backend? I have a Postgres instance of version 10 running on AWS RDS. I have a user there with default roles granted to it. I want to create the same user on my sandbox where the instance is of version 9.4. Thus, I want to know the grants that are included in these default roles of version 10.

Have a look at https://www.postgresql.org/docs/current/infoschema-role-table-grants.html

  The view role_table_grants identifies all privileges granted on tables
  or views where the grantor or grantee is a currently enabled role.
  Further information can be found under table_privileges. The only
  effective difference between this view and table_privileges is that
  this view omits tables that have been made accessible to the current
  user by way of a grant to PUBLIC.

See also https://stackoverflow.com/a/55459289/6368697


How can I generate a script to choose only top 10 items from each table in SQL database? I know how to generate scripts that would contain insert queries of all records in all tables. But how can I limit this to top 10 records only from each table?I can select "Data Only" when generating the script, but it generates insert queries for all data. How can get the script to have only top 10 record insert statements?

If you go to the following stack exchange link and scroll down to the second answer where you see Neeraj Prasad Sharma create a stored procedure named INS, if you go into that stored procedure and modify the line

Select @CONDITIONS= 'Select  '+@CONDITIONS +'FRom  ' +@Schema_name+'.'+@Table_name+' With(NOLOCK) ' + ' Where '+@Condition

to be

Select @CONDITIONS= 'Select top 10 '+@CONDITIONS +'FRom  ' +@Schema_name+'.'+@Table_name+' With(NOLOCK) ' + ' Where '+@Condition

Here is a quick table I put together so you can do that and run it to see how it works

CREATE TABLE tbltest
(
    id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
    ,col1 VARCHAR(20)
);
GO
INSERT INTO dbo.tbltest( col1 )
VALUES  ( 'test1' ), ( 'test2' ),( 'test3' ),( 'test4' );
GO
EXEC ins 'dbo.tblTest where 1=1 order by id'

From here you might have to generate your Exec INS executions by using sys.objects or information_schema.tables, but this should do what you are looking to do as long as I understand correctly what you are trying to do.  As Mr. Bertrand said in his comment, this does not take any relationships into account.  But if you are just looking to generate top 10 inserts for each table ordered by something, this should do that for you.


Rename cell based on duplicate compound key in MS Access I'm using MS Access (I know, I know...), and I'm trying to rename a cell when there are duplicates of mutliple columns.

Here's an example:

Col_1   Col_2   Col_3   Note
A1      B1      C1
A1      B2      C1      Not a Dup
A1      B1      C2      Not a Dup
A2      B2      C2
A2      B2      C2      Duplicate
A3      B3      C3
A3      B3      C3      Duplicate
A3      B3      C3      Duplicate

I would like the query or code to update Col_3 (or add another column if that's easier) as below. Note that the order of the duplication is important.

Col_1   Col_2   Col_3   Note
A1      B1      C1
A1      B2      C1      Not a Dup
A1      B1      C2      Not a Dup
A2      B2      C2
A2      B2      C2Dup1  Duplicate
A3      B3      C3
A3      B3      C3Dup1  Duplicate
A3      B3      C3Dup2  Duplicate

I've seen solutions that use row_number() and over() clauses, but those just work in SQL Server.

Any thoughts for how to tackle this in Access?

You will need two things

First, make sure mytable as a autoincrement column called ID

Next, place this SQL in the SQL View of a Query and run it:

SELECT Col_1,Col_2,IIF(DupKey=0,Col_3,Col3+TRIM$(STR$(ID))) as Column_3 FROM
(SELECT AA.*,
AA.ID - BB.ID as DupKey
FROM
(
   SELECT A.*,B.ID,
   IIF(DupCOunt=1,B.Col_3,B.Col_3+"Dup") AS Col3
   FROM
   (
      SELECT Col_1, Col_2, Col_3, Count(1) AS DupCount
      FROM mytable GROUP BY Col_1, Col_2, Col_3
   ) AS A, mytable AS B
   WHERE A.Col_1=B.Col_1 AND A.Col_2=B.Col_2 AND A.Col_3=B.Col_3
) as AA,
(
   SELECT A.Col_1,A.Col_2,A.Col_3,A.DupCount,MIN(B.ID) as ID
   FROM
   (
      SELECT Col_1, Col_2, Col_3, Count(1) AS DupCount
      FROM mytable GROUP BY Col_1, Col_2, Col_3
   ) AS A, mytable AS B
   WHERE A.Col_1=B.Col_1 AND A.Col_2=B.Col_2 AND A.Col_3=B.Col_3
   GROUP BY A.Col_1,A.Col_2,A.Col_3,A.DupCount
) as BB
WHERE AA.Col_1=BB.Col_1 AND AA.Col_2=BB.Col_2 AND AA.Col_3=BB.Col_3
) as AAA;

Give it a Try !!!

It will not make consecutive Dup numbers but it will assign unique Dup Numbers. This is too hard to get consecutive numbers without VB Code or another table


Getting Msg 15151, Cannot alter the user 'xxxx', because it does not exist or you do not have permission I have the following script and I keep getting errors when it is executed.

USE [master]
GO

If Not EXISTS (Select loginname from [master].[dbo].[syslogins]
    Where name = 'xxxx' and dbname = 'xxxx-xxxx')
BEGIN
    CREATE LOGIN [xxxx] WITH PASSWORD=N'xxxxx', DEFAULT_DATABASE=[xxxx-xxxx], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
    ALTER LOGIN [xxxx] ENABLE
END
GO

USE [xxxx-xxxx]
ALTER USER [xxxx] WITH DEFAULT_SCHEMA=[db_datareader]
ALTER ROLE [db_datareader] ADD MEMBER [xxxx]
ALTER ROLE [db_datawriter] ADD MEMBER [xxxx]
ALTER ROLE [db_owner] ADD MEMBER [xxxx]
GO

I have tried several permutations of this script with no success. It does create the login but won't let me alter the "user" or "role" properties. When I can get it to work with no errors, it does not make the changes to the user profile.

You are missing create user before you alter it.

Login - gets you entry to the server
Database User - gets you entry to a particular database

so in your script, add create user from login ...

USE [xxxx-xxxx]
create user [xxxx] from login [xxxx] <--- this is needed !
ALTER USER [xxxx] WITH DEFAULT_SCHEMA=[db_datareader]
ALTER ROLE [db_datareader] ADD MEMBER [xxxx]
ALTER ROLE [db_datawriter] ADD MEMBER [xxxx]
ALTER ROLE [db_owner] ADD MEMBER [xxxx]
GO

few things :

sys.syslogins is deprecated.
dbatools has cmdlets for Login and user management which you can leverage for automation.


sql server session revert Sql server log file stores the every log in information.is it possible to revert the query operation which we doing through query editor or by application based on the session we are logged in?

Thanks in Advance

If you are using transactions, you can rollback your transaction.

If you are looking to restore your database, the answer is no. Providing that you are in Full Recovery model and have log backups, you can restore to a point in time, such as LSN, Time or Marked Transaction, but you cannot roll forward your restore on a session by session basis as this could leave the database in an inconsistent state.

Imagine your session deleted some rows from a table...What if another session had created those rows and you did not roll forward the other session? It just wouldnt work.

You could consider using Database Snapshots moving forward. This will give you a point-in-time copy of the database which you can use to "undo" data modifications. For example if your session deleted some rows, you could do an INSERT into your database, SELECT from snapshot


Why can't we have multiple cascade paths? You can see that many questions have being asked about multiple cascade paths. For example:

https://stackoverflow.com/questions/851625/foreign-key-constraint-may-cause-cycles-or-multiple-cascade-paths

https://stackoverflow.com/questions/6065501/multiple-cascade-delete-path-in-many-many-relationship-ef-4-1

https://stackoverflow.com/questions/27613117/introducing-foreign-key-constraint-may-cause-cycles-or-multiple-cascade-paths-s

However, from what I see and understand, it's totally OK that you want to delete a child record on many and not just one condition of the deletion of the  related master records.

While in a question it's said that SQL Server tries to be safe by preventing this from happening, I really don't get it that what might go wrong if we have multiple cascade paths, and what problems it prevents to make it safe?

I hope someone can explain that to me, in plain and simple words, preferably using examples of what might go wrong in case of multiple cascade paths.

This question "what can go wrong" can only be answered by the developers at Microsoft or Sybase. When there are multiple cascade paths to the same record, it's possible that the code might attempt to delete it multiple times. If it isn't constructed to deal with the possibility that the record to be deleted has already been deleted, it might throw an error. Apparently this problem actually exists (or is thought to exist!), so instead of fixing it, the implementation avoids it by preventing the definition of duplicate cascade paths. It's clearly a short-cut, because other DBMS have no problem with this situation.


Extract Date Portion Only From DateTime Field In SQL Server 2008 I use this to take out only the date piece Cast(field as date).  For example, if I use the below garbage DDL

  Create Table #Bbb
  (
    id int
    ,datetime1 datetime
  )

  Insert Into #Bbb Values
  ('1', '2016-03-01 17:35:19.000')
  ,('2','2016-03-01 18:44:15.000')
  ,('3','2016-03-05 18:58:05.000')
  ,('4','2016-03-05 19:52:27.000')

I run this query it will only give me the 2 rows where the date is equal to 03/01/2016:

Select *
FROM #Bbb
WHERE CAST(datetime1 As Date) = '2016-03-01'

Now the question being, how would I do the same in a SQL Server 2000 instance, since Cast(datetime1 As Date) does not exist?

You can check for the daily boundaries i.e. between the begining of the day at 00:00 and the begining of the next day at 00:00.

Query:

DECLARE @date datetime = '20160301'

SELECT *
FROM #Bbb
WHERE datetime1 >= @date AND datetime1 < DATEADD(day, 1, @date)
;

Or this WHERE clause for dates on a given day or later:

WHERE datetime1 >= @date

Output:

id  | datetime1
1   | 2016-03-01 17:35:19.000
2   | 2016-03-01 18:44:15.000

You can also remove the TIME part (set it to 00:00:00.000) with this query:

DECLARE @date datetime = getdate()
SELECT @date, DATEADD(day, DATEDIFF(day, 0, @date), 0);

However using functions will limit index usage.

Ouput:

datetime                | date
2016-03-09 14:40:25.080 | 2016-03-09 00:00:00.000


Which Index for WHERE UPPER("my_col"::text) LIKE "FOO" search on bytea I use the unusual bytea column type in a PostgreSQL DB because the data in this column is like a blob. I mostly contains text, but sometimes it contains binary data.

Which index would fit if the query like this gets done?

WHERE (UPPER("my_bytea_col"::text) LIKE '%FOO%'

You could create an immutable function which changes the \000 null characters (plus any other characters you consider to be binary) to spaces, then create a trigram index on that function over the column. There might be such a function built in, but if so I don't know of it.


Syspolicy_purge_history connection issue I am having an issue with the syspolicy_purge_history SQL Agent job on a 2008 SQL Server system.  The server is using a non default port number and is in a cluster.  Before it was put in a cluster, it was a stand alone box and the job ran with no issues with the non default port number.  It is failing at step 3, which is

(Get-Item SQLSERVER:\SQLPolicy\VirtualServer\DEFAULT).EraseSystemHealthPhantomRecords()

I have already addressed an issue of using the computer node name instead of the virtual server name as we have other servers in clusters.

The only difference with this setup is that it is not using the default port of 1433.  The server is already  set to receive remote connections.

Here is the error.

The job script encountered the following errors.
These errors did not stop the script:
    A job step received an error at line 1 in a PowerShell script.
    The corresponding line is '(Get-Item SQLSERVER:\SQLPolicy\VirtualServer\DEFAULT).EraseSystemHealthPhantomRecords()'.
    Correct the script and reschedule the job.
    The error information returned by PowerShell is: 'SQL Server PowerShell provider error: Could not connect to 'VirtualServer\DEFAULT'. [Failed to connect to server VirtualServer. --> A network-related or instance-specific error occurred while establishing a connection to SQL Server.
    The server was not found or was not accessible.
    Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)]

You will need to put the port number in there.  When using the default instance the SQL Browser is not contacted in order to get the port number to use to connect.  Because of this you have to specify the port number when connecting otherwise the OLE/ODBC driver doesn't know what TCP port number to connect to.

Please clarify how you took a standalone server and made it a cluster.  That isn't supported unless you uninstalled SQL and reinstalled it.


Unexpected result of case expression The following case expression results in a,

select case 0 when true  then 'c' when 'true1222' then 'a' else 'b' end;

Anyone knows why? How could 0 be equal to true1222?

the problem is - use 0 as CASE, 0 == false
so query compare:

TRUE - not, go forward
'true1222' - FALSE == 0, OK

try to change query:

SELECT
    CASE 3
WHEN TRUE THEN
    'c'
WHEN 'true1222' THEN
    'a'
ELSE
    'b'
END;

and it return expected - 'b'


Postgres full text search with unaccent and inflection (conjugation, etc.) I want to be able to search unaccented phrases in an inflected (Polish) language in Postgres.

Say, if a document contains robiłem, the lexeme should be robić (the infinivite). Its forms are robię, robił, robiła and so on. I want to be able to find it, for example, with a phrase robie which is unaccented robię.

What I did is I started out with a perfectly well working polish text search config

CREATE TEXT SEARCH DICTIONARY polish_ispell (
TEMPLATE = pg_catalog.ispell,
dictfile = 'polish', afffile = 'polish', stopwords = 'polish' );

Then I tried to extend it to include the unaccent.

create extension unaccent;
create text search configuration polish_unaccented (copy = polish);
ALTER TEXT SEARCH CONFIGURATION polish_unaccented   ALTER MAPPING FOR hword,
hword_part, word WITH unaccen, polish_ispell, simple, ;

Sadly, lexems are not created correctly with this config:

select to_tsvector('polish_unaccented' ,'robił');

'robil':1

The lexem should be of course:

'robić':1

So the below cant return true (and that's what I need I think):

select to_tsvector('polish_unaccented','robić') @@ to_tsquery('polish_unaccented','robie');

I've googled but did not find any documents showing how to really configure Postgres for my case. The docs only show the lame 'Hôtels' example, which is not a 'lexemed' word.

Cheers

AFAIK, you cannot do what you want with current PostgreSQL full text configurations (dictionaries and parsers/lexers), although some workarounds might do something close to the trick.

I don't know Polish, but I've had similar problems with Spanish (that also has conjugations, etc.), and with the fact that people has got completely accostumed to the fact that Google is able to ignore accents, and they just frequently ignore them as well.

Dictionaries

You can have several dictionaries for PostgreSQL, which can do different things, but basically simplify in some fashion your texts. What you want to do if you want to convert words to its lexemes is to use an ISpell dictionary:

  [...] which can normalize many different linguistic forms of a word into the same lexeme. For example, an English Ispell dictionary can match all declensions and conjugations of the search term bank, e.g., banking, banked, banks, banks', and bank's.

However, this dictionary (at least in Spanish) will only recognize properly accented words, or it won't be able to find out to which lexeme a certain (possibly accented) word corresponds to. This is because the entries in the ISpell dictionaries, such as the Polish ISpell dictionary are written with all the proper accents (or diacriticals) [as it should].

The ISpell Polsih dictionary is composed of two files, one called pl_PL.dic (encoded as ISO-8859-2, as far as I've been able to guess) and one called pl_PL.aff. The first one contains lexemes + rules that apply to them, the second one contains the meanings of those rules. The ISpell software interprets those files to figure out how to transform words into its lexemes [and also how to check whether a spelling is correct or not].

The entries on the .dic file look like:

abecadło/UV
abecadłowy/bxXyY
[...]
Abisyńczyk/NOqsT
abisyński/XxYbyc

The .aff file gives rules for the meanings of the "U" and "V" and all the other letters than follow the / sign. Some if this rules (which I am far from knowing) will thell the software how suffixes or prefixes work for the word abecadło. For instance:

SFX U   ło        le        [^astz]ło

As there is no word like abecadlo or abisynski in this dictionary, if you enter those texts into your search, the dictionary will not return any lexeme.

Possible workaround: Manipulate the dictionary file and duplicate all lines with accented characters with the unaccented equivalent. You probably would need to do something similar with the .aff part of the dictionary.

Probably, you would also need to use a Synonym dictionary to make the accented and unaccented versions of all words have the same meaning.

This is a brute force approach, and, in practice you'd be inventing a "new version of the Polish language, where accented letters are equivalent to their unaccented counterparts". [Don't tell the makers of dictionaries who made them to make sure people would spell correctly ;-)].

I think this approach does have very many risks. I know I wouldn't do it in either Spanish or Catalan, because the presence or absence of a diacritical mark can radically change the meaning of a word ("año" doesn't have much to do with "ano", in Spanish; and considering them synonyms is extremely delicate).

You'll have to evaluate whether this applies to Polish or not.

Alternative: you can just use a combination of a Simple Dictionary and the "filtering" unnacent module. You won't get lexemes, and the transformations this combination is able to do are not so sophisticated... but you'll get the same result when you search for abecadło or abecadlo.

In my case, I ended up settling for this solution.

Second alternative: If you need a text search which has the capability to ignore accents, allow for small misspellings and have a lot of sophisticated possibilities, consider using a solution out of the database, such as Apache Solr. It's obviously a very different approach, and you need some process to make it synchronized with the database.


MySQL ORDER BY id, but keep identical names next to each other Suppose I have the following table:

id  name
---------
1   lion
2   tiger
3   lion
4   bear
5   tiger
6   bear

I would like to be able to return results with the most recent id column first, but keep all of the names together, like this:

id  name
---------
6   bear
4   bear
5   tiger
2   tiger
3   lion
1   lion

Is there a simple ORDER BY clause that can do this? Or do I have to do a UNION of some type?

You need to create an ordering based on the maximum id for name. Here's one attempt:

select id, name
from animals x
order by (select max(id) from animals y where x.name = y.name) desc
    , id desc;

It might be easier to understand if the derived column is explicit, and then order by that:

select id, name
from (
    select id, name
         , (select max(id)
            from animals y
            where x.name = y.name
           ) as max_id
    from animals x
) as t
order by max_id desc, id desc;

You could also use a join to a derived table, instead of the correated subquery:

select x.id, x.name
from animals x
    join
       (select name, max(id) as max_id
        from animals
        group by name
       ) y
    on x.name = y.name
order by y.max_id desc, x.id desc;


What are the different ways to restore a database? From the most inoffensive of the cases, to the worst of the scenarios they know. What are the different ways to restore or recover a MySQL database? I refer to the use of full backups and incremental backups, among other ways to do it. I do not know if it can be done through ibdata and iblogfiles, is what I need to know ... Thanks in advance.

Backup scenarios are designed around the risk profile. These include resilience and time to restore as the most critical factors.

The backup needs to account for the potential risks to be encountered including physical destruction, a malicious insider, a technological competent criminal, or a sever database flaw.

Backups will include offsite/offline backup based on risk assessment.

Backups can include a bunch of logical (SQL dumps), and binary backups (copy of the entire datadir based on stopped server or xtrabackup), binary logs for point in time recovery, and can frequently involve a replication slave to assist with such mechanisms.


Mysql Innodb as Key value store, performance I read about NoSQL databases using Key value pairs (KVP) tables. So i wanted to find out if KVP tables would be faster than conventional relational tables (with multiple columns) in mysql with innodb engine.

So I created a database table with an auto increment primary key and 12 other columns (firstname,lastname etc). All string values except the primary key. I filled it up with 500.000 records (random strings).

Now i created another database and a table but only with 2 columns: Key and value. Now i converted all the records of the conventional database to the KVP principle so row(1. john) becomes 000001_firstname = john throug queries like INSERT INTO kvp (SELECT CONCAT(LPAD(id, 8, "0"),"_firstname"),first_name FROM datatable);. This resulted in a table with 6 million records.

Now i disabled query caching and other caching and i ran the following query on the conventional table: "SELECT id FROM datatable WHERE first_name = 'Ehm'"
and the following query on the kvp table: "SELECT id FROM datatable WHERE key like '%firstname' and value = 'Ehm'"

I tested the speed of this query doing 100 iterations which resulted in 0.4 seconds for the kvp table but 20 seconds for the conventional table (with a flush tables after each query).

I used a php-script for testing:

public function benchmark($query,$dbName){
    $db = new mysqli("localhost", "root", null, $dbName);
    $db->set_charset("utf8");
    $time = microtime(true);
    for($i=0; $i<$this->iterations; $i++){
        mysqli_query($db,$query);
        mysqli_query($db,'flush tables;');
    }
    $duration = microtime(true)-$time;
    $db->close();
    return $duration;
}

how is this speed difference possible?

i initialized both tables the same way
disabled query caching
i did not put an index on value column of kvp table.

same result with the query: "SELECT id FROM datatable WHERE first_name like '%ad%'"

i tried to find if there was a fault in my testingscript but i could not find anny. Furthermor i tried to find literature explaining this but i could not find anny. I use mysql 5.6 and somehwere i read mysql uses memcache since 5.6, maybe this has influenced the result?

This is the result i got:

what could have influenced this result?

Joel, there is a lot of unknowns. If you post your my.cnf, queries, table definitions and queries, EXPLAIN plan output, then you might get more precise answers.

You say "disabled query caching and other caching" what 'other caching' are you referring to? What steps did you take to disable QC? Did you restart your MySQL server between tests to flatten any buffers? If no and the KVP table was smaller and fit into RAM then InnoDB might have been able to serve queries fitting a certain criteria from data in the innodb buffer pool.

You also refer to memcache. With MySQL 5.6 there is a memcache api. this means you can use memcache calls to access data stored in innodb. This will not be incidentally speeding up your queries. There is also another method of bypassing the SQL layer within MySQL called HandlerSocket. These are a couple of subjects that I would point you at should you continue to desire this type of access to your data with the added bonus of having MySQL persist the data to disk and also be crash safe through InnoDB's crash recovery process.


Design / Normalization of a Database I’ve got a question concering the design of a SQLite database. I’m building a database for analyzing purposes that constists of works of literature and metadata concering those entities.
The thing I’m asking is wether it makes sense to split my table into several tables, e.g. buliding a relational database. Right now I’ve got one main table in which my entites are stored. The columns are a unique title, one for the author (who may quite well not be qunique in the database), a publication year, publisher (also not unique) and a lot of specialized fields containing tags connected with the work. Those tags are organized in three categories and each entity can be connected with multiple tags (therefore Tag A 1 and so on). One entity would look like this:

Title | Author | Year | Publisher | Tag A 1 | Tag A 2 | Tag A 3 | Tag B 1 | Tag B 1 | Tag B 3 | Tag C 1 | ...

Is it useful to build a table with authors, one for publishers and than a table for each category of tags and than link those back with foreign keys? What would be the advantages besides no more doubling of data? I’m going to query the database for things like "Titles with more than 2 manifestations of Tag A from 1980 to 1990" or "Authors using Tags A and B".

All my columns are filled with atomic values. If I’m correct I’m looking at brining my database into the first normal form by trying to "eliminate repeating groups in individual tables" – is that correct?

Thanks for your help!

Is really up to you if you want to normalize or not, depends largely on the requirements of your implementation and how are you displaying (or not) the data...

I think an alternative approach to the dataset you present would benefit a lot of a structure such as:

      ----------------
      |   Authors    |
      ----------------
      | AuthorId     |<-.
      | Name         |  |
      ----------------  |
                        |
      ----------------  |
      |  Publishers  |  |
      ----------------  |
      | PublisherId  |<-+-.
      | Name         |  | |
      | IsActive     |  | |
      ----------------  | |
                        | |
      ----------------  | |
      |    Tags      |  | |
      ----------------  | |
  .-->|  TagId       |  | |
 | .--|  CategoryId  |  | |
 | |  |  TagValue    |  | |
 | |  ----------------  | |
 | |                    | |
 | |  ----------------  | |
 | |  |  Categories  |  | |
 | |  ----------------  | |
 | `->| CategoryId   |  | |
 |    | CategoryValue|  | |
 |    ----------------  | |
 |                      | |
 |    ----------------  | |
 |    |    Titles    |  | |
 |    ----------------  | |
 | .->| TitleId      |  | |
 | |  | AuthorId     |--` |
 | |  | PublisherId  |----`
 | |  | TitleName    |
 | |  | Year         |
 | |  ----------------
 | |
 | |  ----------------
 | |  |   TitleTags  |
 | |  ----------------
 | `--| TitleId      |
 `----| TagId        |
      ----------------

A junction table will store any amount of tags per title and an additional [tag]Category table could group this tags so you'll be able to query for entire groups instead of 'per tag' search.

Note that by, also, separating the Publishers you could add more fields (like IsActive in the example), so then you can have more granular queries, be more specific on what your looking for.


Create dynamic folder using SQL*Plus I need to create directory in Linux with date stamp from SQL*Plus. Is there a command that can be run in *.sql file to create a directory with current date?

The SQL*Plus command you're looking for is host. It allows you to run a Linux shell command, so

host mkdir $(date +%F);

Adjust the date format as necessary (man date will help you with that).


Deleting files from within SQL Server I've been using xp_delete_file to delete old backup files but I now an a need to delete files that are part of an application (as part of GDPR). As xp_delete_file only will only let you delete log or backup files, I thought I'd write my own in C#.

The trouble I'm finding now is extended stored procedures can only be rolled out to the master database. I'd rather contain this new stored procedure in my own user db so I can distribute via source control etc.

Does anyone know a way round this without using xp_cmdshell?

It could be done in PowerShell. I could even write the entire thing in C#. It however be really handy to be able to do it in T-SQL as well.

I am open to using a CLR stored procedure that invokes C# code. I was thinking of changing the stored procedure to a function if I can't deploy a stored procedure.

Extended Stored Procedures (XPs) have been deprecated as of the release of SQL Server 2005 and new projects should not be using that API.

This can be done easily via SQLCLR, and yes, you can return a string (or most datatypes) from a C# method. The only construct restricted to returning an INT is a Stored Procedure, and that holds true for both T-SQL and SQLCLR Stored Procedures.

You would use something along the lines of:

using System;
using System.IO;

public class Stuff
{
  [SqlFunction(IsPrecise=true)]
  public static SqlString DeleteFile ([SqlFacet(MaxSize=500)] SqlString FilePath)
  {
    string _ReturnMessage = String.Empty;

    try
    {
      File.Delete(FilePath.Value);

      _ReturnMessage = "some text here";
    }
    catch (Exception _Error)
    {
      _ReturnMessage = _Error.Message;
    }
  }

  return new SqlString(_ReturnMessage);
}

When you create the T-SQL wrapper object, be sure to specify the RETURNS NULL ON NULL INPUT option so that it won't even both executing if a NULL is passed in (you should probably add additional validation to the method, such as returning if an empty string is passed in, etc):

CREATE FUNCTION dbo.DeleteFile
(
  @FilePath NVARCHAR(500)
)
WITH RETURNS NULL ON NULL INPUT
EXTERNAL NAME.... ;

Since the Assembly will need to be marked as PERMISSION_SET = EXTERNAL_ACCESS, please do not set the Database to TRUSTWORTHY ON. Instead you should sign the Assembly, create the Asymmetric Key or Certificate in [master], create a Login from that Asymmetric Key or Certificate, and then grant that Login the EXTERNAL ACCESS ASSEMBLY permission (if using SQL Server 2005 - 2016) or the UNSAFE ASSEMBLY permission (starting with SQL Server 2017).

For detailed instructions for doing this via a build process, you can try either of the two techniques I describe in the following blog posts (both work fully with SSDT or independently):

SQLCLR vs. SQL Server 2017, Part 2: “CLR strict security” – Solution 1
SQLCLR vs. SQL Server 2017, Part 3: “CLR strict security” – Solution 2

Also consider supporting my Asymmetric Key suggestion which would eliminate most of the hassle of publishing signed Assemblies:

Allow Asymmetric Key to be created from binary hex bytes string just like CREATE CERTIFICATE

OR, if you would rather not deal with any of this, there is a File_Delete function in the SQL# SQLCLR library (that I wrote) that does this. It even returns a string, which is the error message if an error occurs, else is just an empty string. No way to have it pass back a custom string on success, though. Also, please note that while there is a Free version, the File System functions are only available in the Full version. But the security aspect would all be handled cleanly and properly, and there is even a T-SQL Stored Procedure, that exists in the same DB as the SQLCLR code, that will set up the Asymmetric Key and associated Login in [master] (which could help with deployments to new systems by not requiring a full install, as long as this Stored Procedure was deployed and executed prior to deploying any Assemblies (assuming an environment of SQL Server 2017)).

UPDATE

SQL Server 2019 (which was not available at the time I posted this answer) introduces a new, undocumented system stored procedure, xp_delete_files, that is a bit more flexible than xp_delete_file. For more info, please see my answer here (also on DBA.SE):

How is the new system stored procedure sys.xp_delete_files different from sys.xp_delete_file?


GROUP BY Returning Different Totals I have two very similar queries that are returning different totals. The first query, where the total [of subscription_payment.price] is calculated with PHP (and verified in Excel), is the same query that the second query is based off of.

First Query:

SELECT *,
       subscriptions_new.id AS subscription_id,
       plans_new.name AS plan_name,
       plans_new.guideid AS guideid,
       subscription_payment.date AS date,
       subscription_payment.renewal AS renewal,
       subscription_payment.price AS price,
       subscription_payment.price AS renewal_price
FROM transactions_new
JOIN accounts ON accounts.id = transactions_new.userid
JOIN subscriptions_new ON FIND_IN_SET(subscriptions_new.id, transactions_new.subscription_ids)
JOIN plans_pricing ON subscriptions_new.pricing_id = plans_pricing.id
JOIN subscription_payment ON subscription_payment.subscription_id = subscriptions_new.id
JOIN plans_new ON plans_new.id = plans_pricing.plan_id
WHERE
  subscription_payment.date >= 1417410000
  AND subscription_payment.date <= 1418187540
  AND subscription_payment.deleted != 1
GROUP BY subscriptions_new.id
ORDER BY plan_code DESC

Second Query:

SELECT SUM(subscription_payment.price) AS total,
       COUNT(*) AS qty,
       plan_code AS plan_code
FROM transactions_new
JOIN accounts ON accounts.id = transactions_new.userid
JOIN subscriptions_new ON FIND_IN_SET(subscriptions_new.id, transactions_new.subscription_ids)
JOIN plans_pricing ON subscriptions_new.pricing_id = plans_pricing.id
JOIN subscription_payment ON subscription_payment.subscription_id = subscriptions_new.id
JOIN plans_new ON plans_new.id = plans_pricing.plan_id
WHERE
  subscription_payment.date >= 1417410000
  AND subscription_payment.date <= 1418187540
  AND subscription_payment.deleted != 1
GROUP BY plan_code
ORDER BY plan_code DESC

In the second query,total is not matching what has been calculated from adding all of the records in the first query.

Any help is appreciated. Also note that the GROUP BY makes no difference when calculating the totals (so it's okay for them to be different - that's not causing the issue).

I followed @ypercube's suggestion and just rewrote both queries - turns out there wasn't a relationship in the database that was needed to tie two tables together, and when there were multiple relationships on a set of columns MySQL did what @JNK said and was non-deterministic and grouped inconsistently.

If you run into something like this, check your table relationships, especially if there are multiple possibilities with what you're joining on and you only need to be joining on one.

Thanks for the help everyone.


Cassandra: memtable_offheap_space_in_mb and memtable_cleanup_threshold I use C* v2.1.2 do write only workload (into one column family) and experiment with offheap-objects memtable space.

I set parameters to:

memtable_offheap_space_in_mb = 51200  # 50Gb
memtable_cleanup_threshold = 0.99

and expect that flush will not be triggered until available memtable offheap space reaches ~50Gb. But flushes are triggered
before that limit. System monitor shows that in used only ~16Gb at that moment (linux+jvm+heap+...).

Why such thing is happened?

Memtable flush happens in 3 ways1. When memtable_offheap_space_in_mb size is exceeded.2. When commitlog_total_space_in_mb size is exceeded.3.When forcefully Nodetool flush commend is executed. In your case I'm guess for second reason that flush happened. For more you can check this. http://academy.datastax.com/courses/understanding-cassandra-write-path/understanding-memtables-and-commitlog


Could I use a copy of the database directory instead of using initdb? I have an offline Windows software that is installed locally on each user's computer. Each installation uses its own standalone PostgreSQL database. To create the database on each user's computer, the software uses the initdb command and then creates the tables and keys using normal SQL.

I want to avoid using the initdb command on each user's computer because sometimes this command fails (especially when the user uninstalls the Microsoft Visual C ++ 2010 x86 Redistributable).

I was thinking of embedding a copy of the initial database directory in the installer, so I did not have to use initdb. Would this work? Is there a better alternative?

Note: All users use the exact same version of PostgreSQL and the same database structure. However, they use different versions of Windows.

You can do so - a given initdb will produce a datadir compatible with builds of the same postgres.

But.

If initdb fails, the rest of postgres probably will too. So you won't really be solving anything. You should instead bundle the MSVC redist in your installer.

BTW, if you install PostgreSQL bundled in an app, please use a non-default port, do NOT use the default port 5432. Thanks!


Issue of structure of query does not match function result type "structure of query does not match function result type" this isssue occurred when i try to execute the this function.

CREATE OR REPLACE FUNCTION historicalavg01() RETURNS SETOF text AS
$$
DECLARE starttime timestamp;
DECLARE endtime timestamp;
DECLARE total_hour_avg DOUBLE PRECISION;
l_row RECORD;
BEGIN
CREATE TEMP TABLE historical_temp(Histroical DOUBLE PRECISION,time timestamp);
total_hour_avg := 0;
FOR b IN 0 .. 23 LOOP
    FOR a IN 0 .. 27 LOOP

        starttime := (CURRENT_DATE - (a  * INTERVAL '1 day')) + (b * INTERVAL '1 hour');

        endtime := (CURRENT_DATE - (a  * INTERVAL '1 day')) + ((b + 1) * INTERVAL '1 hour');

        total_hour_avg := total_hour_avg + (SELECT Avg("activepower") AS "Avg" FROM sca_meter WHERE "time" between starttime AND endtime);

        IF(a = 27) THEN
            INSERT INTO historical_temp SELECT AVG(total_hour_avg) AS "Histroical Avg", CURRENT_DATE - ((b + 1) * interval '23 hour') AS "time";
        END IF;

    END LOOP;
END LOOP;

RETURN QUERY SELECT * FROM historical_temp;

END$$ LANGUAGE plpgsql;
SELECT * FROM historicalavg01();

Please give the solution for it. thanks in adavance.

To return a TABLE add the table definition:

CREATE OR REPLACE FUNCTION historicalavg01()
RETURNS TABLE
(
    Hist double precision,
    Tm   timestamp
) AS
$$
DECLARE starttime timestamp;
DECLARE endtime timestamp;
DECLARE total_hour_avg DOUBLE PRECISION;
l_row RECORD;
BEGIN
CREATE TEMP TABLE historical_temp(Histroical DOUBLE PRECISION,time timestamp);
total_hour_avg := 0;
FOR b IN 0 .. 23 LOOP
    FOR a IN 0 .. 27 LOOP

        starttime := (CURRENT_DATE - (a  * INTERVAL '1 day')) + (b * INTERVAL '1 hour');

        endtime := (CURRENT_DATE - (a  * INTERVAL '1 day')) + ((b + 1) * INTERVAL '1 hour');

        --total_hour_avg := total_hour_avg + (SELECT Avg("activepower") AS "Avg" FROM sca_meter WHERE "time" between starttime AND endtime);
        total_hour_avg := b * 0.5;

        IF(a = 27) THEN
            INSERT INTO historical_temp SELECT AVG(total_hour_avg) AS "Histroical Avg", CURRENT_DATE - ((b + 1) * interval '23 hour') AS "time";
        END IF;

    END LOOP;
END LOOP;

RETURN QUERY SELECT * FROM historical_temp;

END$$ LANGUAGE plpgsql;
SELECT * FROM historicalavg01();

hist | tm
:--- | :------------------
0    | 2020-02-05 01:00:00
0.5  | 2020-02-04 02:00:00
1    | 2020-02-03 03:00:00
1.5  | 2020-02-02 04:00:00
2    | 2020-02-01 05:00:00
2.5  | 2020-01-31 06:00:00
3    | 2020-01-30 07:00:00
3.5  | 2020-01-29 08:00:00
4    | 2020-01-28 09:00:00
4.5  | 2020-01-27 10:00:00
5    | 2020-01-26 11:00:00
5.5  | 2020-01-25 12:00:00
6    | 2020-01-24 13:00:00
6.5  | 2020-01-23 14:00:00
7    | 2020-01-22 15:00:00
7.5  | 2020-01-21 16:00:00
8    | 2020-01-20 17:00:00
8.5  | 2020-01-19 18:00:00
9    | 2020-01-18 19:00:00
9.5  | 2020-01-17 20:00:00
10   | 2020-01-16 21:00:00
10.5 | 2020-01-15 22:00:00
11   | 2020-01-14 23:00:00
11.5 | 2020-01-14 00:00:00

db<>fiddle here

Have a look at this tutorial about it.


Grant table privileges to a user connecting from any host I have a user account - let's call it 'wordpress' - that I need to allow to access a few catalog tables in another e-commerce database on the same server. I've configured the user with three host masks it's allowed to connect from: 'localhost', the IP address of the web server, and the hostname of the web server. No problems there.

The 'wordpress' user also has full access to its own database, granted via the Schema Privileges section in MySQL Workbench. Here, it shows the host is '%', which is what I want, since I don't want to manage three duplicate sets of privileges for the same user. If I look in mysql.db, I see these privileges, with '%' in the Host column.

So now I want to grant SELECT permission on a handful of tables in another database - let's call it 'store'. So I try this:

GRANT SELECT ON store.catalog TO 'wordpress'@'%';

And I get 'Can't find any matching row in the user table', for the obvious reason that '%' isn't a host mask I've explicitly allowed a connection from for this particular user. So what's the proper syntax to grant a table privilege to a user from any of its allowed host masks? How is MySQL Workbench getting away with it for schema privileges? I don't have to manually insert rows into mysql.tables_priv, do I?

UPDATE: To clarify, here's what the current user/grant tables look like. I've anonymized some names, obviously. Note that the host in the schema privilege table is '%', but there aren't any users with that host. How do I get MySQL to let me do that with schema object grants? Preferably without mucking around directly in mysql.tables_priv, but I'll do it if it comes down to it.

mysql> SELECT user, host FROM mysql.user WHERE user = 'wordpress';
+-----------+-----------+
| user      | host      |
+-----------+-----------+
| wordpress | 10.0.0.22 |
| wordpress | webserver |
| wordpress | localhost |
+-----------+-----------+
3 rows in set (0.00 sec)

mysql> SELECT user, host, db, select_priv FROM mysql.db WHERE User = 'wordpress';
+-----------+------+----------------+-------------+
| user      | host | db             | select_priv |
+-----------+------+----------------+-------------+
| wordpress | %    | wordpress      | Y           |
| wordpress | %    | wordpress_test | Y           |
+-----------+------+----------------+-------------+
2 rows in set (0.00 sec)

mysql> SHOW GRANTS FOR 'wordpress'@'localhost';
+---------------------------------------------------------------------------+
| Grants for wordpress@localhost                                            |
+---------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wordpress'@'localhost' IDENTIFIED BY PASSWORD '--' |
+---------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS FOR 'wordpress'@'%';
ERROR 1141 (42000): There is no such grant defined for user 'wordpress' on host '%'

Is MySQL Workbench doing something horribly undocumented with schema/object privileges? Just for kicks, I granted some table privileges to one of the specific user@host combinations, then updated mysql.tables_priv to change the host to '%'. After running FLUSH PRIVILEGES, it worked perfectly. Weird.

When you execute GRANT SELECT ON store.catalog TO 'wordpress'@'%';, mysqld wants to insert a row into the grant table mysql.tables_priv. Here is mysql.tables_priv:

mysql> show create table mysql.tables_priv\G
*************************** 1. row ***************************
       Table: tables_priv
Create Table: CREATE TABLE `tables_priv` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Grantor` char(77) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `Table_priv` set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') CHARACTER SET utf8 NOT NULL DEFAULT '',
  `Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '',
  PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`),
  KEY `Grantor` (`Grantor`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Table privileges'
1 row in set (0.00 sec)

mysql>

Since you want to insert a row into mysql.table_priv where user='wordpress' and host='%', there has to exist a row in mysql.user where user='wordpress' and host='%'.

You also mentioned that you are using MySQL Workbench. You must be using 'root'@'localhost'. That would usually have all rights and a password.

If you want to just allow anonymous SELECT against that table, first run this:

GRANT USAGE ON *.* TO 'wordpress'@'%';

This will place wordpress@'%' into mysql.user. Afterwards, GRANT SELECT ON store.catalog TO 'wordpress'@'%' should run just fine.

You will have to see what other wordpress entries are in mysql.user. This should show what SQL GRANT commands you need:

SELECT CONCAT('GRANT SELECT ON store.catalog TO ',userhost,';') GrantCommand
FROM
(
    SELECT CONCAT('''',user,'''@''',host,'''') userhost
    FROM mysql.user WHERE user='wordpress'
) A;


MsSql express export from local pc to RDS, how to transfer NDF/LDF files? First time mssql admin here. Never worked with mssql before I might asked question improperly.
Couldn't find any posts mentioning ndf/ldf files import to AWS RDS mssql database.
Is data from these files included in mssql backup or we need to import NDF/LDF files separately?
We are using latest Express db.

convention is that ndf is used for the 2nd & subsequent data file & ldf for the transaction log file - so YES these are included in your database backup. Also worth noting that these are just conventions and you can give the files any extension you like.


Normalization with two potential primary keys I have the following STUDENT relation in first normal form, where id has been identified as the primary key (email here is also unique for all tuples):

STUDENT(id, email, first_name, last_name)

I want to normalize this relation/table such that it is in third normal form (3NF).

The dependencies that I've been able to identify from the above STUDENT relation/table are the following

Full:

{ id } → { email }

Transitive:

{ email } → { first_name, last_name }

Here's the part where I'm confused. I've identified email as a transitive dependency as it can be identified by the primary key (id), and the attribute/column email itself can identify and determine the first_name and last_name.

...if I was to continue with this idea, I would eventually get to a 3NF which looks like so:

STUDENT(id, email)
STUDENT_INFO(email, first_name, last_name)

However, the STUDENT relation/table here seems very redundant, as all it does is point to another relation/table. Is my normalization process here correct? Or did I get my dependencies wrong? It seems that I will always run into this problem whenever the initial relation/table has two candidate keys which both could either be the primary key (id or email both could uniquely identify each row).

Here is where I went wrong:

The important thing about transitive dependencies are these two key factors:

If B depends on A, and C depends on B, then C also depends on A (A → B → C)
B is not (or part of) a candidate key

In my example, I identified the following as a transitive dependency:

{ email } → { first_name, last_name }

However, this is not the case, as email is a candidate key, and so instead, if we use id as the primary key, we have a full dependency:

{ id } → { email, first_name, last_name }


wildcard expansion using sql I've a table named permissions.

CREATE TABLE `permissions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `role_code` varchar(45) NOT NULL,
  `permission` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

It has data such as

+----+--------------+----------------------+
| id |  role_code   |      permission      |
+----+--------------+----------------------+
|  1 | ADMIN        | app.*                |
|  2 | LOC_ADMIN    | app.location.*       |
|  3 | LOC_READER   | app.location.read    |
|  4 | LOC_APPROVER | app.location.approve |
|  2 | EMP_ADMIN    | app.employee.*       |
|  3 | EMP_READER   | app.employee.read    |
|  4 | EMP_APPROVER | app.employee.approve |
+----+--------------+----------------------+

I would like to query this table to get list of fine-grained permissions for a given role_code

For e.g., for role_code 'ADMIN' I would like to get

app.location.read
app.location.approve
app.employee.read
app.employee.approve

Is it possible to write a SQL query to achieve this?

Thanks for your help.

Your specification does raise some questions, but should be easily possible.

Since % is a wildcard character, and * is not, then you might want to use % as your marker.

+----+--------------+----------------------+
| id |  role_code   |      permission      |
+----+--------------+----------------------+
|  1 | ADMIN        | app.%                |
|  2 | LOC_ADMIN    | app.location.%       |
|  3 | LOC_READER   | app.location.read    |
|  4 | LOC_APPROVER | app.location.approve |
|  2 | EMP_ADMIN    | app.employee.%       |
|  3 | EMP_READER   | app.employee.read    |
|  4 | EMP_APPROVER | app.employee.approve |
+----+--------------+----------------------+

If so, then you could run code something like this:

SELECT a.role_code, b.permission
FROM permissionstable a
  JOIN permissionstable b on a.permission LIKE b.permission
       and RIGHT(b.permission,1) <> '%'
  ORDER BY b.permission

This does depend on my understanding of the permission strings.


How to optimise this Mysql query How do I optimise this query . Its taking long time.
The inner query is fine. But outer query is taking more time . Please guide.Have attached the explain output.

SELECT users.ID, IFNULL( CONCAT( users.firstname, ' ', users.surname ) , '' ) AS Name, email
FROM users
where users.enabled= "Y" and users.ID in (
SELECT userID
FROM userData
WHERE paramName = 'Skills' AND paramValue IS NOT NULL AND paramValue != ''
UNION
SELECT users.ID
FROM users
INNER JOIN contributors ON contributors.name = users.email
)

MySQL is slow with subqueries except when they are in the FROM clause, so you move your subquery to a JOIN like this:

SELECT users.ID, IFNULL( CONCAT( users.firstname, ' ', users.surname ) , '' ) AS Name, email
FROM users
JOIN (
    SELECT userID
    FROM userData
    WHERE paramName = 'Skills' AND paramValue IS NOT NULL AND paramValue != ''
    UNION
    SELECT users.ID as userID
    FROM users
    INNER JOIN contributors ON contributors.name = users.email
) t ON t.userID = users.ID
WHERE users.enabled= "Y"


unreachable SQL Server for a group of computers I'm deploying SQL Server 2008 Express in my company. I already installed, did a application, tested, gave permission to user.

All desktop users run the program without any problem. But the notebook users can't establish a connection with SQL Server.

Facts:

Notebook has permission in the company's network (already access network drive)
Notebook pings the computer with SQL Server.
I gave permission for the company's groups and explicit for notebook users
Firewall and Anti-virus is turned off in SQL Server computer.

Any idea?

  System.Data.SqlClient.SqlException (0x80131904): A network-related or
  instance-specific error occurred while establishing a connection to
  SQL Server. The server was not found or was not accessible. Verify
  that the instance name is correct and that SQL Server is configured to
  allow remote connections. (provider: SQL Network Interfaces, error: 26
  - Error Locating Server/Instance Specified)

EDIT: The problem was that the notebooks wasn't in the same domain.

This sounds like a network problem; if the other remote clients can access the instance then it seems unlikely it is an instance-specific problem.

Be sure that firewalls on the notebooks are turned off or (better) are allowing TCP traffic through the port your server is using.
If you are connecting using the server's host name or machine name, make sure it resolves to the correct IP from the notebooks.  Try this from a command prompt on the notebooks:

C:\> ping my.databaseserver.com

or

C:\> ping OUR_SQLSERVER

In the output the IP address to which my.databaseserver.com/OUR_SQLSERVER resolves will be displayed.
See if you can connect directly via TCP to check for misconfiguration of the SQL clients.  On the notebooks, type this at a command prompt:

C:\> telnet 192.168.1.200 1433

Substitute your SQL Server's IP and the port it is using.  The reply you get will not be readable, but unless you get a message like Could not open connection to the host, on port 1433: Connect failed, you are at least able to connect to the server.

Hope this points you toward a resolution.


What complications arise from edits to a table receiving a new index? I have a very large table (1.9 million records) that currently has a unique index. I need to change that unique index to a non-unique index.

Because I am trying to avoid an "outage" from this activity, I am considering taking this clever approach to creating the new non-unique index. Creating the index in this way means that a working index is always available to benefit queries of that table--this benefit solves one of the arguments for an outage.

What is not clear to me is what adverse affects I am risking by building this index without restricting updates/inserts on the table. If those risks are negligible, that would remove the remaining need for an outage.

What complications arise from edits to a table receiving a new index?

Standard index build locks the table for the whole index build process, and DML statements on the table will be blocked. You may call that a complication.

Online index build is a feature of Enterprise Edition.
This method locks the table at the beginning, and at the end of the index build process. In between, DML operations on the table can run as usual.

Creating an Index Online


Oracle Stored Procedures / schema maintenace but still keep service online, how? I got a service running on a classic 3 tier model: 2 web servers and 2 application servers connecting to a single Oracle database instance.  Most of the business logic are written as stored procedures in the Oracle DB.  The problem I am facing is that the service has to be taken down to update the stored proc each time bugs need to be fixed or new features have to be introduced.

I'm just wondering if there is any way to keep the service running while we are updating the stored proc.  Approaches involving getting new hardware / software are also welcome.

Why does the service need to be taken down when you fix a bug?

You can deploy a new PL/SQL package without shutting anything down.  If you are changing the interface between packages or to the middle tier this may be complicated by needing to keep a backwards compatible version of some procedures or functions around.

If someone is running the existing PL/SQL package then your install of a new package will block until the user exits as it needs to obtain a lock at the package level.


Insert Into table Exec SP with bad performance I am working on a datawarehouse. One of our staging tables that is refreshed every night has about 10 million rows. We are using a custom built ETL tool that I can't make too many changes to. The tool loads this staging table like this:

truncate stage_table;
insert into stage_table with (tablockx) (column1, column2, etc...)
exec load_stage_table @batch_id = @batch_input

The contents of load_stage_table has some setup and a select statement. I can't share the exact code, but here is a basic example.

create table load_stage_table
(
     @batch_id varchar(max) = null
)
as

-- <update batch_id in batch_table>

-- collect data

select
    column1 = table1.column1,
    column2 = table2.column2,
    ...
from table1
join table2
    on table2.id = table1.table2_id
-- many more similar joins

The problem is that when I run the stored procedure as its meant to be run with our ETL tool, the run time is almost 30 minutes. However if I modify the stored procedure to have the insert statement on the inside, then it only takes 1 minute.

    create table load_stage_table
(
     @batch_id varchar(max) = null
)
as

-- <update batch_id in batch_table>

-- collect data
insert into stage_table with (tablockx) (column1, column2, etc...)
select
    column1 = table1.column1,
    column2 = table2.column2,
    ...
from table1
join table2
    on table2.id = table1.table2_id
-- many more similar joins

After running this a few times both ways and examining the execution plans, it seems that parallelism is not used when the insert is outside the stored procedure.

Does loading the table from the return, outside of the stored procedure, prevent parallelism? Or is this an indicator that the select statement needs some query tuning?

INSERT...EXEC might prohibit parallelism, but I highly doubt that is the main issue here. The problem is that INSERT...EXEC works differently than INSERT...SELECT. When inserting the results of a query (i.e. a SELECT statement or OUTPUT clause) into a table, the results are streamed into the table. If you are watching the row_count and used_page_count from sys.dm_db_partition_stats, you will seem them increase immediately as the INSERT...SELECT starts.

But, when inserting the results of an EXEC (typically a Stored Procedure but could be Dynamic SQL), the sub-process / Stored Procedure needs to fully complete before anything is inserted into the table (yes, I just tested this). If you are watching the row_count and used_page_count from sys.dm_db_partition_stats, you will seem them stay at 0 (or whatever they started at) for a looooong time after the INSERT...EXEC starts. This is not a recipe for success when the Stored Procedure returns millions of rows as those results need to be stored somewhere prior to them being inserted into the table.


when is it necessary to create aliases in the SQL Configuration Manager? I am having a problem with connectivity in one of my transactional replications and when checking the situation with the SQL Server configuration manager

I see there are aliases.
I was wonder why they have been created...

What aliases are used for in the SQL Server Configuration Manager?

Does it have anything to do that in this particular case the machines (publisher and subscriber) are on different domains?

What aliases are used for in the SQL Server Configuration Manager?

Aliases are a way for the client to be redirected to a different location without having to change things such as the connection strings. It's akin to adding in a record to your system's HOSTS file.

I can't see the names (as you've properly redacted them which is a good thing) but if any of those names are the same as the connectivity question you have open then what's happening is that everywhere a client uses that name to connect, it'll be replaced with the information in the alias. This could completely be the reason why your connectivity no longer works after an IP change as the alias could be forcing an old address.

  Does it have anything to do that in this particular case the machines (publisher and subscriber) are on different domains?

It doesn't deal with any of that. It specifically says, "Wherever you see this name in a data source, replace it with this other information instead."


Proper technique for storing users event data I am mostly a self-taught when it comes to database designs. I am posing this question because I have settled on this common structure, but am wondering if it is the most efficient or 'industry standard' method.

Most databases I design have a user table, and then a persons activty is tracked in another table. I understand that the beauty of the database is to have these sorts of efficiencies, but the activity table will gather many many events fairly quickly just from every user using it regularly, thus becoming a huge table fairly quickly with moderate user usage. Is this best practice to just let it grow in this way? Or is a tier of tables, or splitting to different tables based on dates, or per amount of users, or something else?

+--------------------+                   +------------------------+
|   UserData         |                   |   Activity             |
+-=------------------+                   +------------------------+
| ID     (auto uint) | <--1-to-many-+    | ID  (auto uint)        |
| UserName (text)    |              +--> | UserID (uint)          |
| Email    (text)    |                   | Timestamp (time)       |
| additional info... |                   | Type (ID to elsewhere) |
+--------------------+                   | additional info...     |
                                         +------------------------+

I just would like to know of where I can improve anything, as to help me learn.

You have made a very good observation. The Activity table will grow fast and large. What I have done in the past is archive off the older data (say older than 14 days) to an ActivityHistory table. Doing so keeps the Activity table to a manageable size and if you need to do research you can always look back at the ActivityHistory table.


SQL Server 2008 Express backup and restore to test We have a database system currently in SQL Server 2008 Express that we want to copy to a SQL Server 2012 server for testing. Having never moved a SQL Server database I have a few questions.

Can I take a full backup without taking the database offline?
Our database is 6Gb, however our .LDF file is over 200Gb. Can I take just a backup of the data and not bring over the transaction log?
Is the right click Backup in SSMS better/worse than doing BACKUP DATABASE in T-SQL?

Just making sure I have my ducks in a row since I don't have a current test DB with any real data to test the process. Thanks!

Full Backup always constitutes of (.mdf) and (.ldf) files as a whole DB inside Backup.If 200GB (.ldf) is the problem, then as Raz Said, Switching the recovery model to 'SIMPLE'. Follow this; Take full backup if possible before switching [if free disk space is present].
Change the recovery model to 'SIMPLE' and click OK. then again revert to 'FULL' model. Shrink the (.LDF) file now it should be able to see the (.LDF) size reduced to few MBs. Now take Full Backup and move it to 2012 server.


Server permissions of an activation stored procedure of a Server Broker queue I have a stored procedure that queries the sys.dm_exec_requests view. In the stored procedure the view only returns one row, while the stored procedure needs to see all of them. The MSDN article on the view says that what is returned depends on the user permission:

  If the user has VIEW SERVER STATE permission on the server, the user
  will see all executing sessions on the instance of SQL Server;
  otherwise, the user will see only the current session.

The stored procedure is actually an activation stored procedure of a broker queue:

CREATE QUEUE test_queue
  WITH
  STATUS = ON,
  RETENTION = OFF ,
  ACTIVATION (
    STATUS = ON,
    PROCEDURE_NAME = test_procedure,
    MAX_QUEUE_READERS = 1,
    EXECUTE AS SELF ),
POISON_MESSAGE_HANDLING (STATUS = ON)
ON [PRIMARY]

When I read the MSDN article, I changed

EXECUTE AS SELF

to

EXECUTE AS 'dbo'

which did not make any difference. sys.dm_exec_requests would still return a single row.

Also I tried to do

EXECUTE AS OWNER

Which made no difference either. As I understand the stored procedure owner is who created it, which was me. And as I'm in a sysadmin role that should be working, but it does not. I'll appreciate any troubleshooting tips.

In particular I would like to know how to list a server permissions for a given user so I could check if they include 'VIEW SERVER STATE' permission as per the article.

The confusing part is that user as I understand it is on database level, so it's unclear to me how it can have server permission. If you could clarify that, it would be great too.

See Signing an activated procedure for an example of how to properly sign an activated procedure exactly so it it can leverage VIEW SERVER STATE privilege from an activated procedure. The steps are:

inspect the procedure code to ensure that you trust it
change the procedure to have an EXECUTE AS OWNER clause (without EXECUTE AS, even if the module is signed, the principal will not have access outside the host database because of how Service Broker executes the activation procedure)
create a certificate with a private key in your app database
sign the procedure with the private key of the certificate you created
drop the private key of the certificate (to prevent it from ever being used again)
copy the certificate into the master database
create a login from the certificate
grant AUTHENTICATE SERVER to the certificate derived login
grant any additional privilege required by the procedure (e.g. VIEW SERVER STATE) to the certificate derived login


Should I drop this column or is there a query that can achieve a results table without duplicate columns? I use a column named active in several of my database tables to adjust whether a particular entry/row/user/etc should be included in future query result tables instead of deleting the information.

How can I prevent this column, named active, from showing up in the result tables from queries joining a number of tables which contain the active column?

The result table would normally be a summary based on a link table which may have its own active column as well (example: perhaps a schedule table)

This schedule table may contain the names/ids of employees/admins gathered from their own respective tables each of which house an active column.

The goal here is to get educated on queries and keep the active column.

Or just get educated and drop the column from all my tables if it happens to be a silly means of retaining information - whichever works.

Thanks to all that read and respond.

First and foremost only ever name the columns you actually need in the select list.  Never ever use SELECT *.

That said, you may already be in a hole out of which you cannot dig.  You could create a view to return only the active rows and "data" columns:

create table T(c1 int, c2 int, active bit);

create view V as
select c1, c2 from T
where active = true;

If you rename the existing, underlying table and use the table's current name for the new view you shouldn't have to change any SQL in the application (except for references to the active column, of course).

Whether you should have the active column at all depends on the application's needs and your operational constraints.  If you don't need this history for audit/ reporting/ whatever then get rid of the column and DELETE rows when they are no longer useful.  Smaller databases are better databases!

If you must retain old records moving them to an archive table is a good solution.  It keeps the operational table small and access fast (did I mention that small is good?).  You may have foreign key constraints in place; that will complicate matters.  A DELETE from the operational table and an INSERT to the archive table will be more work than a simple UPDATE to the active column and hence slower.  If this is a problem, marking them inactive, then moving a short time later in a batch process could work.


Will reindexing use the empty space or just grow table? I have a table which is 170 GB and it has 98 GB of free/empty space. The free space was created by a massive deletion. If I run DBCC REINDEX or ALTER INDEX REBUILD, will the index use that free space first or will the free space be ignored and table will just grow?

The whole point of an INDEX REBUILD is to make the processing of data more efficient.  This means that when possible it reorganizes the data to reduce the number of pages (according to the fill factor and other limitations) used for that index (whether it is a PK, unique, or non-unique index)and to order the rows of data more efficiently within the used pages and extents.

Therefore, it will reuse existing pages as the first choice. This should result in less storage being used. Of course if the indexes are modified, this could expand the amount of storage needed, depending on the modification.

Using tempdb to rebuild indexes can be a performance boost, but is unlikely to dramatically affect the required storage.

In this case you should definitely rebuild the indexes.

Note that this will not reduce the physical size of the .mdf and .ndf files.  It will free up space that can be used as tables in the database expand in size.


Is there a way to force an index to stay in memory with SQL Server 2008? I've got a table with several millions rows, from which I need to run some queries from time to time. First query will usually be quite slow (around 10s), and subsequent queries are usually way faster (around 1s). After a few hours, a slow/then fast cycle starts again.

I've checked in my execution plan that all the needed index were present and appropriately used, and I assume the performance difference is due to the fact that the index are actually in memory for the subsequent queries (am I right, or are there other possible causes?)

I'm also running a lot of other queries using indexes as well, but those queries are less time-consuming and their performance is less critical, so I'm concerned those indexes are actually pushing my critical index out of the memory cache.

Apart from the obvious 'add more RAM' fix, I've been thinking about scripting dummy queries to run every hour to force the index back in memory.

Is there a more elegant way to do this? Like a way to hint SQLServer that if it only has enough memory to keep one single index cached, it should be that one ?

I know that usually the best thing is not to mess up wich SQLServer with regards to that kind of things, but the unusual nature of my query (runs very rarely, but time-critical) makes me believe it would make sense (if possible).

I'm also curious to know if there's a way to know which indexes are cached in memory at a given time ?

There used to be a DBCC PINTABLE command but I believe that stopped working in 6.5 or maybe 7.0. The statement will probably still suggest that it worked if you try it, but it just returns, it really is a no-op.

Unfortunately there's not really any way to control which indexes are kept in cache - the best workaround I know of for tables that are periodically hot is to keep them hot manually (which you've already described in your question).

For which indexes are in memory, you can get a rough idea from sys.sm_os_buffer_descriptors. I published a tip about this:

http://www.mssqltips.com/sqlservertip/2393/determine-sql-server-memory-use-by-database-and-object/


MySQL database normalization 3NF I have homework where I need to create simple web interface and a DB normalized to 3NF. I've chosen ISP client selfcare as my database purpose. Web interface is no problem, the normalized DB is. After a really hard time I've come up with the model below.

I'd like to know if it's in 3NF?! If no, I'd be glad if you explained why.

I would say that you are pretty close to be fair - and I think (though it is a personal opinion) that 3NF is more important at an educational level that it is when it comes to the real world. I say this because it is not always logical or practical to obtain perfect 3NF, this could be due to an applications design or the performance of the queries etc etc...

That being said, the best thing I could give you to remember 3NF is a quote a friend once told me (who read it in a book to be fair!) and that is this: Every non-key attribute must provide a fact about the key, the whole key, and nothing but the key. - I think the person who said it originally was Bill Kent.

So on that basis, there are some minor improvements you could make; let's take your city table for example. You have an ID, Title and Post Code; well if you think about it, there are many Post Codes per City, and your design would mean that for each new Post Code you would need to add the ID and Title again - thus breaking the requirements for 3NF. For this you could have an additional table called PostCodes which could contain an ID, Name and City columns, you could then reference your city this way - which is better. The same goes for your Client Info table which has an address field - but it's possible that an address can be shared among many people, as can phone numbers, email addresses etc etc you get the picture!

The best way to remember it is this - if there is a possibility that you have to duplicate information just to satisfy your design - then you either have a special requirement (which is possible - like I said "real world") or your design is wrong - you need to judge it on a case by case basis.

I hope that this helps you.


Grant access to only one key in azure keyvault There are multiple keys and secrets in azure key vault . I need to grant a user access to only one key and secret in azure portal. Is that possible?

Permissions for keys are at the vault level. In order to get the granularity that you want, you would have to create another key vault.

From the documentation (emphasis added):

  Key vault access policies grant permissions to keys, secrets and certificates separately. For example, you can give a user access to only keys, but no permissions for secrets. However, permissions to access keys or secrets or certificates are at the vault level. In other words, key vault access policy does not support object level permissions. You can use Azure portal, the Azure CLI tools, PowerShell, or the key vault Management REST APIs to set access policies for a key vault.


Will table partitions help me? I have 10 tables.

dbo.Table2008
dbo.Table2009
...
dbo.Table2018

Each table holds approximately 500,000,000 records which are 20 columns wide (if that matters). And each table has a clustered columnstore index on it.
Each table also holds data only for the year in its name. For instance, in dbo.Table2008 you'll only see records for which the CreatedDate column is >= 20080101 and <= 20081231. But if I want to issue a query across 3 years I need to hit all three tables with a union.

My theory is if I have a single table called dbo.Table which is partitioned into ~120 partitions, one partition for every month of every year, then it'll not only reduce my table count. So I can change my query from

with cte as (
    select col1, col2, col3, col4
    from table2008
    where col4 >= 20080201 and col4 <= 20120801
    union
    select col1, col2, col3, col4
    from table2009
    where col4 >= 20080201 and col4 <= 20120801
    union
    select col1, col2, col3, col4
    from table2010
    where col4 >= 20080201 and col4 <= 20120801
    union
    select col1, col2, col3, col4
    from table2011
    where col4 >= 20080201 and col4 <= 20120801
) select ...
from cte
join LookupTable1 on ... = ...
join LookupTable2 on ... = ...

to

select col1, col2, col3, col4, lookuptablecol1, lookuptable2col2
from dbo.Table
join ALL MY LOOKUP TABLES

Is my theory correct?
Is table partitioning with parition schemes and partition functions what I'm after?
And will this theory work using clustered columnstore indexes?

Partitioning does not sound like a good fit for your scenario. Partitioning isn't something to be taken lightly. It requires a lot of planning and you'd have to rewrite 5 billion rows in order to convert the data. Instead you should consider a UNION ALL view. Depending on your requirements you could even make it a partitioned view.

A view will give you an easy way to query your data without any date movement. It's by far the simplest solution.

In general, partitioning doesn't improve query performance. However, with columnstore tables it can be helpful if it gives you better rowgroup elimination on the partitioned column. That can be accomplished without partitioning depending on how you build your columnstore tables. If you don't modify the old data it should be relatively straightforward to build your columnstore in a way that maintains order.

Columnstore tables do support partitioning, if you do have a legitimate need for it at some point. It's easy to experiment with it. Simply try to create an empty, partitioned columnstore table.


Joining subqueries with GROUP is slow Can anyone help me with the following query?

SELECT
  voc.username AS Medewerker,
  voc.bestvastonbep_count AS Doorgezet,
  IFNULL(cc.call_count, 0) AS Telefoongesprekken,
  voc.bestvastonbep_count / IFNULL(cc.call_count, 0) AS Ratio
FROM (
  SELECT
    u.id AS user_id,
    CONCAT(u.firstname, ' ', u.lastname) AS username,
    COUNT(vo.id) AS bestvastonbep_count
  FROM `bestvastonbep`       vo
  JOIN `best`                b   ON b.id = vo.best_id
  JOIN `vastonbepbeststatus` vos ON b.id = vos.best_id
  JOIN `user`                u   ON u.id = b.user_id
  WHERE vos.type = 2
  AND   vos.status = 2
  AND  (b.date BETWEEN '2017-02-04' AND '2017-05-04')
  GROUP BY u.id
) voc
LEFT JOIN (
  SELECT
    user_id,
    COUNT(id) AS call_count
  FROM `call` c
  WHERE  (c.start BETWEEN '2017-02-04' AND '2017-05-04')
  AND c.duration > 0
  AND c.calltype = 'out'
  GROUP BY c.user_id
) cc
ON voc.user_id = cc.user_id;

It produces the desired result, but it's immensely slow.
I've read questions from other people about similar queries. For them it turned out that joining subqueries wasn't necessary.
The difference for me is that I'm using GROUP for both subqueries.
Hopefully there's a way and I'm just not seeing it.

Edit in response to RDFozz

The EXPLAIN query gives the following result.

The first subquery is very fast. (0.1 s)
The second subquery takes about 6 seconds.
The complete query takes about 57 seconds.

There is an index on call.user_id as well as call.start.
But the call table currently has about 32 million rows.

Answer
I took Plan A as described by Rick James.
The query now looks like the following:

SELECT
    voc.username AS Medewerker,
    voc.bestvastonbep_count AS Doorgezet,
    IFNULL(count(c.user_id), 0) AS Telefoongesprekken,
    IFNULL(voc.bestvastonbep_count / count(c.user_id), "&infin;") AS Ratio
FROM (
    SELECT u.id AS user_id, CONCAT(u.firstname, ' ', u.lastname) as username, COUNT(vo.id) AS bestvastonbep_count
    FROM `bestvastonbep`       vo
    JOIN `best`                b   ON b.id = vo.best_id
    JOIN `vastonbepbeststatus` vos ON b.id = vos.best_id
    JOIN `user`                u   ON u.id = b.user_id
    WHERE vos.type = 2
    AND   vos.status = 2
    AND  (b.date BETWEEN '2017-02-04' AND '2017-05-04')
    GROUP BY u.id
) voc
LEFT JOIN `call` c on
    (c.start BETWEEN '2017-02-04' AND '2017-05-04')
    AND c.duration > 0
    AND c.calltype = 'out'
    AND voc.user_id = c.user_id
GROUP BY voc.user_id;

It takes about 7 seconds, depending on the size. Faster would be nice, but this is just about acceptable for my purposes.

Explode-Implode

In the first derived table (voc) you have what call the "explode-implode" syndrone.  First the JOINs generate a lot of rows, then the GROUP BY whittles it back to a few rows.

Sometimes the solution is to turn a JOIN into a subquery:

SELECT  ...,
        b.foo
    FROM a
    LEFT JOIN b
    GROUP BY a.id

-->

SELECT  ...,
        ( SELECT foo FROM b WHERE ... = a... ) AS foo
    FROM a

Note that the GROUP BY goes away.  This example works especially well for LEFT JOIN because the "optional" nature of foo is preserved.

(The GROUP BY in cc is not the same situation.)

Derived JOIN Derived

MySQL is terrible (pre-5.6) or poor (5.6+) at dealing with JOINing two subqueries (voc and cc).

Plan A:  Turn one of those subqueries into a JOIN with the other.

Plan B:  Put one of the subqueries into a TEMPORARY TABLE and give it an appropriate index: (user_id).

Indexes

In the absence of SHOW CREATE TABLE, must do some guessing...

vos: INDEX(type, status, best_id)
b:   INDEX(date)
c:   INDEX(calltype, start, duration, user_id)

And change COUNT(id) to COUNT(*).

Note:  start BETWEEN '2017-02-04' AND '2017-05-04' is "inclusive".  Consider changing to

    start >= '2017-02-04'
AND start  < '2017-02-04' + INTERVAL 3 MONTH

Please provide EXPLAIN SELECT ... after adding those indexes.

I see a possible divide-by-zero; maybe you want

IFNULL(voc.bestvastonbep_count / cc.call_count, 0)


How can I get status messages during a large re-indexing operation with MySQL 5.5? I get large data transfers periodically that I used to completely replace the contents of a MySQL table.  Each time I do this, I truncate the table, disable keys, add several million rows of data, and then enable keys.  As expected, once I enable keys, there is a lengthy delay while the table is being reindexed. The table has several active indices and it takes about 30 minutes to complete.

Unfortunately, I can't figure out how to get status messages that would give me progress updates on how the operation is proceeding. I found an old thread from 2001 on the MySQL forum that basically says you can't get progress updates.

I was hoping that was no longer the case. I am running MySQL Server 5.5 and MySQL Workbench 5.2, both community editions.  Perhaps there's something I can do with workbench?

Side question:  Currently, I do my updates from a Windows program that sequentially parses the transfer data file into SQL INSERT statements that I immediately dispatch to the MySQL client over a socket. Would it be faster to generate all the INSERT statements to a file and then do a bulk import by executing the SQL script from Workbench instead?

-- roschler

You need to monitor that progress outside of all mysql client programs.

For this example, let's say your MyISAM table is named workingset in the database mydata and the datadir is the default, /var/lib/mysql

Sidenote : If you are using Windows, run this SQL command to get your datadir

SHOW VARIABLES LIKE 'datadir';

You will have three files representing the table

/var/lib/mysql/mydata/workingset.frm
/var/lib/mysql/mydata/workingset.MYD
/var/lib/mysql/mydata/workingset.MYI

Try a temp table approach to loading the table workingset

use mydata
DROP TABLE IF EXIST workingsetload;
CREATE TABLE workingsetload LIKE workingset;
ALTER TABLE workingsetload DISABLE KEYS;
Perform 7 million row insert into workingsetload here, preferably LOAD DATA INFILE
ALTER TABLE workingsetload ENABLE KEYS;
ALTER TABLE workingset RENAME workingsetold;
ALTER TABLE workingsetload RENAME workingset;
DROP TABLE workingsetold;

During the load of the table workingsetload, there will be a temp table whose first five(5) characters are "#sql-". You can visually monitor in the Linux OS the progress of the loading of workingsetload like this:

cd /var/lib/mysql/mydata
watch -n 1 "ls -l workingsetload.MY[ID] *sql-*.MY[ID] | awk '{print $5,$9}'"

You could script this in linux like this:

cd /var/lib/mysql/mydata
ls -l workingsetload.MY[ID] *sql-*.MY[ID] | awk '{print $5,$9}' > /tmp/IndexRebuild.txt

You could then parse the columns accordingly and subtract the .MYD sizes to know when the data portion of the MyISAM table is complete. Subsequently, you could then parse the columns accordingly and subtract the .MYI sizes to know when the index portion of the MyISAM table is complete.

For Windows, you could script this using Perl (ActivePerl for Windows) or just have a DOS Batch loop over and over again.

Regardless of OS, once the "ls -l #sql-*.MY[ID]" or "if not exist #sql-*.MY*" comes back with nothing, the reload and indexing are complete.

Give it a Try !!!

UPDATE 2011-07-20 16:55 EDT

Monty has successfully implemented this feature in MariaDB. Check it out !!!