Feature tutorial - Streaming for GenAI and Chat

Introduction

Streaming allows an AI chat to display responses incrementally as they are generated, instead of waiting for the entire response to be ready. This gives users immediate feedback and creates a faster, more interactive chat experience.

This tutorial assumes that you already know how the Chat and GenAI components work. It explains how to connect them using client sync so that streamed GenAI responses can be shown in the chat as they arrive.

Contents of this tutorial

In this tutorial, we will implement a GenAI component that streams its response and a Chat component that will update accordingly. The main idea is that:

  1. When a user sends a message in the chat, we store the user message and an empty assistant message which will be updated during streaming.
  2. Start the GenAI menu item through a forward link in the "Chat action" result set with RunInBackground = 1.
  3. The chat menu item is configured with a client sync entity, which the GenAI procedure will reference. This automatically raises client sync events.
  4. Return the updated message from the Chat's Client sync action to display the new text.
  5. Mark the message as no longer streaming when the GenAI call has finished.

Prerequisites

The system setting ClientSyncCommunicationMethod must be enabled, and a valid AI provider must be registered. For the purpose of this tutorial, we will be using the below table:

-- Check if the schema already exists
IF SCHEMA_ID('StreamingFeatureTutorial') IS NOT NULL
BEGIN
	PRINT 'Error: The schema already exists and this script has most likely already been executed in this database.';
	SELECT 1/0; -- Stop here
END;

-- Create schema
IF SCHEMA_ID('StreamingFeatureTutorial') IS NULL
BEGIN
	EXEC(N'CREATE SCHEMA StreamingFeatureTutorial AUTHORIZATION [dbo]');
END;
GO

CREATE TABLE StreamingFeatureTutorial.ChatMessage
(
	ChatMessageId int NOT NULL IDENTITY(1, 1),
	ChatId int NOT NULL,
	ParticipantId varchar(20) NULL,
	Message nvarchar(MAX) NULL,
	MessageMarkdown nvarchar(MAX) NULL,
	IsSystemMessage bit NOT NULL,
	IsStreaming bit NOT NULL,
	SentDatetimeUtc datetime2(7) NOT NULL CONSTRAINT DF_ChatMessage_SentDatetimeUtc DEFAULT (SYSUTCDATETIME()),
	UpdateDatetimeUtc datetime2(7) NULL,

	CONSTRAINT PK_ChatMessage PRIMARY KEY (ChatMessageId)
);
  • ParticipantId is nullable because system messages do not have an associated participant.
  • MessageMarkdown is used for AI messages because models typically generate responses in Markdown.
  • UpdateDatetime is crucial to keep track of the message that is being streamed.

Part 1) GenAI

Create the menu item

Begin by creating a GenAI menu item, include ChatId and AssistantMessageId as passing fields, as they will be needed later.

Streaming must also be enabled under GenAI settings.
GenAi settings

Stored procedure

Create the procedure StreamingFeatureTutorial.GenAi which will be used by the menu item.

Note: The procedure uses the system's default AI provider, model, and endpoint. These can be overridden with ProviderAlias, ModelAlias, and EndpointAlias, respectively.

CREATE OR ALTER PROCEDURE StreamingFeatureTutorial.GenAi
	@Action varchar(30) = NULL,
	@Response nvarchar(MAX) = NULL,
	@Chunk nvarchar(MAX) = NULL,
	@ErrorMessage nvarchar(MAX) = NULL,

	-- Passing fields
	@ChatId int = NULL,
	@AssistantMessageId int = NULL
