sqlcmd issues with -E and -S flags Consider the following command, which I've wrapped for readability:

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup]
@Databases = 'USER_DATABASES', @Directory = N'C:\Backup', @BackupType = 'DIFF',
@Verify = 'Y', @CleanupTime = NULL, @CheckSum = 'Y', @LogToTable = 'Y'" -b

It returns the following error:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'E'.
Msg 103, Level 15, State 4, Line 3
The identifier that starts with 'EXECUTE [dbo].[DatabaseBackup]
@Databases = 'USER_DATABASES', @Directory = N'C:\Backup',
@BackupType = 'DIFF', @Verify = 'Y', @C' is too long.
Maximum length is 128.

When I remove the sqlcmd it works.  I know I'm doing something wrong, however I don't understand what the problem is.

I just put your command into a SQL Server Agent Jobstep configured to use CmdExec; it ran the command perfectly.

USE [msdb]
GO

/****** Object:  Job [CmdExecTest]    Script Date: 2018-03-19 10:55:07 AM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 2018-03-19 10:55:07 AM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'CmdExecTest',
        @enabled=1,
        @notify_level_eventlog=0,
        @notify_level_email=0,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N'This job provides a way to test SQL Server Agent Jobs',
        @category_name=N'[Uncategorized (Local)]',
        @owner_login_name=N'<me>', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Step1]    Script Date: 2018-03-19 10:55:07 AM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step1',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'CmdExec',
        @command=N'sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = ''USER_DATABASES'', @Directory = N''C:\Backup'', @BackupType = ''DIFF'', @Verify = ''Y'', @CleanupTime = NULL, @CheckSum = ''Y'', @LogToTable = ''Y''" -b',
        @flags=32
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

The result was a job failure for me because I don't have the dbo.DatabaseBackup stored procedure:

Msg 2812, Level 16, State 62, Server MyServer, Line 1
Could not find stored procedure 'dbo.DatabaseBackup'.

However, this indicates that sqlcmd was successfully executed.


SQL Server 2012 - Create Alias from default instance (i.e. '.') to named instance of SQL 2012 Scenario is: I have SQL 2012 running as a named instance 'SQL2012'. I can connect to this via SSMS using '.\SQL2012'

Working on a new project, I now want this named instance to be accessible as '.'

To complicate matters, I also have SQL 2014 installed as the default instance, don't need this simultaneously so we can assume this instance is stopped.

I have tried various combinations of alias, the most obvious being:

I've also tried setting the port number to the Dynamic Port found in the network configuration, but nothing I've tried so far seems to work.

Is this supported, and if so, what am I missing?

Turns out, it is possible, and after some more experimentation I figured this out. I think the missing elements were:

Using .\SQL2012 as the server name instead of MACHINENAME\SQL2012
Ensuring SQL Server Browser service is running.

Full Steps

Ensure SQL Server Browser service is started, as well as your named SQL2012 instance.
Open SQL Server Configuration Manager on your machine
Expand the SQL Server Network Configuration node, and double click Protocols for
Ensure TCP/IP is set to Enabled, if it's not, right-click and choose Enable
Double click TCP/IP

Ensure Listen All is set to Yes
Select the IP Addresses tab, and scroll to the bottom. Make a note of the value in TCP Dynamic Ports.
Press OK

Expand the SQL Native Client 11.0 Configuration node and double click Aliases

Right click in white space and select 'New Alias'
In Alias Name, enter '.'
In Port No, enter the value you noted earlier from TCP Dynamic Ports.
Leave Protocol as TCP/IP
In Server, enter .\<yourinstancename>
Press OK

Repeat the steps for SQL Native Client 11.0 Configuration for SQL Native Client 11.0 Configuration (32 bit)
Test the connection by using SSMS to attempt to connect to '.'


BUILTIN\Administrators allowing access via linked server but not locally We have two servers:

LYNDB01 & LYNDB02

On LYNDB01 we have a database called db_ExampleDatabase

We have created a Linked Server on LYNDB02 to LYNDB01 using no mappings and the "Be made using the login's current security context" option for authentication:

EXEC master.dbo.sp_addlinkedserver @server = N'LYNDB01', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LYNDB01',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

We have a windows user on LYNDB02 for DOMAIN\USER1 - this windows user does not have a login on LYNDB01 at all but is a domain admin and thus is a member of BUILTIN\Administrators on both LYNDB01 & LYNDB02

I have created a view in the LYNDB01.db_ExampleDatabase database to confirm what user is running the transaction:

CREATE VIEW [dbo].[vw_CurrentUser]
AS
    SELECT  SYSTEM_USER AS CurrentUser

For testing we gave DOMAIN\USER1 RDP access to LYNDB01 & LYNDB02.

If we RDP to LYNDB01 as DOMAIN\USER1 and open up SSMS with windows authentication - we get a logon error as DOMAIN\USER1 does not have a login on LYNDB01, so that's fine.

If we RDP to LYNDB02 as DOMAIN\USER1 and open up SSMS with windows authentication it connects OK (Which is fine as DOMAIN\USER1 has a login on LYNDB02). Here is our problem, we can access LYNDB01.db_ExampleDatabase.dbo.vw_CurrentUser via the Linked Server!

SELECT * FROM LYNDB01.db_ExampleDatabase.dbo.vw_CurrentUser

How is this possible when LYNDB01 does not have a login on LYNDB01 (Which is confirmed by the error we get when connecting locally!)

And yes, the remote query confirms that DOMAIN\USER1 is the current user??

VERY confused?!

Edit: Theroy 1

DOMAIN\USER1 is a domain admin and is therefore a local admin on both LYNDB01 & LYNDB02 - could this be causing it? if so, why would i get access denied on LYNDB01 as USER1 has no logon but work via a linked server?

OK, if i delete BUILTIN\Administrators logon from LYNDB01 this problem goes away and the query from LYNDB02 fails - why is BUILTIN\Administrators letting USER1 authenticate remotely via a linked server but not locally & what can i do about it?

This is expected. LUA strips administrators membership from your token. From Getting Started with User Account Control on Windows Vista:

  To help prevent malware silent installation and computer-wide infection, Microsoft developed the UAC feature for Windows Vista. Unlike previous versions of Windows, when an administrator logs on to a Windows Vista computer, the userâ€™s full administrator access token is split into two access tokens: a full administrator access token and a standard user access token. During the logon process, authorization and access control components that identify an administrator are removed or disabled, resulting in a standard user access token. The standard user access token is then used to launch the desktop, the Explorer.exe process. Because all applications inherit their access control data from the initial launch of the desktop, they all run as a standard user as well. Contrasting with this process, when a standard user logs on, only a standard user access token is created. This standard user access token is then used to launch the desktop.

Since your local desktop token has the administrator membership removed, it cannot log in locally to SQL Server if you expect to log in through builtin\Administrators. On remote access your token preserves the Administrator membership and you gain access.

The solution is to grant SQL Server login rights through an explicit domain group account and add all users that require access to your SQL instance to this domain group. Do not rely on Administrators membership, is a bad practice.


MySql Workbench Data Import .csv Hi I am trying to use to MySql Workbench Data Import facility to import a .csv file (thousands of records). However it stops importing the first 5 records.

After studying the .csv file I could see that on line 5 there was a double quote - see below;

1,90945601,123.123.123.123,Fujitsu,Esprimo Q900,YLBX097807192,,01/07/2011,408000085A,G.039,Weekend  PT (BS),,,,
2,90456978905,123.123.123.123,Fujitsu,Esprimo Q900,YLBX009787188,,01/07/2011,408089005A,"G,.039",Joe (ert),,,,
3,90945610,123.123.123.123,Fujitsu,Esprimo Q900,YLBX007897202,,01/07/2011,408097805A,2.012,Jo,,,,
4,90945978616,,Apple ,Mac ZOPF00PJ ,DGKLM05BF8J9,,01/10/2013,408005A,1.029,Jim and ICRH,,,wif,
5,90456789917,,D4M ,FlexiBasic Kiosk ,,,01/08/2013,408098705A,1.029,,,,"19"" Saw Screen with Artwork",
6,94560918,143.117.198.197,3M,9100BC,91201338,,01/07/2013,408005A+J101,G.036,SD RF,,,Dual (91201567338) ,

I have tried to configure the settings during the import however it still doesn't work - see image;

It's not possible to change the 'Field Seperator' tp a comma, only three options are provided.

I manually removed line 5 from the csv and this time over 100 records were imported, but again, it stropped at the next double quotes ". The problem is the csv file contains thousands of items that have descriptions that include size in inches.

Are there any other changes I can make without having to manually remove all the double quotes from my .csv file?

I've also tried PhpMyAdmin, doesn't work there either.

Any suggestions appreciated.

You could try using sed and substitute | (the pipe character - not normally found in data dumps) for the " (double quote) character

sed -i 's/"/|/g' My_File.csv

sed -i does an inline replace - take a backup before messing with your original file. If you're on Windows - as you appear to be, you can get sed functionality from the answer here or use the editor of your choice - VIM for Windows for example or whatever you prefer.

Then when you've loaded the data, do a REPLACE() on your tables


How to truncate an SSAS Tabular partition I have a tabular cube with a large partitioned table.

I'd like to truncate some of the partitions (freeing the memory space), but retain the metadata, so that I can quickly  rebuild them if required at a later time, without having to recreate the partition.

Is this possible? If so, how can it be done?

In SQL Server Management Studio you can connect Object Explorer to SSAS, expand to the table, right click, choose Partitions, then click to process one or more partitions and do a Process Clear:


SQL Server 2016 Security - Restrict users from viewing a single database Our client requirement, is to restrict other users from viewing/accessing the database if they are not assigned to work on it.

Some background on how the databases are setup:

For each client, a new database is created to host their data. Also there are
  other databases which are used by the front end application.

There are three types of users:

Data manager - modify tables according to data, load data, execute SP
Application user - used by front end web application
SME - They are data experts who work on the DB in need

All of the above mentioned users are configured as SysAdmin role. We
  understand this is not a good practice and efforts are in place to assign
  appropriate role to each db user

Consider this scenario:

There are

5 databases
3 data managers and 3 SME's and 1 application user.

1 data manager and 1 SME are assigned to work on the client data.

Also the application user is required to access by default.

The expectation is:

2 data managers will have access to 4 database (excluding this client)
2 SME's will have access to 4 database (excluding this client)
1 application user will have access to all databases
1 assigned data managers will have access to all databases
1 assigned SME will have access to all databases

My knowledge on SQL Server 2016 security and roles are limited. Please suggest an approach for this requirement.

Contained Databases are your option.
Your server version supports them.

No one of your user will require server access, all of them can be authenticated by corresponding database.
As consequence, none of them will "see" databases other than they are mapped too.

The problem of "seeing" all the databases derives from VIEW ANY DATABASE server level permission granted to public, if you revoke it from public or deny to someone, that login will not "see" even the database it is mapped to, except for the database owners (not to be confused with members of db_owner database role, the owner is only one server principal that owns the database).

So when we have no logins, we have no problem of databases visible to everyone


Company supplied data list, with customer additions Imagine a company/supplier supplied table of items
table - blueprints
 id [pk]
 name
 color

blueprints
- 1 - chair - pink
- 2 - table - blue

As a company we would supply blueprints as a global list,
the customer would have a local copy of that list.
The customer would need to add more blueprints which would add rows to their local copy (which is a bad idea)
Occasionally as the company/supplier would have additions to the blueprints table.
Ideally after several iterations of updates from the supplier, it would be nice that across all customers the supplied blueprint list has the same id across all customers.
Im sure this is know problem, or there is a better solution for this.
Any thoughts / ideas / existing posts / explanations of what this is, with a proper solution?

For the sake of the customer having a local copy and the supplier having the master copy you could have two tables...
Supplier's Master Copy
blueprints_master
~id ~ name ~ colour ~
~ 1 ~ chair   ~  pink    ~
~ 2 ~ table   ~  blue    ~

Customer Local Copy
blueprints_customer

~id ~ name ~ colour ~ blueprits_master_id ~
~ 1 ~ chair   ~hot pink~  1                                 ~
~ 2 ~ desk   ~ wood   ~   null                            ~

Problems Solved

  A list of blue prints the customer can add, remove, and update
  A link (foreign key) between a customers blueprint and a master blueprint
  I think this is useful in case the customer want's to "change" one of the master blueprints. Internally the system will create a new entry in the _cusomter table rather then changing the _master table, but the customer will still feel like they are doing what they wanted
  A list of blueprints the supplier has absolute control over

Problems Remaining

  Syncing the _customer and _master tables.

unfortunately I do not have any proposed solution to the syncing problem nor can I reference to any other post...


In Oracle, can we restore a database onto the same instance with a different name? In SQL server I can restore a database onto the same instance with a different name. Eg: If there is a database DB_prod, I can take a full backup and restore it on the same instance with a different name like DB_test.

Is a similar task possible with Oracle?

Easily - just use RMAN to clone it. I think you mean in Oracle terminology, clone the database into a new instance on the same server. If you really do mean clone the schema into the same Oracle instance, then the easiest way is Datapump.


Slave SQL thread got hanged We have a master - slave setup with ROW based replication.
We are seeing huge delay's on the salve even though there is no activity running wither on master or slave.

When we looked in, we observed the SQL thread looks like hanged. It has been in "Reading event from the relay log" state since last 3 hours or more.

baleaf:(none)> show processlist ;
+--------+-------------+-----------+------+---------+-------+----------------------------------+-----    -------------+
| Id     | User        | Host      | db   | Command | Time  | State                            |        Info             |
+--------+-------------+-----------+------+---------+-------+----------------------------------+-----    -------------+
| 217159 | system user |           | NULL | Connect |  1039 | Waiting for master to send event | NULL             |
| 217160 | system user |           | NULL | Connect |  10045 | Reading event from the relay log | NULL             |
+--------+-------------+-----------+------+---------+-------+----------------------------------+-----    -------------+
4 rows in set (0.00 sec)

baleaf:blackarrow_dw> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
               Slave_IO_State: Queueing master event to the relay log
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.001403
          Read_Master_Log_Pos: 95601911
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 12757514
        Relay_Master_Log_File: binlog.001403
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 32820355
              Relay_Log_Space: 75539220
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 7720
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
1 row in set (0.00 sec)

Shall some one please have a look into this ASAP.

You may need to reset the relay logs. PLEASE DON'T USE RESET SLAVE;

You need to reset the relay logs in such a way that it picks up from the last SQL statement it executed.

Please run the the following:

STOP SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE='binlog.001403',MASTER_LOG_POS=32820355;
START SLAVE;

If Seconds_Behind_Master stays at zero, CONGRATULATIONS !!!

If Seconds_Behind_Master goes back to 7720 immediately, check the date and time set in the OS on the Master against the the date and time set in the OS on the Slave.

Give it a Try !!!


How to use mysqltuner from remote host We do not have access to our servers with operating system console. Our production environment is Ubuntu 12.
Is it possible to use mysqltuner from remote host?
If yes, please provide guidelines. We are using mysql5.6

What I am about to show you comes from lines 89-122 of the mysqltuner.pl code:

You will still have to connect like a mysql client:

Connection and Authentication
    --host <hostname>    Connect to a remote host to perform tests (default: localhost)
    --socket <socket>    Use a different socket for a local connection
    --port <port>        Port to use for connection (default: 3306)
    --user <username>    Username to use for authentication
    --pass <password>    Password to use for authentication

Since you are using a remote host, use parameters to supply values from the OS

--forcemem <size>    Amount of RAM installed in megabytes
--forceswap <size>   Amount of swap memory configured in megabytes

You may have to contact your remote SysAdmin to ask how much RAM and swap you have

If the database has too many tables, or very large table, use this:

--skipsize           Don't enumerate tables and their types/sizes (default: on)
                     (Recommended for servers with many tables)


Performance difference between Clustered and Non Clustered Index I was reading Clustered and Non Clustered Indexes.

Clustered Index - It contains Data Pages. That means the complete row
information will be present in the Clustered Index Column.

Non Clustered Index - It only contains the Row Locator information in the
form of Clustered Index column(if availabe) or the File Indentifier +
Page Number + Total Rows in a Page.  This means that the query engine must take an additional step in order to locate the actual data.

Query - How can I check the performance difference with a help of a practical example as we know that the table can have only one Clustered Index and provides sorting at the Clustered Index Column and Non Clustered Index don't provide sorting and can support 999 Non Clustered Indexes in SQL Server 2008 and 249 in SQL Server 2005.

Very good question as it is such a important concept. This is a big topic though and what I am going to show you is a simplification so you can understand the base concepts.

Firstly when you see clustered index think table. In SQL server if a table does not contain a clustered index it is a heap. Creating a clustered index on the table actually transforms the table into a b-tree type structure. Your clustered index IS your table it is not separate from the table

Ever wondered why you can only have one clustered index? Well if we had two clustered indexes we would need two copies of the table. It contains the data after all.

I am going to try and explain this by using a simple example.

NOTE: I created the table in this example and filled it with over 3 million random entries. Then ran the actual queries and pasted the execution plans here.

What you really need to grasp is O notation or operational efficiency. Let's assume you have the following table.

CREATE TABLE [dbo].[Customer](
[CustomerID] [int] IDENTITY(1,1) NOT NULL,
[CustomerName] [varchar](100) NOT NULL,
[CustomerSurname] [varchar](100) NOT NULL,
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
[CustomerID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF
  , IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS  = ON
  , ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

So here we have  basic table with a clustered key on CustomerID (Primary key is clustered by default). Thus the table is arranged/ordered based on the primary key CustomerID. The intermediate levels will contain the CustomerID values. The data pages will contain the whole row thus it is the table row.

We will also create a non-clustered index on the CustomerName field. The following code will do it.

CREATE NONCLUSTERED INDEX [ix_Customer_CustomerName] ON [dbo].[Customer]
 (
[CustomerName] ASC
 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF
  , SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF
  , DROP_EXISTING = OFF, ONLINE = OFF
  , ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

So in this index you would find on the data pages/leaf level nodes a pointer to the intermediate levels in the clustered index. The index is arranged/ordered around the CustomerName field. Thus the intermediate level contains the CustomerName values and the leaf level will contain the pointer (these pointer values are actually the primary key values or CustomerID column).

Right so if we execute the following query:

SELECT * FROM Customer WHERE CustomerID = 1

SQL will probably read the clustered index via a seek operation. A seek operation is a binary search which is much more efficient than a scan which is a sequential search. So in our above example the index is read and by using a binary search SQL can eliminate the data that don't match the criteria we are looking for.  See attached screen shot for the query plan.

So the number of operations or O Notation for the seek operation is as follows:

Do binary search on clustered index by comparing the value searched for to the values in the intermediate level.
Return the values that match( remember since the clustered index has all the data in it can return all the columns from the index as it is the row data)

So it is two operations. However if we executed the following query:

SELECT * FROM Customer WHERE CustomerName ='John'

SQL will now use the non-clustered index on the CustomerName to do the search. However since this is a non-clustered index it does not contain the all of the data in the row.

So SQL will do the search on the intermediate levels to find the records that match then do a lookup using the values returned to do another search on the clustered index(aka the table) to retrieve the actual data. This sounds confusing I know but read on and all will become clear.

Since our non-clustered index only contains the CustomerName field(the indexed field values stored in the intermediate nodes) and the pointer to the data which is the CustomerID, the index has no record of the CustomerSurname. The CustomerSurname has to be fetched from the clustered index or table.

When running this query I get the following execution plan:

There are two important things for you to notice in the screen shot above

SQL is saying I have a missing index(the text in green). SQL is suggesting I create a index on CustomerName which includes CustomerID and CustomerSurname.
You will also see that 99% of the time of the query is spent on doing a key lookup on the primary key index/clustered index.

Why is SQL suggesting the index on CustomerName again? Well since the index contains only the CustomerID and the CustomerName SQL still has to find the CustomerSurname from the table/clustered indexes.

If we created the index and we included the CustomerSurname column in the index SQL would be able to satisfy the entire query by just reading the non-clustered index. This is why SQL is suggesting I change my non-clustered index.

Here you can see the extra operation SQL needs to do to get the CustomerSurname column from the clustered key

Thus the number of operations are as follows:

Do binary search on non-clustered index by comparing the value searched for to the values in the intermediate level
For nodes that match read the leaf level node which will contain the pointer for the data in the clustered index (the leaf level nodes will contain the primary key values by the way).
For each value returned do a read on the clustered index(the table) to get the row values out here we would read the CustomerSurname.
Return matching rows

That is 4 operations to get the values out. Twice the amount of operations needed compared to reading the clustered index. The show you that your clustered index is your most powerful index as it contains all the data.

So just to clarify one last point. Why do I say that the pointer in the non-clustered index is the primary key value? Well to demonstrate that the leaf level nodes of the non-clustered index contains the primary key value I change my query to:

SELECT CustomerID
FROM Customer
WHERE CustomerName='Jane'

In this query SQL can read the CustomerID from the non-clustered index. It does not need to do a lookup on the clustered index. This you can see by the execution plan which looks like this.

Notice the difference between this query and the previous query. There is no lookup. SQL can find all the data in the non-clustered index

Hopefully you can begin to understand that clustered index is the table and non-clustered indexes DON'T contain all the data. Indexing will speed up selects due to the fact that binary searches can be done but only clustered indexes contain all the data. So a search on a non-clustered index will almost always result in values being loaded from the clustered index. These extra operations make non-clustered indexes less efficient than a clustered index.

Hope this clears things up. If anything does not make sense please post a comment and I will try clarify. It is rather late here and my brain is feeling a wee bit flat. Time for a red bull.


Filtering by minimum value per group problem I have a select statement, which when simplified produces the following:

+------------+----------+-----+
| Group      | Name     | ID  |
| vegetables | potatoes | 1   |
| vegetables | carrots  | 6   |
| fruit      | apples   | 100 |
| fruit      | mangoes  | 97  |
+------------+----------+-----+

I would like it to produce this:

+------------+----------+----+
| Group      | Name     | ID |
| vegetables | potatoes | 1  |
| fruit      | mangoes  | 97 |
+------------+----------+----+

Turning the original select into a table is not an option. Ideally for readability, I'd like to not copy/paste the select; I'd rather alias it somehow but I feel like my syntax for that isn't quite right.

Here's my actual code. It is a horrible mess (actually if you have any suggestions for making it less of a mess that'd be great). It gets ORA-00942: table or view does not exist on the top layer - the layer below that works fine (producing the first table above, essentially)

-- first record of each brand name that has been interchanged
SELECT *
FROM
(
-- all brand name  records that have been interchanged
SELECT DIN, DRUG_INFO_NOS.TRADE_NAME tn, FORM, GENERIC_NAME, MANUFACTURER, CR_DATE, NOC_DATE, FIRST_CLAIM_DATE, DRUG_INFO_NOS.SEQUENTIAL_ID blah
FROM
DRUG_INFO_NOS
INNER JOIN
-- Brand name drugs that have been interchanged.
(
SELECT DRUG_INFO_NOS.TRADE_NAME FROM
DRUG_INFO_NOS
INNER JOIN
-- seq_ids corresponding to interchanges
(SELECT MIN(SEQUENTIAL_ID) seq_ID FROM
 (
 --set of sequential ids not belonging to brand names
SELECT * FROM DRUG_INFO_NOS
WHERE DRUG_INFO_NOS.SEQUENTIAL_ID NOT IN
(
--set of all sequential ids belonging to brand names
SELECT SEQUENTIAL_ID FROM
DRUG_INFO_NOS
WHERE
DRUG_INFO_NOS.TRADE_NAME IN
(
--set of brand names
SELECT TRADE_NAME FROM
(
(SELECT MIN(SEQUENTIAL_ID) as seq FROM DRUG_INFO_NOS
GROUP BY GENERIC_NAME) a1

INNER JOIN
DRUG_INFO_NOS
ON a1.seq = DRUG_INFO_NOS.SEQUENTIAL_ID
  )
  )
  )
  )
  GROUP BY GENERIC_NAME) interchangeSeqIDs

  ON interchangeSeqIDS.seq_ID = DRUG_INFO_NOS.SEQUENTIAL_ID + 1) interchangeddrugs
ON DRUG_INFO_NOS.TRADE_NAME = interchangeddrugs.TRADE_NAME) interchangedbrandrecords
INNER JOIN
(SELECT tn, MIN(blah) minseq FROM interchangedbrandrecords GROUP BY tn) filter
on filter.tn = interchangedbrandrecords.tn;

To get the desired result from your sample table,

select tgroup, name, id
from
(
    select tgroup, name, id, row_number() over (partition by tgroup order by id) rn
    from (<<your subselect>>)
 )
 where rn = 1

I've renamed group to tgroup as group is a reserved word in SQL.

SQLFiddle

As for your real query, please first make it readable.


How to compare the database of a backup from another environment Has anyone had any luck with taking a backup (approx 30 tables) from an Oracle database's live environment, then loading a test environment with this backup and doing a comparison between the test environment and the backup taken from the live environment? Preferably without using queries against the live environment.

Note that the live environment will have transactions changing its data by the time the test environment is loaded, so I can't do a comparison using the live environment's data after loading the test environment.

The idea is to pick up any missing records, columns or even tables that the backup has missed. It would also be great to know if the actual data values were the same.

I'm thinking a hash function might be the best way to go. Are there any tools available for this?

It sounds to me like the used procedure is somehow suboptimal :-)
We have two easy ways to create an identical test Database from a live DB:
Simple Cloning and Snapshot Standby.
Simple Cloning is the RMAN command duplicate target database:
http://download.oracle.com/docs/cd/E11882_01/backup.112/e10642/rcmdupdb.htm#BRADV010

Snapshot Standby is even more comfortable, because you can do it very fast & easy multiple times subsequently after your live DB did change:
http://uhesse.wordpress.com/2010/09/24/snapshot-standby-database-in-action/


How to get row(s) with max or null value in Postgres I would like get all rows with h_id max value or NULL if present there. For example result for table below will be:
fid     |h_id    |
--------|--------|
13374976|        |
13377411|66666666|

I tried to write select statement but it returns only max values. How should I extend subselect in this statement for getting correct values?
select
    a.fid,
    a.h_id
from
    my_table a
where
    a.h_id = (
    select
        MAX(sub.h_id)
    from
        my_table sub
    where
        sub.FID = a.FID)
group by
    a.fid,
    a.h_id
order by
    a.fid

fid     |h_id    |
--------|--------|
13374976|58280198|
13374976|58372885|
13374976|        |
13377411|58280199|
13377411|66666666|

In order to answer your question, I did the following (fiddle available here):
CREATE TABLE test
(
  fid INTEGER NOT NULL,
  h_id INTEGER,
  CONSTRAINT h_id_uq UNIQUE (h_id)
);

populate the table - added a couple of extra records for testing purposes.
INSERT INTO test VALUES
(13374976, 58280198),
(13374976, 58372885),
(913374976,  NULL  ),
(23, NULL),
(913377411, 58280199),
(13377411, 66666666),
(13377411, 77777777);

And the ran the following query:
SELECT fid, h_id FROM test
WHERE h_id =
(
  SELECT MAX(h_id)
  FROM test
)
UNION
SELECT fid, h_id
FROM test
WHERE h_id IS NULL;

Result:
fid            h_id
23             NULL
13377411       77777777
913374976      NULL

This provides the answer you require - there is an assumption that h_id is UNIQUE or else you'll have to do a GROUP BY fid - but that's a different question.
In future, could you please provide a fiddle with your table structures and your (presumably) sample data - it makes life much easier for those trying to help you and eliminates duplication of effort as well as providing a Single Source of Truth.
Also, although it doesn't make much difference in this case, could you also please provide your PostgreSQL version as a tag or in the question itself - it can be helpful!
p.s. welcome to the forum!


Make Postgres database temporarily read-only (for performing volume snapshots) The PostgreSQL built-in backup mechanism isn't always very suitable. Sometimes, you want to put the application in a quiescent state, because it has external data with which you want to backup at the same time you back up the PG data. But the only way to put the application in a quiescent state is to "lock" the database also. PG lacks a database-wide or cluster-wide locking mechanism. Putting PG into a read-only state would be a piece in the following solution:

Quiesce application data (disable logins)
Quiesce database (by making it read-only)
Perform a PG checkpoint or pg_xlog_switch()
Create a snapshot of the App and Data volumes
Resume the database (make it RW again)
Resume the application
Backup the snapshots

After culling answers elsewhere on the internet, I devised a solution. The other answers were in and of themselves, incomplete. So I am presenting an answer here in hopes it will benefit others.

The Strategy

Disable connections to the database (not the cluster).
Set the database's default_transaction_read_only setting to true.
Terminate the existing connections to that database.
Re-enable (read-only) connections.

Once that is done, you would (in my solution):

Perform the CHECKPOINT (I think this is the safest, but a pg_xlog_switch() would be appropriate for very high-load servers)
Take the volume snapshot
Reverse the previous steps. (But this is tricky!)

Pitfalls

Terminating connections while they are mid-transaction is probably a bad idea. Better to kill idle connections, wait for a few seconds, then kill idle ones, wait a few more, repeat until they are all gone.
At some point, you'll have to kill open/hung queries or abort the backup.
At the start of a transaction of session, Postgresql takes a kind of a snapshot of the process table. You have to reset this snapshot every time you go to check if unwanted processes are still there. See pg_stat_clear_snapshot()
Restoring the read-write state is not so simple. If read-only connections now exist, you must terminate them in order for the new read-write status to take effect. But new connections might arrive while killing existing ones. So again, you must

Disable connections to the database
change default_transaction_read_only status to false
kill the existing connections
Re-enable (r/w) connections to the database

Alternate strategy

Another strategy is to change the permissions on the role used by the application. This can be quite messy and is less general.

For instance, you'd have to revoke/re-grant on not just tables, but sequences, large objects, and probably the schema itself. Further, what exactly is the behavior of existing connections when you change the access? Probably no impact, which means you also need to kill those backends. Finally, let's say the application has read-write access to most tables, but not to others in the schema. You'd have to make sure your re-granting doesn't include those objects as well.

Another possibility is to LOCK all the tables, by querying the catalog and performing a dynamic query. That seemed to perilous for my tastes.

Implementation

Pause_service

The database instance name is 'gitlabhq' and the username of the application is 'gitlab'. Replace it with your own:

  psql -Upostgres  <<'PAUSE_DB'
    -- 1. disable new connections
    alter database gitlabhq_production with allow_connections = off;
    -- 2. Make DB read-only
    alter database gitlabhq set default_transaction_read_only = true;
    -- 3. Inobtrusively but safely terminate current connections
    DO $X$ BEGIN
        -- kill open idle connections, try up to 9x. Last time, kill regardless
        FOR i IN 1..10 LOOP
          PERFORM pg_terminate_backend(pid) from pg_stat_activity where usename = 'gitlab'
            and (i >= 10 OR state in ('idle', 'disabled' ));
          PERFORM pg_stat_clear_snapshot();
          EXIT WHEN NOT EXISTS ( select pid from pg_stat_activity where usename = 'gitlab' );
          RAISE NOTICE 'pg backends still open: sleeping 2 seconds';
          PERFORM pg_sleep(2);
          PERFORM pg_stat_clear_snapshot();
        END LOOP;
        -- send notice if still open connections
        IF EXISTS ( select pid from pg_stat_activity where usename = 'gitlab' ) THEN
            RAISE NOTICE 'Hung backends. Backup might not be 100%% consistent';
        END IF;
    END;$X$;
    -- 4. Allow read-only connections while checkpointing/snapshotting
    alter database gitlabhq with allow_connections = on;
    CHECKPOINT;

Resume

    alter database gitlabhq_production with allow_connections = off;
    alter database gitlabhq set default_transaction_read_only = false;
    SELECT pg_stat_clear_snapshot();
    SELECT pg_terminate_backend(pid) from pg_stat_activity where usename = 'gitlab';
    alter database gitlabhq with allow_connections = on;

There's a possibility that in this last step you will kill long-running read-only/SELECT queries, but in my experience, such long-running queries can last minutes if not hours, and it's acceptable to kill these in order to ensure uptime for everyone else.


How to insert rows for each of the type column? How to insert rows for each of the type column? If there is two type, type 1 and type 2, then I need to insert two rows and also need to change the order and id value for whole table.
current status:
CHOICE Table
id  choice  type    order
1    AA      1       1
2    BB      1       2
3    CC      1       3
4    AAA     2       4
5    BBB     2       5
6    CCC     2       6
7    DDD     2       7

Required updated table:
Now i wan to insert choice "000"  for each type. The updated table will be look like bellow.  How can I achieve this?
updated  CHOICE Table
id  choice  type    order
1    000     1       1
2    AA      1       2
3    BB      1       3
4    CC      1       4
5    000     2       5
6    AAA     2       6
7    BBB     2       7
8    CCC     2       8
9    DDD     2       9

here, id and order column serialized again.
The actual table is too big, so I cannot insert by edit. Please help for this complex query. I have no clue to solve this.

You absolutely do not want to change the id for any row when inserting new data.
Your Primary Key (id) value should be set when each record is created, remain the same throughout the entire lifetime of that record, right up to the point where that record is finally destroyed.
Imagine if banks "renumbered" people's bank accounts every time someone [else] closed their account.  There would be chaos.
If the combination of "choice" and "type" is unique, then that could form a Natural, Composite, Primary Key for this table, removing the need for the id column entirely.
YMMV.
Alternatively, just let your database allocate ids to the new records - their actual values do not matter, just that they are unique.
I do not believe you need the order column either.  From the data you show ...
select
. . .
order by type, choice

... would achieve exactly the same thing, without the need to update anything as data is added or removed.


Number of active connections and remaining connections I would like to get statistics about the peak number of connections over a period of time.

I know the pg_stat_activity view, like select count(*) from pg_stat_activity, but I think this method is not very smart.

Are there other views or tables that can provide the information I need?

This SQL will help you

select max_conn,used,res_for_super,max_conn-used-res_for_super res_for_normal
from
  (select count(*) used from pg_stat_activity) t1,
  (select setting::int res_for_super from pg_settings where name=$$superuser_reserved_connections$$) t2,
  (select setting::int max_conn from pg_settings where name=$$max_connections$$) t3

Result:

max_conn | used | res_for_super | res_for_normal
---------+------+---------------+----------------
  100    |    2 |             3 |             95
(1 row)

You can put this in shell:

#!/bin/bash
for (( c=1; c<=3600; c++ ))
do
     gsql -U pgdba -W pgdba -p 6432 -c "sql" >> /home/pgdba/res_data.log
     sleep 1  # once per second
done

or you can record the results into a table, then execute

postgres=# copy restbl to '/home/pgdba/res.csv' csv header;

to get result csv file.


Designing a database where records can reference different foreign keys I can use PostgreSQL or Microsoft SQL, whichever provides the needed capabilities.

I need help designing a database that has records that can reference FKs in different tables. Given the following fictitious structure:

CREATE TABLE Lists (
  ListID int NOT NULL AUTO_INCREMENT,
  ListName varchar(50) NOT NULL,
  Description varchar(255),
  PRIMARY KEY (ListID)
)

CREATE TABLE PeopleNames (
  PersonNameID int NOT NULL AUTO_INCREMENT,
  GivenName varchar(50) NOT NULL,
  Surname varchar(50) NOT NULL,
  PRIMARY KEY (PersonNameID )
)

CREATE TABLE PetNames (
  PetNameID int NOT NULL AUTO_INCREMENT,
  PetName varchar(50) NOT NULL,
  PRIMARY KEY (PetName)
)

CREATE TABLE ListEntries (
  ListEntryID int NOT NULL AUTO_INCREMENT,
  EntryID int NOT NULL, <-- this could be a PetNameID, PersonNameID, or even ListID, it's not a strictly enforced FK except by application code
  PRIMARY KEY (NameID)
)

Ok, so in this example, the ListEntries table has a column called EntryID. The EntryID could reference the PersonNameID, PetNameID, or even another ListID field. CRUD functionality will be wrapped in an API which will enforce relations.

What's the best way to model this? Should I just create another column in the ListEntries table called Table varchar(50) NOT NULL which references the table that the EntryID relates to?

I know I can use something like Redis or MongoDB, but fundamentally, the data is related, so shouldn't a Relational DB be used?

The database will NOT be updated frequently, but it will be read frequently. Also, could I generate a JSON file as a Materialized View and stuff that into another table for fast reads?

When you are trying to relate a table row to rows in different tables, depending on the circumstances, it is nearly always a case of type/subtype aka class/subclass aka generalization/specialization.  The second of these terms is widely used in object modeling, while the third one is widely used in ER modeling.

In your case, the three subclasses would be PersonNames, PetNames, and Lists.  I'm not sure what to call the superclass.  I also wonder whether your fundamental entities are PersonNames or Persons, and ditto for Pets.

If you deside to model these items as a superclass/subclass case, then I'm going to recommend two design patterns or techniques to you.  They are class table inheritance, and shared primary key.  There are tags for these two techniques over in StackOverflow, and there is a tag for Subtypes here in the dba area.  The questions tagged, and the info under the tags may help you to understand these techniques as they apply to your case.


Will this Database design logic work? I am currently working on structuring out a database in MySQL Workbench to keep track of expenses for Truck drivers that would be turned into an application using Java.

Objective:

Create a report of expenses in a table format
View or Edit past expenses
Display data Yearly report
        Total Company Salary
        Total Gas Expense
        Total Toll Expense
        Total Miscellaneous Expense

Input Needed:

Enter the Company Income earned weekly
Enter the Gas Expense weekly
Enter Toll Expense Weekly
Enter Miscellaneous expense weekly
        Create a Miscellaneous category

Calculation:

Get Personal Salary =
        Company Income - (Gas Expense + Toll Expense + Miscellaneous Expense)

Result:

Display information in a table

Right now the issue is structuring out MySQL tables.
Currently I have:

The portion of the results and calculations will be done in Java but I would like to know if this table structure is correct? I also want the user to input the type of miscellaneous expense.

There are a couple of problems with this design.

Your link between users and expenses is around the wrong way. As it is designed, a single expense record can have multiple users. This should be a single user can have multiple expense records. Remove expense_id from users and add user_id to expenses (with related foreign keys).
You're going to end up with many NULL values in your expenses table and it will be a pain to query for analysis because of this, you will end up having to coalesce fields for any analysis of total expenses rather than individual expense types. Instead of having separate columns for the gas expense, toll expense and misc expense, have a single expense_value field and create an expense_type field linking to an expense_type lookup table that contains gas, toll and miscellaneous as records
Your personal_salary field should not be an attribute of an expense record, it should be an attribute of the user record. Again, you'll be storing this data repeatedly for no reason. Imagine if you were asked to provide a report on personal salaries only - do you want to query thousands of expense records or a few dozen employee records?
As above but with company_income, this should be an attribute of a company record, not an expense record.

NOTE: With points 3 & 4 above, if personal salary and company income are not annual/on-going static values but are instead values for a specific project or contract then these should still not be stored with expenses. Create a new table for Project/Contract, add a foreign key referencing a project/contract to the expense table and do it that way.

Also, consider your application design. Doing aggregations and calculations across small data sets in Java is fine, but if you're talking about producing annual reports and your data set grows large, this processing would probably be best handled by the database engine which can handle this work in a set-based method rather than row-by-row processing as happens in most applications.


Property DefaultLanguageName is not available Error on saving db settings When I open DB settings and try to change any setting it throws an error on save:

  Property DefaultLanguageName is not available for Database
  '[TMS-API555]'. This property may not exist for this object, or may
  not be retrievable due to insufficient access rights.
  (Microsoft.SqlServer.Smo)

It really does show empty language setting

I tried to reset language via TSQL

EXEC sp_configure 'default language', 2 ;
GO
RECONFIGURE ;
GO

  Configuration option 'default language' changed from 3 to 2. Run the
  RECONFIGURE statement to install.

After that it still shows empty language and throws the error on save.

Tried

ALTER AUTHORIZATION ON DATABASE::dbname TO [sa];

didn't work

Facts:

I updated SQL Server 2016 to latest available version 13.0.4474.0
Windows Server 2016
Management Studio 14.0.17199.0
Logged in as sysadmin
If I create a new empty DB - there is no error and everything works

For a contained database, set the default language with ALTER DATABASE. For example:

ALTER DATABASE [TMS-API555] SET DEFAULT_LANGUAGE = english;


How to avoid creating and dropping temp tables in functions? I have been creating some functions lately that run into the "out of shared memory" warning in postgres. I have tried to increase the max_locks_per_transaction, but it still occurs.
As far as i understand from the answer in this post ("PostgreSQL complaining about shared memory, but shared memory seems to be OK") creating and dropping temp tables creates locks which can result in the earlier-mentioned warning.
for example:
create or replace function remove_vertices(par_geom geometry)
returns geometry as $$
DECLARE
cnt int;
BEGIN

drop table if exists invalid_pnts;
create temp table invalid_pnts as
select * from selfintersects(par_geom) ;
-- calls other function. selfintersects(par_geom) returns X rows with 4 columns/attributes,
-- where X corresponds to number of faulty vertices in a postgis geometry
-- can return 0 rows

select count(*) into cnt from invalid_pnts;
if cnt > 0
then -- do something with invalid_pnts table and the input "par_geom"

else
    return par_geom

end $$
language 'plpgsql';

Since this function is supposed to be called per row of a table (i.e. select  remove_vertices(geom) from some_table), the dropping and creation of the temp table can occur as many times as there are rows in a table.
What is an alternative to dropping/creating temp tables, if you need a "table variable" in a function?

Wouldn't a simple loop be enough?
create or replace function remove_vertices(par_geom geometry)
returns geometry as $$
DECLARE
  declare invalid_pnts record;
begin
  for invalid_pnts in select * from selfintersects(par_geom)
  loop
    -- do something with every record
  end loop;

  if not found then
    -- loop was empty
    return par_geom;
  end if;
end;
$$ language plpgsql;

Frequent creation of temporary tables will also bloat the system catalog and therefore slow down each and every query. It is really a bad idea to create and drop temporary tables (say a few tens per second) frequently in PostgreSQL.


Using integrated security over VPN We've got a client that we're going to allow to use one of our applications.  Staff on site regularly connect to our network via VPN.  The .Net application that connects to the SQL Server uses integrated security.  The laptops they use are members of our domain.

The client has requested use of the application as well.  So we gave them a network account and the ability to create a VPN session on our network.  I gave that account a login on the SQL server with the same rights as our own staff.

The problem is that when they try to use the application they get the following error:  The login is from an untrusted domain and cannot be used with Windows authentication.

I'm guessing that since they are using a computer that is not on our domain, it doesn't matter that they are using a login with the VPN that is.  So, what is the best way to address this problem?  I'd rather not switch over to SQL authentication for this small application.

If the client computer isn't on the same domain as the SQL Server, and if there's no trusts set up between the client computer's domain and the SQL Server's domain, you can't use integrated authentication.


How can I truly DELETE data from a SQL Server table - still shows up in notepad in backup file? SQL Server DELETE only marks as DELETED though leaves as data in the data pages.
Data pages are 1024 KB in size and this problem can be eliminated by adding a FILLFACTOR that ensures only 1 record per page. This will however increase the size of your database substantially.

So far I have tried the following without complete success, whereby they will fix the problem a little, reduce some of the DELETED record data from within the pages, though not all.

ALTER INDEX...WITH REBUILD
DBCC SHRINKFILE using NOTRUNCATE can compact pages into
dbcc shrinkdatabase (0,0,NOTRUNCATE)
sp_clean_db_free_space 'DBNAME'

This does not work and the only way I have found to fix this problem is to:

Insert all data into a temp table
DROP the table Reinsert the data back and recreate all indexes,
triggers, constraints etc etc
drop Temp table

Any better ideas out there?

The problems with your approach:

if DELETE compacted the pages immediately, it will cost more I/O than it is worth. The point of RDBMS is to be fast. It will also not help fillfactors and b-tree balancing (indexes)
not mentioned, but similar - if DROPping columns really removed data immediately, it would require a large amount of I/O to completely restructure the table
DBCC Shrinkfile + NOTRUNCATE is already a brutal operation, it shuffles all used pages from the end of the file towards the front.  To have it move data within pages is even more expensive and would be a very bad idea, since you will end up with FILLFACTOR of 100%, which is very rarely a good ideal at all.
sp_clean_db_free_space which you have found is not risk-free, as Book Online clearly states. It is not something they envision sane DBAs to be running often (if even more frequently than once per lifetime), hence the warning "Before you run sp_clean_db_free_space, we recommend that you create a full database backup.", one of few procedures to carry it
If your database is full-logged, the transaction log contains a copy of the data before you removed it, so you need 2 iterations of backup before the log (contained in the backup) will no longer contain the data removed.


Designing a database which can track stock movements I work for a manufacturing company which sells machines to customers which have a control unit installed in them. We need to keep track of the locations of all the Machines after they are sold to the Customers. We need to keep track of all the Control Units whether they are assigned to the Machines, they are sent to Customers as spares, they have being returned back to the Supplier for repair or in stock.
I am trying to setup tables to record of the history of the Locations of Machines which can be assigned to a Customer but then the same machine can be moved/reassigned to another Customer. The Machine has a Serial Number.
Also need to record the history of the Locations of a Control Unit which can be assigned to a Machine which can then be moved/reassigned to another Machine. A Control Unit can also be sent to a Customer as a backup unit in case of failure or it can be sent to the manufacturer for repair. After the Control Unit comes back from the Manufacturers it is put back in stock and at some stage it can be reassigned to another Machine. The Control Unit also has a Serial Number.

I have attached an ER Diagram of what I think might be a suitable solution.
The ERD diagram shows the link to MachineID, CustomerID and ManufacturerID in the TBL_ControlUnitLocations table. The problem I see is that when you select which link you want, the other two will need to be made NULL. When you view the history on a particular Control Unit you will end up with two fields always being blank.

One option is not to show the MachineID, CustomerID and ManufacturerID fields on the main form and only show the Location field. Is it possible to have another form which pops up and gives you the option to choice which one of the MachineID, CustomerID and ManufacturerID fields you wish to link to and then assign the link to the Location field and then close the popup form. So all you see then is the Location field.

How would you implemented the main form and the popup form to record the association?

What you're looking for is a VIEW. Here is a link for more description.

https://msdn.microsoft.com/en-us/library/office/ff836312.aspx

This is what it will look like, and you can create a form around the view.

CREATE VIEW vw_cust_mach_loc
AS
SELECT cust.CustomerName,
   cust.CustomerAddress,
   cust.SuburbTownCity,
   cust.State,
   cust.PostCode,
   cust.Country,
   cust.FirstName,
   cust.SurnameName,
   cust.PhoneNumber,
   cust.MobileNumber,
   cust.EmailAddress,
   cust.Notes,
   m_lo.CustomerReference,
   m_lo.TimeStamp,
   m_lo.StatusID,
   m_lo.OriginalCustomer,
   m_lo.Notes,
   mach.MachineSerialNumber,
   mach.MachineModeID,
   mach.MachineOtherFields,
   mach.Notes
FROM TBL_Customers cust
   INNER JOIN TBL_MachineLocations m_lo
       ON cust.CustomerID = m_lo.CurrentCustomerID
   INNER JOIN TBL_Machines mach
       ON m_lo.MachineID = mach.MachineID;

CREATE VIEW vw_cust_mach_sup_ctrl_units
AS
SELECT cust.CustomerName,
       cust.CustomerAddress,
       cust.SuburbTownCity,
       cust.State,
       cust.PostCode,
       cust.Country,
       cust.FirstName,
       cust.SurnameName,
       cust.PhoneNumber,
       cust.MobileNumber,
       cust.EmailAddress,
       cust.Notes,
       mach.MachineSerialNumber,
       mach.MachineModeID,
       mach.MachineOtherFields,
       mach.Notes,
       culs.Location,
       culs.Reference,
       culs.TimeStamp,
       culs.OriginalMachine,
       culs.OtherLocationID,
       culs.StatusID,
       culs.Notes,
       supl.SupplierName,
       supl.ContactFirstName,
       supl.ContactLastName,
       supl.ContactTitle,
       supl.Address,
       supl.City,
       supl.Region,
       supl.PostalCode,
       supl.Country,
       supl.PhoneNumber,
       supl.MobileNumber,
       supl.Fax,
       supl.[E-mail Address],
       supl.HomePage,
       supl.Notes,
       cuts.ControlUnitSerielNumber,
       cuts.ControlUnitBarCodeID,
       cuts.ControlUnitStatusID,
       cuts.TimeStamp,
       cuts.Notes
FROM TBL_Customers cust
     INNER JOIN TBL_ControlUnitLocations culs
         ON cust.CustomersID = culs.CustomerID
     INNER JOIN TBL_Machines mach
         ON culs.MachineID = mach.MachineID
     INNER JOIN TBL_Suppliers supl
         ON culs.ManufactureID = supl.SupplierID
     INNER JOIN TBL_ControlUnits cuts
         ON culs.ControlUnitID = cuts.ControlUnitIDMachineID;

You can select from it like a table.

SELECT * FROM vw_cust_mach_loc;
SELECT * FROM vw_cust_mach_sup_ctrl_units;

Example using specific columns instead of *:

SELECT
    Location,
    Reference,
    TimeStamp,
    OriginalMachine,
    OtherLocationID,
    StatusID,
    Notes,
    SupplierName,
    ContactFirstName,
    ContactLastName,
    ContactTitle,
    Address,
    City,
    Region,
    PostalCode,
    Country,
    PhoneNumber,
    MobileNumber,
    Fax,
    [E-mail Address],
    HomePage,
    Notes,
    ControlUnitSerielNumber,
    ControlUnitBarCodeID,
    ControlUnitStatusID,
    TimeStamp,
    Notes
FROM vw_cust_mach_sup_ctrl_units


How to download Oracle Database 11g release 2 for Mac OS I have been trying to download Oracle Database 11g release 2 for mac os but in the given link by oracle I can only see Linux x86  and Linux x86-64. Is this ok to use this Linux file for installing oracle database in mac

Oracle 11g R2 Database Release 2 only support Windows, Linux, and Solaris. So if you want to using Oracle 11g R2 on a Mac OS you will need to using something like Virtual Box or VMware Fusion. You can read more about installing Oracle 11g R2 on these operating system here.


Mysql innodb_log_buffer_size change I'm new to Mysql, and i'm reading this in a book: "A large log buffer allows large transactions to run without a need to write the log to disk before the transactions commit. Thus, if you have big transactions, making the log buffer larger saves disk I/O."

Ok...Perfect.. so i'm going to issue this statement: set global innodb_log_buffer_size=xxx;
Mysql reply to me with: "ERROR 1238 (HY000): Variable 'innodb_log_buffer_size' is a read only variable"

So, How can i make it bigger in case i have big transaction, and need to increase the  innodb_log_buffer_size?

I use mysql 5.7 on Windows.

Thank you.

The files are created when you first install MySQL.  It is clumsy to change the size after that.  In particular, the size cannot be changed dynamically.

How big is the setting now?  How many rows with the one transaction be affecting?  It may not be worth it.

If you do choose to make a change; see How to safely change MySQL innodb variable 'innodb_log_file_size'?
Be sure to read the Comments, too.

Not Dynamic

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_log_file_size says that innodb_log_file_size is not dynamic, meaning that your SET cannot work.  You can change the value in my.cnf, but then you have the issues discussed above.  A note from the 5.6.8 Changelog:

  On startup, MySQL would not start if there was a mismatch between the
  value of the innodb_log_file_size configuration option and the actual
  size of the ib_logfile* files that make up the redo log. This behavior
  required manually removing the redo log files after changing the value
  of innodb_log_file_size. The fix causes MySQL to write all dirty pages
  to disk and re-create the redo log files during startup if it detects
  a size mismatch. (Bug #14596550)


MS Access with MySQL My current team of 20 people use an Access database front end on their desktop. The backend of the access database is on a network drive. I have been asked to create a Access database front end with MySQL as the back end. I have installed the MySQL workbench and the ODBC connector on my computer. I have created the schema and tables and I have connected the front end of the database connected to the MySQL table I created in workbench.

My questions are:

How do I deploy this for the team to use. I believe I can move the front end of the Access database to the network drive and the team can copy it to their desktop. But what do I do about the MySQL backend? Where should it be placed?
Does the team need to have the ODBC connector installed in their computers?
Should I move the MySQL workbench to the network drive?

PS: I am a new developer and just learning about databases and I am only familiar with Access databases.

Usually, when you want to keep the Access front end, you migrate from Access DB to SQL Server (Express).

  But what do I do about the MySQL backend? Where should it be placed?

Database servers are usually located on a dedicated "server".  Now days, that server can be Virtualized.  Many regulations require limited physical access to that server (eg physically placed in a locked "server room").

  Should I move the mysql workbench to the network drive?

It looks like MySQL Workbench can be used as an Admin tool.  Only developers and DBAs need it installed.  Placing it on a network drive may not be the best method when you are in a disaster situation.

  does the team need to have the ODBC connector installed in their computers?

Everyone using the Access front-end will probably need the ODBC driver for the backend (DB Server).  This can make deployment (and maintenance) a small nightmare.

  how do I deploy this for the team to use.

I don't know.

  PS: I am a new developer and just learning about databases

The first commandment of a Database Administrator (DBA) is Thou shall not lose data.  Make backups and test them on a regular basis.

Work with the Business to find out how frequently you need to do backup, how often you should test, and how long to keep the backups.  There are Financial commitments that need to be made and probably some Legal ones too.  These decisions are "above your pay grade".

Try to document everything.  "Security" should be one of the first. (Who can do What to Which pieces of data). This information is usually broken down into ROLEs.

Backup/Recovery procedures will also need to be well documented.  This includes the Business Requirements gather from above.

Use a Code Repository to store your CREATE scripts, etc.


Is there any scenario where the existence of one or more Indexes in a table can prejudice it? The title sums it up.

I've learned and always heard that indexes in tables improve CRUD operations. A developer that I met last weekend told me that he does not like Indexes because they are bad - yes, "bad" does not clarify anything but we did not had time to discuss it further (we were at a party).

Anyway, maybe because my lack of experience, I do not know of a scenario where Indexes can cause troubles during CRUD operations, but maybe there are a few out there. I'm asking this question to know if there are any...

Well, I think you have some mixed concepts:

An index improves performance of READ OPERATIONS ( those of SELECT ) while increase the processing time of INSERT/UPDATE OPERATIONS ( So they don't improve all CRUD operations, as you've heard ). As each time you insert a new row, you should update the index, if you have too much indexes you are increasing the time of insertions, and sometimes also the updates ( if the update involves something indexed.
An index uses space, a lot of space if you have a lot of rows.
It's not a problem for the system to know which is the best index to use, I think this is not a real performance killer, but you should look for redundant indexes, as they're using space and time of inserts / updates.

For this, you should know how your DB engine works with indexes, in MySQL if you have an index over name, surname fields and other over name, this latter is redundant, as it's included in the first one ( because it appears in the same order, surname only is not included ), as an example of redundant indexes.

Also, you should test how your DB is going to interpret the query and which indexes are going to be used ( in MySQL, you can use Explain... and the query you are testing )
Finally, indexes are one of the most important features of databases, indexing can't be 'bad' by itself, and normally the problem appears when you forget to add some particular index, and not the excess of indexes, but it could happen.


Row padding to get more performance I have read a document (Designing Highly Scalable OLTP Systems) that describe a trick how to get more performance:

  Because rows are small (many fit a page) multiple locks may compete for one PAGELATCH.
  We can ”waste” a bit of space to get more performance. Solution: Pad
  rows with CHAR column to make each row take a full page.

Does anyone use this approach? What results have you got?

Thank you.

ADDED:

Warning. The linked article is not a guide to action. Thomas Kejser describes tests on terabyte-sized databases, no more.

I've only briefly scanned though the document but my first thought was "oh, a SQLCAT publication". These guys push out some fantastic material but much of their guidance is derived from the very extreme end of pushing the boundaries.

You can learn a great deal from reading about SQL Server behaviour in multi-million $$$ implementations but very little of it applies to typical installations. This particular document is the result of testing a 10k/tps, 256 core, 500 spindle system.

In the real world where you aren't pushing SQL Server internals beyond their anticipated limits, these are interesting diversions, not recommendations.

Your question is perhaps somewhat dangerous. A casual passer by may read your question, briefly scan the linked document and conclude that padding rows to fill a page is a good idea.

  If I have 256 cores and 500 disks delivering 18GB/sec but I'm seeing
  PAGELATCH_EX waits for my highly specialised workload xyz, will I
  increase throughput by padding rows to fill a page?

Yes. As Thomas Kejser has demonstrated.

  Should we all be doing the same?

Absolutely not!


Configured identity seed starting value on database level Is there an option in sql server to configure the default starting value(seed) of an identity column on a database level .

I want that by default, every table that created with an identity column ,the first  value will start from 3000 . And not from 1 .
I know how to do this in a table level. But is it possible in a database level?

10x

No it is not possible at a database level.

IDENTITY is not a data type, It is a table's column definition.


Cassandra security - private IPs with some VPN solution or public IPs and open ports? there are many different solutions to secure a cassandra cluster. One of them is to enter public IPs and open the 7000/9042 port on each sever.

Another one is to have everything in a private network with private IPs and connect the nodes with some kind of VPN solution. I have OPNsense with Wireguard for this, but I'm wondering if this makes any problems in production.

What are you guys using to prevent attacks on public ports?

Thank you so much.

If you care about your data, do not EVER put a database server on a public network. There is no preventing attacks on public ports. Databases are secured from most direct attacks by placing them behind firewalls, and using web servers and application servers (also protected by firewalls and load balancers) to reverse proxy user requests for information. No one should ever connect directly to a database or its server directly over the Internet. A database server or cluster, directly exposed on a public network, is begging to be hacked and its contents stolen.

https://www.vpnmentor.com/blog/how-to-secure-website-database/
https://www.esecurityplanet.com/network-security/6-database-security-best-practices.html
https://www.slideshare.net/DataStax/securing-cassandra-for-compliance


Profiler: Only want to see Provider Errors I'm replaying a trace in Profiler, and I'd like to only see the Provider Errors.  Granted, there are ~30,000 of them but I'm not concerned at the moment about row update conflicts, foreign key or unique index issues.

Currently, I'm output the trace from the replay to file.  I've tried creating a table from the result trace file, but either I'm missing something or the Provider Errors are not being imported into the table:

SELECT *
  INTO MyTraceTemp
  FROM ::fn_trace_gettable('c:\x\MyTrace.trc', default)

This is using Profiler against SQL Server 2008R2, if that matters.

Thanks Aaron - I confirmed the same number of rows were there between the trace dumped to a table, and what was showing in Profiler.

Then, I used the following WHERE to get the records I wanted to see:

WHERE CAST(textdata AS VARCHAR(MAX)) LIKE '%(Code%'

...because all the Provider errors stated the code for the error.


User can't stop Oracle listener (TNS-01190) I have Oracle 10g installed on RedHat Enterprise Linux 5.

I have 2 users that use Oracle:

uid=501(user1) gid=502(group1) groupes=500(oinstall),501(dba),502(group1)

and

uid=500(oracle) gid=500(oinstall) groupes=500(oinstall),501(dba),502(group1)

When a user tries to stop a listener that was started by the other user, I get this error:

TNS-01190: The user is not authorized to execute the requested listener command

How can I allow both users to stop the listener, regardless of who started it?

The description for the message you're seeing is:

  Cause: Most of the listener administrative commands are only intended
  to be issued by privileged users, for example DBAs or system
  administrators. If the listener password is not set, then the listener
  only accepts administrative requests from LSNRCTL running with the
  same OS credentials, or running as a local administrator (also
  referred to as super user).

  Action: If an authorized user is attempting the command, then make
  sure that LSNRCTL is executed with the same OS user credentials as the
  running listener, or as a local administrator.

Unless you want to get into setuid wrappers, you need to have a password set for your listener. There's a good summary of how to set that up here, if you haven't already done so.

Then if you start the listener as normal as oracle, you can shut it down as user1 by going into the interactive mode of the controller, enter command 'set password', and enter the password for the listener:

$ lsnrctl

LSNRCTL for Solaris: Version 11.2.0.2.0 - Production on 12-NOV-2012 14:55:29

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> stop
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
TNS-01190: The user is not authorized to execute the requested listener command
LSNRCTL> set password
Password: <secret>
The command completed successfully
LSNRCTL> stop
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
The command completed successfully
LSNRCTL>

... and it should shut down. I know this is a different O/S and version but the principle is the same. And the reverse is true; if you start it as user1, you need to set password to be able to shut it down as oracle.

However, this is deprecated from 11gR2. The manual mentons using ssh or OEM, but you could also just make user1 switch to the oracle account to start and stop the listener via su. Obviously that gives them full DBA access, it isn't restricted to the listener, which is the case for ssh too.

I'm not sure why you'd want anyone you didn't trust to administer the database to be able to mess with the listener, or why you need to shut it down and start it up often enough for this to be an issue.


How to specify with a parameter a field to extract from a RECORD variable Here's a table:

CREATE TABLE t_heights (id INT, height REAL);
INSERT INTO t_heights VALUES (1,53.63), (2,45.19), (3,47.06);

Reading through the table rows with a FOR...LOOP and a RECORD type variable like this works:

CREATE OR REPLACE FUNCTION fnct_row_by_row (input_table regclass)
RETURNS VOID
LANGUAGE plpgsql
AS $$

DECLARE
    current_row RECORD;

BEGIN
FOR current_row IN EXECUTE 'SELECT * FROM '||input_table
        LOOP
            RAISE NOTICE 'field value: %',current_row.height; -- *<<< hardcoded field name*
        END LOOP;
END;
$$
;

Results:

SELECT fnct_row_by_row ('t_heights') ;
NOTICE:  field value: 53.63
NOTICE:  field value: 45.19
NOTICE:  field value: 47.06

However, the field extracted from the RECORD variable needs to have its name hardcoded.

How to specify dynamically the field to be extracted?

The following generates an error:

CREATE OR REPLACE FUNCTION fnct_row_by_row2 (input_table regclass, input_field_name TEXT)
RETURNS VOID
LANGUAGE plpgsql
AS $$

DECLARE
    current_row RECORD;

BEGIN

FOR current_row IN EXECUTE 'SELECT * FROM '||input_table
        LOOP
            RAISE NOTICE 'specified field: %',current_row.input_field_name; -- *<<< field name from parameter*
        END LOOP;
END;
$$
;

Execution:

SELECT fnct_row_by_row2 ('t_heights','height') ;
ERROR:  record "current_row" has no field "input_field_name"
CONTEXTE : SQL statement "SELECT current_row.input_field_name"
PL/pgSQL function fnct_row_by_row2(regclass,text) line 10 at RAISE

Just do

        RAISE NOTICE 'specified field: %', input_field_name;

It's already a variable at the top. That will work if you want the column/field name. If you want the column's value you'll need to use something that provides a dynamic accessor to a row type. For that, you need.

jsonb (as mentioned in the comments by Abelisto)
hstore

That said, This whole thing is a really bad idea. When possible, you should just create a function that processes a scalar and run it over the result set.

CREATE FUNCTION f(a text)
RETURNS text
AS $$
  SELECT 'foo' + a;
$$ LANGUAGE sql;

SELECT f(x) FROM t;

Rather than doing something that's more complex and statically types the table name burried within.

CREATE FUNCTION fThatOpensACursor(col text)
RETURNS SETOF text
AS $$
  SELECT to_jsonb(t)->>col
  FROM statically_typed_table AS t;
$$ LANGUAGE sql;

SELECT fThatOpensACursor('t.myCol');


Return list of columns in user-provided subquery I am trying to fill a ComboBox of all columns from results of a query. That query is submitted by the user on the form, so the program needs to run the query then have a procedure run that extracts just the column names. I know the following works for a typical table:

SELECT
    c.name 'Column Name'
FROM
    sys.columns c
WHERE
    c.object_id = OBJECT_ID('Vendor')

What would be the best way to insert the user query (select * from vendor) as a subselect and get the same result?

Edit:
I could do(as Thomas Stringer noted):

for (int i=0; i < dataSet.Tables["Vendor"].Columns.Count; i++ )
{
    ColumnChoices.Items.Add(dataSet.Tables["Vendor"].Columns[i].ColumnName.ToString());
}

...though that isn't very efficient because it requires loading all of the data into the dataset when I really only want the columns.

Just pull the DataTable object(s) and enumerate the DataColumnCollection.  Here is some sample code to show how you can enumerate the collection and dump the column names:

DataTable queryResults = // get your data

foreach (DataColumn column in queryResults.Columns)
    Console.WriteLine("Column Name :: {0}", column.ColumnName);

Of course this is an extremely rudimentary illustration, but it shows what you need:


How to get all tables in informix database and their sizes in GB? I can get a crude estimate of table sizes with

SELECT tabname, (rowsize*nrows)/1024/1024/1024 AS tabsize
FROM informix.systables
ORDER BY tabsize DESC

Is there a way to get a more accurate measurement of the size of the tables?

There is a lot of nuances to this question.  Like do you want the size of the total number of pages currently allocated for use (so this would include pages available to be used by the table...so pages reserved for use by only this table, but that currently don't have any data on them), or do you only want the size of the currently used pages?  Do you want to include the size of any indexes that would be defined on the the table?

So here is a query that could be used that would get the size of any non-system catalog table in the current database connected to that is the size of all the space reserved for the table, not including detached indexes:

    select a.tabname, (b.nptotal * b.pagesize / 1024 / 1024 / 1024) as tabsize
    from sysmaster:systabnames a, sysmaster:sysptnhdr b, systables c
    where c.tabname = a.tabname and
    a.partnum = b.partnum and
    c.tabid > 99;

Sample output from my tiny test system (note that t3 is a fragmented table where 1 fragment has no data, so you could use sum and group by if you wanted to report at a table level rather then fragment level if the your schema included fragmented tables)

    tabname  t1
    tabsize  1.5258789063e-05

    tabname  t2
    tabsize  1.5258789063e-05

    tabname  t3
    tabsize  0.00000000000000

    tabname  t3
    tabsize  1.5258789063e-05

If you change the query to the following, it would change the results to only count the pages that are currently used for the table, not all the space that is reserved for use:

    select a.tabname, (b.npused * b.pagesize / 1024 / 1024 / 1024) as tabsize
    from sysmaster:systabnames a, sysmaster:sysptnhdr b, systables c
    where c.tabname = a.tabname and
    a.partnum = b.partnum and
    c.tabid > 99;

If you want the sizes of detached indexes to be included in the table sizes, you would then have to include sysfragments into the query as well (would probably be a union of 1 form of the above 2 queries along with something that then got the index partition information by also then including the sysfragments into the join as well)


Some data is missing in the table of SQL I have given a description column datatype as NVARCHAR(MAX) but when I add the data it shows me only half  data. Not full data. For Example:-
If I have two para like below:-

As the saying goes “Seeing is Believing”, based on this premise Aditya Jyot was founded in 2005 to illuminate the lives of the less privileged people suffering ailments related to visual impairment. The organisation aims at redressing the suffering of low income groups, marginalised population living in the rural and urban slums and tribal pockets through free eye camps and treatment.
The foundation in association with us (RBL Bank) aims at spreading awareness and implementing preventive healthcare services on Retinopathy in diabetic patients causing blindness through a well-equipped outreach centre in the urban slums of Dharavi, Mumbai touching about 6 lakh beneficiaries. Thus, contributing towards creating healthier communities

I will only see the data till Treatment. I am not able to see the data which is ahead. Please suggest

This is the default setting SQL Server Management Studio, Navigate on Menu button as follows

If you are using Result to Grid:

Tools --> Options --> Expand Query Results --> Expand SQL Server -- > select result to Grid

then at the right side you will see Maximum number of characters Retrieved and Non XML Data with the Text box, enter 65535(This is maximum possible value) then click on OK.

If you are using result to Text:

Tools --> Options --> Expand Query Results --> Expand SQL Server -- > select result to Text

then at the right side you will see Maximum number of characters displayed in each Column and the Text box, enter 8192 (This is maximum possible value) then click on OK.

Note:
You need to open new Window for the changes to take effect, it will not be applicable for opened windows.


Reset a sequence every year Every time a new row is added to the table below, I want the sequence (Import Permit No) to be increased by 1....20160001, 20160002, etc and reset to 20170001, 20170002, etc in a new year.

CREATE TABLE [dbo].[tblPermits](
[ImportPermitID] [int] IDENTITY(1,1) NOT NULL,
[ImportPermitNo] [nchar](20) NULL,
[ImporterName] [int] NULL,
[Province] [varchar](50) NULL,
[LodgementDate] [datetime] NULL,
[PortofEntry] [int] NOT NULL,
[EstDateofArrival] [datetime] NULL,
[ConsignmentInvoicePONo] [varchar](50) NULL,
[OtherImportConditions] [varchar](400) NULL,
[Supplier] [int] NOT NULL,
[SupplierCountry] [varchar](50) NULL,
[CountryofOrigion] [int] NOT NULL,
CONSTRAINT [PK_tblPermits] PRIMARY KEY CLUSTERED
(
[ImportPermitID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Currently what I have is a trigger as shown below.

ALTER TRIGGER [dbo].[trgPermitsInsertImportPermitNo]
ON [dbo].[tblPermits] FOR INSERT
AS
UPDATE dbo.tblPermits
SET ImportPermitNo = 'IP' + CAST(YEAR(i.LodgementDate) AS CHAR(4)) + RIGHT('000000' + CAST(i.ImportPermitID AS VARCHAR(6)), 6)
FROM dbo.tblPermits p
INNER JOIN INSERTED i ON p.ImportPermitID = i.ImportPermitID

But I couldn't get the sequence in the trigger above to reset to 0001 in a new year.

How do I modify the trigger to reset the sequence in a new year?

Rows can be deleted from this table. Other rows would not get renumbered in that case.
The number of new rows in a year is expected to be less than 5000, but in any case the number of prefix zeros could be expanded in the final design to accommodate a larger range.
Lodgement Date is a normal datetime field to be entered by the user.
All entries are made immediately when received from the client. Dates from previous years turning up late will not be an issue.
Duplicate ImportPermitNo values are not allowed.

In SQL Server 2012 or later, I would implement this using sequence objects.

For SQL Server 2008 R2, my replacement for that missing feature is Sequence Tables. In this case, there would be a key in the master sequence table for each year, for example:

CREATE TABLE dbo.SequenceTable
(
    sequence_name   nvarchar(20) NOT NULL,
    next_value      integer NOT NULL,

    CONSTRAINT [PK dbo.SequenceTable sequence_name]
    PRIMARY KEY CLUSTERED (sequence_name),
);
GO
INSERT dbo.SequenceTable
    (sequence_name, next_value)
VALUES
    (N'PermitIDs for 2016', 1),
    (N'PermitIDs for 2017', 1),
    (N'PermitIDs for 2018', 1);

The standard allocation stored procedure to robustly allocate a key or range of keys from a sequence is:

CREATE PROCEDURE dbo.Allocate_TSQL

    @SequenceName       nvarchar(20),   -- The name of the sequence to allocate keys from
    @RangeSize          integer = 1,    -- The number of keys to allocate
    @FirstAllocated     integer OUTPUT  -- The first key allocated (output)

AS
BEGIN

    SET XACT_ABORT ON;  -- Most errors will abort the batch
    SET NOCOUNT ON;     -- Supress 'x row(s) affected' messages
    SET ROWCOUNT 0;     -- Reset rowcount

    -- Validate the range size requested
    IF (@RangeSize IS NULL OR @RangeSize < 1)
    BEGIN
        RAISERROR('@RangeSize must be a positive integer (supplied value = %i)', 16, 1, @RangeSize);
        RETURN  999;
    END;

    -- Initialize the output parameter
    SET @FirstAllocated = NULL;

    -- Update the row associated with @SequenceName, returning the
    -- current value, and then incrementing it by @RangeSize
    UPDATE dbo.SequenceTable WITH (READCOMMITTEDLOCK)
    SET @FirstAllocated = next_value,
        next_value = next_value + @RangeSize
    WHERE sequence_name = @SequenceName;

    -- If @Allocated has a non-NULL value, we know we successfully updated a row
    RETURN CASE WHEN (@FirstAllocated IS NOT NULL) THEN 0 ELSE -999 END;
END;

Then, given a simplified version of the table in the question:

CREATE TABLE dbo.ImportPermits
(
    ImportPermitID integer IDENTITY (1, 1)
        CONSTRAINT [PK dbo.ImportPermits ImportPermitID]
        PRIMARY KEY CLUSTERED,
    ImportPermitNo nchar(12) NULL,
    LodgementDate datetime NULL
);

We can assign sequence numbers per year using the following trigger:

CREATE TRIGGER ImportPermitsImportPermitNo
ON dbo.ImportPermits
AFTER INSERT AS
BEGIN
    IF @@ROWCOUNT = 0 RETURN;   -- Return immediately if no rows affected
    SET XACT_ABORT, NOCOUNT ON; -- Most errors abort the batch; no row count messages
    SET ROWCOUNT 0;             -- Ensure all rows are visible (local to trigger)

    DECLARE
        @rc integer,
        @FirstAllocated integer,
        @RowCount integer,
        @Years integer,
        @SeqName nvarchar(20);

    -- Count rows and distinct lodgement years in the insert set
    SELECT
        @RowCount = COUNT(*),
        @Years = COUNT(DISTINCT(YEAR(INS.LodgementDate))),
        @SeqName = N'PermitIDs for ' +
            CONVERT(nchar(4), MIN(YEAR(INS.LodgementDate)))
    FROM Inserted AS INS;

    -- Check for multiple lodgement years (not implemented)
    IF @Years > 1
    BEGIN
        RAISERROR('Multiple LodgementDate years are not supported.', 16, 1);
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        RETURN;
    END;

    -- Allocate the range of sequence numbers we will need
    EXECUTE @rc = dbo.Allocate_TSQL
        @SequenceName = @SeqName,
        @RangeSize = @RowCount,
        @FirstAllocated = @FirstAllocated OUTPUT;

    IF @rc <> 0
    BEGIN
        RAISERROR('Sequence allocation failed.', 16, 1);
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        RETURN;
    END;

    -- Assign ImportPermitNo values using the sequence numbers allocated
    WITH Sequenced AS
    (
        SELECT
            IP.ImportPermitNo,
            IP.LodgementDate,
            Seq =
                @FirstAllocated - 1 +
                ROW_NUMBER() OVER (
                    ORDER BY IP.LodgementDate ASC)
        FROM Inserted AS INS
        JOIN dbo.ImportPermits AS IP
            ON IP.ImportPermitID = INS.ImportPermitID
    )
    UPDATE Sequenced
    SET Sequenced.ImportPermitNo =
        N'IP' +
        CONVERT(nchar(4), YEAR(Sequenced.LodgementDate)) +
        RIGHT(N'000000' + CONVERT(nvarchar(11), Sequenced.Seq), 6);
END;

For brevity, the trigger is limited to inserting row(s) from a single year only, but it is not difficult to extend the logic.

Demo: db<>fiddle


Substitute names for IDs stored in two columns referencing the same table I have 2 tables:

table1:

id  no  name
1   501  john
2   354  sam
3   201  anderson

and
table2:

status  type      createdby  approvedby
1       recharge     2         1
1       topup        3         1

I need a query that would replace the createdby and approvedby references with corresponding names from table1, like this:

type           status           createdby         approvedby
recharge        1                 sam                john
topup           1                anderson            john

This is my attempted query:

SELECT `table2`.*, `tabl1`.`no`
FROM `table2`
JOIN `tabl1` ON `table2`.`createdby` =`table1`.`id`
JOIN `table1` `myTable` ON `table2`.`approvedby` =`orgTable`.`id`

But I am getting only createdby name, not approvedby name.

How can I solve this?

You were almost there.

First of all, your query contains what seems to be typos: there are two references to `tabl1` in it that should most likely be `table1` instead, and the `orgTable` alias should probably be `myTable` (or the other way round; my query below assumes the former).

Now with that out of the way, you can get the other name by referencing it via the `myTable` alias that you assign to the second instance of table1:

SELECT `table2`.`type`,
       `table2`.`status`,
       `table1`.`name`  AS createdBy,
       `myTable`.`name` AS approvedBy
FROM `table2`
JOIN `table1` ON `table2`.`createdby` = `table1`.`id`
JOIN `table1` AS `myTable` ON `table2`.`approvedby` = `myTable`.`id`;

Let me also note that your query will arguably be clearer if you use aliases for both instances of table1, as well as avoid excessive use of backticks:

SELECT table2.type,
       table2.status,
       creator.name  AS createdBy,
       approver.name AS approvedBy
FROM table2
JOIN table1 AS creator  ON table2.createdby  = creator.id
JOIN table1 AS approver ON table2.approvedby = approver.id;


What is more efficient: text (765) or varchar (765) to store a freeform searchable description? EDIT : To paraphrase quesiton - at what point do I switch from varchar to text and back?

A little background on the question. Let's say we have sales_orders table and we want to be able to keep a note field on the order with customer's or sales reps comments. It needs to be searchable LIKE "%goldfinger's gun%" We know we are not going to have "War & Peace" stored in the field, but at same time it should be big enough to handle a sentence or two.

So which option is:

faster for reads?
better at storing data?
better for full text searching?
indexing?

Assume MySQL(InnoDB) or PostgreSQL use case

As far as Postgres is concerned, there is no difference in performance. And there is no length modifier for text to begin with, just (optionally) for varchar.

Unless you need to enforce a certain length, just use text or varchar.

Would index lookup be noticeably faster with char vs varchar when all values are 36 chars

To support pattern matching with LIKE, I suggest a trigram index. Details:

How to create an index to speed up an aggregate LIKE query on an expression?

There is also full-text-search with its own set of operators, building on dictionaries. Here's an overview of pattern-matching options in Postgres:

Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL


How can Inner join be optimize? I have a table where there are in some cases twin records.

In the table, I have the following columns:

id (identity column)
action_id
session_id
row_id
timestamp
...and more records.

I need to find a twin record for a specific id.

A twin record for action_id=11 is when action_id=12 and session_id, row_id and timestamp are the same.

Table Defintion

Assuming my table created as follows. (no additional index)

CREATE TABLE table_name (
      [id]         BIGINT IDENTITY(1,1) PRIMARY KEY,
      [session_id] BIGINT   default @@SPID,
      [row_id]     BIGINT   default 0,
      [timestamp]  DATETIME default CURRENT_TIMESTAMP,
      [action_id] SMALLINT NOT NULL,
      [value1]     NVARCHAR(max) NULL,
      [value2]     NVARCHAR(max) NULL,
      [value3]     NVARCHAR(max) NULL)

Select Statement

My current query is:

SELECT d.*
FROM [table_name] d INNER JOIN [table_name] i
   ON d.[session_id] = i.[session_id]
   AND d.[row_id] = i.[row_id]
   AND d.[timestamp] = i.[timestamp]
WHERE  d.[action_id] = 12
   AND i.[action_id] = 11
   AND i.[id] = X -- where X is the input

Questions

I want the query to be as fast as it can, so my questions are:

Is there a difference if I move the where condition in the joins?

SELECT d.*
    FROM [table_name] d INNER JOIN [table_name] i
       ON d.[session_id] = i.[session_id]
       AND d.[row_id] = i.[row_id]
       AND d.[timestamp] = i.[timestamp]
       AND d.[action_id] = 12
       AND i.[action_id] = 11
    WHERE  i.[id] = X -- where X is the input

Should I add an index for the timestamp, row_id and session_id columns?
What will happen first, JOIN the tables, or filter results by WHERE?
is the following query is better?

SELECT d.* FROM (
   (SELECT * FROM [table_name] WHERE [id] = X AND action_id = 11) i
   INNER JOIN
   (SELECT * FROM [table_name] WHERE [id] > X AND action_id = 12) d
       ON  d.[session_id] = i.[session_id]
       AND d.[row_id] = i.[row_id]
       AND d.[timestamp] = i.[timestamp]
 )

What else can I do to make it run faster?

Is there a difference if I put the where condition at the join
  section?

In your case, NO. Optimizer is smart enough to understand.

Where Clause further put filter on what is produce by JOIN.

INNER JOIN :Here WHERE clause do not have much to to put filter.It has smaller resultset to apply filter.  In fact, in case of, INNER JOIN, it do not matter where you put condition in the JOIN or WHERE. Optimizer is smart enough to understand.There is no performance difference .

OUTER JOIN: Here WHERE clause  have much larger resultset to apply filter.
Since Where clause have much larger resultset to filter out, it make sense to Filter as much as possible in JOIN ON condition,so that smaller resultset is produce.

If where condition is applied in Right table ,then such LEFT JOIN is equivalent to INNER JOIN

Though in most cases of OUTER JOIN, optimizer is smart enough to understand and convert the WHERE to ON internally.

It depend upon example to example, specially in complex query involving large volume of data.It is always wise to check the query plan and decide WHERE condition accordingly.

You should use predicate column in ON ,which logically relate the joining table.

If it is filter condition then use it in Where clause.

  Should I add an index for the timestamp, row_id, session_id?

Yes. Create composite Non clustered index.Choice of Clustered index([id]) is perfect.

Create NonClustered Index NCI_Table_Action_Row_Session_Timestamp
on table_name(action_id,row_id,session_id,Timestamp)
GO

Notice the order of columns. The narrow column should be in LEFT most.
There is no point in Covering index as nvarchar(max) are not use in covering index.

  What else can I do to make it run faster?

I think instead of INNER use EXISTS.Don't use *

SELECT d.[id]     ,
      [session_id] ,
      [row_id]     ,
      [timestamp]  ,
      [action_id] ,
      [value1]     ,
      [value2]     ,
      [value3]
FROM [table_name] d
where(select 1 from [table_name] i
   where  i.[id] = X -- where X is the input
  AND i.[action_id] = 11 ---- where 11 is the input
  AND  d.[action_id] = i.[action_id]+1 --12 ---- if 12 is  input then it is more good
  AND d.[session_id] = i.[session_id]
   AND d.[row_id] = i.[row_id]
   AND d.[timestamp] = i.[timestamp]

   )


What to do when archive logs are lost/corrupted that are not backed up All recently generated archive logs were corrupted but datafiles are fine.What if recently generated archives corrupted and the archives backup is not available, so what would be the strategy to resolve the issue and is there any possible way to rectify the archive log corruption.

As soon as your recovery thread is interrupted, making it impossible to recover from an earlier backup to the current time, the remedy is to take a backup before something more serious happens.

Next investigate how the archives became corrupted and solve that.

Backups are of no importance, important is the ability to recover from a disaster and for that backups or physical standby databases can be very convenient to have.