AS
BEGIN
	SET NOCOUNT, XACT_ABORT ON;

	IF @Action = 'Init'
	BEGIN
		SELECT
			'StreamingFeatureTutorial' AS SyncEntitySchemaName,
			'Chat' AS SyncEntityObjectName,
			@ChatId AS SyncId;

		SELECT
			CASE ParticipantId
				WHEN 'Assistant' THEN 'assistant'
				ELSE 'user'
			END AS Role,
			COALESCE(M.Message, M.MessageMarkdown) AS Message
		FROM
			StreamingFeatureTutorial.ChatMessage M
		WHERE
			M.ChatMessageId <> @AssistantMessageId AND
			M.ChatId = @ChatId AND
			M.ParticipantId IS NOT NULL -- Exclude system messages
		ORDER BY
			ChatMessageId;
	END;

	IF @Action = 'StoreChunk'
	BEGIN
		UPDATE StreamingFeatureTutorial.ChatMessage SET
			MessageMarkdown = MessageMarkdown + @Chunk,
			UpdateDatetimeUtc = SYSUTCDATETIME()
		WHERE
			ChatMessageId = @AssistantMessageId;
	END;

	IF @Action = 'StoreResponse'
	BEGIN
		UPDATE StreamingFeatureTutorial.ChatMessage SET
			MessageMarkdown = @Response,
			IsStreaming = 0,
			UpdateDatetimeUtc = SYSUTCDATETIME()
		WHERE
			ChatMessageId = @AssistantMessageId;
	END;

	IF @Action = 'StoreError'
	BEGIN
		UPDATE StreamingFeatureTutorial.ChatMessage SET
			IsStreaming = 0,
			UpdateDatetimeUtc = SYSUTCDATETIME()
		WHERE
			ChatMessageId = @AssistantMessageId;

		INSERT StreamingFeatureTutorial.ChatMessage
		(
			ChatId,
			ParticipantId,
			Message,
			IsSystemMessage,
			IsStreaming
		)
		VALUES
		(
			@ChatId,
			NULL,
			'An unexpected error occured...',
			1,
			0
		);
	END;

	IF @Action = 'Finished'
	BEGIN
		-- Not used in this tutorial.
		RETURN;
	END;
END;

The error created in StoreError should not expose the actual error message.

Important for streaming
  • Streaming component setting enabled.
  • SyncEntitySchemaName, SyncEntityObjectName, and SyncId used in the Init action, matching the entity given to the chat menu item.
  • The StoreChunk action appending the new chunk to the streaming message.

Part 2) Chat

Create the menu item

Begin by creating a Chat menu item, make sure to tick Enable client sync. Create an entity with the Schema name "StreamingFeatureTutorial" and Object name "Chat". It is also recommended to enable the Show thinking indicator setting.
Chat settings

Stored procedure

Create the procedure StreamingFeatureTutorial.Chat which will be used by the menu item. Include the UserId session variable as a passing field.

Note: For this tutorial, UserId is used as the chat and client sync ID. In a real implementation, it is recommended to use a dedicated conversation ID instead, allowing each user to have multiple chats.

CREATE OR ALTER PROCEDURE StreamingFeatureTutorial.Chat
	@Action varchar(30) = NULL,
	@Message nvarchar(max) = NULL,
	@MessageId int = NULL, -- Used for EditMessage and DeleteMessage, which are not used in this tutorial.

	@SyncId varchar(100) = NULL,
	@SyncTimestamp varchar(100) = NULL,

	@UserId int = NULL
AS
BEGIN
	SET NOCOUNT, XACT_ABORT ON;

	DECLARE @ChatId int = @UserId;

	IF @Action = 'Init'
	BEGIN
		SELECT
			CONVERT(bit, 1) AS ShowAiWarning,
			@ChatId AS SyncId,
			SYSUTCDATETIME() AS SyncTimestamp;
	END;

	IF @Action = 'GetChatData'
	BEGIN
		SELECT
			'User' AS ParticipantId,
			'User' AS DisplayName,
			CONVERT(bit, 1) AS IsSelf,
			CONVERT(bit, 0) AS IsAi
		UNION ALL
		SELECT
			'Assistant' AS ParticipantId,
			'Assistant' AS DisplayName,
			CONVERT(bit, 0) AS IsSelf,
			CONVERT(bit, 1) AS IsAi;

		SELECT
			M.ChatMessageId AS MessageId,
			M.ParticipantId,
			M.Message,
			M.MessageMarkdown,
			M.IsSystemMessage,
			M.IsStreaming,
			M.SentDatetimeUtc
		FROM
			StreamingFeatureTutorial.ChatMessage M
		WHERE
			M.ChatId = @ChatId
		ORDER BY
			M.ChatMessageId;
	END;

	IF @Action = 'StoreMessage'
	BEGIN
		INSERT StreamingFeatureTutorial.ChatMessage
		(
			ChatId,
			ParticipantId,
			Message,
			IsSystemMessage,
			IsStreaming
		)
		VALUES
		(
			@ChatId,
			'User',
			@Message,
			0,
			0
		);

		INSERT StreamingFeatureTutorial.ChatMessage
		(
			ChatId,
			ParticipantId,
			MessageMarkdown,
			IsSystemMessage,
			IsStreaming
		)
		VALUES
		(
			@ChatId,
			'Assistant',
			N'',
			0,
			1
		);

		DECLARE @AssistantMessageId int = SCOPE_IDENTITY();

		SELECT
			'GenAiResponse' AS LinkAlias,
			CONVERT(bit, 1) AS RunInBackground,
			@ChatId AS ChatId,
			@AssistantMessageId AS AssistantMessageId;
	END;

	IF @Action = 'EditMessage'
	BEGIN
		-- Not used in this tutorial.
		RETURN;
	END;

	IF @Action = 'DeleteMessage'
	BEGIN
		-- Not used in this tutorial.
		RETURN;
	END;

	IF @Action = 'ClientSync'
	BEGIN
		SELECT
			M.ChatMessageId AS MessageId,
			M.ParticipantId,
			M.Message,
			M.MessageMarkdown,
			M.IsSystemMessage,
			M.IsStreaming,
			M.SentDatetimeUtc
		FROM
			StreamingFeatureTutorial.ChatMessage M
		WHERE
			M.ChatId = @SyncId AND
			(
				M.UpdateDatetimeUtc > @SyncTimestamp
				OR
				M.SentDatetimeUtc > @SyncTimestamp
			)
		ORDER BY
			M.ChatMessageId;

		SELECT
			SYSUTCDATETIME() AS SyncTimestamp;
	END;
END;

When a menu item executes with RunInBackground enabled, errors will not result in the usual dialog and the chat will still display the thinking indicator, as if the response is still being generated. Therefore, it is important to handle such errors in the BackgroundError action, in this example we clear the IsStreaming bit and create a system message informing the user that an error has occurred.

When messages are returned from the ClientSync action, the chat performs a partial update on the messages, allowing the content of the streamed message to be updated.

Important for streaming
  • The StoreMessage action inserts two messages:
    • The user's message
    • An empty assistant message with IsStreaming = 1 which will be updated by the GenAI procedure.
  • The ChatAction result set starts the GenAI menu item through the GenAiResponse forward link, with RunInBackground = 1.
  • The ClientSync action returns messages where SentDatetimeUtc or UpdateDatetimeUtc is newer than the previous SyncTimestamp.
  • The BackgroundError removes IsStreaming from the message being streamed.

The StoreMessage action forwards to the GenAI menu item using a forward link with the alias GenAiResponse. Create this forward link from the Chat menu item to the GenAI menu item, and add ChatId and AssistantMessageId as column value passing fields.
Forward link settings

Conclusion

The chat should now be fully functional and stream AI responses incrementally.

When the user sends a message, the Chat procedure stores both the user message and an empty streaming assistant message. The GenAI menu item then runs in the background, appends each streamed chunk to that assistant message, and raises client sync notifications so the Chat component can refresh the visible message while the response is still being generated.

This tutorial intentionally keeps the data model simple by using UserId as the chat ID and by hard-coding the participants. Ideally there would be a dedicated conversation table, a participant table, and more complete error handling.

Cleanup

The script below can be used to drop the SQL objects created in this tutorial. If you named the objects differently, you will need to drop them manually. The menu items must also be deleted manually.

DROP PROCEDURE IF EXISTS StreamingFeatureTutorial.Chat;
DROP PROCEDURE IF EXISTS StreamingFeatureTutorial.GenAi;
DROP TABLE IF EXISTS StreamingFeatureTutorial.ChatMessage;
DROP SCHEMA IF EXISTS StreamingFeatureTutorial;